Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Cannot Login to Oracle 10g DB from client machine

Posted on 2010-11-22
16
Medium Priority
?
758 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

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.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

636 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