ITPub博客

首页 > 数据库 > Oracle > [20141202]改变文件大小与检查点.txt

[20141202]改变文件大小与检查点.txt

原创 Oracle 作者:lfree 时间:2014-12-02 08:58:09 0 删除 编辑

[20141202]改变文件大小与检查点.txt

Resize datafile会触发一个文件级检查点,真的是这样吗?自己做一个测试:

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

$ rlrman
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 2 08:47:25 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST (DBID=2071943378)
RMAN> report schema ;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    770      SYSTEM               ***     /u01/app/oracle11g/oradata/test/system01.dbf
2    920      SYSAUX               ***     /u01/app/oracle11g/oradata/test/sysaux01.dbf
3    718      UNDOTBS1             ***     /u01/app/oracle11g/oradata/test/undotbs01.dbf
4    768      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   1        UNDOTBS2             ***     /u01/app/oracle11g/oradata/test/undotbs02.dbf
11   101      TEST16K              ***     /u01/app/oracle11g/oradata/test/test16k01.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          11991495125                    7           3011113647 ONLINE             856621853
                   2          11991495125                 2140           3011113647 ONLINE             856621847
                   3          11991495125           3241444492           3011113647 ONLINE             856621848
                   4          11991495125                17993           3011113647 ONLINE             856621853
                   5          11991495125               973735           3011113647 ONLINE             856621850
                   6          11991495125              1412559           3011113647 ONLINE             856621803
                   7          11991495125              4383251           3011113647 ONLINE             856621845
                   8          11991495125             13169364           3011113647 ONLINE             856621847
                   9          11991495125           3223747107           3011113647 ONLINE             856621845
                  10          11991495125           3223804181           3011113647 ONLINE             856621845
                  11          11991495125          11673111577           3011113647 ONLINE                   329

11 rows selected.

--修改file#11 ,datafile='/u01/app/oracle11g/oradata/test/test16k01.dbf',大小现在101M。

SCOTT@test> alter database datafile '/u01/app/oracle11g/oradata/test/test16k01.dbf' resize 100m;
Database 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          11991495125                    7           3011113647 ONLINE             856621853
                   2          11991495125                 2140           3011113647 ONLINE             856621847
                   3          11991495125           3241444492           3011113647 ONLINE             856621848
                   4          11991495125                17993           3011113647 ONLINE             856621853
                   5          11991495125               973735           3011113647 ONLINE             856621850
                   6          11991495125              1412559           3011113647 ONLINE             856621803
                   7          11991495125              4383251           3011113647 ONLINE             856621845
                   8          11991495125             13169364           3011113647 ONLINE             856621847
                   9          11991495125           3223747107           3011113647 ONLINE             856621845
                  10          11991495125           3223804181           3011113647 ONLINE             856621845
                  11          11991495750          11673111577           3011113647 ONLINE                   330
11 rows selected.

--可以发现CHECKPOINT_CHANGE#从11991495125=>11991495750.CHECKPOINT_COUNT增加1.

--但是如果增加文件大小呢?

SCOTT@test> alter database datafile '/u01/app/oracle11g/oradata/test/test16k01.dbf' resize 102m;
Database 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          11991495125                    7           3011113647 ONLINE             856621853
                   2          11991495125                 2140           3011113647 ONLINE             856621847
                   3          11991495125           3241444492           3011113647 ONLINE             856621848
                   4          11991495125                17993           3011113647 ONLINE             856621853
                   5          11991495125               973735           3011113647 ONLINE             856621850
                   6          11991495125              1412559           3011113647 ONLINE             856621803
                   7          11991495125              4383251           3011113647 ONLINE             856621845
                   8          11991495125             13169364           3011113647 ONLINE             856621847
                   9          11991495125           3223747107           3011113647 ONLINE             856621845
                  10          11991495125           3223804181           3011113647 ONLINE             856621845
                  11          11991495750          11673111577           3011113647 ONLINE                   330

11 rows selected.

--可以发现CHECKPOINT_CHANGE#保持不变.CHECKPOINT_COUNT也没有变化.

SCOTT@test> host ls -l /u01/app/oracle11g/oradata/test/test16k01.dbf
-rw-r-----  1 oracle11g oinstall 106971136 Dec  2 08:50 /u01/app/oracle11g/oradata/test/test16k01.dbf

-- 102*1024*1024+16384=106971136 ,大小一致。
-- 说明:数据文件大小是定义的值+1个数据块,这个数据文件块大小是16K的。

总结:
1.数据文件改小,才会触发一个文件级检查点.
2.数据文件改大,不会触发一个文件级检查点.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1353454/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2703
  • 访问量
    6492790