X

Alejandro Vargas' Blog

  • December 18, 2006

Bug 4631662 Script to Automatically change parameters on RAC ASM instance and Database

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

On a  previous post I have described how to check if a RAC 10.2.0.1 database is hitting bug 4631662, ora-29740 instance evicted.

This post contains a script that automatically implement the workaround : Turn off ach reaping time by setting
_disable_instance_params_check = TRUE and _skgxp_udp_ach_reaping_time = 0 on the ASM spfiles and _skgxp_udp_ach_reaping_time = 0 on the Database spfile.

The script may be adapted to automatically change other static parameters with minimal downtime.

The script executes the following steps :

1) Makes a backup of the ASM and Database spfiles on pfiles
2) Add _skgxp_udp_ach_reaping_time=0 parameter on the database spfile
3) Create another ASM pfile based on ASM spfile
4) Add parameters _disable_instance_params_check=TRUE and _skgxp_udp_ach_reaping_time=0 on top of the new ASM pfile
5) Shutdown both Database Instances
6) Shutdown both ASM instances
7) Start ASM instance #1 with the new pfile
8) Recreates ASM spfile based on the new pfile
9) Shutdown ASM instance #1
10) Startup both ASM instances with the new spfile
11) Startup both Database instances with the modified spfile
12) Check that changes were applied correctly on both the ASM and Database spfiles.

The script needs to be run on an enviroment where the following settings are included on the .cshrc script.

Settings for .cshrc script



setenv ORACLE_BASE /oradisk/app01/oracle



setenv ORA_CRS_HOME /oradisk/app01/oracle/product/10gCRS



setenv ASM_HOME /oradisk/app01/oracle/product/10gASM



setenv BASE_PATH

$ORACLE_BASE/scripts/general:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin:/oradisk/app01/oracle/scripts:/usr/local/maint/oracle:/crmdb/app01/oracle/product/db_scripts/RAC:/crmdb/app01/oracle/product/db_scripts



setenv PATH ${ORACLE_HOME}/bin:${BASE_PATH}



alias 10db 'setenv ORACLE_HOME /oradisk/app01/oracle/product/10gDB; setenv PATH ${ORACLE_HOME}/bin:${BASE_PATH}'



alias 10crs 'setenv ORACLE_HOME /oradisk/app01/oracle/product/10gCRS; setenv PATH ${ORACLE_HOME}/bin:${BASE_PATH}'



alias 10asm 'setenv ORACLE_HOME /oradisk/app01/oracle/product/10gASM; setenv PATH ${ORACLE_HOME}/bin:${BASE_PATH}'



alias sts 'setenv ORACLE_SID $1'



Main Script:

#!/bin/tcsh
# 1) cd to working Directory
cd /oradisk/app01/oracle/scripts/av/bug4631662
set v_dst=`pwd`

# 2) Backup spfile and then Introduce the new parameter into it
10db
sts racdbtst1
sqlplus '/ as sysdba' <<eof1
set echo on head on feed on veri on
spool step1_DB.log
create pfile='$v_dst/database_pfile_backup.ora' from spfile;
rem * original spfile location is:
show parameters spfile;
rem * introduce new parameter _skgxp_udp_ach_reaping_time=0
alter system set  "_skgxp_udp_ach_reaping_time"=0 sid='*';
spool off
exit
eof1

# 3) Backup ASM spfile and Introduce changes into new temporary ASM pfile
10asm
sts +ASM1
sqlplus '/ as sysdba' <<eof1
set echo on head on feed on veri on
spool step2_ASM.log
rem * Backup spfile to pfile
create pfile='$v_dst/asm_pfile_backup.ora' from spfile;
rem * Create temporary pfile to introduce the new parameter
create pfile='$v_dst/tmp.ora' from spfile;
spool off
exit
eof1

echo "*._disable_instance_params_check = TRUE"   >$v_dst/asmpfile.ora
echo "*._skgxp_udp_ach_reaping_time = 0"        >>$v_dst/asmpfile.ora
cat  $v_dst/tmp.ora                             >>$v_dst/asmpfile.ora
rm   $v_dst/tmp.ora

echo This is the new ASM pfile
echo
cat $v_dst/asmpfile.ora
echo

# 4) get asm spfile name
set v_spfn=`echo "show parameters spfile ;" | sqlplus '/ as sysdba' | grep "string" |awk '{print $3}'`
echo
echo "The original spfile name is $v_spfn"
echo

# 5) bring down database and asm instances
10crs
chkcrs
echo "all RAC components up"
echo
echo " Stoping database services ..."
srvctl stop service -d racdbtst
echo " Stoping database ..."
srvctl stop database -d racdbtst
echo " Stoping asm on node 1 ..."
srvctl stop asm -n vmractest1
echo " Stoping asm on node 2 ..."
srvctl stop asm -n vmractest2

# 5) bring up ASM with new pfile and recreate the spfile including the new parameters
10asm
sts +ASM1
sqlplus '/ as sysdba' <<eof1
set echo on head on feed on veri on
spool step3_ASM.log
startup pfile='$v_dst/asmpfile.ora';
create spfile='$v_spfn' from pfile='$v_dst/asmpfile.ora';
shutdown immediate;
spool off
exit
eof1

# 6) bring up asm database and services using srvctl
10crs
chkcrs
echo "all RAC components down"
echo " Starting asm on node 1 ..."
srvctl start asm -n vmractest1
echo " Starting asm on node 2 ..."
srvctl start asm -n vmractest2
echo " Starting database ..."
srvctl start database -d racdbtst
echo " Starting database services ..."
srvctl start service -d racdbtst
chkcrs

# 7) checkup database and asm parameters

echo Creating pfile from ASM current spfile:
10asm
sts +ASM1
sqlplus -s '/ as sysdba' <<eof1
create pfile='$v_dst/changed_asm.ora' from spfile;
exit
eof1

echo Creating pfile from Database current spfile:
10db
sts racdbtst1
sqlplus -s '/ as sysdba' <<eof1
create pfile='$v_dst/changed_db.ora' from spfile;
exit
eof1

echo
echo Checking ASM instance Parameters:
echo
grep "_disable_instance_params_check" $v_dst/changed_asm.ora; set v_chk=`echo $?`
if ($v_chk == 0) then
 echo "Parameter *._disable_instance_params_check was added to ASM instance"
else
 echo "Parameter  failed: *._disable_instance_params_check on ASM instance"
endif

echo
grep "_skgxp_udp_ach_reaping_time" $v_dst/changed_asm.ora; set v_chk=`echo $?`
if ($v_chk == 0) then
 echo "Parameter *._skgxp_udp_ach_reaping_time, was added to ASM instance"
else
 echo "Parameter  failed: *._skgxp_udp_ach_reaping_time, on ASM instance"
endif

echo
echo Checking Database Parameters:
echo
grep "_skgxp_udp_ach_reaping_time" $v_dst/changed_db.ora; set v_chk=`echo $?`
if ($v_chk == 0) then
 echo "Parameter *._skgxp_udp_ach_reaping_time, was added to Database instance"
else
 echo "Parameter  failed: *._skgxp_udp_ach_reaping_time, on Database instance"
endif

rm changed_db.ora changed_asm.ora

echo
echo "Process completed"
echo


Script Output:

The following is a sample of the output generated when running the script on a 2 node RAC

{oracle} > ./apply_workaround_4RAC.sh
destination directory is: /oradisk/app01/oracle/scripts/av/bug4631662

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 18 17:03:53 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> SQL> SQL>
File created.

SQL> SQL>
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATADG/racdbtst/spfileracdbts
                                                 t.ora
SQL> SQL>
System altered.

SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 18 17:03:54 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters and Data Mining options

SQL> SQL> SQL> SQL>
File created.

SQL> SQL>
File created.

SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters and Data Mining options
This is the new ASM pfile

*._disable_instance_params_check = TRUE
*._skgxp_udp_ach_reaping_time = 0
+ASM1.asm_diskgroups='DATADG'#Manual Mount
*.asm_diskgroups='DATADG'
*.background_dump_dest='/oradisk/app01/oracle/admin/+ASM/bdump'
*.cluster_database=true
*.core_dump_dest='/oradisk/app01/oracle/admin/+ASM/cdump'
+ASM2.instance_number=2
+ASM1.instance_number=1
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='exclusive'
*.user_dump_dest='/oradisk/app01/oracle/admin/+ASM/udump'


The original spfile name is /dev/raw/spfile+ASM.ora

HA Resource                                   Target     State
-----------                                   ------     -----
ora.racdbtst.db                               ONLINE     ONLINE on vmractest1
ora.racdbtst.racdbtst1.inst                   ONLINE     ONLINE on vmractest1
ora.racdbtst.racdbtst2.inst                   ONLINE     ONLINE on vmractest2
ora.racdbtst.ractest1.cs                      ONLINE     ONLINE on vmractest2
ora.racdbtst.ractest1.racdbtst1.srv           ONLINE     ONLINE on vmractest1
ora.racdbtst.ractest1.racdbtst2.srv           ONLINE     ONLINE on vmractest2
ora.racdbtst.ractest2.cs                      ONLINE     ONLINE on vmractest1
ora.racdbtst.ractest2.racdbtst1.srv           ONLINE     ONLINE on vmractest1
ora.racdbtst.ractest3.cs                      ONLINE     ONLINE on vmractest2
ora.racdbtst.ractest3.racdbtst2.srv           ONLINE     ONLINE on vmractest2
ora.vmractest1.ASM1.asm                       ONLINE     ONLINE on vmractest1
ora.vmractest1.LISTENER_VMRACTEST1.lsnr       ONLINE     ONLINE on vmractest1
ora.vmractest1.gsd                            ONLINE     ONLINE on vmractest1
ora.vmractest1.ons                            ONLINE     ONLINE on vmractest1
ora.vmractest1.vip                            ONLINE     ONLINE on vmractest1
ora.vmractest2.ASM2.asm                       ONLINE     ONLINE on vmractest2
ora.vmractest2.LISTENER_VMRACTEST2.lsnr       ONLINE     ONLINE on vmractest2
ora.vmractest2.gsd                            ONLINE     ONLINE on vmractest2
ora.vmractest2.ons                            ONLINE     ONLINE on vmractest2
ora.vmractest2.vip                            ONLINE     ONLINE on vmractest2
all RAC components up

 Stoping database services ...
 Stoping database ...
 Stoping asm on node 1 ...
 Stoping asm on node 2 ...

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 18 17:05:11 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> SQL> SQL> ASM instance started

Total System Global Area   92274688 bytes
Fixed Size                  1217884 bytes
Variable Size              65890980 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL>
File created.

SQL> ASM diskgroups dismounted
ASM instance shutdown
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters and Data Mining options
HA Resource                                   Target     State
-----------                                   ------     -----
ora.racdbtst.db                               OFFLINE    OFFLINE
ora.racdbtst.racdbtst1.inst                   OFFLINE    OFFLINE
ora.racdbtst.racdbtst2.inst                   OFFLINE    OFFLINE
ora.racdbtst.ractest1.cs                      OFFLINE    OFFLINE
ora.racdbtst.ractest1.racdbtst1.srv           OFFLINE    OFFLINE
ora.racdbtst.ractest1.racdbtst2.srv           OFFLINE    OFFLINE
ora.racdbtst.ractest2.cs                      OFFLINE    OFFLINE
ora.racdbtst.ractest2.racdbtst1.srv           OFFLINE    OFFLINE
ora.racdbtst.ractest3.cs                      OFFLINE    OFFLINE
ora.racdbtst.ractest3.racdbtst2.srv           OFFLINE    OFFLINE
ora.vmractest1.ASM1.asm                       OFFLINE    OFFLINE
ora.vmractest1.LISTENER_VMRACTEST1.lsnr       ONLINE     ONLINE on vmractest1
ora.vmractest1.gsd                            ONLINE     ONLINE on vmractest1
ora.vmractest1.ons                            ONLINE     ONLINE on vmractest1
ora.vmractest1.vip                            ONLINE     ONLINE on vmractest1
ora.vmractest2.ASM2.asm                       OFFLINE    OFFLINE
ora.vmractest2.LISTENER_VMRACTEST2.lsnr       ONLINE     ONLINE on vmractest2
ora.vmractest2.gsd                            ONLINE     ONLINE on vmractest2
ora.vmractest2.ons                            ONLINE     ONLINE on vmractest2
ora.vmractest2.vip                            ONLINE     ONLINE on vmractest2
all RAC components down
 Starting asm on node 1 ...
 Starting asm on node 2 ...
 Starting database ...
 Starting database services ...
HA Resource                                   Target     State
-----------                                   ------     -----
ora.racdbtst.db                               ONLINE     ONLINE on vmractest2
ora.racdbtst.racdbtst1.inst                   ONLINE     ONLINE on vmractest1
ora.racdbtst.racdbtst2.inst                   ONLINE     ONLINE on vmractest2
ora.racdbtst.ractest1.cs                      ONLINE     ONLINE on vmractest2
ora.racdbtst.ractest1.racdbtst1.srv           ONLINE     ONLINE on vmractest1
ora.racdbtst.ractest1.racdbtst2.srv           ONLINE     ONLINE on vmractest2
ora.racdbtst.ractest2.cs                      ONLINE     ONLINE on vmractest1
ora.racdbtst.ractest2.racdbtst1.srv           ONLINE     ONLINE on vmractest1
ora.racdbtst.ractest3.cs                      ONLINE     ONLINE on vmractest2
ora.racdbtst.ractest3.racdbtst2.srv           ONLINE     ONLINE on vmractest2
ora.vmractest1.ASM1.asm                       ONLINE     ONLINE on vmractest1
ora.vmractest1.LISTENER_VMRACTEST1.lsnr       ONLINE     ONLINE on vmractest1
ora.vmractest1.gsd                            ONLINE     ONLINE on vmractest1
ora.vmractest1.ons                            ONLINE     ONLINE on vmractest1
ora.vmractest1.vip                            ONLINE     ONLINE on vmractest1
ora.vmractest2.ASM2.asm                       ONLINE     ONLINE on vmractest2
ora.vmractest2.LISTENER_VMRACTEST2.lsnr       ONLINE     ONLINE on vmractest2
ora.vmractest2.gsd                            ONLINE     ONLINE on vmractest2
ora.vmractest2.ons                            ONLINE     ONLINE on vmractest2
ora.vmractest2.vip                            ONLINE     ONLINE on vmractest2
Creating pfile from ASM current spfile:

File created.

Creating pfile from Database current spfile:

File created.


Checking ASM instance Parameters:

*._disable_instance_params_check=TRUE
Parameter *._disable_instance_params_check was added to ASM instance

*._skgxp_udp_ach_reaping_time=0
Parameter *._skgxp_udp_ach_reaping_time, was added to ASM instance

Checking Database Parameters:

*._skgxp_udp_ach_reaping_time=0
Parameter *._skgxp_udp_ach_reaping_time, was added to Database instance

Process completed

Logs and Files Generated by the Script :


apply_workaround_4RAC.shMain Script
step1_DB.logLog database stage 1
step2_ASM.logLog ASM stage 1
step3_ASM.logLog ASM stage 2
asmpfile.oraGenerated ASM pfile with changes
database_pfile_backup.oraDatabase pfile for rollback
asm_pfile_backup.oraASM pfile for rollback
     

Content of the Log Files :

   step1_DB.log

SQL> create pfile='/oradisk/app01/oracle/scripts/av/bug4631662/database_pfile_backup.ora' from spfile;

File created.

SQL> rem * original spfile location is:
SQL> show parameters spfile;

NAME                                 TYPE        VALUE
------------------------------------ -----------
spfile                               string      +DATADG/racdbtst/spfileracdbtst.ora
SQL> rem * introduce new parameter _skgxp_udp_ach_reaping_time=0
SQL> alter system set  "_skgxp_udp_ach_reaping_time"=0 sid='*';

System altered.

SQL> spool off

   step2_ASM.log

SQL> rem * Backup spfile to pfile
SQL> create pfile='/oradisk/app01/oracle/scripts/av/bug4631662/asm_pfile_backup.ora' from spfile;

File created.

SQL> rem * Create temporary pfile to introduce the new parameter
SQL> create pfile='/oradisk/app01/oracle/scripts/av/bug4631662/tmp.ora' from spfile;

File created.

SQL> spool off

   step3_ASM.log

SQL> startup pfile='/oradisk/app01/oracle/scripts/av/bug4631662/asmpfile.ora';

ASM instance started

Total System Global Area   92274688 bytes
Fixed Size                  1217884 bytes
Variable Size              65890980 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> create spfile='/dev/raw/spfile+ASM.ora' from pfile='/oradisk/app01/oracle/scripts/av/bug4631662/asmpfile.ora';

File created.

SQL> shutdown immediate;
ASM diskgroups dismounted
ASM instance shutdown
SQL> spool off

Rollbacking the changes :

In case that rollback is required the following steps are required:
1. Shutdown both database instances
2. Shutdown both ASM instances
3. Startup the ASM instance #1 using the backup  pfile
asm_pfile_backup.ora
4. Recreate the ASM spfile
5. Shutdown ASM instance #1
6. Startup both ASM instances with the recreated spfile
7. Startup Database instance #1 using the backup pfile
database_pfile_backup.ora
8. recreate the database spfile
9. Shutdown database instance #1
10. Startup both instances with the recreated spfile.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.