达梦数据库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 查看。