Automatically starting the DB
Wilson, November 01, 2002 - 8:20 am UTC
I have a problem on Win2000 with this whenever the server restarts, the db does not yet the services are started. Secondly, when another user with administrative rights log into the Server after a reboot, the services will be started but the database will fail to open. Until the users who installed the db logs on.
Automatically starting the DB
Wilson, November 01, 2002 - 8:25 am UTC
I have a problem on Win2000 and 8.1.7 with this whenever the server restarts, the db does not start automatically yet the services are showing as started. Secondly, when another user with administrative rights log into the Server after a reboot, the services will be started but the database will fail to open. Until the user who installed the db logs on and then manually start the database.
Oracle not starting automatically in WIN NT/2000
A reader, September 01, 2003 - 7:04 am UTC
I have installed Oracle 8.1.7 in Windows 2000. When I start the windows OS I find the services have been started, but Oracle is not running. So I have to run svrmgrl and startup the database. In 9i on Windows also this problem is happening. How to make Oracle start automatically.
September 01, 2003 - 8:35 am UTC
read the nt admin guides (all available on otn.oracle.com) they describe how to use oradim to set the autostart'ness of your database.
best practice
A reader, October 11, 2007 - 2:18 am UTC
The technical answers are all very well, but what about best practice?
e.g. consider an ASM environment, is it generally considered good practice to configure the server to automatically startup ASM + Database on boot?
Are there any known scenarios under which this would be undesirable, or is it purely a business decision (as opposed to a dba decision).
My manager wants the database to automatically start on boot (in case no DBA is available in time of trouble), but I'm thinking it may be better to have to rely on a DBA or backup DBA role in such scenarios.
Thanks
October 14, 2007 - 9:58 pm UTC
I hate best practice.
why?
because you are compelled to list the 5000 times the best practice does NOT APPLY (this is why i like to give worst practices presentations - they are most close to universally true!)
If the server is to serve oracle data, it would benefit you to startup ASM and then Oracle the database upon restart, sure.
If the server is a database server, I would be hard pressed to not start the database upon a restart of the machine. It seems to make sense.
And - it'll either restart or need the attention of the DBA.
This is why I would say a worst practice would be "to have unattended startup/shutdowns scheduled - what happens when they do not work?"
i dont understand
antonio, December 26, 2007 - 5:40 pm UTC
hi,
my problem is with the scrip $ORACLE_HOME/bin/dbstart,
why i cant connect without tnsname, see
[oracle9@opli09 9.2.0]$ set
BASH=/bin/bash
BASH_VERSINFO=([0]="2" [1]="05b" [2]="0" [3]="1" [4]="release" [5]="i386-redhat-linux-gnu")
BASH_VERSION='2.05b.0(1)-release'
CLASSPATH=/sda/app/oracle9/product/9.2.0/jdk/jre/lib/ext/mail.jar:/sda/app/oracle9/product/9.2.0/jdk/jre/lib/ext/activation.jar:.
DIRSTACK=()
DISPLAY=acantillo.open.com.co:0.0
EUID=516
GROUPS=()
G_BROKEN_FILENAMES=1
HISTIGNORE='&:bg:fg:ll:h'
HISTSIZE=1000
HOME=/home/oracle9
HOST=opli09.xxxx.com.co
HOSTNAME=opli09.xxxx.com.co
HOSTTYPE=i386
IFS=$' \t\n'
INPUTRC=/etc/inputrc
LANG=
LANGUAGE=Spanish_America.WE8ISO8859P1
LD_ASSUME_KERNEL=2.4.19
LD_LIBRARY_PATH=/lib:/usr/lib:/sda/app/oracle9/product/9.2.0//lib:/sda/app/oracle9/product/9.2.0//ctx/lib:/sda/app/oracle9/product/9.2.0//jdk/jre/lib:.:/sda/app/oracle9/product/9.2.0/lib
LESSOPEN='|/usr/bin/lesspipe.sh %s'
LIB_PATH=/sda/app/oracle9/product/9.2.0//lib:/sda/app/oracle9/product/9.2.0//ctx/lib:/sda/app/oracle9/product/9.2.0//jdk/jre/lib:.
LINE=OPENLB:/sda/app/oracle9/product/9.2.0:Y
LOGNAME=oracle9
LS_COLORS='no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:ex=01;32:*.tar=01;31:*.tbz=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.deb=01;31:*.rpm=01;31:*.jpg=01;35:*.png=01;35:*.gif=01;35:*.bmp=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.png=01;35:*.mpg=01;35:*.avi=01;35:*.fli=01;35:*.gl=01;35:*.dl=01;35:'
MACHTYPE=i386-redhat-linux-gnu
MAIL=/var/spool/mail/oracle9
NLS_DATE_FORMAT='dd-mm-yyyy HH24:MI:SS'
NLS_LANG='LATIN AMERICAN SPANISH_AMERICA.WE8ISO8859P1'
OPTERR=1
OPTIND=1
ORACLE_BASE=/sda/app/oracle9/
ORACLE_HOME=/sda/app/oracle9/product/9.2.0
ORACLE_LIB=/sda/app/oracle9/product/9.2.0//lib:/sda/app/oracle9/product/9.2.0//ctx/lib:/sda/app/oracle9/product/9.2.0//jdk/jre/lib:.
ORACLE_OWNER=oracle9
ORACLE_SID=OPENLB
ORATAB=/etc/oratab
OSTYPE=linux-gnu
PATH=/sda/app/oracle9/product/9.2.0/bin:/bin:/usr/bin:/etc
PFILE=/sda/app/oracle9/product/9.2.0/dbs/initOPENLB.ora
PIPESTATUS=([0]="1")
PPID=4040
PS4='+ '
PWD=/sda/app/oracle9/product/9.2.0
SAVE_LLP=/lib:/usr/lib:/sda/app/oracle9/product/9.2.0//lib:/sda/app/oracle9/product/9.2.0//ctx/lib:/sda/app/oracle9/product/9.2.0//jdk/jre/lib:.
SHELL=/bin/ksh
SHELLOPTS=braceexpand:hashall:interactive-comments
SHLVL=2
SQLDBA='sqlplus /nolog'
SQLPATH=/home/oracle9/utl
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
SSH_CLIENT='132.147.170.240 3474 22'
SSH_CONNECTION='132.147.170.240 3474 132.147.150.26 22'
SSH_TTY=/dev/pts/3
STATUS=1
TERM=xterm
TIMEFORMAT=$'\nreal %3R\tuser %3U\tsys %3S\tpcpu %P\n'
UID=516
USER=oracle9
VERSION=undef
_=']'
[oracle9@opli09 9.2.0]$ $ORACLE_HOME/bin/dbstart
SQL*Plus: Release 9.2.0.8.0 - Production on Mié Dic 26 17:38:08 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> Connected to an idle instance.
> ORACLE instance started.
Total System Global Area| 235475360|bytes
Fixed Size | 450976|bytes
Variable Size | 218103808|bytes
Database Buffers | 16777216|bytes
Redo Buffers | 143360|bytes
Database mounted.
Database opened.
> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
Database "OPENLB" warm started.
[oracle9@opli09 9.2.0]$ ps -ef | grep pmon
oracle9 10361 1 0 17:38 ? 00:00:00 ora_pmon_OPENLB
oracle9 10404 4040 0 17:39 pts/3 00:00:00 grep pmon
[oracle9@opli09 9.2.0]$ id
uid=516(oracle9) gid=500(dba) groups=500(dba)
but .....
[oracle9@opli09 9.2.0]$ sqlplus 'sys as sysdba'
SQL*Plus: Release 9.2.0.8.0 - Production on Mié Dic 26 17:41:09 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to an idle instance.
OPENLB>
work fine with tnsname ...
[oracle9@opli09 9.2.0]$ sqlplus scott/tiger@openlb
SQL*Plus: Release 9.2.0.8.0 - Production on Mié Dic 26 17:43:00 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
openlb> exit
[oracle9@opli09 9.2.0]$ $ORACLE_HOME/bin/dbshut
SQL*Plus: Release 9.2.0.8.0 - Production on Mié Dic 26 17:44:18 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> Connected.
> Database closed.
Database dismounted.
ORACLE instance shut down.
> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
Database "OPENLB" shut down.
now, work fine if ....
[oracle9@opli09 9.2.0]$ sqlplus 'sys as sysdba'
SQL*Plus: Release 9.2.0.8.0 - Production on Mié Dic 26 17:44:51 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to an idle instance.
OPENLB> startup
ORACLE instance started.
Total System Global Area| 235475360|bytes
Fixed Size | 450976|bytes
Variable Size | 218103808|bytes
Database Buffers | 16777216|bytes
Redo Buffers | 143360|bytes
Database mounted.
Database opened.
OPENLB> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
without tnsname, work ....
[oracle9@opli09 9.2.0]$ sqlplus 'sys as sysdba'
SQL*Plus: Release 9.2.0.8.0 - Production on Mié Dic 26 17:45:20 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
OPENLB>
why with the same user dont work with that script? what can i looking for?,
gracias.....
sorry for my english....
December 26, 2007 - 10:58 pm UTC
dbstart grabs the oracle home out of the oratab in /etc
what is in there.
if the oracle home in oratab differs from your oracle home in your environment, you will not be able to connect directly.
antonio, December 27, 2007 - 8:31 am UTC
I CANT BELIEVE,
with this oratab dont work
---- oratab ----
...
#
# *:/sda/app/oracle9/product/9.2.0:N
OPENLB:/sda/app/oracle9/product/9.2.0:Y
but, if i put a '/' in the end of directory, work
OPENLB:/sda/app/oracle9/product/9.2.0/:Y
i dont like this.... to me with or without '/' must to be the same thing, a directory.
thank you...
December 27, 2007 - 9:51 am UTC
is this STRING:
/abc
the same as this STRING:
/abc/
they are both strings - but they are different.
The key to the SGA is a function of the oracle_sid plus the oracle_home
we take your SID || ORACLE_HOME
ORACLE_HOME=/sda/app/oracle9/product/9.2.0
ORACLE_SID=OPENLB
put them together, hash them and that is the key to attach the SGA.
If you use DIFFERENT STRINGS, you will obviously get different hashes and therefore would attempt to attach to different SGA's
don't think of oracle home as a directory in this context, it is just a string and must match - if you want to do a direct connection.
when you connected via the listener, the listener must have had the same oracle home set as was used to start the database and it was able to generate the correct key.
automatically restart
sky, October 15, 2008 - 12:54 pm UTC
Hey Tom:
Have you heard of automatically restart the database after the database is crashed? we have a shell script that run s every min to check the connection to the database. If it can't connect for 3 times, it will send me page and say "db possible shutdown". And since we don't want the down time to be long, so wonder if we can add something in there, and let it to restart the database automatically when it detect the database is down.
Just don't know if this is a wel-known way, or if there is some disadvantages to do this automatically.
Thank you very much!
October 17, 2008 - 6:37 pm UTC
There are thousands of monitoring tools that do this, yes...
You could write your own shell script, but be careful. You'll need to very carefully process the errors to make sure the database is down - for real.
For example, what if the archive process is stuck because the archive destination is full? You don't want to startup again.
You can do this - but be very very careful.