联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
遇到两次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