Toad World Blog

How to run SQL Statements across Pluggable Databases with catcon.pl

May 24, 2017 10:18:00 AM by Deiby Gomez

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.

Tags: Oracle

Deiby Gomez

Written by Deiby Gomez

Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.

Certifications:

  • Oracle Database Cloud Administrator Certified Associate
  • Oracle Database Cloud Administrator Certified Professional
  • Oracle Database Cloud Service Operations Certified Associate
  • Oracle Certified Expert, Oracle Database 12c Maximum Availability Architecture
  • Oracle Certified Expert, Oracle Database 12c: RAC and Grid Infrastructure Administration
  • Oracle Database 12c Maximum Availability Architecture Certified Master
  • Oracle Database 12c: Data Guard Administrator
  • Oracle Database 12c Administrator Certified Master (OCM 12c)
  • Oracle Real Application Clusters 12c Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Master (OCM 11g)
  • Oracle Database 12c Administrator Certified Professional (OCP 12c)
  • Oracle RAC 11g and Grid Infraestructure Administrator
  • Oracle Certified Expert, Oracle Exadata X3 and X4 Administrator
  • Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert
  • Oracle Database 11g Administrator Certified Professional (OCP 11g)
  • Oracle Linux Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Associate