ORACLE RAC KSH SCRIPT

#!/bin/ksh
#############################
# Put program variable here
#
db1=orcl
db2=RMAN
###############################
while [ : ] ; do
while [ : ] ; do
echo ' ';
echo '*******************************************';
echo ' M E N U ';
echo '*******************************************';
echo ' 1. Stop Cluster '
echo ' 2. Stop database '
echo ' 3. Stop instance '
echo ' 4. Start database '
echo ' 5. Start instance '
echo ' 6. Start Cluster '
echo ' 7. Cluster status '
echo ' 8. Databases status '
echo ' 9. OCFS2 status '
echo ' 10. Show OCR backups '
echo ' 11. Show RMAN backups'
echo ' 99. EXIT '
echo '*******************************************';
echo ' ';
read reply
case $reply in
1)
print ' +---------------------------+'
print ' | STOP CLUSTER |'
print ' +---------------------------+'
print '*** You choose to stop Oracle cluster services. ***'
print '*** By stopping Oracle cluster services all available database will be stop ***'
print 'To continue press [Y/y]'
read ans
if [[ $ans == @(Y|y) ]]; then
srvctl stop database -d $db1 -o immediate
srvctl stop database -d $db2 -o immediate
/etc/init.d/init.crs stop
$ORA_CRS_HOME/bin/crs_stat -t
fi
break ;;
2)
print ' +---------------------------+'
print ' | STOP DATABASE |'
print ' +---------------------------+'
print '*** You choose to stop Oracle database , this action will stop all instances in all nodes ***'
print 'To continue press [Y/y]'
read ans
if [[ $ans == @(Y|y) ]]; then
print '\n Enter the database name \c'
read db
srvctl stop database -d $db -o immediate
fi
break ;;
3)
print ' +---------------------------+'
print ' | STOP INSTANCE |'
print ' +---------------------------+'
print '*** You choose to stop Oracle instance on this node ***'
print 'To continue press [Y/y]'
read ans
if [[ $ans == @(Y|y) ]]; then
print '\n Enter the database name \c'
read db
print '\n Enter the instance name \c'
read instance
srvctl stop instance -d $db -i $instance
fi
break ;;
4)
print ' +---------------------------+'
print ' | START DATABASE |'
print ' +---------------------------+'
print '\n Enter the database name \c'
read db
srvctl start database -d $db -o open
break ;;
5)
print ' +---------------------------+'
print ' | DATABASE STATUS |'
print ' +---------------------------+'
print '\n Enter the database name \c'
read db
print '\n Enter the instance name \c'
read instance
srvctl start instance -d $db -i $instance
break ;;
6)
print ' +---------------------------+'
print ' | START CLUSTER |'
print ' +---------------------------+'
print ' Starting cluster components , and all avaible databases '
/etc/init.d/init.crs start
srvctl start database -d $db1 -o open
srvctl start database -d $db2 -o open
srvctl start service -d ORCL -s TEST
srvctl start service -d RMAN -s RMAN_CATALOG
$ORA_CRS_HOME/bin/crs_stat -t
break ;;
7)
print ' +---------------------------+'
print ' | CLUSTER STATUS |'
print ' +---------------------------+'
$ORA_CRS_HOME/bin/crs_stat -t
break ;;
8)
print ' +---------------------------+'
print ' | DATABASE STATUS |'
print ' +---------------------------+'
if ps -aef | grep pmon | grep $db1 > /dev/null ; then
print "Database $db1 is Up."
else
print "Database $db1 is down."
exit 1
fi
if ps -aef | grep pmon | grep $db2 > /dev/null ; then
print "Database $db2 is Up."
else
print "Database $db2 is down."
exit 1
fi
break ;;
9)
print ' +---------------------------+'
print ' | OCFS2 STATUS |'
print ' +---------------------------+'
/etc/init.d/o2cb status
break ;;
10) print 'Show OCR backups'
break ;;
99) exit
break ;;
esac
done
done

Backup RAC Database

select resource_name
current_utilization,
max_utilization
from v$resource_limit
where resource_name like 'g%s_%';


--Client TNS names must be point on Vip-address

srvctl stop database -d ORCL -o immediate

srvctl start database -d ORCL -o open


Create services

srvctl add service -d ORCL -s TEST -r ORCL1 -a ORCL2

start the service

srvctl start service -d ORCL -s TEST
srvctl start service -d RMAN -s RMAN_CATALOG

TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.domain.local)(PORT = 1526))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.domain.local)(PORT = 1526))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
############################################################################################################

Backup RAC Database using RMAN with catalog
================================================

As sys create the rman's tablespace and rman user
=================================================
create tablespace RMAN
datafile '/u01/oradata/oracle/databases/RMAN/rman_01.dbf' size 512M autoextend on
segment space management auto;

create user rman identified by rman
default tablespace RMAN
quota unlimited on RMAN;

grant recovery_catalog_owner to rman;
grant connect, resource to rman;


Create RAM Service
====================

srvctl add service -d RMAN -s RMAN_CATALOG -r RMAN1 -a RMAN2

srvctl start service -d RMAN -s RMAN_CATALOG


For each node add the following
=================================
RMAN_CATALOG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.dl.mlsi.gov.cy)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.dl.mlsi.gov.cy)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RMAN_CATALOG)
)
)


test service
============
sqlplus rman/rman@RMAN_CATALOG




Create catalog
===============
rman catalog rman/rman@RMAN_CATALOG

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 8 12:40:03 2007

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

connected to recovery catalog database

RMAN> create catalog tablespace "RMAN";


Register database with catalog
===============================
C:\>rman target sys/oracle@CPSWEB

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 8 14:49:10 2007

connected to target database: CPS (DBID=3507563536)
connected to target database: CPSWEB (DBID=3789508129)



RMAN> connect catalog rman/rman@RMAN_CATALOG

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete


RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 7;

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;


/************

alter system set db_recovery_file_dest='/u01/oradata/oracle/backup/flash_recovery_area' scope=both;

********/




where backupScript.sh :

#!/bin/ksh
####################################################################################################
# BACKUP DATABASES WITH RMAN
####################################################################################################
sid=`print $RAC_NODE_ID`
export ORACLE_SID=cpsinst$sid
rman TARGET / catalog rman/rman@RMAN_CATALOG cmdfile=/u01/oradata/oracle/backup/scripts/scriptForBackup.rman
export ORACLE_SID=cpsweb$sid
rman TARGET / catalog rman/rman@RMAN_CATALOG cmdfile=/u01/oradata/oracle/backup/scripts/scriptForBackup.rman
####################################################################################################
# EXPORT DATABASES WITH DATAPUMP #
# Comments:
# create directory BACKUP_DIR as '/u01/oradata/oracle/backup/exports/CPSWEB/day1';
####################################################################################################




sid=`print $RAC_NODE_ID`
export ORACLE_SID=cpsinst$sid
print ' Cleaning export area for CPS database '
print '-----------------------------------------'
rm -rf /u01/oradata/oracle/backup/exports/CPS/day5/*.dmp
mv /u01/oradata/oracle/backup/exports/CPS/day4/*.dmp /u01/oradata/oracle/backup/exports/CPS/day5
mv /u01/oradata/oracle/backup/exports/CPS/day3/*.dmp /u01/oradata/oracle/backup/exports/CPS/day4
mv /u01/oradata/oracle/backup/exports/CPS/day2/*.dmp /u01/oradata/oracle/backup/exports/CPS/day3
mv /u01/oradata/oracle/backup/exports/CPS/day1/*.dmp /u01/oradata/oracle/backup/exports/CPS/day2
expdp "'/ as sysdba'" full=y directory=BACKUP_DIR dumpfile=CPS_full.dmp
sid=`print $RAC_NODE_ID`
export ORACLE_SID=cpsweb$sid
print ' Cleaning export area for CPSWEB database '
print '-----------------------------------------'
rm -rf /u01/oradata/oracle/backup/exports/CPSWEB/day5/*.dmp
mv /u01/oradata/oracle/backup/exports/CPSWEB/day4/*.dmp /u01/oradata/oracle/backup/exports/CPSWEB/day5
mv /u01/oradata/oracle/backup/exports/CPSWEB/day3/*.dmp /u01/oradata/oracle/backup/exports/CPSWEB/day4
mv /u01/oradata/oracle/backup/exports/CPSWEB/day2/*.dmp /u01/oradata/oracle/backup/exports/CPSWEB/day3
mv /u01/oradata/oracle/backup/exports/CPSWEB/day1/*.dmp /u01/oradata/oracle/backup/exports/CPSWEB/day2
expdp "'/ as sysdba'" full=y directory=BACKUP_DIR dumpfile=CPSWEB_full.dmp
####################################################################################################
# BACKUP VOTING #
# Voting disk contains and manages information of all the node memberships #
####################################################################################################
dd if=/u01/oradata/oracle/cluster/CRS/crs0.ora of=/u01/oradata/oracle/backup/voting-disk/crs0.ora
dd if=/u01/oradata/oracle/cluster/CRS/crs1.ora of=/u01/oradata/oracle/backup/voting-disk/crs1.ora
dd if=/u01/oradata/oracle/cluster/CRS/crs2.ora of=/u01/oradata/oracle/backup/voting-disk/crs2.ora
####################################################################################################
# BACKUP OCR #
# Files that manage the cluster and RAC Configuration #
####################################################################################################
print 'last OCR backup are:'
ocrconfig -showbackup
# add crontab job as root on both nodes
# for node 1
# 0 20 * * 1-5 /disk01/oracle/product/10.2.0/db_1/bin/ocrconfig -export /u01/oradata/oracle/backup/flash_recovery_area/scripts/ocr-backup/ocr-export-node1.ora -s online
# for node 2
# 0 20 * * 1-5 /disk01/oracle/product/10.2.0/db_1/bin/ocrconfig -export /u01/oradata/oracle/backup/flash_recovery_area/scripts/ocr-backup/ocr-export-node2.ora -s online


where scriptForBackup.rman :

resync catalog;
run
{
allocate channel ch1 type disk;
backup incremental level 0 as backupset tag=full_backup (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup archivelog all tag=archives_backup
delete all input;
release channel ch1;
}
crosscheck backupset ;
crosscheck archivelog all;
delete noprompt obsolete;
restore validate database;
list backup;
exit;




/***** THIS DONE IN RMAN DATABASE *******/

/*
begin dbms_scheduler.drop_job (job_name=>'daily_backup');
end;
/
begin dbms_scheduler.drop_program(program_name => 'backup_database');
end;
/
begin dbms_scheduler.drop_schedule(schedule_name =>'weekly_backup_at_20pm'); end;
/
*/


begin
dbms_scheduler.create_schedule
(
schedule_name => 'weekly_backup_at_20pm',
repeat_interval => 'FREQ=WEEKLY; INTERVAL=1; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=20',
comments => 'schedule to run weekly at 20 pm'
);
dbms_scheduler.create_program
(
program_name => 'backup_database',
program_type => 'EXECUTABLE',
program_action => '/u01/oradata/oracle/backup/scripts/backupScript.ksh',
enabled => TRUE,
comments => 'Backup database using rman and then backup rman database via hot backup.'
);
dbms_scheduler.create_job
(
job_name=>'daily_backup',
program_name =>'backup_database',
schedule_name=> 'weekly_backup_at_20pm',
enabled => true,
comments => 'Backup database using rman and then backup.'
);
end;
/

Returns The First Day Of A Month

CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS
vMo VARCHAR2(2);
vYr VARCHAR2(4);
BEGIN
vMo := TO_CHAR(value_in, 'MM');
vYr := TO_CHAR(value_in, 'YYYY');
RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
/
REF : http://www.psoug.org/reference/date_func.html

Returns The Number Of Seconds Between Two Date-Time Values

CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);

BEGIN
-- Get Julian date number from first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

-- Get Julian date number from second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

-- Get seconds since midnight from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

-- Get seconds since midnight from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/

REF : http://www.psoug.org/reference/date_func.html