The aim when shutting down for maintenance (or cold backup) is that the database is left in a consistent state with no need for rollback/recovery on startup.
There are 3 SQL*Plus shutdown
commands that achieve this in theory, all of which immediately prevent new sessions connecting to the instance:
shutdown normal
or just shutdown
: waits for all sessions to disconnect. This mode is rarely used in practice because it relies on well-behaved clients not leaving connections open. This used to be the only shutdown
mode that did not cancel running transactions.
shutdown transactional
: disconnects sessions once currently running transactions complete, preventing new transactions from beginning.
shutdown immediate
: disconnects all sessions immedately and rolls back interrupted transactions before shutting down. Note that the disconnections are immediate, but the shutdown may not be as any interrupted transactions may take time to roll back.
The fourth mode of shutdown
is shutdown abort
. This is like pulling the power cord - the instance stops now without any cleanup. You usually want to bring the database up again afterwards and shut down cleanly immediately afterwards as in your example. The concepts guide says:
This mode is intended for emergency situations, such as when no other form of shutdown is successful.
All the examples you give perform a checkpoint as part of the shutdown [normal]
or shutdown immediate
so explicit checkpointing is presumably to reduce the time required for recovery.
general advice:
- Do not use
shutdown normal
.
- Use
shutdown transactional
for attended shutdown only, when you want to minimise cancelled transactions (attended only because this kind of shutdown is not guaranteed to shut the database down at all if timeouts are breached).
- Use
shutdown immediate
for unattended shutdown or when you do not care about currently running transactions.
- Do not use
shutdown abort
(plus startup/shutdown) unless you have to - this was more common in much earlier versions of Oracle that it is today. In other situations (not patch/upgrade), if you have a need to minimise downtime this mode may be appropriate.
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.
Best Answer
Your output shows hugepages were not configured, but the database is configured to use only large pages (
use_large_pages=only
), hence it can not start.Configure the required amount of pages by setting the
vm.nr_hugepages
in/etc/sysctl.conf
, the appropriatememlock
limit in/etc/security/limits.conf
, then reboot and start the database.Configuring HugePages on Linux
If you do not need hugepages, simply edit the parameter file of the database, and unset the
use_large_pages
parameter.