What are the
different ways to connect to Oracle 12c pluggable database (PDB)
Multitenant is new Oracle 12c feature where
one container database can contains multiple pluggbale databases. As new to
oracle 12c every body is curious about it and wanted to know easy and possible
methods to explore its features.
·
Container Database (CDB) :
It is similar to conventional
RDBMS database. It contains most of the working parts you will be already
familiar with controlfiles, datafiles, undo files, temp files, redo logs etc.).
It also contains the data dictionary for those objects that are owned by the
root container and those that are visible to all PDBs.
·
Pluggable Database (PDB) :
As CDB contains most of
the working parts for the database, the PDB only needs to contain information
specific to itself i.e. respective pluggable database. It does not need to
worry about controlfiles, redo logs and undo etc. It is just made up of
datafiles and tempfiles to handle it's own objects. This includes it's own data
dictionary, containing information about only those objects that are specific
to the PDB only.
In
below given example various ways to connect to pluggable databases.
To
make a direct connect connection to your pluggabale database is possible
through service name only. Below are the known methods to me.
Method
1 - Using alter session set container –
============================================================
This
is most commonly used by database administrators. Where first you are
connecting to container database and then using "ALTER SESSION
SET..." clause you can connect to the any pluggable database.
This is the most common way which most of the Database Administrator preferred.
[oracle@Server Desktop]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 4 09:18:22 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
SQL>
SQL> sho pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3
PDB_LONDON READ
WRITE NO
4 PDB_BERLIN
MOUNTED
5
PDB_ROMA
MOUNTED
6
PDB_PARIS
MOUNTED
SQL>
SQL> alter session set container=PDB_LONDON;
Session altered.
SQL>
SQL> sho con_name
CON_NAME
------------------------------
PDB_LONDON
SQL>
Method
2 – Using tnsnames of pluggable database
============================================================
In
this method user should be aware about the username, password and respected
connect string of the pluggable database.
This
is the most preferred way to connect for the developers.
[oracle@Server Desktop]$ sqlplus sun@pdb_london
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 4 09:22:48 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
SQL> sho con_name
CON_NAME
------------------------------
PDB_LONDON
SQL>
SQL> !tnsping pdb_london
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 04-JAN-2017
09:23:06
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL =
TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME = pdb_london)))
OK (10 msec)
SQL>
Method
3 – Using EZCONNECT
============================================================
In
this method user is using detailed connect string (mostly used in JDBC
connection).
Here
you should know the username, password, server hostname, post etc to connect to
the pluggable database.
oracle@Server Desktop]$ sqlplus sun/sun@//localhost.localdomain:1521/pdb_london
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 4 09:27:49 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Wed Jan 04 2017 09:22:51 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Advanced Analytics and Real
Application Testing options
SQL>
SQL> sho con_name
CON_NAME
------------------------------
PDB_LONDON
SQL>
I
hope this help in some extents to new body in Oracle 12c.
Thank you!
No comments:
Post a Comment