Introduction
In my
previous article we have reviewed the first Oracle Flashback feature which was introduced in Oracle 9i, named "Flashback Query" and we saw how this feature works "behind the scenes" using undo tablespace' contents.
In this article, we will review Oracle 10g Flashback features.
Oracle 10g Flashback Features
Oracle Database version 10g introduced some great flashback-related enhancements and new features. We can categorize these features into two main categories, Flashback query enhancements and additional flashback features.
Oracle Flashback Query Enhancements
This category contains all of the Oracle 10g flashback query enhancements including the following: Flashback Version Query, Flashback Transaction Query, and Flashback Table. The reason these features are categorized as enhancements to the 9i Flashback query feature is because they all rely on the undo records in the Undo Tablespace, where the “Additional Flashback Features” are flashback capabilities that do not rely on the undo tablespace, but rather on other Oracle Database components and features.
Flashback Version Query
The Flashback Version Query allows viewing the historical versions of a specific row or set of rows. Let us continue with the previous example of table EMP. In the previous example, there was an update of employee with ID #1 to be named ROBERT instead of DAVID, and then using by the flashback query, it was updated to be DAVID (as it was originally).
By using Flashback Version Query, we can see the history of the row modifications:
SQL> select versions_starttime, versions_endtime,
versions_xid, versions_operation, name
from EMP
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2016-01-10 18:02:28', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2016-01-10 18:03:00', 'YYYY-MM-DD HH24:MI:SS')
where id = 1
order by VERSIONS_STARTTIME;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V NAME
---------------------- ---------------------- ---------------- - --------------------
10-JAN-16 06.02.47 PM 10-JAN-16 06.04.08 PM 060016000A2A0100 U ROBERT
10-JAN-16 06.04.08 PM 01000200EBFA0000 U DAVID
The VERSIONS_XID column represents the ID of the transaction that is associated with the row. The transaction ID is useful for retrieving the undo SQL statement for the transaction using the Flashback Transaction Query (more details to follow in the next section). The VERSIONS_OPERATION column value is ‘U’ which indicates that an update has occurred. Other possible values are ‘I’ (which indicates an INSERT) and ‘D’ (which indicates a DELETE).
The “VERSIONS BETWEEN” clause allows the DBA to specify SCN MINVALUE AND MAXVALUE, which takes all of the undo information that is available in the undo tablespace as follows:
SQL> select versions_starttime, versions_endtime,
versions_xid, versions_operation, name
from EMP
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE id = 1
order by VERSIONS_STARTTIME;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V NAME
---------------------- ---------------------- ---------------- - --------------------
10-JAN-16 06.02.47 PM 10-JAN-16 06.04.08 PM 060016000A2A0100 U ROBERT
10-JAN-16 06.04.08 PM 01000200EBFA0000 U DAVID
Let us see an example of the output of the query after an insertion and deletion of a row.
SQL> insert into EMP values (2, 'STEVE');
1 row created.
SQL> commit;
Commit complete.
SQL> delete EMP where id=2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select versions_starttime, versions_endtime,
versions_xid, versions_operation, name
from EMP
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
order by VERSIONS_STARTTIME;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V NAME
---------------------- ---------------------- ---------------- - --------------------
10-JAN-16 06.02.47 PM 10-JAN-16 06.04.08 PM 060016000A2A0100 U ROBERT
10-JAN-16 06.04.08 PM 01000200EBFA0000 U DAVID
10-JAN-16 06.27.55 PM 10-JAN-16 06.28.01 PM 08000E007A330100 I STEVE
10-JAN-16 06.28.01 PM 0A0000009C120100 D STEVE
Flashback Transaction Query
The flashback transaction query feature allows the DBA to view the transaction information including the start and the end of the transaction as well as the undo SQL statements for rolling back the transaction. In order to use this feature Oracle introduced a new dictionary view in version 10g named FLASHBACK_TRANSACTION_QUERY which requires having the SELECT ANY TRANSACTION system privilege. In the example above, we found out that transaction ID 0A0000009C120100 has deleted the row of employee named “STEVE” by using the flashback version query. The flashback transaction query can assist in rolling back the transaction by using the UNDO_SQL column, as follows:
SQL> select xid, start_scn, commit_scn,
operation OP, undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('0A0000009C120100');
XID START_SCN COMMIT_SCN UNDO_SQL
----------------- ---------- ---------- -----------------------------------------------------------
090017001B380100 483051290 483051291 insert into "PINI"."EMP"("ID","NAME") values ('2','STEVE');
Note that in order to have the UNDO_SQL column populated with data, a minimal database supplemental logging must be enabled, which will add additional information to the redo logs. Verify whether minimal database supplemental logging is enabled or not by querying SUPPLEMENTAL_LOG_DATA_MIN from V$DATABASE. If minimal database supplemental logging is disabled (the output of the query is “NO”), you can enable it by executing the following command:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Flashback Table
The flashback table feature allows restoring the entire table’s data into an historical point in time in a very simple and straight-forward way- by specifying either SCN or TIMESTAMP. This feature is the best way for the DBA to recover the entire table’s data from human errors or undesired application changes (e.g. inserts, updates, deletes). In order to use this feature, make sure to be aligned with following prerequisites:
- Have either FLASHBACK object privilege on the table or FLASHBACK ANY TABLE system privilege. In addition, have the following privileges on the table: ALTER, SELECT, INSERT, UPDATE, and DELETE.
- Enable row movement for the table using ALTER TABLE … ENABLE ROW MOVEMENT. The reason for this requirement is because rows will be moved (inserted, updated, and deleted) during the FLASHBACK TABLE, which is the reason that the user must be granted with the INSERT, UPDATE, DELETE privileges on the table.
The following is a demonstration of this feature:
SQL> CREATE TABLE EMP (ID NUMBER, NAME VARCHAR2(20));
Table created.
SQL> insert into EMP values (1, 'DAVID');
1 row created.
SQL> insert into EMP values (2, 'ROBERT');
1 row created.
SQL> insert into EMP values (3, 'DANIEL');
1 row created
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
483077247
SQL> delete EMP;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from EMP;
no rows selected
SQL> flashback table EMP to scn 483077247;
flashback table emp to scn 483077247
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
The ORA-08189 is expected because as previously mentioned, one of the prerequisites for using this feature is to enable row movement, and then it would be possible to execute the flashback table command, as follows:
SQL> alter table EMP enable row movement;
Table altered.
SQL> flashback table EMP to scn 483077247;
Flashback complete.
SQL> select * from EMP;
ID NAME
---------- --------------------
1 DAVID
2 ROBERT
3 DANIEL
Note that this feature is using the information in the undo tablespace in order to recover the table so it can only be used to recover the data and not the structure of the table. If there was a change in the structure of the table, for example, by adding a column, Oracle would not be able to recover the table prior to the execution of DDL command. Also, if a table has been dropped, Oracle would not be able to recover it using this feature.
Additional Flashback Features
So far we have reviewed the Flashback features that rely on the contents of the undo tablespace. In this section we will explore the other 10g Flashback features: Flashback Drop and Flashback Database. These features do not rely on the contents of the undo tablespace.
Flashback Drop
Starting with Oracle version 10g, Oracle introduced a new parameter named “RECYCLEBIN” (defaults to “ON”):
SQL> show parameter RECYCLEBIN
NAME TYPE VALUE
------------------------------------ ----------- --------
recyclebin string on
Assuming that RECYCLEBIN parameter is set to ON, then once the object is dropped, it will remain in the tablespace and Oracle will keep the information about the dropped table and its associated objects in a dictionary view named USER_RECYCLEBIN (it has a synonym named RECYCLEBIN), which shows per each schema its objects in the recycle bin, as follows:
SQL> SELECT object_name, original_name, droptime FROM RECYCLEBIN;
OBJECT_NAME ORIGINAL_NANE TYPE DROPTIME
------------------------------ ------------- ----- --------------
BIN$ZW5M6bSsRKe6PiqynWR9Xw==$0 EMP TABLE 2016-01-21:17:24:26
BIN$tWgtlRlzTZ2lCoZd0Ex7Rg==$0 ID_PK INDEX 2016-01-21:17:24:25
Note: that it is possible to query the recycle bin of the entire instance using DBA_RECYCLEBIN, and CDB_RECYCLEBIN in version 12c to query the recycle bin of all the schemas across all the containers.
As seen in the demonstration above, the names of the table and its associated objects in the RECYCLEBIN have a system-generated name (starts with BIN$).
It is possible to query directly the recycle bin system-generated names:
SQL> SELECT object_name, original_name, droptime FROM RECYCLEBIN;
SQL> select * from "BIN$ZW5M6bSsRKe6PiqynWR9Xw==$0";
ID NAME
---------- --------------------
1 DAVID
2 ROBERT
However, it is not possible to execute DML or DDL commands against the tables in the recycle bin.
Once the table is being restored from the recycle bin, it will be restored with its original name, but the associated objects will be restored with system-generated names so it is possible to rename these objects later as an optional step.
The following is an example that demonstrates how simple it is to restore a dropped table using this feature:
SQL> SELECT object_name, original_name, droptime FROM RECYCLEBIN;
SQL> FLASHBACK TABLE EMP TO BEFORE DROP;
Flashback complete.
SQL> select * from EMP;
ID NAME
---------- --------------------
1 DAVID
2 ROBERT
It is also possible that the object will be restored with a different name (for example, when another object with the same name already exists), using a very simple syntax, as follows:
SQL> SELECT object_name, original_name, droptime FROM RECYCLEBIN;
SQL> FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_OLD;
Flashback complete.
Note that it is not guaranteed to have the dropped objects in the recycle bin. In the following scenarios the objects will not be available in the recycle bin:
- Execution of a DROP TABLE command with the PURGE clause
- Manual execution of PURGE RECYCLEBIN or PURGE DBA_RECYCLEBIN commands
- Drop of the entire tablespace will not leave its objects in the recycle bin
- When dropping a user, all its objects are not placed in the recycle bin
- Space-pressure in the tablespace on which the objects reside
Another thing to keep in mind is that Oracle restores the objects from the recycle bin in a LIFO (Last In First Out) order, so if there are several objects with the same name in the recycle bin and the DBA restores that object using the Flashback Drop feature, then the last one that was dropped will be restored.
Flashback Database
The Flashback Database feature is the most simple and straightforward way to rewind the entire database to a historical point in time. The reason it is so fast and simple is because it does not require restoring database backups using either RMAN or user-managed backup. However, its biggest disadvantage is that it can only recover “logical failures”, i.e. unnecessary data modifications due to human error. It cannot be used to recover from block corruptions or a loss of a file (e.g. Data Files, Control File).
In order to undo changes in the database, this feature uses flashback logs. Flashback logs are being generated once the Flashback Database is enabled. The flashback logs contain before-images of data blocks prior to their change. The Flashback Database operates at a physical level and revert the current data files to their contents at a past time using the flashback logs. The prerequisites for enabling this feature are:
- The database must be running in ARCHIVELOG mode
- Enable the FRA (Flash Recovery Area).
The FRA is a storage location that contains recovery-related files such as archived logs, RMAN backups, and of course, flashback logs. Once configured, the FRA will simplify the DBA’s daily tasks by retaining the recovery-related files as long as they are needed, and delete them once they are no longer needed (based on the retention policies that are defined by the DBA).
Once the FRA prerequisites have been configured properly, it is possible to enable the Flashback Database feature by executing the following command:
SQL> ALTER DATABASE FLASHBACK ON;
Prior to 11gR2, in order to execute this command, the database had to be restarted to a mounted state and only then it was possible to execute the above command. Starting with 11gR2, it is possible to enable the Flashback Database with no downtime by executing this command when the instance is in OPEN status.
It is possible to set the DB_FLASHBACK_RETENTION_TARGET parameter which specifies the upper limit (in minutes) on how far back in time the database may be flashed back. Its default value is 1440 minutes (=one day) of retention for the flashback logs. The reason that it is only an upper limit and not a guaranteed retention period is because if the FRA is full (reached the maximum FRA size limit defined by the DB_RECOVERY_FILE_DEST_SIZE parameter) or if there is not enough disk space, then Oracle will reuse the oldest flashback logs which might be within the retention period. It is possible to monitor the oldest possible flashback time via the V$FLASHBACK_DATABASE_LOG dictionary view. In the following demonstration, the FRA is set with a 100GB size limit and flashback retention target of 1 week (=10080 minutes):
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100g;
System altered.
SQL> ALTER SYSTEM SET db_recovery_file_dest='/oravl01/oracle/FRA';
System altered.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=10080;
System altered.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
In order to rewind the database, restart the database in a MOUNT mode, and then execute the FLASHBACK DATABASE command. It is possible to rewind the database to an exact SCN or Time. It is also possible to rewind the database prior to the SCN or Time. Another simple way is to create a restore point in which a name represents a specific point in time, and then the flashback will restore the database to the time that the restore point had been created.
In the following example, a sample table is created, populated with a few records and truncated right after a restore point name “before_truncate” has been created. In this demo, a Flashback Database is used to rewind the enitre database prior to the truncate command that was executed using the “before_truncate” restore point.
SQL> create table test_flashback (id number, name varchar2(20));
Table created.
SQL> insert into test_flashback values (1, 'DAVID');
1 row created.
SQL> insert into test_flashback values (2, 'JOHN');
1 row created.
SQL> commit;
Commit complete.
SQL> create restore point before_truncate;
Restore point created.
SQL> truncate table test_flashback;
Table truncated.
SQL> select name,scn,time from V$RESTORE_POINT;
NAME SCN TIME
--------------- ---------- -------------------------------
BEFORE_TRUNCATE 119193304 24-JAN-16 09.14.06.000000000 PM
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 809500672 bytes
Fixed Size 2929600 bytes
Variable Size 318770240 bytes
Database Buffers 482344960 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT before_truncate;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from test_flashback;
ID NAME
---------- ---------------
1 DAVID
2 JOHN
Summary
In this article, have reviewed Oracle 10g Flashback features. We started with the Flashback Query enhancements (Flashback Version Query, Flashback Transaction Query, Flashback Table) and continued with additional flashback enhancements (Flashback Drop and Flashback Database).
In the next part, we will review Oracle 11g Flashback features.
Stay tuned :)
