How To Connect To 12c Databases
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
PDBIf 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 serviceLSNRCTL 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!
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
Nice and usefull document
Thank you Naseem!