tablespace hot backup是否锁定数据文件
Jan042015
昨天群里讨论了关于表空间热备是否锁定数据文件的话题，经测试，之前理解的完全是错误的，看来书上写的并不都是对的。
下面演示实验过程，测试环境OEL4.8，ORACLE 10.2.0.1.0。先打开检查点信息打印到告警日志参数，以便后续观察。
SESSION 1 >alter system set log_checkpoints_to_alert=true; System altered.
新开一个终端，创建测试表空间和测试用户。
SESSION 1 >create tablespace users datafile '/u01/app/oracle/oradata/PROD/disk1/users01.dbf' size 2M autoextend on;
Tablespace created.
SESSION 1 >alter database default tablespace users;
Database altered.
SESSION 1 >create user dbdream identified by dbdream default tablespace;
create user dbdream identified by dbdream default tablespace
*
ERROR at line 1:
ORA-02155: invalid DEFAULT tablespace identifier
SESSION 1 >create user dbdream identified by dbdream default tablespace users;
User created.
SESSION 1 >grant dba to dbdream;
Grant succeeded.
SESSION 1窗口开启USERS表空间热备。
SESSION 1 >alter tablespace users begin backup; Tablespace altered.
在新打开一个SESSION 2窗口，对数据库进行操作。
SESSION 2 >create table t_test as select * from dba_objects; Table created. SESSION 2 >insert into t_test select * from t_test; 9567 rows created. SESSION 2 >/ 19134 rows created. SESSION 2 >/ 38268 rows created. SESSION 2 >/ 76536 rows created. SESSION 2 >/ 153072 rows created. SESSION 2 >/ 306144 rows created. SESSION 2 >/ 612288 rows created. SESSION 2 >/ 1224576 rows created. SESSION 2 >/ 2449152 rows created. SESSION 2 >commit;
新开一个RMAN窗口，删除所以归档日志。
RMAN> list archivelog all; List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - --------- ---- 46 1 50 A 19-AUG-14 /home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_50_9z5kcmrk_.arc 47 1 51 A 19-AUG-14 /home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_51_9z5kcqz1_.arc 48 1 52 A 19-AUG-14 /home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_52_9z5kcwp9_.arc 49 1 53 A 19-AUG-14 /home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_53_9z5kd2tk_.arc 50 1 54 A 19-AUG-14 /home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_54_9z5kd8tw_.arc RMAN> delete archivelog all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=271 devtype=DISK List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - --------- ---- 46 1 50 A 19-AUG-14 /home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_50_9z5kcmrk_.arc 47 1 51 A 19-AUG-14 /home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_51_9z5kcqz1_.arc 48 1 52 A 19-AUG-14 /home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_52_9z5kcwp9_.arc 49 1 53 A 19-AUG-14 /home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_53_9z5kd2tk_.arc 50 1 54 A 19-AUG-14 /home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_54_9z5kd8tw_.arc Do you really want to delete the above objects (enter YES or NO)? yes deleted archive log archive log filename=/home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_50_9z5kcmrk_.arc recid=46 stamp=856006388 deleted archive log archive log filename=/home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_51_9z5kcqz1_.arc recid=47 stamp=856006393 deleted archive log archive log filename=/home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_52_9z5kcwp9_.arc recid=48 stamp=856006400 deleted archive log archive log filename=/home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_53_9z5kd2tk_.arc recid=49 stamp=856006405 deleted archive log archive log filename=/home/oracle/flash/PROD/archivelog/2014_08_19/o1_mf_1_54_9z5kd8tw_.arc recid=50 stamp=856006410 Deleted 5 objects
SESSION 1窗口关闭USERS表空间热备。
SESSION 1 >alter tablespace users end backup; Tablespace altered.
查看相应数据文件大小。
SESSION 1 >! du -sh /u01/app/oracle/oradata/PROD/disk1/users01.dbf 482M /u01/app/oracle/oradata/PROD/disk1/users01.dbf
重启数据库。
SESSION 1 >alter system checkpoint; System altered. SESSION 1 >startup force ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1219184 bytes Variable Size 79693200 bytes Database Buffers 230686720 bytes Redo Buffers 2973696 bytes Database mounted. Database opened.
查询数据是否正常。
SESSION 1 >conn dbdream/dbdream Connected. SESSION 1 >select count(*) from t_test; COUNT(*) ---------- 4898304
经上述测试，数据文件从2M增长到482M，说明在热备时并不会锁定数据文件，而是数据文件可以正常写入的。删除归档日志，可以正常停止热备，说明这期间产生的日志是对热备产生的备份文件恢复时所用，对数据库本身并没有影响，关闭热备时，热备的表空间对应的数据文件本身并不需要恢复。
我非常肯定的记得，以前绝对有老师说过，热备的时候锁定数据文件，也肯定以前绝对是看过这么写的书和网上的资料，我也一直都是这么理解的，直到群友有争议，进行这个测试。
网友分享的文章对这个问题做了详细的解释，原文连接http://www.bluegecko.net/oracle/oracle-tablespace-hot-backup-mode-revisited/。该作者也解释了热备的原理：
DBWn checkpoints the tablespace (writes out all dirty blocks as of a given SCN) CKPT stops updating the Checkpoint SCN field in the datafile headers and begins updating the Hot Backup Checkpoint SCN field instead LGWR begins logging full images of changed blocks the first time a block is changed after being written by DBWn
DBWN进程要写出内存中的脏数据到数据文件，但是从告警日志来看，这时并没有做检查点操作。CKPT进程停止更新数据文件头的SCN信息。LGWR进程将变化的数据块完整镜像到日志文件。下面是告警日志记录的相关信息。
Tue Aug 19 15:11:46 2014 alter tablespace users begin backup Tue Aug 19 15:11:46 2014 Completed: alter tablespace users begin backup Tue Aug 19 15:13:30 2014 Beginning log switch checkpoint up to RBA [0x39.2.10], SCN: 2439095 Thread 1 advanced to log sequence 57 Current log# 3 seq# 46 mem# 0: /u01/app/oracle/oradata/PROD/disk1/redo03.log
可见，在触发热备时，并没有相关的检查点信息发生。
总结：在热备时，不会锁定数据文件。热备时，检查点进程会冻结数据文件的SCN，使之不再变化，结束热备时，检查点进程会更新数据文件头部的SCN。日志写进程会记录数据块的镜像，在拷贝数据文件时，由于数据文件没有锁定，拷贝后的数据文件可能是不一致的，一旦需要恢复时，需要使用这部分日志进行恢复，所以开启热备的过程中，redo日志的产生量会明显增加。
tablespace hot backup是否锁定数据文件：等您坐沙发呢！
发表评论
