清理DB2的歸檔日志

DB2歸檔日志的相關信息保存在恢復歷史文件中，可以通過 db2 list history 命令查看。    db2inst1@suse-db:~> db2 list history archive log all for ggyy    List History File for ggyy    Number of matching file entries = 6    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  -- --- ------------------ ---- --- ------------ ------------ --------------  X D 20100103235553 1 D S0000038.LOG C0000007  ----------------------------------------------------------------------------  ----------------------------------------------------------------------------  Comment:  Start Time: 20100103235553  End Time: 20100103235553  Status: A   www.2cto.com   ----------------------------------------------------------------------------  EID: 80 Location: /data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000038.LOG    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  -- --- ------------------ ---- --- ------------ ------------ --------------  X D 20100103235553 N S0000038.LOG  ----------------------------------------------------------------------------  ----------------------------------------------------------------------------  Comment: ARCHIVE LOG  Start Time: 20100103235553  End Time: 20100103235553  Status: A  ----------------------------------------------------------------------------  EID: 81    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  -- --- ------------------ ---- --- ------------ ------------ --------------  X D 20100103235608 1 D S0000039.LOG C0000007  ----------------------------------------------------------------------------  ----------------------------------------------------------------------------  Comment:  Start Time: 20100103235608  End Time: 20100103235608  Status: A  ----------------------------------------------------------------------------  EID: 82 Location: /data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000039.LOG    ......    清除這些信息，需要使用 db2 prune history 命令。該命令會清除指定時間戳及之前的所有條目（不只是歸檔日志的）。默認情況下，系統不會清除最近一次數據庫全備之後的歸檔日志信息。    db2inst1@suse-db:~> db2 prune history 20100103235553  SQL1024N A database connection does not exist. SQLSTATE=00000  db2inst1@suse-db:~> db2 connect to ggyy   www.2cto.com     Database Connection Information    Database server = DB2/LINUX 9.7.0  SQL authorization ID = DB2INST1  Local database alias = GGYY    db2inst1@suse-db:~> db2 prune history 20100103235553  DB20000I The PRUNE command completed successfully.  db2inst1@suse-db:~> db2 list history archive log all for ggyy | head -20    List History File for ggyy    Number of matching file entries = 6    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  -- --- ------------------ ---- --- ------------ ------------ --------------  X D 20100103235553 1 D S0000038.LOG C0000007  ----------------------------------------------------------------------------    ----------------------------------------------------------------------------  Comment:  Start Time: 20100103235553  End Time: 20100103235553  Status: A  ----------------------------------------------------------------------------  EID: 80 Location: /data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000038.LOG    db2inst1@suse-db:~> db2 list history backup all for ggyy    List History File for ggyy    Number of matching file entries = 0    對數據庫進行一次全備，再次嘗試清除：    db2inst1@suse-db:~> db2 backup db ggyy online to /data/backup    Backup successful. The timestamp for this backup image is : 20100104004121    db2inst1@suse-db:~> db2 connect to ggyy   www.2cto.com     Database Connection Information    Database server = DB2/LINUX 9.7.0  SQL authorization ID = DB2INST1  Local database alias = GGYY    db2inst1@suse-db:~> db2 prune history 20100103235553  DB20000I The PRUNE command completed successfully.  db2inst1@suse-db:~> db2 list history archive log all for ggyy | head -20    List History File for ggyy    Number of matching file entries = 5    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  -- --- ------------------ ---- --- ------------ ------------ --------------  X D 20100103235608 1 D S0000039.LOG C0000007  ----------------------------------------------------------------------------    ----------------------------------------------------------------------------  Comment:  Start Time: 20100103235608  End Time: 20100103235608  Status: A  ----------------------------------------------------------------------------  EID: 82 Location: /data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000039.LOG    通過使用 and delete 參數，可以在清理條目的同時刪除對應的歸檔日志文件。    db2inst1@suse-db:~> ls -l /data/palog/db2inst1/GGYY/NODE0000/C0000007  total 48  -rw-r----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:55 S0000038.LOG  -rw-r----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:56 S0000039.LOG  -rw-r----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:56 S0000040.LOG  -rw-r----- 1 db2inst1 db2iadm1 12288 2010-01-04 00:41 S0000041.LOG  db2inst1@suse-db:~> db2 prune history 20100103235608 and delete  DB20000I The PRUNE command completed successfully.   www.2cto.com   db2inst1@suse-db:~> db2 list history archive log all for ggyy | head -20    List History File for ggyy    Number of matching file entries = 3    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  -- --- ------------------ ---- --- ------------ ------------ --------------  X D 20100103235618 1 D S0000040.LOG C0000007  ----------------------------------------------------------------------------    ----------------------------------------------------------------------------  Comment:  Start Time: 20100103235618  End Time: 20100103235618  Status: A  ----------------------------------------------------------------------------  EID: 84 Location: /data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000040.LOG    db2inst1@suse-db:~> ls -l /data/palog/db2inst1/GGYY/NODE0000/C0000007  total 36  -rw-r----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:55 S0000038.LOG  -rw-r----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:56 S0000040.LOG  -rw-r----- 1 db2inst1 db2iadm1 12288 2010-01-04 00:41 S0000041.LOG    可以看到，歸檔日志文件S0000039.LOG已被刪除，但S0000038.LOG還存在，因為之前已經清除了S0000038.LOG的相關條目。  嘗試同時清除S0000040.LOG和S0000041.LOG：    db2inst1@suse-db:~> db2 list history archive log all for ggyy | tail -15    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  -- --- ------------------ ---- --- ------------ ------------ --------------  X D 20100104004127 1 D S0000041.LOG C0000007  ----------------------------------------------------------------------------    ----------------------------------------------------------------------------  Comment:  Start Time: 20100104004127  End Time: 20100104004128  Status: A   www.2cto.com   ----------------------------------------------------------------------------  EID: 86 Location: /data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000041.LOG    db2inst1@suse-db:~> db2 prune history 20100104004127 and delete  DB20000I The PRUNE command completed successfully.  db2inst1@suse-db:~> db2 list history archive log all for ggyy | head -20    List History File for ggyy    Number of matching file entries = 1      Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID  -- --- ------------------ ---- --- ------------ ------------ --------------  X D 20100104004127 1 D S0000041.LOG C0000007  ----------------------------------------------------------------------------    ----------------------------------------------------------------------------  Comment:  Start Time: 20100104004127  End Time: 20100104004128  Status: A   www.2cto.com   ----------------------------------------------------------------------------  EID: 86 Location: /data/palog/db2inst1/GGYY/NODE0000/C0000007/S0000041.LOG    db2inst1@suse-db:~> ls -l /data/palog/db2inst1/GGYY/NODE0000/C0000007/  total 24  -rw-r----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:55 S0000038.LOG  -rw-r----- 1 db2inst1 db2iadm1 12288 2010-01-04 00:41 S0000041.LOG    S0000041.LOG依然存在，因為它是全備之後生成的。如果想要強制清除，可以使用 with force option 參數。    db2inst1@suse-db:~> db2 prune history 20100104004127 with force option and delete  DB20000I The PRUNE command completed successfully.  db2inst1@suse-db:~> db2 list history archive log all for ggyy | head -20    List History File for ggyy    Number of matching file entries = 0    db2inst1@suse-db:~> ls -l /data/palog/db2inst1/GGYY/NODE0000/C0000007/  total 12   www.2cto.com   -rw-r----- 1 db2inst1 db2iadm1 12288 2010-01-03 23:55 S0000038.LOG    注意，這裡連帶著將數據庫全備的條目也刪除了，但備份文件還在：    db2inst1@suse-db:/data/backup> db2 list history backup all for ggyy    List History File for ggyy    Number of matching file entries = 0    db2inst1@suse-db:/data/backup> ls -l /data/backup  total 99994  -rw------- 1 db2inst1 db2iadm1 102293504 2010-01-04 00:41 GGYY.0.db2inst1.NODE0000.CATN0000.20100104004121.001    此外，使用 db2 prune logfile 命令可以清除活動日志目錄下的在線歸檔日志文件，但對離線歸檔日志文件無效，這裡不再贅述。       作者 jackytang520