📋Steps in this guide1/6

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

This article presents a number of solutions to help transition your shell scripts to work with the multitenant option.

oracle 12cconfigurationintermediate

by OracleDba

13 views

Secure External Password Store

Some of the other methods listed below may be simpler for transitioning existing scripts, but in my opinion using the Secure External Password Store is the best solution. 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. Create a wallet to hold the credentials. Since 11gR2 this is better done using , as it prevents the auto-login working if the wallet is copied to another machine. Create a credential associated with a TNS alias. The parameters are "alias username password". Create an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file with an alias that matches that used in the wallet. With this in place, we can now make connections to the database using the credentials in the wallet.

Code/Command (click line numbers to comment):

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

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

$ 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:           
   
$

$ 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
$

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

$ 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>

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 command. To make the script generic, pass the PDB name as a parameter. Save the following code as a script called "set_container_test.sh". Running the script with the PDB name as the first parameter shows the container is being set correctly.

Code/Command (click line numbers to comment):

sqlplus / as sysdba <<EOF

ALTER SESSION SET CONTAINER = pdb1;

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

EXIT;
EOF

sqlplus / as sysdba <<EOF

ALTER SESSION SET CONTAINER = $1;

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

EXIT;
EOF

$ 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 environment variable. Unfortunately this does not work when using "/ AS SYSDBA". When connecting using a specific username/password combination works as before. 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 environment variable will allow you to connect to a specific PDB.

Code/Command (click line numbers to comment):

$ 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:

$ 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>

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 and job types make it significantly easier to deal with credentials and the multitenant environment. You can read more about this functionality here .

catcon.pl

- Multitenant : Running Scripts Against Multiple Pluggable Databases Using 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 container. The "catcon.pl" utility takes care of that and provides container-specific logs, allowing you to easily check the outcome of the action. Let's try some examples. We'll start by checking our environment. We set the of the database we want to use, and source , to check our environment is set correctly. The catcon.pl script is in the "$ORACLE_HOME/rdbms/admin" directory. Let's check to see what pluggable databases are present. We connect to the root container. The command lists the pluggable databases. We can see we have the seed database, and 3 user-defined pluggable databases named PDB1, 2 and 3. To keep things simple we'll run the "utlrp.sql" script, which compiles invalid objects in the database. We make an output directory to hold the any log files produced by the "catcon.pl" script. We run the "catcon.pl script" with some command-line arguments. The "-d" option is the directory where we can find the script we want to run. The "-l" option is the directory where the log files will be spooled to. The "-b" option is the prefix for the log file names. And of course we have the "utlrp.sql" script we want to run. The command can take a long time to complete, depending on how many pluggable databases there are, and what work the script is doing. Checking the output directory we can see 4 log files have been produced. Using fgrep, we output references to "Current Container" to see what output has been put in each log. Log 0 contains the output from the "utlrp.sql" script when it was run against the root container and the seed database. Log 1 contains the output from PDB1. Log 2 contains the output from PDB2. Log 3 contains the output from PDB3. You can check these logs for errors. We clear the output directory and repeat the test, this time using the --C" option. This is an exclude list. Here we are excluding the root container and the seed database. Now we only have 3 log files. Log 0 contains the output from PDB1. Log 1 contains the output from PDB2. Log 2 contains the output from PDB3. We clear the output directory and repeat the test, this time using the "-c" option. This is an include list. Here we are including the PDB2 and PDB3 pluggable databases. Now we only have 2 log files. Log 0 contains the output from PDB2. Log 1 contains the output from PDB3. As well as scripts, we can use "catcon.pl" to run SQL, DDL and DML statements. Here we exclude the root container and the seed database, so the statement will only be run in the user-defined pluggable databases. We use the "--" to indicate any following strings are not options, then each statement we want to run is double-quoted and prefixed by hyphen-hyphen-x. Now we have 3 log files. Log 0 contains the output from PDB1. Log 1 contains the output from PDB2. Log 2 contains the output from PDB3. If we had issued multiple statements, we would get more output logs. We can run multiple statements with a single call. Remember, we only need the "--" at the start of the list of non-option strings. We now have 4 log files, but they they contain entries for more than one PDB per log. The full syntax of the utility is described here , but running the utility with no parameters, or the "-h" and "--help" flags, displays the full usage. The following output is from Oracle 19c, which has additional options compared to Oracle 12.1.

Code/Command (click line numbers to comment):

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

cd $ORACLE_HOME/rdbms/admin

sql / as sysdba -s <<EOF
SHOW PDBS
EXIT;
EOF

SQLcl: Release 19.1 Production on Fri Jul 31 10:20:51 2020

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO

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

# Make an output directory.
rm -Rf /tmp/utlrp_output
mkdir /tmp/utlrp_output

# Compile invalid objects in all containers.
perl catcon.pl \
       -d $ORACLE_HOME/rdbms/admin \
       -l /tmp/utlrp_output \
       -b utlrp_output \
       utlrp.sql

$ # Show output files.
$ ls -l /tmp/utlrp_output/*
-rw-------. 1 oracle oinstall 9911 Jul 31 10:29 /tmp/utlrp_output/utlrp_output0.log
-rw-------. 1 oracle oinstall 5265 Jul 31 10:29 /tmp/utlrp_output/utlrp_output1.log
-rw-------. 1 oracle oinstall 5264 Jul 31 10:29 /tmp/utlrp_output/utlrp_output2.log
-rw-------. 1 oracle oinstall 5263 Jul 31 10:29 /tmp/utlrp_output/utlrp_output3.log
-rw-------. 1 oracle oinstall  434 Jul 31 10:28 /tmp/utlrp_output/utlrp_output_catcon_7581.lst
$
$ # Check contents.
$ fgrep "Current Container =" /tmp/utlrp_output/*.log
/tmp/utlrp_output/utlrp_output0.log:==== Current Container = CDB$ROOT Id = 1 ====
/tmp/utlrp_output/utlrp_output0.log:==== Current Container = CDB$ROOT Id = 1 ====
/tmp/utlrp_output/utlrp_output0.log:==== Current Container = PDB$SEED Id = 2 ====
/tmp/utlrp_output/utlrp_output0.log:==== Current Container = PDB$SEED Id = 2 ====
/tmp/utlrp_output/utlrp_output1.log:==== Current Container = PDB1 Id = 3 ====
/tmp/utlrp_output/utlrp_output1.log:==== Current Container = PDB1 Id = 3 ====
/tmp/utlrp_output/utlrp_output2.log:==== Current Container = PDB2 Id = 4 ====
/tmp/utlrp_output/utlrp_output2.log:==== Current Container = PDB2 Id = 4 ====
/tmp/utlrp_output/utlrp_output3.log:==== Current Container = PDB3 Id = 5 ====
/tmp/utlrp_output/utlrp_output3.log:==== Current Container = PDB3 Id = 5 ====
$

# Make an output directory.
rm -Rf /tmp/utlrp_output
mkdir /tmp/utlrp_output

# Exclude the root and seed containers (-C, --excl_con).
perl catcon.pl \
       -d $ORACLE_HOME/rdbms/admin \
-C 'CDB$ROOT PDB$SEED'
\
       -l /tmp/utlrp_output \
       -b utlrp_output \
       utlrp.sql

$ # Show output files.
$ ls -l /tmp/utlrp_output/*
-rw-------. 1 oracle oinstall 5225 Jul 31 10:31 /tmp/utlrp_output/utlrp_output0.log
-rw-------. 1 oracle oinstall 5225 Jul 31 10:31 /tmp/utlrp_output/utlrp_output1.log
-rw-------. 1 oracle oinstall 5225 Jul 31 10:31 /tmp/utlrp_output/utlrp_output2.log
-rw-------. 1 oracle oinstall  434 Jul 31 10:30 /tmp/utlrp_output/utlrp_output_catcon_7809.lst
$
$ # Check contents.
$ fgrep "Current Container =" /tmp/utlrp_output/*.log
/tmp/utlrp_output/utlrp_output0.log:==== Current Container = PDB1 Id = 3 ====
/tmp/utlrp_output/utlrp_output0.log:==== Current Container = PDB1 Id = 3 ====
/tmp/utlrp_output/utlrp_output1.log:==== Current Container = PDB2 Id = 4 ====
/tmp/utlrp_output/utlrp_output1.log:==== Current Container = PDB2 Id = 4 ====
/tmp/utlrp_output/utlrp_output2.log:==== Current Container = PDB3 Id = 5 ====
/tmp/utlrp_output/utlrp_output2.log:==== Current Container = PDB3 Id = 5 ====
$

# Make an output directory.
rm -Rf /tmp/utlrp_output
mkdir /tmp/utlrp_output

# Include only PDB2 and PDB3 containers (-c, --incl_con).
perl catcon.pl \
       -d $ORACLE_HOME/rdbms/admin \
-c 'PDB2 PDB3'
\
       -l /tmp/utlrp_output \
       -b utlrp_output \
       utlrp.sql

$ # Show output files.
$ ls -l /tmp/utlrp_output/*
-rw-------. 1 oracle oinstall 5225 Jul 31 10:34 /tmp/utlrp_output/utlrp_output0.log
-rw-------. 1 oracle oinstall 5225 Jul 31 10:34 /tmp/utlrp_output/utlrp_output1.log
-rw-------. 1 oracle oinstall  434 Jul 31 10:33 /tmp/utlrp_output/utlrp_output_catcon_7949.lst
$
$ # Check contents.
$ fgrep "Current Container =" /tmp/utlrp_output/*.log
/tmp/utlrp_output/utlrp_output0.log:==== Current Container = PDB2 Id = 4 ====
/tmp/utlrp_output/utlrp_output0.log:==== Current Container = PDB2 Id = 4 ====
/tmp/utlrp_output/utlrp_output1.log:==== Current Container = PDB3 Id = 5 ====
/tmp/utlrp_output/utlrp_output1.log:==== Current Container = PDB3 Id = 5 ====
$

# Make an output directory.
rm -Rf /tmp/select_output
mkdir /tmp/select_output

# Run query. Exclude the root and seed containers (-C, --excl_con).
perl catcon.pl \
       -C 'CDB$ROOT PDB$SEED' \
       -l /tmp/select_output \
       -b select_output \
-- --x"SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual"

$ # Show output files.
$ ls -l /tmp/select_output/*
-rw-------. 1 oracle oinstall 2243 Jul 31 10:36 /tmp/select_output/select_output0.log
-rw-------. 1 oracle oinstall 2243 Jul 31 10:36 /tmp/select_output/select_output1.log
-rw-------. 1 oracle oinstall 2244 Jul 31 10:36 /tmp/select_output/select_output2.log
-rw-------. 1 oracle oinstall  438 Jul 31 10:36 /tmp/select_output/select_output_catcon_8069.lst
$
$ # Check contents.
$ fgrep "Current Container =" /tmp/select_output/*.log
/tmp/select_output/select_output0.log:==== Current Container = PDB1 Id = 3 ====
/tmp/select_output/select_output0.log:==== Current Container = PDB1 Id = 3 ====
/tmp/select_output/select_output1.log:==== Current Container = PDB2 Id = 4 ====
/tmp/select_output/select_output1.log:==== Current Container = PDB2 Id = 4 ====
/tmp/select_output/select_output2.log:==== Current Container = PDB3 Id = 5 ====
/tmp/select_output/select_output2.log:==== Current Container = PDB3 Id = 5 ====
$

# Make an output directory.
rm -Rf /tmp/select_output
mkdir /tmp/select_output

# Run query. Exclude the root and seed containers (-C, --excl_con).
perl catcon.pl \
       -C 'CDB$ROOT PDB$SEED' \
       -l /tmp/select_output \
       -b select_output \
-- --x"SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual" \
       --x"SELECT USER FROM dual"

$ # Show output files.
$ ls -l /tmp/select_output/*
-rw-------. 1 oracle oinstall 2243 Jul 31 10:46 /tmp/select_output/select_output0.log
-rw-------. 1 oracle oinstall 3739 Jul 31 10:46 /tmp/select_output/select_output1.log
-rw-------. 1 oracle oinstall 2244 Jul 31 10:46 /tmp/select_output/select_output2.log
-rw-------. 1 oracle oinstall 3582 Jul 31 10:46 /tmp/select_output/select_output3.log
-rw-------. 1 oracle oinstall  438 Jul 31 10:46 /tmp/select_output/select_output_catcon_8220.lst
$
$ # Check contents.
$ fgrep "Current Container =" /tmp/select_output/*.log
/tmp/select_output/select_output0.log:==== Current Container = PDB1 Id = 3 ====
/tmp/select_output/select_output0.log:==== Current Container = PDB1 Id = 3 ====
/tmp/select_output/select_output1.log:==== Current Container = PDB1 Id = 3 ====
/tmp/select_output/select_output1.log:==== Current Container = PDB1 Id = 3 ====
/tmp/select_output/select_output1.log:==== Current Container = PDB3 Id = 5 ====
/tmp/select_output/select_output1.log:==== Current Container = PDB3 Id = 5 ====
/tmp/select_output/select_output2.log:==== Current Container = PDB2 Id = 4 ====
/tmp/select_output/select_output2.log:==== Current Container = PDB2 Id = 4 ====
/tmp/select_output/select_output3.log:==== Current Container = PDB2 Id = 4 ====
/tmp/select_output/select_output3.log:==== Current Container = PDB2 Id = 4 ====
/tmp/select_output/select_output3.log:==== Current Container = PDB3 Id = 5 ====
/tmp/select_output/select_output3.log:==== Current Container = PDB3 Id = 5 ====
$

$ perl catcon.pl

  Usage: catcon  [-h, --help]
                 [-u, --usr username
                   [{/password | -w, --usr_pwd_env_var env-var-name}]]
                 [-U, --int_usr username
                   [{/password | -W, --int_usr_pwd_env_var env-var-name]]
                 [-d, --script_dir directory]
                 [-l, --log_dir directory]
                 [{-c, --incl_con | -C, --excl_con} container]
                 [-p, --catcon_instances degree-of-parallelism]
                 [-z, --ez_conn EZConnect-strings]
                 [-e, --echo]
                 [-s, --spool]
                 [-E, --error_logging
                   { ON | errorlogging-table-other-than-SPERRORLOG } ]
                 [-F, --app_con Application-Root]
                 [-V, --ignore_errors errors-to-ignore ]
                 [-I, --no_set_errlog_ident]
                 [-g, --diag]
                 [-v, --verbose]
                 [-f, --ignore_unavailable_pdbs]
                 [--fail_on_unopenable_pdbs]
                 [-r, --reverse]
                 [-R, --recover]
                 [-m, --pdb_seed_mode pdb-mode]
                 [--force_pdb_mode pdb-mode]
                 [--all_instances]
                 [--upgrade]
                 [--ezconn_to_pdb pdb-name]
                 [--sqlplus_dir directory]
                 [--dflt_app_module app-module]
                 -b, --log_file_base log-file-name-base
                 --
                 { sqlplus-script [arguments] | --x
} ...

   Optional:
     -h, --help
        print usage info and exit
     -u, --usr
        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"
     -w, --usr_pwd_env_var
        name of environment variable which contains a password for a user
        whose name was specified with --usr;
        NOTE: should NOT be used if --usr specified a password
     -U, --int_usr
        username (optional /password; otherwise prompts for password)
        used to connect to the database to perform internal tasks
        defaults to "/ as sysdba"
     -W, --int_usr_pwd_env_var
        name of environment variable which contains a password for a user
        whose name was specified with --int_usr;
        NOTE: should NOT be used if --int_usr specified a password
     -d, --script_dir
        directory containing the file to be run
     -l, --log_dir
        directory to use for spool log files
     -c, --incl_con
        container(s) in which to run sqlplus scripts, i.e. skip all
        Containers not named here; for example,
          --incl_con 'PDB1 PDB2',
     -C, --excl_con
         container(s) in which NOT to run sqlplus scripts, i.e. skip all
        Containers named here; for example,
          --excl_con 'CDB PDB3'

       NOTE: --incl_con and --excl_con are mutually exclusive

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

       NOTE: this parameter rarely needs to be specified

     -z, --ez_conn
        blank-separated EZConnect strings corresponding to RAC instances
        which can be used to run scripts
     -e, --echo
        sets echo on while running sqlplus scripts
     -s, --spool
        output of running every script will be spooled into a file whose name
        will be
_
_[
].
-E, --error_logging
        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
     -F, --app_con
        causes scripts to run in a Application Root and all Application PDBs
        belonging to it;
        ***CANNOT*** be specified concurrently with -{cC} flags
     -V, --ignore_errors
        causes catcon to ignore errors encountered during specified operations.
        The following options are supported:
          script_path == ignore errors while validating script path
     -S, --user_scripts
        running user scripts, meaning that _oracle_script will not be set and
        all entities created by scripts will not be marked as Oracle-maintained
     -I, --no_set_errlog_ident
        do not issue set Errorlogging Identifier (ostensibly because the
        caller already did it and does not want us to override it)
     -g, --diag
        turns on production of diagnostic info while running this script
     -v, --verbose
        turns on verbose output which is less verbose than debugging output
     -f, --ignore_unavailable_pdbs
        instructs catcon to ignore PDBs which are closed or, if --incl_con or
        --excl_con was used, do not exist and process existing PDBs which
        were specified (explicitly or implicitly) and are open

        NOTE: if this flag is not specified and some specified PDBs do not
              exist or are not open, an error will be returned and none of
              the Containers will be processed.

     --fail_on_unopenable_pdbs
        by default, if the caller instructs catcon to open PDBs against which
        scripts will be run in a certain mode (using --pdb_seed_mode or
        --force_pdb_mode), and some of them could not be opened in that mode,
        catcon will issue a warning and proceed to execute scripts, skipping
        such PDBs.

        This option should be specified if the caller prefers that
        in such cases catcon report an error and not execute supplied scripts
        against any PDBs.

     -r, --reverse
        causes scripts to be run in all PDBs and then in the Root (reverse
        of the default order); required for running catdwgrd.sql in a CDB
     -m, --pdb_seed_mode
        mode in which PDB should be opened; one of the following values
        may be specified:
        - UNCHANGED - leave PDB in whatever mode it is already open
        - READ WRITE (default)
        - READ ONLY
        - UPGRADE
        - DOWNGRADE

        NOTE: if the desired mode is different from the mode in which
              PDB is open, it is will be closed and reopened in the
              desired mode before running any scripts; after all scripts were
              run, it will be restored to the original mode

              --pdb_seed_mode should not be specified if --force_pdb_mode
              is specified because mode supplied with the latter will apply
              to PDB

     --force_pdb_mode
        mode in which ALL PDBs against which scripts will be run must be
        opened; one of the following values may be specified:
        - UNCHANGED - leave PDBs in whatever mode they are already
                      open (default)
        - READ WRITE
        - READ ONLY
        - UPGRADE
        - DOWNGRADE

        NOTE: if the desired mode is different from the mode in which
              some of the PDBs specified by the caller are open, they will be
              closed and reopened in the desired mode before running any
              scripts; after all scripts were run, they will be restored to
              the original mode

              --force_pdb_mode should not be specified if --pdb_seed_mode
              is specified because mode supplied with the latter will apply
              to PDB

     -R, --recover
        causes catcon to recover from unexpected death of a SQL*Plus process
        that it spawned; if not specified, such event will cause catcon to die

     -D, --disable_lockdown
       causes catcon to disable lockdown profile before running script(s) in
       a PDB and reenable them before existing

     --all_instances
       if used to run scripts against a CDB and if --force_pdb_mode was
       specified, catcon will attempt to run scripts on PDBs using all
       instances on which a CDB is open

     --upgrade
       catcon is being invoked in the course of upgrading a database

     --ezconn_to_pdb
       caller is expected to provide catcon with one or more EZConnect strings
       leading to the specified PDB; all specified scripts will be run ONLY
       against that PDB; neither --incl_con nor --excl_con may be specified
       concurrentrly with this flag

     --sqlplus_dir
       directory where sqlplus binary which catcon should use can be found
       (e.g. if  does not include it or if the caller wants
       catcon to use a particular version of sqlplus binary)

     --dflt_app_module
       if specified, value to which catcon should set APPLICATION MODULE

   Mandatory:
     -b, --log_file_base
        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
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
     - occupying middle ground between --p and --P, parameters whose values
       are stored in environment variables can be specified using --e
       (as in --e"env_var_holding_password")

     For example,
       perl catcon.pl ... x.sql --p"John" --P"Enter Password for John:" ...
     or store John's password in environment variable JOHNS_PASSWORD and
     then issue
       perl catcon.pl ... x.sql --p"John" --e"JOHNS_PASSWORD" ...

$

18c Onward (ORACLE_PDB_SID)

From 18c onward there is an undocumented environment variable called that allows you to OS authenticate to a PDB. Mike Dietrich wrote about this here , and later about the pitfalls of using this feature here . The fact it is undocumented, means you shouldn't really use it! Here is an example of its use. Notice that both the and the are set. It doesn't work if the is not set. If you want to use this as a stop-gap solution, that is your choice, but in my opinion you should ignore this and migrate to using a secure external password store, which is a superior solution. I'm not an Oracle on Windows guy, but I'm told by Ernest Kalwa there is a bug related to this on Windows. Also, see MOS Doc ID 2728684.1 . For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

export ORACLE_SID=cdb1
export ORACLE_PDB_SID=pdb1

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 08:04:39 2020
Version 19.3.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.3.0.0.0

SQL> show con_name

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

unset ORACLE_SID
export ORACLE_PDB_SID=pdb1

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 08:08:01 2020
Version 19.3.0.0.0

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

ERROR:
ORA-12162: TNS:net service name is incorrectly specified


Enter user-name:

Bug 31627193 â€“ ORACLE_PDB_SID ENVIRONMENT VARIABLE DOES NOT WORK ON WINDOWS â€“ FAILS TO CONNECT TO PDB

Comments (0)

Please to add comments

No comments yet. Be the first to comment!