tablespace hot backup是否锁定数据文件

     昨天群里讨论了关于表空间热备是否锁定数据文件的话题，经测试，之前理解的完全是错误的，看来书上写的并不都是对的。

     下面演示实验过程，测试环境OEL4.8，Oracle 10.2.0.1.0。先打开检查点信息打印到告警日志参数，以便后续观察。

[AppleScript] 纯文本查看 复制代码

SESSION 1 >alter system set log_checkpoints_to_alert=true;

System altered.

     新开一个终端，创建测试表空间和测试用户。

[AppleScript] 纯文本查看 复制代码

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表空间热备。

[AppleScript] 纯文本查看 复制代码

SESSION 1 >alter tablespace users begin backup;

Tablespace altered.

     在新打开一个SESSION 2窗口，对数据库进行操作。

[AppleScript] 纯文本查看 复制代码

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窗口，删除所以归档日志。

[AppleScript] 纯文本查看 复制代码

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表空间热备。

[AppleScript] 纯文本查看 复制代码

SESSION 1 >alter tablespace users end backup;

Tablespace altered.

     查看相应数据文件大小。

[AppleScript] 纯文本查看 复制代码

SESSION 1 >!  du -sh /u01/app/oracle/oradata/PROD/disk1/users01.dbf
482M    /u01/app/oracle/oradata/PROD/disk1/users01.dbf

     重启数据库。

[AppleScript] 纯文本查看 复制代码

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.

     查询数据是否正常。

[AppleScript] 纯文本查看 复制代码

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/。该作者也解释了热备的原理：

[AppleScript] 纯文本查看 复制代码

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进程将变化的数据块完整镜像到日志文件。下面是告警日志记录的相关信息。

[AppleScript] 纯文本查看 复制代码

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日志的产生量会明显增加。

来自群组: Oracle DBA交流

白学了

看来，实践是最好的老师哈