管理运维--解决ORACLE数据库RAC环境DEBUG出错问题

前提环境、配置简要说明11gR2：

SCAN 10.20.30.60
NODE1 10.20.30.61
NODE2 10.20.30.62
服务名称：JIMRAC两个实例名：JIMRAC1、JIMRAC2
服务别名：JIMDBPR
连接用户：SCM_CHINA

RAC数据库DEBUG需要显式指定实例节点

JIMRAC1_30.61=
 (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=10.20.30.61)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=JIMDBPR)
      (INSTANCE_NAME=JIMRAC1)
    )
 )

如果不想固定节点配置多个tnsnames连接串在多节点间自动故障转移

显然这种方法就埋没了RAC环境SCAN IP的设计初衷、但是为了随时DEBUG也是个选择

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

Else will Error:
Unable to find RAC connection information for JIMRAC1 (INST_ID = 1).

连接用户需要有编译权限

grant debug any procedure to SCM_CHINA;
grant debug connection to SCM_CHINA;
grant create any procedure to SCM_CHINA; --通常是已经拥有

程序包PACKAGE存储过程需要编译并添加DEBUG信息Add Debug Information

alter package SCM_CHINA.YOUR_DEFINITION_PKG compile debug;
alter package SCM_CHINA.YOUR_DEFINITION_PKG compile debug body;
或者
alter session set plsql_optimize_level = 1;
alter session set plsql_debug = true;
exec dbms_utility.compile_schema(SCM_CHINA); --用户对象多时避免

验证是否已添加DEBUG属性

--当前用户查看自己的对象
select name, type, plsql_optimize_level, plsql_debug
from user_plsql_object_settings where name = 'YOUR_DEFINITION_PKG';

--管理用户查看其他用户的对象
select name, type, plsql_optimize_level, plsql_debug
from ALL_PLSQL_OBJECT_SETTINGS where name = 'YOUR_DEFINITION_PKG';

SELECT PO.OWNER,PO.OBJECT_NAME,PO.OBJECT_TYPE,PO.DEBUGINFO
,'ALTER ' || REPLACE(object_type,'PACKAGE BODY','PACKAGE') || ' ' || owner || '.' || object_name ||DECODE(object_type,'PACKAGE BODY',' COMPILE BODY;',' COMPILE;') COPILE_NO_DEBUG
,'ALTER ' || REPLACE(object_type,'PACKAGE BODY','PACKAGE') || ' ' || owner || '.' || object_name ||DECODE(object_type,'PACKAGE BODY',' COMPILE DEBUG BODY;',' COMPILE DEBUG;') COPILE_WITH_DEBUG
FROM   SYS.ALL_PROBE_OBJECTS PO
--WHERE OBJECT_NAME='YOUR_DEFINITION_PKG' AND DEBUGINFO IN ('F','T')
ORDER BY owner, object_type, object_name;

待研究使用调用方式

CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.20.30.61', '52199' )
CALL DBMS_DEBUG_JDWP.DISCONNECT()

关闭调试信息

alter session set PLSQL_DEBUG=false;
alter session set PLSQL_OPTIMIZE_LEVEL=2;