Oracle Multitenant - Create new service for PDB using DBMS_SERVICE
When an Oracle PDB is created it starts up a default service
with the PDB name. For direct connections to
PDB this service can be used. But there are situations where new service
name is required in PDB level, common example is after a PDB restoration/clone,
in order to enable application connections without changes in connection string. Setting service_names parameter in PDB level is not possible hence it is not an option in multitenant setup.
How to create a new PDB service on Oracle 12C
Check the current services, here only the default service is running, SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 NFACTORPDB READ WRITE NO
SQL> select service_id, name, pdb from dba_Services;
SERVICE_ID NAME PDB
---------- ---------------------------------------- ----------
7 nfactorpdb NFACTORPDB
SQL>
Create the new service using DBMS_SERVICE.create_service,
This procedure accepts multiple inputs but the mandatory to
create the service are, service_name and the network name. Here the network
name refers to a tnsnames.ora entry containing the new service name. For the
example we assume the service name new_service
Add below tns entry to tnsnames.ora,
NEW_SERVICE=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=192.168.7.7)
(PORT=1539)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=new_service)
)
)
Switch to pdb and create the service
SQL> alter session set container=NFACTORPDB;
Session altered.
SQL> BEGIN
DBMS_SERVICE.create_service(
service_name => 'new_service',
network_name => 'NEW_SERVICE'
);
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL>
Start the newly created service,
SQL> exec dbms_service.start_service('new_service');
PL/SQL procedure successfully completed.
SQL>
Save state the PDB. Other wise service needs to be manually started after PDB open each time.
SQL> alter pluggable database save state;
Pluggable database altered.
SQL>
Service will be registered in listener as below and tt is now possible to connect to the PDB directly using this new service name.
oracle@nptdb1:/export/home/oracle$ sqlplus servt@new_service
SQL*Plus: Release 12.2.0.1.0 Production on Sun Aug 2 22:52:27 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Aug 02 2020 22:52:04 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> col name format a20
SQL> set lines 1000
SQL> select dbid, con_id, name from v$pdbs;
DBID CON_ID NAME
---------- ---------- --------------------
2347017102 3 NFACTORPDB
SQL> exit
Connecting using the default service
oracle@nptdb1:~$ sqlplus sys as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Aug 2 06:20:23 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 NFACTORPDB READ WRITE NO
SQL>
SQL> alter session set container=NFACTORPDB;
Session altered.
SQL>
TNS
NFACPDB=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=192.168.7.7)
(PORT=1539)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=NFACTORPDB)
)
)
oracle@nptdb1:/export/home/oracle$ sqlplus servt@nfactorpdb
SQL*Plus: Release 12.2.0.1.0 Production on Sun Aug 2 22:54:44 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Aug 02 2020 22:52:31 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> col name format a20
SQL> set lines 1000
SQL> select dbid, con_id, name from v$pdbs;
DBID CON_ID NAME
---------- ---------- --------------------
2347017102 3 NFACTORPDB
SQL>
Related Articles --
Comments
Post a Comment