ORA-01274: 无法添加数据文件

Oracle DataGuard主库添加数据文件后，备库无法自动创建，出现报错：ORA-01274: 无法添加数据文件

查看备库alert日志

Sat Feb 06 10:43:03 2021
File #28 added to control file as 'UNNAMED00028' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Errors in file d:\app\administrator\diag\rdbms\rzorcl\orcl\trace\orcl_mrp0_2464.trc:
ORA-01274: 无法添加数据文件 'D:\APP\ADMINISTRATOR\ORADATA\HIS4\TS_HIS404.ORA' - 无法创建文件
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1233404502
Errors in file d:\app\administrator\diag\rdbms\rzorcl\orcl\trace\orcl_mrp0_2464.trc:
ORA-01274: 无法添加数据文件 'D:\APP\ADMINISTRATOR\ORADATA\HIS4\TS_HIS404.ORA' - 无法创建文件

alert日志有明显报错信息：

File #28 added to control file as 'UNNAMED00028' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL

查看standby_file_management

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      %ORACLE_HOME%\RDBMS
standby_file_management              string      MANUAL

解决方法：
手工设置standby_file_management

SQL> alter system set standby_file_management=auto;

系统已更改。

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      %ORACLE_HOME%\RDBMS
standby_file_management              string      AUTO

此时修改standby_file_management参数后，数据文件无法再自动创建了，需要将主库创建的数据文件备份后拷贝至备库，然后在rename

查看主库数据文件：

SQL> select file#,name from v$datafile where file#>=28;

     FILE#
----------
NAME
--------------------------------------------------------------------------------

        28
D:\APP\ADMINISTRATOR\ORADATA\HIS4\TS_HIS404.ORA

        29
D:\APP\ADMINISTRATOR\ORADATA\HIS4\TS_HIS405.ORA

主库添加了2个数据文件，将28号和29号数据文件备份拷贝至备端数据文件目录，备份拷贝过程不做赘述。

查看备库数据文件目录：

SQL> select name from v$dbfile;

NAME
----------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
...
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00028

已选择28行。

查询得知，需将28号29号文件拷贝至D:\APP\ADMINISTRATOR\ORADATA\ORCL目录

拷贝完成后，再rename数据文件

需要先rename 28号数据文件，然后开启mrp，待alert日志报错29号数据文件创建失败后，再rename数据文件。

SQL> alter database rename file 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00028'  to 'D:\APP\ADMINIS
TRATOR\ORADATA\HIS4\TS_HIS04.ORA';
alter database rename file 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00028'  to 'D:\APP\ADMINISTRATO
R\ORADATA\HIS4\TS_HIS404.ORA'
*
第 1 行出现错误:
ORA-01511: 重命名日志/数据文件时出错
ORA-01275: 自动进行备用文件管理时, 不允许进行 RENAME 操作。

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

系统已更改。

SQL> ALTER DATABASE RENAME FILE 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00028' TO 'D:\APP\ADMINIST
RATOR\ORADATA\HIS4\TS_HIS404.ORA';

数据库已更改。

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

系统已更改。

SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=28;

     FILE# NAME
---------- ---------------------------------------------
        28 D:\APP\ADMINISTRATOR\ORADATA\HIS4\TS_HIS404.ORA

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

数据库已更改。

观察alert日志

anaged Standby Recovery not using Real Time Apply
ORA-279 signalled during: ALTER DATABASE RECOVER  STANDBY DATABASE  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000070711_0952354508.0001
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Errors in file d:\app\administrator\diag\rdbms\rzorcl\orcl\trace\orcl_ora_3852.trc:
ORA-01119: 创建数据库文件 'D:\APP\ADMINISTRATOR\ORADATA\HIS4\TS_HIS405.ORA' 时出错
ORA-27038: 所创建的文件已存在
OSD-04010: 指定了 <create> 选项, 但文件已经存在
File #29 added to control file as 'UNNAMED00029'.
Originally created as:
'D:\APP\ADMINISTRATOR\ORADATA\HIS4\TS_HIS405.ORA'
Recovery was unable to create the file as:
'D:\APP\ADMINISTRATOR\ORADATA\HIS4\TS_HIS405.ORA'
Recovery interrupted!

手工rename一下

SQL> ALTER DATABASE RENAME FILE 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00029' TO 'D:\APP\ADMINIST
RATOR\ORADATA\HIS4\TS_HIS405.ORA';
ALTER DATABASE RENAME FILE 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00029' TO 'D:\APP\ADMINISTRATOR
\ORADATA\HIS4\TS_HIS405.ORA'
*
第 1 行出现错误:
ORA-01511: 重命名日志/数据文件时出错
ORA-01275: 自动进行备用文件管理时, 不允许进行 RENAME 操作。


SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

系统已更改。

SQL> ALTER DATABASE RENAME FILE 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00029' TO 'D:\APP\ADMINIST
RATOR\ORADATA\HIS4\TS_HIS405.ORA';

数据库已更改。

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

系统已更改。

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

数据库已更改。

查看alert日志

Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071447_0952354508.0001
Completed: alter database recover managed standby database using current logfile disconnect from session
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071448_0952354508.0001
Tue Mar 16 10:03:46 2021
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071449_0952354508.0001
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071450_0952354508.0001
Tue Mar 16 10:04:00 2021
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071451_0952354508.0001
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071452_0952354508.0001
Tue Mar 16 10:04:15 2021
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071453_0952354508.0001
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071454_0952354508.0001
Tue Mar 16 10:04:34 2021
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071455_0952354508.0001
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071456_0952354508.0001
Tue Mar 16 10:04:48 2021
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071457_0952354508.0001
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071458_0952354508.0001
Tue Mar 16 10:05:03 2021
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071459_0952354508.0001
Media Recovery Log D:\APP\ARCHIVELOG\ARC0000071460_0952354508.0001

归档日志已在应用，问题解决