oracle 归档试图,归档和非归档模式下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

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

--在归档模式下，直接offline数据文件就可以

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

Database altered.

--而在非归档模式下，需要先把表空间给offline掉，然后才能offline数据文件，可以参见如下的实例。

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.