Oracle principle: flashback


1, Flash back query method in 9i

2, 10g flashback version query method

3, 10g flashback query method and data recovery

4, 10g flashback meter

5, 10g flashback delete

6, 10g Flashback Database

1, Flash back query method in 9i

You can query forward according to the time point or SCN(System Change Number) to obtain the data before modification. The flashback query depends on the front image of the data stored in the rollback segment by setting undo_retention parameter is the retention time of the previous image.

     select ...   as of scn |timestamp

For example, there are 8 rows of data in an existing table:

When the retention time of the rollback segment is still in effect, you can know the exact time you want to restore to

delete salary_tbl where employer_nm like 'Employer import 1';
commit ;
select * from salary_tbl  as of timestamp to_date('20200723 22:45:30','yyyymmdd hh24:mi:ss');

Checkpoint query: you need to estimate the value of checkpoints

select current_scn from v$database;  --Current query checkpoint
delete salary_tbl where employer_nm like 'Employer import 1';
commit ;
select * from salary_tbl  as of scn 8681060;

Recover data with flashback query

create table salary_tbl_bak as select * from salary_tbl  as of timestamp to_date('20200723 22:45:30','yyyymmdd hh24:mi:ss');
truncate table salary_tbl;
insert into salary_tbl select * from salary_tbl_bak;
drop table salary_tbl_bak;

2, 10g flashback version query method

10g provides a flashback version, which can be queried in different versions within a time period.

select  ...  from ... versions between 

Select can select fake or inferior, query ID, SCN number, etc

select versions_startTime,versions_endtime,versions_xid,versions_operation,
                                      salary_tbl.leader_nm from  salary_tbl  versions between timestamp minvalue and maxvalue

version_xid stands for object Id and version_operation can see the operation type

3, 10g flashback query method and data recovery

    alter database add supplemental log data; ---- enable minimum additional logs

select * from flashback_transaction_query where TABLE_NAME='SALARY_TBL' order by start_timestamp desc;

The function of this view is as follows

comment on table SYS.FLASHBACK_TRANSACTION_QUERY is 'Description of the flashback transaction query view';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.XID is 'Transaction identifier';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.START_SCN is 'Transaction start SCN';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.START_TIMESTAMP is 'Transaction start timestamp';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.COMMIT_SCN is 'Transaction commit SCN';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.COMMIT_TIMESTAMP is 'Transaction commit timestamp';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.LOGON_USER is 'Logon user for transaction';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.UNDO_CHANGE# is '1-based undo change number';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.OPERATION is 'forward operation for this undo';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.TABLE_NAME is 'table name to which this undo applies';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.TABLE_OWNER is 'owner of table to which this undo applies';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.ROW_ID is 'rowid to which this undo applies';
comment on column SYS.FLASHBACK_TRANSACTION_QUERY.UNDO_SQL is 'SQL corresponding to this undo';

Special note: UNDO_SQL is the SQL of transaction rollback; Just follow the commit_ When timestamp is arranged in order, you can know the history of the submitted things.

If you want to restore the data of no tables: first, use TABLE_NAME to filter, COMMIT_TIMESTAMP is arranged in order. Where do you want to restore to? Execute undo one by one_ SQL is OK


4, 10g flashback meter

10g can return the data in the table to a certain time, or SCN. The premise is that row migration must be enabled

    alter  table  salary_tbl enable row movement;

Then you can flash back the table.

flashback table table_name  to timestamp|SCN

flashback table salary_tbl to timestamp timestamp'2020-7-23 22:00:00'


5, 10g flashback delete

oracle # also has something like a recycle bin to hold deleted data. Enable the recycle bin. The recycle bin only puts the deleted tables of ordinary users, so the SYS user and SYSTEM tablespace will not be placed here;

alter session set recylcebin=on

>>show recyclebin 

flashback table <tablename> to before drop; -- flashback delete

drop table <tablename> purge; --------- completely delete

purge recyclebin ;--------- Empty Trash

-----Query the original table name and recycle bin
select * from tab where TABTYPE='TABLE';
select * from recyclebin;
--------Delete a table------------
create table salary_tbl_bak as select * from salary_tbl;
drop table  salary_tbl_bak;
flashback table salary_tbl_bak to before drop;

In Oracle, if the same table is deleted repeatedly, the flashback table is the most recently deleted table, or you can directly select < "table name in recyclebin" > double quotes


6, 10g Flashback Database

If there is a physical error in the database, such as a damaged hard disk, the flashback database is not applicable here. If there is a logical error in the database, you can recover and fallback by flashing back to the database. The previous flashbacks are flashbacks using rollback segments, while the flashback database is returned using logs.

1. The database shall be in archive mode

  >>shutdown immediate;

  >>startup mount

 >>alter database archivelog

 >> alter database open

2. Configure flashback recovery area

   show parameter DB_RECOVE

3. Configure flashback retention time (unit: minutes)

show parameter db_flashback_reten

4. In the mount state, enable the database flashback to alter database flashback on;

At this point, you can_ recovery_ file_ You can see the files needed to flash back in the directory under the dest parameter value;

5. Flash back to the database in the mount state

 flashback database  to timestamp | scn

There is an existing table. The time at this time is "select systimestamp from dual"---- 26-july-20 PM + 08:00

Execute drop table salary_tbl;

Tags: Database Oracle

Posted by ilight on Mon, 30 May 2022 06:42:17 +0530