Saturday, November 10, 2007

ORACLE RECOVERY MANAGER(RMAN)




ORACLE RECOVERY MANAGER (RMAN)

Recovery Manager (RMAN) is an Oracle utility that can back up, restore, and recover database files. This product is a feature of the Oracle database server and does not require separate installation.

It greatly simplifies backing up, restoring, and recovering the databases. RMAN reduces the complexity of backup and recovery. RMAN can determine what needs to be backed up or restored.

Why Should we use RMAN ?

RMAN has the,
Ø Ability to perform incremental backups.
Ø Ability to recover one block of a datafile.
Ø Ability to perform the backup and restore with parallelization.
Ø Ability to automatically delete archived redo logs after they are backed up.
Ø Ability to automatically backup the control file and the SPFILE.
Ø Ability to restart a failed backup without having to start from the beginning.
Ø Ability to verify the integrity of the backup.
Ø Ability to test the restore process without having to actually perform the restore.

RMAN Architecture :

In oracle RMAN comprises of RMAN EXECUTABLE (this could be present and fired even through client side), TARGET DATABASE (This is the database which needs to be backed up) and RECOVERY CATALOG (Recovery catalog is optional otherwise backup details are stored in target database controlfile.)

The RMAN environment consists of the utilities and databases that play a role in backing up our data. At a minimum, the environment for RMAN must include the following:


  1. The target database. This is the database to be backed up by RMAN.

  2. The RMAN client is a command-line-oriented database client, much like SQL*Plus, with its own command syntax. From the RMAN client you can issue RMAN commands and some SQL statements to perform and report on backup and recovery operations.

Some environments will also use these optional components:


  1. Flash recovery area: This is a disk location in which the database can store and manage files related to backup and recovery.

  2. Media management software: Software provided by media manager vendors which is required for RMAN to interface with backup devices such as tape drives.

  3. Recovery catalog database: A separate database schema used to record RMAN activity against one or more target database

RMAN maintains metadata about the target database and its backup and recovery operations in the RMAN repository. Among other things, RMAN stores information about its own configuration settings, the target database schema, archived redo logs, and all backup files on disk or tape.

The primary store for RMAN repository data is always the control file of the target database. The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter controls how long backup records are kept in the control file before those records are re-used to hold information about more recent backups. Another copy of the RMAN repository data can also be saved in the recovery catalog. Using a recovery catalog preserves RMAN repository information if the control file is lost, making it much easier to restore and recover following the loss of the control file

RMAN uses server sessions on the target database instance to perform all backup, restore and recovery operations. Each server session used by RMAN is known as an RMAN channel. A channel can be either a disk channel, used for backup tasks that perform disk I/O, or an sbt channel, which is used to interact with media managers.

Recovery Manager Command Syntax:




























































CommandPurpose
"@" Run a command file.
"@@"Run a command file in the same directory as another command file that is currently running. The @@ command differs from the @ command only when run from within a command file.
"ALLOCATE CHANNEL"Establish a channel, which is a connection between RMAN and a database instance.
"ALLOCATE CHANNEL FOR MAINTENANCE"Allocate a channel in preparation for issuing maintenance commands such as DELETE.
"allocOperandList"A subclause that specifies channel control options such as PARMS, FORMAT, and MAXOPENFILES.
"ALTER DATABASE" Mount or open a database.
"archivelogRecordSpecifier"Specify a range of archived redo logs files.
"BACKUP"Back up a database, tablespace, datafile, archived log, or backup set.
"BLOCKRECOVER"Recover an individual data block or set of data blocks within one or more datafiles.
"CATALOG"Add information about a datafile copy, archived redo log, or control file copy to the repository.
"CHANGE"Mark a backup piece, image copy, or archived redo log as having the status UNAVAILABLE or AVAILABLE; remove the repository record for a backup or copy; override the retention policy for a backup or copy.
"completedTimeSpec"Specify a time range during which the backup or copy completed.
"CONFIGURE"Configure persistent RMAN settings. These settings apply to all RMAN sessions until explicitly changed or disabled.
"CONNECT"Establish a connection between RMAN and a target, auxiliary, or recovery catalog database.
"connectStringSpec"Specify the username, password, and net service name for connecting to a target, recovery catalog, or auxiliary database. The connection is necessary to authenticate the user and identify the database.
"COPY"Create an image copy of a datafile, control file, or archived redo log.
"CREATE CATALOG"Create the schema for the recovery catalog.
"CREATE SCRIPT"Create a stored script and store it in the recovery catalog.
"CROSSCHECK"Determine whether files managed by RMAN, such as archived logs, datafile copies, and backup pieces, still exist on disk or tape.
"datafileSpec"Specify a datafile by filename or absolute file number.
"DELETE"Delete backups and copies, remove references to them from the recovery catalog, and update their control file records to status DELETED.
"DELETE SCRIPT"Delete a stored script from the recovery catalog.
"deviceSpecifier"Specify the type of storage device for a backup or copy.
"DROP CATALOG"Remove the schema from the recovery catalog.
"DUPLICATE"Use backups of the target database to create a duplicate database that you can use for testing purposes or to create a standby database.
"EXECUTE SCRIPT"Run an RMAN stored script.
"EXIT"Quit the RMAN executable.
"HOST"Invoke an operating system command-line subshell from within RMAN or run a specific operating system command.
"keepOption"Specify that a backup or copy should or should not be exempt from the current retention policy.
"LIST"Produce a detailed listing of backup sets or copies.
"listObjList"A subclause used to specify which items will be displayed by the LIST command.
"maintQualifier"A subclause used to specify additional options for maintenance commands such as DELETE and CHANGE.
"obsOperandList"A subclause used to determine which backups and copies are obsolete.
"PRINT SCRIPT"Display a stored script.
"QUIT"Exit the RMAN executable.
"recordSpec"A subclause used to specify which objects the maintenance commands should operate on.
"RECOVER"Apply redo logs or incremental backups to a restored backup set or copy in order to update it to a specified time.
"REGISTER"Register the target database in the recovery catalog.
"RELEASE CHANNEL"Release a channel that was allocated with an ALLOCATE CHANNEL command.
"releaseForMaint"Release a channel allocated with an ALLOCATE CHANNEL FOR MAINTENANCE command.
"REPLACE SCRIPT"Replace an existing script stored in the recovery catalog. If the script does not exist, then REPLACE SCRIPT creates it.
"REPORT"Perform detailed analyses of the content of the recovery catalog.
"RESET DATABASE"Inform RMAN that the SQL statement ALTER DATABASE OPEN RESETLOGS has been executed and that a new incarnation of the target database has been created, or reset the target database to a prior incarnation.
"RESTORE"Restore files from backup sets or from disk copies to the default or a new location.
"RESYNC"Perform a full resynchronization, which creates a snapshot control file and then copies any new or changed information from that snapshot control file to the recovery catalog.
"RUN"Execute a sequence of one or more RMAN commands, which are one or more statements executed within the braces of RUN.
"SEND"Send a vendor-specific quoted string to one or more specific channels.
"SET"
Make the following session-level settings:

· Control whether RMAN commands are displayed in the message log
· Set the DBID when restoring a control file or server parameter file
· Specify new filenames for restored datafiles
· Specify a limit for the number of permissible block corruptions
· Override default archived redo log destinations
· Specify the number of copies of each backup piece
· Determine which server session corresponds to which channel
· Control where RMAN searches for backups when using an Oracle Real Application Clusters configuration
· Override the default format of the control file autobackup
"SHOW"Displays the current CONFIGURE settings.
"SHUTDOWN"Shut down the target database. This command is equivalent to the SQL*Plus SHUTDOWN command.
"SPOOL"Write RMAN output to a log file.
"SQL"Execute a SQL statement from within Recovery Manager.
"STARTUP"Start up the target database. This command is equivalent to the SQL*Plus STARTUP command.
"SWITCH"Specify that a datafile copy is now the current datafile, that is, the datafile pointed to by the control file. This command is equivalent to the SQL statement ALTER DATABASE RENAME FILE as it applies to datafiles.
"untilClause"A subclause specifying an upper limit by time, SCN, or log sequence number. This clause is usually used to specify the desired point in time for an incomplete recovery.
"UPGRADE CATALOG"Upgrade the recovery catalog schema from an older version to the version required by the RMAN executable.
"VALIDATE"Examine a backup set and report whether its data is intact. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksums to verify that the contents can be successfully restored.




Creating ,Configuring the Recovery Catalog and Registering the target Database:

The Target database :STAR
The Recovery catalog :RECCAT

Part1: Creating the Recovery catalog.

Step1: Create a database (RECCAT) for storing RMAN Recovery Catalog.

Step2: Create a new Tablespace (RMAN_TBS) for the recovery catalog in the database.

Step3: Create a schema user to host the repository schema in the recovery catalog.
SQL> Create user rman_user identified by xxxx
Default Tablespace rman_tbs
Temporary Tablespace temp
Quota unlimited on rman_tbs
Quota unlimited on indx;

Step4: Grant the needed privileges for the schema user in the recovery catalog.
SQL>Grant general_user,Recovery_Catalog_Owner to rman_user;

Step5: Create The Catalog Using RMAN
$rman catalog rman_user/xxxx@reccat
RMAN>create catalog tablespace rman_tbs;

Part2: Registering the Target database

Step1: Find out whether the target database is already registered with the Recovery catalog
$sqlplus
SQL>connect rman_user/xxxx@reccat
SQL>select * from rc_database;
(The result set will show all the registered databases with this recovery catalog.)

Step2: Create a user in the Target database to backup the database.
SQL> Create user rman_user identified by xxxx
Default Tablespace rman_tbs
Temporary Tablespace temp
Quota unlimited on rman_tbs
Quota unlimited on indx;

Step3: Grant the needed privileges for the user in the target database.
SQL>Grant create session,sysdba to rman_user;

Step4: Connect from the target database to register the database with the recovery catalog.
$export ORACLE_SID=star
$rman target rman_user/xxxx@star Catalog rman_user/xxxx@reccat
or
$rman target=/ catalog rman_user/xxxx@reccat
RMAN>register database;

Part3: Verify the target database registeration with the Recovery Catalog

Step1: Connect to the Recovery catalog to find out whether the target database is properly registered.
$sqlplus
SQL>connect rman_user/xxxx@reccat
SQL>select * from rc_database;
(The result set will show all the registered target databases with this recovery catalog.)

Unregister the Target database from The Recovery Catalog:

Step1: Delete the existing backups for the database
$rman catalog rman_user/xxxx@reccat target rman_user/xxxx@star
RMAN> list backup summary;
RMAN> delete backup device type disk;

Step2: Connect to the recovery catalog to get the db_key and db_id for the target database.
$sqlplus
SQL>connect rman_user/xxxx@reccat
SQL>select db_key,dbid from rc_database where name ='STAR';

Step3: Unregister the target database from the Recovery Catalog
SQL>EXECUTE dbms_rcvcat.unregisterdatabase(db_key, db_id);

Drop the Recovery Catalog:
Step1: Connect to the Recovery catalog database
$rman catalog rman_user/xxxx@reccat

Step2: Issue the drop catalog command twice
RMAN> DROP CATALOG;
RMAN> DROP CATALOG;

Configuring the RMAN Environment

We can configure persistent settings in the RMAN environment. The configuration setting is done once and is used by RMAN to perform all subsequent operations.

To see the existing configurations issue the following command in the target database.

$rman target rman_user/xxxx@star catalog rman_user/xxxx@reccat
RMAN> show all;


The script to configure the RMAN environment:
#!/usr/bin/ksh
###########################################################################
###Script for configuring RMAN
### Author: Anbu Subramanian
### Date : 10-10-2007
### Run this script to set up the persistent RMAN configurations.
### $1 is the Recovery catalog instance name.
### $2 is the Target instance name to be backed up.
###########################################################################
. /oracle/.102boraenv
mailto:Dba_team=
dtstamp=`date +%Y%m%d`
logdir=/u01/oracle/rman_backups/logs/
filedir=/u01/oracle/rman_backups/backupsets/
opwd=`cat /u01/oracle/rman_backups/scripts/.infodb.pw`
export opwd
opwd1=`cat /u01/oracle/rman_backups/scripts/.infodb.pw1`
export opwd1
export ORACLE_SID=$2
$ORACLE_HOME/bin/rman target $opwd1@$2 catalog $opwd@$1 log=$logdir/$2_rman_config_$dtstamp.log <<>
run {
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE disk PARALLELISM 2;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$filedir/%d_%T_%F_cfabk.dbf';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$filedir/$2_cf_snap_$dtstamp.dbf';
CONFIGURE MAXSETSIZE TO UNLIMITED;
}
EXIT;
EOF
/usr/bin/cat $logdir/$2_rman_config_$dtstamp.log grep 'ERROR MESSAGE STACK FOLLOWS' > /dev/null
if [ $? = 0 ]
then
mailx -s "ERROR IN CONFIGURING RMAN IN $2 on $dtstamp " $Dba_team< $logdir/$2_rman_config_$dtstamp.log else mailx -s "SUCCESS IN CONFIGURING RMAN IN $2 on $dtstamp" $Dba_team< $logdir/$2_rman_config_$dtstamp.log fi Note: Make sure the password file .infodb.pw and .infodb.pw1 exits.

Backingup the databases:
The BACKUP command is used to back up databases to the configured default device by using the configured channels. RMAN stores its backups in backup sets. A backup set is a logical structure that contains one or more backup pieces, which are the physical files containing the data. The database backups can be a Full backup or Incremental backups.

Back up Strategy
Sunday----------Full
Monday---------Differential
Tuesday--------Cumulative
Wednesday---Differential
Thursday-------Cumulative
Friday-----------Differential
Saturday-------Cumulative

Full backup: A full backup of a database is a backup that includes every used data block in all the datafiles in the target database.

The script for full backup for an instance running in archived log mode,
#!/usr/bin/ksh
###################################################################################
### Script for RMAN full database backup with Archivelog and current control file
### Author: Anbu Subramanian
### Date : 10-10-2007
### $1 is the Recovery catalog instance name.
### $2 is the Target instance name to be backed up. ###################################################################################
. /oracle/.102boraenv
mailto:Dba_team=
dtstamp=`date +%Y%m%d`
filedir=/u01/oracle/rman_backups/backupsets/
logdir=/u01/oracle/rman_backups/logs/
opwd=`cat /u01/oracle/rman_backups/scripts/.infodb.pw`
export opwd
opwd1=`cat /u01/oracle/rman_backups/scripts/.infodb.pw1`
export opwd1
export ORACLE_SID=$2
$ORACLE_HOME/bin/rman target $opwd1@$2 catalog $opwd@$1 log=$logdir/$2_rman_full_$dtstamp.log <<>
run {
configure snapshot controlfile name to '$filedir/$2_full_cf_snap_$dtstamp.dbf';
allocate channel ch1 device type disk;
crosscheck backup;
crosscheck archivelog all;
resync catalog;
backup
full
skip inaccessible
tag $2_$dtstamp_full_db_backup
format '$filedir/%d_%u_%s_%p_%T_full_df.dbf'
database ;
release channel ch1;
allocate channel ch1 device type disk;
backup
tag $2_$dtstamp_full_cfile_backup
format '$filedir/%d_%u_%s_%p_%T_full_cf.dbf'
current controlfile;
release channel ch1;
allocate channel ch1 device type disk;
sql 'alter system archive log current';
backup
tag $2_$dtstamp_full_archlogs_backup
archivelog all format '$filedir/%d_%u_%s_%p_%T_full_arc.dbf' delete all input;
release channel ch1;
allocate channel ch1 device type disk;
crosscheck backup;
crosscheck archivelog all;
restore database validate;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;
report unrecoverable;
report obsolete orphan;
resync catalog;
release channel ch1;
}
EXIT;
EOF
/usr/bin/cat $logdir/$2_rman_full_$dtstamp.log grep 'ERROR MESSAGE STACK FOLLOWS' > /dev/null
if [ $? = 0 ]
then
mailx -s "ERROR IN $2 RMAN FULL backup on $dtstamp " $Dba_team< $logdir/$2_rman_full_$dtstamp.log
else
mailx -s "SUCCESS IN $2 RMAN FULL BACKUP on $dtstamp" $Dba_team< $logdir/$2_rman_full_$dtstamp.log
fi
/usr/bin/gzip $filedir/*.dbf


The script for full backup for an instance running in Noarchived log mode.
#!/usr/bin/ksh
#######################################################################################
###Script for RMAN full database backup and current control file (NO Archivelog mode)
### Author: Anbu Subramanian
### Date : 10-30-2007
### $1 is the Recovery catalog instance name.
### $2 is the Target instance name to be backed up. ########################################################################################
. /oracle/.102boraenv
mailto:Dba_team=
dtstamp=`date +%Y%m%d`
filedir=/u01/oracle/rman_backups/backupsets/
logdir=/u01/oracle/rman_backups/logs/
opwd=`cat /u01/oracle/rman_backups/scripts/.infodb.pw`
export opwd
opwd1=`cat /u01/oracle/rman_backups/scripts/.infodb.pw1`
export opwd1
export ORACLE_SID=$2
$ORACLE_HOME/bin/rman target $opwd1@$2 catalog $opwd@$1 log=$logdir/$2_Noarch_rman_full_$dtstamp.log <<>
run
{
shutdown immediate
startup mount
configure snapshot controlfile name to '$filedir/$2_full_cf_snap_$dtstamp.dbf';
allocate channel ch1 device type disk;
crosscheck backup;
resync catalog;
backup
full
skip inaccessible
tag $2_$dtstamp_noarch_full_db_backup
format '$filedir/%d_%u_%s_%p_%T_full_df.dbf'
database;
release channel ch1;
allocate channel ch1 device type disk;
backup
tag $2_$dtstamp_noarch_full_cfile_backup
format '$filedir/%d_%u_%s_%p_%T_full_cf.dbf'
current controlfile;
release channel ch1;
allocate channel ch1 device type disk;
crosscheck backup;
restore database validate;
delete noprompt obsolete;
delete noprompt expired backup;
report unrecoverable;
report obsolete orphan;
resync catalog;
release channel ch1;
alter database open;
}
EXIT;
EOF
/usr/bin/cat $logdir/$2_Noarch_rman_full_$dtstamp.log grep 'ERROR MESSAGE STACK FOLLOWS' > /dev/null
if [ $? = 0 ]
then
mailx -s "ERROR IN $2 RMAN FULL backup on $dtstamp " $Dba_team< $logdir/2_Noarch_rman_full_$dtstamp.log
else
mailx -s "SUCCESS IN $2 RMAN FULL BACKUP on $dtstamp" $Dba_team< $logdir/2_Noarch_rman_full_$dtstamp.log
fi
/usr/bin/gzip $filedir/*.dbf

Incremental Backups:
RMAN incremental backups back up only datafile blocks that have changed since a specified previous backup. Incremental backups are of two types
1.Differential Backup and 2.Cumlative Backup.
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0. A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0. 1.

The script for differential backup for an instance running on Archive log mode.
#!/usr/bin/ksh
#################################################################################
### Script for RMAN differntial backup with Archivelog and current control file
### Author: Anbu Subramanian
### Date : 10-10-2007
### $1 is the Recovery catalog instance name.
### $2 is the Target instance name to be backed up. ##################################################################################
. /oracle/.102boraenv
Dba_team='anbu@win.com'
dtstamp=`date +%Y%m%d`
filedir=/u01/oracle/rman_backups/backupsets/
logdir=/u01/oracle/rman_backups/logs/
opwd=`cat /u01/oracle/rman_backups/scripts/.infodb.pw`
export opwd
opwd1=`cat /u01/oracle/rman_backups/scripts/.infodb.pw1`
export opwd1
export ORACLE_SID=$2
$ORACLE_HOME/bin/rman target $opwd1@$2 catalog $opwd@$1 log=$logdir/$2_rman_diff_$dtstamp.log <<>
run
{
configure snapshot controlfile name to '$filedir/$2_diff_cf_snap_$dtstamp.dbf';
allocate channel ch1 device type disk;
crosscheck backup;
crosscheck archivelog all;
resync catalog;
backup
incremental level 1
skip inaccessible
tag $2_$dtstamp_diff_db_backup
format '$filedir/%d_%u_%s_%p_%T_diff_df.dbf'
database;
release channel ch1;
allocate channel ch1 device type disk;
backup
tag $2_$dtstamp_diff_controlfile_backup
format '$filedir/%d_%u_%s_%p_%T_diff_cf.dbf'
current controlfile;
release channel ch1;
allocate channel ch1 device type disk;
sql 'alter system archive log current';
backup
tag $2_$dtstamp_diff_archlogs_backup
archivelog all format '$filedir/%d_%u_%s_%p_%T_diff_arc.dbf' delete all input;
release channel ch1;
allocate channel ch1 device type disk;
crosscheck backup;
crosscheck archivelog all;
restore database validate;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;
report unrecoverable;
report obsolete orphan;
resync catalog;
release channel ch1;
}
EXIT;
EOF
/usr/bin/cat $logdir/$2_rman_diff_$dtstamp.log grep 'ERROR MESSAGE STACK FOLLOWS' > /dev/null
if [ $? = 0 ]
then
mailx -s "ERROR IN $2 RMAN DIFFERENTIAL backup on $dtstamp " $Dba_team< $logdir/$2_rman_diff_$dtstamp.log
else
mailx -s "SUCCESS IN $2 RMAN DIFFERNTIAL BACKUP on $dtstamp" $Dba_team< $logdir/$2_rman_diff_$dtstamp.log
fi
/usr/bin/gzip $filedir/*.dbf

The script for Cumulative backup for an instance running on Archive log mode.
#!/usr/bin/ksh
###############################################################################
### Script for RMAN CUMLATIVE backup with Archivelog and current control file
### Author: Anbu Subramanian### Date : 10-10-2007
### $1 is the Recovery catalog instance name passed as a parameter.
### $2 is the Target instance name to be backed up. ################################################################################
. /oracle/.102boraenv
mailto:Dba_team=
dtstamp=`date +%Y%m%d`
filedir=/u01/oracle/rman_backups/backupsets/
logdir=/u01/oracle/rman_backups/logs/
opwd=`cat /u01/oracle/rman_backups/scripts/.infodb.pw`
export opwd
opwd1=`cat /u01/oracle/rman_backups/scripts/.infodb.pw1`
export opwd1
export ORACLE_SID=$2
$ORACLE_HOME/bin/rman target $opwd1@$2 catalog $opwd@$1 log=$logdir/$2_rman_cum_$dtstamp.log <<>
run
{
configure snapshot controlfile name to '$filedir/$2_cum_cf_snap_$dtstamp.dbf';
allocate channel ch1 device type disk;
resync catalog;
crosscheck backup;
crosscheck archivelog all;
backup
incremental level 1 cumulative
skip inaccessible
tag $2_$dtstamp_cum_db_backup
format '$filedir/%d_%u_%s_%p_%T_cum_df.dbf'
database;
release channel ch1;
allocate channel ch1 device type disk;
backup
tag $2_$dtstamp_cum_cfile_backup
format '$filedir/%d_%u_%s_%p_%T_cum_cf.dbf'
current controlfile;
release channel ch1;
allocate channel ch1 device type disk;
sql 'alter system archive log current';
backup
tag $2_$dtstamp_cum_arclogs_backup
archivelog all format '$filedir/%d_%u_%s_%p_%T_cum_arc.dbf' delete all input;
release channel ch1;
allocate channel ch1 device type disk;
crosscheck backup;
crosscheck archivelog all;
restore database validate;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;
report unrecoverable;
report obsolete orphan;
resync catalog;
release channel ch1;
}
EXIT;
EOF
/usr/bin/cat $logdir/$2_rman_cum_$dtstamp.log grep 'ERROR MESSAGE STACK FOLLOWS' > /dev/null
if [ $? = 0 ]
then
mailx -s "ERROR IN $2 RMAN CUMLATIVE backup on $dtstamp " $Dba_team< $logdir/$2_rman_cum_$dtstamp.log
else
mailx -s "SUCCESS IN $2 RMAN CUMLATIVE BACKUP on $dtstamp" $Dba_team< $logdir/$2_rman_cum_$dtstamp.log
fi
/usr/bin/gzip $filedir/*.dbf

The script for differential backup for an instance running on Noarchive log mode.
#!/usr/bin/ksh
#####################################################################################################
### Script for RMAN LEVEL1 DIFFERENTIAL database backup and current control file (NO Archivelog mode)
### Author: Anbu Subramanian
### Date : 10-30-2007
### $1 is the Recovery catalog instance name passed as a parameter.
### $2 is the Target instance name to be backed up. ######################################################################################################
. /oracle/.102boraenv
mailto:Dba_team=
dtstamp=`date +%Y%m%d`
filedir=/u01/oracle/rman_backups/backupsets/
logdir=/u01/oracle/rman_backups/logs/
opwd=`cat /u01/oracle/rman_backups/scripts/.infodb.pw`
export opwd
opwd1=`cat /u01/oracle/rman_backups/scripts/.infodb.pw1`
export opwd1
export ORACLE_SID=$2
$ORACLE_HOME/bin/rman target $opwd1@$2 catalog $opwd@$1 log=$logdir/$2_Noarch_rman_diff_$dtstamp.log <<>
run
{
shutdown immediate
startup mount
configure snapshot controlfile name to '$filedir/$2_diff_cf_snap_$dtstamp.dbf';
allocate channel ch1 device type disk;
crosscheck backup;
resync catalog;
backup
incremental level 1
skip inaccessible
tag $2_$dtstamp_noarch_diff_db_backup
format '$filedir/%d_%u_%s_%p_%T_diff_df.dbf'
database;
release channel ch1;
allocate channel ch1 device type disk;
backup
tag $2_$dtstamp_noarch_diff_cfile_backup
format '$filedir/%d_%u_%s_%p_%T_diff_cf.dbf'
current controlfile;
release channel ch1;
allocate channel ch1 device type disk;
crosscheck backup;
restore database validate;
delete noprompt obsolete;
delete noprompt expired backup;
report unrecoverable;
report obsolete orphan;
resync catalog;
release channel ch1;
alter database open;
}
EXIT;
EOF
/usr/bin/cat $logdir/$2_Noarch_rman_diff_$dtstamp.log grep 'ERROR MESSAGE STACK FOLLOWS' > /dev/null
if [ $? = 0 ]
then
mailx -s "ERROR IN $2 RMAN DIFFERENTIAL backup on $dtstamp " $Dba_team< $logdir/$2_Noarch_rman_diff_$dtstamp.log
else
mailx -s "SUCCESS IN $2 RMAN DIFFERENTIAL BACKUP on $dtstamp" $Dba_team< $logdir/$2_Noarch_rman_diff_$dtstamp.log
fi
/usr/bin/gzip $filedir/*.dbf


The script for Cumulative backup for an instance running on Noarchive log mode.
#!/usr/bin/ksh
#########################################################################################
### Script for RMAN LEVEL1 CUMULATIVE database backup and current control file (NO Archivelog mode)
### Author: Anbu Subramanian
### Date : 10-30-2007
### $1 is the Recovery catalog instance name passed as a parameter.
### $2 is the Target instance name to be backed up. ##########################################################################################
. /oracle/.102boraenv
mailto:Dba_team=
dtstamp=`date +%Y%m%d`
filedir=/u01/oracle/rman_backups/backupsets/
logdir=/u01/oracle/rman_backups/logs/
opwd=`cat /u01/oracle/rman_backups/scripts/.infodb.pw`
export opwd
opwd1=`cat /u01/oracle/rman_backups/scripts/.infodb.pw1`
export opwd1
export ORACLE_SID=$2
$ORACLE_HOME/bin/rman target $opwd1@$2 catalog $opwd@$1 log=$logdir/$2_Noarch_rman_cum_$dtstamp.log <<>
run
{
shutdown immediate
startup mount
configure snapshot controlfile name to '$filedir/$2_cum_cf_snap_$dtstamp.dbf';
allocate channel ch1 device type disk;
crosscheck backup;
resync catalog;
backup
incremental level 1 cumulative
skip inaccessible
tag $2_$dtstamp_noarch_cum_db_backup
format '$filedir/%d_%u_%s_%p_%T_cum_df.dbf'
database;
release channel ch1;
allocate channel ch1 device type disk;
backup
tag $2_$dtstamp_noarch_cum_cfile_backup
format '$filedir/%d_%u_%s_%p_%T_cum_cf.dbf'
current controlfile;
release channel ch1;
allocate channel ch1 device type disk;
crosscheck backup;
restore database validate;
delete noprompt obsolete;
delete noprompt expired backup;
report unrecoverable;
report obsolete orphan;
resync catalog;
release channel ch1;
alter database open;
}
EXIT;
EOF
/usr/bin/cat $logdir/$2_Noarch_rman_cum_$dtstamp.log grep 'ERROR MESSAGE STACK FOLLOWS' > /dev/null
if [ $? = 0 ]
then
mailx -s "ERROR IN $2 RMAN CUMULATIVE backup on $dtstamp " $Dba_team< $logdir/$2_Noarch_rman_cum_$dtstamp.log else mailx -s "SUCCESS IN $2 RMAN CUMULATIVE BACKUP on $dtstamp" $Dba_team< $logdir/$2_Noarch_rman_cum_$dtstamp.log fi /usr/bin/gzip $filedir/*.dbf
Recovery Scenorios for databases running on Archivelog Mode:
Important Note: Backup the database after any type of recovery.
Case 1: Recovery from corrupted or missing datafile (NON SYSTEM tablespace data files).(Database is in use)
This scenario deals with a situation where a datafile has gone missing, or is corrupted beyond repair. The database is still open and in use. When we try to create an object we get, SQL> create table test as select * from dba_objects;
create table test as select * from dba_objects
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/oracle/oradata/star/users01.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

Recovery steps:
Step1: Connect to the target database using RMAN
$export ORACLE_SID=star
$rman target / catalog rman_user/xxxx@reccat

Step2: Make the affected tablespace offline
RMAN> sql 'alter tablespace USERS offline immediate';

Step3: Restore the datafile from the backup.
RMAN> restore datafile 4;

Step4: Recover the tablespace from the backup.
RMAN> recover tablespace USERS;

Step 5: Make the affected tablespace online.
RMAN> sql 'alter tablespace USERS online';

Step 6: Restart the database.
RMAN> shutdown immediate;
RMAN> startup

Case 2: Recovery from corrupted or missing datafile (NON SYSTEM tablespace data files). (Database is not in use)

This scenario deals with a situation where a datafile has gone missing, or is corrupted beyond repair.
The database is not in use and not starting up properly.

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 2030880 bytes
Variable Size 230687456 bytes
Database Buffers 377487360 bytes
Redo Buffers 2162688 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/u01/oracle/oradata/star/pubdata01.dbf'

The error message tells us that file# 9 is missing. Note that although the startup command has failed, the database is in the mount state.
Thus, the database control file, which is also the RMAN repository can be accessed by the instance and by RMAN.
We can recover the missing datafile by using the RMAN from the existing RMAN backup.
The database must be mounted before any datafile recovery can be done.

Recovery steps:
Step1: Connect to the target database using RMAN
$export ORACLE_SID=star
$rman target / catalog rman_user/xxxx@reccat

Step2: Restore the missing or corrupted datafile
RMAN> restore datafile 9;

Step3: Recover the datafile
RMAN> recover datafile 9;

Step4: Open database for general use
RMAN> alter database open;

Case 3: Recovery from corrupted or missing datafile (SYSTEM Tablespace) while in use

When we try to login, we get the following error as,
$ sqlplus

SQL> connect username@star
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oracle/oradata/star/system01.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3


Error accessing package DBMS_APPLICATION_INFO
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
Connected.
SQL>

Recovery steps:
Step1: Shutdown the database
$export ORACLE_SID=star
$sqlplus
SQL> connect / as sysdba
SQL> shutdown abort

Step2: Connect to the database using RMAN
$export ORACLE_SID=star
$rman target / catalog rman_user/xxxx@reccat

Step3: Restore database
RMAN> restore database;

Step4: Recover database
RMAN> recover database;

Step5: Open database for general use
SQL> alter database open;

Case4: Recovery from block corruption
It is possible to recover corrupted blocks using RMAN backups. When we query a table we get,

SQL> connect username/xxxx@star
Enter password:
Connected.
SQL>

SQL> select count(*) from test_table;
select count(*) from test_table
*
ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 2015)
ORA-01110: data file 4: '/u01/oracle/oradata/star/users01.dbf'

Recovery steps:
Since we know the file and block number, we can perform block level recovery using RMAN.

Step1: Connect to the database using RMAN
$export ORACLE_SID=star
$rman target / catalog rman_user/xxxx@reccat

Step2: Recover the corrupted block
RMAN> blockrecover datafile 4 block 2015;

Now we should be able to query the table from SQLPlus session.

A couple of important points regarding block recovery:

a.Block recovery can only be done using RMAN.
b.The entire database can be open while performing block recovery.
c.Check all database files for corruption. This is important - there could be other corrupted blocks. Verification of database files can be
done using RMAN or the dbverify utility. To verify using RMAN simply do a complete database backup with default settings. If RMAN detects
block corruption, it will exit with an error message pointing out the guilty file/block.

Case5: All members of a log group lost.
In this case an incomplete recovery is the best we can do. We will lose all transactions from the missing log and all subsequent logs.

We see something like the below error message in the alert.log file
Fri Nov 2 15:12:20 2007
Errors in file /u01/oracle/admin/star/bdump/star_arc1_5775.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/star/redo01.log'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Recovery setps:
Step1: Get the first_change value for the lost log group
SQL> select first_change# from v$log where group#=1 ;

FIRST_CHANGE#
-------------
2069354

The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last SCN stamped in the previous log is 2069353
(FIRST_CHANGE#-1). This is the highest SCN that we can recover to. In order to do the recovery we must first restore ALL datafiles to this
SCN, followed by recovery (also up to this SCN). This is an incomplete recovery, so we must open the database resetlogs after we're done.

Step2: Shutdown the database
SQL> shutdown immediate

Step3:Connect to the database using RMAN
$export ORACLE_SID=star
$rman target / catalog rman_user/xxxx@reccat

Step4: restore until first_change-1
RMAN> restore database until scn 2069353;

Step5: Recover the database until first_change-1
RMAN> recover database until scn 2069353;

Step6:Open the database with resetlogs
RMAN> alter database open resetlogs;

Step7:Restart the database
SQL> shutdown immediate
SQL> startup

Note: Backup immediately.

The following points should be noted:
a. The entire database must be restored to the SCN that has been determined by querying v$log.
b. All changes beyond that SCN are lost.
c. This method of recovery should be used only if we are sure that we cannot do better. Be sure to multiplex our redo logs, and (space permitting) our archived logs.
d. The database must be opened with RESETLOGS, as a required log has not been applied. This resets the log sequence to zero, thereby rendering all prior backups worthless.
e.Therefore, the first step after opening a database RESETLOGS is to take a fresh backup.
Note that the RESETLOGS option must be used for any incomplete recovery.

Case 6: Recovery from missing or corrupted control file: (A multiplexed copy of the control file is available) On startup Oracle must read the control file in order to find out where the datafiles and online logs are located. Oracle expects to find control files at locations specified in the CONTROL_FILE initialisation parameter. The instance will fail to mount the database if any one of the control files are missing or corrupt. A brief error message will be displayed, with further details recorded in the alert log. The exact error message will vary depending on what has gone wrong.

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 2030880 bytes
Variable Size 230687456 bytes
Database Buffers 377487360 bytes
Redo Buffers 2162688 bytes
ORA-00205: error in identifying control file, check alert log for more info

In alert log file, we may see
ALTER DATABASE MOUNT
Wed Oct 31 11:36:51 2007
ORA-00202: control file: '/u01/oracle/oradata/star/control01.ctl'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
Wed Oct 31 11:36:54 2007
ORA-205 signalled during: ALTER DATABASE MOUNT...

Recovery steps:
Step1.Shutdown the database
Step2.Copy the good control file to the corrupted or missing control file
Step3.Start the database.

Case7: Recovery from missing or corrupted control file (All control files lost)

In this case we have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all
logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be
restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all
intervening logs are available

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 2030880 bytes
Variable Size 230687456 bytes
Database Buffers 377487360 bytes
Redo Buffers 2162688 bytes
ORA-00205: error in identifying control file, check alert log for more info

Recovery steps:
Step1:Get the dbid for the database from the recovery catalog
SQL> select dbid from rc_database where name =’STAR’;

Step2: Connect to the target database using RMAN
$export ORACLE_SID=star
$rman target / catalog rman_user/xxxx@reccat

Step3: Set the DBID
RMAN> set dbid 2582160548

Step4:Start the database nomount
RMAN> STARTUP FORCE NOMOUNT;

Step5:Restore the controlfile
RMAN> RESTORE CONTROLFILE;

Step6: Mount the database
RMAN> ALTER DATABASE MOUNT;

Step7: Restore the Database
RMAN> restore database;

Step8. Recover the database
RMAN> recover database;

Step9. Open the database with resetlogs
RMAN> alter database open resetlogs;

Several points to be noted
1.Recovery using a backup controlfile should be done only if a current control file is unavailable.
2. All datafiles must be restored from backup. This means the database will need to be recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
3.After recovery using a backup controlfile, all temporary files associated with locally-managed tablespaces may be no longer available.Create the TEMP tablespaces after the recovery if needed.
4.As with any database recovery involving RESETLOGS, take a fresh backup immediately.

Step10. Add the tempfile to the TEMP tablespace
SQL>alter tablespace temp add tempfile '/u01/oracle/oradata/star/temp01.dbf';

Case 8: The Real disaster (Everything is lost)
Recovery steps:
Step1: Build the database server.
We need a server to host the database, so the first step is to acquire or build the new machine. The main point to keep in mind is that the replacement server should, as far as possible, be identical to the old one.
a. The same disk layout and directory structure.
b. The operating system environment should be the same as the original, right up to service pack and patch level.

Step2: Restore backup from tape.
a. Copy the backup files to the new server from tape.

Step3: Install Oracle software.
a. Install the same version of Oracle as was on the destroyed server. The version number should match right down to the patch level, so this may be
a multi-step process involving installation followed by the application of one or more patchsets and patches.
b. Create a listener using the Network Configuration Assistant. Ensure that it has the same name and listening ports as the original listener.

Step4: Create directory structure for database files
a. After software installation is completed, create all directories required for datafiles, (online and archived) logs, control files ,dump file locations and backups.
All directory paths should match those on the original server. This, though not mandatory, saves additional steps associated with renaming files during recovery.

Step5: Create an Oracle Password File for the Auxiliary Instance
$orapwd file=/opt/oracle/102/dbs/orapwstar password=xxxxx entries=5

Step6: Restore spfile from backup.
$export ORACLE_SID=star
$rman target /
RMAN> Startup nomount;
RMAN>restore spfile from '/u01/backsets/STAR_8vj06sn8_287_1_20071104_full_df.dbf';
RMAN> startup force nomount;

Step7: Restore Control file from backup.
RMAN>restore controlfile from '/u01/backupsets/STAR_90j06snv_288_1_20071104_full_cf.dbf';
RMAN> shutdown immediate;

Step8: Restore all datafiles.
RMAN> startup mount;
RMAN> restore database;

Step9: Recover database
RMAN>recover database;

Step10: Open the database with reserlogs
RMAN>alter database open resetlogs;

Step11: Restart the database
RMAN>shutdown immediate;
RMAN>startup;

Case 9: Duplicate the existing database

We can use the RMAN DUPLICATE command to create a duplicate database from backups of the target database (primary database) while retaining the original target database. The duplicate database can be identical to the target database or contain only a subset of the tablespaces in the target database. The target site and the duplicate site can be on separate hosts or on the same host.
For the duplication to work, we must connect RMAN to both the target (primary) database and an auxiliary instance(duplicate) started in MOUNT mode. Allocate at least one auxiliary channel on the auxiliary instance. The principal work of the duplication is performed by the auxiliary channel, which starts a server session on the duplicate host. This channel then restores the necessary backups of the primary database and uses them to create the duplicate database, and initiates recovery.

Step1: Copy the RMAN backup files to the the auxiliary instance. Make sure the files are in the same directory path as the target database.

Create the auxiliary database by following step2, step3 and step4.

Step2: Create an Oracle Password File for the Auxiliary Instance
$orapwd file=orapwstar password=xxxx entries=5

Step3: Establish Oracle Net Connectivity to the Auxiliary Instance
# listener.ora Network Configuration File: /opt/oracle/102/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/102)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = star)
(ORACLE_HOME = /opt/oracle/102)
(SID_NAME = star)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
---------------------------------------------------------------------------------------------------
# Addto the tnsnames.ora
star =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = star)
)
)

# Reload listener
$lsnrctl reload

Step 4: Create an Initialization Parameter File (PFILE) for the Auxiliary Instance
*.db_name='star'
*.control_files='/u01/oracle/oradata/star/control01.ctl','/u01/oracle/oradata/star/control02.ctl','/u01/oracle/oradata/star/control03.ctl'
Note: The block size for the auxiliary database must match that of the target database.

Step5: Connect to the auxillary instance and create the SPFILE
$ export ORACLE_SID=star
$sqlplus
SQL> connect / as sysdba
SQL> create spfile from pfile;

Step6: Start the Auxiliary database in nomount mode.
$export ORACLE_SID=star
$sqlplus
SQL>connect / as sysdba
SQL>startup force nomount;

Step7: Run the below commands from the Target database
$rman TARGET rman_user/xxxx@star CATALOG rman_user/xxxx@reccat AUXILIARY SYS/xxxx@staraux

RUN
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
DUPLICATE TARGET DATABASE TO star
NOFILENAMECHECK;
}

Note: After duplicating the database, add the needed init.ora parameters in the pfile of the duplicate database and restart and recreate the spfile.

II. Recovery Scenarios for databases running in Noarchive log Mode

In NOARCHIVELOG mode, the only option is typically to restore from the most recent backup without applying redo.
Restore of a database running in NOARCHIVELOG mode is very similar to restore of a database in ARCHIVELOG mode.
The main differences are:
1.Only cold backups (that is, backups created when the database was shut down normally) can be used in restoring a database in NOARCHIVELOG mode.
2. Media recovery is not possible, because there are no archived logs.

A limited form of restore and recovery is possible for NOARCHIVELOG databases if the backup strategy for the database includes incremental backups.
The incremental backups (which, like the full backup of a NOARCHIVELOG database must be created when the database is shut down) can be applied to a full database
backup to apply recent changes up to the time of the incremental backup. We can do a incomplete media recovery only.


Case 1: Recovering from complete database loss with Recovery catalog (Noarchive log Mode).

Step1: Copy the backup files to the database server.

Step2: Restore the spfile from the backup.
$export ORACLE_SID=star
$rman target rman_user/xxxx@star catalog rman_user/xxxx@reccat
RMAN>startup force nomount;
RMAN>restore spfile from '/u01/backupsets/STAR_99j09dd8_297_1_20071105_cum_df.dbf';

Step3: Restart the database in Nomount state.
RMAN> shutdown immediate;

Step4: Restore the control file:
RMAN>restore controlfile from '/u01/backupsets/STAR_9aj09ddk_298_1_20071105_cum_cf.dbf';

Step 5: Mount the database
RMAN>sql 'alter database mount';

Step 6: Restore the database
RMAN>restore database;

Step 7: Recover the database with noredo.
RMAN>recover database noredo;

Step 8: Open the database in Resetlogs
RMAN>sql 'alter database open resetlogs';

Step9: Restart the database.
RMAN>shutdown immediate;
RMAN>startup


Case 2: Recovering from complete database loss with No Recovery catalog (Noarchive log Mode).

Step1: Copy the backup files to the database server.

Step 2: Restore the spfile from the backup.
$export ORACLE_SID=star
$rman target/
RMAN>startup force nomount;
RMAN>restore spfile from '/u01/backupsets/STAR_9dj09gpb_301_1_20071105_full_df.dbf';

Step 3: Restart the database in Nomount state.
RMAN>shutdown immediate
RMAN>startup nomount;

Step 4: Restore the controlfile from the backup
RMAN>restore controlfile from '/u01/backupsets/STAR_9ej09gpo_302_1_20071105_full_cf.dbf';

Step 5: Mount the database
RMAN>sql 'alter database mount';

Step 6: Restore database
RMAN>restore database;

Step 7: Recover the database with noredo.
RMAN>recover database noredo;

Step 8: Open the database in Resetlogs
RMAN>sql 'alter database open resetlogs';

Step9 : Restart the database.
RMAN>shutdown immediate
RMAN>startup
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)