oracle 12c: catcon.pl

the last post quickly mentioned a new perl script which is used to setup the catalog in 12c. first of all the script is officially document here. in summary it is used to execute sql scripts in one or more containers. it is even possible to execute scripts in parallel, e.g. if there are multiple containers to run the scripts against.

if you take a look at the script it seems there is not much in there. but if you are used to read perl scripts you’ll quickly notify that all the logic is packed into a perl module file called catcon.pm ( which is located in the same directory ). here is a simple flow diagram which rawly describes the path through the perl code:

flow of the container scripts

so, when for for example this is executed:

!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/lcsdb -b catalog /opt/oracle/product/base/12.1.0.1/rdbms/admin/catalog.sql;

… this actually executes the catalog.sql in all the containers databases as no “-c”-switch is given. this for sure is one reason why the setup of the catalog takes much more time than in 11g.

we can confirm this by looking where catconExec is called in catcon.pl:

$RetCode = catconExec(@Scripts, 0, 0, 0);

the header of catconExec explains the parameters:

  # catconExec - run specified sqlplus script(s) or SQL statements
  #
  # If connected to a non-Consolidated DB, each script will be executed
  # using one of the processes connected to the DB.
  #
  # If connected to a Consolidated DB and the caller requested that all
  # scripts and SQL statements be run against the Root (possibly in addition
  # to other Containers), each script and statement will be executed in the
  # Root using one of the processes connected to the Root.
  #
  # If connected to a Consolidated DB and were asked to run scripts and SQL
  # statements in one or more Containers besides the Root, all scripts and
  # statements will be run against those PDBs in parallel
  #
  # Parameters:
  #   - reference to an array of sqlplus script name(s) or SQL statement(s);
  #     script names are expected to be prefixed with @
  #   - an indicator of whether scripts need to be run in order
  #       TRUE => run in order
  #   - an indicator of whether scripts or SQL statements need to be run only
  #     in the Root if operating on a CDB (temporarily overriding whatever
  #     was set by catconInit)
  #       TRUE => if operating on a CDB, run in Root only
  #   - an indicator of whether per process initialization/completion
  #     statements need to be issued
  #     TRUE => init/comletion statements, if specified, will be issued

in short: run all the scripts referenced in the first parameter ( which is a perl array ), do not run it in order, do not run it only in the root container and do not issue per process initialization/completion.

at the end a little hardcoding in catconInit: “(default sys/knl_test7 AS SYSDBA)”. i wonder if this is a default password used at oracle development :) :

find . -type f | xargs grep "knl_test7"
./admin/catcon.pm:#     set connect string to 'sys/knl_test7 AS SYSDBA'
./admin/catcon.pm:    $connect = "sys/knl_test7"; 
./admin/catcon.pm:  #     undefined (default sys/knl_test7 AS SYSDBA)

update 2013-JUN-29: knl_test7 is also referenced in some older documentations:
10g Release 1 (10.1)
Oracle 8i Data Cartridge Developer’s Guide