RMAN restore database

RMAN full restore/recover
RMAN recovery
RMAN Restore Database


RMAN> connect target /

connected to target database (not started)



start up the database with pfile

RMAN> startup force nomount pfile='/opt/app/oracle/product/11.2/dbs/initorcl.ora';

Oracle instance started

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes
Variable Size 629147640 bytes
Database Buffers 205520896 bytes
Redo Buffers 2396160 bytes
=================

Backup file ....


-rw-r----- 1 oracle oinstall 98304 2011-07-23 15:29 spfile_ORCL_20_20110723.bak
-rw-r----- 1 oracle oinstall 469909504 2011-07-23 15:33 databasefiles_ORCL_0lmi5sv0_21_20110723
-rw-r----- 1 oracle oinstall 1130496 2011-07-23 15:33 controlfile_ORCL_0mmi5t5q_22_20110723



restore controlfile from '/home/oracle/RmanBackup/controlfile_ORCL_0mmi5t5q_22_20110723';

----


RMAN> restore controlfile from '/home/oracle/RmanBackup/controlfile_ORCL_0mmi5t5q_22_20110723';

Starting restore at 23-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/oracle/oradata/orcl/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 23-JUL-11



RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


RMAN> list backup;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13 Full 80.00K DISK 00:00:00 23-JUL-11
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: SPFILE
Piece Name: /home/oracle/RmanBackup/spfile_ORCL_20_20110723.bak
SPFILE Included: Modification time: 23-JUL-11
SPFILE db_unique_name: ORCL

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 448.13M DISK 00:03:30 23-JUL-11
BP Key: 16 Status: AVAILABLE Compressed: YES Tag: TAG20110723T152936
Piece Name: /home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/system01.dbf
2 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/sysaux01.dbf
3 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/undotbs01.dbf
4 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/users01.dbf
5 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/example01.dbf
6 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/anujtest.dbf
7 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/tsapexf01.dbf
8 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/tsapexu01.dbf
9 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/test.dbf
10 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/rman.dbf

RMAN> restore database;

Starting restore at 23-JUL-11
Starting implicit crosscheck backup at 23-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 23-JUL-11

Starting implicit crosscheck copy at 23-JUL-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 23-JUL-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_23/o1_mf_s_757265596_72otfxny_.bkp

using channel ORA_DISK_1

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 /opt/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/orcl/anujtest.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/app/oracle/oradata/orcl/tsapexf01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/orcl/tsapexu01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /opt/app/oracle/oradata/orcl/test.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/app/oracle/oradata/orcl/rman.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723 tag=TAG20110723T152936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:55
Finished restore at 23-JUL-11



RMAN> recover database;

Starting recover at 23-JUL-11
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=509
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2011 16:28:17
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 509 and starting SCN of 12374114


so we have to restore the database upto 12374114 -1 = 12374113




RMAN>

run {
set until scn 12374113;
recover database;
}



RMAN> run {
set until scn 12374113;
recover database;
}
2> 3> 4>
executing command: SET until clause

Starting recover at 23-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2011 16:30:09
RMAN-06556: datafile 1 must be restored from backup older than SCN 12374113


then try again

12374113 -1 =12374112



run {
set until scn 12374112;
recover database;
}


RMAN> run {
set until scn 12374112;
recover database;
}2> 3> 4>

executing command: SET until clause

Starting recover at 23-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2011 16:31:20
RMAN-06556: datafile 1 must be restored from backup older than SCN 12374112



no luck ... then I did resetlogs


RMAN> alter database open resetlogs;

database opened


RMAN> exit





sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 23 16:35:57 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN



SQL> select group#, first_change#, status, archived from v$log;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
4 12374115 CURRENT NO
5 0 UNUSED YES
6 0 UNUSED YES


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