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;
/
