Home » RDBMS Server » Server Administration » Problem with archive_log_format parameter in SPFILE (oracle, 10g, windows 2003 server)
Problem with archive_log_format parameter in SPFILE [message #405329] Wed, 27 May 2009 06:54 Go to next message
dave_keyur
Messages: 5
Registered: May 2009
Junior Member
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

Report message to a moderator

Re: Problem with archive_log_format parameter in SPFILE [message #405353 is a reply to message #405329] Wed, 27 May 2009 07:25 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
How many spfiles do you have?

It would seem if you are starting it once and it works then when you start it again and it doesn't work that it is using a different spfile/pfile.

Does sho parameter spfile show the same each time it works and doesn't work ? How are you starting the database the time it doesn't work?

[Updated on: Wed, 27 May 2009 07:33]

Report message to a moderator

Re: Problem with archive_log_format parameter in SPFILE [message #405362 is a reply to message #405353] Wed, 27 May 2009 07:34 Go to previous messageGo to next message
dave_keyur
Messages: 5
Registered: May 2009
Junior Member
Hi,

Thanks for quick reply.

Database is starting with same spfile both the times.
show parameter spfile shows the same result both the times.
But log_archive_format parameter changed second time to caps latters.

Thanks and regards,
Keyur Dave

Report message to a moderator

Re: Problem with archive_log_format parameter in SPFILE [message #405371 is a reply to message #405362] Wed, 27 May 2009 07:50 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
How are you starting the database the second time?

Once you create the spfile with the appropriate format did you verify the format in the spfile on the filesystem? If so, when the format converts back what does it show in the spfile on the filesystem then?

Report message to a moderator

Re: Problem with archive_log_format parameter in SPFILE [message #405756 is a reply to message #405371] Fri, 29 May 2009 06:04 Go to previous messageGo to next message
dave_keyur
Messages: 5
Registered: May 2009
Junior Member
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\pfile\init.ora'

create spfile='D:\oracle\product\10.2.0\db_1\database\SPFILECWHODDB.ORA' from pfile='D:\oracle\product\10.2.0\admin\cwhoddb\pfile\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

Report message to a moderator

Re: Problem with archive_log_format parameter in SPFILE [message #405758 is a reply to message #405756] Fri, 29 May 2009 06:12 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
It would appear that there is another spfile located somewhere. Have you searched the filesystem for any other spfiles? Once you restart the database via Windows service, check to see what sho parameter spfile indicates. Open the spfile and check it's contents.

Report message to a moderator

Re: Problem with archive_log_format parameter in SPFILE [message #405853 is a reply to message #405758] Sat, 30 May 2009 06:13 Go to previous messageGo to next message
dave_keyur
Messages: 5
Registered: May 2009
Junior Member
Hi,

I have serched through whole system there is only one spfile. And I am damne sure its not the issue of multiple spfile. Because I created spfile from pfile, after removing capital lettered parameter from pfile, and resterted and then restart with spfile, and create pfile from spfile, the pfile contains the parameter with capital letters as well as with small letters i.e. two same parameter with small and capital letter value.

Regards,
Keyur

Report message to a moderator

Re: Problem with archive_log_format parameter in SPFILE [message #405858 is a reply to message #405853] Sat, 30 May 2009 08:20 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
It's simple, you could do the following:

1. Create a pfile from spfile.
2. Shutdown the database.
3. Check the pfile that was created and verify it's contents.
4. Rename the spfile and ensure there are no other pfiles or spfiles in %ORACLE_HOME%/database directory.
5. Startup database with pfile.
6. Create spfile from pfile.
7. Shutdown database.
8. Verify the contents of the spfile by opening it in notepad (DO NOT edit it's contents).
9. Rename the pfile.
10. Start database and verify the log_archive_format.
11. Shutdown database.
12. Start database using Window's service and verify the log_archive_format.
13. If it has changed then either the Window's service was created with oradim using a different pfile or you have discovered a bug.

Report message to a moderator

Re: Problem with archive_log_format parameter in SPFILE [message #406060 is a reply to message #405858] Tue, 02 June 2009 00:18 Go to previous message
dave_keyur
Messages: 5
Registered: May 2009
Junior Member
HI,

Thanks for discriptive help.

But there are two log_archive_format parameter in one spfile with different values. So its not the issue of different spfile. I have implemented standby database using data guard. And I think this is the cause for the second parameter which is automatically generated even after removed from spfile. I am also using RMAN. But it should be the cause from standby database only.

Regards,
Keyur Dave

Report message to a moderator

Previous Topic: Create DB link from oracle 9i to SQL Server (2 merged)
Next Topic: Dropped View stil needs Storage
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 29 13:25:02 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.