You can use the dbstart
/dbshut
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.
Check the secure log in the event it was run via a sudo service call
sudo grep mysql /var/log/secure
Check to see if there's any mysql related stuff going on in cronjobs
sudo grep mysql /var/log/cron
Check shell histories for mysqladmin calls
cd /home; for u in *: do; sudo grep mysql /home/$u/.bash_history; done
Check with people you know that either have sudo or mysql root access on this machine
"Hey, which one of you has been shutting down mysql?"
Best Answer
It seems like the obvious answer is that you can't stop a shutdown that is happening, but you can do either a shutdown immediate or shutdown abort from a different session if you did a shutdown normal. As long as you have enough online redo log groups and each group has a log that is large enough, then you can recover from either a shutdown immediate or shutdown abort. If you ask someone what is the exact risk that someone faces for doing a shutdown abort, they may struggle to answer you. On shutdown immediate transactions are rolled back as sessions are killed. On shutdown abort, all transactions are rolled back when the database is started up. Hence the same thing should be happening in both cases, just in a different order. Once you start a shutdown normal, either ask everyone to log out, or just do shutdown immediate in a different session. Either way, Oracle won't allow a database to open, if it isn't in a consistent state.