Thursday, December 12, 2013

Oracle 12c - Oracle-Supplied - CATCON.pl

The installation of an Oracle database is done by executing serveral scripts. Those scripts perform operations such as creating data dictionary views and installing options. Oracle has now introduced multiple database [PDB's] inside one database [CDB]. For maintenacne and a new strategy Oracle's best way is to run SQL scripts and SQL statements is done by using catcon.pl. It can run them in the root and in specified PDBs in the correct order, and it generates log files that you can view to confirm that the SQL script or SQL statement did not generate unexpected errors.
It also starts multiple processes and assigns new scripts to them as they finish running scripts previously assigned to them.

Example of the Statement
perl /u02/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u02/app/oracle/admin/CDB1/scripts -b catalog /u02/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catalog.sql

CatCon.pl = Perl Module for creating - upgrading CATalogs for CONtainer databases


The catcon.pl script has the following syntax and parameters:

perl 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 } ] [-g] -b log_file_name_base 
{ SQL_script [arguments] | --xSQL_statement }

Usage: catcon.pl

  [-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 } ...
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$ROOT 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 
        __[].
   -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 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:' ...

Cause and Resolution
If CATALOG.SQL, CATPROC.SQL and PUPBLD.SQL scripts were not executed using the catcon.pl script provided by Oracle, but manually as you did before. This means views for the data dictonary were only created in your root database (the CDB) but not in the seed database. This will lead to errors :
SQL> CREATE PLUGGABLE DATABASE joords_pdb ADMIN USER jos IDENTIFIED BY tiger;
CREATE PLUGGABLE DATABASE joords_pdb  ADMIN USER jos IDENTIFIED BY tiger
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
SQL> select * from cdb_pdbs;
select * from cdb_pdbs
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P001
ORA-00942: table or view does not exist

Note:
  • Unless you exclude the seed when you run catcon.pl, the SQL script or SQL statement is run on the seed. 
  • You can use the catcon.pl script to run scripts on both CDBs and non-CDBs.