STANDBY数据库因为数据文件配置不对，造成无法继续恢复

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上创建数据文件会报错，问题不大。
主库上增加数据文件目录时，一定要先修改从库的配置

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13454868/viewspace-1075114/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13454868/viewspace-1075114/