BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 12c - How to create new service name for PDB?

2018-01-10

Oracle 12c - How to create new service name for PDB?

We can use service_names to name or add service for database.

However we can NOT use service_names to name or add service for PDB container. So How to create new service name for PDB?

SYS> show parameter service_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 PROD.example.com
SYS>  -

Showing the Services Associated with PDBs

SYS@PRODCDB> 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
	 4 PRODCDB		CDB$ROOT		      1
	 3 PRODCDBXDB		CDB$ROOT		      1
	 2 SYS$USERS		CDB$ROOT		      1
	 6 pdbprod1.example.com PDBPROD1		      3
	 7 pdbprod2.example.com PDBPROD2		      4
	 8 pdbprod3.example.com PDBPROD3		      5

7 rows selected.

SYS@PRODCDB>

Creating a Service for a PDB

We can use pacakge#dbms_service to create another service for pdb.

SYS@PRODCDB> 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
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

SYS@PRODCDB> 

Step 1. Change to pdb container;

SYS@PRODCDB> alter session set container=pdbprod1;

Session altered.

SYS@PRODCDB>

SYS@PRODCDB> select con_id,name,open_mode from v$containers;

	CON_ID NAME 		OPEN_MODE
---------- -------------------- ----------
	 3 PDBPROD1		READ WRITE

SYS@PRODCDB>

Step 2. Create service for current pdb using name and new network service name.

SYS@PRODCDB> exec dbms_service.CREATE_SERVICE('pdb1','pdbprod1.us.oracle.com');

PL/SQL procedure successfully completed.

SYS@PRODCDB> select service_id,name,pdb,con_id from cdb_services order by con_id;

SERVICE_ID NAME 		PDB			 CON_ID
---------- -------------------- -------------------- ----------
	 6 pdbprod1.example.com PDBPROD1		      3
	 1 pdb1 		PDBPROD1		      3

SYS@PRODCDB>

Step 3. start the new service

Do forget thie step!!!

SYS@PRODCDB> exec dbms_service.start_service('pdb1');

PL/SQL procedure successfully completed.

SYS@PRODCDB>        

SYS@PRODCDB> alter system register;

System altered.

SYS@PRODCDB>  

Step 4. lsnrclt and connect check

pdbprod1.us.oracle.com is new service name in lsnrctl.

lsnrctl status 

Service "pdbprod1.us.oracle.com" has 1 instance(s).
  Instance "PRODCDB", status READY, has 1 handler(s) for this service...

connect test:

sqlplus system/oracle@host01:1521/pdbprod1.us.oracle.com

Step 5. Save the state for PDB.

SQL> alter pluggable database save state;

Need save the state for pluggable for the new pdb service.

Otherwise, you need to manually start the service after you restart the pdb just like the following test.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 10-OCT-2018 01:32:39

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=emccsvr.oracle.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                08-OCT-2018 03:27:11
Uptime                    1 days 22 hr. 5 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/emccsvr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=emccsvr.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "77133bc555247726e0530100007fa8fe.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "pdb1.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> select service_id,name,pdb,con_id from cdb_services order by con_id;

SERVICE_ID NAME       PDB            CON_ID
---------- ---------- ---------- ----------
		 8 PDB1       PDB1                3

SQL>
SQL> exec dbms_service.create_service('prodpdb1','prodpdb1');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_service.start_service('prodpdb1');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 10-OCT-2018 01:37:01

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=emccsvr.oracle.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                08-OCT-2018 03:27:11
Uptime                    1 days 22 hr. 9 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/emccsvr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=emccsvr.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "77133bc555247726e0530100007fa8fe.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "pdb1.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "prodpdb1.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL>
SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 3 PDB1                           READ WRITE NO
SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 10-OCT-2018 01:37:45

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=emccsvr.oracle.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                08-OCT-2018 03:27:11
Uptime                    1 days 22 hr. 10 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/emccsvr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=emccsvr.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "77133bc555247726e0530100007fa8fe.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "pdb1.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> alter system register;

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 10-OCT-2018 01:38:01

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=emccsvr.oracle.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                08-OCT-2018 03:27:11
Uptime                    1 days 22 hr. 10 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/emccsvr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=emccsvr.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "77133bc555247726e0530100007fa8fe.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "pdb1.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL>
SQL> select service_id,name,pdb,con_id from cdb_services order by con_id;

SERVICE_ID NAME       PDB            CON_ID
---------- ---------- ---------- ----------
		 8 PDB1       PDB1                3
		 1 prodpdb1   PDB1                3

SQL> exec dbms_service.start_service('prodpdb1');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status;

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 10-OCT-2018 01:39:45

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=emccsvr.oracle.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                08-OCT-2018 03:27:11
Uptime                    1 days 22 hr. 12 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/emccsvr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=emccsvr.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "77133bc555247726e0530100007fa8fe.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "pdb1.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "prodpdb1.us.oracle.com" has 1 instance(s).		=================================================> new pdb service.
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> alter pluggable database save state;

Pluggable database altered.

SQL> alter pluggable database close;

Pluggable database altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 10-OCT-2018 01:40:25

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=emccsvr.oracle.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                08-OCT-2018 03:27:11
Uptime                    1 days 22 hr. 13 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/emccsvr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=emccsvr.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "77133bc555247726e0530100007fa8fe.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "pdb1.us.oracle.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "prodpdb1.us.oracle.com" has 1 instance(s).		=================================================> new pdb service.
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@emccsvr ~]$
[oracle@emccsvr ~]$ sqlplus system/welcome@127.0.0.1:1521/prodpdb1.us.oracle.com

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Oct 10 01:45:50 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Mon Oct 08 2018 05:24:53 -04:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL>

Reference documents

Database Administrator's Guide / 42 Administering PDBs with SQL*Plus 

++++++++++++++++ EOF LinHong ++++++++++++++++


Similar Posts

Comments