非归档模式下ORA-01145错误的解决方法

jiawang

测试环境        
库为19C   非归档模式

[oracle@strong ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 22 13:35:18 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/EXAMPLE_20230822134105.dbf

6 rows selected.

尝试把EXAMPLE_20230822134105文件offline
SQL>  alter database datafile '/u01/app/oracle/oradata/orcl/EXAMPLE_20230822134105.dbf' offline;
alter database datafile '/u01/app/oracle/oradata/orcl/EXAMPLE_20230822134105.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

查看归档情况

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/archivelog
Oldest online log sequence     117
Current log sequence           119
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size                  8895920 bytes
Variable Size             771751936 bytes
Database Buffers          419430400 bytes
Redo Buffers                7880704 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL>  alter database datafile 6 offline;

Database altered.

复制EXAMPLE_20230822134105.dbf文件到EXAMPLE02.dbf文件
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/EXAMPLE_20230822134105.dbf' to '/u01/app/oracle/oradata/orcl/EXAMPLE02.dbf';

Database altered.

SQL> alter database datafile 6 online;

Database altered.

重启数据库有问题
SQL> startup;
ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size                  8895920 bytes
Variable Size             771751936 bytes
Database Buffers          419430400 bytes
Redo Buffers                7880704 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/EXAMPLE02.dbf'


SQL> recover datafile 6;
Media recovery complete.

SQL> alter database open;
Database altered.

jiawang

12C 以上可以在线使用move修改数据文件名称了
SQL> r
  1* select name,file#,status from v$datafile

NAME                                                    FILE# STATUS
-------------------------------------------------- ---------- -------
/u01/app/oracle/oradata/orcl/system01.dbf                   1 SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf                   2 ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf                  3 ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf                    4 ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf                  5 ONLINE
/u01/app/oracle/oradata/orcl/EXAMPLE02.dbf                 6 ONLINE

6 rows selected.

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/system01.dbf' to '/home/oracle/system.dbf';

Database altered.

SQL> select name,file#,status from v$datafile
  2  ;

NAME                                                    FILE# STATUS
-------------------------------------------------- ---------- -------
/home/oracle/system.dbf                                     1 SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf                   2 ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf                  3 ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf                    4 ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf                  5 ONLINE
/u01/app/oracle/oradata/orcl/EXAMPLE02.dbf                 6 ONLINE

6 rows selected.