问题现象，测试环境执行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. ************************************************************************