Avatar of akvaidyanathan
akvaidyanathan asked on

Oracle XE connectivity problem ORA-01034,ORA-27101

Hi,

I'm trying to connect to Oracle XE using SQL developer and Toad, and am faced with ORA-01034 : Oracle not available and ORA 27101

I reckon the problem has to do with my tsnames,listener,sqlnet files

Here they are:

Listener.ora
=========

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = XE)
    (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
    (SID_NAME = XE)
    )
  )

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.16.45)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)

SQLNET.ORA
===========
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NONE)


NAMES.DIRECTORY_PATH= (TNSNAMES)

TSNAMES.ORA
=-============

XE =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.16.45)(PORT = 1521))
  (CONNECT_DATA = (ORACLE_SID = XE))
 )


10.10.16.45 is my own IP address got from ipconfig.

Been busting my head for a while now....Any help will be appreciated.
Oracle Database

Avatar of undefined
Last Comment
schwertner

8/22/2022 - Mon
MikeOM_DBA


Did you set up "loopback" for you ip?

Dr_Billy

what is the command you use to connect ?

 Userid/<password>@xe

 the other thing , why did you include your ip address in the listener configuration file , you should include the server ip address where you have your DB listener installed and configured unless you are working with a local host installed instance , then you should be using the 127.0.0.1 or the loopback address to represent that your database instance is installed locally on your machine , try to ping 127.0.0.1 or localhost and see what does that comes back with ?
ASKER
akvaidyanathan

nope havent set loopback, how do you do that?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
akvaidyanathan

I want to be able to connect using toad or SQL developer

User is SYSTEM ( As was specified during the Oracle XE installation)

Password: The one I set

And I could use either the Direct or TNS option (In toad)

Dr_Billy

ASKER
akvaidyanathan

yup configured loopback
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dr_Billy

now  modify your configuration files as per the following , you need to modify the host ip address and set it as 127.0.0.1 instead of your IP address this will work for sure .

Another thing to verify afterwards, you need to make sure your instance is up and service for your listener is running , you can check that in your services from administrative tools .

Listener.ora
=========

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = XE)
    (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
    (SID_NAME = XE)
    )
  )

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)

SQLNET.ORA
===========
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NONE)


NAMES.DIRECTORY_PATH= (TNSNAMES)

TSNAMES.ORA
=-============

XE =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))  
  (CONNECT_DATA = (ORACLE_SID = XE))
 )

ASKER
akvaidyanathan

I looked up services

OracleServiceXE is  showing as started

and OracleXETNSListener is also showing as started.

Modifed the .ora files as above

Checked connection in Toad, I still get ORA-01034, and ORA-27101
MikeOM_DBA


Maybe you need to perform the standard WinDoze fix-it-all: RE-BOOT?


Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
akvaidyanathan

Rebooted it,

Now I get the following error :

Failure -lo exception:The Network Adapter could not establish the connection
MikeOM_DBA


A-Hah...The Network Adapter
ASKER
akvaidyanathan

That was a missing comma in listener.ora
We are back to ORA-01034 and ORA-27101 now
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dr_Billy

do you have the DHCP enabled on your Network ? can you check that for me please !
MikeOM_DBA


Check C:\WINDOWS\system32\drivers\etc\hosts file for 127.0.0.1 and 10.10.16.45 entries.

copy and paste that file here...

ASKER
akvaidyanathan

here is the host file content
====================================================================================
# Copyright (c) 1993-1999 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a '#' symbol.
#
# For example:
#
#      102.54.94.97     rhino.acme.com          # source server
#       38.25.63.10     x.acme.com              # x client host

127.0.0.1       localhost

====================================================================================

I also checked for DHCP,Its enabled

Been busting my head for a while now....just cant get it to work...
Your help has saved me hundreds of hours of internet surfing.
fblack61
schwertner

Backup the Listener.ora.
Delete
    (GLOBAL_DBNAME = XE)
from listener.ora and restart the listener
 
In sqlnet.ora
comment the line
#SQLNET.AUTHENTICATION_SERVICES= (NTS)
Also pay attention to this

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

Check if the listener is working

lsnrctl status
lsnrctl services

Check if the instance is up

try using local SQL*Plus to connect to the instance.

Check the default and any other firewalls (there are also hidden).
Turn them off. Antiviruses also.
ASKER
akvaidyanathan

Made the changes as specified:

lsnrctl status gives the following
======================================================================================

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-MAY-2008 10:52
:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
Start Date                22-MAY-2008 10:37:43
Uptime                    0 days 0 hr. 14 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oraclexe\app\oracle\product\10.2.0\server\network\a
dmin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\product\10.2.0\server\network\l
og\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
====================================================================================

lsncrtl services gives the following:
====================================================================================
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-MAY-2008 10:52
:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XE" has 1 instance(s).
  Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
====================================================================================

tried to connect with SQL developer. Same error ORA-01034 and ORA-27101
schwertner

Either the OS authorization in sql.net (as recommended in my posting)
or
running hidden or not hidde firewall.
XP SP2 has default firewall turned off.
Beware Trend Micro and Alarm Zone also.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
akvaidyanathan

I can connect to another instance of oracle10g (server is not my laptop), using SQL developer..

Its just this Oracle XE which Im hosting on my own PC thats giving me a problem.

lsnrctl reveals that

Instance "XE", status UNKNOWN is a problem , It should be in Ready state...

Kindly help...
schwertner

No, this is not the problem!
But on the Laptop you have to put as
first adapter the Loopback adapter (when the
laptop is not networked).

Read  here:

http://download-west.oracle.com/docs/html/B10130_02/reqs.htm#BABDJJFF
2 Oracle Database Preinstallation Requirements

Connecting the Computer to the Network after Installation
If you connect the computer to a network after installation, your Oracle Database instance on your computer can work with other instances on the network. Remember that you must have installed a loopback adapter on your computer. Your computer can use a static IP or DHCP, depending on the network to which you are connected.
2.4.5 Installing a Loopback Adapter
When you install a loopback adapter, the loopback adapter assigns a local IP for your computer. After you install a loopback adapter on your computer, you have at least two network adapters on your computer: your own network adapter and the loopback adapter. Oracle Database needs to have Windows using the loopback adapter as the primary adapter.


But also check for firewalls.
ASKER
akvaidyanathan

I checked:

When I do an ipconfig the loopback adapter is the 1st in order, so I suppose its the primary.

Heres the result on ipconfig:
=================================================================================

Ethernet adapter Local Area Connection 2:

        Connection-specific DNS Suffix  . :
        IP Address. . . . . . . . . . . . : 192.168.1.1
        Subnet Mask . . . . . . . . . . . : 255.255.255.0
        Default Gateway . . . . . . . . . :

Ethernet adapter ADVISORY BOARD NETWORK:

        Connection-specific DNS Suffix  . : advisory.com
        IP Address. . . . . . . . . . . . : 10.10.16.45
        Subnet Mask . . . . . . . . . . . : 255.255.255.0
        Default Gateway . . . . . . . . . : 10.10.16.1
        DHCP Class ID . . . . . . . . . . :

Ethernet adapter Wireless Network Connection:

        Connection-specific DNS Suffix  . : advisory.com
        IP Address. . . . . . . . . . . . : 10.10.40.109
        Subnet Mask . . . . . . . . . . . : 255.255.248.0
        Default Gateway . . . . . . . . . : 10.10.40.1
        DHCP Class ID . . . . . . . . . . :
================================================================================
Now as per the documentation
1. I edited the loopback adapter to make the add a non routable ip address as 192.168.1.1
and subnet mask as 255.255.255.0

2. I added a new line to my host file, so it looks like this now.

127.0.0.1       localhost
192.168.1.1      KRISHNAV.ADVISORY      KRISHNAV

where krishnav is hostname and advisory is domain name. After that I restarted.

Same error:-( Pls help.


This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
MikeOM_DBA

Try again the standard WinDoze type solution:

1) Verify that your environment variables the ORACLE_HOME and ORACLE_SID are set properly and pointing to an oracle home that exists.
2) Execute:

set oracle_sid = XE
sqlplus /nolog
conn system/manager as sysdba
shutdown abort
startup

Open in new window

ASKER
akvaidyanathan

On executing

conn system/manager as sysdba

I get insufficient priveleges  ORA-01031
ASKER
akvaidyanathan

ok i worked around that and executed the series of commands...

the problem seems to be in spfilexe.ora

i did the instance shutdown,

after that on issuing startup it says"

error in identifying file
C:\oraclexe\app\oracle\product\10.2.0\server\dbs/spfileXE.ora

ORA-27041 unable to open the file
system cannot find the file specified

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
akvaidyanathan

this is what i get

SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file 'C:\oraclexe\app\oracle\product\10.2.0\serv
er\dbs/spfileXE.ora'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>
MikeOM_DBA


Do a 'find' in windoze explorer to see if there is an spfilexe.ora and/or initxe.ora file somewhere.
ASKER
akvaidyanathan

found initiexe.ora

and it contains the following


SPFILE='C:\oraclexe\app\oracle\product\10.2.0\server\dbs/spfileXE.ora'



spfilexe.ora is absent in the dbs folde...do i have to create it?...

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
schwertner

In the pfile section (.../admin/pfile subdirectory)
you will find a int.ora file.
Copy and rename it. use it so

SQL>connect sys/passw@some_inst as sysdba
SQL>startup pfile=c:\init.ora

After the instance starts create SPFILE

SQL> create spfile='c:\SPFILE<SID>.ora' from pfile='c:\init.ora';

Place it where you need.
ASKER
akvaidyanathan

I found init.ora in

What do I rename it to? And do I place it in c:/  ?

Kindly clariffy
ASKER
akvaidyanathan

Sorry about the incomplete previous message...

I found init.ora in C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
schwertner

You have to check if this init.ora is relevant to your database.
SID, locations of the files, directories.
If the answer is yes then copy it in another directory
and start the server with pfile.
ASKER
akvaidyanathan

the init.ora is relevant except that the path for the control file is

control_files=("C:\oraclexe\oradata\XE\control.dbf")

This is absent, so when I started the instance using startup pfile I got an error ORA-00205
error in identifying controlfile, check alert log for more info

but the instance was still started...

so i went ahead and executed create spfile but nothing happens....the spfile is nor create in the desired location
ASKER
akvaidyanathan

allright i went further...the spfile was created startup was performed but with error, no control file found.

Still I checked if the connect would work.It didnt says )ORA-01033-Oracle initialization or shutdown in progress
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
schwertner

This is not the correct pfile.
I said look in ORACLR_BASE\admin\pfile subdirectory

Also try to searc all pfiles using mask like init*.ora
ASKER
akvaidyanathan

still havent been able to install it :-(..pls help.
ASKER CERTIFIED SOLUTION
schwertner

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question