Configure the flash recovery zone and multi-archive path of the Oracle database

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 new feature called Flashback recovery area, which allows you to restore all relevant files, such as flashback log, archive log and backup set are stored in this region 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.

So how to set up archiving? Simple Steps
1. Shut down the database
SQL> shutdown immediate;
2. Start the database in mount mode.
SQL> startup mount
3. Display and modify the archive Mode
SQL> archive log list
SQL> alter database archivelog;
SQL> alter database open
4. Set the format of archived logs
SQL> alter system set log_archive_format = 'arc % s % t % r. log' scope = spfile;
5. set the path for storing archived logs
SQL> alter system set log_archive_dest = '+ data/arcl' scope = spfile;
SQL> shutdown immediate
SQL> startup
6. Forcibly switch the archive day
SQL> alter system switch logfile;
7. Cancel Archiving
SQL> alter database noarchivelog;
 
Parameters
1. format parameters
% S log serial number
% S log serial number (with leading 0)
% T redo thread number
% A activity ID
% D Database ID
ID value of % r RESELOGS

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 156
Next log sequence to archive 158
Current log sequence 158

Then, start the database to the mount status
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 75499088 bytes
Database Buffers 205520896 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> alter database flashback on;
The database has been changed.
SQL> alter database open;
The database has been changed.
SQL> selectFLASHBACK_ONfrom v $ database;
FLASHBACK_ON
------------------------------------
YES

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

1 2 3 Next Page