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 youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator