Oracle的归档操作

devtools/2024/9/29 8:51:45/

1、查询数据库归档模式

两种方式：查询v$database和命令查看。

NOARCHIVELOG和No Archive Mode都是未开启归档

SQL> select name,log_mode from v$database;NAME      LOG_MODE
--------- ------------
ORCL      NOARCHIVELOGSQL> 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.dbfSQL> 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 openDatabase 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.0Copyright (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:10Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_21/o1_mf_1_12_mdbz2690_.arc2       1    13      A 2024-08-21 13:33:58Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_21/o1_mf_1_13_mdc32yw7_.arc3       1    14      A 2024-08-21 14:42:38Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_23/o1_mf_1_14_mdjdwvp8_.arc4       1    15      A 2024-08-23 15:00:43Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_23/o1_mf_1_15_mdjkg7dt_.arc5       1    16      A 2024-08-23 16:18:15Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_08_26/o1_mf_1_16_mdr6gy8c_.arc6       1    17      A 2024-08-26 13:59:58Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2024_09_02/o1_mf_1_17_mfbmx6ng_.arcdeleted 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 objectsdeleted 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}.logdelPriArch(){
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

http://www.ppmy.cn/devtools/118613.html

相关文章

笔记整理—linux进程部分（2）使用fork创建进程

笔记整理—linux进程部分（2）使用fork创建进程

为什么要创建进程&#xff0c;首先每个程序的运行都需要一个进程&#xff1b;多进程实现宏观上的并行。 fork的原理&#xff0c;是进程的分裂生长模式。如果操作系统需要一个新的进程&#xff0c;那么就会以cp的方法得到一个新的进程&#xff0c;此时老的进程是父进程&#xff…
阅读更多...
【电商搜索】现代工业级电商搜索技术-Facebook语义搜索技术QueSearch

【电商搜索】现代工业级电商搜索技术-Facebook语义搜索技术QueSearch

【电商搜索】现代工业级电商搜索技术-Facebook语义搜索技术Que2Search 目录 文章目录 【电商搜索】现代工业级电商搜索技术-Facebook语义搜索技术Que2Search目录0. 论文信息1. 研究背景&#xff1a;2. 技术背景和发展历史&#xff1a;3. 算法建模3.1 模型架构3.1.1 双塔与分类 …
阅读更多...
【春秋云境】CVE-2024-23897-Jenkins 2.441之前版本存在任意文件读取漏洞

【春秋云境】CVE-2024-23897-Jenkins 2.441之前版本存在任意文件读取漏洞

一、靶场介绍 Jenkins 2.441及更早版本&#xff0c;以及LTS 2.426.2及更早版本没有禁用其CLI命令解析器的一个功能&#xff0c;该功能会将参数中’字符后跟的文件路径替换为该文件的内容&#xff0c;允许未经身份验证的攻击者读取Jenkins控制器文件系统上的任意文件。 二、P…
阅读更多...
深刻理解Redis集群（上）：RDB快照和AOF日志

深刻理解Redis集群（上）：RDB快照和AOF日志

RDB快照 save同步阻塞 客户端 服务端 .conf配置文件 # The filename where to dump the DB dbfilename dump.rdb# rdb-del-sync-files是Redis配置文件中的一个选项&#xff0c;它的作用是在主节点上执行BGSAVE或AOF持久化操作时&#xff0c;删除同步锁文件&#xff0c;以释放磁…
阅读更多...
linux环境下使用sqlplus访问远程oracle数据库

linux环境下使用sqlplus访问远程oracle数据库

1.前提 由于sqlplus rpm包安装，需要其它rpm包依赖，建议linux服务器需要提前配置好yum源 2.下载rpm安装包 下载地址(需要oracle account)：Instant Client for Linux x86-64 (64-bit) E.g： oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm oracle-instantclie…
阅读更多...
上交所服务器崩溃：金融交易背后的技术隐患暴露杭州BGP高防服务器43.228.71.X

上交所服务器崩溃：金融交易背后的技术隐患暴露杭州BGP高防服务器43.228.71.X

一、上交所宕机事件始末 2024 年 9 月 27 日&#xff0c;上交所交易系统突发崩溃&#xff0c;这一事件犹如一颗巨石投入平静的湖面&#xff0c;引起了轩然大波。当天上午&#xff0c;众多投资者反馈券商交易出现延迟问题&#xff0c;随后上交所发布了《关于股票竞价交易出现异常…
阅读更多...
【MySQL】MySQL库的操作

【MySQL】MySQL库的操作

> 作者&#xff1a;დ旧言~ > 座右铭&#xff1a;松树千年终是朽&#xff0c;槿花一日自为荣。 > 目标&#xff1a;掌握对库操作的语句并能熟练掌握。 > 毒鸡汤&#xff1a;有些事情&#xff0c;总是不明白&#xff0c;所以我不会坚持。早安! > 专栏选自&#x…
阅读更多...
2016年国赛高教杯数学建模C题电池剩余放电时间预测解题全过程文档及程序

2016年国赛高教杯数学建模C题电池剩余放电时间预测解题全过程文档及程序

2016年国赛高教杯数学建模 C题 电池剩余放电时间预测 铅酸电池作为电源被广泛用于工业、军事、日常生活中。在铅酸电池以恒定电流强度放电过程中&#xff0c;电压随放电时间单调下降&#xff0c;直到额定的最低保护电压&#xff08;Um&#xff0c;本题中为9V&#xff09;。从充…
阅读更多...
最新文章

Copyright @ 2022~2023