Problems connecting via OEM...only a restart works.

[MikeTruscott]

Hi everyone......I'd be interested to hear people's thoughts on a problem I've been having.

Firstly, I'm not an experienced ORACLE guru! I can get by using the GUIs and SQL to do basic stuff but as soon as it gets complicated I'm stuck.

Anyway.....I have some databases which I created using the Database Configuration Assistant. Sometimes I can connect to them and sometimes I can't. The only way I can get round this at the moment is to restart the machine, but the last instance of the problem took about 7 restarts!

Obviously this is not ideal. Can anyone think of what the problem might be and provide an easier way to fix it please?

Additional info which may or may not help......There are 4 databases, one of which has millions of lines in and three which are very very small. One of the small ones doesn't appear in the services list so I'm wondering if I haven't deleted it properly and ORACLE is getting upset.

Any help much appreciated.

Cheers

Mike

 

[Thargy]

Mike,

I work on windoze, so can't help if you're on unix.

From the command prompt enter "SET ORACLE_SID = MYDBNAME"
and then sqplus /nolog.

Connect with username and password and you should be ok.

The underlying problem that I have found is dynamic registration of databases with the listener. This is where the database PMON process registers the db with the listener on startup.

I have had no end of trouble with this, and now manually make entries in the listener.ora file to statically (and permanently) register my databases.

Let me know if this works.

Regards

Tharg

Grinding away at things Oracular

 

[MikeTruscott]

Hi Tharg, thanks for replying.

So, I went into DOS and typed

"set oracle_sid = archive"
"sqlplus/nolog"
"connect <username>/<password>@archive"

...and it just hangs.

I'll have a look at the listener.ora file now to see if I can understand that!

Cheers

Mike

 

[Thargy]

Mike,

rather than restarting the machine, restart the service of archive, and that should dynamically register it.

sqlplus should then be able to "see" archive. If it really does need a hard server reboot, I'd be most surprised.

Let me know how you get on.

Regards

Tharg

Grinding away at things Oracular

 

[MikeTruscott]

Hi Tharg,

I tried restarting the service of ARCHIVE, stopped the other services and also stopped & restarted the listener service....but it still didn't work.

Couldn't connect through SQLPlus or the OEM.

It only took two restarts this time though.

Cheers

Mike

 

[Thargy]

Mike,

if you stop and restart the listener, any dynamically registered databases will be lost. Don't restart the service AND the listener, leave the listener running so that it's there when the database tries to dynamically register itself.

Regards

T

Grinding away at things Oracular

 

[MikeTruscott]

In case anyone reads this and wondered if it solved the problem.
------------------------------------------------

Haven't posted a reply until now as the problem went by itself. Now it's back!

Restarting only the service of the database didn't work either. SQLPlus and the enterprise manager both still hang when I try to log on.

Tried the command prompts again and they didn't work either.

I'm sure it'll temporarily fix itself but it'd be interesting to know what's going on.

Thanks for everyone's help to date!