RMAN Backup , List ,Configure and Report Backups

RMAN Backup , List ,Configure and Report Backups

A. Backup a database

RMAN> BACKUP DATABASE;

backup current controlfile
RMAN> BACKUP CURRENT CONTROLFILE;

--backup datafiles
RMAN> BACKUP AS BACKUPSET DATAFILE
'<datafile name>', '<datafile name>';

--backup all datafiles in the database: (bit-for-bit copies, created on disk)
RMAN> BACKUP AS COPY DATABASE;

Backup archive logs:
RMAN> BACKUP ARCHIVELOG TIME BETWEEN 'SYSDATE-10' AND 'SYSDATE-2';

Backup tablespace:
RMAN> BACKUP TABLESPACE system, users, tools;

Backup controlfile:
RMAN> BACKUP CURRENT CONTROLFILE TO DESTINATION '<directory where backup is to be taken>';

Backup parameter file:
RMAN> BACKUP SPFILE;


Backup validation confirms that a backup could be run, by confirming that all database filesexist and are free of physical and logical corruption, this does not generate any output.
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

backup all archive logs created more than 1 and less than 5 days ago.
BACKUP ARCHIVELOG FROM TIME 'SYSDATE-5' UNTIL TIME 'SYSDATE-1';

backup all archive redo logs created between sequence numbers <startseq> and <tillseq>
BACKUP ARCHIVELOG FROM SEQUENCE <startseq> UNTIL SEQUENCE <tillseq>;

backup all archive redo logs created between SCN <startscn> and <tillscn>
BACKUP ARCHIVELOG FROM SCN <startscn> UNTIL SCN <tillscn>;


Example 1 : To Backup a database with 2 channels (for parallelism) to Tape with 2 files in one backupset at a particular location use :
(for Disk use disk instead of sbt_tape)
run {
allocate channel t1 type SBT_TAPE;
allocate channel t2 type SBT_TAPE;
backup filesperset 2 format ‘/u01/backuploc/df_%t_%s_%p’ database;
}

Example 2 : To Backup a archives with 2 channels (for parallelism) to Tape , and remove the archive after backup use :

run {
allocate channel t1 type ‘SBT_TAPE’;
allocate channel t2 type ‘SBT_TAPE’;
backup format ‘al_%t_%s_%p’ archivelog all delete input;
}




B. LISTING BACKUPS

Listing Incarnations

A new incarnation of the database is created when you perform the open resetlogs operation. To view the list of database incarnations,

RMAN> List incarnation;

List Summary of Backups

The summary of backups include backupset key, the status, device type, completion time etc,

RMAN> List Backup Summary;
RMAN> List expired Backup of archivelog all summary;
RMAN> List Backup of tablespace Users summary;

List Backups of various files

It provides the summary of the backups available for each datafile, controlfile, archivelog file and spfile.

RMAN> List Backup By File;

Detailed Report

If you want the detailed report on the backups, then issue the following command.

RMAN> List Backup;

It lists the all available information about the backups.

Expired Backups

The list backup shows both available and expired backups. To view only the expired backups,

RMAN> List expired Backup;
RMAN> List expired Backup summary;
RMAN> List expired Backup of Archivelog all;
RMAN> List expired Backup of datafile 1;

Listing Tablespace and Datafile Backups

RMAN> List Backup of Tablespace Users;
RMAN> List Backup of Datafile 1;

Listing Archivelog Backups

RMAN> List Archivelog all;
RMAN> List Archivelog all backedup 2 times to device type sbt;

Listing Controlfile and Spfile Backups

RMAN> List Backup of Controlfile;
RMAN> List Backup of Spfile;

The above list commands displayed information about the backusets.
If you have performed Image copy backups then you must use the list copy command as shown below

RMAN> List Copy;
RMAN> List Copy of database;
RMAN> List Copy of tablespace test;
RMAN> List Copy of archivelog all;
RMAN> List Copy of archivelog from sequence 121 until sequence 160;
RMAN> List Copy of Controlfile;
RMAN> List Copy of Spfile;


C. CONFIGURE BACKUPS

Show all configurable settings:
RMAN> SHOW ALL;

Write disk backups to the /tmp directory (%U will be replaced with unique filenames)
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/tmp/%U';

Backup using a flash recovery area rather than disk
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT CLEAR;

Configure RMAN to back up the control file after each backup
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

By default, RMAN automatically names control file backups and stores them in the flash recovery area.
To configure RMAN to write control file backups to the /cfilebackups directory( %F will generate a unique filename) :
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/cfilebackups/cf%F';

Ensure that RMAN retains all backups needed to recover the database to any point in time in the last 7 days:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Retain three backups of each datafile:
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

Delete backups no longer required by the retention policy:
RMAN> DELETE OBSOLETE

To override the configured retention policy for individual backups - use BACKUP.. KEEP (or CHANGE.. KEEP)

Configure backups to run in parallel by assigning two sbt channels:
RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

Reset any CONFIGURE setting to its default by running the command with the CLEAR option
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
RMAN> CONFIGURE RETENTION POLICY CLEAR;


D. REPORT BACKUPS

To see what datafiles/tablespaces haven't been backed up in the last 3 days use:
RMAN> report need backup days=3;


To see for which datafiles/tablespaces you haven't got at least 2 copies of backups (Retention policy to REDUNDANCY 2).
RMAN> report need backup redundancy 2;


To see objects which have been created with options like UNRECOVERABLE (for reasons of speed) and have not been backed up yet use:
RMAN> report unrecoverable;

Oracle 11g create spfile from memory.

In pre Oracle 11g releases we had the feature of conversion from SPFILE to PFILE and vice versa. But Oracle 11g has gone one step further, SPFILE can now be created directly from memory.

SQL> create spfile=’/u01/app/spfileorcl.bak’ from memory;

This command will capture all the settings from the memory and dump them to an SPFILE. This is a very useful utility to take backup of your SPFILE before any changes are made using ‘alter system’ command. In order to check and read the new created file the following command can be used : -


SQL>!strings /u01/app/spfileorcl.bak

Loss of all controlfiles of the Database (User Managed Recovery)

Error in identifying controlfile. Recover it 2 ways....

SQL> startup nomount pfile=/u02/home/dbtest/initdbtest.ora ;

( We must restore ONLY the backup controlfile and we would do recovery from there and finally open the database with RESETLOGS )
$cp /u02/home/backups/weekly/dbtest/hot/control_bkp.ctl /u02/home/dbtest/control01.ctl
$cp /u02/home/backups/weekly/dbtest/hot/control_bkp.ctl /u02/home/dbtest/control02.ctl

SQL> alter database mount ;

Database altered.

SQL> recover database using backup controlfile until cancel ;
Media recovery complete.

SQL> alter database open resetlogs ;

Database altered.

NOTE 1 : Even if the database is open with RESETLOGS, this is complete recovery of the database; since no data has been lost in the recovery )
NOTE 2 : In both HOT / COLD backups you need to restore the backup controlfile only to do the complete recovery. In both cases RESETLOTS must be used to open up the database.

================================================== ===

2) SQL> startup nomount pfile=/u02/home/dbtest/initdbtest.ora ;
ORACLE instance started.

SQL> CREATE CONTROLFILE REUSE DATABASE "DBTEST" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 40
MAXINSTANCES 1
MAXLOGHISTORY 452
LOGFILE
GROUP 1 (
'/u02/home/dbtest/redo/log1a.dbf',
'/u02/home/dbtest/redo/log1b.dbf'
) SIZE 1M,
GROUP 2 (
'/u02/home/dbtest/redo/log2a.dbf',
'/u02/home/dbtest/redo/log2b.dbf'
) SIZE 1M,
GROUP 3 (
'/u02/home/dbtest/redo/log3a.dbf',
'/u02/home/dbtest/redo/log3b.dbf'
) SIZE 1M
DATAFILE
'/u02/home/dbtest/system.dbf',
'/u02/home/dbtest/users01.dbf',
'/u02/home/dbtest/users02.dbf',
'/u02/home/dbtest/undo02.dbf'
CHARACTER SET WE8ISO8859P1 ;

Control file created.

SQL> select open_mode from v$database ;

OPEN_MODE
----------
MOUNTED

SQL> recover database ;
Media recovery complete.

SQL> alter database open ;

Database altered.

( Only Commited data would be there. All Uncommited data would be rolled back. )

NOTE : There was NO need to open the database with RESETLOGS now.