Starting 12c, it is even easier to patch/upgrade existing databases if you have already employed multitenant environment. To do patching in multitenant environment, we can install a new Oracle home and patch it to the level we want to patch, and then run requited scripts (post-patch scripts) in the container database (CDB). At this point, you will have 2 oracle homes, one new and one old home where you have current pluggable database(s) running (which remain available while we install and patch new oracle home).
Now you just need to unplug your pluggable database(s) form old CDB (running under old home), and plug into the new oracle home. This way of patching reduces your down time drastically.
In the following I will explain how we perform the patching on windows (using bundle patch), but process is same on Linux based platforms (we use PSUs for Linux based platforms).
1)
In this scenario, I have a 12.1.0.2 CDB with 1 PDB (pdb2) running on windows platform. To apply Bundle Patch 22809813, I installed a new oracle home 12.1.0.2 and applied the bundle patch 22809813. Installation of patch is not being covered here as you can follow the patch installation guide for that purpose.
2)
Now I unplugged my PDB that is running from old home. Check my article unpluggingand plugging in a pluggable database for how to unplug and plug a pluggable database.
3)
I plugged in my pdb in CDB running from new patch oracle home.
4)
I Opened my pdb and executed datapatch to patch my newly plugged-in PDBs. After opening the PDBs, I immediately saw the warning, and reason of warning is patch post installation steps that are yet to be executed in my PDB.
SQL> alter pluggable database pdb2 open;
Warning: PDB altered with errors.
col name format a8
col cause format a20
col message format a20
select name, cause, message,action from pdb_plug_in_violations;
NAME CAUSE MESSAGE ACTION
-------- -------------------- -------------------------------------------------------------------------------- ---------------------------------------------
PDB2 SQL Patch PSU bundle patch 160419 Installed in the Call datapatch to install in the PDB or the CDB
CDB but not in the PDB (WINDOWS DB
BUNDLE PATCH 12.1.0.2.160419(64bit):22809813):
|
5)
As you see, my PDB needs post patch installation actions to be execute. So I executed datapatch as follows.
C:\app\salmqure\product\121~1.0\dbhome_1\OPatch>datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Fri Jun 3 15:53:06 2016
Copyright (c) 2015, Oracle. All rights reserved.
Log file for this invocation: C:\app\salmqure\product\121~1.0\dbhome_1\cfgtoollogs\sqlpatch\sqlpatch_6164_2016_06_03_15_53_07\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:
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 PDB1
Adding patches to installation queue and performing prereq checks...
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB1
Nothing to roll back
Nothing to apply
For the following PDBs: PDB2
Nothing to roll back
The following patches will be applied:
22809813 (WINDOWS DB BUNDLE PATCH 12.1.0.2.160419(64bit):22809813)
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...
Patch 22809813 apply (pdb PDB2): SUCCESS
logfile: C:\app\salmqure\product\121~1.0\dbhome_1\cfgtoollogs\sqlpatch\22809813\20122528/22809813_apply_SALMAN12_PDB2_2016Jun03_15_53_49.log (no errors)
SQL Patching tool complete on Fri Jun 3 15:54:11 2016
|
6)
Now I will check if PDB has been patched.
C:\app\salmqure\product\121~1.0\dbhome_1\OPatch>sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jun 3 15:54:40 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ---------- --------------------------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE YES
SQL> alter pluggable database pdb2 close;
Pluggable database altered.
SQL> alter pluggable database pdb2 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ---------- ---------------------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
|
7)
Check if PDB is patched successfully and issue is resolved in PDB_PLUG_IN_VIOLATIONS
SQL> select name, cause, status from pdb_plug_in_violations;
NAME CAUSE STATUS
-------- -------------------- ---------
PDB2 SQL Patch RESOLVED
|
Querying dba_registry_sqlpatch from any PDB will show the patch details, or alternatively cdb_registry_sqlpatch can also be used from CDB to see patch details of all PDBs.
SQL> show pdbs
CON_ID CON_NAME PEN MODE RESTRICTED
---------- ------------------------------ ---------- -----------------------------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> select con_id, patch_id, action, status, action_time from cdb_registry_sqlpatch;
CON_ID PATCH_ID ACTION STATUS ACTION_TIME
---------- ---------- --------------- --------------- ----------------------------------------------------------------
1 22809813 APPLY SUCCESS 03-JUN-16 03.23.47.490000 PM
4 22809813 APPLY SUCCESS 03-JUN-16 03.54.10.392000 PM
3 22809813 APPLY SUCCESS 03-JUN-16 03.23.48.514000 PM
|
Now my PDB (pdb2) has been patched while keeping the downtime to the minimum.
No comments:
Post a Comment