ORACLE FLASHBACK

一、启用闪回数据库
闪回数据库相关参数db_flashback_retention_target，意为闪回日志的保留期限，单位为分钟
1)修改闪回日志的保存目标为2天
SYS@ORA10G>show parameter flash


NAME TYPE VALUE
------------------------------------ ----------- ---------------
db_flashback_retention_target integer 1440
SYS@ORA10G>
SYS@ORA10G>alter system set db_flashback_retention_target=2880;


System altered.


SYS@ORA10G>
SYS@ORA10G>show parameter flash


NAME TYPE VALUE
------------------------------------ ----------- ---------------
db_flashback_retention_target integer 2880
SYS@ORA10G>


2)查看是否开启闪回，未开启，在mount模式下启动闪回
SYS@ORA10G> select flashback_on from v$database;


FLASHBACK_ON
------------------
NO


SYS@ORA10G>
SYS@ORA10G> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA10G>
SYS@ORA10G> startup mount;
ORACLE instance started.


Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 121636240 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS@ORA10G>


3)启动闪回，报错，数据库要求在归档模式，启动归档
SYS@ORA10G>alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.


SYS@ORA10G>
SYS@ORA10G>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Current log sequence 5
SYS@ORA10G>
SYS@ORA10G>alter database archivelog;


Database altered.


SYS@ORA10G>


4)启动闪回数据库
SYS@ORA10G> alter database flashback on;


Database altered.


SYS@ORA10G> select flashback_on from v$database;


FLASHBACK_ON
------------------
YES


SYS@ORA10G> alter database open;


Database altered.


SYS@ORA10G>


二、闪回数据库的步骤
1)关闭数据库
2)启动数据库到mount状态[exclusive模式]
3)闪回至某个时间点，SCN或还原点
4)使用resetlogs打开数据库


三、不能使用闪回数据库的情况
1)已经还原或重新创建了控制文件
2)已经删除了表空间
3)已经收缩了数据文件


四、闪回数据库的方法


1)sqlplus下基于SCN闪回
FLASHBACK [STANDBY] DATABASE [<database_name>] TO
[BEFORE] SCN <system_change_number>


2)sqlplus下基于时间戳闪回
FLASHBACK [STANDBY] DATABASE [<database_name>] TO
[BEFORE] TIMESTMP <system_timestamp_value>


3)sqlplus下基于还原点闪回
FLASHBACK [STANDBY] DATABASE [<database_name>] TO
[BEFORE] RESTORE POINT <restore_point_name>


4)RMAN下基于时间戳闪回
RMAN> FLASHBACK DATABASE TO TIME = "TO_DATE('2012-08-03 14:51:13','YYYY-MM-DD HH24:MI:SS')";


5)RMAN下基于SCN闪回
RMAN> FLASHBACK DATABASE TO SCN=23565;


7)RMAN下基于：归档序号
RMAN> FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1;


---------------SQL--------------------


select flashback_archive_name name, status from dba_flashback_archive;

select to_timestamp('2013-11-11 15:58:30','yyyy-mm-dd hh24:mi:ss') from dual

create table CMS_WINDOW_DATA_bk as select * from CMS_WINDOW_DATA

show parameter db_recovery_file;


flashback table HELP to timestamp to_timestamp('2013-10-13 15:16:30','YYYY-MM-DD HH24:MI:SS');

select row_movement from user_tables where table_name='HELP'

alter table HELP enable row movement;

flashback database to scn 947921;
--
show parameter db_recovery_file;
--查看修改闪回日志的保存目标为7天，默认1天
show parameter flash;
alter system set db_flashback_retention_target=10080;
--查看是否启用归档日志
archive log list;
--
select flashback_on from v$database;
--查询当前的scn
SELECT CURRENT_SCN FROM V$DATABASE
--是否启用
select log_mode,open_mode,flashback_on from v$database;


select * from v$OPTION where parameter like 'Flashback%';

alter database flashback on;

alter tablespace tablespace_name flashback off/on