sid and pluggable

I wrote about SID there. (CONNECT_DATA=(SID=DB01)) is undocumented since Oracle 11gR1 and breaks things with multi-tenant / single-tenant.

You create a container CDB01 and a pluggable DB01, you can connect with


sqlplus "scott/tiger@
  (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(service_name=DB01.example.com)))"

But one of your application has hardcoded SID=DB01


sqlplus "scott/tiger@
  (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(sid=DB01)))"
ORA-12514: TNS:listener does not currently know of 
    service requested in connect descriptor

How do you do the trick?

  • Remove the domain name in the pluggable
  • 
    alter session set container=DB01;
    alter system set db_domain='' 
      container=current 
      scope=spfile;
    

  • You use the listener parameter USE_SID_AS_SERVICE
  • listener.ora

    
    USE_SID_AS_SERVICE_LISTENER = on
    

    You restart the listener and the database.

    
    sqlplus "scott/tiger@
      (description=(address=
        (host=srv01)(port=1521)(protocol=tcp))
        (connect_data=(sid=DB01)))"
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
    

    You just connected with SID to a pluggable database

Single-Tenant over bequeath connections

If you follow Oracle recommendation to use SingleTenant CDB instead of Non-CDB, then a lot of things will break.

I won’t go into details, but basically, in this new architecture, you have one core container, called CDB$ROOT, which is the default container where you connect to if you connect locally


sqlplus / as sysdba
SQL> select cdb from v$database;
CDB
---
YES
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------
CDB$ROOT

SQL> select con_id, NAME from V$CONTAINERS

    CON_ID NAME
---------- ----------
         1 CDB$ROOT
         2 PDB$SEED
         3 ST01

Then you’ll soon realise, you can no longer do what you used to do


SQL> create user u identified by u;
create user u identified by u
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Some scripts still run in the root container. SHUTDOWN ABORT, ALTER SYSTEM SWITCH LOGFILE. Doing a full backup or clone probably won’t hurt you much. Relatively..

But now let’s imagine I have a very simple and very old script to lock scott


vintage.sh:
  echo "alter user scott account lock;"|
    sqlplus -s / as sysdba

This won’t work. I need to lock scott in the SingleTenant container ST01.

I could do this in the container


SQL> alter session set container=ST01;
  Session altered.
SQL> alter user scott account lock;
  User altered.

So fine, so good. NOW : how do I make this work without changing the script ?

Remember, non-cdb database, as they were used in Oracle 11, 10, 9, 8, 7 … are now deprecated. Remember, cdb is recommended. Now face it : it’ll break your dba scripts.

As a production dba, I don’t want to rewrite all the existing scripts. Some are ultra-old and used by people who did not write them.

One method for my script would be to change the container in a login script.


echo "alter session set container=ST01;" > /tmp/login.sql
export ORACLE_PATH=/tmp
vintage.sh
  Session altered.
  User altered.  

(ORACLE_PATH in latest 12.1 and in 12.2, SQL_PATH in older release)

However, if my script must work with both CDB and non-CDB, I need to set the container in only this case.

In my login.sql, I first tried to implement some plsql logic, but alter session set container is not working (aka working with limitation) with execute immediate.

As well, I don’t want my script to break Oracle 11.

So I decide to do some sqlplus magic with defined variable.


set ver off feed off
-- 1) check if the column v$database.cdb exists
col column_name new_v cdb nopri
def cdb=null
select column_name from dba_tab_columns 
where owner='SYS' and 
table_name='V_$DATABASE' and column_name='CDB';
-- 2) if cdb is YES, then select a dynamic statement using V$PDB 
col pdb new_v pdb nopri
def pdb="null stmt from dual where 1=0"
select 
'''set container="''||name||''"'' stmt from v$pdbs where name!=''PDB$SEED'''
  pdb 
from v$database 
where &cdb='YES';
-- 3) get a dynamic alter session statement. I use a dummy flagger for non-cdb
col stmt new_val stmt nopri
def stmt="SET FLAGGER=OFF"
select &pdb;
-- 4) alter session
alter session &stmt;
set feed 6
col column_name clear
col stmt clear
col pdb clear
undef cdb
undef stmt
undef pdb
del

Now I run my script


11g: ./vintage.sh
User altered.

12c-non-cdb: ./vintage.sh
User altered.

12cR2-single-tenant: ./vintage.sh
User altered.

DISCLAIMER: you shouldn’t use a global login.sql and you should know that secretly fixing old scripts may have side effects. Test, test and retest your code
DISCLAIMER 2: my frequent readers surely wonder if this statement generating a statement generating a statement is for an obfuscation contest