归档和非归档模式下ORA-01145错误的解决方法(87天)
公众号：杨建荣的学习笔记 · 作者：笔记87天 · 发布：2014-05-29 16:15:51 · 原文链接
总结了一下，在归档和非归档的场景下，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.