CONNECT TO PDB IN ORACLE MULTITENANT 19C
Configure the TNS entry for pdb connection in tnsnames.ora file
[oracle@xhydra bin]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
PDBZX1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.99)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDBZX1)
)
)
Check the tnsping connectivity using service name
[oracle@xhydra bin]$ tnsping PDBZX1
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-MAY-2022 10:06:25
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.99)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PDBZX1)))
OK (10 msec)
Check the listener for active services
[oracle@xhydra bin]$ lsnrctl services|grep 'pdbzx1'
Service "pdbzx1" has 1 instance(s).
Connect to PDB directly with credentials using the login account
[oracle@xhydra bin]$ sqlplus sys/password@pdbzx1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 19 09:30:30 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
kIsH@Xhydra<>show con_id;
CON_ID
------------------------------
3
kIsH@Xhydra<>show con_name;
CON_NAME
------------------------------
PDBZX1
Or use sysdba to connect to the container
[oracle@xhydra bin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 19 09:12:12 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
kIsH@Xhydra<>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBZX1 READ WRITE NO
4 PDBZX2 READ WRITE NO
kIsH@Xhydra<>alter session set container=PDBZX1;
Session altered.
kIsH@Xhydra<>show con_id;
CON_ID
------------------------------
3
Check the view v$pdbs and cdb_pdbs
kIsH@Xhydra<>col NAME for a20
kIsH@Xhydra<>select name,con_id,open_mode,dbid from v$pdbs;
NAME CON_ID OPEN_MODE DBID
-------------------- ---------- ---------- ----------
PDBZX1 3 READ WRITE 3704848633
kIsH@Xhydra<>col PDB_NAME for a20
kIsH@Xhydra<>select PDB_ID,PDB_NAME,DBID,STATUS,LOGGING,CON_ID from cdb_pdbs;
PDB_ID PDB_NAME DBID STATUS LOGGING CON_ID
---------- -------------------- ---------- ---------- --------- ----------
3 PDBZX1 3704848633 NORMAL LOGGING 3
2 PDB$SEED 3998211234 NORMAL LOGGING 2
4 PDBZX2 2815989561 NORMAL LOGGING 4
To check the services of the pdbs
kIsH@Xhydra<>col NAME for a20
kIsH@Xhydra<>col NETWORK_NAME for a20
kIsH@Xhydra<>col CON_NAME for a20
kIsH@Xhydra<>select name,network_name,con_name,con_id from v$active_services;
NAME NETWORK_NAME CON_NAME CON_ID
-------------------- -------------------- -------------------- ----------
pdbzx2 pdbzx2 PDBZX2 4
SYS$BACKGROUND CDB$ROOT 1
SYS$USERS CDB$ROOT 1
pdbzx1 pdbzx1 PDBZX1 3
db9zx db9zx CDB$ROOT 1
db9zxXDB db9zxXDB CDB$ROOT 1
6 rows selected.
To show the current container
kIsH@Xhydra<>kIsH@Xhydra<>show con_id con_name
CON_ID
------------------------------
1
CON_NAME
------------------------------
CDB$ROOT