Home » RDBMS Server » Server Administration » cannot startup database from sqlplus client (unix, oracle 10g)
cannot startup database from sqlplus client [message #296567] Mon, 28 January 2008 05:03 Go to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

Sir,

I logged in into sqlplus client as sysdba. (usernm/pwd@service as sysdba)

On shutdown and startup I get the error ORA-12514. Why so?

just see the following

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


....by the way, I can shutdown and startup from the server it gives no error.


After a faulty shutdown from sqlplus client, I can see that the instance is actually not properly shutdown. The database is properly closed and dismounted. From the server when I log in with ( / as sysdba)

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1261224 bytes
Variable Size 218104152 bytes
Database Buffers 37748736 bytes
Redo Buffers 7127040 bytes
Database mounted.
Database opened.

....why this difference (between client login and server login)??
Re: cannot startup database from sqlplus client [message #296584 is a reply to message #296567] Mon, 28 January 2008 05:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
Action: - Wait a moment and try to connect a second time.

- Check which services are currently known by the listener by executing: lsnrctl services <listener name>

- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

- Check for an event in the listener.log file.



Thumbs Up
Rajuvan.
Re: cannot startup database from sqlplus client [message #296590 is a reply to message #296567] Mon, 28 January 2008 06:15 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
the client needs address of the server.
That address is specified in the tnsnames.ora file of the client.

In the same way,the srever needs to know what clients are giving requests.
So the client information is stored in listener.ora file of the srever.


In your case,you need to modify your listner.ora file on the server with the information of the client.
Re: cannot startup database from sqlplus client [message #296597 is a reply to message #296567] Mon, 28 January 2008 06:33 Go to previous messageGo to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

Thanks.. but the mystery is not yet solved Sad


I can log in into the instance kol_test and work with the database cleanly using the oracle client.. it means the listener is up, no doubt!!.. when I use

UNIX_PROMPT> lsnrctl services LISTENER

I get as one of the entries,

Service "kol_test" has 1 instance(s).
Instance "kol_test", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER



My tnsname.ora has the entry

kt =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = kol_test)
)
)



So, why is the problem only for shutdown and startup commands??
Re: cannot startup database from sqlplus client [message #296607 is a reply to message #296597] Mon, 28 January 2008 06:43 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

OP,
It is bug in 10g.
it is becuase of dynamic registration of listener.
it is only happen when we use "tns entry" with "sys as sysdba" to connect to database.

you can avoid to change listener registration type from dynamic to static.
like below
http://dbataj.blogspot.com/2007/02/ora-12514.html

And it is not with client side even if we connect on Server with "tns entry" with sysdba we also getting same error message during "startup".

Regards
Taj
Re: cannot startup database from sqlplus client [message #296734 is a reply to message #296567] Mon, 28 January 2008 21:54 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Yes , in dbstart script listener location is hard coded. I raised this issue a few months ago.
Re: cannot startup database from sqlplus client [message #296811 is a reply to message #296567] Tue, 29 January 2008 02:35 Go to previous message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

thanks to all of you..

special thanks to Mohammad Taj... your blog really helped!
Previous Topic: Metrics "Database Time Spent Waiting (%)" is at 30.09256 for event class "Commit"
Next Topic: Metrics "Database Time Spent Waiting (%)" is at 49.71505 for event class "Network&quo
Goto Forum:
  


Current Time: Wed Dec 07 06:57:54 CST 2016

Total time taken to generate the page: 0.11818 seconds