oracle数据开启变跟踪性能,【学习笔记】Oracle块修改跟踪特性 深入研究Block Change Tracking特性案例...

天萃荷净

深入研究Oracle新特性数据块修改跟踪特性Block Change Tracking，结合案例理解块修改跟踪特性原理

有一服务器有1T左右的数据，备份策略是(1+2)*2(1全备，2增量备份，备份保留2周期)的备份策略，随便增量备份减少了备份的体积，但是增量备份的时间，基本上和全备无差别，都是要近6小时(包括压缩)。

这里没有完全体现出增量备份的强大之处，因为没有开启块修改跟踪，无论是增量备份还是全备都需要扫描所有的数据块。虽然网上说开启块修改跟踪可能会会触发一些bug，但是我找了下，10.2.0.4以后块修改跟踪还是比较稳定的，所以决定开启块修改跟踪功能，节约增量备份时间，提高系统性能。

一、开启Oracle块修改跟踪

[oracle@node1 bdump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Thu Nov 3 11:13:54 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database enable block change tracking using file

2 '/opt/oracle/oradata/ecp/Oracle_change.trace';

Database altered.

SQL> set long 200

SQL> col filename for a50

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

ENABLED /opt/oracle/oradata/ecp/Oracle_change.trace 11599872

SQL> !/opt/oracle/oradata/ecp/Oracle_change.trace

/bin/bash: /opt/oracle/oradata/ecp/Oracle_change.trace: 权限不够

SQL> !

[oracle@node1 ~]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace

-rw-r—– 1 oracle oinstall 11600384 11-03 11:05 /opt/oracle/oradata/ecp/Oracle_change.trace

[oracle@node1 ~]$ ps -ef|grep ctwr|grep -v grep

oracle 2771 1 0 11:05 00:00:00 ora_ctwr_ecp

[oracle@node1 bdump]$ tail -11 /opt/oracle/admin/ecp/bdump/alert_ecp.log

Thu Nov 03 11:05:47 CST 2011

alter database enable block change tracking using file

'/opt/oracle/oradata/ecp/Oracle_change.trace'

Thu Nov 03 11:05:47 CST 2011

Block change tracking file is current.

Starting background process CTWR

CTWR started with pid=18, OS id=2771

Block change tracking service is active.

Thu Nov 03 11:05:48 CST 2011

Completed: alter database enable block change tracking using file

'/opt/oracle/oradata/ecp/Oracle_change.trace'

通过这些都可以看出来，开启块修改跟踪功能，会启动CTWR进程，并且修改的块号会被记录到指定文件中

二、关闭块改变跟踪

SQL>alter database disable block change tracking;

Database altered.

SQL> col filename for a50

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

DISABLED

SQL> !

[oracle@node1 bdump]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace

ls: /opt/oracle/oradata/ecp/Oracle_change.trace: 没有那个文件或目录

[oracle@node1 bdump]$ ps -ef|grep ctwr|grep -v grep

[oracle@node1 bdump]$ tail -8 /opt/oracle/admin/ecp/bdump/alert_ecp.log

Thu Nov 03 11:14:06 CST 2011

alter database disable block change tracking

Thu Nov 03 11:14:07 CST 2011

Block change tracking service stopping.

Thu Nov 03 11:14:07 CST 2011

Stopping background process CTWR

Deleted file /opt/oracle/oradata/ecp/Oracle_change.trace

Completed: alter database disable block change tracking

通过这些都可以看出来，关闭块修改跟踪功能，会关闭CTWR进程，并且删除跟踪文件(Linux系统会删除，Window不会)

三、块修改跟踪文件重命名

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

ENABLED /opt/oracle/oradata/ecp/Oracle_change.trace 11599872

SQL> alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

2 to '/opt/oracle/oradata/ecp/Oracle_change.trace_new';

alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

*

ERROR at line 1:

ORA-01511: error in renaming log/data files

ORA-19771: cannot rename change tracking file while database is open

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size 2096632 bytes

Variable Size 385876488 bytes

Database Buffers 1207959552 bytes

Redo Buffers 14680064 bytes

Database mounted.

SQL>alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

2 to '/opt/oracle/oradata/ecp/Oracle_change.trace_new';

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

ENABLED /opt/oracle/oradata/ecp/Oracle_change.trace_new 11599872

[oracle@node1 bdump]$ tail -100 /opt/oracle/admin/ecp/bdump/alert_ecp.log |more

Thu Nov 03 11:22:34 CST 2011

alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

to '/opt/oracle/oradata/ecp/Oracle_change.trace_new'

Completed: alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

to '/opt/oracle/oradata/ecp/Oracle_change.trace_new'

Thu Nov 03 11:22:40 CST 2011

alter database open

Thu Nov 03 11:22:40 CST 2011

CHANGE TRACKING is enabled for this database, but the

change tracking file can not be found. Recreating the file.

Change tracking file recreated.

Block change tracking file is current.

[oracle@node1 bdump]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace*

-rw-r—– 1 oracle oinstall 11600384 11-03 11:22 /opt/oracle/oradata/ecp/Oracle_change.trace

-rw-r—– 1 oracle oinstall 11600384 11-03 12:05 /opt/oracle/oradata/ecp/Oracle_change.trace_new

数据库在open状态下不能修改，所以必须把数据库重启至mount状态才能够修改，如果库不能重启，那么可以先关闭原块修改跟踪功能，再开启块修改跟踪功能。

两种方法比较：

1、都会重新建立一个跟踪文件

2、如果是重命名，老的跟踪文件不会自动被删除，需要人工删除

3、推荐使用关闭跟踪功能，然后在重新制定跟踪文件开启跟踪功能

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle块修改跟踪特性 深入研究Block Change Tracking特性案例