Solved

Cannot Login to Oracle 10g DB from client machine

Posted on 2010-11-22
16
744 Views
Last Modified: 2012-06-22
I have refreshed the 10.1.0.3 oracle database from production using RMAN and the Dev database is up and running. I can login to the database from the DB server but could not do so from Client machine. I was able to login before Refresh. Also After the refresh, our Unix Admin rebooted the server. Now I am getting the following error.

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 22 10:48:29 2010

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

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


The LISTENER is also up and running. I can do tnsping from my client machine.

C:\Documents and Settings\xxxx>tnsping testdb

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-NOV-2010 10:52:09

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

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xxx.xx)(PORT = 1521))) (
CONNECT_DATA = (SID = test)))
OK (10 msec)

Database version: 10.1.0.3
OS: Linux 3

Any resolution for this error?
0
Comment
Question by:Waqasulhaq
  • 7
  • 4
  • 2
  • +3
16 Comments
 

Expert Comment

by:alorca
ID: 34189119
Oracle is not started up. You should connect internal and startup open the Database.

If you get some error post them here too.
0
 
LVL 7

Expert Comment

by:jocave
ID: 34189339
After doing the refresh, did you change the SID of the database?  Or is the SID still the same as the production database?  My first guess is that your TNS alias is referring to the SID of the old dev database while the refreshed database is still using the SID of the production database (which I am assuming is not TEST).
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 34189449
what is the SID name you are using ?

tnsping testdb

and in tnsnames config

(SID = test)))

since you have set different connect string you have to specify the correct connect string.

0
 

Author Comment

by:Waqasulhaq
ID: 34189464
@alorca:

The database is up and running.

@jocave:

Both the Production and Dev databases have the same SID. Since these two databases are on different VPN's we keep the same SID for Prod and Dev. I have changed the databases name on this forum as "test" for security purpose. The actual database name is not "test". it is same as Production database.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 34189477
Check if the Listener is up:

lsnrctl status

lsnrctl services

Check if the instance is up:

% ps -ef | grep pmon
0
 
LVL 47

Expert Comment

by:schwertner
ID: 34189997
How many NIC (Network cards) your server has?
It they are more then one check their IPs and check on which is the Listener listening.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 34190701
It would seem that the listener is up and they are addressing the correct NIC card.  Otherwise, tnsping would fail.
0
 

Author Comment

by:Waqasulhaq
ID: 34191912
@schwertner:The server have two nic cards of which only 1 is active. And the IP address is also correct.

[oracle@xxxxx oracle]$ lsnrctl services

LSNRCTL for Linux: Version 10.1.0.3.0 - Production on 22-NOV-2010 13:42:13

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

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCTEST))
Services Summary...
Service "TEST" has 1 instance(s).
  Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:17 refused:0
         LOCAL SERVER
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
The command completed successfully

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Waqasulhaq
ID: 34191923
[oracle@xxxxxx oracle]$ ps -ef|grep pmon
oracle    5651     1  0 Nov19 ?        00:00:46 ora_pmon_test
oracle   20328 20109  0 16:01 pts/0    00:00:00 grep pmon
0
 

Author Comment

by:Waqasulhaq
ID: 34192203
@praveencpk:

Yes that is how my tnsnames.ora is listed.

It has SID=test. testdb is just an alias.
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 34195370
yeah thats what i'm telling when u r trying to connect from the client try with correct connect string.

how do u have configure your client system?

0
 
LVL 47

Expert Comment

by:schwertner
ID: 34195639
If your post at Date:11/22/10 11:02 was run on the server machine then you haven't running Oracle instance at all.

To run the instance:

%sqlplus /nolog

SQL> connect / as sysdba

SQL> startup
0
 

Author Comment

by:Waqasulhaq
ID: 34196650
@schwertner: The database was up since Nov 19th, Here is the result from v$instance view.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST
---------- --- ----------------- ------------------ ---------
              1 test
xxxxxxx
10.1.0.3.0       19-NOV-10 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL

If you look at the STARTUP column above, it is up since Nov 19th 2010.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 34212225
If so I suspect the old Oracle 'feature" - the instance is not registered by the listener.
To check this mess:

% lsnrctl services

You have to see entry for test instance.

If it is not mentioned in the output then do static registration of the instance - use the search machine on EE, we have explained this many times.
0
 

Accepted Solution

by:
Waqasulhaq earned 0 total points
ID: 34239431
@schwertner:I had already done that long before.

@All:

I have found the issue. In the listener the database name was listed in UPPER case letters. I changed it to LOWER case letters and bingo !!!!!!!... IT WORKED. Thanks everyone for your valuable inputs.
0
 

Author Closing Comment

by:Waqasulhaq
ID: 34276704
I have found the issue. In the listener the database name was listed in UPPER case letters. I changed it to LOWER case letters and bingo !!!!!!!... IT WORKED. Thanks everyone for your valuable inputs.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now