查看表空间及数据文件的checkpoint信息
查看表空间及数据文件的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博客 ” ，链接：https://blog.itpub.net/15480802/viewspace-687334/ ，如需转载，请注明出处，否则将追究法律责任。
