Oracle 11g Data Guard 增加資料檔案報錯：ORA-01111、ORA-01110、ORA-01157

Oracle 11g Data Guard 增加資料檔案時報錯，在主庫執行的語句如下：

SQL> alter tablespace TS_MIS_DATA add datafile '/oradata1/misdb/TS_MIS_DATA_32.dbf'  SIZE 34351349760   AUTOEXTEND OFF;

SQL> show parameter standby

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest     string ?/dbs/arch
standby_file_management     string AUTO
備庫 ALERT 日誌中的報錯如下：

Tue Feb 09 17:52:30 2016
Errors in file /u01/app/oracle/diag/rdbms/misdbdg/misdb/trace/misdb_dbw0_16033.trc:
ORA-01186: file 43 failed verification tests
ORA-01157: cannot identify/lock data file 43 - see DBWR trace file
ORA-01111: name for data file 43 is unknown - rename to correct file
ORA-01110: data file 43: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00043'
File 43 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/misdbdg/misdb/trace/misdb_pr00_26503.trc:
ORA-01111: name for data file 43 is unknown - rename to correct file
ORA-01110: data file 43: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00043'
ORA-01157: cannot identify/lock data file 43 - see DBWR trace file
ORA-01111: name for data file 43 is unknown - rename to correct file
ORA-01110: data file 43: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00043'
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-1111 exception
檢查備庫檔案系統的許可權，發現新掛載的盤沒有授權，給指定目錄授權

chown -R oracle.dba oradata1

授權後，在備庫進行日誌應用，但依舊報錯

ORA-01111: name for data file 43 is unknown - rename to correct file
ORA-01110: data file 43: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00043'
ORA-01157: cannot identify/lock data file 43 - see DBWR trace file
ORA-01111: name for data file 43 is unknown - rename to correct file
ORA-01110: data file 43: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00043'
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-1111 exception

使用 ALTER DATABASE CREATE DATAFILE 命令將報錯的資料檔案改為正確的資料檔案

在備庫中執行如下命令：

alter database create datafile  '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00043'  as  '/oradata1/misdbdg/TS_MIS_DATA_31.dbf';

CREATE DATAFILE 命令的含義是根據控制檔案建立一個新的資料檔案，來代替原來的資料檔案，之後再透過應用日誌來對這個資料檔案進行介質恢復。

--實時日誌應用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

錯誤被修復，ALERT 日誌中的內容如下：

Media Recovery Log /arch/arch1/misdbdg/1_17845_827421642.dbf
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Recovery deleting file #43:'/oradata1/misdbdg/TS_MIS_DATA_31.dbf' from controlfile.
Recovery created file /oradata1/misdbdg/TS_MIS_DATA_31.dbf
Successfully added datafile 43 to media recovery