Archive Log Management

• v  Archive log Management: v  What is Archive Redo Logs? Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving. An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group. For example, if you are multiplexing your redo log, and if group 1 contains identical member files a_log1 and b_log1, then the archiver process (ARCn) will archive one of these member files. Should a_log1 become corrupted, then ARCn can still archive the identical b_log1. The archived redo log contains a copy of every group created since you enabled archiving. When the database is running in ARCHIVELOG mode, the log writer process (LGWR) cannot reuse and hence overwrite a redo log group until it has been archived. The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind. Choosing Between NOARCHIVELOG and ARCHIVELOG Mode: v  Running a Database in NOARCHIVELOG Mode: NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup. In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode. To restore a database operating in NOARCHIVELOG mode, you can use only whole database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals. v  Running a Database in ARCHIVELOG Mode: When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs. The archiving of filled groups has these advantages: ·          A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure. ·          If you keep an archived log, you can use a backup taken while the database is open and in normal system use. ·          You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.         Important Tip: It is good practice to move archived redo log files and corresponding database backups from the local disk to permanent offline storage media such as tape. A primary value of archived logs is database recovery, so you want to ensure that these logs are safe should disaster strike your primary database.                     Controlling Archiving: v  Setting the Initial Database Archiving Mode: You set the initial archiving mode as part of database creation in the CREATE DATABASE statement. Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated by that process. After creating the database, decide whether to change the initial archiving mode. v  Changing the Database Archiving Mode: The following steps switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG: 1.        Shutdown the database SHUTDOWN         You cannot change the mode from ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery. Back up the database. Before making any major change to a database, always back up the database to protect against any problems. This will be your final backup of the database in NOARCHIVELOG mode and can be used if something goes wrong during the change to ARCHIVELOG mode. 2.        Use the Backup guide for backing up the database as per your environment. 3.        Edit the initialization parameter file to include the initialization parameters that specify the destinations for the archived redo log files. 4.        Start a new instance and mount, but do not open, the database STARTUP MOUNT         To enable or disable archiving, the database must be mounted but not open.         Change the database archiving mode. Then open the database for normal operations. ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; 5.        Shut down the database. SHUTDOWN IMMEDIATE                    6.  Back up the database. Changing the database archiving mode updates the control file. After changing the database archiving mode, you must back up all of your database files and control file. Any previous backup is no longer usable because it was taken in NOARCHIVELOG mode. v  Performing Manual Archiving: SHUTDOWN STARTUP MOUNT ALTER  DATABASE ARCHIVELOG MANUAL   //This is the only change while placing database in the manual archival state. ALTER DATABASE OPEN; SHUTDOWN IMMEDIATE Backup the database immediately.         To archive a filled redo log group manually, connect with administrator privileges. Ensure that the database is either mounted or open. Use the ALTER SYSTEM statement with the ARCHIVE LOG clause ALTER  SYSTEM ARCHIVE LOG ALL;         When you use manual archiving mode, you cannot specify any standby databases in the archiving destinations.         The LOG_ARCHIVE_MAX_PROCESSES initialization parameter specifies the number of ARCn processes that the database initially starts. The default is four processes. There is usually no need specify this initialization parameter or to change its default value, because the database starts additional archiver processes (ARCn) as needed to ensure that the automatic processing of filled redo log files does not fall behind.         However, to avoid any run-time overhead of starting additional ARCn processes, you can set the LOG_ARCHIVE_MAX_PROCESSES initialization parameter to specify that up to 30 ARCn processes be started at instance startup. The LOG_ARCHIVE_MAX_PROCESSES parameter is dynamic, so you can change it using the ALTER SYSTEM statement. v  The following statement configures the database to start six ARCn processes upon startup: ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=6; Specifying Archive Destinations: v  Setting Initialization Parameters for Archive Destinations: ·          To archive to only a single destination, specify that destination using the LOG_ARCHIVE_DEST initialization parameter. ·           To archive to multiple destinations, you can choose to archive to two or more locations using the LOG_ARCHIVE_DEST_n initialization parameters or to archive only to a primary and secondary destination using the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST initialization parameters. ·          Typically, you determine archive log destinations during database planning, and you set the initialization parameters for archive destinations during database installation. However, you can use the ALTER SYSTEM command to dynamically add or change archive destinations after your database is running. Any destination changes that you make take effect at the next log switch (automatic or manual). v  Initialization Parameters: ·          LOG_ARCHIVE_DEST_n Where:  n is an integer from 1 to 31. Archive destinations 1 to 10 are available for local or remote locations. Archive destinations 11 to 31 are available for remote locations only. LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/arc' LOG_ARCHIVE_DEST_2 = 'LOCATION=/disk2/arc' LOG_ARCHIVE_DEST_3 = 'SERVICE=standby1' LOG_ARCHIVE_DEST = '/disk1/arc' LOG_ARCHIVE_DUPLEX_DEST = '/disk2/arc' v  Method 1: Using the LOG_ARCHIVE_DEST_n Parameter ·          LOCATION           A local file system location or            LOG_ARCHIVE_DEST_n = 'LOCATION=/disk1/arc' ·          LOCATION           Oracle ASM disk group                        LOG_ARCHIVE_DEST_n = 'LOCATION=+DGROUP1' ·          LOCATION           The Flash Recovery Area                     LOG_ARCHIVE_DEST_n = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' ·          SERVICE               Remote archival through Oracle Net service name.     LOG_ARCHIVE_DEST_n = 'SERVICE=standby1'         If you specify SERVICE, supply a net service name that Oracle Net can resolve to a connect descriptor for a standby database. The connect descriptor contains the information necessary for connecting to the remote database. ·          Optionally, set the LOG_ARCHIVE_FORMAT initialization parameter, using %t to include the thread number as part of the file name, %s to include the log sequence number, and %r to include the resetlogs ID (a timestamp value represented in ub4). Use capital letters (%T, %S, and %R) to pad the file name to the left with zeroes.  ·          If the COMPATIBLE initialization parameter is set to 10.0.0 or higher, the database requires the specification of resetlogs ID (%r) when you include the LOG_ARCHIVE_FORMAT parameter. The default for this parameter is operating system dependent. For example, this is the default format for UNIX: LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf IMPORTANT*: This setting will generate archived logs as follows for thread 1; log sequence numbers 100, 101, and 102; resetlogs ID 509210197. The identical resetlogs ID indicates that the files are all from the same database incarnation: EXAMPLE  as below: /disk1/archive/arch_1_100_509210197.arc, /disk1/archive/arch_1_101_509210197.arc, /disk1/archive/arch_1_102_509210197.arc /disk2/archive/arch_1_100_509210197.arc, /disk2/archive/arch_1_101_509210197.arc, /disk2/archive/arch_1_102_509210197.arc /disk3/archive/arch_1_100_509210197.arc, /disk3/archive/arch_1_101_509210197.arc, /disk3/archive/arch_1_102_509210197.arc v  Method 2: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST: To specify a maximum of two locations, use the LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination. All locations must be local. Whenever the database archives a redo log, it archives it to every destination specified by either set of parameters. ·          Specify destinations for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameter (you can also specify LOG_ARCHIVE_DUPLEX_DEST dynamically using the ALTER SYSTEM statement). For example, enter: ·          Set the LOG_ARCHIVE_FORMAT initialization parameter as described in step 2 for method 1. NOTE:* If you configure a Flash Recovery Area (by setting the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters) and do not specify any local archive destinations, the database automatically selects the Flash Recovery Area as a local archive destination and sets LOG_ARCHIVE_DEST_1 to USE_DB_RECOVERY_FILE_DEST. v  Understanding Archive Destination Status ·          Each archive destination has the following variable characteristics that determine its status: ·          Valid/Invalid: indicates whether the disk location or service name information is specified and valid ·          Enabled/Disabled: indicates the availability state of the location and whether the database can use the destination ·          Active/Inactive: indicates whether there was a problem accessing the destination The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 31) initialization parameter lets you control the availability state of the specified destination (n). ·          ENABLE indicates that the database can use the destination. ·          DEFER  indicates that the location is temporarily disabled. ·          ALTERNATE indicates that the destination is an alternate. The availability state of an alternate destination is DEFER. If its parent destination fails, the availability state of the alternate becomes ENABLE. ALTERNATE cannot be specified for destinations LOG_ARCHIVE_DEST_11 to LOG_ARCHIVE_DEST_31. v  Specifying Alternate Destinations: To specify that a location be an archive destination only in the event of a failure of another destination, you can make it an alternate destination. Both local and remote destinations can be alternates. The following example makes LOG_ARCHIVE_DEST_4 an alternate for LOG_ARCHIVE_DEST_3: ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = 'LOCATION=/disk4/arch'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = 'LOCATION=/disk3/arch  ALTERNATE=LOG_ARCHIVE_DEST_4'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ALTERNATE; SQL> SELECT dest_name, status, destination FROM v$archive_dest; DEST_NAME               STATUS    DESTINATION ----------------------- --------- ---------------------------------------------- LOG_ARCHIVE_DEST_1      VALID     /disk1/arch LOG_ARCHIVE_DEST_2      VALID     +RECOVERY LOG_ARCHIVE_DEST_3      VALID     /disk3/arch LOG_ARCHIVE_DEST_4      ALTERNATE /disk4/arch v  Managing Archive Destination Failure: ·          Specifying the Minimum Number of Successful Destinations:         The optional initialization parameter LOG_ARCHIVE_MIN_SUCCEED_DEST=n determines the minimum number of destinations to which the database must successfully archive a redo log group before it can reuse online log files. The default value is 1. Valid values for n are 1 to 2 if you are using duplexing, or 1 to 31 if you are multiplexing.