前言:
非常简单的一个添加数据文件的操作,在oracle单机环境下,通常只需要确认表空间名称,数据文件路径和名称,剩余空间大小等,就可以进行添加数据文件的操作。
但是在Oracle RAC+ADG环境下,除了要考虑主备库的表空间名称,数据文件路径和名称,剩余空间大小等,还需要考虑备库的db_file_name_convert、standby_file_management、db_create_file_dest等参数。
环境说明:
OS:AIX 7.1
DB:Oracle RAC(11.2.0.4.0)+DG
问题现象:
主库向CJC_INDX表空间添加数据文件,同步到备库失败,备库mrp进程自动中断。
主库执行:
alter tablespace CJC_INDX add datafile '+XX_XX_CJC_DATA/syCJC/datafile/CJC_INDX09.dbf' SIZE 30g;
备库报错:
主库添加数据文件,在备库同步失败,提示备库+CJC2_SYS磁盘组空间不足?
Sun Sep 27 18:44:16 2020
RFS[5]: Selected log 26 for thread 2 sequence 31454 dbid 1025103238 branch 861598022
Sun Sep 27 18:44:16 2020
RFS[3]: Selected log 17 for thread 1 sequence 32404 dbid 1025103238 branch 861598022
Sun Sep 27 18:44:18 2020
Archived Log entry 66634 added for thread 2 sequence 31453 ID 0x483e375b dest 1:
Sun Sep 27 18:44:18 2020
Archived Log entry 66635 added for thread 1 sequence 32403 ID 0x483e375b dest 1:
Sun Sep 27 18:44:20 2020
Media Recovery Log arch1/1_32403_861598022.dbf
Media Recovery Log arch1/2_31453_861598022.dbf
Errors in file /oracle/db/diag/rdbms/CJC/CJC1/trace/CJC1_pr00_2950750.trc:
ORA-01119: error in creating database file '+CJC2_SYS'
ORA-17502: ksfdcre:4 Failed to create file +CJC2_SYS
ORA-15041: diskgroup "CJC2_SYS" space exhausted
File #63 added to control file as 'UNNAMED00063'.
Originally created as:
'+XX_XX_CJC_DATA/syCJC/datafile/CJC_INDX09.dbf'
Recovery was unable to create the file as a new OMF file.
Errors with log /arch1/2_31453_861598022.dbf
MRP0: Background Media Recovery terminated with error 1274
Errors in file /oracle/db/diag/rdbms/CJC/CJC1/trace/CJC1_pr00_2950750.trc:
ORA-01274: cannot add datafile '+XX_XX_CJC_DATA/syCJC/datafile/CJC_INDX09.dbf' - file could not be created
Sun Sep 27 18:44:34 2020
Recovery interrupted!
Recovered data files to a consistent state at change 411495416088
Sun Sep 27 18:44:36 2020
MRP0: Background Media Recovery process shutdown (CJC1)
查看路径转换参数,备库应该在+CJC2_DATA磁盘组同步数据文件,为什么跑到+CJC2_SYS磁盘组了?
SYS@CJC1> show parameter file_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string +XX_XX_CJC_DATA/syCJC, +CJC2_DATA/CJC
log_file_name_convert string +XX_XX_CJC_DATA/syCJC, +CJC2_DATA/CJC
SYS@CJC1> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
和db_create_file_dest参数有关。
SYS@CJC1> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +CJC2_SYS
在数据库创建初期,使用的磁盘组是+CJC2_SYS,空间很小,后来又创建了一个大的磁盘组+CJC2_DATA,但是db_create_file_dest记录的还是+CJC2_SYS。
又由于db_create_file_dest优先级高于db_file_name_convert,导致备库同步数据文件到db_create_file_dest对应磁盘组。
解决方案:
那么如何继续添加数据文件呢?
虽然备库数据文件同步失败了,但是查看告警日志,控制文件里已经添加了新的数据文件信息,名称自动重命名为UNNAMED00063,所以备库不仅要手动添加数据文件,还要将控制文件中记录的数据文件名称改为正确的。
ORA-01119: error in creating database file '+CJC2_SYS'
ORA-17502: ksfdcre:4 Failed to create file +CJC2_SYS
ORA-15041: diskgroup "CJC2_SYS" space exhausted
File #63 added to control file as 'UNNAMED00063'.
1.备库将standby_file_management由auto改成manual
alter system set standby_file_management=manual;
2.添加数据文件,并同时改正控制文件中记录的错误名称
添加时间较久,30g需要5分钟左右
alter database create datafile '/oracle/db/product/11.2.0/db/dbs/UNNAMED00063' as '+CJC2_DATA/CJC/datafile/CJC_INDX09.dbf' SIZE 30g;
3.将standby_file_managemen改回auto
alter system set standby_file_management=auto;
4.启动备库mrp
alter database recover managed standby database disconnect from session;
恢复正常。
###chenjuchao 20240412###
欢迎关注我的公众号《IT小Chen》