本文将描述由于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,创建完毕之后建议对数据库的每个参数做最终的检验和确认。
Good luck.
secooler
10.08.10
-- The End --
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,创建完毕之后建议对数据库的每个参数做最终的检验和确认。
Good luck.
secooler
10.08.10
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-670703/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-670703/