Oracle 12c multitenant database normally uses the same oracle networking files as other Oracle RDBMS implementations. This article covers the configuration of the tnsnames.ora file for connection to a PDB database cdb1.
- Connect to your oracle database server as the oracle software owner.
[larry@linux2 ~]$ su – oracle
Password:
Last login: Mon Apr 24 10:45:22 EDT 2016 on pts/1
[oracle@linux2 ~]$
- You can use the following code to create the appropriate tnsnames.ora entries in the file $ORACLE_HOME/network/admin/tnsnames.ora or manually insert into the file knowing the machine IP, Port, SID, and service name.
NOTE: You have to edit the parameter SERVICE_NAME and Alias, in below example they are pdb1 and PDB1 respectively. PDBs do not have SID listings because they are Services which are contained within a CDB and only CDBs have SIDs in multitenant architecture.
echo -e “PDB1 =\n” \
” (DESCRIPTION =\n” \
” (ADDRESS_LIST =\n” \
” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \
” )\n” \
” (CONNECT_DATA =\n” \
” (SERVER = DEDICATED)\n” \
” (SERVICE_NAME = pdb1)\n” \
” (SID = “$ORACLE_SID”)\n” \
” )\n” \
” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora
Execution Example:
[oracle@linux2 admin]$ echo -e “PDB1 =\n” \
> ” (DESCRIPTION =\n” \
> ” (ADDRESS_LIST =\n” \
> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \
> ” )\n” \
> ” (CONNECT_DATA =\n” \
> ” (SERVER = DEDICATED)\n” \
> ” (SERVICE_NAME = pdb1)\n” \
> ” (SID = “$ORACLE_SID”)\n” \
> ” )\n” \
> ” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora
[oracle@linux2 admin]
- After execution or editing, you can view the contains of the tnsnames.ora file with the cat command as shown below:
[oracle@linux2 admin]$ cat tnsnames.ora
CDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1)
(SID = cdb1)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
(SID = cdb1)
)
)
[oracle@linux2 admin]$
- As long as the listener processes is started, the container database is up and running, and the respective PDB is up; you can test the connection through the tnsping utility by suppling the reference alias name in the tnsnames.ora file. In this example the reference name is PDB1, as shown below.
[oracle@linux2 admin]$ tnsping PDB1
TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 24-APR-2016 11:56:34
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1) (SID = cdb1)))
OK (0 msec)
[oracle@linux2 admin]$
- This completes setup of tnsnames.ora file for PDB connection in Oracle 12c.
Larry Catt
OCP