ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查看表空间及数据文件的checkpoint信息

查看表空间及数据文件的checkpoint信息

原创 Linux操作系统 作者:myownstars 时间:2011-02-16 15:06:21 0 删除 编辑

查看表空间及数据文件的checkpoint信息

执行begin backup和end backup时各执行一次
SQL> select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME from v$datafile_header d;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     CHECKPOINT_COUNT TABLESPACE_NAME
------------------ ------------------- ---------------- ------------------------------
        6259037158 2011-02-16 10:38:35             1293 SYSTEM
        6259037158 2011-02-16 10:38:35             1293 SYSAUX
        6259037317 2011-02-16 10:40:42             1284 TEST01
        6259037158 2011-02-16 10:38:35              505 UNDOTBS2
        6259037158 2011-02-16 10:38:35              194 NEW_TBS

SQL> alter tablespace test01 begin backup;

Tablespace altered.

SQL> select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME from v$datafile_header d;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     CHECKPOINT_COUNT TABLESPACE_NAME
------------------ ------------------- ---------------- ------------------------------
        6259037158 2011-02-16 10:38:35             1293 SYSTEM
        6259037158 2011-02-16 10:38:35             1293 SYSAUX
        6259039678 2011-02-16 10:42:32             1285 TEST01
        6259037158 2011-02-16 10:38:35              505 UNDOTBS2
        6259037158 2011-02-16 10:38:35              194 NEW_TBS

SQL> alter tablespace test01 end backup;

Tablespace altered.

SQL> select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME from v$datafile_header d;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     CHECKPOINT_COUNT TABLESPACE_NAME
------------------ ------------------- ---------------- ------------------------------
        6259037158 2011-02-16 10:38:35             1293 SYSTEM
        6259037158 2011-02-16 10:38:35             1293 SYSAUX
        6259039678 2011-02-16 10:42:32             1286 TEST01
        6259037158 2011-02-16 10:38:35              505 UNDOTBS2
        6259037158 2011-02-16 10:38:35              194 NEW_TBS

 

执行表空间offline和online也会发生checkpoint
SQL> alter tablespace test01 offline;

Tablespace altered.

SQL> select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME from v$datafile_header d;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     CHECKPOINT_COUNT TABLESPACE_NAME
------------------ ------------------- ---------------- ------------------------------
        6259037158 2011-02-16 10:38:35             1293 SYSTEM
        6259037158 2011-02-16 10:38:35             1293 SYSAUX
                 0                                    0
        6259037158 2011-02-16 10:38:35             1281 TEST02
        6259037158 2011-02-16 10:38:35              505 UNDOTBS2
        6259037158 2011-02-16 10:38:35              194 NEW_TBS

 

SQL> alter tablespace test01 online;

Tablespace altered.

SQL> select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME from v$datafile_header d;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     CHECKPOINT_COUNT TABLESPACE_NAME
------------------ ------------------- ---------------- ------------------------------
        6259037158 2011-02-16 10:38:35             1293 SYSTEM
        6259037158 2011-02-16 10:38:35             1293 SYSAUX
        6259039907 2011-02-16 10:45:11             1288 TEST01
        6259037158 2011-02-16 10:38:35              505 UNDOTBS2
        6259037158 2011-02-16 10:38:35              194 NEW_TBS

 

执行数据文件的offline和online,该数据文件同样会执行检查点
SQL> select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME,d.NAME from v$datafile_header d;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     CHECKPOINT_COUNT TABLESPACE_NAME                NAME
------------------ ------------------- ---------------- ------------------------------ --------------------------------------------------
        6259037158 2011-02-16 10:38:35             1293 SYSTEM                         /data/oracle/oradata/justin/system.dbf
        6259037158 2011-02-16 10:38:35             1293 SYSAUX                         /data/oracle/oradata/justin/sysaux.dbf
        6259040985 2011-02-16 10:52:41                2 TEST01                         /data/oracle/oradata/justin/test001.dbf
        6259039907 2011-02-16 10:45:11             1288 TEST01                         /data/oracle/oradata/justin/test01.dbf
        6259037158 2011-02-16 10:38:35              505 UNDOTBS2                       /data/oracle/oradata/justin/undotbs02.dbf
        6259037158 2011-02-16 10:38:35              194 NEW_TBS                        /data/oracle/oradata/new_tbs.dbf


SQL> alter database datafile '/data/oracle/oradata/justin/test001.dbf' offline;

Database altered.

SQL> select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME,d.NAME from v$datafile_header d;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     CHECKPOINT_COUNT TABLESPACE_NAME                NAME
------------------ ------------------- ---------------- ------------------------------ --------------------------------------------------
        6259037158 2011-02-16 10:38:35             1293 SYSTEM                         /data/oracle/oradata/justin/system.dbf
        6259037158 2011-02-16 10:38:35             1293 SYSAUX                         /data/oracle/oradata/justin/sysaux.dbf
        6259040985 2011-02-16 10:52:41                2 TEST01                         /data/oracle/oradata/justin/test001.dbf
        6259039907 2011-02-16 10:45:11             1288 TEST01                         /data/oracle/oradata/justin/test01.dbf
        6259037158 2011-02-16 10:38:35              505 UNDOTBS2                       /data/oracle/oradata/justin/undotbs02.dbf
        6259037158 2011-02-16 10:38:35              194 NEW_TBS                        /data/oracle/oradata/new_tbs.dbf

 

SQL> alter database datafile '/data/oracle/oradata/justin/test001.dbf' online;
alter database datafile '/data/oracle/oradata/justin/test001.dbf' online
*
ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/data/oracle/oradata/justin/test001.dbf'


SQL> recover datafile '/data/oracle/oradata/justin/test001.dbf';
Media recovery complete.
SQL> alter database datafile '/data/oracle/oradata/justin/test001.dbf' online;

Database altered.

SQL> select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME,d.NAME from v$datafile_header d;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     CHECKPOINT_COUNT TABLESPACE_NAME                NAME
------------------ ------------------- ---------------- ------------------------------ --------------------------------------------------
        6259037158 2011-02-16 10:38:35             1293 SYSTEM                         /data/oracle/oradata/justin/system.dbf
        6259037158 2011-02-16 10:38:35             1293 SYSAUX                         /data/oracle/oradata/justin/sysaux.dbf
        6259041453 2011-02-16 10:55:24                4 TEST01                         /data/oracle/oradata/justin/test001.dbf
        6259039907 2011-02-16 10:45:11             1288 TEST01                         /data/oracle/oradata/justin/test01.dbf
        6259037158 2011-02-16 10:38:35              505 UNDOTBS2                       /data/oracle/oradata/justin/undotbs02.dbf
        6259037158 2011-02-16 10:38:35              194 NEW_TBS                        /data/oracle/oradata/new_tbs.dbf

 

执行alter system suspend/resume, 则不会发生检查点

SQL> alter system suspend
  2  ;

System altered.

SQL> select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME,d.NAME from v$datafile_header d;
select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME,d.NAME from v$datafile_header d
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> alter system resume;

System altered.

SQL> select d.CHECKPOINT_CHANGE#,d.CHECKPOINT_TIME,d.CHECKPOINT_COUNT,d.TABLESPACE_NAME,d.NAME from v$datafile_header d;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     CHECKPOINT_COUNT TABLESPACE_NAME                NAME
------------------ ------------------- ---------------- ------------------------------ --------------------------------------------------
        6259037158 2011-02-16 10:38:35             1293 SYSTEM                         /data/oracle/oradata/justin/system.dbf
        6259037158 2011-02-16 10:38:35             1293 SYSAUX                         /data/oracle/oradata/justin/sysaux.dbf
        6259041453 2011-02-16 10:55:24                4 TEST01                         /data/oracle/oradata/justin/test001.dbf
        6259039907 2011-02-16 10:45:11             1288 TEST01                         /data/oracle/oradata/justin/test01.dbf
        6259037158 2011-02-16 10:38:35              505 UNDOTBS2                       /data/oracle/oradata/justin/undotbs02.dbf
        6259037158 2011-02-16 10:38:35              194 NEW_TBS                        /data/oracle/oradata/new_tbs.dbf

 

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

下一篇: 11g下编译使用BBED
请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3112346