Wednesday 12 November 2014

ORA-04021: timeout occurred while waiting to lock object error while executing catbundle.sql script

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.

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