Oracle10g flash back

1. flashbackdatabase1: 1) accidental deletion of users 2) truncatetable3) Multi-table data confusion 2. Database flashback condition 1) Number

I. flashback database1, which can be processed: 1) mistaken deletion of users 2) truncate table3) Multi-table data confusion 2. Database flashback condition 1)

1. flashback database
1. Applicable scenarios:
1) delete users by mistake
2) truncate table
3) Multi-table data chaos
2. Database flashback Conditions
1) configure the database to archive
2) configure the flash recovery area (FRA: flash recovery area)
SQL> show parameter db_recovery_file; ------ contains the path and space used
3) configure the flash back retention time
SQL> show parameter db_flashback_retention_target; ------- note: the unit is minutes, for example, 1440 = 24 hours.
SQL & gt; alter system set db_flashback_retention_target = 1440;
4) Start the flash back database
Run the following command when the database is in the mount state:
SQL> alter database flashback on;

SQL> alter database flashback on; ---- Note: Once the database is closed, all FRA logs are automatically deleted.

Example:
SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> flashback database to timestamp to_date ('20140901', 'yyyymmddhh24mis ');
After it is restored to a certain point in time, read only opens the database for confirmation, re-starts the database, and opens the database in the resetlogs mode to ensure that the flash log exists so that it can flash back again.
SQL> alter database open read only;
SQL> select * from... ---- confirm the recovered data
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open resetlogs;
Flash back is restored Based on the flash back log. You can also perform flash back Based on the SCN Number of the flash back log.
SQL> flashback database to scn 4879890;

In addition, you can use RMAN to flash back the database to the mount status.
[Oracle @ book ~] $ Export NLS_LANG = american_america
[Oracle @ book ~] $ Export NLS_DATE_FORMAT = 'yyyymmddhh24miss'
[Oracle @ book ~] $ Rman target/
RAMN> flashback database to time = '000000 ';
Or RMAN> flashback database to time = '000000 ';

Note: You can obtain the relevant flashback information in v $ flashback_database_log.
Estimated_flashback_size: according to the current flash log speed, the length of db_flashback_retention_target time is about the number of logs to be generated.
Flashback_size: the number of bytes occupied by the current flash log
Oldest_flashback_scn: the scn that can be flashed back at most.
Oldest_flashback_time: the time point that can flash back the most.

To prevent flash back logs from being automatically deleted due to space or other reasons, you can create a restore point at a certain time point.
SQL> create restore point wuxzh_001 guarantee flashback database;
Once subsequent processing fails, you can return to the time point before the operation.

Ii. flash back to delete flashback drop
1. The drop table is not completely deleted, but is renamed and stored in the recycle bin. The corresponding triggers and indexes enter the recycle bin together.
You can use dba_recyclebin or user_recyclebin to find the deleted object.
The show recyclebin command also shows the information about the table that the current user has changed to or deleted.
SQL> show recyclebin;
Oracle space usage rules: use the available space occupied by objects not in the recycle bin in the tablespace to release the space occupied by the recycle bin (first release the oldest object ), data File Extension (automatic extension defined ).
Once the recycle bin is released, it cannot be flashed back.
2. Flashback statement:
SQL> flashback table t to before drop;
SQL> flashback table t to before drop rename to t_new; -- flash back and rename
Concurrent re-indexing after Flashback

If a table is created multiple times and drop multiple times. The recycle bin has multiple records. When a record is flashed back, the table is flashed back in the "advanced post-output (FILO)" mode.
You can also perform a flash back Based on the surface displayed in recyclebin.
SQL> flashback table "FSFASnFMMFDmfd = $0" to before drop; --- contains special characters and uses double quotation marks.
If the table name cannot be determined, use the following statement to confirm
SQL> desc "FSFASnFMMFDmfd = $0 ";
SQL> select count (*) from "FSFASnFMMFDmfd = $0 ";

3. Reclaim the space of the deleted object
1) Automatic Recovery
Oracle space usage rules: use the available space occupied by objects not in the recycle bin in the tablespace to release the space occupied by the recycle bin (first release the oldest object ), data File Extension (automatic extension defined ).
Once the recycle bin is released, it cannot be flashed back.
2) manual recycling
SQL> purge table t; -- Recycle deleted table t. If there are multiple tables, only the oldest table deleted is recycled.
SQL> purge index idx_t;
SQL> purge tablespace users; -- clears the space occupied by users tablespace objects in the recycle bin.
SQL> purge user_recyclebin; -- clears the space occupied by all objects belonging to the current user in the recycle bin.
SQL> purge dba_recyclebin; -- clear the space occupied by all objects in the recycle bin.

The following statement cannot flash back
SQL> drop table t purge;
SQL> drop user wuxzh cascade constraints;
SQL> drop tablespace users including contents;

3. flashback table
1. the flashback table uses the value before the data recorded in the undo tablespace is changed.
If the retention time exceeds the value specified by undo_retention, data is overwritten by other transactions and cannot be flashed back.
2. data may be transferred in database storage after the Flash. Before the flash, you must start the transfer feature of the Data row.
SQL> alter table t enable row movement;

3. Flashback
SQL> delete table t;
SQL> commit;
SQL> flashback table to timestamp to_date ('201312', 'yyyymmddhh24mis ');
You can constantly modify the flash back time to make sure that the flash back result meets the requirements.
Note: If you perform a DDL operation on a table, the data cannot be flashed back, and the table in sys cannot be flashed back.

Iv. Flash back version query flashback version query
1. The so-called version is the change of data rows caused by each transaction. Each change is a version.
2. Check the versions table to determine the corresponding transaction.
3. Flashback VERSION Query does not support external tables, temporary tables, X $ tables (base tables of the dynamic performance view), and views.

V. flashback transaction query
1. Flash Back View query refers to a view: flashback_transaction_query
It is also a diagnostic tool used to determine the data changes caused by transactions and provides SQL statements for revoking transactions.
2. Flashback transaction query uses the undo data in the undo tablespace.
3. You can combine the flashback VERSION Query and the flashback transaction query to find the transaction ID through the flashback version query, and then use the flashback transaction query to find the corresponding undo SQL.

6. Flash back query flashback query
SQL> select * from t as of timestamp to_date ('201312', 'yyyymmddhh24miss ');
SQL> select * from t as of timestamp to_timestamp ('000000', 'yyyymmddhh24miss ');
SQL> create table t_bak as select * from t as of timestamp to_date ('20140901', 'yyyymmddhh24miss ');