CHANGE ... UNCATALOG
CATALOG
DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS
BACKUP
INCREMENTAL LEVEL 0
MAXSETSIZE 10M
DATABASE
TAG 'BACKUP_1';
CONFIGURE BACKUP OPTIMIZATION ON
CONFIGURE ARCHIVELOG DELETION POLICY
TO BACKED UP 1 TIMES TO SBT;
DELETE
ARCHIVELOG ALL
BACKED UP 2 TIMES TO DEVICE TYPE sbt;
CHANGE BACKUP OF ARCHIVELOG TAG SECOND_COPY UNAVAILABLE;
BACKUP DATABASE
SKIP INACCESSIBLE
SKIP READONLY
SKIP OFFLINE;
Performing Block Media Recovery
###############################
To recover specific data blocks
===============================
RECOVER
DATAFILE 8 BLOCK 13
DATAFILE 2 BLOCK 199
FROM TAG mondayam;
To recover all blocks logged in V$DATABASE_BLOCK_CORRUPTION
===========================================================
RECOVER CORRUPTION LIST;
make a backup with tag
======================
backup as backupset
database device type disk
format '/disk1/backup_%U'
tag mondaybkp;
Making Compressed Backups
=========================
The following example backs up the database, switches the online redo logs, and includes archived logs in the backup:
By archiving the logs immediately after the backup, you ensure that you have a full set of archived logs through the time of the backup. In this way, you guarantee that you can perform media recovery after restoring this backup.
backup as compressed backupset
database plus archivelog;
Backing Up Tablespaces and Datafiles with RMAN
==============================================
RMAN automatically backs up the control file and the server parameter file (if the instance was started with a server parameter file) when datafile 1 is included in the backup. If control file autobackup is enabled, then RMAN writes the current control file and server parameter file to a separate autobackup piece. Otherwise, RMAN includes these files in the backup set that contains datafile 1.
backup device type sbt
tablesapce users, tools;
backup device type sbt
datafile 1,2,3
To manually back up the control file
====================================
backup device type sbt
tablespace users
include current controlfile;
BACKUP AS COPY
CURRENT CONTROLFILE
FORMAT '/tmp/control01.ctl';
To back up the server parameter file
====================================
backup device type sbt spfile;
Archived Redo Log Failover
==========================
The archived redo log failover feature enables RMAN to complete a backup even when some archiving destinations are missing logs or contain logs with corrupt blocks.
Online Redo Log Switching
=========================
Another important RMAN feature is automatic online redo log switching. To make an open database backup of archived redo logs that includes the most recent online redo log, you can execute the BACKUP command with any of the following clauses:
PLUS ARCHIVELOG
ARCHIVELOG ALL
ARCHIVELOG FROM ...
Before beginning the backup, RMAN switches out of the current redo log group, and archives all online redo logs that have not yet been archived, up to and including the redo log group that was current when the command was issued. This feature ensures that the backup contains all redo generated before the start of the command.
One of the most effective ways of backing up archived redo logs is the BACKUP ... PLUS ARCHIVELOG clause, which causes RMAN to do the following:
1.Runs the ALTER SYSTEM ARCHIVE LOG CURRENT statement.
2.Runs BACKUP ARCHIVELOG ALL. If backup optimization is enabled, then RMAN skips logs that it has already backed up to the specified device.
3.Backs up the rest of the files specified in BACKUP command.
4.Runs the ALTER SYSTEM ARCHIVE LOG CURRENT statement.
Backs up any remaining archived logs generated during the backup. If backup optimization is not enabled, then RMAN backs up the logs generated in step 1 plus all the logs generated during the backup.
The preceding steps guarantee that datafile backups taken during the command are recoverable to a consistent state. Also, unless the online redo log is archived at the end of the backup, DUPLICATE is not possible with the backup.
To back up archived redo log files
==================================
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP ARCHIVELOG ALL;
BACKUP ARCHIVELOG
FROM TIME 'SYSDATE-30'
UNTIL TIME 'SYSDATE-7';
To back up archived redo logs that need backups
===============================================
BACKUP ARCHIVELOG ALL NOT BACKED UP 2 TIMES;
To delete archived redo logs after a backup
===========================================
For the following procedure, assume that you archive to /arc_dest1, /arc_dest2, and the fast recovery area.
BACKUP DEVICE TYPE sbt
ARCHIVELOG ALL
DELETE ALL INPUT;
In this case, RMAN backs up only one copy of each log sequence number in these archiving locations. RMAN does not delete any logs from the fast recovery area, but it deletes all copies of any log that it backed up from the other archiving destinations. The logs in the fast recovery area that are eligible for deletion are automatically removed by the database when space is needed.
If you had specified DELETE INPUT rather than DELETE ALL INPUT, then RMAN would only delete the specific archived redo log files that it backed up. For example, RMAN would delete the logs in /arc_dest1 if these files were used as the source of the backup, but leave the contents of the /arc_dest2 intact.
The following SQL query determines the number of blocks written to an incremental level 1 backup of each datafile with at least 20% of its blocks backed up
====================================================
SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME,
BLOCKS, DATAFILE_BLOCKS
FROM V$BACKUP_DATAFILE
WHERE INCREMENTAL_LEVEL > 0
AND BLOCKS / DATAFILE_BLOCKS > .2
ORDER BY COMPLETION_TIME;
To make an incremental backup
=============================
backup incremental level 0 database;
backup incremental level 1 tablesapce system, tools;
backup incremental level 1 cumulative tablesapce users;
To enable block change tracking
===============================
SHOW PARAMETER DB_CREATE_FILE_DEST
alter database enable block change tracking
[using file '/mydir/rman_change_track.f' reuse];
alter database disable block change tracking;
SELECT STATUS, FILENAME
FROM V$BLOCK_CHANGE_TRACKING;
Mount the database and move the change tracking file to a location that has more space
ALTER DATABASE RENAME FILE
'/disk1/bct/RDBMS/changetracking/o1_mf_2f71np5j_.chg' TO
'/disk2/bct/RDBMS/changetracking/o1_mf_2f71np5j_.chg';
To make a long-term archival backup
===================================
RUN {
ALLOCATE CHANNEL c1
DEVICE TYPE sbt
PARMS 'ENV=(OB_MEDIA_FAMILY=archival_backup)';
BACKUP DATABASE
TAG quarterly
KEEP FOREVER # KEEP UNTIL TIME 'SYSDATE+365'
RESTORE POINT FY06Q4;
}
To back up backup sets from disk to tape
========================================
list backupset 3;
BACKUP device type sbt BACKUPSET ALL
DELETE INPUT;
BACKUP device type sbt BACKUPSET 1,2
DELETE INPUT;
To back up image copies from disk to tape
=========================================
BACKUP DATAFILE COPY FROM TAG monDBCopy;
BACKUP DEVICE TYPE sbt
TAG "quarterly_backup"
COPY OF DATABASE
DELETE INPUT;
ARCHIVELOG DELETION POLICY
==========================
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 3 TIMES TO DISK;
RMAN> backup archivelog all delete input;
RMAN-08138: WARNING: archived log not deleted - must create more backups
RMAN> delete archivelog all;
RMAN-08138: WARNING: archived log not deleted - must create more backups
BACKUP OPTIMIZATION
===================
CONFIGURE BACKUP OPTIMIZATION ON;
skipping archived log file /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_42_808520212.dbf; already backed up 1 time(s)
Advanced
=========
To limit the size of backup sets
================================
BACKUP DEVICE TYPE sbt
MAXSETSIZE 100M
ARCHIVELOG ALL;
To backup with multisection
===========================
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup section size 100M database format '/u01/backup/full_%D_%s_%p_%T'
plus archivelog format '/u01/backup/arc_%D_%s_%p_%T';
}
To make a split mirror backup of a tablespace by using SUSPEND/RESUME
=====================================================================
SQL 'ALTER TABLESPACE users BEGIN BACKUP';
SQL 'ALTER SYSTEM SUSPEND';
SQL 'ALTER SYSTEM RESUME';
SQL 'ALTER TABLESPACE users END BACKUP';
CATALOG DATAFILECOPY '/dk2/oradata/trgt/users01.dbf'; # catalog split mirror
BACKUP DATAFILECOPY '/dk2/oradata/trgt/users01.dbf';
CHANGE DATAFILECOPY '/dk2/oradata/trgt/users01.dbf' UNCATALOG;
To make dual-mode encrypted backups
===================================
SET ENCRYPTION IDENTIFIED BY password ON FOR ALL TABLESPACES;
BACKUP DATABASE PLUS ARCHIVELOG;
To specify a backup duration
============================
BACKUP
DURATION 4:00
TABLESPACE users;
restore preview
===============
RESTORE DATABASE PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE DATABASE PREVIEW SUMMARY;
RESTORE DATABASE validate header;
You can use RESTORE ... PREVIEW RECALL to instruct the media manager to make offsite backups available.
RESTORE ARCHIVELOG ALL PREVIEW RECALL
Restore point
=============
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
REPORT used to determine whether your database is recoverable are
=================================================================
REPORT NEED BACKUP [redundancy n | recovery window of n days]
Reports which database files require backup because they have been
affected by some NOLOGGING operation such as a direct-path INSERT
==================================================================
REPORT UNRECOVERABLE
REPORT SCHEMA
Displays files that require more than n days' worth of archived redo log files for recovery.
REPORT NEED BACKUP DAYS n
REPORT NEED BACKUP INCREMENTAL n
REPORT NEED BACKUP REDUNDANCY 2 DATAFILE 1;
report need backup device type tap;
To report obsolete backups
==========================
CROSSCHECK BACKUP DEVICE TYPE sbt;
CROSSCHECK BACKUP; # crosshecks all backups on all devices
REPORT OBSOLETE;
REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
To query details about past and current RMAN jobs
=================================================
for information about the backup type, status, and start and end time
---------------------------------------------------------------------
COL STATUS FORMAT a9
COL hrs FORMAT 999.99
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
for the rate of backup jobs in an RMAN session
----------------------------------------------
COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
for the size of the backups in an RMAN session
----------------------------------------------
COL in_size FORMAT a10
COL out_size FORMAT a10
SELECT SESSION_KEY,
INPUT_TYPE,
COMPRESSION_RATIO,
INPUT_BYTES_DISPLAY in_size,
OUTPUT_BYTES_DISPLAY out_size
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
Unique Identifiers for Registered Databases
===========================================
Most recovery catalog views contain the columns DB_KEY and DBINC_KEY. Each database registered in the recovery catalog can be uniquely identified by either the primary key, which is the DB_KEY column value, or the DBID, which is the 32-bit unique database identifier. Each incarnation of a database is uniquely identified by the DBINC_KEY column.
CATALOG
DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS
BACKUP
INCREMENTAL LEVEL 0
MAXSETSIZE 10M
DATABASE
TAG 'BACKUP_1';
CONFIGURE BACKUP OPTIMIZATION ON
CONFIGURE ARCHIVELOG DELETION POLICY
TO BACKED UP 1 TIMES TO SBT;
DELETE
ARCHIVELOG ALL
BACKED UP 2 TIMES TO DEVICE TYPE sbt;
CHANGE BACKUP OF ARCHIVELOG TAG SECOND_COPY UNAVAILABLE;
BACKUP DATABASE
SKIP INACCESSIBLE
SKIP READONLY
SKIP OFFLINE;
Performing Block Media Recovery
###############################
To recover specific data blocks
===============================
RECOVER
DATAFILE 8 BLOCK 13
DATAFILE 2 BLOCK 199
FROM TAG mondayam;
To recover all blocks logged in V$DATABASE_BLOCK_CORRUPTION
===========================================================
RECOVER CORRUPTION LIST;
make a backup with tag
======================
backup as backupset
database device type disk
format '/disk1/backup_%U'
tag mondaybkp;
Making Compressed Backups
=========================
The following example backs up the database, switches the online redo logs, and includes archived logs in the backup:
By archiving the logs immediately after the backup, you ensure that you have a full set of archived logs through the time of the backup. In this way, you guarantee that you can perform media recovery after restoring this backup.
backup as compressed backupset
database plus archivelog;
Backing Up Tablespaces and Datafiles with RMAN
==============================================
RMAN automatically backs up the control file and the server parameter file (if the instance was started with a server parameter file) when datafile 1 is included in the backup. If control file autobackup is enabled, then RMAN writes the current control file and server parameter file to a separate autobackup piece. Otherwise, RMAN includes these files in the backup set that contains datafile 1.
backup device type sbt
tablesapce users, tools;
backup device type sbt
datafile 1,2,3
To manually back up the control file
====================================
backup device type sbt
tablespace users
include current controlfile;
BACKUP AS COPY
CURRENT CONTROLFILE
FORMAT '/tmp/control01.ctl';
To back up the server parameter file
====================================
backup device type sbt spfile;
Archived Redo Log Failover
==========================
The archived redo log failover feature enables RMAN to complete a backup even when some archiving destinations are missing logs or contain logs with corrupt blocks.
Online Redo Log Switching
=========================
Another important RMAN feature is automatic online redo log switching. To make an open database backup of archived redo logs that includes the most recent online redo log, you can execute the BACKUP command with any of the following clauses:
PLUS ARCHIVELOG
ARCHIVELOG ALL
ARCHIVELOG FROM ...
Before beginning the backup, RMAN switches out of the current redo log group, and archives all online redo logs that have not yet been archived, up to and including the redo log group that was current when the command was issued. This feature ensures that the backup contains all redo generated before the start of the command.
One of the most effective ways of backing up archived redo logs is the BACKUP ... PLUS ARCHIVELOG clause, which causes RMAN to do the following:
1.Runs the ALTER SYSTEM ARCHIVE LOG CURRENT statement.
2.Runs BACKUP ARCHIVELOG ALL. If backup optimization is enabled, then RMAN skips logs that it has already backed up to the specified device.
3.Backs up the rest of the files specified in BACKUP command.
4.Runs the ALTER SYSTEM ARCHIVE LOG CURRENT statement.
Backs up any remaining archived logs generated during the backup. If backup optimization is not enabled, then RMAN backs up the logs generated in step 1 plus all the logs generated during the backup.
The preceding steps guarantee that datafile backups taken during the command are recoverable to a consistent state. Also, unless the online redo log is archived at the end of the backup, DUPLICATE is not possible with the backup.
To back up archived redo log files
==================================
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP ARCHIVELOG ALL;
BACKUP ARCHIVELOG
FROM TIME 'SYSDATE-30'
UNTIL TIME 'SYSDATE-7';
To back up archived redo logs that need backups
===============================================
BACKUP ARCHIVELOG ALL NOT BACKED UP 2 TIMES;
To delete archived redo logs after a backup
===========================================
For the following procedure, assume that you archive to /arc_dest1, /arc_dest2, and the fast recovery area.
BACKUP DEVICE TYPE sbt
ARCHIVELOG ALL
DELETE ALL INPUT;
In this case, RMAN backs up only one copy of each log sequence number in these archiving locations. RMAN does not delete any logs from the fast recovery area, but it deletes all copies of any log that it backed up from the other archiving destinations. The logs in the fast recovery area that are eligible for deletion are automatically removed by the database when space is needed.
If you had specified DELETE INPUT rather than DELETE ALL INPUT, then RMAN would only delete the specific archived redo log files that it backed up. For example, RMAN would delete the logs in /arc_dest1 if these files were used as the source of the backup, but leave the contents of the /arc_dest2 intact.
The following SQL query determines the number of blocks written to an incremental level 1 backup of each datafile with at least 20% of its blocks backed up
====================================================
SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME,
BLOCKS, DATAFILE_BLOCKS
FROM V$BACKUP_DATAFILE
WHERE INCREMENTAL_LEVEL > 0
AND BLOCKS / DATAFILE_BLOCKS > .2
ORDER BY COMPLETION_TIME;
To make an incremental backup
=============================
backup incremental level 0 database;
backup incremental level 1 tablesapce system, tools;
backup incremental level 1 cumulative tablesapce users;
To enable block change tracking
===============================
SHOW PARAMETER DB_CREATE_FILE_DEST
alter database enable block change tracking
[using file '/mydir/rman_change_track.f' reuse];
alter database disable block change tracking;
SELECT STATUS, FILENAME
FROM V$BLOCK_CHANGE_TRACKING;
Mount the database and move the change tracking file to a location that has more space
ALTER DATABASE RENAME FILE
'/disk1/bct/RDBMS/changetracking/o1_mf_2f71np5j_.chg' TO
'/disk2/bct/RDBMS/changetracking/o1_mf_2f71np5j_.chg';
To make a long-term archival backup
===================================
RUN {
ALLOCATE CHANNEL c1
DEVICE TYPE sbt
PARMS 'ENV=(OB_MEDIA_FAMILY=archival_backup)';
BACKUP DATABASE
TAG quarterly
KEEP FOREVER # KEEP UNTIL TIME 'SYSDATE+365'
RESTORE POINT FY06Q4;
}
To back up backup sets from disk to tape
========================================
list backupset 3;
BACKUP device type sbt BACKUPSET ALL
DELETE INPUT;
BACKUP device type sbt BACKUPSET 1,2
DELETE INPUT;
To back up image copies from disk to tape
=========================================
BACKUP DATAFILE COPY FROM TAG monDBCopy;
BACKUP DEVICE TYPE sbt
TAG "quarterly_backup"
COPY OF DATABASE
DELETE INPUT;
ARCHIVELOG DELETION POLICY
==========================
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 3 TIMES TO DISK;
RMAN> backup archivelog all delete input;
RMAN-08138: WARNING: archived log not deleted - must create more backups
RMAN> delete archivelog all;
RMAN-08138: WARNING: archived log not deleted - must create more backups
BACKUP OPTIMIZATION
===================
CONFIGURE BACKUP OPTIMIZATION ON;
skipping archived log file /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_42_808520212.dbf; already backed up 1 time(s)
Advanced
=========
To limit the size of backup sets
================================
BACKUP DEVICE TYPE sbt
MAXSETSIZE 100M
ARCHIVELOG ALL;
To backup with multisection
===========================
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup section size 100M database format '/u01/backup/full_%D_%s_%p_%T'
plus archivelog format '/u01/backup/arc_%D_%s_%p_%T';
}
To make a split mirror backup of a tablespace by using SUSPEND/RESUME
=====================================================================
SQL 'ALTER TABLESPACE users BEGIN BACKUP';
SQL 'ALTER SYSTEM SUSPEND';
SQL 'ALTER SYSTEM RESUME';
SQL 'ALTER TABLESPACE users END BACKUP';
CATALOG DATAFILECOPY '/dk2/oradata/trgt/users01.dbf'; # catalog split mirror
BACKUP DATAFILECOPY '/dk2/oradata/trgt/users01.dbf';
CHANGE DATAFILECOPY '/dk2/oradata/trgt/users01.dbf' UNCATALOG;
To make dual-mode encrypted backups
===================================
SET ENCRYPTION IDENTIFIED BY password ON FOR ALL TABLESPACES;
BACKUP DATABASE PLUS ARCHIVELOG;
To specify a backup duration
============================
BACKUP
DURATION 4:00
TABLESPACE users;
restore preview
===============
RESTORE DATABASE PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE DATABASE PREVIEW SUMMARY;
RESTORE DATABASE validate header;
You can use RESTORE ... PREVIEW RECALL to instruct the media manager to make offsite backups available.
RESTORE ARCHIVELOG ALL PREVIEW RECALL
Restore point
=============
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
REPORT used to determine whether your database is recoverable are
=================================================================
REPORT NEED BACKUP [redundancy n | recovery window of n days]
Reports which database files require backup because they have been
affected by some NOLOGGING operation such as a direct-path INSERT
==================================================================
REPORT UNRECOVERABLE
REPORT SCHEMA
Displays files that require more than n days' worth of archived redo log files for recovery.
REPORT NEED BACKUP DAYS n
REPORT NEED BACKUP INCREMENTAL n
REPORT NEED BACKUP REDUNDANCY 2 DATAFILE 1;
report need backup device type tap;
To report obsolete backups
==========================
CROSSCHECK BACKUP DEVICE TYPE sbt;
CROSSCHECK BACKUP; # crosshecks all backups on all devices
REPORT OBSOLETE;
REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
To query details about past and current RMAN jobs
=================================================
for information about the backup type, status, and start and end time
---------------------------------------------------------------------
COL STATUS FORMAT a9
COL hrs FORMAT 999.99
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
for the rate of backup jobs in an RMAN session
----------------------------------------------
COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
for the size of the backups in an RMAN session
----------------------------------------------
COL in_size FORMAT a10
COL out_size FORMAT a10
SELECT SESSION_KEY,
INPUT_TYPE,
COMPRESSION_RATIO,
INPUT_BYTES_DISPLAY in_size,
OUTPUT_BYTES_DISPLAY out_size
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
Unique Identifiers for Registered Databases
===========================================
Most recovery catalog views contain the columns DB_KEY and DBINC_KEY. Each database registered in the recovery catalog can be uniquely identified by either the primary key, which is the DB_KEY column value, or the DBID, which is the 32-bit unique database identifier. Each incarnation of a database is uniquely identified by the DBINC_KEY column.