准备:
首先必须按照完毕Oracle11g数据库产品及数据库;其次当前数据库的闪回是关闭的。
开始实验:
检查数据库的闪回状态(我这是关闭的)
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
2.检查闪回相关的参数情况(可以看到我的闪回区参数值是空的,闪回区大小也没有指定)
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
3.指定闪回区大小,指定闪回目录路径(顺序必须先设置闪回区大小,才能指定闪回目录,否则报错)
SQL> alter system set db_recovery_file_dest='/ora_log/flashbak_log';
alter system set db_recovery_file_dest='/ora_log/flashbak_log'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE
没有设置闪回区大小,直接设置闪回目录,就会报如上错误
SQL> alter system set db_recovery_file_dest_size=100M scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='/ora_log/flashbak_log' scope=spfile;
System altered.
这两个参数修改需要重启实例后生效
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2253624 bytes
Variable Size 1207962824 bytes
Database Buffers 369098752 bytes
Redo Buffers 7393280 bytes
Database mounted.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /ora_log/flashbak_log
db_recovery_file_dest_size big integer 100M
recovery_parallelism integer 0
SQL> alter database flashback on;
Database altered.
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
总结:
闪回区的管理,及闪回日志管理,数据库能闪回到过去的多久时间点,这个由闪回区大小以db_flashback_retention_target
参数控制,在闪回区大小足够的情况,下默认能闪回1440秒也就是一天的数据。
SQL> show parameter flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440