· 作者小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接:http://www.oracleblog.cn/working-case/who-change-flashback-on/
oracle对flashback log采用了自动管理的方式,并且不允许手工去删除flashback log。当flashback log写满时,会自动的覆盖掉最早的一个flashback日志。并且值得注意的是,如果手工删除了flashback log,数据库将自动把flashback设置成no!
我们先把数据库的flashback打开:
SQL>startupmount;
ORACLEinstancestarted.
TotalSystemGlobalArea 167772160bytes
FixedSize 1247900bytes
VariableSize 67110244bytes
DatabaseBuffers 96468992bytes
RedoBuffers 2945024bytes
Databasemounted.
SQL>alterdatabaseflashbackon;
Databasealtered.
Elapsed:00:00:04.00
SQL>
SQL>
SQL>alterdatabaseopen;
Databasealtered.
Elapsed:00:00:29.82
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>selectflashback_on fromv$database;
FLASHBACK_ON
------------------
YES
Elapsed:00:00:22.05
SQL>
SQL>
ORACLEinstancestarted.
TotalSystemGlobalArea 167772160bytes
FixedSize 1247900bytes
VariableSize 67110244bytes
DatabaseBuffers 96468992bytes
RedoBuffers 2945024bytes
Databasemounted.
SQL>alterdatabaseflashbackon;
Databasealtered.
Elapsed:00:00:04.00
SQL>
SQL>
SQL>alterdatabaseopen;
Databasealtered.
Elapsed:00:00:29.82
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>selectflashback_on fromv$database;
FLASHBACK_ON
------------------
YES
Elapsed:00:00:22.05
SQL>
SQL>
我们来看看当前flashback日志有哪些:
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>ls -l
total 16016
-rwxrwxrwa 1 Administrators SYSTEM 8200192 Jan 21 15:54 O1_MF_4QFKO379_.FLB
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>ls -l
total 16016
-rwxrwxrwa 1 Administrators SYSTEM 8200192 Jan 21 15:54 O1_MF_4QFKO379_.FLB
我们再看看flashback的相关参数设置:目录大小设置了50M,当前1个flashback日志空间占用率为15.63%
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>sqlplus"/ as sysdba"
SQL*Plus:Release10.2.0.1.0-Productionon星期三1月2115:49:452009
Copyright(c)1982,2005,Oracle. Allrightsreserved.
Connectedto:
OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-Production
WiththePartitioning,OLAPandDataMiningoptions
SQL>showparameterrecover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0\flash
_recovery_area
db_recovery_file_dest_size biginteger50M
recovery_parallelism integer 0
SQL>
SQL>select*fromV$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLENUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 15.63 0 1
6rowsselected.
Elapsed:00:00:04.01
SQL>
SQL>select*fromv$flashback_database_log;
OLDEST_FLASHBACK_SCNOLDEST_FLASHBARETENTION_TARGETFLASHBACK_SIZEESTIMATED_FLASHBACK_SIZE
-------------------- -------------- ---------------- -------------- ------------------------
140223221-1月 -09 1440 8192000 70410240
Elapsed:00:00:01.54
SQL>
SQL>
SQL>
SQL>
SQL*Plus:Release10.2.0.1.0-Productionon星期三1月2115:49:452009
Copyright(c)1982,2005,Oracle. Allrightsreserved.
Connectedto:
OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-Production
WiththePartitioning,OLAPandDataMiningoptions
SQL>showparameterrecover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0\flash
_recovery_area
db_recovery_file_dest_size biginteger50M
recovery_parallelism integer 0
SQL>
SQL>select*fromV$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLENUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 15.63 0 1
6rowsselected.
Elapsed:00:00:04.01
SQL>
SQL>select*fromv$flashback_database_log;
OLDEST_FLASHBACK_SCNOLDEST_FLASHBARETENTION_TARGETFLASHBACK_SIZEESTIMATED_FLASHBACK_SIZE
-------------------- -------------- ---------------- -------------- ------------------------
140223221-1月 -09 1440 8192000 70410240
Elapsed:00:00:01.54
SQL>
SQL>
SQL>
SQL>
现在,我们执行一些dml,来将block的pre image写到flashback log中。
SQL>
SQL>updatetest.t1seta=9898998;
261344rowsupdated.
Elapsed:00:19:31.50
SQL>
SQL>
SQL>commit;
Commitcomplete.
Elapsed:00:00:03.03
SQL>updatetest.t1seta=9898998;
261344rowsupdated.
Elapsed:00:19:31.50
SQL>
SQL>
SQL>commit;
Commitcomplete.
Elapsed:00:00:03.03
查看目录的使用率情况:
SQL>
SQL>select*fromV$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLENUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 99.38 83.75 12
6rowsselected.
Elapsed:00:00:04.20
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>select*fromV$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLENUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 99.38 83.75 12
6rowsselected.
Elapsed:00:00:04.20
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
查看目录下的文件,删除这些文件:
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>ls
O1_MF_4QFKO379_.FLB O1_MF_4QFO2K5L_.FLB O1_MF_4QFO39PO_.FLB O1_MF_4QFO3P9Y_.FLB O1_MF_4QFO45HB_.FLB O1_MF_4QFO58MP_.FLB
O1_MF_4QFO2DT2_.FLB O1_MF_4QFO2O7K_.FLB O1_MF_4QFO3JFD_.FLB O1_MF_4QFO3VCX_.FLB O1_MF_4QFO4ZVK_.FLB O1_MF_4QFO5MR3_.FLB
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>rm *.FLB
rm: cannot unlink entry "O1_MF_4QFKO379_.FLB": 另一个程序正在使用此文件,进程无法访问。
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>ls
O1_MF_4QFKO379_.FLB O1_MF_4QFO2K5L_.FLB O1_MF_4QFO39PO_.FLB O1_MF_4QFO3P9Y_.FLB O1_MF_4QFO45HB_.FLB O1_MF_4QFO58MP_.FLB
O1_MF_4QFO2DT2_.FLB O1_MF_4QFO2O7K_.FLB O1_MF_4QFO3JFD_.FLB O1_MF_4QFO3VCX_.FLB O1_MF_4QFO4ZVK_.FLB O1_MF_4QFO5MR3_.FLB
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>rm *.FLB
rm: cannot unlink entry "O1_MF_4QFKO379_.FLB": 另一个程序正在使用此文件,进程无法访问。
由于其中一个文件正在被使用,我们只能删除除这个文件外的其他文件:
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>ls
O1_MF_4QFKO379_.FLB
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>exit
O1_MF_4QFKO379_.FLB
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>
D:\oracle\product\10.2.0\flash_recovery_area\ORALOCAL\FLASHBACK>exit
我们来看看删除flashback日志后,flashback的参数变化:
--刚刚开始时,还是yes:
SQL>
SQL>selectflashback_on fromv$database;
FLASHBACK_ON
------------------
YES
Elapsed:00:00:00.18
--做一些dml
SQL>updatetest.t1seta=12112;
261344rowsupdated.
Elapsed:00:02:38.09
SQL>commit;
Commitcomplete.
Elapsed:00:00:00.04
--查看目录下flashbacklog情况,发现就只有1个,且时间是最新的:
SQL>hostls-l
total7792
-rwxrwxrwa 1Administrators SYSTEM 3989504Jan2117:30O1_MF_4QFKO379_.FLB
--虽然只有1个文件,但是使用率还是在91.34%:
SQL>select*fromV$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLENUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 91.34 0 12
6rowsselected.
Elapsed:00:00:00.15
SQL>
--参数自动被改成NO了!!!
SQL>selectflashback_on fromv$database;
FLASHBACK_ON
------------------
NO
Elapsed:00:00:00.29
SQL>
SQL>
SQL>selectflashback_on fromv$database;
FLASHBACK_ON
------------------
YES
Elapsed:00:00:00.18
--做一些dml
SQL>updatetest.t1seta=12112;
261344rowsupdated.
Elapsed:00:02:38.09
SQL>commit;
Commitcomplete.
Elapsed:00:00:00.04
--查看目录下flashbacklog情况,发现就只有1个,且时间是最新的:
SQL>hostls-l
total7792
-rwxrwxrwa 1Administrators SYSTEM 3989504Jan2117:30O1_MF_4QFKO379_.FLB
--虽然只有1个文件,但是使用率还是在91.34%:
SQL>select*fromV$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USEDPERCENT_SPACE_RECLAIMABLENUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 91.34 0 12
6rowsselected.
Elapsed:00:00:00.15
SQL>
--参数自动被改成NO了!!!
SQL>selectflashback_on fromv$database;
FLASHBACK_ON
------------------
NO
Elapsed:00:00:00.29
SQL>