oracle10g-flashback

oracle10 闪回

1、闪回查询
2、闪回版本查询
3、闪回表
4、闪回drop
5、闪回数据库

条件：
1、开启归档
2、undo_management 设置为auto
3、flashback 设置为YES（闪回数据库用）

(1) 闪回查询依靠undo


AS OF 
Specify AS OF to retrieve the single version of the rows returned by the query at a particular change number (SCN) or timestamp. If you specify SCN, then expr must evaluate to a number. If you specify TIMESTAMP, then expr must evaluate to a timestamp value. Oracle Database returns rows as they existed at the specified system change number or time.

--查询某一时间点
select id,name from user01.a08 as of timestamp to_date('2014-08-07 14.00.20','yyyy-mm-dd hh24.mi.ss')

        ID NAME
---------- ------------------------------
         1 aaaaaa
         1 zzzzzz
         1 zzzzzz

--查询5分钟之前
select id,name from user01.a08 as of timestamp SYSDATE-5/1440


--查询某SCN的数据
select id,name from user01.a08 as SCN

(2) 版本查询
VERSIONS 
Specify VERSIONS to retrieve multiple versions of the rows returned by the query. 
Oracle Database returns all committed versions of the rows that existed between two SCNs or between two timestamp values. 
The rows returned include deleted and subsequently reinserted versions of the rows.
Specify BETWEEN SCN ... to retrieve the versions of the row that existed between two SCNs. 
Both expressions must evaluate to a number. 
MINVALUE and MAXVALUE resolve to the SCN of the oldest and most recent data available, respectively.
Specify BETWEEN TIMESTAMP ... to retrieve the versions of the row that existed between two timestamps. 
Both expressions must evaluate to a timestamp value. 
MINVALUE and MAXVALUE resolve to the timestamp of the oldest and most recent data available, respectively.

create table a07 as select * from a08;
insert into a07 values(1,'a');
insert into a07 values(2,'b');
insert into a07 values(3,'c');
commit;

select id,name,ORA_ROWSCN,scn_to_timestamp(ORA_ROWSCN) from a07;

        ID NAME     ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- -------- ---------- ---------------------------------------------------------------------------
         1 a            799041 09-AUG-14 10.29.33.000000000 PM
         2 b            799041 09-AUG-14 10.29.33.000000000 PM
         3 c            799041 09-AUG-14 10.29.33.000000000 PM

col name for a8
col VERSIONS_STARTTIME for a22
col VERSIONS_ENDTIME for a22

select id,name,VERSIONS_XID,VERSIONS_STARTTIME,VERSIONS_ENDTIME,versions_operation from a07 
 versions between timestamp
 to_timestamp('2014-08-09 22:29.30','yyyy-mm-dd hh24:mi:ss') and
 to_timestamp('2014-08-09 22:30.00','yyyy-mm-dd hh24:mi:ss');

        ID NAME     VERSIONS_XID     VERSIONS_STARTTIME     VERSIONS_ENDTIME       V
---------- -------- ---------------- ---------------------- ---------------------- -
         3 c        050021002C010000 09-AUG-14 10.29.33 PM                         I
         2 b        050021002C010000 09-AUG-14 10.29.33 PM                         I
         1 a        050021002C010000 09-AUG-14 10.29.33 PM                         I
    
insert into a07 values(4,'d');
commit;

user01@PROD> select id,name,ORA_ROWSCN,scn_to_timestamp(ORA_ROWSCN) from a07;

        ID NAME     ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- -------- ---------- ---------------------------------------------------------------------------
         1 a            799290 09-AUG-14 10.41.06.000000000 PM
         2 b            799290 09-AUG-14 10.41.06.000000000 PM
         3 c            799290 09-AUG-14 10.41.06.000000000 PM
         4 d            799290 09-AUG-14 10.41.06.000000000 PM

select id,name,VERSIONS_XID,VERSIONS_STARTTIME,VERSIONS_ENDTIME,versions_operation from a07 
 versions between timestamp
 to_timestamp('2014-08-09 22:29.30','yyyy-mm-dd hh24:mi:ss') and
 to_timestamp('2014-08-09 22:42.00','yyyy-mm-dd hh24:mi:ss');

        ID NAME     VERSIONS_XID     VERSIONS_STARTTIME     VERSIONS_ENDTIME       V
---------- -------- ---------------- ---------------------- ---------------------- -
         4 d        02000D002B010000 09-AUG-14 10.41.06 PM                         I
         3 c        050021002C010000 09-AUG-14 10.29.33 PM                         I
         2 b        050021002C010000 09-AUG-14 10.29.33 PM                         I
         1 a        050021002C010000 09-AUG-14 10.29.33 PM                         I

update a07 set name='e' where id=4;
commit;

        ID NAME     ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- -------- ---------- ---------------------------------------------------------------------------
         1 a            799336 09-AUG-14 10.43.18.000000000 PM
         2 b            799336 09-AUG-14 10.43.18.000000000 PM
         3 c            799336 09-AUG-14 10.43.18.000000000 PM
         4 e            799336 09-AUG-14 10.43.18.000000000 PM
         
select id,name,VERSIONS_XID,VERSIONS_STARTTIME,VERSIONS_ENDTIME,versions_operation from a07 
 versions between timestamp
 to_timestamp('2014-08-09 22:29.30','yyyy-mm-dd hh24:mi:ss') and
 to_timestamp('2014-08-09 22:44.00','yyyy-mm-dd hh24:mi:ss');
 
        ID NAME     VERSIONS_XID     VERSIONS_STARTTIME     VERSIONS_ENDTIME       V
---------- -------- ---------------- ---------------------- ---------------------- -
         4 e        04002E002C010000 09-AUG-14 10.43.18 PM                         U
         4 d        02000D002B010000 09-AUG-14 10.41.06 PM  09-AUG-14 10.43.18 PM  I
         3 c        050021002C010000 09-AUG-14 10.29.33 PM                         I
         2 b        050021002C010000 09-AUG-14 10.29.33 PM                         I
         1 a        050021002C010000 09-AUG-14 10.29.33 PM                         I

update a07 set name='f' where id=4;
commit;

        ID NAME     ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- -------- ---------- ---------------------------------------------------------------------------
         1 a            799381 09-AUG-14 10.45.24.000000000 PM
         2 b            799381 09-AUG-14 10.45.24.000000000 PM
         3 c            799381 09-AUG-14 10.45.24.000000000 PM
         4 f            799381 09-AUG-14 10.45.24.000000000 PM


select id,name,VERSIONS_XID,VERSIONS_STARTTIME,VERSIONS_ENDTIME,versions_operation from a07 
 versions between timestamp
 to_timestamp('2014-08-09 22:29.30','yyyy-mm-dd hh24:mi:ss') and
 to_timestamp('2014-08-09 22:46.00','yyyy-mm-dd hh24:mi:ss');

        ID NAME     VERSIONS_XID     VERSIONS_STARTTIME     VERSIONS_ENDTIME       V
---------- -------- ---------------- ---------------------- ---------------------- -
         4 f        01002A002F010000 09-AUG-14 10.45.24 PM                         U
         4 e        04002E002C010000 09-AUG-14 10.43.18 PM  09-AUG-14 10.45.24 PM  U
         4 d        02000D002B010000 09-AUG-14 10.41.06 PM  09-AUG-14 10.43.18 PM  I
         3 c        050021002C010000 09-AUG-14 10.29.33 PM                         I
         2 b        050021002C010000 09-AUG-14 10.29.33 PM                         I
         1 a        050021002C010000 09-AUG-14 10.29.33 PM                         I

(3) 闪回表

alter table user01.a07 enable row movement;
FLASHBACK TABLE user01.a07 TO TIMESTAMP to_timestamp('2014-08-09 22:43:17','yyyy-mm-dd hh24:mi:ss');

select id,name,ORA_ROWSCN,scn_to_timestamp(ORA_ROWSCN) from a07;

        ID NAME     ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- -------- ---------- ---------------------------------
         1 a            799549 09-AUG-14 10.50.57.000000000 PM
         2 b            799549 09-AUG-14 10.50.57.000000000 PM
         3 c            799549 09-AUG-14 10.50.57.000000000 PM
         4 d            799549 09-AUG-14 10.50.57.000000000 PM

sys@PROD> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0

sys@PROD> show parameter bin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
sys@PROD> select FLASHBACK_ON from v$database;

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

sys@PROD> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/disk1/arch
Oldest online log sequence     76
Current log sequence           78

--数据库非归档，闪回未开启，回收站开启
所以，闪回查询，闪回version查询，闪回表，都是依靠undo实现

(4) 闪回删除
RECYCLEBIN is used to control whether the Flashback Drop capability is turned on or off. 
If the parameter is set to off, then dropped tables do not go into the recycle bin.
If this parameter is set to on, then dropped tables go into the recycle bin and can be recovered.

drop table user01.a07;   
   
user01@PROD> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
A07              BIN$ADSyKqZq5HDgUHgKjvkZ1A==$0 TABLE        2014-08-09:23:08:36

flashback table user01.a07 to before drop;


--首先们需要明白一点，recyclebin是user_recyclebin的同义词，如此你当前的登陆用户是system此时运用
show recyclebin是没有数据据的


FLASHBACK TABLE TO SCN or TIMESTAMP does not preserve rowids, and FLASHBACK TABLE TO BEFORE DROP does not recover referential constraints.

Oracle Database does not revert statistics associated with table to their earlier form. 
Indexes on table that exist currently are reverted and reflect the state of the table at the Flashback point.
If the index exists now but did not yet exist at the Flashback point, 
then the database updates the index to reflect the state of the table at the Flashback point.
However, indexes that were dropped during the interval between the Flashback point and the current time are not restored.

--验证一下constraint and index
col owner for a10
col table_name for a10
col CONSTRAINT_NAME for a13
col SEARCH_CONDITION for a20
col STATUS for a8

ALTER TABLE user01.a07 MODIFY name NOT NULL;

select OWNER,table_name,CONSTRAINT_NAME, SEARCH_CONDITION,CONSTRAINT_TYPE,STATUS
 from dba_constraints
 where table_name='A07' and owner='USER01'

OWNER      TABLE_NAME CONSTRAINT_NA SEARCH_CONDITION     C STATUS
---------- ---------- ------------- -------------------- - --------
USER01     A07        SYS_C003144   "NAME" IS NOT NULL   C ENABLED

CREATE UNIQUE INDEX a07_id ON USER01.A07(id);

select owner,index_name,index_type,table_name,status from dba_indexes where table_name='A07';

OWNER      INDEX_NAME                     INDEX_TYPE                  TABLE_NAME STATUS
---------- ------------------------------ --------------------------- ---------- --------
SYS        A07_ID                         NORMAL                      A07        VALID

drop table user01.a07
show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
A07              BIN$AEeCPgq0V6fgUHgKjvkZkg==$0 TABLE        2014-08-10:21:35:16

flashback table user01.a07 to before drop;

insert into user01.a07 values(1,'');
ERROR at line 1:
ORA-01400: cannot insert NULL into ("USER01"."A07"."NAME")

insert into user01.a07 values(1,'a');
ERROR at line 1:
ORA-00001: unique constraint (SYS.BIN$AEeCPgqzV6fgUHgKjvkZkg==$0) violate


--index 和 constraint都恢复了，但名字都凌乱了。         
sys@PROD> select OWNER,table_name,CONSTRAINT_NAME, SEARCH_CONDITION,CONSTRAINT_TYPE,STATUS
  2   from dba_constraints
  3   where table_name='A07' and owner='USER01';


OWNER      TABLE_NAME CONSTRAINT_NA SEARCH_CONDITION     C STATUS
---------- ---------- ------------- -------------------- - --------
USER01     A07        BIN$AEeCPgqyV "NAME" IS NOT NULL   C ENABLED
                      6fgUHgKjvkZkg
                      ==$0

Elapsed: 00:00:00.00
sys@PROD> select owner,index_name,index_type,table_name,status from dba_indexes where table_name='A07';


OWNER      INDEX_NAME                     INDEX_TYPE                  TABLE_NAME STATUS
---------- ------------------------------ --------------------------- ---------- --------
SYS        BIN$AEeCPgqzV6fgUHgKjvkZkg==$0 NORMAL                      A07        VALID


(5) flashback database
Following a FLASHBACK DATABASE operation, 
in order to have write access to the flashed back database, 
you must reopen it with an ALTER DATABASE OPEN RESETLOGS statement.


1、You must have the SYSDBA system privilege. 
2、A flash recovery area must have been prepared for the database. 
3、The database must have been put in FLASHBACK mode with an ALTER DATABASE FLASHBACK ON 
4、The database must be mounted but not open
5、The database must run in ARCHIVELOG mode
6、The database must be mounted, but not open, with a current control file. 
The control file cannot be a backup or re-created. 
When the database control file is restored from backup or re-created,
all existing flashback log information is discarded.
7、The database must contain no online tablespaces for which flashback functionality was disabled with the SQL statement ALTER TABLESPACE ... FLASHBACK OFF.

Examples
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/disk1/arch' SCOPE=BOTH;

shutdown immediate;
STARTUP MOUNT 
alter database archivelog;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

select to_char(sysdate,'yy-mm-dd hh24:mi:ss') time from dual;
SELECT CURRENT_SCN FROM V$DATABASE; 

 
TIME
-----------------
14-08-10 22:14:07


CURRENT_SCN
-----------
     833248


select oldest_flashback_scn os, 
to_char(oldest_flashback_time,'yy-mm-dd hh24:mi:ss') ot, 
retention_target rt,flashback_size fs, estimated_flashback_size es 
 from v$flashback_database_log;

        OS OT                        RT         FS         ES
---------- ----------------- ---------- ---------- ----------
    832810 14-08-10 22:11:27       1440    8192000          0
    
drop table user01.a07;


user01@PROD> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
A07              BIN$AEglwvfoXsbgUHgKjvkrdg==$0 TABLE        2014-08-10:22:20:59

SHUTDOWN DATABASE
STARTUP MOUNT 
FLASHBACK DATABASE TO before TIMESTAMP to_timestamp('2014-08-10:22:20:59','yyyy-mm-dd hh24:mi:ss'); 
alter database open resetlogs


--打开数据库： 
--在执行完flashback database 命令之后，oracle 提供了两种方式让你修复数据库： 
1). 直接alter database open resetlogs 打开数据库，当然，指定scn 或者timestamp 时间点之后产生的数据统统丢失。 
2). 先执行alter database open read only 命令以read-only 模式打开数据库，然后立刻通过逻辑导出的方式将误操作涉及表的数据导出，再执行recover database 命令以重新应用数据库产生的redo，将数据库修复到flashback database 操作前的状态，然后再通过逻辑导入的方式，将之前误操作的表重新导入，这样的话对现有数据的影响最小，不会有数据丢失。 
 
这里演示，就以resetlogs方式打开


sys@PROD> conn user01/user01
Connected.
user01@PROD> select * from a07;


        ID NAME
---------- ------------------------------
         1 a
         2 b
         3 c
         4 d
         
FLASHBACK DATABASE TO SCN
before SCN
TIMESTAMP
before TIMESTAMP

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22193071/viewspace-1248869/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22193071/viewspace-1248869/