“Dumping current patch information” in Alert Log 12c can lead to a misinterpretation

Oracle Database 12.1 shows information on applied patches in the alert log during instance startup.

A question come up: The msg info in alert.log (below) is about patch applied on OH only or this information is related to patch applied on Database?

alert.log
...
2016-09-01 18:07:52.476000 -03:00
===========================================================
Dumping current patch information
===========================================================
Patch Id: 23144544
Patch Description: DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544)
Patch Apply Time: 2016-08-31 17:44:09 GMT-03:00
...

To get the answer I installed a OH 12.1 without create a database and apply the DATABASE BUNDLE PATCH: 12.1.0.2.160719.

I created a CDB database  with DBCA using template General purpose or transaction processing.

During a database creation  was looking at alert log and I noticed two things.

First the alert log show:

===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================

Then at end of database creation  alert log show:

...
2016-09-01 18:07:52.476000 -03:00
===========================================================
Dumping current patch information
===========================================================
Patch Id: 23144544
Patch Description: DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544)
Patch Apply Time: 2016-08-31 17:44:09 GMT-03:00
...

The patch has been applied on my database during database creation?

The anwser is NO.
To my surprise, the “Dumping current patch information” is about Patches applied on OH only.

Why  “No Patches” the after some time “Show Patches”? 

The patch information is populated by calling DBMS_QOPATCH. It is populated if the database was started in a open mode, as it’s not possible to execute any package with database not opened in read-write.
At first moment database was started in a non-open mode , then NO PATCHES, when database was started in OPEN mode then alertlog was pouplated with the info.

Why this can lead a misinterpretation? 

DBMS_QOPATCH was introduced in 12c to query about Patch information  applied on database, but the info on alert.log is not about patch applied on database, the info is about patch applied on OH only, it can lead us to think wich database have listed patch on alertlog applied.

(I think this is some bug or something else, because it makes no sense to me)

What is the safe mode to check if database have a patch applied?

 

conn as sysdba

SQL> set serverout on
SQL> exec dbms_qopatch.get_sqlpatch_status;

PL/SQL procedure successfully completed.

No outuput then No Patch applied.

After create database I checked if patch was applied and none patch was applied, but the “Dumping current patch information”  show all applied patch in the alertlog at startup of instance.

 

How to apply a BP or PSU on Database 12c?

By executing datapatch utility.

./datapatch -h
SQL Patching tool version 12.1.0.2.0 on Thu Sep 1 19:35:49 2016
Copyright (c) 2016, Oracle. All rights reserved.

sqlpatch usage:
All arguments are optional, if there are no arguments sqlpatch
will automatically determine which SQL scripts need to be run in
order to complete the installation of any SQL patches.

Optional arguments:
-db <db name>
 Use the specified database rather than $ORACLE_SID
-bundle_series <bundle_series>
 Specify if the patch is a bundle patch
 Should also be accompanied by -force option
 if -bundle_series option is specified,only 1 patch will
 be considered by the -force command
-apply <patch1,patch2,...,patchn>
 Only consider the specified patch list for apply operations
-rollback <patch1,patch2,...,patchn>
 Only consider the specified patch list for rollback operations
-upgrade_mode_only
 Only consider patches that require upgrade mode
-force
 Run the apply and/or rollback scripts even if not necessary
 per the SQL registry
-pdbs <pdb1,pdb2,...,pdbn>
 Only consider the specified list of PDBs for patching. All
 other PDBs will not be patched
-prereq
 Run prerequisite checks only, do not actually run any scripts
-oh <oracle_home value>
 Use the specified directory to check for installed patches
-verbose
 Output additional information used for debugging
-help
 Output usage information and exit
-version
 Output build information and exit

Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)

Let’s  apply the patch.

cd $ORACLE_HOME/OPatch
./datapatch -verbose

Suprise: When execute ./datapatch -verbose it has no warning or confirmation, the datapatch automatically  will apply patch in  all CDBs and in all opened PDBs of current OH environment.

The same concern that we have about “dd” command, we must have about datapatch command. Hope Oracle fix it.

Output:

./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Thu Sep 1 18:28:47 2016
Copyright (c) 2016, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/
sqlpatch_23003606_2016_09_01_18_28_47/sqlpath_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 DBBP:
 ID 160719 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
 Nothing to roll back
 The following patches will be applied:
 23144544 (DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544))

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

Validating logfiles...
Patch 23144544 apply (pdb CDB$ROOT): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/23144544/20355564/23144544_apply_
PRDCDB_CDBROOT_2016Sep01_18_29_26.log (no errors)
Patch 23144544 apply (pdb PDB$SEED): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/23144544/20355564/23144544_apply_
PRDCDB_PDBSEED_2016Sep01_18_32_36.log (no errors)
SQL Patching tool complete on Thu Sep 1 18:34:54 2016

datapatch always validate if a BP or PSU already was applied  as show below:

./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Thu Sep 1 18:35:21 2016
Copyright (c) 2016, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/
sqlpatch_23593462_2016_09_01_18_35_21/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 DBBP:
 ID 160719 in the binary registry and ID 160719 in PDB CDB$ROOT, 
 ID 160719 in PDB PDB$SEED

Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED
 Nothing to roll back
 Nothing to apply

SQL Patching tool complete on Thu Sep 1 18:35:53 2016

 

Checking what patches have been applied (or rolled back) on database

SQL> set serverout on
SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 23144544
 Action : APPLY
 Action Time : 01-SEP-2016 18:34:46
 Description : DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544)
 Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/23144544/20355564/23144544_apply_PRDCDB_CDB
ROOT_2016Sep01_18_29_26.log
 Status : SUCCESS

PL/SQL procedure successfully completed.

 

Some notes:

  • Patch Information in Alert Log is about OH only
  • Creating a Database a CDB or Non-CDB database using a default Template it does not apply any Patch, you must apply patch manually after database creation.
  • datapatch utility is dangerous because has no warning or confirmation when executed.

 

Advertisements


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