Detailed analysis of flash back recovery in Oracle database

Oracle9i begins to provide a flashback query so that it can check for consistent data at some point in the past, as needed, through undo. There is a big limit to this function, that is, the undo of the related transaction cannot be overwritten, otherwise it will be powerless. ORACLE10G greatly enhances the functionality of the Flashback query and provides the ability to roll back the entire database to a certain time in the past, by introducing a new flashback log implementation.

Flashback log is a bit like redo log, but redo log rolls the database forward, flashback log rolls back the Oracle database. In order to save management and backup recovery related files, ORACLE10G provides a new feature called the Flashback Recovery Area (Flashback Recovery Zone) that can restore all related files, such as Flashback log,archive log, The backup set, etc., is centrally managed in this area.

1. Setting the Flashback Recovery area

The flashback recovery area is mainly set up and managed by 3 initialization parameters.

Db_recovery_file_dest: Specify the location of the Flashback recovery area

Db_recovery_file_dest_size: Specify the amount of free space in the Flashback recovery area

Db_flashback_retention_target: Specifies the time that the Oracle database can be rolled back, in minutes, by default of 1440 minutes, or one day. Of course, the amount of time that can actually be rolled back is also determined by the size of the flashback recovery area, since the Flash log required for fallback is saved. So this parameter should be modified with db_recovery_file_dest_size.

2. Start Flashback Database

After setting the Flashback recovery area, you can start the Flashback database function.

First, the database must already be in archive mode

So how do you set up an archive? It's a simple step.

1. Close the database

1. sql> shutdown immediate;

2. Start the Oracle database as Mount mode

1. Sql> Startup Mount

3. Display and modify the archive mode

1. Sql> Archive Log List
2. sql> ALTER DATABASE Archivelog;
3. Sql> ALTER DATABASE Open

4. Set the format of the archive log

1. Sql>alter system set log_archive_format= ' Arc%s%t%r.log ' scope=spfile;

5. Set the storage path for the archive log

1. Sql>alter system set log_archive_dest= ' +DATA/ARCL ' scope=spfile;
2. Sql>shutdown Immediate
3. Sql>startup

6. Force switch Filing date to

1. Sql>alter system switch logfile;

7. Canceling the archive

1. Sql>alter database Noarchivelog;

Parameters

1. Format parameters

%s Log sequence Number

%s Log sequence number (with leading 0)

%t Redo Thread Number

ID number of the%a activity

%doracle Database ID Number

ID value of%r reselogs

1. sql> archive log list;
2. Database Log Mode Archive mode
3. Automatic Archival Enabled
4. Archive Destination Use_db_recovery_file_dest
5. Oldest online log sequence 156
6. Next log sequence to archive 158
7. Current log Sequence 158

Then, start the Oracle database to Mount state

1. sql> shutdown immediate;   
2. database closed.  
3. Database  dismounted.  
4. oracle instance shut down.  
5. sql> startup mount  & nbsp;
6. oracle instance started.  
7. total system global area 285212672 bytes  
8. fixed size 1218992 bytes  
9. variable size 75499088 bytes  
10. database buffers 205520896 bytes  
11. redo buffers 2973696 bytes  
12. database mounted.  
13. sql>alter database flashback on;