oracle rman 11g 恢复,Oracle 11g RMAN恢复-用户误删除表空间

--0.场景模拟

--0.1查看当前的备份，确定有控制文件和初始化参数文件的备份

RMAN>list backup;

usingtarget database controlfileinsteadofrecoverycatalog

ListofBackupSets

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

BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime

------- ---- -- ---------- ----------- ------------ ---------------

91Full1.29GDISK00:01:3614-AUG-13

BPKey:91Status:AVAILABLECompressed:NOTag:TAG20130814T193012

Piece Name:/pooldisk02/backup03/3joha2a5_1_1

ListofDatafilesinbackupset91

FileLVTypeCkp SCNCkpTimeName

---- -- ---- ---------- --------- ----

1Full278591914-AUG-13/oradata/system01.dbf

2Full278591914-AUG-13/oradata/sysaux01.dbf

3Full278591914-AUG-13/oradata/undotbs01.dbf

4Full278591914-AUG-13/oradata/users01.dbf

5Full278591914-AUG-13/oradata/example01.dbf

6Full278591914-AUG-13/oradata/newts01.dbf

7Full278591914-AUG-13/oradata/fbtbs01.dbf

8Full278591914-AUG-13/oradata/users02.dbf

9Full278591914-AUG-13/oradata/mynewts01.dbf

BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime

------- ---- -- ---------- ----------- ------------ ---------------

92Full9.95MDISK00:00:0114-AUG-13

BPKey:92Status:AVAILABLECompressed:NOTag:TAG20130814T193158

Piece Name:/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462318_90r8cyy7_.bkp

SPFILE Included:Modificationtime:14-AUG-13

SPFILE db_unique_name:TESTDB11

ControlFileIncluded:Ckp SCN:2785972Ckptime:14-AUG-13

BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime

------- ---- -- ---------- ----------- ------------ ---------------

93Full80.00KDISK00:00:0014-AUG-13

BPKey:93Status:AVAILABLECompressed:NOTag:TAG20130814T193602

Piece Name:/pooldisk02/backup03/3loha2l2_1_1

SPFILE Included:Modificationtime:14-AUG-13

SPFILE db_unique_name:TESTDB11

BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime

------- ---- -- ---------- ----------- ------------ ---------------

94Full9.95MDISK00:00:0014-AUG-13

BPKey:94Status:AVAILABLECompressed:NOTag:TAG20130814T193603

Piece Name:/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkp

SPFILE Included:Modificationtime:14-AUG-13

SPFILE db_unique_name:TESTDB11

ControlFileIncluded:Ckp SCN:2786150Ckptime:14-AUG-13

--0.2查看当前的表空间，确定要删除mynewts表空间(删除之前确定它有备份)

sys@TESTDB11>selecttablespace_namefromdba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

NEWTS

FBTBS

MYNEWTS

9rowsselected.

--0.3切3组日志

--0.4确定当前的SCN，省去日志挖掘的步骤

sys@TESTDB11>selectcurrent_scnfromv$database;

CURRENT_SCN

-----------

2808329

--0.5误删除表空间

sys@TESTDB11>droptablespace

mynewts including contentsanddatafiles;

Tablespace dropped.

--0.6切3组日志

--0.7确定当前的控制文件中已经没有关于mynewts的信息了，所以需要从备份中还原控制文件

sys@TESTDB11>selecttablespace_namefromdba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

NEWTS

FBTBS

8rowsselected.

--1从备份中还原控制文件(到底还原哪个控制文件，可以确保该控制文件中包含被删除的表空间的信息呢)

RMAN>run{

2>startup nomount force;--1.1重新启动到NOMOUNT状态

3>restore controlfilefromautobackup;--1.2还原备份的控制文件

4>}

Oracle instance started

Total SystemGlobalArea855982080bytes

FixedSize2230792bytes

VariableSize742393336bytes

Database Buffers109051904bytes

Redo Buffers2306048bytes

Starting restoreat15-AUG-13

allocated channel:ORA_DISK_1

channel ORA_DISK_1:SID=18devicetype=DISK

recovery area destination:/u01/app/oracle/fast_recovery_area

database name(ordatabaseuniquename)usedforsearch:TESTDB11

channel ORA_DISK_1:AUTOBACKUP/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkpfoundinthe recovery area

channel ORA_DISK_1:lookingforAUTOBACKUPonday:20130815

channel ORA_DISK_1:lookingforAUTOBACKUPonday:20130814

channel ORA_DISK_1:restoring controlfilefromAUTOBACKUP/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkp

channel ORA_DISK_1:controlfilerestorefromAUTOBACKUP complete

outputfilename=/u01/app/oracle/oradata/TestDB11/control01.ctl

outputfilename=/u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl

Finished restoreat15-AUG-13

--2.加载还原的控制文件,并确定该控制文件中包含删除的表空间的信息

RMAN>mount database;

database mounted

released channel:ORA_DISK_1

RMAN>reportschema;

Starting implicit crosscheck backupat15-AUG-13

allocated channel:ORA_DISK_1

channel ORA_DISK_1:SID=18devicetype=DISK

Crosschecked3objects

Finished implicit crosscheck backupat15-AUG-13

Starting implicit crosscheck copyat15-AUG-13

usingchannel ORA_DISK_1

Finished implicit crosscheck copyat15-AUG-13

searchingforallfilesinthe recovery area

cataloging files...

cataloging done

ListofCataloged Files

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

FileName:/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkp

FileName:/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823460342_90r6g6dq_.bkp

RMAN-06139:WARNING:controlfileisnotcurrentforREPORTSCHEMA

Reportofdatabaseschemafordatabasewithdb_unique_name TESTDB11

ListofPermanent Datafiles

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

FileSize(MB)TablespaceRB segs

Datafile Name

---- -------- -------------------- ------- ------------------------

1750SYSTEM***/oradata/system01.dbf

2740SYSAUX***/oradata/sysaux01.dbf

3290UNDOTBS1***/oradata/undotbs01.dbf

46USERS***/oradata/users01.dbf

5345EXAMPLE***/oradata/example01.dbf

620NEWTS***/oradata/newts01.dbf

7200FBTBS***/oradata/fbtbs01.dbf

850USERS***/oradata/users02.dbf

90MYNEWTS***/oradata/mynewts01.dbf

ListofTemporaryFiles

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

FileSize(MB)TablespaceMaxsize(MB)Tempfile Name

---- -------- -------------------- ----------- --------------------

1500TEMP500/oradata/temp01.dbf

--3.还原和恢复

RMAN>run{

2>setuntil scn2808329;--3.1以SCN设置恢复的终点

3>restore database;--3.2还原

4>recover database;--3.3恢复

5>}

executing command:SETuntil clause

Starting restoreat15-AUG-13

usingchannel ORA_DISK_1

channel ORA_DISK_1:starting datafile backupsetrestore

channel ORA_DISK_1:specifying datafile(s)torestorefrombackupset

channel ORA_DISK_1:restoring datafile00001to/oradata/system01.dbf

channel ORA_DISK_1:restoring datafile00002to/oradata/sysaux01.dbf

channel ORA_DISK_1:restoring datafile00003to/oradata/undotbs01.dbf

channel ORA_DISK_1:restoring datafile00004to/oradata/users01.dbf

channel ORA_DISK_1:restoring datafile00005to/oradata/example01.dbf

channel ORA_DISK_1:restoring datafile00006to/oradata/newts01.dbf

channel ORA_DISK_1:restoring datafile00007to/oradata/fbtbs01.dbf

channel ORA_DISK_1:restoring datafile00008to/oradata/users02.dbf

channel ORA_DISK_1:restoring datafile00009to/oradata/mynewts01.dbf

channel ORA_DISK_1:readingfrombackup piece/pooldisk02/backup03/3joha2a5_1_1

channel ORA_DISK_1:piece handle=/pooldisk02/backup03/3joha2a5_1_1 tag=TAG20130814T193012

channel ORA_DISK_1:restored backup piece1

channel ORA_DISK_1:restore complete,elapsedtime:00:01:35

Finished restoreat15-AUG-13

Starting recoverat15-AUG-13

usingchannel ORA_DISK_1

starting media recovery

archivedlogfilename=/archive2/1_1_823462113.dbf thread=1sequence=1

archivedlogfilename=/archive2/1_2_823462113.dbf thread=1sequence=2

archivedlogfilename=/archive2/1_3_823462113.dbf thread=1sequence=3

archivedlogfilename=/archive2/1_4_823462113.dbf thread=1sequence=4

archivedlogfilename=/archive2/1_5_823462113.dbf thread=1sequence=5

media recovery complete,elapsedtime:00:00:03

Finished recoverat15-AUG-13

--4.开库

MAN>sql'alter database open resetlogs';

sqlstatement:alterdatabaseopenresetlogs

--5.验证表空间和数据文件都回来了

sys@TESTDB11>selecttablespace_name,file_namefromdba_data_files;

TABLESPACE_NAMEFILE_NAME

------------------------------

--------------------------------------------------

EXAMPLE/oradata/example01.dbf

USERS/oradata/users01.dbf

UNDOTBS1/oradata/undotbs01.dbf

SYSAUX/oradata/sysaux01.dbf

SYSTEM/oradata/system01.dbf

NEWTS/oradata/newts01.dbf

FBTBS/oradata/fbtbs01.dbf

MYNEWTS/oradata/mynewts01.dbf

USERS/oradata/users02.dbf

9rowsselected.

--查看数据文件得以还原

sys@TESTDB11>!ls/oradata/mynewts01.dbf

/oradata/mynewts01.dbf

--6.删除原有备份，创建新的备份

RMAN>deletebackup;

RMAN>backup database;

--补充知识：解释如果确定还原哪个控制文件

--7.1备份当前的控制文件

RMAN>backupcurrentcontrolfile;

Starting backupat15-AUG-13

usingchannel ORA_DISK_1

channel ORA_DISK_1:starting full datafile backupset

channel ORA_DISK_1:specifying datafile(s)inbackupset

includingcurrentcontrolfileinbackupset

channel ORA_DISK_1:starting piece1at15-AUG-13

channel ORA_DISK_1:finished piece1at15-AUG-13

piece handle=/pooldisk02/backup03/3rohb929_1_1 tag=TAG20130815T063137comment=NONE

channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01

Finished backupat15-AUG-13

Starting ControlFileandSPFILE Autobackupat15-AUG-13

piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823501899_90sh0vk9_.bkpcomment=NONE

Finished ControlFileandSPFILE Autobackupat15-AUG-13

--7.2确定系统当前scn,假定一会要恢复到这个SCN

sys@TESTDB11>selectcurrent_scnfromv$database;

CURRENT_SCN

-----------

2809203

--7.3系统中有多个控制文件的备份，找一个SCN比要恢复到的SCN小，而且最近的一个SCN,所以为98号备份集中的控制文件备份

--记录备份片的名称：u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823501899_90sh0vk9_.bkp

RMAN>backupcurrentcontrolfile;

Starting backupat15-AUG-13

usingchannel ORA_DISK_1

channel ORA_DISK_1:starting full datafile backupset

channel ORA_DISK_1:specifying datafile(s)inbackupset

includingcurrentcontrolfileinbackupset

channel ORA_DISK_1:starting piece1at15-AUG-13

channel ORA_DISK_1:finished piece1at15-AUG-13

piece handle=/pooldisk02/backup03/3tohb9bq_1_1 tag=TAG20130815T063642comment=NONE

channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01

Finished backupat15-AUG-13

Starting ControlFileandSPFILE Autobackupat15-AUG-13

piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823502204_90shbf2g_.bkpcomment=NONE

Finished ControlFileandSPFILE Autobackupat15-AUG-13

--7.4再备份当前的控制文件

RMAN>list backupofcontrolfile;

ListofBackupSets

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

BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime

------- ---- -- ---------- ----------- ------------ ---------------

96Full9.95MDISK00:00:0015-AUG-13

BPKey:96Status:AVAILABLECompressed:NOTag:TAG20130815T062836

Piece Name:/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823501716_90sgv4nz_.bkp

ControlFileIncluded:Ckp SCN:2808932Ckptime:15-AUG-13

BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime

------- ---- -- ---------- ----------- ------------ ---------------

97Full9.92MDISK00:00:0115-AUG-13

BPKey:97Status:AVAILABLECompressed:NOTag:TAG20130815T063137

Piece Name:/pooldisk02/backup03/3rohb929_1_1

ControlFileIncluded:Ckp SCN:2809052Ckptime:15-AUG-13

BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime

------- ---- -- ---------- ----------- ------------ ---------------

98Full9.95MDISK00:00:0015-AUG-13

BPKey:98Status:AVAILABLECompressed:NOTag:TAG20130815T063139

Piece Name:/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823501899_90sh0vk9_.bkp

ControlFileIncluded:Ckp SCN:2809059Ckptime:15-AUG-13

BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime

------- ---- -- ---------- ----------- ------------ ---------------

99Full9.92MDISK00:00:0115-AUG-13

BPKey:99Status:AVAILABLECompressed:NOTag:TAG20130815T063642

Piece Name:/pooldisk02/backup03/3tohb9bq_1_1

ControlFileIncluded:Ckp SCN:2809603Ckptime:15-AUG-13

BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime

------- ---- -- ---------- ----------- ------------ ---------------

100Full9.95MDISK00:00:0115-AUG-13

BPKey:100Status:AVAILABLECompressed:NOTag:TAG20130815T063644

Piece Name:/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823502204_90shbf2g_.bkp

ControlFileIncluded:Ckp SCN:2809609Ckptime:15-AUG-13

--7.5重启到nomount状态，用指定的控制文件备份还原控制文件(不要做)

RMAN>run{

2>startup nomount force;

3>restore controlfilefrom'/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823501899_90sh0vk9_.bkp';

4>}