Monday, February 26, 2018

Oracle Patching in Multitenant Environment with Minimal Downtime

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 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).

In this scenario, I have a CDB with 1 PDB (pdb2) running on windows platform. To apply Bundle Patch 22809813, I installed a new oracle home 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.

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.

 I plugged in my pdb in CDB running from new patch oracle home.

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

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 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:

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

Now I will check if PDB has been patched.


SQL*Plus: Release 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 - 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

---------- ------------------------------ ---------- ----------  ---------------------
         2 PDB$SEED                       READ ONLY        NO
         3 PDB1                                 READ WRITE       NO
         4 PDB2                                 READ WRITE       NO

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;

---------- ---------- --------------- --------------- ----------------------------------------------------------------
         1   22809813 APPLY           SUCCESS         03-JUN-16 PM
         4   22809813 APPLY           SUCCESS         03-JUN-16 PM
         3   22809813 APPLY           SUCCESS         03-JUN-16 PM

Now my PDB (pdb2) has been patched while keeping the downtime to the minimum.

Upgrading pluggable database to a new version/release

In above, I explained the patching in a multitenant environment. To upgrade form one release to the other, for example upgrading to, we can unplug a pluggable database from old oracle home and plug into the new release oracle home, but we would need to follow steps mentioned in database upgrade guide.

No comments:

Post a Comment

Popular Posts - All Times