Re: alter system archive log stop;

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 02 Mar 2005 13:12:38 +0100
Message-ID: <d04ajn$fd1$1@news.BelWue.DE>

Comments embedded.

Tof wrote:
> Holger Baer wrote:
>

>> Tof wrote:
>> [Snip]
>>
>>>>
>>>> So, you're not on 10G, then?
>>>
>>>
>>>
>>>
>>> I am on 10g. There a difference between 9i and 10g ?
>>
>>
>>
>> No they just changed the version out of greed ;-)
>>
>> Check out the 10g  Administrators Guide, Chapter 7.
>>
>> Also:
>>
>> sys_at_DEMO10G>select name, isdeprecated from v$parameter where name like 
>> 'log_arc%';
>>
>> NAME                                                                             
>> ISDEP
>> -------------------------------------------------------------------------------- 
>> -----
>> log_archive_config                                                               
>> FALSE
>> log_archive_start                                                                
>> TRUE    <--
>> log_archive_dest                                                                 
>> FALSE
>> <rest snipped out>
>>
>> Finally, check out the SQL Reference on the archive log clause of 
>> alter system.
>>
>> Cheers
>> Holger

>
> Ok, i see, but it don't explain about "alter system archive log (STOP or
> START)"
Yes, it does: From the SQL Reference:

<quote>
STOP Clause

In earlier releases, this clause disabled automatic archiving of redo log file groups for the thread assigned to your instance. This clause has been deprecated. It has no effect, and if you specify it, Oracle Database writes a message to the alert log. </quote>

> And i try this and i don't understant :
>
> SQL> alter system archive log start;
>
> System altered.
>
> SQL> select name, isdeprecated from v$parameter where
> name='log_archive_start';
>
> NAME ISDEP
> ------------------------------ -----
> log_archive_start TRUE
>
> SQL> alter system archive log stop;
>
> System altered.
>
> SQL> select name, isdeprecated from v$parameter where
> name='log_archive_start';
>
> NAME ISDEP
> ------------------------------ -----
> log_archive_start TRUE

My guess is, that log_archive_start only reflects if your database is in automatic or manual archiving, i.e. it reflects the automatic archival mode.

Demonstration:
sys_at_DEMO10G>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys_at_DEMO10G>startup mount
ORACLE instance started.

Total System Global Area 440401920 bytes

Fixed Size                   779260 bytes
Variable Size             207363076 bytes
Database Buffers          230686720 bytes
Redo Buffers                1572864 bytes

Database mounted.
sys_at_DEMO10G>alter database archivelog manual;

Database altered.

sys_at_DEMO10G>alter database open;

Database altered.

sys_at_DEMO10G>show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------

.
<snipped>
.

log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE   <--
log_archive_trace                    integer     0

sys_at_DEMO10G>

However, the outcome of archive log list is a bit unexpected:

sys_at_DEMO10G>archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled				<--- ?? I'd expected manual here
Archive destination            /opt/app/oracle/oradata/demo10g_bkp
Oldest online log sequence     3732
Next log sequence to archive   3734
Current log sequence           3734

>
> But if i want to stop it, i must shutdown database and modify the
> parameter log_archive_start at false to init.ora

Does this really work?
>
> But i don't understand for the "alter system archive log stop"
> if it run, i must see a indication in database parameter the change of
> this action.

Check the alert.log
>
> I see at SQL Reference :
> archive_log_clause
>
> The archive_log_clause manually archives redo log files or enables or
> disables automatic archiving. To use this clause, your instance must
> have the database mounted. The database can be either open or closed
> unless otherwise noted.

This seems to be an documentation bug, since a few lines below that it clearly states that START | STOP have no effect.

>
> Also the "alter system archive log stop" there no action on database
> open !!!.

As a final conclusion, after shutdown immediate/startup mount/alter database archivelog; the log_archive_start still reports false. And setting it through

    alter system set log_archive_start = true scope = spfile;

leads to this
sys_at_DEMO10G>startup
ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started.

No harm done, I suppose, but not necessary, so back to resetting it:

    alter system reset log_archive_start scope = spfile sid='*'; Bouncing the database and log_archive_start is back to false. Even with alter database noarchivelog, bouncing and alter database archivelog leave log_archive_start at false.

Summary: just ignore log_archive_start if you're on 10g or higher. It's not even in the reference, so it's no longer documented and it's meaning is, err, meaningless.

Cheers

Holger Received on Wed Mar 02 2005 - 06:12:38 CST