Solved

Cannot Login to Oracle 10g DB from client machine

Posted on 2010-11-22
16
745 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to reset the password in Oracle 11g Express Edition 4 100
PAYER_ID has both atributes 4 31
execute immediate plsql block 5 33
PL/SQL Display based on value 4 17
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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 …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

911 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

19 Experts available now in Live!

Get 1:1 Help Now