Automatic Archiving won't start (problem with init file?)

[gertvangaever]

Hello!

I have a Oracle 9.0.1 Database which is not in Archive log mode now. I'm trying to put it into archive log mode with automatic archive logging enabled.

I shut the database down, and then I took a backup of all the database files.

After this I changed the initoldap.ora file, which is in $ORACLE_HOME/dbs. There is a link that points to /oldap_data/admin/oldap/pfile/initoldap.ora.
So in fact I changed that last file!

I added the following lines (the variabeles where not included before!)

LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_DEST=/orahome/9.0.1/dbs/arch
LOG_ARCHIVE_FORMAT=arch%s.arc

When I restart the database, and I enter 'archive log list', I get the following:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Disabled
Archive destination /orahome/9.0.1/dbs/arch
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1

So Automatic Archival is disabled ?!

First I thought it had something to do with the initoldap.ora file. But when I try
'startup pfile=/oldap_data/admin/oldap/pfile/initoldap.ora'
The result is that the automatic archival is ENABLED!

So it seems the default startup initfile is NOT $ORACLE_HOME/dbs (which points to
/oldap_data/admin/oldap/pfile/initoldap.ora)

I thought $ORACLE_HOME/dbs was ALWAYS the directory to read the init file from?!
Or had this changed is r9?

Tnx
Gert

 

[karluk]

Automatic archiving is not enabled with an initialization parameter. To get automatic archiving you have to mount your database and issue the command

alter database archivelog;

It looks to me as if you (or someone else) did this as part of your attempts to get archiving started. That's good because it's what you needed to do.

The change in your startup command is unrelated to automatic archiving. Both ways are equivalent, since they point to the same initialization parameters. You wouldn't have seen a difference in archiving unless you also did the alter database command.

 

[gertvangaever]

What about 'LOG_ARCHIVE_START=TRUE'
This starts automatic archiving
Alter Database archivelog only puts the database into archive log mode!

Gert

 

[gertvangaever]

I'm now sure that the problem lays within the (location of) the parameter file.
When I change LOG_ARCHIVE_DEST in the parameter file mentioned above, and then shutdown/restart, the archive destination (when I type archive log list) still is the same!
When I use 'startup pfile=<changed parameter file>', the automatic archiving is enables AND the archive destination has CHANGED!!
So HOW do I determine WHICH parameter file is used????


Gert

 

[DBAwhosaysNIE]

hey,

don't know if they changed this w/9 but on every version of Oracle I've ever used (7.3.2 - 8.1.7 & most in between) if you don't specify pfile=$somefile it will use $ORACLE_HOME/dbs/init${ORACLE_SID}.ora. most people (myself included) create a soft link here to the &quot;real&quot; file they want to use (something like $ORACLE_BASE/admin/$ORACLE_SID/pfile/init${ORACLE_SID}.ora) but are too lazy to type. ;-)

 

[karluk]

Sorry for the clunky advice. It looks as if you're right about the default pfile not being what you expect.

If you want to verify the default pfile, I suggest that you log into SQL*Plus from a client machine somewhere and issue the startup command. The client won't have an init.ora file, so you should see an error similar to the one I see on an Oracle 8.1.7 system:

LRM-00109: could not open parameter file '%ORACLE_HOME%\DATABASE\INIT%ORACLE_SID%.ORA'
ORA-01078: failure in processing system parameters

You can then verify that the default is the same on the server. Rename the file that you think is the default and issue the startup command. If you are right you should see the same error message.

 

[gertvangaever]

Found the solution!
As i'm using Oracle 9i, I have to edit the 'spfileSID.ora' file instead of the initSID.ora!!

Seems init.ora isn't used anymore!

Tnx anyway!

 

[karluk]

You might want to start posting in forum759, which is specific to 9i. Right now the membership in that forum is smaller than in the Oracle 8 forum, but you're probably more likely to get a response from someone who's had practical experience in 9i.

Personally I monitor both forums, but I certainly can't claim 9i expertise. Maybe someday...

 

[jee]

Do you realize you posted your question to the Oracle 8/8i forum and that there is a separate Oracle 9 Forum? That is probably why you had to answer your question yourself!