【Oracle】 rman 刪除歸檔日誌的命令

楊奇龍發表於2012-01-30
Oracle
    因為資料倉儲上asm的磁碟空間不足，需要刪除早期的歸檔日誌，檢視資料有以下兩種方式：
delete archivelog until time 'sysdate-N';
delete archivelog all completed before 'sysdate-N';
這兩個命令的差別在哪裡呢？
使用list 命令來檢視一下究竟...
RMAN>  list archivelog until time 'sysdate-1';
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name YANGDB
=====================================================================
Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
459     1    463     A 08-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_463_762800109.log
460     1    464     A 08-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_464_762800109.log
461     1    465     A 08-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_465_762800109.log
462     1    466     A 08-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_466_762800109.log
463     1    467     A 09-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_467_762800109.log
464     1    468     A 09-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_468_762800109.log
465     1    469     A 10-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_469_762800109.log
466     1    470     A 11-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_470_762800109.log
467     1    471     A 11-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_471_762800109.log
468     1    472     A 12-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_472_762800109.log
469     1    473     A 13-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_473_762800109.log
470     1    474     A 13-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_474_762800109.log
471     1    475     A 14-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_475_762800109.log
472     1    476     A 15-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_476_762800109.log
歸檔日誌的序列號碼到476
RMAN> list archivelog all completed before 'sysdate-1';
List of Archived Log Copies for database with db_unique_name YANGDB
=====================================================================
Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
459     1    463     A 08-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_463_762800109.log
460     1    464     A 08-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_464_762800109.log
461     1    465     A 08-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_465_762800109.log
462     1    466     A 08-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_466_762800109.log
463     1    467     A 09-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_467_762800109.log
464     1    468     A 09-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_468_762800109.log
465     1    469     A 10-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_469_762800109.log
466     1    470     A 11-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_470_762800109.log
467     1    471     A 11-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_471_762800109.log
468     1    472     A 12-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_472_762800109.log
469     1    473     A 13-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_473_762800109.log
470     1    474     A 13-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_474_762800109.log
471     1    475     A 14-JAN-12 Name: /opt/oracle/yangdb_arch/yangdb_1_475_762800109.log
歸檔日誌的序列號碼到475 
檢視v$archived_log 檢視從中獲取 時間和序列號資訊！
SYS@yangdb> select THREAD#,sequence#,FIRST_TIME,COMPLETION_TIME  
2   from v$archived_log order by thread#,sequence#;

THREAD# SEQUENCE# FIRST_TIME      COMPLETION_TIME
------- ---------- -------------- -------------------
1        470  2012-01-11 11:00:10  2012-01-11 22:08:23
1        471  2012-01-11 22:08:20  2012-01-12 17:00:10
1        472  2012-01-12 17:00:10  2012-01-13 02:00:28
1        473  2012-01-13 02:00:28  2012-01-13 22:09:56
1        474  2012-01-13 22:09:55  2012-01-14 22:01:02
1        475  2012-01-14 22:01:02  2012-01-15 06:46:14 ==list archivelog all completed 以COMPLETION_TIME為基準   
1        476  2012-01-15 06:46:14  2012-01-30 14:10:18 ==list archivelog until 以FIRST_TIME 為基準 編號476的日誌橫跨15號和30號兩天，以first_time的值來擷取！
1        477  2012-01-30 14:10:15  2012-01-30 14:10:23
1        478  2012-01-30 14:10:22  2012-01-30 14:10:29
1        479  2012-01-30 14:10:28  2012-01-30 14:10:33
1        480  2012-01-30 14:10:32  2012-01-30 14:10:40  

總結：
delete archivelog until time 'sysdate-N';
該命令刪除日誌時的時間基準是first_name
delete archivelog all completed before 'sysdate-N';
該命令刪除日誌時的時間基準是completion_time   

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-715261/，如需轉載，請註明出處，否則將追究法律責任。

相關文章