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

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

enmo:/home/oracle/worksh dg.sh
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 13 04:19:13 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 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-19815: WARNING: db_recovery_file_dest_size of 4385144832 bytes is 100.00% used, and has 0 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:
1. 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
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. 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 19809 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. ************************************************************************

转载于:https://www.cnblogs.com/lvcha001/p/10857437.html