RMAN Backup & Recovery Step by Step Process for Windows O.S. :

Folks, here are all the steps for RMAN and its step by step for simple understanding. I hope there won't be any confusion while going through the following steps.

RMAN Backup & Recovery Step by Step Process for Windows :

The RMAN ambiance consists of the utilities and databases that comedy a role in abutment up your data. At a minimum, the ambiance for RMAN charge accommodate the following:

The ambition database to be backed up

The RMAN client, which interprets advancement and accretion commands, directs server sessions to assassinate those commands, and annal your advancement and accretion action in the ambition database ascendancy file.

Logical Backup : backup utility that performs backups on the logical components of the database. A logical backup consists of backing up the database at the tablespace level or backing up other logical database components such as a table.

Physical Backup: Backing up the database files such as the datafiles, control files, and redo log file

RMAN Repository and Control Files



The RMAN utility uses two methods of storing information about the target databases that are backed up. Oracle calls the collection of metadata about the target databases the RMAN repository There are two methods of storing data in the RMAN repository.

Recovery Catalog

The first method is by accessing an RMAN catalog of information about backups. The second method is by accessing the necessary information about backups in the target database’s control files. The init.ora or spfile.ora parameter CONTROL_FILE_RECORD_KEEP_TIME determines how long information that can be used by RMAN is kept in the control file. The default value for this parameter is 7 days and can be as many as 365 days.

The recovery catalog is designed to be a central storage place for multiple databases’ RMAN information. This centralizes the location of the RMAN information instead of having this information dispersed in each target database’s control file. Here is a list of the components contained in a recovery catalog:

Backup and recovery information that is logged for long-term use from the target databases
RMAN scripts that can be stored and reused
Backup information about datafiles and archive logs
Information about the physical makeup, or schema, of the target database

RMAN (Configuration ) Using Recovery Catalog

Creating user for RMAN, grant required resources/privileges and register database

Note: Here I am not utilizing defalt rman backup location (db_recovery_dest), I will create new directory named "rman" for holding all rman backups.

Database : Oracle 10g, Oracle 11g

1. Login as sys user, create directory and permit to rman user for reading/writing backups on directory.

2. Login to database , create rman user and grant necessary privileges

SQL> set oracle_sid=orcl

SQL> sqlplus / as sysdba
Connected.

SQL> create user rman identified by rman default tablespace users temporary tablespace temp;
User created.

SQL> grant connect, resource, recovery_catalog_owner to rman;
Grant succeeded.

3. Connect to RMAN, create catalog and register database.

C:\Documents and Settings\user> rman
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 3 18:38:55 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target
connected to target database: ORCL (DBID=1232238763)

RMAN> connect catalog rman/rman@orcl
connected to recovery catalog database

RMAN> create catalog tablespace users;
recovery catalog created

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

4. View default RMAN parameters and change if required

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL 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 BACKUP TYPE TO BACKUPSET; # 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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\APP\RAMTECH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default

Note: Here I am changing default parameters - (you can leave it, if want to utilize default )

RMAN> configure device type disk backup type to compressed backupset;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete


RMAN> configure retention policy to recovery window of 30 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure controlfile autobackup format for device type disk to 'D:\app\Ramtech\flash_recovery_area\%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\app\Ramtech\flash_recovery_area\%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Creating Backup Sets :

RMAN> connect target

Full Database Backup (archive logs not included)

RMAN> BACKUP DATABASE;

Note: we can also take backup as following:

Starting backup at 03-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=D:\APP\RAMTECH\ORADATA\ORCL\SDE1.DBF
input datafile file number=00001 name=D:\APP\RAMTECH\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAMTECH\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00004 name=D:\APP\RAMTECH\ORADATA\ORCL\USERS01.DBF
input datafile file number=00003 name=D:\APP\RAMTECH\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 03-JUL-15
channel ORA_DISK_1: finished piece 1 at 03-JUL-15
piece handle=D:\APP\RAMTECH\PRODUCT\11.2.0\DBHOME_1\DATABASE\08QB4TTH_1_1 tag=TAG20150703T182801 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15
Finished backup at 03-JUL-15

Starting Control File and SPFILE Autobackup at 03-JUL-15
piece handle=D:\APP\RAMTECH\FLASH_RECOVERY_AREA\C-1402635108-20150703-01 comment=NONE
Finished Control File and SPFILE Autobackup at 03-JUL-15

RMAN> run
2> { allocate channel c1 type disk;backup database format 'D:\app\Ramtech\flash_recovery_area\%d_%I_%s_%T';
3> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=21 device type=DISK

Starting backup at 03-JUL-15
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=D:\APP\RAMTECH\ORADATA\ORCL\SDE1.DBF
input datafile file number=00001 name=D:\APP\RAMTECH\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAMTECH\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00004 name=D:\APP\RAMTECH\ORADATA\ORCL\USERS01.DBF
input datafile file number=00003 name=D:\APP\RAMTECH\ORADATA\ORCL\UNDOTBS01.DBF
channel c1: starting piece 1 at 03-JUL-15
channel c1: finished piece 1 at 03-JUL-15
piece handle=D:\APP\RAMTECH\FLASH_RECOVERY_AREA\ORCL_1402635108_11_20150703 tag=TAG20150703T183319 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:15
Finished backup at 03-JUL-15

Starting Control File and SPFILE Autobackup at 03-JUL-15
piece handle=D:\APP\RAMTECH\FLASH_RECOVERY_AREA\C-1402635108-20150703-02 comment=NONE
Finished Control File and SPFILE Autobackup at 03-JUL-15
released channel: c1


Database and Archive log Backup :

RMAN> run
{ allocate channel c1 type disk;
backup database format 'db_%u_%d_%s';
backup format 'log_t%t_s%s_p%p' (archivelog all);
}
RMAN>

Note: can user following parameters if required--BACKUP (ARCHIVELOG ALL DELETE INPUT);

Using the TAG command to name the backup monthly backup.

RMAN> run
{ allocate channel c1 type disk;
backup database format 'db_%u_%d_%s' tag monthly_backup;
backup format 'log_t%t_s%s_p%p' (archivelog all);
}

We can create rman script, and execute when required :

CREATE SCRIPT wholedbbackup
COMMENT "Whole database backup and archive log"
{ ALLOCATE CHANNEL C1 TYPE DISK;
ALLOCATE CHANNEL C2 TYPE DISK;
BACKUP DATABASE [backup as compressed backupset database TAB COMPRESED_DBBK_082807];
BACKUP FORMAT 'ARC%S_%R.%T' (ARCHIVELOG ALL); }

RUN { EXECUTE script wholedbbackup; }

Backup the control file:

RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> BACKUP CURRENT CONTROLFILE TO 'D:\app\Ramtech\flash_recovery_area\cntrlfile.copy';

Backup tablespace:

RMAN> BACKUP TABLESPACE system, users, tools;

Backup datafiles :

RMAN> BACKUP AS BACKUPSET DATAFILE 'D:\app\Ramtech\oradata\orcl\users01.dbf','D:\app\Ramtech\oradata\ORCL\undotbs01.dbf';

Backup parameter file:

RMAN> BACKUP SPFILE;

Backup Everything Using Backupset Command:

RMAN> BACKUP BACKUPSET ALL;

Database Recovery from RMAN backups (Examples)

Database recovery depend on events, it may requird full database recover or single database file, redolog , controlfile even spfile (parameter file), here some examples for database recover using RMAN backup.

Restore spfile from backup

RMAN> CONNECT TARGET;
RMAN> SET DBID 1232238763
RMAN> STARTUP FORCE NOMOUNT
RMAN> RESTORE SPFILE FROM AUTOBACKUP;


Restoring and recovering a datafile

RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> SQL 'ALTER DATABASE DATAFILE 7ONLINE';

Restore and recover a tablespace

RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

Restore and recover the whole database

RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

Monitoring RMAN Backups

Connect database and find databas dbid

SQL> connect system/manager@orcl
SQL> select dbid from v$database;

SQL> connect rman_user/rman_user@orcl
SQL> select db_key from rc_database where dbid = 1232238763
SQL> select bs_key, backup_type, completion_time
from rc_database_incarnation a, rc_backup_set b
where a.db_key = b.db_key
and a.db_key = b.db_key
and a.current_incarnation = ‘YES’;

RMAN Reporting :

RMAN> LIST BACKUPSET BY BACKUP SUMMARY;
RMAN> list backupset by file;
RMAN> LIST BACKUPSET BY BACKUP SUMMARY;
RMAN> REPORT OBSOLETE;
RMAN> REPORT SCHEMA;