Oracle Flashback (Flashback table or drop)

In Oracle 10g, the Flash back family is divided into the following members:
Flashback Database
Flashback Drop
Flashback Table
Flashback Query (sub Flashback query,flashback Version query,flashback Transaction query)
Here's a look at Flashback Drop and flashback Table

One flashback setting

1. Open Flashback:

Close the database

Boot to mount mode sql>startup mount;

If the archive is not open, open the archive [because flashback relies on media recovery, you must enable archiving before opening the flashback:

Sql>alter database Archivelog;

Open Flash Back

Sql> Show Parameter Db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_recovery_file_dest string
Db_recovery_file_dest_size Big Integer 0
Sql> alter system set db_recovery_file_dest_size= ' 500M ';

System altered.

Sql> Show Parameter Db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_recovery_file_dest string
Db_recovery_file_dest_size Big Integer 500M

Sql> alter system set db_recovery_file_dest= '/opt/flashbin ';

System altered.

Sql> ALTER DATABASE flashback on;

Database altered.

2. Close Flashback:

Close database: Sql>shutdown Immediate

Boot to mount mode sql>startup mount;

Turn off flash back sql> ALTER DATABASE flashback off;

3. Other Settings

See if Flashback is on: select name,flashback_on from V$database;

See if the Flashback recovery area and size are set: Show parameter db_recovery;

Set the size of the flashback recovery area first: Alter system set db_recovery_file_dest_size= ' 2G ';

Set the Flashback recovery Zone path: Alter system set db_recovery_file_dest= ' E:\oracle\product\10.2.0\db_recovery_file_dest ';

Set the time for database fallback by default of 1440 minutes for one day: Alter system set db_flashback_retention_target = 1440;

The main points to note about Oracle Flashback opening and closing are the following:

1. Open the premise that the database archive must be opened

2. Flash back on/off and archive on/off, all in Mount mode

3. If you want to close the archive on the database that has the flashback turned off, you must first turn off the archive-dependent flashback feature

                         

two Flashback DROP

Flashback Drop is the beginning of Oracle 10g, used to restore the user mistakenly deleted objects (including tables, indexes, etc.), this technology relies on the tablespace Recycle Bin (table space Recycle Bin), this feature and Windows Recycle Bin very similar.
This function is not related to the database flashback, it has no relation to the flashback parameter, and the table does not require row movement.
Tested sql> ALTER DATABASE flashback off to flash back the dropped table.
Flashback does not support SYS users. Objects under the system table space cannot be retrieved from the Recycle Bin. Therefore, when using SYS or system user login, show RecycleBin is empty.
1. Tablespace Recycle Bin
Starting with Oracle 10g, each tablespace will have a logical region called the Recycle Bin, and when the user executes the drop command, the associated objects (including indexes, constraints, triggers, LOB segments, LOB index segments) of the deleted table and table are not physically deleted, and the objects are first transferred to the Recycle Bin. This gives the user a chance to recover.
The initialization parameter recyclebin is used to control whether the RecycleBin feature is enabled, which is on by default and can be off by using off.
Sql> Show Parameter Recycle
RecycleBin string on

Disable this feature:
Sql> alter system set Recyclebin=off;
Sql> alter system set Recyclebin=on;
Sql> alter session set Recyclebin=off;
Sql> alter session set Recyclebin=on;
After disabling the deleted object will be deleted directly, will not write to recycle, of course, when the deletion, specify the purge parameter, the table will also be deleted directly, will not be written to RecycleBin.

The Recycle Bin area of a tablespace is just a logical region, not a block of physical drawing from a table space for the Recycle Bin, so Recycle Bin is a storage area that is common to common objects, or Recycle Bin objects to rob storage space with ordinary objects. When there is not enough space, Oracle overwrites the objects in the Recycle Bin in first-in, first-out order. You can also manually delete the space occupied by the Recycle Bin.

1). Purge tablespace tablespace_name: Recycle Bin for empty table space
2). Purge tablespace tablespace_name User user_name: Empties the specified user's object in the Recycle Bin of the specified table space
3). Purge RecycleBin: Delete objects in Recycle Bin of the current user
4). Purge Dba_recyclebin: Delete all users Recycle Bin objects, the command to SYSDBA permissions
5). DROP TABLE table_name Purge: Deletes the object and does not place it in Recycle Bin, permanent deletion, and cannot be restored with flashback.
6). Purge Index Recycle_bin_object_name: When you want to release the space in the Recycle Bin and want to be able to restore the table, you can relieve the space pressure by releasing the space occupied by the object's index. Because the index can be rebuilt.

2. Flashback Drop instance Operation

The following is done when the system parameter Recyclebin=on:
1,sql> drop table test1;
Table dropped.

2, view RecycleBin's information
sql> Col origninal_name format A15;
sql> col type format A15;
Sql> select Object_name,original_name,type from User_recyclebin;

object_name original_name TYPE
------------------------------ -------------------------------- -------
Bin$fr5g/49+sz2oesrtx4uchg==$0 Idx_testid INDEX
Bin$x1ey4htfseilywuq7kkm+w==$0 TEST1 TABLE
Sql> show RecycleBin; --note that this is Sqlplus's order.
ORIGINAL name RecycleBin name OBJECT TYPE DROP Time
---------------- ------------------------------ ------------ -------------------
TEST1 bin$x1ey4htfseilywuq7kkm+w==$0 TABLE 2012-01-13:16:35:24

3. Flash Back the deleted table
Sql> Flashback table Test1 to before drop;
Flashback complete.
Sql> select * from Test1;
ID NAME
---------- ----------
3763392 A

If such a situation occurs, after the table test1 is deleted, an object with the same name test1 (table or procedure ...) is created and needs to be renamed when Flash back
Flashback table Test1 to before drop rename to Testx
In another case, a table of the same name was repeatedly drop into the RecycleBin, which was followed by the principle of LIFO.
Once the flashback recovery is complete, the objects in the Recycle bin disappear.
Sql> select Object_name,original_name,type from User_recyclebin;
No rows selected

Flashbacktable drop is not supported after RecycleBin is set to OFF.
Sql> alter system set Recyclebin=off;
System altered.
sql> drop table test1;
Table dropped.
Sql> select Object_name,original_name,type from User_recyclebin;
No rows selected

Flashback Drop needs to be noted:
1). Table spaces that can be used only for non-system tablespaces and local administration
2). The reference constraint for the object is not restored, and the foreign key constraint that points to the object needs to be rebuilt.
3). Whether the object can be recovered successfully depends on the reuse of the object space as overridden.
4). When you delete a table, materialized views that rely on the table are also deleted, but because materialized views are not put into the Recycle Bin, you cannot restore materialized views that depend on them when you execute Flashback table to before drop, requiring a DBA Manually involved re-creation.
5). For objects in the Recycle Bin, only queries are supported.

 

three Flashback Table

Note The SYS user does not support flashback, as explained earlier.
Flashback table also uses undo tablespace content to achieve fallback to data. The command is relatively simple, input: Flashback table table_name to SCN (to timestamp).
Note: If you want to flashback the table, you must allow the table's row movement.
Alter table table_name row movement;
To see if a table is enabled for row movement, you can query (or all_tables,dba_tables) in User_tables.
For example:
Sql> Select Row_movement from user_tables where table_name= ' TEST1 ';
Row_move
--------
DISABLED
This is not supported for row movement.

Sql> select Current_scn from V$database;
Current_scn
-----------
3769840
Sql> Delete from test1;
1 row deleted.
Sql> commit;
Commit complete.
Sql> Flashback table Test1 to SCN 3769840;
Flashback table Test1 to SCN 3769840
*
ERROR at line 1:
Ora-08189:cannot Flashback the table because row movement is not enabled
The above error occurs because the row movement is not supported.
Sql> ALTER TABLE test1 enable row movement;
Table altered.
Sql> Flashback table Test1 to SCN 3769840;
Flashback complete.
Sql> select * from Test1;
Id
----------
1
The Flashback Table command supports simultaneous manipulation of multiple tables, separating the table names by commas, and remembering that a single Flashback table is in the same transaction if you specify more than one table when you execute a Flashback table command. Therefore, the recovery operations of these tables either succeed or fail.
such as: Flashback Table A, B, C to SCN 1103864;

Undo-based table recovery requires attention to the effects of DDL operations
For example, TRUNCATE TABLE cannot be flashback table, error occurs: ora-01466:unable to read data-table definition has changed.
Other operations include: drop/modify column, move table, drop partition (if any), truncate table/partition, these actions invalidate revocation data in the other table space, and apply flashback to tables that have performed these operations Query triggers a ORA-01466 error. While some table structure modification statements do not affect Undo records in the Undo tablespace, it is possible that the redo records in undo cannot be applied due to table structure modifications, such as the addition of constraints, and the undo record queried by flashback query does not conform to the new constraint. This time the direct recovery is obviously not successful, you either temporarily disable the constraint, or through appropriate logic, the data to be recovered after processing, and then perform the recovery.
In addition, flashback query has no effect on dynamic performance views such as V$tables,x$tables, but is valid for data dictionaries such as dba_*,all_*,user_*. This feature also fully supports access to remote databases such as SELECT * from [email protected] as of SCN 360;
2. With undo-based table recovery, Flashback table actually does DML operations (with DML locks on the table being manipulated), so you also need to be aware of the impact triggers has on it, by default, Flashback table to scn/ Timestamp will automatically disable out of the triggers of its operating table when executed, and if you want trigger to continue to do so during this period, you can append it to the Flashback table
The ENABLE TRIGGERS clause.