歸檔和非歸檔模式下ORA-01145錯誤的解決方法

總結了一下，在歸檔和非歸檔的場景下，ora-01145這個錯誤可能有如下三種情況：

1.off line tablespace

--在非歸檔模式下嘗試ofline 資料檔案

SQL> alter tablespace tools offline immediate;
alter tablespace tools offline immediate
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

 

以上操作需要在歸檔模式下才可以。

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     244
Next log sequence to archive   246
Current log sequence           246
SQL> alter tablespace tools offline immediate;

Tablespace altered.

--在非歸檔模式下可以採用如下
SQL> alter tablespace tools offline;

Tablespace altered.

 

2.offline datafile

3.offline drop

誤刪資料檔案後，這個需要 alter database datafile xxxxx offline drop;

 

 

 

--關於測試環境

庫為10g 10.2.0.1.0  非歸檔模式

 

表空間情況如下：

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
TEMPTS1                        ONLINE
TBS1                           ONLINE
TBS3                           ONLINE
TEMP1                          ONLINE
TEMP2                          ONLINE
TOOLS                          ONLINE
OMF1                           ONLINE
UNDOTBS2                       ONLINE
資料檔案情況如下：

SQL> select name,status from v$datafile;

NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/disk_4/system01.dbf
SYSTEM

/u01/app/oracle/oradata/disk_4/sysaux01.dbf
ONLINE

/u01/app/oracle/oradata/disk_1/tbs01.dbf
ONLINE

NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/disk_2/tbs02.dbf
ONLINE

/u01/app/oracle/oradata/disk_3/tbs03.dbf
ONLINE

/u01/app/oracle/oradata/disk_3/tbs3.dbf
ONLINE

NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/disk_4/undotbs2.dbf
ONLINE

/u01/app/oracle/oradata/disk_5/tools.dbf
ONLINE

/u01/app/oracle/oradata/disk_5/PODD/datafile/o1_mf_omf1_88ygncsw_.dbf
ONLINE

9 rows selected.
--我嘗試將資料檔案tbs01給offline掉

SQL> alter database datafile '/u01/app/oracle/oradata/disk_1/tbs01.dbf' offline;
alter database datafile '/u01/app/oracle/oradata/disk_1/tbs01.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

 

但是操作失敗，我記得平時都好好的，怎麼就不行了。

--檢視錯誤解析

SQL> !oerr ora 01145
01145, 00000, "offline immediate disallowed unless media recovery enabled"
// *Cause: ALTER TABLESPACE ... OFFLINE IMMEDIATE or ALTER DATABASE DATAFILE
//         ... OFFLINE is only allowed if database is in ARCHIVELOG mode.
// *Action:Take tablespace offline normally or shutdown abort. Reconsider your
//         backup strategy. You could do this if you were archiving your logs.

 

我這個庫是非歸檔的，

 

SQL> alter tablespace tbs1 offline;

Tablespace altered.

SQL> alter database datafile '/u01/app/oracle/oradata/disk_1/tbs01.dbf' offline;

Database altered.