alter database offline 與 alter database offline drop效果比對

xz43發表於2014-01-26
如果datafile資料檔案被誤刪除,導致資料庫不能正常open,這時,就需要把該資料檔案先offline,或者恢復該資料檔案。
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: 無法標識/鎖定資料檔案 3 - 請參閱 DBWR 跟蹤檔案
ORA-01110: 資料檔案 3: '/app/oradata/datafile/TEST02.dbf'

SQL> alter database datafile '/app/oradata/datafile/TEST02.dbf' offline drop;


Database altered.


SQL> alter database open;


Database altered.

接下來,就研究一下 offline 與 offline drop的異動。

先在歸檔模式下測試
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /app/archivelog/logfile
Oldest online log sequence     ....
Next log sequence to archive   ....
Current log sequence           ....


SQL> select name,status from v$datafile;


NAME                                               STATUS
-------------------------------------------------- --------------
...
/app/oradata/datafile/TEST02.dbf                   OFFLINE
...

可以看到,此時的這個資料檔案的狀態為OFFLINE。

首先,新增測試用的資料檔案 TEST03.dbf

SQL> alter tablespace test01 add datafile '/app/oradata/datafile/TEST03.dbf' size 10m;


Tablespace altered.

先測試offline方式。

SQL> alter database datafile '/app/oradata/datafile/TEST03.dbf' offline;


Database altered.


SQL> select name,status from v$datafile;


NAME                                               STATUS
-------------------------------------------------- --------------
...
/app/oradata/datafile/TEST02.dbf                   OFFLINE
/app/oradata/datafile/TEST03.dbf                   RECOVER
...

此時資料檔案的狀態為RECOVER,恢復該資料檔案。
SQL> alter database recover datafile '/app/oradata/datafile/TEST03.dbf';


Database altered.


SQL> alter database datafile '/app/oradata/datafile/TEST03.dbf' online;


Database altered.


SQL> select name,status from v$datafile;


NAME                                               STATUS
-------------------------------------------------- --------------
...
/app/oradata/datafile/TEST02.dbf                   OFFLINE
/app/oradata/datafile/TEST03.dbf                   ONLINE
...

接下來,測試 offline drop 方式。

SQL> alter database datafile '/app/oradata/datafile/TEST03.dbf' offline drop;


Database altered.


SQL> select name,status from v$datafile;


NAME                                               STATUS
-------------------------------------------------- --------------
...
/app/oradata/datafile/TEST02.dbf                   OFFLINE
/app/oradata/datafile/TEST03.dbf                   RECOVER
...

此時資料檔案的狀態還是RECOVER,恢復該資料檔案。

SQL> alter database recover datafile '/app/oradata/datafile/TEST03.dbf';


Database altered.


SQL> alter database datafile '/app/oradata/datafile/TEST03.dbf' online;


Database altered.


SQL> select name,status from v$datafile;


NAME                                               STATUS
-------------------------------------------------- --------------
...
/app/oradata/datafile/TEST02.dbf                   OFFLINE
/app/oradata/datafile/TEST03.dbf                   ONLINE
...

資料檔案恢復正常。
由上可知,歸檔模式下,offline 與 offline drop選項的作用相同。
下面把資料庫切換到非歸檔模式。
SQL> shutdown immediate

SQL> startup mount

SQL> alter database noarchivelog;

SQL> shutdown immediate

SQL> startup

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
...

首先,新增測試用資料檔案TEST04.dbf

SQL> alter tablespace test01 add datafile '/app/oradata/datafile/TEST04.dbf' size 10m;


Tablespace altered.


SQL> select name,status from v$datafile where name like '%TEST0%';


NAME                                               STATUS
-------------------------------------------------- --------------
/app/oradata/datafile/TEST02.dbf                   OFFLINE
/app/oradata/datafile/TEST01.dbf                   ONLINE
/app/oradata/datafile/TEST03.dbf                   ONLINE
/app/oradata/datafile/TEST04.dbf                   ONLINE


SQL> alter database datafile '/app/oradata/datafile/TEST04.dbf' offline;
alter database datafile '/app/oradata/datafile/TEST04.dbf' offline
*
ERROR at line 1:
ORA-01145: 除非啟用了介質恢復, 否則不允許立即離線




SQL> alter database datafile '/app/oradata/datafile/TEST04.dbf' offline drop;


Database altered.


SQL> select name,status from v$datafile where name like '%TEST0%';


NAME                                               STATUS
-------------------------------------------------- --------------
/app/oradata/datafile/TEST02.dbf                   OFFLINE
/app/oradata/datafile/TEST01.dbf                   ONLINE
/app/oradata/datafile/TEST03.dbf                   ONLINE
/app/oradata/datafile/TEST04.dbf                   RECOVER


SQL> alter database recover datafile '/app/oradata/datafile/TEST04.dbf';


Database altered.


SQL> alter database datafile '/app/oradata/datafile/TEST04.dbf' online;


Database altered.


SQL> select name,status from v$datafile where name like '%TEST0%';


NAME                                               STATUS
-------------------------------------------------- --------------
/app/oradata/datafile/TEST02.dbf                   OFFLINE
/app/oradata/datafile/TEST01.dbf                   ONLINE
/app/oradata/datafile/TEST03.dbf                   ONLINE
/app/oradata/datafile/TEST04.dbf                   ONLINE


可以看到,非歸檔模式下,不允許直接 offline 資料檔案,必須增加 drop 選項。而對於RECOVER狀態的檔案同樣可以恢復回去。

既然datafile都被刪除,是否可以刪除對於的資料檔案記錄咧?

SQL> alter tablespace test01 drop datafile '/app/oradata/datafile/TEST02.dbf';
alter tablespace test01 drop datafile '/app/oradata/datafile/TEST02.dbf'
*
ERROR at line 1:
ORA-03264: 無法刪除本地管理的表空間中的離線資料檔案


可以看到,offline的資料檔案是不允許被刪除的。那再試試刪除online的資料檔案
SQL> select TABLESPACE_NAME ,file_id,ONLINE_STATUS from dba_data_files;


TABLESPACE_NAME                   FILE_ID ONLINE_STATUS
------------------------------ ---------- --------------
...
TEST_DB                                 5 ONLINE
TEST_DB                                 6 ONLINE
...


SQL> alter tablespace TEST_DB drop datafile 5;                              
alter tablespace TEST_DB drop datafile 5
*
ERROR at line 1:
ORA-03263: 無法刪除表空間 TEST_DB 的第一個檔案




SQL> alter tablespace TEST_DB drop datafile 6;
alter tablespace TEST_DB drop datafile 6
*
ERROR at line 1:
ORA-03262: 檔案非空

必須找到位於該資料檔案上的物件(dba_extents),移動到其他表空間後(alter table test move tablespace test01),再刪除。

由此可見,datafile不能輕易被刪除了。

刪除表空間卻簡單:

SQL> drop tablespace test01 including contents and datafiles;


Tablespace dropped.

這樣,表空間相關的資料檔案也一併被刪除了。

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

相關文章