关于flashback database的一个小细节
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 关于flashback database的一个小细节
今天同事反馈我们一个客户的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的清理动作：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
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。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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进程来验证一下前面的观点：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
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的，不过也不是标准操作：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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 |
比较简单的知识点，简单记录一下～～～由此可见对于闪回监控，也是比较重要的！
Leave a Reply
You must be logged in to post a comment.