Tales From A Lazy Fat DBA

$ prashantdixit/dbs90@ace as sysdba

  • Likes

    • 110,996
  • Archives

  • Categories

  • Cause I Support!!

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Oracle Radio

  • Magic Of Oracle

  • Disclaimer!

    FatDBA or Oracle 'Ant' is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Posts Tagged ‘Patching’

Days of ‘catbundle PSU apply’are over, Lets welcome ‘DATAPATCH’ in 12c!!

Posted by FatDBA on August 19, 2016

Hi Mates,
With the introduction of Oracle 12c the SQL commands belonging to one patch are not installed by the catbundle.sql but by the “datapatch” tool, located in the OPatch directory. Apart from that the datapatch checks before if the requirement for the installation are met.

Let me be more simple — Datapatch is the new tool that enables automation of post-patch SQL actions for RDBMS patches. So, In 12c you don’t use carbundle psu apply now this is all done using datapatch.

With the Enterprise Manager and OPatchAuto we gets the further automation of database patches by calling datapatch automatically after applying the binary patch.

Enterprise Manager: Starting version 12.1 Enterprise Manager now calls datapatch to complete post patch actions upon any 12c or later database restart.

OPatchAuto : OPatchAuto calls datapatch to complete post patch actions upon installation of the binary patch and restart of the database.

OPatch : Datapatch integration with OPatch is not possible as OPatch is executed when the database is down and datapatch requires the database to be opened to complete its activity.
When patches are installed or rollbacked using OPatch then datapatch needs to be explicitly invoked if instructed to do so in the patch readme.

RAC specific: For a RAC environment, after the binary patch has been applied on all nodes run Datapatch to complete the post-install SQL deployment for the PSU only from one node. Datapatch need not be run on all the nodes.

Below are the steps that you normally perform while applying a patch.

1
2

Restart your Database now when the patch is successfully applied to the binaries.
3

Now apply the patch to the database using DATAPATCH Utility from ORACLE_HOME/OPatch directory.
4

Lest query the database to check the patch apply status.
5

or

To check the PSU applied to your database using the following SQL statement

select * from DBA_REGISTRY_SQLPATCH;

Hope It Helps
Prashant Dixit

Advertisements

Posted in Advanced | Tagged: , | Leave a Comment »

Opatch Failed error code 73: OUI-67073: UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables”

Posted by FatDBA on March 2, 2015

Issue:
Upgrade error from 11.2.0.2 to 11.2.0.4

Error Description:
Oracle SPU / CPU patch deployment using Opatch filed with following error message.
Following executables are active :
/u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1
UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.
Log file location: /u01/app/oracle/product/11.2.0.2/home/cfgtoollogs/opatch/opatch2014-9-14_12-10-00PM.log

OPatch failed with error code 73

Cause:
There are some files which are locked or some processes still running while applying patch . those should be avoided

Full Error in log:

[Mar 1, 2015 4:19:20 PM] Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1 at Fri Nov 22 14:10:20 CET 2014
[Mar 1, 2015 4:19:20 PM] Following executables are active:
/u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1
[Mar 1, 2015 4:19:20 PM] Prerequisite check “CheckActiveFilesAndExecutables” failed.
The details are:
Following executables are active:
/u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1
[Mar 1, 2015 4:19:20 PM] OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.
[Mar 1, 2015 4:19:20 PM] Finishing UtilSession at Fri Nov 22 14:10:20 CET 2014

Solution Description
==================================

This error is simple. Firstly make sure DB and listener are down .

Solution 1:
some processes are still in running .to find out them try
ps -ef|grep db_name
then kill each process by using kill -9 1196 command

Solution 2:
check which process is locking this library file using below command and kill it:

$ /sbin/fuser /u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1
/u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1: 1196m 2215m

$ kill -9 1196

now run opatch apply and it will run without any issues this time.


Hope That Helps
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

Online Patching & Bug Fixing — > : Bug 16342845 : EXCESSIVE CPU IN DBW PROCESSES FOR FAST OBJECT CHECKPOINTSDB

Posted by FatDBA on March 2, 2015

During further investigation we found that all the Database Writers started consuming high resources and waits on ‘DB FILE ASYNC I/O SUBMIT’ event and all rest of the database sessions started waiting on ‘enq: KO – Fast Object Checkpoint’

We have one most critical production system running on oracle 11.2.0.3 on the top of RHEL.
Recently we started experiencing a very bizarre and weird issue where applications timeouts happens intermittently and during that specific period extremely slow IO operations happens at the database level.

While investigation found that during the issue DB started throwing huge and frequent waits on ‘enq: KO – fast object checkpoint’ and ‘db file async I/O submit’ events and all of the queries in the database during the period starts snailing.
At the same time all the 8 available DBWR processes started waiting on “db file async I/O Submit” wait event and seems takes time to write blocks to the disk and this leads to late check-pointing in the system which in turn slow down database.

DB_Waits_Stats-duringSlowness-onetmproduction___mmma

From AWR
—————
foreground wait event
enq: KO – fast object checkpoint — 70% of the DB time.

background wait event
db file async I/O submit — 90% of bg time

Wait Event Histogram
———————-
db file async I/O submit — 100% >1s (% of waits)
enq: CR – block range reuse ckpt — 100% >1s (% of waits)
enq: KO – fast object checkpoint — 100% >1s (% of waits)
enq: RO – fast object reuse — 100% >1s (% of waits)

After weeks of probing and hours of analyzing system state dumps, Hang analyze and writer process traces we reached to a conclusion that we have strike by a Bug 16342845 : EXCESSIVE CPU IN DBW PROCESSES FOR FAST OBJECT CHECKPOINTSDB

Below mentioned are steps performed to apply the recommended patch.
PRIMARY: disable log shipping
STANDBY: stop MRP

1. Disable Log Shipping on Primary Database.
2. Stop MRP process on Physical Standby Database.
3. Apply the patch first in to Physical Standby (online)
4. Monitor the performance on the Phy STDBY db.
5. Will apply the patch on Logical Standby Database.
6. Monitor the logical database performance.
7. Will apply the patch on Primary Database.
8. Monitor the system performance.
9. Start Log shipping on Primary DB end.
10.Start MRP process on Standby database.

Step 1.
Disable log shipping on Primary DB.

SQL> show parameter LOG_ARCHIVE_DEST_

NAME TYPE VALUE
———————————— ——————————– ——————————
log_archive_dest_1 string location=”/vol5/oracle/dixit_db_
arc/dixitdb/arch”, valid_for=(A
LL_LOGFILES,ALL_ROLES)

log_archive_dest_2 string service=”dixitdb_al”, LGWR ASYNC
NOAFFIRM delay=0 optional com
pression=disable max_failure=0
max_connections=1 reopen=300
db_unique_name=”dixitdb_al” net_
timeout=30, valid_for=(all_log
files,primary_role)

log_archive_dest_3 string service=”dixitdb_r”, NOAFFIRM AS
YNC VALID_FOR=(ALL_LOGFILES,PR
IMARY_ROLE) DB_UNIQUE_NAME=one
tm_r

alter system set log_archive_dest_state_2=defer scope=both;

Step 2:
Stop MRP process on Physical Standby Database.

Before
PROCESS STATUS DELAY_MINS
——— ———— ———-
MRP0 WAIT_FOR_LOG 0

SQL> alter database recover managed standby database cancel;

After cancelling recover process the MRP automatically stops.

PROCESS STATUS DELAY_MINS
——— ———— ———-

Step 3:
Opatch online apply – common to all environments.

Recommendations
1. Take binaries backup (ORACLE_HOME).
2. Take Full DB Backup.
3. Check Opatch Prerequisites in order to identify and resolve any patch conflicts.

-bash-3.2$ export PATH=$PATH:$ORACLE_HOME/OPatch

-bash-3.2$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /opt/app/oracle/product/11gR2
Central Inventory : /opt/app/oracle/oraInventory
from : /opt/app/oracle/product/11gR2/oraInst.loc
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.3.0
Log file location : /opt/app/oracle/product/11gR2/cfgtoollogs/opatch/opatch2015-03-01_05-34-42AM_1.log

Invoking prereq “checkconflictagainstohwithdetail”

Prereq “checkConflictAgainstOHWithDetail” passed.

-bash-3.2$ opatch apply online -connectString dixitdb_al:sys:xxxxxxx
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /opt/app/oracle/product/11gR2
Central Inventory : /opt/app/oracle/oraInventory
from : /opt/app/oracle/product/11gR2/oraInst.loc
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.3.0
Log file location : /opt/app/oracle/product/11gR2/cfgtoollogs/opatch/20505138_Mar_01_2015_05_35_29/apply2015-03-01_05-35-29AM_1.log

The patch should be applied/rolled back in ‘-all_nodes’ mode only.
Converting the RAC mode to ‘-all_nodes’ mode.
Applying interim patch ‘20505138’ to OH ‘/opt/app/oracle/product/11gR2’
Verifying environment and performing prerequisite checks…
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
Backing up files…

Patching component oracle.rdbms, 11.2.0.3.0…
Installing and enabling the online patch ‘bug20505138.pch’, on database ‘dixitdb_al’.

Verifying the update…
Patch 20505138 successfully applied
Log file location: /opt/app/oracle/product/11gR2/cfgtoollogs/opatch/20505138_Mar_01_2015_05_35_29/apply2015-03-01_05-35-29AM_1.log

OPatch succeeded.


From Alert log during the patch apply:

Sun Mar 01 06:17:13 2015
Patch file bug20505138.pch is out of sync with oracle binary; performing fixup
Patch file bug20505138.pch has been synced with oracle binary
Patch bug20505138.pch Installed – Update #1
Patch bug20505138.pch Enabled – Update #2
Sun Mar 01 06:17:15 2015
Online patch bug20505138.pch has been installed
Online patch bug20505138.pch has been enabled

-bash-3.2$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /opt/app/oracle/product/11gR2
Central Inventory : /opt/app/oracle/oraInventory
from : /opt/app/oracle/product/11gR2/oraInst.loc
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.3.0
Log file location : /opt/app/oracle/product/11gR2/cfgtoollogs/opatch/opatch2015-03-01_05-35-53AM_1.log

Lsinventory Output file location : /opt/app/oracle/product/11gR2/cfgtoollogs/opatch/lsinv/lsinventory2015-03-01_05-35-53AM.txt

——————————————————————————–
Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.

Interim patches (5) :

Patch (online) 20505138: applied on Sun Mar 01 05:35:42 CET 2015
Unique Patch ID: 18568953
Created on 9 Feb 2015, 19:34:29 hrs PST8PDT
Bugs fixed:
16367081, 16463153, 16342845


‘Hope That Helps’
Thanks
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

OPatch: ApplySession failed: Patch ID is null (OPatch failed with error code 73).

Posted by FatDBA on August 20, 2013

Going to discuss one of the most recent issue that I’ve faced while applying PSU patch for the month of July on one of our Pre-Prod Database. I’ve tried to reproduce the similar situation on one of my test machine.

We were trying to apply it (Patch) on our databse which is running on 11.2.0.1.0.  After sucessfully passed all prechecks of the patch we downloaded from Metalink

Prechecks:
1. Setting Env Variables (ORACLE_SID, ORACLE_HOME, PATH etc.)
2. Shutting down DB and Listener (No RAC Env)
3. Uncompressed zip file to the OPatch directory.

ORACLE_SID = [orcl] ? sairam
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

[oracle@prashant ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 20 22:00:32 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> exit

[oracle@prashant ~]$ cd /u01/app/oracle/product/11.2.0/db_1/OPatch/
[oracle@prashant OPatch]$ cd 12419378/
[oracle@prashant 12419378]$ ls
custom  etc  files  patchmd.xml  README.html  README.txt

[oracle@prashant 12419378]$ opatch apply
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /u01/app/oracle/product/11.2.0/db_1/oui
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2013-08-20_22-02-22PM.log

Patch history file: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession failed: Patch ID is null.
System intact, OPatch will not attempt to restore the system

OPatch failed with error code 73

* After Checking the current OPatch Version it Looks like we will need a higher version of OPatch utility to apply this patch.
[oracle@prashant 12419378]$ opatch version
Invoking OPatch 11.1.0.6.6
OPatch Version: 11.1.0.6.6

OPatch succeeded.

Hence downloaded latest version of Opatch from My Oracle Support (patch 6880880).
1. Downloaded Patch 6880880.
2. Took a backup of $ORACLE_HOME/OPatch into a dedicated backup location.
3. Removed the contents of $ORACLE_HOME/OPatch directory (Please do not
remove $ORACLE_HOME/OPatch directory itself).
4. Unzipped the OPatch downloaded zip into $ORACLE_HOME directory.

[oracle@prashant OPatch]$ ls
p6880880_112000_Linux-x86-64.zip
[oracle@prashant OPatch]$ unzip p6880880_112000_Linux-x86-64.zip
Archive:  p6880880_112000_Linux-x86-64.zip
creating: OPatch/
creating: OPatch/oplan/
……….

……….

[oracle@prashant OPatch]$ ls -ltr
total 32408
-rw-rw—- 1 oracle oinstall       26 Jun 26 11:54 version.txt
-rwxrwxr-x 1 oracle oinstall     2991 Jun 26 11:54 README.txt
-rw-rw-r– 1 oracle oinstall     2576 Jun 26 11:54 opatch.pl
-rw-rw-r– 1 oracle oinstall       49 Jun 26 11:54 opatch.ini
-rwxrwxr-x 1 oracle oinstall     9352 Jun 26 11:54 opatchdiag.bat
-rwxrwxr-x 1 oracle oinstall    10056 Jun 26 11:54 opatchdiag
-rwxrwxr-x 1 oracle oinstall    19867 Jun 26 11:54 opatch.bat
-rwx–x— 1 oracle oinstall    31493 Jun 26 11:54 opatch
-rw-rw-r– 1 oracle oinstall    23695 Jun 26 11:54 emdpatch.pl
drwxrwxr-x 2 oracle oinstall     4096 Jun 26 11:54 docs
drwxrwxr-x 3 oracle oinstall     4096 Jun 26 11:54 oplan
drwxrwxr-x 3 oracle oinstall     4096 Jun 26 11:54 jlib
drwxrwxr-x 3 oracle oinstall     4096 Jun 26 11:54 crs
drwxrwxr-x 4 oracle oinstall     4096 Jun 26 11:54 opatchprereqs
drwxrwxr-x 5 oracle oinstall     4096 Jun 26 11:54 ocm
-rw-rw-r– 1 oracle oinstall     2147 Aug 20 02:22 PatchSearch.xml
-rw-r–r– 1 oracle oinstall 32996451 Aug 20 22:07 p6880880_112000_Linux-x86-64.zip

And finally after deploying last patch, we have the latest OPatch version available.

[oracle@prashant OPatch]$ opatch version
OPatch Version: 11.2.0.3.5

OPatch succeeded.

Now, we can go and deploy our PSU Patch.

[oracle@prashant OPatch]$ cd 12419378/
[oracle@prashant 12419378]$ opatch apply

Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.1.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/12419

Applying interim patch ‘12419378’ to OH ‘/u01/app/oracle/product/11.2.0/db_1’
Verifying environment and performing prerequisite checks…
Patch 12419378: Optional component(s) missing : [ oracle.client, 11.2.0.1.0 ]
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/11.2.0/db_1’)

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…

Patching component oracle.rdbms.rsf, 11.2.0.1.0…

Patching component oracle.rdbms.dbscripts, 11.2.0.1.0…

Patching component oracle.rdbms, 11.2.0.1.0…

Patching component oracle.oraolap, 11.2.0.1.0…

Patching component oracle.rdbms.deconfig, 11.2.0.1.0…

Patching component oracle.javavm.server, 11.2.0.1.0…

Patching component oracle.precomp.common, 11.2.0.1.0…

Patching component oracle.network.rsf, 11.2.0.1.0…

Patching component oracle.network.listener, 11.2.0.1.0…

Patching component oracle.rdbms.dv.oc4j, 11.2.0.1.0…

Patching component oracle.sdo.locator, 11.2.0.1.0…

Patching component oracle.sysman.console.db, 11.2.0.1.0…

Patching component oracle.sysman.oms.core, 10.2.0.4.2…

Patching component oracle.rdbms.dv, 11.2.0.1.0…

Patching component oracle.xdk.rsf, 11.2.0.1.0…

Patching component oracle.ldap.rsf.ic, 11.2.0.1.0…

Patching component oracle.ldap.rsf, 11.2.0.1.0…

Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.1.0…

Verifying the update…
Patch 12419378 successfully applied
Log file location: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/12419378_Aug_20_2013_22_38_06/apply2013-08-20_22-38-06PM_1.log

OPatch succeeded.

Let’s Check the status of patch applied.
[oracle@prashant OPatch]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.1.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2013-08-20_22-56-30PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2013-08-20_22-56-30PM.txt

——————————————————————————–
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.1.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  12419378     : applied on Tue Aug 20 22:41:38 IST 2013
Unique Patch ID:  13710328
Created on 8 Jul 2011, 02:48:54 hrs PST8PDT
Bugs fixed:
9068088, 9363384, 8865718, 8898852, 8801119, 9054253, 8725286, 8974548
9093300, 8909984, 8755082, 8780372, 9952216, 8664189, 8769569, 7519406
9302343, 9471411, 8822531, 7705591, 8650719, 10205230, 9637033, 8883722
8639114, 8723477, 8729793, 8919682, 8856478, 9001453, 8733749, 8565708
8735201, 8684517, 8870559, 8773383, 8981059, 8812705, 9488887, 12534742
8813366, 12534743, 9242411, 12534745, 12534746, 12534747, 8822832
12534748, 8897784, 8760714, 12534749, 8775569, 8671349, 8898589, 9714832
8642202, 9011088, 9369797, 9170608, 9165206, 8834636, 8891037, 8431487
8570322, 8685253, 8872096, 8718952, 8799099, 12534750, 9032717, 9399090
12534751, 12534752, 9713537, 9546223, 12534753, 12534754, 8588519
8783738, 12534755, 12534756, 8834425, 9454385, 8856497, 8890026, 8721315
10248516, 8818175, 8674263, 10249532, 9145541, 8720447, 9272086, 9467635
9010222, 9102860, 9197917, 8991997, 8661168, 8803762, 12419378, 8769239
9654983, 8706590, 8546356, 10408903, 8778277, 9058865, 8815639, 11724991
9971778, 9971779, 9027691, 9454036, 9454037, 9454038, 8761974, 9255542
9275072, 8496830, 8702892, 8818983, 8475069, 8875671, 9328668, 8891929
8798317, 9971780, 8782959, 8774868, 8820324, 8544696, 8702535, 9952260
9406607, 8268775, 9036013, 9363145, 8933870, 8405205, 9467727, 8822365
9676419, 11724930, 8761260, 8790767, 8795418, 8913269, 8717461, 8861700
9531984, 8607693, 8780281, 8330783, 8784929, 8780711, 9341448, 9015983
10323077, 8828328, 9119194, 10323079, 8832205, 8717031, 8665189, 9482399
9676420, 9399991, 8821286, 8633358, 9321701, 9655013, 9231605, 8796511
9167285, 8782971, 8756598, 8703064, 9390484, 9066116, 9007102, 9461782
10323080, 10323081, 10323082, 8753903, 8505803, 9382101, 9352237, 9216806
8918433, 11794163, 9057443, 8790561, 11794164, 8733225, 8795792, 11794165
11794167, 9067282, 8928276, 8837736, 9210925

——————————————————————————–

OPatch succeeded.

 

MOSC Note: 1066937.1

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Advanced | Tagged: , | Leave a Comment »

What and Why CatBundle.sql ?

Posted by FatDBA on March 29, 2013

What is a Catbundle.sql script and it’s purpose after patching (Part of PSU Post-Patching) ?

Before asnwering about the role or importance of catbundle.sql script i would like to explain about what a database bundle is.
– A database bundle series is a sequence of patches where each patch in the series includes the contents of the previous patch in the series.

Installation of the database changes is done by catbundle.sql, which takes input from an XML file named bundledata_<bundle series>.xml.
catbundle.sql determines the last bundle applied to the database and executes only the scripts in the patch that have changed since the last bundle patch was applied.

Opatch tool replaces software files but does not modify database catalog. Applying a patch set update requires modifying the catalog. There is an sql script file named “catbundle.sql” under “$ORACLE_HOME/rdbms/admin” directory. Execute that script as sysdba to make necessary modification to the catalog.

* Patch your ORACLE_HOME before you start and execute the catbundle script.

Once done follow below provided steps if patching (PSU Track).

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

sql>@catbundle.sql psu apply

Once you’ve executed the catbundle.sql means your database is fully patched.

 

PrashAnt Dixit

Posted in Advanced | Tagged: | Leave a Comment »

Patching Explained

Posted by FatDBA on March 16, 2013

Patching
A patch is a piece of software designed to fix problems with, or update a computer program or its supporting data. This includes fixing security vulnerabilities and other bugs, and improving the usability or performance. Oracle Database suffers from these problems as well, hence patching is an important DBA activity that is performed by any administrator on a frequent basis. The method of applying patches on database is known as Patching.

Types of Patches:
Upgrade Patches:     Patches released by Oracle to upgrade databases. Like which takes your database from 10.2.0.1 to 10.2.0.4
CPU Patches:            Critical Patch Update, quarterly delivered to fix security issues.
PSU Patches:            PatchSet Update, also quarterly delivered, it includes CPU and a bunch of other one-off patches. First time introduced on 14th July 2009 by Oracle. Hence PSU = CPU + OneOff
One-Off Patches:     One-off patch is single fix patch, to fix a particular issue.

* Both CPU & PSU are released on a predictable time or schedule. specifically the Tuesday closest to the 15th of January, April, July,      and October.
* Although you can opt between PSU & CPU but it is always recommended that you choose CPU track.
* Once a PSU has been installed, the recommended way to get future security content is to apply subsequent PSUs. Reverting from PSU back to CPU, while possible, would require significant effort, and so is not advised.
Opatch is the built-in tool provided by Oracle. Resides in ORACLE_HOME/Opatch directory is the most trusted tool to apply almost all types of patches.

There are various Flags or options available in Opatch to apply patches in different scenarios.

-all_nodes: If want to apply/rollback/lsinventory (Check) on different nodes e.g in a RAC environment, use all_node option.
-force: If a conflict exist which prevents the patch from being applied, the -force flag can be used to apply the patch. OPatch will remove all the conflicting patches before applying the current patch.
-idfile: The input file which contains list of all patches seperated by commas or white spaces.

-invPtrLoc: used to locate orainst.loc file. In inventory oracle keep the detail of installed products in all oracle homes on the server, orainst.loc provides that list of installed products.
e.g  $ opatch lsinventory -InvPtrLoc /oracle/TEST/product/9.2.0/oraInst.loc

Contents of oraInst.loc file:
inventory_loc=/u01/app/oraInventory
inst_group=oinstall

-jre: This option tells Opatch to use JRE (java) from specified location instead of the default location under Oracle Home.
-local: Patch the local node, then update the inventory of the local node. Do not pass the patch or inventory update to other nodes.
-local_node:  This option can be used to specify to OPatch the local node name to be used for RAC mode application of the patch.
-no_inventory: Bypass the inventory for reading and updates.

– no_relink: option does not perform any ‘apply’ operations. This option can be used during multiple patch. This performs the linking step only once.
-no_sysmod: Option specifies not to update the files in the system. It just updates the inventory. It also not execute the pre and post scripts.

-phBaseFile: IF <patch_location> is not specified, use this option to point Optach to a file containing a list of patches to be n-applied.
-ocmrf: Give Opatch the absolute path to the OCM response file to be used for OCM configuration. OCM can collect the configuration of your servers and send it to Oracle support.  Oracle support staff will use it when you open a service request. With OCM, you’ll get better and faster response from Oracle support. You can also view your configuration on metalink. Oracle can suggest you updates or patches according to your configuration.Although it is useful, using OCM is not mandatory. You can still get support from Oracle without OCM. However, during patching, opatch utility may ask for an OCM response file.

To create OCMRF File — Opatch utility ships with a script file named “emocmrsp” which is used for creating a response file. “emocmrsp” script will ask your metalink account information and your internet connection information and store it in a response file named “ocm.rsp”.

-post: This option is used to pass parameters to the post script. This script is executed after application of the patch. The value for this option have to be enclosed in double quotes. The parameters will be common parameters which will be passed to post scripts of all patches being applied.
This option should be ended by option ‘opatch_post_end’.

-pre: This option is used to pass parameters to the pre script. This script is executed before application of the patch. The value for this option have to be enclosed in double quotes. The parameters will be common parameters which will be passed to pre scripts of all patches being applied. This option should be ended by option ‘opatch_pre_end’.

-report: Prints out the actions without executing.
-retry: Tells OPatch how many times it should retry in case of an inventory lock failure.
-runsql: This options commands OPatch to run sql scripts and procedures if they are present in the given patch.
-silent: This supresses any user inteeractions.
-sqlScript: Option used to specify the custom sql script to be run by OPatch after patching is completed.

-verbose: This option prints more OPatch output to the screen as well as to the log file.
-oh: Oracle Home  e.g. ./opatch lsinventory -oh /u01/app/grid/11.2.0.2/
-och: Oracle Clusterware Home (Path to CRS Home)

Command Description Syntax (Examples)
apply Installs an interim patch. The apply command applies an interim patch to a specified Oracle home. The ORACLE_HOME environment variable must be set to the Oracle home to be patched. opatch apply [-force] [-invPtrLoc (path)] [-oh] [-patch_location]
napply Installs n number of patches (hence napply). This command applies interim patches to several Oracle homes at the same time. opatch napply <patch_location> -id 1,2,3
auto Applies Oracle Clusterware patches. (For RAC systems)     —
lsinventory Lists what is currently installed on the system. opatch lsinventory -detail:                                                            opatch lsinventory -bugs_fixed asc:
query The query command queries a specific patch for specific details. It provides information about the patch and the system being patched. opatch query [-all] [patch_location] …
rollback The rollback command removes a specific interim patch from the appropriate Oracle home directory. opatch rollback -id (patch_id) [-ph (patch directory)]
version Prints the current version of the patch tool. opatch version

Posted in Advanced | Tagged: | Leave a Comment »

‘ORA-12547: TNS:lost contact’ after OPATCH_PLATFORM_ID Reset in sqlplus.

Posted by FatDBA on July 5, 2012

Resolution:

1. De-install the deployed patch first  —

[oracle@prashant 8974084]$ opatch rollback -id 8974084   (Click Y/Yes when it asked you to select out of Yes/No).

Once the patch uninstalled unset the OPATCH_PLATFORM_ID

example:     [oracle@prashant 8974084]$ unset OPATCH_PLATFORM_ID

2. Stop all middle wares included e.g. ENTERPRISE MANAGER, LISTENER and shutdown the database.

3. Revert back the PLATFORM_ID back to it’s old value —

[oracle@prashant 8974084]$ export OPATCH_PLATFORM_ID=46   (46 was the old value of the machine)

4. Recheck all Network files located in $ORACLE_HOME/dbs     like listener.ora, tnsnames.ora and tnsnames.ora they might corrupted as well. Make sure you have the correct Host Name, Post Number, Service number defined in  tnsnames.ora. Check sqlnet,ora and verify if it has the EZCONNECT , TNSNAMES.

5. reboot your machine.

6.  Start listener and to give the database a try to start. Most probably  chances are there that you’ll receive LOCAL_LISTENER definition failure message during the start.

In that case you have to check your parameter file and perform requisite changes to the local listener definitions (*.LOCAL_LISTENER=’ ‘)

7. Re try to start the database and you’ll be able to start DB this time.

Feedbacks: If failed to start the DB box even after performing these steps and that will also motivate me to write about and research  🙂

Posted in Advanced | Tagged: , , | Leave a Comment »

OraDose… (05-07-2012)

Posted by FatDBA on July 5, 2012

If getting error regarding Platform ID during prerequisite checks when applying any patch on database.

Below is the error code:

[oracle@prashant 8974084]$ opatch apply
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /u01/app/oracle/product/11.2.0/db_1/oui
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2012-07-05_12-07-37PM.log

Patch history file: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch ‘8974084’ to OH ‘/u01/app/oracle/product/11.2.0/db_1’

Running prerequisite checks…
Prerequisite check “CheckPatchApplicableOnCurrentPlatform” failed.
The details are:
Patch ( 8974084 ) is not applicable on current platform.
Platform ID needed is : 46
Platform IDs supported by patch are: 226
ApplySession failed during prerequisite checks: Prerequisite check “CheckPatchApplicableOnCurrentPlatform” failed.
System intact, OPatch will not attempt to restore the system

OPatch failed with error code 74

Resolution:

Reset Platform ID using OPATCH_PLATFORM_ID to desired ID.

(Note: Changing Platform ID sometimes affects machines networks and you might loose connections – TNS Error – No Connection

Please perform said steps on your own risk)

example: [oracle@prashant 8974084]$ export OPATCH_PLATFORM_ID=226

Let’s re-run the patch after we are done with the ID resets —

[oracle@prashant 8974084]$ opatch apply
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /u01/app/oracle/product/11.2.0/db_1/oui
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2012-07-05_12-19-49PM.log

Patch history file: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch ‘8974084’ to OH ‘/u01/app/oracle/product/11.2.0/db_1’

Running prerequisite checks…

Using user-defined value, OPatch will treat this system as platform ID ‘226’.

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/11.2.0/db_1’)

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch ‘8974084’ for restore. This might take a while…
Backing up files affected by the patch ‘8974084’ for rollback. This might take a while…

Patching component oracle.rdbms, 11.2.0.1.0…
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcb.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbb.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbo.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbs.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbz.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcl.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbi.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kcbr.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kjb.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kjbl.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kjbm.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kjbr.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kjfc.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kmgs.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/kmgsb.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libserver11.a”  with “lib/libserver11.a/ksu.o”
Updating archive file “/u01/app/oracle/product/11.2.0/db_1/lib/libgeneric11.a”  with “lib/libgeneric11.a/skgvm.o”
Running make for target ioracle
ApplySession adding interim patch ‘8974084’ to inventory

Verifying the update…
Inventory check OK: Patch ID 8974084 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8974084 are present in Oracle Home.

The local system has been patched and can be restarted.

OPatch succeeded.

—— WE ARE DONE ——

Posted in Advanced | Tagged: , , | Leave a Comment »

 
%d bloggers like this: