Oracle 11g RMAN恢復-場景2：所有的資料檔案損壞，OPEN狀態

--1. 建立映象複製

--1.1 檢視Large Pool的大小

sys@TESTDB11>select name, bytes/1024/1024 as "size(MB)" from v$sgainfo;

NAME                               size(MB)

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

Fixed SGA Size                   2.12744904

Redo Buffers                     2.19921875

Buffer Cache Size                       200

Shared Pool Size                        316

Large Pool Size                           4

Java Pool Size                            4

Streams Pool Size                         4

Shared IO Pool Size                       0

Granule Size                              4

Maximum SGA Size                 816.328125

Startup overhead in Shared Pool  66.1327667

Free SGA Memory Available               284

12 rows selected.

--1.2 調整Large Pool的大小

sys@TESTDB11>alter system set large_pool_size=100m;

System altered.

--1.3 進行映象複製

RMAN> backup as copy database;

Starting backup at 14-AUG-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/oradata/system01.dbf

output file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-SYSTEM_FNO-1_24oh8ns9 tag=TAG20130814T072601 RECID=5 STAMP=823418796

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/oradata/sysaux01.dbf

output file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-SYSAUX_FNO-2_25oh8ntm tag=TAG20130814T072601 RECID=6 STAMP=823418845

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/oradata/example01.dbf

output file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-EXAMPLE_FNO-5_26oh8nv4 tag=TAG20130814T072601 RECID=7 STAMP=823418867

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/oradata/undotbs01.dbf

output file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-UNDOTBS1_FNO-3_27oh8nvj tag=TAG20130814T072601 RECID=8 STAMP=823418883

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=/oradata/fbtbs01.dbf

output file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-FBTBS_FNO-7_28oh8o0c tag=TAG20130814T072601 RECID=9 STAMP=823418901

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00008 name=/oradata/users02.dbf

output file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-USERS_FNO-8_29oh8o0r tag=TAG20130814T072601 RECID=10 STAMP=823418909

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/oradata/newts01.dbf

output file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-NEWTS_FNO-6_2aoh8o0u tag=TAG20130814T072601 RECID=11 STAMP=823418911

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/oradata/users01.dbf

output file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-USERS_FNO-4_2boh8o10 tag=TAG20130814T072601 RECID=12 STAMP=823418912

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 14-AUG-13

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

piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823418913_90pxzl9g_.bkp comment=NONE

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

--2. 切日誌2次

--3. 所有資料檔案丟失

sys@TESTDB11>select name from v$datafile;

NAME

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

/oradata/system01.dbf

/oradata/sysaux01.dbf

/oradata/undotbs01.dbf

/oradata/users01.dbf

/oradata/example01.dbf

/oradata/newts01.dbf

/oradata/fbtbs01.dbf

/oradata/users02.dbf

8 rows selected.

[oracle@S1011:/oradata]$ rm *.dbf

--4. 關庫並重新進入mount狀態

sys@TESTDB11>startup mount force;

--5. 連線rman，並檢視備份集和鏡複製的資訊

--5.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

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

62      Full    1.25G      DISK        00:02:39     14-AUG-13     

        BP Key: 62   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T070001

        Piece Name: /pooldisk02/backup03/22oh8mbh_1_1

  List of Datafiles in backup set 62

  File LV Type Ckp SCN    Ckp Time  Name

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

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

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

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

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

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

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

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

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

63      Full    9.67M      DISK        00:00:02     14-AUG-13     

        BP Key: 63   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T070246

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

  SPFILE Included: Modification time: 14-AUG-13

  SPFILE db_unique_name: TESTDB11

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

64      Full    9.67M      DISK        00:00:01     14-AUG-13     

        BP Key: 64   Status: AVAILABLE  Compressed: NO  Tag: TAG20130814T072833

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

  SPFILE Included: Modification time: 14-AUG-13

  SPFILE db_unique_name: TESTDB11

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

--5.2 檢視映象複製

RMAN> list copy;

List of Datafile Copies

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

Key     File S Completion Time Ckp SCN    Ckp Time      

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

5       1    A 14-AUG-13       2669395    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-SYSTEM_FNO-1_24oh8ns9

        Tag: TAG20130814T072601

6       2    A 14-AUG-13       2669419    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-SYSAUX_FNO-2_25oh8ntm

        Tag: TAG20130814T072601

8       3    A 14-AUG-13       2669448    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-UNDOTBS1_FNO-3_27oh8nvj

        Tag: TAG20130814T072601

12      4    A 14-AUG-13       2669484    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-USERS_FNO-4_2boh8o10

        Tag: TAG20130814T072601

4       4    A 14-AUG-13       2635169    14-AUG-13     

        Name: /pooldisk02/backup01/user01.bak

        Tag: TAG20130814T024444

3       4    A 14-AUG-13       2634778    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-USERS_FNO-4_0hoh86r8

        Tag: TAG20130814T023519

7       5    A 14-AUG-13       2669441    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-EXAMPLE_FNO-5_26oh8nv4

        Tag: TAG20130814T072601

11      6    A 14-AUG-13       2669482    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-NEWTS_FNO-6_2aoh8o0u

        Tag: TAG20130814T072601

9       7    A 14-AUG-13       2669462    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-FBTBS_FNO-7_28oh8o0c

        Tag: TAG20130814T072601

10      8    A 14-AUG-13       2669479    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-USERS_FNO-8_29oh8o0r

        Tag: TAG20130814T072601

List of Control File Copies

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

Key     S Completion Time Ckp SCN    Ckp Time      

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

2       A 13-AUG-13       2530130    13-AUG-13     

        Name: /pooldisk02/backup01/inconsistent/control01.bak

        Tag: TAG20130813T062512

List of Archived Log Copies for database with db_unique_name TESTDB11

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

Key     Thrd Seq     S Low Time

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

1       1    93      A 13-AUG-13

        Name: /archive1/1_93_813665348.dbf

...

84      1    31      A 14-AUG-13

        Name: /archive2/1_31_823328120.dbf

--5.3 只檢視資料庫的映象複製

RMAN> list copy of database;

List of Datafile Copies

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

Key     File S Completion Time Ckp SCN    Ckp Time      

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

5       1    A 14-AUG-13       2669395    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-SYSTEM_FNO-1_24oh8ns9

        Tag: TAG20130814T072601

6       2    A 14-AUG-13       2669419    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-SYSAUX_FNO-2_25oh8ntm

        Tag: TAG20130814T072601

8       3    A 14-AUG-13       2669448    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-UNDOTBS1_FNO-3_27oh8nvj

        Tag: TAG20130814T072601

12      4    A 14-AUG-13       2669484    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-USERS_FNO-4_2boh8o10

        Tag: TAG20130814T072601

4       4    A 14-AUG-13       2635169    14-AUG-13     

        Name: /pooldisk02/backup01/user01.bak

        Tag: TAG20130814T024444

3       4    A 14-AUG-13       2634778    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-USERS_FNO-4_0hoh86r8

        Tag: TAG20130814T023519

7       5    A 14-AUG-13       2669441    14-AUG-13      

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-EXAMPLE_FNO-5_26oh8nv4

        Tag: TAG20130814T072601

11      6    A 14-AUG-13       2669482    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-NEWTS_FNO-6_2aoh8o0u

        Tag: TAG20130814T072601

9       7    A 14-AUG-13       2669462    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-FBTBS_FNO-7_28oh8o0c

        Tag: TAG20130814T072601

10      8    A 14-AUG-13       2669479    14-AUG-13     

        Name: /pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-USERS_FNO-8_29oh8o0r

        Tag: TAG20130814T072601

--6. 還原,預設會用最新的備份（無論是備份集還是映象複製，因為需要做的恢復會少）

RMAN> restore database;

Starting restore at 14-AUG-13

configuration for DISK channel 2 is ignored

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring datafile 00001

input datafile copy RECID=5 STAMP=823418796 file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-SYSTEM_FNO-1_24oh8ns9

destination for restore of datafile 00001: /oradata/system01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00001

output file name=/oradata/system01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00002

input datafile copy RECID=6 STAMP=823418845 file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-SYSAUX_FNO-2_25oh8ntm

destination for restore of datafile 00002: /oradata/sysaux01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00002

output file name=/oradata/sysaux01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00003

input datafile copy RECID=8 STAMP=823418883 file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-UNDOTBS1_FNO-3_27oh8nvj

destination for restore of datafile 00003: /oradata/undotbs01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00003

output file name=/oradata/undotbs01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00004

input datafile copy RECID=12 STAMP=823418912 file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-USERS_FNO-4_2boh8o10

destination for restore of datafile 00004: /oradata/users01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00004

output file name=/oradata/users01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00005

input datafile copy RECID=7 STAMP=823418867 file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-EXAMPLE_FNO-5_26oh8nv4

destination for restore of datafile 00005: /oradata/example01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00005

output file name=/oradata/example01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00006

input datafile copy RECID=11 STAMP=823418911 file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-NEWTS_FNO-6_2aoh8o0u

destination for restore of datafile 00006: /oradata/newts01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00006

output file name=/oradata/newts01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00007

input datafile copy RECID=9 STAMP=823418901 file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-FBTBS_FNO-7_28oh8o0c

destination for restore of datafile 00007: /oradata/fbtbs01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00007

output file name=/oradata/fbtbs01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00008

input datafile copy RECID=10 STAMP=823418909 file name=/pooldisk02/backup03/data_D-TESTDB11_I-2578856066_TS-USERS_FNO-8_29oh8o0r

destination for restore of datafile 00008: /oradata/users02.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00008

output file name=/oradata/users02.dbf RECID=0 STAMP=0

Finished restore at 14-AUG-13

--7. 恢復

RMAN> recover database;

Starting recover at 14-AUG-13

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 14-AUG-13

--8. 開庫

RMAN> open database;

database opened