Oracle archive log configuration Query

Oracle Archive Log configuration query Archive Log is a non-active redo Log backup. archive logs can be used to keep all redo history records. When the database is in ARCHIVELOG mode and the log is switched, the background process ARCH will save the redo log content to the archive log. when a media failure occurs in the database, you can use data file backup, archive logs and redo logs to completely restore the database. log operation mode: archivelog noarchivelog 1. Change log operation mode: Check the current log operation mode SELECT log_mode from v $ database; close the database, then load the database shutdown immediatestartup mount to change the log operation mode, then OPEN the database alter database archivelog; ALTER database OPEN; 2, execute manual archiving from the oracle DATABASE 10 Gb, when the log operation mode is not In RCHIVELOG mode, oracle automatically starts the ARCH process. if you want to use manual archiving. when changing the log operation mode, you must run the alter database archivelog manual command. note that the database administrator must manually execute the archive command in the manual archiving mode. if the manual archiving command is not executed, the original content of the log group cannot be overwritten. the alter database archivelog manual command is retained for compatibility with previous versions ,. in future oracle versions, this command will be phased out. In the manual archiving mode, the database administrator can execute the following command to archive the redo log: alter system archivelog all; 3, configure the archive process initialization parameter LOG_ARCHIVE_MAX_PROCESSES to specify the maximum number of archive processes started by the routine. When the database is changed to ARCHIVELOG mode, oracle automatically starts two archive processes by default. by changing the initialization parameter 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 the archiving location and file format when the database is in ARCHIVELOG mode, if you switch the log, the background process automatically generates archived logs. The default location of archived logs is % oracle_home % rdbms. in oracle database 10 Gb, the default file format of archived logs is ARC % S _ % R. % T. to change the location and name format of the archived log, you must change the corresponding initialization parameter. 1. The initialization parameter LOG_ARCHIVE_FORMAT is used to specify the file name format of the archived log, you can specify the following match: % s: log serial number: % S: log serial number (with leading 0) % t: redo thread number. % T: redo thread number (with leading 0) % a: Activity ID % d: Database ID % r RESETLOGS ID value. starting from 10 Gb, When configuring the archive log file format, it must contain the % s, % t, and % r matching characters. After configuring the archive file format, you must restart the database. 2. Use LOG_ARCHIVE_DEST to configure the archive location. If no backup database is used, you only need to store the archive logs in the local directory. to configure the local archive location, you can use the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST. 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 archiving locations. the initialization parameter LOG_ARCHIVE_DEST_n is used to specify multiple archiving locations. You can specify up to 10 Archiving location. by using the initialization parameter LOG_ARCHIVE_DEST_n, you can configure the local archiving location and remote archiving location. if you want to generate archive logs on the master node and transfer the archive logs to the slave node, you must use the LOG_ARCHIVE_DEST_n parameter. this parameter is different from LOG_ARCHIVE_DEST. The initialization parameter LOG_ARCHIVE_DEST_n can be used to configure the local archive location and remote archive location. The initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST can only be used. the initialization parameter LOG_ARCHIVE_DEST_n can be configured with up to 10 archiving locations, while the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST can be configured with up to two archiving locations. the initialization parameters LOG_ARCHIVE_DEST_n cannot match the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIV. E_DUPLEX_DEST is used at the same time. because the initialization parameters 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 you use the initialization parameter LOG_ARCHIVE_DEST_n to configure the local archiving location, you must specify the LOCALTION option. when configuring the remote archiving location, you must specify the SERVICE option. example: 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_a Rchive_dest_2 = 'location = d: demoarchive2'; alter system set Partition = 'location = d: demoarchive3 '; alter system set log_archive_dest_4 = 'service = standby, the Network SERVICE name of the remote database (in tnsnames. (configured in the ora file) 4. Use the LOG_ARCHIVE_DEST_n option to configure the archive location using the initialization parameter LOG_ARCHIVE_DEST_n. You can specify the OPTIONAL or MANDATORY option on the archive location. you can set the REOPEN attribute when specifying the MANDATORY option. 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: Forced archiving. when this option is used, only Logs can be overwritten only after they are archived. REOPEN: This attribute is used to specify the time interval for archiving again. The default value is 300 seconds. It must be after MANDATORY. for example, Alter system set log_archive_dest_1 = 'location = d: demoarchive1 mandatory '; Alter system set log_archive_dest_2 = 'location = d: demoarchive2 mandatory reopen = 100 '; alter system set log_archive_dest_3 = 'location = d: demoarchive3 optional'; 5. Minimum number of successful local archives. use the initialization parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to control the minimum successful number of local archives. Alter system set log_archive_min_su Cceed_dest = 2; 6. Use the initialization parameter LOG_ARCHIVE_DEST_STATE_n to control the availability of the archive location. if this parameter is set to ENABLE (default), the corresponding archiving location is activated. If this parameter is set to DEFER, the corresponding archiving location is disabled. when the disk of the archived log is damaged or filled up, the DBA must 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 archived LOG information 1. Use the archive log list command to display the LOG operation mode and ARCHIVE location, the log serial number to be archived by the automatic archiving machine. 2. SELECT name, log_mode FROM v $ database; 3. The archived log information is displayed. col name format a46Sele Ct name, swquence #, first_change # FROM v $ archived_log; Name indicates the file name of the archived log, and sequence # indicates the log serial number corresponding to the archived log, firs_change # indicates the starting SCN value of the archived log. 4. archive log files are required for media recovery. Therefore, you must accurately locate the storage location of archived logs. you can query the dynamic performance view v $ archive_dest to obtain the directory where the archived logs are located. SELECT destination FROM v $ archive dest; 5, displays log History Information SELECT * FROM v $ loghist; THREAD # used to identify the redo THREAD number, SEQUNCE # used to identify the log serial number, FIRST_CHANGE # indicates the start SCN value corresponding to the log serial number, and FIRST_TIME indicates the occurrence time of the start SCN. SWICTH_CHANGE # The SCN value used to identify log switching. 6. displays archived process information. during log switching, the ARCH process automatically copies the redo log content to the archive log. To speed up archiving, multiple ARCH processes should be enabled. query the dynamic performance view V $ ARCHIVE_PROCESSES to display information about all archiving processes! SELECT * FROM v $ archive_processes; Porcess identifies the number of the ARCH process, and status identifies the status of the ARCH process (ACTIVE: ACTIVE, STOPPED: not started ), log_sequence is used to identify the log serial number being archived, and state is used to identify the working state of the ARCH process.