[20150109]关于热备份.txt
--热备份仅仅冻结数据文件以及控制文件对应的CHECKPOINT_CHANGE#。昨天别人提到如果热备份长时间没有完成或者结束，异常关机会出
--现一些问题，容易导致误判。自己做一个测试。
1.建立测试环境：
SYS@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
$ cat db_status.sql
set echo on
SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT FROM v$datafile_header;
SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
set echo off
SYS@test> @ &r/db_status
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
------------ ------------------ --------------------- ------------ --------------- -------------- -------
1 2714007 0 2667798 2667799 SYSTEM
2 2714007 0 2667798 2667799 ONLINE
3 2714007 0 2667798 2667799 ONLINE
4 2714007 0 2667798 2667799 ONLINE
5 2714007 0 2667798 2667799 ONLINE
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT
------------ ------------------ ---------------- ----------------- ------- ----------------
1 2714007 5 2667799 ONLINE 868464769
2 2714007 600647 2667799 ONLINE 868464769
3 2714007 6678 2667799 ONLINE 868464769
4 2714007 10685 2667799 ONLINE 868464771
5 2714007 625439 2667799 ONLINE 868464769
SYS@test> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME CHECKPOINT_CHANGE# OPEN_MODE CURRENT_SCN
----------- -------------------------------------------------- ------------------ ---------- ------------
controlfile SYSTEM checkpoint 2714007 READ WRITE 2714220
2.进入热备份模式：
SYS@test> alter database begin backup;
Database altered.
SYS@test> select * from v$backup ;
FILE# STATUS CHANGE# TIME
------------ ------------------ ------------ -------------------
1 ACTIVE 2714252 2015-01-09 09:29:00
2 ACTIVE 2714252 2015-01-09 09:29:00
3 ACTIVE 2714252 2015-01-09 09:29:00
4 ACTIVE 2714252 2015-01-09 09:29:00
5 ACTIVE 2714252 2015-01-09 09:29:00
SYS@test> @ &r/db_status
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
------------ ------------------ --------------------- ------------ --------------- -------------- -------
1 2714252 0 2667798 2667799 SYSTEM
2 2714252 0 2667798 2667799 ONLINE
3 2714252 0 2667798 2667799 ONLINE
4 2714252 0 2667798 2667799 ONLINE
5 2714252 0 2667798 2667799 ONLINE
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT
------------ ------------------ ---------------- ----------------- ------- ----------------
1 2714252 5 2667799 ONLINE 868464770
2 2714252 600647 2667799 ONLINE 868464770
3 2714252 6678 2667799 ONLINE 868464770
4 2714252 10685 2667799 ONLINE 868464772
5 2714252 625439 2667799 ONLINE 868464770
SYS@test> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME CHECKPOINT_CHANGE# OPEN_MODE CURRENT_SCN
----------- -------------------------------------------------- ------------------ ---------- ------------
controlfile SYSTEM checkpoint 2714007 READ WRITE 2714261
--注意看控制文件以及数据问题的CHECKPOINT_CHANGE#都发生了变化，CHECKPOINT_COUNT也增加1.
--但是如果没有结束热备份，CHECKPOINT_CHANGE#就不会发生变化，但是CHECKPOINT_COUNT会增加1，在发alter systemn checkoint的时
--候.
3.开始操作：
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
SYS@test> select * from v$logfile ;
GROUP# STATUS TYPE MEMBER IS_
------------ ------- ---------------------------------------- ------------------------------------------------------------ ---
3 ONLINE /mnt/ramdisk/test/redo03.log NO
2 ONLINE /mnt/ramdisk/test/redo02.log NO
1 ONLINE /mnt/ramdisk/test/redo01.log NO
SYS@test> select * from v$log ;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------------ ------------ ------------ ------------ ------------ --- ---------------- ------------- -------------------
1 1 7 52428800 1 YES INACTIVE 2714367 2015-01-09 09:33:08
2 1 8 52428800 1 NO CURRENT 2714371 2015-01-09 09:33:13
3 1 6 52428800 1 YES INACTIVE 2714364 2015-01-09 09:33:07
--当前CHECKPOINT_CHANGE#=2714252,已经不再redo文件的范围。
--再顺便做一些事务。
create table t1 (id number,name varchar2(20));
insert into t1 values (1,'aaaa');
insert into t1 values (2,'bbbb');
commit ;
4.模拟异常情况出现。
SYS@test> shutdown abort ;
ORACLE instance shut down.
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/mnt/ramdisk/test/system01.dbf'
--^_^问题出现。
--查看alert*.log文件，根本没有给出信息。
Fri Jan 9 09:38:34 2015
ALTER DATABASE OPEN
ORA-1113 signalled during: ALTER DATABASE OPEN...
SYS@test> @ &r/db_status
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
------------ ------------------ --------------------- ------------ --------------- -------------- -------
1 2714252 0 2667798 2667799 SYSTEM
2 2714252 0 2667798 2667799 ONLINE
3 2714252 0 2667798 2667799 ONLINE
4 2714252 0 2667798 2667799 ONLINE
5 2714252 0 2667798 2667799 ONLINE
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT
------------ ------------------ ---------------- ----------------- ------- ----------------
1 2714252 5 2667799 ONLINE 868464774
2 2714252 600647 2667799 ONLINE 868464774
3 2714252 6678 2667799 ONLINE 868464774
4 2714252 10685 2667799 ONLINE 868464776
5 2714252 625439 2667799 ONLINE 868464774
SYS@test> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME CHECKPOINT_CHANGE# OPEN_MODE CURRENT_SCN
----------- -------------------------------------------------- ------------------ ---------- ------------
controlfile SYSTEM checkpoint 2714371 MOUNTED 0
SYS@test> select * from v$log ;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------------ ------------ ------------ ------------ ------------ --- ---------------- ------------- -------------------
1 1 7 52428800 1 YES INACTIVE 2714367 2015-01-09 09:33:08
3 1 6 52428800 1 YES INACTIVE 2714364 2015-01-09 09:33:07
2 1 8 52428800 1 NO CURRENT 2714371 2015-01-09 09:33:13
--数据文件记录的CHECKPOINT_CHANGE#=2714252,可以发现不再redo文件的范围。如果不告诉你系统做热备份失败，选择的方式是
--recover database .然后打开数据库。
SYS@test> recover database ;
ORA-00279: change 2714252 generated at 01/09/2015 09:29:00 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_01_09/o1_mf_1_3_%u_.arc
ORA-00280: change 2714252 for thread 1 is in sequence #3
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2714357 generated at 01/09/2015 09:33:00 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_01_09/o1_mf_1_4_%u_.arc
ORA-00280: change 2714357 for thread 1 is in sequence #4
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_01_09/o1_mf_1_3_bbycydof_.arc' no longer needed for this recovery
ORA-00279: change 2714360 generated at 01/09/2015 09:33:01 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_01_09/o1_mf_1_5_%u_.arc
ORA-00280: change 2714360 for thread 1 is in sequence #5
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_01_09/o1_mf_1_4_bbycyfkl_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SYS@test> @ &r/db_status
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
------------ ------------------ --------------------- ------------ --------------- -------------- -------
1 2714597 0 2714597 2667798 2667799 SYSTEM
2 2714597 0 2714597 2667798 2667799 ONLINE
3 2714597 0 2714597 2667798 2667799 ONLINE
4 2714597 0 2714597 2667798 2667799 ONLINE
5 2714597 0 2714597 2667798 2667799 ONLINE
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT
------------ ------------------ ---------------- ----------------- ------- ----------------
1 2714597 5 2667799 ONLINE 868464775
2 2714597 600647 2667799 ONLINE 868464775
3 2714597 6678 2667799 ONLINE 868464775
4 2714597 10685 2667799 ONLINE 868464777
5 2714597 625439 2667799 ONLINE 868464775
SYS@test> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME CHECKPOINT_CHANGE# OPEN_MODE CURRENT_SCN
----------- -------------------------------------------------- ------------------ ---------- ------------
controlfile SYSTEM checkpoint 2714371 MOUNTED 0
SYS@test> alter database open ;
Database altered.
SYS@test> select * from v$backup ;
FILE# STATUS CHANGE# TIME
------------ ------------------ ------------ -------------------
1 NOT ACTIVE 2714252 2015-01-09 09:29:00
2 NOT ACTIVE 2714252 2015-01-09 09:29:00
3 NOT ACTIVE 2714252 2015-01-09 09:29:00
4 NOT ACTIVE 2714252 2015-01-09 09:29:00
5 NOT ACTIVE 2714252 2015-01-09 09:29:00
--可以发现热备份模式已经关闭。
SYS@test> select * from scott.t1;
ID NAME
--- ------
1 aaaa
2 bbbb
5.当然如果知道出在热备份模式，出现异常重启，也可以结束热备份模式。
--再重复测试,步骤不再重复.
SYS@test> shutdown abort ;
ORACLE instance shut down.
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/mnt/ramdisk/test/system01.dbf'
SYS@test> select * from v$backup ;
FILE# STATUS CHANGE# TIME
------------ ------------------ ------------ -------------------
1 ACTIVE 2755143 2015-01-09 10:02:54
2 ACTIVE 2755143 2015-01-09 10:02:54
3 ACTIVE 2755143 2015-01-09 10:02:54
4 ACTIVE 2755143 2015-01-09 10:02:54
5 ACTIVE 2755143 2015-01-09 10:02:54
SYS@test> select * from v$log ;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------------ ------------ ------------ ------------ ------------ --- ---------------- ------------- -------------------
1 1 31 52428800 1 YES INACTIVE 2755177 2015-01-09 10:03:10
3 1 30 52428800 1 YES INACTIVE 2755174 2015-01-09 10:03:09
2 1 32 52428800 1 NO CURRENT 2755180 2015-01-09 10:03:12
SYS@test> @ &r/db_status
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
------------ ------------------ --------------------- ------------ --------------- -------------- -------
1 2755143 0 2667798 2667799 SYSTEM
2 2755143 0 2667798 2667799 ONLINE
3 2755143 0 2667798 2667799 ONLINE
4 2755143 0 2667798 2667799 ONLINE
5 2755143 0 2667798 2667799 ONLINE
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT
------------ ------------------ ---------------- ----------------- ------- ----------------
1 2755143 5 2667799 ONLINE 868464799
2 2755143 600647 2667799 ONLINE 868464799
3 2755143 6678 2667799 ONLINE 868464799
4 2755143 10685 2667799 ONLINE 868464801
5 2755143 625439 2667799 ONLINE 868464799
SYS@test> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME CHECKPOINT_CHANGE# OPEN_MODE CURRENT_SCN
----------- -------------------------------------------------- ------------------ ---------- ------------
controlfile SYSTEM checkpoint 2755180 MOUNTED 0
--结束热备份
SYS@test> alter database end backup;
Database altered.
SYS@test> @ &r/db_status
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
------------ ------------------ --------------------- ------------ --------------- -------------- -------
1 2755180 0 2667798 2667799 SYSTEM
2 2755180 0 2667798 2667799 ONLINE
3 2755180 0 2667798 2667799 ONLINE
4 2755180 0 2667798 2667799 ONLINE
5 2755180 0 2667798 2667799 ONLINE
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT
------------ ------------------ ---------------- ----------------- ------- ----------------
1 2755180 5 2667799 ONLINE 868464800
2 2755180 600647 2667799 ONLINE 868464800
3 2755180 6678 2667799 ONLINE 868464800
4 2755180 10685 2667799 ONLINE 868464802
5 2755180 625439 2667799 ONLINE 868464800
SYS@test> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME CHECKPOINT_CHANGE# OPEN_MODE CURRENT_SCN
----------- -------------------------------------------------- ------------------ ---------- ------------
controlfile SYSTEM checkpoint 2755180 MOUNTED 0
--可以发现结束热备份CHECKPOINT_CHANGE#更新。
SYS@test> alter database open ;
Database altered.
--当然出现这个问题不是很严重，如果了解用户当时的状态，以及采用备份模式很重要，实际上他们遇到的问题是年底在报表手工统计时
--生成了许多中间表，占用了大量的磁盘空间，备份空间不足，热备份报错，又没人检查备份情况，正好出现数据库异常，检查备份又失
--败,才有点惊慌。