Unable to connect to PDB?
Issue: Same PDB name on 2 CDBs and tns connection will be in trouble.
12c Multitenant: How to Connect to PDBs with Same Name in Different CDBs (Doc ID 2123106.1)
Solution: Include INSTANCE_NAME in TNS entries
DEVCDB (PDB1)
sqlplus user1/user1@PDB1_DEVCDB
sqlplus mallik/mallik@PDB1_DEVCDB
TESTCDB(PDB1)
sqlplus user2/user2@PDB1_TESTCDB
sqlplus mallik/mallik@PDB1_TESTCDB
Traditional TNS:
PDB1_DEVCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1) (INSTANCE_NAME=DEVCDB)
)
)
PDB1_TESTCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1)(INSTANCE_NAME=TESTCDB)
)
)
Best Practice for TNS:
PDB1_DEVCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1)(INSTANCE_NAME=DEVCDB)
)
)
PDB1_TESTCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1)(INSTANCE_NAME=TESTCDB)
)
)
Best Practice for Setup:
One Database (Normal Database / CDB Database) - DEVCDB
One Oracle Home - /u01/app/oracle/product/19.0.0.0/dbhome_1
One Listener - LISTENER_DEVCDB (1521)
One Database (Normal Database / CDB Database) - TESTCDB
One Oracle Home - /u01/app/oracle/product/19.0.0.0/dbhome_2
One Listener - LISTENER_TESTCDB (1522)
Current Setup (Not followed Best Practice):
One Oracle Home - /u01/app/oracle/product/19.0.0.0/dbhome_1
One Listener - LISTENER (1251)
Multiple DBs (DEVCDB - PDB1 & TESTCDB - PDB1)
Best Practice for TNS:
PDB1_DEVCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1)(INSTANCE_NAME=DEVCDB)
)
)
PDB1_TESTCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1)(INSTANCE_NAME=TESTCDB)
)
)
[oracle@oraclelab1 admin]$ pwd
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
[oracle@oraclelab1 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@oraclelab1 admin]$ cat tnsnames.ora
DEVCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVCDB)
)
)
TESTCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TESTCDB)
)
)
PDB1_DEVCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1)
)
)
PDB1_TESTCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1)
)
)
[oracle@oraclelab1 admin]$
Regards,
Mallik
No comments:
Post a Comment