About Oracle Flashback Technology
You can use the various features of Oracle Flashback to view past states of data and rewind your database without restoring backups or performing point-in-time recovery. In general, flashback features are more efficient and less disruptive than media recovery in most situations in which they apply. Most of the flashback features of Oracle operate at the logical level, enabling you to view and manipulate database objects. Except for Oracle Flashback Drop, the logical flashback features rely on undo data, which are records of the effects of each database update and the values overwritten in the update. Flashback Technology can be devided in two category as below => Features Used by Developers A-> Flashback Query B-> Flashback Version Query C-> Flashback Transaction Query D-> Flashback Transaction => Features Used by Database Administrator. A-> Flashback TABLE B-> Flashback Drop C-> Flashback Database
PART 1 => FLASHBACK Features Used by Developers
A=> Flashback Query (SELECT AS OF)
To use Oracle Flashback Query, use a SELECT statement with an AS OF clause.
Oracle Flashback Query retrieves data as it existed at an earlier time.
The query explicitly references a past time through a time stamp or System Change Number (SCN).
It returns committed data that was current at that point in time.
It usages UNDO records in combination with Archivelogs.
Example
=> Check FLASHBACK_ON as below.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
=> Check if Database is in Archivelog Mode
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 262
Next log sequence to archive 264
Current log sequence 264
SQL>
=> Check if DB_Reco parameter is Set as below.
SQL> show parameter db_Reco
NAME TYPE VALUE
------------------------------------ -----------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 8256M
=>Check if UNDO is set as below,we can go back in time for Last 15 minutes
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
As our prerequisites completes let we start creating sample table and Insert few sample data as below.
=> Create Table
SQL> create table Flash_data (id int,name varchar2(20));
Table created.
=> Insert Few sample data
SQL> insert into Flash_data values (10,'DEV');
1 row created.
SQL> select * from Flash_data;
ID NAME
---------- --------------------
10 DEV
20 TRAT
30 GRETA
=> Check Timestamp
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
03-AUG-22 08.21.45.037603 PM +05:30
=> Let we DELETE 1 record as below
SQL> delete from flash_data where ID=10;
1 row deleted.
SQL> commit;
Commit complete.
=> Check Current data in table
SQL> select * from Flash_data;
ID NAME
---------- --------------------
20 TRAT
30 GRETA
=> Let we Use FLASHBACK Query as check previous data.
SQL> select * from flash_data AS OF TIMESTAMP TO_TIMESTAMP('03-AUG-22 20.21.45','DD-MON-YY HH24.MI.SS');
ID NAME
---------- --------------------
10 DEV
20 TRAT
30 GRETA
As we can see , we can fetch deleted Rows using Flashback Query Method.
B=> Oracle Flashback Version Query
Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval.
A row version is created whenever a COMMIT statement is executed.
Specify Oracle Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement.
The syntax is :
VERSIONS BETWEEN { SCN | TIMESTAMP } start AND end
where start and end are expressions representing the start and end, respectively, of the time interval to be queried. The time interval includes (start and end).
Oracle Flashback Version Query returns a table with a row for each version of the row that existed at any time during the specified time interval.
Each row in the table includes pseudocolumns of metadata about the row version, which can reveal when and how a particular change (perhaps erroneous) occurred to your database.
VERSIONS_STARTSCN & VERSIONS_STARTTIME -> Starting System Change Number (SCN) or TIMESTAMP when the row version was created.
This pseudocolumn identifies the time when the data first had the values reflected in the row version.
If this pseudocolumn is NULL, then the row version was created before start.
VERSIONS_ENDSCN & VERSIONS_ENDTIME -> SCN or TIMESTAMP when the row version expired.
If this pseudocolumn is NULL, then either the row version was current at the time of the query or the row corresponds to a DELETE operation.
VERSIONS_OPERATION -> Operation performed by the transaction: I for insertion, D for deletion, or U for update.
The version is that of the row that was inserted, deleted, or updated; represented as two version rows with a D followed by an I VERSIONS_OPERATION.
Example
=> Let we insert few rows in above
SQL> select * from Flash_data;
ID NAME
---------- --------------------
20 TRAT
30 GRETA
SQL> insert into flash_data values (10,'GITA');
1 row created.
SQL> insert into flash_data values (40,'MOJO');
1 row created.
SQL> commit;
Commit complete.
SQL> select systimestamp from dual;
SYSTIMESTAMP
------------------------------------------
03-AUG-22 08.34.25.129217 PM +05:30
=> Check Flash Version Query as below.
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
id,name
FROM flash_data
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('03-AUG-22 20.21.45','DD-MON-YY HH24.MI.SS')
AND TO_TIMESTAMP('03-AUG-22 20.34.25','DD-MON-YY HH24.MI.SS');
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_OPERATION ID NAME
----------------- ------------------------------ --------------- ------------------------- -------------------- ---------- -----
5385961 03-AUG-22 08.33.44 PM I 40 MOJO
5385961 03-AUG-22 08.33.44 PM I 10 GITA
5382475 03-AUG-22 08.21.59 PM D 10 DEV
5382475 03-AUG-22 08.21.59 PM 10 DEV
20 TRAT
30 GRETA
6 rows selected.
C=>Oracle Flashback Transaction Query
Use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY, whose columns are described in Oracle Database Reference. The column UNDO_SQL shows the SQL code that is the logical opposite of the DML operation performed by the transaction. You can usually use this code to reverse the logical steps taken during the transaction. However, there are cases where the UNDO_SQL code is not the exact opposite of the original transaction. For example, a UNDO_SQL INSERT operation might not insert a row back in a table at the same ROWID from which it was deleted. This statement queries the FLASHBACK_TRANSACTION_QUERY view for transaction information, SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('000200030000002D'); This statement uses Oracle Flashback Version Query as a subquery to associate each row version with the LOGON_USER responsible for the row data change: SELECT xid, logon_user FROM flashback_transaction_query WHERE xid IN ( SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS') ); Note: If you query FLASHBACK_TRANSACTION_QUERY without specifying XID in the WHERE clause, the query scans many unrelated rows, degrading performance. Example => Create Table SQL> create table FLASH_DETAIL (ID INT,NAME VARCHAR2(20)); Table created. => Insert Sample Records SQL> insert into flash_detail values (100,'GLASS'); 1 row created. SQL> insert into flash_detail values (101,'BIRD'); 1 row created. SQL> insert into flash_detail values (102,'COW'); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM FLASH_DETAIL; ID NAME ---------- -------------------- 100 GLASS 101 BIRD 102 COW SQL> SELECT SYSTIMESTAMP FROM DUAL; SYSTIMESTAMP --------------------------------------------------------------------------- 03-AUG-22 09.22.33.876504 PM +05:30 => Update Few Records SQL> UPDATE FLASH_dETAIL SET NAME='FOX' WHERE ID=100; 1 row updated. SQL> COMMIT; Commit complete. => Check Flashback transaction Query as below> SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid IN ( SELECT versions_xid FROM abhi_test.flash_DETAIL VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('03-AUG-22 21.21.45','DD-MON-YY HH24.MI.SS') AND TO_TIMESTAMP('03-AUG-22 21.22.25','DD-MON-YY HH24.MI.SS') ); SQL> COL UNDO_SQL FOR A90 SQL> / XID OPERATION START_SCN COMMIT_SCN LOGON_USER UNDO_SQL ---------------- ---------- ---------- ---------- ---------- ------------------------------------------------------------------------------------------ 02000E00B0030000 UPDATE 5400840 5400850 ABHI_TEST update "ABHI_TEST"."FLASH_DETAIL" set "NAME" = 'GLASS' where ROWID = 'AAASAtAAHAAAAFnAAA'; 02000E00B0030000 BEGIN 5400840 5400850 ABHI_TEST 02001800AD030000 INSERT 5400169 5400222 ABHI_TEST delete from "ABHI_TEST"."FLASH_DETAIL" where ROWID = 'AAASAtAAHAAAAFnAAC'; 02001800AD030000 INSERT 5400169 5400222 ABHI_TEST delete from "ABHI_TEST"."FLASH_DETAIL" where ROWID = 'AAASAtAAHAAAAFnAAB'; 02001800AD030000 INSERT 5400169 5400222 ABHI_TEST delete from "ABHI_TEST"."FLASH_DETAIL" where ROWID = 'AAASAtAAHAAAAFnAAA'; 02001800AD030000 BEGIN 5400169 5400222 ABHI_TEST 6 rows selected.
D=>Flashback Transaction
Use Flashback Transaction to roll back a transaction and its dependent transactions while the database remains online.
This recovery operation uses undo data to create and run the corresponding compensating transactions that return the affected data to its original state.
(Flashback Transaction is part of DBMS_FLASHBACK package).
DBMS_FLASHBACK
Using DBMS_FLASHBACK, you can flash back to a version of the database at a specified time or a specified system change number (SCN).
DBMS_FLASHBACK Overview
DBMS_FLASHBACK provides an interface for the user to view the database at a particular time in the past,
with the additional capacity provided by transaction back out features that allow for selective removal of the effects of individual transactions.
This is different from a flashback database which moves the database back in time.
When DBMS_FLASHBACK is enabled, the user session uses the Flashback version of the database, and applications can execute against the Flashback version of the database.
DBMS_FLASHBACK Operational Notes
DBMS_FLASHBACK is automatically turned off when the session ends, either by disconnection or by starting another connection.
DML and DDL operations and distributed operations are not allowed while a session is running in Flashback mode.
You can use PL/SQL cursors opened before disabling Flashback to perform DML.
Under Automatic Undo Management (AUM) mode, you can use retention control to control how far back in time to go for the version of the database you need.
If you need to perform a Flashback over a 24-hour period, the DBA must set the undo_retention parameter to 24 hours.
This way, the system retains enough undo information to regenerate the older versions of the data.
You can set the RETENTION GUARANTEE clause for the undo tablespace to ensure that unexpired undo is not discarded.
UNDO_RETENTION is not in itself a guarantee because, if the system is under space pressure, unexpired undo may be overwritten with freshly generated undo. In such cases,
RETENTION GUARANTEE prevents this.
Summary of DBMS_FLASHBACK Subprograms
This table lists the DBMS_FLASHBACK subprograms and briefly describes them.
Subprogram Description
DISABLE -> Disables the Flashback mode for the entire session
ENABLE_AT_SYSTEM_CHANGE_NUMBER -> Enables Flashback for the entire session. Takes an SCN as an Oracle number and sets the session snapshot to the specified number.
Inside the Flashback mode, all queries return data consistent as of the specified wall-clock time or SCN
ENABLE_AT_TIME -> Enables Flashback for the entire session. The snapshot time is set to the SCN that most closely matches the time specified in query_time
GET_SYSTEM_CHANGE_NUMBER -> Returns the current SCN as an Oracle number. You can use the SCN to store specific snapshots
TRANSACTION_BACKOUT -> Provides the mechanism to back out a transaction
Example
=> Create Sample Table and SCN Table to store current scn of database.
SQL> CREATE TABLE keep_scn (scn number);
SQL > Table created.
SQL> CREATE TABLE employee (
employee_no number(5) PRIMARY KEY,
employee_name varchar2(20),
employee_mgr number(5),
salary number,
hiredate date
);
SQL > Table created.
=> Populate Records
REM -- Populate the company with employees
INSERT INTO employee VALUES (1, 'John Doe', null, 1000000, '5-jul-81');
INSERT INTO employee VALUES (10, 'Joe Johnson', 1, 500000, '12-aug-84');
INSERT INTO employee VALUES (20, 'Susie Tiger', 10, 250000, '13-dec-90');
INSERT INTO employee VALUES (100, 'Scott Tiger', 20, 200000, '3-feb-86');
INSERT INTO employee VALUES (200, 'Charles Smith', 100, 150000, '22-mar-88');
INSERT INTO employee VALUES (210, 'Jane Johnson', 100, 100000, '11-apr-87');
INSERT INTO employee VALUES (220, 'Nancy Doe', 100, 100000, '18-sep-93');
INSERT INTO employee VALUES (300, 'Gary Smith', 210, 75000, '4-nov-96');
INSERT INTO employee VALUES (310, 'Bob Smith', 210, 65000, '3-may-95');
COMMIT;
=> Check Records
SQL> select * from employee;
EMPLOYEE_NO EMPLOYEE_NAME EMPLOYEE_MGR SALARY HIREDATE
----------- -------------------- ------------ ---------- ---------
1 John Doe 1000000 05-JUL-81
10 Joe Johnson 1 500000 12-AUG-84
20 Susie Tiger 10 250000 13-DEC-90
100 Scott Tiger 20 200000 03-FEB-86
200 Charles Smith 100 150000 22-MAR-88
210 Jane Johnson 100 100000 11-APR-87
220 Nancy Doe 100 100000 18-SEP-93
300 Gary Smith 210 75000 04-NOV-96
310 Bob Smith 210 65000 03-MAY-95
9 rows selected.
=> Store current SCN
SQL> REM -- Store this snapshot for later access through Flashback
DECLARE
I NUMBER;
BEGIN
I := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
INSERT INTO keep_scn VALUES (I);
COMMIT;
END;
/SQL> 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
=> Check current scn from table.
SQL> SELECT * FROM keep_scn;
SCN
----------
5467821
=> Delete 1 record and Commit.
SQL> DELETE FROM EMPLOYEE WHERE employee_name = 'Scott Tiger';
COMMIT;
1 row deleted.
Commit complete.
=> Check Record - EMP_NO 100 is missing
SQL> select * from employee;
EMPLOYEE_NO EMPLOYEE_NAME EMPLOYEE_MGR SALARY HIREDATE
----------- -------------------- ------------ ---------- ---------
1 John Doe 1000000 05-JUL-81
10 Joe Johnson 1 500000 12-AUG-84
20 Susie Tiger 10 250000 13-DEC-90
200 Charles Smith 100 150000 22-MAR-88
210 Jane Johnson 100 100000 11-APR-87
220 Nancy Doe 100 100000 18-SEP-93
300 Gary Smith 210 75000 04-NOV-96
310 Bob Smith 210 65000 03-MAY-95
8 rows selected.
=> Use DBMS_FLASHBACK and ENABLE CHANGE as below,
SQL> DECLARE
restore_scn number;
BEGIN
SELECT scn INTO restore_scn FROM keep_scn;
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (restore_scn);
END;
/
PL/SQL procedure successfully completed.
=> Check value of missing record ,It's present in table now.
SQL> select * from employee;
EMPLOYEE_NO EMPLOYEE_NAME EMPLOYEE_MGR SALARY HIREDATE
----------- -------------------- ------------ ---------- ---------
1 John Doe 1000000 05-JUL-81
10 Joe Johnson 1 500000 12-AUG-84
20 Susie Tiger 10 250000 13-DEC-90
100 Scott Tiger 20 200000 03-FEB-86
200 Charles Smith 100 150000 22-MAR-88
210 Jane Johnson 100 100000 11-APR-87
220 Nancy Doe 100 100000 18-SEP-93
300 Gary Smith 210 75000 04-NOV-96
310 Bob Smith 210 65000 03-MAY-95
9 rows selected.
Same information can be used to insert into the table.
PART 2 => FLASHBACK Features Used by DBA
A-> Flashback TABLE
1-> Flashback TABLE using Time.
-> Check data in sample table
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
->Get Current time either using SCN
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3653408
SQL> select scn_to_timestamp(3653408) from dual;
SCN_TO_TIMESTAMP(3653408)
---------------------------------------------------------------------------
27-NOV-22 11.04.18.000000000 PM
->Insert rows in sample table.
SQL> insert into table_del values (40,'OLDZEAL');
1 row created.
SQL> insert into table_del values (50,'TAMIL');
1 row created.
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
40 OLDZEAL
50 TAMIL
SQL> COMMIT;
Commit complete.
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
40 OLDZEAL
50 TAMIL
->Flashback Table using Time
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> flashback table ABHI_TEST.TABLE_DEL TO TIMESTAMP TO_TIMESTAMP('27-NOV-22 23.04.18', 'DD-MON-YY HH24.MI.SS');
Flashback complete.
SQL>
->Check records in table
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
SQL>
2-> Flashback TABLE using SCN.
-> Check Sample Table
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> ALTER TABLE TABLE_DEL ENABLE ROW MOVEMENT;
Table altered.
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
-> Get Curren_SCN from database
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3653408
-> Insert values in sample table
SQL> insert into table_del values (40,'OLDZEAL');
1 row created.
SQL> insert into table_del values (50,'TAMIL');
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
40 OLDZEAL
50 TAMIL
-> Flashback table to SCN as below
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 27 22:56:07 2022
Version 19.3.0.0.0
SQL> flashback table ABHI_TEST.TABLE_DEL TO SCN 3653408;
Flashback complete.
SQL>
-> Check Rows of flashbacked table
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
SQL>
3-> Flashback TABLE using Restore Point.
-> Create Smaple Table
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create table table_del (id int,name varchar2(20));
Table created.
SQL> insert into table_del values (10,'GREAT');
1 row created.
SQL> insert into table_del values (20,'MEA');
1 row created.
SQL> insert into table_del values (30,'AFRICA');
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
-> Create Restore Point
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create restore point before_del;
Restore point created.
SQL> select SCN,DATABASE_INCARNATION#,TIME,RESTORE_POINT_TIME,NAME FROM v$restore_point WHERE NAME LIKE 'BEFORE_DEL';
SCN DATABASE_INCARNATION# NAME
--------------------------------------------------------------------------------
3648386 5 BEFORE_DEL
-> Insert data into Smaple Table
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> insert into table_del values (40,'OLDZEAL');
1 row created.
SQL> insert into table_del values (50,'TAMIL');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
40 OLDZEAL
50 TAMIL
> Flashback TABLE
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> FLASHBACK TABLE ABHI_TEST.TABLE_DEL TO RESTORE POINT BEFORE_DEL;
FLASHBACK TABLE ABHI_TEST.TABLE_DEL TO RESTORE POINT BEFORE_DEL
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
-> Enable ROW MOVEMENT
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> ALTER TABLE TABLE_DEL ENABLE ROW MOVEMENT;
Table altered.
> Flashback TABLE
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> FLASHBACK TABLE ABHI_TEST.TABLE_DEL TO RESTORE POINT BEFORE_DEL;
Flashback complete.
SQL>
>Count records in Sample Table
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
SQL>
B-> Flashback Drop
Flashback Drop reverses the effects of a DROP TABLE operation.
When you drop a table, the database does not immediately remove the space associated with the table.
Instead, the table is renamed and, along with any associated objects, placed in the recycle bin.
System-generated recycle bin object names are unique.
You can query objects in the recycle bin, just as you can query other objects.
A flashback operation retrieves the table from the recycle bin.
When retrieving dropped tables, you can specify either the original user-specified name of the table or the system-generated name.
-> Check sample table and DROP
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
SQL> drop table table_del;
Table dropped.
SQL> SHOW RECYCLEBIN;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TABLE_DEL BIN$7niIwSEfG4XgU2w4qMCMog==$0 TABLE 2022-11-27:23:30:46
SQL> select * from "BIN$7niIwSEfG4XgU2w4qMCMog==$0";
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
SQL> select owner,object_name,original_name from dba_recyclebin;
OWNER OBJECT_NAME ORIGINAL_NAME
-------------------- -------------------------------------------------- -----------------------
ABHI_TEST BIN$7niIwSEfG4XgU2w4qMCMog==$0 TABLE_DEL
-> Flashback table using BEFORE DROP
SQL> flashback table table_Del to before drop;
Flashback complete.
SQL> SHOW RECYCLEBIN;
SQL> select * from table_del;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
-> Check recycleBin
SQL> show recyclebin
SQL> select owner,object_name,original_name from dba_recyclebin;
no rows selected
SQL>
-> Rename dropped table as below.
SQL> drop table table_del;
Table dropped.
SQL> SHOW RECYCLEBIN;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TABLE_DEL BIN$7nigb+DcQqngU2w4qMDV4A==$0 TABLE 2022-11-27:23:37:23
SQL> FLASHBACK TABLE "BIN$7nigb+DcQqngU2w4qMDV4A==$0" TO BEFORE DROP RENAME TO table_del_new;
Flashback complete.
SQL> select * from table_del_new;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
SQL>
C1-> Flashback Database (Using Gauranteed restore point Only) - NO FLASHBACK_LOGS
Flashback Database reverses unwanted changes by returning your database to its state at a previous point in time.
Prerequisites of Flashback Database
Flashback Database works by undoing changes to the data files that exist at the moment that you run the command. Prerequisites must be met to perform a Flashback Database operation.
To use the FLASHBACK DATABASE command to return your database contents to points in time within the flashback window, your database must have been previously configured for flashback logging.
To return the database to a guaranteed restore point, you must have previously defined a guaranteed restore point.
-> Check Sample tables as below.
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
DATA
FLASH_DATA
TABLE_FLASH
TABLE_DEL_NEW
SQL>
->Create Gauranteed restore point as below
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 27 22:48:38 2022
Version 19.3.0.0.0
SQL> select * from v$restore_point;
no rows selected
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> create restore point CLEAN_DB guarantee flashback database;
Restore point created.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
SQL> select SCN,GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;
SCN GUARANTEE_FLASHBACK_DATABASE NAME
---------- --- -------------------------------------------------------
3661407 YES CLEAN_DB
SQL>
->Perform OPerations on Sample schema tables.
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
DATA
FLASH_DATA
TABLE_FLASH
TABLE_DEL_NEW
=>DROP OPERATION
SQL> drop table TABLE_DEL_NEW;
Table dropped.
=> INSERT OPERATION
SQL> select * from DATA;
DETAIL
--------------------
Before_Full
LEVEL1
LEVEL1_2
SQL> insert into DATA values ('TO_DEL');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM DATA;
DETAIL
--------------------
Before_Full
LEVEL1
LEVEL1_2
TO_DEL
SQL>
->Flashback database as below.
STOP AND START DB IN MOUNT MODE AS BELOW
[oracle@racsetn2 ~]$ srvctl config database
TNT
[oracle@racsetn2 ~]$ srvctl stop database -d TNT
[oracle@racsetn2 ~]$ srvctl start database -d TNT -o "MOUNT"
SQL> select name,open_mode,database_role from gv$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TNT MOUNTED PRIMARY
TNT MOUNTED PRIMARY
FALSHBACK DATABASE TO RESTORE POINT
SQL> flashback database to restore point CLEAN_DB;
Flashback complete.
->Recycle database with OPEN RESETLOGS as below.Start on NODE2 ONLY IN MOUNT
[oracle@racsetn2 ~]$ srvctl stop database -d TNT
[oracle@racsetn2 ~]$ srvctl start instance -i TNT2 -d TNT -o "MOUNT"
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter database open resetlogs;
Database altered.
SQL> EXIT
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@racsetn2 ~]$ srvctl stop database -d TNT
[oracle@racsetn2 ~]$ srvctl start database -d TNT
->Check Sample data as we checked above.
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
DATA
FLASH_DATA
TABLE_FLASH
TABLE_DEL_NEW <<< Table restored
SQL> select * from TABLE_DEL_NEW;
ID NAME
---------- --------------------
10 GREAT
20 MEA
30 AFRICA
SQL> SELECT * FROM DATA; <<< rows removed
DETAIL
--------------------
Before_Full
LEVEL1
LEVEL1_2
SQL>
Alert Log details
----
2022-11-27T22:45:39.833540+05:30
ALTER SYSTEM SET _ipddb_enable=TRUE SCOPE=MEMORY SID='TNT2';
2022-11-27T23:57:48.447556+05:30
Starting background process RVWR
2022-11-27T23:57:48.458838+05:30
RVWR started with pid=59, OS id=29268
2022-11-27T23:58:07.904644+05:30
Allocating 15937344 bytes in shared pool for flashback generation buffer.
Allocated 15937344 bytes in shared pool for flashback generation buffer
2022-11-27T23:58:48.907820+05:30
Created guaranteed restore point CLEAN_DB
2022-11-28T00:04:21.748251+05:30
---
----
flashback database to restore point CLEAN_DB
2022-11-28T00:06:39.631839+05:30
Flashback Restore Start
2022-11-28T00:06:40.710055+05:30
Flashback Restore Complete
Flashback Media Recovery Start
Started logmerger process
2022-11-28T00:06:41.315896+05:30
Parallel Media Recovery started with 4 slaves
2022-11-28T00:06:41.825863+05:30
Recovery of Online Redo Log: Thread 2 Group 4 Seq 30 Reading mem 0
Mem# 0: +DATA/TNT/ONLINELOG/group_4.292.1115904165
Mem# 1: +FRA/TNT/ONLINELOG/group_4.738.1115904165
2022-11-28T00:06:41.845904+05:30
Recovery of Online Redo Log: Thread 1 Group 2 Seq 52 Reading mem 0
Mem# 0: +DATA/TNT/ONLINELOG/group_2.288.1115903115
Mem# 1: +FRA/TNT/ONLINELOG/group_2.735.1115903115
2022-11-28T00:06:41.958755+05:30
Incomplete Recovery applied until change 3661408 time 11/27/2022 23:58:48
2022-11-28T00:06:42.335366+05:30
Flashback Media Recovery Complete
Completed: flashback database to restore point CLEAN_DB
2022-11-28T00:06:43.146580+05:30
ALTER SYSTEM SET remote_listener=' racset-scan:1521' SCOPE=MEMORY SID='TNT2';
----------
Completed: ALTER DATABASE MOUNT /* db agent *//* {2:294:2755} */
2022-11-28T00:12:49.481248+05:30
ALTER SYSTEM SET remote_listener=' racset-scan:1521' SCOPE=MEMORY SID='TNT2';
2022-11-28T00:12:49.482148+05:30
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='TNT2';
2022-11-28T00:12:59.910047+05:30
alter database open resetlogs
2022-11-28T00:13:00.714561+05:30
RESETLOGS after incomplete recovery UNTIL CHANGE 3661408 time 11/27/2022 23:58:48
2022-11-28T00:13:08.350754+05:30
NET (PID:6420): Archived Log entry 180 added for T-1.S-51 ID 0x5542054c LAD:1
2022-11-28T00:13:10.050495+05:30
TMON (PID:6199): STARTING ARCH PROCESSES
Starting background process ARC0
2022-11-28T00:13:10.063261+05:30
ARC0 started with pid=65, OS id=6464
Starting background process ARC1
2022-11-28T00:13:10.074080+05:30
ARC1 started with pid=67, OS id=6466
Starting background process ARC2
2022-11-28T00:13:10.085349+05:30
ARC2 started with pid=68, OS id=6468
Starting background process ARC3
2022-11-28T00:13:10.099455+05:30
ARC3 started with pid=69, OS id=6470
TMON (PID:6199): ARC0: Archival started
TMON (PID:6199): ARC1: Archival started
TMON (PID:6199): ARC2: Archival started
TMON (PID:6199): ARC3: Archival started
TMON (PID:6199): STARTING ARCH PROCESSES COMPLETE
2022-11-28T00:13:10.124829+05:30
TT00 (PID:6472): Gap Manager starting
2022-11-28T00:13:12.599809+05:30
NET (PID:6420): Archived Log entry 181 added for T-1.S-52 ID 0x5542054c LAD:1
2022-11-28T00:13:14.079377+05:30
NET (PID:6420): Archiving disabled T-2.S-29
2022-11-28T00:13:14.525889+05:30
NET (PID:6420): Archived Log entry 182 added for T-2.S-29 ID 0x5542054c LAD:1
2022-11-28T00:13:15.825041+05:30
NET (PID:6420): Archiving disabled T-2.S-30
2022-11-28T00:13:17.125299+05:30
NET (PID:6420): Archived Log entry 183 added for T-2.S-30 ID 0x5542054c LAD:1
NET (PID:6420): Clearing online redo logfile 1 +DATA/TNT/ONLINELOG/group_1.287.1115903115
NET (PID:6420): Clearing online redo logfile 2 +DATA/TNT/ONLINELOG/group_2.288.1115903115
NET (PID:6420): Clearing online redo logfile 3 +DATA/TNT/ONLINELOG/group_3.291.1115904155
NET (PID:6420): Clearing online redo logfile 4 +DATA/TNT/ONLINELOG/group_4.292.1115904165
Clearing online log 1 of thread 1 sequence number 51
Clearing online log 2 of thread 1 sequence number 52
Clearing online log 3 of thread 2 sequence number 29
Clearing online log 4 of thread 2 sequence number 30
2022-11-28T00:13:37.037158+05:30
Decreasing number of high priority LMS from 2 to 0
2022-11-28T00:13:50.195138+05:30
NET (PID:6420): Clearing online redo logfile 1 complete
NET (PID:6420): Clearing online redo logfile 2 complete
NET (PID:6420): Clearing online redo logfile 3 complete
NET (PID:6420): Clearing online redo logfile 4 complete
Resetting resetlogs activation ID 1430390092 (0x5542054c)
Online log +DATA/TNT/ONLINELOG/group_1.287.1115903115: Thread 1 Group 1 was previously cleared
Online log +FRA/TNT/ONLINELOG/group_1.736.1115903117: Thread 1 Group 1 was previously cleared
Online log +DATA/TNT/ONLINELOG/group_2.288.1115903115: Thread 1 Group 2 was previously cleared
Online log +FRA/TNT/ONLINELOG/group_2.735.1115903115: Thread 1 Group 2 was previously cleared
Online log +DATA/TNT/ONLINELOG/group_3.291.1115904155: Thread 2 Group 3 was previously cleared
Online log +FRA/TNT/ONLINELOG/group_3.737.1115904155: Thread 2 Group 3 was previously cleared
Online log +DATA/TNT/ONLINELOG/group_4.292.1115904165: Thread 2 Group 4 was previously cleared
Online log +FRA/TNT/ONLINELOG/group_4.738.1115904165: Thread 2 Group 4 was previously cleared
2022-11-28T00:13:51.264442+05:30
Setting recovery target incarnation to 6
2022-11-28T00:13:52.064004+05:30
ARC0 (PID:6464): Becoming a 'no FAL' ARCH
ARC0 (PID:6464): Becoming the 'no SRL' ARCH
2022-11-28T00:13:52.440773+05:30
This instance was first to open
Ping without log force is disabled:
not an Exadata system.
Picked broadcast on commit scheme to generate SCNs
Endian type of dictionary set to little
2022-11-28T00:13:53.384835+05:30
ALTER SYSTEM SET remote_listener=' racset-scan:1521' SCOPE=MEMORY SID='TNT2';
2022-11-28T00:13:53.386916+05:30
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='TNT2';
2022-11-28T00:13:53.624725+05:30
Assigning activation ID 1433176438 (0x556c8976)
Redo log for group 3, sequence 1 is not located on DAX storage
2022-11-28T00:13:54.808994+05:30
Thread 2 opened at log sequence 1
Current log# 3 seq# 1 mem# 0: +DATA/TNT/ONLINELOG/group_3.291.1115904155
Current log# 3 seq# 1 mem# 1: +FRA/TNT/ONLINELOG/group_3.737.1115904155
Successful open of redo thread 2
2022-11-28T00:13:54.809724+05:30
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
2022-11-28T00:13:55.093443+05:30
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
validate pdb 0, flags x10, valid 0, pdb flags x84
* validated domain 0, flags = 0x80
Instance recovery complete: valid 1 (flags x10, recovery domain flags x80)
2022-11-28T00:13:56.068920+05:30
Undo initialization recovery: err:0 start: 5551608 end: 5552442 diff: 834 ms (0.8 seconds)
2022-11-28T00:13:56.489718+05:30
Set LMHB to elevated priority
2022-11-28T00:13:56.758126+05:30
[6420] Successfully onlined Undo Tablespace 11.
Undo initialization online undo segments: err:0 s
C2-> Flashback Database (Using FLASHBACK_LOGS Only) - NO Gauranteed restore point .
In this article I will be showing how to FLASHBACK DATABASE with FLASHBACK logs ,But without any Gauranteed restore Points. Prerequisite Before starting this practice FLASHBACK has to be configured as per mentioned ENABLING_FLASHBACK_DATABASE. Step 1 => Let we check of any restore point created as database level. SQL> select * from v$restore_point; no rows selected Step 2=> Check Flashback status as below. SQL> select log_mode,flashback_on from gv$database; LOG_MODE FLASHBACK_ON ------------ ------------------ ARCHIVELOG YES ARCHIVELOG YES Step 3=> Check Recovery_file_dest and flashback_retentions details as below. SQL> show parameter db_reco NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 9000M SQL> SQL> show parameter flash db_flashback_retention_target integer 720 SQL> Step 4=> Let we create Sample TABLE with data as below. [oracle@racsetn1 ~]$ sqlplus ABHI_TEST/Oracle_4U Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> create table time_Data (time_Date timestamp); Table created. SQL> desc time_Data; Name Null? Type ----------------------------------------- -------- ---------------------------- TIME_DATE TIMESTAMP(6) SQL> insert into time_Data values (sysdate); 1 row created. SQL> alter session set nls_Date_format='DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> SELECT * FROM time_Data; TIME_DATE --------------------------------------------------------------------------- 16-DEC-22 01.09.03.000000 PM SQL> insert into time_Data values (sysdate); 1 row created. SQL> / 1 row created. SQL> SQL> insert into time_Data values (sysdate); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM time_Data; TIME_DATE --------------------------------------------------------------------------- 16-DEC-22 01.09.03.000000 PM 16-DEC-22 01.10.18.000000 PM 16-DEC-22 01.10.21.000000 PM 16-DEC-22 01.11.10.000000 PM Step 5=> Let we Check Current SCN and Current timestamp till where we will be FLASHBACK database. SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 3792832 SQL> select scn_to_timestamp(3792832) as timestamp from dual; TIMESTAMP --------------------------------------------------------------------------- 16-DEC-22 01.12.42.000000000 PM Step 6=> Insert few more Records in Sample TABLE as below. [oracle@racsetn1 ~]$ sqlplus ABHI_TEST/Oracle_4U Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> insert into time_Data values (sysdate); 1 row created. SQL> insert into time_Data values (sysdate); 1 row created. SQL> insert into time_Data values (sysdate); 1 row created. SQL> / 1 row created. SQL> SQL> COMMIT; Commit complete. SQL> SELECT * FROM time_Data; TIME_DATE --------------------------------------------------------------------------- 16-DEC-22 01.09.03.000000 PM 16-DEC-22 01.10.18.000000 PM 16-DEC-22 01.10.21.000000 PM 16-DEC-22 01.11.10.000000 PM 16-DEC-22 01.13.13.000000 PM 16-DEC-22 01.13.15.000000 PM 16-DEC-22 01.13.16.000000 PM 16-DEC-22 01.13.17.000000 PM 8 rows selected. FALSHBACK STEPS Step7=> Now we will FLASHBACK database post stoping DB and starting in MOUNT mode as below. [oracle@racsetn2 ~]$ srvctl stop database -d TNT [oracle@racsetn2 ~]$ srvctl start database -d TNT -o "MOUNT" [oracle@racsetn2 ~]$ sqlplus "/as sysdba" Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> flashback database to scn 3792832; Flashback complete. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Step8=>Again we will stop database and start on any one node and open with RESETLOGS as below, [oracle@racsetn2 ~]$ srvctl stop database -d TNT [oracle@racsetn2 ~]$ srvctl start instance -i TNT2 -d TNT -o "MOUNT" [oracle@racsetn2 ~]$ sqlplus "/as sysdba" Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> alter database open resetlogs; Database altered. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Step9=>We stop and start DB on both nodes as below, [oracle@racsetn2 ~]$ srvctl stop database -d TNT [oracle@racsetn2 ~]$ srvctl start database -d TNT Step10=> Post that we will verify data as below. [oracle@racsetn1 trace]$ sqlplus ABHI_TEST/Oracle_4U Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select * from time_Data; TIME_DATE --------------------------------------------------------------------------- 16-DEC-22 01.09.03.000000 PM 16-DEC-22 01.10.18.000000 PM 16-DEC-22 01.10.21.000000 PM 16-DEC-22 01.11.10.000000 PM SQL> Hence OUR FLASHBACK DATABASE method works as explained. NOTE If you find that you used the wrong restore point, time, or SCN for the flashback, then mount the database and perform one of the following mutually exclusive options => If your chosen target time was not far enough in the past, then use another FLASHBACK DATABASE command to rewind the database further back in time: FLASHBACK DATABASE TO SCN 42963; #earlier than current SCN => If you chose a target SCN that is too far in the past, then use RECOVER DATABASE UNTIL to wind the database forward in time to the desired SCN: RECOVER DATABASE UNTIL SCN 56963; #later than current SCN => If you want to completely undo the effect of the FLASHBACK DATABASE command, then you can perform complete recovery of the database by using the RECOVER DATABASE command without an UNTIL clause or SET UNTIL command: RECOVER DATABASE; The RECOVER DATABASE command reapplies all changes to the database, returning it to the most recent SCN.
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444