How to Navigate Multitenant Architecture in Oracle 12c - dummies

How to Navigate Multitenant Architecture in Oracle 12c

By Chris Ruel, Michael Wessler

A big difference with working in a multitenant architecture is how you connect to your Oracle 12c databases. Because there is only one System Global Area (SGA) and one set of background processes, simply connecting to an instance like you have been taught for non- Container Databases (CDBs) does not apply in quite the same way.

You’re going to want to be aware of some key new commands and data dictionary views.

First of all, how do you connect to the CDB and or Pluggable Databases (PDBs)? You connect to the CDB the same way you used to do in the past. You can set your ORACLE_SID and connect with SQL*Plus or RMAN as SYSDBA. Connecting to the pluggable databases is where things differ. You can connect to a PDB in two ways:

  • You can connect to the CDB and then alter your session to set your environment to a PDB.

  • You can set up Oracle Net to route you to a PDB through a service name by using the TNSNAMES.ORA file.

To connect to a PDB through the CDB on Linux, follow these steps:

  1. Open a terminal for a user with the required privileges to connect to the database through SQL*Plus.

  2. Set your environment to the CDB using the oraenv tool by typing

    <. oraenv>

    You see something like this:

    [oracle@orasvr01 ~]$ . oraenv
    ORACLE_SID = [oracle] ? devcdb
    The Oracle base remains unchanged with value /u01/app/oracle
  3. Connect to the CDB just like you would any non-CDB by typing

    <sqlplus / as sysdba>

    You something like this:

    SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 16 23:34:59 2013
    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
  4. To see where in the multitenant architecture you’re connected, type the new 12c command:

    <show con_name>

    You see something like this:

    CON_NAME
    ------------------------------
    CDB$ROOT
  5. Get a list of your PDBs by querying one of the new data dictionary views for supporting a multitenant environment by typing

    <select name, open_mode
    from v$pdbs;>

    Alternatively, you can use this shortcut to get the same output:

    SQL> show pdbs

    You see something like this:

    NAME    OPEN_MODE
    ---------- ----------
    PDB$SEED  READ ONLY
    DEVPDB1  READ WRITE

    Note that the PDB$SEED is visible in read only mode. It is rare that you would ever need to connect to this database — it is used primarily internally for optimization purposes when creating PDBs.

  6. To connect to your PDB DEVPDB1 from within your CBD, type:

    < alter session set container=devpdb1;>

    You see something like this:

    Session altered.
  7. To show that you are now in the PDB container, type

    <show con_name>

    You see something like this:

CON_NAME
------------------------------
DEVPDB1

The other way to connect to your containers is directly through Oracle Net. This method supports the guarantee that the multitenant environment will be entirely compatible with non-CDB environments.

  1. Make sure that the databases are listed with the listener on the server. To do this, log in to the OS as the oracle software owner and type

    <lsnrctl status>

    You see something like this:

    Service "devcdb" has 1 instance(s).
     Instance "devcdb", status READY, has 1 handler(s) for this service...
    Service "devpdb1" has 1 instance(s).
     Instance "devcdb", status READY, has 1 handler(s) for this service...

    This output shows that the CDB and PDB have service registered with the listener.

  2. Make sure there are TNS entries on the clients from which you want to connect.

    For example, you should have entries modeled after this example:

    DEVPDB1 =
     (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orasvr01)(PORT = 1521))
      (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = devpdb1)
      )
     )
  3. After you confirm the preceding configurations, you can connect from your Oracle SQL*Plus client by typing

    <sqlplus system@devpdb1>

    You see something like this:

Last Successful login time: Fri Feb 01 2013 09:48:20 -04: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>