今天同事反馈我们一个客户的Oracle RAC数据库出现归档满；无法写入的情况，最好紧急加盘进行了扩容。后面进一步排查发现是数据库闪回打开了，而且闪回日志默认跟归档在同一个diskgroup，由于闪回日志近2年没有进行清理，因此消耗了大量空间。
可以看到查询结果结果高达88万分钟，大概就是600多天，也就是2020年9月1号至今。相关参数设置都是默认值，按理说Oracle会自动进行清理，可是这里却没有进行进行清理闪回日志。
实际上Oracle本身又没有提供清理闪回日志的相关功能或者命令，大概只能通过如下2种方式来操作；
1、修改闪回日志空间大小限制
alter system set “_flashback_max_log_size”=xxxGB scope=both sid=’*’;
2、关闭数据库闪回等Oracle完成闪回日志清理后再打开
首先让同事尝试了第一种方案，发现并没有起作用；由此可见是遇到未知Bug了；Mos上也搜索了一下，确实有想过的bug，导致Oracle不会去清理闪回日志，比如关闭omf的情况下。
这里我模拟一下关闭闪回情况下，观察oracle的清理动作：
oracle@ora19c2:/home/oracle $sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 7 21:22:08 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 8338273712 bytes
Fixed Size 9156016 bytes
Variable Size 2432696320 bytes
Database Buffers 5888802816 bytes
Redo Buffers 7618560 bytes
Database mounted.
Database opened.
SQL> alter database flashback on;
Database altered.
SQL>
SQL>
SQL>
SQL>
SQL> select sysdate, min(first_time), (sysdate-min(first_time))*24*60
from v$flashback_database_logfile; 2
SYSDATE MIN(FIRST (SYSDATE-MIN(FIRST_TIME))*24*60
--------- --------- -------------------------------
07-MAY-22 07-MAY-22 1.16666667
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 8338273712 bytes
Fixed Size 9156016 bytes
Variable Size 2432696320 bytes
Database Buffers 5888802816 bytes
Redo Buffers 7618560 bytes
Database mounted.
Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TDEPDB READ WRITE NO
4 PDB01 READ WRITE NO
5 JYZ READ WRITE NO
6 EMON_TEST READ WRITE NO
SQL> alter database flashback off;
Database altered.
SQL>
---告警日志
2022-05-07T21:25:33.427724+08:00
db_recovery_file_dest_size of 30720 MB is 7.57% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2022-05-07T21:25:41.420079+08:00
RVWR shutting down
2022-05-07T21:25:41.601406+08:00
Deleted Oracle managed file +ARCH/ORA19C/FLASHBACK/log_1.2558.1104096177
Deleted Oracle managed file +ARCH/ORA19C/FLASHBACK/log_2.2628.1104096179
Flashback Database Disabled
2022-05-07T21:26:08.635342+08:0
Oracle闪回日志的写入进程是RVWR；该进程是一个核心进程；可以通过如下方式来查询Oracle数据库 RAC环境下，哪些进程是核心进程，对于核心进程我们是不能随便Kill的，否则会导致实例crash。
SQL> alter database flashback on;
Database altered.
SQL>
SQL>
SQL> SELECT indx,ksuprpnm,TO_CHAR(ksuprflg,'XXXXXXXXXXXXXXXX'),KSUPROSID
FROM x$ksupr
2 3 WHERE BITAND(ksuprflg,4) = 4 ORDER BY indx
4 /
INDX KSUPRPNM TO_CHAR(KSUPRFLG, KSUPROSID
---------- ------------------------------------------------ ----------------- ------------------------
2 oracle@ora19c2 (PMON) E 27028
3 oracle@ora19c2 (CLMN) E 27032
4 oracle@ora19c2 (PSP0) 6 27036
5 oracle@ora19c2 (IPC0) 6 27041
6 oracle@ora19c2 (VKTM) 6 27045
7 oracle@ora19c2 (GEN0) 6 27051
8 oracle@ora19c2 (MMAN) 6 27055
15 oracle@ora19c2 (DBRM) 6 27069
19 oracle@ora19c2 (ACMS) 6 27079
20 oracle@ora19c2 (PMAN) 6 27083
22 oracle@ora19c2 (LMON) 6 27089
23 oracle@ora19c2 (LMD0) 6 27093
24 oracle@ora19c2 (LMS0) 6 27095_27102
26 oracle@ora19c2 (LMS1) 6 27097_27103
28 oracle@ora19c2 (LMD1) 6 27101
29 oracle@ora19c2 (RMS0) 6 27111
31 oracle@ora19c2 (LCK1) 6 27118
32 oracle@ora19c2 (DBW0) 6 27122
33 oracle@ora19c2 (LGWR) 6 27127
34 oracle@ora19c2 (CKPT) 6 27131
35 oracle@ora19c2 (RS01) 6 27097_27132
36 oracle@ora19c2 (RS00) 6 27095_27133
37 oracle@ora19c2 (SMON) 16 27137
41 oracle@ora19c2 (LREG) 6 27147
44 oracle@ora19c2 (RBAL) 6 27156
45 oracle@ora19c2 (ASMB) 6 27160
46 oracle@ora19c2 (FENC) 6 27166
50 oracle@ora19c2 (IMR0) 6 27179
52 oracle@ora19c2 (LCK0) 6 27183
101 oracle@ora19c2 (CL00) E 29574
114 oracle@ora19c2 (CL01) E 29576
115 oracle@ora19c2 (CL02) E 29578
116 oracle@ora19c2 (CL03) E 29580
120 oracle@ora19c2 (RVWR) 6 31571
34 rows selected.
可以kill一下rvwr进程来验证一下前面的观点：
2022-05-07T21:29:23.789863+08:00
Thread 2 advanced to log sequence 7326 (LGWR switch), current SCN: 284244438
Current log# 2 seq# 7326 mem# 0: +DATA/ORA19C/ONLINELOG/group_2.279.1098989397
2022-05-07T21:29:23.930075+08:00
ARC2 (PID:27634): Archived Log entry 24965 added for T-2.S-7325 ID 0x44acd155 LAD:1
2022-05-07T21:29:49.144210+08:00
PMON (ospid: 27028): terminating the instance due to ORA error 479
2022-05-07T21:29:49.144661+08:00
Cause - 'Instance is being terminated due to fatal process death (pid: 120, ospid: 31571, RVWR)'
2022-05-07T21:29:49.250185+08:00
System state dump requested by (instance=2, osid=27028 (PMON)), summary=[abnormal instance termination]. error - 'Instance is terminating.
'
System State dumped to trace file /u01/app/oracle/diag/rdbms/ora19c/ora19c2/trace/ora19c2_diag_27064.trc
2022-05-07T21:29:49.468448+08:00
ORA-1092 : opitsk aborting process
2022-05-07T21:29:51.035116+08:00
License high water mark = 25
2022-05-07T21:29:55.215756+08:00
Instance terminated by PMON, pid = 27028
2022-05-07T21:29:55.243075+08:00
Warning: 2 processes are still attacheded to shmid 48168964:
(size: 81920 bytes, creator pid: 26534, last attach/detach pid: 27095)
2022-05-07T21:29:56.037428+08:00
USER(prelim) (ospid: 7642): terminating the instance
2022-05-07T21:29:56.039518+08:00
Instance terminated by USER(prelim), pid = 7642
2022-05-07T21:29:58.948053+08:00
Starting ORACLE instance (normal) (OS id: 7690)
2022-05-07T21:29:59.105832+08:00
************************************************************
Instance SGA_TARGET = 7952 MB and SGA_MAX_SIZE = 7942 MB
************************************************************
2022-05-07T21:29:59.106592+08:00
****************************************************
Sys-V shared memory will be used for creating SGA
****************************************************
2022-05-07T21:29:59.108901+08:00
**********************************************************************
2022-05-07T21:29:59.109036+08:00
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
2022-05-07T21:29:59.109227+08:00
Per process system memlock (soft) limit = UNLIMITED
2022-05-07T21:29:59.109321+08:00
Expected per process system memlock (soft) limit to lock
instance MAX SHARED GLOBAL AREA (SGA) into memory: 7954M
2022-05-07T21:29:59.109511+08:00
Available system pagesizes:
4K, 2048K
2022-05-07T21:29:59.109741+08:00
Supported system pagesize(s):
2022-05-07T21:29:59.109851+08:00
PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
2022-05-07T21:29:59.109949+08:00
4K Configured 20 2035732 NONE
2022-05-07T21:29:59.110132+08:00
2048K 0 3977 0 NONE
2022-05-07T21:29:59.110226+08:00
RECOMMENDATION:
2022-05-07T21:29:59.110316+08:00
可以看到rvwr进程一旦被kill，实例立刻被强行终止，出现实例重启。
最后回到正题，对于闪回日志来讲，建议还是通过关闭flashback 的方式，让Oracle自己来清理日志。
如果非要问，是否可以手工去删除闪回日志，其实也是ok的，不过也不是标准操作：
SQL>select name,LOG#,SEQUENCE#,FIRST_TIME from v$flashback_database_logfile;
NAME LOG# SEQUENCE# FIRST_TIM
------------------------------------------------------------------ ---------- ---------- ---------
+ARCH/ORA19C/FLASHBACK/log_1.2558.1104096465 1 1 07-MAY-22
+ARCH/ORA19C/FLASHBACK/log_2.2565.1104096467 2 1
+ARCH/ORA19C/FLASHBACK/log_3.2564.1104096471 3 1 07-MAY-22
+ARCH/ORA19C/FLASHBACK/log_4.2543.1104096475 4 1
SQL> alter database flashback off;
Database altered.
ASMCMD> cd FLASHBACK
ASMCMD> ls
log_1.2558.1104096465
log_2.2565.1104096467
log_3.2564.1104096471
log_4.2543.1104096475
ASMCMD> rm log_2.2565.1104096467
ASMCMD> ls
ASMCMD-8002: entry 'FLASHBACK' does not exist in directory '+ARCH/ORA19C/'
ASMCMD>
---alert log
2022-05-07T21:41:39.787627+08:00
ARC3 (PID:9299): Archived Log entry 24979 added for T-2.S-7330 ID 0x44acd155 LAD:1
2022-05-07T21:50:36.614964+08:00
RVWR shutting down
2022-05-07T21:50:36.795848+08:00
Deleted Oracle managed file +ARCH/ORA19C/FLASHBACK/log_1.2558.1104096465
Deleted Oracle managed file +ARCH/ORA19C/FLASHBACK/log_2.2565.1104096467
Deleted Oracle managed file +ARCH/ORA19C/FLASHBACK/log_3.2564.1104096471
Deleted Oracle managed file +ARCH/ORA19C/FLASHBACK/log_4.2543.1104096475
Flashback Database Disabled
2022-05-07T21:51:39.843067+08:00
比较简单的知识点，简单记录一下～～～由此可见对于闪回监控，也是比较重要的！
发表回复