oracle清理闪回日志文件测试-优快云博客

文档课题：oracle清理闪回日志文件测试.
数据库：oracle 19.13
1、问题描述
闪回日志文件导致快速恢复区空间使用率过高，如下所示：
[root@leo-oracle-19c:/u01/app/oracle/flash_recovery_area/ORCLCDB]# du -sch *
18M     autobackup
17G     flashback
17G     total
[root@leo-oracle-19c:/u01/app/oracle/flash_recovery_area/ORCLCDB]# pwd
/u01/app/oracle/flash_recovery_area/ORCLCDB

sys@ORCLCDB> select FILE_TYPE,PERCENT_SPACE_USED,PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES,CON_ID from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                           .09                         0               1          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                        81.15                     80.29              53          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

sys@ORCLCDB> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 20G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
sys@ORCLCDB> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     4320

--闪回恢复功能开启后，对应的进程名为rvwr.
sys@ORCLCDB> ! ps -ef | grep -i rvwr
oracle     2071      1  0 08:30 ?        00:00:00 ora_rvwr_orclcdb
oracle     8775   7177  0 09:58 pts/3    00:00:00 /bin/bash -c  ps -ef | grep -i rvwr
oracle     8777   8775  0 09:58 pts/3    00:00:00 grep -i rvwr

2、解决方案
--临时关闭闪回功能
sys@ORCLCDB> alter database flashback off;

Database altered.

--rvwr进程停止
sys@ORCLCDB> ! ps -ef | grep -i rvwr
oracle     8826   7177  0 09:59 pts/3    00:00:00 /bin/bash -c  ps -ef | grep -i rvwr
oracle     8828   8826  0 09:59 pts/3    00:00:00 grep -i rvwr

--闪回日志文件自动被删除
[root@leo-oracle-19c:/u01/app/oracle/flash_recovery_area/ORCLCDB]# du -sch *
18M     autobackup
0       flashback
18M     total

--后台告警日志
2024-07-16T09:59:15.283129+08:00
alter database flashback off
Stopping background process RVWR
2024-07-16T09:59:16.310216+08:00
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcrcx_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mddtsj_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdjrvw_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6lz5vy8_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6lz5xmn_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6lz6rc6_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6lzdlz7_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6m7xp5r_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6m8czb4_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6m8dr8c_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6m8hl36_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6m9y438_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6m9z4qg_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mbfwot_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mbyyhg_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mc0lh6_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcd3p_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcfpk_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcgcf_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcgw3_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdch8f_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdchk8_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdchqw_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcj0k_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcj8k_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcjhv_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcjq9_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcjxy_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdck5q_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdckdm_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdckn9_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdckw8_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcl3s_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcl8v_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcljy_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdclqv_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcm0z_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcm9g_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcmhr_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcmp9_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcmx1_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcn5z_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcnfr_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcnqf_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcnyq_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdco7k_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcobb_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcoq3_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcp0b_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcp7j_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcpgc_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcpno_.flb
Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCLCDB/flashback/o1_mf_m6mdcpvt_.flb
Flashback Database Disabled 
Completed: alter database flashback off

说明：如上闪回日志文件被delete.

3、开启闪回功能测试
sys@ORCLCDB> alter database flashback on;

Database altered.

--rvwr进程重新运行
sys@ORCLCDB> ! ps -ef | grep -i rvwr
oracle     8891      1  0 10:00 ?        00:00:00 ora_rvwr_orclcdb
oracle     8918   7177  0 10:00 pts/3    00:00:00 /bin/bash -c  ps -ef | grep -i rvwr
oracle     8920   8918  0 10:00 pts/3    00:00:00 grep -i rvwr

--后台日志信息
2024-07-16T10:00:34.117342+08:00
alter database flashback on
Starting background process RVWR
2024-07-16T10:00:34.140416+08:00
RVWR started with pid=38, OS id=8891 
2024-07-16T10:00:35.283203+08:00
Already allocated 15933248 bytes in shared pool for flashback generation buffer.
Flashback Database Enabled at SCN 15746077
Completed: alter database flashback on