Recovery Manager (RMAN)
Topics
Recovery Manager
Major
Features
Recovery Manager contains a number of new features to aid in the
backup, restore and recovery operations in large, complex Oracle
environments.
These include:
Backup file compression
Backup file Compression
Recovery Manager only copies data blocks that contain data. A
tablespace
that is only half filled will be backed up into a file half the size
of an OS image copy of the same tablespace.
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:
o alter tablespace <tablespace name> begin backup
o alter tablespace <tablespace name> end backup
they are not needed when implementing online backups through RMAN.
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:
The Recovery Manager report and
recover commands can be used to answer these questions. These
commands can be executed interactively, or as scripts.
To find out if a tablespace has a datafile that can't be recovered
(this could be due to an unrecoverable operation, such as running
SQL*Loader
in UNRECOVERABLE mode), enter:
report unrecoverable tablespace
"users";
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. The catalog itself rarely occupies more than 40MB.
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). Connect to the Oracle instance by using your DBA account, create an account for RMAN, and grant it appropriate rights.
connect
internal
create tablespace RMAN datafile
'.......' size 20M autoextend on next 5M maxzize 300M;
create user RMAN identified by
rman temporary tablespace temp default tablespace RMAN quota unlimited
on RMAN;
grant RECOVERY_CATALOG_OWNER,
connect, resource, dba to RMAN;
connect RMAN/RMAN@RMAN
Then, create the catalog.
%export ORACLE_SID=RMAN
rman catalog rman/rman
create catalog;
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.
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".
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
export
ORACLE_SID=PROD
rman target / rcvcat rman/rman@RMAN
or
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 the archive
redo log files (furthermore, it actually deletes the logs after
backing them up) and a copy of the current control file :
run {
# backup the complete
database to disk
allocate channel ch1
type disk;
backup full database include current controlfile
tag prod_full_backup
format '/data/archdata/DBbackup/%d_t%t_s%s_p%p';
sql "alter system archive log current";
backup archivelog all
delete input
# or you can use backup
archivelog from logseq=16 thread 1 delete input for specific files
format
'/data/archdata/DBbackup/prodarch_t%t';
release channel ch1;
}
When you back up to disk, you need to tell RMAN where to put the backup files by using the format parameter. The format specifies the full path and filename of the backup. Here, %d is the database name, %t is the timestamp, %s is the backup set number and %p is the backup piece number. Archivelog backups cannot be tagged. Finally, we are backing up all the archivelogs starting from the log sequence number 16, since we do not have the older archivelogs that were produced before we set up RMAN. We are also deleting the archivelogs after the backup is successfully completed. If the backups fail, the archivelogs will not be deleted, but use this with caution, since archivelogs are paramount for full database recovery
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
To save the script into the catalog you can use:
create script prod_full_backup
{
# backup the complete database to disk
allocate channel ch1 type disk;
backup full database include current controlfile
tag prod_full_backup
format '/data/archdata/DBbackup/%d_t%t_s%s_p%p';
sql "alter system archive log current";
backup archivelog all
delete input
# or you can use backup
archivelog from logseq=16 thread 1 delete input for specific files
format
'/data/archdata/DBbackup/prodarch_t%t';
release channel ch1;
}
replace script prod_full_backup {
# backup the complete
database to disk
allocate channel ch1 type disk;
backup full database include current controlfile
tag prod_full_backup
format '/data/archdata/DBbackup/%d_t%t_s%s_p%p';
sql "alter system archive log current";
backup archivelog all
delete input
# or you can use backup
archivelog from logseq=16 thread 1 delete input for specific files
format
'/data/archdata/DBbackup/prodarch_t%t';
release channel ch1;
}
To Drop it from RMAN just perform:
delete script script_name;
To Execute it from RMAN just perform:
run {execute script
prod_full_backup;}
To read the code from the Database you can run the following query:
select text from rc_stored_script_line where
script_name='prod_full_backup';
Step 7 (optional)
- Then you can create a Unix Script
That will call the saved script in RMAN
We want to run it every Sunday night as a scheduled job. Let's run it
as a cron job. Create a Korn shell script called "rman_backup.ksh".
This shell script will run the RMAN script, prod_full_backup.rcv.
#!/bin/ksh
# Shell script to run RMAN script prod_full_backup.rcv
# Logging messages to prod_full_backup.log
# Set environment variables
export ORACLE_SID=PPPPROD
export ORACLE_HOME=/usr/local/OraHome1
# Connect to RMAN and run the backup script (all in one line)
rman target / rcvcat rman/rman@rman cmdfile
/home/oracle/script/rman_prod_full_backup.rcv
msglog /home/oracle/script/rman_full_backup.log
exit
Change permissions on the shell script appropriately.
chmod 755 rman_backup.ksh
Now, create the RMAN script file, prod_full_backup.rcv. This just runs
the previously stored catalog script, prod_full_backup. Here are the
contents of prod_full_backup.rcv
run {execute script
prod_full_backup;}
Finally, schedule the shell script using the crontab.
[prod-server:oracle] $ crontab -e
00 21 * * 0 /home/oracle/script/rman_backup.ksh
We have scheduled our script to run every Sunday night at 9:00 PM.
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
In one session perform:
shutdown immediate;
startup force dba;
shutdown immediate
startup mount;
In another session
perform:
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;
}
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.
Another example:
Create a suitable backup strategy
A proper plan must be made to protect your production database against
potential failures based on the service requirements and the database
size. It may not be acceptable to lose any data in a production
environment if a disk failure damages some of the files that constitute
a database. Therefore, the database may have to be operated in
ARCHIVELOG mode, ideally with a multiplexed online redo log. The
following plan is an example.
Backup Sunday Monday
Tuesday Wednesday
Thursday Friday Saturday
Level 0 8:00 PM
Level 1
2:00 AM
2:00 AM
Level 2
2:00 AM
2:00 AM
2:00 AM
Level 0 backup: Backs up the whole database. This backup
is part of the incremental strategy. It means that it can have
incremental backups of levels greater than 0 applied to it.
Level 1 backup: This backup will only back up the blocks which
have been modified since the last level 0 backup was performed.
Otherwise it is exactly the same backup as the level 0 above
Level 2 backup: The backup will only back up the blocks which
have been modified since the last level 0 or 1 backup was
performed.
Otherwise it is exactly the same backup as the level 0 or 1 backups
above.
The following scripts describe how to do the three levels backups.
# Incremental level 0 (whole) database backup
# The control file is automatically included each time file 1 of the
# system tablespace is backed up.
replace script backup_db_level_0_disk {
execute script alloc_all_disks;
set maxcorrupt for datafile 1 to 0;
backup
incremental level 0
tag backup_db_level_0
# The skip inaccessible clause ensures the backup
will continue
# if any of the datafiles are inaccessible.
skip inaccessible
filesperset 9
format '/bkup/SID/%d_0_t%t_s%s_p%p'
(database);
execute script rel_all_disks;
sql 'alter system archive log current';
execute script backup_al_all_disk;
}
# Incremental level 1 database backup
replace script backup_db_level_1_disk {
execute script alloc_all_disks;
set maxcorrupt for datafile 1 to 0;
backup
incremental level 1
# You can use the 'skip readonly' and 'skip offline'
clauses
# to omit backing up these files if you have already
had
# valid backups of them.
skip offline
skip readonly
skip inaccessible
tag backup_db_level_1
filesperset 9
format '/bkup/SID/%d_1_t%t_s%s_p%p'
(database);
execute script rel_all_disks;
sql 'alter system archive log current';
execute script backup_al_all_disk;
}
# Incremental level 2 database backup
replace script backup_db_level_2_disk {
execute script alloc_all_disks;
set maxcorrupt for datafile 1 to 0;
backup
incremental level 2
skip offline
skip readonly
skip inaccessible
tag backup_db_level_2
filesperset 9
format '/bkup/SID/%d_2_t%t_s%s_p%p'
(database);
execute script rel_all_disks;
sql 'alter system archive log current';
execute script backup_al_all_disk;
}
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
# all
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.
5. In this example the database is offline to
restore
the controlfile, we still have the online logs so complete recovery is
possible:
% set ORACLE_SID=PROD
svrmgrl> connect internal
svrmgrl> shutdown abort;
svrmgrl> startup nomount;
rman target / rcvcat rman/rman@RMANDB
run {
allocate channel t1 type 'sbt_tape';
allocate channel d1 type disk;
restore controlfile to 'd:\target\ctl1targ.ora';
replicate controlfile from 'd:\target\ctl1targ.ora';
restore database;
sql "alter database mount";
recover database;
sql "alter database open";
}
6.In this example we'll restore the system tablespace
% set ORACLE_SID=PROD
svrmgrl> connect internal
svrmgrl> shutdown immediate;
svrmgrl> startup mount;
rman target / rcvcat rman/rman@RMANDB
run {
allocate channel d1 type disk;
restore tablespace SYSTEM;
recover database;
sql "alter database open";
}
7. Restoring the database to a new server
1. You must have the Oracle binaries installed on the node to restore
to and have updated the sqlnet information on that node so Recovery
Manager can connect to the new instance started at nomount. We will
perform time based recovery.
Note: If you are restoring from a disk backupset. You must
copy the backupsets that will be used for restore to the new node
and they must reside in the same image path used on the original
node.
Rule of thumb for disk backups. Keep the path simple so it is
easy
to duplicate the path if needed on a new node.
% set ORACLE_SID=PROD
svrmgrl> connect internal
svrmgrl> shutdown abort;
svrmgrl> startup nomount;
rman target / rcvcat rman/rman@RMANDB
run {
set until time 'Jul 01 1999 00:05:00';
allocate channel d1 type disk;
set newname for datafile '/u04/oracle/prod/sys1prod.dbf' to
'/u02/oracle/prod/sys1prod.dbf';
set newname for datafile '/u04/oracle/prod/usr1prod.dbf' to
'/u02/oracle/prod/usr1prod.dbf';
set newname for datafile '/u04/oracle/prod/tmp1prod.dbf' to
'/u02/oracle/prod/tmp1prod.dbf';
set newname for datafile '/u04/oracle/prod/rbs1prod.dbf' to
'/u02/oracle/prod/rbs1prod.dbf';
set newname for datafile '/u04/oracle/prod/indx1prod.dbf' to
'/u02/oracle/prod/indx1prod.dbf';
restore controlfile to '/u02/oracle/prod/ctl1prod.ora';
replicate controlfile from '/u02/oracle/prod/ctl1prod.ora';
restore database;
sql "alter database mount";
switch datafile all;
recover database;
sql "alter database open resetlogs";
release channel d1;
}
Note: Switch in this command updates the restore controlfile with the
new datafile path name just like "alter database datafile rename.."
Since we are restoring to a new server we will not have the use of the
online redo logs so complete recovery is not possible and the database
is open with the resetlogs option.
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
Makings Lists of
Backups
and Copies
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:
Datafile copies for
which there are at least two more recent copies.
1.
Generate
a report with redundancy set to 2:
report obsolete redundancy 2;
Report of obsolete backups and copies
Type
Recid Stamp Filename
-------------------- ------ ---------
--------------------------
Datafile
Copy
23 345392880 /oracle/dbs/tbs_01.copy
Datafile
Copy
22 345392456 /oracle/dbs/tbs_01_copy.f
Backup
Set
31 345552065
Backup
Piece
31 345552061 /oracle/dbs/0va9hd5o_1_1
Backup
Set
23 345399397
Backup
Piece
23 345399391 /oracle/dbs/0ma9co2p_1_1
Backup
Set
20 345397468
Backup
Piece
20 345397464 /oracle/dbs/0ka9cm6l_1_1
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;
Duplicate
Database using RMAN
1. Run the script below on the database
in which you plan to duplicate to generate the new paths for the data
files. This example assumes you have enough disk space on one mount
point. However, you can easily change some of the mount points in VI to
point another location or modify the script to suit your specific needs.
select 'set auxname for datafile ' || file# || ' to ' ||
'''/ora<XXX>/oradata/<SID>'
||
substr(name, instr(name, '/', -1)) || ''';'
from v$datafile
/
2. Create the RMAN script to perform the duplicate. The
number of log groups must match the source database. You may increase
or decrease the number of log members as needed. You must also insert
your auxname commands before the rman run block. If you are not doing a
point in time dupilcate you can remove the optional line "set until
time ...".
-------------------------------------------------------------------------------------------------------------------------
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;
}
3. Log into the server where you will be duplicating the
database.
4. If you are using RMAN duplicate to refresh a TEST or
Development database first. Shutdown the instance if it already running.
5. You will need to recreate/create the password file on
for the target database.
You do not have to delete the data files of the existing database if
one exists (applicable to only a refresh). It would be a good idea to
do so if you are not sure that the source and the target you are
duplicating too has the same name and number of data files. If not you
will have unused data files on the duplicate database, wasting server
resources.
6. STARTUP NOMOUNT the Target database.
Note: You must have an adequately sized SORT_AREA_SIZE for RMAN to do
its quering against the auxiliary database since it will not have any
temp space available in NOMOUNT.
7. For the recovery part of the duplicate you will need
the archive logs of the source database available. The archive logs can
be copied over from the production machine to the target machine as
long as the archive logs are placed in the exact path as on the
production machine. Otherwise, you must do an archive log backup via
Rman to either disk or tape. Remember that if you use a combination of
Disk and Tape backups to allocate channels for both tape and disk in
your script.
8. If you are doing a duplicate from a point in time you
must set the NLS environmental variables. If not you can skip this step.
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
9. Run the completed duplicate script
$ rman @runscript
Note: If for any reason the duplicate fails due to missing archive log
file or any other reason, it is still possible to finish the recovery
manually, however the DBID will not be reset. Also, If you are
duplicating
a Multimaster replicated production database be sure to set
job_queue_processes=0
to avoid pushing replication transactions to your production databases.
10. Make sure your global name is set correctly fro your target
database because RMAN does not set this for you. It is also important
to note that if you change the domain you must rebuild any database
links.
SQL> SELECT * from global_name;
SQL> ALTER database rename global_name to <new name>;
11. Remeber to add any temporary files for any tablespaces that
are using them.
12. Change any passwords for the new database.
Always use a recovery catalog. Place your catalog
on totally separate hardware from your target database. If you can,
house it in a separate geographical location. Make regular backups of
your catalog. Resynchronize the catalog at least as frequently as the
control file recycles backup info (governed by the
CONTROL_FILE_RECORD_KEEP_TIME initialization parameter). Always
resynchronize the catalog when you have changed a database's physical
layout.
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');
rem
-----------------------------------------------------------------------
rem Filename: rmanlist.sql
rem Purpose: List backups registered in RMAN
catalog database
rem Author: Diego Pafumi
rem
-----------------------------------------------------------------------
connect rman/rman
col media format a8
col tag format a12 trunc
col minutes format 990
select d.name, p.tag, p.media,
s.incremental_level "LEVEL",
to_char(s.start_time,
'DD-MON-YY
HH24:MI') start_time,
s.elapsed_seconds/60 "MINUTES"
from RC_DATABASE d, RC_BACKUP_PIECE p, RC_BACKUP_SET s
where
d.name
= 'WH'
and s.start_time >
'04-MAY-02'
and s.completion_time < '06-MAY-02'
and p.backup_type = 'D' --
D=Database,
L=Log
and d.db_key = p.db_key
and s.db_key = p.db_key
and p.bs_key = s.bs_key
/
rem
-----------------------------------------------------------------------
rem Filename: rmanstat.sql
rem Purpose: List completed RMAN backups for the
last 24-hours
rem
(use info from Dictionary - Control File Views)
rem Date: 12-Feb-2002
rem Author: Diego Pafumi
rem
-----------------------------------------------------------------------
tti "RMAN Backups for last 24-hours"
col type format a4
col handle format a35 trunc
col file# format 9999
col duration format a9
select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'DDMON
HH24:MI') start_time,
to_char(a.elapsed_seconds/60,
'99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#)
file#, nvl(d.blocks, l.blocks) blocks
from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
SYS.V_$BACKUP_DATAFILE d,
SYS.V_$BACKUP_REDOLOG l
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = b.SET_STAMP
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#
/
rem
-----------------------------------------------------------------------
rem Filename: rman_run.sql
rem Purpose: Monitor RMAN status, while a backup
is running
rem Date: 12-Nov-2001
rem Author: Diego Pafumi
rem
-----------------------------------------------------------------------
prompt RMAN Backup Status:
SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE
(Min)",
round(sofar/totalwork*100,2)
"Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore'
/
USE UNIX SCRIPTS TO CLEAN UP OLD TRACE AND LOG FILES
#!/bin/ksh
# Cleanup archive logs more than 7 days old
find /u01/app/oracle/admin/mysid/arch/arch_mysid*.arc -ctime +7
-exec rm {} ;
The following script loops through each database, visiting the bdump,
udump, and audit directories, and removes all files that are more than
two
weeks old.
#!/bin/ksh
for ORACLE_SID in `cat /etc/oratab|egrep ':N|:Y'|grep -v \*|cut
-f1-d':'`
do
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -d":" -f2`
DBA=`echo $ORACLE_HOME | sed -e 's:/product/.*::g'`/admin
find $DBA/$ORACLE_SID/bdump -name \*.trc -mtime +14 -exec rm {} \;
$DBA/$ORACLE_SID/udump -name \*.trc -mtime +14 -exec rm {} \;
find $ORACLE_HOME/rdbms/audit -name \*.aud -mtime +14 -exec rm {}
\;
done
Unregister a DB from
RMAN
First we start up RMAN with a connection to the catalog and the target,
making a note of the DBID in the banner:
C:\>rman catalog=rman/rman@rman target=sys/password@prod
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: W2K1 (DBID=1487421514)
connected to recovery catalog database
Next we list and delete any backupsets recorded in the repository:
RMAN> LIST BACKUP SUMMARY;
RMAN> DELETE BACKUP DEVICE TYPE SBT;
RMAN> DELETE BACKUP DEVICE TYPE DISK;
Next we connect to the RMAN catalog owner using SQL*Plus and issue the
following statement:
SQL> CONNECT rman/rman@RMAN
SELECT db_key, db_id
FROM db
WHERE db_id = 1487421514;
DB_KEY DB_ID
---------- ----------
1 1487421514
The resulting key and id can then be used to unregister the database:
SQL> EXECUTE dbms_rcvcat.unregisterdatabase(1, 1487421514);
Here is a way to delete expired backup sets and archived logs in Oracle
8i. You still have to execute the commands below manually, since
Oracle does not provide functionality to execute them from a stored
scripts.
Scripts were created and tested on Oracle 8.1.6 and 8.1.7 Enterprise
Edition database instances. I execute them once a month, to remove the
expired backup references from my RMAN catalog.
-------------- DB BACKUP -----------------------------
# bkup_db_crosscheck.rcv
#
# Crosscheck db logs backup and keep one week old in the repoitory
#
connect rcvcat rman/rman@rman;
connect target ' / as sysdba';
allocate channel for maintenance type disk;
crosscheck backup of database completed between 'sysdate - 30' and
'sysdate - 7';
delete expired backup;
release channel;
exit;
------------ ARCHIVED LOGS BACKUP ----------------
# bkup_arch_logs_crosscheck.rcv
#
# Crosscheck archived logs backup and keep one week old in the repoitory
#
connect rcvcat rman/rman@rman01;
connect target ' / as sysdba';
allocate channel for maintenance type disk;
crosscheck backup of archivelog completed between 'sysdate - 30' and
'sysdate - 7';
delete expired backup;
release channel;
exit;