?
Solved

Cannot Login to Oracle 10g DB from client machine

Posted on 2010-11-22
16
Medium Priority
?
755 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Praveen Kumar Chandrashekatr
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 48

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 48

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 35

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
 

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:Praveen Kumar Chandrashekatr
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 48

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 48

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

777 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