查询pdb的服务关系
SYS@orcl>col name for a25;
SYS@orcl>col pdb for a25;
SYS@orcl>select service_id,name,pdb,con_id from cdb_services order by con_id;
SERVICE_ID NAME PDB CON_ID
---------- ------------------------- ------------------------- ----------
1 SYS$BACKGROUND CDB$ROOT 1
6 orcl.example.local CDB$ROOT 1
2 SYS$USERS CDB$ROOT 1
3 orcl CDB$ROOT 1
5 orclXDB CDB$ROOT 1
7 testpdb TESTPDB 2
6 rows selected.
SYS@orcl>为pdb创建一个服务
可以使用dbms_service 包为pdb创建另一个service。
SYS@orcl>desc dbms_service
PROCEDURE CREATE_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
NETWORK_NAME VARCHAR2 IN
PARAMETER_ARRAY TABLE OF VARCHAR2(100) IN
PROCEDURE CREATE_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
NETWORK_NAME VARCHAR2 IN
GOAL NUMBER IN DEFAULT
DTP BOOLEAN IN DEFAULT
AQ_HA_NOTIFICATIONS BOOLEAN IN DEFAULT
FAILOVER_METHOD VARCHAR2 IN DEFAULT
FAILOVER_TYPE VARCHAR2 IN DEFAULT
FAILOVER_RETRIES NUMBER IN DEFAULT
FAILOVER_DELAY NUMBER IN DEFAULT
CLB_GOAL NUMBER IN DEFAULT
EDITION VARCHAR2 IN DEFAULT
PROCEDURE DELETE_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
PROCEDURE DISCONNECT_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
DISCONNECT_OPTION NUMBER IN DEFAULT
GUID VARCHAR2 IN DEFAULT
PROCEDURE KILL_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
KILL_OPTION NUMBER IN DEFAULT
PROCEDURE MODIFY_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
PARAMETER_ARRAY TABLE OF VARCHAR2(100) IN
PROCEDURE MODIFY_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
GOAL NUMBER IN DEFAULT
DTP BOOLEAN IN DEFAULT
AQ_HA_NOTIFICATIONS BOOLEAN IN DEFAULT
FAILOVER_METHOD VARCHAR2 IN DEFAULT
FAILOVER_TYPE VARCHAR2 IN DEFAULT
FAILOVER_RETRIES NUMBER IN DEFAULT
FAILOVER_DELAY NUMBER IN DEFAULT
CLB_GOAL NUMBER IN DEFAULT
EDITION VARCHAR2 IN DEFAULT
MODIFY_EDITION BOOLEAN IN DEFAULT
PROCEDURE START_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
INSTANCE_NAME VARCHAR2 IN DEFAULT
PROCEDURE STOP_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
INSTANCE_NAME VARCHAR2 IN DEFAULT
STOP_OPTION VARCHAR2 IN DEFAULT
DRAIN_TIMEOUT NUMBER IN DEFAULT
REPLAY BOOLEAN IN DEFAULT
PROCEDURE TAG_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2 IN
GUID VARCHAR2 IN
SYS@orcl>切换到pdb容器
SYS@orcl>alter session set container = testpdb;
Session altered.
SYS@TESTPDB>select con_id,name,open_mode from v$containers;
CON_ID NAME OPEN_MODE
---------- ------------------------- --------------------
2 TESTPDB READ WRITE
SYS@TESTPDB>为当前容器创建新service
SYS@TESTPDB>exec dbms_service.CREATE_SERVICE('testpdb1','testpdb1');
PL/SQL procedure successfully completed.
SYS@orcl>select service_id,name,pdb,con_id from cdb_services order by con_id;
SERVICE_ID NAME PDB CON_ID
---------- ------------------------- ------------------------- ----------
16 testpdb TESTPDB 2
1 testpdb1 TESTPDB 2
SYS@TESTPDB>启动新的服务
千万不要忘记这一步！！！
SYS@TESTPDB>exec dbms_service.start_service('testpdb1');
PL/SQL procedure successfully completed.
SYS@TESTPDB>alter system register;
System altered.
SYS@TESTPDB>检查监听中注册的服务
SYS@TESTPDB>!lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 12-JUL-2019 20:22:23
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db.digeekao.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 03-JUL-2019 20:18:31
Uptime 9 days 0 hr. 3 min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/network/admin/listener.ora
Listener Log File /u01/app/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
......
Service "testpdb" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "testpdb1" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
......
SYS@TESTPDB>保存pdb设置
SYS@TESTPDB> alter pluggable database save state;
Pluggable database altered.
SYS@TESTPDB>