RMAN Client

The RMAN client sends RMAN and SQL commands to the database.

The main commands implemented by RMAN include:

BACKUP
RESTORE
RECOVER
LIST
REPORT
CROSSCHECK
CATALOG

RMAN is similar in many respects to the SQL*Plus client. However, it was clearly developed separately and there are many minor differences between the two clients.

RMAN Catalog

RMAN maintains a catalog of backups created for the database. The catalog is always stored in the control file of the target database. The catalog may also optionally be stored in a CATALOG which is a dedicated schema, usually in a separate schema

When connecting to RMAN a target database must always specified. For example to connect to the local database using the bequeath protocol use:

  rman TARGET /

If a catalog is not specified then RMAN will use data in the control files

Optionally NOCATALOG can be specified. For example:

  rman TARGET / NOCATALOG

If a catalog database exists this should be specified using the CATALOG clause. For example:

  rman TARGET / CATALOG catowner/catpassword@rman

RMAN Logging

There are several ways to log RMAN output.

LOG clause

A log file can be specified by the LOG clause in the RMAN command. For example:

  rman TARGET / LOG rman.log

The above command will redirect all output to the file rman.log

The log file is opened with write access so any previous output will be overwritten

Tee Command

The LOG command redirects output to the log file. Output is not displayed in the RMAN session.

For Linux/Unix output can alternatively be redirected to the tee command For example:

  rman TARGET / | tee rman.log

In the above example output will be displayed in the RMAN session, but will also be written to rman.log. Therefore the tee command is often preferable to the LOG clause.

SPOOL Command

In addition to command line LOG clause, logging can be enabled and disabled interactively using the SPOOL command. The syntax of this command differs from SQL*Plus.

To start spooling output to a file use:

RMAN> SPOOL LOG TO <filename>;

To stop spooling output use:

RMAN> SPOOL LOG OFF;

Timestamps

RMAN outputs timestamps within various commands including:

BACKUP
RECOVER
RESTORE
LIST
REPORT

By default the timestamps will be in the format DD-MOM-YY. To specify an alternative timestamp format, set the NLS_DATE_FORMAT environment variable.

For example:

$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
$ rman target /

In this example the above command changed:

  2015-08-12

  2015-08-12 11:26:51

To include a comment in an RMAN command file or run block prefix the line with a hash sign e.g.:

RMAN> # This is a comment

Most commands including BACKUP, RESTORE and RECOVER must be terminated with a semicolon. Some commands including STARTUP, SHUTDOWN and CONNECT do not require a semicolon.

To exit the client use:

RMAN> EXIT

Note that no semicolon is required for the EXIT statement

Command Files

It is possible to execute external scripts (command files) from RMAN

The command file must contain valid RMAN commands.

The command file can have any name, but by convention the extension is ".rman"

The command file can be parameterized using positional substition variables which are &1, &2 etc.

There are three ways to execute command files within RMAN

Command Line

Command files can be executed within the command line For example:

rman target / @BackupDatabase.rman

RMAN Prommpt

Command files can be executed at the RMAN prompt. For example:

RMAN> @BackupDatabase.rman

Within a RUN command

Command files can be executed within a RUN command. For example:

RUN {
  ALLOCATE CHANNEL ch11 TYPE DISK;
  @BackupDatabase.rman
  RELEASE CHANNEL ch11;

RMAN Backup Command

RMAN backups are performed using the BACKUP statement

To perforn a full database backup use:

RMAN> BACKUP DATABASE;

To backup all archive logs use:

RMAN> BACKUP ARCHIVELOG ALL;

To backup the database and all archive log files use:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Note that the PLUS ARCHIVELOG clause performs the following:

Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command
Runs the BACKUP ARCHIVELOG ALL command. If backup optimization is enabled only backs up logs that have not already been backed up.
Backs up files specified in the BACKUP command
Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command
Backs up any remaining archived logs including those generated during the backup

A FORMAT can be specified for each subclause in the BACKUP DATABASE PLUS ARCHIVELOG command. For example:

RUN
{
  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
  BACKUP
  FORMAT '/u03/app/oracle/TEST/%d_D_%T_%u_s%s_p%p'
  DATABASE
  PLUS ARCHIVELOG
  FORMAT '/u03/app/oracle/TEST/%d_A_%T_%u_s%s_p%p';
  RELEASE CHANNEL ch11;
}

Note that FORMAT clause precedes the DATABASE clause, but follows the PLUS ARCHIVELOG clause.

The BACKUP command can be extended to backup the current control file and the SPFILE.

For example:

RUN
{
  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
  BACKUP
  FORMAT '/u03/app/oracle/TEST/%d_D_%T_%u_s%s_p%p'
  DATABASE
  CURRENT CONTROLFILE
  FORMAT '/u03/app/oracle/TEST/%d_C_%T_%u'
  SPFILE
  FORMAT '/u03/app/oracle/TEST/%d_S_%T_%u'
  PLUS ARCHIVELOG
  FORMAT '/u03/app/oracle/TEST/%d_A_%T_%u_s%s_p%p';
  RELEASE CHANNEL ch11;
}

Controlfile backups

The current controlfile can be automatically backed up by the BACKUP command by configuring the CONTROLFILE AUTOBACKUP parameters

To backup the current controlfile explicitly use:

RMAN> BACKUP CURRENT CONTROLFILE;

SPFILE backups

The SPFILE can be automatically backed up with the control file during database backups by configuring the CONTROLFILE AUTOBACKUP parameters

To backup up the SPFILE explicitly use:

RMAN> BACKUP SPFILE;

Datafile backups

To backup a specific data file use BACKUP DATAFILE. For example:

RMAN> BACKUP DATAFILE '/u01/app/oradata/TEST/users01.dbf';

Altermatively specify the data file number. For example:

RMAN> BACKUP DATAFILE 4;

The data file number can be obtained from V$DATAFILE. For example:

SQL> SELECT file#, name FROM v$datafile;

Tablespace Backups

To backup a tablespace use the BACKUP TABLESPACE command. For example:

RMAN> BACKUP TABLESPACE USERS;

Compressed Backups

To compress the backup use:

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;

The resulting compressed backup is around 20%-30% of the size of the uncompressed equivalent.

Format clause

The format clause allows the backup files to be directed to a specific location.

For example:

BACKUP FORMAT '/u01/app/oracle/backup/%U' DATABASE;

The above statement created the following files in /u01/app/oracle/backup:

[oracle@vm3]$ ls -l /u01/app/oracle/backup
total 1161280
-rw-r----- 1 oracle oinstall 1178050560 Aug 14 06:31 15qeibgs_1_1
-rw-r----- 1 oracle oinstall    9928704 Aug 14 06:31 16qeibld_1_1

Other formats can be specified. For example:

BACKUP FORMAT '/backup2/TEST/TEST_df_%t_s%s_p%p' DATABASE;

In the above example %t is the backup set timestamp, %s is the backup set number and %p is the piece number within the backup set.

Incremental Backups

By default backups are full (level 0). Backups can also be incremental (level 1).

Incremental backups can be:

Differential - includes all changes since the last full or incremental backup
Cumulative - includes all changes since the last full backup

Differential backups require less space. Cumulative backups are faster to restore

Differential backups are the default.

To run a diffential incremental backup use:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

To run a cumulative incremental backup use:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Note that in order to take a level 1 backup, a level 0 backup must already exist.

A full backup using BACKUP DATABASE is not the same as a level 0 backup. - the LV column of the LIST BACKUP output is NULL after a full backup.

In order to take a level 0 backup use

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

To create an image copy of an entire database use:

RMAN> BACKUP AS COPY DATABASE;

To create an image copy of a specific datafile use:

RMAN> BACKUP AS COPY DATAFILE <file#>

For example:

RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '/u01/app/oracle/copy/users01.dbf';

Alternatively specify the source file name. For example:

RMAN> BACKUP AS COPY DATAFILE '/u01/app/oradata/TEST/users01.dbf'
FORMAT '/u01/app/oracle/copy/users01.dbf';

Recovery Area

To backup the recovery area use:

RMAN> BACKUP RECOVERY AREA TO DESTINATION '/u02/app/oracle';

Note that a destination must be specified if the recovery area is being backed up to disk.

RMAN Catalog Command

It is sometimes necessary to relocate backups to another directory for operational reasons e.g. space management or file system mount points.

The CATALOG command allows one or more backup files to be registered with the catalog within the control file and optionally in the RMAN catalog database.

To register all backup files within a directory use

CATALOG START WITH

For example:

CATALOG START WITH '/u01/oracle/backup/TEST';

The above command will register all backup files in the /u01/oracle/backup/TEST directory with the RMAN catalogs.

RMAN Configure Command

The CONFIGURE command can be used to show or set parameter values.

Default Parameters

RMAN includes a number of parameters which initially have default values.

The CONFIGURE command can be used to get or set parameter values.

The default values are as follows:

RMAN> SHOW ALL;

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
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;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_TEST.f'; # default

To set a configuration parameter use the CONFIGURE command. For example:

RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;

To unset a configuration parameter use the CLEAR keyword. For example:

RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR;

Non-standard parameters can also be cleared. For example:

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK CLEAR;

Non-default configuration parameter values are reported in the V$RMAN_CONFIGURATION dynamic performance view.

In Oracle 11.2 this view has the following columns:

Column Name	Data Type	Notes
CONF#	NUMBER
NAME	VARCHAR2(65)
VALUE	VARCHAR2(1025)

In Oracle 11.2 V$RMAN_CONFIGURATION contains the following rows:

Conf#	Name	Value
1	RETENTION POLICY	TO REDUNDANCY 1
2	BACKUP OPTIMIZATION	OFF
3	DEFAULT DEVICE TYPE TO	DISK
4	CONFIGURE AUTOBACKUP	OFF
5	CONFIGURE AUTOBACKUP FORMAT FOR DEVICE TYPE	DISK TO '%F'
6	DEVICE TYPE	DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET
7	DATAFILE BACKUP COPIES FOR DEVICE TYPE	DISK TO 1
8	ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE	DISK TO 1
9	MAXSETSIZE TO	UNLIMITED
10	ENCRYPTION FOR DATABASE	OFF
11	ENCRYPTION ALGORITHM	'AES128'
12	COMPRESSION ALGORITHM	'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE
13	ARCHIVELOG DELETION POLICY	TO NONE

RMAN CrossCheck Command

The CROSSCHECK statement compares backup files on disk with entries in the catalog or controlfile.

If a file is found in the catalog, but does not exist on disk it is marked as "EXPIRED".

To crosscheck all backups use:

RMAN> CROSSCHECK BACKUP;

To list any expired backups detected by the CROSSCHECK command use:

RMAN> LIST EXPIRED BACKUP;

De forma resumida:

RMAN> LIST EXPIRED BACKUP SUMMARY;

To delete any expired backups detected by the CROSSCHECK command use:

RMAN> DELETE EXPIRED BACKUP;

To crosscheck all archive logs use:

RMAN> CROSSCHECK ARCHIVELOG ALL;

To list all expired archive logs detected by the CROSSCHECK command use:

RMAN> LIST EXPIRED ARCHIVELOG ALL;

To delete all expired archive logs detected by the CROSSCHECK command use:

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

To crosscheck all datafile image copies use:

RMAN> CROSSCHECK DATAFILECOPY ALL;

To list expired datafile copies use:

RMAN> LIST EXPIRED DATAFILECOPY ALL;

To delete expired datafile copies use:

RMAN> DELETE EXPIRED DATAFILECOPY ALL;

To crosscheck all backups of the USERS tablespace use:

RMAN> CROSSCHECK BACKUP OF TABLESPACE USERS;

To list expired backups of the USERS tablespace:

RMAN> LIST EXPIRED BACKUP OF TABLESPACE USERS;

To delete expired backups of the USERS tablespace:

RMAN> DELETE EXPIRED BACKUP OF TABLESPACE USERS;

RMAN Delete Command

The RMAN DELETE command deletes backups from disk and/or from the catalog

To delete all backups for the target database use:

RMAN> DELETE BACKUP;

Delete Backupset

To delete a backup set specify the set number e.g. 23:

RMAN> DELETE BACKUPSET 23;

NOPROMPT keyword

By default the DELETE command will prompt for confirmation before deleting any backup files

Do you really want to delete the above objects (enter YES or NO)?

To suppress the prompt specify the NOPROMPT keyword. For example:

RMAN> DELETE NOPROMPT BACKUP;

Image Copies

To delete all datafile copies:

RMAN> DELETE DATAFILECOPY ALL;

To delete an individual datafile copy use:

RMAN> DELETE DATAFILECOPY <key>;

For example:

RMAN> DELETE DATAFILECOPY 26;

Alternatively specify the datafile image copy name. For example:

RMAN> DELETE DATAFILECOPY '/u01/app/oracle/copy/users01.dbf';

To delete a specific controlfile copy use:

RMAN> DELETE CONTROLFILECOPY <key>;

For example:

RMAN> DELETE CONTROLFILECOPY 20;

Alternatively specify the control file copy name e.g.:

RMAN> DELETE CONTROLFILECOPY '/u01/app/oracle/copy/cf_D-TEST_id-2066695660_1tqek8bd';

To delete all backups of the USERS tablespace use:

RMAN> DELETE BACKUP OF TABLESPACE USERS;

Expired Backups

To delete any expired backups detected by the CROSSCHECK command use:

RMAN> DELETE EXPIRED BACKUP;

To delete all expired archive logs detected by the CROSSCHECK command use:

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

Obsolete Backups

To delete backups that have become obsolete based on the retention policy.

RMAN> DELETE OBSOLETE;

RMAN List Command

Database

The LIST command allows the backup data to be listed in the RMAN utility

To list all existing backups use:

RMAN> LIST BACKUP;

To list all existing copy use:

RMAN> LIST COPY OF DATABASE;

To list all existing database backups use:

RMAN> LIST BACKUP OF DATABASE;

To list all existing backups of a specific datafile use:

RMAN> LIST BACKUP OF DATAFILE <file#>;

For example:

RMAN> LIST BACKUP OF DATAFILE 4;

Alternatively specify the datafile name. For example:

RMAN> LIST BACKUP OF DATAFILE '/u01/app/oradata/TEST/users01.dbf';

To list all existing archivelog backups use:

RMAN> LIST BACKUP OF ARCHIVELOG ALL;

list copy of archivelog all;

To list all existing controfile backups use:

RMAN> LIST BACKUP OF CONTROLFILE;

To list all existing SPFILE backups use:

RMAN> LIST BACKUP OF SPFILE;

Archive Logs

To list all archive logs use:

RMAN> LIST ARCHIVELOG ALL;

Backup sets

To list the contents of an individual backup set use:

RMAN> LIST BACKUPSET <key>;

For example:

RMAN> LIST BACKUPSET 44;

Datafile Image Copies

To list all datafile image copies use:

RMAN> LIST DATAFILECOPY ALL;

To list an individual datafile image copy use:

RMAN> LIST DATAFILECOPY <key>;

For example:

RMAN> LIST DATAFILECOPY 26;

Alternatively specify the name of the datafile image copy file:

RMAN> LIST DATAFILECOPY '/u01/app/oracle/copy/users01.dbf';

Controlfile Image Copies

To list all controlfile copies use

RMAN> LIST COPY OF CONTROLFILE;

For example:

RMAN> LIST COPY OF CONTROLFILE;

List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
25      A 15-AUG-15       3324254    15-AUG-15
        Name: /u01/app/oracle/copy/cf_D-TEST_id-2066695660_23qekadv
        Tag: TAG20150815T001910

20      A 14-AUG-15       3322965    14-AUG-15
        Name: /u01/app/oracle/copy/cf_D-TEST_id-2066695660_1tqek8bd
        Tag: TAG20150814T234341

15      A 14-AUG-15       3322589    14-AUG-15
        Name: /u01/app/oracle/copy/cf_D-TEST_id-2066695660_1nqek7j8
        Tag: TAG20150814T232907

To list an individual control file copy use:

RMAN> LIST CONTROLFILECOPY <key>;

For example:

RMAN> LIST CONTROLFILECOPY 20;

List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
20      A 14-AUG-15       3322965    14-AUG-15
        Name: /u01/app/oracle/copy/cf_D-TEST_id-2066695660_1tqek8bd
        Tag: TAG20150814T234341

Tablespaces

To list backups of a tablespace use the LIST BACKUP OF TABLESPACE command. For example:

RMAN> LIST BACKUP OF TABLESPACE USERS;

Incarnations

The LIST INCARNATION command shows the incarnations of the database. Note that multiple incarnations may share the same database ID.

RMAN> LIST INCARNATION;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2066695660       PARENT  1          17-SEP-11
2       2       TEST     2066695660       PARENT  995548     30-NOV-11
3       3       TEST     2066695660       ORPHAN  1190860    09-FEB-12
4       4       TEST     2066695660       CURRENT 1190860    09-FEB-12

RMAN Recover Command

After copies of files have been restored from the backup media using the RMAN RESTORE command, it is necessary to perform complete or incomplete recovery to ensure that the restored data files are consistent. Recovery is performed using the RECOVER command.

To recover the entire database restored from a backup use:

RECOVER DATABASE;

RMAN Report Command

The RMAN REPORT command generates various reports from the backup catalog.

To report which database files need to be backed up to meet the current retention policy use:

RMAN> REPORT NEED BACKUP;

To report all obsolete backups use:

RMAN> REPORT OBSOLETE;

RMAN Restore Command

RMAN restores are performed using the RESTORE statement

A restore operation copies one or more files from the backup media back to disk. Following a restore operation, RECOVER operation is required to ensure that the restored files are consistent.

To restore the entire database from a backup use:

RESTORE DATABASE;

To restore a specific data file use RESTORE DATAFILE. For example:

RESTORE DATAFILE '/u01/app/oradata/TEST/users01.dbf';

Altermatively specify the data file number. For example:

RESTORE DATAFILE 4;

The data file number can be obtained from V$DATAFILE. For example:

SELECT file#, name FROM v$datafile;

To restore the control file from an autobackup use:

  RESTORE CONTROLFILE FROM AUTOBACKUP;

For example:

RUN
{
  SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/TEST/%F';
  ALLOCATE CHANNEL ch11 DEVICE TYPE DISK FORMAT '/u01/backup/TEST/%F';
  RESTORE CONTROLFILE FROM AUTOBACKUP;
}

Restore Preview

To generate a preview of a restore operation use:

RMAN> RESTORE DATABASE PREVIEW;

Note that this operation uses metadata to generate output. It does not scan the backup files

For example:

RMAN> RESTORE DATABASE PREVIEW;

Starting restore at 23-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
57      Full    1.13G      DISK        00:02:29     23-SEP-15
        BP Key: 60   Status: AVAILABLE  Compressed: NO  Tag: TAG20150923T044455
        Piece Name: /u03/app/oracle/fast_recovery_area/TEST/backupset/2015_09_23/o1_mf_nnndf_TAG20150923T044455_c0481qyb_.bkp
  List of Datafiles in backup set 57
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 4456951    23-SEP-15 /u01/app/oradata/TEST/system01.dbf
  2       Full 4456951    23-SEP-15 /u01/app/oradata/TEST/sysaux01.dbf
  3       Full 4456951    23-SEP-15 /u01/app/oradata/TEST/undotbs01.dbf
  4       Full 4456951    23-SEP-15 /u01/app/oradata/TEST/users01.dbf
using channel ORA_DISK_1

archived logs generated after SCN 4453629 not found in repository
Media recovery start SCN is 4453629
Recovery must be done beyond SCN 4456951 to clear datafile fuzziness
Finished restore at 23-SEP-15

To generate a summary of a restore operation preview use:

RMAN> RESTORE DATABASE PREVIEW SUMMARY;

For example:

RMAN> RESTORE DATABASE PREVIEW SUMMARY;

Starting restore at 23-SEP-15
using channel ORA_DISK_1


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
57      B  F  A DISK        23-SEP-15       1       1       NO         TAG20150923T044455

archived logs generated after SCN 4453629 not found in repository
Media recovery start SCN is 4453629
Recovery must be done beyond SCN 4456951 to clear datafile fuzziness
Finished restore at 23-SEP-15

RMAN Set Command

The SET command is used to specify various parameters both inside and outside RUN blocks.

SET DBID

When recreating a database it is necessary to specify the DBID. RMAN uses the DBID to identify automatic controlfile backup files.

The DBID can be obtained from another copy of the database using the following query:

SELECT dbid FROM v$database;

SET NEWNAME

The SET NEWNAME statement is used to specify that data files etc should be relocated by the RESTORE command.

For example to rename a specific data file use SET NEWNAME FOR DATAFILE

SET NEWNAME FOR DATAFILE 24 TO '/u01/oradata/TEST/test_07.dbf';

It is also possible to rename all datafiles using SET NEWNAME FOR DATABASE

SET NEWNAME FOR DATABASE TO '/u01/oradata/TEST/%b';

Note the use of the %b wildcard which matches the base name of every file in the source directory.

SET NEWNAME FOR DATABASE is not supported in Oracle 10.2.0.1

SET UNTIL

The SET UNTIL statement specifies an SCN at which recovery should stop.

For example:

SET UNTIL SCN 1533392288;

RMAN Show Command

The SHOW command returns the values of various parameters.

SHOW ALL

The SHOW ALL command shows the value of all configuration parameters.

The default values are as follows:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
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;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_TEST.f'; # default

The default value for SNAPSHOT CONTROLFILE NAME is $ORACLE_HOME/snapcf_.f

The SHOW command can also report a single parameter value. For example:

RMAN> SHOW BACKUP OPTMIZATION;

RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION ON;

RMAN Validate Command

Database files can be validated using the RMAN VALIDATE statement

To validate an entire database use:

VALIDATE DATABASE;

For example:

RMAN> VALIDATE DATABASE;

Starting validate at 23-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/app/oradata/TEST/system01.dbf
input datafile file number=00002 name=/u01/app/oradata/TEST/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oradata/TEST/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oradata/TEST/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              15399        93442           4464162
  File Name: /u01/app/oradata/TEST/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              62215
  Index      0              13060
  Other      0              2766

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              22615        87042           4464038
  File Name: /u01/app/oradata/TEST/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              19664
  Index      0              16520
  Other      0              28241

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              33           19200           4464161
  File Name: /u01/app/oradata/TEST/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              19167

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              1292         3846            4088662
  File Name: /u01/app/oradata/TEST/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1228
  Index      0              383
  Other      0              937

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              604
Finished validate at 23-SEP-15

To validate an individual datafile use VALIDATE DATAFILE:

For example:

RMAN> VALIDATE DATAFILE '/u01/app/oradata/TEST/users01.dbf';

Starting validate at 23-SEP-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=/u01/app/oradata/TEST/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              1292         3846            4088662
  File Name: /u01/app/oradata/TEST/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1228
  Index      0              383
  Other      0              937

Finished validate at 23-SEP-15

Altermatively specify the data file number. For example:

RMAN> VALIDATE DATAFILE 4;

Starting validate at 23-SEP-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=/u01/app/oradata/TEST/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              1292         3846            4088662
  File Name: /u01/app/oradata/TEST/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1228
  Index      0              383
  Other      0              937

Finished validate at 23-SEP-15

The data file number can be obtained from V$DATAFILE. For example:

SELECT file#, name FROM v$datafile;

RMAN Backup Optimization

Backup optimization specifies that files can be omitted from the backup in some circumstances if an identical copy of the file has already been backed up.

Backup optimization is an RMAN parameter which is specified using the CONFIGURE parameter.

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

Backup optimization can be used with following operations:

BACKUP DATABASE
BACKUP ARCHIVELOG ALL
BACKUP ARCHIVELOG LIKE
BACKUP BACKUPSET ALL
BACKUP RECOVERY AREA
BACKUP RECOVERY FILES
BACKUP DATAFILECOPY

Only one type of channel can be allocated - disk and tape channels cannot be specified in the same backup command.

RMAN determines that a file is identical to one that has already been backed up based on the following rules:

Datafile - Must have same DBID, checkpoint SCN, creation SCN, RESETLOGS SCN and RESETLOGS time as a data file that has already been backed up. The datafile must be offine-normal, read-only or closed normally.
Archived log - Must have same DBID, thread, sequence number, RESETLOGS SCN and RESETLOGS time. as an archived log that has already been backed up.
Backup set - Must have same DBID, backup set record ID and stamp as a backup set that already exists

In addition RMAN considers both the retention policy and backup duplexing when determining if a file can be skipped.

Backup optimization can be overridden using the FORCE option. For example:

RMAN> BACKUP DATABASE FORCE;
RMAN> BACKUP ARCHIVELOG ALL FORCE;

RMAN Channels

In RMAN channels can either be allocated implicitly or explicitly

To allocate channels explicitly use the ALLOCATE CHANNEL and RELEASE CHANNEL statements within a RUN block

For Standard Edition databases only one channel can be allocated. Backup and restore operations will be processed serially.

For Enterprise Edition databases one or more channels can be allocated. If multiple channels are allocated then backup and restore operations may be processes in parallel.

ALLOCATE CHANNEL

Channels are allocated using the ALLOCATE CHANNEL statement

For example:

  ALLOCATE CHANNEL ch11 TYPE DISK;
  ALLOCATE CHANNEL ch12 TYPE DISK;

The ALLOCATE CHANNEL statement takes a number of options.

To specify a maximum piece size use MAXPIECESIZE. For example to restrict each backup piece to 5G use:

  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 5G;

RELEASE CHANNEL

Channels are released using the RELEASE CHANNEL statement.

For example:

  RELEASE CHANNEL ch11;
  RELEASE CHANNEL ch12;

RMAN Controlfile Backups

Autobackup

CONTROLFILE AUTOBACKUP automatically backs up the control file and SPFILE following any backup command e.g. BACKUP DATABASE.

CONTROLFILE AUTOBACKUP is a configuration parameter. The default value is OFF.

To enable controlfile autobackups use:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

The destination and file name format for the controlfile autobackup can be specified. For example:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/TEST/%F';

Snapshot Controlfile

A snapshot control file is created at the start of a backup to preserve a consistent version of the control file in case the database structure is modified during the backup.

The location of the snapshot control file is a configuration parameter. For example:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backup/TEST/snapcf_TEST.f';

RMAN Format Specifiers

Format specifiers allow backup file names to be customized.

The following substitiion variables can be used:

%a	Database Activition ID
%b	Base name - only vaied for SET NEWNAME and image copies
%c	Backup piece copy number
%d	Database name
%D	Day of month (DD)
%e	Archive log sequence number
%f	Absolute file number
%F	System-generated name. See below
%h	Archived redo log thread number
%I	DBID
%M	Month (MM)
%N	Tablespace name
%n	Database name - Right padded with 'x' character e.g. TESTxxxx
%p	Piece number within backup set
%s	Backup set number
%t	Backup set timestamp. Use with %s to provide unique name
%T	Date (YYYYMMDD)
%u	Short system generated file name. See below
%U	Long System generated file name. See below
%Y	Year (YYYY)
%%	Percent (%) character

The following section describes the system-generated names in more detail:

%F - Generates a name in the format c-IIIIIIIIII-YYYYMMDD-QQ where:
- IIIIIIIIII is the DBID
- YYYYMMDD is a timestamp
- QQ is the sequence number in hexadecimal

%u - Generates an 8-character name using compressed versions of:
- Backup set or image copy number
- Time name was generated

%U - System-generated unique filename. Differs for backup pieces and image copies
For backup pieces equivalent to %u_%p_%c where:
- %u is the short generated name
- %p is the piece number
- %c is the copy number
For image copies depends on file type:
- For datafiles format is data-D-%d_id-%I_TS-%N_FNO-%f_%u
- For archived redo log format is arch-D-%d_id-%I_S-%e_T-%h_A-%a_%u
- For control files format is cf-D_%d-id-%I_%u

RMAN Image Copies

There are several ways to create an image copy

Using the FORMAT clause

For example:

BACKUP AS COPY DATABASE FORMAT '/u01/app/oracle/copy/%U';

Using tbe CONFIGURE command

For example:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/copy/%U';
BACKUP AS COPY DATABASE;

Using ALLOCATE CHANNEL command

For example:

RUN
{
  ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/u01/app/oracle/copy/%U';
  BACKUP AS COPY DATABASE;
  RELEASE CHANNEL c1;
}

To create an image copy of an entire database use:

RMAN> BACKUP AS COPY DATABASE;

To create an image copy of a specific datafile use:

RMAN> BACKUP AS COPY DATAFILE <file#>

For example:

RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '/u01/app/oracle/copy/users01.dbf';

To list all datafile image copies use:

LIST DATAFILECOPY ALL;

To delete all datafile image copies use:

DELETE DATAFILECOPY ALL;

Each datafile copy has a key which is reported by LIST DATAFILECOPY ALL;

The key is not the same as the data file number - there can be multiple copies of the same data file.

Individual datafile copies can be listed by specifying the key. For example:

RMAN> LIST DATAFILECOPY 26

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
26      4    A 15-AUG-15       3325470    15-AUG-15
        Name: /u01/app/oracle/copy/users01.dbf
        Tag: TAG20150815T010526

Individual datafile copies can be deleted by specifying the key. For example:

RMAN> DELETE DATAFILECOPY 26;

Controlfile Image Copies

To list all controlfile copies use

RMAN> LIST COPY OF CONTROLFILE;

For example:

RMAN> LIST COPY OF CONTROLFILE;

List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
25      A 15-AUG-15       3324254    15-AUG-15
        Name: /u01/app/oracle/copy/cf_D-TEST_id-2066695660_23qekadv
        Tag: TAG20150815T001910

20      A 14-AUG-15       3322965    14-AUG-15
        Name: /u01/app/oracle/copy/cf_D-TEST_id-2066695660_1tqek8bd
        Tag: TAG20150814T234341

15      A 14-AUG-15       3322589    14-AUG-15
        Name: /u01/app/oracle/copy/cf_D-TEST_id-2066695660_1nqek7j8
        Tag: TAG20150814T232907

To list an individual control file copy use:

RMAN> LIST CONTROLFILECOPY <key>;

For example:

RMAN> LIST CONTROLFILECOPY 20;

List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
20      A 14-AUG-15       3322965    14-AUG-15
        Name: /u01/app/oracle/copy/cf_D-TEST_id-2066695660_1tqek8bd
        Tag: TAG20150814T234341

To delete a specific controlfile copy use:

RMAN> DELETE CONTROLFILECOPY <key>;

For example:

RMAN> DELETE CONTROLFILECOPY 20;

Alternatively specify the control file copy name e.g.:

RMAN> DELETE CONTROLFILECOPY '/u01/app/oracle/copy/cf_D-TEST_id-2066695660_1tqek8bd';

RMAN Retention Policy

The RMAN retention policy determines how long backups are kept for and how many copies are retained.

The retention policy can be defined in terms of:

REDUNDANCY - number of copies to retain
RECOVERY WINDOW - number of days to retain backups

The retention policy is configured by the RETENTION POLICY parameter. The default value is:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

To specify a recovery window of 1 day use:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

Backup files can be marked as expired or obsolete.

Expired - RMAN has performed a crosscheck and the file cannot be found
Obsolete - based on the retention policy the file is not needed for recovery

The backup retention policy only applies to full or level 0 datafile and control file backups.

The retention policy does not directly affect archived redo logs and incremental level 1 backups. These files become obsolete when no full backups exist that need them.

Datafile backup sets cannot be deleted until all datafile backups within the backup set are obsolete.

Obsolete backups can be identified using the REPORT OBSOLETE command and deleted using the DELETE OBSOLETE command.

The retention policy can also be set to NONE

RMAN> CONFIGURE RETENTION POLICY TO NONE;

To revert to the default value use:

RMAN> CONFIGURE RETENTION POLICY CLEAR;

RMAN Recovery Catalog

RMAN metadata is always stored in the control file of the target database

RMAN metadata can be additionally be stored in a recovery catalog. The recovery catalog is a dedicated schema, usually in a separate database.

The RMAN recovery catalog is created by the RMAN client running the CREATE CATALOG command.

To create a recovery catalog, first configure a separate database e,g. RMAN1

Create a TNS entry for the recovery catalog database. For example:

RMAN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vm23)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RMAN1)
    )
  )

As SYSDBA in the new database create an RMAN user as follows:

CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;

Grant RECOVERY_CATALOG_OWNER privilege to the RMAN user:

GRANT RECOVERY_CATALOG_OWNER TO rman;

Start an RMAN client that connects to both target database and the RMAN catalog database. For example assume the RMAN client is started on the target database server:

rman TARGET / CATALOG rman/rman@rman;

Create a catalog as follows:

CREATE CATALOG;

Note that the CATALOG must be explicitly specified in all subsequent RMAN commands.

To register a database with the recovery catalog use:

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

A full resynchronization is performed when the target database is registered with the recovery catalog.

To resynchonize the catalog with the control file use:

RESYNC CATALOG;

starting full resync of recovery catalog
full resync complete

Note that the database must already be registered with the recovery catalog to perform a resynchronization.

To remove the target database from the recovery catalog use UNREGISTER DATABASE. For example:

RMAN> UNREGISTER DATABASE;

database name is "TEST" and DBID is 2066695660

Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog

To suppress the prompt use:

RMAN> UNREGISTER DATABASE NOPROMPT;

database name is "TEST" and DBID is 2066695660
database unregistered from the recovery catalog

Falhas:

RMAN> LIST FAILURE; 
RMAN> ADVISE FAILURE; 
RMAN> REPAIR FAILURE; 
RMAN> LIST FAILURE;

In Oracle 11.2.0.4 the recovery catalog contains the following object types:

Object Type	# Objects
FUNCTION	2
INDEX	96
PACKAGE	2
PACKAGE BODY	2
SEQUENCE	1
TABLE	44
TRIGGER	3
TYPE	3
TYPE BODY	1
VIEW	98

Of the views there are 55 top level recovery catalog views with the RC prefix:

RC_ARCHIVED_LOG	RC_BACKUP_PIECE_DETAILS	RC_DATAFILE	RC_RESYNC
RC_BACKUP_ARCHIVELOG_DETAILS	RC_BACKUP_REDOLOG	RC_DATAFILE_COPY	RC_RMAN_BACKUP_JOB_DETAILS
RC_BACKUP_ARCHIVELOG_SUMMARY	RC_BACKUP_SET	RC_LOG_HISTORY	RC_RMAN_BACKUP_SUBJOB_DETAILS
RC_BACKUP_CONTROLFILE	RC_BACKUP_SET_DETAILS	RC_OFFLINE_RANGE	RC_RMAN_BACKUP_TYPE
RC_BACKUP_CONTROLFILE_DETAILS	RC_BACKUP_SET_SUMMARY	RC_PROXY_ARCHIVEDLOG	RC_RMAN_CONFIGURATION
RC_BACKUP_CONTROLFILE_SUMMARY	RC_BACKUP_SPFILE	RC_PROXY_ARCHIVELOG_DETAILS	RC_RMAN_OUTPUT
RC_BACKUP_COPY_DETAILS	RC_BACKUP_SPFILE_DETAILS	RC_PROXY_ARCHIVELOG_SUMMARY	RC_RMAN_STATUS
RC_BACKUP_COPY_SUMMARY	RC_BACKUP_SPFILE_SUMMARY	RC_PROXY_CONTROLFILE	RC_SITE
RC_BACKUP_CORRUPTION	RC_CHECKPOINT	RC_PROXY_COPY_DETAILS	RC_STORED_SCRIPT
RC_BACKUP_DATAFILE	RC_CONTROLFILE_COPY	RC_PROXY_COPY_SUMMARY	RC_STORED_SCRIPT_LINE
RC_BACKUP_DATAFILE_DETAILS	RC_COPY_CORRUPTION	RC_PROXY_DATAFILE	RC_TABLESPACE
RC_BACKUP_DATAFILE_SUMMARY	RC_DATABASE	RC_REDO_LOG	RC_TEMPFILE
RC_BACKUP_FILES	RC_DATABASE_BLOCK_CORRUPTION	RC_REDO_THREAD	RC_UNUSABLE_BACKUPFILE_DETAILS
RC_BACKUP_PIECE	RC_DATABASE_INCARNATION	RC_RESTORE_POINT

Mais em: Visit ORACLEdocs

RMAN

Manual de consulta rápida

RMAN Client

RMAN Catalog

RMAN Logging

LOG clause

Tee Command

SPOOL Command

Timestamps

Command Files

Command Line

RMAN Prommpt

Within a RUN command

RMAN Backup Command

Controlfile backups

SPFILE backups

Datafile backups

Tablespace Backups

Compressed Backups

Format clause

Tags

Incremental Backups

Recovery Area

RMAN Catalog Command

RMAN Configure Command

Default Parameters

RMAN CrossCheck Command

RMAN Delete Command

Delete Backupset

NOPROMPT keyword

Image Copies

Expired Backups

Obsolete Backups

RMAN List Command

Database

Archive Logs

Backup sets

Datafile Image Copies

Controlfile Image Copies

Tablespaces

Incarnations

RMAN Recover Command

RMAN Report Command

RMAN Restore Command

Restore Preview

RMAN Set Command

SET DBID

SET NEWNAME

SET UNTIL

RMAN Show Command

SHOW ALL

RMAN Validate Command

RMAN Backup Optimization

RMAN Channels

ALLOCATE CHANNEL

RELEASE CHANNEL

RMAN Controlfile Backups

Autobackup

Snapshot Controlfile

RMAN Format Specifiers

RMAN Image Copies

Using the FORMAT clause

Using tbe CONFIGURE command

Using ALLOCATE CHANNEL command

Controlfile Image Copies

RMAN Retention Policy

RMAN Recovery Catalog