【DataGuard】由于备库参数设置不当导致数据文件无法添加的故障分析

本文将描述由于standby_file_management和db_file_name_convert设置不当导致数据文件无法添加的故障处理过程。

1.故障再现
1）主库创建新的表空间tbs_sec1
SQL> create tablespace tbs_sec1 datafile '/u01/app/oracle/oradata/secdb/tbs_sec1.dbf' size 10m;

Tablespace created.

2）手工切换日志
SQL> alter system switch logfile;

System altered.

3）此时在备库alert中记录中便可以查看到如下报错内容
Tue Aug 10 16:16:21 2010
RFS[2]: No standby redo logfiles created
RFS[2]: Archived Log: '/u01/app/oracle/flash_recovery_area/SECDG/archivelog/2010_08_10/o1_mf_1_10_6622ponv_.arc'
Tue Aug 10 16:16:22 2010
Media Recovery Log /u01/app/oracle/flash_recovery_area/SECDG/archivelog/2010_08_10/o1_mf_1_10_6622ponv_.arc
File #6 added to control file as 'UNNAMED00006' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log /u01/app/oracle/flash_recovery_area/SECDG/archivelog/2010_08_10/o1_mf_1_10_6622ponv_.arc
MRP0: Background Media Recovery terminated with error 1274
Tue Aug 10 16:16:22 2010
Errors in file /u01/app/oracle/admin/secdg/bdump/secdg_mrp0_15738.trc:
ORA-01274: cannot add datafile '/u01/app/oracle/oradata/secdb/tbs_sec1.dbf' - file could not be created
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Tue Aug 10 16:16:24 2010
Errors in file /u01/app/oracle/admin/secdg/bdump/secdg_mrp0_15738.trc:
ORA-01274: cannot add datafile '/u01/app/oracle/oradata/secdb/tbs_sec1.dbf' - file could not be created
Tue Aug 10 16:16:24 2010
MRP0: Background Media Recovery process shutdown (secdg)

对应的trace文件中记录的报错内容大同小异。

2.故障原因
问题是由于没有正确设置standby_file_management和db_file_name_convert参数导致的。但此时，在已经出现问题的前提下修改这个参数，已经于事无补。

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/secdb/','/u01/app/oracle/oradata/SECDG/datafile/' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              96470416 bytes
Database Buffers          213909504 bytes
Redo Buffers                2973696 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

此时alert中仍然会记录如下报错信息
Tue Aug 10 16:24:36 2010
alter database recover managed standby database disconnect from session
Tue Aug 10 16:24:36 2010
Attempt to start background Managed Standby Recovery process (secdg)
MRP0 started with pid=23, OS id=16942
Tue Aug 10 16:24:36 2010
MRP0: Background Managed Standby Recovery process started (secdg)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Tue Aug 10 16:24:41 2010
Errors in file /u01/app/oracle/admin/secdg/bdump/secdg_mrp0_16942.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006'
Tue Aug 10 16:24:41 2010
Errors in file /u01/app/oracle/admin/secdg/bdump/secdg_mrp0_16942.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006'
Tue Aug 10 16:24:41 2010
MRP0: Background Media Recovery process shutdown (secdg)
Tue Aug 10 16:24:42 2010
Completed: alter database recover managed standby database disconnect from sessi on

可见，问题依旧！

3.处理方法
在这种故障场景下，我们可以通过调整数据文件的方法在备库端进行处理。

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/SECDG/datafile/o1_mf_system_661zb5rh_.dbf
/u01/app/oracle/oradata/SECDG/datafile/o1_mf_undotbs_661zc97r_.dbf
/u01/app/oracle/oradata/SECDG/datafile/o1_mf_sysaux_661zd3d0_.dbf
/u01/app/oracle/oradata/SECDG/datafile/o1_mf_tbs_1_661zdwb9_.dbf
/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006

6 rows selected.

SQL> alter database create datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' as '/u01/app/oracle/oradata/SECDG/datafile/tbs_sec1.dbf';

Database altered.

此时，对应的数据文件已经处于正确的状态，该问题已经得到比较圆满的处理。

4.杜绝出现该问题的方法
防止该问题出现的的根本方法是在创建物理DataGuard的过程中就将standby_file_management、db_file_name_convert及log_file_name_convert参数设置正确。
1）调整standby_file_management参数为AUTO
SQL> alter system set standby_file_management=auto;

System altered.

2）设置db_file_name_convert参数
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/secdb/','/u01/app/oracle/oradata/SECDG/datafile/' scope=spfile;

3）设置log_file_name_convert参数
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/secdb/','/u01/app/oracle/oradata/SECDG/datafile/' scope=spfile;

5.小结
关于物理DataGuard的配置，每一个参数都要细细揣摩。针对文中提到的故障一旦出现，会给我们带来很多不必要的麻烦。
无论使用Grid Control还是通过脚本来创建物理DataGuard，创建完毕之后建议对数据库的每个参数做最终的检验和确认。