Oracle OCP Notes (31) using the Flashback feature

Oracle OCP Notes (31) using the Flashback feature

Oracle's flashback feature is supported by one of 3 database architectures: Undo data, Flashback recovery area, and Recycle Bin.
Undoing data in the undo Tablespace not only supports transaction rollback, but also most of the flashback table operations. Flashback Data Archives allows querying a previous version of a table row, which provides a zone in one or more table spaces outside of the undo tablespace, and supports a longer retention period than the undo table space. The flashback log is saved in the Flashback recovery area, eliminating the need to perform traditional restore and restore operations to easily roll back the entire database to the past point in time. The Recycle Bin in each tablespace contains one or more versions of the deleted tables and indexes, which users can easily restore if there is ample space in the table space.


1. Restore a deleted table from the Recycle Bin
(1). Enable Recycle Bin Feature
Alter session set Recyclebin=on; --Enable/disable Recycle Bin (Session level) on/off
alter system set Recyclebin=on; --Enable/disable Recycle Bin (System level) on/off

After you enable the Recycle Bin feature, the object names in the Recycle Bin are changed to the system-specified name when the objects are deleted and displayed in the Recycle Bin view:
Bin$unique_id$version
UNIQUE_ID: Globally unique name
Version: Revision number


When an object is deleted, the free space allocated by the object is displayed in view Dba_free_space, but the object still occupies the user space quota.


(2). Viewing the objects of the Recycle Bin
SELECT * from Dba_recyclebin;
SELECT * from User_recyclebin;
SELECT * from RecycleBin; --Synonyms for view User_recyclebin
Show RecycleBin;


(3). Restore a table using the Recycle Bin
Flashback table "Bin$u/===$0" to before drop;


If you attempt to restore a table that has been recreated since it was deleted, you need to use the Rename to clause to provide a new name for the restored table, or an error will occur.
Flashback table TableName to before drop rename to Oldtablename;

When you use the Rename option to flash back to a table, the table uses the original name, but the subordinate objects of the table are not, and if you need to retain the original name for the indexes, triggers, and constraints, query the Recycle Bin to flash back and rename the other objects after the restore.


(4). Emptying the Recycle Bin
Purge RecycleBin; --Empties the current user's Recycle Bin object
Purge Dba_recyclebin; --Clears all user's Recycle Bin objects (SYSDBA permissions)
Purge tablespace users; --Empty the table space for the Recycle Bin object
Purge tablespace tbname user username; --Empty the Recycle Bin object for a specific user tablespace (drop any table system permissions)


(5). Bypass Recycle Bin when deleting objects
drop table ... purge;
Drop tablespace ... including contents;
Drop user ... cascade;


2. Performing a flashback query
(1). Parameters for the Flashback query
Using the Flashback query feature, you need to correctly configure the undo Tablespace parameters:
Undo_management=auto
Undo_tablespace=undotbs1
undo_retention=1800


When the size of the undo table space is limited, ensuring that the flashback operation or long-running queries succeed, you must specify retention guarantee for the undo tablespace after creating the tablespace.
Alter TABLESPACE UNDOTBS1 retention guarantee;


(2). Using a flashback query
SELECT * FROM tablename as of timestamp to_timestamp (' 2014/11/19 19:22:30 ', ' yyyy/mm/dd HH24:MI:SS ');
SELECT * FROM employees as of timestamp systimestamp-interval ' minute; --60 Minutes ago
SELECT * FROM TableName as of timestamp (systimestamp-1/1440); --1 minutes ago


(3). Use Flash back version query
Select Dbms_flashback.get_system_change_number from dual; --View SCN number
SELECT * FROM tablename versions between SCN 2710111 and 2710113 where ...;


Select A.*, VERSIONS_STARTSCN, Versions_starttime,
VERSIONS_ENDSCN, Versions_endtime,
Versions_xid, Versions_operation
From MMS.USR_MSTR versions between SCN 2847161 and 2847403 a


Flash back version query pseudo-column:
· versions_start{scn| TIME}--SCN or timestamp
· versions_end{scn| TIME}--SCN or timestamp
· VERSIONS_XID--Transaction ID
· Versions_operation--actions performed on the line (i= Insert, d= Delete, u= update)


With the limitations of the Flashback version query, you cannot use the VERSIONS clause to query the following objects:
• External Tables
• Fixed table (internal Oracle x$ table)
• Temporary tables
• view


3. Using Flashback transactions
(1). Prerequisites for using Flash back transactions
The database must be in archive mode.
The database must have supplemental logs enabled.


Supplemental LOG: Additional logging for redo log streams must be enabled before using Flashback transaction queries. The redo log stream is the same as the data used by Logminer, except that the interface is different. It is clear that redo log stream data is a supplement to the information that is logged in the table space (supplemental log). Flashback transaction queries require both enhanced redo information and revocation of information.
V$database column-related parameters
Supplemental_log_data_min
Supplemental_log_data_pk
supplemental_log_data_ui
Supplemental_log_data_fk
Supplemental_log_data_all


Select Supplemental_log_data_min, SUPPLEMENTAL_LOG_DATA_PK, supplemental_log_data_ui, SUPPLEMENTAL_LOG_DATA_FK, Supplemental_log_data_all from V$database;


--Enable logging of column values and primary key values that are referenced by DML changes
ALTER DATABASE add supplemental log data;
ALTER DATABASE Add supplemental log data (primary key) columns;


--Grant the appropriate permissions to the user who will use the Flashback transaction query
Grant execute on dbms_flashback to HR;
Grant Select any transaction to HR;


(2). Flashback Transaction Query
Unlike the Flashback version query, the Flashback transaction query does not refer to the table involved in the DML transaction, instead it queries the data dictionary view flashback_transaction_query.
Columns for view Flashback_transaction_query
· XID--Transaction ID number
· START_SCN-The SCN of the first DML in a transaction
· Start_timestamp-Timestamp of the first DML in a transaction
· COMMIT_SCN--SCN when committing a transaction
· Commit_timestamp-Timestamp when the transaction was committed
· Logon_User-A user with a transaction
· undo_change#--Undoing the SCN
· Operation-DML operations performed: DELETE, INSERT, UPDATE, begin, or unknown
· TABLE_NAME--DML changed table
· Table_owner--DML changed the owner of the table
· ROW_ID-rowID of rows modified by DML
· Undo_sql--SQL statement to revoke DML operations


The undo space is limited, so the flashback_transaction_query contains only a subset of the transactions. In this case, the value of the Dml,operation column for any selected transaction that is no longer displayed in the Undo Tablespace is unknown.


Select XID, START_SCN, COMMIT_SCN, Logon_User, operation, TABLE_OWNER, TABLE_NAME, undo_sql
From Flashback_transaction_query
WHERE table_name = tablename;


Select XID, START_SCN, COMMIT_SCN, Logon_User, operation, TABLE_OWNER, TABLE_NAME, undo_sql
From Flashback_transaction_query
where XID = Hextoraw (' 030002001f060000 ');


The undo_sql is the resulting fallback statement.


(3). Revoking a transaction
Use Process dbms_flashback.transaction_backout.
Parameters of the procedure:
· Numtxns: The number of transacted transactions that were canceled.
· Names: The list of canceled transactions (sorted by name).
· Timehint: If the transaction is identified by name, you can provide a time prompt, such as at some time before the transaction begins.
· Options: Specifies the order in which a transaction is canceled and its dependent transactions are processed.
Options for rollback:
· CASCADE--rollback of all transactions and their subordinate transactions
· Nocascade--rollback only the specified transaction
· Nocascade_force--rollback of the specified transaction, ignoring dependent transactions
· Nonconflict_only--rollback of changes to non-conflicting rows of the specified transaction only


Dbms_flashback.transaction_backout (Numtxns=>1,xids=>sys.xid_array (' 050004009d0a0000 '), Options=>dbms_ Flashback.cascade);
Commit --The undo transaction requires an explicit commit.


4. Performing a flash-back table operation
(1). Flash Back Table
Oracle's flashback feature restores the state of the table row to a previous point in time. It also restores the indexes, triggers, and constraints of the table, while the database is still online. This improves the overall availability of the database.
You can restore a table by timestamp or SCN. If the user error range is small and limited to one or few tables, the flashback table is superior to the other flashback methods.
In order to restore the state of a larger number of tables, it may be better to flash back to the database. The flashback table is not available for stand-alone databases, and it is not possible to reconstruct all DDL operations, such as adding and removing columns.
Because the flashback database uses the Undo tablespace, the recovery window is relatively small compared to other recovery methods, such as the Rman-based recovery or flashback database.
The flashback table executes in-place, and the changes that are made to the table and all subordinate objects (such as indexes) are rolled back when the database is still online. If the table has other subordinate objects, you can specify multiple tables in the Flashback Table command. This operation is treated as a single transaction regardless of whether a table or multiple table is specified in the Flashback table operation. All changes are either successful or rolled back.


(2). Configuring the Flash Back Table feature
Grant flashback on TableName to username; --Grant the flashback permissions of the table to the user
Grant flashback any table to username; --or grant flashback any table permission to the user
ALTER TABLE tablename enable row movement; --Open Row movement


(3). Using the Flashback table
Delete from TableName;
Flashback table Tablename1,tablename2 to timestamp to_timestamp (' 2014-12-08 15:30:01 ', ' yyyy-mm-dd hh24:mi:ss ');
Flashback table TableName to SCN 1763103;
Flashback table TableName to timestamp systimestamp-interval ' minute;


5. Setting up and using the Flashback data archive
(1). Flash Back Data archive (Flashback Archive)
Flashback Data Archive creates one or more warehouse zones (one is the default zone), assigns a default retention period for objects in the warehouse, and then adds trace flags for the appropriate tables.
Flashback Data archive behaves like a undo table space, but Flashback data archive records only the update and DELETE statements, not the INSERT statement. Also, for revocation data, the retention period for all objects is typically several hours or days, while the rows in Flashback data archive can span years, and Flashback data archive has a narrower focus, documenting only historical changes to the table rows.
You can access the data in Flashback data Archive as you would with the as of clause in the SELECT statement of the flashback query. Flash back version queries and flashback transaction queries can also use data from Flashback data archive.


Dba_flashback_archive_tables-a table with a flashback file enabled
Dba_flashback_archive--List archive
Dba_flashback_archive_ts--mapping relationship between table space and archive


(2). Configure Archiving
<1>flashback the permissions of the Data archive
Flashback archive Administer--Create or modify permissions for Flashback Data Archive
Flashback Archive--Enable tracking of tables permission

SELECT * from Dba_sys_privs where grantee= ' USERNAME ';
Grant Flashback archive administer to username;
Grant Flashback archive to username;


<2> Create an archive
Create tablespace fbdata01 datafile '/u01/app/oracle/oradata/sales/fbdat01.dbf ' size 500m;
Create tablespace fbdata02 datafile '/u01/app/oracle/oradata/sales/fbdat02.dbf ' size 500m;
Create tablespace fbdata03 datafile ' +data01 ' size 1g;
Create Flashback archive Default Fbarcdef tablespace fbdata01 quota 400m retention 2 year;
Create Flashback archive Fbarcdata tablespace fbdata03 quota 500m retention 7 year;


<3> Maintenance Archive
--Add a tablespace to the archive
Alter FLASHBACK archive FBARCDEF add tablespace fbdata02 quota 400m;
--use purge to clear archived data
Alter FLASHBACK archive Fbarcdef purge before timestamp to_timestamp (' 2014-12-12 00:00:00 ', ' yyyy-mm-dd HH24:MI:SS ');


<4> assign a table to an archive.
ALTER TABLE TableName Flashback archive Fbarcdef;

<5> Cancel the filing of the table.
ALTER TABLE TableName no flashback archive;


(3). Query Flashback Data Archive
You can query the history of a table in flashback data archive by using the AS clause in the table, as you would with DML activities in the Undo table space.
In fact, it is completely transparent to the user whether the query uses the Undo table space or flashback Data Archive.


Delete FROM employees where enployee_id = 169;
SELECT * FROM employees as of timestamp (Systimestamp-interval ');
SELECT * FROM employees as of timestamp (systimestamp-interval ' 2 ' minute);


6. Flash back to the database
Oracle's Flashback database feature uses the Flashback Database command to return the databases to the past time or SCN, providing a quick alternative to performing incomplete database restores.
You can use the flashback database to quickly restore the entire database to a point in time, and when the flashback database is enabled, the pre-image of the modified block is stored in the Flashback recovery area as a flashback database log, and if there is a logical corruption that needs to be restored to a previous point in time, the Flashback database log restores the pre-images of the block. It then rolls forward to the desired flashback time using the archive and online redo logs, which typically perform much faster than performing traditional restore and restore operations, as there is no need to restore the database's data files.
When the flashback database is enabled, the pre-image data is saved in the flashback buffer, and the pre-image information in the flashback buffer is saved in the Flashback database log in the Flashback recovery area using the recovery writer (Recovery WRITE,RVWR) background process. Reusing logs from the Flashback recovery area in a cyclic manner depends on the amount of space in the Flashback recovery area as well as the configured secured restore point, depending on how much time the database is back to daub.
To enable the Flashback database feature, you must set the database to archive mode and must be configured to archive to the quick recovery area.


(1). Configuring the Flashback Database
Db_recovery_file_dest--Flashback Recovery Zone path
Db_recovery_file_dest_size--Flashback recovery Area size
Db_flashback_retention_target-Restore window target limit (minutes)


--You must configure the archive log to the Quick recovery area
Alter system set db_recovery_file_dest= '/u01/app/oracle/fast_recovery_area ';
Alter system set log_archive_dest_3= ' location=use_db_recovery_file_dest mandatory ';
alter system set db_recovery_file_dest_size=40g;


--Enable Flash back database
Shutdown immediate;
startup Mount Exclusive;
ALTER DATABASE Archivelog;
alter system set db_flashback_retention_target=2880;
ALTER DATABASE flashback on;
ALTER DATABASE open;


--Close the flashback database
startup Mount Exclusive;
ALTER DATABASE flashback off;
ALTER DATABASE open;


(2). Executing the Flashback database
Flashback database can be executed using Rman and Sqlplus


--Flashback of Thread (instance) and specific log sequence number
Rman> Flashback database to sequence=307 thread=2;


--use Sqlplus to execute Flashback database command
Flashback [Standby] database [Database]
{to {SCN | timestamp} expr |
To before {SCN | timestamp} expr |
To restore point expr
}


In addition to the guaranteed restore point, you can use the to SCN or to timestamp clause to set the point at which the entire database will flash back. You can flash back to the critical point, such as a transaction that generates unexpected results for multiple tables.
Use the ORA_ROWSCN pseudo-column of a specific table row to view Scn:select ORA_ROWSCN for the most recent change to the row, a.* from TableName A;
Perform a flashback, confirm that the flashback data is available, and then perform a flashback, Flash back to a certain time before, and will lose all the transactions after that.
When you execute the Flashback database command, Oracle performs a check to ensure that all required archive and online redo logs are available. If the log is available, the online data file is restored to the specified time, SCN, or secured restore point.
If there is insufficient online data in the archive log and flashback area, the traditional database recovery method needs to be used to recover the data. For example, you can first use the file system recovery method and then roll forward the data.
After the flashback is complete, you must open the database with the Resetlogs option to obtain write access to the database:


Shutdown immediate;
startup Mount Exclusive;
Flashback database to timestamp sysdate-(1/24);
Flashback database to SCN 1790640; --Scn:select ORA_ROWSCN from TableName; --select Dbms_flashback.get_system_change_number from dual;
ALTER DATABASE open resetlogs; --Open Database reset log

(3). Exclude Table Space Flashback
Alter Tablespace example flashback off; --Table Space Close Flashback Database
Alter Tablespace example flashback on; --Table space enable flashback database


(4). Using a secured restore point
A secured restore point is similar to a normal restore point and can be used as an alias for the SCN during a recovery operation. The difference between a guaranteed restore point is that it does not expire in the control file and must be explicitly deleted. The secured restore point applies to the Flashback database operation. If you create a secured restore point when you enable flashback logging, you will ensure that the flashback log is retained in the Flashback recovery area to ensure that the database can be rolled back to any point in time after the secured restore point was created.
--Create a guaranteed restore point
Create restore point restore_point_name guarantee Flashback database;


--Flash back guarantee restore point
Shutdown immediate;
startup Mount Exclusive;
Flashback database to restore point restore_point_name;
ALTER DATABASE open resetlogs;


--Delete the secured restore point
Drop restore point restore_point_name;


Prerequisites to use a secured restore point
Compatible initialization parameter must be 10.2 or higher
Database must be run in Archivelog mode
Archive redo log files must be available for use from the first guaranteed restore point
The flashback recovery area must be configured
Because any flashback log after the first secured restore point is retained in the Flashback recovery area, the secured restore point can cause space tension in the flashback recovery area.


(5). Monitoring the Flashback database
Select Current_scn, flashback_on from V$database;
SELECT * from V$flashback_database_log;
SELECT * from V$flashback_database_stat;

Oracle OCP Notes (31) using the Flashback feature