配置闪回恢复区开启归档，未配置清理归档脚本，数据库hang住

时间：2022-04-21 06:12:08

问题现象，测试环境执行SQL hang住

enmo:/home/oracle/worksh dg.sh

SQL*Plus: Release 11.2.0.4. Production on Mon May  ::

Copyright (c) , , Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4. - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
查询会话等待事件
select sql_id,event from v$session where username='SYS' and status='ACTIVE'

SQL_ID                         EVENT

------------------------------ ----------------------------------------

b8252j71hq6kq                  log file switch (archiving needed)

0gn4hjtcbdm9q                  SQL*Net message from client

SQL> select sql_text from v$sql where sql_id='b8252j71hq6kq';

no rows selected

SQL> select sql_text from dba_hist_sqltext where sql_id='b8252j71hq6kq';

no rows selected

归档的等待事件，等待归档日志切换

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 114
Next log sequence to archive 114
Current log sequence 116

SQL> show parameter recover

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest string /11.2.0.4/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4182M

闪回区满了

[root@enmo TT11204]# pwd
/11.2.0.4/app/oracle/fast_recovery_area/TT11204
[root@enmo TT11204]# du -sm
4187

闪回区并不能自动删除归档日，因此还是需要配置归档日志清理策略。

本次处理方式，不使用闪回区，删除原归档日志。

SQL> alter system set log_archive_dest_1='location=/11.2.0.4/app/oracle/fast_recovery_area/TT11204/archivelog';

查询Alert输出
Errors in file /11.2.0.4/app/oracle/diag/rdbms/tt11204/tt11204/trace/tt11204_arc1_20121.trc:

ORA-: WARNING: db_recovery_file_dest_size of  bytes is 100.00% used, and has  remaining bytes available.

************************************************************************

************************************************************************

You have following choices to free up space from recovery area:

You have following choices to free up space from recovery area:

. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

   then consider changing RMAN ARCHIVELOG DELETION POLICY.
--归档清理未配置DG保护方式，没有问题
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

. Back up files to tertiary device such as tape using RMAN

   BACKUP RECOVERY AREA command.

. Add disk space and increase db_recovery_file_dest_size parameter to

   reflect the new space.

. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
 ************************************************************************ 
system command was used to delete files, then use RMAN CROSSCHECK and
 ARC1: Error  Creating archive log file to '/11.2.0.4/app/oracle/fast_recovery_area/TT11204/archivelog/2019_05_13/o1_mf_1_115_%u_.arc' 
DELETE EXPIRED commands. ************************************************************************

相关文章