RMAN Guide for Oracle Real Application Clusters 10gR2;
A short guide to backup and recovery using RMAN in RAC 10gR2
RMAN is a utility that backs up, restores, and recovers Oracle databases. It can be used with or without the central information repository called a recovery catalog. If a recovery catalog is not used, RMAN uses the database’s control file to store information necessary for backup and recovery operations.
There are two levels of backup, first a backup is written to the Flash Recovery Area (FRA), and second a backup is performed to tape using a 3rd party product. For more information on tape backups, please refer to the Oracle RMAN documentation.
Database Parameters
The ‘CONTROL_FILE_RECORD_KEEP_TIME’ specifies the minimum number of days before a reusable record in the control file can be reused. If a recovery catalog is used, then it’s a good idea to keep the same amount of information in the control file.
The following will keep 7 days of information in the control file.
SQL>alter system set CONTROL_FILE_RECORD_KEEP_TIME=7
scope=both;
The Fast-Start Fault Recovery feature reduces the time required for cache recovery, and makes the recovery bounded and predictable by limiting the number of dirty buffers and the number of redo records generated between the most recent redo record and the last checkpoint
SQL>ALTER SYSTEM SET FAST_START_MTTR_TARGET=30 scope=both;
Monitoring Cache Recovery with V$INSTANCE_RECOVERY
The V$INSTANCE_RECOVERY view displays the current recovery parameter settings. You can also use statistics from this view to determine which factor has the greatest influence on check pointing.
Flash Recovery Area Configuration
Using a flash recovery area simplifies the ongoing administration of the database by automatically naming recovery-related files, retaining them as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed to restore the database and space is needed for some other backup and recovery-related purpose.
Increasing the size of the FRA
After starting SQL*Plus, connect to the database, set the size of the flash recovery area. For example, set it to 500 GB:
SQL>alter system set db_recovery_file_dest_size=500g
scope=both sid=’*’;
Configuring a Recovery Window-Based Retention Policy
The RECOVERY WINDOW parameter of the CONFIGURE command specifies the number of days between the current time and the earliest point of recoverability. RMAN does not consider any full or level 0 incremental backup as obsolete if it falls within the recovery window.
Additionally, RMAN retains all archived logs and level 1 incremental backups that are needed to recover to a random point within the window. Run the CONFIGURE RETENTION POLICY command at the RMAN prompt. This example ensures that you can recover the database to any point within the last four days.
RMAN>configure retention policy to recovery window of 7 days;
RMAN does not automatically delete backups rendered obsolete by the recovery window. Instead, RMAN shows them as OBSOLETE in the REPORT OBSOLETE output and in the OBSOLETE column of V$BACKUP_FILES. RMAN deletes obsolete files if you run the DELETE OBSOLETE command.
Once the target database is started, the following RMAN commands configure the retention policy, backup optimization, and the control file autobackup:
RMAN>configure retention policy to redundancy 7;
RMAN>configure backup optimization on;
RMAN>configure controlfile autobackup on;
RMAN Configuration Settings
RMAN>;configure retention policy to redundancy 7 days;
configure backup optimization on;
configure default device type to disk; # default
configure controlfile autobackup on;
configure autobackup format for device type disk
to '/app/oracle/%F';
configure device type disk parallelism 3 backup type to backupset;
configure datafile backup copies for device type disk to 1;
configure archivelog backup copies for device type disk to 1;
configure channel 1 device type disk connect '*';
configure channel 2 device type disk connect '*';
configure channel 3 device type disk connect '*';
configure maxsetsize to unlimited; # default
configure encryption for database off; # default
configure encryption algorithm 'AES128'; # default
configure archivelog deletion policy to none; # default
configure snapshot controlfile name to '/app/oracle/snapcf_DB1.f';Recovering Voting Disks
To restore the backup of your voting disk, issue the dd command. In the following examples, backup_file_name is the name of the voting disk backup file and voting_disk_name is the name of the active voting disk. Issue the following command from the UNIX prompt as the root user:
dd if=backup_file_name of=voting_disk_name
Recovering OCR Disks
As the root user, issue the following command to add an OCR location using either destination_file or disk to designate the target location of the additional OCR. From the UNIX prompt issue the following command as the root user:
ocrconfig -replace ocr destination_file or disk
Database Backups
Backups are performed daily first to the FRA (Flash Recovery Area), from there they are backed up to tape.
#!/bin/ksh
# Script : backupdb
# Author: Mark Young
# Backup database & archived logs required to recover
#it out of ASM to File System
# List backups: crs_stat |grep db|cut -c10-17
usage ()
{
echo "script usage"
echo "backupdb database"
}
# Environment Variables
nodedb=$1
export ORACLE_SID=$nodedb
logfile=`date -u +%d%m%y%H%M%S_$nodedb`
# Remove obsolete backups and backup the database
# Read the environment variables
. /app/oracle/.profile
$ORACLE_HOME/rman target / catalog rman/pass@CATDB <
spool log to '/app/oracle/localtool/log/$nodedb/$logfile.log';
allocate channel for maintenance type disk;
allocate channel for maintenance type sbt;
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
release channel;
run
{
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup database filesperset 1 plus archivelog tag "$logfile";
delete noprompt archivelog all backed up 1 times to device
type disk completed before ‘sysdate-1’ ;
BACKUP CURRENT CONTROLFILE;
}
spool log off;
exit
EOF
# End of scriptThis script will backup each database that is currently open. There is no need to modify the script if new databases are incorporated into the cluster.
The backupall script calls backupdb.sh which performs the relevant procedures to perform the backup of each of the databases using RMAN.
#!/bin/ksh
# Script: backupall
# Author: Mark Young
# Backup all databases in on node1
sp=/app/oracle/src
node=1
x=’crs_stat |grep db|cut -c10-17’
for var in $x; do
echo Backing up $var$node
$sp/backupdb.sh $var$node
done
#end of scriptShutdown and start-up procedures
Mounting the database must be completed prior to any recovery procedure. SRVCTL should be used to shutdown any of the databases. The recovery process only needs to be performed via a single instance.
To stop the database across the entire cluster (this command can be run from any node). Traditional methods can also be used to shutdown and startup instances, however this mostly be performed by srvctl.
dbserver1>srvctl stop database –d MYDATABASE
Mounting the database
Mounting the database can be done via numerous methods:
dbserver1>srvctl start instance –d MYDATABASE –i MYDATABASE1 –o mount
Database Recovery Methods
The following are examples used to perform multiple recovery methods.
Recovery from (Flash Recovery Area) FRA
RMAN>startup force mount;
restore database;
recover database;
sql ‘alter database open’;Recover a tablespace from FRA
RMAN>sql 'alter tablespace users offline';
restore tablespace users;
recover tablespace users;
sql 'alter tablespace users online;Recover a data file from FRA
select file#, ts#, status, name from v$datafile where status=’RECOVERY’;
6 6 RECOVERY +DATA/datafile/users.5053.636640119
RMAN>sql ‘alter database datafile 6 offline’;
restore datafile 6;
recover datafile 6;
sql ‘alter database datafile 6 online’;
Point in time recovery from disk
A typical scenario is that you dropped a table at say noon, and want to recover it. You will have to restore the appropriate data files and do a point-in-time recovery to a time just before noon.
Note: You will lose any transactions that occurred after noon. After you have recovered until noon, you must open the database with resetlogs. This is necessary to reset the log numbers, which will protect the database from having the redo logs that weren’t used be applied. The four incomplete recovery scenarios all work the same:
Recover database until time '2007-12-12:12:00:00';
Recover database until cancel; (you type in cancel to stop)
Recover database until change n;
Recover database until cancel using backup controlfile;When performing an incomplete recovery, the data files must be online.
Perform a select * from v$recover_file to find out if there are any files that are offline. If you were to perform a recovery on a database which has tablespaces offline, and they had not been taken offline in a normal state, you will lose them when you issue the open resetlogs command. This is because the data file needs recovery from a point before the resetlogs option was used.
RMAN>run {
restore database;
recover database until time
"to_date('10-12-2007 16:30:00','DD-MM-YYYY HH24:MI:SS')";
}Point in time recovery from tape
RMAN>List backup summary
RMAN>run
{
allocate channel c1 type sbt_tape;
restore database
recover database until time "to_date('10/DEC/2007
23:08:00','DD/MON/YYYY HH24:MI:SS')";
}
Restore and Mount a Controlfile
Restore a controlfile using the appropriate ‘until’:
RMAN>run
{
restore controlfile from autobackup until time
"TO_DATE('05/DEC/2007 15:40:00','DD/MON/YYYY HH24:MI:SS')";
}
RMAN>sql "alter database mount clone database";Restore Controfile, database and recover
Restore a controlfile then restore and recover the database:
RMAN>run
{
allocate channel d1 type disk;
restore controlfile from
‘+FRA\db1\controlfile\current.3679.643031601’;
sql ‘alter database mount’;
restore database;
recover database;
sql ‘alter database open’;
}Point in time recovery from tape
RMAN>run
{
allocate channel c1 type sbt_tape;
restore controlfile from ‘+FRA\db1\controlfile\current.3679.643031601’;
sql ‘alter database mount’;
restore database;
recover database;
sql ‘alter database open’;
}NOTE: MOUNT CLONE DATABASE forces all datafiles to be put OFFLINE. Just for safety reasons.
Recovering Individual Data Blocks
RMAN can recover individual corrupted datafile blocks. When RMAN performs a complete scan of a file for a backup, any corrupted blocks are listed in V$DATABASE_BLOCK_CORRUPTION. Corruption is usually reported in alert logs, trace files or results of SQL queries. Use “blockrecover” to repair all corrupted blocks:
RMAN>blockrecovery corruption list;
Individual blocks can be recovered as shown below:
RMAN>blockrecover datafile 7 block 233, 235 datafile 4 block 102;
Restore database that’s no longer in the recovery catalog
The current configuration consists of 2 backups of the control file. These will need to be restored from tape to perform a recovery where the information is not contained in the recovery catalogue.
Restore the control file from tape
Mount the database with the restored control file and perform the recovery.
RMAN>run
{
allocate channel c1 type sbt_tape;
set until time
"to_date('16/02/2008 16:30:00','DD/MM/YYYY HH24:MI:SS')";
restore database;
recover database;
}
Recent Comments