Oracle flash back Technology

The flashback query is introduced in oracle 9i. The flashback query actually uses the UNDO information to obtain the previous data. Since the UNDO space is used cyclically and the pre-transaction image cannot be permanently saved, the flashback query can only support data queries over the past period. This time is related to undo_retention. The default value of undo_retention is 900 s.

The following is an example of flashback's data recovery query:

1. create a user flySQL> create User fly identified by fly; user created. SQL> grant dba, connect, resource to fly; Grant succeeded.2, create table and index SQL> conn fly/flyConnected. SQL> create table fly as select * from dba_objects; Table created. SQL> create index idx_fly on fly (object_id); Index created. SQL> select count (1) from fly; COUNT (1) ---------- 781933, change the session time display format SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'; Session altered. SQL> select sysdate from dual; SYSDATE-------------------2013-12-05, assuming that the accidentally deleted data and submitted, as shown below: SQL> delete from fly where rownum <500; 499 rows deleted. SQL> commit; Commit complete.5. You can consider the following methods to restore data. Use flashback5.1 flashback5.2 imp/impdp5.3 rman5.4 logminer6 and use flashback to flash back to query the data at the time point before the table is deleted, save as a temporary table SQL> create table fly_tmp_1205 as select * from fly as of timestamp to_timestamp ('2017-12-05 21:19:52 ', 'yyyy-mm-dd hh24: mi: ss'); Table created. SQL> select count (*) from fly_tmp_1205; COUNT (*) ---------- 78193 if you only remember that the data was deleted a few minutes ago, assume that the data at the current time has been deleted for about 11 minutes: SQL> select count (1) from fly as of timestamp sysdate-11/1440; COUNT (1) ---------- 78193

In Oracle 10 Gb, the flash-back query function is enhanced, and the ability to roll back the entire database to a time point in the past is provided. This function
It is implemented through flashback log. The flashback log is a bit similar to the redo log, but the redo log rolls the database forward. The flashback log
Roll back the database. To save files related to management and Backup recovery, oracle10g provides a file called Flash recovery, which is created in the ORACLE_BASE directory by default. All recovery-related files, such as flashback log, archive log,
Backup set and so on, and put it in this region for centralized management.
At the Oracle 11 GB, another new feature was released: Oracle Flashback Data Archive. the FDA stores changed data to the Flashback Archive created in another flash Archive area, which is different from the undo. In this way, you can separately set storage policies for the flash Archive area, you can also flash back to the old data before the specified time without affecting the undo policy, that is, to reduce the dependency on undo. Because in a very busy system, undo Protection
The storage time is very limited. But through the FDA, we need to be much more flexible.
The Flashback Technology can be divided into the following four types:
Flashback Database. The flash back function must be enabled. The flash back function is disabled by default. The flash back function is not enabled in the production Database.
Flashback Drop. the recycle bin must be enabled. It is enabled by default.
Flashback Query (divided into Flashback Query, Flashback Version Query, and Flashback Transaction Query) based on undo Information
Flashback Table. The principle is based on undo information. flashback query is generally used to recover data instead of flashback table.

SQL> select file_type from v $ flash_recovery_area_usage; FILE_TYPE--------------------CONTROL FILEREDO LOGARCHIVED LOGBACKUP PIECEIMAGE COPYFLASHBACK LOGFOREIGN ARCHIVED LOG7 rows selected.

Starting from Oracle 10 Gb, a new column is added in some dynamic performance views, such as V $ CONTROLFILE, V $ LOGFILE, V $ ARCHIVED_LOG, and V $ DATAFILE_COPY: IS_RECOVERY_DEST_FILE, specifies whether the relevant files are in the recovery area.

SQL> select recid,is_recovery_dest_file from v$archived_log where recid<=5;RECID IS_---------- ---1 YES2 YES3 YES4 YES5 YES

1. Set the flashback recovery area Parameter
The Flash recovery zone is mainly set and managed through three initialization parameters:
Db_recovery_file_dest: Specifies the position of the flash recovery zone.
Db_recovery_file_dest_size: Specifies the available space size of the flash recovery zone.
Db_flashback_retention_target: specifies the time when the database can be rolled back. The unit is minute. The default value is 1440 minutes, that is, one day.
Of course, the rollback time is also determined by the size of the flash recovery zone, because the flash log required for rollback is saved. So this
The parameter must be modified with db_recovery_file_dest_size.

SQL> show parameter db_recoveryNAME                                TYPE       VALUE-----------------------------------------------------------------------------db_recovery_file_dest               string        /home/oracle/flash_recovery_areadb_recovery_file_dest_size     big integer   2GSQL> alter system set db_recovery_file_dest_size=8G scope=both;System altered.SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';System altered.SQL> show parameter db_recoveryNAME                                TYPE       VALUE-----------------------------------------------------------------------------db_recovery_file_dest               string          /u01/app/oracle/flash_recovery _areadb_recovery_file_dest_size        big integer   8G

Note:
If the log_archive_dest_n parameter is not set after the flash recovery zone is set, archive logs are saved to this region by default. We need
Pay attention to the usage of the space in the Flash recovery area. If the flash recovery area is full, there will be no archiving, and the database will be hang. In fact, oracle is
Implicitly set log_archive_dest_10 = 'location = USE_DB_RECOVERY_FILE_DEST. Flash recovery of multiple databases
The partition can be specified to the same location, but db_name cannot be the same, or db_unique_name is different. The Flash recovery zone of RAC must be located
Shared disks can be accessed by all instances.

Production environment. It is generally recommended to change the archiving path to the non-flash recovery zone. SQL> alter system set log_archive_dest_1 = 'location =/archivelog ';

 

2. Flashback Database

2.1. How to enable and disable 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
2) When flashback is enabled, the database can be flashed back to within one day by default, according to The db_flashback_retention_target Parameter
By default, the flashback database is disabled and can be enabled in the mount status. After the flash recovery area is set, you can start the flash back database function.

SQL> select flashback_on from v $ database; FLASHBACK_ON------------------NO -- the database must already be in archive mode: SQL> archive log list; SQL> shutdown immediate; SQL> startup mount; SQL> alter database flashback on; SQL> alter database open; SQL> select flashback_on from v $ database; SQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut down. SQL> startup mountORACLE instance started. total System Global Area 3240239104 bytesFixed Size 2164048 bytesVariable Size 2499807920 bytesDatabase Buffers 721420288 bytesRedo Buffers 16846848 bytesDatabase mounted. SQL> archive log list; Database log mode Archive ModeAutomatic archival EnabledArchive destination/archivelogOldest online log sequence 511 Next log sequence to archive 514 Current log sequence 514SQL> alter database flashback on; Database altered. SQL> alter database open; Database altered. SQL> select flashback_on from v $ database; FLASHBACK_ON------------------YES

2.2 example of using Flashback Database for Database Incomplete recovery

2.2.1. query the current scn and time SQL> show userUSER is "SYS" SQL> select dbms_flashback.get_system_change_number from dual; or SQL> select current_scn from v $ database; CURRENT_SCN-----------32945957SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'; Session altered. SQL> select sysdate from dual; SYSDATE-------------------2013-12-05 23: 12: 392.2.2. create a table SQL> conn fly/flyConnected. SQL> drop table fly; Table dropped. SQL> create table fly as select * from dba_objects; Table created.2.2.3. restart the database to mountSQL> conn sys/oracle as sysdbaConnected. SQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut down. SQL> startup mountORACLE instance started. total System Global Area 3240239104 bytesFixed Size 2164048 bytesVariable Size 2499807920 bytesDatabase Buffers 721420288 bytesRedo Buffers 16846848 bytesDatabase mounted. SQL> 2.2.4. restore execution: You can use timestamp or scn to flash back SQL> flashback database to timestamp to_timestamp ('2017-12-05 23:12:39 ', 'yyyy-mm-dd hh24: mi: ss'); or SQL> flashback database to scn 32945957; 2.2.5. you need to use resetlogs to open the database and check that the fly table does not exist. SQL> alter database open resetlogs; SQL> select count (*) from fly;

 

3. flashback drop
Flashback drop occurs from Oracle 10 GB and is used to restore objects accidentally deleted by users (including tables and indexes). This technology relies on Tablespace Recycle Bin (Tablespace Recycle Bin ), the function is very similar to the recycle bin of windows.
Flashback drop does not support sys and system users because the objects they create are in the system tablespace by default. Objects in the system tablespace are deleted and will not enter the recycle bin. Therefore, show recyclebin is empty when sys or system users log on.

3.1.Tablespace Recycle Bin
Starting from Oracle 10 Gb, each tablespace has a logical region called the recycle bin. When you run the drop command, the deleted table and associated objects (including indexes and constraints) of the table are deleted, trigger, LOB segment, and LOB index segment) will not be physically deleted. These objects are first transferred to the recycle bin, which provides users with a possibility of recovery.
Note: dba_recyclebin only retains objects in non-SYSTEM tablespace. Objects in the SYSTEM tablespace are also deleted directly when they are dropped, and will not be placed in the recycle bin.
If the recycle bin function is enabled after an object is dropped. it is not actually deleted. In fact, we only modified the name. We can still find it using select * from user_objects where type = 'table. its name is a bit strange. for example, BIN $ qAUuckGyd3TgQKjAFAFTAg ==$ 0

The recyclebin parameter is used to control whether to enable the recyclebin function. The default value is ON and can be disabled with OFF. SQL> show parameter recyclebinNAME TYPE VALUE items ------------- ------------------------------ recyclebin string on the Recycle Bin AREA OF THE tablespace is only a logical area, rather than physically setting a partition area from the tablespace for the Recycle Bin, therefore, Recycle Bin shares the storage area of the tablespace with common objects, or the objects of Recycle Bin must snatch the storage space with common objects. When space is insufficient, Oracle overwrites the objects in Recycle Bin in the first-in-first-out order. You can also manually delete the space occupied by Recycle Bin: 1. purge dba_recyclebin: delete objects in all users' Recycle Bin. This Command requires sysdba permission 2. purge recyclebin: Delete the objects in the current user's Recycle Bin. 3. drop table table_name purge: delete objects permanently and cannot be restored using Flashback instead of in Recycle Bin. 4. purge tablespace tablespace_name: used to clear the Recycle Bin5. Purge tablespace tablespace_name user user_name: Clear the specified user object in the Recycle Bin of the specified tablespace 6. purge index recycle_bin_object_name: to release the space of the Recycle bin and restore the table, you can release the space occupied by the index of the object to relieve the space pressure. Because indexes can be rebuilt. 3. 2. view the content of the recycle bin. The deleted objects will be deleted directly and will not be written to recyclebin. Of course, when you delete the objects, specify the purge parameter, and the table will be deleted directly without being written to recyclebin. View the content in the recyblebin object: SQL> select * from "BIN $ RWXQQcTPRde0ws4h9ewJcg ==$ 0"; 3.flashback Drop instance operation recover fly table: SQL> flashback table fly to before drop; if the name of the fly table is the same as that of the fly table after the fly table is deleted, rename the table to be restored: SQL> flashback table fly to before drop rename to fly007;

 

4. Flashback Query uses the multi-version read consistency feature to read the record data before the operation from the UNDO tablespace.
Flashback query is invalid for dynamic performance views such as v $ tables and x $ tables, but is effective for data dictionaries such as dba _ *, all _ *, and user. Supplement the Flashback query restoration function, stored procedures, packages, triggers, and other objects. Flashback Drop can flash back the objects associated with the table, for example, functions, procedure, and trigger. At this time, we need to use the all_resource table for Flashback Query.

4. 1. view All dba_source's typeSQL> select type from all_source group by type; TYPE------------PROCEDUREPACKAGEPACKAGE BODYLIBRARYTYPE BODYTRIGGERFUNCTIONTYPE8 rows selected.4.2. create a function SQL> conn fly/flyConnected. SQL> CREATE OR REPLACE function getdate return date2 as3 v_date date; 4 begin5 select sysdate into v_date from dual; 6 return v_date; 7 end; 8/Function created. SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'; Session altered. SQL> select getdate () from dual; GETDATE () ----------------- 2013-12-05. 3. query the dba_source table: SQL> select text from dba_source where name = 'getdate' order by line; SQL> drop function GETDATE; 4.4 Flashback Query SQL> select text from dba_source as of timestamp to_timestamp ('2017-12-05 23:51:23 ', 'yyyy-mm-dd hh24: mi: ss ') where name = 'getdate' order by line;

 

This article from the "Yang youyoucuncaoxin" blog, please be sure to keep this source http://fly1116.blog.51cto.com/8301004/1336893