Recovery Manager
Major
Features
Recovery Manager contains a number features to aid in the
backup, restore and recovery operations in large, complex Oracle
environments.
These include:
- Backup file compression
- Incremental backups
- Corrupt block detection
- Fractured Block Detection
- Automatic parallelization
- Backup usability and restore validation reporting
- Recovery catalog for centralizing operations
- Trial Recovery
- Skip Unused blocks
- Block Level Recovery
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.
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.
Backup Usability and Restore
Validation
Reporting
Recovery Manager has several features that allow a DBA to look at their
backups and analyze if the backups are OK.
The RMAN commands report and
recover will help you to identify the backup status. 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:
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.
If you have just a few databases, then the recovery
catalog is probably not worth the extra effort and hassle. If you have
a database environment with many databases in it, you should consider
using a recovery catalog. Generally, the added flexibility and
centralized enterprise-wide reporting benefits of the recovery catalog
outweigh the additional maintenance and administrative requirements
that are added with the use of a recovery catalog. One downside to
using a recovery catalog, though, is that if the catalog database is
down, your backups will all fail unless you have coded your backup
scripts to perform a backup without the recovery catalog in cases where
the first backup with the recovery catalog fails.
Additionally, a recovery catalog is an essential part of a Data Guard
backup environment and Split mirror backups. In these configurations,
when you back up the database from the backup host, the recovery
catalog is considered the most current information, so it is the brains
behind the strategy and becomes a single point of failure if not
maintained properly. The bottom line is that you need to decide for
yourself whether your environment calls for a recovery catalog.
Change-Aware
Incremental Backups
In previous releases of the Oracle database, RMAN had to examine every
block in the data file to determine which blocks had been changed when
performing an incremental backup. The time to perform an
incremental backup was proportional to the size of the data
files. Therefore, performing an incremental backup on a
very large database could take some time, even if only a few blocks
were changed.
In Oracle 10g, you can create a block change tracking file that records
the blocks modified since the last backup. RMAN uses the tracking
file to determine which blocks to include in the incremental
backup. RMAN no longer needs to examine the entire data
file. The time to perform an incremental backup is now
proportional to the amount of content modified since the last backup.
Here are the steps RMAN will perform to do an incremental backup:
1. Read the Block Change Tracking File to determine
which blocks in the data file need to be read.
2. Scan only the changed blocks in the data file and
then back them up.
The size of the block change tracking file is
proportional to:
- The database size in bytes: the block change tracking file contains
data representing every data file block in the database. The data
is approximately 1/250000 of the total size of the database.
- The number of enabled threads: In a Real Application Cluster (RAC)
environment, the instances update different areas of the tracking file
without any locking or inter-node block swapping. You enable
block change tracking for the entire database and not for individual
instances.
- The number of old backups: The block change tracking file keeps a
record of all changes between previous backups, in addition to the
modification since the last backup. It retains the change history
for a maximum of eight backups.
The size of the file is calculated by the following formula:
Size of the Block Change Tracking File = (
(Threads*2) + number of old backups ) * database size in bytes / 250,000
The initial size for the block change tracking file is 10 MB. Using
this formula, a 2 TB database with only one thread and having five
backups in the RMAN repository will require a block change tracking
file of 59 MB.
Enabling,
Disabling and Monitoring Block Change Tracking
By default, Oracle will not record block change information. To
enable this feature, you need to issue the following command:
SQL> alter database enable
block change tracking USING FILE ‘/u01/oradata/ora1/change_tracking.f’;
To disable this feature, you issue this command:
SQL> alter database disable
block change tracking;
To monitor the status of block change tracking, you type:
select file, status, bytes
from v$block_change_tracking;
STATUS
FILE
BYTES
--------
---------------------------- ---------------
ENABLED
/dba/backup/01_mf_yzmrr7.chg 10,000,000
If the location needs to be moved, change tracking can
be disabled, and a new change tracking file can be created, but this
causes the database to lose all change tracking information. Moreover,
unfortunately the change tracking file cannot be moved without shutting
down the database, moving it with the appropriate ALTER DATABASE RENAME
FILE <filename> command, and then restarting the database.
Oracle does recommend that this feature be activated for any database
whose disaster recovery plan utilizes incremental backups of differing
levels. Oracle also notes that theirs is a small performance hit during
normal operations, but that hit should be discounted against the need
to avoid scans of datafiles during restoration and recovery operations.
Expanded Image Copying Features
A standard RMAN backup set contains one or more backup pieces, and each
of these pieces consists of the data blocks for a particular datafile
stored in a special compressed format. When a datafile needs to be
restored, therefore, the entire datafile essentially needs to be
recreated from the blocks present in the backup piece.
An image copy of a datafile, on the other hand, is much faster to
restore because the physical structure of the datafile already exists.
Oracle 10g now permits image copies to be created at the database,
tablespace, or datafile level through the new RMAN directive BACKUP AS COPY. For example, here is
a command script to create image copies for all datafiles in the entire
database:
RUN {
# Set the default channel
configuration. Note the use of the
# %U directive to insure unique
file names for the image copies
ALLOCATE CHANNEL dbkp1 DEVICE TYPE
DISK FORMAT 'c:\oracle\rmanbkup\U%';
# Create an image copy of all
datafiles in the database
BACKUP AS COPY DATABASE;
}
More examples:
RUN {
# Set the
default channel configuration
ALLOCATE
CHANNEL dbkp1 DEVICE TYPE DISK FORMAT
'c:\oracle\rmanbkup\ic_%d_%s_%t_%p';
# Back up
specific datafiles and retain them as an image copies
BACKUP AS COPY
(DATAFILE 2, 6, 9 MAXSETSIZE 25M);
# Back up a
specific tablespace and retain it as an image copy
BACKUP AS COPY
(TABLESPACE example MAXSETSIZE 15M);
# Back up the
whole database and retain it as an image copy
BACKUP AS COPY
DATABASE;
}
Incrementally
Updated Backups
As we saw before, it is now much simpler to
create image copy backups of the database. Another new Oracle 10g
feature, incrementally updated backups, allows me to apply incremental
database changes to the corresponding image copy backup - also known as
rolling forward the datafile image copy -- of any datafile in the
database. Since image copy backups are much faster to restore in a
media recovery situation, this new feature gives me the option to have
updated image copies ready for restoration without having to recreate
the image copies on a regular basis.
To utilize this feature, you will use the new BACKUP ... FOR RECOVER OF COPY
command to create the incremental level 1 backups to roll forward the
changes to the image copy of the datafiles, and use the new RMAN RECOVER COPY OF DATABASE command to
apply the incremental backup to the image copies of the datafiles. Note
that the TAG directive becomes extremely important to this
implementation, as it is used to identify to which image copies the
changes are to be rolled forward. Here is a script that illustrates a
daily cycle of creation and application of the incrementally updated
backups. This would be appropriate for a database that has sufficient
disk space for storage of image copies, and has a relatively high need
for quick restoration of media:
RUN {
# Roll forward any available
changes to image copy files
# from the previous set of
incremental Level 1 backups
recover copy of database with tag 'img_cpy_upd';
# Create incremental level 1
backup of all datafiles in the database
# for roll-forward application
against image copies
backup incremental level 1
for recover of copy with tag
'img_cpy_upd' database;
}
Though this appears a bit counter-intuitive at first, here is an
explanation of what happens during the initial
run of this script:
* The RECOVER command actually has no effect,
because it cannot find any incremental backups with a tag of img_cpy_upd.
* However, the BACKUP command will create a new
Incremental Level 0 backup that is labeled with a tag of img_cpy_upd because no backups have
been created yet with this tag.
And during the second run of this script:
* The RECOVER command still will have no effect,
because it cannot find any Level 1 incremental backups with a tag of img_cpy_upd.
* The BACKUP command will create its first
Incremental Level 1 backup that is labeled with a tag of img_cpy_upd.
But during the third and subsequent runs of this script:
* The RECOVER command finds the incremental level 1
image copy backups from the previous night's run tagged as img_cpy_upd, and applies them to the
existing datafile image copies.
* The BACKUP command will create the next
Incremental Level 1 backup that is labeled with a tag of img_cpy_upd.
After the third run of this script, RMAN would then choose the
following files during a media recovery scenario: the image copy of the
database for tag img_cpy_upd from the previous night, the most recent
incremental level 1 backup, and all archived redo logs since the image
copy was taken. This strategy offers a potentially quick and flexible
recovery, since the datafile image copies will be relatively quick to
restore, and the incremental level 1 backup plus all archived redo logs
can be used to perform either a point-in-time or a complete recovery.
Example: Incrementally-Updated Backups: A Weekly Implementation
RUN {
######
# This script will create image
copy backups to which incremental
# changes can be applied on a
weekly schedule
######
# Roll forward any available
changes to image copy files
# from the previous set of
incremental Level 1 backups. Note that
# the roll-forward will not occur
until 7 days have elapsed!
recover copy of database with tag
'img_cpy_upd'
until time (sysdate-7);
## Create incremental level 1
backup of all datafiles in the database
# for roll-forward application
against weekly image copies
backup incremental level 1
for recover of copy with tag 'img_cpy_upd' database;
}
Database
Dropping and Deregistration
Oracle 10g allows a database to be dropped and its entry removed from
the RMAN catalog. The following statement drops the entire database and
removes the database files:
RMAN> drop database;
The statement below drops the entire database, removes the database
files, and deletes all backup copies of the database and the archive
log files:
RMAN> drop database including
backups;
The two statements above drop the database and delete the database
files. However, they do not unregister the database from the RMAN
catalog. The following statement will remove the database information
from the RMAN catalog:
RMAN> unregister database grid;
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.
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.
IMPORTANT NOTE:
Save the database id displayed in the RMAN output if you are operating
RMAN backups in nocatalog mode, since it is required during disaster
recovery.
You will see the database id in output from RMAN on connecting to
target database like:
connected to target database: INVENTORY
(DBID=1670954628)
It's also recommended to save your init.ora file and
your tnsnames.ora file in a "safe" place.
Terminology in
RMAN
When you issue an RMAN backup command, RMAN creates backup sets, which
are logical groupings of physical files. The physical files that RMAN
creates on your backup media are called backup pieces. When working
with RMAN, you need to understand that the following terms have
specific meanings:
a) RMAN Backup - A
backup of all or part of your database. This results from issuing an
RMAN backup command. A backup consists of one or more backup sets.
b) Backup Set - A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.
c) Backup piece:
A physical binary file created by RMAN during a backup. Backup pieces
are written to your backup medium, whether to disk or tape. They
contain blocks from the target database's datafiles, archived redo log
files, and control files.
When RMAN constructs a backup piece from datafiles,
there are a several rules that it follows:
* A datafile cannot span
backup sets.
* A datafile can span backup
pieces as long as it stays within one backup set.
* Datafiles and control
files can coexist in the same backup sets.
* Archived redo log files
are never in the same backup set as datafiles or control files.
RMAN is the only tool that can operate on backup
pieces.
Other Useful Terms
- Image Copy -
is bit-by-bit
copy of database files created on Disk. This is equivalent to cp (on
Linux) or copy (on windows).
* Backup Set is default method to backup, for image copy use BACKUP AS
COPY while backup.
- Hot backup - backup taken
while Oracle Database is Up. (Database should be in “Archive Log” mode
for hot backup)
- Cold backup - backup taken
while Oracle database is down or in mount state (NOT OPEN).
- Full Backup - Backing up
entire database is called as full backup
- Incremental Backup - Backup
of block level changes to database made after previous incremental/full
backup.
- “Level 0" Incremental backup
- This backs up all blocks in database. This is equivalent to full
backup.
- “Level 1" Incremental backup
- This backs up database block changes after previous incremental
backup.
If there is no level 0 incremental backup and you run level 1
incremental backup, RMAN will automatically make level 0 incremental
backup.
- Cumulative incremental backup
- level 1 incremental backup which includes all blocks changed since
most recent level 0 incremental backup.
- Differential incremental
backup
- level 1 incremental backup which includes only block changed since
most recent incremental backup.
By default, incremental backups are differential
Flash
Recovery
Area (FRA)
By configuring the RMAN RETENTION POLICY, the FRA will automatically delete obsolete backups and archive logs that are no longer required based on that configuration. If you set the RETENTION POLICY to a recovery window of 7 days, then RMAN will retain all backups required to recover the database 7 days in the past. FRA will delete obsolete files once the disk is arriving to the quote defined.
All files that are needed to completely recover a
database from a media
failure are part of the Flash Recovery Area. Those recovery related
files include:
• Control file: A copy is created in the Flash
Recovery Area location at database creation.
• Archived log files: When the Flash Recovery Area is
configured, the archiver background process then creates archived files
in the Flash Recovery Area and in other configured LOG_ARCHIVE_DEST_n
locations.
• Flashback logs: the Flash Recovery Area
automatically manages Flashback Database logs.
• Control file autobackups: The default location for
control file .
• Data file copies: The default location for
data file copies created by RMAN is stored in the Flash Recovery Area.
• RMAN backups: The default location for RMAN to
create files during backup & copy operations. It is also the
default location to restore archive logs from tape if they are required
during a recover task.
Per default a backuppiece is created in the Flash
Recovery Area unless it is overruled by using the FORMAT-clause.
The FORMAT-clause can be specified in the BACKUP command or
configured in the persistent configuration, by using CONFIGURE
command.
Like with normal backup, the Controlfile autobackup
will be stored in the Flash Recovery Area unless the location is
overruled by the FORMAT for the autobackup.
RMAN> configure controlfile autobackup format for device type
disk to <....>;
Per default RMAN restores the archived redologs in the
LOG_ARCHIVE_DEST_n location. If one of the LOG_ARCHIVE_DEST_n
parameters is set to "LOCATION=USE_DB_RECOVERY_FILE_DEST" then
restored archived redo log files will be stored in the Flash Recovery
Area. This can be overrulled by using the RMAN command :
RMAN> SET ARCHIVELOG DESTINATION <....>;
Installing a
Catalog
One important decision when using RMAN is deciding
whether to use a recovery catalog. This section covers the pros and
cons of a catalog and then details catalog implementation issues.
What Is the
Catalog?
In many respects, RMAN can be thought of as an Oracle database file
backup and restoration utility. RMAN must keep track of many things.
When was the last backup taken? Which files were backed up? Which
backup sets contain which database files? The answers to these
questions can be found in the target control files. Optionally, RMAN
can be configured to store this information in a separate database
known as the catalog. The catalog consists collectively of a database
and a database schema. The schema objects hold the RMAN-specific
information for each of your target databases.
Catalog
advantages
When you use a catalog repository, you get more flexibility and access
to all the features of RMAN. The advantages of using a catalog accrue
in the following areas:
- You can retain backup and recovery metadata for long periods of time.
- You can centralize operations.
- RMAN becomes more flexible in certain backup and recovery scenarios.
A catalog enables you to recover your control files in
the event that they are all corrupted or lost. If you are not using a
catalog, and you haven't backed up your control files via an ALTER
DATABASE BACKUP CONTROLFILE command, you could find yourself in the
unenviable position of not being able to recover your target.
Another good reason to use a catalog is that information pertaining to
your RMAN backup and recovery activities can be stored for very long
periods of time. If for any reason you had to use a backup set that was
several months old, a catalog provides more flexibility to go back
further in time than with the NOCATALOG option.
With a catalog, you can manage all of your backup and recovery
activities from one repository. The advantage of this is that if you
have multiple databases to maintain, you're storing all of your backup
and recovery metadata in one place.
Finally, when using the catalog, you have greater flexibility during
certain recovery situations. For example, you can use previous
incarnations of the database for recovery.
Catalog
disadvantages
While the advantages of the catalog are substantial, there are a few
potential headaches that you should be aware of:
- Upgrades and compatibility can be problematic.
- A catalog adds complexity.
- A catalog can increase your needs for hardware and DBA resources.
If you're backing up production databases, and you want
the most flexibility possible for any given backup and recovery
scenario, Oracle recommends that you use a catalog database. However,
if you have nonproduction databases for which you want to implement
RMAN functionality, but you don't want the overhead of a catalog, you
may want to consider just using the target control files.
Using RMAN
without a catalog
RMAN can be used out of the box to back up, restore, and recover a
database without setting up a catalog. You may decide not to use a
catalog because you have limited resources or because you want a quick
and easy backup and recovery mechanism for your databases. This section
points out the issues you need to be aware of if you choose this route:
If you are using Oracle8i or an earlier version, you may not be able to
recover your database if you lose all of your control files. If this
happens, you may not be able to recover your database. If you
exclusively use the target control files for the repository, you must
put into place a mechanism that backs up your control files. (This
should be a standard practice anyway.) You should also multiplex your
control files to multiple disk drives. If you do not use a catalog,
make sure that your control files are backed with:
ALTER DATABASE
BACKUP CONTROLFILE ......
Catalog Issues
The creation of the catalog database is fairly straightforward. It is,
after all, just a database, schema, and objects. Here are some issues
you should consider:
- Where to put the database that will host the catalog
- Which database version to use for the catalog
- How to size the catalog
- How to manage multiple target databases
Physical
location of the catalog
You should place the catalog database on a different server than the
target database. If you fail to do this, you jeopardize backup and
recovery operations, because you make it possible to lose both the
catalog and target databases.
The catalog can coexist in a database used for other applications. The
advantage to this is that you leverage existing resources. The
disadvantage is that the performance and availability of your catalog
can be affected by other applications within the same database.
Version of
catalog
What version of Oracle should you use to create the catalog database?
We suggest that the catalog database be created with the latest version
of Oracle in your production environment. This helps to minimize
compatibility issues and complexity when you start to back up your
target databases.
RMAN Configuration
STEP 1 Modify your Net Connnections and Create
the Recovery Catalog (Optional)
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 ONE IS VERY IMPORTANT!!!
Your TNSNAMES.ORA file should have an entry for both
your catalog and target databases. For this example, we will use two
databases,
the catalog database (RMAN) and the target database (PROD).
Then, create the catalog (optional)
Connect to the Oracle RMAN instance by using your DBA account, create
an
account for RMAN, and grant it appropriate rights.
sqlplus
"sys@RMAN as sysdba"
create tablespace RMAN datafile
'.......' size 150M autoextend on next 10M maxzize 700M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE
MANAGEMENT AUTO;
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
rman catalog=rman/rman@RMAN
rman> create catalog;
To drop the catalog run (no backups performed yet):
rman> drop catalog;
STEP 2 Check Flash Recovery Area for your PROD (optional)
This is where the backups are going to be saved. You can check
this parameter by quering query v$recovery_file_dest
view. If
the space is not big enough, then increase it.
If this parameter is not
defined yet, cou can do it by:
alter system db_recovery_file_dest
= '/temp/...' scope=both;
alter system
db_recovery_file_dest_size = 10G scope=both;
(ALTER
SYSTEM set db_recovery_file_dest_size=60G scope=both sid='*' ;
if using RAC)
STEP 3 Register Your Database (only if you
created a catalog)
Now you need to register your target database (PROD) with
the
catalog you've created (if you did it). 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.
Call to the rman
command file FROM your TARGET DB. For example from the PROD box:
rman target / catalog=rman/rman@RMAN
OR
rman target
<qstring> [rcvcat <qstring> | cmdfile <qstring> |
msglog <qstring> | append
| trace <qstring>]
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".
--As a comment, you can start RMAN from any PROD server
using:
rman catalog=rman/rman@RMAN
target=sys/passwd@PROD
or
rman
connect target sys/mh_flyers@PROD
connect rcvcat rman/rman@RMAN
Then Register the Database:
rman> register database
Once complete, the DBID, DB_NAME and structure are
captured
in the recovery catalog. To verify the registration run:
rman> list incarnation;
List of
Database Incarnations
DB Key
Inc Key DB Name DB
ID
CUR Reset SCN Reset Time
-------
------- -------- ---------------- --- ---------- ----------
38
39 PROD
4283267716
YES 19688901 10-MAY-01
STEP 4 Back Up the Catalog and Synchronize
All Databases
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.
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 Setup RMAN Settings
You can always setup the settings from the OEM Console: DB Home
Page/Maint Property / Backup and Recovery. This settings are specified
only one time.
Use the following command to check your settings once you connect:
rman target /
catalog=rman/rman@RMAN
rman> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_ORA920.f'; # default
| Recommended Configuration Parameters | |
configure default device type to disk; |
|
rman> show all;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
#By Default save all the Backups to disk
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
# Keep at least two copies of each datafile and controlfile backup.
# Note that when configuring a retention policy, RMAN will not cause backups to be automatically deleted.
# The retention policy will, however, mark backups as OBSOLETE that have fallen outside the retention period.
# RMAN commands like "REPORT OBSOLETE" and "DELETE OBSOLETE" will work with these obsolete backups.
# If you have certain backups which must be retained longer than this retention policy,
# you can use the KEEP option with the BACKUP command when creating those backups
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
# By default put 2 channels per backup:
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/orabackup/rman/ORA920/backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 5000 M;
# Specifies the location and name of the backup files. We need to specify the format for each channel.
# The format specifier %t is replaced with a 4-byte timestamp, %s with the backup set number, and %p with the backup piece number.
The "%U" ensures that Oracle appends a unique identifier to the backup file name.
The MAXPIECESIZE (optional) attribute sets a maximum file size for each file in the backup set.
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
# Performs backup of controlfile and spfile to the same backup piece each time a backup or copy is performed (default location is FRA).
NOTE= If you are not going to use a recovery catalog, and you wish to be able to recover your control file after an automated control file backup,
you must know the DBID of the database. You should, as a part of your initial setup and configuration of RMAN, note the DBIDs of the databases that
you will be backing up and save that list somewhere safe.
The DBID of the database is available from the V$DATABASE view in the DBID column and it's also displayed when you start RMAN and connect to a target database.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/some_location/rman_backup_controlfile_%d_%F_ctl.bckp';
# Performs the Backup of the controlfile on that place instead of the Flash Recovery Area,
# The "%F" in the file name translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:
* IIIIIIIIII - stands for the DBID. (The DBID is printed in decimal so that it can be easily associated with the target database.
* YYYYMMDD - is a time stamp in the Gregorian calendar of the day the backup is generated.
* QQ - is the sequence in hexadecimal number that starts with 00 and has a maximum of 'FF' (256).
CONFIGURE BACKUP OPTIMIZATION ON;
#If we turn this command ON, then RMAN will not backup already backuped READ ONLY tablespaces
Other Options:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/some_location/snapcf_ORA920.f';
# Where to create the snapshot file for resync
CONFIGURE EXCLUDE FOR TABLESPACE tbl_exclude;
# Excludes tbs_exclude from full database backups.
# NOEXCLUDE can be specified with the BACKUP command to override this configuration.
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; (10g only)
Governs archived redo log deletion policy for the flash recovery area. Possible options :
APPLIED ON STANDBY - enables FRA to delete archivelogs that are applied on mandatory standby.
NONE - enables FRA to delete archivelogs that are backed up to tertiary device and that are obsolete based on the configured backup retention policy.
This is the default configuration.
CLEAR - clears the deletion policy and returns the specified configuration to default value. The default value is NONE.
By default, archived redo log files in a FRA that were backed up to a tertiary device or made obsolete (as defined by the RMAN retention policy) are eligible for deletion.
Examples of the CONFIGURE ARCHIVELOG DELETION POLICY Command:
When backups of archived redo log files are taken on the standby database:
1. Issue the following command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
2. Issue the following command on the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
When backups of archived redo log files are taken on the primary database:
1. Issue the following command on the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
2. Issue the following command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
Reconfiguring the Deletion Policy After a Role Transition :
After a switchover or failover, you may need to reissue the RMAN CONFIGURE ARCHIVELOG DELETION POLICY command on each database.
If the backup site for archived redo log files remains the same, then do nothing.
Otherwise, you must switch the archivelog deletion policy by issuing the
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY statement on the database where backups are not taken, and issuing the CONFIGURE ARCHIVELOG DELETION POLICY TO NONE statement on the database where backups are taken.
Viewing the Current Deletion Policy
To view the current setting (APPLIED ON STANDBY, CLEAR, NONE) for a database,issue the following query:
SELECT NAME, VALUE
FROM V$RMAN_CONFIGURATION
WHERE NAME LIKE '%ARCHIVELOG DELETION POLICY%';
NAME VALUE
----------------------------- --------------
ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY
You can also find the existing configuration using the RMAN SHOW ARCHIVELOG DELETION POLICY command:
RMAN> SHOW ARCHIVELOG DELETION POLICY
RMAN configuration parameters are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
Using Substitution Variables
RMAN can make use of substitution variables in creating format strings to generate filenames. Without due care, non-unique filenames can be generated and, under certain circumstances, this can cause backup data to be overwritten and therefore lost. So be careful; this is not a bug with Oracle, but rather a usage error.
Format Description %p specifies the backup piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 as each backup piece is created. %s specifies the backup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. Also, CREATE CONTROLFILE initializes the counter back to 1. %d specifies the database name. %n specifies the database name, padded on the right with 'x' characters to a total length of 8 characters. For example, if PROD1 is the database name, then PROD1xxx is the padded database name. %t specifies the backup set timestamp, which is a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set. %u specifies an 8-character name constituted by compressed representations of the backup set number and the time the backup set was created. %c (Oracle8i or higher) - specifies the copy number of the backup piece within a set of duplexed backup pieces. If you did not issue the set duplex command, then this variable will be 1 for regular backup sets and 0 for proxy copies. If you issued set duplex, the variable identifies the copy number: 1, 2, 3, or 4. %U (Oracle8i or higher) - specifies a convenient shorthand for %u_%p_%c that guarantees uniqueness in generated backup filenames. If you do not specify a format, RMAN uses %U by default. %U is the default in Oracle8i.
We can always clean these settings to its default values using:
configure ...... clear;
STEP 5.5 Validating Backups (optional)
You can run a test RMAN backup that does not generate any output. The test checks datafiles for physical and logical corruption and that all database files exist and are in the correct locations. For example:
RMAN> backup validate database archivelog all;
If you get:
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
It means that the Archive Log Files doesn't exist (maybe was removed on OS level but not by RMAN). Your options are either to restore the missing file(s), or to perform a crosscheck. To perform a crosscheck, run the following command from within RMAN:
RMAN> crosscheck archivelog all;
This will give non-existing archivelogs status of 'EXPIRED', these are no longer used for backups.
When an archive log crosscheck is performed, RMAN checks each archive log in turn to make sure that it exists on disk (or tape). Those that are missing are marked as unavailable. If you have got missing logs, this won't bring them back. It will allow you to get past this error and back-up the database though.
It is advisable to perform a full backup of the database at this point. Schedule a crosscheck every once in a while (e.g. weekly) in order to prevent this.
And then run:
RMAN> list expired archivelog all;
to see if it lists any missing archive logs.
And then:
RMAN> delete expired;
to remove the expired ones.
STEP 6 Create Database Backups
Now you are ready to run a Full Backup.
The simplest backup is an offline (Cold) backup, you
can:
% rman target
system/manager@PROD
RMAN>
shutdown immediate
RMAN>
startup force dba;
RMAN>
shutdown immediate
RMAN>
startup mount
RMAN>
backup database include current controlfile tag 'before_upgrade';
RMAN> alter
database open;
Note the inclusion of the current control file with the backup, and the
use of the tag to identify the backup. To use this backup of the
database, the control file must be restored from the same backup as the
rest of the database. Adding INCLUDE CURRENT CONTROLFILE ensures that a
usable backup of the control file is included with the backup and
tagged in order to simplify restoring the control file with the rest of
the database.
Now for an Online (Hot) Bakup:
export
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
% rman target
system/manager@PROD
rman> backup
spfile;
#Just to test the backup of this file, it will save it to the default
place (Flash Recovery Area or Disk)
rman> backup
database plus archivelog tag="Full_Database"; #
Will backup to the
default place (Flash Recovery Area
or Disk) with a tag
rman> backup
database format
'/tmp/%U';
#Backup to a Specific Place, the %U generates a unique name.
rman> backup
archivelog all delete
input;
#Backup
All Archive log Files and delete the ones that were backuped from PROD
rman> backup
archivelog all delete all
input;
#Backup
All Archive log Files and delete all of them from PROD
Next we perform a complete database backup using a
single command, we will put a tag and delete obsolete files:
RMAN>
run {
backup database plus archivelog tag="Full_Database";
delete noprompt force obsolete;
}
When you run RMAN in command line mode, it sends the
output to the terminal.
If you specify the msglog
option, then RMAN writes the output to a specified log file instead.
Example:
rman target=/
nocatalog msglog Full_RMAN_Backup.txt
In this example, a sample RMAN script is placed into a
command file called commandfile.rcv. You can run this file from the
operating system command line and write the output into the log file
outfile.txt as follows:
rman TARGET / CATALOG rman/cat@catdb
CMDFILE commandfile.rcv LOG outfile.txt
The Abbreviated Syntax is:
BACKUP [ FULL | INCREMENTAL LEVEL
integer ]
[ CUMULATIVE ]
[ FORMAT '/path/filename' ]
[ TAG tag ]
{DATABASE | TABLESPACE
'name' , .... |
DATAFILE 'name' | number
BACKUPSET ALL | number |
time
CURRENT CONTROLFILE}
(choose one)
[ PLUS ARCHIVELOG [DELETE
INPUT] ]
STEP 7 -
Test Restore process of your backup (optional)
This will do everything except actually restore the database. This is
the best method to determine if your backup is good and usable before
being in a situation where it is critical and issues exist.
The following commands can
be run any time to check if RMAN is capable of restoring
database/tablespace using existing backups.
# Check if database can be restored
RMAN> restore database validate;
# Check if tablespace is restorable
RMAN> restore validate tablespace read_only_tablespace_name
;
# Check if controlfile can be restored
RMAN> restore controlfile validate ;
#check if archivelogs for the past two weeks can be
restored
RMAN> restore archivelog from
time 'sysdate-14' validate;
Some
Points to Remember:
- RMAN doesn't automatically delete obsolete files, you need to specify
the DELETE OBSOLETE command, but if
you are using FRA you don't need to
specify the OBSOLETE command.
- The catalog is usefull ONLY to
save some scripts, except for that, all the RMAN features work equally
well with or without a catalog. The repository is ALWAYS on the
controlfile of the TARGET DB.
- A whole backup is a backup of all database files and current control
file, perform this one using the BACKUP DATABASE command.
- You can specify SQL commands on RMAN like:
rman> sql 'alter
system
archive log current'; #switch log files
Other RMAN
Commands
#Backup Some tablespaces only:
rman> backup tablespace users,
tools;
rman> backup tablespace
SYSTEM format '/u01/oracle/db/AKI1/bck/ora_df%t_s%s_s%p';
#Backup a datafile:
rman>backup datafile copy
'/tmp/system01.dbf';
#Also backup the Archve log Files:
rman>backup ....... plus
archivelog;
#Backup all the archive log Files:
rman> backup archivelog all;
#Backup all the archive log Files and delete them from
their location:
rman> backup archivelog all
delete all input;
#Backup current control file to specific place:
rman> backup current
controlfile to '/.../....';
Step 7 (optional)
- 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 NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export ORACLE_SID=PROD
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.
Understanding the Backup
Process
Now that RMAN is configured, backing up the database
(including backing up all archive redo logs), is as easy as:
| RMAN Statements to Perform Full (online) Database Backup | |
% rman target system/manager@PROD nocatalog msglog rman_full_backup_db_online_ORA920.log |
|
The following table includes all resulting backup sets contained in the control file after performing the above backup routine.
| BS Key |
Backup Type |
Device Type |
Controlfile Included? |
SPFILE Included? |
Piece Number |
Handle (Piece) Name | Start Time |
End Time |
Elapsed Seconds |
Tag | Block Size |
| 2 | Datafile Full | DISK | 1 | /orabackup/rman/ORA920/backup_db_ORA920_S_5_P_1_T_543443342 | 11/28/04 20:29:02 | 11/28/04 20:32:44 | 222 | TAG20041128T202902 | 8,192 | ||
| 2 | /orabackup/rman/ORA920/backup_db_ORA920_S_5_P_2_T_543443342 | 11/28/04 20:32:47 | 11/28/04 20:34:45 | 118 | TAG20041128T202902 | 8,192 | |||||
| 3 | Archived Logs | DISK | 1 | /orabackup/rman/ORA920/backup_db_ORA920_S_6_P_1_T_543443693 | 11/28/04 20:34:53 | 11/28/04 20:34:53 | 0 | TAG20041128T203452 | 512 | ||
| 4 | Datafile Full | DISK | YES | YES | 1 | /orabackup/rman/ORA920/c-2542143170-20041128-01 | 11/28/04 20:34:54 | 11/28/04 20:34:55 | 1 | 8, |
Now, let's talk see actual steps that were performed by RMAN after running the full (online) database backup statements. I attempted to annotate the RMAN log file (below) with the following steps. Note that the first 6 steps were all part of the backup database plus archivelog delete input; statement. The 7th step includes the delete noprompt force obsolete; statement
- The first action performed by RMAN is a log switch (using the alter
system srchivelog current command).
- Next, all existing archived redo logs (including the one just
archived) are backed up to a Archive Log backup set. Note that
at the end of the RMAN statements in our example, this archive log
backup set (backup_db_ORA920_S_4_P_1_T_543443326)
will be removed as it will no longer be needed once the database files
are backed up.
- RMAN then starts the backup of the actual database files. For
this backup set, (BS Key - 2), RMAN creates two backup pieces: backup_db_ORA920_S_5_P_1_T_543443342
and backup_db_ORA920_S_5_P_2_T_543443342.
- After the full database backup, RMAN performs another archive
redo log switch (using the alter system archivelog current
command).
- After the archive redo log switch completes, RMAN creates an Archived
Log backup set (BS Key - 3) of the remaining archived redo logs
(using the backup archivelog all command). This is
backup piece backup_db_ORA920_S_6_P_1_T_543443693.
Keep in mind that this archived redo log backup set includes the redo
logs that occured during the full database backup.
- Finally, the autobackup of the control file and SPFILE
occurs and creates another datafile backup set (BS Key - 4). This is
contained in backup piece c-2542143170-20041128-01.
Since a full database backup will always include datafile 1, which
belongs to the SYSTEM tablespace, there will always be a backup of the
control file and the SPFILE. At this point, all tasks for the backup
database plus archivelog delete input; statement have been
completed.
- The delete noprompt force obsolete; statement is then run to apply the retention policy we have configured. In our example, the first Archive Log backup set (that was performed at the begining on this example) will be removed. This was piece backup_db_ORA920_S_4_P_1_T_543443326.
| RMAN Full (online) Database Backup Log File | |
Recovery Manager: Release 9.2.0.5.0 - Production |
|
Special Considerations for
RAC
- Check that your DB is in archive log mode
- Set up Flash Recovery to save the archive log files and backuped
files to an NFS mounted drive or to
the cluster file system (much better). The directory for Archive Log
Files MUST exist in all the nodes.
- Setup the snapshot control file Location. The snapshot control file
is a temporary snapshot control file that RMAN creates to
re-synchronize from a read-consistent version of the control file. RMAN
only needs a snapshot control file when re-synchronizing with the
recovery catalog or when making a backup of the current control file.
In RAC, the snapshot control file is only needed on the nodes on which
RMAN performs backups; the snapshot control file does not need to be
globally available to all instances in a RAC environment.
You can specify a cluster file system file or a raw device destination
for the location of your snapshot control file.
- Verify connectivity to all the boxes
- Connect to RMAN to verify and set the controlfile persistent
configuration. The controlfiles are shared between the instances. Also
note the target DBID at connection. Perform the following parameter
modifications:
rman> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_ORA920.f'; # default
configure retention policy to redundancy 3; #retain the 3 last backups performed
configure default device type to disk;
configure controlfile autobackup on; #Performs backup of controlfile and spfile each time a backup is performed
configure snapshot controlfile name to '$ORACLE_HOME/dbs/snapcf_ORACLE_SID'; # Where to create the snapshot file for resync
configure controlfile autobackup format for device type disk to '/backup_oltp/%d_%F_ctl.bckp';
#Performs Backups on that place instead of the Flash Recovery Area, The "%F" in the file name instructs RMAN
#to append the database identifier and backup timestamp to the backup filename.
#The database identifier, or DBID, is a unique integer identifier for the database. Note that the control file is only specified for a single location,
#which requires each node to have that directory in each node.
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup_oltp/backup_db_%d_S_%s_P_%p_T_%t';
configure device type disk parallelism 2; #Put 2 because 2 nodes
configure channel 1 device type disk connect 'SYS/password@node1';
configure channel 2 device type disk connect 'SYS/password@node2';
Managing
Archived Redo Logs on RAC
When a node generates an archived redo log, Oracle always records the
filename of the log in the control file of the target database. If you
are using a recovery catalog, then RMAN also records the archived redo
log filenames in the recovery catalog when a re-synchronization occurs.
The archived redo log naming convention that you use is important
because when a node writes to a log with a specific filename on its
file system, the file must be readable by any node that needs to access
this archived redo log. For example, if node 1 archives a log to
/oracle/arc_dest/log_1_100_23452345.arc, then node 2 can only back up
this archived redo log only if it can read
/oracle/arc_dest/log_1_100_23452345.arc on its own file system.
The backup and recovery strategy that you choose depends on how you
configure the archiving destinations for each node. Whether only one
node or all nodes perform archived redo log backups, you need to ensure
that all archived redo logs are backed up. If you use RMAN parallelism
during recovery, then the node that performs recovery must have read
access to all archived redo logs in your cluster.
As an example, if the instance associated with redo thread number 1
sets LOG_ARCHIVE_FORMAT to log_%t_%s_%r.arc, then its archived redo log
files are named:
log_1_1000_23435343.arc
log_1_1001_23452345.arc
log_1_1002_23452345.arc
The primary consideration is to ensure that all archived redo logs
can be read from every node during recovery, and if possible during
backups.
I HIGHLY recommend to use a
CFS to save the archived redo logs from all the nodes to a single
location
- Perform a Backup with differential incremental 0 and
then archived logs using delete input option.
rman> backup incremental
level 0 format '/shared_directory/%d_LVL0_%T_%u_s%s_p%p'
database PLUS ARCHIVELOG format '/shared_directory/%d_ARCGLOG_%T_%u_s%s_p%p'
delete input;
- Backupset Maintenance using the configured retention policy
rman>
list backup summary;
list backup of
database;
list backup of
archivelog all;
list backup of
controlfile;
report obsolete;
delete obsolete or delete noprompt obsolete;
report schema;
- Deleting archived Redo Logs after a Successful Backup
Assuming that you have configured the automatic channels, you can use
the following example to delete the archived logs that you backed up n
times (in this case I'm using a '1' to specify RMAN not to delete the
archive logs unless it has a record of them being backed up at least
once). The device type can be DISK or SBT:
rman> DELETE ARCHIVELOG
ALL BACKED UP 1 TIMES TO DEVICE TYPE disk;
During a delete operation, as long as at least one of the channels
allocated has access to the archived log, RMAN will automatically
schedule the deletion of the specific log on that channel. For a local
archiving scheme, there must be at least one channel allocated that can
delete an archived log. For a CFS archiving scheme, assuming that every
node writes to the archived logs on the same CFS, the archived log can
be deleted by any allocated channel.
If you have not configured automatic channels, then you can manually
allocate the maintenance channels as follows and delete the archived
logs.
rman> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE
TYPE DISK CONNECT 'SYS/oracle@node1';
ALLOCATE CHANNEL FOR MAINTENANCE
DEVICE TYPE DISK CONNECT 'SYS/oracle@node2';
DELETE ARCHIVELOG ALL BACKED UP 1
TIMES TO DEVICE TYPE disk;
Backing up Backups
There is a legitimate need to take a copy of a backup set: for example,
you backup to disk, because restores and recoveries from disk will be
quicker than ones to tape. But you can’t just keep backing up to
disk, because it will run out of room. Instead, you treat the
disk as a staging area… as a new backup to disk is taken, the previous
one is moved off onto tape.
The default backup directory is:
/path1/backup_dir
You need to move the backups to another disk or directory.
/path2/backup_dir/
To move the backupsets directory structure to restore a backup use:
BACKUP DEVICE TYPE disk BACKUPSET
ALL format '/path2/backup_dir/prod9/%d_%U' DELETE INPUT;
To move the backupsets directory to tape and leave them on disk use:
Backup device type sbt backupset
all;
This command would typically be run in a scenario where
the user wants more recent backups to exist on disk and older backups
to exist on tape, but does not need backups to exist on both disk and
tape at the same time. Using DELETE INPUT effectively makes this a move
operation. If you don't use DELETE INPUT then rman can failover if a
error occurs reading the backup backupset.
BACKUP DEVICE TYPE SBT BACKUPSET
CREATED BEFORE 'sysdate-7' DELETE INPUT;
…but (assuming a nightly backup, and room on disk for
just one night’s
backup) you could do this:
backup device type sbt backupset
created before ‘sysdate-1’ delete input;
…and the ‘delete input’ bit ensures that the source backup set is
removed from the disk, after being archived onto tape.
Unregister a
Database 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
rman catalog=rman/rman@rman
target=sys/password@PROD
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@dba1
SQL> 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);
Deleting data
from Catalog
If the backuped files has been deleted from the host, you can perform a
"CLEAN" process running the following:
************************************************************************
--Clean ALL INVALID on RMAN (specially if I delete files manually)
************************************************************************
rman> crosscheck backup;
rman> report obsolete;
rman> delete obsolete;
rman> report schema;
rman> report need backup;
rman> list backup;
rman> list expired backup;
rman> delete expired backup;
rman> list expired backupset;
rman> delete expired backupset;
rman> RESYNC CATALOG;
The advantage of using Image Copies is that they can be
used in manual recovery operations. The disadvantage of them is that
they take more space than backup sets.
Incremental backups
If your database is particularly large, you may want to consider making
incremental backups.
RMAN has the ability to detect which blocks in a datafile have changed
since the last backup and will copy only those modified 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.
Each data block in a datafile contains a system change number (SCN),
which is the SCN at which the most recent change was made to the block.
During an incremental backup, RMAN reads the SCN of each data block in
the input file and compares it to the checkpoint SCN of the parent
incremental backup. If the SCN in the input data block is greater than
or equal to the checkpoint SCN of the parent, then RMAN copies the
block.
Much of the nomenclature around incremental backups contains the term
level. The basic idea behind RMAN level-based backups is to back up
only blocks that have been modified since the previous backup.
Incremental backups can be applied to the database, tablespaces, or
datafiles. Oracle refers to this usage of levels as a multilevel
incremental backup. These levels can range from level 0 to a maximum of
level 4.
There are two flavors of incremental backups -- differential and
cumulative.
A differential incremental backup
tells RMAN to back up blocks that have changed since level n or lower.
For example, if you take a level 1 differential backup, you will back
up blocks that have changed since the previous level 1 backup.
Differential backups are the default incremental backup mode.
A cumulative incremental backup
instructs RMAN to back up blocks that have changed since level n-1 or
lower. For example, if you take a level 1 cumulative backup, RMAN will
back up blocks that have changed since the most recent level 0 backup.
NOTES
If you take an incremental backup
higher than level 0, and no prior level 0 exists, RMAN automatically
creates a level 0 backup
Why all the choices? A differential backup takes less space and time to perform but requires more time to restore. It follows that a cumulative backup takes more space and time to perform but less time to restore. So it becomes a tradeoff issue; do you want to minimize your backup time or minimize your restore time? We prefer to minimize our restore time, and therefore, we use cumulative backups. For small databases, we recommend daily RMAN level 0 backups.
Level 0 is equivalent to a full backup. Level 0
backup of
database is taken by
RMAN>BACKUP INCREMENTAL LEVEL 0 DATABASE;
Level 1 incremental backup can be two types,
1) Differential Level 1 Backup: It backs up all blocks changed after
the most recent incremental backup at level 1.
If no level 1 found then backs up all blocks after most recent
incremental backup at level 0.
If no level 1 and level 0 is found then the behavior is based on
COMPATIBILITY settings.
If compatibility is >=10.0.0, RMAN copies all blocks changed since
the file was created, and stores the results as a level 1 backup.
If compatibility <10.0.0, RMAN generates a level 0 backup.
This is the default level 1 incremental backup. Level 1 backup of
database is taken by
RMAN>BACKUP INCREMENTAL LEVEL 1 DATABASE;
2) Cumulative Level 1 Backup: It backs up all blocks changed after the
most recent incremental backup at level 0. Level 1 cumulative backup
can be taken by,
RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
To improve the performance of incrementally updated
backups another feature called Change Tracking has also been introduced
in Oracle 10g. This lets you record the changes in the blocks of
datafiles in a separate datafile in the database called Change Tracking
File. Then when the time for backup comes, RMAN reads the Change
Tracking File to find out the changes which happened to the database
instead of scanning whole datafile. This makes the backup faster. This
feature is available on Enterprise EDITION Only. You can enable Change
Tracking with:
SQL> alter database enable block change tracking using file
'/usr/local/oracle/testdw0/rman_change_track.dbf';
The Script I used to take an Incrementally Updated Backup is
rman_incr_backup.sh below. This is a script which uses non-default RMAN
location as I explicitly indication where the backups should be stored
on disk. This version doesn't use FRA (Flash Recovery Area) either.
#
########################################
# !/bin/bash
# Unix controls
export
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
trap cleanup 1 2 3 15
cleanup()
{
echo "Caught CTRL-C Signal ...
exiting script."
exit 1
}
# Oracle Variables
export ORACLE_SID=testdw0
export
ORACLE_BASE=/usr/local/oracle
export
ORACLE_HOME=/usr/local/oracle/product/10.2.0.1
export
PATH=$PATH:${ORACLE_HOME}/bin
# RMAN INCREMENTALLY UPDATED
BACKUPS (Window of 24 hours)
rman target=/ << EOF
CONFIGURE CONTROLFILE AUTOBACKUP
ON;
CONFIGURE CONTROLFILE AUTOBACKUP
FORMAT FOR DEVICE TYPE DISK TO
'/usr/local/oracle/backups/rman_bkps/dwbacks/atbckp_cntrlfile_testdw0%F';
run {
ALLOCATE CHANNEL RMAN_BACK_CH01
TYPE DISK FORMAT
'/usr/local/oracle/backups/rman_bkps/dwbacks/databasefiles_%d_%u_%s_%T';
CROSSCHECK BACKUP;
RECOVER COPY OF DATABASE with TAG
'testdw0_incr_update';
BACKUP INCREMENTAL LEVEL 1 FOR
RECOVER OF COPY with TAG 'testdw0_incr_update' DATABASE;
sql 'ALTER SYSTEM ARCHIVE LOG
CURRENT';
BACKUP as compressed backupset
ARCHIVELOG ALL format
'/usr/local/oracle/backups/rman_bkps/dwbacks/archivelogs_%d_%u_%s_%T'
DELETE INPUT;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL RMAN_BACK_CH01;
}
EXIT;
EOF
#########################################
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 Level 0
backup
incremental level 0 tag
= "Weekly_Full_Database" format
'/data/oracle8/BACKUP/rman_LVL0_%d.%t.%p.%c' database;
# Resync the
catalogue
resync catalog;
# Backup the database level 1
backup incremental level 1 tag="Oracle_Backup_Level_1" format
'/data/oracle8/BACKUP/rman_LVL1_%d.%t.%p.%c' database;
or
backup incremental level 1 cumulative tag="Oracle_Backup_Level_1" filesperset 5 format '%d_%t_%s_%p.dbf' database ;
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 --> Contains all changes since Sunday
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.
Example:
Following scenario assumes that you want to take one full database once
a week, doing every day incrementals.
Backup cycle starts on friday, i.e., every friday full backup, and on
other days incrementals.
# Section 1 - Start script for
backup cycle
#
-------------------------------------------
# The following commands are run
each Friday to start the backup cycle.
# The steps are:
# - Take an incremental level 0
backup of the database. A level 0
backup is a complete backup of the entire file which can be used as the
basis for a subsequent incremental backup.
# - Backup all archivelogs that
have not already been backed up.
# - Delete on-disk archivelogs
older than seven days.
run{
backup
incremental level 0 tag =
"Weekly_Full_Database" format
'full_inc1_%u' fileperset 4 database plus
archivelog;
--backup
archivelog all;
delete archivelog until time
'sysdate-4';
delete nopromp obsolete;
}
If the above backup fails for any reaon, you can use
the NOT BACKED UP SINCE option on the BACKUP command (9i restartable
backup feature) to continue from the point of failure. The small value
of FILESPERSET is good for restartable backups. However you should note
that smaller FILESPERSET produces more backup sets.
Use the following commands to re-start backups after a failure:
BACKUP INCREMENTAL LEVEL 0 DATABASE FILESPERSET 4
NOT BACKED UP SINCE TIME 'SYSDATE-1';
BACKUP ARCHIVELOG ALL;
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7';
# Section 2 - script for other
days of backup cycle
#
-----------------------------------------------------
# The following commands can be
run from Saturday through Thursday to
take cumulative incremental backups.
# The steps are:
# - delete incrementals on disk
that were taken before 7 days.
# - take differential incremental
backup of complete database.
# - copy incrementals to tape.
# - backup all archiveogs that
are not backed up.
# - deletes any copies of
archivelog on disk older than 7 days.
run{
delete
backup completed before
'sysdate-7' ;
backup
incremental level 1
cumulative tag="Incremental_Oracle_Backup"
filesperset
4 database plus archivelog;
--backup
archivelog all;
delete
archivelog until time
'sysdate-7';
}
CONCEPT NOTE ON CUMULATIVE INCREMENTAL BACKUPS:
Cumulative incremental
level 1 backups will backup the blocks that changed since the last
level 0 backup. Incremental backups are similar in function to
archived logs and RMAN uses them in favor of archived logs during
recovery. If the CUMULATIVE option was not specified, then only the
blocks that have changed since the last level 1 backup will be backed
up. The advantage of a cumulative backup is that only one incremental
backup ever needs to be applied during recovery.
As in section 1, you can use the NOT BACKED UP SINCE option with the
BACKUP command (9i re-startable backup feature) to continue from the
point of failure.
Use the following commands to re-start backups after a failure:
DELETE BACKUP COMPLETED BEFORE 'SYSDATE-7' DEVICE TYPE DISK;
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DEVICE TYPE DISK DATABASE
FILESPERSET 4
NOT BACKED UP SINCE TIME 'SYSDATE -1 ';
BACKUP BACKUPSET ALL; # copies backups from disk to tape
BACKUP ARCHIVELOG ALL;
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7';
Backup and Recovery Best
Practices (ML Note 388422.1)
1.
Turn on block checking.
REASON: The aim is to detect, very early the presence of
corrupt blocks in the database. This
has a slight performance overhead, but Checksums allow Oracle to detec
early corruption caused by underlying disk, storage system,
or I/O system problems.
SQL> alter system set db_block_checking = true scope=both;
2. Turn on block
tracking when using RMAN backups (if running 10g)
REASON: This will allow RMAN to backup only those blocks that
have
changed since the last full backup, which will reduce the time
taken to back up, as less blocks will be backed up.
SQL>
alter database enable block change tracking using file
‘/u01/oradata/ora1/change_tracking.f’;
3. Duplex log
groups and members and have more than one archive log dest.
REASON: If an archivelog is corrupted or lost, by having
multiple copies in multiple locations, the other logs will still
be available and could be used.
If an online log is
deleted or becomes corrupt, you will have another member that can be
used to recover if required.
SQL> alter system
set log_archive_dest_2='location=/new/location/archive2' scope=both;
SQL> alter database add logfile member '/new/location/redo21.log'
group 1;
4. When backing up the database use the 'check logical'
parameter
REASON: This will cause
RMAN to check for logical corruption within a block as well as the
normal head/tail checksumming. This is the best way to ensure
that you will get a good backup.
RMAN> backup check logical
database plus archivelog delete input;
5.
Test your backup.
REASON: This will do everything except actually restore the
database. This is the best method to
determine if your backup is good and usable before being in a situation
where it is critical and issues exist.
# check if database can be restored
RMAN> restore database validate;
# check if tablespace is restorable
RMAN> restore tablespace read_only_tablespace_name
validate ;
6. Have each datafile in a single backup piece
REASON: When doing a
partial restore RMAN must read through the entire piece to get the
datafile/archivelog requested. The smaller the backup piece
the quicker the restore can complete. This is especially relevent with
tape backups of large databases or where the restore
is only on individual / few files. However you should note that
smaller FILESPERSET produces more backup sets.
RMAN> backup database
filesperset 1 plus archivelog delete input;
7.
Maintain your RMAN catalog/controlfile
REASON: Choose your retention policy carefully. Make sure that
it compliments
your tape subsystem retention policy, requirements for backup recovery
strategy. If not using a catalog, ensure that your
controlfile record keep time instance parameter matches your retention
policy.
SQL> alter system set control_file_record_keep_time=21
scope=both;
This will keep 21 days of backup records.
Run regular catalog maintenance.
REASON: Delete
obsolete will remove backups that are outside your retention policy. If
obsolete backups are not deleted, the catalog will continue
to grow until performance becomes an issue.
RMAN> delete obsolete;
REASON: crosschecking will check that
the catalog/controlfile matches the physical backups. If a backup is
missing, it will set the piece to 'EXPIRED' (missing), so when a
restore
is started, that it will not be eligible, and an earlier backup will be
used. To remove the expired backups from the catalog/controlfile
use the delete expired command.
RMAN> crosscheck backup;
RMAN> delete expired backup;
The CROSSCHECK command has many parameters and formats:
RMAN> crosscheck backup of
database; --Crosscheck all
obsolete datafile backups, arc log backups and control file backups
RMAN> crosscheck backup of
archivelog all;
RMAN> crosscheck backup of
controlfile;
RMAN> crosscheck backup
completed after 'sysdate-7'; -- Check
existence of pieces created in last 7 days
RMAN> crosscheck backup device
type sbt;
RMAN> crosscheck backup of
tablespace users;
RMAN> crosscheck archivelog
all; --Check existence of archivelogs
8. Prepare
for loss of controlfiles.
set autobackup on
REASON: This will ensure that you always have an up
to date controlfile available that has been taken at the end of the
current backup not during.
RMAN> configure controlfile
autobackup on;
keep your backup logs
REASON: The backup log contains parameters for your tape access,
locations on controlfile backups that can be utilised if complete loss
occurs.
9. Test your recovery
REASON:
During a recovery situation this will let you know how the recovery
will go without actually doing it, and can avoid having
to restore source datafiles again.
SQL> recover database test;
10. Do not specify 'delete all input'
when backing up archivelogs
REASON: Delete all input' will backup from one destination
then delete both copies of the
archivelog where as 'delete input' will backup from one location and
then delete what has been backed up. The next backup
will back up those from location 2 as well as new logs from location 1,
then delete all that are backed up. This means that
you will have the archivelogs since the last backup available on disk
in location 2 (as well as backed up once) and two copies backup up
prior to the previous backup.
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 prod_full_backup {
# backup the complete database to disk, backup all Arch Logs and delete them
backup database plus archivelog tag="Full_Database"
format '/data/archdata/DBbackup/%d_t%t_s%s_p%p';
sql "alter system archive log current";
backup archivelog all delete all input;
}
To Drop it from RMAN just perform:
delete script script_name;
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';
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 prod_full_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
export
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
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
RMAN has a few maintenance commands, shown next.
| list
backupset; list backup of database; list backup of archivelog all; |
List backup in catalog |
|
report
need backup; report
obsolete; |
Display a
list of files that need to be backed up based on the retention policy |
|
crosscheck
backup; |
Determines
whether a backup set and its related pieces still exist on media. If a
backup piece exists in the location recorded in the control file of the
target database or in the optional recovery catalog, its status is
marked as AVAILABLE. If it is not at the specified location, it is
marked as EXPIRED. |
|
delete
expired backup of database; |
For Oracle9i,
this command deletes the physical files associated with backup sets and
datafile copies, updates their status in the control file, and removes
their information from the optional recovery catalog (if one is used). In Oracle backups are flagged as EXPIRED if they cannot be found at their recorded location. Deletion of EXPIRED backups removes their information from the control file and from the optional recovery catalog (if one is used). |
|
create
catalog; |
Create
Recovery Catalog. |
|
report
need backup days 2 database; |
To report on
those datafiles that, if restored, would require application of two
days (or more) worth of archived redo log files: |
Could I restore, if I
needed to?
restore
database validate;
report schema;
Report of database
schema
List of Permanent Datafiles
===========================
File Size(MB)
Tablespace
RB segs Datafile Name
---- --------
-------------------- ------- ------------------------
1
540
SYSTEM
***
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\SYSTEM01.DBF
2
270
UNDOTBS1
***
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\UNDOTBS01.DBF
3
290
SYSAUX
***
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\SYSAUX01.DBF
4
5
USERS
***
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\USERS01.DBF
5
859
FG_DATA
***
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\FG_DATA.DBF
List of Temporary Files
=======================
File Size(MB)
Tablespace
Maxsize(MB) Tempfile Name
---- --------
-------------------- ----------- --------------------
1
71
TEMP
32767
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\TEMP01.DBF
RMAN has extensive listing and reporting functionality allowing
you to monitor you backups and maintain the recovery catalog. Here are
a few useful commands:
Lists
The LIST command will query the RMAN repository (control file or
recovery catalog) to produce a record of its contents. Use this command
to obtain data about:
* RMAN-generated files, that
is, backup sets, proxy copies, and image copies.
* Specified objects
contained in the RMAN-generated files, that is, archived logs,
datafiles, and control files.
* Incarnations of a
specified database or of all databases known to the recovery catalog
The primary purpose of the LIST command is to determine which backups
or copies are available. Note that only backups and copies that
completed successfully are stored in the repository.
RMAN records the output of the LIST command to either standard output
or the message log, but not to both at the same time. You can also
control how the output is organized as well as the level of detail in
the output.
The LIST command displays the same files that the CROSSCHECK and DELETE
commands operate on. Consequently, you can issue LIST to see what is in
the repository, and then run CROSSCHECK to ensure that these files
exist on disk or tape.
You can control how the output is displayed by using the BY BACKUP and
BY FILE options of the LIST command and choosing between the SUMMARY
and VERBOSE options. Examples will be provided below.
Let's see a couple of examples:
rman> list
backup;
# lists
backup sets, images copies, backup pieces, and proxy copies
rman> list
backup summary; # lists a
summary of the backup sets
List of Backups
===============
Key TY LV
S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - -----------
--------------- ------- ------- ---------- ---
7
B F A DISK
24-APR-08
1
1
NO TAG20080424T112235
9
B F A DISK
25-APR-08
1
1
NO TAG20080425T195459
10
B A A DISK
25-APR-08
1
1
NO FULL_DATABASE
11
B F A DISK
25-APR-08
1
1
NO TAG20080425T195748
12
B F A DISK
25-APR-08
1
1
NO TAG20080425T195748
13
B A A DISK
25-APR-08
1
1
NO FULL_DATABASE
Status:
A: Available
U: Unavailable –RMAN doesn't knows where it is
X: Expired –RMAN can't find it
rman> list
expired backup;
#
lists
backups not found during crossckeck
rman> list
backup by file;
#
lists in which place is each file from the database
List of Datafile Backups
========================
File Key
TY LV S Ckp SCN Ckp Time #Pieces #Copies
Compressed Tag
---- ------- - -- -
---------- --------- ------- ------- ---------- ---
1
11 B F A
3459225 25-APR-08
1
1
NO TAG20080425T195748
2
11 B F A
3459225 25-APR-08
1
1
NO TAG20080425T195748
3
11 B F A
3459225 25-APR-08
1
1
NO TAG20080425T195748
4
11 B F A
3459225 25-APR-08
1
1
NO TAG20080425T195748
9 B F A
3459109 25-APR-08
1
1
NO TAG20080425T195459
5
11 B F A
3459225 25-APR-08
1
1
NO TAG20080425T195748
List of Archived Log Backups
============================
Thrd Seq
Low SCN Low Time BS Key S #Pieces #Copies
Compressed Tag
---- ------- ---------- ---------
------- - ------- ------- ---------- ---
1
214 3351077 24-APR-08
10 A
1
1
NO FULL_DATABASE
1
215 3358167 24-APR-08
10 A
1
1
NO FULL_DATABASE
1
216 3360062 24-APR-08
10 A
1
1
NO FULL_DATABASE
1
217 3360407 24-APR-08
10 A
1
1
NO FULL_DATABASE
1
218 3392731 25-APR-08
10 A
1
1
NO FULL_DATABASE
1
219 3409205 25-APR-08
10 A
1
1
NO FULL_DATABASE
1
220 3425916 25-APR-08
10 A
1
1
NO FULL_DATABASE
1
221 3435770 25-APR-08
10 A
1
1
NO FULL_DATABASE
1
222 3449518 25-APR-08
10 A
1
1
NO FULL_DATABASE
1
223 3459186 25-APR-08
13 A
1
1
NO FULL_DATABASE
List of Control File Backups
============================
CF Ckp SCN Ckp Time BS
Key S #Pieces #Copies Compressed Tag
---------- --------- ------- -
------- ------- ---------- ---
3459462
25-APR-08 12 A
1
1
NO TAG20080425T195748
List of SPFILE Backups
======================
Modification Time BS Key S
#Pieces #Copies Compressed Tag
----------------- ------- -
------- ------- ---------- ---
25-APR-08
12 A
1
1
NO TAG20080425T195748
24-APR-08
7 A
1
1
NO TAG20080424T112235
rman> list
backup of database;
# Show all backup details for backup sets
List of Backup Sets
===================
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ----------
----------- ------------ ---------------
9
Full 376.00K
DISK
00:00:01 25-APR-08
BP Key: 3 Status: AVAILABLE Compressed: NO Tag:
TAG20080425T195459
Piece Name:
C:\ORACLE\RMAN_BACKUP\BACKUP\BACKUP_DB_DIE_S_9_P_1_T_652996499
List of Datafiles in
backup set 9
File LV Type Ckp
SCN Ckp Time Name
---- -- ---- ----------
--------- ----
4 Full 3459109
25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\USERS01.DBF
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ----------
----------- ------------ ---------------
11
Full 880.00M
DISK
00:02:08 25-APR-08
BP Key: 5 Status: AVAILABLE Compressed: NO Tag:
TAG20080425T195748
Piece Name:
C:\ORACLE\RMAN_BACKUP\BACKUP\BACKUP_DB_DIE_S_11_P_1_T_652996669
List of Datafiles in
backup set 11
File LV Type Ckp
SCN Ckp Time Name
---- -- ---- ----------
--------- ----
1 Full 3459225
25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\SYSTEM01.DBF
2 Full 3459225
25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\UNDOTBS01.DBF
3 Full 3459225
25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\SYSAUX01.DBF
4 Full 3459225
25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\USERS01.DBF
5 Full 3459225
25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\FG_DATA.DBF
rman> list
copy of archivelog all;
# lists all archived redo logs and copies of logs
List of Archived Log Copies
Key Thrd
Seq S Low Time Name
------- ---- ------- - ---------
----
1
1 214 A 24-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_1_214_640688428.DBF
2
1 215 A 24-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00215_640688428.ARC
3
1 216 A 24-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00216_640688428.LOG
4
1 217 A 24-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00217_640688428.LOG
5
1 218 A 25-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00218_640688428.LOG
6
1 219 A 25-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00219_640688428.LOG
7
1 220 A 25-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00220_640688428.LOG
8
1 221 A 25-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00221_640688428.LOG
9
1 222 A 25-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00222_640688428.LOG
10
1 223 A 25-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00223_640688428.LOG
More List Commands:
list backup of
archivelog all;
# lists all archive logs file backups
list
backup of controlfile;
# lists all control file backups
report
obsolete;
delete obsolete or delete noprompt
obsolete;
# Delete un-necessary backups. This command deletes backups based on
the retention policy
To restrict by object,
use
list copy or list backup with the of listObjList condition. For
example,
enter:
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
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; # checks RMAN backups on configured
devices
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
report those backups that are obsolete.
report obsolete;
5. To delete the obsolete:
delete obsolete;
To report on
backups
that are unrecoverable
report unrecoverable database; # examines all datafiles
# Show datafiles that connot currently be recovered
report unrecoverable database;
report unrecoverable tablespace
'USERS';
Recovere
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. An instance can be started up from SQL
Plus or RMAN using the startup command. Three variants of the startup
command, relevant to the present discussion are:
- startup nomount: The instance is started up - i.e. the required memory structures are associated with the pre-existing Oracle process. At this point the instance is not associated with any database.
- startup mount: The instance is started up and the database control file is read. At this point Oracle knows the locations of all files that make up the database. However, the database is not open. It is possible to go from the nomount state to the mount state using the SQL command "alter database mount".
- startup: The instance is started up, the control file is read and the database opened for general use. At this point the instance is fully associated with the database. It is possible to go from nomount / mount to the open state using the SQL command "alter database open"
For Information on how to use Recovery Manager (RMAN) to back up and
restore Oracle Real Application Clusters (RAC) databases click
HERE.
The RESTORE and RECOVER commands are the most common
recovery-related RMAN commands. However, there are several additional
recovery- related commands:
* RESTORE – Restore any of the following files:
SPFILE, Control file, datafile, archivelog, image copy.
* RECOVER – Apply incremental backups and/or redo to
datafiles to bring the file up to the most recent commit (complete
recovery) or up to the specified log sequence number, SCN or time
(point-in-time recovery).
* SET NEWNAME – Restore files to an alternate
location.
* SWITCH – Update the control file to reflect new
file location.
* SET UNTIL – Use in point-in-time (PIT) recoveries
to stop recovery at log sequence, SCN or time.
* ALTER DATABASE OPEN RESETLOGS – Use in PIT
recoveries to open database and reset the log sequence number back to
zero. This creates a new “incarnation” of the database and prevents
logs from the old incarnation from being applied to this new
incarnation. The RESETLOGS option is required if complete recovery was
not used. (It is notable that when using RMAN with a recovery catalog,
recovery across a resetlog operation is supported. This is because the
RMAN catalog knows about earlier incarnations of the database and knows
which logs belong to those incarnations. Refer to the Oracle9i Recovery
Manager User’s Guide, Chapter 10, “Recovering Through a Resetlogs
Operation” for more information.)
* RESET DATABASE – Record the new incarnation of
database in recovery catalog. Used after OPEN RESETLOGS.
Recovery
Methods
In general there are three steps involved in restoring
files:
- Ensure that the target database is started in the appropriate mode for the restoration operation. For lost control files, this will be nomount mode. If the entire database needs to be restored, this will be mount mode. If datafiles that don't belong to the SYSTEM tablespace are damaged, you have the option of keeping the database open and taking only the tablespace(s)/datafile(s) that needs to be restored offline.
- Start RMAN and connect to the target and recovery catalog if one is being used.
- Run the appropriate RMAN RESTORE command to bring back required files. The requested files and the appropriate archived redo log files will be restored.
Once the necessary files are restored, you need to
recover your database and open it for use. You can recover the database
from either RMAN or SQL*Plus.
Preview Files
to Restore
One nice feature is that you can "see" what files are needed for
Recovery. If you have ever wondered exactly what backup files or image
copies RMAN will use to perform restoration, Oracle 10g now offers the RESTORE ... PREVIEW command set to
show exactly what backup pieces or image copies RMAN plans to utilize.
For example, if I wanted to explore exactly what RMAN will choose if I
want to restore the database's SYSTEM tablespace, from within an RMAN
session, I can issue the RESTORE DATAFILE 1 PREVIEW; .
Example: What files will RMAN use during a RESTORE operation?
-- NOTE: these
commands should be
issued from within an active rman session
spool log to c:\restoresummary.txt;
restore datafile 1
preview;
## show what files will be
used to restore the system tablespace's
datafile
restore tablespace USERS
preview;
#
# show what files will be used to restore a specific tablespace
restore database preview
summary;
#
# show a summary for a full database restore
spool log
off;
#
# close the log file
-- The resulting output:
Starting restore at 26-APR-08
using target database control
file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155
devtype=DISK
List of Backup Sets
===================
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ----------
----------- ------------ ---------------
22
Full 880.47M
DISK
00:02:06
25-APR-08
BP Key: 13 Status: AVAILABLE Compressed: NO
Tag: TAG20080425T204941
Piece Name:
C:\ORACLE\RMAN_BACKUP\BACKUP\BACKUP_DB_DIE_S_22_P_1_T_652999781
List of Datafiles in
backup set 22
File LV Type Ckp
SCN Ckp Time Name
---- -- ---- ----------
--------- ----
4 Full 3462522
25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\USERS01.DBF
using channel ORA_DISK_1
List of Archived Log Copies
Key Thrd
Seq S Low Time Name
------- ---- ------- - ---------
----
14
1 227 A 25-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00227_640688428.LOG
Media recovery start SCN is
3462522
Recovery must be done beyond SCN
3462522 to clear data files fuzziness
Finished restore at 26-APR-08
RMAN>
Starting restore at 26-APR-08
using channel ORA_DISK_1
List of Backups
===============
Key TY LV
S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - -----------
--------------- ------- ------- ---------- ---
22
B F A DISK
25-APR-08
1
1
NO TAG20080425T204941
List of Archived Log Copies
Key Thrd
Seq S Low Time Name
------- ---- ------- - ---------
----
14
1 227 A 25-APR-08
C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00227_640688428.LOG
Media recovery start SCN is
3462522
Recovery must be done beyond SCN
3462522 to clear data files fuzziness
Finished restore at 26-APR-08
CASE 1 -
Recovery Full DB or Disaster Recovery
This example shows how to restore all of the datafiles of the target
database. RMAN will go to its last good backup set and restore the
datafiles to the state they were in when that backup set was
created. When restoring database files with RMAN, it reads
the datafile header and makes the determination as to whether the file
needs to be restored. The recovery is done by allocating a channel for
I/O and then issuing the RMAN restore database command. The database
must not be open when restoring or recovering the entire database.
SQL> startup mount;
or
RMAN> startup force mount;
then connect to rman and perform:
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
Another option
SET DBID
<database_id>; # use database id
from RMAN, not required if using recovery catalog
CONNECT TARGET
<target_connect_string>;
STARTUP NOMOUNT;
RUN
{
# You need to allocate channels if
not using recovery catalog.
ALLOCATE CHANNEL FOO TYPE DISK;
# Optionally you can use SET
NEWNAME and SWITCH commands to restore datafiles to a new location.
RESTORE CONTROLFILE FROM
AUTOBACKUP;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
Another
Method:
1- Assuming that we have a full backup under /pruebarman
2. Start RMAN
$ rman target / nocatalog
3. Start the DB (nomount) and restore the pfile
RMAN> set DBID=248804095;
startup nomount;
run
{
restore spfile to pfile
'/oracle/product/10.2.0/dbs/initBAIHONT.ora' from
'/pruebarman/c-248804095-20090130-04';
shutdown abort;
}
4. Start the DB with the pfile that we just restore:
RMAN> set DBID=248804095;
RMAN> startup force nomount
pfile = '/oracle/product/10.2.0/dbs/initBAIHONT.ora';
5. Restore the control files
run
{
restore controlfile from
'/pruebarman/c-248804095-20090130-04';
alter database mount;
}
6. Restore the data files
run
{
restore database;
switch datafile all;
recover database;
}
7. Start the DB
RMAN> alter database open
resetlogs;
CASE 2 - Recovery
from Missing or Corrupted Datafile(s) or Tablespace:
This scenario deals with a situation where a datafile has gone
or is corrupted.
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.
Example, you get the following error message:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
The error message tells us that file# 4 is missing. The DB can be in
mount state, thus, the database control file, which is also the RMAN
repository can be accessed by the instance and by RMAN. We now recover
the missing file using RMAN. The transcript of the recovery session is
reproduced below :
--restore missing datafile - RMAN
applies all logs automatically
RMAN> restore datafile 4;
RMAN> recover datafile 4;
--open database for general use
RMAN> alter database open;
In the previous scenario, the database is already in
the mount state before the RMAN session is initiated. If the database
is not mounted, you should issue a "startup mount" command before
attempting to restore the missing datafile. The database must be
mounted before any datafile recovery can be done.
If the database is already open when datafile
corruption is detected,
you can recover the datafile without shutting down the database. The
only additional step is to take the relevant tablespace offline before
starting recovery. In this case you would perform recovery at the
tablespace level. The commands are:
--offline affected tablespace
RMAN> sql 'alter tablespace USERS offline
immediate';
-- If you want to restore to a
different location, uncomment the following command.
# RMAN> SET NEWNAME FOR DATAFILE 4 TO
'/newdirectory/new_filename.dbf';
or
# RMAN> SET NEWNAME FOR DATAFILE '/old_dir/old_filename.dbf' TO
'/newdirectory/new_filename.dbf';
-- If you
restored to a different location, uncomment the command below to switch
the controlfile to point to the file in the new location.
# RMAN> SWITCH DATAFILE ALL;
--recover offlined tablespace
RMAN> recover tablespace USERS;
or
RMAN> recover datafile 4;
--online recovered tablespace
RMAN> sql 'alter tablespace USERS online';
CASE 3 - Recovery from
Block Corruption
It is possible to recover corrupted blocks using RMAN backups. This is
a somewhat exotic scenario, but it can be useful in certain
circumstances, as illustrated by the following example. Here's the
situation: a user connected to SQLPlus gets a data block corruption
error when she queries a table. Corruption can be reported in alert
logs, trace files or results of SQL queries.. Here's a part of the
session transcript:
# For example, as a result of SQL command
# SQL> select * from emp;
#
#NAME
#ORA-01578: ORACLE data block corrupted (file # 7, block # 233)
#ORA-01578: ORACLE data block corrupted (file # 7, block # 235)
#ORA-01578: ORACLE data block corrupted (file # 4, block # 101)
#ORA-01110: data file 7: '/oracle/dbs/tbs_07.dbf'
#ORA-01110: data file 4: '/oracle/dbs/tbs_04.dbf'
Since we know the file and block number, we can perform
block level recovery using RMAN. This is best illustrated by example:
--restore AND recover specific block
RMAN> blockrecover datafile 7 block 233,235
datafile 4 block 101;
A couple of important points regarding block recovery:
- Block recovery can only be done using RMAN.
- The entire database can be open while performing block recovery.
- Check all database files for corruption. This is important -
there could be other corrupted blocks. Verification of database
files can be done using RMAN or the dbverify utility. To verify using
RMAN simply do a complete database backup with default settings. If
RMAN detects block corruption, it will exit with an error message
pointing out the guilty file/block.
# The BLOCKRECOVER command can also be used to repair
all corrupted blocks
# listed in V$BACKUP_CORRUPTION and V$COPY_CORRUPTION. These views are
# populated whenever an RMAN process peforms a complete scan of a file
for the
# purpose of backing it up, such as with the BACKUP or COPY command.
Use the
# following command to repair all blocks listed in the V$xxx_CORRUPTION
views:
# command:
BLOCKRECOVER CORRUPTION LIST;
CASE 4
- Recovery
from Missing or Corrupted Redo Log Group:
Option 1: A
multiplexed copy of the missing log is available.
If a redo log is missing, it should be restored from a multiplexed
copy, if possible. This is the only way to recover without any losses.
Here's an example, where I attempt to startup from SQLPlus when a redo
log is missing:
ORACLE instance started.
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG'
Database altered.
Option 2: All
members of a log group lost.
In this case an incomplete recovery is the best we can do. We will lose
all transactions from the missing log and all subsequent logs. We
illustrate using the same example as above. The error message indicates
that members of log group 3 are missing. We don't have a copy of this
file, so we know that an incomplete recovery is required. The first
step is to determine how much can be recovered. In order to do this, we
query the V$LOG view (when in the mount state) to find the system
change number (SCN) that we can recover to (Reminder: the SCN is a
monotonically increasing number that is incremented whenever a commit
is issued):
--The database should be in the mount state for v$log access
SQL> select first_change# from v$log where group#=3 ;
FIRST_CHANGE#
-------------
370255
The FIRST_CHANGE# is the first SCN stamped in the missing log. This
implies that the last SCN stamped in the previous log is 370254
(FIRST_CHANGE#-1). This is the highest SCN that we can recover to. In
order to do the recovery we must first restore ALL datafiles to this
SCN, followed by recovery (also up to this SCN). This is an incomplete
recovery, so we must open the database resetlogs after we're done.
Here's a transcript of the recovery session (typed commands in bold,
comments in italics, all other lines are RMAN feedback):
--Restore ENTIRE database to
determined SCN
RMAN>
restore database until scn 370254;
--Recover database
RMAN>
recover database until scn 370254;
--open database with RESETLOGS
(see comments below)
RMAN> alter
database open resetlogs;
The following points should be noted:
- The entire database must be restored to the SCN that has been determined by querying v$log.
- All changes beyond that SCN are lost. This method of recovery should be used only if you are sure that you cannot do better. Be sure to multiplex your redo logs, and (space permitting) your archived logs!
- The database must be opened with RESETLOGS, as a required log has not been applied. This resets the log sequence to zero, thereby rendering all prior backups worthless. Therefore, the first step after opening a database RESETLOGS is to take a fresh backup. Note that the RESETLOGS option must be used for any incomplete recovery.
CASE 5
- Recovery
from Missing or Corrupted Control File:
Option 1: A multiplexed copy of the control file is available.
On startup Oracle must read the control file in order to find out where
the datafiles and online logs are located. Oracle expects to find
control files at locations specified in the CONTROL_FILE initialization
parameter. The instance will fail to mount the database if any one of
the control files are missing or corrupt. A brief error message will be
displayed, with further details recorded in the alert log. The exact
error message will vary depending on what has gone wrong. Here's an
example:
ORACLE instance started.
ORA-00205: error in identifying controlfile, check alert log for more info
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 5447783)
The solution is simple, provided you have at least one uncorrupted control file - replace the corrupted control file with a copy using operating system commands. Remember to rename the copied file. The database should now start up without any problems.
Option 2: All control files lost
What if you lose all your control files? In that case you have no
option but to use a backup control file. The recovery needs to be
performed from within RMAN, and requires that all logs (archived and
current online logs) since the last backup are available. The logs are
required because all datafiles must also be restored from backup. The
database will then have to be recovered up to the time the control
files went missing. This can only be done if all intervening logs are
available.
C:\>rman target /
connected to target database: ORCL (not mounted)
-- set DBID - get this from the name of the controlfile autobackup.
-- For example, if autobackup name is CTL_SP_BAK_C-1507972899-20050124-00 then the DBID is 1507972899.
-- This step will not be required if the instance is started up from RMAN or if you are using a catalog.
RMAN> set dbid 1507972899
executing command: SET DBID
--restore controlfile. The backup is not at the default location so the path must be specified
RMAN> startup nomount
RMAN> restore controlfile from autobackup;
or
RMAN> restore controlfile;
Starting restore at 26/JAN/05
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL01.CTL
output filename=E:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL02.CTL
output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL03.CTL
Finished restore at 26/JAN/05
-- Now that control files have been restored, mount the database.
RMAN> alter database mount;
database mounted
-- All datafiles must be restored, since the controlfile is older than the current datafiles
-- Datafile restore must be followed by recovery up to the current log.
RMAN> restore database;
--Database must be recovered because all datafiles have been restored from backup
RMAN> recover database;
-- Recovery completed. The database must be opened with RESETLOGS
-- because a backup control file was used.
RMAN> alter database open resetlogs;
- Recovery using a backup controlfile should be done only if a current control file is unavailable.
- All datafiles must be restored from backup. This means the database will need to be recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
- As with any database recovery involving RESETLOGS, take a fresh backup immediately.
- Technically the above is an example of complete recovery - since all committed transactions were recovered. However, some references consider this to be incomplete recovery because the database log sequence had to be reset.
Case 6 - Database Point-in-time Recovery
This scenario assumes that all initialization files and the current controlfile are in place and you want to recover to a point in time '2001-04-09:14:30:00'.
The 'SET UNTIL TIME' must match with the variable NLS_DATE_FORMAT.
Prior logging on RMAN set the NLS_DATE_FORMAT in the desired format. For example:
If unix ===> export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS';
If on windows nt ===> set this vaiable in the registery.
STARTUP MOUNT FORCE;
RUN
{
SET UNTIL TIME "TO_DATE('2001-04-09:14:30:00','yyyy-dd-mm:hh24:mi:ss')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
# You must take a new whole database backup after resetlogs since backups of previous incarnation are not easily usable.
Case 7 - Restore Archivelog (may be needed for Oracle 10g with physical standby)
Look and check which logfile sequence numbers are in the gap.
Then login to RMAN and type:
RMAN> list backupset;
List of Archived Logs in backup set 40885
Thrd Seq Low SCN Low Time Next SCN Next Time
1 12870 132214214 31-OCT-05 132240966 31-OCT-05
1 12871 132240966 31-OCT-05 132291702 01-NOV-05
1 12872 132291702 01-NOV-05 132400078 01-NOV-05
1 12873 132400078 01-NOV-05 132504581 01-NOV-05
1 12874 132504581 01-NOV-05 132594119 01-NOV-05
1 12875 132594119 01-NOV-05 132683505 01-NOV-05
I am just showing a few of the Sequence numbers - the output is quite long.
Let’s say I needed 12872-1874.
RMAN> run {
2> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
3> restore archivelog sequence 12872;
4> restore archivelog sequence 12873;
5> restore archivelog sequence 12874;
}
Case 8 - Recovering a Table from Hot backup
1- Since the table is dropped, we need to first recover the same using an interim TEST database as direct recovery is not possible. Let us consider the database name as TEST.
2- We need to identify the data files, control files and the log files of the existing production database. This can be obtained by the following:
select file_name,file_id,status,tablespace_name
from dba_data_files
order by tablespace_name desc;
Also, get the list of the Log files:
select * from v$logfile;
3- Once we obtain the details of the data files and the log files, the next step is to have the INTERIM or the TEMP database created for doing the recovery. For this, we may need to copy the existing INIT.ORA file of the production database from where we need to get the table and get the create control file script of that database. This can be obtained by using the following command or you can restore it from the previous successful online backup:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
The control file script will look as follows:
CREATE CONTROLFILE REUSE DATABASE "<DB1>" NORESETLOGS
NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 1588
LOGFILE
GROUP 1 'D:\ORACLE\ORA92\<DB1>\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\ORACLE\ORA92\<DB1>\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\ORACLE\ORA92\<DB1>\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLE\ORA92\<DB1>\SYSTEM01.DBF',
'D:\ORACLE\ORA92\<DB1>\UNDOTBS01.DBF',
'D:\ORACLE\ORADATA\<DB1>\IMPACT_01.DBF',
'D:\ORACLE\ORADATA\<DB1>\IMPACT2_01.DBF',
'D:\ORACLE\ORADATA\<DB1>\IMPINDEX_01.DBF',
'D:\ORACLE\ORA92\<DB1>\INDX01.DBF',
'D:\ORACLE\ORA92\<DB1>\TOOLS01.DBF',
'D:\ORACLE\ORA92\<DB1>\XDB01.DBF',
'D:\ORACLE\ORADATA\<DB1>\ORWTRC.DBS',
'C:\ORACLE\ORADATA\<DB1>\UNDOTBS02.DBF',
'D:\ORACLE\ORADATA\<DB1>\IMPINDEX_02.DBF'
CHARACTER SET WE8ISO8859P1
;
In the above script, change the Keywords REUSE to SET, NORESETLOGS to RESETLOGS and also the database name to a TEST database name.
Instead of this, you can also use the existing the control files that have been backed up, by just changing the location of the files in the INIT.ORA file so that it points to the new location where the files are restored.
4. Restore the Data files from the previous successful backup to a temporary location and also make the
5. Edit the INIT.ORA file to update the correct location of the restored control files, the database and the Instance name to the TEST database name.
6. Set the Oracle SID value for the new database. I.e. set ORACLE_SID=TEST. Then login to the TEST database as SYSDBA and execute the following command to start with the modified INIT.ORA file as below :
STARTUP pfile = ‘D:\ORACLE\ORA92\ADMIN\inittest.ora MOUNT
7. Check for the tablespace which contains the TABLE that needs to be recovered and drop or mark other tablespaces as OFFLINE which does not need recovery. By doing this, we can expedite the process of recovery. Execute the ALTER DATABASE RENAME FILE command for all the data files so that it has the new location updated in the database dictionary views.
8. Once the above step is done, recover the database by executing the following command :
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
This will prompt you to apply for the Archive log files. So enter the location of the log files or just enter CANCEL which will recover the database.
9. Once the recovery is done, open the database with the RESETLOGS option as below :
SQL> ALTER DATABASE OPEN RESETLOGS;
10. Once this is done, we need to export the TABLE that we need to recover. Use the following command to do the same :
EXP username/password@connectionstring file=<file.dmp> owner=<schemaname> tables=<tab1,tab2> rows=y grants=y consistent=y log=test1.log
This will export the table that needs to be recovered.
11. Once we have the dump, IMPORT the table to the existing production database. Before that please disable all the constraints, triggers and the Referential constraints so that ROW Rejections can be avoided. The import can be done using the following command:
IMP username/password file=<file1.dmp> fromuser=<user1> touser=<user1> tables=<tab1,tab2> rows=y grants=y ignore=y buffer=100000 log=imp.log
12. Check the Import log for any errors and take corrective action based on the same.
13. Enable all the constraints, triggers and the Referential constraints.
Case 9- Restore Backup files from Disk Location other than Default
RMAN looks for the backupfiles to restore within its catalog and therefore there is no RESTORE DATABASE FROM <Disk-Location> command. You can circumvent this constraint using the RMAN command CATALOG START WITH <Disk-Location>. The following example shows how to restore from any disk location:
1. Copy the Backup Set Files to any Disk Location
cp <backupset> /tmp/backup
2. Mount the Database
sqlplus / as sysdba
startup mount;
3. Cleanup RMAN Catalog
rman target /
crosscheck backup;
delete noprompt expired backup of database;
delete noprompt expired backup of controlfile;
delete noprompt expired backup of archivelog all;
list backup;
At this point no backup should be available !
4. Make new Backup Location visible to RMAN Catalog
catalog start with '/tmp/backup';
searching for all files that match the pattern /tmp/backup
List of Files Unknown to the Database
=====================================
File Name: /tmp/backup/PROD_datafile_14_1.bak
File Name: /tmp/backup/PROD_controlfile_17.bak
File Name: /tmp/backup/PROD_archivelog_16_1.bak
File Name: /tmp/backup/PROD_datafile_15_1.bak
Do you really want to catalog the above files (enter YES or NO)? yes
List of Cataloged Files
=======================
File Name: /tmp/backup/PROD_datafile_14_1.bak
File Name: /tmp/backup/PROD_controlfile_17.bak
File Name: /tmp/backup/PROD_archivelog_16_1.bak
File Name: /tmp/backup/PROD_datafile_15_1.bak
list backup;
At this point the backup must be available !
5. Restore and Recover the Database
restore database;
recover database;
alter database open;
Duplicate a Database
In Oracle 10G you have new features to perform this. But the Oracle9i procedure is still valid for 10G as well.
You can use the RMAN DUPLICATE command to create a duplicate database from target database backups while still retaining the original target database.
The duplicate database can be either identical to the original database or contain only a subset of the original tablespaces.
To prepare for database duplication, you must first create an auxiliary instance. For the duplication to work, you must connect RMAN to both the target (primary) database and an auxiliary instance started in NOMOUNT mode.
As part of the duplicating operation, RMAN manages the following:
1) Restores the target datafiles to the duplicate database and performs incomplete recovery by using all available incremental backups and archived logs.
2) Shuts down and starts the auxiliary database.
3) Opens the duplicate database with the RESETLOGS option after incomplete recovery to create the online redo logs.
4) Generates a new, unique DBID for the auxiliary database.
During duplication, RMAN must perform incomplete recovery because the online redo logs in the target are not backed up and cannot be applied to the auxiliary database. The farest that RMAN can go in recovery of the duplicate database is the most recent redo log archived by the target database.
When duplicating a database, you can do the following:
1) Run the DUPLICATE command with or without a recovery catalog
2) Skip read-only tablespaces with the SKIP READONLY clause. Read-only tablespaces are included by default. If you omit them, then you can add them later.
3) Exclude tablespaces from the auxiliary database with the SKIP TABLESPACE clause. You cannot skip the SYSTEM tablespace or tablespaces containing rollback or undo segments.
4) Create the auxiliary database in a new host. If the directory structure is the same on the new host, then you can specify the NOFILENAMECHECK option and reuse the target datafile filenames for the auxiliary datafiles.
5) Use the SET UNTIL command or DUPLICATE command with the UNTIL clause when creating the auxiliary database to recover it to a noncurrent time.
By default, the DUPLICATE command creates the database by using the most recent backups of the target database and then performs recovery to the most recent consistent point contained in the incremental backups and archived logs.
Basic Steps
Perform these tasks before performing RMAN duplication:
Task 1: export ORACLE_SID=AUX
Create an Oracle Password File for the Auxiliary Instance
Task 2: Ensure Oracle Net Connectivity to the Auxiliary Instance. You must connect to the auxiliary instance with SYSDBA privileges, so a password file must exist.
Task 3: Create an Initialization Parameter File for the Auxiliary Instance. Mandatory initialization parameter settings for the auxiliary database:
db_block_size = <same size as the target>
compatible = 9.2.0.0 /* should be the same as the target
CONTROL_FILES=(/dup/oracle/oradata/trgt/control01.ctl, /dup/oracle/oradata/trgt/control02.ctl)
#DB_FILE_NAME_CONVERT=('/oracle/oradata/trgt/','/dup/oracle/oradata/trgt/')
#LOG_FILE_NAME_CONVERT=('/oracle/oradata/trgt/redo','/dup/oracle/oradata/trgt/redo')
CONNECT SYS/oracle@aux AS SYSDBA
STARTUP FORCE NOMOUNT
Task 5: Mount or Open the Target Database
Task 6: Make Sure You Have the Necessary Backups and Archived Redo Logs
Task 7: Allocate Auxiliary Channels if Automatic Channels Are Not Configured
Start RMAN with a connection to the target database, the auxiliary database, and (if you use one) the recovery catalog database.
You can start the RMAN executable on any host so long as it can connect to all the instances.
If you do not have automatic channels configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel within the same RUN command.
If the backups reside on disk, then the more channels you allocate, the faster the duplication will be.
For tape backups, limit the number of channels to the number of devices available for the operation.
This example assumes the following:
- You are using a recovery catalog.
- The target database is on HOST1 and contains eight datafiles.
- You want to duplicate the target to database aux on the same host.
- You want to store the datafiles for AUX under /export/home/oracle/AUX/ subdirectory.
- You want to exclude tablespace INDX from the duplicate database, but keep all of the other tablespaces.
- You want to restore the duplicate db at a noncurrent time.
- You want two online redo logs groups, each with two members of size 200 KB.
- You have configured the default device to disk.
- The auxiliary instance AUX has initialization parameter file in the default location (so the PFILE parameter is not necessary on the DUPLICATE command).
- You start RMAN from the AUX site.
RMAN> CONNECT TARGET system/system@R920;
RMAN> CONNECT CATALOG rman/rman@T920;
RMAN> CONNECT AUXILIARY SYS/oracle;
RMAN> CONFIGURE CHANNEL DEVICE TYPE disk CLEAR;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;
RMAN> CONFIGURE DEVICE TYPE disk PARALLELISM 3;
# note that a RUN command is necessary because you can only execute SET NEWNAME
# within a RUN command
RMAN> RUN
{
# the DUPLICATE command uses an automatic disk channel
set until time "to_date('Jan 29 2003 10:50:00','Mon DD YYYY HH24:MI:SS')";
SET NEWNAME FOR DATAFILE 1 TO '/export/home/oracle/AUX/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/export/home/oracle/AUX/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/export/home/oracle/AUX/example01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/export/home/oracle/AUX/tools01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/export/home/oracle/AUX/users01.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/export/home/oracle/AUX/logmnrts.dbf';
SET NEWNAME FOR DATAFILE 8 TO '/export/home/oracle/AUX/marius01.dbf';
DUPLICATE TARGET DATABASE TO AUX
SKIP TABLESPACE indx
LOGFILE
GROUP 1 ('/export/home/oracle/AUX/redo01a.log',
'/export/home/oracle/AUX/redo01b.log') SIZE 10M REUSE,
GROUP 2 ('/export/home/oracle/AUX/redo02a.log',
'/export/home/oracle/AUX/redo02b.log') SIZE 10M REUSE;
}
More Information HERE (Creating a duplicate database on a new host Note:388431.1)
Duplicate a database on the local server with a different file structure
On this example we will create a new DB called AUX based on one called PROD on the same box.
1. Create the directories for the Datafiles required by the Duplicate database. Ensure that their is enough space on this volume to hold all required files. At a minimum this will be the following: (1 control file, System data files, Undo data files, All datafiles required for the duplicate clone application tablespaces, The number of redo log groups and redo logs as on the production database).
2. Create the directories for the database administration area for the duplicate clone database AUX. This administration area will hold the following directories: bdump, cdump, udump.
3. Next create an init.ora file for the duplicate database. Since we are duplicating the database onto the same server as the original we must convert the file names so there is no conflict: (initAUX.ora)
# Minimum Requirement.
DB_NAME=DUP
INSTANCE_NAME=AUX
CONTROL_FILES=(/u02/oradata/AUX/control01.ctl, /u02/oradata/AUX/control02.ctl, /u02/oradata/AUX/control03.ctl)
background_dump_dest
core_dump_dest
user_dump_dest
log_archive_dest_1
# Convert file names to allow for different directory structure.
DB_FILE_NAME_CONVERT=(/u02/oradata/PROD/,/u02/oradata/AUX/)
LOG_FILE_NAME_CONVERT=(/u01/oradata/PROD/,/u01/oradata/AUX/)
# make sure block_size and compatible parameters match if you are not using the default.
DB_BLOCK_SIZE=8192
COMPATIBLE=9.2.0.0.0
4. From the operating system level ensure the ORACLE_SID environment variable is set to the duplicate clone database
ORACLE_SID=DUP; export ORACLE_SID
We also need to create a password file for the duplicate instance:
orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwAUX password=password entries=10
5. Next add the appropriate entries into the tnsnames.ora and listener.ora files in the $ORACLE_HOME/network/admin directory. Remember to load the new configuration into the listener:
# Added to the listener.ora SID_LIST
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
(SID_NAME = AUX)
)
# Added to the tnsnames.ora
AUX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
)
(CONNECT_DATA =
(ORACLE_SID = AUX)
)
)
# Reload listener
lsnrctl reload
6. Next connect to the duplicate instance: and nomount it:
sqlplus /nolog
conn / as sysdba
Next we create an SPFILE based on the init.ora:
CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/AUX/pfile/initAUX.ora';
STARTUP FORCE NOMOUNT;
7. With the duplicate database mounted, we can now connect to it from RMAN. For the duplication to work we must connect to the original database (TARGET), the recovery catalog (CATALOG) and our duplicate database (AUXILIARY):
ORACLE_SID=AUX; export ORACLE_SID
rman TARGET sys/password@PROD CATALOG rman/rman@RMAN AUXILIARY sys/password@AUX
We can then dupicate the database using one of the following commands:
# Duplicate database to TARGET's current state.
DUPLICATE TARGET DATABASE TO AUX;
# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO AUX UNTIL TIME 'SYSDATE-4';
#Duplicate a database upto a given point in time:
run {
set until time '31-AUG-1999 11:08:00';
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
duplicate target database to AUX;
}
# Duplicate database to TARGET's except a few tablespaces.
DUPLICATE TARGET DATABASE TO AUX skip tablespace indx, users, tools;
Once the process is finished RMAN produces a completion message and you have your duplicate instance.
After you have your database, you can perform several actions:
- Add it to /etc/oratab
- Modify init.ora parameters
- etc
Restoring an
RMAN Backup to Another Node
In certain circumstances, it may be desirable to restore a database
from an RMAN backup onto a machine other than the original host. For
example, to recover data at a given point in time, or to
duplicate a production instance.
The example assumes:
- the target database is on host A
- the database is to be restored onto host B
- the directory structure of host B is different to host A
- the ORACLE_SID will not change for the restored database
- a recovery catalog is being used
- the backups were carried out to disk (for illustrative
purposes, and to disassociate from any media manager specific issues)
The following steps are required:
1- backup the target on host A
2- list the datafile locations on host A
3- make the backup available to host B
4- make a copy of the init.ora available to host B
5- edit the init.ora to reflect directory structure changes
6- configure SQL*Net connectivity from host to the recovery
catalog and duplicated database
7- set up a password file for the duplicated database
8- startup nomount the duplicated database
9- RMAN restore the controlfile(s)
10- mount the database
11- restore and rename the datafiles
12- recover and open the database
These steps are expanded further below.
1.Backup the Target on Host A
The target database needs to be backed up using RMAN. The following is
one example of RMAN doing an online database backup. In this example,
the backup sets are written to disk.
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
#backup
the whole db
backup tag whole_database_open
format
'/oracle/backups/BFS/df_%u'
database;
# switch out of the current
logfile
sql 'alter system archive log
current';
#backup the archived logs
backup archivelog all
format
'/oracle/backups/BFS/al_%u';
# backup a copy of the
controlfile that contains records for the other backups just made
backup current controlfile
tag = cf1
format
'/oracle/backups/BFS/cf_%u';
}
2.List Datafile Locations on
Host A
The datafile numbers and location on host A are required. These
datafile locations will change on host B (see Section 7.3).
select file#, name from
v$datafile;
file# name
-----
------------------------------
1
/oracle/OFA_base/u01/oradata/V805X/system01.dbf
2
/oracle/OFA_base/u01/oradata/V805X/rbs01.dbf
3
/oracle/OFA_base/u01/oradata/V805X/temp01.dbf
4
/oracle/OFA_base/u01/oradata/V805X/tools01.dbf
5
/oracle/OFA_base/u01/oradata/V805X/users01.dbf
6
/oracle/OFA_base/u01/oradata/V805X/users02.dbf
7
/oracle/OFA_base/u01/oradata/V805X/rbs02.dbf
8
/oracle/OFA_base/u01/oradata/V805X/rcvcat.dbf
The log file names should also be recorded (see Section 7.4).
select group#, member from
v$logfile;
group# member
-----
------------------------------
1
/oracle/OFA_base/u01/oradata/V805X/redo01.log
2
/oracle/OFA_base/u01/oradata/V805X/redo02.log
3
/oracle/OFA_base/u01/oradata/V805X/redo03.log
3 Make the Backups Available
to Host B
3.1 Disk Backups
During restore, RMAN will expect the backup sets to be located in the
same directory as written to during the backup. For disk backups, the
DBA can accomplish this in many ways:
- set up an NFS directory, mounted on both host A and host
B
- create the same directory structure on host A and host B
- use of symbolic links on host B
3.2 Tape Backups
The media management software must be configured such that host B is a
media manager client, and can read the backup sets. The media
management vendor should be consulted for support on this issue.
4. and 5. init.ora on host B
The "init.ora" needs to be made available on host B. Any location
specific parameters must be ammended. For example,
- ifile
- *_dump_dest
- log_archive_dest*
- control_files
6. SQL*Net configuration
If running rman from host A:
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on host A to connect to duplicated db
on host B configure listener.ora on host B to accept connections for
duplicated database
If running rman from host B:
a. configure tnsnames.ora on host B to connect to catalog
listener.ora on catalog host remains unchanged
b. configure tnsnames.ora on host B to connect to duplicated db
on host B configure listener.ora on host B to accept connections for
duplicated database
If running rman from host C (ie, neither host A or host B):
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on host C to connect to duplicated db
on host B configure listener.ora on host B to accept connections for
duplicated database
7. Setup Password File
In order to allow RMAN remote connections, a password file must be
setup for the duplicated database. For example,
orapwd
file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=kernel
8. Startup nomount the
database
SVRMGR> startup nomount pfile=<location of
init.ora>
9. RMAN
restore the controlfile(s)
Restore the controlfile(s). For example,
run{
allocate channel c1 type disk;
restore controlfile;
}
10. Mount the
database
SVRMGR> alter database mount;
11. Rename and restore the
datafiles
Rename and Restore the Files, and perform database recovery. RMAN can
be used to change the location of the datafiles from the location on
host A (see Section 2) to the new location on host B. For example,
run {
allocate
channel c1 type disk;
allocate
channel c2 type disk;
allocate
channel c3 type disk;
set
newname for datafile 1 to '/oracle/datafiles/system01.dbf';
set
newname for datafile 2 to '/oracle/datafiles/rbs01.dbf';
set
newname for datafile 3 to '/oracle/datafiles/temp01.dbf';
set
newname for datafile 4 to '/oracle/datafiles/tools01.dbf';
set
newname for datafile 5 to '/oracle/datafiles/users01.dbf';
set
newname for datafile 6 to '/oracle/datafiles/users02.dbf';
set
newname for datafile 7 to '/oracle/datafiles/rbs02.dbf';
set
newname for datafile 8 to '/oracle/datafiles/rcvcat.dbf';
restore
database;
switch
datafile all;
}
11. Recover and open the
database
Perform incomplete recovery:
SVRMGR> recover database using backup controlfile until cancel;
Forward the database applying archived redo log
files until you decide to stop recovery by typing cancel at the prompt
(assuming that you have required archived redo log files in the
log_archive_dest directory). You may archive the source database redo
log files and apply them at the target database if
required.
SVRMGR> alter database open resetlogs;
Note: this will create the online redo logs in the
same location as that on host A. If this directory location does not
exist, then this will fail with:
ora-344 : unable to recreate online log <name>
The workaround is to rename the logfiles prior to
opening the database:
SVRMGR> alter database rename file '<host A location>' to
'<host B location>';
Alternatively, the logfile groups can be dropped and
recreated. However, attempts to drop the current logfile group will
fail. The current logfile must be renamed.
Script to get
information from RMAN
Using Catalog
rem
-----------------------------------------------------------------------
rem Purpose: Determine which DB's are
registered in the recovery catalog
rem Author: Diego Pafumi
rem
-----------------------------------------------------------------------
SELECT * FROM RC_DATABASE;
DB_KEY DBINC_KEY
DBID NAME
RESETLOGS_CHANGE# RESETLOGS
-------- ----------- ------------ ------ --------------------
-----------
1
2 3939414311
OLTP4R
1 27-APR-06
rem
------------------------------------------------------------------------------------
rem Purpose: Determine which tablespaces are
currently stored in the recovery catalog
rem Author: Diego Pafumi
rem
------------------------------------------------------------------------------------
SELECT DB_KEY, DBINC_KEY, DB_NAME,
TS#, substr(NAME,1,20) name, CREATION_CHANGE#, CREATION_TIME
FROM RC_TABLESPACE
order by 4;
DB_KEY DBINC_KEY DB_NAME
TS#
NAME
CHANGE# CRE_DATE
-------- ----------- --------- -----
------------------ --------- ----------
1
2 OLTP4R 0
SYSTEM
3 27-APR-06
1
2 OLTP4R 1
UNDOTBS1
9611 27-APR-06
1
2 OLTP4R 1
SYSAUX
9599 27-APR-06
1
2 OLTP4R 4
TEMP
14023 27-APR-06
1
2 OLTP4R 2
UNDOTBS2
9605 27-APR-06
1
2 OLTP4R 2
USERS
9605 27-APR-06
1
2 OLTP4R 2
WPJ_STW_INDEXES_TBS 9605
27-APR-06
1
2 OLTP4R 2
WPJ_STW_TABLES_TBS 9605
27-APR-06
1
2 OLTP4R 2
WPJ_T01_INDEXES_TBS 9605
27-APR-06
1
2 OLTP4R 2
WPJ_T01_LOBS_TBS
9605 27-APR-06
1
2 OLTP4R 2
WPJ_T01_TABLES_TBS 9605
27-APR-06
1
2 OLTP4R 2
WPJ_T01_INDEXES_TBS 9605
27-APR-06
rem
------------------------------------------------------------------------------------
rem Purpose: Determine which scripts are currently
stored in the recovery catalog
rem Author: Diego Pafumi
rem
------------------------------------------------------------------------------------
SQL> SELECT * FROM
RC_STORED_SCRIPT;
DB_KEY DB_NAME SCRIPT_NAME
-------- --------- ---------------
1
OLTP4R nightlybackup
1
OLTP4R archivebackup
rem
-----------------------------------------------------------------------
rem Purpose: Get information from Catalog
rem Author: Diego Pafumi
rem
-----------------------------------------------------------------------
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');
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 > sysdate-5
and s.completion_time <
sysdate
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;
No catalog
rem
-----------------------------------------------------------------------
rem Filename: rmanlist24.sql
rem Purpose: List completed RMAN backups for the
last 24-hours
rem
(use info from Dictionary - Control File Views)
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:
rmanstat24.sql
rem Purpose:
Summary of all datafiles and archlogs backed up over the
rem
last 24-hours.
rem
Author: Diego Pafumi
rem
-----------------------------------------------------------------------
tti "RMAN Status for past
24-hours"
-- Datafiles Backed up during
past 24 Hours
SELECT dbfiles||' from
'||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*)
numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1);
-- Archlog Files Backed up during
past 24 Hours
SELECT backedup||' from
'||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*)
archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO');
rem
-----------------------------------------------------------------------
rem Filename: rman_run.sql
rem Purpose: Monitor RMAN status, while a backup
is running
rem Author: Diego Pafumi
rem
-----------------------------------------------------------------------
select SID, to_char(start_time, 'DD/MON/YY HH24:MI') START_TIME,
TOTALWORK,
sofar, (sofar/totalwork) * 100
PCT_DONE,
sysdate + TIME_REMAINING/3600/24
END_AT
-- ,elapsed_seconds/60 "ELAPSE
(Min)", round(sofar/totalwork*100,2)
"Complete%"
from
v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';
SID
START_TIME
TOTALWORK
SOFAR DONE END_AT
----------- ----------------- ---------- ---------- ----------
-----------------
287 15/07/08 21:08:58
22824638 21120316 92.5329725 16/07/08 04:19:42
For all
the UNIX scripts, is HIGHLY suggested to use:
export
NLS_DATE_FORMAT='YYYY-MM-DD
HH24:MI:SS'
To get a nice format of the date that the backups starts and
ends.
Script to clean up OLD Trace files
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 {} ;
Script to loop 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
Script for Oracle 9/10 database that shuts down the database, mounts it, backs it up, and opens it:
#!/bin/bash
rman target / catalog rman/rman@RMAN <<EOF
shutdown immediate;
startup mount;
backup database format '/u01/oracle/db/AKI1/bck/ora_df%t_s%s_s%p';
alter database open;
EOF
exit
$ ./rman_offline.bash
Full Database Online Backup and Send e-mail
#!/bin/ksh
#
################################################################################
#* Name: Full_RMAN_Backup.ksh
#*
#* Date: May, 07, 2008
#*
#* Author : Diego Pafumi
#*
#* Description: Performs Full HOT Backup using RMAN
#*
#* Output: Full_RMAN_Backup.txt
#*
#* Exceptions: No handled errors.
#*
#* Usage: Full_RMAN_Backup.ksh
#*
#* Modifications: NONE
################################################################################
##############Initialization parameters ################################
#Set env info for oracle
#sh /home/oracle/DBA_SCRIPTS/Ora_Param.ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=CCOM
export ORACLE_HOME=$ORACLE_BASE/OraHome_1
export PATH=$PATH\:$ORACLE_HOME/bin
export SQL="sqlplus -s /nolog"
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LOGIN="/ as sysdba"
export DATESTAMP=`date '+%C%y%m%d'` #Will show 20080508
export LOG_FILE="Full_RMAN_Backup.txt"
export BACKUP_LOC=/data/u03/HOTBCKP
cd /home/oracle/DBA_SCRIPTS/RMAN
##############End Initialization parameters ################################
$ORACLE_HOME/bin/rman target=/ nocatalog msglog Full_RMAN_Backup_${DATESTAMP}.log <<EOF
crosscheck backup;
crosscheck archivelog all;
delete noprompt force obsolete;
delete noprompt force expired backup;
delete noprompt force expired archivelog all;
#backup full database tag rman_nocat_${ORACLE_SID} plus archivelog delete input tag rman_nocat_${ORACLE_SID} ;
backup database plus archivelog tag="Full_Database";
list recoverable backup;
exit;
EOF
cat Full_RMAN_Backup_${DATE}.log | mail -s "RMAN_Backup" mail_Address
Another Simple FULL DB Backup Script
#!/bin/bash
##############Initialization parameters ################################
#Set env info for oracle
#sh /home/oracle/DBA_SCRIPTS/Ora_Param.ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=CCOM
export ORACLE_HOME=$ORACLE_BASE/OraHome_1
export PATH=$PATH\:$ORACLE_HOME/bin
export SQL="sqlplus -s /nolog"
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LOGIN="/ as sysdba"
export DATESTAMP=`date '+%C%y%m%d'` #Will show 20080508
export LOG_FILE="Full_RMAN_Backup.txt"
export BACKUP_LOC=/data/u03/HOTBCKP
cd /home/oracle/DBA_SCRIPTS/RMAN
#set a date format that includes the time-of-day portion
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; export NLS_DATE_FORMAT
##############End Initialization parameters ################################
# Compress the older archive_log files, so we can keep more on disk,
# have the most recent days uncompressed, and prevent RMAN from deleting them as "obsolete".
cd /data/u03/app/oracle/oradata/CCOM/ARCH/
find . -name 'arc*.log' -mtime +1 -exec nice gzip -9 {} \;
# Run RMAN with "nice" to minimize impact on other processes:
nice $ORACLE_HOME/bin/rman target=/ catalog=rman/[rman_password]@rman log='/home/oracle/DBA_SCRIPTS/rman.log' @/home/oracle/DBA_SCRIPTS/backdb_rman.sql
# Rename today's *.log file to include the current month, day, hour and minute:
mv rman.log rman_`date '+%m%d%H%M'`.log
# The RMAN syntax for a compressed backup is:
#backup as compressed backupset database
# (instead of the simple: "backup database" command, but it took 2-3 times longer when tested
# with 10gR1 on Aug. 11, 2006 and gave only a 33% reduction in space used)
exit
"backdb_rman.sql" Script
delete noprompt obsolete;
crosscheck archivelog all;
delete noprompt expired archivelog all;
#resync catalog;
backup database plus archivelog tag="Full_Database";
exit;
Another FULL DB Backup Script with more control
#!/usr/bin/ksh
# Instructions:
# 1- Determine target instance nls setting for nls_lang parameter.
# SQL> select value from v$nls_parameters where parameter = 'NLS_LANGUAGE';
# SQL> select value from v$nls_parameters where parameter = 'NLS_TERRITORY';
# SQL> select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
# Use above nls information to edit script to change charset parameter.
# charset="LANGUAGE_TERRITORY.CHARACTERSET" # Characterset of the database
##########################################################################
#set -x # Uncomment for debugging the code
##########################################################################
#* Name: RMAN_Full.ksh
#*
#* Date : Sept, 10, 2006
#*
#* Description : Take an RMAN full backup
#*
#* Modifications:
#*
#* Author: Diego Pafumi
#*
#* Usage: RMAN_Full.ksh
#*
#* Used By: CRON job via oracle UNIX account
#* 03 4 * * 0-6 /opt/oracle/DBA_SCRIPTS/RMAN_Full.ksh
#*
#****************************************************************
##############Initialization parameters ################################
#Set env info for oracle
#sh /home/oracle/DBA_SCRIPTS/Ora_Param.ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=CCOM
export ORACLE_HOME=$ORACLE_BASE/OraHome_1
export PATH=$PATH\:$ORACLE_HOME/bin
export SQL="sqlplus -s /nolog"
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LOGIN="/ as sysdba"
export DATESTAMP=`date '+%C%y%m%d'` #Will show 20080508
export LOG_FILE="Full_RMAN_Backup.txt"
export BACKUP_LOC=/data/u03/HOTBCKP
cd /home/oracle/DBA_SCRIPTS/RMAN
#set a date format that includes the time-of-day portion
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 # Characterset of the database
export LOG_FOLDER=/home/oracle/DBA_SCRIPTS/RMAN # Log Location
##############End Initialization parameters ################################
db_status="CLOSED"
archive_log="NOARCHIVELOG"
#Get the day of the week such as Sun, Mon,....,Sat
BACKUP_DAY=`date +%a`
#Get the date in the format YYMMDD
DATE=`date +%Y%m%d`
############# End Initialization Parameters ############################
#
# Check status of database
#
pmon=`ps -ef | egrep pmon_$ORACLE_SID | grep -v grep`
if [ "$pmon" = "" ]; then
db_status="CLOSED"
else
db_status=`sqlplus -s "/ as sysdba" <<EOF
set heading off
set feedback off
set verify off
select status from v\\$instance;
exit
EOF
`
fi
if [ $db_status = "MOUNTED" -o $db_status = "OPEN" ]; then
#
# Get the archivelog-mode of the database
#
archive_log=`sqlplus -s "/ as sysdba" <<EOF
set heading off
set feedback off
set verify off
select log_mode from v\\$database;
exit
EOF
`
fi
if [ $archive_log = "ARCHIVELOG" ]; then
rman target / catalog rman/rman@RMAN msglog /home/oracle/DBA_SCRIPTS/RMAN/Connection.txt <<EOF
#Show Some Information
spool log to '$LOG_FOLDER/Schema$BACKUP_DAY.log'
show all;
crosscheck backup;
report obsolete;
list expired backup;
list expired backupset;
delete noprompt obsolete;
delete expired backup;
delete expired backupset;
report schema;
# RESYNC CATALOG;
spool log off
spool log to '$LOG_FOLDER/Backup$BACKUP_DAY.log'
BACKUP DATABASE PLUS ARCHIVELOG delete input tag="Full_DB_CCOM";
delete noprompt obsolete;
# resync catalog;
spool log off
EOF
else
echo "ERROR ON RMAN BACKUP!!!!" > $LOG_FOLDER/Backup$BACKUP_DAY.log
echo "DATABASE $ORACLE_SID IS NOT OPENED OR NOT IN ARCHIVE LOG MODE" >> $LOG_FOLDER/Backup$BACKUP_DAY.log
fi
#mail -s "RMAN Backup Report for $ORACLE_SID" mail@gmail.com < $LOG_FOLDER/Backup$BACKUP_DAY.log
#mail -s "RMAN Backup Report for $ORACLE_SID" mail@yahoo.com < $LOG_FOLDER/Backup$BACKUP_DAY.log
Another FULL DB Backup Script with more control
#! /bin/sh
# script: prod_rman_backup_job.sh
# Features: This shell script performs an RMAN full database backup of all
# datafiles, archivelogs, control file, init.ora and the orapw file.
# The prod1_disaster_recovery.txt file is also created every time a backup is
# performed for disaster recovery purposes.
# This RMAN backup procedure uses the controlfile for storage
# of information about backup sets.
#
# Output Files: /home/oracle/DBA_SCRIPTS/RMAN/prod_disaster_recovery.txt
#
# Used By: CRON job via oracle UNIX account
# Usage:
# ******** RMAN Oracle database full backup at 4:03 ********
# 03 4 * * 0-6 /opt/server_scripts/prod1_rman_backup_job_1.sh
#
# ---------------------- Revision History ---------------
# Date By Changes
##############Initialization parameters ################################
#Set env info for oracle
#sh /home/oracle/DBA_SCRIPTS/Ora_Param.ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=CCOM
export ORACLE_HOME=$ORACLE_BASE/OraHome_1
export PATH=$PATH\:$ORACLE_HOME/bin
export SQL="sqlplus -s /nolog"
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LOGIN="/ as sysdba"
export DATESTAMP=`date '+%C%y%m%d'` #Will show 20080508
export LOG_FILE="Full_RMAN_Backup.txt"
export BACKUP_LOC=/data/u03/HOTBCKP
cd /home/oracle/DBA_SCRIPTS/RMAN
export ORACLE_OWNER=oracle
export ORACLE_GROUP=dba
export ORACLE_OWNER_PATH=/home/oracle
export ORA_BDUMP_DIR=/data/u01/app/oracle/admin/CCOM/bdump/
export LD_LIBRARY_PATH=/u01/v901/lib:/u01/v901/lib64:/usr/dt/lib
##############End Initialization parameters ################################
# make a backup copy of the init.ora file
cp $ORACLE_HOME/dbs/init$ORACLE_SID.ora $BACKUP_LOC
# make a backup copy of the orapwd file
cp $ORACLE_HOME/dbs/orapw$ORACLE_SID $BACKUP_LOC
# start the rman backup
$ORACLE_HOME/bin/rman target / << EOF
backup database plus archivelog tag="Full_Database";
crosscheck backup;
restore controlfile validate;
restore tablespace SYSTEM validate;
restore archivelog all validate;
list backup of database;
report unrecoverable;
report schema;
report need backup;
report obsolete;
delete noprompt obsolete;
delete noprompt expired backup of database;
delete noprompt expired backup of controlfile;
quit
EOF
# write the SQL file which obtains the list of disaster recovery info
FORMAT_VAR=\'99,999,990.90\';
TS_FREE='SM$TS_FREE'
TS_AVAIL='SM$TS_AVAIL'
exec 3>/home/oracle/DBA_SCRIPTS/RMAN/CCOM_disaster_recovery_get.sql
echo "set echo off">&3
echo "set feedback off">&3
echo "set verify off">&3
echo "set pagesize 0">&3
echo "set linesize 100">&3
echo "spool /data/u03/HOTBCKP/CCOM_disaster_recovery.txt">&3
echo "select '-- file: CCOM_disaster_recovery.txt ' from dual;">&3
echo "select '-- Features: This text file provides a listing of tablespaces' from dual;">&3
echo "select '-- data file names,sizes and user account info. ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select '-- Used By: DBA for disaster recovery purposes ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select 'Report produced on: ' || sysdate from dual;">&3
echo "select 'Database name: CCOM' from dual;">&3
echo "select ' ' from dual;">&3
echo "select 'Tablespace' || 'File' || CHR(9) || CHR(9) || CHR(9) || CHR(9) || 'Size (bytes)' from dual;">&3
echo "select tablespace_name || CHR(9) || CHR(9) || file_name || CHR(9) || CHR(9) || bytes from dba_data_files order by tablespace_name;">&3
echo "select ' ' from dual;">&3
echo "select 'Username' || CHR(9) || 'Status' || CHR(9) || 'Tablespace'|| CHR(9) || 'Temp' || CHR(9) || 'Created' from dual;">&3
echo "select username || CHR(9) || CHR(9) || account_status || CHR(9) || default_tablespace || CHR(9) || CHR(9) || temporary_tablespace || CHR(9) || created from dba_users order by username;">&3
echo "select ' ' from dual;">&3
echo "select 'Tablespace' || CHR(9) || CHR(9) || CHR(9) || 'Status' || CHR(9) || CHR(9) || 'Size (Mb)' || CHR(9) || 'Used (Mb)' || CHR(9) || 'Free (Mb)' from dual;">&3
echo "SELECT D.TABLESPACE_NAME,D.STATUS,TO_CHAR((A.BYTES/1024/1024),$FORMAT_VAR),TO_CHAR(((A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),$FORMAT_VAR),TO_CHAR(((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),$FORMAT_VAR) FROM DBA_TABLESPACES D,SYS.$TS_AVAIL A,SYS.$TS_FREE F WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME AND F.TABLESPACE_NAME(+)=D.TABLESPACE_NAME;">&3
echo "spool off">&3
# check for corrupt blocks found during RMAN backup
echo "check for corrupt blocks found during RMAN backup"
# only use the next line if this script needs run via CRON
#su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/sqlplus /nolog"<< EOF
$ORACLE_HOME/bin/sqlplus /nolog<< EOF
connect / as SYSDBA
set echo on
set verify on
set feedback on
-- check for corruption found by RMAN
select * from V\$BACKUP_CORRUPTION;
-- temporarily change the user dump dest so that
-- the text copy of the control file rebuilding sql commands
-- go into the /u01/backup directory
ALTER SYSTEM SET USER_DUMP_DEST='/data/u03/HOTBCKP';
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER SYSTEM SET USER_DUMP_DEST='/data/u01/app/oracle/admin/CCOM/udump';
-- output the disaster recovery info to /data/u03/HOTBCKP/CCOM_disaster_recovery.txt
@/home/oracle/DBA_SCRIPTS/RMAN/CCOM_disaster_recovery_get.sql
exit;
# ---------------------- Troubleshooting ---------------
# to solve RMAN 6089 error - archivelog file not found, after file deleted
# after being logged into RMAN
# CHANGE ARCHIVELOG ALL VALIDATE;
Another one!!!!
#/bin/bash
# --------------------------------------------------------------------------
# File: backup_database.bash
#
# Autor: Martin Zahn, Akadia AG, 10.10.2008
#
# Purpose: Backup Oracle Database using RMAN (Physical Backup)
# and DataPump (Logical Backup)
#
# Usage: backup_database.bash 1>${BACKUP_LOG} 2>&1
# as a Unix User which belongs to the DBA group
# --------------------------------------------------------------------------
# DEBUG
# set -x
#
# Define Mail Recipient(s)
# ------------------------
SENDTO="<your-mail-address>"
export SENDTO
# Setup ORACLE Environment
# ------------------------
ORAENV=/export/home/oracle/admin
ORACLE_SID=$1
if [ -z "${ORACLE_SID}" ]
then
echo "Usage: $0 ORACLE_SID"
exit 1
fi
if [ ! -f ${ORAENV}/${ORACLE_SID}/.oraenv ]
then
echo "Couldn't find ${ORAENV}/${ORACLE_SID}/.oraenv to set Oracle Environment ... exiting"
exit 1
else
. ${ORAENV}/${ORACLE_SID}/.oraenv
fi
# Logfile
# -------
BACKUP_LOG=/var/log/oracle/backup_database.log
export BACKUP_LOG
# Check that User belongs to the DBA Group
# ----------------------------------------
DBA_GROUP=`/usr/bin/id -G -n | grep "dba" | wc -l`
if [ "${DBA_GROUP}" != "1" ]
then
exit 1
fi
# Keep Backup online for X days
# -----------------------------
BACKUP_WINDOW=1
export BACKUP_WINDOW
# Setup ORACLE Environment
# ------------------------
echo $0
exit 0
if [ ! -f ../.oraenv ]
then
echo "Couldn't find ../.oraenv to set Oracle Environment ... exiting"
exit 1
else
. ../.oraenv
fi
# Set Backup Destination
# ----------------------
BACKUP_DATE=`date +%Y%m%d`; export BACKUP_DATE
BACKUP_DEST=${ORACLE_BASE}/work/backup/${ORACLE_SID}
cd ${BACKUP_DEST} 1>/dev/null 2>&1
if [ `pwd` != ${BACKUP_DEST} ]
then
echo "Backup Directory: ${BACKUP_DEST} not exists ... exiting"
exit 1
fi
# Set DataPump Parameters
# -----------------------
EXPORT_FILE=${BACKUP_DATE}_datapump_full_${ORACLE_SID}.dp
export EXPORT_FILE
EXPORT_LOG=${BACKUP_DATE}_datapump_full_${ORACLE_SID}.log
export EXPORT_LOG
ORACLE_DATAPUMP_DIR=dp${ORACLE_SID};
export ORACLE_DATAPUMP_DIR
# Backup Parameterfiles
# ---------------------
if [ -f ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora ]
then
cp ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora ${BACKUP_DEST}/${BACKUP_DATE}_init${ORACLE_SID}.ora
fi
if [ -f ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora ]
then
cp ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora ${BACKUP_DEST}/${BACKUP_DATE}_spfile${ORACLE_SID}.ora
fi
# Backup Passwordfile
# -------------------
if [ -f ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} ]
then
cp ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} ${BACKUP_DEST}/${BACKUP_DATE}_orapw${ORACLE_SID}
fi
# Start the RMAN physical backup
# ------------------------------
${ORACLE_HOME}/bin/rman nocatalog target / <<-EOF
configure retention policy to redundancy ${BACKUP_WINDOW};
configure retention policy to recovery window of ${BACKUP_WINDOW} days;
configure backup optimization on;
configure controlfile autobackup off;
configure default device type to disk;
configure device type disk parallelism 1 backup type to compressed backupset;
configure datafile backup copies for device type disk to 1;
configure channel device type Disk format '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_datafile_%s_%p.bak';
configure maxsetsize to unlimited;
configure snapshot controlfile name to '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_snapshot_controlfile';
set controlfile autobackup format for device type Disk to '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_controlfile_%F';
show all;
run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup full database noexclude
include current controlfile
format '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_datafile_%s_%p.bak'
tag 'datafile_daily';
}
run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup archivelog all
delete all input
format '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_archivelog_%s_%p.bak'
tag 'archivelog_daily';
}
run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup format '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_controlfile_%s.bak' current controlfile;
}
crosscheck backup;
# -----------------------------------------------------------------------
# The following commands can be run any time to check if RMAN is capable
# of restoring database/tablespace using existing backups.
# -----------------------------------------------------------------------
# restore controlfile validate;
# restore database validate;
# restore archivelog all validate;
# -----------------------------------------------------------------------
list backup of database;
report unrecoverable;
report schema;
report need backup;
report obsolete;
delete noprompt expired backup of database;
delete noprompt expired backup of controlfile;
delete noprompt expired backup of archivelog all;
delete noprompt obsolete recovery window of ${BACKUP_WINDOW} days;
quit
EOF
if [ "$?" != 0 ]
then
cat ${BACKUP_LOG} | mail -s "`uname -n`: Oracle RMAN Backup for ${ORACLE_SID} failed." ${SENDTO}
exit 1
fi
# Store the old Password for SYSTEM
# ---------------------------------
${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<-SAVE_PASSWORD 1>/dev/null 2>&1
set heading off
set pagesize 0
set tab off
set trimspool on
spool /tmp/old_password.lst
SELECT password FROM dba_users WHERE UPPER(username) = 'SYSTEM';
spool off;
SAVE_PASSWORD
OLD_PASSWOD=`cat /tmp/old_password.lst | sed -e 's/^$//' -e '/^ *$/d'`
export OLD_PASSWOD
rm -f /tmp/old_password.lst 1>/dev/null 2>&1
# Change temporarily the SYSTEM Password
# --------------------------------------
${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<-TMP_PASSWORD 1>/dev/null 2>&1
ALTER USER SYSTEM IDENTIFIED BY manager;
TMP_PASSWORD
# Create DataPump Directory
# -------------------------
${ORACLE_HOME}/bin/sqlplus -s "system/manager" <<-DATA_PUMP_DIR 1>/dev/null 2>&1
CREATE OR REPLACE DIRECTORY ${ORACLE_DATAPUMP_DIR} AS '${BACKUP_DEST}';
exit;
DATA_PUMP_DIR
# Start the DataPump logical backup
# ---------------------------------
cd ${BACKUP_DEST}
ORACLE_DATAPUMP_USR_PWD="system/manager";
export ORACLE_DATAPUMP_USR_PWD
${ORACLE_HOME}/bin/expdp userid=${ORACLE_DATAPUMP_USR_PWD} directory=${ORACLE_DATAPUMP_DIR} parallel=5 full=y dumpfile=${EXPORT_FILE} logfile=${EXPORT_LOG}
gzip ${EXPORT_FILE}
# Reset the SYSTEM Password
# -------------------------
${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<-RESET_PASSWORD 1>/dev/null 2>&1
ALTER USER SYSTEM IDENTIFIED BY VALUES '${OLD_PASSWOD}';
RESET_PASSWORD
# Create TAR/GZIP file with all the backuped files
# ------------------------------------------------
cd ${BACKUP_DEST}
if [ "`pwd`" = "${BACKUP_DEST}" ]
then
tar czvf ${BACKUP_DATE}_backupset_${ORACLE_SID}.tgz ${BACKUP_DATE}_*
find . -type f -mtime +${BACKUP_WINDOW} -exec rm -rf {} \;
fi
exit;
#!/bin/ksh
# -----------------------------------------------------------------------
# Filename: purgecat.ksh
# Purpose: RMAN Catalog maintenance:
# Delete RMAN backupsets older than a specified number of days
# Author: Frank Naude, Oracle FAQ
# -----------------------------------------------------------------------
DAYSTOKEEP=60
# -- Do not change anything below this line ---------------------------
CMDFILE=/tmp/rmanpurge$$.rcv
LOGFILE=/tmp/rmanprige$$.log
if [ ! -x $ORACLE_HOME/bin/rman ]; then
echo "ERROR: RMAN not found or ORACLE_HOME not set."
exit 8
fi
if [ ! $ORACLE_SID ]; then
echo "ERROR: ORACLE_SID not set."
exit 8
fi
if [ ! $1 ]; then
echo "USAGE: $0 CatalogConnectString"
exit;
else
RCVCAT=$1
fi;
echo "Delete RMAN backupsets older than $DAYSTOKEEP days for db $ORACLE_SID."
echo "ALLOCATE CHANNEL FOR DELETE TYPE 'SBT_TAPE';" >>$CMDFILE
# List all old entries that needs to be deleted
(rman rcvcat $RCVCAT target / <<-EOF
list backupset of database
from time 'SYSDATE-3000' until time 'SYSDATE-$DAYSTOKEEP';
exit;
EOF
) | grep RMAN-06233 | while read filler key filler filler date rest
do
echo "# Delete backupset $key dated $date..." >>$CMDFILE
echo "CHANGE BACKUPSET $key DELETE;" >>$CMDFILE
done
echo "RELEASE CHANNEL;" >>$CMDFILE
# Delete the old entries
rman rcvcat $RCVCAT target / cmdfile $CMDFILE
echo "Done!"
Another FULL DB Backup Script, with incremental options
This Korn Shell script incorporates the RMAN scripts for Oracle 9i database backup.
It takes incremental or full db backup, deletes the old archived logs and also crosscheck and deletes the two weeks old disk backups.
#!/bin/ksh
################################################################################
#* Name: RMAN_Incremental_Backup.ksh
#*
#* Date: May, 07, 2008
#*
#* Author : Diego Pafumi
#*
#* Description: Perform Incremental RMAN Backup
#*
#* Exceptions: No handled errors.
#*
#* Usage: Incremental_RMAN_Backup.ksh
#*
#* Modifications: NONE
#*
##############Initialization parameters ################################
#Set env info for oracle
. /home/oracle/DBA_SCRIPTS/RMAN/Ora_Param.ksh
export DATESTAMP=`date '+%C%y%m%d'` #Will show 20080508
export LOG_FILE="Full_RMAN_Backup.txt"
export BACKUP_LOC=/data/u03/HOTBCKP
cd /home/oracle/DBA_SCRIPTS/RMAN
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS";
export NLS_LANG=AMERICAN_AMERICA.UTF8;
#Get Date of Week
DOW=`date '+%a'`
CTLBKFILE="'/data/u03/HOTBCKP/`date '+%Y-%m-%d_%H%M%S'`_ctl_bk'"
LEVEL0="'/data/u03/HOTBCKP/`date '+%Y-%m-%d_%H%M%S'`_level0_%U'"
LEVEL1="'/data/u03/HOTBCKP/`date '+%Y-%m-%d_%H%M%S'`_level1_%U'"
LEVEL2="'/data/u03/HOTBCKP/`date '+%Y-%m-%d_%H%M%S'`_level2_%U'"
##############End Initialization parameters ################################
############### FUNCTIONS ################
CTLBK()
{
$ORACLE_HOME/bin/sqlplus / as sysdba << EOF
alter system switch logfile;
alter database backup controlfile to $CTLBKFILE;
exit;
EOF
}
XCHECK()
{
$ORACLE_HOME/bin/rman target / msglog Incremental_CHECK_RMAN_Backup_${DATESTAMP}.log << EOF
allocate channel for maintenance type disk;
delete noprompt force obsolete;
delete noprompt force expired backup;
delete noprompt force expired archivelog all;
crosscheck backup;
crosscheck archivelog all;
release channel ;
exit;
EOF
}
INCR0()
{
$ORACLE_HOME/bin/rman target=/ nocatalog msglog Incremental_RMAN_Backup_${DATESTAMP}.log << EOF
run {
sql "alter system archive log current";
backup incremental level 0 tag = "Level_0_Backup" format $LEVEL0 database plus archivelog;
}
EOF
}
INCR1()
{
$ORACLE_HOME/bin/rman target=/ nocatalog msglog Incremental_RMAN_Backup_${DATESTAMP}.log << EOF
run {
sql "alter system archive log current";
backup incremental level 1 tag = "Level_1_Backup" format $LEVEL1 database plus archivelog;
}
EOF
}
INCR2()
{
$ORACLE_HOME/bin/rman target=/ nocatalog msglog Incremental_RMAN_Backup_${DATESTAMP}.log << EOF
run {
sql "alter system archive log current";
backup incremental level 2 tag = "Level_2_Backup" format $LEVEL2 database plus archivelog;
}
EOF
}
case $DOW in
Mon ) INCR1; CTLBK ;;
Tue ) INCR0; XCHECK; CTLBK ;;
Wed ) INCR0; CTLBK ;;
Thu ) INCR1; XCHECK; CTLBK ;;
Fri ) INCR2; CTLBK ;;
Sat ) INCR0; XCHECK; CTLBK ;;
Sun ) INCR0; XCHECK; CTLBK ;;
esac
$ORACLE_HOME/bin/rman target=/ nocatalog msglog Incremental_LIST_SUMMARY_RMAN_Backup_${DATESTAMP}.log << EOF
list backup summary;
exit;
EOF
#Copy some other files that are important
$ORACLE_HOME/bin/rman target=/ nocatalog << EOF
run {
sql 'create pfile from spfile';
host 'cp $ORACLE_HOME/network/admin/tnsnames.ora $BACKUP_LOC';
host 'cp $ORACLE_HOME/network/admin/listener.ora $BACKUP_LOC';
host 'cp $ORACLE_HOME/dbs/initCCOM.ora $BACKUP_LOC';
EOF
#gzip $BACKUP_LOC/*_1_1
#find $BACKUP_LOC/*.gz -mtime +3 -exec rm {} \;
#Delete the ARCH LOGS from RMAN Directory
find /data/u03/app/oracle/oradata/CCOM/ARCH_RMAN/CCOM1_*.dbf -mtime +5 -exec rm {} \;
mail -s "Incremental_RMAN_Backup" mail@someone.com < Incremental_RMAN_Backup_${DATESTAMP}.log
mail -s "Incremental_CHECK_Backup" mail@someone.com < Incremental_CHECK_RMAN_Backup_${DATESTAMP}.log
mail -s "Incremental_LIST_SUMMARY_Backup" mail@someone.com < Incremental_LIST_SUMMARY_RMAN_Backup_${DATESTAMP}.log
Collected Resources
Oracle10g - Getting Started with Recovery Manager (RMAN) Note:360416.1 GREAT!!
RMAN: RAC Backup and Recovery using RMAN Note: 243760.1
Configuring Recovery Manager and Archiving
Managing Backup and Recovery
10G RMAN Testing Part 1
10G RMAN Testing Part 2
Oracle10g Backup & Recovery by Oracleadvice.com
Overview of the RMAN Environment
Oracle RMAN Demo by psoug
Duplicate a Database Using RMAN
Oracle: Duplicating a Database with Recovery Manager
Creating a Duplicate Database Note: 73912.1
Restoring an RMAN Backup to Another Node Note: 73974.1
Duplicate Database in Oracle9i / Oracle 10G Note: 228257.1
Oracle10G RMAN Database Duplication Note: 259694.1
RMAN and Flash Recovery Area Note: 305796.1
Backup Backupset Note: 151123.1
Create A Production (Full or Partial) Duplicate On The Same Host Note: 388424.1
Backup and Recovery Practices Best Practices. Note: 388422.1
Creating a 9i Data Guard Database with RMAN (Recovery Manager) Note:183570.1
Step By Step Guide To Create Physical Standby Database Using RMAN Note:469493.1
Creating a Standby Database on a new host Note:374069.1
PRINTED and NOT USED
Oracle10g Grid Computing with RAC
RMAN Scripts for 9i RAC
Oracle RAC RMAN Backup
RAC Survival Kit: Real Application Clusters Troubleshooting and Information, Note:203226.1
RAC Survivial Kit: Troubleshooting Manual Backups, Note:207059.1
How to delete Archivelog from multiple locations for a RAC database, Note:360473.1
RMAN Backup Shell Script Example, Note:137181.1
Simple RMAN Scripts For 9i RAC
A Simple Demonstration of RMAN Setup
Simple 10g rman demo
More Information
Making Backups with RMAN: Advanced Topics
Advanced RMAN Recovery Techniques
Duplicating a Database with Recovery Manager
Managing the Recovery Catalog