Home » RDBMS Server » Server Administration » Unable to connect to Oracle DB (11g R2) (Oracle 11g R2 version(11.2.0.1))
Unable to connect to Oracle DB (11g R2) [message #664387] Sat, 15 July 2017 02:14 Go to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I have been trying to connect to the Oracle DB using sqlplus and sql developer too but both are unable to connect. When I try to connect, it gives me the following error. Yesterday windows update took place and after that it has been not working.
C:\Users\abc>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 15 12:34:14 2017

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

Connected to an idle instance.
When I try to connect with a different user:
SQL> connect hd/hd
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

Warning: You are no longer connected to ORACLE.
I tried with the EM and here is the summary:
Database Instance
Host: abc-PC
Port: 1521
SID: mydb
Oracle Home: D:\app\abc\product\11.2.0\dbhome_1

Listener
Status: Down
Host: abc-PC
Port: 1521
Name: LISTENER
Oracle Home: D:\app\abc\product\11.2.0\dbhome_1
Location: D:\app\kaos\product\11.2.0\dbhome_1\NETWORK\ADMIN
Details: Although a Listener with name "LISTENER", is running on this host at port: "1521", it was not started using this target's "LISTENER.ORA" file. CORRECTIVE ACTION: To monitor this "EM Listener Target" with its present configuration, you must stop the currently running listener process, and start it again using the Listener Parameter file: D:\app\kaos\product\11.2.0\dbhome_1\NETWORK\ADMIN/listener.ora . Alternatively, you can update this target's "LISTENER.ORA Location" parameter with the location of the currently running Listener, which was started using: D:\app\abc\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
The thing is the location mentioned above i.e. "D:\app\kaos\product\11.2.0\dbhome_1\NETWORK\ADMIN/listener.ora" does not exist. How do I correct this? Please help. The second location i.e. " D:\app\abc\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora" is the correct location.
Re: Unable to connect to Oracle DB (11g R2) [message #664388 is a reply to message #664387] Sat, 15 July 2017 02:25 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
C:\Users\john>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 15 08:24:20 2017

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

Connected to an idle instance.

orclz> startup
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size                  3050600 bytes
Variable Size             373293976 bytes
Database Buffers          406847488 bytes
Redo Buffers                5337088 bytes
Database mounted.
Database opened.
orclz>
Re: Unable to connect to Oracle DB (11g R2) [message #664389 is a reply to message #664388] Sat, 15 July 2017 02:27 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
C:\Users\abc>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 15 12:56:25 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 631914496 bytes
Fixed Size 1376436 bytes
Variable Size 515903308 bytes
Database Buffers 109051904 bytes
Redo Buffers 5582848 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9384
Session ID: 5 Serial number: 3


SQL> connect hd/hd
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0
Re: Unable to connect to Oracle DB (11g R2) [message #664390 is a reply to message #664389] Sat, 15 July 2017 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
ORA-03113
You had an error during startup so database is not open and so hr can't connect, have a look at alert.log and the generated trace file.

Re: Unable to connect to Oracle DB (11g R2) [message #664391 is a reply to message #664390] Sat, 15 July 2017 02:47 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Ok and where do I find these. What about the corrective action regarding the LISTENER? If I change the location will not that work?

[Updated on: Sat, 15 July 2017 02:50]

Report message to a moderator

Re: Unable to connect to Oracle DB (11g R2) [message #664392 is a reply to message #664391] Sat, 15 July 2017 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

have a look at diagnostic_dest and %dump_dest parameters.

Re: Unable to connect to Oracle DB (11g R2) [message #664393 is a reply to message #664392] Sat, 15 July 2017 03:13 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
It says "Oracle not available".
Re: Unable to connect to Oracle DB (11g R2) [message #664395 is a reply to message #664393] Sat, 15 July 2017 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Connected as SYS?
I doubt.
SYS> alter database close;

Database altered.

SYS> show parameter diag
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------------------------------------------
diagnostic_dest                      string      E:\ORACLE\ADMIN\MIKB2\TRACE
SYS> show parameter dump_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------------------------------------------
background_dump_dest                 string      E:\ORACLE\ADMIN\MIKB2\TRACE\diag\rdbms\mikb2\mikb2\trace
core_dump_dest                       string      E:\ORACLE\ADMIN\MIKB2\TRACE\diag\rdbms\mikb2\mikb2\cdump
user_dump_dest                       string      E:\ORACLE\ADMIN\MIKB2\TRACE\diag\rdbms\mikb2\mikb2\trace
Re: Unable to connect to Oracle DB (11g R2) [message #664396 is a reply to message #664395] Sat, 15 July 2017 04:16 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
C:\Users\abc>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 15 14:44:56 2017

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

Connected to an idle instance.

SQL> alter database close
2 ;
alter database close
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


SQL> startup
ORACLE instance started.

Total System Global Area 631914496 bytes
Fixed Size 1376436 bytes
Variable Size 515903308 bytes
Database Buffers 109051904 bytes
Redo Buffers 5582848 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 6612
Session ID: 4 Serial number: 3


SQL> alter database close
2 ;
ERROR:
ORA-03114: not connected to ORACLE
Re: Unable to connect to Oracle DB (11g R2) [message #664397 is a reply to message #664396] Sat, 15 July 2017 04:20 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
Well, go on: look at the alert log. If you don't know where it is, search your disc for alert*.log
Re: Unable to connect to Oracle DB (11g R2) [message #664398 is a reply to message #664396] Sat, 15 July 2017 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't need "alter database close", I ddi it just to show you I am in the same state than you.
You just need to execute the "show parameter".

And FORMAT your posts.

Re: Unable to connect to Oracle DB (11g R2) [message #664399 is a reply to message #664398] Sat, 15 July 2017 04:35 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
show parameter doesn't help it just says "oracle not available". I do anything, it just says either "Oracle not Available" or "not connected to target database."

[Updated on: Sat, 15 July 2017 04:36]

Report message to a moderator

Re: Unable to connect to Oracle DB (11g R2) [message #664400 is a reply to message #664399] Sat, 15 July 2017 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Show your complete SYS SQL*Plus session.
And FORMAT it.

Re: Unable to connect to Oracle DB (11g R2) [message #664401 is a reply to message #664400] Sat, 15 July 2017 04:43 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
SQL> startup
ORACLE instance started.

Total System Global Area  631914496 bytes
Fixed Size                  1376436 bytes
Variable Size             515903308 bytes
Database Buffers          109051904 bytes
Redo Buffers                5582848 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 10424
Session ID: 5 Serial number: 1


SQL> show parameter
ERROR:
ORA-03114: not connected to ORACLE


SQL> show parameter background
ERROR:
ORA-03114: not connected to ORACLE
Re: Unable to connect to Oracle DB (11g R2) [message #664402 is a reply to message #664401] Sat, 15 July 2017 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, exit and restart a SQL*Plus with SYS and execute:
SYS> startup nomount
ORACLE instance started.
Total System Global Area  644468736 bytes
Fixed Size                  1407172 bytes
Variable Size             515901244 bytes
Database Buffers          121634816 bytes
Redo Buffers                5525504 bytes
SYS> show parameter diag
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------------------------------------------------
diagnostic_dest                      string      E:\ORACLE\ADMIN\MIKB2\TRACE
SYS> show parameter dump_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------------------------------------------------
background_dump_dest                 string      E:\ORACLE\ADMIN\MIKB2\TRACE\diag\rdbms\mikb2\mikb2\trace
core_dump_dest                       string      E:\ORACLE\ADMIN\MIKB2\TRACE\diag\rdbms\mikb2\mikb2\cdump
user_dump_dest                       string      E:\ORACLE\ADMIN\MIKB2\TRACE\diag\rdbms\mikb2\mikb2\trace
Re: Unable to connect to Oracle DB (11g R2) [message #664403 is a reply to message #664402] Sat, 15 July 2017 04:53 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
C:\Users\abc>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 15 15:21:58 2017

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  631914496 bytes
Fixed Size                  1376436 bytes
Variable Size             515903308 bytes
Database Buffers          109051904 bytes
Redo Buffers                5582848 bytes
SQL> show parameter diag

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      D:\APP\ABC
SQL> show parameter dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      d:\app\abc\diag\rdbms\mydb\myd
                                                 b\trace
core_dump_dest                       string      d:\app\abc\diag\rdbms\mydb\myd
                                                 b\cdump
user_dump_dest                       string      d:\app\abc\diag\rdbms\mydb\myd
                                                 b\trace
Re: Unable to connect to Oracle DB (11g R2) [message #664404 is a reply to message #664403] Sat, 15 July 2017 05:26 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
The problem is with the listener. How do I perform the
"CORRECTIVE ACTION" mentioned in the question?
Re: Unable to connect to Oracle DB (11g R2) [message #664405 is a reply to message #664404] Sat, 15 July 2017 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The problem is with the listener

I don't think so, prove your claim.

Quote:
How do I perform the
"CORRECTIVE ACTION" mentioned in the question?

What corrective action? What question?

Re: Unable to connect to Oracle DB (11g R2) [message #664406 is a reply to message #664405] Sat, 15 July 2017 06:17 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
The corrective action mentioned in the main question
Re: Unable to connect to Oracle DB (11g R2) [message #664407 is a reply to message #664404] Sat, 15 July 2017 08:07 Go to previous messageGo to next message
EdStevens
Messages: 850
Registered: September 2013
Senior Member
kaos.tissue wrote on Sat, 15 July 2017 05:26
The problem is with the listener. How do I perform the
"CORRECTIVE ACTION" mentioned in the question?
The problems is NOT with the listener.
Your connectioin "sqlplus / as sysdba" is NOT using the listener to connect.
The listener has NOTHING to do with starting the database.
The cited message about the listener is a red herring because EM uses the listener to connect to the database. But if the database is not available, it is not available.
You need to forget (for now) anything about the listener and focus on getting the database cleanly started from sqlplus.
Re: Unable to connect to Oracle DB (11g R2) [message #664408 is a reply to message #664407] Sat, 15 July 2017 08:16 Go to previous message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

quoting BlackSwan:
Those who live by the GUI, die by the GUI.
Previous Topic: How to find out Database Owner in Oracle?
Next Topic: Is there any way to find out if a database is a copy of another database?
Goto Forum:
  


Current Time: Fri Nov 24 23:31:30 CST 2017

Total time taken to generate the page: 0.05509 seconds