Wednesday, September 13, 2017

A workaround for ORA-06553: PLS-213: package STANDARD not accessible when using datapatch in a CDB

Short background:

I was having trouble applying "patch 26550023 - COMBO of OJVM Component 12.1.0.2.170718 DB PSU + DB PSU 12.1.0.2.170814" in my Multitenant environment. The container database only had one PDB at the time, the PDB$SEED.

After having successfully applied opatch apply for both patches, I ran datapatch -verbose to load modified SQL into the database. I had already opened my container database in upgrade mode, and also opened the PDB$SEED in upgrade mode by executing
alter pluggable database all open upgrade;
The state of the PDB$SEED could be confirmed in the alert log, as well as from v$pdb:

SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
--------- --------------
PDB$SEED   MIGRATE

Still, I kept getting weird errors like


Bootstrapping registry and package to current versions...done
Error in bootstrap log /u01/oracle/cfgtoollogs/sqlpatch/sqlpatch_17273_2017_09_13_13_29_29/bootstrap1_CDBVEG_PDBSEED.log:
Error at line 7: ORA-06553: PLS-213: package STANDARD not accessible
Error at line 17: ORA-06553: PLS-213: package STANDARD not accessible
Error at line 25: SP2-0310: unable to open file "/u01/oracle/product/12102/sqlpatch/FALSE.sql"
Prereq check failed, exiting without installing any patches.


There was little information about the problem and potential workarounds to be found on the internet.

After trying different options without success, I could find no other solution than to drop the PDB$SEED container, so that patching could continue.

Here's how:
SQL> 
-- necessarry to avoid "ORA-65017: seed pluggable database may not be dropped or altered"
alter session set "_oracle_script"=TRUE;

Session altered.

SQL> alter pluggable database PDB$SEED close;

Pluggable database altered.

SQL> drop pluggable database pdb$seed including datafiles;

Pluggable database dropped.

SQL> alter session set "_oracle_script"=FALSE;

Session altered.

SQL> select * from cdb_pdbs;

no rows selected


After this point, run datapatch again:
oracle@myserver:[cdbveg]# datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Wed Sep 13 13:34:05 2017
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/oracle/cfgtoollogs/sqlpatch/sqlpatch_18072_2017_09_13_13_34_05/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 26027162 (Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017)):
  Installed in the binary registry only
Bundle series PSU:
  ID 170814 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT
    Nothing to roll back
    The following patches will be applied:
      26027162 (Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017))
      26609783 (DATABASE PATCH SET UPDATE 12.1.0.2.170814)

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...
Patch 26027162 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/oracle/cfgtoollogs/sqlpatch/26027162/21319014/26027162_apply_CDBVEG_CDBROOT_2017Sep13_13_34_18.log (no errors)
Patch 26609783 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/oracle/cfgtoollogs/sqlpatch/26609783/21481899/26609783_apply_CDBVEG_CDBROOT_2017Sep13_13_34_18.log (no errors)
SQL Patching tool complete on Wed Sep 13 13:34:52 2017

Verification that the patches are applied:
SQL> select ACTION,DESCRIPTION,STATUS,BUNDLE_SERIES from registry$sqlpatch;

ACTION     DESCRIPTION                                                            STATUS               BUNDLE_SERIES
---------- ---------------------------------------------------------------------- -------------------- --------------------
APPLY      Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017)        SUCCESS
APPLY      DATABASE PATCH SET UPDATE 12.1.0.2.170814                              SUCCESS              PSU

No comments:

Post a Comment