Cannot Login to Oracle 10g DB from client machine

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?
WaqasulhaqAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
WaqasulhaqConnect With a Mentor Author Commented:
@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
 
alorcaCommented:
Oracle is not started up. You should connect internal and startup open the Database.

If you get some error post them here too.
0
 
jocaveCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
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
 
WaqasulhaqAuthor Commented:
@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
 
schwertnerCommented:
Check if the Listener is up:

lsnrctl status

lsnrctl services

Check if the instance is up:

% ps -ef | grep pmon
0
 
schwertnerCommented:
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
 
johnsoneSenior Oracle DBACommented:
It would seem that the listener is up and they are addressing the correct NIC card.  Otherwise, tnsping would fail.
0
 
WaqasulhaqAuthor Commented:
@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
 
WaqasulhaqAuthor Commented:
[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
 
WaqasulhaqAuthor Commented:
@praveencpk:

Yes that is how my tnsnames.ora is listed.

It has SID=test. testdb is just an alias.
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
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
 
schwertnerCommented:
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
 
WaqasulhaqAuthor Commented:
@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
 
schwertnerCommented:
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
 
WaqasulhaqAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.