Oracle 11g RMAN恢復-使用者誤刪除表空間

--0. 場景模擬

--0.1 檢視當前的備份，確定有控制檔案和初始化引數檔案的備份

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

91      Full    1.29G      DISK        00:01:36     14-AUG-13     

        BP Key: 91   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T193012

        Piece Name: /pooldisk02/backup03/3joha2a5_1_1

  List of Datafiles in backup set 91

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 2785919    14-AUG-13 /oradata/system01.dbf

  2       Full 2785919    14-AUG-13 /oradata/sysaux01.dbf

  3       Full 2785919    14-AUG-13 /oradata/undotbs01.dbf

  4       Full 2785919    14-AUG-13 /oradata/users01.dbf

  5       Full 2785919    14-AUG-13 /oradata/example01.dbf

  6       Full 2785919    14-AUG-13 /oradata/newts01.dbf

  7       Full 2785919    14-AUG-13 /oradata/fbtbs01.dbf

  8       Full 2785919    14-AUG-13 /oradata/users02.dbf

  9       Full 2785919    14-AUG-13 /oradata/mynewts01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

92      Full    9.95M      DISK        00:00:01     14-AUG-13     

        BP Key: 92   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T193158

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

  SPFILE Included: Modification time: 14-AUG-13

  SPFILE db_unique_name: TESTDB11

  Control File Included: Ckp SCN: 2785972      Ckp time: 14-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

93      Full    80.00K     DISK        00:00:00     14-AUG-13     

        BP Key: 93   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T193602

        Piece Name: /pooldisk02/backup03/3loha2l2_1_1

  SPFILE Included: Modification time: 14-AUG-13

  SPFILE db_unique_name: TESTDB11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

94      Full    9.95M      DISK        00:00:00     14-AUG-13     

        BP Key: 94   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T193603

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

  SPFILE Included: Modification time: 14-AUG-13

  SPFILE db_unique_name: TESTDB11

  Control File Included: Ckp SCN: 2786150      Ckp time: 14-AUG-13

--0.2 檢視當前的表空間，確定要刪除mynewts表空間（刪除之前確定它有備份）

sys@TESTDB11>select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

NEWTS

FBTBS

MYNEWTS

9 rows selected.

--0.3 切3組日誌

--0.4 確定當前的SCN，省去日誌挖掘的步驟

sys@TESTDB11>select current_scn from v$database;

CURRENT_SCN

-----------

    2808329

--0.5 誤刪除表空間

sys@TESTDB11>drop tablespace mynewts including contents and datafiles;

Tablespace dropped.

--0.6 切3組日誌

--0.7 確定當前的控制檔案中已經沒有關於mynewts的資訊了，所以需要從備份中還原控制檔案

sys@TESTDB11>select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

NEWTS

FBTBS

8 rows selected.

--1 從備份中還原控制檔案（到底還原哪個控制檔案，可以確保該控制檔案中包含被刪除的表空間的資訊呢)

RMAN> run {

2> startup nomount force;                        --1.1 重新啟動到NOMOUNT狀態

3> restore controlfile from autobackup;       --1.2 還原備份的控制檔案

4> }

Oracle instance started

Total System Global Area     855982080 bytes

Fixed Size                     2230792 bytes

Variable Size                742393336 bytes

Database Buffers             109051904 bytes

Redo Buffers                   2306048 bytes

Starting restore at 15-AUG-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

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

database name (or database unique name) used for search: TESTDB11

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkp found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130815

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130814

channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkp

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=/u01/app/oracle/oradata/TestDB11/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl

Finished restore at 15-AUG-13

--2. 載入還原的控制檔案,並確定該控制檔案中包含刪除的表空間的資訊

RMAN> mount database;

database mounted

released channel: ORA_DISK_1

RMAN> report schema;

Starting implicit crosscheck backup at 15-AUG-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 15-AUG-13

Starting implicit crosscheck copy at 15-AUG-13

using channel ORA_DISK_1

Finished implicit crosscheck copy at 15-AUG-13

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

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

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

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name TESTDB11

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

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

1    750      SYSTEM               ***     /oradata/system01.dbf

2    740      SYSAUX               ***     /oradata/sysaux01.dbf

3    290      UNDOTBS1             ***     /oradata/undotbs01.dbf

4    6        USERS                ***     /oradata/users01.dbf

5    345      EXAMPLE              ***     /oradata/example01.dbf

6    20       NEWTS                ***     /oradata/newts01.dbf

7    200      FBTBS                ***     /oradata/fbtbs01.dbf

8    50       USERS                ***     /oradata/users02.dbf

9    0        MYNEWTS              ***     /oradata/mynewts01.dbf

List of Temporary Files

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

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    500      TEMP                 500         /oradata/temp01.dbf

--3. 還原和恢復

RMAN> run {

2> set until scn 2808329;                    --3.1 以SCN設定恢復的終點

3> restore database;                          --3.2 還原

4> recover database;                          --3.3 恢復

5> }

executing command: SET until clause

Starting restore at 15-AUG-13

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 /oradata/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /oradata/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /oradata/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /oradata/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /oradata/example01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /oradata/newts01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /oradata/fbtbs01.dbf

channel ORA_DISK_1: restoring datafile 00008 to /oradata/users02.dbf

channel ORA_DISK_1: restoring datafile 00009 to /oradata/mynewts01.dbf

channel ORA_DISK_1: reading from backup 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 piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:35

Finished restore at 15-AUG-13

Starting recover at 15-AUG-13

using channel ORA_DISK_1

starting media recovery

archived log file name=/archive2/1_1_823462113.dbf thread=1 sequence=1

archived log file name=/archive2/1_2_823462113.dbf thread=1 sequence=2

archived log file name=/archive2/1_3_823462113.dbf thread=1 sequence=3

archived log file name=/archive2/1_4_823462113.dbf thread=1 sequence=4

archived log file name=/archive2/1_5_823462113.dbf thread=1 sequence=5

media recovery complete, elapsed time: 00:00:03

Finished recover at 15-AUG-13

--4. 開庫

MAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

--5. 驗證表空間和資料檔案都回來了

sys@TESTDB11>select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_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

9 rows selected.

--檢視資料檔案得以還原

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

/oradata/mynewts01.dbf

--6. 刪除原有備份，建立新的備份

RMAN> delete backup;

RMAN> backup database;

--補充知識：解釋如果確定還原哪個控制檔案

--7.1 備份當前的控制檔案

RMAN> backup current controlfile;

Starting backup at 15-AUG-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 15-AUG-13

channel ORA_DISK_1: finished piece 1 at 15-AUG-13

piece handle=/pooldisk02/backup03/3rohb929_1_1 tag=TAG20130815T063137 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 15-AUG-13

Starting Control File and SPFILE Autobackup at 15-AUG-13

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

Finished Control File and SPFILE Autobackup at 15-AUG-13

--7.2 確定系統當前scn,假定一會要恢復到這個SCN

sys@TESTDB11>select current_scn from v$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> backup current controlfile;

Starting backup at 15-AUG-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 15-AUG-13

channel ORA_DISK_1: finished piece 1 at 15-AUG-13

piece handle=/pooldisk02/backup03/3tohb9bq_1_1 tag=TAG20130815T063642 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 15-AUG-13

Starting Control File and SPFILE Autobackup at 15-AUG-13

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

Finished Control File and SPFILE Autobackup at 15-AUG-13

--7.4 再備份當前的控制檔案

RMAN> list backup of controlfile;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

96      Full    9.95M      DISK        00:00:00     15-AUG-13     

        BP Key: 96   Status: AVAILABLE  Compressed: NO  Tag: TAG20130815T062836

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

  Control File Included: Ckp SCN: 2808932      Ckp time: 15-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

97      Full    9.92M      DISK        00:00:01     15-AUG-13     

        BP Key: 97   Status: AVAILABLE  Compressed: NO  Tag: TAG20130815T063137

        Piece Name: /pooldisk02/backup03/3rohb929_1_1

  Control File Included: Ckp SCN: 2809052      Ckp time: 15-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

98      Full    9.95M      DISK        00:00:00     15-AUG-13     

        BP Key: 98   Status: AVAILABLE  Compressed: NO  Tag: TAG20130815T063139

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

  Control File Included: Ckp SCN: 2809059      Ckp time: 15-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

99      Full    9.92M      DISK        00:00:01     15-AUG-13     

        BP Key: 99   Status: AVAILABLE  Compressed: NO  Tag: TAG20130815T063642

        Piece Name: /pooldisk02/backup03/3tohb9bq_1_1

  Control File Included: Ckp SCN: 2809603      Ckp time: 15-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

100     Full    9.95M      DISK        00:00:01     15-AUG-13     

        BP Key: 100   Status: AVAILABLE  Compressed: NO  Tag: TAG20130815T063644

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

  Control File Included: Ckp SCN: 2809609      Ckp time: 15-AUG-13

--7.5 重啟到nomount狀態，用指定的控制檔案備份還原控制檔案（不要做）

RMAN> run {

2> startup nomount force;

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

4> }