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/