3.3 configure other archive Parameters

When the database is in archivelog mode and logs are switched, the background process arch automatically generates archive logs. Starting from Oracle Database 10 Gb, archive logs are stored in the directory corresponding to the recovery area (corresponding to the initialization parameter db_recovery_file_dest) by default, and archive log file names are generated according to the specific format. To change the location and name format of archived logs, you must change the corresponding initialization parameters.

1. Configure the name format of the archive log

The initialization data log_archive_format is used to specify the file name format of the archived log. When this initialization parameter is set, you can specify the following matching characters:

• % S: log serial number
• % S: log serial number (with leading 0)
• % T: Number of the redo thread
• % T: Number of the redo thread (with leading 0)
• % A: Activity ID
• % D: Database ID
• % R: ID value of resetlogs

Starting from Oracle Database 10g, When you configure the name format of the archive log, the matching characters % s, % T, and % R are required. Because the initialization parameter log_archive_format is a static parameter, you must restart the database after modification. If the archived log is stored in the quick recovery area, the file name of the archived log is not in the format of log_archive_format.

SQL> alter system set log_archive_format = '% S _ % T _ % R. log' scope = spfile;

The system has been changed.

SQL> Shutdown
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
SQL> startup
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1247900 bytes
Variable Size 75498852 bytes
Database buffers 88080384 bytes
Redo buffers 2945024 bytes
The database has been loaded.
The database has been opened.

 

2. Use log_archive_dest_n to configure the archive location

The initialization parameter log_archive_dest_n is used to specify multiple archiving locations, where n is an integer between 1 and 10. By using this parameter, you can configure not only the local archiving location, but also the remote archiving location.

If you want to generate archive logs on the master node (the master database machine) and transfer the archive logs to the slave node (the backup database machine), you must configure the archive location using the log_archive_dest_n parameter.

• The differences between log_archive_dest_n, log_archive_dest, and log_archive_duplex_dest are as follows:
• Log_archive_dest_n can be used to configure the local and remote archiving locations. log_archive_dest and log_arvhive_duplex_dest can only be used to configure the local archiving location.
• Log_archive_dest_n can be configured with up to 10 archiving locations. log_archive_dest and log_archive_duplex_dest can be configured with up to two archiving locations.
• Log_archive_dest_n cannot be used with log_archive_dest and log_archive_duplex_dest.

When you use log_archive_dest_n to configure the local archiving location, you need to use the Location Option to specify the local directory. When configuring the remote archiving location, you need to use the service option to specify the network service name to the standby database.

SQL> alter system set log_archive_dest_1 = 'location = c: \ demo \ archive ';

The system has been changed.

SQL> alter system set log_archive_dest_2 = 'location = D: \ demo \ archive ';

The system has been changed.

SQL> alter system set log_archive_dest_3 = 'service = orcl ';

The system has been changed.

The preceding statement configures two local archiving locations (Location Option) and one remote archiving location (Service option ). When switching logs, two identical archive logs (mirror each other) are generated on the master node, and an archive log is transferred to the slave node.

 

3. Use other common options of log_archive_dest_n

When you use the initialization parameter log_archive_dest_n to configure the archive location, you can specify the optional or mandatory option on the archive location. When the mandatory option is specified, you can also set the reopen attribute.

Optional: This option is the default option. When this option is used, the redo log can be overwritten no matter whether the archiving is successful or not.

Mandatory: This option is used to force archiving. When this option is used, the redo log can be overwritten only after the archive is successful.

Reopen: Specifies the rearchive interval. The default value is 300 seconds (the reopen attribute must follow the mandatory option ).

SQL> alter system set log_archive_dest_1 = 'location = c: \ demo \ archive optional ';

The system has been changed.

SQL> alter system set log_archive_dest_2 = 'location = D: \ demo \ archive mandatory reopen = 100 ';

The system has been changed.

 

4. Use log_archive_dest_state_n to control the archiving location

If the remaining space of the disk corresponding to the archive location is insufficient or damaged, archiving to this location will fail. To solve disk problems, you may need to temporarily disable the corresponding archiving location. You can use the initialization parameter log_archive_dest_state_n (n = 1, 2... 10) to control the availability of the archive location. The value can be enable or defer ).

SQL> alter system set log_archive_dest_state_1 = Enable;

The system has been changed.

SQL> alter system set log_archive_dest_state_2 = Enable;

The system has been changed.

SQL> alter system set log_archive_dest_state_3 = defer;

The system has been changed.

 

5. Use log_archive_max_processes to control the archiving process

The initialization parameter log_archive_max_processes is used to control the number of archiving processes. When the database is in archivelog mode, Oracle starts two archiving processes by default.

SQL> alter system set log_archive_max_processes = 3;

The system has been changed.

 

6. Use log_archive_min_succeed_dest to control the minimum number of successful local archives

SQL> alter system set log_archive_min_succeed_dest = 2;

The system has been changed.

After executing the preceding statement, if there are less than two archived logs generated locally, the redo log cannot be overwritten.

 

7. Use log_archive_dest to configure the archive location

If no backup database is used, you only need to store the archived logs in the local directory. You can use the initialization parameters log_archive_dest and log_archive_duplex_dest to configure one or two local archiving locations. The first parameter is used to specify the first archiving location, the second parameter is used to specify the second archive location (log_archive_dest and log_arvhive_duplex_dest are reserved for early compatibility and cannot be used together with log_archive_dest_n ).

SQL> alter system set log_archive_dest = 'd: \ demo \ archive1 ';
Alter system set log_archive_dest = 'd: \ demo \ archive1'
*
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 = 'd: \ demo \ archive2 ';
Alter system set log_archive_duplex_dest = 'd: \ demo \ archive2'
*
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