Friday, September 12, 2008

Archive Destination Settings fails with ORA-32017 and ORA-16179

Problem Description
While enabling my archived log file to more than one location whenever I set log_archive_dest_1 it fails with error ORA-32017 and ORA-16179.
SQL> alter system set log_archive_dest_1='c:\test';
alter system set log_archive_dest_1='c:\test'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

Cause of The Problem

To set LOG_ARCHIVE_DEST_n you must include either the LOCATION or the SERVICE attribute for each destination to specify where to archive the redo data. But in the command this is not used.

Solution of The Problem
While setting LOG_ARCHIVE_DEST_n there are several usage notes like,

•Either the LOCATION or the SERVICE attribute must be specified. There is no default.

•With the LOCATION attribute local destination is specified. You can set local disk path or USE_DB_RECOVERY_FILE_DEST that will serve as the flash recovery area using the DB_RECOVERY_FILE_DEST initialization parameter.

•With the SERVICE attribute you can specify remote destination.

•SERVICE attribute is specified with a valid Oracle Net service name (SERVICE=net_service_name) that identifies the remote Oracle database instance to which the redo data will be sent.

Example:
To a local disk destination,
SQL> alter system set log_archive_dest_1='LOCATION=c:\test';
System altered.

SQL> alter system set log_archive_dest_2='LOCATION=g:\';
System altered.

Using flash recovery area,
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST';

Setting to a remote location,
ALTER SYSTEM SET LOG_ARCHIVE_DEST_4='SERVICE=stby1';
In the tnsnames.ora stby1 is defined to a remote location where archived redo logs to be located.

Related Documents
ORA-16018 And ORA-16019 LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n

What will be the Archived Redo Log Destination in Oracle

1 comment:

  1. Important: Without spaces

    Error:
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION = USE_DB_RECOVERY_FILE_DEST';

    OK:
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST';

    ReplyDelete