flashback database log导致hung机
来源：互联网 发布：湘潭湖南软件职业学院 编辑：程序博客网 时间：2024/06/10 00:26
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
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
0 0
- flashback database log导致hung机
- flashback之flashback database
- Flashback Database！
- flashback database.
- Flashback Database
- Flashback Database
- Flashback Database
- Flashback Database
- flashback database
- Oracle Flashback之Flashback database
- Oracle Flashback之flashback database
- enable flashback database feature
- flashback database的使用
- rac flashback database 配置
- FLASHBACK DATABASE 的方法
- oracle flashback database
- oracle flashback database
- Flashback database说明
- 嵌入式系统的多道程序技术
- LeetCode-101:Symmetric Tree
- wxpython 简单文本编辑
- 走遍中国 —— 华南
- workerman 实现消息推送
- flashback database log导致hung机
- Swift编程基础之排序（三）-----实战篇
- struts2标签Iterator迭代时获取下标、判断list是否为空
- Java逻辑运算符
- 1076. Forwards on Weibo (30)
- linux的x window system
- mysql创建远程用户
- Java网络编程 服务器Socket
- redis学习网站
