RMAN BACKUP STATUS
set linesize 500 pagesize 2000
col Hours format 9999.99
col input_type for a20
col STATUS format a10
col RMAN_Bkup_start_time for a20
col RMAN_Bkup_end_time for a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm-dd-yyyy hh24:mi:ss’) as RMAN_Bkup_start_time,
to_char(END_TIME,’mm-dd-yyyy hh24:mi:ss’) as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
Database backups
RMAN> BACKUP DATABASE;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
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;
RMAN> BACKUP ARCHIVELOG from time ‘sysdate-2’;
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;
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;
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;
To backup a tablespace use the BACKUP TABLESPACE command. For example:
RMAN> BACKUP TABLESPACE USERS;
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’;
Ref Link:http://www.juliandyke.com/Research/RMAN/ListCommand.php
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 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;
To list all existing controfile backups use:
RMAN> LIST BACKUP OF CONTROLFILE;
To list all existing SPFILE backups use:
RMAN> LIST BACKUP OF SPFILE;
To list all archive logs use:
RMAN> LIST ARCHIVELOG ALL;
To list the contents of an individual backup set use:
RMAN> LIST BACKUPSET <key>;
For example:
RMAN> LIST BACKUPSET 44;
When you issue an RMAN backup command, RMAN creates backup sets, which are logical groupings of physical files. The physical files that RMAN creates on your backup media are called backup pieces. When working with RMAN, you need to understand that the following terms have specific meanings:
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.
Backup set
A logical grouping of backup files — the backup pieces — that are created when you issue an RMAN backup command. A backup set is RMAN’s name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.
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. If you need to restore a file from an RMAN backup, you must use RMAN to do it. There’s no way for you …
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;
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;
To delete a backup set specify the set number e.g. 23:
RMAN> DELETE BACKUPSET 23;
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;
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: