文章目录
- 一、Oracle开启归档日志
- 1 关闭数据库
- 2 数据库以挂载模式打开
- 3 使数据库处于归档模式
- 4 打开数据库
- 5 修改归档日志存储路径
- 6 查看归档日志状态
- 7 查看归档日志
- 二、Oracle如何删除归档日志文件?
- 1.切换Oracle用户
- 2.查看归档日志路径
- 3.方法一:删除归档日志物理文件
- 3.1 删除物理文件
- 3.2 查看控制文件
- 3.3 对比检查归档日志物理文件和控制文件一致性
- 3.4 删除过期文件
- 4.方法二:rman删除归档日志
- 4.1 查看控制文件记录
- 4.2 删除指定日期前日志
- 4.3 查看物理文件
- 5.使用到的命令集合
- 三、Oracle定时删除归档日志文件
- 1.定时删除脚本
- 2.root用户下启动cron
- 3.Oracle用户下部署定时任务
- 4.几天后...
一、Oracle开启归档日志
初次安装Oracle数据,没有特殊调整的情况下,归档日志默认是NoArchivelog
模式。当数据库未开启归档日志时,Oracle不会归档联机重做日志文件。因此,当在线重做日志文件满时,会被覆盖掉。这样会导致在数据库需要恢复的时候,没有旧的归档日志,就没办法恢复。
[oracle@ea9446c43596 /]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 6 13:25:24 2022
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
1 关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2 数据库以挂载模式打开
SQL> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2213776 bytes
Variable Size 402655344 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7360512 bytes
Database mounted.
3 使数据库处于归档模式
SQL> alter database archivelog;
Database altered.
4 打开数据库
SQL> alter database open;
Database altered.
5 修改归档日志存储路径
自行创建归档日志文件夹
[oracle@ea9446c43596 /]$ cd /home/oracle/app/oracle/oradata/
[oracle@ea9446c43596 oradata]$ mkdir archlogs
[oracle@ea9446c43596 oradata]$ cd helowin/ & pwd
[1] 1167
/home/oracle/app/oracle/oradata
alter system set log_archive_dest_1='location=/home/oracle/app/oracle/oradata/archlogs';
修改归档日志存储路径
SQL> alter system set log_archive_dest_1='location=/home/oracle/app/oracle/oradata/archlogs';
System altered.
6 查看归档日志状态
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/oradata/archlogs
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
7 查看归档日志
暂时不清楚Oracle生成归档日志的规则;
第二天查看时,已经在对应目录生成归档日志
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name HELOWIN
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 2 A 06-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_2_900206934.dbf
二、Oracle如何删除归档日志文件?
Oracle启用归档日志的状态下,会在指定的归档日志目录生成大量的日志文件,而且这些日志文件默认是不会定期清理。长时间运行下,会占用大量的存储空间,最终可能导致Oracle没有足够的空间维持启动状态。
在Oracle的控制文件中记录着每一个归档日志文件得相关信息。当手动删除了归档日志的物理文件后,控制文件中依然记录着已删除的归档日志信息。所以当我们删除归档日志文件时,不仅需要删除物理文件,还要删除控制文件中对应的信息。
1.切换Oracle用户
[root@ea9446c43596 /]# su - oracle
2.查看归档日志路径
归档日志路径不一定相同,如果不清楚归档日志在哪,可以通过命令find / -name *.dbf
查找
[oracle@ea9446c43596 ~]$ cd /home/oracle/app/oracle/oradata/archlogs/
[oracle@ea9446c43596 archlogs]$ ls
1_10_900206934.dbf 1_12_900206934.dbf 1_14_900206934.dbf 1_5_900206934.dbf 1_7_900206934.dbf 1_9_900206934.dbf
1_11_900206934.dbf 1_13_900206934.dbf 1_4_900206934.dbf 1_6_900206934.dbf 1_8_900206934.dbf
3.方法一:删除归档日志物理文件
如果之前手动删除过物理文件,可以查看这个方法,将控制文件中归档日志记录删除
3.1 删除物理文件
删除物理文件:1_4_900206934.dbf
[oracle@ea9446c43596 archlogs]$ rm -f 1_4_900206934.dbf
3.2 查看控制文件
通过如下命令查看Oracle控制文件记录归档日志信息,发现"1_4_900206934.dbf"还存在
rman target /
rman登录命令list archivelog all;
查看所有的归档日志
[oracle@ea9446c43596 archlogs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 12 10:01:18 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HELOWIN (DBID=1384114315)
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name HELOWIN
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
3 1 4 A 07-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf
4 1 5 A 07-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf
5 1 6 A 08-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf
6 1 7 A 09-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_7_900206934.dbf
7 1 8 A 09-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf
8 1 9 A 10-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf
9 1 10 A 10-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_10_900206934.dbf
10 1 11 A 10-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_11_900206934.dbf
11 1 12 A 11-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_12_900206934.dbf
12 1 13 A 11-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_13_900206934.dbf
13 1 14 A 11-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_14_900206934.dbf
3.3 对比检查归档日志物理文件和控制文件一致性
如果你之前已经删除过物理文件,并没有删除控制文件中的信息,请看如下操作:
crosscheck archivelog all;
优先使用这个命令,核验比对物理文件和归档日志记录信息一致性list expired archivelog all;
crosscheck
后,可以使用这个命令,查看有哪些过期的日志文件
RMAN> crosscheck archivelog all;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=772 device type=DISK
validation failed for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf RECID=3 STAMP=1122847256
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf RECID=4 STAMP=1122894018
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf RECID=5 STAMP=1122948041
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_7_900206934.dbf RECID=6 STAMP=1122980424
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf RECID=7 STAMP=1123029988
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf RECID=8 STAMP=1123066818
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_10_900206934.dbf RECID=9 STAMP=1123095615
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_11_900206934.dbf RECID=10 STAMP=1123124827
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_12_900206934.dbf RECID=11 STAMP=1123153640
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_13_900206934.dbf RECID=12 STAMP=1123184937
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_14_900206934.dbf RECID=13 STAMP=1123211251
Crosschecked 11 objects
RMAN> list expired archivelog all;
List of Archived Log Copies for database with db_unique_name HELOWIN
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
3 1 4 X 07-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf
3.4 删除过期文件
delete expired archivelog all;
删除过期日志文件,在执行命令后,会提示你将要删除哪些过期的日志文件
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=772 device type=DISK
List of Archived Log Copies for database with db_unique_name HELOWIN
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
3 1 4 X 07-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf RECID=3 STAMP=1122847256
Deleted 1 EXPIRED objects
4.方法二:rman删除归档日志
这种方法,可以将控制文件中记录信息和物理文件同步删除,更为方便。
4.1 查看控制文件记录
rman target /
登录rmanlist archivelog all;
查看所有归档日志信息
[oracle@ea9446c43596 archlogs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 12 10:14:25 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HELOWIN (DBID=1384114315)
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name HELOWIN
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
4 1 5 A 07-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf
5 1 6 A 08-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf
6 1 7 A 09-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_7_900206934.dbf
7 1 8 A 09-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf
8 1 9 A 10-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf
9 1 10 A 10-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_10_900206934.dbf
10 1 11 A 10-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_11_900206934.dbf
11 1 12 A 11-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_12_900206934.dbf
12 1 13 A 11-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_13_900206934.dbf
13 1 14 A 11-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_14_900206934.dbf
4.2 删除指定日期前日志
delete archivelog all completed before 'SYSDATE - 3';
删除3天前的归档日志,在删除控制文件记录的同时,也会将物理文件删除。
RMAN> delete archivelog all completed before 'SYSDATE - 3';
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=772 device type=DISK
List of Archived Log Copies for database with db_unique_name HELOWIN
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
4 1 5 A 07-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf
5 1 6 A 08-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf RECID=4 STAMP=1122894018
deleted archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf RECID=5 STAMP=1122948041
Deleted 2 objects
4.3 查看物理文件
在上一步中删除了1_5_900206934.dbf
和1_6_900206934.dbf
文件,观察物理文件列表后,可以看到物理文件也会被同步删除。
[oracle@ea9446c43596 archlogs]$ ls
1_10_900206934.dbf 1_12_900206934.dbf 1_14_900206934.dbf 1_8_900206934.dbf
1_11_900206934.dbf 1_13_900206934.dbf 1_7_900206934.dbf 1_9_900206934.dbf
5.使用到的命令集合
- 登录rman
rman target /
- 查看所有归档日志
list archivelog all;
- 对比控制文件、物理文件
crosscheck archivelog all;
- 查看过期归档日志
list expired archivelog all;
- 删除过期归档日志
delete expired archivelog all;
- 删除指定日期归档日志
delete archivelog all completed before 'SYSDATE - n';
- 退出rman
exit;
三、Oracle定时删除归档日志文件
1.定时删除脚本
- crosscheck检查物理文件和归档日志
- delete expired删除过期归档日志
- delete …unitl…删除指定日期之前的日志
[oracle@ea9446c43596 oradata]$ vi delete_arc.sh
[oracle@ea9446c43596 oradata]$ chmod +x delete_arc.sh
- 脚本内容
#! /bin/bash
source ~/.bash_profile
exec >> /home/oracle/app/oracle/oradata/deletelogs/del_arch`date +%F-%H`.log
$ORACLE_HOME/bin/rman target / <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog until time 'sysdate-7';
exit;
EOF
2.root用户下启动cron
[root@ea9446c43596 oradata]# service crond start
Starting crond: [ OK ]
3.Oracle用户下部署定时任务
- 切换用户
[root@ea9446c43596 oradata]# su - oracle
- 部署定时任务
[oracle@ea9446c43596 ~]$ crontab -e
0 2 * * * /home/oracle/app/oracle/oradata/delete_arc.sh
- 查看定时任务
[oracle@ea9446c43596 ~]$ crontab -l
0 2 * * * /home/oracle/app/oracle/oradata/delete_arc.sh
4.几天后…
通过日志del_arch2022-12-17-02.log
可以看到,2022年12月17号2点的日志文件中,定时删除了七天前的1_8_900206934.dbf
和1_9_900206934.dbf
两个日志文件。
[oracle@ea9446c43596 deletelogs]$ cat del_arch2022-12-17-02.log
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 17 02:00:01 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HELOWIN (DBID=1384114315)
RMAN>
RMAN>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=775 device type=DISK
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf RECID=7 STAMP=1123029988
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf RECID=8 STAMP=1123066818
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_10_900206934.dbf RECID=9 STAMP=1123095615
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_11_900206934.dbf RECID=10 STAMP=1123124827
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_12_900206934.dbf RECID=11 STAMP=1123153640
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_13_900206934.dbf RECID=12 STAMP=1123184937
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_14_900206934.dbf RECID=13 STAMP=1123211251
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_15_900206934.dbf RECID=14 STAMP=1123243645
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_16_900206934.dbf RECID=15 STAMP=1123300833
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_17_900206934.dbf RECID=16 STAMP=1123326019
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_18_900206934.dbf RECID=17 STAMP=1123376427
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_19_900206934.dbf RECID=18 STAMP=1123412416
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_20_900206934.dbf RECID=19 STAMP=1123455608
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_21_900206934.dbf RECID=20 STAMP=1123498814
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_22_900206934.dbf RECID=21 STAMP=1123502886
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_23_900206934.dbf RECID=22 STAMP=1123560383
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_24_900206934.dbf RECID=23 STAMP=1123585220
validation succeeded for archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_25_900206934.dbf RECID=24 STAMP=1123599642
Crosschecked 18 objects
RMAN>
RMAN>
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=775 device type=DISK
specification does not match any archived log in the repository
RMAN>
RMAN>
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=775 device type=DISK
List of Archived Log Copies for database with db_unique_name HELOWIN
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
7 1 8 A 09-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf
8 1 9 A 10-DEC-22
Name: /home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf
deleted archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf RECID=7 STAMP=1123029988
deleted archived log
archived log file name=/home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf RECID=8 STAMP=1123066818
Deleted 2 objects
RMAN>
Recovery Manager complete.