Plug in violations and datapatch

When you plugin/clone a pluggable from a source container into a target container it is possible you have a mismatch in parameters or patches installed.

In this blog post I list an example what you will notice when you perform a pluggable cloning action, into a new container with newer installed patches. I always advise to check PDB_PLUG_IN_VIOLATIONS or run data patch -verbose as part of the procedure.

We first create a new pluggable database PPROD from another database container.



CREATE PLUGGABLE DATABASE PPROD FROM PPROD@DBCLONE_LINK file_name_convert=(‘/u01/app/oracle/oradata/PCDB01/datafile/PPROD/system01.dbf’,’/u01/app/oracle/oradata/PPROD/system01.dbf’,’/u01/app/oracle/oradata/PCDB01/datafile/PPROD/sysaux01.dbf’,’/u01/app/oracle/oradata/PPROD/sysaux01.dbf’,’/u01/app/oracle/oradata/PCDB01/datafile/PPROD/PPROD_users01.dbf’,’/u01/app/oracle/oradata/PPROD/PPROD_users01.dbf’,’/u01/app/oracle/oradata/PCDB01/datafile/PPROD/PURE_DATA’,’/u01/app/oracle/oradata/PPROD/PURE_DATA’,’/u01/app/oracle/oradata/PCDB01/datafile/PPROD/temp01.dbf’,’/u01/app/oracle/oradata/PPROD/temp01.dbf’);

In the alert.log you will see the execution of the statement and the below output:

****************************************************************
Pluggable Database PPROD with pdb id – 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************

Database Characterset for PPROD is AL32UTF8
Deleting old file#7 from file$
Deleting old file#8 from file$
Deleting old file#9 from file$
Deleting old file#10 from file$
Adding new file#10 to file$(old file#7)
Adding new file#11 to file$(old file#8)
Adding new file#12 to file$(old file#9)
Adding new file#13 to file$(old file#10)
Successfully created internal service PPROD at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PPROD with pdb id – 4 is now marked as NEW.
****************************************************************

…….

ALTER PLUGGABLE DATABASE PPROD OPEN
Pluggable database PPROD dictionary check beginning
Pluggable Database PPROD Dictionary check complete
Database Characterset for PPROD is AL32UTF8
***************************************************************
WARNING: Pluggable Database PPROD with pdb id – 4 is
         altered with errors or warnings. Please look into
         PDB_PLUG_IN_VIOLATIONS view for more details.
***************************************************************

Checking the current status of the pluggable it is open but in restricted mode.

CDB$ROOT@PCDB01:SYS> show pdbs
CON_ID CON_NAME                 OPEN MODE RESTRICTED
---------- -------------------- ---------- ----------
 2 PDB$SEED                      READ ONLY NO
 3 PRMAN                         READ WRITE NO
 4 PPROD                         READ WRITE YES
CDB$ROOT@PCDB01:SYS> select name,cause,message from pdb_plug_in_violations;
NAME     CAUSE MESSAGE
 -------- ---------- ----------------------------------------
PPROD   SQL Patch PSU bundle patch 2 (Database Patch Set Update : 12.1.0.2.2 (19769480)):
Installed in the PDB but not in the CDB.
PPROD   SQL Patch PSU bundle patch 4 (Database Patch Set Update : 12.1.0.2.4 (20831110)): 
Installed in the CDB but not in the PDB.
PPROD   SQL Patch SQL patch ID/UID 19877336/18313828 (Database PSU 12.1.0.2.2, Oracle JavaVM Component (): 
Installed in the PDB but not in the CDB.
PPROD   SQL Patch SQL patch ID/UID 21068507/18985494 (Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015)): 
Installed in the CDB but not in the PDB.

Now it is time to run datapatch -verbose it will display the steps and also report if things are wrong. So without doing anything just execute datapatch and check the output


 
[oracle@PCDB01@server43 /u01/app/oracle/product/12.1.0/dbhome_1/OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0
Copyright (c) 2015, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_21698_2015_10_23_07_29_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 19877336 (Database PSU 12.1.0.2.2, Oracle JavaVM Component ():
 Installed in PPROD only
 Patch 21068507 (Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015)):
 Installed in binary and CDB$ROOT PDB$SEED PRMAN
 Bundle series PSU:
 ID 4 in the binary registry and ID 4 in PDB CDB$ROOT, ID 4 in PDB PDB$SEED, ID 4 in PDB PRMAN, ID 2 in PDB PPROD
Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED PRMAN
 Nothing to roll back
 Nothing to apply
 For the following PDBs: PPROD
 The following patches will be rolled back:
 19877336 (Database PSU 12.1.0.2.2, Oracle JavaVM Component ()
 The following patches will be applied:
 21068507 (Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015))
 20831110 (Database Patch Set Update : 12.1.0.2.4 (20831110))
Error: prereq checks failed!
 patch 21068507: The pluggable databases that need to be patched must be in upgrade mode
 Prereq check failed, exiting without installing any patches.
Please refer to MOS Note 1609718.1 and/or the invocation log
 /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_21698_2015_10_23_07_29_00/sqlpatch_invocation.log
 for information on how to resolve the above errors.
SQL Patching tool complete

Now start the pluggable PPROD in migrate mode and rerun the datapatch to install the proper patches.


 CDB$ROOT@PCDB01:SYS> alter pluggable database PPROD close;
 CDB$ROOT@PCDB01:SYS> alter pluggable database PPROD open upgrade;

 

CDB$ROOT@PCDB01:SYS> show pdbs
CON_ID CON_NAME                       OPEN MODE RESTRICTED
 ---------- ------------------------------ ---------- ----------
 2 PDB$SEED                      READ ONLY NO
 3 PRMAN                         READ WRITE NO
 4 PPROD                         MIGRATE   YES

 
Reexecute the datapatch and you will notice that the patches are applied.
[oracle@PCDB01@server43 /u01/app/oracle/product/12.1.0/dbhome_1/OPatch]$ ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 o
Copyright (c) 2015, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_26610_2015_10_23_07_30_37/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 19877336 (Database PSU 12.1.0.2.2, Oracle JavaVM Component ():
 Installed in PPROD only
 Patch 21068507 (Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015)):
 Installed in binary and CDB$ROOT PDB$SEED PRMAN
 Bundle series PSU:
 ID 4 in the binary registry and ID 4 in PDB CDB$ROOT, ID 4 in PDB PDB$SEED, ID 4 in PDB PRMAN, ID 2 in PDB PPROD
Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED PRMAN
 Nothing to roll back
 Nothing to apply
 For the following PDBs: PPROD
 The following patches will be rolled back:
 19877336 (Database PSU 12.1.0.2.2, Oracle JavaVM Component ()
 The following patches will be applied:
 21068507 (Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015))
 20831110 (Database Patch Set Update : 12.1.0.2.4 (20831110))
Installing patches...
Patch installation complete. Total patches installed: 3
 Validating logfiles...
Patch 19877336 rollback (pdb PPROD): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/19877336/18313828/19877336_rollback_PCDB01_PPROD_2015Oct23_07_30_52.log (no errors)
 Patch 21068507 apply (pdb PPROD): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/21068507/18985494/21068507_apply_PCDB01_PPROD_2015Oct23_07_31_09.log (no errors)
 Patch 20831110 apply (pdb PPROD): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/20831110/18977826/20831110_apply_PCDB01_PPROD_2015Oct23_07_31_09.log (no errors)

 

SQL Patching tool complete

 

CDB$ROOT@PCDB01:SYS> alter pluggable database PPROD close;
 CDB$ROOT@PCDB01:SYS> alter pluggable database PPROD open;

 

CDB$ROOT@PCDB01:SYS> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED READ ONLY NO
 3 PRMAN READ WRITE NO
 4 PPROD READ WRITE NO

 
In addition to the above you can check again the PDB_PLUG_IN_VIOLATIONS and use the STATUS column to see it the status is <> ‘RESOLVED’.


CDB$ROOT@PCDB01:SYS> select name,cause,type,action from pdb_plug_in_violations where status <> 'RESOLVED';
no rows selected

 

10 november 2015|12c, Multitenancy, rdbms|

Share This Story, Choose Your Platform!

Leave A Comment