Problem with archive_log_format parameter in SPFILE

[dave_keyur]

Hi all,

My database is running of oracle 10g. My archive log sequence crossed the 5 digits. Now to make it of 6 digits i modified my parameter in init.ora and spfile using following steps:

shutdown immediate
set parameter in init.ora to log_archive_format='%t_dbname_%s_%r.ARC'
startup pfile='path\init.ora'
create spfile from pfile='path\init.ora';
shutdown immediate
startup

Now when I start the database it generates archive log sequence with 6 digits. But when I again restart the database it again start generating archive log sequence with 5 digits.
And parameter in spfile shows parameter automatically converted to '%T_CWHODDB_%S_%R.ARC'

So how to stop database to automatically convert the log_archive_format in spfile? I do not want to open database with resetlogs.

Help is appriciated.

Thanks and regards
Keyur Dave

[skhanal]

Once the database is up, can you check what value it is using?

show parameter log_archive_format

also see if database is using spfile.

show parameter spfile

if you have spfile defined in init.ora you may have to comment that out when you restart the database, create spfile, then uncomment the spfile line.

[dave_keyur]

Hi,

Thanks for your reply.

I had done following steps earlier to remove this parameter but was not succeded:

1)
In my parameter file I had removed parameter:
cwhoddb.log_archive_format='%T_CWHODDB_%S_%R.ARC'
and kept only *.log_archive_format='%t_CWHODDB_%s_%r.ARC'

2)
Shut down the database and started with pfile

startup pfile='D:\oracle\product\10.2.0\admin\cwhoddb\pfil e\init.ora'

create spfile='D:\oracle\product\10.2.0\db_1\database\SPF ILECWHODDB.ORA' from pfile='D:\oracle\product\10.2.0\admin\cwhoddb\pfil e\init.ora';

shutdown immediate;

3)
Then started with spfile
startup

This time its generating sequence number with 6 digits.

4)
Then my server was down due to power failure and I restarted the server and database automatically starts as server starts with spfile
And the sequence now generating with 5 digits.

This is the whole scenario I gone through.

But here are some questions from me.

When I check in OEM -> All Initialization Parameter -> spfile -> log_archive_format,

it shows me two values as below:

log_archive_format "%t_dbname_%s_%r.ARC"
log_archive_format "%T_CWHODDB_%S_%R.ARC"

here CWHODDB is our dbname. So in first parameter is shows dbname which dynamically takes database name and in second parameter its taking hardcoded values. So why this two parameter is shown in spfile?

And also when I create pfile from spfile it is containing two values for log_archive_parameter which are as follows:

*.log_archive_format='%t_CWHODDB_%s_%r.ARC'
cwhoddb.log_archive_format='%T_CWHODDB_%S_%R.ARC'

So why spfiel is having this two formats? And how to remove the second parameter cwhoddb.log_archive_format='%T_CWHODDB_%S_%R.ARC'?

Help is appreciated.

Thanks and regards,
Keyur Dave

[skhanal]

Since it looks like a RAC environment, do you have separate pfile for each instance or it is a shared one?. If they are separate, you will need to change it on both.

You can remove the instance specific setting by

alter system reset command.

[dave_keyur]

HI,

I am using standby database feature but not RAC. Does standby database related to this issue? If have any idea then please explain to me. I will check it out.

Thanks and Regards,
Keyur

[skhanal]

I think you should try removing instance specific setting and leave *.log_archive_format, use ALTER SYSTEM RESET to remove cwhoddb.log_archive_format

[dave_keyur]

I think you should try removing instance specific setting and leave *.log_archive_format, use ALTER SYSTEM RESET to remove cwhoddb.log_archive_format

Hi,

Thank you very much for your reply.
Your help worked for me.
When I RESET parameter it removes the parameter starting with * i.e. parameter for all instances and keep the instance specific parameter.
So I done following thigs.

alter system reset log_archive_format scope=spfile sid='*';
alter system set log_archive_format='%t_dbname_%s_%r.ARC' scope=spfile sid='CWHODDB';
select * from V$SPPARAMETER where name='log_archive_format';

Thus now there is only one parameter cwhodd.log_archive_format with all letters in small.

My problem got solved. Thank you very much for your dedicated help.