oracle歸檔日誌丟失後的資料庫恢復

 

1、先做全庫備份

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';
 release channel c1;
 }2> 3> 4> 5> 6>

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

Starting backup at 07-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 07-JUL-12
channel c1: finished piece 1 at 07-JUL-12
piece handle=+DGASM/backup/haozg/ctl_ora11_76 tag=TAG20120707T072603 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
Finished backup at 07-JUL-12

Starting backup at 07-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=00005 name=+DGASM/ora11/datafile/test.dbf
input datafile file number=00003 name=+DGASM/ora11/datafile/undotbs1.259.785186901
input datafile file number=00004 name=+DGASM/ora11/datafile/users.272.787948839
channel c1: starting piece 1 at 07-JUL-12
channel c1: finished piece 1 at 07-JUL-12
piece handle=+DGASM/backup/haozg/db_ora11_77_1_787994772 tag=TAG20120707T072611 comment=NONE
channel c1: starting piece 2 at 07-JUL-12
channel c1: finished piece 2 at 07-JUL-12
piece handle=+DGASM/backup/haozg/db_ora11_77_2_787994772 tag=TAG20120707T072611 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:10
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 07-JUL-12
channel c1: finished piece 1 at 07-JUL-12
piece handle=+DGASM/backup/haozg/db_ora11_78_1_787994843 tag=TAG20120707T072611 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUL-12

released channel: c1

RMAN>

2、檢視當前日誌狀態，然後做一些操作，讓不同的操作分部在不同的歸檔日誌中。

SQL> select * from test55;

NAME                  AGE
-------------- ----------
haozg                  30
haozg                  30

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

FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1242824   2.8147E+14          1          4 CURRENT
      1242817      1242820          2          2 INACTIVE
      1242820      1242824          3          3 INACTIVE

SQL> show parameter _allow_resetlogs_corruption

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption          boolean     TRUE
SQL>                                                                     
SQL> delete from test55;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

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

FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1242824      1243031          1          4 ACTIVE
      1243031   2.8147E+14          2          5 CURRENT
      1242820      1242824          3          3 INACTIVE

SQL> insert into test55 values('a',5); 

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

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

FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1242824      1243031          1          4 ACTIVE
      1243031      1243064          2          5 ACTIVE
      1243064   2.8147E+14          3          6 CURRENT

SQL> insert into test55 values('a',6);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

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

FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1243125   2.8147E+14          1          7 CURRENT
      1243031      1243064          2          5 ACTIVE
      1243064      1243125          3          6 ACTIVE

SQL> insert into test55 values('a',7);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

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

FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1243125      1244092          1          7 ACTIVE
      1244092   2.8147E+14          2          8 CURRENT
      1243064      1243125          3          6 INACTIVE

SQL> insert into test55 values('a',8);

1 row created.

SQL> commit;

Commit complete.

SQL> alter sytem switch logfile;
alter sytem switch logfile
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /.

System altered.

SQL> /

System altered.

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

FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1244847   2.8147E+14          1         13 CURRENT
      1244841      1244844          2         11 INACTIVE
      1244844      1244847          3         12 INACTIVE

3、非一致性關閉資料庫

SQL> shutdown abort;

4、刪除user資料檔案，模擬資料檔案損壞

ASMCMD> cd datafile
ASMCMD> ls
SYSAUX.258.785186845
SYSTEM.257.785186755
TP_TEST.275.787641771
UNDOTBS1.259.785186901
USERS.272.787487359
test.dbf
ASMCMD> rm -f user*
ASMCMD> ls
SYSAUX.258.785186845
SYSTEM.257.785186755
TP_TEST.275.787641771
UNDOTBS1.259.785186901
test.dbf

5、刪除歸檔

[oracle@haozg archivelog]$ cp 1_8_787993729.dbf 1_8_787993729.dbf.bak
[oracle@haozg archivelog]$ rm -f 1_8_787993729.dbf
[oracle@haozg archivelog]$ ls
1_10_787993729.dbf  1_4_787993729.dbf  1_7_787993729.dbf
1_11_787993729.dbf  1_5_787993729.dbf  1_8_787993729.dbf.bak
1_12_787993729.dbf  1_6_787993729.dbf  1_9_787993729.dbf

6、啟動資料庫出現錯誤

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.787948839'

7、檢查 控制檔案和資料檔案、redo日誌檔案檢查點

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

FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1244847   2.8147E+14          1         13 CURRENT
      1244844      1244847          3         12 INACTIVE
      1244841      1244844          2         11 INACTIVE

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
           1244847
           1244847
           1244847
           1244847
           1244847

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           1244847
           1244847
           1244847
                 0
           1244847

8、在rman下轉儲資料庫

RMAN> restore database;

Starting restore at 07-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 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.787948839
channel ORA_DISK_1: restoring datafile 00005 to +DGASM/ora11/datafile/test.dbf
channel ORA_DISK_1: reading from backup piece +DGASM/backup/haozg/db_ora11_77_1_787994772
channel ORA_DISK_1: piece handle=+DGASM/backup/haozg/db_ora11_77_1_787994772 tag=TAG20120707T072611
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: reading from backup piece +DGASM/backup/haozg/db_ora11_77_2_787994772
channel ORA_DISK_1: piece handle=+DGASM/backup/haozg/db_ora11_77_2_787994772 tag=TAG20120707T072611
channel ORA_DISK_1: restored backup piece 2
channel ORA_DISK_1: restore complete, elapsed time: 00:01:31
Finished restore at 07-JUL-12

RMAN>

9、在sqlplus下恢復資料庫

SQL> recover database until cancel;
ORA-00279: change 1242935 generated at 07/07/2012 07:26:12 needed for thread 1
ORA-00289: suggestion : /oracle/archivelog/1_4_787993729.dbf
ORA-00280: change 1242935 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1243031 generated at 07/07/2012 07:29:50 needed for thread 1
ORA-00289: suggestion : /oracle/archivelog/1_5_787993729.dbf
ORA-00280: change 1243031 for thread 1 is in sequence #5
ORA-00278: log file '/oracle/archivelog/1_4_787993729.dbf' no longer needed for
this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1243064 generated at 07/07/2012 07:30:59 needed for thread 1
ORA-00289: suggestion : /oracle/archivelog/1_6_787993729.dbf
ORA-00280: change 1243064 for thread 1 is in sequence #6
ORA-00278: log file '/oracle/archivelog/1_5_787993729.dbf' no longer needed for
this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1243125 generated at 07/07/2012 07:31:51 needed for thread 1
ORA-00289: suggestion : /oracle/archivelog/1_7_787993729.dbf
ORA-00280: change 1243125 for thread 1 is in sequence #7
ORA-00278: log file '/oracle/archivelog/1_6_787993729.dbf' no longer needed for
this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1244092 generated at 07/07/2012 07:56:11 needed for thread 1
ORA-00289: suggestion : /oracle/archivelog/1_8_787993729.dbf
ORA-00280: change 1244092 for thread 1 is in sequence #8
ORA-00278: log file '/oracle/archivelog/1_7_787993729.dbf' no longer needed for
this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/oracle/archivelog/1_8_787993729.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

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

FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1244847   2.8147E+14          1         13 CURRENT
      1244844      1244847          3         12 INACTIVE
      1244841      1244844          2         11 INACTIVE

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
           1244847
           1244847
           1244847
           1244847
           1244847

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           1244092
           1244092
           1244092
           1244092
           1244092

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.

9、檢查資料

SQL> select * from test55;

NAME                  AGE
-------------- ----------
a                       5
a                       6
a                       7

SQL>

沒有 age 是 8 的記錄，說明sequence# = 8 的歸檔日誌沒有應運，資料庫完成了不完全恢復。

總結：用rman 和sqlplus 相結合來完成資料庫的不完全恢復。如果只用rman來完成資料庫的不完全的恢復，那麼需要
      指定恢復的終點，也就是丟失的歸檔日誌的前一個歸檔日誌對應的scn 或者是sequence#。但是在sqlplus 下用
      until cancel ，就不用去找這個scn或者是sequenc#，資料庫自動完成不完全恢復。