10G RAC环境中存储过程无任何反应的解决方法

环境：Windows 2003 R2 SP2

     Oracle 10g RAC

 

故障描述：在编译存储过程的时候,程序死住，如图所示。

所有语句都在测试机(单机)上通过测试才拿到生产机上运行的，但是在调试的时候就没反应了。检查listener_sid.log和alert_sid.log均无任何错误。

查看listener.ora文件发现，缺少实例名的解释。错误就在这里。

db_1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db-sys1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = db-sys2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVICE_NAME = db)
    )
  )

When using the PL/SQL Debugger with a RAC database, you must either have an additional entry in your TNSNames.ora file for the connected instance, or you must connect directly to an instance of the cluster without letting the server assign an instance.

During debugging, Toad creates two background sessions for handling debugger calls, called the Target and Debug sessions. These two sessions must be created on the same instance as the main Toad session, because Oracle does not support Debugging across RAC instances.

To accomplish this, Toad queries the data dictionary to find the current instance name, then searches the TNSNames.ora file, starting at the beginning, for the first entry which has either the SERVICE_NAME or INSTANCE_NAME equal to the name found in the data dictionary. Toad will then use this secondary TNSNames entry when creating the background debugger sessions.

For example, in a two node RAC environment, a user would have their main RAC entry looking something like this:

RAC =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.2)(PORT = 1521))
      )
      (LOAD_BALANCE = ON)
      (CONNECT_DATA =
          (SERVICE_NAME = RAC)
          (FAILOVER_MODE =
      (TYPE = SELECT)
      (METHOD = BASIC)
      (RETRIES = 180)
            (DELAY = 5)
          )
      )
  )

In this two node environment, the user would then need to have two additional TNSNames entries, one each for the individual instances in the RAC. In these entries, the can either use the syntax:

RAC1 =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521))
      )
      (CONNECT_DATA =
          (SERVICE_NAME = RAC) # notice the difference
          (INSTANCE_NAME = RAC1) # betwen this
      )
  )

Or:

RAC2 =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.2)(PORT = 1521))
      )
      (CONNECT_DATA =
          (SERVICE_NAME = RAC2) # and this
      )
  )