ORA-02095: specified initialization parameter cannot be modified


Yesterday i was trying to relocate my control files from default location (/u01/app/oracle/oradata/orabreakfix/control01.ctl ) to new location /s01/oradata1/orabreakfix/control01.ctl.

So the command i ran is

alter system set control_files=’/s01/oradata1/orabreakfix/control01.ctl, /s01/oradata1/orabreakfix/control02.ctl,/s01/oradata1/orabreakfix/control03.ctl’;

But something unexpected happened

ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified.

It took me a while to think the work around, which is related my previous post  https://rajat1205sharma.wordpress.com/2015/11/30/how-to-identify-a-static-or-dynamic-parameter-in-oracle/

SQL> select NAME,VALUE,DISPLAY_VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE,ISMODIFIEd from v$parameter where NAME like ‘control%’;

NAME

VALUE

DISPLAY_VALUE

ISDEFAULT ISSES_MODIFIABL ISSYS_MODIFIABLE ISINSTANCE_MODI ISMODIFIED

control_files
/u01/app/oracle/oradata/orabreakfix/control01.ctl, /u01/app/oracle/oradata/orabreakfix/control02.ctl, /u01/app/oracle/oradata/orabreakfix/control03.ctl
/u01/app/oracle/oradata/orabreakfix/control01.ctl, /u01/app/oracle/oradata/orabreakfix/control02.ctl, /u01/app/oracle/oradata/orabreakfix/control03.ctl
FALSE     FALSE     FALSE    FALSE     FALSE

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter system set control_files=’/s01/oradata1/orabreakfix/control01.ctl, /s01/oradata1/orabreakfix/control02.ctl,/s01/oradata1/orabreakfix/control03.ctl’ scope=spfile;

System altered.

similarly when i tried to run below query i got the same problem

Database mounted.
SQL> alter system set log_archive_format=’log%t_%s_%r.arc’;
alter system set log_archive_format=’log%t_%s_%r.arc’
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

now i know what i need to check, so i checked

SQL> Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name=’log_archive_format’;

NAME

VALUE ISDEFAULT ISSES_MODIFIABL ISSYS_MODIFIABLE


log_archive_format
%t_%s_%r.dbf TRUE FALSE FALSE

so i ran the below query and it worked and  all i need to doafter that is to restart my database to get this parameter changed.

SQL> alter system set log_archive_format=’log%t_%s_%r.arc’ scope=spfile;

System altered.

SQL> show parameter LOG_ARCHIVE_FORMAT;

NAME TYPE VALUE


log_archive_format string log%t_%s_%r.arc

I hope it would be helpful.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s