Unable to Drop Pluggable Database - ORA-00942

I was in process of building a test CDB, container database on one of the Exadata Machine and it was not fully ready to be used for testing. Meanwhile a DBA logged in to the system and fired the CREATE PLUGGABLE DATABASE command to create a PDB which completed with an error(error was not captured that time) but when he tried to drop that PDB he faced below issue.

When he tried to drop the PDB1, following error comes up.

SQL> drop pluggable database PDB1 including datafiles;
drop pluggable database PDB1 including datafiles
*
ERROR at line 1:
ORA-00942: table or view does not exist

CREATE PLUGGABLE DATABASE operation was not successful because catcdb.sql script was not completed in the container database itself and appropriate container dictionary views were not created yet to store the PDBs information in there. Hence CDB view cdb_pdbs doesn't not exist which stores the pluggable database information. So when he tried to drop the PDB, cdb_pdbs view being called to get information of the PDB that results in the error.

SQL> desc cdb_pdbs;
ERROR:
ORA-04043: object cdb_pdbs does not exist


SQL>

To fix the issue, run the catcdb.sql/or below 2 individual scripts to create all internal tables and container dictionary views.

$ /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql;
$ /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catproc $ORACLE_HOME/rdbms/admin/catproc.sql;

[oracle@exa01dbadm01bin]$ /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql;
catcon: ALL catcon-related output will be written to /home/oracle/catalog_catcon_42568.lst
catcon: See /home/oracle/catalog*.log files for output generated by scripts
catcon: See /home/oracle/catalog_*.lst files for spool files, if any
validate_con_names: PDB1 is not open
catconInit: Unexpected error returned by validate_con_names
Unexpected error encountered in catconInit; exiting
[oracle@exa01dbadm01bin]

You can't run the catcdb bundle script if all pluggable databases inside the CDB are opened or until you explicitly exclude the problematic/wish-list PDBs from candidates for script execution.

Use -C PDB1 option to exclude the PDB1 from the script execution candidate list as below, then script runs fine.

So, change the command as below to exclude the PDB1 from script execution.

$ /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catcon.pl -C PDB1 -n 1 -l /home/oracle -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql;
$ /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catcon.pl -C PDB1 -n 1 -l /home/oracle -b catproc $ORACLE_HOME/rdbms/admin/catproc.sql;

[oracle@exa01dbadm01~]$ /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catcon.pl -C PDB1 -n 1 -l /home/oracle -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql;
catcon: ALL catcon-related output will be written to /home/oracle/catalog_catcon_69532.lst
catcon: See /home/oracle/catalog*.log files for output generated by scripts
catcon: See /home/oracle/catalog_*.lst files for spool files, if any
catcon.pl: completed successfully  --- script completed successfully here......
[oracle@exa01dbadm01~]$

[oracle@exa01dbadm01~]$ /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catcon.pl -C PDB1 -n 1 -l /home/oracle -b catproc $ORACLE_HOME/rdbms/admin/catproc.sql;
catcon: ALL catcon-related output will be written to /home/oracle/catproc_catcon_105335.lst
catcon: See /home/oracle/catproc*.log files for output generated by scripts
catcon: See /home/oracle/catproc_*.lst files for spool files, if any
catcon.pl: completed successfully    --- script completed successfully here.....

[oracle@exa01dbadm01~]$

Now, CDB_PDBS view is created.

SQL> desc cdb_pdbs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PDB_ID                                    NOT NULL NUMBER
 PDB_NAME                                  NOT NULL VARCHAR2(128)
 DBID                                      NOT NULL NUMBER
 CON_UID                                   NOT NULL NUMBER
 GUID                                               RAW(16)
 STATUS                                             VARCHAR2(9)
 CREATION_SCN                                       NUMBER
 VSN                                                NUMBER
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 FORCE_NOLOGGING                                    VARCHAR2(3)
 CON_ID                                    NOT NULL NUMBER

SQL>

SQL> select * from cdb_pdbs;

    PDB_ID PDB_NAME                                                                                                                               DBID    CON_UID GUID                                  STATUS    CREATION_SCN
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- -------------------------------- --------- ------------
       VSN LOGGING   FOR FOR     CON_ID
---------- --------- --- --- ----------
         2 PDB$SEED                                                                                                                         2931554829 2931554829 8FA8EFE7EE287653E0537C17800A501C NORMAL          269
 202375680 LOGGING   NO  NO           2

         3 PDB1                                                                                                                             2665816632 2665816632 8FBC4BF9D200D225E0537C17800A60D5 UNUSABLE      57891
         0 LOGGING   NO  NO           3


SQL>

Let's now try to drop the PDB1 now and it works fine.

SQL> drop pluggable database PDB1 including datafiles;

Pluggable database dropped.

SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
SQL>

Hope it helps, subscribe to this blog to stay updated on latest Oracle Technologies and new articles.

Twitter : @rajsoft8899
Linkedin : https://www.linkedin.com/in/raj-kumar-kushwaha-5a289219/