dg create datafile auto failed 排除处理

1.Environment：
11.2.0.4 dg

2.Symptoms：
告警描述：Wed Sep 22 14:29:15 2021
Errors in file /data/app/oracle/diag/rdbms/dgsmsdb/dgsmsdb/trace/dgsmsdb_pr00_23607.trc:
ORA-01119: error in creating database file '+data'
ORA-17502: ksfdcre:4 Failed to create file +data
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
File #60 added to control file as 'UNNAMED00060'.
Originally created as:
'+DATA/smsdb/datafile/sms_tbsp.2513.1083940131'
Recovery was unable to create the file as:
'+data'
MRP0: Background Media Recovery terminated with error 1274
Errors in file /data/app/oracle/diag/rdbms/dgsmsdb/dgsmsdb/trace/dgsmsdb_pr00_23607.trc:
ORA-01274: cannot add datafile '+DATA/smsdb/datafile/sms_tbsp.2513.1083940131' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 184

3.Changes：
主库添加数据文件。
首先确认备库是否自动创建数据文件
然后查看是否是omf管理
最后查看convert参数

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string +DATA/dbsms, /piccdata/oradata

4.Cause
数据库通过dg的switchover方式迁移后，由于新的主库的数据文件路径发生了变化，原来的备库的convert参数依旧是原来的配置，导致dg的文件无法正确创建。

5.Solution
alter system set standby_file_management=MANUAL scope=both;
alter database create datafile '/data/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00010' as '/piccdg/oradata/dgsmis/smiscrm_tbs.299.1018797201';
alter system set standby_file_management=AUTO scope=both;

可以修改Db_create_file_dest使用omf来避免这个问题。
此外，如果修改convert参数，需要重启库。遇到了备库数据文件路径分散在两个路径下，重启库提示如下报错。手工swtich 后起库
Errors in file /data/app/oracle/diag/rdbms/dgsms/dgsms/trace/dgsms_dbw0_10583.trc:
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '+DATA/dbsms/sms_ts4.dbf'
ORA-17503: ksfdopn:2 Failed to open file +DATA/dbsms/sms_ts4.dbf
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
Errors in file /data/app/oracle/diag/rdbms/dgsms/dgsms/trace/dgsms_dbw0_10583.trc:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '+DATA/dbsms/sms_ts5.dbf'
ORA-17503: ksfdopn:2 Failed to open file +DATA/dbsms/sms_ts5.dbf
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
Errors in file /data/app/oracle/diag/rdbms/dgsms/dgsms/trace/dgsms_dbw0_10583.trc:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '+DATA/dbsms/sms_ts6.dbf'
ORA-17503: ksfdopn:2 Failed to open file +DATA/dbsms/sms_ts6.dbf
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
Errors in file /data/app/oracle/diag/rdbms/dgsms/dgsms/trace/dgsms_dbw0_10583.trc:
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '+DATA/dbsms/sms_ts7.dbf'

6.References
Steps to create a new standby from an existing ADG standby (Doc ID 2305009.1)
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)
Usage and Limitation of db_file_name_convert and log_file_name_convert (Doc ID 1367014.1)
On Standby Datafiles are Going Into Wrong Diskgroup ( Db_file_name_convert, Db_create_file_dest ) (Doc ID 1408666.1) https://docs.oracle.com/cd/E18283_01/server.112/e17022/scenarios.htm
http://blog.itpub.net/31397003/viewspace-2149519/
https://www.cnblogs.com/jyzhao/p/7459415.html

拓展：关于temp文件

Physical Standby Database Reported Corrupted Datafiles in Temporary Tablespace (Doc ID 1290412.1)

set linesize 200
col NAME for a90
select FILE# ,NAME from v$datafile;
25 /data/oradata/ump_05.dbf
26 /data/oradata/undotbs02.dbf
27 /data/oradata/yxt2ai_data2.db
28 /data/oradata/yxt2ai_data3.db
29 /data/oradata/sms_ts16.dbf
30 /data/oradata/sms_ts17.dbf
31 /data/oradata/sms_ts18.dbf
32 /data/oradata/ump_06.dbf
33 /data/oradata/datafile/sms_tbsp.985.1016276421
34 /data/oradata/datafile/ump_ts.580.1023362685
35 /data/oradata/datafile/ump_ts.897.1023362785
36 /data/oradata/datafile/ump_ts.983.1023362821
37 /data/oradata/datafile/yxt2ai_data.894.1023362887

巴特西

dg create datafile auto failed 排除处理

最新文章

热门文章