Oracle Flashback-Flashback database

In Oracle 10 Gb, the Flash back family is divided into the following members:
Flashback Database
Flashback Drop
Flashback Table
Flashback Query (Flashback Query, Flashback Version Query, and Flashback Transaction Query)
The following describes the Flashback Database.
 
1. the Flashback Database cannot solve Media Failure, and the RMAN recovery is still the only option.
2. if you delete a data file or use the Shrink technology to reduce the size of the data file, you cannot use the Flashback Database technology to roll back to the previous state, at this time, you must first use RMAN to back up and restore the files before or after deletion, and then use Flashback Database to execute the remaining Flashback Datbase.
3. If the control file is recovered from the backup or the control file is rebuilt, Flashback Database cannot be used.
4. The oldest SCN that can be recovered using the Flashback Database lock depends on the oldest SCN recorded in the Flashback Log.
 
Flashback Database architecture
 
The entire architecture of Flashback Database includes a process Recover Writer (RVWR) background process, Flashback Database Log and Flash Recovery Area. Once the Flashback Database is enabled for the Database, the RVWR process starts and writes the Flashback Database Log to the Flash Recovery Area, these logs include the "before image" of the data block, which is also the reason why the Flashback Database technology does not completely restore the block.
 
Enable Flashback Database
 
The Flashback Database function of the Database is disabled by default. To enable this function, you need to make the following configuration.
 
1. Configure Flash Recovery Area
To use the Flashback Database, you must use the Flash Recovery Area, because the Flashback Database Log can only be saved here. The two parameters to be configured are as follows: size and location. If the database is RAC, The flash recovery area must be in the shared storage. The database must be in archivelog mode.
 
Enable Flash Recovery Area:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G SCOPE = BOTH;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = 'd: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ 'scope = BOTH;
Disable Flash Recovery Area:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '';
For Flash Recovery Area, Oracle suggests this. The larger the flash recovery area is, the stronger the recovery capability of the flashback database is. Therefore, it is recommended that all data files be stored in the flash Recovery area, incremental backup, and all the archive files that have not yet been backed up, as well as its own flashback logs.
 
When the database is running, oracle automatically writes files to the region. When the remaining space is less than 15%, it will add a warning in alert, prompting you that the space is insufficient. However, this will not affect the normal operation of the database. After all the space is used up, oracle first tries to delete some expired files, redundant files, or backed up files, if there is still no free space, the database will be hang.
 
For the processing of database hang caused by Flash Recovery Area, see: http://blog.csdn.net/tianlesoftware/archive/2009/10/14/4668991.aspx
Or http://user.qzone.qq.com/251097186/blog/1244650673
 
2. Enable the Flashback function of the database.
1). The database is started to the mount status.
SQL> startup mount;
2) Check the Flashback function. The time-saving function is disabled.
SQL> select name, current_scn, flashback_on from v $ database;
NAME CURRENT_SCN FLASHBACK_ON
--------------------------------------
ORCL 3757148 NO
 
3). Start the Flashback function. The show parameter flashback is invisible.
You must change the mount exclusive status. Otherwise, the following error occurs.
SQL> alter database flashback on;
Alter database flashback on
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.
 
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> alter database flashback on;
Database altered.
-- If off is not enabled, you can change it to off in the open state. After the change, the flashback log file under flash_recovery_area \ will be automatically deleted by Oracle.
At this time, an ORCL \ FLASHBACK directory will be generated under the starting Directory D: \ oracle \ product \ 10.2.0 \ flash_recovery_area (ORCL varies according to the Database Name). The flashback log file will be placed below.
SQL> select name, current_scn, flashback_on from v $ database;
NAME CURRENT_SCN FLASHBACK_ON
--------------------------------------
ORCL 0 YES
 
4) set the initialization parameter DB_FLASHBACK_RETENTION_TARGET:
SQL> alter system set db_flashback_retention_target = 1440 scope = both;
This parameter is used to control the retention time of the flashback log data, or the earliest time point you want the flashback database to restore. The default value is 1440, and the Unit is minute, that is, 24 hours. Note that although this parameter does not directly specify the flash recovery area size, it is restricted, for example, if the database has about 10% data changes every day, if the initial parameter value is set to 1440, the size of the flash recovery area should be at least 10% of the actual capacity of the current database, if this initialization parameter is set to 2880, the size of the flash recovery area is at least 20% of the database capacity.
 
5). Start the database
SQL> alter database open;
 
Flashback Database operation example
 
1. Check whether flash recovery area:
SQL> show parameter db_recovery_file
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest string D: \ oracle \ product \ 10.2.0 \ flash _ recovery_area \
Db_recovery_file_dest_size big integer 2G
2. Check whether archive is enabled
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
3. Check whether flashback database is enabled
SQL> select flashback_on from v $ database;
FLASHBACK_ON
------------------
YES
 
The data in the database before the restoration point: Each table in test1 and test2 has a data record as follows:
SQL> select * from test1;
ID NAME
--------------------
3759479
 
SQL> select * from test2;
ID VAL
--------------------
3759479
 
Here, 3759479 is a value of scn, which indicates that the data is before the restoration point.
 
4. query the current scn.
SQL> SELECT CURRENT_SCN FROM V $ DATABASE;
CURRENT_SCN
-----------
3761875
5. query the current time
SQL> select to_char (sysdate, 'yy-mm-dd hh24: mi: ss') time from dual;
TIME
-----------------
12-01-13 15:26:34
 
At that time, only the above points will be restored.
 
6. operations after recovery point:
A user accidentally deleted and submitted the data in Table test1.
SQL> delete from test1;
1 row deleted.
SQL> commit;
Commit complete.
At the same time, another user added data in table test2.
SQL> insert into test2 values (dbms_flashback.get_system_change_number, 'B ');
1 row created.
SQL> commit;
Commit complete.
 
SQL> select * from test2;
ID VAL
--------------------
3759479
3761885 B
 
7. Flashback Database is actually an incomplete restoration operation on the Database. Therefore, you need to shut down the Database and restart it to the mount state.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup mount
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 213912452 bytes
Database Buffers 390070272 bytes
Redo Buffers 7135232 bytes
Database mounted.
 
8. Restore: timestamp or SCN.
SQL> Flashback database to scn 3761875;
 
Flashback complete.
Flash back at a time point: Flashback database to timestamp to_timestamp ('12-01-13 15:26:34 ', 'yy-mm-dd hh24: mi: ss ');
 
9. Open the database:
 
After executing the flashback database Command, oracle provides two methods for you to restore the database:
1 ). run the alter database open read only command to open the database in read-only mode, and then export the data related to the table by mistake in logical export, execute the recover database command to re-apply the redo generated by the database, fix the database to the status before the flashback database operation, and then re-import the tables that were mistakenly operated by logic import, this will minimize the impact on existing data and avoid data loss.
2) directly alter database open resetlogs to open the database. Of course, all data generated after the specified scn or timestamp time point is lost.
 
SQL> alter database open read only;
Database altered.
SQL> select * from test1;
ID NAME
--------------------
3759479
 
For the test1 table, we found that the data before the recovery point has been flashed back. At this time, we should export the data in the flashback test1 table, perform a complete restoration to the database, and then import the data.
SQL> select * from test2;
ID NAME
--------------------
3759479
For the test2 table, we found that the data before the restoration point also exists, but the data after the restoration point (3761885 B) is lost.
 
SQL> shutdown immediate
SQL> startup mount
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
 
Verify the data again:
SQL> select * from test1;
No rows selected
SQL> select * from test2;
ID VAL
--------------------
3759479
3761885 B
 
It is the correct data after full recovery. Import the exported data from Table test1 to table test1. data retrieval is complete.
Two views related to the Flashback Database:
1. V $ database
Check whether the Flashback database function is enabled in this view.
SQL> select flashback_on from v $ database;
2. V $ flashback_database_log
The earliest time the Flashback Database can roll back to depends on the number of Flashback Database logs retained. This view can view a lot of useful information.
Oldest_flashback_scn/Oldest_flashback_time: These two columns are used to record the earliest point in time that can be recovered.
Flashback_size: records the size of the currently used Flash Recovery Area space
Retention_target: policy defined by the System
Estimated_flashback_size: estimate the size of the required space based on the policy.
3. V $ flashback_database_stat
This view is used to record and estimate the Flashback log space in a more fine-grained manner. This view records the activity volume of the database per hour. Flashback_Data indicates the number of Flashback logs generated, DB_Date indicates the number of data changes, and Redo_Date indicates the number of logs, these three quantities can reflect the Activity Characteristics of the data and more accurately predict the space requirements of the Flash Recovery Area.


From kkdelta