DG 同步报错处理
[TOC]
一、查看报错信息
Errors in file /u01/app/oracle/diag/rdbms/phonetddg/phonetddg/trace/phonetddg_pr00_11405.trc:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted
File #1303 added to control file as 'UNNAMED01303'.
Originally created as:
'+DATA/phonetd/datafile/sysbsidx.4437.1154869877'
Recovery was unable to create the file as a new OMF file.
Errors with log +DATDG/phonetddg/archivelog/2023_12_06/thread_2_seq_67736.403.1154870077
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/oracle/diag/rdbms/phonetddg/phonetddg/trace/phonetddg_pr00_11405.trc:
ORA-01274: cannot add datafile '+DATA/phonetd/datafile/sysbsidx.4437.1154869877' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 17370649583597
Wed Dec 06 14:46:59 2023
MRP0: Background Media Recovery process shutdown (phonetddg)
复制二、原因分析
SQL> select name,state,type,total_mb ,free_mb from v$asm_diskgroup;
NAME STATE TYPE TOTAL_MB FREE_MB
---------- ---------------------- ------------ ---------- ----------------------------------------
DATA CONNECTED EXTERN 38912000 28908
DATDG CONNECTED EXTERN 24576000 20949507
复制分析：DG同步创建数据文件时候，抛出错误信息ORA-15041: diskgroup “DATA” space exhausted，提示DATA磁盘组空间不足，经检查，DATA 磁盘组free空间确实不足。
三、解决办法
从上面信息可以看到，DATA 磁盘组空间不足，但是我们还有一个DATDG 磁盘组空间比较充足，我们需要手动创建报错的数据文件到DATDG磁盘组，并将主库后面创建的数据文件都同步到到DATDG中。
SQL> ALTER SYSTEM SET standby_file_management=MANUAL SCOPE=BOTH;
System altered.
-- 不支持OMF 命令方式
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED01303' as '+DATDG/phonetddg/datafile/sysbsidx.4437.1154869877' ;
alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED01302' as '+DATDG/phonetddg/datafile/sysbsidx.4437.1154869877'
*
ERROR at line 1:
ORA-01276: Cannot add file +DATDG/phonetddg/datafile/sysbsidx.4437.1154869877. File has an Oracle Managed Files file name.
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED01303' as '+DATDG/phonetddg/datafile/sysbsidx.4437.dbf' ;
Database altered.
SQL> select name from v$datafile where file#=1303;
NAME
----------------------------------------------------------------------------------------------------
+DATDG/phonetddg/datafile/sysbsidx.4437.dbf
SQL> ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH;
System altered.
-- 修改db_file_name_convert参数，将DATA映射到DATDG中
SQL> alter system set db_file_name_convert='+DATA/phonetd/datafile/','+DATDG/phonetddg/datafile/' scope=spfile;
System altered.
-- 重启数据库，生效参数
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3.2068E+11 bytes
Fixed Size 2273888 bytes
Variable Size 2.4159E+10 bytes
Database Buffers 2.9635E+11 bytes
Redo Buffers 170278912 bytes
Database mounted.
Database opened.
-- 再次检查1303号数据文件，居然发现不见了。。。
SQL> select name from v$datafile where file#=1303;
no rows selected
-- 检查alert 日志，发现被自动删除了
File #1303 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 1303: '+DATDG/phonetddg/datafile/sysbsidx.4437.dbf'
-- 尝试启动MRP进程
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
-- 再次查看alert 日志又抛出了上面的错误信息，并且自动删除后，又开始重现同步创建1303号数据文件
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +DATDG/phonetddg/archivelog/2023_12_06/thread_1_seq_186312.266.1154870129
Completed: alter database recover managed standby database using current logfile disconnect
Media Recovery Log +DATDG/phonetddg/archivelog/2023_12_06/thread_2_seq_67736.403.1154870077
Errors in file /u01/app/oracle/diag/rdbms/phonetddg/phonetddg/trace/phonetddg_pr00_28132.trc:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted
File #1303 added to control file as 'UNNAMED01303'.
Originally created as:
'+DATA/phonetd/datafile/sysbsidx.4437.1154869877'
Recovery was unable to create the file as a new OMF file.
Errors with log +DATDG/phonetddg/archivelog/2023_12_06/thread_2_seq_67736.403.1154870077
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/oracle/diag/rdbms/phonetddg/phonetddg/trace/phonetddg_pr00_28132.trc:
ORA-01274: cannot add datafile '+DATA/phonetd/datafile/sysbsidx.4437.1154869877' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
-- 再次查看1303号数据文件，我们发现又变成了UNNAMED01303
SQL> select name from v$datafile where file#=1303;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED01303
复制四、思考
1. 为什么上面已经手动创建过1303号数据文件又变成了UNNAMED01303？
-- 检查db_file_name_convert参数，已经设置生效，没有问题
SQL> show parameter db_file_name_convert
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------------------------------------
db_file_name_convert string +DATA/phonetd/datafile/, +DATDG/phonetddg/datafile/
-- 仔细查看alert 日志报错信息，我们可以看到，DG 仍然在同步创建1303号文件，并行提示是创建 a new OMF file，说明我们默认设置的OMF参数还在，优先级高于了db_file_name_convert参数设置。
Originally created as:
'+DATA/phonetd/datafile/sysbsidx.4437.1154869877'
Recovery was unable to create the file as a new OMF file.
复制2. 关闭OMF
SQL> show parameter db_cre
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_create_file_dest string +DATA
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter system set db_create_file_dest='' scope=both;
System altered.
复制3. 再次处理
SQL> ALTER SYSTEM SET standby_file_management=MANUAL SCOPE=BOTH;
System altered.
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED01303' as '+DATDG/phonetddg/datafile/sysbsidx.4437.dbf' ;
Database altered.
SQL> ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH;
System altered.
SQL> select name from v$datafile where file#=1303;
NAME
-------------------------------------------------------------------------------------------------------------------------------------------------
+DATDG/phonetddg/datafile/sysbsidx.4437.dbf
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
-- 再次查看alert ，发现MRP同步进程正常，并且1304、1305等后面的datafile 也都成功同步创建到DATDG磁盘组
Wed Dec 06 15:08:19 2023
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +DATDG/phonetddg/archivelog/2023_12_06/thread_1_seq_186312.266.1154870129
Media Recovery Log +DATDG/phonetddg/archivelog/2023_12_06/thread_2_seq_67736.403.1154870077
Completed: alter database recover managed standby database using current logfile disconnect
Datafile 1303 added to flashback set
Wed Dec 06 15:08:39 2023
RFS[3]: Selected log 61 for thread 2 sequence 67777 dbid -1505792114 branch 1091294606
Wed Dec 06 15:08:41 2023
Archived Log entry 131331 added for thread 2 sequence 67776 ID 0xa9217057 dest 1:
Wed Dec 06 15:08:44 2023
Recovery created file +datdg
Datafile 1304 added to flashback set
Successfully added datafile 1304 to media recovery
Datafile #1304: '+DATDG/phonetddg/datafile/sysbsidx.5558.1154876907'
Wed Dec 06 15:09:03 2023
Recovery created file +datdg
Datafile 1305 added to flashback set
Successfully added datafile 1305 to media recovery
Datafile #1305: '+DATDG/phonetddg/datafile/sysbsidx.436.1154876925'
Wed Dec 06 15:09:22 2023
Recovery created file +datdg
Datafile 1306 added to flashback set
Successfully added datafile 1306 to media recovery
Datafile #1306: '+DATDG/phonetddg/datafile/sysbsidx.769.1154876945'
复制4. 检查同步状态，正常
SQL> select process,status,thread#,sequence# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE#
------------------ ------------------------ ---------- ----------
ARCH CLOSING 2 67775
ARCH CLOSING 1 186347
ARCH CONNECTED 0 0
ARCH CLOSING 2 67776
RFS IDLE 0 0
RFS IDLE 0 0
RFS RECEIVING 1 186348
RFS IDLE 0 0
RFS IDLE 2 67777
RFS IDLE 0 0
MRP0 APPLYING_LOG 2 67736
11 rows selected.
复制5. 关闭自动扩展
注意：因为DG DATA磁盘组只剩下28G空间，我们需要关闭主库对应的数据文件自动扩展，防止主库数据文件自动扩展，导致DG DATA磁盘组空间被撑爆，同步报错。
以下操作在主库执行：
- 查看是否存在自动扩展的数据文件
- 可以看到，存在部分自动扩展的数据文件，并且这部分数据文件都映射在DG上DATA磁盘组里面的数据文件，如果任其自动扩展，那么DATA磁盘组很容易撑爆。
select file_id,a.file_name,a.autoextensible,a.bytes,a.maxbytes from dba_data_files a where autoextensible='YES';
复制
- 批量生成关闭自动扩展数据文件脚本
select 'alter database datafile '''||file_name|| ''' autoextend off;' from dba_data_files a where autoextensible='YES' and file_id<1033;
复制
- 批量执行上面的脚本
SQL> alter database datafile '+DATA/phonetd/datafile/system.1129.1139690233' autoextend off;
Database altered.
.....
....
复制- 关闭自动扩展后，需要重现检查表空间使用率，防止因关闭了自动扩展，导致表空间不足。 比如system、sysaux增加新的数据文件
SQL> alter tablespace SYSTEM add datafile '+DATA' size 10G autoextend on;
Tablespace altered.
SQL> alter tablespace SYSAUX add datafile '+DATA' size 10G autoextend on;
Tablespace altered.
复制最后修改时间：2023-12-08 10:04:41
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
