Summary of oracle flashback database, oracleflashback

Summary of oracle flashback database, oracleflashback

Flashback database is a very important feature of oracle databases. Compared with the function of retrieving historical versions of table data through undo and executing logical error recovery, flashback database has completely different functions: undo data is targeted at the entire database; undo data is stored for a limited period of time; undo data is essentially to maintain data consistency. The flashback database can only store historical data for a tablespace. As long as the storage space permits, the flashback database can save any long history time. The flashback database Log stores the pre-image of the database data.

The overall architecture of the flash back database includes a process (rvmr), a storage space (flash recovery area), and a log (flashback database log ). The "front image" of the data block is stored in the flashback database log ).

Enable flashback Database

To use the Flashback Database, you must use the Flash Recovery Area. 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. The db_flashback_retention_target parameter controls the retention time of the flash log. The default value is 1440, that is, 24 hours.

SQL> show parameter db_recoveryNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest                string      /u01/app/oraele/fast_recovery_                                                 areadb_recovery_file_dest_size           big integer 4122MSQL> show parameter flashbackNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_flashback_retention_target        integer     1440

SQL> archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     5Next log sequence to archive   7Current log sequence           7

To enable the flash back function, you can use alter database flashback on;

You can run this command in mount and open modes at 11 GB:

SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE    11.2.0.3.0      ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL> alter database flashback on;Database altered.SQL> select status from v$instance;STATUS------------OPEN

At 10 Gb, You need to execute this command in the mount status.

SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE    10.2.0.5.0      ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - ProductionSQL> select flashback_on from v$database;FLASHBACK_ON------------------NOSQL> select status from v$instance;STATUS------------OPENSQL> 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.

Create lab table

SQL> truncate table test;Table truncated.SQL> insert into test values((select count(*) from test),(select current_scn from v$database));1 row created.SQL> commit;Commit complete.SQL>  insert into test values((select count(*) from test),(select current_scn from v$database));1 row created.SQL> commit;Commit complete.SQL>  insert into test values((select count(*) from test),(select current_scn from v$database));1 row created.SQL> commit;Commit complete.SQL> select * from test;        ID SCN---------- --------------------         0 1121575         1 1131647         2 1142043

After executing the flashback database command, you can fix the database in multiple ways:

1) directly alter database open resetlogs to open the database. All data generated after the specified scn or timestamp time point is lost.

2 ). run the alter database open read only command to open the database in read-only mode and check whether the recovered data meets the requirements. If yes, use resetlogs to open the database. Otherwise, execute the flashback operation again.

3 ). 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.

The second method is demonstrated below:

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area  267227136 bytesFixed Size                  2227504 bytesVariable Size             192938704 bytesDatabase Buffers           67108864 bytesRedo Buffers                4952064 bytesDatabase mounted.SQL> flashback database to scn  1121575;Flashback complete.SQL> alter database open read only;Database altered.SQL> select * from test;no rows selectedSQL> startup mount forceORACLE instance started.Total System Global Area  267227136 bytesFixed Size                  2227504 bytesVariable Size             192938704 bytesDatabase Buffers           67108864 bytesRedo Buffers                4952064 bytesDatabase mounted.SQL>SQL>SQL>SQL>SQL>SQL> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area  267227136 bytesFixed Size                  2227504 bytesVariable Size             192938704 bytesDatabase Buffers           67108864 bytesRedo Buffers                4952064 bytesDatabase mounted.SQL> flashback database to scn  1121600;Flashback complete.SQL> select * from test;select * from test              *ERROR at line 1:ORA-01219: database not open: queries allowed on fixed tables/views onlySQL> alter database open read only;Database altered.SQL> select * from test;        ID SCN---------- --------------------         0 1121575SQL> startup mount forceORACLE instance started.Total System Global Area  267227136 bytesFixed Size                  2227504 bytesVariable Size             192938704 bytesDatabase Buffers           67108864 bytesRedo Buffers                4952064 bytesDatabase mounted.SQL> alter database open resetlog;alter database open resetlog                    *ERROR at line 1:ORA-02288: invalid OPEN modeSQL> alter database open resetlogs;Database altered.SQL> select * from test;        ID SCN---------- --------------------         0 1121575

Views related to flashback Databases

V $ database

In this view, you can check whether the Flashback database function is enabled.

SQL> select flashback_on from v $ database;

FLASHBACK_ON

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

YES

 

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: recoverable duration

Estimated_flashback_size: estimate the size of the required space based on the length of restoration time.

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.

 

SQL> desc v$flashback_database_stat Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- BEGIN_TIME                                         DATE END_TIME                                           DATE FLASHBACK_DATA                                     NUMBER DB_DATA                                            NUMBER REDO_DATA                                          NUMBER ESTIMATED_FLASHBACK_SIZE                           NUMBER

Working principle of flash back database

To flash back to the database, Oracle needs to record the foreground of the data block as before image to a log, which is named as flashback database logs flash back log. When a data block is modified for the first time, the foreground process copies the before image of the data block to the flashback log buffer in the shared pool, the RVWR Process regularly refreshes records in the flashback log buffer to the disk. Before the DBWR process can write dirty data blocks to the disk, DBWR must ensure that the Flashback log buffer of the buffer header-related FBA (flashback Byte Address) has been written into the flash log. This is called the write-back logging mechanism. In conventional maintenance operations, the RVWR Process regularly inserts flashback markers into the flashback database logs. Flashback markers is used to notify Oracle of How to flashback to a previous time point in the database. During the execution of the flash back operation, Oracle will use the information in the flash back mark (flashback markers) to determine the scope of the flashback database log to be used to restore the data block scene block image; oracle then uses the forward recovery Method to traverse the database to the user-specified SCN or time point. Note that every block change in the database Triggers before image to be recorded in the flashback log. If flashback log record is recorded for each block change, the flashback log will be much larger than the redo log! For hot block hot blocks, Oracle records the block image to the flash back log only once within a period of time. Oracle implements this through the flashback barriers. In a conventional database, flashback barriers is periodically triggered (generally 15 minutes). One flashback barriers is written to the flashback log for each flashback barriers. Under normal conditions, only one block image is recorded for each modified data block in a flashback region (the region separated by barriers, no matter how many times the data block has been modified in this region and how many times it has been written to the disk.

When a flash is performed, the system first restores to the corresponding target time, that is, a previous image, and then re-applies the redo log according to the redo log, recover data to the specified flash back time point. It can be seen from this that the flash back database requires redo log and archive log support.

Limitations on flash back database

1: Flash back database cannot be used for media recovery

2: The Flash back database cannot be used to restore data files that use the shrink technology or deleted data files.

3: You cannot flash back to the database from the reconstruction control file or the recovered control file, because the flash log information in the control file has been expired and discarded.

4: we cannot use the flashback database to flash the database back to the time point for performing the nologging operation, because it may cause rapid data damage.

Simulate the control file recovery scenario:

[oraele@vm1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 27 09:13:12 2015Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> recover database using backup controlfile;ORA-00279: change 1138457 generated at 01/26/2015 16:10:27 needed for thread 1ORA-00289: suggestion :/u01/app/oraele/fast_recovery_area/ORCL/archivelog/2015_01_27/o1_mf_1_1_%u_.arcORA-00280: change 1138457 for thread 1 is in sequence #1Specify log: {<RET>=suggested | filename | AUTO | CANCEL}/u01/app/oraele/oradata/orcl/redo01.logLog applied.Media recovery complete.SQL> alter database open resetlogs;Database altered.SQL> select * from test;        ID SCN---------- --------------------         0 1121575         1 1126681         2 1138419         3 1150711SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area  267227136 bytesFixed Size                  2227504 bytesVariable Size             192938704 bytesDatabase Buffers           67108864 bytesRedo Buffers                4952064 bytesDatabase mounted.SQL> flashback database to scn 1138419;flashback database to scn 1138419*ERROR at line 1:ORA-38729: Not enough flashback database log data to do FLASHBACK.SQL> desc v$flashback_database_log Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- OLDEST_FLASHBACK_SCN                               NUMBER OLDEST_FLASHBACK_TIME                              DATE RETENTION_TARGET                                   NUMBER FLASHBACK_SIZE                                     NUMBER ESTIMATED_FLASHBACK_SIZE                           NUMBERSQL> select olddest_flashback_scn from v$flashback_database_log;select olddest_flashback_scn from v$flashback_database_log       *ERROR at line 1:ORA-00904: "OLDDEST_FLASHBACK_SCN": invalid identifierSQL> c/oldd/old  1* select oldest_flashback_scn from v$flashback_database_logSQL> /OLDEST_FLASHBACK_SCN--------------------             1150901SQL>

SQL> select begin_time from v$flashback_database_stat;BEGIN_TIM---------27-JAN-15

When using the flash back database technology or other flash back technology, we need to specify the scn or time point to flash back. To simplify the work, we can create a restroe point to work with the database flash back.

Restore point can be divided into normal restorepoint and guaranteed restore point. Normal restore point is used only as the alias of scn and time point. It has no other purposes and occupies a very small amount of space in the control file. Generally, you do not need to pay special attention to it. The control file automatically clears the information of the normal restore point. The following rules are followed when clearing the control file:

(1) The most recent 2048 restorepoints are always kept in the control file, regardless of their age.

(2) Any restore point more recentthan the value of CONTROL_FILE_RECORD_KEEP_TIME is retained, regardless of how many restore points are defined.

Guaranteed restore point is also used as the alias of scn and time point, but the control file does not automatically clear guaranteed restore point. It must be manually deleted. In addition, without enabling the flashback database, the guaranteed restore point still allows us to use the flashback database technology, but now we can only flash back to the time point specified by the guaranteed restore point. At this time, the flash log will still be generated, but the flash log only stores the "before image" when the data block changes for the first time after the guaranteed restore point ".

We know that the flashback database and nologging support is not very good, and may cause bad blocks (the pre-image must be redo to restore Data intermittently ). However, if the flashback database is not enabled, the guaranteed restore point can support nologging, because redo log data is not required at this time. When flashback database log is enabled, If you create a guaranteed restore point, you can ensure that the database can flash back at any time point from the guaranteed restore point to the current time. If you enableFlashback Database and define one or more guaranteed restore points, then thedatabase performs normal flashback logging.

Flashback database can be disabled for a tablespace, for example:

SQL> alter database flashback on;Database altered.SQL>  alter tablespace users flashback off;Tablespace altered.SQL> create table test1 tablespace users as select * from test;Table created.SQL> insert into test values(1,1);1 row created.SQL> commit;Commit complete.SQL> insert into test1 select * from test;1 row created.SQL> commit;Commit complete.SQL> select current_scn from v$database;CURRENT_SCN-----------    1188094SQL> insert into test select * from test;1 row created.SQL> commit;Commit complete.SQL> insert into test1 select * from test1;1 row created.SQL> commit;Commit complete.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area  267227136 bytesFixed Size                  2227504 bytesVariable Size             192938704 bytesDatabase Buffers           67108864 bytesRedo Buffers                4952064 bytesDatabase mounted.SQL> flashback database to scn  2   1188094;flashback database to scn*ERROR at line 1:ORA-38753: Cannot flashback data file 4; no flashback log data.ORA-01110: data file 4: '/u01/app/oraele/oradata/orcl/users01.dbf'SQL> alter tablespace users offline;alter tablespace users offline*ERROR at line 1:ORA-01109: database not openSQL> alter tablespace users read only;alter tablespace users read only*ERROR at line 1:ORA-01109: database not openSQL> alter database open;Database altered.SQL> alter tablespace users offline;Tablespace altered.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area  267227136 bytesFixed Size                  2227504 bytesVariable Size             192938704 bytesDatabase Buffers           67108864 bytesRedo Buffers                4952064 bytesDatabase mounted.SQL> flashback database to scn 1188094;Flashback complete.SQL> alter database open resetlogs;Database altered.SQL> alter tablespace users online;alter tablespace users online*ERROR at line 1:ORA-01190: control file or data file 4 is from before the last RESETLOGSORA-01110: data file 4: '/u01/app/oraele/oradata/orcl/users01.dbf'

The tablespace of flashback off must be offline (not involved in flashback) before it is flashed back to the database. The database cannot be opened before the inconsistency of all data files scn.
Some offline data files must be restored and incomplete before going online, but time can be saved.