Topics
configure archivelog deletion policy to backed up 1 times to device type disk;
Quick Backup with Details
run {
allocate channel t1 type
disk;
allocate channel t2 type
disk;
allocate channel t3 type
disk;
# Backup the whole DB
backup database spfile
tag="Full_Database" format
'C:\app\RMAN_Backup\backup_%d_set%s_piece%p_%T_%U' ;
# Switch out of the
current logfile
alter system archive log
current;
# Backup the archived logs. The crosscheck command
will mark the archive files that are not needed as ready to be
deleted
crosscheck archivelog
all;
backup archivelog all
tag="ARCH_BACKUP" format 'C:\app\RMAN_Backup\arch_logs_%u';
# Backup a copy of the
controlfile that contains records for the other backups just
made
backup current controlfile
tag="Control_File" format
'C:\app\RMAN_Backup\rman_backup_controlfile_%d_ctl.bckp'
reuse;
# Creates a controlfile copy on a specific
location
backup as copy current
controlfile format 'C:\app\RMAN_Backup\Control_File_copy.ctl';
#Crosschecking will check that the catalog/controlfile matches
the physical backups. If a backup is missing, it will set the
piece to 'EXPIRED' (missing)
CROSSCHECK BACKUP;
# Delete un-necessary
backups. This command deletes backups based on the retention
policy.
# Ignores any I/O errors,
including those that occur when a backup is missing from disk
or tape
DELETE NOPROMPT force
OBSOLETE;
# Delete the expired
backups.
DELETE NOPROMPT EXPIRED
BACKUP;
RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
RELEASE CHANNEL t3;
}
Also, when the RMAN backup is running, if you want to see the
process, you can query the V$RMAN_STATUS table from sql*plus as
shown below.
SQL> SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME,
END_TIME from V$RMAN_STATUS order by start_time;
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
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.
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.
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.
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.
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. Since version 10g Oracle 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 feature since Oracle 10g is
to 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 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 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)
There are 2 ways to find the DBID:
a. If RMAN Backup text logs are maintained, the DBID can be
found when the initial connection is made to the target , or
b. If autobackup is enabled and has the autobackup format set
(let’s say the format is '%F'), then the filename of the
autobackup has the DBID in it.
For example, 'c-1106578690-20100408-00' is the name of the
autobackup file; so the DBID should be '1106578690'.
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.
You can see this value in your reports as the "BS KEY" column.
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. Backup sets consist of datafiles or
archivelogs. A single backup set cannot contain a
combination of archivelogs and datafiles.
Backup sets consist of one or more individual backup files.
The individual files contained in a backup set are called
backup pieces.
Backup sets can be full or incremental. A full backup is a
backup of all of the blocks that make up a datafile or
datafiles. Recovery Manager allows you to take full backups of
datafiles, datafile copies, tablespaces, archive logs, control
files and databases. Incremental backups copy blocks that have
been changed since a previous backup. Incremental copes can be
taken of datafiles, tablespaces and databases. Recovery
Manager also provides cumulative backups. Cumulative backups
copy all blocks that have been changed since the most recent
incremental backup.
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.
e) Parallel
Backup and Recovery
Recovery Manager is able to parallelize a single backup,
recovery or restore operation, but is unable to process
multiple commands in a stored script in parallel.
The RMAN script below uses three channels to back up three
data files identified by their file number. This creates
three separate server processes each one sending a data stream
to the tape device.
RUN
{
ALLOCATE CHANNEL t1 DEVICE TYPE sbt;
ALLOCATE CHANNEL t2 DEVICE TYPE sbt;
ALLOCATE CHANNEL t3 DEVICE TYPE sbt;
BACKUP DATAFILE 3,4,5 ;
}
- 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
Connection to RMAN
Target
There are several ways to connect to your target DB:
rman target /
rman
target=sys/passwd@PROD
rman
connect
target sys/passwd@PROD
rman target sys/passwd
rman
connect
target sys
or
rman
connect target sys/passwd
Flash Recovery
Area (FRA) and Estimate Size
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.
RMAN File Creation in the
Flash Recovery Area
This section describes RMAN commands or implicit actions
(such as control file autobackup) that can create files in
the flash recovery area, and how to control whether a
specific command creates files there or in some other
destination. The assumption in all cases is that a flash
recovery area has already been configured for your
database. The commands are:
- BACKUP
If you do not specify a FORMAT option to the BACKUP
command, and do not configure a FORMAT option for disk
backups, RMAN creates backup pieces and image copies in
the flash recovery area, with names in Oracle Managed
Files name format.
- Control File Autobackup
RMAN can create control file autobackups in the flash
recovery area. Use the RMAN command CONFIGURE CONTROLFILE
AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR to clear any
configured format option for the control file autobackup
location on disk. Control file autobackups will be placed
in the flash recovery area when no other destination is
configured.
- RESTORE ARCHIVELOG
Explicitly or implicitly (as in the case of ), set one of
the LOG_ARCHIVE_DEST_n) parameters to
'LOCATION=USE_DB_RECOVERY_FILE_DEST'. If you do not
specify SET ARCHIVELOG DESTINATION to override this
behavior, then restored archived redo log files will be
stored in the flash recovery area.
- RECOVER DATABASE or TABLESPACE, BLOCKRECOVER, and
FLASHBACK DATABASE
These commands restore archived redo logs from backup for
use during media recovery, as required by the command.
RMAN restores any redo log files needed during these
operations to the flash recovery area, and delete them
once they are applied during media recovery.
To direct the restored archived redo logs to the flash
recovery area, set one of the LOG_ARCHIVE_DEST_n
parameters to 'LOCATION=USE_DB_RECOVERY_FILE_DEST", and
make sure you are not using SET ARCHIVELOG DESTINATION to
direct restored archived logs to some other destination.
FRA File Retention and Deletion
When FRA space needs exceed quota, automatic file deletion
occurs in the following order:
1. Flashback logs = Oldest Flashback time can be affected
(with exception of guaranteed restore points)
2. RMAN backup pieces/copies and archived redo logs that
are:
• Not needed to
maintain RMAN retention policy, or
• Have been backed
up to tape (via DEVICE TYPE SBT) or secondary disk
location (via BACKUP RECOVERY AREA TO DESTINATION ‘..’)
If archived log deletion policy is configured as:
– APPLIED ON [ALL] STANDBY = Then archived log must have
been applied to mandatory or all standby databases
– SHIPPED TO [ALL] STANDBY = Then archived log must have
been transferred to mandatory or all standby databases
– BACKED UP <N> TIMES TO DEVICE TYPE [DISK | SBT] =
Then archived log must have been backed up at least
<N>times
– If [APPLIEDor SHIPPED] and BACKED UPpolicies are
configured, both conditions must be satisfied for
an archived log to be considered for deletion.
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:
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:
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:
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.
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, create
session, 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)
--As a comment, you can start RMAN from
any PROD server using:
rman
catalog=rman/rman@RMAN target=sys/passwd@PROD
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 ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 3 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_ORA920.f'; # default
Recommended Configuration Parameters | |
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS; |
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
# Keep at least two copies (last 2 copies, not last 2 days) 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
OR
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
# This specify how long you want to retain the backup for.
# This setting is not based on the number of backup copies, but on the number of days for which you want to keep the backup.
# This does not put any condition on the number of backup copies that can be retained; you can have n number of copies within the specified period of the recovery window.
# Any backup that is older than the recovery window is marked as obsolete.
CONFIGURE BACKUP OPTIMIZATION ON;
#If we turn this command ON, then RMAN will not backup already backuped unchanged data files
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
#By Default save all the Backups to disk
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 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 MAXSETSIZE TO UNLIMITED; # default
The MAXPIECESIZE (optional) attribute sets a maximum file size for each file in the backup set.
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.
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/orabackup/rman/snap_controlfile.ora.ORA';
# Where to create the snapshot controlfile for resync
Other Options:
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;
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 :
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;
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. Format YYYYMMDD
%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.
The BACKUP VALIDATE and RESTORE VALIDATE commands perform
the same checks as the VALIDATE command for the files
targeted by the backup or restore command, but they don't
actually perform the specified backup or restore
operation. This allows you to check the integrity of a
backup or restore operation before actually performing it.
The following code shows some of the possible syntax
variations.
# Check for physical
corruption of files to be backed up.
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
# Check for physical
and logical corruption of files to be backed up.
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG
ALL;
# Check for physical
corruption of files to be restored.
RESTORE VALIDATE DATABASE;
# Check for physical
and logical corruption of files to be restored.
RESTORE VALIDATE CHECK LOGICAL DATABASE;
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;
or a Full Validation:
run
{
restore database validate;
restore archivelog from time 'sysdate-1'
validate;
restore controlfile validate;
restore spfile 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>
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 '/.../....' reuse;
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.
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/backup_%d_set%s_piece%p_%T_%U'
database;
# switch out of the current logfile
alter system archive log current;
#backup the archived logs
crosscheck archivelog all;
backup archivelog all format
'/oracle/backups/BFS/arch_logs_%u';
# backup a copy of the controlfile that contains
records for the other backups just made
backup current controlfile tag = "Control_File" format
'/oracle/backups/BFS/ControlFile_%u' reuse;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
RELEASE CHANNEL t3;
}
Also, when
the RMAN backup is running, if you want to see the
process, you can query the V$RMAN_STATUS table from
sql*plus as shown below.
SQL> SELECT OPERATION, STATUS, MBYTES_PROCESSED,
START_TIME, END_TIME from V$RMAN_STATUS order by
start_time;
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
RMAN Full (online) Database Backup Log File | |
Recovery Manager: Release 9.2.0.5.0 - Production |
Delete
specified archivelogs and backups
As part of the regular backup file log maintenance, the
recovery window and redundancy commands are usually used
within backup scripts to define how long the backups
would stay on disk or on tape. For archive log
retention, the delete archivelog command is utilized.
DELETE Archive logs
To clean-up older archives the “backup archive log
delete input” is very commonly used to manage the
archive log retention on disk. This however has a very
serious drawback because as soon as the backup is
finished all the archive logs are purged. In this
scenario if recovery is required then the backup of the
archive from tape has to be restored and then they can
be applied. This increases the downtime due to the
slower recovery rate from tape. Also for environments
that extract data from archive logs, immediate purging
of these log files could interfere with processes like
Golden Gate and Streams, in case these processes are
down for some reason and the backup job fires, it will
remove the archive logs that will be required on the
process start-up.
Thus to retain these archive logs for a longer time the
following can used in the backup script to manage the
archive logs on disk
DELETE archivelog ALL COMPLETED
BEFORE ‘sysdate-1’;
DELETE archivelog ALL BACKED UP 2 TIMES to disk;
DELETE NOPROMPT archivelog UNTIL SEQUENCE = 3790;
Delete Archive log Backups
The following command can be used to manage the backup
of the archive log when storage space needs to be
released.
DELETE BACKUP OF archivelog UNTIL
TIME='sysdate-5';
Obsolete database backups
Backups that fall outside the specified recovery window
can be purged uisng the obsolete command
DELETE OBSOLETE RECOVERY WINDOW OF
4 DAYS;
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;
Nice Script for RAC
backup.sh
#!/bin/csh -x
#
rman_backup_as_copy_to_FS
#
-------------------
# 29-01-07
Alejandro Vargas
#
-------------------
# This script make
a backup copy to file system
# This backup can
be restored on File system as a regular hot backup
# Or can be
restored to ASM by using rman
#
--------------------------------------
# This script
does:
# 1)
Administrative tasks:
# crosscheck
# delete obsolete
# 2) Archive log
current on 1st Instance
# 3) Archive log
current on 2nd Instance
# 4) Rman backup
as copy to file system including controlfile and
archivelogs
# 5) Archive log
current on 1st Instance
# 6) Archive log
current on 2nd Instance
# 7) Rman backup
as copy archivelogs not backed up and print backupset
list to log
#
--------------------------------------
# This script
works with 2 nodes only, if you have more than 2 nodes
you need to customize it.
#
# This script use
aliases and Environment variables set on .cshrc
# to setup the
environment to point to the Database:
# setenv DBS_HOME
/u01/app01/oracle/product/10gDB
# setenv BASE_PATH
/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
# alias 10db
‘setenv $ORACLE_HOME $DBS_HOME; setenv PATH
$ORACLE_HOME/bin:$BASE_PATH’
# This script do
require as parameters the 2 instance names
# It will use them
to archive all required logs from instances 1 and 2
#
--------------------------------------
set v_inst1=mars1
set v_inst2=mars2
# Rman Backup
Location variable
#
-------------------
set
v_rman_loc=/orabackup/rman
# Step 1:
Administrative tasks, crosscheck and delete obsolete
#
-------------------
#10db
setenv ORACLE_SID
$v_inst1
rman target /
nocatalog <<EOF
crosscheck
backupset;
crosscheck copy;
crosscheck
archivelog all;
delete noprompt
expired backup ;
delete noprompt
obsolete;
exit
EOF
# This script run
from 1st node. We use an external identified DBA user,
ops$oracle, to execute
# the archive log
current. From the same session we connect as
ops$oracle into the 2nd instance
# You need
remote_os_authent=TRUE on both instances to connect
remotely without password
# Step 2: Archive
log current on 1st Instance
# Step 3: Archive
log current on 2nd Instance
#
--------------------------------------
sqlplus -s
sys/pass@$v_inst1 as sysdba << EOF
select
instance_name from v\$instance
/
alter system
archive log current
/
connect
sys/pass@$v_inst2 as sysdba;
select
instance_name from v\$instance
/
alter system
archive log current
/
exit
EOF
# On step 4 we use
4 channels. This needs to be customized according the
number of cpu’s/IO
# channels
available. Rman is invoked in nocatalog mode, we need
to have configured
# ORACLE_HOME,
ORACLE_SID and PATH on the environment, as we did in
the previous steps.
# Step 4: Rman
backup as copy to file system including controlfile
and archivelogs
#
--------------------------------------
rman target /
nocatalog <<EOF
run {
allocate channel
backup_disk1 type disk format ‘$v_rman_loc/%U’;
allocate channel
backup_disk2 type disk format ‘$v_rman_loc/%U’;
backup as COPY tag
‘%TAG’ database include current controlfile;
release channel
backup_disk1;
release channel
backup_disk2;
}
exit
EOF
# Step 5 and 6:
Archive log current on 1st and 2nd Instances
#
--------------------------------------
sqlplus -s
sys/pass@$v_inst1 as sysdba << EOF
select
instance_name from v\$instance
/
alter system
archive log current
/
connect
sys/pass@$v_inst2 as sysdba;
select
instance_name from v\$instance
/
alter system
archive log current
/
exit
EOF
# Step 7: Rman
backup as copy archivelogs not backed up and print
backupset list to log
rman target /
nocatalog <<EOF
backup as copy
archivelog all format
‘$v_rman_loc/%d_AL_%T_%u_s%s_p%p’ ;
list backupset;
exit
EOF
# Redirecting rman
output to log will suppress standard output, because
of that
# running
separately.
rman target /
nocatalog log=$v_rman_loc/backupset_info.log
<<EOF
list backup
summary;
list backupset;
list backup of
controlfile;
exit
EOF
# eof backup
Another RAC on Linux with ASM RMAN Backup
Script:
#!/bin/ksh
set -x
# Script :
backupdb
# Alejandro Vargas
# March 6, 2007
# Backup database
and archived logs required to recover it out of ASM to
File System
# Backup as copy
generate datafile copies that can be used to manually
clone the database
# Environment
Variables
#
---------------------
export
ORACLE_SID=racdbtst1
export
ORACLE_BASE=/oradisk/app01/oracle
export
ORACLE_HOME=/oradisk/app01/oracle/product/10gDB
# Path Variables
# --------------
export
BASE_PATH=/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
export
PATH=${ORACLE_HOME}/bin:${BASE_PATH}
# Script Variables
# ----------------
# v_logvw = set
the name of dynamic view v$log to variable v_logvw to
be used inside a command.
# v_scni1 =
initial sequence thread1
# v_scnf1 = final
sequence thread1
# v_scni2 =
initial sequence thread2
# v_scnf2 = final
sequence thread2
# v_bdest = backup
destination
v_logvw=gv\$log
v_scni1=`echo
"select 'xX '||min(sequence#) from $v_logvw where
thread#=1;" | sqlplus -s '/as sysdba' | grep "xX" |awk
'{print $2}'`
v_scni2=`echo
"select 'xX '||min(sequence#) from $v_logvw where
thread#=2;" | sqlplus -s '/as sysdba' | grep "xX" |awk
'{print $2}'`
v_bdest=/vmasmtest/BACKUP/rman_backups/
# Backup database
# ---------------
rman target /
nocatalog <<EOF
run {
alter system
archive log current;
allocate channel
backup_disk1 type disk format '$v_bdest/%U';
allocate channel
backup_disk2 type disk format '$v_bdest/%U';
allocate channel
backup_disk3 type disk format '$v_bdest/%U';
allocate channel
backup_disk4 type disk format '$v_bdest/%U';
backup as COPY
database include current controlfile;
release channel
backup_disk1;
release channel
backup_disk2;
release channel
backup_disk3;
release channel
backup_disk4;
alter system
archive log current;
}
exit
EOF
v_scnf1=`echo
"select 'xX '||max(sequence#) from $v_logvw where
thread#=1;" | sqlplus -s '/as sysdba' | grep "xX" |awk
'{print $2}'`
v_scnf2=`echo
"select 'xX '||max(sequence#) from $v_logvw where
thread#=2;" | sqlplus -s '/as sysdba' | grep "xX" |awk
'{print $2}'`
rman target /
nocatalog <<EOF
run {
allocate channel
backup_disk1 type disk format '$v_bdest/%U';
allocate channel
backup_disk2 type disk format '$v_bdest/%U';
backup as copy
archivelog
sequence between
$v_scni1 and $v_scnf1 thread 1;
backup as copy
archivelog
sequence between
$v_scni2 and $v_scnf2 thread 2;
release channel
backup_disk1 ;
release channel
backup_disk2 ;
}
exit
EOF
# Clean Catalog
# -------------
rman target /
nocatalog <<EOF
crosscheck
backupset;
crosscheck copy;
crosscheck
archivelog all;
delete noprompt
expired backup ;
delete noprompt
obsolete;
exit
EOF
rman target /
nocatalog log=$v_bdest/backuset_info.log <<EOF
list backupset;
exit
EOF
# Eof backupdb
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;
An Image Copy is a Full OS type copy
of a single file
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.
By default the BACKUP command in RMAN creates
BackupSet(s) -- each of which is one or more
BackupPiece(s). A datafile may span
BackupPieces but may not span a BackupSet.
However, RMAN does allow another method -- BACKUP AS
COPY. This is like a "User Managed Backups"
created with OS commands -- except that the ALTER
TABLESPACE | DATABASE BEGIN BACKUP command does not
have to be issued.
BACKUP AS COPY creates a byte-for-byte copy of each
datafile [except, blocks being modified by
concurrent writes to the datafile].
If an active datafile is corrupted, the DBA can
choose to SWITCH TO COPY instead of having to
restore the datafile copy. Thus, a switch can
be a fast operation. Obviously, the DBA must
plan carefully where he creates such copies if he
intends to SWITCH anytime later (he wouldn't
keep a datafile copy on a non-protected [RAID or
ASM] storage target).
Here's a simple demo :
SQL> select
file_id, file_name from dba_data_files where
tablespace_name = 'ADD_TBS';
FILE_ID FILE_NAME
----------
----------------------------------------
14 /oradata/orcl/add_tbs_01.dbf
SQL> alter
tablespace add_tbs add datafile
'/oradata/orcl/add_tbs_02.dbf' size 100M;
SQL> create
table scott.add_table tablespace add_tbs as select
* from dba_source;
SQL> select
file_id, count(*) from dba_Extents where owner =
'SCOTT' and segment_name = 'ADD_TABLE' group by
file_id order by 1;
FILE_ID COUNT(*)
----------
----------
14
48
15
37
So, I have begun with two datafiles and extents
spread across both datafiles. I now take backups of
the two datafiles.
RMAN> backup as copy datafile 14 format
'/tmp/ADDTBS01.DBF';
RMAN>
backup as copy datafile 15 format
'/tmp/add_tbs_second.dbf' ;
RMAN> list
backup of tablespace add_tbs completed after
"sysdate-1";
specification
does not match any backup in the repository
RMAN> list
backup of datafile 14;
List of Backup
Sets
===================
BS Key
Type LV Size
Device Type Elapsed Time Completion Time
------- ----
-- ---------- ----------- ------------
---------------
18
Full 530.30M
DISK
00:01:44
23-MAR-12
BP Key: 18 Status: AVAILABLE
Compressed: YES Tag: TAG20120323T231454
Piece Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp
List of
Datafiles in backup set 18
File LV
Type Ckp SCN Ckp Time Name
---- --
---- ---------- --------- ----
14 Full
5192430 23-MAR-12
/oradata/orcl/add_tbs_01.dbf
BS Key
Type LV Size
Device Type Elapsed Time Completion Time
------- ----
-- ---------- ----------- ------------
---------------
21
Full 530.70M
DISK
00:01:23
23-MAR-12
BP Key: 21 Status: AVAILABLE
Compressed: YES Tag: TAG20120323T235528
Piece Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T235528_7ps77jf2_.bkp
List of
Datafiles in backup set 21
File LV
Type Ckp SCN Ckp Time Name
---- --
---- ---------- --------- ----
14 Full
5194695 23-MAR-12
/oradata/orcl/add_tbs_01.dbf
BS Key
Type LV Size
Device Type Elapsed Time Completion Time
------- ----
-- ---------- ----------- ------------
---------------
24
Full
1.23M
DISK
00:00:00
24-MAR-12
BP Key: 24 Status: AVAILABLE
Compressed: NO Tag: TAG20120324T001527
Piece Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp
List of
Datafiles in backup set 24
File LV
Type Ckp SCN Ckp Time Name
---- --
---- ---------- --------- ----
14 Full
5196666 24-MAR-12
/oradata/orcl/add_tbs_01.dbf
BS Key
Type LV Size
Device Type Elapsed Time Completion Time
------- ----
-- ---------- ----------- ------------
---------------
29
Full 487.60M
DISK
00:00:00
01-JAN-12
BP Key: 29 Status: AVAILABLE
Compressed: YES Tag: TAG20120101T112516
Piece Name:
/newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
List of
Datafiles in backup set 29
File LV
Type Ckp SCN Ckp Time Name
---- --
---- ---------- --------- ----
14 Full
4956816 01-JAN-12
/oradata/orcl/add_tbs_01.dbf
Notice how a LIST BACKUP doesn't show the backups !
Why ? Because it shows only BACKUPSET Backups ! If I
want to see these BACKUP AS COPY backups, I must use
LIST COPY :
RMAN>
list copy of datafile 14;
List of
Datafile Copies
=======================
Key
File S Completion Time Ckp SCN
Ckp Time
------- ---- -
--------------- ---------- ---------------
5
14 A
03-JUN-12
5353830
03-JUN-12
Name: /tmp/ADDTBS01.DBF
Tag: TAG20120603T224508
4
14 A
24-MAR-12
5196322
24-MAR-12
Name:
/home/oracle/app/oracle/oradata/orcl/add_tbs.dbf
RMAN> list
copy of datafile 15;
List of
Datafile Copies
=======================
Key
File S Completion Time Ckp SCN
Ckp Time
------- ---- -
--------------- ---------- ---------------
6
15 A
03-JUN-12
5353869
03-JUN-12
Name: /tmp/add_tbs_second.dbf
Tag: TAG20120603T224538
What happens when I lose the datafiles
?
SQL> !rm
/oradata/orcl/add_tbs_0[1-2].dbf
SQL> select count(*) from scott.add_table;
COUNT(*)
----------
703021
SQL> alter system checkpoint;
SQL> select count(*) from scott.add_table;
COUNT(*)
----------
703021
SQL> alter system flush buffer_cache;
SQL> select count(*) from scott.add_table;
COUNT(*)
----------
703021
SQL> alter tablespace add_tbs offline;
SQL> alter tablespace add_Tbs online;
alter
tablespace add_Tbs online
*
ERROR at line
1:
ORA-01157:
cannot identify/lock data file 14 - see DBWR trace
file
ORA-01110:
data file 14: '/oradata/orcl/add_tbs_01.dbf'
As an aside : Note how the extents
and datafiles seemed to be accessible until I tried
to ONLINE the datafiles.
Now, I do not have regular backups. Can I use the
datafile copies ? YES.
RMAN>
restore datafile 14;
RMAN>
switch datafile 15 to copy;
datafile 15
switched to datafile copy
"/tmp/add_tbs_second.dbf"
I actually restored datafile 14 to the
target location but only switched datafile 15 to the
copy. This is clearly evidenced by :
SQL> select
file_id, file_name from dba_data_files where
tablespace_name = 'ADD_TBS';
FILE_ID FILE_NAME
----------
----------------------------------------
14 /oradata/orcl/add_tbs_01.dbf
15 /tmp/add_tbs_second.dbf
I can now simply recover the tablespace.
RMAN>
recover tablespace add_tbs;
RMAN> alter
tablespace add_tbs online;
and query it now.
SQL> select
/*+ FULL */ count(*) from scott.add_table;
COUNT(*)
----------
703021
SQL> alter
tablespace add_tbs offline;
SQL> alter
tablespace add_tbs online;
SQL> select
/*+ FULL */ count(*) from hemant.add_table;
COUNT(*)
----------
703021
I can also restore datafile 15 by using the same "AS
COPY" and "SWITCH" trick.
RMAN>
backup as copy datafile 15 format
'/oradata/orcl/add_tbs_02.dbf' ;
RMAN>
alter tablespace add_tbs offline;
RMAN>
switch datafile 15 to copy;
RMAN>
recover tablespace add_tbs;
RMAN> alter
tablespace add_tbs online;
SQL> select
file_id, file_name from dba_data_files where
tablespace_name = 'ADD_TBS';
FILE_ID FILE_NAME
----------
----------------------------------------
14 /oradata/orcl/add_tbs_01.dbf
15 /oradata/orcl/add_tbs_02.dbf
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.
Incremental backups capture block-level changes to a
database made after a previous incremental backup.
Incremental backups are generally smaller and faster
to make than full database backups. Recovery with
incremental backups is faster than using redo logs
alone.
The starting point for an incremental backup
strategy is a level 0 incremental
backup, which backs up all blocks in
the database. An incremental backup at level 0 is
identical in content to a full backup, but unlike a
full backup the level 0 backup is considered a part
of the incremental backup strategy.
A level 1 incremental backup
contains only blocks changed after a previous
incremental backup. If no level 0 backup exists in
either the current or parent database incarnation
when you run a level 1 backup, then RMAN makes a
level 0 backup automatically.
A level 1 backup can be a cumulative
incremental backup, which includes all
blocks changed since the most recent level 0 backup,
or a differential
incremental backup, which includes only
blocks changed since the most recent incremental
backup. Incremental backups are differential by
default.
When restoring incremental backups, RMAN uses the
level 0 backup as the starting point, then updates
changed blocks based on level 1 backups where
possible to avoid reapplying changes from redo one
at a time. Recovering with incremental backups
requires no additional effort on your part. If
incremental backups are available, then RMAN uses
them during recovery.
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.
To improve the performance of
incrementally updated backups another feature called
Change Tracking has also been introduced since
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';
To make incremental backups of the
database:
1- Run the BACKUP INCREMENTAL command.
The following example creates a level 0 incremental
backup to serve as a base for an incremental backup
strategy:
rman> BACKUP INCREMENTAL
LEVEL 0 DATABASE;
Another Example:
rman> backup incremental
level 0 tag
= "Weekly_Full_Level_0_Database" format
'/data/oracle/BACKUP/rman_LVL0_%d.%t.%p.%c'
database;
The following example creates a level
1 cumulative incremental backup (It backs up all
blocks changed after the most recent incremental
backup at level 0):
rman> BACKUP
INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
Another Example:
backup incremental
level 1 cumulative
tag="Oracle_Backup_Level_1" filesperset 5 format '/data/oracle/BACKUP/%d_%t_%s_%p.dbf' database ;
The following example creates a level
1 differential incremental 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):
rman> BACKUP
INCREMENTAL LEVEL 1 DATABASE;
Another Example:
rman> backup
incremental level 1 tag="Oracle_Backup_Level_1"
format
'/data/oracle/BACKUP/rman_LVL1_%d.%t.%p.%c'
database;
Rmember:
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.
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.
Incrementally
Updated Backups
The RMAN incrementally updated backup feature is an
efficient incremental backup routine. Changes from
level 1 backups roll forward an image copy level 0
incremental backup, so that it includes all changes
as of the SCN at which the level 1 incremental
backup was created. Recovery of the updated level 0
incremental backup is faster because all changes
from the level 1 incremental backup have already
been applied.
The BACKUP FOR RECOVER OF COPY command specifies
that an incremental backup should contain all
changes since the SCN of a specified datafile copy
(level 0 incremental backup) of your database. The
following table explains which options to use with
FOR RECOVER OF COPY to implement an incrementally
updated backup strategy.
Using this feature all changes between
the SCN of the original image copy and the SCN of
the incremental backup are applied to the image
copy, winding it forward to make the equivalent of a
new database image copy without the overhead of such
a backup.
BACKUP Option |
Description |
Example |
FOR RECOVER OF
COPY WITH TAG 'tag_name' |
Use the TAG
parameter to identify the level 0 incremental
backup serving as the basis of the incremental
backup. If no level 0 datafile copy with the specified tag exists in either the current or parent database incarnation, then RMAN creates a level 0 datafile copy with the specified tag. |
BACKUP
INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' DATABASE; |
FOR RECOVER OF
COPY DATAFILECOPY FORMAT 'format' |
Identifies the
datafile copies to use as the basis for this
incremental backup. |
BACKUP
INCREMENTAL LEVEL 1 FOR RECOVER OF COPY DATAFILECOPY FORMAT '/disk2/df1.cpy' DATABASE; |
To implement an incrementally updated
backup strategy:
Start RMAN and connect to a target database.
Run the RECOVER COPY and BACKUP INCREMENTAL
commands.
The following script, run on a regular basis, is all
that is required to implement a strategy based on
incrementally updated backups.
RECOVER COPY OF DATABASE WITH TAG 'incr_update';
BACKUP INCREMENTAL
LEVEL 1 FOR RECOVER OF COPY WITH
TAG 'incr_update' DATABASE;
You can use the script bellow to take
an Incrementally Updated Backup. This is a script
which uses non-default RMAN location and 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
'/oracle/backups/rman_bkps/atbckp_cntrlfile_testdw0%F';
run {
ALLOCATE
CHANNEL RMAN_BACK_CH01 TYPE DISK FORMAT
'/oracle/backups/rman_bkps/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;
ALTER SYSTEM
ARCHIVE LOG CURRENT;
BACKUP as
compressed backupset ARCHIVELOG ALL format
'/oracle/backups/rman_bkps/archivelogs_%d_%u_%s_%T'
DELETE INPUT;
CROSSCHECK
BACKUP;
DELETE
NOPROMPT OBSOLETE;
DELETE
NOPROMPT EXPIRED BACKUP;
RELEASE
CHANNEL RMAN_BACK_CH01;
}
EXIT;
EOF
#########################################
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
resaon, you can use the NOT BACKED UP SINCE option on
the BACKUP command 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';
}
As in section 1, you can use the NOT BACKED UP SINCE
option with the BACKUP command 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
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, so 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. Use the 'check logical'
parameter when backing up the
database
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.
# Check if database can be restored
RMAN> restore
database validate;
or a Full Validation:
run
{
restore database validate;
restore archivelog from time 'sysdate-1'
validate;
restore controlfile validate;
restore spfile 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.
#This will keep 21 days of backup records.
SQL> alter
system set control_file_record_keep_time=21
scope=both;
8. 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
9. 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;
10. 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.
11. 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;
11. 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.
12. Set Archivelog Deletion Policy
Let RMAN to manage archivelogs deletion instead of
doing it manually. Simply configure an archivelog
deletion policy as follow:
RMAN> CONFIGURE ARCHIVELOG
DELETION POLICY TO APPLIED ON ALL STANDBY BACKED
UP 1 TIMES TO DEVICE TYPE DISK ;
In this case, when backing up archivelogs with DELETE
INPUT option, RMAN will delete archivelogs only after
they have been backed up in two diferent backupset and
applied on standby databases.
13. Setup parallelism for Tuning
RMAN> CONFIGURE DEVICE
TYPE DISK PARALLELISM 2 ;
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.
To Drop it from RMAN just perform:
delete
script script_name;
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.
report schema; | Report
datafiles structure of the DB in RMAN. The names of all datafiles (permanent and temporary) and tablespaces for the target database at the specified point in time. If you use RMAN in a Data Guard environment, then you can report the schema for a specified DB_UNIQUE_NAME. |
report
obsolete; report obsolete redundancy = 2; report obsolete redundancy = 2 until time 'SYSDATE-14'; delete obsolete |
Report backups
that are obsolete. Full backups,
datafile copies, and archived redo logs
recorded in the RMAN repository that can
be deleted because they are no longer
needed according to the current
retention policy. Report backups that are obsolete because there are at least 2 more recent backups. Report backups that are obsolete because there are at least 2 more recent backups that are no more than 2 weeks old Delete obsolete backups. |
report
need backup; report need backup days 2 database; report need backup days 2 tablespace system; report unrecoverable; |
Display a list
of files that need to be backed up based
on the retention policy. To report on those datafiles that, if restored, would require application of two days (or more) worth of archived redo log files: To generate the same report but only for SYSTEM tablespace datafiles. Reports on all unrecoverable datafiles. |
list
incarnation; list db_unique_name; |
You can
list all incarnations of a database. A new
database incarnation is created when you
open with the RESETLOGS option. A database in a Data Guard environment is distinguished by its DB_UNIQUE_NAME initialization parameter setting. You can list all databases that have the same DBID. |
list backup
summary; list backup; list backup of database; list backupset; list backup by file; list backup of archivelog all; list backup of controlfile; |
Lists a
summary of the Backups and Copies. List all backup sets, copies, and proxy copies of a database, tablespace, datafile, archived redo log, control file, or server parameter file. Lists backups of all files in database List backup by set. List backups by file. List backuped files containing Arch Log Files. List backuped files containing control files. |
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; |
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). |
For more information on the
meaning of each column in the LIST command,
please click
HERE
report schema;
using target database control file
instead of recovery catalog
Report of database schema for database with
db_unique_name DIE
List of Permanent Datafiles
===========================
File Size(MB)
Tablespace
RB segs Datafile Name
---- -------- -------------------- -------
------------------------
1
730
SYSTEM
***
C:\APP\ORADATA\DIE\SYSTEM01.DBF
2
610
SYSAUX
***
C:\APP\ORADATA\DIE\SYSAUX01.DBF
3
925
UNDOTBS1
***
C:\APP\ORADATA\DIE\UNDOTBS01.DBF
4
5
USERS
***
C:\APP\ORADATA\DIE\USERS01.DBF
5
100
EXAMPLE
***
C:\APP\ORADATA\DIE\EXAMPLE01.DBF
6
1800
FG_DATA
***
C:\APP\ORADATA\DIE\FG_DATA01.DBF
7
20
FG_INDX
***
C:\APP\ORADATA\DIE\FG_INDX01.DBF
List of Temporary Files
=======================
File Size(MB)
Tablespace
Maxsize(MB) Tempfile Name
---- -------- -------------------- -----------
--------------------
1
20
TEMP
32767
C:\APP\ORADATA\DIE\TEMP01.DBF
report
obsolete;
RMAN retention policy will be applied to the
command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type
Key Completion
Time Filename/Handle
-------------------- ------ ------------------
--------------------
Archive
Log
1
05-AUG-11
C:\APP\ORADATA\DIE\ARCH\ARC0000000248_0748807412.0001
Archive
Log
2
05-AUG-11
C:\APP\ORADATA\DIE\ARCH\ARC0000000249_0748807412.0001
Archive
Log
3
24-AUG-11
C:\APP\ORADATA\DIE\ARCH\ARC0000000270_0748807412.0001
Archive
Log
4
24-AUG-11
C:\APP\ORADATA\DIE\ARCH\ARCH_0001_0000000271_748807412.ARC
Backup
Set
5 24-AUG-11
Backup
Piece
5
24-AUG-11
C:\APP\RMAN_BACKUP\RMAN_BACKUP_CONTROLFILE_DIE_C-311636530-20110824-00_CTL
Backup
Set
7 24-AUG-11
Backup
Piece
7
24-AUG-11
C:\APP\RMAN_BACKUP\ARCH_LOGS_06MKQ5OG
Backup
Set
8 24-AUG-11
Backup
Piece
8
24-AUG-11
C:\APP\RMAN_BACKUP\ARCH_LOGS_07MKQ5OG
Backup
Set
10 24-AUG-11
Backup
Piece
10
24-AUG-11
C:\APP\RMAN_BACKUP\CONTROLFILE_0AMKQ5ON
report unrecoverable;
Report of files that need backup due to
unrecoverable operations
File Type of Backup Required Name
---- -----------------------
-----------------------------------
5 full or
incremental
C:\APP\ORADATA\DIE\EXAMPLE01.DBF
6 full or
incremental
C:\APP\ORADATA\DIE\FG_DATA01.DBF
list incarnation;
List of
Database Incarnations
DB
Key Inc Key DB Name DB
ID
STATUS Reset SCN Reset Time
-------
------- -------- ---------------- ---
---------- ----------
1
1
DIE
311636530
PARENT
1
02-APR-10
2
2
DIE
311636530
CURRENT 940976
18-APR-11
restore database validate;
or a Full Validation:
run
{
restore database validate;
restore archivelog from time
'sysdate-1' validate;
restore controlfile validate;
restore spfile validate;
}
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.
Let's see a couple of examples:
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
------- -- -- - ----------- ---------------
------- ------- ---------- ---
1
B F A
DISK
24-AUG-11
1
1
NO
TAG20110824T143207
3
B F A
DISK
24-AUG-11
1
1
NO
TAG20110824T143207
4
B F A
DISK
24-AUG-11
1
1
NO
TAG20110824T143207
5
B F A
DISK
24-AUG-11
1
1
NO
TAG20110824T143450
6
B A A
DISK
24-AUG-11
1
1
NO
TAG20110824T143456
7
B A A
DISK
24-AUG-11
1
1
NO
TAG20110824T143456
8
B A A
DISK
24-AUG-11
1
1
NO
TAG20110824T143456
9
B A A
DISK
24-AUG-11
1
1
NO
TAG20110824T143456
10 B
F A
DISK
24-AUG-11
1
1
NO
CONTROL_FILE
11 B
F A
DISK
24-AUG-11
1
1
NO
TAG20110824T143506
12 B
F A
DISK
24-AUG-11
1
1
NO
TAG20110824T144246
13 B
F A
DISK
24-AUG-11
1
1
NO
FULL_DATABASE
14 B
F A
DISK
24-AUG-11
1
1
NO
TAG20110824T144246
15 B
F A
DISK
24-AUG-11
1
1
NO
TAG20110824T144556
rman> list backup;
# lists backup sets, images
copies, backup pieces, and proxy copies
List of
Backup Sets
===================
BS
Key Type LV
Size
Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------
---------------
1
Full
441M
DISK
00:01:23
15-OCT-04
BP Key: 1 Status:
AVAILABLE Compressed: NO Tag:
TAG20041015T175207
Piece Name:
/export/rman/rman_CUDDLE_01g2k8m8_1_1.bus
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp
Time Name
---- -- ---- ---------- --------- ----
1 Full
396472 15-OCT-04
/u02/oradata/cuddle/system01.dbf
2 Full
396472 15-OCT-04
/u02/oradata/cuddle/undotbs01.dbf
3 Full
396472 15-OCT-04
/u02/oradata/cuddle/sysaux01.dbf
4 Full
396472 15-OCT-04
/u02/oradata/cuddle/users01.dbf
BS
Key Type LV
Size
Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------
---------------
2
Full
2M
DISK
00:00:02
15-OCT-04
BP Key: 2 Status:
AVAILABLE Compressed: NO Tag:
TAG20041015T175207
Piece Name:
/export/rman/rman_CUDDLE_02g2k8ou_1_1.bus
Controlfile Included: Ckp SCN:
396502
Ckp time: 15-OCT-04
SPFILE Included: Modification time:
15-OCT-04
Here we can see 2 backup pieces:
The first contains the datafiles for the
"cuddle" database, is 441MB in side, was made
to disk and took 1 minute and 23 seconds to
make. We aslo see the peices tag (notice the
tag is the same for both peices). Notice also
that each datafile in the backup peice has a
Checkpoint System Change Number (Ckp SCN)
associated with it (SCNs were covered
earlier).
The second peice is 2MB in size, took 2
seconds to backup and was done to disk. Notice
that the second peice lists the modification
time for the SPFILE and the SCN for the
Controlfile.
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 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;
#
List backuped files containing Arch Log
Files.
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:
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 Oracle 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.
Restore and Recovery process is dependent on the
type of failure.
Generally RMAN automates much of the process.
There are still some times that you might have
to perform some manual intervention.
Generally the commands you will use are:
Recovery
Methods
In general there are three steps
involved in restoring files:
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 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
For the impatient, here is a quick
snippet of one particular rman restore scenario.
Change this accordingly for your scenario. Read
below to understand more details about these
commands.
RMAN> SET DBID 12345;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM
"/backup/rman/ctl_c-12345-20141003-03";
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;
Before the restore, verify the current RMAN configuration on the server where you’ll be performing the restore.
To connect to RMAN, execute the following rman command, which will take you to the RMAN> prompt. From here, you can execute all RMAN commands.
$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Mon Nov 17 11:17:11 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: DEVDB (DBID=821773) RMAN>
Execute “show all”, which will display all current RMAN configuration. As you see below, the current RMAN backup is located under “/backup/rman” directory.
RMNAN> SHOW ALL; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/ctl_%F'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/data/rman-backup/full_%u_%s_%p' MAXPIECESIZE 4096 M; CONFIGURE MAXSETSIZE TO UNLIMITED; CONFIGURE ENCRYPTION FOR DATABASE OFF; CONFIGURE ENCRYPTION ALGORITHM 'AES128'; CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backup/rman/snapcf_med.f';
On a high-level, the following three steps are performed in recovering the database from RMAN backup.
WARNING: Execute rman restore commands only on a test instance. If you try these restore commands in a production instance, and if something goes wrong, you’ll lose your production data.
First, you may want to restore the control file from the backup before you start the restore.
This step is required only if you are restoring the backup on a new server where the control file doesn’t exist. Or, if the control file on the system you are restoring is corrupted or missing.
RMAN> SET DBID 12345; RMAN> STARTUP NOMOUNT; RMAN> RESTORE CONTROLFILE FROM "/backup/rman/ctl_c-12345-20141003-03"; RMAN> ALTER DATABASE MOUNT;
Before you start the RMAN restore process, do the following:
The following is an example output of the restore controlfile command:
Starting restore at 22-NOV-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=124 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 output filename=/u01/oradata/devdb/control01.ctl output filename=/u02/oradata/devdb/control02.ctl output filename=/u03/oradata/devdb/control03.ctl Finished restore at 22-NOV-14
When the RMAN backup was taken, if a tag was specified, you can also restore controlfile based a tag name as shown below.
RMNAN> RESTORE CONTROLFILE FROM TAG 'WEEKLY_FULL_BKUP';
You can also use the autobackup option to restore the controlfile as shown below:
RMNAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
To restore from the RMAN full backup that is located under the /backup/rman directory, execute the following command.
RMAN> RESTORE DATABASE;
Apart from the above straight forward restore database, there are also few variations of this command which are explained in the examples below. Use the one that is appropriate for your situation.
The following is a sample output of the above restore database command:
RMAN> Starting restore at 22-NOV-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=125 devtype=DISK ... channel ORA_DISK_2: starting datafile backupset restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set restoring datafile 00020 to /u01/oradata/devdb/dev01_1.dbf restoring datafile 00021 to /u02/oradata/devdb/report_data.dbf restoring datafile 00022 to /u01/oradata/devdb/analytics01.dbf channel ORA_DISK_2: reading from backup piece /backup/rman/full_32qakgmpa_123456_1 channel ORA_DISK_4: starting datafile backupset restore ....
If you’ve restored the controlfile from the backup, you need to perform this step.
In the last step, recover the database, and then you should open the database with resetlogs options as show below:
RMAN> RECOVER DATABASE; RMAN> ALTER DATABASE OPEN RESETLOGS;
CASE
2 - Recovery from
Missing or Corrupted Datafile(s) or
Tablespace or a non-backed up Datafile:
Case A
= One of the Datafiles or Tablespace is Lost,
DB is UP, Valid Backups exist.
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.
In this scenario, the database is
already in the MOUNT or OPEN 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.
Example, you get the following
error message:
ORA-01157:
cannot identify/lock data file 4 - see DBWR
trace file
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4:
'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
The error message tells us that file# 4 is
missing. The DB is in mount or opened 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
following query may help you to identify the
file that needs recovery:
select
rf.file#, d.name, rf.error from
v$recover_file rf, v$datafile d where
rf.file#=d.file#;
Check if we have a Backup of that datafile or
tablespace:
RMAN> list backup of
datafile 4;
or
RMAN> list backup of
tablespace USERS;
Take the datafile(s)
Offline
RMAN> alter
tablespace USERS offline immediate;
or
RMAN> alter
database datafile
'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
offine;
Restore and recover the missing datafile(s) or Tablespaces - RMAN applies
all logs automatically
RMAN>
restore tablespace
USERS;
RMAN> recover tablespace USERS;
or
RMAN> restore datafile 4;
RMAN>
recover
datafile 4;
or
RMAN> restore
datafile 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF';
RMAN>
recover
datafile'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
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';
--Open database
for general use (if not already opened)
RMAN>
alter
database open;
IMPORTANT NOTE on READ ONLY
TABLESPACES
If the tablespace to recover is READ ONLY, use
the CHECK READONLY option during database
restore
By default "restore database" and "recover
database" will ignore tablespaces in read-only
mode.
So you will use:
RMAN>
restore database
check readonly;
RMAN>
recover
database check readonly;
IMPORTANT NOTE on TEMP
TABLESPACE
As tempfiles aren't checkpointed, we don't need
to back them up. We can recreate them at any
point after the database has been restored,
recovered and opened.
Temporary datafiles that belong to locally
managed temporary tablespaces are automatically
re-created during database recovery. This
eliminates the need to manually create temporary
tablespaces after recovery.
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..
To simulate a block corruption
scenario, we will do the following:
• Create a table in
tablespace users
• Identify the blocks
belonging to that table
• Corrupt all or some of
those blocks using the Unix dd command.
• Flush the buffer cache to
ensure we read blocks from disk and not from
memory(buffer cache)
• Verify block corruptions
from V$DATABASE_BLOCK_CORRUPTION
create table mytab
tablespace users as select * from tab;
select count(*) from mytab;
Now on UNIX:
$ dd of=/u03/oradata/leventwo/users01.dbf
bs=8192 seek=1027 conv=notrunc count=1
if=/dev/zero
1+0 records in
1+0 records out
alter system flush
buffer_cache;
select count(*) from mytab;
ERROR at line 1:
ORA-01578: ORACLE data block
corrupted (file # 4, block # 1027)
ORA-01110: data file 4:
'/u03/oradata/leventwo/users01.dbf'
select * from v$database_block_corruption;
FILE#
BLOCK# BLOCKS
CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ----------
------------------ ---------
4
1027
1
0 ALL ZERO
Get the corruption details:
SELECT tablespace_name, segment_type,
owner, segment_name
FROM dba_extents
WHERE file_id = 4
and 1027 between block_id AND
block_id + blocks - 1;
We can either now recover the corrupted blocks
using the command
BLOCKRECOVER DATAFILE 4,
BLOCK 1027
Or, if there are a number of data blocks which
are corrupted, we can issue a single command
BLOCKRECOVER CORRUPTION
LIST
# 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:
BLOCKRECOVER
CORRUPTION LIST;
. Another Example:
--restore AND
recover specific block (on this example, do
it for 2 blocks at the same time!)
RMAN> blockrecover
datafile 7 block 233,235 datafile 4 block
101;
We can also recover to a different
location:
RMAN>
run
{
allocate
channel d1 type disk format
'/u01/backupdb/orcl_%T_%s_%p.bkp';
set newname
for datafile 5 to
'/u03/oradb/oracle/example.dbf';
restore
database ;
switch
datafile all;
recover
database ;
}
Select *
from v$datafile;
A couple of important points
regarding block recovery:
CASE
4 - Recovery
from Missing or Corrupted Redo Log Group:
The STATUS column of V$LOG view
reflects the status of the log group:
CURRENT: The log group that is currently being
written to by the log writer.
ACTIVE : The log group is
required for crash recovery and may or may not
have been archived.
INACTIVE :The log group isn’t needed for crash
recovery and may or may not have been archived.
UNUSED : The log group has never
been written to as it was recently created.
The STATUS column of V$LOGFILE
reports the status of a online redo log file
member :
INVALID:The log file member is inaccessible, or
it has been recently created.
NULL: The log file member is being
used by the database.
Option 1: A member of multiplexed
redo log group is lost and DB is UP
Solution(s):
Fix the media or Drop the affected Redo Log File
and Create a new one in a different location
DIE/SYSTEM>
select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 INACTIVE
DIE/SYSTEM> select group#,
status , substr(member,1,60) member from
v$logfile;
GROUP# STATUS MEMBER
----------
-------
------------------------------------------------------------
1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO01.LOG
2
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO02.LOG
3
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO03.LOG
4
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO04a.LOG
4
E:\ORADATA\DIE\REDO04b.LOG
If we lost , E:\ORADATA\DIE\REDO04b.LOG then Oracle keeps working but alert.log will report the errors (you can force the errors by performing a few log switches):
Errors in file
c:\DIE\dump\DIE_lgwr_3724.trc:
ORA-00321: log 4 of thread 1, cannot update log
file header
ORA-00312: online log 4 thread 1:
'E:\ORADATA\DIE\REDO04a.LOG'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04008: WriteFile() failure, unable to write
to file
O/S-Error: (OS 1006) The volume for a file has
been externally altered so that the opened file
is no longer valid.
Sat Aug 14 20:43:50 2010
Errors in file c:\DIE\dump\db10g_lgwr_3724.trc:
ORA-00313: open failed for members of log group
4 of thread 1
Oracle marks the unavailable Redo
Log File as INVALID and the DB remains UP:
alter
system switch logfile;
alter
system switch logfile;
select
group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
select group#, status ,
substr(member,1,60) member from v$logfile;
GROUP# STATUS MEMBER
----------
-------
------------------------------------------------------------
1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO01.LOG
2
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO02.LOG
3
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO03.LOG
4
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO04a.LOG
4
INVALID E:\ORADATA\DIE\REDO04b.LOG
Force a Log Switch:
Log Group Status = ACTIVE
Member Status = INVALID
alter system switch logfile;
select
group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
4 ACTIVE
select group#, status ,
substr(member,1,60) member from v$logfile;
GROUP# STATUS MEMBER
----------
-------
------------------------------------------------------------
1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO01.LOG
2
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO02.LOG
3
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO03.LOG
4
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO04a.LOG
4
INVALID E:\ORADATA\DIE\REDO04b.LOG
SOLUTION
Drop and recreate the affected member to a
different location
alter
database drop logfile member
'E:\ORADATA\DIE\REDO04b.LOG';
alter
database add logfile member
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO04b.LOG'
to group 4;
Option
2: Loosing INACTIVE Redo Log Files
Problem:
The only member of the redo log group is lost
Status of the Redo Log Group is INACTIVE
Database is Up
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:
Option 4: 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 1 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>
startup mount;
We will try to identify the last
good SCN
select group#, sequence#, status, archived,
first_change# from v$log order by sequence#;
GROUP# SEQUENCE#
STATUS
ARC FIRST_CHANGE#
----------
---------- ---------------- --- -------------
2
348
INACTIVE
YES 11306138
3
349
INACTIVE
YES 11306234
1
350
CURRENT
NO
11306491
The FIRST_CHANGE# is the first SCN stamped in
the missing log. This implies that the last SCN
stamped in the previous log is
11306491(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
11306491;
--Recover
database
RMAN> recover database until scn
11306491;
--open
database with RESETLOGS (see comments below)
RMAN> alter database open resetlogs;
-- Re-create the redo log group to
a different location
alter database rename file
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO03.LOG'
to
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO03.LOG'
The following points should be noted:
Check their Status by:
select * from v$log;
select * from v$logfile;
## Command Example
#
recover table
"P42"
.
"TEST"
until time
"to_date('15/02/2019
18:18:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION
'+RECO'
DATAPUMP DESTINATION
'/u01/oradata/DUMP'
DUMP FILE
'P42_TEST.dmp'
NOTABLEIMPORT;
The same we are recovering a Table from a non
container Database, we can recover a table from
a PDB.
Lets create the table, do a Backup and drop it
as we did on previous cases, but this time
inside PDB1 of our 12.2 Database called "db122"
ALTER SESSION
SET
CONTAINER = pdb1;
create
table TEST
(
ID
VARCHAR2(4
BYTE)
NOT NULL primary key,
First_Name
VARCHAR2(10 BYTE),
Last_Name
VARCHAR2(10 BYTE),
Start_Date
DATE,
End_Date
DATE,
Salary
Number(8,2),
City
VARCHAR2(10 BYTE),
Description
VARCHAR2(15 BYTE))
/
BEGIN
FOR v_LoopCounter IN
1..50 LOOP
INSERT INTO TEST (id)
VALUES
(v_LoopCounter);
END LOOP;
END;
/
RMAN> backup
database plus archivelog;
alter session
set
nls_date_format =
'dd/mm/yyyy
hh24:mi:ss'
;
drop table test;
## Command Example ##
recover table
"P42"
.
"TEST"
OF PLUGGABLE
DATABASE pdb1
until time
"to_date('16/02/2019
12:14:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY
DESTINATION
'+RECO'
Lets try now something a bit different. Maybe
you didnt drop the table but delete part of it,
and you want to conserve the "current" one as
well as the previous version.
To do that, we can use REMAP option as below so
you will have a new table called "TEST1" as
result
Also, instead of using Filesystem as AUXILIARY
DESTINATION, lets use +ASM Diskgroup for a
faster recovery process
## Command Example
##
recover table
"P42"
.
"TEST"
until time
"to_date('15/02/2019
18:18:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION
'+RECO'
remap table
"P42"
.
"TEST"
:
"TEST1"
ORA-19809
If you are trying to do a backup of your
database and you get somethinkg like:
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK
FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03009:
failure of backup command on t1 channel
at 10/19/2011 10:54:21
ORA-19809:
limit exceeded for recovery files
ORA-19804:
cannot reclaim 52428800 bytes disk space
from 6442450944 limit
The first thing to do is to check the
amount of used and free space in your
recovery area:
set
lines 100
col
name format a60
select
name, floor(space_limit / 1024 / 1024)
"Size MB", ceil(space_used / 1024
/ 1024) "Used MB", floor(space_limit /
1024 / 1024) - ceil(space_used /
1024 / 1024) "Free Space MB"
from
v$recovery_file_dest
order
by name;
NAME
Size MB Used MB Free
Space MB
------------------------------------------------------------
---------- ---------- -------------
C:\app\flash_recovery_area
6144
5769
375
As you can see on this example, you do not
have much space free.
To fix the problem, you need to either
make the flash recovery area larger, or
remove some files from it.
If you have the disk space available, make
the recovery area larger. Example:
alter
system set
db_recovery_file_dest_size=15G
scope=both;
To remove files you must use RMAN.
Manually moving or deleting files will
have no effect as oracle will be unaware.
The obvious choice is to backup and remove
some archive log files. However, if you
usually write your RMAN backups to disk,
this could prove tricky. RMAN will attempt
to write the backup to the flash recovery
area...which is full. You could try
sending the backup elsewhere using a
command such as this:
rman
target /
run
{
allocate
channel t1 type disk;
backup
archivelog all delete input format
'/<temp backup
location>/arch_%d_%u_%s';
release
channel t1;
}
This will backup all archive log files to
a location of your choice and then remove
them.
Another
"dirty" choice
The removal is desribed in the RMAN
documentation but this is a quick and
dirty way if you don’t have an rman
repository - but could endanger your
ability to recover - so be careful.
a) delete unwanted archive log files from
disk ( rm /del )
b) connect to rman
c) crosscheck archivelog
all; --> marks
the controlfile that the archives have
been deleted
d) delete expired archivelog all; -->
deletes the log entries identified above.
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 archivelog all;
to remove the expired ones.
11g
New Feature: RMAN Data Recovery
Advisor (DRA)
One of the great new features of RMAN in
Oracle 11g is the new tool named Data
Recovery Advisor (DRA).
This tool automates the diagnose of the
data failure and corruption, advises the
recovery steps and performs automatic
recovery of the database failure.
This tool could be used from GUI as well
as from CLI.
To list of database failures, you can
issue:
RMAN> list failure;
List
of Database Failures
=========================
Failure
ID Priority Status
Time Detected Summary
----------
-------- --------- ------------- -------
522
HIGH
OPEN
08-SEP-09 One or
more non-system datafiles are
missing
DRA will consolidate related failures into
a single failure. You can list a failure
individually by issuing "list failure X
detail" command.
RMAN> list failure 522 detail;
List
of Database Failures
=========================
Failure
ID Priority Status
Time Detected Summary
----------
-------- --------- ------------- -------
522
HIGH
OPEN
08-SEP-09 One or
more non-system datafiles are
missing
Impact: See impact for individual child
failures
List of child failures for parent
failure ID 522
Failure ID Priority
Status Time Detected
Summary
---------- -------- ---------
------------- -------
525
HIGH
OPEN
08-SEP-09
Datafile 4: '/abc' is missing
Impact: Some objects in tablespace USERS
might be unavailable
By default, the list failure command shows
all failures with the critical and high
priorities. You can list failures for a
specific priority:
RMAN> list
failure closed;
RMAN> list
failure low;
RMAN> list
failure high;
RMAN> list
failure critical;
When you fix a failure, the failure will
be closed automatically.
However, sometimes a failure may be
irrelevant for you. In this case, you can
use RMAN’s change failure command to close
it.
You can also use this command to change
the priority (you can’t change a priority
level of critical):
RMAN>
change failure 522 closed;
RMAN>
change failure 522 priority low;
DRA can also recommend how
to fix the failures:
RMAN> advise failure;
List
of Database Failures
=========================
Failure
ID Priority Status
Time Detected Summary
----------
-------- --------- ------------- -------
522
HIGH
OPEN
08-SEP-09 One or
more non-system datafiles are
missing
analyzing
automatic repair options; this may take
some time
using
channel ORA_DISK_1
analyzing
automatic repair options complete
Mandatory
Manual Actions
========================
no
manual actions available
Optional
Manual Actions
=======================
1.
If file /u01/oracle/oradata/users01.dbf
was unintentionally renamed or
moved, restore it
Automated
Repair Options
========================
Option
Repair Description
------
------------------
1
Restore and recover datafile 4
Strategy: The repair includes complete
media recovery with no data loss
Repair script:
/u01/oracle/diag/rdbms/reco_990089206.hm
The REPAIR FAILURE command applies the
repair scripts produced by the ADVISE
FAILURE command. Using the PREVIEW option
lists the contents of the repair script
without applying it.
RMAN> REPAIR FAILURE PREVIEW;
Strategy:
The repair includes complete media
recovery with no data loss
Repair
script: /u01/oracle/diag/rdbms/reco_990089206.hm
contents
of repair script:
# restore and recover datafile
alter database datafile 4 offline;
restore datafile 4;
recover datafile 4;
alter database datafile 4 online;
Once DRA provides its repair
recommendations, you can choose to run the
repair failure command to fix and close a
specific failure.
By default, the REPAIR FAILURE command
prompts the user to confirm the repair,
but this can be prevented using the
NOPROMPT keyword.
If the advise failure command recommends
any manual repairs, you need to perform
these repairs first:
RMAN> repair failure;
-- Optional use repair failure
noprompt
Strategy:
The repair includes complete media
recovery with no data loss
Repair
script:
/u01/oracle/diag/rdbms/reco_990089206.hm
contents
of repair script:
# restore and recover datafile
alter database datafile 4 offline;
restore datafile 4;
recover datafile 4;
alter database datafile 4 online;
Do
you really want to execute the above
repair (enter YES or NO)? YES
executing
repair script
sql
statement: alter database datafile 4
offline
Starting
restore at 08-SEP-09
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting datafile backup set
restore
channel
ORA_DISK_1: specifying datafile(s) to
restore from backup set
channel
ORA_DISK_1: restoring datafile 4 to
/u01/oracle/oradata/users01.dbf
channel
ORA_DISK_1: reading from backup piece
/u01/oracle/fra/backup.bkp
channel
ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed
time: 00:00:03
Finished
restore at 08-SEP-09
Starting
recover at 08-SEP-09
using
channel ORA_DISK_1
starting
media recovery
media
recovery complete, elapsed time:
00:00:01
Finished
recover at 08-SEP-09
sql
statement: alter database datafile 4
online
repair
failure complete
The full syntax for the REPAIR FAILURE
command can be seen here.
The VALIDATE command initiates data
integrity checks, logging physical, and
optionally logical, block corruptions of
database files and backups in the
V$DATABASE_BLOCK_CORRUPTION view and the
Automatic Diagnostic Repository as one or
more failures.
The following code shows some of the
possible syntax variations.
# Check for physical corruption of all
database files.
VALIDATE
DATABASE;
#
Check for physical and logical
corruption on a tablespace
VALIDATE
TABLESPACE USERS;
#
Check for physical and logical
corruption of a tablespace.
VALIDATE
CHECK LOGICAL TABLESPACE USERS;
# Check for physical and logical
corruption on a datafile and block
VALIDATE
DATAFILE 4 block 1;
# Check
for physical and logical corruption of a
datafile.
VALIDATE CHECK
LOGICAL DATAFILE 4;
#
Check for physical corruption of all
archived redo logs files.
VALIDATE
ARCHIVELOG ALL;
#
Check for physical and logical
corruption of the controlfile.
VALIDATE CHECK
LOGICAL CURRENT CONTROLFILE;
#
Check for physical and logical
corruption of a specific backupset.
VALIDATE CHECK
LOGICAL BACKUPSET 3;
The BACKUP VALIDATE and RESTORE VALIDATE
commands perform the same checks as the
VALIDATE command for the files targeted by
the backup or restore command, but they
don't actually perform the specified
backup or restore operation. This allows
you to check the integrity of a backup or
restore operation before actually
performing it.
The following code shows some of the
possible syntax variations.
#
Check for physical corruption of files
to be backed up.
BACKUP VALIDATE
DATABASE ARCHIVELOG ALL;
#
Check for physical and logical
corruption of files to be backed up.
BACKUP VALIDATE
CHECK LOGICAL DATABASE ARCHIVELOG ALL;
#
Check for physical corruption of files
to be restored.
RESTORE VALIDATE
DATABASE;
#
Check for physical and logical
corruption of files to be restored.
RESTORE VALIDATE
CHECK LOGICAL DATABASE;
SET NEWNAME Clause Enhancements
Now it simplifies setting
new names for files. It handles all files
with a single SET NEWNAME. Use it with
Duplicate, Restore and Switch commands.
The order of Precedence is:
- FOR DATAFILE
- FOR TEMPFILE
- FOR TABLESPACE
SET NEWNAME FOR TABLESPACE is used for
setting the datafiles for a particular
tablespace using the specified
format. When using the SET NEWNAME
FOR TABLESPACE the substitution variables
in the TO <filename> clause are
required to avoid duplicate filenames,
therefore at least one of the following
substitution variables must be utilized:
%b, %f and %U. %I and %N are
additional optional substitution variables
that can be utilized. Note the precedence
order is used on this example:
RUN
{ SET
NEWNAME FOR DATABASE TO ‘%U’ ;
SET NEWNAME FOR TEMPFILE TO
‘/opt/oracle/oradata/%U’ ;
SET NEWNAME FOR TABLESPACE example TO
‘/opt/oracle/oradata/%b’ ;
DUPLICATE TARGET DATABASE TO newdb ;
}
- FOR DATABASE
SET NEWNAME FOR DATABASE clause is used to
set a new name format for all datafiles
for the database for DUPLICATE, RESTORE
and SWITCH commands.
When using the SET NEWNAME FOR DATABASE
the substitution variables in the TO
<filename> clause are required to
avoid duplicate filenames, therefore at
least one of the following substitution
variables must be utilized: %b, %f and
%U. %I and %N are additional
optional substitution variables that can
be utilized.
Note that SET NEWNAME FOR DATABASE does
not set new filenames for tempfiles
therefore you will need to set a
specification for tempfiles when using SET
NEWNAME FOR DATABASE. Example:
RUN
{ SET
NEWNAME FOR DATABASE TO
‘/opt/oracle/oradata/%U’ ;
SET NEWNAME FOR TEMPFILE TO
‘/opt/oracle/oradata/%U’ ;
DUPLICATE TARGET DATABASE TO newdb ;
}
Substitution Variables
The setting of a file format can use a
defined set of substitution variables in
the TO <filename> format
specification.
This allows file names in the TO to be
generated and simplifies that naming so
that all can be handles in a single set
operation.
%b
filename without the directory path
%f absolute file number
of the datafile
%I Database ID (DBID)
%N Tablespace Name
%U System Generated file
name in the format
(data-D-%d_id-%I_TS_FNO-%f)
11g New
Feature: Compression
Prior to 11g Oracle RMAN had a single
compression algorithm, called BZIP2. The
algorithm has a very satisfactory
compression ratio in terms of decreasing
the size of RMAN output. However, high CPU
cost makes algorithm not suitable for many
sites especially for sites having CPU
bottleneck.
As a result people still use hardware
compression capabilities of tape drivers
(ratios like 1:3) to decrease the backup
time and increase the effective write
speed of backup drivers. By 11g Oracle
introduces a new compression algorithm
that is announced to be less compressive
but less aggressive in terms of CPU.
To configure RMAN to use compression at
all you can use:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO
COMPRESSED BACKUPSET;
or
CONFIGURE DEVICE TYPE TAPE BACKUP TYPE TO
COMPRESSED BACKUPSET;
To configure the different backup
algorithms:
CONFIGURE COMPRESSION ALGORITHM 'BASIC';
CONFIGURE COMPRESSION ALGORITHM 'NONE';
CONFIGURE COMPRESSION ALGORITHM 'LOW';
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
CONFIGURE COMPRESSION ALGORITHM 'HIGH';
Compression |
Original size |
Uncompressed |
BZIP2 |
ZLIB |
Size |
15GB |
13 Gb |
2.1 Gb |
2.4 Gb |
Duration |
|
4 min. |
7 min. |
5 min. |
12c New Feature:
Recover a Table or a Partition
With
12c R1, you can recover a
particular table or partition to a
point-in-time or SCN from RMAN
backups in the event of a table
drop or truncate, you don't need
to restore to a full auxiliary DB.
When a table or partition recovery
is initiated via RMAN, the
following actiona are performed:
12c
New Feature: Command
Line Improvements
Up
until version 12c, the RMAN command
line parser was capable of parsing
certain DML and DDL statements.
To do this, we use the “sql” command
from the RMAN command line, like this.
RMAN> sql 'create table
altdotoracle (col1 number)';
RMAN> sql 'insert into
altdotoracle values (1)';
However, one operation we could not do
was SELECT. The RMAN parser
lacked the facility to return arrays
of data to the screen. This
shouldn’t be surprising, considering
that RMAN is intended to be used to
run backup and restore operations.
RMAN> sql 'select * from
altdotoracle';
The statement does not error, but no
data is returned. Now, in
version 12c, we get two new benefits
in this area. First, we no
longer need to use the ‘sql’
command. For example…
RMAN> create table altdotoracle
(col1 number);
RMAN> insert into
altdotoracle values (1);
Additionally, the RMAN parser in 12c
allows us to run SELECT commands, as
well as DESCRIBE commands.
RMAN> select * from
altdotoracle;
RMAN> describe
altdotoracle;
12c New Feature: Duplication Enhancement
Scripts to get
information from RMAN
Using a 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;
Without
a 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
substr(dbfiles||' from '||numfiles, 1,
30) "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
substr(backedup||' from '||archived, 1,
30) "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