Partial checkpoint
– Dirty buffers belonging to the tablespace
– ALTER TABLESPACE BEGIN BACKUP
– ALTER TABLESPACE tablespace OFFLINE NORMAL

上面2种操作会发生局部检查点！使相应的dirty buffer被写入和tablespace对应的datafile中

SQL> select name , checkpoint_change# from v$datafile;

NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB 806682
F

E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 806682

1.begin backup会使和该表空间对应的dirty buffer写入相应的datafile

SQL> alter tablespace users begin backup;

表空间已更改。

SQL> select name , checkpoint_change# from v$datafile;

NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB 806682
F

E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 808050

SQL> alter tablespace users end backup;

表空间已更改。

SQL> select name , checkpoint_change# from v$datafile;

NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB 806682
F

E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 808050

2.offline=offline normal操作也会发生局部检查点，使和该表空间对应的dirty buffer写入相应的datafile

SQL> alter tablespace users offline;

表空间已更改。

SQL> select name , checkpoint_change# from v$datafile;

NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB 806682
F

E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 808076

SQL> alter tablespace users online;

表空间已更改。

SQL> select name , checkpoint_change# from v$datafile;

NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB 806682
F

E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 808094

SQL> alter tablespace users offline normal;

表空间已更改。

SQL> select name , checkpoint_change# from v$datafile;

NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB 806682
F

E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 808118

SQL> alter tablespace users online;

表空间已更改。

SQL> select name , checkpoint_change# from v$datafile;

NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB 806682
F

E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 808131

3. offline immediate不会发生局部检查点，所以当online时对应的datafile要求recovery

SQL> alter tablespace users offline immediate;

表空间已更改。

SQL> select name , checkpoint_change# from v$datafile;

NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB 806682
F

E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 806682
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 808131

SQL> alter tablespace users online;
alter tablespace users online
*
第 1 行出现错误:
ORA-01113: 文件 4 需要介质恢复
ORA-01110: 数据文件 4: 'E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF'

SQL> recover datafile 4;
完成介质恢复。
SQL> alter tablespace users online;

表空间已更改。

SQL>