standby_file_management参数为MANUAL导致添加数据文件错误

症状:
standby_file_management 在MANUAL模式下添加数据文件报错处理:

1.standby_file_management为MANUAL,主库添加数据文件,从库发现如下报错:

Errors in file /u01/app/oracle/diag/rdbms/slnngkdg/slnngkdg/trace/slnngkdg_pr00_16209.trc:
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00011'
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00011'


2.查看从库的复制进程发现没有启动
select process,status from v$managed_standby;

将standby_file_management参数设置为auto
alter system set standby_file_management = auto;现在修改为auto,针对在manual添加的数据文件无济于事,修改后添加的数据文件操作才有效
再次应用日志:
alter database recover managed standby database using current logfile disconnect from session;
发现还是不行,查看该数据文件
SQL> set linesize 100;
SQL> Column Name format a60;
SQL> Select Name From v$datafile t;

NAME
------------------------------------------------------------
/u01/app/oracle/oradata/slnngkdg/system01.dbf
/u01/app/oracle/oradata/slnngkdg/sysaux01.dbf
/u01/app/oracle/oradata/slnngkdg/undotbs01.dbf
/u01/app/oracle/oradata/slnngkdg/users01.dbf
/u01/app/oracle/oradata/slnngkdg/tps_data01.dbf
/u01/app/oracle/oradata/slnngkdg/mrkt.dbf
/u01/app/oracle/oradata/slnngkdg/tps_data02.dbf
/u01/app/oracle/oradata/slnngkdg/tps_data03.dbf
/u01/app/oracle/oradata/slnngkdg/tps_data04.dbf
/u01/app/oracle/oradata/slnngkdg/tps_data05.dbf
/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00011


11 rows selected
发现有UNNAMED00011这个文件,该文件就是在主库上创建,没有同步到从库的文件,该文件是物理不存在的,只是数据库记录下来而已


3.创建文件
alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00011' as '/u01/app/oracle/oradata/tps_data06.dbf';
使用方法为:
alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00011' as '备库数据文件存放的路径/主库查出来的数据文件';


SQL> alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00011' as '/u01/app/oracle/oradata/tps_data06.dbf';
alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00011' as '/u01/app/oracle/oradata/tps_data06.dbf'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management is automatic.

再次修改为MANUAL
alter system set standby_file_management = MANUAL;

数据库在打开模式下操作
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00011' as '/u01/app/oracle/oradata/tps_data06.dbf';

Database altered.

4.将standby_file_management修改为auto
alter system set standby_file_management = auto;

5.再次启用日志
alter database recover managed standby database using current logfile disconnect from session;