Oracle begin backup研究

由于昨天客户数据库数据文件头scn远远低于当前scn，我猜测是表空间做了begin backup。为了验证这一想法，特做了如下实验：
数据库版本为：
[quote]SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production[/quote]

将数据库置于begin backup模式
[quote]SQL> alter database begin backup;

Database altered.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 9.7448E+12 20-MAR-10
2 ACTIVE 9.7448E+12 20-MAR-10
3 ACTIVE 9.7448E+12 20-MAR-10
4 ACTIVE 9.7448E+12 20-MAR-10
5 ACTIVE 9.7448E+12 20-MAR-10
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/ora10g/archlog
Oldest online log sequence 1071
Next log sequence to archive 1073
Current log sequence 1073[/quote]

切换几个归档，模拟业务正在进行
[quote]SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.[/quote]

将数据库shutdown abort
[quote]SQL> shutdown abort
ORACLE instance shut down.[/quote]

恢复之前数据文件头处在冻结状态
[quote]SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 9.7448E+12 20-MAR-10
2 ACTIVE 9.7448E+12 20-MAR-10
3 ACTIVE 9.7448E+12 20-MAR-10
4 ACTIVE 9.7448E+12 20-MAR-10
5 ACTIVE 9.7448E+12 20-MAR-10[/quote]

根据客户现场用"recover database using backup controlfile;"将数据库进行恢复
[quote]SQL>[color=red] recover database using backup controlfile;[/color]
ORA-00279: change 9744845765380 generated at 03/20/2010 14:42:22 needed for
thread 1
ORA-00289: suggestion : /opt/ora10g/archlog/1_1073_665850840.dbf
ORA-00280: change 9744845765380 for thread 1 is in sequence #1073


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
。。。
SQL> recover database using backup controlfile;
ORA-00279: change 9744845765485 generated at 03/20/2010 14:44:08 needed for
thread 1
ORA-00289: suggestion : /opt/ora10g/archlog/1_1077_665850840.dbf
ORA-00280: change 9744845765485 for thread 1 is in sequence #1077


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
[color=red]/opt/ora10g/oradata/dbra10g/redo02.log[/color]
Log applied.
Media recovery complete.[/quote]

可以看到v$backup已经置为 inactive
[quote]SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 9.7448E+12 20-MAR-10
2 NOT ACTIVE 9.7448E+12 20-MAR-10
3 NOT ACTIVE 9.7448E+12 20-MAR-10
4 NOT ACTIVE 9.7448E+12 20-MAR-10
5 NOT ACTIVE 9.7448E+12 20-MAR-10[/quote]
最后将数据打开
[quote]SQL> alter database open RESETLOGS;

Database altered.
[/quote]

[color=red]从以上实验可以看出，数据文件头scn远小于当前scn很大可能是ca软件将表空间begin backup所致。[/color]
+++++++++++++++++++++++++华丽的分割线+++++++++++++++++++++++++++++
其实恢复远比以上过程简单:
当数据处在mount状态时，查看数据文件头scn
[quote]SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ --------------------- ---------
1 ACTIVE 9744845806881 20-MAR-10
2 ACTIVE 9744845806881 20-MAR-10
3 ACTIVE 9744845806881 20-MAR-10
4 ACTIVE 9744845806881 20-MAR-10
5 ACTIVE 9744845806881 20-MAR-10

SQL> col checkpoint_change# for 9999999999999999999
SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
--------------------
9744845806881
9744845806881
9744845806881
9744845806881
9744845806881

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
--------------------
9744845806881
9744845806881
9744845806881
9744845806881
9744845806881[/quote]

查看数据库scn
[quote]SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
--------------------
9744845806954
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
0[/quote]
[color=red]在mount状态进行end backup，可以看到数据文件头进行了更新同步[/color]
[quote]SQL> alter database end backup;

Database altered.

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
--------------------
9744845806954
9744845806954
9744845806954
9744845806954
9744845806954

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
--------------------
9744845806954
9744845806954
9744845806954
9744845806954
9744845806954

[/quote]
随后只要将数据库打开即可

[quote]SQL> alter database open;

Database altered.[/quote]

其中alert日志显示
[quote]Sat Mar 20 15:03:28 2010
alter database end backup
Sat Mar 20 15:03:28 2010
Completed: alter database end backup
Sat Mar 20 15:03:57 2010
alter database open
Sat Mar 20 15:03:57 2010
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Sat Mar 20 15:03:57 2010
Started redo scan
Sat Mar 20 15:03:57 2010
Completed redo scan
245 redo blocks read, 41 data blocks need recovery
Sat Mar 20 15:03:57 2010
Started redo application at
Thread 1: logseq 15, block 977
Sat Mar 20 15:03:57 2010
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0
Mem# 0 errs 0: /opt/ora10g/oradata/dbra10g/redo03.log
Sat Mar 20 15:03:57 2010
Completed redo application
Sat Mar 20 15:03:57 2010
Completed crash recovery at
Thread 1: logseq 15, block 1222, scn 9744845826986
41 data blocks read, 41 data blocks written, 245 redo blocks read
Sat Mar 20 15:03:57 2010
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=19, OS id=8939
Sat Mar 20 15:03:57 2010
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=20, OS id=8941
Sat Mar 20 15:03:57 2010
Thread 1 advanced to log sequence 16
Thread 1 opened at log sequence 16
Current log# 2 seq# 16 mem# 0: /opt/ora10g/oradata/dbra10g/redo02.log
Successful open of redo thread 1
Sat Mar 20 15:03:57 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Mar 20 15:03:57 2010
ARC1: STARTING ARCH PROCESSES
Sat Mar 20 15:03:57 2010
[color=red]SMON: enabling cache recovery[/color]
Sat Mar 20 15:03:57 2010
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Sat Mar 20 15:03:57 2010
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC1: Becoming the heartbeat ARCH
ARC2 started with pid=21, OS id=8951
Sat Mar 20 15:03:57 2010
Successfully onlined Undo Tablespace 1.
Sat Mar 20 15:03:57 2010
SMON: enabling tx recovery
Sat Mar 20 15:03:57 2010
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=22, OS id=8960
Sat Mar 20 15:03:58 2010
Completed: alter database open[/quote]