Connect to PDB Directly

With the inception of multitenancy with oracle database, there has been a new world of opportunities and features for databases has come up. Those who have been working with an earlier version of the database are usually used to logging into a database with "sqlplus / as sysdba" command.


Now with multitenancy sqlplus with sysdba login would land you to container database and then you would need to navigate to your pluggable database using command


  alter session set container=<pluggable database name>;


But do you know there is a direct way to login into the pluggable database directly? Let's see how it works.


Let's assume you are working on a Linux environment and your pluggable database name is pdb1 and your database name is orcl1 and the same instance name. With usual SID setting set following


  export ORACLE_SID=orcl1

  export ORACLE_HOME=/u01/oracle/product/db_1

  export PATH=$ORACLE_HOME/bin:$PATH


Once the shell environment variables are set we can log in using sqlplus / as sysdba and land into container root database. Here the catch if you set


  export ORACLE_PDB_SID=pdb1


and log in using sqlplus / as sysdba, we will get into a pluggable database named pdb1. 

You may be wondering how this has happened but it's not so difficult to understand. Setting ORACLE_SID and ORACLE_HOME will let you connect to the oracle database and using ORACLE_PDB_SID oracle will call an trigger named "DBMS_SET_PDB" which will do the same thing as we usually do to run an "alter session set container=pdb1;"


    SQL> select trigger_body from users_triggers where trigger_name = 'DBMS_SET_PDB'


    declare

       pdb_name varchar2(64);

       begin

         DBMS_SYSTEM.get_env ('ORACLE_PDB_SID', pdb_name);

         if(pdb_name is not null)

          then

            EXECUTE IMMEDIATE 'alter session set container = ' || '"' || pdb_name || '"';

          end if;

       exception

         when others then

         NULL;

       end dbms_set_pdb;


Lets also see an example of this login


    $ export ORACLE_PDB_SID=PDB1

    $ sqlplus / as sysdba


    SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 13:25:04 2020

    Version 19.7.0.0.0


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



    Connected to:

    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

    Version 19.7.0.0.0


    SQL> show con_name


    CON_NAME

    ------------------------------

    PDB1

    SQL> exit

    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

    Version 19.7.0.0.0


    $ unset ORACLE_PDB_SID

    $ sqlplus / as sysdba


    SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 13:27:49 2020

    Version 19.7.0.0.0


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



    Connected to:

    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

    Version 19.7.0.0.0


    SQL> show con_name


    CON_NAME

    ------------------------------

    CDB$ROOT

    SQL> exit

    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

    Version 19.7.0.0.0

    $


Now there are some edge cases to this variable some are behavioral and others could be technology-related. Usually when we connect to the container database and then use alter session set container database command, but when you use ORACLE_PDB_SID and exit from sqlplus prompt, the variable will still be set and you may sometimes even not notice and run some command which you intended to executed in the pluggable database. So it's not a drawback but rather we need to be careful about using this command. One can event unset ORACLE_PDB_SID variable and then connect to root container.


The second important thing which read in one of the blogs from Mike Dietrich when you set the value of ORACLE_PDB_SID to some pluggable database that doesn't exits oracle doesn't return any error. This means if instead of having it set to pdb1 you have set the variable to pdb2, the oracle will connect to the root container database with the error being reported. This could be very tricky when you don't have a habit of checking which database you are running the command on and can lead to surprises.


Overall this feature is pretty cool and beneficial to directly login into the Pluggable database but needs to understand and use properly, like every other command in databases or other technologies. Also note this functionality comes with 18c and 19c databases that too with later versions of PSU.


References

Pitfalls: Connect to a PDB directly with ORACLE_PDB_SID (mikedietrichde.com)

How To Connect to PDB By OS Authentication In 19c Container Database Environment (Doc ID 2729416.1)

 

Comments

  1. Akhil22 March 2021 at 05:54

    Its working with 19c only right at 1wc i dont see this trigger

    ReplyDelete
    Replies
    1. Arpit Agrawal26 March 2021 at 22:55

      This trigger is only available from specific patch set with 19c not all with all initial patch sets.

      Delete

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more