8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | 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.
- Secure External Password Store
- SET CONTAINER
- TWO_TASK
- Scheduler
- catcon.pl
- 18c Onward (ORACLE_PDB_SID)
Related articles.
- Secure External Password Store
- Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 12c Release 1 (12.1)
- Multitenant : All Articles
- Multitenant : Running Scripts Against Multiple Pluggable Databases Using catcon.pl
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.
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>
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.
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
This section has been updated with examples run against Oracle 19c, but the examples work from Oracle 12.1 onward. You can watch a video of these examples here.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. Let's try some examples.
We'll start by checking our environment. We set the ORACLE_SID
of the database we want to use, and source oraenv
, to check our environment is set correctly. The catcon.pl script is in the "$ORACLE_HOME/rdbms/admin" directory.
export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES cd $ORACLE_HOME/rdbms/admin
Let's check to see what pluggable databases are present. We connect to the root container. The SHOW PDBS
command lists the pluggable databases. We can see we have the seed database, and 3 user-defined pluggable databases named PDB1, 2 and 3.
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 $
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.
# 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
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.
$ # 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 ==== $
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.
# 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
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.
$ # 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 ==== $
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.
# 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
Now we only have 2 log files. Log 0 contains the output from PDB2. Log 1 contains the output from PDB3.
$ # 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 ==== $
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.
# 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"
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.
$ # 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 ==== $
We can run multiple statements with a single call. Remember, we only need the "--" at the start of the list of non-option strings.
# 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"
We now have 4 log files, but they they contain entries for more than one PDB per log.
$ # 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 ==== $
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.
$ 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 ORACLE_PDB_SID
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.
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>
Notice that both the ORACLE_SID
and the ORACLE_PDB_SID
are set. It doesn't work if the ORACLE_SID
is not set.
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:
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.
Bug 31627193 – ORACLE_PDB_SID ENVIRONMENT VARIABLE DOES NOT WORK ON WINDOWS – FAILS TO CONNECT TO PDB
Also, see MOS Doc ID 2728684.1.
For more information see:
- Secure External Password Store
- Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 12c Release 1 (12.1)
- Running Oracle-Supplied SQL Scripts in a CDB
- Multitenant : All Articles
- Multitenant : Running Scripts Against Multiple Pluggable Databases Using catcon.pl
Hope this helps. Regards Tim...