達夢資料庫DM8之刪除歸檔日誌檔案

1. 檢視歸檔日誌資訊

1）檢查本地歸檔日誌資訊

[dmdba@dmdb01 arch]$ pwd

/dm8/dmdbms/data/dm01/arch

[dmdba@dmdb01 arch]$ ls -lrt

total 1052812

-rw-r--r-- 1 dmdba dinstall 356352 Apr 24 22:12 ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-24_21-20-29.log

-rw-r--r-- 1 dmdba dinstall 430080 Apr 25 19:19 ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-25_10-53-19.log

-rw-r--r-- 1 dmdba dinstall 307712 Apr 26 12:13 ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-26_12-00-37.log

-rw-r--r-- 1 dmdba dinstall 494592 Apr 26 12:35 ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-26_12-13-08.log

-rw-r--r-- 1 dmdba dinstall 872448 Apr 26 19:41 ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-26_12-47-17.log

-rw-r--r-- 1 dmdba dinstall 827904 Apr 27 20:09 ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-27_11-42-08.log

-rw-r--r-- 1 dmdba dinstall 360960 Apr 28 22:11 ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-28_19-54-50.log

-rw-r--r-- 1 dmdba dinstall 353280 Apr 29 18:31 ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-29_14-13-07.log

-rw-r--r-- 1 dmdba dinstall 316928 May 6 11:56 ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-05-06_11-32-02.log

-rw-r--r-- 1 dmdba dinstall 1073741824 May 6 13:44 ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-05-06_11-57-55.log

[dmdba@dmdb01 arch]$

2）使用v$arch_file檢查歸檔日誌資訊

SQL> select arch_seq,path,status,arch_lsn from v$arch_file;

行號 ARCH_SEQ PATH STATUS ARCH_LSN

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

1 4226 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-24_21-20-29.log INACTIVE 25220

2 4245 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-25_10-53-19.log INACTIVE 27883

3 4322 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-26_12-00-37.log INACTIVE 30654

4 4342 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-26_12-13-08.log INACTIVE 33028

5 4491 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-26_12-47-17.log INACTIVE 34747

6 4655 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-27_11-42-08.log INACTIVE 38858

7 5071 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-28_19-54-50.log INACTIVE 43354

8 5105 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-29_14-13-07.log INACTIVE 45989

9 5146 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-05-06_11-32-02.log INACTIVE 48566

10 5172 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-05-06_11-57-55.log ACTIVE 50939

10 rows got

已用時間: 0.253(毫秒). 執行號:523.

SQL>

2. 刪除歸檔日誌方法

方法1：設定歸檔空間大小

在配置資料庫歸檔的時候，可以設定ARCH_SPACE_LIMIT 引數。該參數列示歸檔檔案的磁碟空間限制，如果歸檔檔案總大小超過這個值，

則在生成新歸檔檔案前會刪除最老的一個歸檔檔案。

SQL> ALTER DATABASE ADD ARCHIVELOG 'DEST=/dm8/dmdbms/data/dm/arch, TYPE=LOCAL, FILE_SIZE=1024,

SPACE_LIMIT=51200 ';

或者

配置dmarch.ini檔案

[dmdba@dmdb01 ]$ cat dmarch.ini

#DaMeng Database Archive Configuration file

#this is comments

ARCH_WAIT_APPLY = 0

[ARCHIVE_LOCAL1]

ARCH_TYPE = LOCAL

ARCH_DEST = /dm8/dmdbms/data/dm01/arch

ARCH_FILE_SIZE = 1024

ARCH_SPACE_LIMIT = 51200

[dmdba@dmdb01 dm01]$

注意：將該引數設定為0，表示不受限制；一般建議參照檔案系統大小進行設定，例如/arch路勁50G，建議設定為50G。建議參照備份

策略進行歸檔日誌的刪除，既能保障歸檔日誌的有效性，也能防止歸檔目錄滿了。

方法2：備份歸檔時刪除

在離線備份和聯機備份中，備份歸檔時可以刪除備份的歸檔檔案，比如：

RMAN> backup archivelog all delete input to "ARCH_2022_05_06_14_05_00"

backupset '/dm8/dmdbms/data/dm01/bak/ARCH_2022_05_06_14_05_00';

SQL> backup archivelog lsn between 38858 and 43354 delete input to "ARCH_2022_05_06_14_18_01"

backupset 'ARCH_2022_05_06_14_18_01';

SQL> select arch_seq,path,status,arch_lsn from v$arch_file;

行號 ARCH_SEQ PATH STATUS ARCH_LSN

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

1 5105 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-04-29_14-13-07.log INACTIVE 45989

2 5146 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-05-06_11-32-02.log INACTIVE 48566

3 5172 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-05-06_11-57-55.log INACTIVE 50939

4 5211 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-05-06_14-12-14.log ACTIVE 53378

已用時間: 0.269(毫秒). 執行號:1001.

SQL>

[dmdba@dmdb01 ARCH_2022_05_06_14_18_01]$ ls -l

total 1244

-rw-r--r-- 1 dmdba dinstall 1193984 May 6 14:12 ARCH_2022_05_06_14_18_01.bak

-rw-r--r-- 1 dmdba dinstall 74240 May 6 14:12 ARCH_2022_05_06_14_18_01.meta

[dmdba@dmdb01 ARCH_2022_05_06_14_18_01]$

SQL> backup archivelog time between '2022-04-06 14:24:03' and '2022-05-01 14:24:03' delete input

to "ARCH_2022_05_06_14_24_03" backupset 'ARCH_2022_05_06_14_24_03';

SQL> select arch_seq,path,status,arch_lsn from v$arch_file;

行號 ARCH_SEQ PATH STATUS ARCH_LSN

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

1 5172 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-05-06_11-57-55.log INACTIVE 50939

2 5211 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-05-06_14-12-14.log INACTIVE 53378

3 5216 /dm8/dmdbms/data/dm01/arch/ARCHIVE_LOCAL1_0x58BC45F2[0]_2022-05-06_14-18-08.log ACTIVE 53386

已用時間: 0.351(毫秒). 執行號:1002.

SQL>

[dmdba@dmdb01 ARCH_2022_05_06_14_24_03]$ ls -l

total 736

-rw-r--r-- 1 dmdba dinstall 675328 May 6 14:18 ARCH_2022_05_06_14_24_03.bak

-rw-r--r-- 1 dmdba dinstall 74240 May 6 14:18 ARCH_2022_05_06_14_24_03.meta

[dmdba@dmdb01 ARCH_2022_05_06_14_24_03]$

方法3：使用系統函式刪除

通過SF_ARCHIVELOG_DELETE_BEFORE_TIME 和SF_ARCHIVELOG_DELETE_BEFORE_LSN 函式來直接刪除指定時間或者LSN之前的歸檔檔案。

歸檔日誌的有效 LSN 範圍可以通過 V$ARCH_FILE 檢視。