Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am wondering what is the quickest way to properly startup/shutdown a Oracle DB daemon (Oracle 11.2, installed on a test machine).

I need it for C/C++ programs that use the OCI/Pro*C API.

Currently I script it like this - startup:

sqlplus /nolog <<EOF
connect / as sysdba
startup
quit
EOF
lsnrctl start
emctl start dbconsole

And shutdown:

emctl stop dbconsole
lsnrctl stop
sqlplus /nolog <<EOF
connect / as sysdba
shutdown
quit
EOF

This works - programs work as expected - but this procedure is quite slow.

The Oracle DB runs on CentOS 6.3, it is the free (as-in-beer) available 'standard version'.

share|improve this question
1  
Why would you need to shutdown the database as quickly as possible? –  Yasir Arsanukaev May 22 '13 at 8:10
    
Database startup and shutdown are "slow" operations, they're actually quite complex. You could try other shutdown modes but you need to be aware of the tradeoffs. Why don't you leave the database server running? –  Mat May 22 '13 at 8:11
    
@YasirArsanukaev,@Mat, because I am used to the startup speed of Postgresql and because the daemon runs in a virtual machine which is only started (on-demand) for test cases. –  maxschlepzig May 22 '13 at 8:20
3  
Can you just pause (suspend) your VM saving its state to disk? –  Yasir Arsanukaev May 22 '13 at 8:22

2 Answers 2

up vote 0 down vote accepted

You can use the dbstart/dbstart scripts which come with an Oracle install. They are available under $ORACLE_HOME/bin.

After a fresh install you have to edit the /etc/oratab file:

# cat /etc/oratab
# format: $ORACLE_SID:$ORACLE_HOME:N|Y
my_sid:/home/juser/app/juser/product/11.2.0/dbhome_1:N
# sed -i 's/:N$/:Y/' /etc/oratab
# grep my_sid /etc/oratab
my_sid:/home/juser/app/juser/product/11.2.0/dbhome_1:Y

Then you can use the scripts like this:

$ whoami
juser
$ dbstart $ORACLE_HOME
$ # execute DB jobs ...
$ dbshut $ORACLE_HOME

dbstart brings all up which is needed for Pro*C/OCI programs.

Using dbstart/dbshut is an improvement above the custom method mentioned in the question:

method                time    called tools
―――――――――――――――――――――――――――――――――――――――――――――――――――――
dbstart              5.7 s    lsnrctl, sqlplus
dbshut               5.7 s    lsnrctl, sqlplus
custom startup      27.9 s    lsnrctl, sqlplus, emctl
custom shutdown     31.0 s    lsnrctl, sqlplus, emctl

(times on a Core i7/2.8GHz system, slow spinning hard disk.)

How dbstart/dbshut work

A dbstart $ORACLE_HOME$ call is basically equivalent to:

$ lsnrctl start
$ echo -e 'connect / as sysdba\nstartup\nquit'| sqlplus /nolog

And a dbshut $ORACLE_HOME$ is basically equivalent to:

$ lsnrctl stop
$ echo -e 'connect / as sysdba\nshutdown\nquit'| sqlplus /nolog

(you can verify if everything is shutdown via ps aux | grep 'tnsl\|ora')

Note that the order of the commands is important. That means when lsnrctl start is executed after the sqlplus-startup command then the Pro*C/OCI program still complains about an unavailable TNS-listener.

And this is exactly the problem with the command sequence in the question - where the emctl start just workarounds the wrong order because it fixes the TNS-listener setup part.

Also note that for executing Pro*C/OCI programs the EMCTL service is not needed.

share|improve this answer
    
dbstart and dbshut don't startup/shutdown EM, so you can't even compare it with the OPs script. In fact, if you look at the source for dbstart/dbshut it actually uses sqlplus to do its work in the same way as the custom script in the question, which in itself means it's impossible for dbstart to be faster. I'm guessing your "custom" tests were performed first, which pulled everything into cache thus making your dbstart test faster. Feel free to prove me wrong! My try: pastebin.com/E0yAVuJ0 Not a great deal of difference. Incomparable. –  Phil May 22 '13 at 23:28
1  
@Phil, the 'custom' methods are the command sequences from the question (i.e. sqlplus call + lsnrctl + emctl) - if dbstart/dbshut don't call emctl and emctl is not needed for running Pro*C/OCI programs then this 1) explains perfectly the time differences and 2) gives some insight on what of Oracle is actually needed to execute Pro*C/OCI programs. Because I didn't know that I've asked the question in the first place. I'll update the answer. –  maxschlepzig May 23 '13 at 9:17

Why do you need it at all? What is the reason? For shutdown you have 3 options:

  • plain shutdown - waits for sessions end
  • shutdown immediate - rollbacks and ends sessions (waits for rollback finish)
  • shutdown abort - kills everything - usually database has to perform some recovery on startup.

PS: you probably do not need Enterprise manager to run, if you need the database for tests only. Use the Oracle dbstart/dbshut scripts. If you really need EM, start it in the background.

share|improve this answer
    
Didn't know dbstart/dbshut - what should be the exact command line for dbstart? dbstart $ORACLE_HOME ? With that I get 'SQL-Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor'. –  maxschlepzig May 22 '13 at 10:57
    
dbstart uses config file /etc/oratab (colon delimited). The last field in it contains 'Y' (start db) or 'N' (do not start db). The latest version of this script also generates a startup log. –  ibre5041 May 22 '13 at 12:38
    
@Mat yes you're right, I will update the answer. –  ibre5041 May 24 '13 at 7:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.