Friday, October 26, 2018

ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


This error comes when you use wrong syntax while setting log archive destination using   log_Archive_dest_n parameter.  I faced this error while trying to set log_archive_dest_1 parameter using ALTER SYSTEM command and the reason of this error was that I missed the keyword “location=” while specifying the destination directory. But this error may also come because of other syntax errors in the command while setting the parameter.     

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            %ORACLE_HOME%\RDBMS
Oldest online log sequence     989
Next log sequence to archive   989
Current log sequence           991
SQL> alter system set log_archive_dest_1='USE_DB_RECOVERY_FILE_DEST';
alter system set log_archive_dest_1='USE_DB_RECOVERY_FILE_DEST'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

When I specified “location=” keyword, I was able to set parameter perfectly.
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST';

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     989
Next log sequence to archive   989
Current log sequence           991
SQL>

3 comments:

  1. UnknownFebruary 6, 2020 at 2:01 PM

    SQL> alter system set LOG_ARCHIVE_DEST_2 = 'LOCATION = +ACTD_DG_FLASH/ACTD/ARCHIVELOG';
    alter system set LOG_ARCHIVE_DEST_2 = 'LOCATION = +ACTD_DG_FLASH/ACTD/ARCHIVELOG'
    *
    ERROR at line 1:
    ORA-32017: failure in updating SPFILE
    ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE

    ReplyDelete
    Replies
    1. Salman Ahmed QureshiFebruary 6, 2020 at 6:25 PM

      Remove spaces from both sides of the equal sign and it should work. See below.

      SQL> alter system set LOG_ARCHIVE_DEST_3 = 'LOCATION = +ACTD_DG_FLASH/ACTD/ARCHIVELOG';
      alter system set LOG_ARCHIVE_DEST_3 = 'LOCATION = +ACTD_DG_FLASH/ACTD/ARCHIVELOG'
      *
      ERROR at line 1:
      ORA-32017: failure in updating SPFILE
      ORA-16179: incremental changes to "log_archive_dest_3" not allowed with SPFILE


      SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=+ACTD_DG_FLASH/ACTD/ARCHIVELOG';

      System altered.

      Delete
    2. UnknownJanuary 14, 2022 at 10:12 AM

      thanks its worked

      Delete