Solved

Oracle does not start due to setup of log_archive_dest_1 in init.ora !

Posted on 2003-10-30
21
9,132 Views
Last Modified: 2007-12-19
I have Oracle 8i installed on a Windows NT server.
The database is currently NOT in archivelog mode (checked with DBA Studio).
The instance used the init.ora from which I have the 3 following COMMENTED lines :

# log_archive_start = false                                    
# log_archive_dest_1 = "D:\oracle\oradata\radon\archive"
# log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

Now if I uncomment some of these lines, and stop/start the DB, the DB still works EXCEPT if the second line (log_archiv_dest_1) is uncommented. No idea why.
Even if there is an error on this line,I can not understand why it seems to take that into account as anyway the archivelog mode is NOT set and the line before says log_archive_start = false !
ps : I checked that the indicated location (D:\oracle...) really exists.
0
Comment
Question by:LeTay
  • 8
  • 5
  • 3
  • +3
21 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
Set the initialization parameters for the  database:
 
       Primary INIT.ORA:
   
       log_archive_dest_1='LOCATION=<Path local Archivelogs are stored'
       
       log_archive_dest_state_1=enable
       
       log_archive_format=arch_%t_%s.arc

       log_archive_start=true




SQL> archive log list
             Database log mode    No Archive Mode
            Automatic archival   Disabled
            Archive destination  
                  /u01/app/oracle/product/8.1.7/dbs/arch
            Oldest online log sequence     19
            Current log sequence           21       

       SQL> alter database close;  
      
       Database altered.
 
       SQL> alter database archivelog;      
 
       Database altered.
 
       SQL> shutdown immediate  
                   
       ORA-01109: database not open
 
       Database dismounted.

       ORACLE instance shut down.

    At this point create (if not already created) the new mount point
    /u03/oradata/oralin1/arch and give access to the oracle user to this directory.
 
    Modify the Primay database init.ora so that log_archive_start=true and  
    specify a Archivelog Destination (log_archive_dest_1=/u03/oradata/oralin1/arch then
    restart  the instance. Verify that database is in archive log mode and that  automatic
    archivingis enabled.
 
       SQL> archive log list
       Database log mode            Archive Mode
       Automatic archival                   Enabled
       Archive destination            /u03/oradata/oralin1/arch
       Oldest online log sequence      x
       Next log sequence to archive      y
       Current log sequence            y
0
 

Author Comment

by:LeTay
Comment Utility
I will try all this and keep you informed, but do you mean (I run on Windows) that the syntax of my log_archive_dest_1 is not correct ?
0
 

Author Comment

by:LeTay
Comment Utility
Schwertner,
I did what you wrote.
I added changed the log_archive_dest_1 as follows, including the location=

log_archive_dest_1 = "location=D:\oracle\oradata\mila\archive"

But again, when starting it, it fails (you don't see it directly, but via DBA studio you see that it stops after start and does not mount the database !
When again commenting these, it works.
0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
also, check these parameters:

log_archive_dest_1 = "location=D:\oracle\oradata\mila\archive mandatory'

log_archive_dest_state_1=enable

log_archive_min_succeed_dest = 1

log_archive_max_process = 2

and then, read your alert.log file to see what's happened.

0
 

Author Comment

by:LeTay
Comment Utility
I changed and added lines as you told me : here they are (copy/paste)

log_archive_dest_1 = "location=D:\oracle\oradata\mila\archive"
log_archive_dest_state_1 = enable
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC
log_archive_start = true
log_archive_min_succeed_dest = 1
lot_archive_max_process = 2

Now the result is the same (the db does not start).
There is even not .LOG file in any of the dump directories (cdump, bdump, udump) !
0
 
LVL 2

Accepted Solution

by:
RRainville earned 400 total points
Comment Utility
Are you using Enterprise or Standard edition?

you cannot use <<log_archive_dest_1>>  in Standard edition

you should use
log_archive_dest="D:\oracle\oradata\radon\archive"
0
 

Expert Comment

by:alanbk
Comment Utility
Try adding a \ at the end of your path.  I have seen this on 8.05

If you are using :
log_archive_dest_1 = "location=D:\oracle\oradata\mila\archive"

Change it to:
log_archive_dest_1 = "location=D:\oracle\oradata\mila\archive\"
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
I think RRainville's remarks are right on the money.

Use "log_archive_dest" instead
0
 

Author Comment

by:LeTay
Comment Utility
Seazodiac, RRainville,

The version I use is the standard edition 8i.
Based on your suggestions I have tried the 4following syntaxes :

log_archive_dest_1 = "D:\oracle\oradata\radon\archive"
log_archive_dest_1 = "D:\oracle\oradata\radon\archive\"
log_archive_dest = "D:\oracle\oradata\radon\archive"
log_archive_dest = "D:\oracle\oradata\radon\archive\"

and always the same (bad) result, Oracle is not running.

The Oracle8i DBA Handbook of Kevin Loney and Marlene Theriault mention at page 55/56 that the log_archive_dest = is obsolete since 8i (does not mention which edition, so I guess both). This looks okay as the init.ora that has been automatically provided at installation was :

log_archive_dest_1 = "D:\oracle\oradata\radon\archive"

What is also really strange is that there seem to be no log (trace). Or maybe I do not look at the right place (currently d:\oracle\admin...)

0
 
LVL 2

Expert Comment

by:RRainville
Comment Utility
Hi LeTay

unfortunately I don't have a standard edition underhand to test with
but I clearly remember having problems
with log_archive_dest_n parameters in 8i standard
but no problems at all on 8i Enterprise

BTW exactly which version are you running? 8.1.5,  8.1.6,  8.1.7?
8.1.6 had lots of bugs and should be immediately upgraded to 8.1.7

And why aren't you running in Archivelog mode?

Have you tried starting up with another pfile with only base parameters?

Is background_dump_dest set in your pfile? this is where your alert<SID>.log file should be

hehe more questions.... but we'll get to the bottom of this

cheers
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
Comment Utility
Guys, init.ora doesn't allow to put spaces aroun = character. All examples above use spaces around =


Problem Description
-------------------
 
You are using the log_archive_dest_n parameter in your pfile:
 
log_archive_dest_1 = 'location = /u01/oracle/arc'  
 
and are receiving an:
    ora-16024 'parameter log_archive_dest_1 cannot be parsed'.
 
You know that /u01/oracle/arc is a valid directory and that Oracle has
write permissions there.  
 
 
Solution Description
--------------------
Check the following:
 
1.  Remove the spaces on either side of the '=' sign. You cannot have
    as space on either side on the '=' sign. Note: The word location
    can be in either upper or lower case.
 
          log_archive_dest_1 = 'location=/u01/oracle/arc'  
 
2.   Make sure that there is no trailing /.  
     
3.   Make sure that LOG_ARCHIVE_DEST is not set in the init.ora. These 2
     parameters are not compatible with each other.
 
4.   If still not working set the LOG_ARCHIVE_DEST_n parameter to a null
     value - LOG_ARCHIVE_DEST_n=('')
0
 

Author Comment

by:LeTay
Comment Utility
Indeed running 8.1.7.0.0
background_dump_dest is specified correctly.
But now I am becoming completely crazy with the first mail of schwertner
The db is running normally (no archivelog).
I do the following from a prompt :

svrmgrl.exe
connect internal as sysdba
shutdown immediate
startup exclusive
alter database close
I get error message: alter database close only permitted with no sessions connected !!!
0
 
LVL 2

Expert Comment

by:RRainville
Comment Utility
Can you put your DB in ArchiveLog mode and retry

ie

sqlplus /nolog     or svrmgrl

connect internal
spool d:\thisattemp.log

shutdown immediate;
startup mount;
alter database archivelog;
shutdown immediate;

(set log_archive_dest_1="D:\oracle\oradata\radon\archive")  in your init.ora

startup pfile=<path to your init.ora>;

spool off

and come back with any error messages and the result in d:\thisattemp.log

cheers

0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
Here is a full definiton for LOG_ARCHIVE_DEST from oracle.

LOG_ARCHIVE_DEST
Parameter type:  String
 
Syntax:  LOG_ARCHIVE_DEST = filespec
 
Parameter class:  Dynamic. Scope = ALTER SYSTEM.
 
Default value:  None  
 
Range of values:  Any valid path or device name, except raw partitions
 
Oracle Parallel Server:  Multiple instances can have different values.
 
Note: For Enterprise Edition users, this parameter has been deprecated in favor of the LOG_ARCHIVE_DEST_n parameters. If Oracle Enterprise Edition is not installed or it is installed but you have not specified any LOG_ARCHIVE_DEST_n parameters, this parameter is valid.
 
LOG_ARCHIVE_DEST is applicable only if you are running the database in ARCHIVELOG mode or are recovering a database from archived redo logs. LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters, and must be defined as the null string ("") or (' ') when any LOG_ARCHIVE_DEST_n parameter has a value other than a null string. Use a text string to specify the default location and root of the disk file or tape device when archiving redo log files. (Archiving to tape is not supported on all operating systems.) The value cannot be a raw partition.

If LOG_ARCHIVE_DEST is not explicitly defined and all the LOG_ARCHIVE_DEST_n parameters have null string values, LOG_ARCHIVE_DEST is set to an operating system specific default value on instance startup.
 
To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the SQL*Plus command ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination. To permanently change the destination, use the command ALTER SYSTEM SET LOG_ARCHIVE_DEST = filespec, where filespec is the new archive destination. See Also:

Oracle8i Backup and Recovery Guide.

"LOG_ARCHIVE_DUPLEX_DEST", "LOG_ARCHIVE_MIN_SUCCEED_DEST", and "V$ARCHIVE_DEST" for more information on setting this parameter.

Your Oracle operating system specific documentation for the default value and for an example of how to specify the destination path or filename using LOG_ARCHIVE_DEST.  

0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
You should set

     ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;  
     ALTER SYSTEM SET AQ_TM_PROCESSES=0;  

to avoid message: alter database close only permitted with no sessions connected !!!


STARTUP MOUNT;  
     ALTER SYSTEM ENABLE RESTRICTED SESSION;  
     ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;  
     ALTER SYSTEM SET AQ_TM_PROCESSES=0;  
     ALTER DATABASE OPEN;  
0
 

Author Comment

by:LeTay
Comment Utility
RRainville,
Here is the 'thisattemp.log' using the command you told me
Everything was fine except the startup pfile=...
that generates error ORA-00439


Connected.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area                         55609372 bytes
Fixed Size                                          75804 bytes
Variable Size                                    54636544 bytes
Database Buffers                                   819200 bytes
Redo Buffers                                        77824 bytes
Database mounted.
Statement processed.
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
ORA-00439: feature not enabled: Managed Standby
0
 
LVL 2

Expert Comment

by:RRainville
Comment Utility
have you set
standby_archive_dest?
guess not

use LOG_ARCHIVE_DEST instead of LOG_ARCHIVE_DEST_1
and it should start this is the error I was talking about in my first post


0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
LeTay:

RRainville gave your the right direction.
Now the error message is quite obvious:

you are running the Standard edition and It does not have Managed Standby feature.

In your init<SID>.ora file,
you should comment all the "LOG_ARCHIVE_DEST_N" parameter.
only leave these lines if you want still the archivlog mode:

Log_archive_start=true
log_archive_dest=/path/to/archive/folder
log_archive_format=%s_%t.arc


that's all, restart the database
0
 
LVL 2

Expert Comment

by:RRainville
Comment Utility
Here's a post from MetalLink about that problem
<<
All RDBMS products/versions support achiving and standby databases are supported with Standard Edition. Managed Standby is only available with Enterprise Edition so the use of multiple archive destinations (log_archive_dest_n) is only available for Enterprise Edition. The parameter to specify with Standard Edition is LOG_ARCHIVE_DEST. The following article addresses the problem when using the DBCA to create your database.

160475.1 Starting Database Fails With ORA-00439, ORA-01034, Created by the Database Configuration Assistant Wizard, Oracle Server Standard Edition 8.1:

This note discusses multiple archive destinations:

66433.1 Oracle8i - Multiple Archive Destinations and Remote Archiva

Melissa Holman
Oracle Support

>>
0
 

Author Comment

by:LeTay
Comment Utility
Summary of what I tried now, mainly based on Rrainville info :
svrmgrl
connect internal as sysdba
shutdown immediate;
startup mount;
alter database archive log;
--- here I uncommented the log_archive_dest and did NOT put the _1 suffix
--- I also uncommented the log_archive_format and set log_archive_start = true
startup pfile=<this changed init.ora...>
--- I got the following error lines on the screen :
--- ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated
--- ORA-09291: sksachk: invalid device specified for archive destination
--- OSD-04018: Unable to access the specified directory or device.
--- O/S-Error: (OS 123) The filename, directory name, or volume label syntax is incorrect.
--- Now I changed again the init.ora and I PUT the suffix _1 to log_archive_dest
--- and again issued :
startup pfile=<this changed init.ora...>
--- I again I got the message I mentionned previously :
ORA-00439: feature not enabled: Managed Standby

... I am becoming crazy
0
 

Author Comment

by:LeTay
Comment Utility
No, not so crazy
I restarted all this and put exactly what Rrainville suggested on October 30,
log_archive_dest="D:\oracle\oradata\radon\archive"
So without _1 and without that 'location='
And now ... it WORKS
So I'll accept RRainville answer !
Anyway, thanks to all of you

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now