toad在RAC環境下無法調試oracle預存程序的解決辦法

作業環境：

server : oracle 10g RAC

client :  toad for oracle 8.6

問題現象描述： 

將預存程序load in editor，設定斷點，運行後依次發生下列錯誤提示：

 ORA-12560: TNS:protocol adapter error

 Cannot connect debug session. In order to debug when connected to a RAC instance you must have entries for the individual RAC instances in your tnsnames.ora file or connect directly to an individual RAC instance.

google後得知：

來源文件 <http://asktoad.com/DWiki/doku.php/faq/answers/procedure_editor?DokuWiki=e217ad0ed5bdf990bcc4b95bb9be96b9>

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
)
)

或者 

RAC2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = RAC) # and this [紅色部分原為“RAC2”，但依據上下文，改為“RAC”便於理解和操作]
)
)

經測試以上兩種方案均可行，個人理解就是在用戶端通過修改tnsnames.ora檔案，將伺服器端雙機環境轉換成單機環境，請注意此操作並不對oracle伺服器端環境作任何改變。

參考：<http://asktoad.com/DWiki/doku.php/faq/answers/procedure_editor?DokuWiki=e217ad0ed5bdf990bcc4b95bb9be96b9>