使用TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y导数时出现磁盘空间被撑满的故障

问题描述：使用TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y导数时出现磁盘空间被撑满的故障
数据库：oracle 19.7
数据库架构：rac(两节点)+adg(两节点)
背景介绍：业务反馈有张大表需使用数据泵恢复，大表备份集为2,507g，恢复后大小为13,243g.因考虑存储空间问题，所以在impdp导入时添加TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y配置参数，旨在整个导入期间阻止该表和对应索引redo日志的大量产生，仅生成少量日志，缓解存储压力并加快导入速度.但实际操作过程中归档日志大量生成，将FRA磁盘组撑满，导致数据库Hang住.
故障复盘：
1、导入语句
$ cat impdp_newjs_EMPLOYEES.sh
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240801#job dumpfile=PRM.EMPLOYEES#P_20240801.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240801.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240802#job dumpfile=PRM.EMPLOYEES#P_20240802.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240802.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240803#job dumpfile=PRM.EMPLOYEES#P_20240803.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240803.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240804#job dumpfile=PRM.EMPLOYEES#P_20240804.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240804.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240805#job dumpfile=PRM.EMPLOYEES#P_20240805.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240805.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240806#job dumpfile=PRM.EMPLOYEES#P_20240806.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240806.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240807#job dumpfile=PRM.EMPLOYEES#P_20240807.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240807.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240808#job dumpfile=PRM.EMPLOYEES#P_20240808.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240808.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240809#job dumpfile=PRM.EMPLOYEES#P_20240809.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240809.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240810#job dumpfile=PRM.EMPLOYEES#P_20240810.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240810.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240811#job dumpfile=PRM.EMPLOYEES#P_20240811.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240811.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240812#job dumpfile=PRM.EMPLOYEES#P_20240812.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240812.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240813#job dumpfile=PRM.EMPLOYEES#P_20240813.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240813.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240814#job dumpfile=PRM.EMPLOYEES#P_20240814.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240814.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240815#job dumpfile=PRM.EMPLOYEES#P_20240815.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240815.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240816#job dumpfile=PRM.EMPLOYEES#P_20240816.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240816.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240817#job dumpfile=PRM.EMPLOYEES#P_20240817.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240817.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240818#job dumpfile=PRM.EMPLOYEES#P_20240818.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240818.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240819#job dumpfile=PRM.EMPLOYEES#P_20240819.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240819.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240820#job dumpfile=PRM.EMPLOYEES#P_20240820.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240820.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240821#job dumpfile=PRM.EMPLOYEES#P_20240821.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240821.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240822#job dumpfile=PRM.EMPLOYEES#P_20240822.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240822.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240823#job dumpfile=PRM.EMPLOYEES#P_20240823.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240823.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240824#job dumpfile=PRM.EMPLOYEES#P_20240824.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240824.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240825#job dumpfile=PRM.EMPLOYEES#P_20240825.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240825.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240826#job dumpfile=PRM.EMPLOYEES#P_20240826.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240826.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240827#job dumpfile=PRM.EMPLOYEES#P_20240827.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240827.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240828#job dumpfile=PRM.EMPLOYEES#P_20240828.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240828.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240829#job dumpfile=PRM.EMPLOYEES#P_20240829.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240829.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240830#job dumpfile=PRM.EMPLOYEES#P_20240830.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240830.out CLUSTER=N table_exists_action=append parallel=12
impdp scott/tiger@ORCLPDB remap_table=EMPLOYEES:ZZZ_EMPLOYEES remap_tablespace=USER02:USER03 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y job_name=ZZZ_EMPLOYEESimpdp#P_20240831#job dumpfile=PRM.EMPLOYEES#P_20240831.crmhis1.dpdmp DIRECTORY=dump_dir logfile=impdp_ZZZ_EMPLOYEES#P_20240831.out CLUSTER=N table_exists_action=append parallel=12

$ nohup sh impdp_newjs_EMPLOYEES.sh > impdp_newjs_EMPLOYEES.log &

2、日志告警
2024-12-12T21:41:43.086751+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_arc3_27450.trc:
ORA-19504: failed to create file "+FRA"
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
ARC3(PID:27450):Error 19504 creating archive log file to '+FRA'
ARC3(PID:27450):stuck archiver:insufficient local LADs
ARC3(PID:27450):stuck archiver condition declared

3、原因分析
问题点一：
oracle从12c开始引入TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y参数配置，当前数据库版本19.7，版本满足要求，但却忽略该架构存在ADG，搭建备库时肯定有开启FORCE_LOGGING功能，如下为查询结果：
SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

因此证明当数据库处于FORCE LOGGING模式时，DISABLE_ARCHIVE_LOGGING参数会失效.

问题点二：
数据库归档日志文件放在快速恢复区，当快速恢复区空间紧张时，Oracle会自动删已应用过且最早的归档日志文件以释放空间,即使该归档日志文件未备份，可参考MOS Doc ID 1369341.1.
而当归档日志文件没有应用到ADG时则不会被删除，此种情况下快速恢复区会被撑满直到数据库Hang住.
默认情况下当快速恢复区使用率达到80%后，归档日志文件就会被自动删除，删除过程如下：
Oracle评估所需空间大小(切换日志时产生的归档日志大小)，然后将该大小与当前的快速恢复区占用的空间大小相加，判断是否超过80%，若超过则回收空间(回收的空间应大于等于新产生的归档文件所需要的空间),若不能回收空间(如归档日志未被应用到备库)，则占用剩下20%的新空间，直到空间耗尽.

那为什么该场景下Oracle没有自动删归档日志文件呢？
经查询发现db_recovery_file_dest_size参数被设置过大，导致未达到80%的临界点.
相关查询如下：
SQL> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 16777216M
recovery_parallelism integer 4
remote_recovery_file_dest string

SQL> select name,total_mb,free_mb from v$asm_diskgroup where name='FRA';

NAME TOTAL_MB FREE_MB
------------------------------ ---------- ----------
FRA 6291456 1370288

发现FRA磁盘组总共才6,291,456M，而设置的db_recovery_file_dest_size值乘以80%为13,421,773M，可想而知FRA磁盘组肯定会被撑满.

4、解决方案
使用如下指令调整db_recovery_file_dest_size值.
alter system set db_recovery_file_dest_size=6000g scope=both sid='*';

5、结果验证
此后在导数据时，在告警日志中就会发现如下信息.
2024-12-13T22:00:16.421319+08:00
Deleted Oracle managed file +FRA/ORCLCDB/ARCHIVELOG/2024_12_13/thread_1_seq_417236.772.1187581033
Deleted Oracle managed file +FRA/ORCLCDB/ARCHIVELOG/2024_12_13/thread_2_seq_165613.1092.1187581031

快速恢复区使用率一直保持80%左右，数据库确实也在自动删归档.

6、拓展知识
-- 可通过设置19823 event调整快速恢复区自动删归档的临界百分比.
alter system set event='19823 trace name context forever,level 50' scope=spfile sid='*';

参考网址：https://blog.csdn.net/jycjyc/article/details/104729242/