那些操作會發生區域性檢查點(Partial checkpoint)!

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

上面2種操作會發生區域性檢查點！使相應的dirty buffer被寫入和tablespace對應的datafile中

[@more@]

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>