当前位置：首页 > article >正文

Oracle Flashback示例集锦

article 2024/11/14 20:26:29

Flashback Table

本例参考Rewind a Table Using Oracle Flashback Table

Flashback Table，Flashback Query和Flashback Drop的示例合集可参见这里
首先获得当前的时间，或当前的SCN，以便后续回退使用

-- 获取SCN法1
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

-- 获取SCN法2
select current_scn from v$database;

-- 获取当前时间
alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
select sysdate from dual;

输出如下：

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 942616

CURRENT_SCN
-----------
    9426162

SYSDATE
-----------------
09-01-23 15:03:33

以SYS登入，并修改数据：

connect / as sysdba
alter session set container=orclpdb1;
alter table hr.regions enable row movement;
select * from hr.regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

update hr.regions set region_name = 'ORACLE';
commit;
select * from hr.regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 ORACLE
         2 ORACLE
         3 ORACLE
         4 ORACLE

此时，可以采用以下几种方法恢复：

-- 恢复到5分钟前
flashback table hr.regions to timestamp sysdate - 5/24/60;

-- 恢复到指定时间点
flashback table hr.regions to timestamp to_timestamp('09-01-23 15:03:33','dd-mm-yy hh24:mi:ss');

-- 恢复到指定的SCN
flashback table hr.regions to scn 9426162；

也可以用类似的语法查询之前的数据：

select * from hr.regions as of timestamp to_timestamp('09-01-23 15:03:33','dd-mm-yy hh24:mi:ss');
select * from hr.regions as of 9426162;
select * from hr.regions as of timestamp sysdate - 1/24/60;

Flashback Drop

本例参考[https://docs.oracle.com/en/database/oracle/oracle-database/tutorial-rec-flashback/index.html?opt-release-19c](Recover a Dropped Table Using Oracle Flashback Drop)。

LiveSQL中的例子可参见这里。

以SYS用户登录并执行以下：

connect / as sysdba
alter session set container=orclpdb1;
create table hr.regions_hist as select * from hr.regions; 
select * from hr.regions_hist;


 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

drop table hr.regions_hist;
select * from hr.regions_hist;
select * from hr.regions_hist
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

flashback table hr.regions_hist to before drop;
select * from hr.regions_hist;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

truncate table hr.regions_hist;

Table truncated.

flashback table hr.regions_hist to before drop;
flashback table hr.regions_hist to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

drop table hr.regions_hist purge;

Flashback Transaction

本例参考Backing Out Transactions with Flashback

LiveSQL中的示例可参考这里。
什么是Flashback Transaction Back-Out?

Flashback Transaction Back-Out is a logical recovery option to roll back a target transaction and its dependent transactions while the database remains online. A dependent transaction is related by either a write-after-write (WAW) relationship, in which a transaction modifies the same data that was changed by the target transaction, or a primary key constraint relationship, in which a transaction re-inserts the same primary key value that was deleted by the target transaction. Flashback Transaction utilizes undo and the redo generated for undo blocks, to create and execute a compensating transaction for reverting the affected data back to its original state.

Flashback Transaction要求启用归档。如果没有启用，请运行以下脚本：

echo "******************************************* "
echo "For demo purposes ONLY:"
echo "  * Unlock HR account"
echo "  * Enable ARCHIVELOG mode for database"
echo ""
echo "The script may appear to hang at the SQL prompt"
echo "when the database is shutting down and being"
echo "opened. Wait a few minutes and it should progress."
echo "******************************************* "
sqlplus "/ as sysdba" << EOF

ALTER USER HR IDENTIFIED BY hr ACCOUNT UNLOCK;
ALTER SYSTEM SET db_recovery_file_dest_size = 10737418240 SCOPE=BOTH;

shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list
exit
EOF

本实验的初始化需要运行以下脚本：

REM "******************************************* "
REM "For demo purposes ONLY:"
REM "  * Setup for Flashback Transaction"
REM "Execute script as SYSDBA" 

set echo on
set serveroutput on
set term on
set lines 200
set pause on

/*== Set up the HR database account for this OBE ==*/

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

ALTER SESSION SET CONTAINER = orclpdb1;
GRANT EXECUTE ON dbms_flashback TO hr;
GRANT select any transaction TO hr;
pause Press [Enter] to continue...

/*== Create test data for flashback transaction ==*/
connect hr@orclpdb1;

/*== Test transaction 1 ==*/
INSERT INTO hr.regions VALUES (10,'Pole');
INSERT INTO hr.regions VALUES (20,'Moon');
INSERT INTO hr.regions VALUES (30,'Venus');
INSERT INTO hr.regions VALUES (40,'Mars');
INSERT INTO hr.regions VALUES (50,'Saturn');
COMMIT;
pause Press [Enter] to continue...

/*== Test transaction 2 ==*/
/*== Region 10 and 20 has a WAW dependency on transaction 1 ==*/
UPDATE hr.regions SET region_name='Two Poles'  WHERE region_id = 10;
UPDATE hr.regions SET region_name='Many Moons' WHERE region_id = 20;
COMMIT;
pause Press [Enter] to continue...

/*== Test transaction 3 ==*/
/*== Region 10 has a WAW dependency on transaction 1 and 2 ==*/
/*== Region 40 and 50 has a WAW dependency on transaction 1 ==*/
UPDATE hr.regions SET region_name='No star'  WHERE region_id = 10;
UPDATE hr.regions SET region_name='Red star' WHERE region_id = 40;
UPDATE hr.regions SET region_name='Big star' WHERE region_id = 50;
COMMIT;
pause Press [Enter] to continue...

/*== Test transaction 4 ==*/
/*== Region 30 has a WAW dependency on transaction 1 ==*/
UPDATE hr.regions SET region_name='Still called Venus' WHERE region_id = 30;
COMMIT;

pause Press [Enter] to continue...

connect / as sysdba
ALTER SYSTEM ARCHIVE LOG CURRENT;

prompt "Setup for Flashback Transaction completed"
pause Press [Enter] to continue...

exit

查看当前数据：

select * from hr.regions;


 REGION_ID REGION_NAME
---------- -------------------------
        10 No star
        20 Many Moons
        30 Still called Venus
        40 Red star
        50 Big star
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

9 rows selected.

查询和regions表相关的事务：

set lines 140
set pages 9999
col VERSIONS_STARTTIME for a22
col VERSIONS_ENDTIME for a22
select region_id, region_name, versions_xid, versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_operation from hr.regions versions between scn minvalue and maxvalue;

 REGION_ID REGION_NAME               VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_STARTTIME   VERSIONS_ENDSCN VERSIONS_ENDTIME     V
---------- ------------------------- ---------------- ----------------- -------------------- --------------- -------------------- -
        30 Still called Venus        05001900BE030000           9430052 09-JAN-23 03.26.31 P                                      U
                                                                        M

        50 Big star                  03001400EE030000           9430048 09-JAN-23 03.26.22 P                                      U
                                                                        M

        40 Red star                  03001400EE030000           9430048 09-JAN-23 03.26.22 P                                      U
                                                                        M

        10 No star                   03001400EE030000           9430048 09-JAN-23 03.26.22 P                                      U
                                                                        M

        20 Many Moons                02001600C0030000           9430043 09-JAN-23 03.26.10 P                                      U
                                                                        M

        10 Two Poles                 02001600C0030000           9430043 09-JAN-23 03.26.10 P         9430048 09-JAN-23 03.26.22 P U
                                                                        M                                    M

        50 Saturn                    09000B00BB030000           9430037 09-JAN-23 03.25.53 P         9430048 09-JAN-23 03.26.22 P I
                                                                        M                                    M

        40 Mars                      09000B00BB030000           9430037 09-JAN-23 03.25.53 P         9430048 09-JAN-23 03.26.22 P I
                                                                        M                                    M

        30 Venus                     09000B00BB030000           9430037 09-JAN-23 03.25.53 P         9430052 09-JAN-23 03.26.31 P I
                                                                        M                                    M

        20 Moon                      09000B00BB030000           9430037 09-JAN-23 03.25.53 P         9430043 09-JAN-23 03.26.10 P I
                                                                        M                                    M

        10 Pole                      09000B00BB030000           9430037 09-JAN-23 03.25.53 P         9430043 09-JAN-23 03.26.10 P I
                                                                        M                                    M

         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

15 rows selected.

可查询行的所有修改历史。以上结果按时间降序排列，因此需从底部读起。

以上minvalue和maxvalue实际为scn.minvalue和scn.maxvalue，是常数。也可以查询某事件范围，如between timestamp(systimestamp - 1/24) and timestamp
依赖于伪列(pseudocolumns ); 伪列不是ISO标准。以上VERSIONS_XID，VERSIONS_STARTSCN ，VERSIONS_ENDSCN，VERSIONS_OPERATION都是伪列。最著名的伪列是ROWID。

Flashback Data Archive

本例参考（以后再写）

其它参考

Oracle 10g: Recovering from Human Error Using Flashback

Object Level	Scenario Examples	Flashback Technology	Depends On	Affectes Data
Database	Truncate table; undesired multitable changes made	Database	Flashback logs	TRUE
Table	Drop table	Drop	Recycle bin	TRUE
	Update with the wrong WHERE clause	Table	Undo data	TRUE
	Compare current data with data from the past	Query	Undo data	FALSE
	Compare versions of rows	Version Query	Undo data	FALSE
Transaction	Investigate and back out suspect transactions	Transaction Query	Undo/redo from archive logs	TRUE
Table and Transaction	Audit, compliance, historical reports, ILM	Data Archival (Temporal)	Tablespace	FALSE