offline tablespace 的幾種方式 (轉)

zhouwf0726發表於2019-03-22
offline tablespace 的幾種方式
想要offline tablespace有以下三種方式：
OFFLINE {NORMAL | TEMPORARY | IMMEDIATE} 其中，normal是預設的。
下面通過測試說明幾種情況的異同：
----offline normal:
idle>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
------------------ ------------ ---------------
1416841678 1416800082
idle>alter tablespace test_increment offline normal;
表空間已更改。
idle>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
------------------ ------------ ---------------
1416841750 1416841750 1416800082
----offline normal，tablespace內所有的資料檔案上觸發checkpoint。 checkpoint_change#增加。
idle>alter tablespace test_increment online;
表空間已更改。
idle>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
------------------ ------------ ---------------
1416841783 1416841750
----online時，不需要media recovery，同時tablespace內所有的資料檔案上再次觸發checkpoint。 checkpoint_change#增加。
----offline temporary:
idle>alter tablespace test_increment offline temporary;
表空間已更改。
idle>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
------------------ ------------ ---------------
1416841826 1416841826 1416841750
idle>alter tablespace test_increment online;
表空間已更改。
idle>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
------------------ ------------ ---------------
1416841836 1416841750
----證明了文件中的如下說法：
----If no files are offline, but you use the temporary option, media recovery is not required to bring the tablespace back online.
----offline immediate:
idle>alter tablespace test_increment offline immediate;
表空間已更改。
idle>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
------------------ ------------ ---------------
1416841836 1416841875 1416841750
----offline immediate，tablespace內所有的資料檔案上不觸發checkpoint。 checkpoint_change#不變。
idle>alter tablespace test_increment online;
alter tablespace test_increment online
*
ERROR 位於第 1 行:
ORA-01113: ?? 3 ??????
ORA-01110: ???? 3: 'D:ORA92ORADATAORACLETEST_INCREMENT01.DBF'
----將tablespace online 時需要media recovery。
idle>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
------------------ ------------ ---------------
1416841836 1416841875 1416841750
idle>recover datafile 3;
完成介質恢復。
idle>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
------------------ ------------ ---------------
1416841875 1416841875 1416841750
idle>alter tablespace test_increment online;
表空間已更改。
idle>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
------------------ ------------ ---------------
1416841912 1416841750
----online時，tablespace內所有的資料檔案上再次觸發checkpoint。 checkpoint_change#增加。
---為了更清楚的說明offline temporary，我們新加一資料檔案。
idle>alter tablespace test_increment add datafile
2 'd:ora92oradataoracletest_increment02.dbf' size 5m;
表空間已更改。
idle>select file#,checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ------------ ---------------
3 1416842065 1416842037
4 1416842189 0
idle>alter database datafile 3 offline;
資料庫已更改。
idle>select file#,checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ------------ ---------------
3 1416842065 1416842321 1416842037
4 1416842189 0
---單個的offline資料檔案，checkpoint_change#不變。
idle>alter tablespace test_increment offline temporary;
表空間已更改。
idle>select file#,checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ------------ ---------------
3 1416842065 1416842321 1416842037
4 1416842351 1416842351 0
---對照之前的checkpoint，可以發現：offline temporary只對那些online的資料檔案進行checkpoint，而且在將tablespace online 的時候，那些進行過checkpoint的資料檔案將不需要media recovery（下面可以看出）。
idle>alter session set nls_language=american;
Session altered.
idle>alter tablespace test_increment online;
alter tablespace test_increment online
*
ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: 'D:ORA92ORADATAORACLETEST_INCREMENT01.DBF'
----將tablespace online 的時候，那些進行過checkpoint的資料檔案將不需要media recovery
idle>select file#,checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ------------ ---------------
3 1416842065 1416842321 1416842037
4 1416842351 1416842351 0
idle>recover datafile 3;
Media recovery complete.
idle>select file#,checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ------------ ---------------
3 1416842321 1416842321 1416842037
4 1416842351 1416842351 0
idle>alter tablespace test_increment online;
Tablespace altered.
idle>select file#,checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ------------ ---------------
3 1416842448 1416842037
4 1416842448 0
Oracle 文件的解釋：

Taking Tablespaces Offline

You may want to take a tablespace offline for any of the following reasons:

  • To make a portion of the database unavailable while allowing normal access to the remainder of the database
  • To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)
  • To make an application and its group of tables temporarily unavailable while updating or maintaining the application

When a tablespace is taken offline, Oracle takes all the associated files offline. The SYSTEM tablespace can never be taken offline.

You can specify any of the following options when taking a tablespace offline:

OptionDescription

NORMAL

A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace can be currently offline as the result of a write error. When OFFLINE NORMAL is specified, Oracle takes a checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL is the default.

TEMPORARY

A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When OFFLINE TEMPORARY is specified, Oracle takes offline the datafiles that are not already offline, checkpointing them as it does so.

If no files are offline, but you use the temporary option, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online.

IMMEDIATE

A tablespace can be taken offline immediately, without Oracle taking a checkpoint on any of the datafiles. When you specify OFFLINE IMMEDIATE, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode.

FOR RECOVER

Takes the database tablespaces in the recovery set offline for tablespace point-in-time recovery. For additional information, see Oracle9i User-Managed Backup and Recovery Guide.

Caution:

If you must take a tablespace offline, use the NORMAL option (the default) if possible. This guarantees that the tablespace will not require recovery to come back online. It will not require recovery, even if after incomplete recovery you reset the redo log sequence using an ALTER DATABASE OPEN RESETLOGS statement.

Specify TEMPORARY only when you cannot take the tablespace offline normally. In this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Specify IMMEDIATE only after trying both the normal and temporary options.

The following example takes the users tablespace offline normally:

ALTER TABLESPACE users OFFLINE NORMAL;

Before taking an online tablespace offline, consider taking the following actions:

  • Verify that the tablespace contains no active rollback segments. Such a tablespace cannot be taken offline.
  • You may want to alter the tablespace allocation of any users who have been assigned the tablespace as either a default or temporary tablespace. This is advisable because they will not be able to access objects or sort areas in the tablespace while it is offline.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242083/，如需轉載，請註明出處，否則將追究法律責任。

相關文章