rman 恢復---歸檔丟失and資料檔案損壞

在歸檔日誌有丟失的情況下並且資料檔案損壞，恢復資料庫。

 

用rman恢復，歸檔有丟失，恢復到指定的sequence

1、在資料庫open的情況下，做一些操作，確定這些操作所在的歸檔日誌檔案

SQL> select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         10          1 ACTIVE
         2         11          1 CURRENT
         3          9          1 ACTIVE

SQL> show user;
USER is "HAOZG"
SQL> create table test11(name varchar2(10),age number);

Table created.

SQL> insert into test11 values('haozg',28);

1 row created.

SQL> commit;

Commit complete.

SQL> /

Commit complete.

SQL> insert into test11 values('zhangf',29);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         10          1 ACTIVE
         2         11          1 ACTIVE
         3         12          1 CURRENT

上面做的操作儲存在sequence是11的歸檔日誌檔案中。

2、做全庫備份

RMAN> run{
 allocate channel c1 type disk maxpiecesize=500m;
 backup current controlfile format '+dgasm/backup/haozg/ctl_%d_%s';
 backup full database format '+dgasm/backup/haozg/db_%d_%s_%p_%t';
 sql 'alter system archive log current';
 release channel c1;
 }2> 3> 4> 5> 6> 7>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=44 device type=DISK

Starting backup at 01-JUL-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 01-JUL-12
channel c1: finished piece 1 at 01-JUL-12
piece handle=+DGASM/backup/haozg/ctl_ora11_41 tag=TAG20120701T101120 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 01-JUL-12

Starting backup at 01-JUL-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DGASM/ora11/datafile/system.257.785186755
input datafile file number=00002 name=+DGASM/ora11/datafile/sysaux.258.785186845
input datafile file number=00003 name=+DGASM/ora11/datafile/undotbs1.259.785186901
input datafile file number=00004 name=+DGASM/ora11/datafile/users.272.787470817
channel c1: starting piece 1 at 01-JUL-12
channel c1: finished piece 1 at 01-JUL-12
piece handle=+DGASM/backup/haozg/db_ora11_42_1_787486286 tag=TAG20120701T101126 comment=NONE
channel c1: starting piece 2 at 01-JUL-12
channel c1: finished piece 2 at 01-JUL-12
piece handle=+DGASM/backup/haozg/db_ora11_42_2_787486286 tag=TAG20120701T101126 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:00
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 01-JUL-12
channel c1: finished piece 1 at 01-JUL-12
piece handle=+DGASM/backup/haozg/db_ora11_43_1_787486406 tag=TAG20120701T101126 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-JUL-12

sql statement: alter system archive log current

released channel: c1

RMAN>

3、然再做操作，確定所在的歸檔檔案

SQL> select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         13          1 CURRENT
         2         11          1 INACTIVE
         3         12          1 ACTIVE

SQL> create table test22(name varchar2(10),age number);

Table created.

SQL> insert into test22 values('guany',30);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         13          1 ACTIVE
         2         14          1 CURRENT
         3         12          1 ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         16          1 CURRENT
         2         14          1 ACTIVE
         3         15          1 ACTIVE

上面的操作在sequence是13 的歸檔日誌檔案中

4、到資料庫的歸檔目錄下刪除sequence 是13的歸檔日誌檔案

[oracle@haozg archivelog]$ ls -al
total 9904
drwxr-xr-x  2 oracle oinstall     4096 Jul  1 10:16 .
drwxrwxr-x 12 oracle oinstall     4096 Jun 18 17:04 ..
-rw-r-----  1 oracle oinstall     2560 Jul  1 10:03 1_10_787471195.dbf
-rw-r-----  1 oracle oinstall    20992 Jul  1 10:08 1_11_787471195.dbf
-rw-r-----  1 oracle oinstall 10061312 Jul  1 10:13 1_12_787471195.dbf
-rw-r-----  1 oracle oinstall    18944 Jul  1 10:16 1_13_787471195.dbf
-rw-r-----  1 oracle oinstall     2560 Jul  1 10:03 1_9_787471195.dbf
[oracle@haozg archivelog]$ mv 1_13_787471195.dbf ../
[oracle@haozg archivelog]$ ls
1_10_787471195.dbf  1_11_787471195.dbf  1_12_787471195.dbf  1_9_787471195.dbf
[oracle@haozg archivelog]$ ls -al
total 10168
drwxr-xr-x  2 oracle oinstall     4096 Jul  1 10:20 .
drwxrwxr-x 12 oracle oinstall     4096 Jul  1 10:19 ..
-rw-r-----  1 oracle oinstall     2560 Jul  1 10:03 1_10_787471195.dbf
-rw-r-----  1 oracle oinstall    20992 Jul  1 10:08 1_11_787471195.dbf
-rw-r-----  1 oracle oinstall 10061312 Jul  1 10:13 1_12_787471195.dbf
-rw-r-----  1 oracle oinstall   282624 Jul  1 10:20 1_14_787471195.dbf
-rw-r-----  1 oracle oinstall     3072 Jul  1 10:20 1_15_787471195.dbf
-rw-r-----  1 oracle oinstall     2560 Jul  1 10:03 1_9_787471195.dbf

5、關閉資料庫，刪除user資料檔案，模擬資料檔案損壞

oracle 使用者下操作
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

grid 使用者下操作
ASMCMD> ls
SYSAUX.258.785186845
SYSTEM.257.785186755
UNDOTBS1.259.785186901
USERS.272.787470817
ASMCMD> rm -f user*
ASMCMD> ls
SYSAUX.258.785186845
SYSTEM.257.785186755
UNDOTBS1.259.785186901

6、開啟資料庫，出現錯誤

SQL> startup monut;
SP2-0714: invalid combination of STARTUP options
SQL> startup mount;
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
Database mounted.
SQL>
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DGASM/ora11/datafile/users.272.787470817'

7、轉儲資料庫

RMAN> restore database;

Starting restore at 01-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

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 +DGASM/ora11/datafile/system.257.785186755
channel ORA_DISK_1: restoring datafile 00002 to +DGASM/ora11/datafile/sysaux.258.785186845
channel ORA_DISK_1: restoring datafile 00003 to +DGASM/ora11/datafile/undotbs1.259.785186901
channel ORA_DISK_1: restoring datafile 00004 to +DGASM/ora11/datafile/users.272.787470817
channel ORA_DISK_1: reading from backup piece +DGASM/backup/haozg/db_ora11_42_1_787486286
channel ORA_DISK_1: piece handle=+DGASM/backup/haozg/db_ora11_42_1_787486286 tag=TAG20120701T101126
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: reading from backup piece +DGASM/backup/haozg/db_ora11_42_2_787486286
channel ORA_DISK_1: piece handle=+DGASM/backup/haozg/db_ora11_42_2_787486286 tag=TAG20120701T101126
channel ORA_DISK_1: restored backup piece 2
channel ORA_DISK_1: restore complete, elapsed time: 00:01:50
Finished restore at 01-JUL-12

8、然後再去open，提示需要介質恢復

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '+DGASM/ora11/datafile/system.257.785186755'

9、recover database

RMAN> recover database;

Starting recover at 01-JUL-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /oracle/archivelog/1_12_787471195.dbf
archived log for thread 1 with sequence 14 is already on disk as file /oracle/archivelog/1_14_787471195.dbf
archived log for thread 1 with sequence 15 is already on disk as file /oracle/archivelog/1_15_787471195.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/01/2012 10:32:14
RMAN-06053: unable to perform. media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 13 and starting SCN of 970506 found to restore

提示缺少sequence 為13 的歸檔日誌檔案，然後用下面的命令恢復到sequence 為12 的狀態。

RMAN> recover database until sequence 12;

Starting recover at 01-JUL-12
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/01/2012 10:36:06
RMAN-06556: datafile 1 must be restored from backup older than SCN 968324

仍然出現錯誤，又回到了老問題，切記：恢復到sequence 為13的情況下，實際上只應用歸檔到sequence 12，不包括sequence 為13
的歸檔日誌，所以用下面的命令繼續恢復：

RMAN> recover database until sequence 13; 

Starting recover at 01-JUL-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /oracle/archivelog/1_12_787471195.dbf
archived log file name=/oracle/archivelog/1_12_787471195.dbf thread=1 sequence=12
media recovery complete, elapsed time: 00:00:00
Finished recover at 01-JUL-12

RMAN>

完成了不完全恢復

10、open 資料庫

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select * from test11;
select * from test11
              *
ERROR at line 1:
ORA-00942: table or view does not exist

由於做了不完全恢復，所以用resetlogs方式開啟資料庫
檢查資料：

SQL> connect haozg/haozg
Connected.
SQL> select * from test11;

NAME              AGE
---------- ----------
haozg              28
zhangf             29

SQL> select * from test22;
select * from test22
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>

sequence 為 13 的歸檔日誌麼有恢復，所以在13號中的操作沒有重演。所以表test22不存在。