Compile invalid catalog and catproc in CDB and PDB databases

Compile the invalid catalog and catproc in Container and PDB database

Compile the invalid catalog and catproc invalid component in Oracle 12c CDB and PDBs databases.

1. Set the Perl path from Oracle Home

set PATH=$ORACLE_HOME/perl/bin:$PATH
--OR--
export PATH=$ORACLE_HOME/perl/bin:$PATH

2. Used catcon.pl to run the commands on all open pdbs and cdb databases.
Then run the catalog.sql and catproc.sql from rdbms folder to make registry entry to valid.

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /tmp/validate_catalog.log append
alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /path_for_logs -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /path_for_logs -b catproc $ORACLE_HOME/rdbms/admin/catproc.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /path_for_logs -b name_for_logs $ORACLE_HOME/rdbms/admin/utlrp.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /path_for_logs -b name_for_logs $ORACLE_HOME/rdbms/admin/utlrp.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /path_for_logs -b name_for_logs $ORACLE_HOME/rdbms/admin/utlrp.sql;
spool off

Catcon.pl is used to execute the command in all PDBs and CDB database.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl
[-u username[/password]] [-U username[/password]] [-d directory]
[-l directory] [{-c|-C} container] [-p parallelism] [-e] [-s]
[-E { ON | errorlogging-table-other-than-SPERRORLOG } ] [-I] [-g] [-f]
-b log_file_name_base -- { SQL_script [arguments] | --x'SQL_statement' }

Options
-u for username and password connect with PDB’s or CDB for execute SQL queries or scripts.
-U for username and password with special privileges to perform internal tasks like modified metadata.
-d Directory containing SQL script
-l directory on which log file written.
-c list the containers in which SQL script is run.
-C list the containers in which SQL script is not run.
-p degree of parallelism.
-e means echo on when script running.
-s spool on
-E When ON, errors are written to the table SPERRORLOG in the current schema.
-I Identifier for Error logging option
-g generating debugging information.
-f ignore if PDB’s database is closed.
-b base name of log file (mandatory)

Example of Catcon:

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -d D:\script -b info_output info.sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.