Martins Blog

Trying to explain complex things in simple terms

Tales from the field: potential reasons for PDB plug-in violations part 2

Posted by Martin Bach on July 14, 2016

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: