12C cdb/pdb 配置监听

 

 

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 :

监听服务信息应该如下“
Listener status shows TEST as only a service :

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.oracle.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CDB1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service.
Service "TEST" has 1 instance(s).
Instance "cdb1", 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:

 

alter session set container=PDB1;

alter system set listener_networks='(( NAME=listener)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.com)(PORT=15021)))))' scope=spfile;

 

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 =ora12c_A)(PORT = 1521))
  )


接下来使用SID_LIST_LISTENER来进行静态注册服务。
SID_LIST_LISTENER=
(SID_LIST=
  (SID_DESC =
  (GLOBAL_DBNAME = ora12c) #该服务是我配置的cdb信息
  (SID_NAME = ora12c)
  )

  (SID_DESC =
  (GLOBAL_DBNAME = pdborcl)#该服务是我配置的pdb信息
  (SID_NAME = ora12c)
  )
)

ADR_BASE_LISTENER= /opt/oracle

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

ORA12C=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =ora12c_A)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora12c)
    )
  )

pdborcl=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =ora12c_A)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdborcl)
    )
  )


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





############sample 2  19c 没有生效
 
19c 主机名发生变化， 同时修改listener.ora 和 tnsnames,ora  指向新的主机名， 然后动态注册服务名，alter system register,并没有生效

show parameter local_listener
->point to listener.ora

show parameter service_name
->point to tnsnames.ora

alter system register

 ############### listener.ora
$ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /dd/oracle/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = os01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = dd1521))
    )
  )

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
 
############tnsnames.ora
$ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /dd/oracle/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
Cdd =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = os01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdd)
    )
  )
LISTENER_Cdd =
  (ADDRESS = (PROTOCOL = TCP)(HOST = os01)(PORT = 1521))
 
dd =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = os01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dd)
    )
  )

alter system register;


解决办法

restart cdb