9. Oracle Archive Log

Reprinted from: http://blog.csdn.net/leshami/article/details/5784713

Oracle can save online log files to many different locations, and the process of converting online logs to archived logs is called archiving. The corresponding log is called the archive log.

First, archive the log

is a copy of the online redo log group file that contains the redo record and a unique log sequence number to archive one of the journal files in the log group, and if one of the groups is corrupted, another available log will be archived for log switching for archive mode, The next log can be overwritten or re-use the Auto-archive feature, such as on, the background process ARCN automatically complete the archive on log switchover, otherwise you need to manually archive the archived log usage to    recover the database    updates standby database    use Logminer Extract information about the history log

Ii. two modes of logging

--1. Non-archival mode is not applicable to the production database when the database is created, the default log management mode is non-archive mode logs switchover, after the checkpoint is generated, the online redo log file can be reused online log is overwritten, media recovery only support to the most recent full backup does not support online backup table space, A tablespace corruption will cause the entire database to be unavailable, the need to remove the corrupted tablespace or restore from backup to the operating system-level database backup requires database consistency to be closed should back up all data files, control files (single), parameter files, password files, online log files (optional)--2. Archive mode is able to archive online log files, production database strongly recommends archiving in log switching, the next write log group must be archived after the log group can use the archive log sequence Number information is recorded in the control file must have enough disk space to hold the archive log Oracle 9i requires parameter log_archive_start=true to be able to perform automatic archive backup and recovery    support hot backup, and when a non-system tablespace is corrupted, The database is still available and support for online recovery    using archived logs enables online or offline point-in-time recovery (that is, it can be recovered to a specified dot, a specified archive log, or a specified SCN)

Three, two modes of switching settings and manual archiving

--1. Non-archive to archive mode A. Conformance shutdown database (shutdown [immediate | transactional |normal]) b. Booting to the Mount stage (startup mount) c. Switch to archive mode (alter Database archivelog [manual]) d. Switch to the open stage (ALTER DATABASE open) E. Make a full backup of the data--demo non-archive to archive mode sql> ARCHIVE LOG LIST--To see if the database is in archive mode DB log mode No Archive modeautomatic archival disabledarchive destin  ation use_db_recovery_file_destoldest Online log sequence 14Current log sequence 16sql> SELECT  Log_mode from V$database;     --See if the database is in archive mode Log_mode------------noarchivelog sql> SHUTDOWN IMMEDIATE; --Consistency close database closed. Database dismounted. ORACLE instance shut down.         Sql> STARTUP MOUNT; --Boot to Mount state Oracle instance started. Total System Global area 251658240 bytesfixed size 1218796 bytesvariable size 75499284 byte Sdatabase buffers 171966464 Bytesredo buffers 2973696 bytesdatabase mounted.  sql> ALTER DATABASE ARCHIVELOG; --Switch to Auto-archive mode datAbase altered.        sql> ALTER DATABASE OPEN; --Switch to open state database altered.           sql> ARCHIVE LOG LIST; --View the archive status of the database log mode Archive mode--is set to archive mode automatic archival enabled--Log   Auto Archive archive destination use_db_recovery_file_destoldest online log sequence 14Next log sequence to archive 16Current log sequence 16--backup database step omit--2. Archive mode switch to non-archive mode (step with non-archive to archive mode only C step using ALTER DATABASE Noarchivelog, demo omitted) a. Consistency Close database (shutdown [immediate | transactional |normal]) b. Boot to mount stage (startup mount) c. Switch to archive mode (ALTER DATABASE Noarchivelog) d. Switch to the open stage (ALTER DATABASE open) E. Make a full backup of the data--3. Manually Archiving manual archiving requires that the log mode be switched to alter DATABASE Archivelog manual Archive log current with alter system | All for archiving more manual archive commands: Alter SYSTEM archive_log_clause NOTE: Stanby database is not supported in manual archive mode SQL can also be implemented in ALTER database Archivelog mode     > ALTER DATABASE ARCHIVELOG MANUAL; --Switch the log mode to manual archive mode database altered.                  sql> ALTER DATABASE OPEN; --Open Database DAtabase altered.     sql> ALTER SYSTEM ARCHIVE LOG current; --Archive The current log (recommended for completion in the Mount phase) System altered.         sql> ALTER SYSTEM ARCHIVE LOG all; --Archive all logs alter system archive log all*error at line 1:ora-00271:there is no logs that need archiving-there are no logs to archive--4 The adjustment of the archive process by setting the Log_archive_max_processes parameter, you can specify the number of processes that the database requires to initialize, and the default number of archive processes is 2 You do not need to modify this parameter in general. Oracle automatically launches additional archive processes according to the archive and can dynamically increase or decrease the number of archive processes, such as the need for a large number of archives at the end of the month, usually less archiving, which can be dynamically modified to modify the method: Alter SYSTEM SET LOG_ARCHIVE_MAX_ Processes=3;--5. Configuring archiving (two archive location configuration methods) A. Archive to native and less than or equal to two archive location settings log_archive_dest and log_archive_duplex_dest parameters alter system set Log_archive_dest = '/u01/app/oracle/archivelog1 ' scope = Spfile;alter system Set Log_archive_duplex_dest = '/u01/app/    Oracle/archivelog2 ' scope = SPFile; B. Archive to native or remote host settings log_archive_dest_n parameter, n maximum value is 10alter system set log_archive_dest_1 = ' location=/u01/app/oracle/ Archivelog3 '; alter system set log_archive_dest_2 = ' service=standby1 '; For remote archive locations, the service option requires specifying the network service name of the remote database ( Configured in the Tnsnames.ora file) two common options for Log_archive_dest_n ManadaTORY | Optional:manadatory indicates that the archive must be successfully copied to the destination path before the online redo log can be reused, OPTIONAL exactly the opposite REOPEN: Specify the number of intervals after the archive fails to retry, the default is the 300-second example : log_archive_dest_1 = ' location=/u01/app/oracle/archivelog3 manadatory reopen = ' log_archive_dest_2 = ' SERVICE= Standby1 manadatory Reopen ' C. The similarities and differences of two archiving methods (former Log_archive_dest, log_archive_duplex_dest, latter log_archive_dest_n) both can be archived to this machine , the latter can be archived to a remote host, which does not support the ability to configure up to two archive locations, the latter can be configured for 10 archive locations are incompatible, either use the former, or use the latter to archive to local needs to increase the location option, Backup to remote host requires service option but the archive log is at db_recovery_file_dest default to $oracle_base/flash_recovey_area D. Archive log naming format settings log when no path is specified in either of these ways    _archive_format parameter, the default setting under UNIX is log_archive_format=%t_%s_%r.dbf%s: Log sequence Number:%s: Log sequence number (with leading)%t: Redo thread number. %T: Redo thread number (with preamble)%a: Activity ID number%d: Database ID number%r resetlogs ID value. alter system set Log_archive_format = ' Arch_%t_%s_%r.arc ';    E. Archive location Status Valid/invalid--whether the disk location and service name are valid enabled/disabled--the available state of the disk location and whether the database can use the archive location active/inactive--access to the archive destination for exceptions F. Minimum number of successful archive (using parameter log_archive_min_succeed_dest = n) alter system set log_archive_min_succeed_dest = 2; --Default to 1 online redo log groups can be overridden if all the mandatory path archive locations successfully archive the number of successful archives of the archive destination is greater than or equal to the number of worth mandatory number of the above parameter setting, then the mandatory number is the same as the number of mandatory number of the parameter set, then optional The successful archive path plus the mandatory number of archive paths is at least equal to the parameter SetPoint G. Control the availability of the archive (using parameter log_archive_dest_state_n) alter system set Log_archive_dest_state_1 = Enable |         Deferenable-The default state, which can be archived at that archive location defer--disables the archive location H. Demonstrate archive path configuration and view archive process, archive location status sql> ARCHIVE LOG LIST;            --To see if the log is in an archived state database log mode Archive modeautomatic archival enabledarchive destination           Use_db_recovery_file_destoldest Online log sequence 24Next log sequence to archive 26Current log sequence 26sql> Ho Ps-ef |        grep Ora_arc--see if the archive process has started Oracle 4062 1 0 11:43?        00:00:00 ora_arc0_orcloracle 4064 1 0 11:43?        00:00:00 ora_arc1_orclsql> Show PARAMETER ARCHIVE--to see if the archive path is set, a null value indicates that the name TYPE is not set VALUE-----------------------------------------------------------------------------ARchive_lag_target integer 0log_archive_config stringlog_archive_dest                   Stringlog_archive_dest_1 stringlog_archive_dest_10 stringlog_archive_dest_2 Stringlog_archive_dest_3 Stringlog_archive_dest_4 Stringlog_archive_dest _5 Stringlog_archive_dest_6 stringlog_archive_dest_7 Stringlog_archi      Ve_dest_8 stringlog_archive_dest_9 Stringlog_archive_dest_state_1 String Enablelog_archive_dest_state_10 string enablelog_archive_dest_state_2 string Enablelo G_archive_dest_state_3 string Enablelog_archive_dest_state_4 string Enablelog_archive_de             St_state_5 string Enablelog_archive_dest_state_6 string enablelog_archive_dest_state_7 String ENablelog_archive_dest_state_8 string Enablelog_archive_dest_state_9 string Enablelog_arc              Hive_duplex_dest Stringlog_archive_format String%t_%s_%r.dbflog_archive_local_first      Boolean truelog_archive_max_processes integer 2log_archive_min_succeed_dest integer 1log_archive_start Boolean FALSE--this parameter uses log_archive_trace integer 0 in 9i Remote_archive_enable string Truestandby_archive_dest string?/dbs/archsql> al ter system Set Log_archive_dest = '/u01/app/oracle/archivelog1 ' scope = SPFile; System altered. Sql> alter system set log_archive_duplex_dest= '/u01/app/oracle/archivelog2 ' scope=spfile; System altered. Sql> alter system set Log_archive_format = ' arch_%t_%s_%r.arc ' scope = spfile;--The instance needs to be restarted after the setting is complete, this omits the boot information sql> ho ls/ U01/APP/ORACLE/ARCHIVELOG1--see if a file or folder exists under the archive path sql> ho ls/u01/APP/ORACLE/ARCHIVELOG2--see if there is a file or folder under the archive Path sql> alter system archive log current; --Manually archive the system altered.  Sql> Ho ls-l/u01/app/oracle/archivelog1--Archivelog1 generated archive log in the specified folder total 18224-RW-------1 Oracle Oinstall 18636800 Jul 20:39 arch_1_26_724852763.arcsql> ho ls-l/u01/app/oracle/archivelog2--in the specified folder ARCHIVELOG2 generated archive log total 18224 -RW-------1 Oracle oinstall 18636800 Jul 20:39 arch_1_26_724852763.arcsql> ho Mkdir/u01/app/oracle/archivelog3- -Create a new archive path sql> ho Mkdir/u01/app/oracle/archivelog4--Create a new archive path sql> alter system set log_archive_dest_1 = ' location= /u01/app/oracle/archivelog3 ' cope = spfile; System altered. Sql> alter system set log_archive_dest_2 = ' Location=/u01/app/oracle/archivelog4 ' scope= spfile; System altered.       sql> shutdown immediate; --Close the instance database closed. Database dismounted. ORACLE instance shut down. Sql> Startup-When you start an instance, you receive an error indicating that the two methods of archiving are incompatible Ora-16019:cannot use log_archive_dest_1 with Log_archive_dest or Log_archive_duplex_dest--for error handling caused by the above settings, please refer to: Another example SPFile setting error causes the database to fail to start SPFile error causes the database to fail to start-the following is the correct Processing method, you should first empty the path of an archive, here is the Log_archive_dest sql> alter system set Log_archive_dest = "Scope = SPFile; System altered.--Here is the log_archive_duplex_dest null sql> alter system set Log_archive_duplex_dest = ' scope = SPFile; System altered.--Next set log_archive_dest_1,log_archive_dest_2sql> alter system set log_archive_dest_1 = ' location=/ U01/app/oracle/archivelog3 ' scope = SPFile; System altered. Sql> alter system set log_archive_dest_2 = ' location=/u01/app/oracle/archivelog4 ' scope = SPFile; System altered.--because the standby server is not configured, so omit the configuration to the remote host--After the setup needs to restart the instance, at this time the systems can start normally, this omits to close the boot information sql> alter system archive log  Current --Manual archiving of the system altered. Sql> Ho ls-l/u01/app/oracle/archivelog3/--View archive log total 27752-RW-------1 Oracle oinstall 28382208 2 13:46 arc_ 1_38_724852763.arcsql> Ho ls-l/u01/app/oracle/archivelog4/--View archive log total 27752-RW-------1 Oracle Oinstall 28382208 2 13:45 arc_1_38_724852763.arc sql> col dest_name format A20; sql> col destination format A30; Sql> Select Dest_name,status,archiver,destination, 2 log_sequence,reopen_secs,transmit_mode,process 3 from V$archi    Ve_dest; --View relevant status information for archived destinations dest_name status Archiver DESTINATION log_sequence reopen_secs transmit _mod PROCESS---------------------------------------------------------------------------------------------------   ------------log_archive_dest_1 VALID arch/u01/app/oracle/archivelog3/38 Synchronous Archlog_archive_dest_2 VALID arch/u01/app/oracle/archivelog4/38 Synchronous Archl Og_archive_dest_3 INACTIVE Arch 0 0 Synchronous Arch--------               --------------------------------------------------------------------------------------------------------------- Sql> alter system Set log_archive_dest_state_2 = defer; --Deactivate Log_archive_dest_state_2system altered. Sql> Show parameter log_archive_dest_state NAME TYPE VALUE----------------------             ----------------------------------------log_archive_dest_state_1 string enablelog_archive_dest_state_10 String Enablelog_archive_dest_state_2 string DEFER--the path is displayed as Deferlog_archive_dest_sta             Te_3 string Enablelog_archive_dest_state_4 string enablelog_archive_dest_state_5      String Enablelog_archive_dest_state_6 string Enablelog_archive_dest_state_7 string         Enablelog_archive_dest_state_8 string Enablelog_archive_dest_state_9 string enable   Sql> alter system archive log current; --Manually archive the system altered. Sql> ho ls-l/u01/app/oracle/archivelog3;                --You can see Archivelog3 a few more archive files than Archivelog4 total 124772               --It is recommended to copy the use system commands from the multiple files to ARCHIVELOG4-RW-------1 Oracle oinstall 28382208 2 13:46 ARC_1_38_724852763.ARC-RW -------1 Oracle Oinstall 1788416 2 20:41 arc_1_39_724852763.arc-rw-------1 Oracle oinstall 30257664 2 22:21 A  RC_1_40_724852763.ARC-RW-------1 Oracle oinstall 30257664 2 22:22 arc_1_41_724852763.arc-rw-------1 Oracle Oinstall 30257664 2 22:22 arc_1_42_724852763.arc-rw-------1 Oracle oinstall 6647296 2 22:23 arc_1_43_724852763.arcsql& Gt Ho ls-l/u01/app/oracle/archivelog4;total 29504-rw-------1 Oracle oinstall 28382208 2 13:45 arc_1_38_724852763.arc- RW-------1 Oracle oinstall 1788416 2 20:41 arc_1_39_724852763.arcsql> alter system set log_archive_dest_state_2 = enable; --Enable log_archive_dest_state_2 System altered.   Sql> alter system archive log current; --Manually archive the system altered. Sql> ho ls-l/u01/app/oracle/archivelog3; --enabled after the same Arc_1_44_724852763.arc file appears total 124856-RW-------1 Oracle oinstall 28382208 2 13:46 ARC_1_38_724852763.ARC-RW-------1 Oracle oinstall 1788416 2 20:41 arc_1_39_724852763.arc-rw-------1 Oracle Oinstall 30257664 2 22:21 arc_1_40_724852763.arc-rw-------1 Oracle oinstall 30257664 2 22:22 arc_1_41_724852763.arc-rw- ------1 Oracle Oinstall 30257664 2 22:22 arc_1_42_724852763.arc-rw-------1 Oracle oinstall 6647296 2 22:23 ar C_1_43_724852763.ARC-RW-------1 Oracle oinstall 81408 2 22:25 arc_1_44_724852763.arcsql> ho ls-l/u01/app/ora Cle/archivelog4;total 29588-RW-------1 Oracle oinstall 28382208 2 13:45 arc_1_38_724852763.arc-rw-------1 Oracle Oi Nstall 1788416 2 20:41 arc_1_39_724852763.arc-rw-------1 Oracle oinstall 81408 2 22:25 arc_1_44_724852763.a Rc

Iv. Archive log-related views

V$archived_log---information about archiving from Control files V$archive_dest--Archive path and status V$log_history--historical information of the log in the control file V $database-To see if the database is in an archived state v$archive_processes the archive-related background Process Information command: Archive log listsql> Select Name,s     Equence#,registrar,standby_dest,archived,status from V$archived_log; NAME sequence# registr STA ARC S-------------------------------------- -----------------------------------------/u01/app/oracle/archivelog4/arc_1_38_724852763.arc fgrd N O Yes A/u01/app/oracle/archivelog3/arc_1_39_724852763.arc-ARCH NO YES a/u01/app/oracle/archivelog4/ar C_1_39_724852763.arc-ARCH NO YES A/u01/app/oracle/archivelog3/arc_1_40_724852763.arc RCH No Yes A/u01/app/oracle/archivelog3/arc_1_41_724852763.arc ARCH No yes A/u01/app/oracle/archiv Elog3/arc_1_42_724852763.arc ARCH NO YES a/u01/app/orAcle/archivelog3/arc_1_43_724852763.arc ARCH NO YES a/u01/app/oracle/archivelog3/arc_1_44_724852763.ar C FGRD No yes A/u01/app/oracle/archivelog4/arc_1_44_724852763.arc fgrd No yes A

　　

9. Oracle Archive Log