New Archive Log Dest Question

[tekdudedude]

Hello,

I have found that if I add a new archive log dest (ALTER SYSTEM SET log_archive_dest_n=) if I bounce the database I can see it is being used.

At what point does a newly added log_archive_dest_n become active? Can it be made active without bouncing the database?

Thanks,

TD

 

[karluk]

Log_archive_dest_n is a dynamic parameter (as are the majority of initialization parameters in 10g). Therefore you can add a new archive directory without bouncing the instance. The way to do this is by specifying "scope" on your alter system command. The alternatives are

ALTER SYSTEM SET log_archive_dest_n="LOCATION={directory path} scope=memory;
ALTER SYSTEM SET log_archive_dest_n="LOCATION={directory path} scope=spfile;
ALTER SYSTEM SET log_archive_dest_n="LOCATION={directory path} scope=both;

"Scope=memory" makes the change effective immediately, but does not record it in the spfile. As a result the new archive log dest will disappear when the instance is bounced.

"Scope=spfile" delays the implementation of the change until after the instance is stopped and restarted using the parameters recorded in the spfile.

"Scope=both" makes the change effective immediately and also records the change in the spfile, so it will persist after the instance is bounced.

So, for your purpose you want either "scope=memory" or scope=both". It all depends on whether you are making the change as a temporary expedient (perhaps because a drive filled up) or permanently.

Incidentally, you can verify that the new archive dest is in use by running the command

alter system switch logfile;

That will force a log switch and result in a new archive log being produced.