Thursday, September 15, 2022

What is the catcon.pl script used in a Multitenant Database installation?

What is the catcton.pl script?

From Doc ID 1932340.1:

Oracle has provided script catcon.pl to execute scripts at Container and Pluggable database at once. In a CDB, the catcon.pl script is the best way to run SQL scripts and SQL statements. It can run them in the root and in specified PDBs in the correct order, and it generates log files.

It can be found in the $ORACLE_HOME/rdbms/admin folder.

Example of usage

To execute preupgrd.sql at CDB and all PDBs, copy preupgrd.sql and utlppkf.sql from the software version you want to upgrade to, temporary location (say /u01/oracle) PDB should be in open state before executing script. Its status can be checked using
SYS@cdb> connect / as sysdba
SYS@cdb>SQL>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SALES                          READ WRITE NO

At OS prompt, execute:
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catcon.pl -d /u01/oracle -l /home/oracle/preupgrd_logs -b preupgrade_log_base_name preupgrd.sql
The arguments used are:

-d = directory containing the file to be run (Location of preupgrade script)
-l = directory to use for spool log files
-b = base name for log and spool file names

Not that if neither the -c nor the -C parameter is specified, then catcon.pl runs the script in all containers by default:

-c - container(s) in which to run sqlplus scripts for example, -c 'PDB1 PDB2'
-C - container(s) in which NOT to run sqlplus scripts, i.e. skip all

Sources:
"How to execute sql scripts in Multitenant environment (catcon.pl) (Doc ID 1932340.1)"
"How to recompile invalid objects in all PDBs at the same time (Doc ID 2880643.1)"

Both from Oracle Support.

No comments:

Post a Comment