flashback database log导致hung机

sys@R2> delete a ;--hang


sys@R2> select sid,event,sql_id ,username ,status ,machine, SQL_EXEC_START from v$session where username = 'SYS' ;

  SID EVENT                         SQL_ID        USERNAME        STATUS    MACHINE                SQL_EXEC_START
----- -------------------------------------------------- ------------- --------------- -------- ------------------------------ ------------------
   40 flashback buf free by RVWR                       SYS           ACTIVE    node1
   45 flashback buf free by RVWR             cv0wbqnff1dfv SYS           ACTIVE    node1                   20-JUL-16
   54 flashback buf free by RVWR             11p815z8hkfms SYS           ACTIVE    node1                   19-JUL-16
   57 enq: US - contention                 95zfwdqwfauub SYS           ACTIVE    node1                   21-JUL-16
  182 enq: US - contention                 0z7z2r2vrykp1 SYS           ACTIVE    node1                   22-JUL-16
  184 enq: US - contention                 0z7z2r2vrykp1 SYS           ACTIVE    node1                   22-JUL-16
  186 SQL*Net message to client              5k7mss481k8sx SYS           ACTIVE    node1                   22-JUL-16

7 rows selected.


sys@R2> select sql_fulltext from v$sql where sql_id = '0z7z2r2vrykp1' ;

SQL_FULLTEXT
--------------------------------------------------------------------------------
delete a


yselect dbf.tablespace_name,
       dbf.totalspace "总量(M)",
       dbf.totalblocks as 总块数,
       dfs.freespace "剩余总量(M)",
       dfs.freeblocks "剩余块数",
       (dfs.freespace / dbf.totalspace) * 100 "空闲比例"
  from (select t.tablespace_name,
               sum(t.bytes) / 1024 / 1024 totalspace,
               sum(t.blocks) totalblocks
          from dba_data_files t
         group by t.tablespace_name) dbf,
       (select tt.tablespace_name,
               sum(tt.bytes) / 1024 / 1024 freespace,
               sum(tt.blocks) freeblocks
          from dba_free_space tt
         group by tt.tablespace_name) dfs
 17   where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);

TABLESPACE_NAME                   总量(M)     总块数 剩余总量(M)   剩余块数   空闲比例
------------------------------ ---------- ---------- ----------- ---------- ----------
SYSAUX                      760      97280      39.125       5008 5.14802632
UNDOTBS1                  345      44160    321.6875      41176 93.2427536
USERS                     32.5    4160     18.4375       2360 56.7307692
SYSTEM                      810     103680      8.1875       1048 1.01080247
EXAMPLE                   100      12800     21.1875       2712    21.1875
RCAT_TS                    15    1920      10.375       1328 69.1666667
DOCU                      200      25600     155.125      19856    77.5625



sys@R2> select TABLESPACE_NAME , FILE_NAME ,BYTES/1024/1024 m from dba_data_files where TABLESPACE_NAME =  'UNDOTBS1'
  2  /

TABLESPACE_NAME            FILE_NAME                          M
------------------------------ --------------------------------------------- ----------
UNDOTBS1               /u01/app/oracle/oradata/r2/undotbs01.dbf         345



select tablespace_name, retention from dba_tablespaces where tablespace_name

sys@R2> select TABLESPACE_NAME , RETENTION from  dba_tablespaces where TABLESPACE_NAME = 'UNDOTBS1' ;

TABLESPACE_NAME            RETENTION
------------------------------ -----------
UNDOTBS1               NOGUARANTEE



sys@R2> select * from v$sgainfo
  2  /

NAME                     BYTES RES
----------------------------------- ---------- ---
Fixed SGA Size                   2214456 No
Redo Buffers                   6086656 No
Buffer Cache Size            1342177280 Yes
Shared Pool Size             452984832 Yes
Large Pool Size               16777216 Yes
Java Pool Size                  33554432 Yes
Streams Pool Size              33554432 Yes
Shared IO Pool Size                 0 Yes
Granule Size                  16777216 No
Maximum SGA Size            1887350784 No
Startup overhead in Shared Pool      100663296 No
Free SGA Memory Available             0

Free SGA Memory Available             0 可以内存为0

sys@R2> select username ,sql_id ,status ,event from v$session where username = 'SYS' ;

USERNAME    SQL_ID          STATUS   EVENT
--------------- ------------- -------- --------------------------------------------------
SYS                  ACTIVE   flashback buf free by RVWR
SYS        cv0wbqnff1dfv ACTIVE   flashback buf free by RVWR
SYS        11p815z8hkfms ACTIVE   flashback buf free by RVWR
SYS        95zfwdqwfauub ACTIVE   enq: US - contention
SYS        0z7z2r2vrykp1 ACTIVE   enq: US - contention
SYS        0z7z2r2vrykp1 ACTIVE   enq: US - contention
SYS        2a3cwbbcgkqqk ACTIVE   SQL*Net message to client
SYS        3jb069f9zrgyu ACTIVE   buffer busy waits

都在等着 flashback buf free by RVWR  ， buffer busy waits


sys@R2> show parameter recov

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string     /u01/flash_recovery_area
db_recovery_file_dest_size         big integer 3882M
recovery_parallelism             integer     0
sys@R2> desc v$recovery_area_usage
 Name                                               Null?    Type
 ------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
 FILE_TYPE                                            VARCHAR2(20)
 PERCENT_SPACE_USED                                        NUMBER
 PERCENT_SPACE_RECLAIMABLE                                    NUMBER
 NUMBER_OF_FILES                                        NUMBER

sys@R2> select * from v$recovery_area_usage ;

FILE_TYPE         PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                  0             0        0
REDO LOG                  0             0        0
ARCHIVED LOG                  0             0        0
BACKUP PIECE                  0             0        0
IMAGE COPY               2.58             0        1
FLASHBACK LOG              97.05             0          236
FOREIGN ARCHIVED LOG              0             0        0

7 rows selected.


sys@R2> desc V$FLASHBACK_DATABASE_LOG
 Name                                               Null?    Type
 ------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
 OLDEST_FLASHBACK_SCN                                        NUMBER
 OLDEST_FLASHBACK_TIME                                        DATE
 RETENTION_TARGET                                        NUMBER
 FLASHBACK_SIZE                                         NUMBER
 ESTIMATED_FLASHBACK_SIZE                                    NUMBER

sys@R2> select * from V$FLASHBACK_DATABASE_LOG ;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------ ---------------- -------------- ------------------------
         2363491 27-JUN-16                2880     3950567424            291078144

2880分钟

sys@R2> alter system set  db_recovery_file_dest_size=5G ;

System altered.

sys@R2> select * from v$recovery_area_usage ;

FILE_TYPE         PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                  0             0        0
REDO LOG                  0             0        0
ARCHIVED LOG                  0             0        0
BACKUP PIECE                  0             0        0
IMAGE COPY               1.95             0        1
FLASHBACK LOG              75.07             0          241
FOREIGN ARCHIVED LOG              0             0        0

7 rows selected.

sys@R2> desc a
 Name                                               Null?    Type
 ------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
 A                                                DATE
 B                                                NUMBER(38)

sys@R2> delete a ;

flashback dtabase log如何回收？
有三种机制：(以下观点是借助别人的）
   第1种情况：数据库级别打开flashback功能，但没有创建guaranteed restore point，flashback database能够回溯到的时间点取决于db_flashback_retention_target，
但这并不是一个硬性指标，当遇到Fast Recovery Area用满的时候较早生成的Flashback log会被清理掉以留出足够的空间给新生成的flashback log，
所以无法保证一定能将数据库flashback到db_flashback_retention_target指定的时间点。data block被修改之前其before image被写入flashback log，
对于活跃度较高的data block并不是每次更改都会触发before image写入flashback log，这样做的目的是有效降低Flashback log write对数据库IO性能的影响


   第2种情况：创建了guaranteed restore point，但没有在数据库级别打开flashback功能，也称为Guaranteed restore point without flashback logging，
这种情况确保数据库一定能flashback到guaranteed restore point创建时的状态，如果出现Fast Recovery Area用满且又没有obsolete file可以删除的时候甚
至会引起数据库Hang。guaranteed restore point的功能有点类似存储上的snapshot概念，提供了完整保留某一个时间点数据的功能，创建了guaranteed restore point之后，
某个data block被更改，且一定是首次更改的时候，data block before image才会被写入Flashback log，即Copy-on-first-write，
即使该data block之后再次被修改，其before image也不会再次被写入flashback log，对同一个data block来说其before-image永远只会写入一次flashback log


  第3种情况，也称为Guaranteed restore point with flashback logging，集1、2之大成，确保数据库能flashback到从guaranteed restore point时间点开始的任何时间点

因为第3种情况包含了第1种，我们仅针对上面的第2、3种情况研究一下flashback log的生成机制。
先引入一个隐含参数_flashback_barrier_interval，我们前面提到过在数据库级别的flashback功能开启时，同一个block被更改多次的情况下，
不会每次都将before image写入flashback log，data block被更改的时候需要计算距离上一次更改的时间，当这个时间间隔大于等于_flashback_barrier_interval时
才会将before image写入flashback log，_flashback_barrier_interval的默认值是1800秒，为了方便测试我们调整为60秒，修改此参数需要重启实例

sys@R2> select * from v$restore_point ;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME               RESTORE_POINT_TIME   PRE NAME
---------- --------------------- --- ------------ -------------------- -------------------- --- -----------------------------------
   2364087               5 YES   4110475264 27-JUN-16 04.32.22.0                YES P1
                          00000000 PM
    
至此找到flash database log  致使fash recovery area 满，且log不能清的原因

解决方法：
alter database flashback off;  -- (相反alter database flashback on)
drop restore point P1;   --（相反  create restore point P1 guarantee flashback database）
   

sys@R2> select * from v$recovery_area_usage ;

FILE_TYPE         PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                  0             0        0
REDO LOG                  0             0        0
ARCHIVED LOG                  0             0        0
BACKUP PIECE                  0             0        0
IMAGE COPY               1.95             0        1
FLASHBACK LOG               2.98             0           10
FOREIGN ARCHIVED LOG              0             0        0