Oracle DBA Daily Experiences

This script will format the new 11G crsctl status resource in a tabular format.

#!/usr/bin/ksh
#
# Sample 11g CRS resource status query script
#
# Description:
# – Returns formatted version of crsctl status resource, in tabular
# format, with the complete rsc names and filtering keywords
# – The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# – $ORA_CRS_HOME should be set in your environment
#
#
# HISTORY ##################################
#
# 28/06/2010 – Alex Lima – UPgraded to 11G commands.
#
################################################################
export CRS_HOME=/usr/grid/product/11.2.0

RSC_KEY=$1
QSTAT=-u
AWK=/usr/bin/awk # if not available use /usr/xpg4/bin/awk

# Table header:echo “”
echo “”

$AWK \
‘BEGIN {printf “%-30s %-70s %-18s\n”, “HA Resource”, “State”, “Target”;
printf “%-30s %-70s %-18s\n”, “———–”, “—–”, “——”;}’

# Table body:
$CRS_HOME/bin/crsctl status resource | $AWK \
‘BEGIN { FS=”=”; state = 0; }
$1~/NAME/ && $2~/’$RSC_KEY’/ {appname = $2; state=1};
state == 0 {next;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
state == 3 {printf “%-30s %-70s %-18s\n”, appname, appstate, apptarget; state=0;}’
echo “”

On 10R2 if the Oracle binaries are installed with the RAC, one can’t use those binaries to start Oracle Instance if CRS is not running, so in case when one needs to start the instance without CRS one needs to rebuild the oracle and it’s libraries to start the instance.

To turn off RAC
# link the oracle libraries
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_off
# rebuild oracle
$ cd $ORACLE_HOME/bin
$ relink oracle

To turn on RAC
# link the oracle libraries
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
# rebuild oracle
$ cd $ORACLE_HOME/bin
$ relink oracle

To start the instance:
# check status of instance
$ $CRS_HOME/bin/crs_stat

# seen cases when CRS says OFFLINE but pmon is still running so double checking here
$ ps -ef | grep <instance_name> | grep pmon

# start one of the instance on one of the nodes if not running
$ srvctl start database -d <database name> -i <instance name>

To shutdown the instance:

# verify instance running
$ ps -ef | grep <instance_name> | grep pmon

# shutdown one of the instance on one of the nodes
$ srvctl stop database -d <database name> -i <instance name>

# check status of instance to make sure it’s down
$ $CRS_HOME/bin/crs_stat

# verify instance not running
$ ps -ef | grep <instance_name> | grep pmon

Not cleaning up a failed CRS install can cause problems like node reboots.
Follow these steps to clean up a failed CRS install:

1. Run the rootdelete.sh script then the rootdeinstall.sh script from the
$ORA_CRS_HOME/install directory on any nodes you are removing CRS from. Running
these scripts should be sufficent to clean up your CRS install. If you have
any problems with these scripts please open a service request.

If for some reason you have to manually remove the install due to problems
with the scripts, continue to step 2:

2. Stop the Nodeapps on all nodes:

srvctl stop nodeapps -n <node name>

3. Prevent CRS from starting when the node boots. To do this issue the following
as root:

Sun:

rm /etc/init.d/init.cssd
rm /etc/init.d/init.crs
rm /etc/init.d/init.crsd
rm /etc/init.d/init.evmd
rm /etc/rc3.d/K96init.crs
rm /etc/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

Linux:

rm /etc/oracle/*
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -f /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

HP-UX:

rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc2.d/K960init.crs
rm /sbin/rc2.d/K001init.crs
rm /sbin/rc3.d/K960init.crs
rm /sbin/rc3.d/S960init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

HP Tru64:

rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K96init.crs
rm /sbin/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

IBM AIX:

rm /etc/init.cssd
rm /etc/init.crs
rm /etc/init.crsd
rm /etc/init.evmd
rm /etc/rc.d/rc2.d/K96init.crs
rm /etc/rc.d/rc2.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -Rf /etc/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

4. If they are not already down, kill off EVM, CRS, and CSS processes or reboot
the node:

ps -ef | grep crs
kill <crs pid>
ps -ef | grep evm
kill <evm pid>
ps -ef | grep css
kill <css pid>

Do not kill any OS processes, for example icssvr_daemon process !

5. If there is no other Oracle software running (like listeners, DB’s, etc…),
you can remove the files in /var/tmp/.oracle or /tmp/.oracle. Example:

rm -f /var/tmp/.oracle

or

rm -f /tmp/.oracle

6. Remove the ocr.loc
Usually the ocr.loc can be found at /etc/oracle

7. De-install the CRS home in the Oracle Universal Installer

8. Remove the CRS install location:

rm -Rf <CRS Install Location>/*

9. Clean out the OCR and Voting Files with dd commands. Example:

dd if=/dev/zero of=/dev/rdsk/V1064_vote_01_20m.dbf bs=8192 count=2560
dd if=/dev/zero of=/dev/rdsk/ocrV1064_100m.ora bs=8192 count=12800

If you placed the OCR and voting disk on a shared filesystem, remove them.

If you are removing the RDBMS installation, also clean out any ASM disks if
they have already been used.

10.If you would like to re-install CRS, follow the steps in the RAC Installation manual.

Move from single instance to RAC

1- Install CRS
2- Install new RDBMS binaries rac enable
3- Make sure there is enough space for the new online redo logs for new nodes
4- Make modifications to the pfile for RAC configuration
5- Add the listener configurations to tnsnames file in both nodes
6- Run rconfig or follow instruction on note 208375.1

Some of the steps from note and performed on the PMM RAC conversion:

GROUP# MEMBER
———- ————————————
3 /u03/oradata/PMMPRD/redo03a.log
3 /u04/oradata/PMMPRD/redo03b.log
2 /u03/oradata/PMMPRD/redo02a.log
2 /u04/oradata/PMMPRD/redo02b.log
1 /u03/oradata/PMMPRD/redo01a.log
1 /u04/oradata/PMMPRD/redo01b.log

6 rows selected.
- Add this to tnsnames.ora file in both nodes
-
LISTENER_PMM =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = calpocpmm01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = calpocpmm02-vip)(PORT = 1521))
)
alter database add logfile thread 2
group 4 (‘/u03/oradata/PMMPRD/redo04a.log’,'/u04/oradata/PMMPRD/redo04b.log’) size 500M,
group 5 (‘/u03/oradata/PMMPRD/redo05a.log’,'/u04/oradata/PMMPRD/redo05b.log’) size 500M,
group 6 (‘/u03/oradata/PMMPRD/redo06a.log’,'/u04/oradata/PMMPRD/redo06b.log’) size 500M;

alter database enable public thread 2;
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
‘/u14/oradata/PMMPRD/undotbs_02_210.dbf’ SIZE 200M ;

mkdir -p /u01/app/oracle/product/10.2.0/pmmprd/admin/PMMPRD/adump
mkdir -p /u01/app/oracle/product/10.2.0/pmmprd/admin/PMMPRD/bdump
mkdir -p /u01/app/oracle/product/10.2.0/pmmprd/admin/PMMPRD/cdump
mkdir -p /u01/app/oracle/product/10.2.0/pmmprd/admin/PMMPRD/udump
mkdir -p /u12/oradata/PMMPRD/flash_recovery_area
SQL> create spfile=’/u02/oradata/spfile_dir/spfilePMMPRD.ora’ from pfile=’/home/oracle/pfile.ora’;

Edit initPMMPRD1.ora and initPMMPRD2.ora

SPFILE=’/u02/oradata/spfile_dir/spfilePMMPRD.ora’
Add database to the CRS
srvctl add database -d PMMPRD -o $ORACLE_HOME
srvctl add instance -d PMMPRD -i PMMPRD1 -n calpmmpoc01
srvctl add instance -d PMMPRD -i PMMPRD2 -n calpmmpoc02