Format for LOG_ARCHIVE_FORMAT in Oracle

LOG_ARCHIVE_FORMAT parameter controls the format of the archive log file name. This parameter can only be used when the redo log is in ARCHIVELOG mode . LOG_ARCHIVE_FORMAT parameter is static in nature so it comes into action after restart of the instance. If the format defined in the parameter log_archive_format is invalid the database will startup but archiver will fail to archive logs which will cause database to hang and in the alert log the following message would be reported .

 “ORA-00294: invalid archivelog format specifier..” 

So if we change this parameter a quick test can be done by running the above SQL to make sure oracle is able to archive the redo log.

The format of specifying the archive redo log filename is given below :

LOG_ARCHIVE_FORMAT = “LOG%s_%t_%r.arc”
Various parameters that can be used with the LOG_ARCHIVE_FORMAT parameter are given below:
%s – log sequence number
%S – log sequence number, padded with zero
%t – thread number
%T – thread number, padded with zero
%a – activation id
%d – database id
%r – reset logs id

Whenever uppercase is used for a variable, such as %S or %T, it forces the value of the variable to be of fixed length, and the value to the left is padded with zeros.

Below is the Demo of the log_archive_format parameters.

SQL> alter system set log_archive_dest_1='location=D:\archive\';
System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\archive\
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13
        
SQL> alter system set log_archive_format='noida_%s_%t_%r.arc' scope=spfile;
System altered.

SQL> shut immediate
SQL>startup

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\archive\
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13

SQL> alter system switch logfile;
System altered.

Now the  new archive log file  name  like  'NOIDA_13_1_749730106.ARC'

For more click here 


Enjoy       :-)