POINT IN TIME RECOVERY via
RMAN
Scenario:
DBA dropped the
tablespace TEST which is important for application.
Requirement:
We need to restore
the database before the TEST tablespace was dropped.
Solution:
SQL> drop
tablespace test including contents and datafiles;
Tablespace
dropped.
DBA realized the
mistake;
He will refer
alert log for the exact timing when tablespace was dropped.
Alert log
Sun Feb 4 10:59:43 2007
drop tablespace
test including contents and datafiles
Sun Feb 4
10:59:47 2007
Completed: drop
tablespace test including contents and datafiles
SQL> shutdown
abort
ORACLE instance
shut down.
rman target /
catalog rman/cat@risl64
Recovery
Manager: Release 10.2.0.2.0 - Production on Sun Feb 4 11:02:48 2007
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
Connected
to target database (not started)
Connected
to recovery catalog database
RMAN>
run
{
startup
nomount
set
until time "to_date ('04-02-07 10:58:00', 'DD-MM-YY HH24:MI:SS')";
restore
controlfile;
alter
database mount;
restore
database;
recover
database;
alter
database open resetlogs;
}
Oracle
instance started
Total
System Global Area 268435456 bytes
Fixed
Size 2070448 bytes
Variable
Size 104859728 bytes
Database
Buffers 155189248 bytes
Redo
Buffers 6316032 bytes
executing
command: SET until clause
Starting
restore at 04-FEB-07
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=157 devtype=DISK
channel
ORA_DISK_1: starting datafile backupset restore
channel
ORA_DISK_1: restoring control file
channel
ORA_DISK_1: reading from backup piece /u01/ORACLE/ocm/c-1996161442-20070204-04
channel
ORA_DISK_1: restored backup piece 1
piece
handle=/u01/ORACLE/ocm/c-1996161442-20070204-04 tag=TAG20070204T105219
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:03
output
filename=/u01/ORACLE/ocm/control01.ctl
output
filename=/u01/ORACLE/ocm/control02.ctl
output
filename=/u01/ORACLE/ocm/control03.ctl
Finished
restore at 04-FEB-07
database
mounted
released
channel: ORA_DISK_1
Starting
restore at 04-FEB-07
Starting
implicit crosscheck backup at 04-FEB-07
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=157 devtype=DISK
Crosschecked
18 objects
Finished
implicit crosscheck backup at 04-FEB-07
Starting
implicit crosscheck copy at 04-FEB-07
using
channel ORA_DISK_1
Finished
implicit crosscheck copy at 04-FEB-07
searching
for all files in the recovery area
cataloging
files...
no
files cataloged
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting datafile backupset restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring
datafile 00001 to /u01/ORACLE/ocm/system01.dbf
restoring
datafile 00002 to /u01/ORACLE/ocm/undotbs01.dbf
restoring
datafile 00003 to /u01/ORACLE/ocm/sysaux01.dbf
restoring
datafile 00004 to /u01/ORACLE/ocm/users01.dbf
restoring
datafile 00005 to /u01/ORACLE/ocm/test.dbf
channel
ORA_DISK_1: reading from backup piece /u01/ORACLE/ocm/1ni975e6_1_1
channel
ORA_DISK_1: restored backup piece 1
piece
handle=/u01/ORACLE/ocm/1ni975e6_1_1 tag=TAG20070204T105150
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:17
Finished
restore at 04-FEB-07
Starting
recover at 04-FEB-07
using
channel ORA_DISK_1
starting
media recovery
archive
log thread 1 sequence 19 is already on disk as file
/u01/ORACLE/ocm/OCM/archivelog/2007_02_04/o1_mf_1_19_2wc0nztc_.arc
archive
log thread 1 sequence 20 is already on disk as file /u01/ORACLE/ocm/redo02.log
archive
log filename=/u01/ORACLE/ocm/OCM/archivelog/2007_02_04/o1_mf_1_19_2wc0nztc_.arc
thread=1 sequence=19
archive
log filename=/u01/ORACLE/ocm/redo02.log thread=1 sequence=20
media
recovery complete, elapsed time: 00:00:00
Finished
recover at 04-FEB-07
database
opened
new
incarnation of database registered in recovery catalog
starting
full resync of recovery catalog
full
resync complete
RMAN>
SQL> select
name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TEST
6 rows selected.
No comments:
Post a Comment