GI Patch level mismatch between Nodes - after rolling back failed patch at GI_HOME

Error Messages:


We may get the following error messages while doing ASM operations


ORA-15137: The ASM cluster is in rolling patch state.
The cluster upgrade state is [ROLLING PATCH].




Before implement the solution, Please validate where is the actual problem






1. Check patchlevel


Node1


$ crsctl query crs releasepatch
 Oracle Clusterware release patch level is [1295926169] and the complete list of patches [27678276 ] have been applied on the local node. The release patch string is [18.2.0.0.0].


Node 2

$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [3087104924] and the complete list of patches [27676517 27678271 27678276 ] have been applied on the local node. The release patch string is [18.2.0.0.0].

#  Node 2 is ahead of Node 1



2. Kfod on GI Binary to list the patch levels


Node 1:

 $ $ORACLE_HOME/bin/kfod op=patchlvl
 -------------------
 Current Patch level
 ===================
 1295926169

 $ORACLE_HOME/bin/kfod op=patches
 ---------------
 List of Patches
 ===============
 27678276




Node 2:



$ORACLE_HOME/bin/kfod op=patchlvl
 -------------------
 Current Patch level
 ===================
 3087104924

 $ORACLE_HOME/bin/kfod op=patches
 ---------------
 List of Patches
 ===============
 27676517
 27678271
 27678276


# In Node 1  27676517 &  27678271  are missing.




3.  lsinventory


Node 1
 $ORACLE_HOME/OPatch/opatch lsinventory | grep -i desc
ARU platform description:: Linux x86-64
Patch description:  "OCW RELEASE UPDATE 18.2.0.0.0 (27678276)"
Patch description:  "ACFS RELEASE UPDATE 18.2.0.0.0 (27678271)"
Patch description:  "Database Release Update : 18.2.0.0.180417 (27676517)"

Node 2
$ORACLE_HOME/OPatch/opatch lsinventory | grep -i desc
ARU platform description:: Linux x86-64
Patch description:  "OCW RELEASE UPDATE 18.2.0.0.0 (27678276)"
Patch description:  "ACFS RELEASE UPDATE 18.2.0.0.0 (27678271)"
Patch description:  "Database Release Update : 18.2.0.0.180417 (27676517)"


# But lsinventory shows both patchlevel same


4. Patch log files


Node 1 logs:

Node1_opatch_logs

 Applying /data/oracle/OraclePatches/27681568/27676517 ...
 Patch /data/oracle/OraclePatches/27681568/27676517 applied.

 Applying /data/oracle/OraclePatches/27681568/27678271 ...
 Patch /data/oracle/OraclePatches/27681568/27678271 applied.

 Applying /data/oracle/OraclePatches/27681568/27678276 ...
 Patch /data/oracle/OraclePatches/27681568/27678276 applied. <==== applied successfully





Solutions:


Try the following solutions one by one. Check the releaselevel after each solution implemented, if that particular solution fixed, then you can skip the other steps.


# Sol-1
ALTER SYSTEM STOP ROLLING PATCH

# SOl-2
crsctl stop rollingpatch


#  Sol-3

 As root user,
 $GRID_HOME/crs/install/rootcrs.sh -prepatch
 $GRID_HOME/crs/install/rootcrs.sh -postpatch

 if above does not help, execute following and check status
 $GI_HOME/bin/clscfg -patch


# Sol-4


 Action Plan
 ==============
 Step 1


  $GRID_HOME/crs/install/rootcrs.sh -prepatch


 Step 2

  Add the missing patches to the kfod output[which are available in opatch lsinventory],
  As grid owner,
  $GRID_HOME/bin/patchgen commit -pi 27676517
  $GRID_HOME/bin/patchgen commit -pi 27678271

 Incase if we need to rollback extra patch

  $GRID_HOME/bin/patchgen commit -rb 27678271
  Check the below on both nodes and notify if there is any difference
  kfod op=patchlvl
  kfod op=patches

 Step 3
  If they are same run the below,
  $GRID_HOME/crs/install/rootcrs.sh -postpatch

Fast Mirror resync and Fast Rebalance - 11g new feature

1. Fast Mirror resync and Fast Rebalance - 11g new feature
     Fast Mirror resync and Fast Rebalance two new features available in 11g .

 Fast Mirror resync:

  ASM fast resync keeps track of pending changes to extents on an OFFLINE disk during an outage. The extents are resynced when the disk is brought back online or replaced.

By default, ASM drops a disk shortly after it is taken offline. You can set the DISK_REPAIR_TIME attribute to prevent this operation by specifying a time interval to repair the disk and bring it back online.

To use this feature, the disk group compatibility attributes must be set to 11.1 or higher
Fast Rebalance

In 11g, you can use the STARTUP RESTRICT command to control access to an ASM instance while you perform maintenance. This startup option is used to implement ASM Fast Rebalance feature. Rebalance operations that occur while a disk group is in RESTRICTED mode eliminate the lock and unlock extent map messaging between ASM instances in Oracle RAC environments.

When an ASM instance is started with 'STARTUP RESTRICT' option and is active in this mode, all of the disk groups are mounted in RESTRICTED mode which prevents databases from connecting to the ASM instance. In addition, the restricted clause of the ALTER SYSTEM statement is disabled for the ASM instance.
  Follow the below steps to do the fast rebalance
  1. startup restrict;
  2.  alter diskgroup dg1 mount restricted;
            ( we cannot mount the disk in mount mode and may get ORA-15251: only restricted mount is allowed in the ASM instance )

3.alter diskgroup dg1 rebalance;
  ( we cannot open database against the asm when it is in restrict mode,we may get the below error

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '+DG1/11g/datafile/exy.256.627345731'

4.shutdown immediate
   ( we cannot enable restriced mode as we do in normal rdbms
     we may the below error
ORA-15000: command disallowed by current instance type
)

5.startup


2. How to improve rebalance operation when multiple disk maintenance activities are there and monitor the status of it.

  if you have multiple diskgroup managment commands like add disk /drop disks. It is recomended to run as a single command to have single rebalance operations rather than running the commands multiple times and invoke rebalances operations

  alter diskgroup diskgroup_name add disk '..../ drop disk '...' ;

Montior the sttaus of the asm operations using v$asm_operations view


SQL> desc v$asm_operation
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NUMBER                                       NUMBER
 OPERATION                                          CHAR(5)
 STATE                                              VARCHAR2(4)
 POWER                                              NUMBER
 ACTUAL                                             NUMBER
 SOFAR                                              NUMBER
 EST_WORK                                           NUMBER
 EST_RATE                                           NUMBER
 EST_MINUTES                                        NUMBER
 ERROR_CODE                                         VARCHAR2(44)

State of the operation:
WAIT - No operations running for the group
RUN - Operation running for the group
REAP - Operation is being run down
HALT - Operation halted by admin
ERRORS - Operation halted by errors

select state,ACTUAL ,sofar,EST_MINUTES ,operations from v$asm_operation

How to extract the files from a unmounted ASM Diskgroup

Please note this amdu is undocumented .  Without oracle's confirmation dont try this options

amdu is a utlity comes with 11g in default ( for 10g we need to download from metalink and install it).It has various options like to compare the mirrors,print the block contents,fullscan on the diskgroup,extract the files from a diskgroup.This utility works even if the diskgroups are in unmounted stage.

Suppose one or more diskgroups are not able to mount due to various reason and mirror copy  is also not working, we need to extract few files from the diskgroup to bring the database to consistent mode. For example some datafiles/archive files/redo log files etc… This utility is very much useful in this scenario.

If there is a physical/media error on the files we are trying extract then the whole process may not work as expected.

A case study has been made to restore all data files from an unmounted diskgroups and build the database in the non-asm filesystem.

1.    Created  a sample database into the ASM diskgroups

create database TEST

MAXINSTANCES 32

MAXLOGHISTORY 1000

MAXLOGFILES 64

MAXLOGMEMBERS 5

MAXDATAFILES 2056

datafile '+DATA_01/TEST/system01.dbf' size 300M EXTENT MANAGEMENT

LOCAL

SYSAUX DATAFILE '+DATA_01/TEST/sysaux01.dbf' size 300M

UNDO TABLESPACE "UNDOTBS1" DATAFILE

'+DATA_01/TEST/undotbs01.dbf' size 200M

LOGFILE GROUP 1 ('+REDO_01/TEST/redo01.log') SIZE 51200K,

GROUP 2 ('+REDO_02/TEST/redo02.log') SIZE 51200K,

GROUP 3 ('+REDO_01/TEST/redo03.log') SIZE 51200K ,

GROUP 4 ('+REDO_02/TEST/redo04.log') SIZE 51200K

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE

'+DATA_01/TEST/temp01.dbf' size 500m

CHARACTER SET WE8ISO8859P1

NATIONAL CHARACTER SET AL16UTF16;

@?/rdbms/admin/catalog

@?/rdbms/admin/catproc

@?/rdbms/admin/dbmspool.sql

2.    Identify the file# against the database files.

set pagesize 0

set linesize 200

col full_alias_path format a80

select  x.gnum,x.filnum,x.full_alias_path,f.ftype from (

SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path

FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,

            a.reference_index rindex,a.group_number gnum,a.file_number filnum

      FROM v$asm_alias a, v$asm_diskgroup g

      WHERE a.group_number = g.group_number)

START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,

(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f

where x.filnum != 4294967295

and x.gnum=f.gnum and x.filnum=f.filnum

/

   1        256 +DATA_01/JEYSERVER/control01.ctl                                             CONTROLFILE

         1        256 +DATA_01/JEYSERVER/CONTROLFILE/Current.256.691068219                         CONTROLFILE

         1        257 +DATA_01/JEYSERVER/CONTROLFILE/Current.257.691068219                         CONTROLFILE

         1        257 +DATA_01/JEYSERVER/control02.ctl                                             CONTROLFILE

         1        258 +DATA_01/JEYSERVER/CONTROLFILE/Current.258.691068219                         CONTROLFILE

         1        258 +DATA_01/JEYSERVER/control03.ctl                                             CONTROLFILE

         1        259 +DATA_01/JEYSERVER/system01.dbf                                              DATAFILE

         1        259 +DATA_01/JEYSERVER/DATAFILE/SYSTEM.259.691068223                             DATAFILE

         1        260 +DATA_01/JEYSERVER/DATAFILE/UNDOTBS1.260.691068239                           DATAFILE

         1        260 +DATA_01/JEYSERVER/undotbs01.dbf                                             DATAFILE

         1        261 +DATA_01/JEYSERVER/DATAFILE/SYSAUX.261.691068243                             DATAFILE

         1        261 +DATA_01/JEYSERVER/sysaux01.dbf                                              DATAFILE

         1        262 +DATA_01/JEYSERVER/temp01.dbf                                                TEMPFILE

         1        262 +DATA_01/JEYSERVER/TEMPFILE/TEMP.262.691068249                               TEMPFILE

         1        263 +DATA_01/test_asm.dmp                                                       DUMPSET

         1        263 +DATA_01/JEYSERVER/DUMPSET/SIVASYS_EXPORT_TRANSPORTABLE_0_231115_1.263.75217 DUMPSET

                      0035

         1        264 +DATA_01/JEYSERVER/DATAFILE/TEST_ASM.264.752168641                           DATAFILE

         1        265 +DATA_01/TEST/CONTROLFILE/Current.265.759845783                             CONTROLFILE

         1        265 +DATA_01/TEST/control01.ctl                                                 CONTROLFILE

         1        266 +DATA_01/TEST/CONTROLFILE/Current.266.759845783                             CONTROLFILE

         1        266 +DATA_01/TEST/control02.ctl                                                 CONTROLFILE

         1        267 +DATA_01/TEST/CONTROLFILE/Current.267.759845783                             CONTROLFILE

         1        267 +DATA_01/TEST/control03.ctl                                                 CONTROLFILE

         1        268 +DATA_01/TEST/DATAFILE/SYSTEM.268.759845785                                 DATAFILE

         1        268 +DATA_01/TEST/system01.dbf                                                  DATAFILE

         1        269 +DATA_01/TEST/DATAFILE/SYSAUX.269.759845791                                 DATAFILE

         1        269 +DATA_01/TEST/sysaux01.dbf                                                  DATAFILE

         1        270 +DATA_01/TEST/DATAFILE/UNDOTBS1.270.759845795                               DATAFILE

         1        270 +DATA_01/TEST/undotbs01.dbf                                                 DATAFILE

         1        271 +DATA_01/TEST/TEMPFILE/TEMP.271.759845795                                   TEMPFILE

         1        271 +DATA_01/TEST/temp01.dbf                                                    TEMPFILE

         2        256 +DATA_02/JEYSERVER/XTRANSPORT/TEST_ASM.256.752170417                         XTRANSPORT

         4        256 +REDO_01/JEYSERVER/redo01.log                                                ONLINELOG

         4        256 +REDO_01/JEYSERVER/ONLINELOG/group_1.256.691068219                           ONLINELOG

         4        257 +REDO_01/JEYSERVER/redo03.log                                                ONLINELOG

         4        257 +REDO_01/JEYSERVER/ONLINELOG/group_3.257.691068221                           ONLINELOG

         4        258 +REDO_01/TEST/redo01.log                                                    ONLINELOG

         4        258 +REDO_01/TEST/ONLINELOG/group_1.258.759845783                               ONLINELOG

         4        259 +REDO_01/TEST/redo03.log                                                    ONLINELOG

         4        259 +REDO_01/TEST/ONLINELOG/group_3.259.759845783                               ONLINELOG

         5        256 +REDO_02/JEYSERVER/ONLINELOG/group_2.256.691068221                           ONLINELOG

         5        256 +REDO_02/JEYSERVER/redo02.log                                                ONLINELOG

         5        257 +REDO_02/JEYSERVER/redo04.log                                                ONLINELOG

         5        257 +REDO_02/JEYSERVER/ONLINELOG/group_4.257.691068221                           ONLINELOG

         5        258 +REDO_02/TEST/redo02.log                                                    ONLINELOG

         5        258 +REDO_02/TEST/ONLINELOG/group_2.258.759845783                               ONLINELOG

         5        259 +REDO_02/TEST/redo04.log                                                    ONLINELOG

         5        259 +REDO_02/TEST/ONLINELOG/group_4.259.759845785                               ONLINELOG

48 rows selected.

3.    Shutdown the databases

4.    Dismount the diskgroups

SQL> alter diskgroup DATA_01 dismount;

         alter diskgroup REDO_01 dismount;

         alter diskgroup REDO_02 dismount;

5.    Extract the files from diskgroups

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.265

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.266

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.267

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.268

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.269

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.270

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract DATA_01.271

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract REDO_01.258

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract REDO_02.258

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract REDO_01.259

amdu -dis "/dev/vx_wa/rdsk/hostnamedg/" -extract REDO_02.259

It creates a folder and extract the file and report.txt  into that folder.

drwxr-xr-x   2 oracle   orainst       96 Aug 22 16:29 amdu_2011_08_22_16_22_15

drwxr-xr-x   2 oracle   orainst       96 Aug 22 16:29 amdu_2011_08_22_16_22_28

drwxr-xr-x   2 oracle   orainst       96 Aug 22 16:29 amdu_2011_08_22_16_22_42

drwxr-xr-x   2 oracle   orainst       96 Aug 22 16:29 amdu_2011_08_22_16_22_55

drwxr-xr-x   2 oracle   orainst       96 Aug 22 16:29 amdu_2011_08_22_16_24_09

drwxr-xr-x   2 oracle   orainst       96 Aug 22 16:29 amdu_2011_08_22_16_25_21

drwxr-xr-x   2 oracle   orainst       96 Aug 22 16:29 amdu_2011_08_22_16_26_25

drwxr-xr-x   2 oracle   orainst       96 Aug 22 17:12 amdu_2011_08_22_16_58_18

drwxr-xr-x   2 oracle   orainst       96 Aug 22 17:12 amdu_2011_08_22_17_06_57

drwxr-xr-x   2 oracle   orainst       96 Aug 22 17:12 amdu_2011_08_22_17_10_31

drwxr-xr-x   2 oracle   orainst       96 Aug 22 17:13 amdu_2011_08_22_17_04_04

6.    Rename the files to the relevant name

-rw-r--r--   1 oracle   orainst  524296192 Aug 22 16:28 temp01.dbf

-rw-r--r--   1 oracle   orainst  52429312 Aug 22 17:15 redo02.log

-rw-r--r--   1 oracle   orainst  52429312 Aug 22 17:15 redo03.log

-rw-r--r--   1 oracle   orainst  52429312 Aug 22 17:15 redo04.log

-rw-r--r--   1 oracle   orainst  209723392 Aug 22 17:46 undotbs01.dbf

-rw-r--r--   1 oracle   orainst  314580992 Aug 22 17:46 system01.dbf

-rw-r--r--   1 oracle   orainst  314580992 Aug 22 17:46 sysaux01.dbf

-rw-r--r--   1 oracle   orainst  52429312 Aug 22 17:47 redo01.log

7.    Start the instance with control file pointing to filesystem or available diskgroup

*.control_files='/data/oracle/JEYSERVER/backup/export/control01.ctl','/data/oracle/JEYSERVER/backup/export/control02.ctl'

8. create control file using the below

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 64

    MAXLOGMEMBERS 5

    MAXDATAFILES 2056

    MAXINSTANCES 32

    MAXLOGHISTORY 1168

LOGFILE

  GROUP 1 '/data/oracle/JEYSERVER/backup/DB/redo01.log'  SIZE 50M,

  GROUP 2 '/data/oracle/JEYSERVER/backup/DB/redo02.log'  SIZE 50M,

  GROUP 3 '/data/oracle/JEYSERVER/backup/DB/redo03.log'  SIZE 50M,

  GROUP 4 '/data/oracle/JEYSERVER/backup/DB/redo04.log'  SIZE 50M

-- STANDBY LOGFILE

DATAFILE

  '/data/oracle/JEYSERVER/backup/DB/system01.dbf',

  '/data/oracle/JEYSERVER/backup/DB/sysaux01.dbf',

  '/data/oracle/JEYSERVER/backup/DB/undotbs01.dbf'

CHARACTER SET WE8ISO8859P1

;

--RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/data/oracle/JEYSERVER/backup/DB/temp01.dbf'

     SIZE 524288000  REUSE AUTOEXTEND OFF;

-- End of tempfile additions.

--

8.    Recover the database and If required provide the redo log files/archive log files for the recovery

SQL>  recover database using backup controlfile;

ORA-00279: change 238825 generated at 08/22/2011 16:32:54 needed for thread 1

ORA-00289: suggestion : /data/oracle/TEST/archive1_13_759845780.dbf

ORA-00280: change 238825 for thread 1 is in sequence #13

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/data/oracle/JEYSERVER/backup/DB/redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME

---------

TEST

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/data/oracle/JEYSERVER/backup/DB/system01.dbf

/data/oracle/JEYSERVER/backup/DB/sysaux01.dbf

/data/oracle/JEYSERVER/backup/DB/undotbs01.dbf

ASM Metadata backup - 11G

We know that asm is full of metadata . It is advisable to backup the metadata ,incase if asm instance crash or issue with particalu diskgroups. 11gR1 includes the facility to take metadata backup and restore.

 md_backup and md_restore are asmcmd commands.

This script will help you to take metadata backup

dtm=`date +%d%m%y%H%M%S`
LOG_FILE=/tmp/asm_backup.${dtm}
rm /app/oracle/TEST/backup/export/asm_dg.backup
HOSTNAME=`hostname`
if [ -s /etc/oratab ] ; then
   ORATAB=/etc/oratab
elif [ -s /var/opt/oracle/oratab ] ; then
     ORATAB=/var/opt/oracle/oratab
elif [ -s /etc/opt/oracle/oratab ] ; then
     ORATAB=/etc/opt/oracle/oratab
else
     echo "ORATAB is missing" 2>&1 1>> $LOG_FILE
     cat $LOG_FILE
     exit 1
fi
cat ${ORATAB}|awk -F\# '{print $1}'|grep -v '^$'|awk -F\: '{print $1" "$2" "$3}' |while read ORACLE_SID ORACLE_HOME BOOT_FLAG
do
 if [ "${ORACLE_SID}" = "+ASM" -a "${BOOT_FLAG}" = "Y" ] ; then
  export ORACLE_SID
  export ORACLE_HOME
  export BOOT_FLAG
  PATH=$ORACLE_HOME/bin:$PATH
  export PATH
  asmcmd  lsdg
 asmcmd  md_backup -b /app/oracle/TEST/backup/export/asm_dg.backup #ASMCMD> md_restore -t full -g RECOVER /tmp/dg.backup
fi
done

How to optimize the rebalance operation in asm when multiple disk maintenance activities are need to be run at the same time,also monitor the status of the rebalance operation

Sometime dbas need to do multiple diskgroup managment commands like add a disk  and drop a disk. We used to run the commands one by one.since rebalance process will be forked for one disk maintenance activity,in this case two rebalance process will be running.To avoid that it is recomended to run both the command in a single statement and it invokes single rebalances operations

  alter diskgroup diskgroup_name add disk 'o/xxx/xxxx.'  drop disk  'o/xxx/xxx' ;

We can montior the staus of the asm operations using v$asm_operations view


SQL> desc v$asm_operation
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NUMBER                                       NUMBER
 OPERATION                                          CHAR(5)
 STATE                                              VARCHAR2(4)
 POWER                                              NUMBER
 ACTUAL                                             NUMBER
 SOFAR                                              NUMBER
 EST_WORK                                           NUMBER
 EST_RATE                                           NUMBER
 EST_MINUTES                                        NUMBER
 ERROR_CODE                                         VARCHAR2(44)

State of the operation:
WAIT - No operations running for the group
RUN - Operation running for the group
REAP - Operation is being run down
HALT - Operation halted by admin
ERRORS - Operation halted by errors

select state,ACTUAL ,sofar,EST_MINUTES ,operation from v$asm_operation;