How do I debug PL/SQL when connected to a RAC?

最新推荐文章于 2024-06-17 20:57:15 发布

原创最新推荐文章于 2024-06-17 20:57:15 发布 · 293 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#Toad #debug #RAC

Oracle 专栏收录该内容

6 篇文章

订阅专栏

本文介绍使用TOAD工具连接Oracle RAC数据库进行PL/SQL调试的方法。为确保调试正常运行，用户需具备GV$INSTANCE的SELECT权限，并需在TNSNames.ora文件中为每个RAC实例设置独立条目或直接连接到集群中的某个实例。

用 TOAD 连接 Oracle RAC 数据库，调试存储过程的时候报错：
Cannot debug session. In order to debug when connected to a RAC instance you must have SELECT privilege for GV$INSTANCE.
授予用户DBA角色后，继续抱错：
Cannot debug session. In order to debug when connected to a RAC instance you must have entries for the individual RAC instance in your tnsnames.ora file or connect directly to an individual RAC instacne.
修改 tnsnames.ora 文件后，终于可以 debug 了！

asktoad 给出以下的解释：
How do I debug PL/SQL when connected to a RAC?
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
      )
)

原文出处：http://asktoad.com/DWiki/doku.php/faq/answers/procedure_editor?DokuWiki=e217ad0ed5bdf990bcc4b95bb9be96b9#how_do_i_debug_plsql_when_connected_to_a_rac