delete删除数据造成归档日志增加，操作系统空间不足导致数据库hang住

删除数据后发现，数据库会话数量越来越多，且delete操作session event

log file switch (archiving needed)  等待啥？需要等待日志切换归档完成？  为啥等待日志切换归档这么长时间？ 观察alert

SQL>  select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)* as "s",LAST_CALL_ET
 from gv$session where status='ACTIVE' and username is not null;
INST_ID    SID SERIAL# USERNAME  STATUS     MACHINE              SQL_ID               EVENT

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

              CXPT      ACTIVE     future-app1          fmmd0cjd0ghpf        buffer busy waits

              ac        ACTIVE     pfmptom01.yutong.com 6d7v2nay5vm9n        log file switch (archiving needed)

             ac        ACTIVE     pfmptom02.yutong.com 3dn60pvzrqz0g        buffer busy waits

              CXPT      ACTIVE     future-app1          azakfbj8ss7f8        buffer busy waits

              ac        ACTIVE     pfmptom01.yutong.com 7nbhsah5kw0q2        log file switch (archiving needed)

             SYSTEM    ACTIVE     ZZYT\YT-P610764      1xz9mk73crhu8        log file switch (archiving needed)

             ac        ACTIVE     pfmptom02.yutong.com 3dn60pvzrqz0g        buffer busy waits

             ac        ACTIVE     pfmptom01.yutong.com d8ats3n1vrm7t        enq: US - contention

             ac        ACTIVE     pfmptom01.yutong.com 0jg5553p23hup        enq: SQ - contention

             CXPT      ACTIVE     future-app2          azakfbj8ss7f8        enq: US - contention

             ac        ACTIVE     pfmptom02.yutong.com 3dn60pvzrqz0g        buffer busy waits

             ac        ACTIVE     pfmptom01.yutong.com btgxyzwsc5sd1        enq: US - contention

             ac        ACTIVE     pfmpbak01            94cs4sv2zua0u        log file switch (archiving needed)

             ac        ACTIVE     pfmptom02.yutong.com 3dn60pvzrqz0g        buffer busy waits

 rows selected.
观察ALERT日志，可以发现归档日志无法创建报错

Examine archive trace files for archiving errors
Current log# 8 seq# 41282 mem# 0: /oracle/oradata/fmpdb/redo08.log
Tue Aug 13 19:41:12 2019
ARC1: Encountered disk I/O error 19502
ARC1: Closing local archive destination LOG_ARCHIVE_DEST_1: '/oracle/archivelog/fmpdb_1_41278_961338832.log' (error 19502) (fmpdb)
ARC1: I/O error 19502 archiving log 4 to '/oracle/archivelog/fmpdb_1_41278_961338832.log'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance fmpdb - Archival Error
ORA-16038: log 4 sequence# 41278 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 4 thread 1: '/oracle/oradata/fmpdb/redo04.log'
Tue Aug 13 19:41:13 2019
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance fmpdb - Archival Error
ORA-16014: log 4 sequence# 41278 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/oracle/oradata/fmpdb/redo04.log'
Tue Aug 13 19:44:28 2019
minact-scn: useg scan erroring out with error e:12751
Tue Aug 13 19:46:14 2019
ARC1: Encountered disk I/O error 19502
ARC1: Closing local archive destination LOG_ARCHIVE_DEST_1: '/oracle/archivelog/fmpdb_1_41278_961338832.log' (error 19502) (fmpdb)
ARC1: I/O error 19502 archiving log 4 to '/oracle/archivelog/fmpdb_1_41278_961338832.log'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance fmpdb - Archival Error
ORA-16038: log 4 sequence# 41278 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 4 thread 1: '/oracle/oradata/fmpdb/redo04.log'
Tue Aug 13 19:46:14 2019
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance fmpdb - Archival Error
ORA-16014: log 4 sequence# 41278 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/oracle/oradata/fmpdb/redo04.log'
Tue Aug 13 19:49:35 2019
minact-scn: useg scan erroring out with error e:12751
Tue Aug 13 19:51:15 2019
ARC1: Encountered disk I/O error 19502
ARC1: Closing local archive destination LOG_ARCHIVE_DEST_1: '/oracle/ARC1: I/O error 19502 archiving log 4 to

'/oracle/archivelog/fmpdb_1_41278_961338832.log'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance fmpdb - Archival Error
ORA-16038: log 4 sequence# 41278 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 4 thread 1: '/oracle/oradata/fmpdb/redo04.log'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance fmpdb - Archival Error
ORA-16014: log 4 sequence# 41278 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/oracle/oradata/fmpdb/redo04.log'

Tue Aug 13 19:56:16 2019
Archiver process freed from errors. No longer stopped
Tue Aug 13 19:56:18 2019
Archived Log entry 41273 added for thread 1 sequence 41278 ID 0x67cfc4e dest 1:
krse_arc_driver_core: Successful archiving of previously failed ORL
Tue Aug 13 19:56:18 2019
Thread 1 advanced to log sequence 41283 (LGWR switch)
Current log# 4 seq# 41283 mem# 0: /oracle/oradata/fmpdb/redo04.log
Tue Aug 13 19:56:18 2019
Some DDE async actions failed or were cancelled
Tue Aug 13 19:56:23 2019
Archived Log entry 41274 added for thread 1 sequence 41281 ID 0x67cfc4e dest 1:
Archived Log entry 41275 added for thread 1 sequence 41279 ID 0x67cfc4e dest 1:
Archived Log entry 41276 added for thread 1 sequence 41280 ID 0x67cfc4e dest 1:
Archived Log entry 41277 added for thread 1 sequence 41282 ID 0x67cfc4e dest 1:
Tue Aug 13 19:56:43 2019
Thread 1 advanced to log sequence 41284 (LGWR switch)
Current log# 5 seq# 41284 mem# 0: /oracle/oradata/fmpdb/redo05.log
Tue Aug 13 19:56:45 2019
Archived Log entry 41278 added for thread 1 sequence 41283 ID 0x67cfc4e dest 1:
Tue Aug 13 19:57:10 2019
Thread 1 cannot allocate new log, sequence 41285

操作手工删除归档日志后，快速执行脚本压缩备份归档日志删除已备份的归档日志释放空间。 delete操作恢复且执行完毕后，对数据库立即进行全备份。
[root@pfmpodb01 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00 297G 297G 0G 100% /

清理归档日志
[root@pfmpodb01 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00 297G 271G 12G 97% /

压缩备份归档日志后删除日志
backup format '/oracle/dbbak/archbak_%d_%s_%p_%T' archivelog all delete input;

[root@pfmpodb01 archivelog]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00 297G 230G 52G 82% /