Home » Other » General » Database startup issue after power failure (Oracle Standard Edition Database, 10g, RHEL 5)
Database startup issue after power failure [message #485338] Tue, 07 December 2010 12:00 Go to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
Hello, I am hoping someone might have some ideas for me on this issue. We had a power failure over the weekend and I was not alerted in time to shut down my DB server gracefully. I am running a 10g (10.2.0.4.0) standard database on RHEL 5. When power was restored I booted the server and noticed the database did not start (I have a startup script in init.d), I tried running dbstart manually and that did not work either. Below is the entries in my startup.log file and sqlnet.log file, I cannot figure out what is causing the 12514 error, as everything was working fine before the power failure. Interestingly though I am able to start the DB by running dbca and making a minor config change that requires a restart, dbca is somehow able to start the DB. Any ideas would be greatly apprieciated. Thanks!


STARTUP.LOG

/u01/app/oracle/product/10.2.0/db_1/bin/dbstart: Starting up database "DBS"
Sat Dec 4 15:33:12 PST 2010


SQL*Plus: Release 10.2.0.4.0 - Production on Sat Dec 4 15:33:12 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SQL> ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SQL>
/u01/app/oracle/product/10.2.0/db_1/bin/dbstart: Database instance "DBS" warm started.

SQLNET.LOG

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraserver.domain.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DBS.domain.c om)(CID=(PROGRAM=sqlplus)(HOST=oraserver)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 10.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
Time: 01-OCT-2009 17:19:10
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0

[EDITED by LF: fixed topic title typo; was "Oraclce"]

[Updated on: Wed, 08 December 2010 14:14] by Moderator

Report message to a moderator

Re: Database startup issue after power failure [message #485341 is a reply to message #485338] Tue, 07 December 2010 12:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
First check if instance is up
ps -ef | grep smon


check if you can login to database (logged in as OS user oracle)
export ORACLE_SID=yourSID
sqlplus / as sysdba 
issue some query and exit;


Then check the status of listener (name of listener is available in listener.ora file). and start the listener
lsnrctl status your_listenerName 
lsnrctl start your_listenerName

[Updated on: Tue, 07 December 2010 12:14]

Report message to a moderator

Re: Database startup issue after power failure [message #485344 is a reply to message #485341] Tue, 07 December 2010 12:18 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
No the instance is not up, I get the 12514 error if I try to connect as sysdba, listener is started but not connected.
Re: Database startup issue after power failure [message #485345 is a reply to message #485344] Tue, 07 December 2010 12:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please post what you did, instead of explaining. Helps us better.
Even if you provide a wrong ORACLE_SID or the instance is down,
your sysdba session will not give 12514, if you connect the way I said.


oracle@123#export ORACLE_SID=thisNeverExists
oracle@123#sqlplus / as sysdba 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 7 13:22:19 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.


all you need to do is,
connect and start the instance (hopefully). login as sysdba
and issue

startup

[Updated on: Tue, 07 December 2010 12:24]

Report message to a moderator

Re: Database startup issue after power failure [message #485347 is a reply to message #485345] Tue, 07 December 2010 12:34 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
Since I am able to start it with dbca it is running right now and I need to let my developers work, so I can't shut it down at the moment. What you're suggesting is the first thing I tried though,

ORACLE_SID and all oracle environment variables are set in the oracle users .bash_profile

The first thing I tried was
oracle@123#sqlplus / as sysdba

I got an error, I believe the 12514 error, and got the user name prompt. Tried stopping and restarting listener and trying again, still got the same. I'll have to wait until the end of the day to shut the DB down and do some testing.
Re: Database startup issue after power failure [message #485349 is a reply to message #485347] Tue, 07 December 2010 12:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>No the instance is not up,

>>Since I am able to start it with dbca it is running right now

If the database is up and available, so should be the instance.

[Updated on: Tue, 07 December 2010 12:42]

Report message to a moderator

Re: Database startup issue after power failure [message #485352 is a reply to message #485349] Tue, 07 December 2010 12:48 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
When I said the instance is not up I was referring to when I first boot the machine or try to restart the DB.

Yes it is up now because I am able to start the DB with the dbca workaround. My issue is if I reboot my machine or try to restart my database with dbshut and dbstart or any other manual method it does not start. My workaround has been to run dbca, make a change, and allow that service to start the database.

I need to let my developers work right now so I cannot shut it down and test at the moment, I guess I was just looking for some ideas. This is my startup script that runs out of init.d, it has been working for two years until the power failure this weekend, this script or running dbstart from command line will no longer start my database.

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi

case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start agent"
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop agent"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
;;
esac
Re: Database startup issue after power failure [message #485357 is a reply to message #485352] Tue, 07 December 2010 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL*Net is NOT required to start or use Oracle DB
Re: Database startup issue after power failure [message #485523 is a reply to message #485349] Wed, 08 December 2010 12:37 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
I am thinking permissions got messed up somehow, trying to connect as sysdba gives me the following. The oracle user is a member of the dba group and this used to work.


[oracle@oraserver ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 8 10:33:21 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:
Re: Database startup issue after power failure [message #485527 is a reply to message #485523] Wed, 08 December 2010 12:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The oracle user is a member of the dba group
OK, but are you logged in as oracle user?

>and this used to work.
What changed?

post results from following commands

id
env | sort
Re: Database startup issue after power failure [message #485528 is a reply to message #485523] Wed, 08 December 2010 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post result fo "groups".
Use code tags to post code and command execution.
If you don't know how, read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Are you sure "dba" is the group defined for sysdba?

Regards
Michel
Re: Database startup issue after power failure [message #485531 is a reply to message #485527] Wed, 08 December 2010 12:51 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
>The oracle user is a member of the dba group
>>OK, but are you logged in as oracle user?
Yes

>and this used to work.
>>What changed?
That is what I am trying to figure out, this all happened after the power outage. The server was hard killed, when power returned and I booted back up the issue started.

post results from following commands

id
[oracle@oraserver ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

env | sort
[oracle@oraserver ~]$ env | sort
_=/bin/env
CALIB=/opt/CA/SharedComponents/lib
CASHCOMP=/opt/CA/SharedComponents
CVS_RSH=ssh
G_BROKEN_FILENAMES=1
HISTSIZE=1000
HOME=/home/oracle
HOSTNAME=oraserver
INPUTRC=/etc/inputrc
LANG=en_US.UTF-8
LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/db_1/lib
LESSOPEN=|/usr/bin/lesspipe.sh %s
LIC_ECHO=echo -e
LOGNAME=oracle
LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32: *.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*. zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00; 35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
MAIL=/var/spool/mail/oracle
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
ORACLE_SID=DBS
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/10.2.0/db_1/bin
PWD=/home/oracle
SHELL=/bin/bash
SHLVL=1
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
TERM=xterm
TWO_TASK=DBS
USER=oracle
Re: Database startup issue after power failure [message #485532 is a reply to message #485531] Wed, 08 December 2010 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
unset TWO_TASK

then do as below

sqlplus
/ as sysdba

COPY whole session & PASTE all back here
Re: Database startup issue after power failure [message #485533 is a reply to message #485528] Wed, 08 December 2010 12:54 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
>Are you sure "dba" is the group defined for sysdba?
Yes
Re: Database startup issue after power failure [message #485536 is a reply to message #485532] Wed, 08 December 2010 12:59 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
[oracle@oraserver ~]$ unset TWO_TASK
[oracle@oraserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 8 10:56:37 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL>

------------------------------------------------
That worked! Could you explain why? Don't mean to sound like a newb but when it comes to dba stuff I sort of am still.
Re: Database startup issue after power failure [message #485538 is a reply to message #485536] Wed, 08 December 2010 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The actual question is "why did you set this variable?". This is just a bug ih your configuration.

Regards
Michel
Re: Database startup issue after power failure [message #485539 is a reply to message #485536] Wed, 08 December 2010 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
When TWO_TASK is set, then Oracle accesses the DB via SQL*Net using the TWO_TASK value as TNS alias
It is the functional equivalent to below
sqlplus username/password@DBS
Re: Database startup issue after power failure [message #485542 is a reply to message #485539] Wed, 08 December 2010 13:13 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
First off I really apprieicate the help, I am still wondering though

1. Should this fix my orifginal issue?

2. Why has it worked for the past year+ with TWO_TASK being set?

FYI - I inherited DBA responsibilities for this database a little over a year ago, I have 15+ years experience in IT but am fairly new to database administration. TWO_TASK was being set in the oracle users .bash_profile, this is the way my predecessor set it up, I am not sure why.
Re: Database startup issue after power failure [message #485544 is a reply to message #485542] Wed, 08 December 2010 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>1. Should this fix my orifginal issue?
I don't know & have no way to test; but you do.

>2. Why has it worked for the past year+ with TWO_TASK being set?
I don't know what was done for the last year or exactly how the DB was accessed.
Having TWO_TASK set would "work" for most tasks.
Re: Database startup issue after power failure [message #485545 is a reply to message #485544] Wed, 08 December 2010 13:37 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
>1. Should this fix my orifginal issue?
>>I don't know & have no way to test; but you do.

Understood, I have to let my developers work right now, I will be testing later this evening.

>2. Why has it worked for the past year+ with TWO_TASK being set?
>>I don't know what was done for the last year or exactly how the DB was accessed.
Having TWO_TASK set would "work" for most tasks.

Specifically what I am wondering is why, prior to my power failure incident, I could connect with "sqlplus / as sydba" with TWO_TASK set, and after the PF "sqlplus / as sydba" returned "ORA-01031: insufficient privileges" until TWO_TASK was unset? Perhaps there is not a clear cut answer, I'm just trying to understand.

Again, thanks very much for your help.
Re: Database startup issue after power failure [message #485555 is a reply to message #485545] Wed, 08 December 2010 15:27 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe TWO_TASK wasn't set previously. Maybe someone changed the .bash_profile since the last time the oracle user was logged in.

Maybe before the reboot a listener was started against a different listener.ora file than the one currently in place.

Re: Database startup issue after power failure [message #485556 is a reply to message #485555] Wed, 08 December 2010 15:36 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
>Maybe TWO_TASK wasn't set previously. Maybe someone changed the .bash_profile since the last time the oracle user was logged in.

No, I'm the only one who touches this box, it's in a very regulated corporate environment.

>Maybe before the reboot a listener was started against a different listener.ora file than the one currently in place.

There is only one listener.ora file and only one database on this server.
Re: Database startup issue after power failure [message #485582 is a reply to message #485556] Wed, 08 December 2010 23:18 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
Well I am still having the problem with dbstart, here is what I have done

1. Rebooted the server

2. Checked if oracle instance was running
[oracle@oraserver ~]$ ps -ef|grep smon
oracle    4301  4064  0 20:53 pts/2    00:00:00 grep smon

it was not

3. Tried connecting with sqlplus anyway (just to see what error I got)
[oracle@oraserver ~]$ sqlplus system/controll

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 8 20:41:47 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory


Enter user-name:


4. Checked the startup.log to see what might have gone wrong during dbstart execution
/u01/app/oracle/product/10.2.0/db_1/bin/dbstart: Starting up database "DBS"
Sat Dec  4 15:33:12 PST 2010


SQL*Plus: Release 10.2.0.4.0 - Production on Sat Dec 4 15:33:12 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SQL> ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SQL>
/u01/app/oracle/product/10.2.0/db_1/bin/dbstart: Database instance "DBS" warm started.

Shows the 12514 error but says the database was warm started, it is not though.

5. Checked the listener status
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraserver.jdnet.deere.com)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

It was not running

6. Connected as sysdba (thanks to BlackSwan I am to do that again) and ran startup
[oracle@oraserver ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 8 20:44:13 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup open
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  2097696 bytes
Variable Size            1107299808 bytes
Database Buffers         7465861120 bytes
Redo Buffers               14675968 bytes
Database mounted.
Database opened.

After this the database is started and running fine.

So I can get my database started but my boot time start script, or just running dbstart from the command line, is not working, I cannot figure out what the problem is.
Re: Database startup issue after power failure [message #485584 is a reply to message #485582] Wed, 08 December 2010 23:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ls -ltr $ORACLE_HOME/network/admin

post FORMATTED results of command above
Re: Database startup issue after power failure [message #485723 is a reply to message #485584] Thu, 09 December 2010 11:03 Go to previous messageGo to next message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
[oracle@failatora01 ~]$ ls -ltr $ORACLE_HOME/network/admin
total 32
-rw-r----- 1 oracle oinstall  172 Dec 26  2003 shrept.lst
drwxr-x--- 2 oracle oinstall 4096 Apr  7  2008 samples
-rw-r----- 1 oracle oinstall  993 Apr 18  2008 tnsnames.ora.bak
-rw-r----- 1 oracle oinstall  566 Oct  1  2009 listener.ora
-rw-r----- 1 oracle oinstall 4272 Oct  1  2009 sqlnet.log
-rw-r----- 1 oracle oinstall  203 Jul 27 16:06 sqlnet.ora
-rw-r----- 1 oracle oinstall  738 Dec  7 16:25 tnsnames.ora


Are you thinking permissions? I thought of that too, but they seem fine.
Re: Database startup issue after power failure [message #485724 is a reply to message #485723] Thu, 09 December 2010 11:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Are you thinking permissions?
NO, I wanted to see which files existed (or not) & when last changed.

post results of follow OS command

cat $ORACLE_HOME/network/admin/sqlnet.ora $ORACLE_HOME/network/admin/listener.ora

[Updated on: Thu, 09 December 2010 11:25]

Report message to a moderator

Re: Database startup issue after power failure [message #485725 is a reply to message #485724] Thu, 09 December 2010 11:32 Go to previous message
taylorjp75
Messages: 14
Registered: December 2010
Location: California
Junior Member
[oracle@oraserver ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.EXPIRE_TIME=120


[oracle@oraserver ~]$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraserver.domain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
SUBSCRIBE_FOR_NODE_DOWN_EVENT_Listener=OFF
Previous Topic: Database Design Review
Next Topic: TNS-03505: Failed to resolve name
Goto Forum:
  


Current Time: Thu Mar 28 04:27:56 CDT 2024