Oracle DBA Tips and Techniques

By Arjun Raja

Archive for June, 2012

Grid infrastructure upgrade – 11.2.0.2 to 11.2.0.3 on AIX

Posted by Arjun Raja on June 14, 2012

TWO TASKS.

1 . UPGRADE OF GRID INFRASTRUCTURE AND ASM DATABASE FROM 11.2.0.2 TO  11.2.0.3

2. UPGRADE OF DATABASE  FROM  11.2.0.2 TO 11.2.0.3

Environment is a stand-alone one – NO RAC.

MOS note 1363369.1

Current setup.

  1. Grid Infrastructure 11.2.0.2 version  –  Grid Home ( /u01/oracle/product/11.2.0/grid)
  2. Oracle Database   11.2.0.2 version – Oracle Home ( /u01/oracle/product/11.2.0.2)

Current instances.

  1. ASM instance called +ASM       11.2.0.2
  2. Oracle database called cloudy   11.2.0.2

In this document we will first upgrade the Grid Infrastructure (GI) and then the Oracle database – both to 11.2.0.3

STEP ONE : DOWNLOAD FOLLOWING PATCHES.

  1. 1.     Patch  10404530 –  This is the full 11.2.0.3 patch – a new ORACLE_HOME will be created.

 Following patches to be downloaded and unzipped.

  1. 1.     Grid infrastructure patches.

p10404530_112030_AIX64-5L_1of7.zip

p10404530_112030_AIX64-5L_12of7.zip

  1. 2.     Oracle Database patch

p10404530_112030_AIX64-5L_3of7.zip

      3Deinstaller patch.

p10404530_112030_AIX64-5L_7of7.zip

      4. Opatch version to be used for this installation

Patch  6880880 

    5.  First pre-requisite patch

     Patch 13343424 – pre-requisite patch before applying next mandatory patch 1253900

     6.     Mandatory pre-requsite patch

Patch 12539000

All patches in /u03/backup/stage.

p10404530_112030_AIX64-5L_1of7.zip and p10404530_112030_AIX64-5L_2of7.zip when unzipped creates a directory called grid – both these zip files unzip into the same grid direcotory.

p10404530_112030_AIX64-5L_3of7.zip  when unzipped creates a directory called database.

p10404530_112030_AIX64-5L_7of7.zip when unzipped creates a directory called deinstall.

p6880880_112000_AIX64-5L.zip when unzipped creates directory called OPatch

Copy this OPatch directory to the current GI HOME – therefore creating a $ORACLE_HOME/OPatch directory – this OPatch version will be used for patching…remove any existing OPatch directory or move it somewhere else before copying the new OPatch directory – just as a backup.

p13343424_112020_AIX64-5L.zip – when unzipped creates directory called 13343424

p12539000_112025_AIX64-5L.zip – when unzipped creates directory called 12539000

 STEP 2.  APPLY PATCHES – Need root access for a few steps.

Make sure OPatch with latest version – patch 6880880 is in place in the GI HOME/OPatch directory.

I tried to apply the patch using this opatch auto but it failed…..to avoid this, opatch needs to be installed manually.

To do this –

  1.  unlock the GI HOME with a command run by root.

$ORACLE_HOME/crs/install/roothas.pl -unlock

/u01/oracle/product/11.2.0/grid/crs/install/roothas.pl -unlock

Using configuration parameter file: /u01/oracle/product/11.2.0/grid/crs/install/crsconfig_params

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘xxxu026’

CRS-2673: Attempting to stop ‘ora.evmd’ on ‘xxxxu026’

CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘xxxxu026’

CRS-2673: Attempting to stop ‘ora.cloudy.db’ on ‘xxxx026’

CRS-2673: Attempting to stop ‘ora.LISTENER_UPG.lsnr’ on ‘xxxxu026’

CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘xxxxu026’

CRS-2677: Stop of ‘ora.DATA.dg’ on ‘xxxxx026’ succeeded

CRS-2673: Attempting to stop ‘ora.asm’ on ‘xxxxx026’

CRS-2677: Stop of ‘ora.asm’ on ‘xxxxxx026’ succeeded

CRS-2673: Attempting to stop ‘ora.cssd’ on ‘xxxxxxu026’

CRS-2677: Stop of ‘ora.cloudy.db’ on ‘xxxxu026’ succeeded

CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘xxxxxu026’ succeeded

CRS-2677: Stop of ‘ora.cssd’ on ‘xxxxxxu026’ succeeded

CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘xxxxu026’

CRS-2677: Stop of ‘ora.evmd’ on ‘xxxxu026’ succeeded

CRS-2677: Stop of ‘ora.diskmon’ on ‘xxxxu026’ succeeded

CRS-2677: Stop of ‘ora.LISTENER_UPG.lsnr’ on ‘xxxxu026’ succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘xxxxxu026’ has completed

CRS-4133: Oracle High Availability Services has been stopped.

All services running out of the GI home are stopped automatically .

  1. 2.     Apply the pre-requisite patch as grid user (owner of grid infra software)

export ORACLE_HOME=/u01/oracle/product/11.2.0/grid

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

export LD_LIBRAY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32

cd /u03/backup/stage/13343424

Enter foll :

which opatch

/u01/oracle/product/11.2.0/grid/OPatch/opatch

Apply patch –

cd /u03/backup/stage/13343424 > opatch apply

Prompts for some oracle.com access passwords– no need, simply ignore and carry on.

Once this patch is applied, apply the next mandatory pre-req patch – number 12539000

cd /u01/oracle/product/11.2.0/grid/OPatch

As grid user: $ $GRID_HOME/OPatch/opatch napply -oh <GRID_HOME> -local <UNZIPPED_PATH_LOCATION

Below is the actual command I ran…

cd /u01/oracle/product/11.2.0/grid/OPatch>

./opatch napply -oh /u01/oracle/product/11.2.0/grid -local /u03/backup/stage/12539000

Oracle Interim Patch Installer version 11.2.0.3.0

Copyright (c) 2012, Oracle Corporation.  All rights reserved.

 

Oracle Home       : /u01/oracle/product/11.2.0/grid

Central Inventory : /u01/oracle/oraInventory

   from           : /u01/oracle/product/11.2.0/grid/oraInst.loc

OPatch version    : 11.2.0.3.0

OUI version       : 11.2.0.2.0

Log file location : /u01/oracle/product/11.2.0/grid/cfgtoollogs/opatch/opatch2012-05-29_08-08-23AM_1.log

 

Verifying environment and performing prerequisite checks…

OPatch continues with these patches:   12539000

 

Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

 

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = ‘/u01/oracle/product/11.2.0/grid’)

 

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files…

Applying interim patch ‘12539000’ to OH ‘/u01/oracle/product/11.2.0/grid’

 

Patching component oracle.rdbms, 11.2.0.2.0…

 

Verifying the update…

 

OPatch found the word “warning” in the stderr of the make command.

Please look at this stderr. You can re-run this make command.

Stderr output:

ld: 0711-224 WARNING: Duplicate symbol: fc_softc

ld: 0711-224 WARNING: Duplicate symbol: __fe_def_env

ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.

ld: 0711-773 WARNING: Object /u01/oracle/product/11.2.0/grid/lib//libgeneric11.a[sdbgrfu.o], imported symbol timezone

        Symbol was expected to be local. Extra instructions

        are being generated to reference the symbol.

 

Ignore above warnings….

 

Patch 12539000 successfully applied.

OPatch Session completed with warnings.

Log file location: /u01/oracle/product/11.2.0/grid/cfgtoollogs/opatch/opatch2012-05-29_08-08-23AM_1.log

 

OPatch completed with warnings.

 

  1. As root lock and relink the GI home .

Before running the lock command, to avoid hitting a bug which throws this error below –

Undefined subroutine &main::read_file called at /u01/oracle/product/11.2.0/grid/crs/install/crspatch.pm line 86.

 Take a backup of the file <GridHome>/crs/install/crsconfig_lib.pm

# cd <GridHome>/crs/install

 

cd /u01/oracle/product/11.2.0/grid/crs/install

 cp crsconfig_lib.pm crsconfig_lib.pm.bak

Make the following change in that file crsconfig_lib.pm

From
     my @exp_func = qw(check_CRSConfig validate_olrconfig validateOCR
To
     my @exp_func = qw(check_CRSConfig validate_olrconfig validateOCR read_file

Now lock and relink the GI HOME as root user….

 

  1. xxxxu026 # /u01/oracle/product/11.2.0/grid/rdbms/install/rootadd_rdbms.sh

 

  1. xxx026 # /u01/oracle/product/11.2.0/grid/crs/install/roothas.pl -patch

Using configuration parameter file: /u01/oracle/product/11.2.0/grid/crs/install/crsconfig_params

Undefined subroutine &main::read_file called at /u01/oracle/product/11.2.0/grid/crs/install/crspatch.pm line 86.

xxxxu026 # TMOUT=0

xxxxu026 # /u01/oracle/product/11.2.0/grid/crs/install/roothas.pl -patch

Using configuration parameter file: /u01/oracle/product/11.2.0/grid/crs/install/crsconfig_params

ACFS-9200: Supported

CRS-4123: Oracle High Availability Services has been started.

xxxxxu026 #

 

The roothas.pl –patch command also restarts the ASM instance and all other instances discovered by oracle HAS ( High Availability Services )

 

 

Next step is to upgrade the GI to 11.2.0.3 by applying patch 10404530

 

Before that shutdown all instances except the +ASM instance.

 

Make a couple of changes to the memory parameters in +ASM instance

 

. oraevn

 

+ASM

 

sqlplus sys as sysasm

 

enter

 

SQL>  show parameter memory_target

 

NAME                                 TYPE        VALUE

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

memory_target                        big integer 272M

 

SQL> alter system set memory_max_target=4096m scope=spfile;

 

System altered.

 

SQL>  alter system set memory_target=1536m scope=spfile;

 

System altered.

 

These changes will come into effect at next restart.

 

 

Finally unset ORACLE_HOME and only set the ORACLE_BASE.

 

env |grep ORA

ORACLE_BASE=/u01/oracle

PS1=${ORACLE_SID}:$PWD>

ORAENV_ASK=YES

 

Begin upgrade of GI to 11.2.0.3.

 

Confirm all is okay –

 

cd $ORACLE_HOME/bin > 11.2.0.2 home/bin

 

./crsctl status resource –t

 

./crsctl query  has releaseversion

 

Now launch installer from staging area for software.

 

cd /u02/oradata/software/11.2.0.3/grid – where the new 11.2.0.3 GRID INFRA SOFTWARE EXISTS

 

export DISPLAY=10.4.226.18:0.0

 

./runInstaller

 

Oracle is aware that the 11.2.0.2 GI is already running so straightaway the upgrade option is chosen.

 

Enter the new 11.2.0.3 home directory to install the software and continue.

 

Prompts for rootupgrade.sh to be run after a while – to be run as root user.

 

xxxx026 # /u01/oracle/product/11.2.0.3/grid/rootupgrade.sh

Performing root user operation for Oracle 11g

 

The following environment variables are set as:

    ORACLE_OWNER= xxxxxx

    ORACLE_HOME=  /u01/oracle/product/11.2.0.3/grid

 

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The contents of “dbhome” have not changed. No need to overwrite.

The contents of “oraenv” have not changed. No need to overwrite.

The contents of “coraenv” have not changed. No need to overwrite.

 

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Using configuration parameter file: /u01/oracle/product/11.2.0.3/grid/crs/install/crsconfig_params

Creating trace directory

 

ASM Configuration upgraded successfully.

 

Creating OCR keys for user ‘ofsad2’, privgrp ‘dba’..

Operation successful.

LOCAL ONLY MODE

Successfully accumulated necessary OCR keys.

Creating OCR keys for user ‘root’, privgrp ‘system’..

Operation successful.

CRS-4664: Node xxxxu026 successfully pinned.

Replacing Clusterware entries in inittab

Replacing Clusterware entries in inittab

 

xxxxx026     2012/05/29 10:31:34     /u01/oracle/product/11.2.0.3/grid/cdata/xxxx026/backup_20120529_103134.olr

 

xxxxxu026     2011/08/24 17:37:07     /u01/oracle/product/11.2.0/grid/cdata/xxxxu026/backup_20110824_173707.olr

Successfully configured Oracle Grid Infrastructure for a Standalone Server

xxxxu026 #

 

Once complete click OK and installation continues to completion.

 

cat /etc/oratab

 

+ASM:/u01/oracle/product/11.2.0.3/grid:N

emrep:/u01/ofsad2>

 

Shows that the +ASM instance is now upgraded to 11.2.0.3

 

export ORACLE_HOME=/u01/oracle/product/11.2.0.3/grid

 

cd $ORACLE_HOME/bin >  ./crsctl status resource -t

 

Then stop high availability system ( HAS )

 

+ASM:/u01/oracle/product/11.2.0.3/grid/bin>./crsctl stop has

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘xxxxu026’

CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘xxxxu026’

CRS-2673: Attempting to stop ‘ora.LISTENER_UPG.lsnr’ on ‘xxxu026’

CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘xxxxu026’

CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘xxxx026’ succeeded

CRS-2677: Stop of ‘ora.LISTENER_UPG.lsnr’ on ‘xxxxu026’ succeeded

CRS-2677: Stop of ‘ora.DATA.dg’ on ‘xxxxu026’ succeeded

CRS-2673: Attempting to stop ‘ora.asm’ on ‘xxxxu026’

CRS-2677: Stop of ‘ora.asm’ on ‘xxxxu026’ succeeded

CRS-2673: Attempting to stop ‘ora.cssd’ on ‘xxxxu026’

CRS-2677: Stop of ‘ora.cssd’ on ‘xxxx026’ succeeded

CRS-2673: Attempting to stop ‘ora.evmd’ on ‘xxxxu026’

CRS-2677: Stop of ‘ora.evmd’ on ‘xxxxu026’ succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘xxxxu026’ has completed

CRS-4133: Oracle High Availability Services has been stopped.

+ASM:/u01/oracle/product/11.2.0.3/grid/bin>

 

Start has again.

+ASM:/u01/oracle/product/11.2.0.3/grid/bin>./crsctl start has

CRS-4123: Oracle High Availability Services has been started.

ps -ef |grep tns

 ofsad2 0:00 /u01/oracle/product/11.2.0.3/grid/bin/tnslsnr LISTENER -inherit

+ASM:/u01/oracle/product/11.2.0.3/grid/bin>

Listener started from new 11.2.0.3 GI HOME.

+ASM:/u01/oracle/product/11.2.0.3/grid/bin> ps -ef |grep pmon

  ofsad2 16122102        1   0 13:10:17      –  0:00 asm_pmon_+ASM

+ASM instance also started from new 11.2.0.3 GI home.

All good !

To upgrade 11.2.0.2 databases to 11.2.0.3, first install Oracle database 11.2.0.3 software using the following zip file – p10404530_112030_AIX64-5L_3of7.zip

Unzip the above file to get a database direcotory.

cd  /u03/backup/stage/database.

export ORACLE_HOME=/u01/oracle/product/11.2.0.3/db_1

export DISPLAY

./runInstaller

Install database  software 11.2.0.3 in /u01/oracle/product/11.2.0.3/db_1

Once completed, you can upgrade individual databases to 11.2.0.3 .

 

 

Posted in Uncategorized | Leave a Comment »

LOG: Local Authentication Failed…Attempt PAM authentication…PAM failed with error: Authentication failed

Posted by Arjun Raja on June 7, 2012

When running a job via Grid Control 11g  on AIX trying to use the option of Job type SQL SCRIPT and using Preferred Credentials the following error is received :

LOG: Local Authentication Failed…Attempt PAM authentication…PAM failed with error: Authentication failed

A look at the log file called /var/log/authlog ( can be read only by root ) in the agent host box reveals the following –

auth|security:debug /u01/oracle/agent11g/bin/nmo PAM: pam_authenticate: error Authentication failed

To resolve, we need to append the following to /etc/pam.conf on affected servers:

nmo auth required pam_aix
nmo account required pam_aix

Also make sure the /etc/pam.conf has the foll entries –

emagent auth required pam_aix
emagent account required pam_aix

Only root user can update the /etc/pam.conf file.

All should be fine .

Posted in EM Grid Control | Tagged: | Leave a Comment »