Oracle 11G Physical Standby Database Active Data Guard - 数据文件路径错误问题

问题描述
主库DGDB:在非指定的目录创建表空间。
从库DGDBST:则从库报错，MRP进程死掉。

主库操作：
create tablespace DGTSDATA datafile '/prod/oracle/oradata/DGDB/DGTSDATA.ora' size 10M autoextend on next 2M maxsize 100M;    --成功
create tablespace DGTSINDX datafile '/prod/oracle/temp/DGTSINDX.ora' size 10M autoextend on next 2M maxsize 100M;            --成功
create user DGSYSTEM identified by amaxgs default tablespace DGTSDATA;                                                       --成功
从库错误：
create tablespace DGTSINDX datafile '/prod/oracle/temp/DGTSINDX.ora' size 10M autoextend on next 2M maxsize 100M;            --失败，报错如下
DGDBST> select facility,to_char(timestamp,'dd-mon-yyyy hh24:mi') time,message from v$dataguard_status;
Log Apply Services       06-sep-2013 17:32          MRP0: Background Media Recovery terminated with error 1274
Log Apply Services       06-sep-2013 17:32          Managed Standby Recovery not using Real Time Apply
Log Apply Services       06-sep-2013 17:32          MRP0: Background Media Recovery process shutdown

备库alert日志
Fri Sep 06 17:32:43 2013
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Errors in file /prod/oracle/diag/rdbms/dgdbst/DGDBST/trace/DGDBST_pr00_5748.trc:
ORA-01119: error in creating database file '/prod/oracle/temp/DGTSINDX.ora'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 6109
File #7 added to control file as 'UNNAMED00007'.
Originally created as:
'/prod/oracle/temp/DGTSINDX.ora'
Recovery was unable to create the file as:
'/prod/oracle/temp/DGTSINDX.ora'
MRP0: Background Media Recovery terminated with error 1274
Errors in file /prod/oracle/diag/rdbms/dgdbst/DGDBST/trace/DGDBST_pr00_5748.trc:
ORA-01274: cannot add datafile '/prod/oracle/temp/DGTSINDX.ora' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1374026
Fri Sep 06 17:32:43 2013
MRP0: Background Media Recovery process shutdown (DGDBST)

#######
原因分析：
1，主从库在同一台主机，同一目录不能创建同名的数据文件，导致这个问题；但如果主从库在不同主机，同名目录可以创建同名数据文件。
2，db_file_name_convert='/prod/oracle/oradata/DGDBST','/prod/oracle/oradata/DGDB'没有对/prod/oracle/temp进行转换。
#######

#######
解决方法
参考：http://www.eygle.com/archives/2009/07/dataguard_ora_01111.html
该解决方案，也可以用于将备库数据文件移动位置。
#######
1，从库在MRP进程停掉之后，数据库为open read only状态。
尝试手动关闭后恢复，失败。
DGDBST> shutdown immediate;
DGDBST> startup mount standby database;
DGDBST> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/prod/oracle/product/11.2.0/db_1/dbs/UNNAMED00007'

DGDBST> RECOVER STANDBY DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/prod/oracle/product/11.2.0/db_1/dbs/UNNAMED00007'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/prod/oracle/product/11.2.0/db_1/dbs/UNNAMED00007'

2，保持备库在mount状态下进行如下操作，将未创建成功的数据文件创建出来。
DGDBST> select name from v$datafile;    --列出数据文件，发现控制文件里，有一个存在的数据文件。
NAME
------------------------------------------------------------------------------------------------------------------------------------
/prod/oracle/oradata/DGDBST/system01.dbf
/prod/oracle/oradata/DGDBST/sysaux01.dbf
/prod/oracle/oradata/DGDBST/undotbs01.dbf
/prod/oracle/oradata/DGDBST/users01.dbf
/prod/oracle/oradata/DGDBST/example01.dbf
/prod/oracle/oradata/DGDBST/DGTSDATA.ora
/prod/oracle/product/11.2.0/db_1/dbs/UNNAMED00007  /* 当备库执行create tablespace DGTSINDX datafile '/prod/oracle/temp/DGTSINDX.ora' size 10M autoextend on next 2M maxsize 100M;出现这个不存在数据文件UNNAMED00007，下面手动重新创建DGTSINDX.ora数据文件。*/

操作步骤
DGDBST> select staus from v$instance;--这里需要在mount状态下。
DGDBST> alter system set standby_file_management=manual;
DGDBST> alter database create datafile '/prod/oracle/product/11.2.0/db_1/dbs/UNNAMED00007' as '/prod/oracle/oradata/DGDBST/DGTSINDX.ora';
DGDBST> alter system set standby_file_management=auto;
DGDBST> alter database recover managed standby database using current logfile disconnect from session;    --启动MRP
DGDBST> alter database recover managed standby database cancel;                                           --关闭MRP
DGDBST> shutdown immediate;
DGDBST> startup mount;
DGDBST> alter database open read only;                                                                   --注意read only与MRP的顺序，是在启动MRP，完成恢复，关闭MRP后再重新打开。
DGDBST> alter database recover managed standby database using current logfile disconnect from session;    --启动MRP
DGDBST> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;--查看备库状态及角色
DGDBST> select process,pid,status,thread#,group#,sequence#,delay_mins from v$managed_standby;             --查看进程状态

3，修改备库的db_file_name_convert='/prod/oracle/oradata/DGDB','/prod/oracle/oradata/DGDBST','/prod/oracle/temp'，'/prod/oracle/oradata/DGDBST',注意路径的前后顺序。
DGDBST> alter system set db_file_name_convert='/prod/oracle/oradata/DGDB','/prod/oracle/oradata/DGDBST','/prod/oracle/temp','/prod/oracle/oradata/DGDBST' scope=spfile;

5,重启data guard
DGDBST> alter database recover managed standby database cancel;    --关闭MRP
DGDBST> shutdown immediate;
DGDBST> startup mount;
DGDBST> alter database open read only;
DGDBST> alter database recover managed standby database using current logfile disconnect from session;    --启动MRP

一些测试SQL
create index EBST01_ID_IDX on EBST01(ID) tablespace DGTSINDX;
create index EBST02_ID_IDX on EBST02(ID) tablespace DGTSINDX;
alter tablespace DGTSINDX add datafile '/prod/oracle/temp/DGTSINDX02.ora' size 10M autoextend on next 2M maxsize 100M