How to Connect Container Databases in 12c

This is one of the many questions, that could come up in anyone’s mind. Definitely there are changes in 12c and other old releases.

12c has come with multitenant concept in 12c. So now we can have multiple databases in 1 container database and share the resources.

Next thing is to consider, how can we connect the Root Container (Main Instance of Database) and Pluggable Databases ( Multiple Other Instances under Root Container). Normally all instances are called as container in 12c, except the word changes for root and for pluggable databases.

There is no difference I found connecting to root container. There are few points to think over:

  • We will have /etc/oratab file that will have the entry for root container database name only, no pluggable database name will be there.
  • We don’t have to configure ORACLE_SID in order to connect Pluggable Databases
  • We can connect to root container using OS Authentication from the oracle server itself but we cannot connect to pluggable database if listener is not up. One option is to connect to pluggable database is using ALTER SESSION command that will be executed from the root container.

So how does this connection happen. We will check it first connecting root container and then pluggable databases:

Connecting to Root Container:

Check the /etc/oratab file first:

[oracle@oracle12c ~]$ cat /etc/oratab
#

# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ‘:’, is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, ‘#’, are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , “Y”, or should not,
# “N”, be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
cdb1:/u01/app/oracle/product/12.1.0.2/db_1:N
cdb2:/u01/app/oracle/product/12.1.0.2/db_1:N
cdb3:/u01/app/oracle/product/12.1.0.2/db_1:N
[oracle@oracle12c ~]$

So in my Testing machine, I have 3 Container Databases with name – cdb1, cdb2 and cdb3. So lets connect to cdb1 for now.

[oracle@oracle12c ~]$ . oraenv
ORACLE_SID = [cdb3] ? cdb1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracle12c ~]$

[oracle@oracle12c ~]$ echo $ORACLE_SID
cdb1
[oracle@oracle12c ~]$

So now ORACLE_SID has been configured as cdb3. So to connect to CDB as SYSDBA, it is same as previous versions. We can connect using OS Authentication.

Listener is down in our case, and still we can connect to SYSDBA like older version because it happens because of BEQUEATH Connection ( Oracle’s Bequeath protocol creates that server process for you directly, without the benefit of a listener).

[oracle@oracle12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 11-APR-2016 14:25:59

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory
[oracle@oracle12c ~]$

listener is down, connect to CDB using OS Authentication:

[oracle@oracle12c ~]$ . oraenv
ORACLE_SID = [pdb1] ? cdb1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracle12c ~]$
[oracle@oracle12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 11 14:21:07 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oracle12c ~]$

So that’s how we connect to CDB (Root Container).

Connecting to Pluggable Database:

So now listener is already down we cant connect to pluggable database. First check how many pluggable database is there in root container.

SQL> select name, open_mode from v$pdbs;

NAME                       OPEN_MODE
—————————— ———-
PDB$SEED              READ ONLY
PDB1                         READ WRITE

SQL>

So we have 1 PDB, which is PDB1. So we have to connect to PDB1 now. There are two ways to connect to PDB:

1st way – Using Alter Session Command:

For this way listener does not need to be up if we are connecting from the local server (Server and Client are on the same server), however for any remote connection listener must be up:

[oracle@oracle12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 11 15:00:28 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

SQL> alter session set container=pdb1;

Session altered.

SQL>

2nd way: Using tnsnames.ora file – Connecting using Conn or EZConnect:

First start the listener and check the content of tnsnames.ora file. Listener status is stop as of now, check the status:

[oracle@oracle12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 11-APR-2016 15:03:41

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory
[oracle@oracle12c ~]$

Start the listener:

[oracle@oracle12c ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 11-APR-2016 15:03:45

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Starting /u01/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 /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c.oragyan.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 11-APR-2016 15:03:45
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c.oragyan.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “pdb1.oragyan.com” has 1 instance(s).
Instance “pdb1”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
[oracle@oracle12c ~]$

Check the status again:

[oracle@oracle12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 11-APR-2016 15:03:54

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 11-APR-2016 15:03:45
Uptime 0 days 0 hr. 0 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c.oragyan.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c.oragyan.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service “cdb1.oragyan.com” has 1 instance(s).
Instance “cdb1”, status READY, has 1 handler(s) for this service…
Service “cdb1XDB.oragyan.com” has 1 instance(s).
Instance “cdb1”, status READY, has 1 handler(s) for this service…
Service “pdb1.oragyan.com” has 2 instance(s).
Instance “cdb1”, status READY, has 1 handler(s) for this service…
Instance “pdb1”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
[oracle@oracle12c ~]$

So as per the listener status, we can see that the name of the pdb1 service name is – pdb1.oragyan.com. We can also verify using tnsping command. First check the tnsnames.ora file for the entries:

[oracle@oracle12c ~]$ cat /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb2)
)
)

CDB3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb3)
)
)

CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1.oragyan.com)
)
)

PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c.oragyan.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1.oragyan.com)
)
)

[oracle@oracle12c ~]$

So now check the tnsping command output:

[oracle@oracle12c ~]$ tnsping pdb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 11-APR-2016 15:07:14

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c.oragyan.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.oragyan.com)))
OK (0 msec)
[oracle@oracle12c ~]$

Now the main point is, the service name being given by tnsping command must match from the configured one in the database as well, let us find out what is configured there in the database:

SQL> select name,pdb from v$services order by 2;

NAME PDB
—————————— ——————————
cdb1.oragyan.com                                      CDB$ROOT
cdb1XDB                                                        CDB$ROOT
SYS$USERS                                                   CDB$ROOT
SYS$BACKGROUND                                   CDB$ROOT
pdb1.oragyan.com                                      PDB1

SQL>

Now the output from v$services table, it must match from the output we got from tnsping service_name. So in our case its pdb1.oragyan.com.

So now try connecting to PDB:

[oracle@oracle12c ~]$ sqlplus sys/Oracle123@pdb1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 11 15:21:16 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL>

Other way to connect:

[oracle@oracle12c ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 11 15:24:33 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

SQL> conn sys/Oracle123@pdb1 as sysdba
Connected.
SQL>
SQL> show con_name

CON_NAME
——————————
PDB1
SQL>

So this is the way we can connect to Root Container and Pluggable Database.

I tried to cover almost whatever I have tested so far, if anyone has anything else to share, please do that.

Thanks!

 

 

 

Advertisements

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 )

Google+ photo

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

Connecting to %s