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.
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 :-)
3 comments:
Genius and simple explanation - Many thanks for the hint.
R
Ace
I KNOW this is an old post - but still good inforamtion
Thank you so much for the explain this in very simple way :)
Post a Comment