Solved

Shutdown a Oracle 9i DB

Posted on 2004-09-28
9
10,473 Views
Last Modified: 2011-08-18
Hi,

how can I shutdown an oracle 9i db in sqlplus and/or in dbastudio?

I tried this so far:
---
oracle@svr-ora9i:/etc/init.d> sqlplus /NOLOG

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 28 14:20:58 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect sys/secret@db5 as sysdba
Connected.
SQL> shutdown
ORA-00106: cannot startup/shutdown database when connected to a dispatcher
---

Regards,
pb
0
Comment
Question by:psychicblue
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12170449
try the following:

(login as Oracle on server)
sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown
0
 
LVL 12

Expert Comment

by:geotiger
ID: 12170476
You need to add the immediate (or abort) if you do not want to wait:

SQL> connect sys/secret@db5 as sysdba
Connected.
SQL> shutdown immediate
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 20 total points
ID: 12170489
Assuming you are on the machine that the database is on, then when you connect do not specify the @db5.

If you are not on the machine, then you will need to create a dedicated server connection in order to shut down the database.  It appears that you are running shared server, so to connect in dedicated, you need to add

(SERVER=DEDICATED)

in your tnsnames.ora file in the CONNECT_DATA section.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:cjjclifford
ID: 12170805
"shutdown abort" should only be done as a last resort, as the database will perform a recovery on the next startup... If "shutdown abort" is performed, generally its a good idea to "startup" followed immediately with "shutdown" to make sure the shutdown is a clean one...
0
 

Author Comment

by:psychicblue
ID: 12170914
If I use this method provided by cjjclifford

---
(login as Oracle on server)
sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown
---

how will oracle know, what db to shut down? There are about six dbs running on this machine?
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12170999
export ORACLE_SID=xyz
0
 
LVL 11

Accepted Solution

by:
cjjclifford earned 45 total points
ID: 12171008
before doing the "sqlplus /nolog" do the following:
(assuming bash/sh/ksh)
export ORACLE_SID=xyz
0
 
LVL 12

Assisted Solution

by:geotiger
geotiger earned 20 total points
ID: 12173286
See the startup and shutdown script that I posted in this thread:

http://www.experts-exchange.com/Databases/Oracle/Q_21146865.html
0
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 40 total points
ID: 12176172
If you are on the machine that host the database so what you need to do is set the SID environment variable to the SID database as the above posts. If you use the connection string connect you should add SERVER=DEDICATED instead of SERVER=SHARED in the TNSNAMES.ORA and it should work. To startup/shutdown a database, you must connect via a dedicated server process and not a shared process:

How to force a dedicated connection from a client when running Oracle Names:
=================================================
 
Normally, if a client is running multi-threaded server and you want to force a  
connection to be dedicated you would add the SERVER=DEDICATED parameter to the  
tnsnames file:  
 
<service name> =  
  (DESCRIPTION =  
    (ADDRESS_LIST =  
        (ADDRESS =  
          (COMMUNITY = <community>)  
          (PROTOCOL = <protocol>)  
          (Host = <server>)  
          (Port = 1526)  
  )  
    )  
    (CONNECT_DATA =  
     (SID = <sid>)  
       (SERVER=DEDICATED)           <---  
    )  
 
If the client is running Oracle Names they don't have a tnsnames.ora file to  
put the (SERVER=DEDICATED) parameter into.  
 
Instead of reconfiguring Oracle Names to put this parameter in, you can add  
the (USE_DEDICATED_SERVER=ON) parameter to the sqlnet.ora file on the client.  
 
AUTOMATIC_IPC = ON  
SQLNET.EXPIRE_TIME = 10    
USE_DEDICATED_SERVER=ON               <---  
NAMES.DIRECTORY_PATH = (ONAMES)  
NAMES.DEFAULT_DOMAIN = World    
NAMES.PREFERRED_SERVERS =  
        (ADDRESS_LIST =  
          (ADDRESS =  
            (PROTOCOL = TCP)  
            (HOST = GALAXY)  
            (PORT = 1527)  
          )            
        )  
 
 
Other sulutions include:
 
Use the Net8 Assistant or Netmgr to alter the Oracle Names data.  New  
services names can be explicitly set to use SERVER=DEDICATED.  Service names  
stored in Names that do not have SERVER=DEDICATED set can be dumped to a  
TNSNAMES.ORA file with the NAMESCTL dump_tnsnames command on version 8.1.5  
and higher.  The TNSNAMES file can be altered with the Net8 Assistant or  
Netmgr and then re-uploaded into Oracle Names.
 
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

738 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