New Feature of Oracle10g: Flash recovery area)

 

New Feature of Oracle10g: Flash recovery area)

 

Oracle9i starts to provide a flashback query so that consistent data at a time point in the past can be found as needed, which is implemented through undo. This function has a lot of limitations, that is, the Undo of related transactions cannot be overwritten, otherwise it will be unable to return to the day. Oracle10g greatly enhances the flash-back query function and provides the ability to roll back the entire database to a time point in the past. This is achieved by introducing a new flashback log. The flashback log is a bit similar to the redo log, except that the redo log rolls the database forward, and the flashback log rolls the database back. To save files related to management and Backup recovery, Oracle10g provides a flashback
New features of the recovery area). All recovery-related files, such as flashback log, archive log, and backup set, can be stored in this area for centralized management.

 

1. Set the flash recovery zone

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. Therefore, this parameter must be modified with db_recovery_file_dest_size.

 

2. Start flashback Database

After the flash recovery area is set, you can start the flash back database function.

 

First, the database must be in archive mode.

SQL> archive log list

Database Log mode archiving Mode

Enable automatic archiving

Archiving end point use_db_recovery_file_dest

Oldest online log sequence 245

Next archive log sequence 247

Current Log sequence 247

 

Then, start the database to the Mount status

SQL> shutdown immediate;

The database has been closed.

The database has been detached.

The Oracle routine has been disabled.

 

SQL> startup Mount;

The Oracle routine has been started.

Total system global area 142606336 bytes

Fixed size 1247732 bytes

Variable Size 83887628 bytes

Database buffers 50331648 bytes

Redo buffers 7139328 bytes

The database has been loaded.

 

SQL> alter database flashback on;

The database has been changed.

SQL> alter database open;

The database has been changed.

SQL> select flashback_on from V $ database;

Flashback_on

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

Yes

The flashback database function will not be further explored here.

 

3. Cancel the flash recovery zone

Set db_recovery_file_dest to null to disable the flash recovery zone.

If flashback database is enabled, you cannot cancel flashing back to the recovery zone.

SQL> alter system set db_recovery_file_dest = '';

Alter system set db_recovery_file_dest =''

*

Row 3 has an error:

ORA-02097: The parameter cannot be modified because the specified value is invalid

ORA-38775: Unable to disable quick recovery zone-Flashback database enabled

 

Therefore, you must disable the flashback database before canceling the flashback recovery zone.

 

SQL> shutdown immediate;

The database has been closed.

The database has been detached.

The Oracle routine has been disabled.

 

SQL> startup Mount;

The Oracle routine has been started.

Total system global area 142606336 bytes

Fixed size 1247732 bytes

Variable Size 83887628 bytes

Database buffers 50331648 bytes

Redo buffers 7139328 bytes

The database has been loaded.

 

SQL> alter database flashback off;

The database has been changed.

 

SQL> alter database open;

The database has been changed.

 

SQL> alter system set db_recovery_file_dest = '';

The system has been changed.

 

4. Content of the flash recovery area

All recovery-related files can be stored in the Flash recovery zone.

SQL> select file_type from V $ flash_recovery_area_usage;

File_type

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

Controlfile

Onlinelog

Archivelog

Backuppiece

Imagecopy

Flashbacklog

You have selected 6 rows.

 

As shown in the preceding view, including controfile, online redo logfile, archive logfile, and RMAN backup

Piece, RMAN image copy, and flashback log can all be stored and managed in the Flash recovery zone.

 

5. Restrictions on the flash recovery zone

If the flash recovery zone is set, log_archive_dest and log_archive_duplex_dest are unavailable.

 

Alter system set log_archive_dest = 'e :/'

*

Row 3 has an error:

ORA-02097: The parameter cannot be modified because the specified value is invalid

ORA-16018: unable to match log_archive_dest with log_archive_dest_n or

Db_recovery_file_dest

SQL> alter system set log_archive_duplex_dest = 'e :/';

Alter system set log_archive_duplex_dest = 'e :/'

*

Row 3 has an error:

ORA-02097: The parameter cannot be modified because the specified value is invalid

ORA-16018: Unable to replace log_archive_duplex_dest with log_archive_dest_n or

Db_recovery_file_dest

 

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.

 

SQL> archive log list

Database Log mode archiving Mode

Enable automatic archiving

Archiving end point use_db_recovery_file_dest

Oldest online log sequence 245

Next archive log sequence 247

Current Log sequence 247

 

In fact, Oracle is implemented by implicitly setting log_archive_dest_10 = 'location = use_db_recovery_file_dest. Therefore, if you have modified log_archive_dest_n to save the archived log to another location, you can also modify this parameter to continue to use the flash recovery zone.

 

The Flash recovery zones of multiple databases 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 on a shared disk and can be accessed by all instances.

 

6. space management in the Flash recovery zone

When the flash recovery area is insufficient (more than 85% space is used), alert will have a warning record

Tue Dec 19 10:45:41 2006

Errors in file E:/Oracle/ora10/admin/Ning/bdump/ning_rvwr_31968.trc:

ORA-19815: Warning: db_recovery_file_dest_size bytes (52428800 bytes in total) are 87.29% in use and 6665216 bytes are available.

 

At the same time, Oracle also provides suggestions to solve the problem in alert.

Tue Dec 19 10:45:41 2006

**************************************** ********************************

You have following choices to free up space from flash recovery area:

1. Consider changing RMAN retention policy. If you are using data guard,

Then consider changing RMAN archivelog deletion policy.

2. Back Up Files to tertiary device such as tape using RMAN

Backup recovery area command.

3. Add disk space and increase db_recovery_file_dest_size parameter

Reflect the new space.

4. Delete unnecessary files using rman delete command. If an operating

System Command was used to delete files, then use RMAN crosscheck and

Delete expired commands.

**************************************** ********************************

 

If the space in the Flash recovery area is exhausted and the archiving path is set to the Flash recovery area, the database will hang because the logs cannot be archived.

 

Tue Dec 19 10:45:57 2006

Errors in file E:/Oracle/ora10/admin/Ning/bdump/ning_arc0_32372.trc:

ORA-19815: Warning: db_recovery_file_dest_size bytes (52428800 bytes in total) are 100.00% in use and 0 bytes are available.

 

Tue Dec 19 10:45:57 2006

Errors in file E:/Oracle/ora10/admin/Ning/bdump/ning_arc0_32372.trc:

ORA-19809: exceeds the limit on the number of recovery files

ORA-19804: unable to reclaim 6836224 bytes of disk space (from 52428800 limit)

 

Arc0: Error 19809 creating archive log file

'E:/Oracle/ora10/flash_recovery_area/Ning/archivelog/2006_12_19/o1_mf_1_250_u_.arc'

Arc0: failed to archive thread 1 sequence 250 (19809)

Arch: archival stopped, error occurred. Will Continue retrying

Tue Dec 19 10:45:58 2006

Errors in file E:/Oracle/ora10/admin/Ning/bdump/ning_arc0_32372.trc:

ORA-16038: Log 1 serial number 250 cannot be archived

ORA-19809: exceeds the limit on the number of recovery files

ORA-00312: Online log 1 thread 1: 'e:/Oracle/ora10/oradata/Ning/redo01.log'

 

Therefore, for the production database, if you put the archive in the Flash recovery area, you need to pay close attention to the space usage in the Flash recovery area. Otherwise, once the flash recovery area is used up, the database cannot provide services.

 

Query the view v $ flash_recovery_area_usage to obtain the space usage of the current flash recovery zone.

Which files occupy space, so you can perform corresponding processing, increase the flash recovery area, or remove the corresponding files.

PHP code :--------------------------------------------------------------------------------

SQL> select * from V $ flash_recovery_area_usage;

File_type percent_space_used percent_space_reclaimable number_of_files

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

Controlfile 0 0 0

Onlinelog 0 0 0

Archivelog 91 0 16

Backuppiece 0 0 0

Imagecopy 0 0 0

Flashbacklog 8 4 2

You have selected 6 rows.

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

In addition, the V $ recovery_file_dest view also provides summary information about the flash recovery zone.

SQL> select * from V $ recovery_file_dest;

Name space_limit space_used space_reclaimable number_of_files

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