Friday , October 20 2017



  1. Archive log files are offline copies for online redo log files and are required to recover the database if we have old backup.
  2. Archive log generation can be of two ways – manual and automatic. It is always preferred to use automatic method as DBA’s cannot be dedicated to perform manual archiving.
  3. The following are parameters that are used for archivelog mode with their description.
    1. LOG_ARCHIVE_START – it will enable automatic archiving and useful only till 9i (deprecated in 10g)
    2. LOG_ARCHIVE_TRACE – it is used to generate a trace file to know how ARCHn process working.
    3. LOG_ARCHIVE_MIN_SUCCEEDED_DEST – defines min destinations to which ARCHn process should complete archiving by the time LGWR starts writing to online redolog file.
    4. LOG_ARCHIVE_MAX_PROCESSES – will start multiple ARCH processes and helpful in faster writing.
    5. LOG_ARCHIVE_LOCAL_FIRST – if enabled, ARCHn process will first generate archive in local machine and then in remote machine. It is used in case of dataguard setup.
    6. LOG_ARCHIVE_FORMAT – defines the archive log file format.
    7. LOG_ARCHIVE_DUPLEX_DEST – if want to archive in only 2 locations, we should use this.
    8. LOG_ARCHIVE_DEST_1…10 – if want to archive to more than 2, we should enable this.
    9. LOG_ARCHIVE_DEST_STATE_1…10 – to enable / disable archive locations.
    10. LOG_ARCHIVE_CONFIG – it enables / disables sending redologs to remote location. Used in dataguard environment.
  1. When we want to multiplex into only 2 locations, from 10g we should use LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters.
  2. The default location for archivelogs in 10g is Flash Recovery Area (FRA). The archives in this location are deleted when a space pressure arised. The location and size of FRA can be known using DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters respectively.
  3. To disable archivelog generation into FRA, we shouldn’t use LOG_ARCHIVE_DEST, but should use LOG_ARCHIVE_DEST_1.
Note: archivelogs can also be deleted based on RMAN deletion policy.
# How to check if database is in archive log mode?
SQL> archive log list;
# Enablingarchivelog mode in 10g/11g
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
Note: When we enable archivelog mode using above method, the archives will be generated by default in Flash Recovery Area (FRA). It is the location where files required for recovery exist and introduced in 10g.
# To change the archive destination from FRA to customized location
SQL> alter system set log_archive_dest_1=’location=/u03/archives’ scope=spfile;
SQL> shutdown immediate
SQL> startup
# Enablingarchivelog mode in 9i
SQL> alter system set log_archive_start=TRUE scope=spfile;
SQL> alter system set log_archive_dest=’/u03/archives’ scope=spfile;
SQL> alter system set log_archive_format=’prod_%s.arc’ scope=spfile;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database log archive start;
SQL> alter database archivelog;
SQL> alter database open;
# Disabling  archivelog mode in 9i/10g/11g
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;



The following are the block space utilization parameters
    1. INITT RANS and MAX TRANS – represents no of concurrent transactions that can access a block. MAX TRANS was set to 255 and removed from 10g.
    2. PCT FREE – it is the reserved space for future updates (update statement may or may not increase row size). In case row size increases, it will take space from PCT FREE.
    3. PCT USED – it is the level which will be compared with data level for insertion after deletion.
Note: Block space utilization parameters are deprecated from locally managed table spaces.
Read More:
Using the Result Cache



Check Also

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. …

Leave a Reply

Your email address will not be published. Required fields are marked *