Shulou(Shulou.com)06/01 Report--

In Oracle, data is usually stored in data files, but one of the biggest differences between database and Oracle is that the database can be recovered when the data goes wrong. This is the function of redo log (REDO FILE) in our common Oracle. The redo log is divided into two parts, one is the online redo log file, and the other is the archive log file.

Instead of detailing online redo logs, let's talk about archived logs (Archive Log). After all, the size of the online redo log is limited, and when it is full, we are faced with two choices: the first is to erase the previous online redo log from scratch and continue to write, and the second is to back up the previous online redo log first, and then erase the backed up log and start writing a new online Redo File. The online redo log for this backup is the archive log. If the database adopts this mode of generating archived logs, it is archived log mode (ARCHIVELOG mode), on the contrary, if archived log is not generated, it is non-archived log mode (NOARCHIVELOG mode).

What's the advantage of having archived logs? For example, on the 1st of this month, the data was backed up once, and then after 10 days, hundreds of online redo logs were generated in these 10 days, and suddenly found that one of the data disks was out of order and could not be used. What should I do?

If archive logs are not used, there will actually be only a few of the latest online redo logs on disk. Then I can only or else delete all the tablespaces occupied on the data disk in question. But if there is an error on the disk involved in the SYSTEM tablespace, there is no way to do this, only the second way. The second method is to take out the data from backup No. 1 and restore it. Then the 10-day data between the 1st and the 10th have been lost. If it is a key system, such as securities and finance, it will make you lose money and die.

However, if you have archived logs, your 10-day redo logs will be stored. Then DBA first restores the backup data of No. 1, and then replays the data operations with these 10-day REDO logs, then the latest database can be completely restored without any consequences.

In software development, due to the limited configuration of the test server, especially the limited disk space, it is possible to limit the size of the REDO file, so it is possible to set the system to NOARCHIVELOG mode. But in the actual production environment, we must basically use the ARCHIVELOG mode, otherwise if something goes wrong, it will be too late to cry.

Some people may be afraid of performance loss caused by archiving logs. In fact, this worry is completely unfounded, archiving logs is just to do a backup, in fact, it is just to consume more disk space. In the current software system, the storage capacity cost of hard disk is so low that it can be ignored, and the most important thing is the security of database. The job of DBA is to ensure the security of the data, and if you can't even guarantee it, what's the use of a slight performance improvement?

Archive logs (Archive Log) are inactive redo log backups. By using archive logs, you can keep all redo history records. When the database is in ARCHIVELOG mode and log switching is performed, the background process ARCH will save the contents of the redo logs to the archive log. When there is a media failure in the database, the database can be completely restored by using data file backup, archive log and redo log.

Log operation mode: ARCHIVELOG NOARCHIVELOG

1. Change the log operation mode:

Check the current log mode of operation

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

ARCHIVE LOG START

ALTER DATABASE OPEN

2. Perform manual archiving

Starting from oracle database 10g, oracle automatically starts the ARCH process when the log operation mode is changed to non-ARCHIVELOG mode. If you want to use manual filing. Then the command ALTER DATABASE ARCHIVELOG MANUAL. Log must be used when changing the log operation mode.

It should be noted that using manual archiving, the database administrator must manually execute archiving commands. If the manual archiving command is not executed, the original contents of the log group cannot be overwritten. The ALTER DATABASE ARCHIVELOG MANUAL command is retained for compatibility with previous versions. This command will be obsolete in future versions of oracle, and using manual archiving, the database administrator can archive the redo logs by executing the following command:

SLTER SYSTEM ARCHIVELOG ALL

3. Configure the archiving process

The initialization parameter LOG_ARCHIVE_MAX_PROCESSES is used to specify the maximum number of archiving processes initially started by the routine. When the database is converted to ARCHIVELOG mode, oracle automatically starts two archiving processes by default. By changing the value of the initialization parameter LOG_ARCHIVE_MAX_PROCESS, you can dynamically increase or decrease the number of archiving processes:

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3

Configure archive location and file format

When the database is in ARCHIVELOG mode, if you switch logs, the background process will automatically generate archive logs, the default location of archive logs is% oracle_home%rdbms, and in oracle database 10g, the default file format of archive logs 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 of the archive log. When you set this initialization parameter, you can specify the following matches:

% s: log serial number:

% S: log serial number (with leading 0)

% t: redo thread number.

% T: redo thread number (with leading 0)

% a: active ID number

% d: database ID number

ID value of% r RESETLOGS.

Starting from 10g, the archive log file format must be configured with the% sdepartment% t and% r matches. after configuring the archive file format, the database must be restarted.

2, configure the archive location using LOG_ARCHIVE_DEST

If you do not use an alternate database, you only need to store the archive logs in the local directory. The initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST can be used to configure the local archive location, where the first parameter is used to set the first archive location and the second parameter is used to specify the 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.

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 initialization parameter LOG_ARCHIVE_DEST_n, you can configure not only the local archive location but also the remote archive location.

If you want to generate archive logs on both the primary node and the standby node, you must use the parameter LOG_ARCHIVE_DEST_n. Log. This parameter differs from LOG_ARCHIVE_DEST as follows

Initialization parameter LOG_ARCHIVE_DEST_n can configure local archive location and remote archive location, while initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST can only configure local archive location.

The initialization parameter LOG_ARCHIVE_DEST_n can configure up to 10 archive locations, while the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST can only configure up to two archive locations.

The initialization parameter LOG_ARCHIVE_DEST_n cannot be used with the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST.

Because the initialization parameter LOG_ARCHIVE_DEST_n cannot be used with the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST, the initialization parameters LOG_ARCHVE_DEST and LOG_ARCHIVE_DUPLEX_DEST must be disabled. When configuring a local archive location with the initialization parameter LOG_ARCHIVE_DEST_n, you need to specify the LOCALTION option. When configuring a 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 needs to specify the network service name of the remote database (configured in the tnsnames.ora file)

4, use the LOG_ARCHIVE_DEST_n option

When configuring an archive location with the initialization parameter 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 is the default option. When using this option, you can overwrite the redo log regardless of whether the archive is successful or not.

MANDATORY: mandatory archiving. With this option, the redo log can be overwritten only after the archive is successful.

REOPEN: this attribute is used to specify the time interval for re-archiving. The default value is 300 seconds, which must be followed by MANDATORY.

Example:

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 the initialization parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to control the minimum number of successes for local archiving

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. Set this parameter to ENABLE (default) to activate the corresponding archive location, and set this parameter to DEFER to disable the corresponding archive location. When the disk where the archive log is located is damaged or full, 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)

Display archive log information

1. Use the ARCHIVE LOG LIST command to display the log operation mode, archive location, log sequence number to be archived by the automatic archiving machine and other information.

2 display log operation mode

SELECT name,log_mode FROM v$database

3, display archive log information.

Col name format a40

Select name, swquence#, first_change# FROM v$archived_log

Name is used to represent the file name of the archive log, 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 of the archive log.

When performing media recovery, you need to use archive log files, which must accurately locate the location of the archive log. 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 the occurrence time of the initial SCN. SWICTH _ CHANGE# is used to identify the SCN value of log switching.

6. Displays archiving process information.

During log switching, the ARCH process will automatically copy the contents of the redo log to the archive log. In order to speed up the archiving, multiple ARCH processes should be enabled. You can display information about all archiving processes by querying the dynamic performance view V$ARCHIVE_PROCESSES!

SELECT * FROM v$archive_processes

Porcess is used to identify the number of the ARCH process, status is used to identify the status of the ARCH process (ACTIVE: active, STOPPED: not started), log_sequence is used to identify the log sequence number that is being archived, and state is used to identify the working status of the ARCH process.

Description: finishing and networking

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.