db_file_name_convert設定出錯導致備庫無法recovery

一套10.2.0.5的physical standby，主庫掛載了一系列的sas磁碟，新增的磁碟路徑在備庫上不存在，因此需要設定備庫引數db_file_name_convert，
但是敲入命令出錯，原本的'/storage/disk00','/data/oracle/oradata/test'敲成了'/storage/disk0*','/data/oracle/oradata/test'
在主庫新增表空間的時候備庫出現錯誤，備庫alertlog如下

WARNING: File being created with same name as in Primary
Existing file may be overwritten
Errors in file /data/oracle/diag/rdbms/test/trace/test_pr00_6397.trc:
ORA-01119: error in creating database file '/storage/disk00/JUSTIN_01.dbf'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Linux-x86_64 Error: 13: Permission denied
File #166 added to control file as 'UNNAMED00166'.
Originally created as:
'/storage/disk00/JUSTIN_01.dbf'
Recovery was unable to create the file as:
'/storage/disk00/JUSTIN_01.dbf'
MRP0: Background Media Recovery terminated with error 1274
Errors in file /data/oracle/diag/rdbms/test/trace/test_pr00_6397.trc:
ORA-01274: cannot add datafile '/storage/disk00/JUSTIN_01.dbf' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 23332162008
Tue Oct 11 14:44:44 2011
MRP0: Background Media Recovery process shutdown (test)

由於備庫不存在/storage/disk00，無法建立datafile導致recovery被中止
此時嘗試啟動備庫的recovery程式，觀察alertlog可以看到，recovery的slave程式由於ora-1111錯誤而退出
Tue Oct 11 14:46:43 2011
ALTER DATABASE RECOVER  managed standby database using current logfile disconnect from session 
Attempt to start background Managed Standby Recovery process (test)
Tue Oct 11 14:46:43 2011
MRP0 started with pid=26, OS id=6441
MRP0: Background Managed Standby Recovery process started (test)
 started logmerger process
Tue Oct 11 14:46:48 2011
Managed Standby Recovery starting Real Time Apply
Tue Oct 11 14:46:48 2011
Errors in file /data/oracle/diag/rdbms/test/trace/test_dbw0_6353.trc:
ORA-01186: file 166 failed verification tests
ORA-01157: cannot identify/lock data file 166 - see DBWR trace file
ORA-01111: name for data file 166 is unknown - rename to correct file
ORA-01110: data file 166: '/data/oracle/product/11.2.0/db1/dbs/UNNAMED00166'
File 166 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /data/oracle/diag/rdbms/test/trace/test_pr00_6475.trc:
ORA-01111: name for data file 166 is unknown - rename to correct file
ORA-01110: data file 166: '/data/oracle/product/11.2.0/db1/dbs/UNNAMED00166'
ORA-01157: cannot identify/lock data file 166 - see DBWR trace file
ORA-01111: name for data file 166 is unknown - rename to correct file
ORA-01110: data file 166: '/data/oracle/product/11.2.0/db1/dbs/UNNAMED00166'
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-1111 exception
Errors in file /data/oracle/diag/rdbms/test/trace/test_pr00_6475.trc:
ORA-01111: name for data file 166 is unknown - rename to correct file
ORA-01110: data file 166: '/data/oracle/product/11.2.0/db1/dbs/UNNAMED00166'
ORA-01157: cannot identify/lock data file 166 - see DBWR trace file
ORA-01111: name for data file 166 is unknown - rename to correct file
ORA-01110: data file 166: '/data/oracle/product/11.2.0/db1/dbs/UNNAMED00166'
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (test)
Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect from session 
Tue Oct 11 14:47:06 2011
Archived Log entry 4801 added for thread 1 sequence 10651 ID 0xe339e88c dest 1:
Tue Oct 11 14:47:06 2011
RFS[1]: Selected log 6 for thread 1 sequence 10652 dbid -482780276 branch 754336076
Tue Oct 11 14:47:25 2011
Archived Log entry 4802 added for thread 1 sequence 10652 ID 0xe339e88c dest 1:
Tue Oct 11 14:47:25 2011
RFS[1]: Selected log 6 for thread 1 sequence 10653 dbid -482780276 branch 754336076

此時備庫已經無法正常恢復了，也無法open
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 166 - see DBWR trace file
ORA-01111: name for data file 166 is unknown - rename to correct file
ORA-01110: data file 166: '/data/oracle/product/11.2.0/db1/dbs/UNNAMED00166'

解決辦法，修改備庫的db_file_name_convert引數使之正確，在主庫將新增的tablespace drop，接著在備庫執行offline drop
SQL> alter database datafile '/data/oracle/product/11.2.0/db1/dbs/UNNAMED00166'
  2  offline drop;

Database altered.
此時v$datafile中還可以看到該檔案記錄
SQL> select ts#,name from v$datafile;

       TS# NAME
---------- ----------------------------------------------------------------------------------------------------
         0 /data/oracle/oradata/test/system.dbf
         1 /data/oracle/oradata/test/undotbs01.dbf
         2 /data/oracle/oradata/test/sysaux.dbf
        10 /data/oracle/product/11.2.0/db1/dbs/UNNAMED00166
開啟備庫恢復程式，此時備庫恢復正常，透過alertlog可以看出備庫的執行步驟
由於db_file_name_convert設定正確，先增加datafile，然後再delete並drop tablespace
Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect from session 
Recovery created file /data/oracle/oradata/test/JUSTIN_01.dbf
Successfully added datafile 166 to media recovery
Datafile #166: '/data/oracle/oradata/test/JUSTIN_01.dbf'
Recovery deleting file #166:'/data/oracle/oradata/test/JUSTIN_01.dbf' from controlfile.
Deleted file /data/oracle/oradata/test/JUSTIN_01.dbf
Recovery dropped tablespace 'JUSTIN'
Media Recovery Log /data/oracle/oradata/test/arch/1_10652_754336076.dbf
Media Recovery Log /data/oracle/oradata/test/arch/1_10653_754336076.dbf
Media Recovery Log /data/oracle/oradata/test/arch/1_10654_754336076.dbf
Media Recovery Waiting for thread 1 sequence 10655 (in transit)
Recovery of Online Redo Log: Thread 1 Group 6 Seq 10655 Reading mem 0
  Mem# 0: /data/oracle/oradata/test/standbyredo6.log
Tue Oct 11 14:54:08 2011