This discussion is archived
14 Replies Latest reply: Oct 4, 2012 10:49 AM by KeenOnOracle RSS

Connecting to a No Mounted instance using tnsnames

KeenOnOracle Explorer
Currently Being Moderated
Hi friends

Its been a challenge to me to fix that.

Oracle 11.2.0.3 Windows x64

see:

sqlplus sys/ORACLE as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 2522038272 bytes
Fixed Size 2257832 bytes
Variable Size 553651288 bytes
Database Buffers 1962934272 bytes
Redo Buffers 3194880 bytes

SQL> EXIT

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64 bit Production
with @alias >>>> sqlplus sys/ORACLE@DGB as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 28 18:17:50 2012

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

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

without alias Enter user-name: SYS AS SYSDBA
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>



DOUBT:
How can we connect to a no mounted instance using tnsnames.

here is my sqlnet.ora, listener.ora and tnsnames.ora

SQLNET.ORA
SQLNET.AUTHENTICATION_SERVICES = (NONE) > Even setting up NTS it did not worked as well

LISTENER.ORA
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = AMAZONA-77TPBCB)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = D:\app\Administrator

TNSNAMES.ORA
# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

DGB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = AMAZONA-77TPBCB)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DGB)
)
)


Any tips would be appreciated.
  • 1. Re: Connecting to a No Mounted instance using tnsnames
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    create password file
    set REMOTE_LOGIN_ PASSWORDFILE=EXCLUSIVE

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba007.htm
  • 2. Re: Connecting to a No Mounted instance using tnsnames
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    KeenOnOracle wrote:

    LISTENER.ORA
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = AMAZONA-77TPBCB)(PORT = 1521))
    )
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    )

    Any tips would be appreciated.
    hmm, not sure that remote_login_passwordfile is the issue here.

    To my eye it looks more like missing static registration for the instance in the listener.ora file.
  • 3. Re: Connecting to a No Mounted instance using tnsnames
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    To my eye it looks more like missing static registration for the instance in the listener.ora file.
    good point.

    KeenOnOracle show output of
    lsnrctl status
  • 4. Re: Connecting to a No Mounted instance using tnsnames
    Brian Bontrager Expert
    Currently Being Moderated
    You can bypass the listener with a TNSNAMES entry using the bequeath protocol. You have to be on the database server for it to work, so it is essentially the same as sqlplus / as sysdba, but works when ORACLE_SID is not set.

    An example from my TNSNAMES:
    XE_B =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = BEQ)(PROGRAM = oracle)(ARGV0 = oracleXE)
                   (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')
        )
        (CONNECT_DATA =(SID= XE)
        )
      )
    "sqlplus sys/anything@xe_b as sysdba" will now connect me to an instance that is either idle or started nomount.

    edit: I am also using dynamic listener registration in this case. when the database is opened nomount, it registers with the listener, but maintains a BLOCKED status.
    Connected to an idle instance.
    
    SQL> startup nomount
    ORACLE instance started.
    
    Total System Global Area  535662592 bytes
    Fixed Size                  1384760 bytes
    Variable Size             188747464 bytes
    Database Buffers          339738624 bytes
    Redo Buffers                5791744 bytes
    SQL> exit
    Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
    
    C:\Documents and Settings\bontrab1>lsnrctl status
    
    LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 28-SEP-2012 14:51:41
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
    Start Date                28-SEP-2012 14:26:02
    Uptime                    0 days 0 hr. 25 min. 39 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Default Service           XE
    Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
    Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\nw927725\listener\alert\log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nw927725.nwie.net)(PORT=1521)))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "xe" has 1 instance(s).
      Instance "xe", status BLOCKED, has 1 handler(s) for this service...
    The command completed successfully
    
    C:\Documents and Settings\bontrab1>sqlplus /@xe_b as sysdba
    
    SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 28 14:51:56 2012
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
    
    SQL> 
    Edited by: Brian Bontrager on Sep 28, 2012 2:54 PM
  • 5. Re: Connecting to a No Mounted instance using tnsnames
    vlethakula Expert
    Currently Being Moderated
    You can try below, change TNS entries below add UR=A


    [oracle@test admin]$ sqlplus sys/manager@TST1 as sysdba

    SQL*Plus: Release 10.2.0.5.0 - Production on Fri Sep 28 13:52:45 2012

    Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

    ERROR:
    ORA-12528: TNS:listener: all appropriate instances are blocking new connections


    original TNS entries:

    TST1 =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = TST1)
    )
    )


    After change:


    TST1 =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = TST1 ) (UR = A)
    )
    )



    [oracle@test admin]$ sqlplus sys/manager@TST1 as sysdba

    SQL*Plus: Release 10.2.0.5.0 - Production on Fri Sep 28 13:53:35 2012

    Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> exit

    Edited by: vreddy on Sep 28, 2012 11:56 AM
  • 6. Re: Connecting to a No Mounted instance using tnsnames
    Shivananda Rao Guru
    Currently Being Moderated
    KeenOnOracle wrote:
    SQL> STARTUP NOMOUNT
    ORACLE instance started.

    Total System Global Area 2522038272 bytes
    Fixed Size 2257832 bytes
    Variable Size 553651288 bytes
    Database Buffers 1962934272 bytes
    Redo Buffers 3194880 bytes

    SQL> EXIT

    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64 bit Production
    with @alias >>>> sqlplus sys/ORACLE@DGB as sysdba
    SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 28 18:17:50 2012

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

    ERROR:
    ORA-12528: TNS:listener: all appropriate instances are blocking new connections

    without alias Enter user-name: SYS AS SYSDBA
    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL>
    It is a common thing. When the database is in nomount stage and you try to connect to the database remotely using the Oracle net Service Name, you would be seeing the error ORA 12528.

    If you want to connect to the database when it is nomount stage using the Oracle net Service name, then add (UR=A) in the tnsnames.ora file for the database entry as shown below.

    DGB =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = AMAZONA-77TPBCB)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = DGB) (UR=A)
    )
    )
  • 7. Re: Connecting to a No Mounted instance using tnsnames
    KeenOnOracle Explorer
    Currently Being Moderated
    sorry for the delay... I was testing and testing the scenario.

    I'm unfortunately not able to connect yes..

    After creating a password file on both servers I'm still getting the message.
    The funny thing is thta I can contact the remote instance just typing

    connect auxiliary dgb
    it connects but I receive this error on the Command

    RMAN> run {
    2> allocate channel c1 type disk;
    3> allocate auxiliary channel c2 type disk;
    4> duplicate target database for standby from active database;
    5> }
    RMAN-06217:not connected to auxiliary database with a net service name

    ok, the next steps is to show you guys

    C:\>lsnrctl status "for the LOCAL server"
    LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 04-OUT-2012 13:00:06
    Established connection com (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dga)(PORT=1521)))

    STATUS do LISTENER
    ------------------------
    Apelido LISTENER
    VersÒo TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
    Data Inicial 27-SET-2012 09:10:50
    Funcionamento 7 dias 3 hr. 49 min. 17 seg
    NÝvel de Anßlise off
    Seguranþa ON: Local OS Authentication
    SNMP OFF
    Arq. ParÔm. Listn. C:\app\Administrador\product\11.2.0\dbhome_1\network\admin\listener.ora
    Arq. Log Listener C:\app\Administrador\diag\tnslsnr\dga\listener\alert\log.xml
    Resumo de Atendimento...

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dga)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
    Resumo de Serviþos...

    O serviþo "CLRExtProc" tem 1 instÔncia(s).
    InstÔncia "CLRExtProc", status UNKNOWN, tem 1 handler(s) para este serviþo...
    O serviþo "ORCLXDB" tem 1 instÔncia(s).
    InstÔncia "orcl", status READY, tem 1 handler(s) para este serviþo...
    O serviþo "orcl" tem 1 instÔncia(s).
    InstÔncia "orcl", status READY, tem 1 handler(s) para este serviþo...
    O comando foi executado com Ûxito


    C:\>lsnrctl status "for the REMOTE server"

    LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 04-OCT-2012 15:55:04
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AMAZONA-77TPBCB)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
    Start Date 28-SEP-2012 18:30:04
    Uptime 5 days 21 hr. 24 min. 59 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF

    Listener Parameter File D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
    Listener Log File D:\app\Administrator\diag\tnslsnr\AMAZONA-77TPBCB\listener\alert\log.xml

    Listening Endpoints Summary...

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AMAZONA-77TPBCB)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "dgb" has 1 instance(s).
    Instance "dgb", status BLOCKED, has 1 handler(s) for this service...
    The command completed successfully

    The next think was to change the tnsnames to be like that:

    DGB =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 177.1.1.1)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ORCL) (UR = A)
    )
    )


    but I'm still not able to connect

    C:\>TNSPING DGB
    Tentativa de contatar (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
    (HOST = 177.1.1.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL) (UR = A)))
    OK (310 ms)

    sqlplus sys@dgb as sysdba
    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    any tip would be appreciated
  • 8. Re: Connecting to a No Mounted instance using tnsnames
    vlethakula Expert
    Currently Being Moderated
    reload the listener and try
  • 9. Re: Connecting to a No Mounted instance using tnsnames
    sb92075 Guru
    Currently Being Moderated
    KeenOnOracle wrote:
    sorry for the delay... I was testing and testing the scenario.

    I'm unfortunately not able to connect yes..

    After creating a password file on both servers I'm still getting the message.
    The funny thing is thta I can contact the remote instance just typing

    connect auxiliary dgb
    it connects but I receive this error on the Command

    RMAN> run {
    2> allocate channel c1 type disk;
    3> allocate auxiliary channel c2 type disk;
    4> duplicate target database for standby from active database;
    5> }
    RMAN-06217:not connected to auxiliary database with a net service name

    ok, the next steps is to show you guys

    C:\>lsnrctl status "for the LOCAL server"
    LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 04-OUT-2012 13:00:06
    Established connection com (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dga)(PORT=1521)))

    STATUS do LISTENER
    ------------------------
    Apelido LISTENER
    VersÒo TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
    Data Inicial 27-SET-2012 09:10:50
    Funcionamento 7 dias 3 hr. 49 min. 17 seg
    NÝvel de Anßlise off
    Seguranþa ON: Local OS Authentication
    SNMP OFF
    Arq. ParÔm. Listn. C:\app\Administrador\product\11.2.0\dbhome_1\network\admin\listener.ora
    Arq. Log Listener C:\app\Administrador\diag\tnslsnr\dga\listener\alert\log.xml
    Resumo de Atendimento...

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dga)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
    Resumo de Serviþos...

    O serviþo "CLRExtProc" tem 1 instÔncia(s).
    InstÔncia "CLRExtProc", status UNKNOWN, tem 1 handler(s) para este serviþo...
    O serviþo "ORCLXDB" tem 1 instÔncia(s).
    InstÔncia "orcl", status READY, tem 1 handler(s) para este serviþo...
    O serviþo "orcl" tem 1 instÔncia(s).
    InstÔncia "orcl", status READY, tem 1 handler(s) para este serviþo...
    O comando foi executado com Ûxito


    C:\>lsnrctl status "for the REMOTE server"

    LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 04-OCT-2012 15:55:04
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AMAZONA-77TPBCB)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
    Start Date 28-SEP-2012 18:30:04
    Uptime 5 days 21 hr. 24 min. 59 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF

    Listener Parameter File D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
    Listener Log File D:\app\Administrator\diag\tnslsnr\AMAZONA-77TPBCB\listener\alert\log.xml

    Listening Endpoints Summary...

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AMAZONA-77TPBCB)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "dgb" has 1 instance(s).
    Instance "dgb", status BLOCKED, has 1 handler(s) for this service...
    see line directly ABOVE?
    It means remote client can not connect to service "dgb"
  • 10. Re: Connecting to a No Mounted instance using tnsnames
    KeenOnOracle Explorer
    Currently Being Moderated
    I reloaded both listener but no change.

    sb92075,
    I see that, but thats the point.

    How can I release this connection to the nomounted instance? Thats my first doubt
  • 11. Re: Connecting to a No Mounted instance using tnsnames
    Shivananda Rao Guru
    Currently Being Moderated
    Post the listener.ora file on the server where the Auxiliary DB resides.
    Add a static entry of the auxiliary instance to the listener.
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME=<auxiliary SID>)
    (ORACLE_HOME=<ORACLE_HOME path>)
    )
    )
  • 12. Re: Connecting to a No Mounted instance using tnsnames
    sb92075 Guru
    Currently Being Moderated
    KeenOnOracle wrote:
    I reloaded both listener but no change.

    sb92075,
    I see that, but thats the point.

    How can I release this connection to the nomounted instance? Thats my first doubt
    http://www.lmgtfy.com/?q=oracle+listener+status+blocked
  • 13. Re: Connecting to a No Mounted instance using tnsnames
    vlethakula Expert
    Currently Being Moderated
    I could see difference in tns entries , SERVICE_NAME=ORCL ?
    Try SERVICE_NAME with instance name and (UR=A)
  • 14. Re: Connecting to a No Mounted instance using tnsnames
    KeenOnOracle Explorer
    Currently Being Moderated
    vreddy,

    GREAT TIP!!!

    I now can run the command and I'm following the process to post the results here.

    Tks a lot

    Edited by: KeenOnOracle on Oct 4, 2012 2:49 PM

    Edited by: KeenOnOracle on Oct 4, 2012 2:49 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points