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
Geef een reactie