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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s