How To Connect To 12c Databases

How To Connect To 12c Databases

October 11, 2017 Off By dianarobete

Do you find yourself searching on Google for ways to connect to 12c database, especially PDBs?

Do you feel frustrated, that you keep forgetting the views for PDB names? Do you wonder if it’s only you, or others too?

Feel not bad about it, you are not alone!

Because lots of business still run 11g, not every DBA knows or remembers how to connect to a PDB.

I used to have trouble remembering what views to look at to figure out the names of the PDBs, within the CDB. If your 12c is a standalone database, then everything that worked in 11g works in 12c as well, as far as connecting.

About a year ago, I wrote the article 12c Multitenant Architecture Explained With Mind Maps, a great resource if you are not familiar with 12c architecture.

For the purpose of this article, I’ll just assume you know what PDB and CDB is. However, if you need a refresher, visit the article mentioned above.

1. Connecting To 12c Stand Alone Instance, No CDB/PDB Architecture
2. Connecting To 12c CDB, Root Container
3. Connecting To 12c PDBs
4. What Do I Do If…?
5. Putting It All Together

1. Connecting To 12c Stand Alone Instance, No CDB/PDB Architecture

If you are not using the CDB architecture (and many business will not use it, as it requires extra licensing and adds more layers of complexity), then the way you connected to 11g, works also for 12c.

You could use any of these methods:

  • OS Authentication on the server, assuming you have access to the server. Set your environment, by either using oraenv, or by exporting $ORACLE_SID variable.

    $ . oraenv DBPRD -->> assuming this is the SID of the database
    or
    $ export ORACLE_SID=DBPRD -->> Unix/Linux (set ORACLE_SID=DBPRD for Windows)
    $ sqlplus / as sysdba
    $ sqlplus drobete/****

  • EZ Connect. You will need to specify the server name, port number the listener is listening on and the ORACLE_SID

    $ sqlplus drobete/***@//oraprd1:1521/DBPRD

  • Using a TNS alias, which needs to be setup in $ORACLE_HOME/network/admin/tnsnames.ora (default location) or in the tnsnames.ora file that the $TNS_ADMIN variable is pointing to.
    Assuming you have an entry in the tnsnames.ora file for DBPRD database, as the alias DBPRD, you can connect:

    $sqlplus drobete/***@DBPRD

In order to confirm where you are connected, most likely you will just query v$database. In this case you don’t need to worry about CDB and PDB containers, or common users.

SQL> select name from v$database;

NAME
---------
DBPRD

2. Connecting To 12c CDB, Root Container.

Probably you remember the root container, CDB$ROOT is the one that contains the background processes and the central instance. Thus when you connect using OS Authentication on the server, you will always connect to the root container.
You can still use the same 3 methods from above to connect to the CDB, root container: OS Authentication, EZ Connect, and TNS alias:

  • OS Authentication on the server. Set the environment with oraenv or just export $ORACLE_SID to the name of the CDB.

    $ . oraenv cdb1 -->>assuming this is the name of your CDB
    or
    $ export ORACLE_SID=cdb1
    $sqlplus / as sysdba
    or connecting with a common user C##_DIANA
    $sqlplus C##_DIANA/***

  • EZ connect.

    $ sqlplus drobete/***@//oraprd1:1521/cdb1

  • Using a TNS alias. The alias needs to be setup in the tnsnames.ora file

    $sqlplus C##_DIANA/***@cdb1

In order to confirm where you are connected, querying v$database is not enough, because this view will not give you enough information on the container you are connected to.
The easiest way to find out the container you are connected to is by showing the container:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

or

SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME') container from dual;

CONTAINER
-----------------------------------------------------------------
CDB$ROOT

3. Connecting To 12c PDBs

You cannot connect directly with OS Authentication to the PDB, as the PDB has no instance of it’s own. You can still use EZ connect and TNS alias method to connect. Also you can connect to the CDB and then switch container to the PDB.

  • EZ connect. In order to connect, you need to use the service name that the PDB is registering with the listener.

    $ sqlplus drobete/***@//oraprd1:1521/pdb.localdomain

  • TNS Alias. The alias needs to be setup in the tnsnames.ora file

    sqlplus diana/***@pdb

  • Switching containers:

    $sqlplus / as sysdba -->>connect to the root container
    SQL>alter session set container=PDB;

In order to confirm where you are connected, use the same method as for the root container:

SQL> show con_name

CON_NAME
------------------------------
PDB

or

SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME') container from dual;

CONTAINER
-----------------------------------------------------------------
PDB

4. What Do I Do If…?

When connecting to the PDB, a few questions come to my mind, and most likely yours too.

What do I do if …

  • I Don’t know the PDB names?

    Worry no more. Query V$CONTAINERS view, when connected to the CDB$ROOT:

    SQL> select name from v$containers;

    NAME
    ------------------------------
    CDB$ROOT
    PDB$SEED
    PDB

    If you run the same select when already connected to a PDB, that PDB’s name is returned only.

    or

    SQL> show pdbs
    
    	    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
    	---------- ------------------------------ ---------- ----------
    		 2 PDB$SEED			  READ ONLY  NO
    	 	 3 PDB				  READ WRITE NO
  • I don’t know the PDB service names?

    There are at least 2 ways to get this information:

    Query V$SERVICES

    SQL> select name, pdb from v$services;
    
    	NAME		     PDB
    	-------------------- ---------------
    	pdb.localdomain      PDB
    	cdb1XDB 	     CDB$ROOT
    	cdb1.localdomain     CDB$ROOT
    	SYS$BACKGROUND	     CDB$ROOT
    	SYS$USERS	     CDB$ROOT

    You can also check what is registered with the listener:

    $lsnrctl service

    [oracle@localhost ~]$ lsnrctl service

    LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 11-OCT-2017 20:35:08

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
    Services Summary...
    Service "cdb1.localdomain" has 1 instance(s).
    Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:1 refused:0 state:ready
    LOCAL SERVER
    Service "cdb1XDB.localdomain" has 1 instance(s).
    Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
    "D000" established:0 refused:0 current:0 max:1022 state:ready
    DISPATCHER
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=14688))
    Service "pdb.localdomain" has 1 instance(s).
    Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:1 refused:0 state:ready
    LOCAL SERVER
    The command completed successfully

5. Putting It All Together

I put together a mind map, you can use anytime for reference. Feel free to print it!

Connecting to 12c database

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana