Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Tuesday, October 16, 2007

ORA-19905: log_archive_format must contain %s, %t and %r

During a go-live when an 11.5.10.2 database upgraded from 9.2.0.7 to 10.2.0.3, as soon as the DBA's turned compatible=10.2.0, the database would not start up. It gave the following error:

ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-19905: log_archive_format must contain %s, %t and %r

A solution is given in metalink note 283507.1:

Cause

Could not startup the database in mount / nomount stage using Spfile as Spfile had wrong format
for archive log.

Fix

To implement the solution, please execute the following steps:

1. Create a copy of the init.ora file. ( oracle_home/admin/pfile --> init.ora )
2. Edit the above init.ora to reflect the log_archive_format = 'arch_%r_%t_%s.arc'
and log_archive_dest_1 = f:\archive
3. Shutdown immediate and Startup nomount with the above pfile.
SQL> shutdown immediate
SQL> startup nomount pfile = oracle_home/admin/pfile/init.ora

4. Create spfile from pfile
SQL> create spfile = 'oracle_home/database/spfile.ora' from pfile =
'oracle_home/admin/pfile/init.ora'

5. SQL> shutdown immediate
6. SQL> startup
7. SQL> archive log list --- verify that db is in arhivelog mode.

Requirement of this format is documented in:
Administrator's Guide
10g Release 1 (10.1)
Part No. B10739-01
Chapter7 :Managing Archived Redo Logs
---> Specifying Archive Destinations .
For details please refer the above manual.

13 comments:

shalala said...

Thanks a lot ! Simple and efficient !

Vikram Das said...

I am glad that the post helped you.

- Vikram

Kurta László said...

Thanks, it has worked for me too!

Erik Ros said...

Hi there,

this might work, but it will undo all the changes made to the spfile. Is there a solution that doesn't destroy the spfile?

[ [EBP]] said...

Thanks for this post man!

It really helped me!

Chris Bittakis said...

Great post. Your better then Metalink :)

vikas said...

Hi to all

as i am setting LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

in pfile i am getting error as
ORA-01078


Please help me out as i am not able to start my db instance.

Electrica said...

Hi,
Thanks a lot for the post, did a great help.

Electrica said...

It worked for me... thanks a lot.

Anonymous said...

Saved my life on a morning of a terrible migration night.
Thanks A billion !

Mohan Reddy said...

it is working

Nand Kishor Kumhar said...

After creating spfile. error is occuring of ora-01078: failure in processing....
LRM-00109: could not open parameter file......

Nand Kishor Kumhar said...

After creating spfile the error are occuring of
ora-01078: failure in processing....
LRM-00109: could not open parameter file......