1. 事情發生背景
STANDBY資料庫表空間報警（因為主庫沒有表空間報警機制，所以在STANDBY庫報出來）
我也沒想，就在STANDBY增加資料檔案，結果報錯：
SQL> alter tablespace P2BI_PROD_DW_TBS add datafile '+/nfsbackup/o38bi_dg/P2BI_PROD_DW_TBS_32.dbf' size 2048m autoextend on next 20m maxsize 30720m;
alter tablespace P2BI_PROD_DW_TBS add datafile '+/nfsbackup/o38bi_dg/P2BI_PROD_DW_TBS_32.dbf' size 2048m autoextend on next 20m maxsize 30720m
ORA-16000: database open for read-only access

然後我發現問題，在主庫上增加資料檔案：
SQL> alter tablespace P2BI_PROD_DW_TBS add datafile '/oradata38/o38bi/P2BI_PROD_DW_TBS_32.dbf' size 2048m autoextend on next 20m maxsize 30720m;
Tablespace altered
加完我發現，主庫上/ORADATA38已經沒多少空間了，就又趕緊修改。
SQL> alter database datafile '/oradata38/o38bi/P2BI_PROD_DW_TBS_32.dbf' resize 10m;
SQL> alter database datafile '/oradata38/o38bi/P2BI_PROD_DW_TBS_32.dbf' autoextend off;
然後在另外一個目錄增加資料檔案（這個目錄以前都沒有資料檔案，也沒有在standby庫的db_file_name_convert裡配置）：
SQL> alter tablespace P2BI_PROD_DW_TBS add datafile '/oradata/o38bi/P2BI_PROD_DW_TBS_33.dbf' size 2048m autoextend on next 20m maxsize 30720m;
Tablespace altered

結果從庫報錯，無法繼續恢復：
Fri Jan 24 14:53:28 2014
Recovery created file /nfsbackup/o38bi_dg/P2BI_PROD_DW_TBS_32.dbf
Successfully added datafile 74 to media recovery
Datafile #74: '/nfsbackup/o38bi_dg/P2BI_PROD_DW_TBS_32.dbf'
Fri Jan 24 14:55:52 2014
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Errors in file /nfsbackup/oracle2/diag/rdbms/o38bi_dg/o38bi_dg/trace/o38bi_dg_pr00_21954764.trc:
ORA-01119: error in creating database file '/oradata/o38bi/P2BI_PROD_DW_TBS_33.dbf'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
IBM AIX RISC System/6000 Error: 13: Permission denied
File #75 added to control file as 'UNNAMED00075'.
Originally created as:
'/oradata/o38bi/P2BI_PROD_DW_TBS_33.dbf'
Recovery was unable to create the file as:
'/oradata/o38bi/P2BI_PROD_DW_TBS_33.dbf'
MRP0: Background Media Recovery terminated with error 1274
Errors in file /nfsbackup/oracle2/diag/rdbms/o38bi_dg/o38bi_dg/trace/o38bi_dg_pr00_21954764.trc:
ORA-01274: cannot add datafile '/oradata/o38bi/P2BI_PROD_DW_TBS_33.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 3117526870301
Fri Jan 24 14:55:52 2014
MRP0: Background Media Recovery process shutdown (o38bi_dg)

2. 恢復步驟：
1)停掉standby資料庫
2)配置db_file_name_convert，增加上新目錄的轉化
3)修改standby_file_management為MUNUAL，ALTER SYSTEM SET standby_file_management='MANUAL'
4)執行下面的SQL，讓控制檔案識別到新加的資料檔案
alter database create datafile '/nfsbackup/oracle2/product/11.2.0/dbhome_1/dbs/UNNAMED00075' as '/nfsbackup/o38bi_dg/P2I_PROD_DW_TBS_33.dbf';
5)然後啟動恢復程式
alter database recover managed standby database disconnect from session;
6)再修改回來standby_file_management為AUTO，ALTER SYSTEM SET standby_file_management='AUTO'
7)alter database recover managed standby database cancel
8)alter database open read only
9)alter database recover managed standby database using current logfile disconnect from session

3. 結論
STANDBY上建立資料檔案會報錯，問題不大。
主庫上增加資料檔案目錄時，一定要先修改從庫的配置

STANDBY資料庫因為資料檔案配置不對，造成無法繼續恢復

相關文章