Introduction
Beginning with Oracle Database 12.1.0.1.0, DBAs started to work with Pluggable Databases. There were some large migrations of several databases from 10g/11g to 12c where they were consolidated into a new Oracle Database Container using several Pluggable Databases. However, running operations in several Pluggable Databases became a problem, since people had to login into every Pluggable Database and to run the required script or SQL Statement there. To avoid causing people to spend too much time doing this kind of work Oracle introduced the Perl script “catcon.pl”. Basically catcon.pl receives either a script or the text of a SQL Statement and executes it in the Pluggable Databases that we specify, even in PDB$SEED and CDB$ROOT, depending on which flags of catcon.pl are used. In the following image we see a script received by catcon.pl, and catcon.pl executes the script in CDB$ROOT and PDB$SEED if the flag “-S” is used as well as in the rest of Pluggable Databases.
Using catcon.pl considerably reduces the time spent on running scripts across several databases. One of its advantages is that you can filter the pluggable databases where you want to execute the script or SQL Statement by using “-C” for exclusion of pluggable databases and “-c” for inclusion of pluggable databases. You can also specify the order of the pluggable databases where the script or SQL statement has to be executed.
In this article we will use the environment described in the previous image. I will start creating the three pluggable databases and the scripts that will be executed across the PDBs:
SQL> create pluggable database PDB1 admin user pdbadmin identified by nuvola;
Pluggable database created.
SQL> create pluggable database PDB2 admin user pdbadmin identified by nuvola;
Pluggable database created.
SQL> create pluggable database PDB3 admin user pdbadmin identified by nuvola;
Pluggable database created.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs;
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
Creating the Script #1:
The following script contains a CREATE TABLE statement, an INSERT statement, a commit and a SELECT statement. All these operations use the same table, C##DGOMEZ.COUNTRY.
[oracle@nuvola2 ~]$ pwd
/home/oracle
[oracle@nuvola2 ~]$ vi script.sql
[oracle@nuvola2 ~]$ cat script.sql
show con_name;
create table c##dgomez.country (name varchar2(20));
insert into c##dgomez.country values ('Guatemala');
commit;
select * from c##dgomez.country ;
[oracle@nuvola2 admin]$
Creating the Script #2:
This script doesn’t create any table; instead, it only inserts rows in the table C##DGOMEZ.COUNTRY
[oracle@nuvola2 admin]$ cat /home/oracle/script2.sql
insert into c##dgomez.country values ('Canada');
commit;
[oracle@nuvola2 admin]$
Running catcon.pl without “-S” flag:
When the flag “-S” is not used, catcon.pl executes the script or the SQL Statement in all the containers including CDB$ROOT and PDB$SEED. Also all the objects created by catcon.pl are created as “ORACLE_MAINTAINED”, which means that those will be objects owned by Oracle and which cannot be modified by any database user. I don’t recommend using this method to create objects for the business or our application schema; this method is used to run perhaps a script for patching, migration, or any other task that touches the data dictionary or any other aspect owned by Oracle.
Moving to the directory where catcon.pl is located:
[oracle@nuvola2 ~]$ cd $ORACLE_HOME/rdbms/admin
Executing catcon.pl. The flag “-d” specifies where the script is located. The flag “-l” specifies the directory where all the logs will be created. The flag “-b” specifies the prefix name of the log files that will be generated and finally the value with the name of the script that will be executed by catcon.pl.
[oracle@nuvola2 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d /home/oracle -l /home/oracle/catcon_logs -b catcon-example script.sql
As you can see, the script was executed and it created the objects as “ORACLE_MAINTAINED”. The script was executed in CDB$ROOT and also in PDB$SEED. In this example, the script failed in PDB$SEED because the schema c##dgomez didn’t exist within the PDB, and catcon.pl couldn’t create the table.
SQL> select con_id, owner, object_name, object_type, ORACLE_MAINTAINED from cdb_objects where owner='C##DGOMEZ';
CON_ID OWNER OBJECT_NAM OBJECT_TYP ORACLE_MAINTAIN
---------- ---------- ---------- ---------- ---------------
1 C##DGOMEZ COUNTRY TABLE Y
3 C##DGOMEZ COUNTRY TABLE Y
4 C##DGOMEZ COUNTRY TABLE Y
5 C##DGOMEZ COUNTRY TABLE Y
Running catcon.pl with “-S” flag
I recommend using this flag when you are running either a script or SQL Statement that create objects for your business application schema like the Script #1 or the Script #2 that I created in this article. In other words, when you are running operations not related to patching, upgrades, or to the data dictionary. When the flag “-S” is used, catcon.pl doesn’t execute the script in CDB$ROOT or in PDB$SEED.
[oracle@nuvola2 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@nuvola2 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d /home/oracle -l /home/oracle/catcon_logs -S -b catcon-example script.sql
catcon: ALL catcon-related output will be written to [/home/oracle/catcon_logs/catcon-example_catcon_26297.lst]
catcon: See [/home/oracle/catcon_logs/catcon-example*.log] files for output generated by scripts
catcon: See [/home/oracle/catcon_logs/catcon-example_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@nuvola2 admin]$
The logs will be generated in the directory “/home/oracle/catcon_logs” with the prefix “catcon-example” as it was specified:
[oracle@nuvola2 admin]$ ls -ltr /home/oracle/catcon_logs/
total 12
-rw-r--r-- 1 oracle oinstall 419 May 7 05:57 catcon-example_catcon_26297.lst
-rw-r--r-- 1 oracle oinstall 3371 May 7 05:58 catcon-example0.log
-rw-r--r-- 1 oracle oinstall 1922 May 7 05:58 catcon-example1.log
[oracle@nuvola2 admin]$
The script was executed only in the pluggable databases. It was not executed in CDB$ROOT nor PDB$SEED and the table was created as non-Oracle maintained:
SQL> select con_id, owner, object_name, object_type, ORACLE_MAINTAINED from cdb_objects where owner='C##DGOMEZ'
CON_ID OWNER OBJECT_NAM OBJECT_TYP ORACLE_MAINTAINED
---------- ---------- ---------- ---------- -----------------
3 C##DGOMEZ COUNTRY TABLE N
4 C##DGOMEZ COUNTRY TABLE N
5 C##DGOMEZ COUNTRY TABLE N
We can verify that the table was created and the rows inserted in every PDB:
SQL> select con_id, name from containers(C##DGOMEZ.COUNTRY) ;
CON_ID NAME
---------- --------------------
1 Guatemala
3 Guatemala
4 Guatemala
5 Guatemala
NOTE: I manually created the table in CDB$ROOT, just to make the CONTAINERS clause work.
In the following example I am using the flag “-c”, which is useful when we want to use “inclusion”. We have to provide the list of the PDBs where the script will be executed. In this example, the script will be executed only in PDB1 and PDB3. I will use in this example the script #2, which performs only an INSERT operation.
[oracle@nuvola2 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d /home/oracle -l /home/oracle/catcon_logs -S
-c 'PDB1 PDB3'-b catcon-example script2.sql
catcon: ALL catcon-related output will be written to [/home/oracle/catcon_logs/catcon-example_catcon_27384.lst]
catcon: See [/home/oracle/catcon_logs/catcon-example*.log] files for output generated by scripts
catcon: See [/home/oracle/catcon_logs/catcon-example_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@nuvola2 admin]$
We can verify whether the script was executed in only PDB1 and PDB3 by querying the table c##dgomez.country:
[oracle@nuvola2 admin]$ sqlplus / as sysdba
SQL> select con_id, name from containers(C##DGOMEZ.COUNTRY) ;
CON_ID NAME
---------- --------------------
1 Guatemala
3 Guatemala
3 Canada
4 Guatemala
5 Guatemala
5 Canada
8 rows selected.
Conclusion
When the multi-tenant architecture was introduced, the Perl script catcon.pl was also introduced to help running frequent scripts in multiple pluggable databases. In this article we saw some examples where different flags of catcon.pl were used, such as the flag to include or exclude PDB, the flag to execute a script as if it was provided by Oracle, and when we want to create objects for our application schema. There was also an example in which the order of PDB was provided. The Perl script catcon.pl is certainly useful to avoid wasting too much time executing the same task in every PDB.
Start the discussion at forums.toadworld.com