Starting a database...

[cjohnson6]

I'm a fairly novice user of Oracle but having used SQL Server 7.0 and 2000, i'm very familiar with relational databases and structure. However, i am having problems with an Oracle server that is confirmed to be started and running. the proper tnsnames.ora is in place with the correct entries and i've also confirmed the listener.ora file to be intact.
my problem is after going over these checkpoints, i am still unable to connect to the database via SQLPlus. other databases added to the same tnsnames.ora file can be accessed except one. The error being rec'd is that the Oracle server is not even available. (??)
Has anyone experienced this before and know what other avenues i can double check?
Any assistance would be appreciated.

Thank you,
Claude Johnson
claude_johnson@s2systems.com

 

[karluk]

This sort of error can be very frustrating. Things check out ok, but you still have a problem.

My guess is that you have a problem in one of the areas you mention, even though you think everything is ok.

1. How do you know that Oracle is "confirmed to be started and running"? Are other users able to connect? If so that would definitely confirm that your database is up, and that the problem is confined to your connection. Otherwise I would reserve judgment on whether Oracle is really up. After all the error message seems to be telling you that it's down.

2. Check your alert log for error messages. If the database crashed you will most likely see an error message here.

3. Check the listener log to verify that your connection attempt actually arrived at the listener. Incidentally that should also confirm that the listener is running on your server.

4. Double check your tnsnames.ora and sqlnet.ora entries. If you have client machines that have been able to successfully connect to this database I would copy these files from those machines.

5. Check for firewall or other security problems. Even if Oracle is running on the server you may not have the proper permissions to connect.

6. Don't be reluctant to include in your post the exact error number that you are seeing (eg ORA-11111). Oracle's documentation is geared towards crossreferencing these error numbers, why they occur, and how to resolve them. It may seem descriptive to you to describe the error as "the Oracle server is not even available", but it's actually more helpful to know the error number.

 

[Guest_imported]

The actual error I am receiving is:

"ORA-01034 Oracle not available"

I know the services and listener are started as i tested the connection to other working databases that are in use now. I checked first through Control Panel, Services and all connections to our Oracle databases are started automatically when the system is started. No manual startups. i've even gone as far as stopping the services and restarting manually to see if it made a difference. The next test was through Oracle's Net8 Assistant and testing via Test Net to ensure the proper SIDs were being referenced.

I hope this info helps. As of this morning the results were the same. Any help or advice you can provide is greatly appreciated.

Claude Johnson
claude_johnson@s2systems.com

 

[karluk]

Try to connect using internal or some other id that has sysdba privileges. Sysdba's don't get the ORA-1034 error, so you will either connect successfully or get some other error.

If you get in please query V$INSTANCE. The STATUS should be OPEN and LOGINS=ALLOWED. Also INSTANCE_NAME should match the SID entry in your tnsnames.ora.

Most of my previous suggestions still apply. The alert log should show the database starting up at the time you stopped and restarted the Oracle services. If there is no startup, or if there was an error that prevented startup from completing successfully, that should be reflected in your alert log.

 

[raygg]

You do not say whether this is an NT or Unix platform and you do not mention the Oracle version. I presume you are on NT. Check the start>control panel>system> environment tab and look at the path -or- go to the MSDOS window and type in PATH. The first entry on the path should be the for the Oracle database you just installed. If you or someone installed another Oracle product after the database was installed, then you may have to alter the path to get the Oracle database listed as the first entry.

Sometimes it helps to open the MSDOS window - enter LSNCTL (for 8i, LSNCTL80) for Oracle 8) to get the LSNCTL> prompt. Then enter STATUS to see if the services you set up are working as intneded. Sometimes by entering STOP and then START the services will start up properly.

Does another Oracle EE version exist on the same server? If one does, then you have some listener issues that need modification.

 

[jaymethunt]

I found that on NT, you must explicity tell the OS what the oracle_sid is. Go to System Properties and find the Environment Variables. Make sure you've got a valid SID for the value of oracle_sid. If you don't have, you will need to delete the reference and create a new one to give NT the valid oracle_sid value.