Use RMAN to restore and recover your database

Scenario 1: Rman : Sandeep Panchal:

You’ve experienced a failure and want to use RMAN to restore and recover your database. You
have a current and good backup in the default location, and all needed control files, archived
redo log files, and online redo log files are available.

How It Works
If you have a good backup of your database, it’s fairly simple to use RMAN to restore and
recover your database. RMAN uses information stored in the control file to determine where
to retrieve backups and which files to restore and recover.


C:\Users\Sandeep>rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Sun Apr 4 13:40:15 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: SANDEEP (DBID=4225970695)

RMAN> list backup
2> ;

using target database control file instead of recovery catalog

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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.29G DISK 00:05:10 02-APR-10
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20100402T205507
Piece Name: D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\BACKUPSET\2010_04_02\O1_MF_NNNDF_TAG20100402T205507_5VDLWZ18_.BKP
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 4159842 02-APR-10 D:\11GSOFTWARE\ORADATA\SANDEEP\SYSTEM01.DBF
2 Full 4159842 02-APR-10 D:\11GSOFTWARE\ORADATA\SANDEEP\SYSAUX01.DBF
3 Full 4159842 02-APR-10 D:\11GSOFTWARE\ORADATA\SANDEEP\UNDOTBS01.DBF
4 Full 4159842 02-APR-10 D:\11GSOFTWARE\ORADATA\SANDEEP\USERS01.DBF
5 Full 4159842 02-APR-10 D:\11GSOFTWARE\ORADATA\SANDEEP\EXAMPLE01.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.36M DISK 00:00:03 02-APR-10
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20100402T210025
Piece Name: D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\AUTOBACKUP\2010_04_02\O1_MF_S_715294825_5VDM6VJ8_.BKP
SPFILE Included: Modification time: 02-APR-10
SPFILE db_unique_name: SANDEEP
Control File Included: Ckp SCN: 4161396 Ckp time: 02-APR-10

RMAN> report schema
2> ;

Report of database schema for database with db_unique_name SANDEEP

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 730 SYSTEM *** D:\11GSOFTWARE\ORADATA\SANDEEP\SYSTEM01.DBF
2 816 SYSAUX *** D:\11GSOFTWARE\ORADATA\SANDEEP\SYSAUX01.DBF
3 90 UNDOTBS1 *** D:\11GSOFTWARE\ORADATA\SANDEEP\UNDOTBS01.DBF
4 5 USERS *** D:\11GSOFTWARE\ORADATA\SANDEEP\USERS01.DBF
5 100 EXAMPLE *** D:\11GSOFTWARE\ORADATA\SANDEEP\EXAMPLE01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 D:\11GSOFTWARE\ORADATA\SANDEEP\TEMP01.DBF

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> exit


Recovery Manager complete.

C:\Users\Sandeep>move D:\11GSOFTWARE\ORADATA\SANDEEP\USERS01.DBF D:\11GSOFTWARE\ORADATA\SANDEEP\USERS01.DBF.bak

1 file(s) moved.

C:\Users\Sandeep>rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Sun Apr 4 13:46:14 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database (not started)

RMAN> startup

Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/04/2010 13:46:41
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\11GSOFTWARE\ORADATA\SANDEEP\USERS01.DBF'

RMAN> restore database;

Starting restore at 04-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\11GSOFTWARE\ORADATA\SANDEEP\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\11GSOFTWARE\ORADATA\SANDEEP\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\11GSOFTWARE\ORADATA\SANDEEP\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\11GSOFTWARE\ORADATA\SANDEEP\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\11GSOFTWARE\ORADATA\SANDEEP\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\BACKUPSET\2010_04_02\O1_MF_NNNDF_TAG20100402T205507_5VDLWZ18_.BKP
channel ORA_DISK_1: piece handle=D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\BACKUPSET\2010_04_02\O1_MF_NNNDF_TAG20100402T205507_5VDLWZ18_.BKP tag=TAG20100402T205507
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:35
Finished restore at 04-APR-10

RMAN> recover database;

Starting recover at 04-APR-10
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 120 is already on disk as file D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_02\O1_MF_1_120_5VDQ0YOR_.ARC
archived log for thread 1 with sequence 121 is already on disk as file D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_02\O1_MF_1_121_5VDTLZOZ_.ARC
archived log for thread 1 with sequence 122 is already on disk as file D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_03\O1_MF_1_122_5VF6FVQ6_.ARC
archived log for thread 1 with sequence 123 is already on disk as file D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_03\O1_MF_1_123_5VFMO6JK_.ARC
archived log for thread 1 with sequence 124 is already on disk as file D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_04\O1_MF_1_124_5VJONNMM_.ARC
archived log for thread 1 with sequence 125 is already on disk as file D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_04\O1_MF_1_125_5VJWQ8RH_.ARC
archived log file name=D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_02\O1_MF_1_120_5VDQ0YOR_.ARC thread=1 sequence=120
archived log file name=D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_02\O1_MF_1_121_5VDTLZOZ_.ARC thread=1 sequence=121
archived log file name=D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_03\O1_MF_1_122_5VF6FVQ6_.ARC thread=1 sequence=122
archived log file name=D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_03\O1_MF_1_123_5VFMO6JK_.ARC thread=1 sequence=123
media recovery complete, elapsed time: 00:02:24
Finished recover at 04-APR-10

RMAN> alter database open;

database opened

RMAN>