Re: newbie question - init.ora vs. alter database

"Greg" <ghorie_at_sfu.ca> wrote in message
news:3e484eea.0305160855.1a422575_at_posting.google.com...
> Hi,
>
> I was performing an ALTER DATABASE ARCHIVELOG on my Oracle DB and I
> was wondering if its necessary for me to include the following
> parameters in the init.ora file as well:
>
> log_archive_start = true
> log_archive_dest_1 = "location=/opt/oracle/arch"
> log_archive_format = arch_%t_%S.log
>
> From what I understand, the ALTER statement needs to be performed in
> any case, but I'm not sure whether these init.ora configurations are
> mandatory as well (or if there is just a way to perform these steps
> while the database is open).
>
> Sorry, but I'm a little confused on the relationship between the DB,
> the init file and the control files. If anyone has a decent link to
> explain the relationship between these things, this might help me out.
>

The relationship is simply that everything in the init.ora tells the instance and the database what o do or how to perform (how big should my shared pool be? Where do you want me to write archive logs).

Forget about the control file, really. That's an integral part of the database, and basically has two functions: it points to where everything else comprisiing the dtaabase is physically found; and it sets the timestamp to which all other database components must agree (if you like, it's the master clock for the database).

So the init.ora tells the instance where to find the controlfiles alone... it doesn't have a reference to the location of datafiles or online redo logs. Only the controlfile knows where they are.

As for your specific question, 'alter database archivelog' sets a bit on the controlfile which tells LGWR 'do not overwrite an online redo log unless it has been copied somewhere first'. It doesn't say anything about taking that copy, or where to take it to, or what to call it. Just 'don't overwrite unless it has been copied'.

So yes, you do need to then also tell ARCH where to take the copies, and you do that with the init.ora parameters you mentioned. LOG_ARCHIVE_START tells ARCH to switch itself on. LOG_ARCHIVE_DEST tells ARCH where to stick 'em. And LOG_ARCHIVE_FORMAT tells ARCH what name to call the copies.

The tricky bit is that nearly (but not quite) all parameters have default values, so that it is in fact NOT necessary to specify them in the init.ora, because the instance will assume a value for them if they're not there. For example, log_archive_dest has a default value (something like, if memory serves, ORACLE_HOME/dbs/archive), and so does log_archive_format (from memory, that's %s_%t.dbf). So you could get away without explicitly setting these two, because these defaults will kick in if you don't.

LOG_ARCHIVE_START, however, has a default value of FALSE, so if you don't explicitly set that one, then ARCH doesn't get switched on in the first place. And if you've put your database into archivelog mode, but haven't switched on ARCH, then your system will eventually enter a permanently stalled state, because the former says 'LGWR may not overwrite until the log's been copied' and the latter is saying 'but I'm not going to tell you to take copies').

Now you can temporarily fix up such a problem by manually turning on ARCH: alter system archive log start; has the same effect as LOG_ARCHIVE_START=TRUE in the init.ora.

Trouble is, 'alter system' language tends to be conversations with the instance (whereas 'alter database' language tends to be a conversation with the control file). Therefore, anything set with an alter system command is only valid for the lifetime of the instance... bounce your instance, and the altered value reverts back to the default or init.ora setting. Anything set with an alter database command tends to be set for ever, because it usually means some bit or other has been modified in the physical controlfile.

I'm afraid that 9i muddies the waters here a little, because it introduced the idea of an 'spfile', which is really nothing more than a binary version of the init.ora. But, being a binary file, how do you edit it? Oracle decided to use the 'alter system' commands. By default, these days, an alter system command *will* make a permanent alteration to the database's behaviour, because it implies altering a setting in the spfile as well as altering the currently-running instance's settings. At the next instance bounce, the spfile's settings are read... and they've just been altered, so your 'alter system' change appears to be permanent.

Hope that helps a bit.

Regards
HJR
> Thx.
Received on Fri May 16 2003 - 15:59:14 CDT