Thursday, February 16, 2017

PSU bundle patch 5 Database Patch Set Update : 12.1.0.2.x : Installed in the CDB but not in the PDB 

OR 

validate_con_names: PDB$SEED is not open


The issue happens to one of our Oracle Database Appliance where after patching one the PDB fails to start. 

Upon checking the issue we came across following. The reason that caused the issue was when patch was applied the PDB was not open in read/write mode, hence datapatch component was not applied on that PDB 

SQL> select * from PDB_PLUG_IN_VIOLATIONS;

26-JUL-16 02.15.57.395533 PM                                                PQGECAH02                                                    SQL Patch                                                         ERROR                0          1
PSU bundle patch 5 (Database Patch Set Update : 12.1.0.2.5 (21359755)): Installed in the PDB but not in the CDB.
PENDING
Call datapatch to install in the PDB or the CDB

Then we tried following queries to open the PDB in read write mode. 

SQL> alter session set container = xxxxxx;

alter system disable restricted session;
select con_id,name,open_mode from gv$pdbs;
select con_id,logins,status from gv$instance;


SYS@xxxxx>select con_id,name,open_mode from gv$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 xxxxx                               READ WRITE
         2 PDB$SEED                       READ ONLY
         3 xxxxx                               MOUNTED


SYS@xxxxx>SELECT restricted FROM gv$containers WHERE con_id = 3;

RES
---
YES

SYS@CQXAC06P1>alter session set container = xxxxxx;
Session altered.

SYS@xxxxx>shutdown
Pluggable Database closed.

SYS@xxxxx>startup
Warning: PDB altered with errors.
Pluggable Database opened.

SYS@xxxxx>select con_id, name , open_mode , restricted FROM gv$containers ;

    CON_ID NAME                           OPEN_MODE  RES
---------- ------------------------------ ---------- ---
         1 CDB$ROOT                       READ WRITE NO
         2 PDB$SEED                       READ ONLY  NO
         3 xxxxx                               READ WRITE YES
         1 CDB$ROOT                       READ WRITE NO
         2 PDB$SEED                       READ ONLY  NO
         3 xxxxx                               READ WRITE YES


Run following from one of the nodes.
./datapatch -verbose -pdbs xxxxxx

[oracle@xxxxOPatch]$ ./datapatch -verbose -pdbs xxxxxx
SQL Patching tool version 12.1.0.2.0 on Thu May 26 13:25:17 2016
Copyright (c) 2015, Oracle.  All rights reserved.

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

Bootstrapping registry and package to current versions...done
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:
Bundle series PSU:
  ID 5 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: PQBSMAC06
    Nothing to roll back
    The following patches will be applied:
      21359755 (Database Patch Set Update : 12.1.0.2.5 (21359755))

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

Validating logfiles...
Patch 21359755 apply (pdb PQBSMAC06): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/21359755_apply_CQXAC06P_PQBSMAC06_2016May26_13_26_04.log (no errors)
SQL Patching tool complete on Thu May 26 13:26:09 2016


Lower patch in CDB_DEST than in CDB_SOURCE?

There is another situation, where your PDB has higher patch level than your CDB. 
Now this becomes a bit more tricky. See the output of PDB_PLUG_IN_VIOLATIONS:

TYPE  MESSAGE
---- ----------------------------------------------------------------------------
ERROR PSU bundle patch 1 (PSU Patch 4711): Installed in the PDB but not in the CDB
ACTION
------------------------------------------------
Call datapatch to install in the PDB or the CDB

What does this mean? Should I install now the current CDB/PDB's PSU into my target environment before being able to step down? 

If you think this message is misleading. And when you look into the MyOracle Support Note describing this under scenario 3 (MOS Note:1935365.1 - Multi tenant Unplug/Plug Best Practices) you'll see that the author silently assumed as well that is is more likely that you'll remove the patch from the PDB. 

But how do you remove changes which came in with datapatch from within a PDB only?

You will need to run datapatch -rollback on the affected PDBs only:

[oracle@xxxxOPatch]$ ./datapatch -rollback <patch id> –force [–bundle_series] -pdbs <pdb1,pdb2,...,pdbn>

This will rollback any patches installed on PDBs and then you can subsequently run the datapatch on CDB, which will fix all the issues. 

One more workaround would be following. 

SQL>alter pluggable database pdb close immediate instances=all;

SQL>alter pluggable database pdb open upgrade;

SQL>>select * from gv$pdbs;


-- Run datapatch from ORACLE_HOME/OPatch

[oracle@xxx OPatch]$ ./datapatch -verbose

There is one more thing that one can come across when applying datapatch on CDB. 
during datapatch apply it may wait and fail on time out with failure to grant access to one user as follows. 


SQL> GRANT SELECT ON sys.gv_$instance TO dv_secanalyst
  2  /
GRANT SELECT ON sys.gv_$instance TO dv_secanalyst
                    *
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object


WORKAROUND:

One can startup the database in restrict mode and execute the failed SQL statement manually but require downtime to startup the database in restrict mode.


SQL> GRANT SELECT ON sys.gv_$instance TO dv_secanalyst

The above statement will not work if you startup the database in normal mode.


Another Issue - 


Error
validate_con_names: PDB$SEED is not open
catconInit: Unexpected error returned by validate_con_names for inclusive Container list

Cause - 

PDB$SEED is not open:

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE
------------------------------ ----------
PDB$SEED MOUNTED
PQAUG01  READ WRITE 


Fix - 

SQL> alter session set "_oracle_script"=TRUE;
SQL> alter pluggable database pdb$seed OPEN READ ONLY;
SQL> alter session set "_oracle_script"=FALSE;
SQL> SELECT name, open_mode FROM v$pdbs;

NAME                                                                             OPEN_MODE
-------------------------------------------------------------------------------- ----------
PDB$SEED                                                                         READ ONLY
PQAUG01                                                                          READ WRITE