Oracle Archive log File Management

The archive log (Archive log) is a non-active redo log backup. By using archived logs, you can keep all the redo history, when the database is in Archivelog mode and log-switched, Background process Arch will save the contents of the redo log to the archive log. With data file backups, archive logs and redo logs to fully recover the database when media failures occur on the database.

Log operation mode: ARCHIVELOG Noarchivelog

1, change the log operation mode:

Check the current log operation mode

SELECT Log_mode from V$database;

Close the database, and then mount the database

SHUTDOWN IMMEDIATE
STARTUP MOUNT

Change the log operation mode, and then open the database

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

2, perform manual archiving

Starting with Oracle database 10g, Oracle automatically starts the arch process when the log operation mode is switched to Archivelog mode. If you want to use manual archiving. You must use the command ALTER DATABASE when changing the log operation mode. ARCHIVELOG MANUAL.

It is important to note that with manual archiving, the database administrator must manually execute the archive command. If you do not perform a manual archive command, the original contents of the log group cannot be overwritten. The ALTER DATABASE ARCHIVELOG MANUAL command is reserved for compatibility with previous versions. The future Oracle version will retire the command, using manual archiving, the database administrator can perform the following command to archive the redo log:

ALTER SYSTEM ARCHIVE LOG all;

3, configuring the archive process

The initialization parameter log_archive_max_processes is used to specify the maximum number of archive processes initially initiated by the routine, and Oracle automatically launches two archive processes by default when the database is turned into archivelog mode. By changing the initialization parameters Log_ The value of archive_max_process can dynamically increase or decrease the number of archive processes:

ALTER SYSTEM SET log_archive_max_processes=3;

Configure archive location and file format

When the database is in Archivelog mode, if log switching occurs, the background process automatically generates the archive log, the default location of the archive log is%oracle_home%rdbms, and in Oracle database 10g, the default file format for the archived log is Arc%s_ %r.%t. In order to change the location and name format of the archive log, the corresponding initialization parameters must be changed.

1, the initialization parameter log_archive_format is used to specify the file name format for the archive log, and when you set the initialization parameter, you can specify the following match characters:

%s: Log serial number:

%s: Log sequence number (with leading 0)

%t: Redo thread number.

%T: Redo thread Number (with leading 0)

%A: Activity ID Number

%d: Database ID number

The ID value of the%r resetlogs.

Starting with 10g, when you configure the archive log file format, you must have the%s,%t and%r matches, and after you configure the archive file format, you must restart the database.

2. Configure the archive location using Log_archive_dest

If you do not use a standby database, you only need to store the archive log to a local directory. Configure local archive locations You can use the initialization parameters Log_archive_dest and Log_archive_duplex_dest, where the first parameter is used to set the first archive location, The second parameter specifies a second archive location.

ALTER SYSTEM SET log_archive_dest= ' d:demoarchive1 ';

ALTER SYSTEM SET log_archive_duplex_dest= ' d:demoarchive2 ';

3, configure multiple archive locations using Log_archive_dest_n.

The initialization parameter log_archive_dest_n is used to specify multiple archive locations, which can specify up to 10 archive locations. By using the Initialize parameter log_archive_dest_n, you can configure not only the local archive location, but also the remote archive location.

If you need to generate an archive log on the master node and pass the archive log to an alternate node, you must use the parameter log_archive_dest_n. This parameter differs from the log_archive_dest as follows;

Initialization parameters Log_archive_dest_n can configure local archive locations and remote archive locations, while initialization parameters log_archive_dest and Log_archive_duplex_dest can only be configured for local archive locations.

Initialization parameters Log_archive_dest_n can be configured with up to 10 archive locations, while initialization parameters log_archive_dest and Log_archive_duplex_dest can only be configured with a maximum of two archive locations.

Initialization parameters Log_archive_dest_n cannot be used in conjunction with initialization parameters Log_archive_dest and log_archive_duplex_dest.

Because initialization parameters Log_archive_dest_n cannot be used in conjunction with initialization parameters Log_archive_dest and log_archive_duplex_dest, initialization parameters must be disabled log_archve_dest and Log_ Archive_duplex_dest. When configuring the local archive location using the initialization parameters Log_archive_dest_n, you need to specify the localtion option. When configuring the remote archive location, you need to specify the service option.

Examples are as follows:

ALTER SYSTEM SET log_archive_duplex_dest= ';

ALTER SYSTEM SET log_archive_dest= ';

ALTER SYSTEM SET log_archive_dest_1= ' location=d:demoarchive1 ';

ALTER SYSTEM SET log_archive_dest_2= ' location=d:demoarchive2 ';

ALTER SYSTEM SET log_archive_dest_3= ' location=d:demoarchive3 ';

ALTER SYSTEM SET log_archive_dest_4= ' Service=standby ';

When configuring a remote archive location, the service option requires specifying the network service name of the remote database (configured in the Tnsnames.ora file)

4, using the Log_archive_dest_n option

When you configure the archive location with the initialization parameters Log_archive_dest_n, you can specify the optional or mandatory option at the archive location. When you specify the mandatory option, you can set the reopen property.

OPTIONAL: This option is the default option. When you use this option, you can overwrite the redo log regardless of whether the archive is successful.

MANDATORY: Forced archiving. When you use this option, the redo log is overwritten only after the archive is successful.

REOPEN: This property is used to specify the time interval for re-archiving, the default value is 300 seconds, and must be followed after mandatory.

Cases:

Alter system set log_archive_dest_1= ' location=d:demoarchive1 mandatory ';
Alter system set log_archive_dest_2= ' location=d:demoarchive2 mandatory reopen=500 ';
Alter system set log_archive_dest_3= ' Location=d:demoarchive3 optional ';

5, control the minimum number of successful local archives.

Use initialization parameter log_archive_min_succeed_dest to control the minimum number of successful local archives

Alter system set log_archive_min_succeed_dest=2;

6, use the initialization parameter Log_archive_dest_state_n to control the availability of the archive location. Setting this parameter to enable (the default) means that the appropriate archive location is activated; Set this parameter to defer, Indicates that the appropriate archive location is disabled. When the archive log is on a disk that is damaged or filled, the DBA needs to temporarily disable the archive location.

Alter system set Log_archive_dest_state_3=defer; (disabled)
Alter system set log_archive_dest_state_3=enable; (enabled)

Show archived log Information

1, use the Archive log List command to display the log operation mode, archive location, automatic archiving machine to archive the log sequence number and other information.

2 Show log operation mode

SELECT Name,log_mode from V$database;

3, the archive log information is displayed.

Col name format A46
Select name, swquence#, first_change# from V$archived_log;

Name is used to represent the archive log file name, sequence# is used to represent the log sequence number corresponding to the archive log, and firs_change# is used to identify the starting SCN value for the archive log.

4, when performing media recovery, you need to use the archive log file, this four must accurately locate the archive log storage location. The directory where the archive log is located can be obtained by querying the dynamic performance view v$archive_dest.

SELECT destination from v$archive dest;

5, display log history information

SELECT * from V$loghist;

thread# is used to identify the redo thread number, sequnce# is used to identify the log sequence number, first_change# is used to identify the starting SCN value corresponding to the log sequence number, First_time is used to identify when the starting SCN occurred. swicth_change# The SCN value used to identify the log switchover.

6. Displays the archive process information.

The arch process automatically copies the redo log content to the archive log when the log is switched, and multiple arch processes should be enabled to speed up the archive. Query the dynamic performance view v$archive_processes to display information for all archive processes!

SELECT * from V$archive_processes;

The porcess is used to identify the number of the arch process, which identifies the state of the arch process (active: Active, STOPPED: Not started), and log_sequence is used to identify the log sequence number that is being archived. State is used to identify the working status of the arch process

Oracle Archive log File Management