本文將描述由於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 --

【DataGuard】由於備庫引數設定不當導致資料檔案無法新增的故障分析

相關文章