Initialization parameter setting back to original value

I faced a very strange situation whereby I was setting my log_archive_dest_1 parameter using ALTER SYSTEM command with SCOPE=BOTH, which meant that this parameter value change should have been consistent across the instances reboot as this change was also being made in the spfile (SPFILE was in use for that database/instance) as well as in memory. However, it was not happening actually. After every instance restart, the value of this parameter would again set to the old value and this was causing problems for us.

Eventually I found that there were multiple entries for the same parameter (log_archive_dest_1, in my case) present in the spfile. During instance startup, Oracle was setting the value of this parameter as specified in the parameter prefixed with the instance name, and this was the root cause of the issue.

Following is a sample spfile of my database where I have highlighted multiple entries of log_Archive_dest_1 parameter.

*.archive_lag_target=0

*.audit_file_dest='C:\app\oracle\admin\salman12c\adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='C:\app\oracle\oradata\salman12c\control01.ctl','C:\app\oracle\fast_recovery_area\salman12c\control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='salman12'

*.db_recovery_file_dest='C:\app\oracle\fast_recovery_area'

*.db_recovery_file_dest_size=2621440000

*.db_unique_name='salman12c'

*.diagnostic_dest='C:\app\oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=salman12cXDB)'

*.enable_pluggable_database=true

salman12c.log_archive_dest_1='E:\archivelog\'

*.log_archive_dest_1='D:\archivelog\'

*.open_cursors=300

*.optimizer_capture_sql_plan_baselines=FALSE

*.pga_aggregate_target=512m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=1536m

*.undo_tablespace='UNDOTBS1'

As you can see above, once this parameter is specified with the instance name prefixed with it, and in the very next line, it is specified without instance prefix. For this case, Oracle will always pick value “salman.12c.log_archive_dest_1=’E:\archivelog\”.

You might face similar issue with some other init parameter. To avoid this problem, always make sure that there are no multiple entries for any parameter in the spfile, or pfile. To solve this problem, I reset the value of parameter “salman.12c.log_archive_dest_1=’E:\archivelog\” using ALTER SYSTEM command with SID option.

ALTER SYSTEM RESET log_archive_dest_1 SCOPE=both SID=’salman12c’;

After issuing above command, my entries in spfile were as follows. You can see that prameter salman.12c.log_archive_dest_1 is gone.