忘记执行end backup命令数据库恢复

发表于 2012 年 6 月 5 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：忘记执行end backup命令数据库恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

遇到两次begin backup忘记end backup导致的悲剧.虽然不是自己亲身经历,但是感触很深,这里做了一个小实验,说明在begin backup后忘记end backup,而又丢失了备份归档日志,且数据库异常重启的事故恢复(这里为了加大实验难道,并且使用begin backup命令后的热备文件恢复)
模拟begin end

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/xifenfei/archive
Oldest online log sequence     37
Next log sequence to archive   39
Current log sequence           39
SQL> alter tablespace bbed begin backup;

Tablespace altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/xifenfei/archive
Oldest online log sequence     37
Next log sequence to archive   39
Current log sequence           39
SQL> drop table chf.t_xff;

Table dropped.

SQL> create table chf.t_xff
  2    as
  3  select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

SQL>   delete from chf.t_XFF;

30811 rows deleted.

SQL>   commit;

Commit complete.

SQL>   alter system  switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/xifenfei/archive
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42

cp备份文件

[oracle@xifenfei xifenfei]$ cp bbed01.dbf bbed01.dbf_05
[oracle@xifenfei xifenfei]$ cp bbed02.dbf bbed02.dbf_05

继续操作数据库

SQL> alter system switch logfile;

System altered.

SQL>   insert into chf.t_xff
  2    select * from dba_objects;

30811 rows created.

SQL> commit;

Commit complete.

SQL>  archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/xifenfei/archive
Oldest online log sequence     41
Next log sequence to archive   43
Current log sequence           43
SQL> alter system switch logfile;

System altered.

模拟异常关闭数据库

SQL> shutdown immediate;
ORA-01149: cannot shutdown - file 11 has online backup set
ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf'
SQL> shutdown abort;
ORACLE instance shut down.

删除部分归档日志(模拟归档日志丢失)

[oracle@xifenfei archive]$ mv 1_39.dbf 1_39.dbf_bak
[oracle@xifenfei archive]$ mv 1_40.dbf 1_40.dbf_bak

启动数据库

[oracle@xifenfei xifenfei]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jun 5 03:02:56 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf'

分析相关SCN

SQL> select file#,online_status "STATUS",to_char(change#,'9999999999999999') "SCN",
  2  To_char(time,'yyyy-mm-dd hh24:mi:ss')"TIME" from v$recover_file;

     FILE# STATUS  SCN               TIME
---------- ------- ----------------- -------------------
        11 ONLINE     12286828683164 2012-06-05 02:55:43
        12 ONLINE     12286828683164 2012-06-05 02:55:43

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

     FILE# SCN              STOP_SCN
---------- ---------------- ----------------
         1   12286828684636
         2   12286828684636
         3   12286828684636
         4   12286828684636
         5   12286828684636
         6   12286828684636
         7   12286828684636
         8   12286828684636
         9   12286828684636
        10   12286828684636
        11   12286828683164
        12   12286828683164

12 rows selected.

SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;

     FILE# SCN               RESETLOGS SCN
---------- ----------------- -----------------
         1    12286828684636            174968
         2    12286828684636            174968
         3    12286828684636            174968
         4    12286828684636            174968
         5    12286828684636            174968
         6    12286828684636            174968
         7    12286828684636            174968
         8    12286828684636            174968
         9    12286828684636            174968
        10    12286828684636            174968
        11    12286828683164            174968
        12    12286828683164            174968

12 rows selected.

SQL> select file#,to_char(CHANGE#,'9999999999999999') "SCN",
  2  to_char(TIME,'yyyy-mm-dd hh24:mi:ss') "TIME" from v$backup;

     FILE# SCN               TIME
---------- ----------------- -------------------
         1                 0
         2                 0
         3                 0
         4                 0
         5                 0
         6                 0
         7                 0
         8                 0
         9                 0 
        10                 0
        11    12286828683164 2012-06-05 02:55:43
        12    12286828683164 2012-06-05 02:55:43

12 rows selected.

发现数据库未end backup

Tue Jun  5 02:55:43 2012
alter tablespace bbed begin backup
Tue Jun  5 02:55:43 2012
Completed: alter tablespace bbed begin backup

尝试end backup
出现这个错误是正常的,因为我替换回来的bbed表空间数据文件的版本信息可能和控制文件的不一致,解决方法是重建控制文件

SQL> alter tablespace bbed end backup;
alter tablespace bbed end backup
*
ERROR at line 1:
ORA-01235: END BACKUP failed for 2 file(s) and succeeded for 0
ORA-01122: database file 12 failed verification check
ORA-01110: data file 12: '/u01/oracle/oradata/xifenfei/bbed02.dbf'
ORA-01208: data file is an old version - not accessing current version
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf'
ORA-01208: data file is an old version - not accessing current version

重建控制文件

SQL> shutdown abort;
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes

SQL>@ctl.sql
Control file created.

尝试恢复数据库

SQL> recover database;
ORA-00279: change 12286828683164 generated at 06/05/2012 02:55:43 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_39.dbf
ORA-00280: change 12286828683164 for thread 1 is in sequence #39


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/oracle/oradata/xifenfei/archive/1_39.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


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

执行end backup

SQL> alter tablespace bbed end backup;

Tablespace altered.

再次查看相关SCN
可以发现end backup之后,datafile header 的scn发生了改变,说明begin backup主要是冻住了datafile header scn

SQL> select file#,online_status "STATUS",to_char(change#,'9999999999999999') "SCN",
  2  To_char(time,'yyyy-mm-dd hh24:mi:ss')"TIME" from v$recover_file;

     FILE# STATUS  SCN               TIME
---------- ------- ----------------- -------------------
         1 ONLINE     12286828684636 2012-06-05 03:00:46
         2 ONLINE     12286828684636 2012-06-05 03:00:46
         3 ONLINE     12286828684636 2012-06-05 03:00:46
         4 ONLINE     12286828684636 2012-06-05 03:00:46
         5 ONLINE     12286828684636 2012-06-05 03:00:46
         6 ONLINE     12286828684636 2012-06-05 03:00:46
         7 ONLINE     12286828684636 2012-06-05 03:00:46
         8 ONLINE     12286828684636 2012-06-05 03:00:46
         9 ONLINE     12286828684636 2012-06-05 03:00:46
        10 ONLINE     12286828684636 2012-06-05 03:00:46
        11 ONLINE     12286828683821 2012-06-05 02:56:26
        12 ONLINE     12286828683821 2012-06-05 02:56:26

12 rows selected.

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

     FILE# SCN              STOP_SCN
---------- ---------------- ----------------
         1   12286828684636
         2   12286828684636
         3   12286828684636
         4   12286828684636
         5   12286828684636
         6   12286828684636
         7   12286828684636
         8   12286828684636
         9   12286828684636
        10   12286828684636
        11   12286828684636
        12   12286828684636

12 rows selected.

SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;

     FILE# SCN               RESETLOGS SCN
---------- ----------------- -----------------
         1    12286828684636            174968
         2    12286828684636            174968
         3    12286828684636            174968
         4    12286828684636            174968
         5    12286828684636            174968
         6    12286828684636            174968
         7    12286828684636            174968
         8    12286828684636            174968
         9    12286828684636            174968
        10    12286828684636            174968
        11    12286828683821            174968
        12    12286828683821            174968

12 rows selected.

再次尝试恢复数据库

SQL> recover database;
ORA-00279: change 12286828683821 generated at 06/05/2012 02:56:26 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_41.dbf
ORA-00280: change 12286828683821 for thread 1 is in sequence #41


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

总结说明
在数据库忘记end backup,而又被异常重启数据库时候,会提示你需要恢复.这个时候如果你有所有的归档日志,那没有任何问题,直接recover就可以了.如果因为begin backup命令执行比较久,部分归档日志丢失,这个时候不能直接recover,可以先尝试end backup,然后在recover.如果在这个时候还发现有部分日志不存在,那只能考虑bbed修改datafile header的scn.
温馨提醒:各位dba在执行begin backup之后一定要记得end backup

此条目发表在 Oracle备份恢复 分类目录。将固定链接加入收藏夹。

评论功能已关闭。