【Oracle Database】闪回（FlashBack）
admin
2023-04-17 14:02:19
0次
闪回版本查询
SQL> set line 200
SQL> col starttime for a30
SQL> col endtime for a30
SQL> col operation for a30
SQL> select to_char(versions_starttime,'yyyy-mm-dd hh34:mi:ss') starttime,to_char(versions_endtime,'yyyy-mm-dd hh34:mi:ss') endtime,
versions_xid xid,versions_operation operation from soe.customers versions
between timestamp to_date('2018-09-22 14:00:00','yyyy-mm-dd hh34:mi:ss') and sysdate where versions_xid is not null;
STARTTIME ENDTIME XID OPERATION
------------------------------ ------------------------------ ---------------- ------------------------------
2018-09-22 14:00:36 05001100B8030000 U
2018-09-22 13:59:59 05000700B8030000 U
2018-09-22 14:00:25 08001B00CC030000 U
2018-09-22 14:00:14 05001000B8030000 I
2018-09-22 14:00:20 09001700C9030000 I
2018-09-22 14:00:05 0B00200041000000 I
2018-09-22 14:00:05 09000700CA030000 I
2018-09-22 14:00:11 03001800B4030000 I
8 rows selected.
闪回事务查询
SQL> select undo_sql from flashback_transaction_query where xid = hextoraw('03001800B4030000');
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
delete from "SOE"."LOGON" where ROWID = 'AAAVU+AAGAAAavRAB6';
delete from "SOE"."CARD_DETAILS" where ROWID = 'AAAVU4AAHAAAQEoABz';
delete from "SOE"."ADDRESSES" where ROWID = 'AAAVU3AAHAAAbuoAAy';
delete from "SOE"."CUSTOMERS" where ROWID = 'AAAVU2AAGAAAaqxAAO';
SQL> select undo_sql from flashback_transaction_query where xid = hextoraw('08001B00CC030000');
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
update "SOE"."CUSTOMERS" set "PREFERRED_ADDRESS" = '1162533' where ROWID = 'AAAVU2AAGAAACC9AAS';
delete from "SOE"."ADDRESSES" where ROWID = 'AAAVU3AAHAAAbumAA5';闪回表--scn
SQL> alter table soe.customers enable row movement;
Table altered.
SQL> select count(*) from soe.customers;
COUNT(*)
----------
975317
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1217679
SQL> delete from soe.customers where nls_territory = 'Korea';
12470 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from soe.customers;
COUNT(*)
----------
962847
SQL> flashback table soe.customers to scn 1217679;
Flashback complete.
SQL> select count(*) from soe.customers;
COUNT(*)
----------
975317
闪回表--timestamp
SQL> select count(*) from soe.customers;
COUNT(*)
----------
975317
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1221943
SQL> select scn_to_timestamp(1221943) scn from dual;
SCN
---------------------------------------------------------------------------
23-SEP-18 04.17.24.000000000 PM
SQL> select count(*) from soe.customers;
COUNT(*)
----------
975317
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1222481
SQL> select scn_to_timestamp(1222481) scn from dual;
SCN
---------------------------------------------------------------------------
23-SEP-18 04.22.21.000000000 PM
SQL> select count(*) from soe.customers;
COUNT(*)
----------
975340
SQL> flashback table soe.customers to timestamp to_timestamp('2018-09-23 16:17:24','yyyy-mm-dd hh34:mi:ss');
Flashback complete.
SQL> select count(*) from soe.customers;
COUNT(*)
----------
975317闪回删除 SQL> show parameter recyclebin; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ recyclebin string ON SQL> drop table warehouses; Table dropped. SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- WAREHOUSES BIN$doc0/FwhEVPgU8kBqMBrow==$0 TABLE 2018-09-23:17:07:50 SQL> flashback table warehouses to before drop; Flashback complete. SQL> select count(*) from warehouses; COUNT(*) ---------- 1000 SQL> alter index "BIN$doc0/FwgEVPgU8kBqMBrow==$0" rename to warehouses_pk; Index altered. SQL> alter table warehouses rename constraint "BIN$doc0/FwfEVPgU8kBqMBrow==$0" to warehouses_pk; Table altered.
闪回数据库
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 322962312 bytes
Database Buffers 687865856 bytes
Redo Buffers 55848960 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select dbid,name,flashback_on,current_scn from v$database;
DBID NAME FLASHBACK_ CURRENT_SCN
---------- --------------------------- ---------- -----------
1085678857 KING YES 1229459
SQL> show parameter db_flashback
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_flashback_retention_target integer 1440
SQL> alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';
Session altered.
SQL> select count(*) from orders;
COUNT(*)
----------
1430069
SQL> select sysdate from dual;
SYSDATE
-------------------
2018-09-24 13:43:57
SQL> truncate table orders;
Table truncated.
SQL> select count(*) from orders;
COUNT(*)
----------
0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 322962312 bytes
Database Buffers 687865856 bytes
Redo Buffers 55848960 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2018-09-24 13:43:57','yyyy-mm-dd hh34:mi:ss');
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> conn soe/soe
Connected.
SQL> select count(*) from orders;
COUNT(*)
----------
1430069
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 322962312 bytes
Database Buffers 687865856 bytes
Redo Buffers 55848960 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> conn soe/soe
Connected.
SQL> select count(*) from orders;
COUNT(*)
----------
1430069总结：闪回版本查询，闪回事务查询，闪回表与UNDO有关，与闪回日志没有任何关系 闪回删除与recyclebin有关，与闪回日志没有任何关系 闪回数据库与闪回日志有关
相关内容
热门资讯
缺油！日本快撑不住了
日本零食巨头卡乐比为节省油墨竟将原本漂亮的包装改成了黑白两色，从“喜食”变得看上去像“丧食”。日本石...
英国首相斯塔默再遭逼宫，在内阁...
【文/观察者网 熊超然】在上周经历地方选举惨败后，作为执政党领袖的英国首相斯塔默于当地时间5月11日...
日防相声称：新西兰考虑进口日本...
据凤凰卫视报道，5月12日，日本防卫大臣小泉进次郎在记者会上表示，新西兰已将日本海上自卫队最上型改良...
小米YU7 GT“车厘子红”无...
5 月 12 日消息，博主 @王的男人、昨日晒出了小米 YU7 GT「车厘子红」实车照片。画面显示，...
中关村论坛重磅发布！大兴机场临...
3月27日，在中关村论坛数据跨境流动创新发展论坛上，北京大兴国际机场临空经济区（大兴）正式发布跨境可...
白宫公布随特朗普访华16位商界...
白宫11日公布了将随特朗普一同访华的商界领袖名单。据多家美媒报道，总共将有16位美国商界代表来到北京...
荣耀申请代码生成方法专利，提高...
国家知识产权局信息显示，南京荣耀软件技术有限公司申请一项名为“代码生成方法、电子设备及存储介质”的专...
凤凰连线：中美新一轮经贸磋商，...
中美双方将在韩国举行第七轮经贸磋商。美方的阵容和日程安排如何？在这轮磋商中有哪些关切？凤凰卫视驻韩国...
知情人士：阿联酋秘密打击伊朗，...
据参考消息援引美国《华尔街日报》网站5月11日报道，多名知情人士透露，阿联酋已对伊朗发动军事打击，令...
美防长称美伊停火协议依然有效
△赫格塞思（资料图）当地时间5月12日，美国国防部长赫格塞思表示，他们针对伊朗问题的所有情况都制定了...