Thursday 28 May 2015

How to configure YUM on Oracle Linux


How to configure YUM on Oracle Enterprise Linux?

This is an example for Enterprise Linux 6:

go to the directory /etc/yum.repos.d and edit the file public-yum-el5.repo

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-el5.repo

Open the yum configuration file in a text editor:
vi public-yum-el5.repo
Change enabled=0 to enabled=1 from the repository you need.

If you have other Linux installtions, then go to http://public-yum.oracle.com/ for instructions on how to configure the yum repository for other Linux versions.

Now we can try it!
yum list
yum install firefox

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

Friday 24 October 2014

ORA_01017: Invalid username/password error while creating a standby database

Issue: ORA_01017: Invalid username/password error while creating a standby database

$ rman TARGET sys/change_on_install@TEST

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Oct 24 15:24:44 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied

Though I changed the passwords and recreated the password file in primary and copied back to standby server, still i received the invalid username and password on the standy

Solution:


SQL> show parameter REMOTE_LOGIN_PASSWORD

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      NONE

SQL> show parameter REMOTE_OS_AUTHENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     FALSE
SQL>


The parameter remote_login_password file should be set to exclusive. Changed the value to EXCLUSIVE in primary and restarted the database

SQL> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

System altered.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2405122048 bytes
Fixed Size                  2255592 bytes
Variable Size             503317784 bytes
Database Buffers         1895825408 bytes
Redo Buffers                3723264 bytes
Database mounted.
Database opened.
SQL>

Now the rman command duplicate target database for standby works fine.

Tuesday 21 October 2014

Errors: ORA-00301, ORA-17502, ORA-15005 name is already used by an existing alias

Errors: ORA-00301, ORA-17502, ORA-15005

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
 GROUP 5 ('+TEST_REDO/TEST_redo_t2g1m1',
  2    3           '+TEST_REDO/TEST_redo_t2g1m2') size 20M,
  4   GROUP 6 ('+TEST_REDO/TEST_redo_t2g2m1',
  5           '+TEST_REDO/TEST_redo_t2g2m2') size 20M,
  6   GROUP 7 ('+TEST_REDO/TEST_redo_t2g3m1',
  7           '+TEST_REDO/TEST_redo_t2g3m2') size 20M,
  8   GROUP 8 ('+TEST_REDO/TEST_redo_t2g4m1',
  9           '+TEST_REDO/TEST_redo_t2g4m2') size 20M;
ALTER DATABASE ADD LOGFILE THREAD 2
*
ERROR at line 1:
ORA-00301: error in adding log file '+TEST_REDO/TEST_redo_t2g1m1' - file cannot be created
ORA-17502: ksfdcre:4 Failed to create file +TEST_REDO/TEST_redo_t2g1m1
ORA-15005: name "TEST_redo_t2g1m1" is already used by an existing alias

The problem is because the logfile exists in the ASM disks as below

servTEST501:grid $ asmcmd
ASMCMD> cd TEST_REDO
ls
ASMCMD> TEST/
TEST_def_temp_01
TEST_redo_t1g1m1
TEST_redo_t1g1m2
TEST_redo_t1g2m1
TEST_redo_t1g2m2
TEST_redo_t1g3m1
TEST_redo_t1g3m2
TEST_redo_t1g3m3
TEST_redo_t1g3m4
TEST_redo_t1g4m1
TEST_redo_t1g4m2
TEST_redo_t2g1m1
TEST_redo_t2g1m2
TEST_redo_t2g2m1
TEST_redo_t2g2m2
TEST_redo_t2g3m1
TEST_redo_t2g3m2
TEST_redo_t2g4m1
TEST_redo_t2g4m2
TEST_temp_01
ASMCMD> rm TEST_redo_t2g1m1
ASMCMD> rm TEST_redo_t2g1m2
;
:
ASMCMD>rm TEST_redo_t2g4m2

remove the logfiles created for thread 2 in the asm and recreate the logfiles

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
 GROUP 5 ('+TEST_REDO/TEST_redo_t2g1m1',
         '+TEST_REDO/TEST_redo_t2g1m2') size 20M,
  2    3    4   GROUP 6 ('+TEST_REDO/TEST_redo_t2g2m1',
  5           '+TEST_REDO/TEST_redo_t2g2m2') size 20M,
  6   GROUP 7 ('+TEST_REDO/TEST_redo_t2g3m1',
  7           '+TEST_REDO/TEST_redo_t2g3m2') size 20M,
  8   GROUP 8 ('+TEST_REDO/TEST_redo_t2g4m1',
  9           '+TEST_REDO/TEST_redo_t2g4m2') size 20M;

Database altered.

Explanation: Though we drop the logs using the sql command it is registered as dropped in the controlfile but the physical file exists which has to be removed manually.



Tuesday 8 July 2014

How to change the hostname on Oracle Enterprise Linux 6

How to change the hostname on Oracle Enterprise Linux 6?

If you want to change the hostname of the server, then edit the below two files

/etc/sysconfig/network
/etc/hosts

Lets take the example that we want to change the hostname "myserver001" to "hostserver001"
#vi /etc/sysconfig/network

HOSTNAME=myserver001
NETWORKING=yes
GATEWAY=172.21.110.54
NETWORKING_IPV6=no
IPV6INIT=no
NOZEROCONF=yes

After change

HOSTNAME=hostserver001
NETWORKING=yes
GATEWAY=172.21.110.54
NETWORKING_IPV6=no
IPV6INIT=no
NOZEROCONF=yes

Then update the /etc/hosts file
172.21.110.160          myserver001

After change


172.21.110.160           hostserver001


Now reboot the server..Your new name is assigned to the host.

Friday 27 June 2014

How to check the status of Oracle VM manager through perl / shell script?

How to check the status of Oracle VM manager through perl / shell script?

Scripting helps to automate a task. If you are looking for a script to check the status of the OVM then the below perl script can be used to check the status of the Oracle VM manager.

Save the file as check_vm_status.pl

#!/usr/bin/perl -w
my $status = `service ovmm status`;
chomp($status);
my $statusok = "Oracle VM Manager is running...";
if ( $status ne $statusok ) {
        print "oracle vm is not running";
}
else
{
        print "$status";
}

To run the script,

$ perl check_vm_status.pl
The output of the above script is as below, If the OVM is running then,

Oracle VM Manager is running...

If the OVM is not running then

oracle vm is not running

The same code can be used as Korn shell script as below

Save the file as check_vm_status.ksh

#!/usr/bin/ksh -w
$status = `service ovmm status`;

$statusok = "Oracle VM Manager is running...";
if ( $status ne $statusok ) {
        print "oracle vm is not running";
}
else
{
        print "oracle vm is running";
}

To run the script,

$ perl check_vm_status.ksh


Hope this helps!