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#，数据库自动完成不完全恢复。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23062014/viewspace-735666/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23062014/viewspace-735666/