CATCON.pl – ORA-12801 / ORA-00942: table or view does not exist

In oracle 12c, after creating a CDB database, I was facing a strange error. I was able to describe few of the dictionary tables, but was unable to query them.

Container Database Name: TSTCDB
Database Version : 12.1.0.1
DB Server: ora12c

After creating the CDB by running all the mandatory scripts (catalog.sql and catproc.sql), I was unable to query the dictionary tables. Here is the scenario.
I am connecting to the CDB and querying the view “CDB_DATA_FILES”.

[oracle@ora12c u02]$ sqlplus sys/oracle@tstcdb as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 30 17:23:55 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> desc cdb_data_files
Name                Null?      Type
------------------- ---------- --------------------
FILE_NAME                      VARCHAR2(513)
FILE_ID                        NUMBER
TABLESPACE_NAME                VARCHAR2(30)
BYTES                          NUMBER
BLOCKS                         NUMBER
STATUS                         VARCHAR2(9)
RELATIVE_FNO                   NUMBER
AUTOEXTENSIBLE                 VARCHAR2(3)
MAXBYTES                       NUMBER
MAXBLOCKS                      NUMBER
INCREMENT_BY                   NUMBER
USER_BYTES                     NUMBER
USER_BLOCKS                    NUMBER
ONLINE_STATUS                  VARCHAR2(7)
CON_ID                         NUMBER

Here, above you can notice that the description of the view worked fine. But the same when I tried to query the view, I faced an error message which read “ORA-00942: table or view does not exist”

SQL> select file_name,con_id from cdb_data_files order by con_id;
select file_name,con_id from cdb_data_files order by con_id
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P003
ORA-00942: table or view does not exist

Upon investigating, was able to find out that these errors appear when the CATALOG.SQL, CATPROC.SQL scripts were not executed using the catcon.pl script provided by Oracle.
This means that the data dictonary views were only created in the root database (CDB) but not in the seed database. This leads to the above errors.
In Oracle 12c, Oracle has introduced multiple database [PDB’s] inside one database [CDB]. For maintenance Oracle’s best way is to run SQL scripts by using catcon.pl. This allows the scripts to be run in the CDB (ROOT) and also in the required PDBs. This also generates log files that can be viewed to confirm that the SQL script or SQL statement did not generate unexpected errors.

Here is the usage of the CATCON.pl script.

[oracle@ora12c ~]$ . oraenv
ORACLE_SID = [tstcdb] ? tstcdb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12c ~]$
[oracle@ora12c ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@ora12c admin]$ perl catcon.pl

Usage: catcon [-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<SQL-statement> } ...

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 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
     <log-file-name-base>_<script_name_without_extension>_[<contai     ner_name_if_any>].<default_extension>
-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<SQL-statement> 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:' ...

No, let’s run the CATALOG.SQL, CATPROC.SQL scripts using the CATCON Perl script.

[oracle@ora12c ~]$ . oraenv
ORACLE_SID = [oracle] ? tstcdb
The Oracle base has been set to /u01/app/oracle
[oracle@ora12c ~]$
[oracle@ora12c ~]$ PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
[oracle@ora12c ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@ora12c admin]$ perl catcon.pl -l /home/oracle -b catalog /u01/app/oracle/product/12.1.0.1/db1/rdbms/admin/catalog.sql
[oracle@ora12c admin]$ perl catcon.pl -l /home/oracle -b catalog /u01/app/oracle/product/12.1.0.1/db1/rdbms/admin/catproc.sql

The log files are placed under the base directory (/home/oracle) which was mentioned by using the “-l” option with the log names as “catalog” and “catproc” which was mentioned by using the “-b” option.

[oracle@ora12c admin]$ cd /home/oracle
[oracle@ora12c ~]$
[oracle@ora12c ~]$ ls -lrt cat*
-rw-r--r-- 1 oracle oinstall 445152 Aug 31 11:34 catalog0.log
-rw-r--r-- 1 oracle oinstall 24709417 Aug 31 12:05 catproc0.log

Now after executing these scripts, lets try querying the dictionary views on the TSTCDB.

[oracle@ora12c ~]$ . oraenv
ORACLE_SID = [tstcdb] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12c ~]$
[oracle@ora12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Aug 31 12:09:03 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set linesize 300
SQL> col file_name for a55
SQL> select file_name,con_id from cdb_data_files order by con_id;

FILE_NAME                             CON_ID
------------------------------------- ----------------- ----------
/u02/oradata/tstcdb/system01.dbf      1
/u02/oradata/tstcdb/myts01.dbf        1
/u02/oradata/tstcdb/undotbs01.dbf     1
/u02/oradata/tstcdb/sysaux01.dbf      1
/u02/oradata/pdbseed/system01.dbf     2
/u02/oradata/pdbseed/sysaux01.dbf     2
/u02/oradata/pdbseed/myts01.dbf       2

7 rows selected.

Now, it works !!

Here we go !!

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.