Oracle DBA – A lifelong learning experience

Manage a PSU into a CDB having several PDBS

Posted by John Hallas on October 12, 2015

I thought it would be a good idea to show how to apply PSU into the CDB and PDB that came with 12c. I start off with a quick reminder about how 11g worked and then move into examples of 12c

11G reminder

get the latest version of opatch

check for conflicts

opatch prereq  CheckConflictAgainstOHWithDetail -ph ./

start the downtime

Stop all the databases in the home (one node at a atime for RAC)

apply the patch

opatch apply

start all databases in the home

load SQL into the databases

@catbundle.sql psu apply

end of downtime (but remember to do the standby)

Example of 12c PSU process

Update opatch to latest version

Download and apply patch 6880880 to the oracle home

Check for conflicts with one-offs

Run the prereq check for conflicts:

[CDB2] oracle@localhost:~/12cPSU/19303936

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.1.0.1.8
Copyright (c) 2015, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home       : /u01/app/oracle/product/12.1.0.2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
OPatch version   : 12.1.0.1.8
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2015-09-22_12-12-54PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

No conflicts should be reported.

Stop all dbs in the home (one node at a time for RAC)

If you are using a Data Guard Physical Standby database, you must install this patch on both the primary database and the physical standby database, as described by My Oracle Support Document 278641.1.

If this is a RAC environment, install the PSU patch using the OPatch rolling (no downtime) installation method as the PSU patch is rolling RAC installable. Refer to My Oracle Support Document 244241.1 Rolling Patch – OPatch Support for RAC.

If this is not a RAC environment, shut down all instances and listeners associated with the Oracle home that you are updating.

Apply the patch

[CDB2] oracle@localhost:~/12cPSU/19303936

$ opatch apply

Normal output here -nothing has changed

Start all dbs in the home

Start the CDB:

[setsid] to CDB
startup

If this is multitenant then start and PDBs that are not open:

select name, open_mode from v$pdbs order by 1;
NAME                                    OPEN_MODE
---------------------------------------- ------------------------------
PDB$SEED                                 READ ONLY
PDB1                                     MOUNTED
PDB2                                     MOUNTED
alter pluggable database [all|<<name>>] open;

Start the listener(s) if it was stopped too.

Load SQL into all dbs

Prior to 12c you needed to connect to all databases individually and run catbundle.sql psu apply. Now in 12c, you only need to run datapatch –verbose. This will connect to the CDB$ROOT, PDB$SEED and all **open** PDBs and run the SQL updates:

cd $ORACLE_HOME/OPatch
[CDB2] oracle@localhost:/u01/app/oracle/product/12.1.0.2/OPatch
$ ./datapatch -verbose

 

SQL Patching tool version 12.2.0.0.0 on Tue Sep 22 12:49:21 2015
Copyright (c) 2014, Oracle. All rights reserved.

 

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)
catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_12889.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
Bootstrapping registry and package to current versions...done
Determining current state...done

 

Current state of SQL patches:
Bundle series PSU:
ID 1 in the binary registry and not installed in any PDB

 

Adding patches to installation queue and performing prereq checks...
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB1
   Nothing to roll back
   The following patches will be applied:
     19303936 (Database Patch Set Update : 12.1.0.2.1 (19303936))

 

Installing patches...
Patch installation complete. Total patches installed: 3

 

Validating logfiles...
Patch 19303936 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/19303936/18116864/19303936_apply_CDB2_CDBROOT_2015Sep22_12_50_10.log (no errors)
Patch 19303936 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/19303936/18116864/19303936_apply_CDB2_PDBSEED_2015Sep22_12_50_14.log (no errors)
Patch 19303936 apply (pdb PDB1): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/19303936/18116864/19303936_apply_CDB2_PDB1_2015Sep22_12_50_15.log (no errors)
SQL Patching tool complete on Tue Sep 22 12:50:19 2015

Note that PDB2 was not picked up that is because I left it as MOUNTED and not open.

So what happens now if I try and mount it?

[CDB2] oracle@localhost:/oradata/diag/rdbms/cdb2/CDB2/trace
$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 22 13:13:30 2015

 

Copyright (c) 1982, 2014, Oracle. All rights reserved.

 

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 PDB2                           MOUNTED
         4 PDB1                           READ WRITE NO

 

  SQL> select time,name,cause,status,message from PDB_PLUG_IN_VIOLATIONS order by name;

  no rows selected

SQL> alter pluggable database pdb2 open;

 

Warning: PDB altered with errors.

 

SQL> show pdbs

 

   CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 PDB2                           READ WRITE YES
         4 PDB1                           READ WRITE NO
SQL> col time for a20
SQL> col name for a20
SQL> col cause for a20 wrap
SQL> col status for a20
SQL> col message for a60 wrap
SQL> set lines 200
SQL> select time,name,cause,status,message from PDB_PLUG_IN_VIOLATIONS where cause='SQL Patch' order by name;

 

TIME                 NAME                 CAUSE               STATUS               MESSAGE
-------------------- -------------------- -------------------- -------------------- ------------------------------------------------------------
22-SEP-15 01.14.51.4 PDB2                 SQL Patch           PENDING             PSU bundle patch 1: Installed in Database Patch Set Update :
18558 PM                                                                             12.1.0.2.1 (19303936) but not in the CDB. 

So it tells us that PDB2 does not have the PSU installed.

The fix here is to rerun datapatch now that PDB2 is open:

cd /u01/app/oracle/product/12.1.0.2/OPatch
[CDB2] oracle@localhost:/u01/app/oracle/product/12.1.0.2/OPatch
$ ./datapatch -verbose
SQL Patching tool version 12.2.0.0.0 on Tue Sep 22 13:19:15 2015
Copyright (c) 2014, Oracle. All rights reserved.

 

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)
catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_14499.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
Bootstrapping registry and package to current versions...done
Determining current state...done

 

Current state of SQL patches:
Bundle series PSU:
ID 1 in the binary registry and ID 1 in PDB CDB$ROOT, ID 1 in PDB PDB$SEED, ID 1 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:
     19303936 (Database Patch Set Update : 12.1.0.2.1 (19303936))

 

Installing patches...
Patch installation complete. Total patches installed: 1

 

Validating logfiles...
Patch 19303936 apply (pdb PDB2): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/19303936/18116864/19303936_apply_CDB2_PDB2_2015Sep22_13_19_47.log (no errors)
SQL Patching tool complete on Tue Sep 22 13:19:49 2015

This time it only patched PDB2 and skipped over the others.

Now what does the database think?

[CDB2] oracle@localhost:/u01/app/oracle/product/12.1.0.2/OPatch
$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 22 13:21:11 2015

 

Copyright (c) 1982, 2014, Oracle. All rights reserved.

 


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 PDB2                           READ WRITE YES
         4 PDB1                           READ WRITE NO
SQL> col time for a20
SQL> col name for a20
SQL> col cause for a20 wrap
SQL> col status for a20
SQL> col message for a60 wrap
SQL> set lines 200
SQL> select time,name,cause,status,message from PDB_PLUG_IN_VIOLATIONS order by name;

 

TIME                 NAME                 CAUSE               STATUS               MESSAGE
-------------------- -------------------- -------------------- -------------------- ------------------------------------------------------------
22-SEP-15 01.14.51.4 PDB2                 SQL Patch           PENDING             PSU bundle patch 1: Installed in Database Patch Set Update :
18558 PM                                                                             12.1.0.2.1 (19303936) but not in the CDB.

Nothing, so I need to bounce PDB2:

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 PDB2                           READ WRITE NO
         4 PDB1                           READ WRITE NO

 

SQL> select time,name,cause,status,message from PDB_PLUG_IN_VIOLATIONS order by name;

 

TIME                 NAME                 CAUSE                STATUS               MESSAGE
-------------------- -------------------- -------------------- -------------------- ------------------------------------------------------------
22-SEP-15 01.23.24.0 PDB2                 SQL Patch           RESOLVED            PSU bundle patch 1: Installed in Database Patch Set Update :
47678 PM                                                                             12.1.0.2.1 (19303936) but not in the CDB.

The row is still returned but the STATUS is now RESOLVED.

Moving PDB between PSU versions

In this example we will move PDB1 from CDB1 to CDB2 which is at a higher PSU.

Create new home

Create separate ORACLE_HOME and CDB2 and apply PSU at version higher than existing.

Run datapatch on new home to apply PSU to CDB$ROOT and PDB$SEED.

 

Stop application and existing PDB

Downtime will start now.

Stop the PDB1:

setsid [CDB1]
sysdba
ALTER PLUGGABLE DATABASE PDB1 CLOSE;
Pluggable database altered.

Unplug PDB1 from CDB1

Unplug the PDB into metadata xml file:

ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.
SQL> ALTER PLUGGABLE DATABASE PDB1 UNPLUG INTO '/u01/oradata/CDB1/PDB1/PDB1.xml';
Pluggable database altered

Plug PDB1 into CDB2.

Plug metadata into CDB2:

setsid [CDB2]
sysdba
SQL> CREATE PLUGGABLE DATABASE PDB1
     USING '/u01/oradata/CDB1/PDB1/PDB1.xml'
     MOVE FILE_NAME_CONVERT = ('/u01/oradata/CDB1/PDB1','/u01/oradata/CDB2/PDB1');
Pluggable database created.

The use of the MOVE clause makes the new pluggable database creation very quick, since the database files are not copied but only moved on the file system. This operation is immediate if using the same file system.

Now open up PDB1 on CDB2:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;
Pluggable database altered

Load modified SQL files into the database with Datapatch tool

Run datapatch to load SQL into PDB1

setsid [CDB2]
cd $ORACLE_HOME/OPatch
./datapatch -verbose

Start application

Downtime ends and the application can be restarted

Inventory Reporting

New at 12c

Simon Pane at Pythian has produced a very useful script which we converted into a shell script http://www.pythian.com/blog/oracle-database-12c-patching-dbms_qopatch-opatch_xml_inv-and-datapatch/

You can now query what is applied to both the home and the database by querying just the database at 12c.


 

List of PSUs applied to both the $OH and the DB

 


NAME             PATCH_ID PATCH_UID ROLLBACK STATUS         DESCRIPTION
--------------- ---------- ---------- -------- --------------- ------------------------------------------------------------
JH1PDB           19769480   18350083 true    SUCCESS         Database Patch Set Update : 12.1.0.2.2 (19769480)
JHPDB             19769480   18350083 true     SUCCESS         Database Patch Set Update : 12.1.0.2.2 (19769480)
CDB$ROOT         19769480   18350083 true     SUCCESS         Database Patch Set Update : 12.1.0.2.2 (19769480)

 


PSUs installed into the $OH but not applied to the DB

 


NAME             PATCH_ID PATCH_UID DESCRIPTION
--------------- ---------- ---------- ------------------------------------------------------------
JH2PDB            19769480   18350083 Database Patch Set Update : 12.1.0.2.2 (19769480)

 


PSUs applied to the DB but not installed into the $OH

 


no rows selected

Note – PDB$SEED is normally READ ONLY and hence no record is returned from the CDB_REGISTRY_SQLPATCH view, so this PDB is excluded from this SQL.

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: