14 Replies Latest reply: Oct 4, 2012 12:49 PM by KeenOnOracle RSS

    Connecting to a No Mounted instance using tnsnames

    KeenOnOracle
      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)
          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
            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)
              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
                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
                  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
                    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
                      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
                        reload the listener and try
                        • 9. Re: Connecting to a No Mounted instance using tnsnames
                          sb92075
                          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
                            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
                              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
                                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
                                  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
                                    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