Vanishing database that's right there

[Thargy]

I have a Marie Celeste database at both home and work.

Home = windows xp and 10g2 installation
Work = Windows server 2003 and 10g2 installation.

At home, if I create a database I can connect to it using sqlplus, sqplusw, TOAD and the em console. If I restart the machine, I have to use "SET ORACLE_SID=HOME1" at the command line, then "sqlplus /nolog", followed by 'connect / as sysdba' to get a connection. I can successfully start the console and use the beastie. I can TNSPING the database, and TOAD is reporting a valid oracle home. Any ideas why this is happening? Sqlplusw reports "ORA-12545: connect failed because target host or object does not exist" despite the fact that I know for absolutely certain it does, because I am seeing the console!

At work, Ihave a database that once shutdown, won't restart from SQLPLUSW, and the machine reports a TNS error. If I restart the oracle service all is well. However, this makes it impossible to issue an "alter database" instruction, and then restart for it to have an effect. Any inspiration guys?

Regards

Tharg

Grinding away at things Oracular

 

[dbtoo2001]

Do you have an environment variable for ORACLE_SID? If not, make one, if so, is it correct?

 

[DrD123]

Hi thargtheslayer

A couple of things to try...

Is the Listener started for your dB?

You SQLPLUSW won't pick up what you set in a command line. As dbtoo2001 mentioned you need an environmental variable set for your dB.

Also did you create a connect string for the dB? If you did you wouldn't need the env variable, just use the connect strings either at command line or SQLPLUSW :

sqlplus user/pw @connect_string

Or for the SQLPLUSW, type in user and pw and connect_string in the appropriate forms.

Good luck,
DrD

 

[Thargy]

The plot thickens.

At work now, and on the windows 2003/10g2 installation, I have the same symptoms, i.e. the database can be shutdown, but can't be connected to afterwards, unless the ORACLE_SID variable is set.

I understand that dynamic service registration takes place when a db starts. This would explain why I can always get a connection after restarting the windows service in question.

However, a perusal of the listener log (which had bloated to 2 gigabytes shows the following:-

30-AUG-2007 15:12:31 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=V7-SVR-004.V7TEST.local)(PORT=1521)))(VERSION=169869568)) * status * 0
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PM_EMPTY)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4489)) * establish * PM_EMPTY * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PM_EMPTY)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4490)) * establish * PM_EMPTY * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PM_EMPTY)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4492)) * establish * PM_EMPTY * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PM_EMPTY)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4493)) * establish * PM_EMPTY * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PM_FULL)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4495)) * establish * PM_FULL * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PM_FULL)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4496)) * establish * PM_FULL * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PM_FULL)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4498)) * establish * PM_FULL * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PM_FULL)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4499)) * establish * PM_FULL * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=beta6)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4502)) * establish * beta6 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=beta6)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4503)) * establish * beta6 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=beta6)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4505)) * establish * beta6 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=beta6)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4506)) * establish * beta6 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
30-AUG-2007 15:12:40 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SID=PMFULL)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.33)(PORT=4510)) * establish * PMFULL * 12505
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

Can anyone enlighten me as to what is causing the listener to continually attempt to connect services. Is it the automatic PMON registration repeating incessantly?



Grinding away at things Oracular

 

[dbtoo2001]

Is beta6 actually in the listener.ora file?

 

[Thargy]

Folks,

I have the same problem on another 10g server. I am struggling to find any rhyme or reason for this.

One question comes to mind, concerning dynamic registration.

If a db is shut down by the user, then I presume it will deregister from the listener. How then, once registration is lost, can one restart the database, since the listener will have no knowledge of it?

Regards

Tharg

Grinding away at things Oracular

 

[dbtoo2001]

Are the instances in the listener.ora file?

You can manually register the instance
alter system and register to the listener, but pmon should be doing that.


REGISTER Clause

Specify REGISTER to instruct the PMON background process to register the instance with the listeners immediately. If you do not specify this clause, then registration of the instance does not occur until the next time PMON executes the discovery routine. As a result, clients may not be able to access the services for as long as 60 seconds after the listener is started.


Post your listener.ora file (not listener LOG file.)

 

[Thargy]

thanks for your suggestion.

I am now using static registration by making entries int the SID_LIST_LISTENER section of the listener.ora file. So far it seems to be working.

Since I'm in dev and test, I destroy and rebuild databases several times a day. Unlike a production environment, where a db would come up and stay up. I think that dev/test is probably best done with static registration.

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

I destroy and rebuild databases several times a day.

I believe I discovered the cause of your "Vanishing database". <silly grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Tee Hee

Grinding away at things Oracular