OraFAQ Forum: RAC & Failsafe » Can't enter the "real" instance without tns name

Home » Server Options » RAC & Failsafe » Can't enter the "real" instance without tns name (Oracle enterprise edition 10.2.0.1.0, CentOS 5.5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can't enter the "real" instance without tns name [message #519782]

Tue, 16 August 2011 22:26

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Hi, guys
I am facing a problem with a 2 nodes 10g rac.
When I trying to login with sqlplus, it tells me "connect to idle instances", except I using the tnsname to login, but the instance is started and database is open.

OS and kernel version:

[oracle@rac1 ~]$ cat /etc/redhat-release 
CentOS release 5.5 (Final)
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ uname -a
Linux rac1 2.6.18-194.el5 #1 SMP Fri Apr 2 14:58:35 EDT 2010 i686 i686 i386 GNU/Linux

environment variables:

[oracle@rac1 ~]$ env
ORA_CRS_HOME=/opt/oracle//product/crs/
HOSTNAME=rac1
TERM=vt100
SHELL=/bin/bash
HISTSIZE=1000
TMPDIR=/tmp
SSH_CLIENT=192.168.0.98 3141 22
ORACLE_OWNER=oracle
SSH_TTY=/dev/pts/1
USER=oracle
LD_LIBRARY_PATH=/opt/oracle//product/10.2.0/db1/lib:
LS_COLORS=no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=01;32:*.cmd=01;32:*.exe=01;32:*.com=01;32:*.btm=01;32:*.bat=01;32:*.sh=01;32:*.csh=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tz=01;31:*.rpm=01;31:*.cpio=01;31:*.jpg=01;35:*.gif=01;35:*.bmp=01;35:*.xbm=01;35:*.xpm=01;35:*.png=01;35:*.tif=01;35:
ORACLE_SID=myrac1
ORACLE_BASE=/opt/oracle/
MAIL=/var/spool/mail/oracle
PATH=/opt/oracle//product/10.2.0/db1/bin:/opt/oracle//product/crs//bin:/opt/oracle//product/10.2.0/db1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
INPUTRC=/etc/inputrc
PWD=/home/oracle
LANG=en_US.UTF-8
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
SHLVL=1
HOME=/home/oracle
TMP=/tmp
LOGNAME=oracle
CVS_RSH=ssh
SSH_CONNECTION=192.168.0.98 3141 192.168.0.31 22
LESSOPEN=|/usr/bin/lesspipe.sh %s
ORACLE_HOME=/opt/oracle//product/10.2.0/db1
G_BROKEN_FILENAMES=1
_=/bin/env

Here are the processes related with oracle

[oracle@rac1 ~]$ ps -ef | grep oracle
root      3120     1  0 09:54 ?        00:00:00 /bin/su -l oracle -c sh -c 'ulimit -c unlimited; cd /opt/oracle/product/crs/log/rac1/evmd; exec /opt/oracle/product/crs/bin/evmd '
root      3123     1  0 09:54 ?        00:00:20 /opt/oracle/product/crs/bin/crsd.bin reboot
oracle    3497  3120  0 09:55 ?        00:00:02 /opt/oracle/product/crs/bin/evmd.bin
root      3590  3470  0 09:55 ?        00:00:00 /bin/su -l oracle -c /bin/sh -c 'ulimit -c unlimited; cd /opt/oracle/product/crs/log/rac1/cssd;  /opt/oracle/product/crs/bin/ocssd  || exit $?'
oracle    3591  3590  0 09:55 ?        00:00:00 /bin/sh -c ulimit -c unlimited; cd /opt/oracle/product/crs/log/rac1/cssd;  /opt/oracle/product/crs/bin/ocssd  || exit $?
oracle    3619  3591  0 09:55 ?        00:00:04 /opt/oracle/product/crs/bin/ocssd.bin
oracle    3935  3497  0 09:56 ?        00:00:00 /opt/oracle/product/crs/bin/evmlogger.bin -o /opt/oracle/product/crs/evm/log/evmlogger.info -l /opt/oracle/product/crs/evm/log/evmlogger.log
oracle    4378     1  0 09:56 ?        00:00:00 asm_pmon_+ASM1
oracle    4380     1  0 09:56 ?        00:00:00 asm_diag_+ASM1
oracle    4382     1  0 09:56 ?        00:00:00 asm_psp0_+ASM1
oracle    4384     1  0 09:56 ?        00:00:02 asm_lmon_+ASM1
oracle    4386     1  0 09:56 ?        00:00:00 asm_lmd0_+ASM1
oracle    4388     1  0 09:56 ?        00:00:00 asm_lms0_+ASM1
oracle    4398     1  0 09:56 ?        00:00:00 asm_mman_+ASM1
oracle    4400     1  0 09:56 ?        00:00:00 asm_dbw0_+ASM1
oracle    4402     1  0 09:56 ?        00:00:00 asm_lgwr_+ASM1
oracle    4404     1  0 09:56 ?        00:00:00 asm_ckpt_+ASM1
oracle    4406     1  0 09:56 ?        00:00:00 asm_smon_+ASM1
oracle    4408     1  0 09:56 ?        00:00:00 asm_rbal_+ASM1
oracle    4410     1  0 09:56 ?        00:00:00 asm_gmon_+ASM1
oracle    4462     1  0 09:56 ?        00:00:00 asm_lck0_+ASM1
oracle    4592 23573  0 11:08 pts/1    00:00:00 ps -ef
oracle    4593 23573  0 11:08 pts/1    00:00:00 grep oracle
oracle    4657     1  0 09:56 ?        00:00:00 /opt/oracle/product/10.2.0/db1/bin/racgimon daemon ora.rac1.ASM1.asm
oracle    4677     1  0 09:56 ?        00:00:00 /opt/oracle/product/crs/opmn/bin/ons -d
oracle    4678  4677  0 09:56 ?        00:00:00 /opt/oracle/product/crs/opmn/bin/ons -d
oracle    4751     1  0 09:56 ?        00:00:00 /opt/oracle/product/10.2.0/db1/bin/racgimon startd myrac
oracle    4957     1  0 09:56 ?        00:00:00 asm_o001_+ASM1
oracle    5011     1  0 09:56 ?        00:00:00 ora_pmon_myrac1
oracle    5013     1  0 09:56 ?        00:00:00 ora_diag_myrac1
oracle    5015     1  0 09:56 ?        00:00:00 ora_psp0_myrac1
oracle    5017     1  0 09:56 ?        00:00:02 ora_lmon_myrac1
oracle    5019     1  0 09:56 ?        00:00:05 ora_lmd0_myrac1
oracle    5021     1  0 09:56 ?        00:00:04 ora_lms0_myrac1
oracle    5031     1  0 09:56 ?        00:00:00 ora_mman_myrac1
oracle    5033     1  0 09:56 ?        00:00:00 ora_dbw0_myrac1
oracle    5035     1  0 09:56 ?        00:00:00 ora_lgwr_myrac1
oracle    5037     1  0 09:56 ?        00:00:00 ora_ckpt_myrac1
oracle    5039     1  0 09:56 ?        00:00:01 ora_smon_myrac1
oracle    5041     1  0 09:56 ?        00:00:00 ora_reco_myrac1
oracle    5043     1  0 09:56 ?        00:00:00 ora_cjq0_myrac1
oracle    5045     1  0 09:56 ?        00:00:06 ora_mmon_myrac1
oracle    5047     1  0 09:56 ?        00:00:00 ora_mmnl_myrac1
oracle    5049     1  0 09:56 ?        00:00:00 ora_d000_myrac1
oracle    5051     1  0 09:56 ?        00:00:00 ora_s000_myrac1
oracle    5105     1  0 09:56 ?        00:00:01 ora_lck0_myrac1
oracle    5160     1  0 09:56 ?        00:00:00 ora_asmb_myrac1
oracle    5162     1  0 09:56 ?        00:00:00 oracle+ASM1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5164     1  0 09:56 ?        00:00:00 ora_rbal_myrac1
oracle    5635     1  0 09:57 ?        00:00:00 ora_qmnc_myrac1
oracle    5766     1  0 09:57 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5828     1  0 09:57 ?        00:00:00 /opt/oracle/product/10.2.0/db1/bin/tnslsnr LISTENER_RAC1 -inherit
oracle    5946     1  0 09:57 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5953     1  0 09:57 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5958     1  0 09:57 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6030     1  0 09:57 ?        00:00:00 ora_q000_myrac1
oracle   10086     1  0 10:58 ?        00:00:00 ora_o000_myrac1
oracle   20055     1  0 11:03 ?        00:00:00 ora_q002_myrac1
oracle   22976     1  0 10:07 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   23418     1  0 10:07 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root     23570  2856  0 10:45 ?        00:00:00 sshd: oracle [priv]
oracle   23572 23570  0 10:45 ?        00:00:00 sshd: oracle@pts/1
oracle   23573 23572  0 10:45 pts/1    00:00:00 -bash
oracle   26982     1  0 10:26 ?        00:00:01 ora_j000_myrac1

2 ways enter sqlplus：

[oracle@rac1 ~]$ export ORACLE_SID=myrac1
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 11:09:35 2011

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

Connected to an idle instance.

SQL> 
SQL> 
SQL> exit
Disconnected
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ sqlplus system/oracle@myrac1

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 11:09:50 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> 
SQL> select inst_id, instance_name, status from gv$instance;

   INST_ID INSTANCE_NAME    STATUS
---------- ---------------- ------------
         1 myrac1           OPEN
         2 myrac2           OPEN

all the resources status in this rac:
[code]
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.myrac.db application ONLINE ONLINE rac1
ora....c1.inst application ONLINE ONLINE rac1
ora....c2.inst application ONLINE ONLINE rac2
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2

[/codes]

The parameter of rac instance:

node 1:

[oracle@rac1 ~]$ sqlplus system/oracle@myrac1

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 11:14:56 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> 
SQL> 
SQL> show parameter instance_number    

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_number                      integer     1
SQL> 
SQL> 
SQL> 
SQL> show parameter cluster_database

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
SQL> 
SQL> 
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      myrac1

node 2:

SQL> conn system/oracle@myrac2
Connected.
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      myrac2
SQL> 
SQL> 
SQL> 
SQL> show parameter instance_number

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_number                      integer     2
SQL> 
SQL> 
SQL> 
SQL> show parameter cluster_database

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
SQL>

when I am trying to start the database, it raise the ora-00304 error(The instance myrac1 is already started)

[oracle@rac1 ~]$ export ORACLE_SID=myrac1
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 11:22:17 2011

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

Connected to an idle instance.

SQL> 
SQL> 
SQL> startup     
ORA-00304: requested INSTANCE_NUMBER is busy

alert log when I am trying to start the database:

Wed Aug 17 11:22:34 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Interface type 1 eth1 100.0.0.0 configured from OCR for use as a cluster interconnect
Interface type 1 eth0 192.168.0.0 configured from OCR for use as  a public interface
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /opt/oracle//product/10.2.0/db1/dbs/arch
Autotune of undo retention is turned on. 
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
  processes                = 150
  __shared_pool_size       = 100663296
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  spfile                   = +DG1/myrac/spfilemyrac.ora
  sga_target               = 285212672
  control_files            = +DG1/myrac/controlfile/current.256.757101283
  db_block_size            = 8192
  __db_cache_size          = 171966464
  compatible               = 10.2.0.1.0
  db_file_multiblock_read_count= 16
  cluster_database         = TRUE
  cluster_database_instances= 2
  db_create_file_dest      = +DG1
  thread                   = 1
  instance_number          = 1
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = 
  dispatchers              = (PROTOCOL=TCP) (SERVICE=myracXDB)
  remote_listener          = LISTENERS_MYRAC
  job_queue_processes      = 10
  background_dump_dest     = /opt/oracle/admin/myrac/bdump
  user_dump_dest           = /opt/oracle/admin/myrac/udump
  core_dump_dest           = /opt/oracle/admin/myrac/cdump
  audit_file_dest          = /opt/oracle/admin/myrac/adump
  db_name                  = myrac
  open_cursors             = 300
  pga_aggregate_target     = 94371840
Cluster communication is configured to use the following interface(s) for this instance
  100.0.0.10
Wed Aug 17 11:22:34 2011
cluster interconnect IPC version:Oracle UDP/IP
IPC Vendor 1 proto 2
PMON started with pid=2, OS id=14382
DIAG started with pid=3, OS id=14384
PSP0 started with pid=4, OS id=14393
LMON started with pid=5, OS id=14397
LMD0 started with pid=6, OS id=14399
LMS0 started with pid=7, OS id=14401
MMAN started with pid=8, OS id=14411
DBW0 started with pid=9, OS id=14413
LGWR started with pid=10, OS id=14415
CKPT started with pid=11, OS id=14417
SMON started with pid=12, OS id=14419
RECO started with pid=13, OS id=14421
CJQ0 started with pid=14, OS id=14423
MMON started with pid=15, OS id=14425
MMNL started with pid=16, OS id=14427
Wed Aug 17 11:22:34 2011
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Wed Aug 17 11:22:47 2011
USER: terminating instance due to error 304
Instance terminated by USER, pid = 13955

Is there idea of what cause this issue?

Thanks very much,
BR,
Milo

Report message to a moderator

Re: Can't enter the "real" instance without tns name [message #519793 is a reply to message #519782]

Wed, 17 August 2011 00:14

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Finally, I found the root cause.
This link help me solve my problem:
http://martincarstenbach.wordpress.com/2009/10/16/connected-to-an-idle-instance-rac-10-2-0-4-1/

The root cause is that the double-slash in the $ORACLE_HOME variables.

Here is my test:

[oracle@rac1 ~]$ export ORACLE_HOME=$ORACLE_BASE//product/10.2.0/db1
[oracle@rac1 ~]$ echo $ORACLE_HOME
/opt/oracle//product/10.2.0/db1
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ export ORACLE_SID=myrac1
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 13:04:46 2011

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

Connected to an idle instance.

SQL> 
SQL> 
SQL> 
SQL> exit
Disconnected
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db1
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ echo $ORACLE_HOME
/opt/oracle/product/10.2.0/db1
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ export ORACLE_SID=myrac1
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 13:05:27 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select instance_name, status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
myrac1           OPEN
myrac2           OPEN

SQL>

Very interesting problem, when you have double or multiple slash in the path, it will not misunderstanding of this special path, check this:

[oracle@rac1 ~]$ pwd
/home/oracle

[oracle@rac1 ~]$ cd /opt/////////oracle
[oracle@rac1 oracle]$ pwd
/opt/oracle
[oracle@rac1 oracle]$

Hope this can help someone may have the same issue.

BR,
Milo

Report message to a moderator

Previous Topic:	oinstall group
Next Topic:	rac service not auto-starting on preferred node

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 04 16:45:44 CST 2025