Oracle的归档操作
1、查询数据库归档模式
两种方式：查询v$database和命令查看。
NOARCHIVELOG和No Archive Mode都是未开启归档
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL NOARCHIVELOG
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/app/oracle/product/19.3.0/db_1/dbs/arch
Oldest online log sequence 28
Current log sequence 30
2、开启或关闭归档模式
开启和关闭归档模式都需要在mount模式进行，即需要把数据库重启到mount模式下。
SQL> startup mount
ORACLE instance started.
Total System Global Area 830469464 bytes
Fixed Size 8901976 bytes
Variable Size 666894336 bytes
Database Buffers 150994944 bytes
Redo Buffers 3678208 bytes
Database mounted.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/app/oracle/product/19.3.0/db_1/dbs/arch
Oldest online log sequence 28
Current log sequence 30
– 开启归档模式
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/app/oracle/product/19.3.0/db_1/dbs/arch
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
– 关闭归档模式
SQL> alter database noarchivelog;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/app/oracle/product/19.3.0/db_1/dbs/arch
Oldest online log sequence 28
Current log sequence 30
3、修改归档日志文件默认设置
归档日志的默认位置是$ORACLE_HOME/dbs/arch目录，一般需要重新指定位置，例如：/archivelog
SQL> alter system set log_archive_dest_1='location=/archivelog';
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
4、修改归档日志文件格式
默认的归档日志文件格式后缀名为dbf，而oracle的表空间数据文件名称后缀也是dbf，为了更好的区分两者，我们自定义下归档的归档日志文件的格式，就以arch为后缀吧。由于log_archive_format参数是静态的，需要重启才能生效。
缺省值：%t_%s_%r.dbf
参数值说明：
%s: 日志序列号
%S: 日志序列号(带有前导)
%t: 重做线程编号.
%T: 重做线程编号(带有前导)
%a: 活动ID号
%d: 数据库ID号
%r: RESETLOGS的ID值.
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- ------------------
log_archive_format string %t_%s_%r.dbf
SQL> alter system set log_archive_format='%t_%s_%r.arch' scope=spfile;
System altered.
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- ---------
log_archive_format string %t_%s_%r.dbf
– 重启数据库，由于我们一直是在mount状态下操作，
– 所以关闭数据库是提示数据库没有open是正常的。
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 830469464 bytes
Fixed Size 8901976 bytes
Variable Size 666894336 bytes
Database Buffers 150994944 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.
– 查看归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
– 查看归档日志文件设置，已经显示为自定义设置的后缀文件名了
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- -----------
log_archive_format string %t_%s_%r.arch
5、切换日志查看生成归档信息
数据库中查看
SQL> alter system switch logfile;
System altered.
SQL> select recid, name, first_time from v$archived_log;
使用rman查看
[oracle@dome-[orcl]-/home/oracle]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jun 4 05:42:25 2024
Version 19.14.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1627730222)
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=460 device type=DISK
validation succeeded for archived log
archived log file name=/archivelog/1_30_1099949486.arch RECID=1 STAMP=1106458725
Crosschecked 1 objects
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=460 device type=DISK
validation succeeded for archived log
archived log file name=/archivelog/1_30_1099949486.arch RECID=1 STAMP=1106458725
validation succeeded for archived log
archived log file name=/archivelog/1_31_1099949486.arch RECID=2 STAMP=1106458993
Crosschecked 2 objects
6、归档日志的删除
crosscheck archivelog all：检查log_archive_dest参数指定位置的文件，如果有系统rm删除的归档日志，会有验证失败提示validation failed for archived log，结合delete noprompt expired archivelog all;语句删除过期归档日志。
RMAN> delete noprompt archivelog all;
released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=268 device type=DISK
List of Archived Log Copies for database with db_unique_name CDB1
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
1 1 12 A 2024-08-16 08:32:10
Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_21/o1_mf_1_12_mdbz2690_.arc
2 1 13 A 2024-08-21 13:33:58
Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_21/o1_mf_1_13_mdc32yw7_.arc
3 1 14 A 2024-08-21 14:42:38
Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_23/o1_mf_1_14_mdjdwvp8_.arc
4 1 15 A 2024-08-23 15:00:43
Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_23/o1_mf_1_15_mdjkg7dt_.arc
5 1 16 A 2024-08-23 16:18:15
Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_26/o1_mf_1_16_mdr6gy8c_.arc
6 1 17 A 2024-08-26 13:59:58
Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_09_02/o1_mf_1_17_mfbmx6ng_.arc
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_21/o1_mf_1_12_mdbz2690_.arc RECID=1 STAMP=1177594439
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_21/o1_mf_1_13_mdc32yw7_.arc RECID=2 STAMP=1177598559
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_23/o1_mf_1_14_mdjdwvp8_.arc RECID=3 STAMP=1177772444
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_23/o1_mf_1_15_mdjkg7dt_.arc RECID=4 STAMP=1177777095
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_26/o1_mf_1_16_mdr6gy8c_.arc RECID=5 STAMP=1178027999
Deleted 3 objects
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_09_02/o1_mf_1_17_mfbmx6ng_.arc RECID=6 STAMP=1178631593
Deleted 3 objects
具体用法和个别参数说明
force：强制删除日志
noprompt：忽略提示
验证日志
crosscheck archivelog all;
列出过期日志
list expired archivelog all;
删除所有过期日志
delete noprompt expired archivelog all;
删除所有归档日志
delete noprompt archivelog all;
按日期区间删除
delete noprompt archivelog until time "to_date('yyyy-mm-dd','YYYY-MM-DD')";
按时间区间删除
delete noprompt archivelog until time "to_date('2022-06-03 00:00:00','2022-06-03 03:00:00')";
删除7天前的日志
delete noprompt archivelog all completed before 'sysdate-7';
删除1小时以前的日志
delete noprompt archivelog all completed before 'sysdate-1/24';
删除30分钟以前的日志
delete noprompt archivelog all completed before 'sysdate-1/48';
7、脚本和定时任务删除
DG环境下的归档日志删除脚本，主库保留3天，备库保留10个
source /home/oracle/.bash_profile
export logfile=/home/oracle/rman_del_arch_${ORACLE_SID}.log
delPriArch(){
echo "begin deleting primary archivelog"
rman target / log=${logfile} append <<EOF
delete noprompt archivelog until time 'SYSDATE-3';
exit;
EOF
}
getsql(){
sqlplus -S "/as sysdba" <<EOF
set head off
set feedback off
set time off
set timing off
set echo off
select 'delete noprompt archivelog from sequence 0 until sequence '||max(sequence# - 10)|| ' thread ' || thread# ||';'
from v\$archived_log where applied='YES' group by thread#;
exit
EOF
}
delStdArch(){
sql=$(getsql)
echo "begin deleting standby archivelog"
rman target / log=${logfile} append <<EOF
$sql
exit;
EOF
}
getrole() {
sqlplus -S "/as sysdba" <<EOF
set head off
set feedback off
set echo off
set time off
set timing off
select database_role from v\$database;
exit
EOF
}
role=$(getrole)
role=`echo ${role} |sed 's/ //g'`
echo $role
if [ "${role}" = "PRIMARY" ];then
delPriArch;
elif [ "${role}" = "PHYSICALSTANDBY" ];then
delStdArch;
else
echo "error, unable to connect to the database, try again later"
fi
配置系统定时任务
0 0,2,4,6,8,10,12,14,16,18,20,22 * * * sh /home/oracle/rman_del_arch.sh
原文地址：https://blog.csdn.net/2301_76664379/article/details/142521744
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！