Re: alter system archive log stop;

From: Tof <None_at_ForNotSpam>
Date: Wed, 02 Mar 2005 14:48:17 +0100
Message-ID: <4225c5d5$0$11687$8fcfb975@news.wanadoo.fr>

Holger Baer wrote:
> 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

Thanks for your explanation.

I can see in my alert.log
ARCHIVE LOG START has been deprecated - see ALTER DATABASE ARCHIVELOG ARCHIVE LOG STOP has been deprecated - see ALTER DATABASE ARCHIVELOG

I now not use this command.

Thanks for all.

Christophe Received on Wed Mar 02 2005 - 07:48:17 CST