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;
-------------------------------------------------------------------------------- ----------
PDB$SEED READ ONLY
PQAUG01 READ WRITE
