In the first part of this little series I explained how a missing (default) tablespace in a PDB can have an impact when opening it. In this instalment I’ll add another common cause for PDBs not opening in read-write mode: different patch levels between the PDB to be plugged in and the CDB.
Scenario
Assume for a moment that you upgraded Oracle binaries from 12.1.0.2.5 to 12.1.0.2.160419. In other words, you applied the most current PSU + OJVM patch at the time of writing to your database. Once complete, you would see output similar to this for a single instance Oracle 12c installation without Oracle Restart:
[oracle@server2 ~]$ opatch lspatches 22674709;Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016) 22291127;Database Patch Set Update : 12.1.0.2.160419 (22291127) OPatch succeeded.
I am using database CDB1 in this blog post to indicate the patched CDB:
SYS@CDB$ROOT-CDB1> select patch_id,action,status,description 2 from dba_registry_sqlpatch; PATCH_ID ACTION STATUS ---------- --------------- --------------- DESCRIPTION -------------------------------------------------------------------------------- 21555660 APPLY SUCCESS Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015) 21359755 APPLY SUCCESS Database Patch Set Update : 12.1.0.2.5 (21359755) 21555660 ROLLBACK SUCCESS Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015) 22674709 APPLY SUCCESS Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016) 22291127 APPLY SUCCESS Database Patch Set Update : 12.1.0.2.160419 (22291127)
NB: I double-checked twice and the above output seems correct: there is no rollback of the 12.1.0.2.5 DB PSU part prior to the installation of the new one.
Let’s also assume that you would like to plug a PDB into the newly patched CDB. The PDB to be unplugged from the other CDB (named CDB2) has a lower patch level:
SYS@CDB$ROOT-CDB2> select patch_id,action,status,description 2 from dba_registry_sqlpatch; PATCH_ID ACTION STATUS ---------- --------------- --------------- DESCRIPTION -------------------------------------------------------------------------------- 21555660 APPLY SUCCESS Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015) 21359755 APPLY SUCCESS Database Patch Set Update : 12.1.0.2.5 (21359755)
Plugging in
The steps needed to plug the PDB into its new home have been discussed so many times that I have decided against showing them again. Please refer to the official documentation set (Database Administrator’s Guide) for all the detail. The PDB I would like to plug in to CDB1 is named PLUGINTEST.
SYS@CDB$ROOT-CDB1> create pluggable database plugintest 2 using '/home/oracle/plugintest.xml' nocopy; Pluggable database created. SYS@CDB$ROOT-CDB1> alter pluggable database plugintest open; Warning: PDB altered with errors.
With the information I provided previously you can probably guess what’s wrong, but if you are a production DBA who has been tasked to “plug in a PDB” you might not have the background knowledge about patch levels of all the databases in the estate. How can you find out what went wrong? First stop is the alert.log:
alter pluggable database plugintest open
Thu Jul 07 11:02:30 2016
Pluggable database PLUGINTEST dictionary check beginning
Pluggable Database PLUGINTEST Dictionary check complete
Database Characterset for PLUGINTEST is WE8MSWIN1252
***************************************************************
WARNING: Pluggable Database PLUGINTEST with pdb id - 5 is
altered with errors or warnings. Please look into
PDB_PLUG_IN_VIOLATIONS view for more details.
***************************************************************
Opening pdb PLUGINTEST (5) with no Resource Manager plan active
Pluggable database PLUGINTEST opened read write
Completed: alter pluggable database plugintest open
This sounds a bit tragic, but not all is lost. The primary issue is that the PDB is opened in restricted mode, which is not terribly useful for ordinary users:
SYS@CDB$ROOT-CDB1> select con_id, name, open_mode, restricted
2* from v$pdbs where name = 'PLUGINTEST'
CON_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
5 PLUGINTEST READ WRITE YES
SYS@CDB$ROOT-CDB1> conn user1/user1@localhost/plugintest
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Warning: You are no longer connected to ORACLE.
The detail is – again – in PDB_PLUG_IN_VIOLATIONS:
SYS@CDB$ROOT-CDB1> select cause, type, message 2 from PDB_PLUG_IN_VIOLATIONS 3 where name = 'PLUGINTEST'; CAUSE TYPE ---------------------------------------------------------------- --------- MESSAGE -------------------------------------------------------------------------------- SQL Patch ERROR PSU bundle patch 160419 (Database Patch Set Update : 12.1.0.2.160419 (22291127)) : Installed in the CDB but not in the PDB. SQL Patch ERROR PSU bundle patch 5 (Database Patch Set Update : 12.1.0.2.5 (21359755)): Installe d in the PDB but not in the CDB. SQL Patch ERROR SQL patch ID/UID 21555660/19361790 (Database PSU 12.1.0.2.5, Oracle JavaVM Compo nent (Oct2015)): Installed in the PDB but not in the CDB. SQL Patch ERROR SQL patch ID/UID 22674709/20057886 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)): Installed in the CDB but not in the PDB.
Although the formatting is a bit of a pain to the eye, it should become clear that there are different patch levels preventing the PDB from opening read-write. If the number of options were different between the system a similar error would be thrown. But how does Oracle know about all that? It’s encoded in the XML file that you create when you unplug the PDB. In my case, here are the options used:
<options>
<option>APS=12.1.0.2.0</option>
<option>CATALOG=12.1.0.2.0</option>
<option>CATJAVA=12.1.0.2.0</option>
<option>CATPROC=12.1.0.2.0</option>
<option>CONTEXT=12.1.0.2.0</option>
<option>DV=12.1.0.2.0</option>
<option>JAVAVM=12.1.0.2.0</option>
<option>OLS=12.1.0.2.0</option>
<option>ORDIM=12.1.0.2.0</option>
<option>OWM=12.1.0.2.0</option>
<option>SDO=12.1.0.2.0</option>
<option>XDB=12.1.0.2.0</option>
<option>XML=12.1.0.2.0</option>
<option>XOQ=12.1.0.2.0</option>
</options>
Likewise, patches are recorded in the XML file, too:
<sqlpatches>
<sqlpatch>PSU bundle patch 5 (Database Patch Set Update : 12.1.0.2.5 (21359755)):
APPLY SUCCESS</sqlpatch>
<sqlpatch>SQL patch ID/UID 21555660/19361790 (Database PSU 12.1.0.2.5, Oracle JavaVM
Component (Oct2015)): APPLY SUCCESS</sqlpatch>
</sqlpatches>
Corrective Action
The suggested action is conveniently presented as well.
SYS@CDB$ROOT-CDB1> select cause, type, action from pdb_plug_in_violations where name = 'PLUGINTEST'; CAUSE TYPE ACTION ---------- --------- ------------------------------------------------------------ SQL Patch ERROR Call datapatch to install in the PDB or the CDB SQL Patch ERROR Call datapatch to install in the PDB or the CDB SQL Patch ERROR Call datapatch to install in the PDB or the CDB SQL Patch ERROR Call datapatch to install in the PDB or the CDB
But you don’t follow blindly what you read, do you ;) So I headed over to the official documentation set and My Oracle Support. I found MOS note 1585822.1 most useful; it explains a similar situation (PDB was closed while datapatch ran) to the one I am seeing. So I decided to try running datapatch again.
SYS@CDB$ROOT-CDB1> alter pluggable database plugintest close immediate;
Pluggable database altered.
SYS@CDB$ROOT-CDB1> alter pluggable database plugintest open upgrade;
Pluggable database altered.
SYS@CDB$ROOT-CDB1> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@server2 ~]$ cd $ORACLE_HOME/OPatch
[oracle@server2 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Thu Jul 7 11:20:00 2016
Copyright (c) 2015, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_15280_2016_07_07_11_20_00/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 21555660 (Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)):
Installed in PLUGINTEST only
Patch 22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)):
Installed in binary and CDB$ROOT PDB$SEED SWINGBENCH1
Bundle series PSU:
ID 160419 in the binary registry and ID 160419 in PDB CDB$ROOT, ID 160419 in PDB PDB$SEED,
ID 160419 in PDB SWINGBENCH1, ID 5 in PDB PLUGINTEST
Adding patches to installation queue and performing prereq checks...
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED SWINGBENCH1
Nothing to roll back
Nothing to apply
For the following PDBs: PLUGINTEST
The following patches will be rolled back:
21555660 (Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015))
The following patches will be applied:
22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016))
22291127 (Database Patch Set Update : 12.1.0.2.160419 (22291127))
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...
Patch 21555660 rollback (pdb PLUGINTEST): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/21555660/19361790/21555660_rollback_CDB1_PLUGINTEST_2016Jul07_11_20_35.log (no errors)
Patch 22674709 apply (pdb PLUGINTEST): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22674709/20057886/22674709_apply_CDB1_PLUGINTEST_2016Jul07_11_21_03.log (no errors)
Patch 22291127 apply (pdb PLUGINTEST): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_CDB1_PLUGINTEST_2016Jul07_11_21_04.log (no errors)
SQL Patching tool complete on Thu Jul 7 11:21:14 2016
This fixed my problem:
SYS@CDB$ROOT-CDB1> alter pluggable database plugintest close immediate;
Pluggable database altered.
SYS@CDB$ROOT-CDB1> alter pluggable database plugintest open;
Pluggable database altered.
SYS@CDB$ROOT-CDB1> select con_id, name, open_mode, restricted
2 from v$pdbs where name = 'PLUGINTEST';
CON_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
5 PLUGINTEST READ WRITE NO
Interestingly the entries in PDB_PLUG_IN_VIOLATIONS do not disappear, but there is a timestamp in the view that should help you find out of the message is current or not.
Martins Blog 
Pingback: Purging of PDB_PLUG_IN_VIOLATIONS in Oracle 18c