Oracle 12c pdb/cdb 监听和tns 配置

拼搏现实的明天。 2022-06-12 06:08 797阅读 0赞

1.  
PDB is not an instance, so using SID in the connection string will not work.  
When the database is an Oracle Database 12c container database, the client must specify a service name in order to connect to it.  
Listener status shows TEST as only a service :

监听服务信息应该如下“  
pdb:/home/oracle@oracle>lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2017 00:10:04

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))  
STATUS of the LISTENER  
\------------------------  
Alias                     LISTENER  
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production  
Start Date                16-JUL-2017 16:57:45  
Uptime                    0 days 7 hr. 12 min. 19 sec  
Trace Level               off  
Security                  ON: Local OS Authentication  
SNMP                      OFF  
Listener Parameter File   /u01/app/oracle/product/12.2.0/db\_1/network/admin/listener.ora  
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml  
Listening Endpoints Summary...  
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))  
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))  
Services Summary...  
Service "5471ce802ce230b7e0538d01a8c03136" has 1 instance(s).  
  Instance "pdb", status READY, has 1 handler(s) for this service...  
Service "cdb1" has 1 instance(s).  
  Instance "pdb", status READY, has 1 handler(s) for this service...  
Service "pdb" has 1 instance(s).  
  Instance "pdb", status READY, has 1 handler(s) for this service...  
Service "pdbXDB" has 1 instance(s).  
  Instance "pdb", status READY, has 1 handler(s) for this service...  
The command completed successfully

2.12C 不推荐静态注册，建议动态注册

.动态注册方法信息如下：

To resolve this, make sure that you do the following:  
1. Backup then edit the listener.ora file to REMOVE the "static" SID\_DESC sections for these PDBs  
2. Make sure the Database knows where to register by explicitly setting the LOCAL\_LISTENER to any of the end points (addresses) that this listener is listening on.  
 Within the PDB issue the following statement:

SQL> alter session set container=cdb1;

Session altered.

SQL> show parameter listener\_network

NAME         TYPE  VALUE  
\------------------------------------ ----------- ------------------------------  
listener\_networks       string

SQL> alter system set listener\_networks='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.141)(PORT = 1521))' scope=spfile;

System altered.

3. Restart the Listener and db.

Always use Dynamic Services for connections. Do not use SID which is very old and obsolete with respect to how connections should be established or serviced.

This would not apply to certain components such as DG or RMAN which sometimes requires connections via a "Static Service" due to the Database not being in an OPEN (and therefore not "ready") status. See the following: Understanding Static Service Registration

\-》 静态注册方法信息如下：  
•Use of external procedure calls  
•Use of Oracle Heterogeneous Services  
•Use of Oracle Data Guard  
•Remote database startup from a tool other than Oracle Enterprise Manager Cloud Control  
•Connections to Oracle databases earlier than Oracle8i release 2 (8.1)

1、配置监听

首先要明确，所有的PDB都使用1个监听，配置多个实际上启动时也只有第1个有意义。

LISTENER=  
  (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.141)(PORT = 1521))  
  )

接下来使用SID\_LIST\_LISTENER来进行静态注册服务。  
SID\_LIST\_LISTENER=  
(SID\_LIST=  
  (SID\_DESC =  
  (GLOBAL\_DBNAME = cdb1) \#该服务是我配置的cdb信息  
  (SID\_NAME = cdb1)  
  )

(SID\_DESC =  
  (GLOBAL\_DBNAME = pdb)\#该服务是我配置的pdb信息  
  (SID\_NAME = cdb1)  
  )  
)

ADR\_BASE\_LISTENER= /u01/app/oracle

2、tnsnames.ora配置  
观察发现，在tnsnames中配置pdb跟CDB，即原来11g的配置完全一样。这里SERVICE\_NAME = pdb使用得是PDB的名字，可以在v$pdbs中查看。

CDB =  
  (DESCRIPTION =  
    (ADDRESS\_LIST =  
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.141)(PORT = 1521))  
    )  
    (CONNECT\_DATA =  
      (SERVICE\_NAME = cdb1)  
    )  
  )

PDB =  
  (DESCRIPTION =  
    (ADDRESS\_LIST =  
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.141)(PORT = 1521))  
    )  
    (CONNECT\_DATA =  
      (SERVICE\_NAME = pdb)  
    )  
  )

3、为了保险，检查下sqlnet.ora  
NAMES.DIRECTORY\_PATH=(TNSNAMES, EZCONNECT)  
这一句是为了保证优先使用TNSNAMES解析。  
配置完，在PDB启动的情况下（怎么启动这里不多讲了），就可以直接连接到PDB了。（Oracle12c是没有scott用户的，我自己在PDB下面新建的）。