1. Oracle Flash back feature (FLASHBACK DATABASE)

Tag: Name byte operation-O-MF successfully configured CLU user management

Reprinted from: http://blog.csdn.net/leshami/article/details/6100429

Flash back technology is often used to quickly and easily recover a database of logic errors such as misoperation, from the way can be divided into the flash back based on database level flashback, table level flashback, transaction level flashback, according to the extent of the impact of flashback to the data can be divided into flashback recovery, flash back query. The flashback restore will modify the data, and all data after the flashback point will be lost. The flashback query can then query the data for different versions of DML, and it can also be used to determine whether or not to recover. This paper mainly describes the use of flashback database.

I. Flashback database characteristics

Flashback Data1base Flash back to a moment in the past, all work lost after the flashback point. Use Resetlogs to create a new scene and open the database (once resetlogs, you will no longer be able to flashback to a point in time before Resetlogs) Common scenarios: TRUNCATE TABLE, unexpected errors in multiple tables, etc. use the flashback log to implement a database flashback, and the data after the flashback point is lost

Second, the composition of flashback database

Flashback buffer: When Flashback database is enabled, a new area is created in the SGA as the flashback buffer, and the size is enabled by system allocation to enable the new RVWR process: The RVWR process writes the contents of the flashback buffer to the flashback log, noting that the flashback log differs from the online redo log. The flashback log is generated on top of the online redo log and is a log of the full data block image. The online log is a log of changes. The flashback log cannot be reused or archived. The flashback log uses a circular write method.

Third, the configuration of flashback database

Flashback database requires that the databases be in archive mode and must be opened with Resetlogs after the flashback. View the archive mode of the database and whether the flashback is enabled sql> select Log_mode,open_mode,    Flashback_on from V$database;             Log_mode open_mode flashback_on----------------------------------------ARCHIVELOG READ WRITE NO --FLASHBACK_ON is no, it indicates that the flashback feature has not been enabled B. View and set the Flash back directory, the size of the flashback directory space, and so on--see the Recovery directory and restore the size of the path allocation--you can use the ALTER system set DB_RECOVERY_FILE_D                                 EST to set a new path--you can use the ALTER system set DB_RECOVERY_FILE_DEST_SIZE to set a new size sql> show parameter Db_recovery NAME TYPE VALUE---------------------------------------------------------------------- -------Db_recovery_file_dest String/u01/app/oracle/flash_recovery_area db_recovery_file_dest_s                                 Ize Big integer 2G c. Set flashback retention target Lifetime sql> show parameter Db_flashback--default is minutes, hour NAME TYPE VALUE-----------------------------------------------------------------------------db_flashback_retention_target integer 1440 sql> alter system set Db_flashback_rete ntion_target=30;    --Set the retention time to half an hour d. Flashback is enabled in the Mount state, such as the following error message appears in the open state sql> ALTER DATABASE flashback on; ALTER DATABASE Flashback on * ERROR @ line 1:ora-38759:database must is mounted by only one instance and not O        Pen.    --Consistent close database, set flashback Sql>startup Mount Exclusive in Mount State;    Sql> select status from V$instance;     STATUS------------mounted sql> ALTER DATABASE flashback on; --Open Flashback database function sql> Ho ps-ef |        grep rvw--you can see a new background process RVWR Oracle 3563 1 0 12:12? 00:00:00 ORA_RVWR_ORCL--below to see the size of the flashback zone is about M, the data within the flash-back minute will need about m space-note that the column Oldest_flashback_time shows the earliest point of time allowed to return sql> SEL ECT OLDEST_FLASHBACK_SCN old_flhbck_scn,oldest_flashback_time old_flhbck_tim, 2 retention_target rete_trgt,flashback _size/1024/1024 Flhbck_siz, 3 estimated_flashback_size/1024/1024 est_flhbck_size 4 from V$flashback_database_log; OLD_FLHBCK_SCN OLD_FLHBC rete_trgt flhbck_siz est_flhbck_size---------------------------------------------------- ------915137 24-oct-10 7.8125 11.2519531 sql> select * from V$flashback_database_sta   T --View Flashback begin_tim End_time flashback_data db_data redo_data estimated_flashback_size-------------------------                        ---------------------------------------------------24-oct-10 24-oct-10 7905280 86802432 96329728       0 sql> Select * from V$sgastat where name like ' flashback% '; --View the amount of flash space allocated in the SGA POOL NAME BYTES---------------------------------------------                        ---shared pool flashback generation Buff 3981204 shared pool Flashback_marker_cache_si 9196    Sql> ho ls-hlt $ORACLE _base/flash_recovery_area/orcl/flashback--View generated flashback log Total 7.9M-RW-R-----1 Oracle oinstall 7.9M Oct 12:37 o1_mf_6d7dkogw_.flb 

Iv. using Flashback database flash back databases

Step (Prerequisite archive log available) shut down database start database to mount State (exclusive mode) flash back to a point in time, SCN or log sequence number use Resetlogs to open the database 1. Using Sqlplus for Flashback You can accept a time stamp or a system change number argument sqlplus several commonly used flashback database methods FLASHBACK [STANDBY] Database [<database_name>] to [before] S CN <system_change_number>--based on SCN flashback FLASHBACK [STANDBY] DATABASE [<database_name>] to [before] times TMP <system_timestamp_value>--based on timestamp flash back FLASHBACK [STANDBY] DATABASE [<database_name>] to [before] RESTO RE Point <restore_point_name>--based on point-in-time flashback as in the following example: Sql> Flashback database to timestamp (' 2010-10-24 13:04:30         ', ' yyyy-mm-dd hh24:mi:ss ');        Sql> Flashback database to SCN 918987;        Sql> Flashback database ro restore point b1_load;   A. Timestamp-based Flashback sql> select COUNT (1) from USR1.TB1; --Query user USR1 the number of records in the table tb1 count (1)----------404944 sql> Select To_char (sysdate, '  YYYY-MM-DD Hh24:mi:ss ') TM from dual;     --Get the current time TM of the system   -------------------2010-10-24 13:04:30 sql> drop user USR1 cascade;           --Delete account USR1, and all objects under Account USR1 will be deleted sql> Conn Scott/tiger;  --Login sql> CREATE TABLE tb_emp as SELECT * from EMP using Scott account;        --Newly created table tb_emp sql> shutdown immediate;             --Relational system sql> startup mount; --Back to Mount State sql> Flashback database to timestamp-implementation Flashback 2 To_timestamp (' 2010-10-24 13:04:30 ', ' yyyy-                          Mm-dd Hh24:mi:ss ');                     sql> ALTER DATABASE open resetlogs;    Sql> Select COUNT (1) from USR1.TB1; --Account USR1 and its objects tb1 successfully flashed back to COUNT (1)----------404944 sql> Select COUNT (1) from Scott.tb_em   P --After the flashback succeeds, the data modified after the flashback point is all lost select count (1) from Scott.tb_emp * ERROR on line 1:ora-00942:table or view does not exist B. Based on SCN number flash back sql> select CURRENT_SC N from V$datAbase;   --Obtain the current SCN number CURRENT_SCN-----------918987 sql> drop table usr1.tb1;    --Delete the table under user USR1 TB1 sql> alter system checkpoint;             --Manually perform checkpoint sql> select file#,checkpoint_change# from V$datafile;  file# checkpoint_change#----------------------------5 921478 sql> shutdown                        Abort         sql> startup Mount;         Sql> Flashback database to SCN 918987;          Sql> Select COUNT (1) from USR1.TB1;   COUNT (1)----------404944 C. Based on the point-in-time flashback sql> CREATE TABLE t (ID int,col varchar2 (20));         --CREATE TABLE T sql> insert into T values (1, ' ABC ');         Sql> INSERT INTO t values (2, ' DEF ');         Sql> commit;    sql> Create restore point bef_damage;         --Create a flashback point sql> insert INTO T values (3, ' GHI ');   Sql> select Ora_rowscn,id,col from T;       --View the record of table T ORA_ROWSCN ID COL ----------------------------------------1874406 1 ABC 1874406 2 DEF 18                    74406 3 GHI sql> shutdown immediate;                    Sql> startup Mount Exclusive;  Sql> Flashback database to restore point bef_damage;                    --Implement time Flash back sql> alter DATABASE open resetlogs;   Sql> select * from T; --After the flashback succeeds, the data loss ID of the flash back point COL------------------------------1 ABC 2 DE        F 2. Using Rman for flashback database Several common ways of using Rman to flash back databases rman> flashback to scn=918987;    Rman> Flashback database to sequence=85 thread=1;    Sql> CREATE TABLE Scott.tb_emp as SELECT * from Scott.emp;      Sql> Select COUNT (1) from scott.tb_emp;    COUNT (1)----------sql> Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') TM from dual; TM-------------------2010-10-24 13:59:38 sql> drop table scott.tb_emp;        sql> shutdown abort;    sql> startup Mount;    Rman> Flashback database 2> to Time= "to_date (' 2010-10-24 13:59:00 ', ' yyyy-mm-dd hh24:mi:ss ')";      Sql> Select COUNT (1) from scott.tb_emp; COUNT (1)----------14 query view: V$recovery_file_dest will show the use of the Flashback zone sql> Select name,space_limit/1024/1024 SP_LIMT 2, space_used/1024/1024 sp_usd,space_reclaimable/1024/1024 sp_recl, 3 number_of_files num_fils from V    $recovery _file_dest; NAME SP_LIMT SP_USD sp_recl num_fils----------------------------- --------------------------------------------------------/u01/app/oracle/flash_recovery_area 2048 4    72.070313 380.671875 18 Some table spaces can be excluded from the flashback sql> alter tablespace users flashback off;    Sql> Select name,flashback_on from V$tablespace where ts#=4; NAME FLA------------------UseRS NO If you need to enable the flashback feature for the above table spaces, you will need to turn on this feature in Mount mode for this table space. 

V. Summary

The flashback strategy is an enhancement to the Oracle Revocation feature, which provides more convenience for fast recovery of the database. The database's flashback recovery is faster than Rman and user-managed backup and recovery, mainly because the database flashback uses the flashback log,
A full image of the block is saved in the flashback log. Second, the degree to which the flashback can be recovered depends on the size of the flashback space and the retention policy of the flashback, and the amount of flashback space is recycled, while the flashback retention policy determines the length of time the flashback log is retained. In short, a reasonable balance between recovery speed and available space depends on the specific service requirements.

1. Oracle Flash back feature (FLASHBACK DATABASE)