Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Multitenant : Running Scripts Against Container Databases (CDBs) and Pluggable Databases (PDBs) in Oracle Database 12c Release 1 (12.1)

For many developers and DBAs, one of the biggest changes introduced by the Multitenant option is how it affects running batch jobs from shell scripts. The reliance on CRON and OS Authentication presents a big problem for those switching to the Multitenant option, which requires the use of services to connect to pluggable databases. This article presents a number of solutions to help transition your shell scripts to work with the multitenant option.

Related articles.

SET CONTAINER

For DBA scripts that perform tasks at the container level, using "/ AS SYSDBA" will work as it did in previous releases. The problem comes when you want to perform a task within the pluggable database. The simplest way to achieve this is to continue to connect using "/ as SYSDBA", but to set the specific container in your script using the ALTER SESSION SET CONTAINER command.

sqlplus / as sysdba <<EOF

ALTER SESSION SET CONTAINER = pdb1;

-- Perform actions as before...
SHOW CON_NAME;

EXIT;
EOF

To make the script generic, pass the PDB name as a parameter. Save the following code as a script called "set_container_test.sh".

sqlplus / as sysdba <<EOF

ALTER SESSION SET CONTAINER = $1;

-- Perform actions as before...
SHOW CON_NAME;

EXIT;
EOF

Running the script with the PDB name as the first parameter shows the container is being set correctly.

$ chmod u+x set_container_test.sh
$ ./set_container_test.sh pdb1

SQL*Plus: Release 12.1.0.1.0 Production on Fri Apr 18 16:48:51 2014

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> 
Session altered.

SQL> SQL> SQL> 
CON_NAME
------------------------------
PDB1
SQL> SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$

TWO_TASK

An obvious solution when connecting to specific users is to use the TWO_TASK environment variable. Unfortunately this does not work when using "/ AS SYSDBA".

$ export TWO_TASK=pdb1
$ sqlplus / as sysdba 
SQL*Plus: Release 12.1.0.1.0 Production on Fri Apr 18 16:54:34 2014

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

When connecting using a specific username/password combination TWO_TASK works as before.

$ export TWO_TASK=pdb1
$ sqlplus test/test 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Apr 18 16:57:46 2014

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

Last Successful login time: Wed Apr 02 2014 10:05:22 +01: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> SHOW CON_NAME;

CON_NAME
------------------------------
PDB1
SQL>

Hopefully your scripts do not contain connections with username and password specified, but if they do adding a service directly to the connection or using the TWO_TASK environment variable will allow you to connect to a specific PDB.

Secure External Password Store

Oracle 10g introduced the ability to use a Secure External Password Store for connecting to the database without having to explicitly supply credentials. The fact this is service-based means it works really well PBDs.

Place the following entries into the "$ORACLE_HOME/network/admin/sqlnet.ora" file, specifying the required wallet directory.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/wallet)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

Create a wallet to hold the credentials. Since 11gR2 this is better done using orapki, as it prevents the auto-login working if the wallet is copied to another machine.

$ mkdir -p /u01/app/oracle/wallet
$ orapki wallet create -wallet "/u01/app/oracle/wallet" -pwd "mypassword" -auto_login_local
Oracle Secret Store Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

Enter password:           
   
Enter password again:           
   
$

Create a credential associated with a TNS alias. The parameters are "alias username password".

$ mkstore -wrl "/u01/app/oracle/wallet" -createCredential pdb1_test test test
Oracle Secret Store Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:           
   
Create credential oracle.security.client.connect_string1
$

Create an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file with an alias that matches that used in the wallet.

PDB1_TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-121.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )

With this in place, we can now make connections to the database using the credentials in the wallet.

$ sqlplus /@pdb1_test

SQL*Plus: Release 12.1.0.1.0 Production on Sat Apr 19 10:19:38 2014

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

Last Successful login time: Sat Apr 19 2014 10:18:52 +01: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> SHOW USER
USER is "TEST"
SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB1
SQL>

Scheduler

The scheduler has been enhanced in Oracle 12c to include script-based jobs, allowing you to define scripts in-line, or call scripts on the file system. These are a variation on external jobs, but the SQL_SCRIPT and BACKUP_SCRIPT job types make it significantly easier to deal with credentials and the multitenant environment. You can read more about this functionality here.

catcon.pl

Another issue DBAs will encounter when running in a multitenant environment is the need to run the same script in multiple PDBs. That can be achieved using the methods mentioned previously, but Oracle provide a Perl utility called "catcon.pl" which may be more convenient.

In a multitenant environment, some Oracle supplied scripts must be applied in the correct order, starting with the CDB$ROOT container. The "catcon.pl" utility takes care of that and provides container-specific logs, allowing you to easily check the outcome of the action.

The full syntax of the utility is described here, but running the utility with no parameters displays the full usage.

$ perl catcon.pl

  Usage: catcon  [-u username[/password]] [-U username[/password]] 
                 [-d directory] [-l directory] 
                 [{-c|-C} container] [-p degree-of-parallelism]
                 [-e] [-s]
                 [-E { ON | errorlogging-table-other-than-SPERRORLOG } ]
                 [-g] 
                 -b log-file-name-base 
                 --
                 { sqlplus-script [arguments] | --x<SQL-statement> } ...

   Optional:
     -u username (optional /password; otherwise prompts for password)
        used to connect to the database to run user-supplied scripts or 
        SQL statements
        defaults to "/ as sysdba"
     -U username (optional /password; otherwise prompts for password)
        used to connect to the database to perform internal tasks
        defaults to "/ as sysdba"
     -d directory containing the file to be run 
     -l directory to use for spool log files
     -c container(s) in which to run sqlplus scripts, i.e. skip all 
        Containers not named here; for example, 
          -c 'PDB1 PDB2', 
     -C container(s) in which NOT to run sqlplus scripts, i.e. skip all 
        Containers named here; for example,
          -C 'CDB PDB3'

       NOTE: -c and -C are mutually exclusive

     -p expected number of concurrent invocations of this script on a given 
        host

       NOTE: this parameter rarely needs to be specified

     -e sets echo on while running sqlplus scripts
     -s output of running every script will be spooled into a file whose name 
        will be 
          <log-file-name-base>_<script_name_without_extension>_[<container_name_if_any>].<default_extension>
     -E sets errorlogging on; if ON is specified, default error logging table
        will be used, otherwise, specified error logging table (which must 
        have been created in every Container) will be used
     -g turns on production of debugging info while running this script

   Mandatory:
     -b base name (e.g. catcon_test) for log and spool file names
        
     sqlplus-script - sqlplus script to run OR
     SQL-statement  - a statement to execute

   NOTES:
     - if --x<SQL-statement> is the first non-option string, it needs to be 
       preceeded with -- to avoid confusing module parsing options into 
       assuming that '-' is an option which that module is not expecting and 
       about which it will complain
     - command line parameters to SQL scripts can be introduced using --p
       interactive (or secret) parameters to SQL scripts can be introduced 
       using --P

     For example,
       perl catcon.pl ... x.sql '--pJohn' '--PEnter Password for John:' ...

$

Regarding running Oracle supplied scripts, the manual uses the example of running "catblock.sql" in all containers.

$ . oraenv
ORACLE_SID = [cdb1] ? 
The Oracle base remains unchanged with value /u01/app/oracle
$ cd $ORACLE_HOME/rdbms/admin/
$ perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b /tmp/catblock_output catblock.sql
$ ls /tmp/catblock_output*
catblock_output0.log  catblock_output1.log  catblock_output2.log  catblock_output3.log
$ 

The first output file contains the combined output from the "cdb$root" and "pdb$seed" containers. The last file contains an overall status message for the task. The files between contain the output for all the user-created PDBs.

The "catcon.pl" utility can also be used to run queries against all containers in the CDB. The following command runs a query in each container, placing the output for each in a file called "/tmp/query_outputN.log".

$ cd $ORACLE_HOME/rdbms/admin/
$ perl catcon.pl -e -b /tmp/query_output -- --x"SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual"
$ ls /tmp/query_output*
/tmp/query_output0.log  /tmp/query_output1.log  /tmp/query_output2.log  /tmp/query_output3.log
$ 

You can target specific PDBs using the "-c" option, or exclude PDBs using the "-C" option. The example below runs a query in all user defined PDBs by omitting the root and seed containers.

$ rm -f /tmp/select_output*
$ cd $ORACLE_HOME/rdbms/admin/
$ perl catcon.pl -e -C 'CDB$ROOT PDB$SEED' -b /tmp/select_output -- --x"SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual"
$ 

For more information see:

Hope this helps. Regards Tim...

Back to the Top.