數(shù)據(jù)庫：oracle 11.2.0.4

系統(tǒng)：centos 7.9

問題描述：將數(shù)據(jù)文件users02.dbf離線時，出現(xiàn)“ORA-01145：除非啟用介質(zhì)恢復(fù)，否則不允許立即脫機”告警，如下所示：

SQL> alter database datafile 5 offline;

alter database datafile 5 offline

*

ERROR at line 1:

ORA-01145: offline immediate disallowedunless media recovery enabled

SQL> selectd.tablespace_name,v.file#,d.file_name,d.bytes/1024/1024,v.status,v.enabled,d.autoextensible,d.increment_byfrom dba_data_files d,v$datafile v where d.file_id=v.file#

TABLESPACE_NAME FILE# FILE_NAME D.BYTES/1024/1024 STATUS ENABLED AUT INCREMENT_BY

--------------- ------------------------------------------------------------ ----------------- ----------------- --- ------------

USERS 4/u01/app/oracle/oradata/leodb/users01.dbf 5 ONLINE READ WRITE YES 160

UNDOTBS1 3/u01/app/oracle/oradata/leodb/undotbs01.dbf 70 ONLINE READ WRITE YES 640

SYSAUX 2 /u01/app/oracle/oradata/leodb/sysaux01.dbf 490 ONLINE READ WRITE YES 1280

SYSTEM 1/u01/app/oracle/oradata/leodb/system01.dbf 740 SYSTEM READ WRITE YES 1280

USERS 5 /u01/app/oracle/oradata/leodb/users02.dbf 10 ONLINE READ WRITE YES 1

異常原因：數(shù)據(jù)庫處于非歸檔模式.

解決方案：開啟歸檔，實際操作如下：

SQL> select name,log_mode fromv$database;

NAME LOG_MODE

--------------------------------------------------------------

LEODB NOARCHIVELOG

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3006406656 bytes

Fixed Size 2257032 bytes

Variable Size 704647032 bytes

Database Buffers 2281701376 bytes

Redo Buffers 17801216 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

SQL> alter system setlog_archive_dest_1='location=/u01/app/oracle/archivelog/';

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archivelog/

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

SQL> alter database open;

Database altered.

SQL> alter database datafile 5 offline;

Database altered.