[20170203]关于flashback的问题.txt
--今天生产系统遇到的问题,dataguard机器磁盘空间不足,需要释放磁盘空间,无论如何删除日志,空间回收都是很少.
--但是我检查发现闪回日志占用空间很大.
SYS@dbendg> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@dbendg> show parameter recover
NAME TYPE VALUE
----------------------------- ------------ ----------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 22G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SYS@dbendg> show parameter db_flashback_retention_target
NAME TYPE VALUE
----------------------------- ------------- ----------------------------------
db_flashback_retention_target integer 1440
$ du -sm /u01/app/oracle/fast_recovery_area/DBENDG
20518 /u01/app/oracle/fast_recovery_area/DBENDG
SYS@dbendg> set numw 12
SYS@dbendg> select * from V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
17432448295 2017-01-23 10:45:34 1440 21474836480 1596063744
SYS@dbendg> select sysdate from dual ;
SYSDATE
-------------------
2017-02-03 15:01:47
--//RETENTION_TARGET仅仅1440分钟,也就是1天.而实际上如果flashback可以到时间'2017-01-23 10:45:34'.
SYS@dbendg> column name format a80
SYS@dbendg> select * from V$FLASHBACK_DATABASE_LOGFILE order by FIRST_TIME;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME TYPE
----------------------------------------------------------------------- ---- ------- --------- ---------- ------------- ------------------- --------
/u01/app/oracle/fast_recovery_area/DBENDG/flashback/o1_mf_bxc34ghw_.flb 3 1 234 4294967296 0 2017-01-20 16:51:51 RESERVED
/u01/app/oracle/fast_recovery_area/DBENDG/flashback/o1_mf_bx5cy810_.flb 1 1 235 4294967296 0 2017-01-23 10:10:26 NORMAL
/u01/app/oracle/fast_recovery_area/DBENDG/flashback/o1_mf_bx5cyy6v_.flb 2 1 236 4294967296 0 2017-01-25 13:46:05 NORMAL
/u01/app/oracle/fast_recovery_area/DBENDG/flashback/o1_mf_c4tb157q_.flb 4 1 237 4294967296 0 2017-01-30 08:19:58 NORMAL
/u01/app/oracle/fast_recovery_area/DBENDG/flashback/o1_mf_c4tbd7wx_.flb 5 1 238 4294967296 0 2017-02-03 10:00:19 NORMAL
--//注意看FIRST_TIME字段,是否意味者我可以恢复的时间到2017-01-23 10:10:26,甚至2017-01-20 16:51:51呢? 生产系统无法测试.
SYS@dbendg> alter system set db_recovery_file_dest_size=15G ;
System altered.
$ du -sm /u01/app/oracle/fast_recovery_area/DBENDG
12318 /u01/app/oracle/fast_recovery_area/DBENDG
--//现在仅仅保留12G上下.
SYS@dbendg> select * from V$FLASHBACK_DATABASE_LOGFILE order by FIRST_TIME;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME TYPE
---------------------------------------------------------------------------- ------- ------------ ------------ ------------- ------------------- --------
/u01/app/oracle/fast_recovery_area/DBENDG/flashback/o1_mf_bxc34ghw_.flb 3 1 234 4294967296 0 2017-01-20 16:51:51 RESERVED
/u01/app/oracle/fast_recovery_area/DBENDG/flashback/o1_mf_c4tb157q_.flb 4 1 237 4294967296 0 2017-01-30 08:19:58 NORMAL
/u01/app/oracle/fast_recovery_area/DBENDG/flashback/o1_mf_c4tbd7wx_.flb 5 1 238 4294967296 0 2017-02-03 10:00:19 NORMAL
--//为什么TYPE='RESERVED'不删除??? 而且时间更早.
--//总之flashback的闪回时间点与db_recovery_file_dest_size大小有关,oracle应该尽可能多的保留时间.而不一定受参数db_flashback_retention_target的限制.