delete删除数据造成归档⽇志增加，操作系统空间不⾜导致数
据库hang住
业务需求，对⽇志表历史数据进⾏清理。历史表均很⼤，使⽤delete 操作删除90天前的数据。
第⼀部分：快速删除数据
SQL> alter table CC.F_LOG parallel 4;
SQL>alter ssion enable parallel dml;
SQL> delete FROM CC.F_LOG S WHERE S.CREATE_DATE>(SYSDATE-90);
执⾏计划确定并⾏
-----------------------------------------------
| Id | Operation |
-----------------------------------------------
| 0 | DELETE STATEMENT |
| 1 | PX COORDINATOR |
| 2 | PX SEND QC (RANDOM) |
| 3 | INDEX MAINTENANCE |
| 4 | PX RECEIVE |
| 5 | PX SEND RANGE |
| 6 | DELETE |
| 7 | BUFFER SORT |
| 8 | PX RECEIVE |
| 9 | PX SEND HASH (BLOCK ADDRESS)|
|* 10 | INDEX RANGE SCAN |
-
----------------------------------------------
SQL> lect INST_ID,sid,rial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as
"s",LAST_CALL_ET
from gv$ssion where status='ACTIVE' and urname is not null;
INST_ID SID SERIAL# USERNAME STATUS MACHINE SQL_ID EVENT s LAST_CALL_ET
------- ------ ------- ------------------- --------------- -------------------- ------------------------------ ------- ------------
1 24 44857 SYS ACTIVE p01 6yn37jfu2y88k PX Deq Credit: nd blkd 190 189
1 584 28067 SYS ACTIVE p01 2ymxxw3mapxd9 SQL*Net message from client 196 0
1 884 40483 SYS ACTIVE p01 6yn37jfu2y88k PX Deq: Table Q Normal 190 189
1 888 8663 SYS ACTIVE p01 6yn37jfu2y88k PX Deq: Execute Reply 3679 212
1 1157 9679 SYS ACTIVE p01 6yn37jfu2y88k PX Deq: Table Q Normal 190 189
1 116
2 10801 SYS ACTIVE p01 6yn37jfu2y88k direct path write temp 190 189
1 1437 22187 SYS ACTIVE p01 6yn37jfu2y88k PX Deq Credit: nd blkd 190 189
1 1728 17227 SYS ACTIVE p01 6yn37jfu2y88k PX Deq Credit: nd blkd 190 189
1 1740 25759 SYS ACTIVE p01 6yn37jfu2y88k PX Deq: Table Q Normal 190 189
9 rows lected.
删除完毕还原并⾏1
SQL> alter table CC.F_LOG parallel 1;
家眷的拼音
第⼆部分：数据删除hang
删除数据后发现，数据库会话数量越来越多，且delete操作ssion event
log file switch (archiving needed) 等待啥？需要等待⽇志切换归档完成？为啥等待⽇志切换归档这么长时间？观察alert
SQL> lect INST_ID,sid,rial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400as"s",LAST_CALL_ET from gv$ssion where status='ACTIVE' and urname is not null;
INST_ID SID SERIAL# USERNAME STATUS MACHINE SQL_ID EVENT程滨
------- ------ ------- -------------- ---------- -------------------- -------------------- ------------------------------
159751393 CXPT ACTIVE future-app1 fmmd0cjd0ghpf buffer busy waits
159845595 ac ACTIVE 6d7v2nay5vm9n log file switch (archiving needed)
1142941689 ac ACTIVE 3dn60pvzrqz0g buffer busy waits
114311279 CXPT ACTIVE future-app1 azakfbj8ss7f8 buffer busy waits
114491569 ac ACTIVE 7nbhsah5kw0q2 log file switch (archiving needed)
1145325515 SYSTEM ACTIVE ZZYT\YT-P610764 1xz9mk73crhu8 log file switch (archiving needed)
1145838363 ac ACTIVE 3dn60pvzrqz0g buffer busy waits
1170927961 ac ACTIVE d8ats3n1vrm7t enq: US - contention
1171462631 ac ACTIVE 0jg5553p23hup enq: SQ - contention
1171657967 CXPT ACTIVE future-app2 azakfbj8ss7f8 enq: US - contention
1173047159 ac ACTIVE 3dn60pvzrqz0g buffer busy waits
1173110129 ac ACTIVE btgxyzwsc5sd1 enq: US - contention
1200942483 ac ACTIVE pfmpbak01 94cs4sv2zua0u log file switch (archiving needed)
1203364813 ac ACTIVE 3dn60pvzrqz0g buffer busy waits
48 rows lected.
观察ALERT⽇志，可以发现归档⽇志⽆法创建报错
Examine archive trace files for archiving errors
Current log# 8 q# 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 quence# 41278 cannot be archived
globalizationORA-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 quence# 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: ug 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 quence# 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 quence# 41278 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/oracle/oradata/fmpdb/redo04.log'
grav
Tue Aug 13 19:49:35 2019复兴高级中学
minact-scn: ug scan erroring out with error e:12751
biuret
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 quence# 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 quence# 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 quence 41278 ID 0x67cfc4e dest 1:
kr_arc_driver_core: Successful archiving of previously failed ORL
Tue Aug 13 19:56:18 2019
Thread 1 advanced to log quence 41283 (LGWR switch)
Current log# 4 q# 41283 mem# 0: /oracle/oradata/fmpdb/redo04.log
Tue Aug 13 19:56:18 2019evd
Some DDE async actions failed or were cancelled
Tue Aug 13 19:56:23 2019
Archived Log entry 41274 added for thread 1 quence 41281 ID 0x67cfc4e dest 1:
Archived Log entry 41275 added for thread 1 quence 41279 ID 0x67cfc4e dest 1:
Archived Log entry 41276 added for thread 1 quence 41280 ID 0x67cfc4e dest 1:
Archived Log entry 41277 added for thread 1 quence 41282 ID 0x67cfc4e dest 1:
Tue Aug 13 19:56:43 2019
Thread 1 advanced to log quence 41284 (LGWR switch)
Current log# 5 q# 41284 mem# 0: /oracle/oradata/fmpdb/redo05.log
Tue Aug 13 19:56:45 2019
Archived Log entry 41278 added for thread 1 quence 41283 ID 0x67cfc4e dest 1:
Tue Aug 13 19:57:10 2019
Thread 1 cannot allocate new log, quence 41285
操作⼿⼯删除归档⽇志后，快速执⾏脚本压缩备份归档⽇志删除已备份的归档⽇志释放空间。 delete操作恢复且执⾏完毕后，对数据库⽴即进⾏全备份。
[root@pfmpodb01 ~]# df -h
Filesystem Size Ud Avail U% Mounted on
/dev/mapper/VolGroup00-LogVol00 297G 297G 0G 100% /
清理归档⽇志crumble
[root@pfmpodb01 ~]# df -h
Filesystem Size Ud Avail U% 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 Ud Avail U% Mounted on
/dev/mapper/VolGroup00-LogVol00 297G 230G 52G 82% /
Resolving Issues Where 'log file switch (archiving needed)' Waits Occur Becau Log has not yet been Archived (⽂档 ID 1476444.1)
Problem Confirmation:
The time spent actively in the local databa is significant
Only certain ssions, queries or jobs are experiencing slowness (not throughout the databa)
Waits for'log file switch (archiving needed)' are a significant component of DB Time
小布什简历
Free space in archive destination is less than redolog file size
Waits for'log file switch (archiving needed)'
This wait event occurs when waiting for a log switch becau the log we will be switching into has not been archived yet.
Reducing Waits
Check the alert file to make sure that archiving has not stopped due to a failed archive write.
To speed archiving, consider adding more archive process or putting the archive files on striped disks.
Check the archive log destination for space and make sure it is not full. If it is full, free up space.
