With Oracle 12c multitenant databases connection is still normally performed through the Oracle networking files of listener.ora, tnsnames.ora, and sqlnet.ora. This articles shows the configuration of listener.ora file to support connection to a CDB database called cdb1.
- Logon to your Oracle Server as the oracle software owner.
[larry@linux2 ~]$ su – oracle
Password:
Last login: Mon FEB 24 09:57:56 EDT 2016 on pts/0
[oracle@linux2 ~]$
- Create the file listener.ora with the following statement:
echo -e “SID_LIST_LISTENER =\n” \
” (SID_LIST =\n” \
” (SID_DESC =\n” \
” (SID_NAME = “$ORACLE_SID”)\n” \
” (SERVICE_NAME = “$ORACLE_SID”)\n” \
” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \
” (ORACLE_HOME = “$ORACLE_HOME”)\n” \
” )\n” \
” )\n” \
“\nLISTENER =\n” \
” (DESCRIPTION_LIST =\n” \
” (DESCRIPTION =\n” \
” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \
” ))\n” \
“\nADR_BASE_LISTENER = /opt/app/oracle\n” \
“\nLOGGING_LISTENER=ON\n” \
“\nTRACE_LEVEL_SERVER=SUPPORT\n” \
“\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora
Execution example:
[oracle@linux2 admin]$ echo -e “SID_LIST_LISTENER =\n” \
> ” (SID_LIST =\n” \
> ” (SID_DESC =\n” \
> ” (SID_NAME = “$ORACLE_SID”)\n” \
> ” (SERVICE_NAME = “$ORACLE_SID”)\n” \
> ” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \
> ” (ORACLE_HOME = “$ORACLE_HOME”)\n” \
> ” )\n” \
> ” )\n” \
> “\nLISTENER =\n” \
> ” (DESCRIPTION_LIST =\n” \
> ” (DESCRIPTION =\n” \
> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \
> ” ))\n” \
> “\nADR_BASE_LISTENER = /opt/app/oracle\n” \
> “\nLOGGING_LISTENER=ON\n” \
> “\nTRACE_LEVEL_SERVER=SUPPORT\n” \
> “\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora
- Now start the listener with the command: lsnrctl start
[oracle@linux2 ~]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 10:58:06
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 24-FEB-2016 10:58:06
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))
Services Summary…
Service “cdb1” has 1 instance(s).
Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
[oracle@linux2 ~]$
- You can test the listener configuration by suppling the tnsping utility with the <machine_IP>:<Port>/<service_name> as below:
[oracle@linux2 admin]$ tnsping 10.30.15.75:1521/cdb1
TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 11:04:40
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=cdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))
OK (10 msec)
[oracle@linux2 admin]$
- This completes configuration of listener.ora file for CDB.
Larry Catt
OCP