FIRST TAKE THE WHOLE DATABASE BACKUP
USING RMAN THEN STARTS THE SCNARIO
C:\Users\svss3>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19
15:43:27 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
sql>connect /as sysdba
SQL> Connected.
SQL> select name
from v$Datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
9 rows selected
(--BEFORE DROPPING
TABLESPACE RMAN DATAFILEs 9 IS THERE SEE
THE BOLD LETTER)
SQL>drop tablespace rman including contents and datafiles;
Tablespace dropped.
SQL> select name
from v$Datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
8 rows selected.
(AFTER DROP RMAN
DATAFILE IS REMOVED FROM PHYSICALLY)
SQL> EXIT
SQL> Disconnected from Oracle Database 10g Enterprise
Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
(Dropped tablespace
is removed from database physically,so we go back to previous time when drop
command issued by using the alert log file)
(Recover database
using rman)
Shu down the database
,
Startup nomount.
e:\oracle\product\10.2.0\oradata\agile2>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19
15:50:41 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>connect /as
sysdba
SQL> Connected.
SQL>shutdown
immediate
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup nomount
SQL> ORACLE
instance started.
Total System Global Area
293601280 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
SQL> Disconnected from Oracle Database 10g Enterprise
Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
(Using rman restore
the controlfile from previous backup innomount stage)
(Autobackup is on,so
after drop command issued one controlfile backup is stored in autobackup
area,if we use that it will restore
upto dropped stage itself. So restore
the controfile backup before the drop command issued by using alert log file
time.)
e:\oracle\product\10.2.0\oradata\agile2>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr
19 15:52:00 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: agile2 (not mounted)
RMAN>restore controlfile from
'E:\oracle\product\10.2.0\db_1\flash_recovery_area\AGILE2\AUTOBACKUP\2012_04_19\O1_MF_N_781024853_7RZQZYG2_.BKP'
Starting restore at 19-APR-12
using target database control file instead of recovery
catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\CONTROL01.CTL
output
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\CONTROL02.CTL
output
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\CONTROL03.CTL
Finished restore at 19-APR-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> report schema;
Starting implicit crosscheck backup at 19-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
Crosschecked 8 objects
Crosschecked 18 objects
Finished implicit crosscheck backup at 19-APR-12
Starting implicit crosscheck copy at 19-APR-12
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 19-APR-12
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name:
E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\AUTOBACKUP\2012_04_19\O1_MF_N_781024853_7RZQZYG2_.BKP
File Name:
E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\AUTOBACKUP\2012_04_19\O1_MF_N_781026571_7RZSONJD_.BKP
RMAN-06139: WARNING: control file is not current for REPORT
SCHEMA
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- -------
------------------------
1 480 SYSTEM ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
2 30 UNDOTBS1 *** E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
3 250 SYSAUX ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
4 5 USERS ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
5 100 EXAMPLE ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
6 5 TEST ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
7 50 SVSS ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
8 5 SVSS ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
9 0 RMAN ***
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- -----------
--------------------
1 20 TEMP 32767
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEMP01.DBF
RMAN>run{
2>set
until time "to_date('19-apr-2012 15:49:00','dd-mon-yyyy
hh24:mi:ss')";
3>restore database;
4>recover database;
5> }
executing command: SET until clause
Starting restore at 19-APR-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from
backup set
restoring datafile 00001 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
restoring datafile 00006 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
restoring datafile 00008 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
restoring datafile 00009 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
channel ORA_DISK_2: reading from backup piece
E:\RMAN\TODAY\20120419_DATA_598.BKP
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from
backup set
restoring datafile 00002 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
restoring datafile 00003 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
restoring datafile 00004 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
restoring datafile 00005 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
restoring datafile 00007 to
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
channel ORA_DISK_1: reading from backup piece
E:\RMAN\TODAY\20120419_DATA_599.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\RMAN\TODAY\20120419_DATA_599.BKP
tag=TAG20120419T152018
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_2: restored backup piece 1
piece handle=E:\RMAN\TODAY\20120419_DATA_598.BKP
tag=TAG20120419T152018
channel ORA_DISK_2: restore complete, elapsed time: 00:00:22
Finished restore at 19-APR-12
Starting recover at 19-APR-12
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 23 is already on disk as file
E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\ARCHIVELOG\2012_04_19\O1_MF_1_23_7RZQWMW5_.ARC
archive log thread 1 sequence 1 is already on disk as file
E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\ARCHIVELOG\2012_04_19\O1_MF_1_1_7RZQZLO6_.ARC
archive log thread 1 sequence 2 is already on disk as file
E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\ARCHIVELOG\2012_04_19\O1_MF_1_2_7RZQZM7Y_.ARC
archive log thread 1 sequence 3 is already on disk as file
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\REDO03.LOG
archive log
filename=E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\ARCHIVELOG\2012_04_19\O1_MF_1_1_7RZQZLO6_.ARC
thread=1 sequence=1
archive log filename=E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\ARCHIVELOG\2012_04_19\O1_MF_1_2_7RZQZM7Y_.ARC
thread=1 sequence=2
archive log
filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\REDO03.LOG thread=1 sequence=3
media recovery complete, elapsed time: 00:00:04
Finished recover at 19-APR-12
RMAN>
RMAN> exit
Recovery Manager complete.
e:\oracle\product\10.2.0\oradata\agile2>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19
16:03:28 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> Connected.
(Before give
resetlogs we recover the database upto correct time tablespace is available,for
this we open the database read only mode then check it tablespace is there able
to access the table in that tablespace.)
SQL> alter database open read only;
Database altered.
SQL> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
9 rows selected.
(See dropped
tablespace rman is recovered)
SQL>select * from
rman1.recover;
ID
----------
12
(That particular
table belongs to tablespace also accessible)
SQL>shu immediate
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup mount
SQL> ORACLE instance started.
Total System Global Area
293601280 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> alter database open resetlogs
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
9 rows selected.
SQL> select * from rman1.recover
ID
----------
12
SQL> exit
SQL> Disconnected from Oracle Database 10g Enterprise
Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
(After recover the tablespace
compulsory take the full database backup..)
No comments:
Post a Comment