Topics
Incremental Backups
This feature allows backups to be performed that contain only database
blocks that have changed since the last full or incremental backup. This
can greatly reduce the size of backups. It can also reduce the time required
to do a restoration. A typical scenario would be to perform a weekly full
backup and daily incrementals.
Corrupt Block Detection
When a backup is executed with Recovery Manager, the database blocks
are read and corruption is automatically detected. By default, when a corrupt
block error is detected by Recovery Manager, an error message will be displayed
(or written to a log file if not running interactively), and the backup
terminates. This error message will identify the file that the corrupt
block is in. By setting the value of maxcorrupt to something greater
than zero (the default) and re-executing the backup or copy, more information
on the corrupt block will be put in the views V$BACKUP_CORRUPTION or V$COPY_CORRUPTION.
Fractured Block Detection
Another benefit of an Oracle server process being used when executing
Recovery Manager is that an Oracle server process can detect when a data
block is fractured. A data block is considered fractured when the control
information in the header does not match the information stored in the
footer. A fractured block can occur during an online backup because the
Oracle data blocks are not always the same size as the operating system
block size.
Oracle data blocks are referred to as logical blocks while operating system blocks are called physical blocks. In most environments, the logical block contains multiple physical blocks. Therefore, while the operating system is backing up a database, Oracle can be writing into multiple physical blocks at the same time the operating system is reading a subset of the physical blocks written to by Oracle. To resolve this problem, Oracle implemented the hot backup mode , which tells Oracle to copy the entire logical block to the redo log buffer when a data block is modified. When the database is not in hot backup mode, only the before and after image of the modified row is written to the redo buffer.
Since the server process can detect fractured blocks and will reread the block if a fracture is detected, the hot backup mode is not necessary using Recovery Manager. Although Oracle8 does support the following statements:
File Multiplexing
Another benefit of using Recovery Manager is that Oracle server processes
can operate on one or more datafiles and/or tablespaces concurrently. The
server process will read a set of data blocks from each datafile and intermingle
the data blocks from separate data files into the backup set file (backup
piece). This means that a backup set can consist of one or more files,
and each file in the backup set can contain data blocks from multiple database
datafiles.
For example, assume a backup set will contain data files 1 – 5 from the database. Assume during the backup operation, the filesperset option was set to 5. Therefore, the backup set will contain only one file. However, this backup set file will contain all the used data blocks from data files 1 – 5 in the database. Furthermore, the data blocks from each data file will not be segregated within the backup set file, but will be mixed with data blocks from the other four data files.
File multiplexing aids in the performance of the backup. A backup set can consist of datafiles from separate disks. This helps to distribute the read operations so that an I/O bottleneck does not occur while performing backups.
Automatic Parallelization
Backup and restore operations are automatically parallelized. This
is supported with both disk and tape backup/restore operations. The parallelization
can be implemented by using the allocate channel and filesperset
Recovery
Manager commands. Allocate channel is used to assign I/O devices,
and the filesperset is used to define how many database files will
be written to a single backup output file (called a backup set). The total
backup time can be reduced by taking advantage of parallelism, when
the necessary hardware is available.
Backup Usability and Restore Validation Reporting
Recovery Manager has several features that allow a DBA to look at both
a running database and backups that have been performed on that database.
This information can answer such questions as:
To determine if a database has not been backed up within the last 7
days, enter:
report need backup days 7 database;
Backups can become obsolete for various reasons including: a backup
of a database file that no longer exists, or there is a more recent backup
of an existing file. A report of obsolete backups can be used to determine
what could be deleted from backup media to free up space. To find backups
that are obsolete because there are at least 2 more recent backups, enter:
report obsolete redundancy 2;
Recovery Manager has the capability of doing a "dry run" restoration
of a database. This can be valuable in determining that all files needed
for the restoration are available from backup media. The following is an
example of validating restoration for an entire database:
run {
allocate channel d1 type disk;
restore database validate;
}
This command checks for the database files needed for restoration, but not the archive log files that may be needed to recover.
Recovery Catalog for Centralized Operations
The recovery catalog is used to store information regarding backup
and restore operations. The catalog is a schema that should reside in a
database other than those being backed up (target databases). The recovery
catalog can contain information about multiple target databases. The reports
described in the previous section were generated from information stored
in the recovery catalog. The recovery catalog is also used to store scripts
of Recovery Manager commands.
RMAN
Architecture
The production database that you are backing up is called the target.
A separate database, called the recovery catalog, contains information
about datafile and control-file copies, backup sets, archived redo logs,
and other key elements needed for recovery.
RMAN performs two main functions:
· It maintains the RMAN metadata in the control file or the
recovery catalog.
· It communicates with the Oracle database and the operating
system in order to create, restore, and recover backup sets and image copies.
(RMAN writes image copies to disk only, not to tape.)
RMAN creates several client connections, or channels, between the target
database and the backup storage device. RMAN can create backup sets on
disk or directly on tape. To use tape storage, RMAN requires a media manager
If you have more than one database to back up, you can create more than
one recovery catalog and have each database serve as the other’s recovery
catalog. For example, assume there are two production databases, one called
"prd1," and a second called "prd2." You can install the recovery catalog
for "prd1" in the "prd2" database, and the recovery catalog for the "prd2"
database in "prd1." This enables you to avoid the extra space requirements
and memory overhead of maintaining a separate recovery catalog database.
However, this solution is not practical if the recovery catalog databases
for both reside in tablespaces residing on the same physical disk. Typical
recovery catalog space requirements for 1 year are:
| Tablespace Name | Space |
| SYSTEM | 50 MB |
| TEMP | 5 MB |
| ROLLBACK | 5 MB |
| RECOVERY_CATALOG | 10 MB |
| ONLINE LOGS | 1 MB each one |
RMAN
Setup and Configuration
To get the most out of RMAN, you should always use a recovery catalog,
which contains details of all backup-and-recovery-related operations. RMAN
uses the catalog to identify the relationship between backups and database
files, automatically deciding which recovery events will minimize the mean
time to recover (MTTR). If you run RMAN without using a recovery catalog,
it will gather the necessary information from the database's control file
but won't support point-in-time recovery. And if you lose all copies of
your control file you'll be out of luck. That's why unless you have only
one computer running the Oracle software, you should always use a recovery
catalog and store it on totally separate hardware from any of your production
or target instances—preferably in a separate location. You may already
have a suitable Oracle-database instance for this purpose, such as your
Oracle Enterprise Manager or other systems' management repository.
The catalog itself rarely occupies more than 40MB. You can configure
the catalog in two ways:
STEP 1 Modify your Net8 Connnections and Create the Recovery Catalog
Before invoking RMAN, set the NLS_DATE_FORMAT and NLS_LANG environment
variables. Much of the RMAN LIST output is date/time related. It is often
necessary to have this information displayed as accurately as possible
when performing time-based recovery. Example NLS settings:
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
Your TNSNAMES.ORA file should have an entry for both your catalog and
target databases.
For our paper, we will use two databases, the catalog database (RMAN)
and the target database (PROD).
Then, connect to the Oracle instance by using your DBA account, create an account for RMAN, and grant it appropriate rights.
spool setup_rman.log
connect internal
create tablespace RMAN datafile '.......'
size 20M extend on next 20M maxzize 50M;
create user RMAN identified by rman default
tablespace RMAN quota unlimited on RMAN;
alter user RMAN temporary tablespace temp;
grant RECOVERY_CATALOG_OWNER, connect, resource,
dba to RMAN;
connect RMAN/RMAN@RMAN
In Oracle 8i, the catalog is created differently.
Note: This step only applies to Oracle 8i and greater.
From the UNIX shell run:
%export ORACLE_SID=RMAN
rman catalog rman/rman
create catalog;
This will generate the recovery catalog schema in the default tablespace
for RMAN.
In this example, the RMAN user of the RMAN instance is the owner of
the recovery catalog. The RMAN code is platform-independent, with the exception
of names to operating-system directory paths.
STEP 2 Register Your Database
Now you need to register your target database or databases with the
catalog you've created. This populates the catalog with initial information
regarding the configuration of your target database and includes a full
synchronization of the catalog with the target database's control file.
Before invoking RMAN, set the NLS_DATE_FORMAT and NLS_LANG environment
variables. Much of the RMAN LIST output is date/time related. It is often
necessary to have this information displayed as accurately as possible
when performing time-based recovery. Example NLS settings:
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
This sample RMAN session shows the process:
export ORACLE_SID=PROD
rman target / rcvcat rman/rman@RMAN
OR
rman target <qstring> [rcvcat <qstring>
| cmdfile <qstring> |
msglog <qstring> | append | trace <qstring>]
Argument Quoted String Description
TARGET= A connect string containing a userid and password for the database on which Recovery Manager is to operate.
RCVCAT= A connect string that contains a userid and password for the database that contains the recovery catalog (RMAN).
CMDFILE= The name of a file that contains the input commands for RMAN. If this argument is specified, RMAN operates in batch mode; otherwise, RMAN operates in interactive line mode.
MSGLOG= The name of a file where RMAN records commands and output Results. If not specified, RMAN outputs to the screen.
APPEND= This parameters causes the msglog file to be opened in append mode. If this parameter is not specified and a file with the same name as the msglog file already exists, it is overwritten.
TRACE= A file name where RMAN will dump a trace information.
(useful feature for RMAN jobs debugging)
Note: RMAN automatically requests a connection to the target database as SYSDBA. In order to connect to the target database as SYSDBA, you must either:
Be part of the operating system DBA group with respect to the target database. This means that you have the ability to CONNECT INTERNAL to the target database without a password.-or -
Have a password file setup. This requires the use of the "orapwd" command and the initialization parameter "remote_login_passwordfile".
Note: The connect string (for example, @targdb) should be a valid
TNS alias, as specified in the local to the rman utility tnsnames.ora file
Then Register the Database:
register database
Once complete the DBID, DB_NAME, and structure are captured in the recovery
catalog. To verify the registration run:
list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID
CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
38 39
DIEGO 4283267716
YES 19688901 10-MAY-01
Now we have the target database information stored in the recovery catalog.
Notice the current column (CUR) as this becomes important later. When the
database is opened with the RESETLOGS option, the catalog must also be
reset. This is accomplished by running:
reset database;
This command lets RMAN know we have reset the TARGET database after
an incomplete recovery. The next backup is the baseline for the new incarnation
of the TARGET database and the current target would be listed separately
in the list command.
report schema;
--> Shows information about datafiles in that DB
STEP 3 Back Up the Catalog
If you lose your catalog, you can partially regenerate it from the
information in the control file, but you should avoid that predicament
by backing it up. You can use RMAN to back up the catalog by creating a
recovery-catalog schema in one of your other instances, using that as the
catalog for your primary catalog instance. Providing that these two instances
do not share any common resources, this role reversal is free from single
points of failure.
STEP 4 Synchronize All Databases
It's important that your recovery catalog has an up-to-date view of
your database. When you execute RMAN operations on the database, RMAN automatically
synchronizes the catalog with the target. However, depending on the volatility
of your database, you may need to resynchronize the catalog more frequently.
Always include catalog resynchronization as part of any structural database
change (the addition of files, tablespaces, and so on) and ensure that
a resynchronization occurs at intervals less than the init.ora setting
for the parameter CONTROL_FILE_RECORD_KEEP_TIME. To perform this action,
issue the RESYNC CATALOG command from within RMAN.
STEP 5 Start RMAN
Now you are ready to produce your first backups. You can enter RMAN
commands interactively, or you can hold them in RMAN-stored scripts or
operating-system command files
rman
connect target internal/password@PROD
connect rcvcat rman/rman@RMAN
# then enter your RMAN commands here
STEP 6 Create Database Backups
The following code backs up your entire database, including a copy
of the current control file:
run {
# backup the complete database to disk
allocate channel c1 type disk;
backup database format '/backup/archRMAN_%d_%s';
release channel c1;
}
When you back up to disk, you need to tell RMAN where to put the backup files by using the format parameter. The %u variable causes RMAN to generate a unique eight-character name for the backup set. You can use several other variables, such as %d for database name.
So, in three lines of code, you can accomplish a full backup and dynamically identify the database files to be included. RMAN is very flexible, however, and can perform complex backup routines without your having to add much complexity to the code. The following script, for example, performs a full database backup to disk, in two parallel streams (two channels allocated, each of type 'disk'), automatically excluding any data files that support read-only tablespaces:
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
backup
format '/backup/fullRMAN_%d_%s'
filesperset 10
skip readonly
database;
release channel t1;
release channel t2;
}
The filesperset value specifies the maximum number of datafiles that RMAN will write to a backup set. The combination of multiple output channels and files per set govern the operation's parallelism. RMAN backups that include the first data file in the SYSTEM tablespace automatically include a copy of the current control file. To back up the control file explicitly, add the INCLUDE CURRENT CONTROLFILE clause to your backup statement.
list backup; -->
Shows my backups
list copy;
--> Shows my copies
Backing
up in no archivelog mode
Target database is MOUNTED (but not open). If the database is open
and not in archivelog mode, RMAN will generate an error when you attempt
to perform a datafile backup
1. Example of how to back up a complete database
shutdown immediate;
startup force dba;
shutdown immediate
startup mount;
run {
# backup the complete database to disk
allocate channel dev1 type disk;
# or backup database ?
backup
full
tag full_db_sunday_night
format '/oracle/backups/full_%d_%s'
(database);
release channel dev1;
}
Line#
2: Comment line (anything
after the '#' is a comment)
3&9: Open - Close
Channels
5: Full backup (default
if full or incremental not specified)
6: Meaningful string
(<=30 chars)
7: Filename to use
for backup pieces, including substitution variables.
8: Indicates all files
including controlfiles are to be backed up
To view this backup in the catalog, use the following command:
list backup;
2. Example of how to back up a tablespace
run {
allocate channel dev1 type disk;
backup
tag tbs_users_read_only
format '/oracle/backups/tbs_users_t%t_s%s'
(tablespace users);
release channel dev1;
}
Line#
6: Specifying only
the USERS tablespace for backup
To view this tablespace backup in the catalog, use the following command:
list backupset of tablespace users;
If for example the USERS tablespace is going to be put READ ONLY after
being backed up, subsequent full database backups would not need to backup
this tablespace. To cater for this, specify the 'skip readonly' option
in subsequent backups.
3. Example of how to backup individual datafiles
run {
allocate channel dev1 type 'SBT_TAPE';
backup
format '%d_%u'
(datafile '/oracle/dbs/sysbigdb.dbf');
release channel dev1;
}
Line#
2: Allocates
a tape drive using the media manager layer (MML)
Note that no
tag was specified and is therefore null.
To view this tablespace backup in the catalog, use the following command:
list backupset of datafile 1;
4. Copying datafiles
run {
allocate channel dev1 type 'SBT_TAPE';
copy datafile '/oracle/dbs/temp.dbf' to '/oracle/backups/temp.dbf';
release channel dev1;
}
To view this file copy in the catalog, use the following command:
list copy of datafile '/oracle/dbs/temp.dbf';
Copying a datafile is different to backing up a datafile. A datafile
copy is an image copy of the file. A backup of the file creates a backupset.
5. Backing up the controlfile
run {
allocate channel dev1 type 'SBT_TAPE';
backup
format 'cf_t%t_s%s_p%p'
tag cf_monday_night
(current controlfile);
release channel dev1;
}
Note that a database backup will automatically back up the controlfile.
Full
DB Backup
# Resync the catalogue
resync catalog;
# Backup the database
run {
allocate channel 'dev0' type 'SBT_TAPE';
allocate channel 'dev1' type 'SBT_TAPE';
backup
full
tag Full_Oracle_Backup
filesperset 5
format '%d_%t_%s_%p.dbf'
database
;
backup
filesperset 5
format '%d_%t_%s_%p.cf'
current controlfile
;
sql 'alter system archive log current';
backup
filesperset 50
archivelog all format '%d_%t_%s_%p.arch';
release channel dev0;
release channel dev1;
}
Backing
up in archivelog mode
The commands are identical to those in the previous sections,
except that the target database is in archivelog mode.
1.Backing up archived logs
The following script backs up all archive logs:
run {
allocate channel dev1 type disk;
backup
format '/oracle/backups/log_t%t_s%s_p%p'
(archivelog all);
release channel dev1;
}
The following script backs up archive logs from sequence# 90 to 100:
run {
allocate channel dev1 type disk;
backup
format '/oracle/backups/log_t%t_s%s_p%p'
(archivelog from logseq=90 until logseq=100
thread 1);
release channel dev1;
}
The following script backs up all archive logs generated in the past
24 hours. Furthermore it actually deletes the logs after backing them up.
If the backup fails, logs will NOT be deleted:
run {
allocate channel dev1 type disk;
backup
format '/oracle/backups/log_t%t_s%s_p%p'
(archivelog from time 'sysdate-1' all delete
input);
release channel dev1;
}
To view the archive logs in the catalog, use the following command:
list backupset of archivelog all;
2. Backing up the online logs
Online logs CANNOT be backed up using RMAN; they must be archived first.
To do this, you can issue SQL commands from RMAN e.g.
run {
allocate channel dev1 type disk;
sql "alter system archive log current";
backup
format '/oracle/backups/log_t%t_s%s_p%p'
(archivelog from time 'sysdate-1' all delete
input);
release channel dev1;
}
The above script might be run after performing a full 'database open'
backup. It would ensure that all redo to recover the database to a consistent
state would be backed up.
Incremental
backups
If your database is particularly large, you may want to consider making
incremental backups. Unlike incremental exports, which operate at a table
level, RMAN incremental backups back up only changed blocks. This approach
optimizes not only backup times but recovery operations as well, because
RMAN intelligently decides what combination of full, incremental, and archive-log
backups will produce the optimal recovery path.
RMAN uses the concept of multilevel incremental backups, with levels
0, 1, or 2. Level 0 is equivalent to a full backup. An incremental backup
at a level greater than 0 copies only those blocks that have changed since
a previous incremental backup at the same or lower level. For example,
a level-1 incremental backup backs up any blocks that have changed since
the most recent backup at level 0 or 1. A level-2 backup backs up only
blocks that have changed since the last backup at any level. (See the Oracle8
Backup and Recovery Guide for more details.)
To execute an incremental backup, include the incremental keyword and
a level number in the backup statement:
# Resync the catalogue
resync catalog;
# Backup the database
run {
allocate channel t1 type sbt_tape;
backup
incremental level 0
format 'full_inc1_%u'
database;
release channel t1;
}
Another Example:
# Resync the catalogue
resync catalog;
# Backup the database
run {
allocate channel 'dev0' type 'SBT_TAPE';
allocate channel 'dev1' type 'SBT_TAPE';
backup
incremental level 2
cumulative
tag Full_Oracle_Backup
filesperset 5
format '%d_%t_%s_%p.dbf'
database
;
backup
cumulative
filesperset 5
format '%d_%t_%s_%p.cf'
current controlfile
;
sql 'alter system archive log current';
backup
filesperset 10
archivelog all format '%d_%t_%s_%p.arch';
release channel dev0;
release channel dev1;
}
A level N incremental backup backs up blocks that have changed since
the most recent incremental backup at level N or less.
1. Level 0 - the basis of the incremental backup strategy
run {
allocate channel dev1 type disk;
backup
incremental level 0
filesperset 4
format '/oracle/backups/sunday_level0_%t'
(database);
release channel dev1;
}
Line#
4: Level 0 backup
- backups of level > 0 can be applied to this
5: Specifies maximum
files in the backupset
A list of the database backupsets will show the above backup. The 'type' column is marked 'Incremental'; the 'LV' column shows '0'.
2. Example backup strategy using incremental backups
A typical incremental backup cycle would be as follows:
o. Sun night - level 0 backup
performed
o. Mon night - level 2 backup
performed
o. Tue night - level 2 backup
performed
o. Wed night - level 2 backup
performed
o. Thu night - level 1 backup
performed
o. Fri night - level 2 backup
performed
o. Sat night - level 2 backup
performed
If the database suffered a failure on Sat morning and this resulted
in a restore operation, RMAN could recover to the point of failure by restoring
the backups from Sunday, Thursday, and Friday. This is because Thursdays
level 1 backup contains all changes since Sunday, and Friday's level 2
backup contains all changes since Thursday. Whether the database could
be completely recovered would depend on whether archive logging is enabled.
Cumulative
incremental backups
A cumulative incremental backup backs up all blocks that have
changed since the the most recent incremental backup at level N-1 or less
(contrast with non-cumulative incremental backups that backup blocks that
have changed since the the most recent incremental backup at level N or
less). This means that more work is done in performing the backup (duplication
of backup effort), but time may be saved when restoring (potentially fewer
backupsets to restore).
run {
allocate channel dev01 type disk;
backup incremental level 1 cumulative database;
release channel dev01;
}
Scripting
in RMAN
You can integrate RMAN into operating-system command scripts, either
as a call to RMAN with a command file or with in-line RMAN scripts.
It is very easy to create and replace stored scripts with RMAN. E.g.
create script alloc_disk {
# Allocates one disk
allocate channel dev1 type disk;
setlimit channel dev1 kbytes 2097150 maxopenfiles
32 readrate 200;
}
replace script rel_disk {
# releases disk
release channel dev1;
}
replace script backup_db_full {
# Performs a complete backup
execute script alloc_disk;
backup
.....<backup commands here>
execute script rel_disk;
}
The first 2 scripts allocate and deallocate channels respectively. The
alloc_disk script additionally specifies the maximum size of backup pieces
created on this channel (kbytes), the maximum number of input files that
a backup will have open (maxopenfiles), and the maximum number of buffers
per second which will be read from each of the input datafiles.
The 3rd script calls the previously stored scripts either side of performing
a backup.
Example of executing a stored script:
run {
execute script backup_db_full;
}
It is possible to create a job command list in a
flat file and call that script from the O/S command line as an RMAN option.
E.g. to call scripts stored in a file called 'weekly_cold_backup':
% rman <other RMAN options>
cmdfile weekly_cold_backup
This UNIX script, for example, shows how to work around one of RMAN's current shortcomings—its inability to receive variables.
This script shows you how to pass variables from a UNIX Korn-shell script to RMAN.
#!/bin/ksh
# Back up single tablespace to disk or tape
# Process command-line args
TargetConnStr=$1
RcvcatConnStr=$2
TapeOrDisk=$3
tablespace=$4
# need to do some parameter validation here!
${ORACLE_HOME}/bin/rman << EOF
connect target ${TargetConnStr}
connect rcvcat ${RcvcatConnstr}
run {
execute script alloc_${TapeOrDisk}_channel;
backup tablespace ${tablespace}
format '${tablespace}_%u';
execute script rel_${TapeOrDisk}_channel;
}
exit
EOF
if [ $? -ne 0 ]
then
echo "RMAN failed to backup ${tablespace}"
else
echo "${tablespace} backed up OK"
fi
You can store RMAN commands in scripts and hold them in the recovery catalog itself. The following command creates a script for backing up the ACCT tablespace:
replace script backup_acct {
allocate channel c1 type disk;
backup tablespace acct
format 'z:\backups\acct%u';
release channel c1;
}
To execute this script, simply issue the call to run the backup_acct procedure, as shown in the RMAN run command:
run { execute script backup_acct;}
You may want to create a set of standard scripts for routine tasks.
For example, you may have a set of scripts that allocate channels (allocate_1_disk,
allocate_3_ tapes, and so on). You can call these scripts from within other
scripts.
Alternatively, you can write the RMAN commands to operating-system
command files and invoke them either from within RMAN, using the @filename
notation, or on the RMAN command line, using the cmdfile=
argument.
Managing
Archive Logs
Backing up and keeping track of archive log files has always been challenging
for DBAs. How do you know whether ARCH has finished writing to a log? How
soon should you delete the archive logs after backups? Do you restore the
archive logs all at once or one at a time when you need to recover—and
where do you restore them? RMAN provides several features to assist you.
You back up archived redo logs in much the same way you do datafiles.
Use the RMAN backup command and specify criteria indicating the files to
be backed up. Because RMAN works with the contents of the control files
rather than the operating-system directory, it knows which log files are
still being written by ARCH. The following script backs up all complete
archived redo logs in the log_arch_dest directory. (Note that you can use
RMAN and SQL statements in a single piece of code.)
replace script archlog_all {
allocate channel t1 type sbt_tape;
sql "alter system archive log current";
backup archivelog all format 'arch_%u';
release channel t1;
}
You can be more selective when deciding which logs to back up, and you can delete log files once they've been successfully backed up. Selection criteria can include creation date/time, sequence number, or thread number. The following example backs up and then deletes all logs generated more than 48 hours ago:
replace script archlog_old {
allocate channel t1 type sbt_tape;
backup archivelog
until time 'sysdate-2'
all format 'arch_%u'
delete input;
release channel t1;
}
Recovering
from Failure (with Examples)
Backing up the database is the easy part. The real challenge begins
when the "ORA-01113 file needs recovery" message flashes on your console.
Fortunately, using RMAN for recovery is as simple as backing up in the
first place. Just as it takes only a few commands to back up the database,
it takes just a few to restore it:
run {
allocate channel t1 type 'sbt_tape';
restore database;
recover database;
release channel t1;
}
When you invoke this script, RMAN identifies the most appropriate backup
from which to restore, requests the required tape volumes, and restores
the data files. If an incremental-backup strategy is in place, then it
will restore a combination of levels 0 and above. RMAN then determines
which archive logs are required for rolling forward to the current point
in time, requests that they are restored from tape, and recovers the database.
In theory, this may be the only script you'll ever need for performing
full-database recoveries. In practice, however, you will probably want
a little more flexibility and control.
1. Database open, datafile deleted
Datafile has been deleted from a running database. There are two methods
of open database recovery: restore the datafile and recover either the
datafile, or the tablespace. The next two examples show both methods:
(a) Datafile recovery
run {
allocate channel dev1 type disk;
sql "alter tablespace users offline immediate";
restore datafile 4;
recover datafile 4;
sql "alter tablespace users online";
release channel dev1;
}
(b) Tablespace recovery
run {
allocate channel dev1 type disk;
sql "alter tablespace users offline immediate";
restore tablespace users;
recover tablespace users;
sql "alter tablespace users online";
release channel dev1;
}
Note that if it is the system tablespace datafiles to be restored, the database must be closed. It is not possible to offline the system tablespace.
2. Complete restore (lost online redo) and rollforward - database
closed
run {
allocate channel dev1 type disk;
set until logseq=105 thread=1;
restore controlfile to '/oracle/dbs/ctrltargdb.ctl';
replicate controlfile from '/oracle/dbs/ctrltargdb.ctl';
restore database;
sql "alter database mount";
recover database;
sql "alter database open resetlogs";
release channel dev1;
}
Notes:
- The 'set until' command dictates at which log sequence recovery will
stop. It is critical that this command is issued BEFORE datafiles are restored,
otherwise RMAN will attempt to restore the most recent set of datafiles,
which could be ahead of the specified log
- The 'replicate controlfile' copies the restored controlfile to the
controlfiles referenced in init.ora
- Because the database is opened with resetlogs, it is necessary to
register the new incarnation of the database with the RESET DATABASE command.
As with v7, it is important to take a full backup of the database immediately
after a resetlogs
3. Restore of a subset of datafiles, complete recovery
run {
allocate channel dev1 type disk;
sql "alter database mount";
restore datafile 2;
restore datafile 3;
restore archivelog all;
recover database;
sql "alter database open";
release channel dev1;
}
4.Performs a full-database recovery to a previous point in time:
run {
allocate channel t1 type 'sbt_tape';
allocate channel t2 type 'sbt_tape';
set until time '28-MAY-1999 12:32';
restore database;
recover database;
sql "alter database open resetlogs";
release channel t1;
release channel t2;
}
reset database;
Note: the date format in the SET UNTIL clause must match the date format
of the NLS_DATE_FORMAT environment setting. If you have opened the database
with RESETLOGS, you must register this fact in the recovery catalog, using
the RESET DATABASE; command.
Resource
Utilization
RMAN has several options that allow you to control its use of system
resources. Predominantly, these govern performance and space utilization—for
example, you can increase backup and recovery throughput by increasing
the level of parallelism. Backup speed is governed by a combination of
the number of channels allocated and the files per set (filesperset) parameter.
Recovery throughput is related to the number of channels and the number
of distinct backup sets that RMAN must read in order to perform the recovery.
Wherever possible, RMAN attempts to capitalize on the streaming capabilities
of tape drives by writing several data files simultaneously to a single
backup set. This is all well and good but, as with other aspects of the
Oracle product set, RMAN can potentially take more than its fair share
of the resources on offer. Other users (potentially end users of an application)
may suffer as a result. Because when you're using RMAN, you don't generate
redo information (as you would without RMAN) during hot backups, the desire
to finish the backup ASAP is not quite so strong. RMAN allows you to restrict,
or "throttle," its usage of resources by setting the readrate parameter
on a per-channel basis, using the setlimit statement—for example:
run {
allocate channel t1 type 'sbt_tape';
setlimit channel t1 readrate 30;
backup......
The readrate value is the maximum number of buffers per second.
The buffer size is the result of multiplying the init.ora settings DB_FILE_DIRECT_IO_COUNT
and DB_BLOCK_SIZE. Setting the readrate value inevitably involves
a level of trial and error and is application- and hardware-specific.
In addition, setting the LARGE_POOL_SIZE init.ora parameter can help
optimize I/O buffering during RMAN backup operations.
When backing up very large databases, you run the risk that RMAN will
create a single backup set that is too large for the underlying disk or
tape subsystem to recognize as a single file. In such cases, you need to
split the backup set into several backup pieces. The kbytes parameter of
the setlimit statement specifies the threshold for splitting the
backup set. For example, the following statement limits the size of any
backup pieces produced through channel t1 to a size of 1GB:
run {
allocate channel t1 type 'sbt_tape';
setlimit channel t1 kbytes 1048576;
backup...
Report
& list commands
Lists
The list command queries the recovery
catalog to produce a formatted listing of contents. E.g.
list backup;
# lists backup sets, backup pieces, and
proxy copies
To restrict by object, use list copy or list backup
with the of listObjList condition. For example, enter:
list backup of database;
# lists backups of all files in database
list copy of datafile '/oracle/dbs/tbs_1.f';
# lists copy of specified datafile
list backup of tablespace SYSTEM;
# lists all backups of SYSTEM tablespace
list copy of archivelog all;
# lists all archived redo logs and copies of logs
list backup of controlfile;
# lists all control file backups
You can also restrict your search by specifying
a combination of tag, device type, filename pattern, or time options. For
example, enter:
list backup tag 'weekly_full_db_backup';
# by tag
list copy of datafile '/oracle/dbs/tbs_1.f'
type 'sbt_tape'; # by type
list backup like '/oracle/backup/tbs_4%';
# by filename pattern
list backup of archivelog until time 'SYSDATE-30';
# by time
list copy of datafile 2 completed between
'10-DEC-1999' and '17-DEC-1999'; i
Report objects that needs backup:
1. If necessary, issue crosscheck commands to
update the status of backups and change ... crosscheck commands to update
the status of image copies (if you want to specify image copies by primary
key, issue a list command to obtain the keys). Following is a possible
crosscheck session:
# must allocate maintenance channel for crosscheck
allocate channel for maintenance type disk;
crosscheck backup;
# crosschecks all backups
change datafile copy 100,101,102,103,104,105,106,107
crosscheck; # specified by key
change archivelog copy 50,51,52,53,54 crosscheck;
# specified by key
release channel;
2. Use the need backup option to identify which
datafiles need a new backup, restricting the report by a threshold number
of days or incremental backups. RMAN considers any backups older than the
days parameter value as needing a new backup because backups require days
worth of archived redo logs for recovery.
For example, enter:
report need backup days = 7 database;
# needs at least 7 days of logs to recover
report need backup days = 30 tablespace system;
report need backup days = 14 datafile '/oracle/dbs/tbs_5.f';
You can also specify the incremental parameter.
If complete recovery of a datafile requires more than the specified number
of incremental backups, then RMAN considers it in need of a new backup.
For example, enter:
report need backup incremental = 1 database;
report need backup incremental = 3 tablespace
system;
report need backup incremental = 5 datafile
'/oracle/dbs/tbs_5.f';
To report on backups that are obsolete:
1. Perform crosscheck if neccesary (see previous
step 1).
2. Use the obsolete option to identify which
backups are obsolete because they are no longer needed for recovery. The
redundancy parameter specifies the minimum level of redundancy considered
necessary for a backup or copy to be obsolete. If you do not specify the
parameter, redundancy defaults to 1.
A datafile copy is obsolete if at least integer
more recent backups of this file exist; a datafile backup set is obsolete
if at least integer more recent backups or image copies of each file contained
in the backup set exist. For example, enter:
# lists backups or copies that have at least
2 more recent backups or copies
report obsolete redundancy = 2;
Use the untilClause to use make the redundancy
check for backups sets or copies that are more recent, but not later than
the specified time, SCN, or log sequence number:
# obsolete if there are at least 2 copies/backups
that are no more than 2 weeks old
report obsolete redundancy = 2 until time
'SYSDATE-14';
report obsolete until scn 1000;
report obsolete redundancy = 3 until logseq
= 121 thread = 1;
3. Use the orphan option to list which backups
and copies are unusable because they belong to a incarnation that is not
a direct predecessor of the current incarnation:
report obsolete orphan;
4. Examine the report and delete those backups
that are obsolete.
report obsolete;
To report on backups that are unrecoverable
report unrecoverable database;
# examines all datafiles
List
and Report Scenarios
Following are some examples of list and report
generation:
Makings Lists of Backups and Copies
Use the list command to query the contents
of the recovery catalog or the target database control file if no recovery
catalog is used. You can use several different parameters to qualify your
lists.
The following example lists all backups of datafiles
in tablespace TBS_1 that were made after November 1, 1999:
list backup of tablespace tbs_1 completed
before 'Nov 1 1999 00:00:00';
The following example lists all backup sets or
proxy copies on media management devices:
list backup of database device type 'sbt_tape';
The following example lists all copies of datafile
2 using the tag weekly_df2__copy that are in the copy sub-directory:
list copy of datafile 2 tag weekly_df2_copy
like '/copy/%';
Using Lists to Determine Obsolete Backups
and Copies
Use the list command to determine which
copies and backups can be deleted. For example, if you created a full backup
of the database on November 2, and you know you will not need to recover
the database to an earlier date, then the backups and image copies listed
in the following report can be deleted:
list backup of database completed before 'Nov
1 1999 00:00:00';
list copy completed before 'Nov 1 1999 00:00:00';
Reporting Datafiles Needing Backups
The following command reports all datafiles in
the database that require the application of three or more incremental
backups to be recovered to their current state:
report need backup incremental 3 database;
The following command reports all datafiles from
tablespace SYSTEM that have not had a full or incremental backup in five
or more days:
report need backup days 5 tablespace system;
The following command reports which of datafiles
1 - 5 need backups because they do not have two or more backups or copies
stored on tape:
report need backup redundancy 2 datafile 1,2,3,4,5
device type 'sbt_tape';
Reporting Unrecoverable Datafiles
The following example reports on all datafiles
on tape that need a new backup because they contain unlogged changes that
were made after the last full or incremental backup.
report unrecoverable database device type
'sbt_tape';
Reporting Obsolete Backups and Copies
The following command reports all backups and
copies on disk that are obsolete because three more recent backups or copies
are already available:
report obsolete redundancy 3 device type disk;
The following command reports all backups on tape
that are obsolete because at least two backups already exist that were
made no more than one week ago:
report obsolete redundancy 2 until time 'SYSDATE-7'
device type 'sbt_tape';
The following command reports which datafiles
are obsolete because they belong to a database incarnation that is not
a direct predecessor of the current incarnation:
report obsolete orphan;
Manually Deleting Obsolete Backups and Copies
In this scenario, assume that you want to delete
the following:
2. Issue change ... delete commands for the copies
and backups to delete them and remove their repository records. Use the
filenames or issue a list command to obtain the primary keys:
allocate channel for delete type disk;
change backuppiece '/oracle/dbs/0va9hd5o_1_1',
'/oracle/dbs/0ma9co2p_1_1',
'/oracle/dbs/0ka9cm6l_1_1' delete;
change datafilecopy '/oracle/dbs/tbs_01.copy',
'/oracle/dbs/tbs_01_copy.f'
delete;
release channel;
Deleting Obsolete Backups and Copies Using
a UNIX Shell Script
Oracle provides the $ORACLE_HOME/rdbms/demo/rman1.sh
UNIX script to automate deletion of obsolete backups and copies. The script
uses sed and grep commands to process the output of the RMAN report obsolete
command and constructs an RMAN command file containing the necessary change
... delete commands. This script does not require the use of a recovery
catalog.
You can edit the report obsolete commands in
the script as desired: the script uses the default value for report obsolete
for both disk and tape devices. The output of the commands are stored in
deleted.log.
1. Change into the $ORACLE_HOME/rdbms/demo directory
and run the following shell script:
% rman1.sh
2. If desired, check deleted.log to see the command
output.
Generating Historical Reports of Database
Schema
The following commands reports the database schema
in the present, a week ago, two weeks ago, and a month ago:
report schema;
report schema at time 'SYSDATE-7';
report schema at time "TO_DATE('12/20/98','MM/DD/YY')";
Listing Database Incarnations
Every time that you perform a RESETLOGS operation
on a database, you create a new incarnation. This example lists all database
incarnation of PROD1 registered in the recovery catalog:
list incarnation of database prod1;
select 'set
auxname for datafile ' || file# || ' to ' || '''/ora<XXX>/oradata/<SID>'
||
substr(name,
instr(name, '/', -1)) || ''';'
from v$datafile
/
-------------------------------------------------------------------------------------------------------------------------
connect target
sys/<password>@<sid>
connect auxiliary
/
connect catalog
rman/<password>@<sid>
<Insert data file rename commands from step 1>
run {
-- Optional
Line -- set until time '2001-12-18:08:30:00';
allocate auxiliary
channel a0 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a1 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a2 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a3 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a4 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a5 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a6 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a7 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a8 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a9 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a10 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a11 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a12 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a13 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a14 type <DISK/SBT_TAPE>;
allocate auxiliary
channel a15 type <DISK/SBT_TAPE>;
duplicate target
database to <NEW_SID>
logfile
group 1 ('/ora<XXX>/oradata/<SID>/redo01_01.rdo')
size 200M reuse,
group 2 ('/ora<XXX>/oradata/<SID>/redo02_01.rdo')
size 200M reuse,
group 3 ('/ora<XXX>/oradata/<SID>/redo03_01.rdo')
size 200M reuse,
group 4 ('/ora<XXX>/oradata/<SID>/redo04_01.rdo')
size 200M reuse;
}
-------------------------------------------------------------------------------------------------------------------------
C- Shell
setenv NLS_LANG
american
setenv NLS_DATE_FORMAT
YYYY-MM-DD:HH24:MI:SS
Korn Shell
export NLS_LANG=
American
export NLS_DATE_FORMAT=
YYYY-MM-DD:HH24:MI:SS
SQL> SELECT
* from global_name;
SQL> ALTER
database rename global_name to <new name>;
Script
reports RMAN backup statistics direct from the recovery catalog.
set pages 200
col datafileMb format 9,999,999 heading "Datafile Size Mb"
col backedupMb format 9,999,999 heading "Backuped Size Mb"
break on report
compute sum of datafileMb on report
compute sum of backedupMb on report
show user
SELECT db_name,MIN(completion_time)
FROM rc_backup_datafile
GROUP BY db_name;
REM top running backups
select rownum as rank
,name
, DECODE(backup_type,'D','Full','I','Incremental'
,'L','Archive
Log',backup_type) backup_type
,round(max_secs/60) mins
FROM ( select name , backup_type , max(elapsed_seconds) max_secs
from rc_backup_set bs, rc_database d
where bs.db_key = d.db_key
group by name, backup_type
order by max(elapsed_seconds) desc
)
WHERE rownum <= 10
SELECT db_name
, DECODE(status,'A','Available'
,'D','Deleted'
,'O','Unusable'
,status) status
, COUNT(*)
from rc_backup_datafile
GROUP BY db_name,status
SELECT db_name
, DECODE(backup_type, 'D','Full','Incremental') backup_type
, SUM(datafile_blocks*block_size) /1024/1024 datafileMb
, SUM(blocks*block_size) /1024/1024 backedupMb
FROM rc_backup_datafile
GROUP BY db_name
, DECODE(backup_type, 'D','Full','Incremental');