ORA-04021: timeout occurred while waiting to lock object error while executing the catbundle.sql script
During the patching process, we need to apply the patch sqls to the database. To apply the patches to the database, we execute
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@catbundle.sql apply PSU
the above script hangs due to locked object and the error is written to the log ORA-04021: timeout occurred while waiting to lock object
This was due to the locked object dbms_advisor (in my case).
Solution:
Restarting the database cleared the locks and the catbundle apply completed in few minutes.
Wednesday, 12 November 2014
Thursday, 6 November 2014
Things to consider before upgrading to 11.2.0.4 from 11.2.0.3 - Part 1
Things to consider before upgrading to 11.2.0.4 from 11.2.0.3
Oracle recommends out-of place upgrade from 11.2.0.3 to 11.2.0.4
At the planning stage of the database and grid Infrastructure upgrade, Download and run the ORAchk utility from Oracle to verify and fix any issues before upgrading the clusterware, ASM and the database.
ORAchk tool is the advanced version of RACcheck tool and Oracle recommends to download the ORAchk tool to verify All the Oracle stack.
To download and more details about the script is in Oracle support
$ ./orachk -u -o pre
Enter upgrade target version (valid versions are 11.2.0.3.0, 11.2.0.4.0, 12.1.0.1.0, 12.1.0.2.0):- 11.2.0.4.0
CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /u01/app/grid?[y/n][y]y
Checking ssh user equivalency settings on all nodes in cluster
Node dev02 is configured for ssh user equivalency for oracle user
Searching for running databases . . . . .
. .
List of running databases registered in OCR
1. rac
2. None of above
Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1].1
. .
Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
-------------------------------------------------------------------------------------------------------
Oracle Stack Status
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
dev01 Yes Yes Yes Yes Yes rac01
dev02 Yes Yes Yes Yes Yes rac02
-------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
Installed components summary
---------------------------------------------------------------------------------------------------------------------------------
GI_HOME ORACLE_HOME Database Names
---------------------------------------------------------------------------------------------------------------------------------
/u01/app/grid - 11.2.0.3.0 /u01/app/oracle/product/11.2.0/db - 11.2.0.3.0 rac
---------------------------------------------------------------------------------------------------------------------------------
Copying plug-ins
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . .
Checking Grid Infrastructure and RDBMS upgrade readiness to version 112040
Collections and audit checks log file is
/oracle/software/11204_DB/orachk_dev01_rac_110514_175251/log/orachk.log
Checking for prompts in /oracle/.bash_profile on dev01 for oracle user...
Checking for prompts in /oracle/.bash_profile on dev02 for oracle user...
Starting to run orachk in background on dev02
=============================================================
Node name - dev01
=============================================================
Collecting - ASM Diskgroup Attributes
Collecting - ASM initialization parameters
Collecting - Database Parameters for rac database
Collecting - Database Undocumented Parameters for rac database
Collecting - Pre-upgrade File Locations for rac database
Collecting - /proc/cmdline
Collecting - /proc/modules
Collecting - CPU Information
Collecting - DiskFree Information
Collecting - DiskMount Information
Collecting - Kernel parameters
Collecting - Maximum number of semaphore sets on system
Collecting - Maximum number of semaphores on system
Collecting - Maximum number of semaphores per semaphore set
Collecting - Memory Information
Collecting - NUMA Configuration
Collecting - Network Interface Configuration
Collecting - Network Performance
Collecting - Network Service Switch
Collecting - OS Packages
Collecting - OS version
Collecting - Patches for Grid Infrastructure
Collecting - Patches for RDBMS Home
Collecting - number of semaphore operations per semop system call
Data collections completed. Checking best practices on dev01.
--------------------------------------------------------------------------------------
INFO => Real World Network Bandwidth Guidelines
INFO => Important Automatic Storage Management (ASM) Notes and Technical White Papers
INFO => Unattended Installation and Configuration Options
INFO => Be aware of provided optional integrity checks
INFO => Patching Notes by Release
INFO => Prior to Upgrade Verify NO Materialized Views Being Refreshed (Automatically or Manually) for rac
WARNING => ORACLE_HOME environment variable is set
FAIL => Opatch version is lower than recommended in RDBMS_HOME for /u01/app/oracle/product/11.2.0/db
FAIL => Opatch version is lower than recommended in GRID_HOME
INFO => Special Considerations for SCAN and HAIP of 11.2.0.2 and above Grid Infrastructure
INFO => Minimizing Planned Downtime for Upgrades and Patching
INFO => Upgrade Related References
INFO => Lifetime Support Policy and Product Certification INFO
INFO => Capture performance baseline, backup important configuration files and batch, cron, DBMS_JOBS and DBMS_SCHEDULER jobs
INFO => Known Issues Integrating pre-11gR2 Databases with Grid Infrastructure
INFO => Be Aware of New Instant Client for Simplified Deployment
INFO => Oracle E-Business Suite interoperability and migration resources
INFO => Oracle Software Download Sites
WARNING => One or More Object Names in ALL_OBJECTS table are Reserved Words for rac
WARNING => One or More Column Names in ALL_TAB_COLUMNS table are Reserved Words for rac
WARNING => Some Users Needing Network ACLs for Oracle Utility Packages Found for rac
INFO => Steps to execute rootupgrade.sh
INFO => Save "Oracle Enterprise Manager Database Control" files and data with the emdwgrd Utility before upgrading database.
INFO => Information about ASM process parameter when its not set to default value
INFO => Information about Grid Infrastructure software installation directory
WARNING => OS parameter vm.swappiness is NOT set to the recommended value
Copying results from dev02 and generating report. This might take a while. Be patient.
=============================================================
Node name - dev02
=============================================================
Collecting - /proc/cmdline
Collecting - /proc/modules
Collecting - CPU Information
Collecting - DiskFree Information
Collecting - DiskMount Information
Collecting - Kernel parameters
Collecting - Maximum number of semaphore sets on system
Collecting - Maximum number of semaphores on system
Collecting - Maximum number of semaphores per semaphore set
Collecting - Memory Information
Collecting - NUMA Configuration
Collecting - Network Interface Configuration
Collecting - Network Performance
Collecting - Network Service Switch
Collecting - OS Packages
Collecting - OS version
Collecting - Patches for Grid Infrastructure
Collecting - Patches for RDBMS Home
Collecting - number of semaphore operations per semop system call
Data collections completed. Checking best practices on dev02.
--------------------------------------------------------------------------------------
WARNING => ORACLE_HOME environment variable is set
FAIL => Opatch version is lower than recommended in RDBMS_HOME for /u01/app/oracle/product/11.2.0/db
FAIL => Opatch version is lower than recommended in GRID_HOME
INFO => Information about ASM process parameter when its not set to default value
---------------------------------------------------------------------------------
CLUSTERWIDE CHECKS
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
Detailed report (html) - /oracle/software/orachk_dev01_rac_101014_175251/orachk_dev01_rac_101014_175251.html
UPLOAD(if required) - /oracle/software/orachk_dev01_rac_101014_175251.zip
$
ORAchk generated a detailed report in html which provides the fixes for the failure and warning.
The sample HTML report is as below for the opatch version fix
Oracle RAC Upgrade Readiness Report
CRS Opatch version
Recommendation
Most recent opatch version is 11.2.0.3.6 (or higher). Download latest opatch from following MOS note and install.
Note :- report data may be empty if its role separated environment where oracle does not have permission to call opatch in GRID_HOME. Please run $CRS_HOME/OPatch/opatch version manually and verify that version is equal or higher than 11.2.03.6
Links
Note: 1373255.1 - 11.2.0.1/11.2.0.2 to 11.2.0.3 Database Upgrade on Exadata Database Machine
Note: 274526.1 - How To Download And Install The Latest OPatch Version
Needs attention on dev03, dev04
Passed on -
Status on dev03:
FAIL => Opatch version is lower than recommended in GRID_HOME
DATA FROM dev03 - CRS OPATCH VERSION
OPatch Version: 11.2.0.3.4
OPatch succeeded.
Status on dev04:
FAIL => Opatch version is lower than recommended in GRID_HOME
DATA FROM dev04 - CRS OPATCH VERSION
OPatch Version: 11.2.0.3.4
OPatch succeeded.
Great ORAchk tool from Oracle.
Part 2 coming soon...
Oracle recommends out-of place upgrade from 11.2.0.3 to 11.2.0.4
At the planning stage of the database and grid Infrastructure upgrade, Download and run the ORAchk utility from Oracle to verify and fix any issues before upgrading the clusterware, ASM and the database.
ORAchk Health Checks For The Oracle Stack
ORAchk tool is the advanced version of RACcheck tool and Oracle recommends to download the ORAchk tool to verify All the Oracle stack.
To download and more details about the script is in Oracle support
$ ./orachk -u -o pre
Enter upgrade target version (valid versions are 11.2.0.3.0, 11.2.0.4.0, 12.1.0.1.0, 12.1.0.2.0):- 11.2.0.4.0
CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /u01/app/grid?[y/n][y]y
Checking ssh user equivalency settings on all nodes in cluster
Node dev02 is configured for ssh user equivalency for oracle user
Searching for running databases . . . . .
. .
List of running databases registered in OCR
1. rac
2. None of above
Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1].1
. .
Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
-------------------------------------------------------------------------------------------------------
Oracle Stack Status
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
dev01 Yes Yes Yes Yes Yes rac01
dev02 Yes Yes Yes Yes Yes rac02
-------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
Installed components summary
---------------------------------------------------------------------------------------------------------------------------------
GI_HOME ORACLE_HOME Database Names
---------------------------------------------------------------------------------------------------------------------------------
/u01/app/grid - 11.2.0.3.0 /u01/app/oracle/product/11.2.0/db - 11.2.0.3.0 rac
---------------------------------------------------------------------------------------------------------------------------------
Copying plug-ins
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . .
Checking Grid Infrastructure and RDBMS upgrade readiness to version 112040
Collections and audit checks log file is
/oracle/software/11204_DB/orachk_dev01_rac_110514_175251/log/orachk.log
Checking for prompts in /oracle/.bash_profile on dev01 for oracle user...
Checking for prompts in /oracle/.bash_profile on dev02 for oracle user...
Starting to run orachk in background on dev02
=============================================================
Node name - dev01
=============================================================
Collecting - ASM Diskgroup Attributes
Collecting - ASM initialization parameters
Collecting - Database Parameters for rac database
Collecting - Database Undocumented Parameters for rac database
Collecting - Pre-upgrade File Locations for rac database
Collecting - /proc/cmdline
Collecting - /proc/modules
Collecting - CPU Information
Collecting - DiskFree Information
Collecting - DiskMount Information
Collecting - Kernel parameters
Collecting - Maximum number of semaphore sets on system
Collecting - Maximum number of semaphores on system
Collecting - Maximum number of semaphores per semaphore set
Collecting - Memory Information
Collecting - NUMA Configuration
Collecting - Network Interface Configuration
Collecting - Network Performance
Collecting - Network Service Switch
Collecting - OS Packages
Collecting - OS version
Collecting - Patches for Grid Infrastructure
Collecting - Patches for RDBMS Home
Collecting - number of semaphore operations per semop system call
Data collections completed. Checking best practices on dev01.
--------------------------------------------------------------------------------------
INFO => Real World Network Bandwidth Guidelines
INFO => Important Automatic Storage Management (ASM) Notes and Technical White Papers
INFO => Unattended Installation and Configuration Options
INFO => Be aware of provided optional integrity checks
INFO => Patching Notes by Release
INFO => Prior to Upgrade Verify NO Materialized Views Being Refreshed (Automatically or Manually) for rac
WARNING => ORACLE_HOME environment variable is set
FAIL => Opatch version is lower than recommended in RDBMS_HOME for /u01/app/oracle/product/11.2.0/db
FAIL => Opatch version is lower than recommended in GRID_HOME
INFO => Special Considerations for SCAN and HAIP of 11.2.0.2 and above Grid Infrastructure
INFO => Minimizing Planned Downtime for Upgrades and Patching
INFO => Upgrade Related References
INFO => Lifetime Support Policy and Product Certification INFO
INFO => Capture performance baseline, backup important configuration files and batch, cron, DBMS_JOBS and DBMS_SCHEDULER jobs
INFO => Known Issues Integrating pre-11gR2 Databases with Grid Infrastructure
INFO => Be Aware of New Instant Client for Simplified Deployment
INFO => Oracle E-Business Suite interoperability and migration resources
INFO => Oracle Software Download Sites
WARNING => One or More Object Names in ALL_OBJECTS table are Reserved Words for rac
WARNING => One or More Column Names in ALL_TAB_COLUMNS table are Reserved Words for rac
WARNING => Some Users Needing Network ACLs for Oracle Utility Packages Found for rac
INFO => Steps to execute rootupgrade.sh
INFO => Save "Oracle Enterprise Manager Database Control" files and data with the emdwgrd Utility before upgrading database.
INFO => Information about ASM process parameter when its not set to default value
INFO => Information about Grid Infrastructure software installation directory
WARNING => OS parameter vm.swappiness is NOT set to the recommended value
Copying results from dev02 and generating report. This might take a while. Be patient.
=============================================================
Node name - dev02
=============================================================
Collecting - /proc/cmdline
Collecting - /proc/modules
Collecting - CPU Information
Collecting - DiskFree Information
Collecting - DiskMount Information
Collecting - Kernel parameters
Collecting - Maximum number of semaphore sets on system
Collecting - Maximum number of semaphores on system
Collecting - Maximum number of semaphores per semaphore set
Collecting - Memory Information
Collecting - NUMA Configuration
Collecting - Network Interface Configuration
Collecting - Network Performance
Collecting - Network Service Switch
Collecting - OS Packages
Collecting - OS version
Collecting - Patches for Grid Infrastructure
Collecting - Patches for RDBMS Home
Collecting - number of semaphore operations per semop system call
Data collections completed. Checking best practices on dev02.
--------------------------------------------------------------------------------------
WARNING => ORACLE_HOME environment variable is set
FAIL => Opatch version is lower than recommended in RDBMS_HOME for /u01/app/oracle/product/11.2.0/db
FAIL => Opatch version is lower than recommended in GRID_HOME
INFO => Information about ASM process parameter when its not set to default value
---------------------------------------------------------------------------------
CLUSTERWIDE CHECKS
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
Detailed report (html) - /oracle/software/orachk_dev01_rac_101014_175251/orachk_dev01_rac_101014_175251.html
UPLOAD(if required) - /oracle/software/orachk_dev01_rac_101014_175251.zip
$
ORAchk generated a detailed report in html which provides the fixes for the failure and warning.
The sample HTML report is as below for the opatch version fix
Oracle RAC Upgrade Readiness Report
System Health Score is 92 out of 100 (detail)
CRS Opatch versionRecommendation
Most recent opatch version is 11.2.0.3.6 (or higher). Download latest opatch from following MOS note and install.
Note :- report data may be empty if its role separated environment where oracle does not have permission to call opatch in GRID_HOME. Please run $CRS_HOME/OPatch/opatch version manually and verify that version is equal or higher than 11.2.03.6
Links
Note: 1373255.1 - 11.2.0.1/11.2.0.2 to 11.2.0.3 Database Upgrade on Exadata Database Machine
Note: 274526.1 - How To Download And Install The Latest OPatch Version
Needs attention on dev03, dev04
Passed on -
Status on dev03:
FAIL => Opatch version is lower than recommended in GRID_HOME
DATA FROM dev03 - CRS OPATCH VERSION
OPatch Version: 11.2.0.3.4
OPatch succeeded.
Status on dev04:
FAIL => Opatch version is lower than recommended in GRID_HOME
DATA FROM dev04 - CRS OPATCH VERSION
OPatch Version: 11.2.0.3.4
OPatch succeeded.
Great ORAchk tool from Oracle.
Part 2 coming soon...
Subscribe to:
Posts (Atom)