Oracle Flash Back

Oracle Flash Back Technology

1.Flashback Database

2.Flashback Drop

3.Flashback Table

4.Flashback Query

1.Flashback Database (recovery with flashback log)

Description: The Oracle Flashback database feature allows SQL statements to be Flashback to databases to roll forward to the current previous point in time or to the SCN. The flashback database can quickly return a database to a previous point in time of misoperation or human error, such as CTRL + Z, and can quickly implement a point-in-time recovery without using Backup. Oracle records the flashback operation of the database by creating a new flashback Logs (Flashback log). If you want to be able to flash back to the database, you need to set the following parameters: Db_recover_file_dest log storage location, db_recover_file_dest_size the size of the recovery area.

Note: When creating the database, Oracle will automatically create the recovery area, but it is turned off by default and requires the ALTER DATABASE flashback on command to be executed.

1. Flashback Database cannot resolve media Failure, this error Rman recovery is still the only option

2. If you delete a data file or use shrink technology to reduce the size of the data file, you can not use Flashback database technology to fall back to the state before the change, you must first use Rman to delete before or reduce the previous file backup restore, Then use flashback Database to execute the remaining flashback datbase.

3. If the control file is recovered from the backup, or is a rebuilt control file, you cannot use flashback Database.

4. The earliest SCN that can be recovered using the flashback database lock depends on the earliest SCN recorded in the flashback log.

Flashback database includes a process recover Writer (RVWR) background process, Flashback database log logs and Flash Recovery area. Once the database is enabled for flashback, the RVWR process starts, and the process writes flashback Database log to the Flash Recovery area, which includes the "front mirror" of the data block. This is why flashback Database technology does not completely recover blocks.

Configuring Flash Recovery Area

To use flashback database, you must use the Flash Recovery area, because flashback database log can only be saved here. The 2 parameters to configure are as follows, one is the size and the other is the position. If the database is Rac,flash recovery area must be located in shared storage. The database must be in Archivelog mode.

Enable Flash Recovery Area:

Sql>alter SYSTEM SET db_recovery_file_dest_size=4g scope=both;

Sql>alter SYSTEM SET db_recovery_file_dest= '/home/oracle/fast ' Scope=both;

Disable Flash Recovery area:

Sql>alter SYSTEM SET db_recovery_file_dest= ";

For Flash Recovery area,oracle is recommended, the greater the Flash Recovery area settings, Flashback database recovery capability is stronger, it is recommended that Flash Recovery area can be placed under all Data files, incremental backups, and all the archived files that have not yet been backed up, and of course the flashback logs that it generates.

When the database is running, Oracle automatically writes files to the zone, and when the remaining space is less than 15%, it adds a warning to alert you that there is not enough space. However, this will not affect the normal operation of the database until all the space has been used, Oracle first attempt to delete some outdated files, redundant files or backup files.

Steps:

configuration parameter information;

Close the database

Boot to Mount State

Check the flashback function, which is turned off by default.

Select name,current_scn,flashback_on from V$database;

NAME CURRENT_SCN flashback_on

--------- ----------- ------------------

PROD1 1064824 YES

Off is not open, change to off can be changed in the open state, after the change Flash_recovery_area flashback log file will be automatically deleted by Oracle.

A orcl/flashback directory (ORCL based on the name of the database) is generated under the directory/u01/app/oracle/flash_recovery_area that is set to start, and the flashback log file is placed below.

Setting Parameters: Db_flashback_retention_target

Alter system set DB_FLASHBACK_RETENTION_TARGET=1440 Scope=both;

This parameter is used to control the time that the Flashback log data is retained, or the earliest point in time that you want flashback database to recover. The default value is 1440, the unit is minute, that is, 24 hours, it is important to note that although the Flash recovery area size is not directly specified, but is subject to its constraints, for example, if the database has about 10% data changes per day, if the initialization parameter value is set to 1440, If the size of the flash recovery area is at least 10% of the actual capacity of the current database, if the initialization parameter is set to 2880, the size of the flash recovery area is at least 20% of the capacity of the database.

Cases:

Sql> CREATE TABLE Test1

2 (ID number,

3 name VARCHAR2 (20));

Table created.

Sql> CREATE TABLE Test2

2 (ID number,

3 name VARCHAR2 (20));

Table created.

sql> INSERT into test1 values (dbms_flashback.get_system_change_number, ' A ');

1 row created.

sql> INSERT into test2 values (dbms_flashback.get_system_change_number, ' A ');

1 row created.

Sql> commit;

Commit complete.

Sql> select * from Test1;

ID NAME

---------- --------------------

1065468 A

Sql> select * from Test2;

ID NAME

---------- --------------------

1065470 A

Querying the current SCN

Sql> SELECT CURRENT_SCN from V$database;

Current_scn

-----------

1065501

Query the current time

Sql> Select To_char (sysdate, ' yy-mm-dd hh24:mi:ss ') time from dual;

Delete Table 1 data and submit

Delete from Test1;

Commit

SELECT * from Test1;

No rows selected

Add table 2 Data and submit

INSERT into test2 values (dbms_flashback.get_system_change_number, ' B ');

Commit

SELECT * from Test2;

ID NAME

---------- --------------------

1065470 A

1065529 B

Recovery steps

Shutdown immediate

Startup Mount

SCN or point-in-time recovery

Flashback database to SCN 1065501;

Flashback database to timestamp to_timestamp (' 16-10-15 09:36:05 ', ' yy-mm-dd hh24:mi:ss ');

Open the database:

After executing the Flashback database command, Oracle provides two ways for you to repair your databases:

1). Execute the ALTER DATABASE open read Only command in read-only mode and then immediately export the data to the table in the form of a logical export and then execute the recover database command to re-apply the redo generated by the database, repair the database to the state before flashback database operation, and then re-import the table that was previously mistakenly manipulated by a logical import, so that the impact on existing data is minimal and there is no data loss.

2). Direct ALTER DATABASE open Resetlogs opens the databases, of course, the data generated after the SCN or timestamp point of time is lost.

Sql> ALTER DATABASE open read only;

Database altered.

Sql> select * from Test1;

ID NAME

---------- ----------

3759479 A

For the Test1 table, here we find that the data before the recovery point has been flashed back. This time, the data of the Test1 table should be exported, the database will be fully restored, and then the data will be imported.

Sql> select * from Test2;

ID NAME

---------- ----------

3759479 A

For the Test2 table, we found that the data before the recovery point also existed, but the data after the recovery point (3761885 B) was lost.

sql> shutdown Immediate

Sql> Startup Mount

Exp Sys/[email protected] file=/home/oracle/test1.dmp Tables=test1

sql> Recover database;

Media recovery complete.

sql> ALTER DATABASE open;

Database altered.

Then verify the data:

Sql> select * from Test1;

No rows selected

Sql> select * from Test2;

ID VAL

---------- ----------

3759479 A

3761885 B

Imp sys/[email protected] ignore=y tables=test1 full=n file=/home/oracle/test1.dmp

Is the correct data after the complete recovery, the start Test1 table export data into the Test1 table, the data back to completion.

2 views related to Flashback Database:

1. V$database

This view can see if the Flashback database feature is enabled

Sql> select flashback_on from V$database;

2. V$flashback_database_log

The earliest time Flashback database can fall back to, depending on how much of the Flashback database Log is retained, the view can see a lot of useful information.

Oldest_flashback_scn/oldest_flashback_time: These two columns are used to record the time that can be restored to the earliest

Flashback_size: Records the size of the currently used Flash Recovery area space

Retention_target: System-defined policies

Estimated_flashback_size: Estimated amount of space required based on policy

3. V$flashback_database_stat

This view is used to make finer-grained records and estimates of the flashback log space situation. This view records the amount of activity of the database per unit time in hours, Flashback_data represents the number of Flashback log generation, Db_date represents the number of data changes, Redo_date represents the number of logs, through which 3 quantities can reflect the activity characteristics of the data, More accurate projected space requirements for Flash Recovery area

3. Flashback Drop (recovery using Recycle Bin function)

Oracle's Flashback Drop Flashback removal feature gives us a convenient way to recover from a mistakenly deleted table, and the principle of this functionality is Oracle's "Recycle Bin" (recyclebin) functionality. Note that this feature is not supported if the deleted table was originally stored on the system table space.

Restores the table that was dropped by mistake. When a table is deleted, you can still view the contents of the drop table by looking at the contents of the Recycle Bin.

The deleted table will be in a place called the RecycleBin Recycle Bin, and when the drop is dropped the table is actually changed to a name.

Views about the Recycle Bin

Dba_recyclebin

User_recyclebin

RecycleBin

Dropped table Short Message

Show RecycleBin

Insufficient table space

Insufficient space quota for user

Purge command

When the table is restored using the Flashback command, the contents of that record in the corresponding Recycle Bin are cleared

FLASHBACK TABLE TEST3 to before DROP;

FLASHBACK TABLE "Bin$z6gzdcwg7hfgqab/aqaroq==$0" to before DROP;

FLASHBACK TABLE TEST3 to before DROP RENAME to test3_version_1;

Sql> CREATE TABLE Test3

2 (ID number,

3 name VARCHAR2 (30))

4 tablespace users;

Table created.

sql> INSERT INTO TEST3 values (1, ' A '

2);

1 row created.

sql> INSERT INTO TEST3 values (2, ' B ');

1 row created.

Sql> commit;

Commit complete.

Sql> select * from Test3;

ID NAME

---------- ------------------------------

2 b

1 A

sql> drop table test3;

Table dropped.

Sql> show RecycleBin;

ORIGINAL name recyclebin nameOBJECT TYPE DROP time

---------------- ------------------------------ ------------ -------------------

TEST3 bin$pujbm654as3gukjazhjl+a==$0 TABLE 2016-10-15:10:06:59

Sql> select * from Test3;

SELECT * FROM Test3

*

ERROR at line 1:

Ora-00942:table or view does not exist

Sql> FLASHBACK TABLE TEST3 to before DROP;

Flashback complete.

Sql> select * from Test3;

ID NAME

---------- ------------------------------

1 A

2 B

Sql>

Clears the Recycle Bin for the current user

Sql> purge RecycleBin;

Sql> purge User_recyclebin;

Clears the Recycle Bin for the specified tablespace tbs_sec_d

Sql> purge tablespace tbs_sec_d;

Clears the specified tablespace tbs_sec_d, while specifying the Recycle Bin for the user sec

Sql> Purge tablespace tbs_sec_d user sec;

Clears all content in the Recycle Bin (sys user)

Sql> Purge Dba_recyclebin

Drop table method that does not produce Recycle Bin data

This method is a way to completely delete a table, before use to consider clearly.

sql> drop table ft_1 purge;

3. Flashback Table (restore with undo retention information)

The Oracle Flashback table feature allows the Flashback table statement to be used to ensure that a flash back to the previous point in time of the table. Use rollback segment information to restore one or more tables to a previous point in time (a snapshot). Note that Flashback table is not equal to Flashback query,flashback query is simply querying a previous snapshot point and does not change the state of the current table, but Flashback Table will change the current table and the attached objects together to return to the previous point in time.

Undo_management, the parameter needs to be "AUTO"

Undo_retention, unit is seconds, adjusts the time range for rollback

Online operation

Restores any data to a specified point in time (or SCN)

Automatically recover related properties

Meet distributed consistency

Consistency of the data, all related objects will be automatically consistent.

Sql> Flashback table Test4 to timestamp to_timestamp (' 2012-12-29 16:44:17 ', ' yyyy-mm-dd hh24:mi:ss ');

Sql> Flashback table Test4 to SCN 662421;

Sql> Flashback table Test4 to timestamp to_timestamp (' 2012-12-29 16:44:17 ', ' yyyy-mm-dd hh24:mi:ss ') enable triggers;

The normal user needs to have flashback any table system permission;

There is a SELECT, INSERT, delete, ALTER permission for the table;

You must ensure that the table has row movement (row movement).

Cases:

CREATE TABLE test4 tablespace users as SELECT * from Hr.employees;

Select COUNT (*) from test4;

SELECT * from Test4;

To create an index for a test4 table

Create INDEX Ind_test4 on test4 (employee_id);

Create or Replace Trigger Tr_test

After update on TEST4

For each row

Begin

Null

End

Tr_test;

/

(SYS)

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') time, To_char (Dbms_flashback.get_system_change_number) SCN from dual;

Delete from test4;

Commit

Drop index IND_TEST4;

Create or Replace Trigger Tr_test

After insert on test4

For each row

Begin

Null

End

Tr_test;

/

Ensure row movement functionality in the table

ALTER TABLE test4 enable row movement;

Flashback table Test4 to timestamp to_timestamp (' 2016-10-15 10:49:09 ', ' yyyy-mm-dd hh24:mi:ss ') enable triggers;

Select Index_name from user_indexes where table_name = ' test4 ';

Select object_name, status from User_objects where object_name in (' Tr_test ', ' ind_test ');

Select text from User_source t where T.name = ' tr_test ';

1. Flashback table in a truly high-availability environment, the use of small, limited more, you must ensure that the row migration function

2. Block write operation during Flashback table

3. Using Flashback table, you can flash the Delete method to a previous point in time, and the index in the table does not recover properly because the process of dropping the index does not record undo.

4. The recovered trigger itself is still modified, and does not follow the table flashback to the previous point in time. The description keyword enable triggers only guarantees that the state of the trigger is normal, not the content rollback.

5. Because the principle uses its Undo information to restore its objects, it is also not possible to recover truncate data

6. Recover data with flashback query to achieve better

4.Flashback Query

Flashback Query is the record data that takes advantage of the multi-version read consistency feature to read from the Undo table space before the operation!

What is multi-version read consistency

Oracle employs a very good design that uses the undo data to ensure that write does not clog the read, simply speaking, different transactions when writing data, will write the data's pre-image to the undo table space, so that if there are other transactions to query the table data, you can use the Undo The pre-image of the data in the tablespace to construct the required full set of records without the need to wait for the write transaction to commit or rollback.

Flashback query has a number of ways to build a set of queries, and the selection of a recordset can be based on time or SCN, and can even be queried for a pre-image that records different transactions in the Undo table space. The usage is very similar to a standard query, and the simplest way to query undo data in undo with flashback query is to keep the as of timestamp (time based) or as of SCN (based on SCN) behind the table name of the standard query statement. This feature is not related to flashback on/off and RecycleBin on/off.

Cases:

HR User

CREATE TABLE Test5

(ID number,

Name VARCHAR2 (20));

Table created.

sql> INSERT into HR.TEST5 values (dbms_flashback.get_system_change_number, ' A '); (SYS)

SELECT * from Test5;

ID NAME

---------- --------------------

1061963 A

Select To_char (sysdate, ' yy-mm-dd hh24:mi:ss ') time from dual;

Time

-----------------

16-10-15 11:27:15

ALTER TABLE TEST5 enable row movement;

Delete from Test5;

Commit

SELECT * from Hr.test5 as of timestamp (systimestamp-interval ' 2 ' MINUTE);

Or

SELECT * from Test5 as of timestamp to_timestamp (' 16-10-15 11:27:15 ', ' yy-mm-dd hh24:mi:ss ');

INSERT INTO TEST5 SELECT * from Test5 as of timestamp to_timestamp (' 16-10-15 11:27:15 ', ' yy-mm-dd hh24:mi:ss ');

As of SCN example

SELECT Dbms_flashback.get_system_change_number from dual;

SELECT CURRENT_SCN from V$database;

Delete from Hr.test5;

Commit

INSERT INTO HR.TEST5 SELECT * from Hr.test5 as of SCN 1063434;

Commit

SELECT * from HR.TEST5;

In fact, Oracle uses the SCN internally, even if you specify as of timestamp,oracle to convert it to SCN, a table exists between the system time tag and the SCN, which is the smon_scn_time under SYS

Every 5 minutes, the system generates a system time tag that matches the SCN and is stored in the Sys.smon_scn_time table, which records the last 1440 system time markers and SCN matching records, since the table maintains only the last 1440 records, so if you use the AS Timestamp can only flashback data for the last 5 days (assuming that the system is operating continuously and without interruption or shutdown restart).

Note that the system time mark matches every 5 minutes of the SCN, for example, scn:3764577,3765348 matches 12-01-12 13:52:00 and 12-01-12 13:57:00 respectively when you pass as of timestamp Query 12-01-12 13:52:00 or 12-01-12 13:57:00 at this point in time, Oracle will match it to scn:3764577 to the undo table space, and it says that at this time, no matter what point in time you specify, The query will return the data at this time of 12-01-12 13:52:00.

View the correspondence between SCN and timestamp:

Select Scn,to_char (TIME_DP, ' Yyyy-mm-dd hh24:mi:ss ') from Sys.smon_scn_time;

This article is from the "11862116" blog, please be sure to keep this source http://11872116.blog.51cto.com/11862116/1959078

Oracle Flash Back