[20150308]热备份和数据库检查点.txt
--今天看书,提到在热备份前,会做了一个数据文件检查点操作.
--实际上这个很好理解:
开始热备份时候，做了一个数据文件检查点操作，因为热备份时备份要产生的日志很大，数据库必须要知道那个时候开始，做这项工作。
保证了在热备份期间，只有在发出热备份命令之后的时间里修改的块可能会被写到数据文件上。
--自己做一个简单检查:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
RMAN> report schema ;
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 780 SYSTEM *** /u01/app/oracle11g/oradata/test/system01.dbf
2 1000 SYSAUX *** /u01/app/oracle11g/oradata/test/sysaux01.dbf
3 848 UNDOTBS1 *** /u01/app/oracle11g/oradata/test/undotbs01.dbf
4 864 USERS *** /u01/app/oracle11g/oradata/test/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle11g/oradata/test/example01.dbf
6 64 RMAN *** /u01/app/oracle11g/oradata/test/rman01.dbf
7 64 TOOLS *** /u01/app/oracle11g/oradata/test/tools01.dbf
8 64 TEST *** /u01/app/oracle11g/oradata/test/test01.dbf
9 64 TESTMSSM *** /u01/app/oracle11g/oradata/test/testmssm01.dbf
10 4 UNDOTBS2 *** /u01/app/oracle11g/oradata/test/undotbs02.dbf
11 102 TEST16K *** /u01/app/oracle11g/oradata/test/test16k01.dbf
12 1 TOOLSX *** /u01/app/oracle11g/oradata/test/tools01x.dbf
13 50 TBS_TS1 *** /u01/app/oracle11g/oradata/test/tbs_ts1_01.dbf
14 127 TBS_TS2 *** /u01/app/oracle11g/oradata/test/tbs_ts2_01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 339 TEMP 32767 /u01/app/oracle11g/oradata/test/temp01.dbf
SCOTT@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 12005514399 7 3011113647 ONLINE 856622640
2 12005514399 2140 3011113647 ONLINE 856622634
3 12005514399 3241444492 3011113647 ONLINE 856622635
4 12005514399 17993 3011113647 ONLINE 856622644
5 12005514399 973735 3011113647 ONLINE 856622637
6 12005514399 1412559 3011113647 ONLINE 856622603
7 12005514399 4383251 3011113647 ONLINE 856622632
8 12005514399 13169364 3011113647 ONLINE 856622634
9 12005514399 3223747107 3011113647 ONLINE 856622632
10 12005514399 12002485849 3011113647 ONLINE 151
11 12005514399 11673111577 3011113647 ONLINE 1117
12 12005514399 11994962958 3011113647 ONLINE 475
13 12005514399 11992635787 3011113647 ONLINE 588
14 12005514399 11992670578 3011113647 ONLINE 586
14 rows selected.
--对file#=4 表空间users做热备份。当前CHECKPOINT_CHANGE#=12005514399，CHECKPOINT_COUNT=856622644.
2.开始测试：
SCOTT@test> select rowid,dept.* from dept;
ROWID DEPTNO DNAME LOC
------------------ ------------ -------------- -------------
AABBrlAAEAAAAWDAAB 10 ACCOUNTING new york
AABBrlAAEAAAAWDAAC 20 RESEARCH dallas1
AABBrlAAEAAAAWDAAD 30 SALES chicago
AABBrlAAEAAAAWDAAE 40 OPERATIONS boston
SCOTT@test> @lookup_rowid AABBrlAAEAAAAWDAAB
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
269029 4 1411 1 4,1411 alter system dump datafile 4 block 1411
SCOTT@test> update dept set loc=upper(loc) ;
4 rows updated.
--不提交。切换另外的会话，注意执行如下命令alter tablespace users begin backup ;会隐含发commit命令。
SCOTT@test> alter tablespace users begin backup ;
Tablespace altered.
SCOTT@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 12005514399 7 3011113647 ONLINE 856622640
2 12005514399 2140 3011113647 ONLINE 856622634
3 12005514399 3241444492 3011113647 ONLINE 856622635
4 12005514724 17993 3011113647 ONLINE 856622645
5 12005514399 973735 3011113647 ONLINE 856622637
6 12005514399 1412559 3011113647 ONLINE 856622603
7 12005514399 4383251 3011113647 ONLINE 856622632
8 12005514399 13169364 3011113647 ONLINE 856622634
9 12005514399 3223747107 3011113647 ONLINE 856622632
10 12005514399 12002485849 3011113647 ONLINE 151
11 12005514399 11673111577 3011113647 ONLINE 1117
12 12005514399 11994962958 3011113647 ONLINE 475
13 12005514399 11992635787 3011113647 ONLINE 588
14 12005514399 11992670578 3011113647 ONLINE 586
14 rows selected.
--注意看file#=4，CHECKPOINT_CHANGE#现在是12005514724,CHECKPOINT_COUNT=856622645,CHECKPOINT_COUNT也增加1次。
--通过bbed 观察，我并没有执行alter systenm checkpoint，可以发现bbed观察对应数据块，可以发现修改信息已经写磁盘。
BBED> set dba 4,1411
DBA 0x01000583 (16778627 4,1411)
BBED> p *kdbr[1]
rowdata[151]
------------
ub1 rowdata[151] @8146 0x2c
BBED> x /rncc
rowdata[151] @8146
------------
flag@8146: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8147: 0x03
cols@8148: 3
col 0[2] @8149: 10
col 1[10] @8152: ACCOUNTING
col 2[8] @8163: NEW YORK
SCOTT@test> set numw 12
SCOTT@test> select * from v$backup ;
FILE# STATUS CHANGE# TIME
------------ ------------------ ------------ -------------------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 ACTIVE 12005514724 2015-03-09 08:43:00
5 NOT ACTIVE 0
6 NOT ACTIVE 3011239824 2012-11-08 15:43:19
7 NOT ACTIVE 0
8 NOT ACTIVE 3268230043 2014-03-20 10:13:21
9 NOT ACTIVE 0
10 NOT ACTIVE 0
11 NOT ACTIVE 0
12 NOT ACTIVE 0
13 NOT ACTIVE 0
14 NOT ACTIVE 0
14 rows selected.
--结束热备份：
SCOTT@test> alter tablespace users end backup ;
Tablespace altered.
SCOTT@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 12005514399 7 3011113647 ONLINE 856622640
2 12005514399 2140 3011113647 ONLINE 856622634
3 12005514399 3241444492 3011113647 ONLINE 856622635
4 12005514724 17993 3011113647 ONLINE 856622646
5 12005514399 973735 3011113647 ONLINE 856622637
6 12005514399 1412559 3011113647 ONLINE 856622603
7 12005514399 4383251 3011113647 ONLINE 856622632
8 12005514399 13169364 3011113647 ONLINE 856622634
9 12005514399 3223747107 3011113647 ONLINE 856622632
10 12005514399 12002485849 3011113647 ONLINE 151
11 12005514399 11673111577 3011113647 ONLINE 1117
12 12005514399 11994962958 3011113647 ONLINE 475
13 12005514399 11992635787 3011113647 ONLINE 588
14 12005514399 11992670578 3011113647 ONLINE 586
14 rows selected.
--注意看file#=4，CHECKPOINT_CHANGE#现在是12005514724(没变),CHECKPOINT_COUNT=856622646,CHECKPOINT_COUNT又增加1次。
SCOTT@test> rollback ;
Rollback complete.
SCOTT@test> select rowid,dept.* from dept;
ROWID DEPTNO DNAME LOC
------------------ ------------ -------------- -------------
AABBrlAAEAAAAWDAAB 10 ACCOUNTING new york
AABBrlAAEAAAAWDAAC 20 RESEARCH dallas1
AABBrlAAEAAAAWDAAD 30 SALES chicago
AABBrlAAEAAAAWDAAE 40 OPERATIONS boston
--可以发现在做热备份前，数据库会对涉及到的做了一个数据文件检查点操作.