7 Replies Latest reply: Mar 26, 2012 12:05 PM by 665592 RSS

    ORA-12528: TNS listener BLOCKED - trying to duplicate database

    Catch 22
      Hello,

      I would like to test the RMAN duplicate from active database in version 11.2.0.1.

      The name of the duplicate (auxiliary) database should be "clonedb". I created a password file, spfile and added the clonedb database to tnsnames.ora. The clonedb instance is in nomount state. Tnsping clonedb is also working.

      When I try to make a connection:
      sqlplus 'sys/mypassword@clonedb as sysdba'
      ORA-12528, Blocked, all appropriate instances are blocking new connections.

      So I wonder if this could be due to the nomount state of the instance and dynamic listener configuration. Correct?

      I made an entry in the listener.ora file, and it's showing "clonedb" status UKNOWN now, but I still cannot make a sqlnet connection since the dynamic listener apparently takes precedence. Can I turn off the dynamic listener configuration, but then I will loose the connection to the other databases. Do I need to make manual entries in listener.ora for all databases in order to perform RMAN duplicate?

      I also tried to connect to the auxiliary clonedb instance as local target with OS authentication, but the ORA-12528 error persists. Sqlplus / as sysdba works though. Below some more info. Any ideas how to resolve the issue.

      Thanks!
      $ lsnrctl status
      
      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 19-DEC-2010 01:05:40
      
      Copyright (c) 1991, 2009, Oracle.  All rights reserved.
      
      Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
      Start Date                19-DEC-2010 00:39:22
      Uptime                    0 days 0 hr. 26 min. 18 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
      Listener Log File         /u01/app/oracle/diag/tnslsnr/ol55/listener/alert/log.xml
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol55.example.com)(PORT=1521)))
      Services Summary...
      Service "+ASM" has 1 instance(s).
        Instance "+ASM", status READY, has 1 handler(s) for this service...
      Service "clonedb" has 1 instance(s).
        Instance "clonedb", status UNKNOWN, has 1 handler(s) for this service...
      Service "clonedb.example.com" has 1 instance(s).
        Instance "clonedb", status BLOCKED, has 1 handler(s) for this service...
      Service "orcl.example.com" has 1 instance(s).
        Instance "orcl", status READY, has 1 handler(s) for this service...
      Service "orclXDB.example.com" has 1 instance(s).
        Instance "orcl", status READY, has 1 handler(s) for this service...
      Service "rcat.example.com" has 1 instance(s).
        Instance "rcat", status READY, has 1 handler(s) for this service...
      Service "rcatXDB.example.com" has 1 instance(s).
        Instance "rcat", status READY, has 1 handler(s) for this service...
      The command completed successfully
      
      $ tnsping clonedb
      
      TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 19-DEC-2010 01:06:18
      
      Copyright (c) 1997, 2009, Oracle.  All rights reserved.
      
      Used parameter files:
      /u01/app/oracle/product/11.2.0/db/network/admin/sqlnet.ora
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol55.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = clonedb.example.com)))
      OK (10 msec)
      
      $ cat tnsnames.ora
      # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
      # Generated by Oracle configuration tools.
      
      RCAT =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = ol55.example.com)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = rcat.example.com)
          )
        )
      
      ORCL =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = ol55.example.com)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = orcl.example.com)
          )
        )
      
      CLONEDB =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = ol55.example.com)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = clonedb.example.com)
          )
        )
      
      
      
      $ cat listener.ora
      # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
      # Generated by Oracle configuration tools.
      
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ol55.example.com)(PORT = 1521))
          )
        )
      
      SID_LIST_LISTENER =
         (SID_LIST =
             (SID_DESC =
                 (ORACLE_HOME= /u01/app/oracle/product/11.2.0/db)
                 (SID_NAME = clonedb)
             )
      )
      
      
      ADR_BASE_LISTENER = /u01/app/oracle
      
      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
      
      
      $ cat initclonedb.ora 
      clonedb.__db_cache_size=192937984
      clonedb.__java_pool_size=4194304
      clonedb.__large_pool_size=4194304
      clonedb.__oracle_base='/u01/app/oracle'
      clonedb.__pga_aggregate_target=339738624
      clonedb.__sga_target=503316480
      clonedb.__shared_io_pool_size=0
      clonedb.__shared_pool_size=293601280
      clonedb.__streams_pool_size=0
      *.audit_file_dest='/u01/app/oracle/admin/clonedb/adump'
      *.audit_trail='db'
      *.backup_tape_io_slaves=TRUE
      *.compatible='11.2.0.0.0'
      *.control_files='/scratch/oradata/clonedb/controlfile/control01.ctl','/scratch/oradata/clonedb/controlfile/control02.ctl'
      *.db_block_checking='full'
      *.db_block_checksum='full'
      *.db_block_size=8192
      *.db_create_file_dest='/scratch/oradata/clonedb'
      *.db_domain='example.com'
      *.db_name='clonedb'
      *.db_recovery_file_dest='+FRA'
      *.db_recovery_file_dest_size=12884901888
      *.log_archive_dest='/scratch/oradata/clonedb/archivelog'
      *.diagnostic_dest='/u01/app/oracle'
      *.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
      *.memory_target=843055104
      *.open_cursors=300
      *.processes=150
      *.remote_login_passwordfile='EXCLUSIVE'
      *.undo_tablespace='UNDOTBS1'
      *.db_file_name_convert=('+DATA','/scratch/oradata/clonedb/datafile')
      *.log_file_name_convert=('+DATA','/scratch/oradata/clonedb/logifle')
        • 1. Re: ORA-12528: TNS listener BLOCKED - trying to duplicate database
          sb92075
          what clues exist within /u01/app/oracle/diag/tnslsnr/ol55/listener/alert/log.xml concerning the connection attempt to clonedb?

          post excerpted contents

          Edited by: sb92075 on Dec 18, 2010 4:20 PM
          • 2. Re: ORA-12528: TNS listener BLOCKED - trying to duplicate database
            Catch 22
            Thanks for responding. Here is the log from the last connection attempt - I removed some xml codings:

            time='2010-12-19T01:23:23.007+01:00' org_id='oracle' comp_id='tnslsnr'
            type='UNKNOWN' level='16' host_id='ol55.example.com'
            host_addr='127.0.0.1'>
            WARNING: Subscription for node down event still pending
            time='2010-12-19T01:23:23.008+01:00' org_id='oracle' comp_id='tnslsnr'
            type='UNKNOWN' level='16' host_id='ol55.example.com'
            host_addr='127.0.0.1'>
            19-DEC-2010 01:23:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ol55.example.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
            time='2010-12-19T01:23:33.575+01:00' org_id='oracle' comp_id='tnslsnr'
            type='UNKNOWN' level='16' host_id='ol55.example.com'
            host_addr='127.0.0.1'>
            19-DEC-2010 01:23:33 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=clonedb.example.com)(CID=(PROGRAM=sqlplus)(HOST=ol55.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=56278)) * establish * clonedb.example.com * 12528
            time='2010-12-19T01:23:33.575+01:00' org_id='oracle' comp_id='tnslsnr'
            type='UNKNOWN' level='16' host_id='ol55.example.com'
            host_addr='127.0.0.1'>
            TNS-12528: TNS:listener: all appropriate instances are blocking new connections
            • 3. Re: ORA-12528: TNS listener BLOCKED - trying to duplicate database
              sb92075
              How many systems are involved with this problem set? Please enumerate all.

              Is any type of Operating System Virtualization installed on this system.
              • 4. Re: ORA-12528: TNS listener BLOCKED - trying to duplicate database
                Catch 22
                It's only 1 system, Oracle Linux 5.5 UEK running as guest inside Virtualbox. All instances, orcl, rcat, +ASM, clonedb are on the same machine ol55.example.com. There is no firewall, SELlinux or virtualization configured. The system has only one IP interface with one IP address. I'm connected to it in a terminal session (ssh).

                $ cat /etc/hosts
                # Do not remove the following line, or various programs
                # that require network functionality will fail.
                127.0.0.1     ol55.example.com ol55 localhost.localdomain localhost
                10.0.0.6 ol55.example.com ol55

                Edited by: MaC on Dec 18, 2010 4:48 PM
                • 5. Re: ORA-12528: TNS listener BLOCKED - trying to duplicate database
                  Catch 22
                  Think I found the problem:

                  The *(UR=A)* clause is intended to work with a dynamically registered handler so the use of SERVICE_NAME versus SID is preferred. (ID 362656.1)

                  I modified my tnsnames.ora:
                  CLONEDB =
                    (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = ol55.example.com)(PORT = 1521))
                      (CONNECT_DATA =
                        (SERVER = DEDICATED)
                        (SERVICE_NAME = clonedb.example.com)
                        (UR = A)
                      )
                    )
                  
                  
                  sqlplus 'sys/mypassword@clonedb as sysdba'
                  
                  SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 19 02:12:47 2010
                  
                  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
                  
                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                  • 6. Re: ORA-12528: TNS listener BLOCKED - trying to duplicate database
                    Catch 22
                    There was still a problem, resulting in RMAN-04006 during the rman duplicate operation when executing the Memory Script, right after the first shutdown of the auxiliary database:
                    RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

                    I fixed the problem by modifying my listener.ora file as following, adding the Global_DBname entry:
                    LISTENER =
                      (DESCRIPTION_LIST =
                        (DESCRIPTION =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = ol55.example.com)(PORT = 1521))
                        )
                      )
                    
                    SID_LIST_LISTENER =
                       (SID_LIST =
                           (SID_DESC =
                              (ORACLE_HOME= /u01/app/oracle/product/11.2.0/db)
                              (SID_NAME = clonedb)
                              (GLOBAL_DBNAME= clonedb.example.com)
                           )
                        )
                    • 7. Re: ORA-12528: TNS listener BLOCKED - trying to duplicate database
                      665592
                      Thanks a lot!
                      With "(UR = A)" it's OK.