Archive log in Oracle (Archive log) ____oracle

Archive Logs in Oracle (Archive log)

article Category: Database

In Oracle, data is typically stored in data files, but one of the biggest differences between databases and Oracle is that databases can recover when data is wrong. This is also the function of the redo log (REDO FILE) in our common Oracle. The redo log is divided into 2 parts, one is the online redo log file, the other is the archive log file.

The online redo log is not detailed here, but the archive log (Archive log) is described. The online redo log size is limited after all, when all is written, faced with 2 choices, the first is to erase the previous online redo log from scratch to continue writing, the second is to the previous online redo log backup first, and then to be backed up log erase began to write a new online redo File. The online redo log of this backup is the archive log. In the case of a database that generates an archive log, it is the archive log mode (Archivelog mode), whereas if you do not generate an archive log, it is a non-archived log mode (Noarchivelog mode).

What's the benefit of having an archive log? For example, at 1th this month when the backup of the data, and then 10 days, this 10 days generated hundreds of online redo logs, suddenly found that one of the data disk problems, can not be used, then I should be good.

If an archive log is not used, there will be only a few of the latest online redo logs in the disk. Then I can only get rid of the table space occupied by the problem data disk. However, if the disk involved in the system table space errors, there is no way to do so, only the second method. The second method is to recover the data from the backup number 1th. Then 1th to 10th between the 10 days of data are lost, if it is a key system, such as securities and financial systems, you will lose money to compensate for death.

But if you have an archive log, then your 10 days of redo log will be stored, then the DBA first to restore the backup data 1th, and then take the 10-day redo log to do a data operation replay, then you can fully recover the latest database, there will be no consequences.

In software development , because the test server configuration is limited, especially disk space is limited, so it is possible to limit the size of the redo file, it is possible to set the system to Noarchivelog mode. But in the actual production operation environment, basically must use Archivelog mode, otherwise, if there is a problem, it is too late to cry.

Some people may be afraid of the loss of performance due to archived logs. In fact, this is completely unfounded, the archive log is just a backup, in fact, it is more to consume some disk space. In the current software system, the cost of storage capacity of hard disk has been low to negligible, and the most important is the security of the database. The DBA's task is to ensure that the data is secure, and if security is not guaranteed, what is the point of the minimal performance improvement?

Archive log (Archive log) is a inactive redo log backup. By using an archive log, you can keep all redo histories, and when the database is in Archivelog mode and log-switched, Background process Arch Saves the contents of the redo log to the archive log. When a media failure occurs in a database, a data file backup is used, and the archive log and redo log can fully recover 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 load 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 launches the arch process when the log operation mode is converted 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 the database administrator must manually execute the archive command by using a manual archive. If the Manual archive command is not executed, 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 eliminate the command, and by using the manual archiving method, the database administrator can perform the following command to archive the redo log:

Slter SYSTEM Archivelog All;


3, configure the archive process

Initialization parameters log_archive_max_processes The maximum number of archive processes that are used to specify the initial start of a routine, and Oracle automatically launches two archive processes by default when the database is converted to Archivelog mode. By changing initialization parameters Log_ Archive_max_process value that 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 a log switch occurs when the database is in Archivelog mode, the background process automatically generates an archive log, the default location for the archive log is%oracle_home%rdbms, and in Oracle database 10g the default file format for the archive 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, initialization parameter log_archive_format is used to specify the file name format of the archive log, and when you set the initialization parameter, you can specify the following match characters:

%s: Log sequence 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 the 10g, the archive log file format must be configured with%s,%t and%r matches, and the archive file format must be restarted after the database has been configured.


2, using Log_archive_dest to configure the archive location

If you do not use an alternate database, you only need to store the archive log in the local directory. To configure the local archive location you can use 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 is used to specify a second archive location.

ALTER SYSTEM SET log_archive_dest= ' d:demoarchive1 ';

ALTER SYSTEM SET log_archive_duplex_dest= ' d:demoarchive2 ';

3, use Log_archive_dest_n to configure multiple archive locations.

Initialization parameter log_archive_dest_n is used to specify multiple archive locations, which can specify up to 10 archive locations. By using initialization parameters Log_archive_dest_n, you can configure both the local archive location and the remote archive location.

If you want to generate an archive log on the primary node and then pass the archive log to the standby node, you must use the parameter log_archive_dest_n. The parameter and the log_archive_dest have the following difference;

Initialization parameters Log_archive_dest_n can be configured with local archive locations and remote archive locations, while initialization parameters log_archive_dest and Log_archive_duplex_dest can only be configured with 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 be configured with up to two archive locations.

Initialization parameter Log_archive_dest_n cannot be used concurrently with initialization parameters Log_archive_dest and log_archive_duplex_dest.

Because initialization parameters Log_archive_dest_n cannot be used concurrently 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 you use initialization parameters Log_archive_dest_n Configure a local archive location, you specify the localtion option. You need to specify the service option when configuring a remote archive location.


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 needs to specify the network service name for the remote database (configured in the Tnsnames.ora file)


4, using the Log_archive_dest_n option

When you configure an archive location using initialization parameters Log_archive_dest_n, you can specify optional or mandatory options on 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 was successful or not.

Mandatory: Mandatory archiving. When you use this option, the redo log is overwritten only after the archive succeeds.

REOPEN: This property is used to specify the time interval for archiving, the default value is 300 seconds, and must be followed by 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 archive.

Using initialization parameters Log_archive_min_succeed_dest control the minimum number of successes for local archives

Alter system set log_archive_min_succeed_dest=2;


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

Alter system set Log_archive_dest_state_3=defer; (disabled)

Alter system set log_archive_dest_state_3=enable; (enabled)


displaying archived log information

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


2 Display log operation mode

SELECT Name,log_mode from V$database;


3, display archived log information.

Col name format A40

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, firs_change# is used to identify the starting SCN value of the archive log.


4 When performing media recovery, you need to use an archive log file, which must pinpoint where the archive log resides. Query dynamic performance View V$archive_dest can obtain the directory where the archive logs reside.

SELECT destination from v$archive dest;


5, displaying 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# to identify the start SCN value corresponding to the log sequence number, first_time to identify when the start SCN occurred. swicth_change# The SCN value used to identify the log switch.


6. Display archive process information.

When a log switch is made, the arch process automatically copies the redo log contents to the archive log, and multiple arch processes should be enabled in order to speed up the archive. By querying dynamic performance View v$archive_processes can display information for all archive processes!

SELECT * from V$archive_processes;

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