暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / 20230602_what operation cause the Oracle db redo switch logfile

20230602_what operation cause the Oracle db redo switch logfile

原创 惠星星 云和恩墨技术服务团队 2023-06-26
563

适用范围

Oracle Database - Enterprise Edition - Version 11.2.0.4

问题概述

近期有几个客户咨询关于redolog异常切换的疑问，本文对redolog switch的触发条件进行测试总结

问题原因

情况1. redo写满自动切换

情况2. alter system switch logfile.

### 实例1操作
SQL> alter system switch logfile;

System altered.

SQL> 

### 实例1日志
2023-06-16T10:58:06.664928+08:00
Thread 1 advanced to log sequence 22 (LGWR switch)
  Current log# 2 seq# 22 mem# 0: +DATA01/ORCL/ONLINELOG/group_2.264.1129497131
  Current log# 2 seq# 22 mem# 1: +DATA01/ORCL/ONLINELOG/group_2.266.1129497131
2023-06-16T10:58:06.902512+08:00
ARC1 (PID:10266): Archived Log entry 35 added for T-1.S-21 ID 0x62c77aa7 LAD:1

小结：
1. 切换当前实例redolog，如果开启归档则只触发本节点日志归档。

情况3. alter system archive log current

### 实例1操作
SQL> alter system checkpoint;

SQL> alter system archive log current;

System altered.

SQL>
### 实例1 日志
2023-06-16T11:01:20.691263+08:00
ALTER SYSTEM ARCHIVE LOG
2023-06-16T11:01:22.339419+08:00
Thread 1 advanced to log sequence 24 (LGWR switch)
  Current log# 2 seq# 24 mem# 0: +DATA01/ORCL/ONLINELOG/group_2.264.1129497131
  Current log# 2 seq# 24 mem# 1: +DATA01/ORCL/ONLINELOG/group_2.266.1129497131
2023-06-16T11:01:22.371567+08:00
NET  (PID:14299): Archived Log entry 39 added for T-1.S-23 ID 0x62c77aa7 LAD:1

### 实例2 日志
2023-06-16T11:01:22.259207+08:00
Thread 2 advanced to log sequence 59 (LGWR switch)
  Current log# 3 seq# 59 mem# 0: +DATA01/ORCL/ONLINELOG/group_3.269.1129497309
  Current log# 3 seq# 59 mem# 1: +DATA01/ORCL/ONLINELOG/group_3.270.1129497311
2023-06-16T11:01:22.323619+08:00
ARC2 (PID:15679): Archived Log entry 38 added for T-2.S-58 ID 0x62c77aa7 LAD:1

小结：
1. 只在数据库开启归档模式执行
2. 首先执行本节点日志归档，再发指令触发其它RAC节点执行日志归档

情况4. rman backup archivelog

RMAN> backup archivelog all format '/home/oracle/backup/arch_db_%U.bkp';

Starting backup at 16-JUN-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=1 STAMP=1139680905
input archived log thread=1 sequence=16 RECID=2 STAMP=1139680926
channel ORA_DISK_1: starting piece 1 at 16-JUN-23
channel ORA_DISK_1: finished piece 1 at 16-JUN-23
piece handle=/home/oracle/backup/arch_db_031us9ku_1_1.bkp tag=TAG20230616T180206 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-JUN-23

Starting Control File and SPFILE Autobackup at 16-JUN-23
piece handle=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/c-868846439-20230616-01 comment=NONE
Finished Control File and SPFILE Autobackup at 16-JUN-23

RMAN>

### 实例1 日志
2023-06-16T18:02:06.693091+08:00
ALTER SYSTEM ARCHIVE LOG
2023-06-16T18:02:06.696638+08:00
Thread 1 advanced to log sequence 17 (LGWR switch)
  Current log# 2 seq# 17 mem# 0: /oradata/db19/redo02.log
2023-06-16T18:02:06.698487+08:00
NET  (PID:28779): Archived Log entry 2 added for T-1.S-16 ID 0x33c96467 LAD:1
2023-06-16T18:02:07.944116+08:00
Control autobackup written to DISK device

handle '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/c-868846439-20230616-01'

小结：
1. 备份归档日志时Oracle数据库会自动触发redo 归档操作，类似于被动执行
alter system archive log current;

情况5 backup datafile #/backup database

备份数据文件及备份数据库均不触发redolog 切换操作

情况6 instance recover

### 实例1 操作
SQL> set linesize 200 pagesize 300
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         13   52428800        512          1 NO  INACTIVE               1058264 14-JUN-23      1060750 16-JUN-23
         2          1         14   52428800        512          1 NO  INACTIVE               1060750 16-JUN-23      1080879 16-JUN-23
         3          1         15   52428800        512          1 NO  CURRENT                1080879 16-JUN-23   2.8147E+14

SQL> delete from t1 where rownum<10;

9 rows deleted.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2421825536 bytes
Fixed Size                  2255632 bytes
Variable Size             620758256 bytes
Database Buffers         1778384896 bytes
Redo Buffers               20426752 bytes
Database mounted.
Database opened.
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         16   52428800        512          1 NO  CURRENT                1101264 16-JUN-23   2.8147E+14
         2          1         14   52428800        512          1 NO  INACTIVE               1060750 16-JUN-23      1080879 16-JUN-23
         3          1         15   52428800        512          1 NO  INACTIVE               1080879 16-JUN-23      1101264 16-JUN-23

SQL>

### 实例1 日志
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Started redo scan
Completed redo scan
 read 61 KB redo, 59 data blocks need recovery
Started redo application at
 Thread 1: logseq 15, block 3
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0
  Mem# 0: /oradata/db11/db11/redo03.log
Completed redo application of 0.04MB
Completed crash recovery at
 Thread 1: logseq 15, block 126, scn 1101262
 59 data blocks read, 59 data blocks written, 61 redo k-bytes read
Thread 1 advanced to log sequence 16 (thread open)
Thread 1 opened at log sequence 16
  Current log# 1 seq# 16 mem# 0: /oradata/db11/db11/redo01.log
  
 小结：
 1. Oracle在使用当前current状态的redo完成instance recover后会自动切换新的日志

情况7 shutdown immediate/startup

正常数据库启停操作不会触发redolog switch.

情况8 media recovery/restore datafile #/recover datafile x;/alter database datafile x online;/alter database datafile x offline;

不会触发redolog switch.

情况9 日志切换触发强制全量检查点时，另一个节点自动切换redo

image-20230616095752550

## 10 backup database plus archivelog

### 小结：
步骤1. 切换日志，并备份归档
步骤2. 备份数据文件
步骤3. 切换日志，并备份新产生归档

RMAN> backup database format '/home/oracle/backup/fullbk_%s_%p_%t.bk' plus archivelog format '/home/oracle/backup/archive_%s_%t';


Starting backup at 04-JUL-23
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=2 STAMP=1141247502
input archived log thread=1 sequence=5 RECID=1 STAMP=1141247502
input archived log thread=1 sequence=6 RECID=3 STAMP=1141247502
input archived log thread=1 sequence=7 RECID=4 STAMP=1141298371
input archived log thread=1 sequence=8 RECID=5 STAMP=1141298462
channel ORA_DISK_1: starting piece 1 at 04-JUL-23
channel ORA_DISK_1: finished piece 1 at 04-JUL-23
piece handle=/home/oracle/backup/archive_1_1141298462 tag=TAG20230704T112102 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-JUL-23

Starting backup at 04-JUL-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/db11/db11/system01.dbf
input datafile file number=00002 name=/oradata/db11/db11/sysaux01.dbf
input datafile file number=00003 name=/oradata/db11/db11/undotbs01.dbf
input datafile file number=00004 name=/oradata/db11/db11/users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-JUL-23
channel ORA_DISK_1: finished piece 1 at 04-JUL-23
piece handle=/home/oracle/backup/fullbk_2_1_1141298463.bk tag=TAG20230704T112103 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 04-JUL-23
channel ORA_DISK_1: finished piece 1 at 04-JUL-23
piece handle=/home/oracle/backup/fullbk_3_1_1141298470.bk tag=TAG20230704T112103 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-JUL-23

Starting backup at 04-JUL-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=6 STAMP=1141298472
channel ORA_DISK_1: starting piece 1 at 04-JUL-23
channel ORA_DISK_1: finished piece 1 at 04-JUL-23
piece handle=/home/oracle/backup/archive_4_1141298472 tag=TAG20230704T112112 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-JUL-23

RMAN>

解决方案

1. 触发redo log switch的情况

情况1. redo写满自动切换
情况2. alter system switch logfile.
情况3. alter system archive log current
情况4. rman backup archivelog
情况6 instance recover
情况9 日志切换触发强制全量检查点时，另一个节点自动切换redo
情况10 backup database plus archivelog <--数据文件备份前后，各触发一次

2. 未触发redo log switch的情况

情况5 backup datafile x /  backup database
情况7 shutdown immediate  /  startup
情况8 包括以下
media recovery
restore datafile x
recover datafile x
alter database datafile x online;
alter database datafile x offline;

参考文档

Checkpoint Tuning and Troubleshooting Guide (Doc ID 147468.1)
墨力计划
最后修改时间：2023-07-04 11:50:44
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论