RAC 攻击 - Oracle 集群数据库在家/并行查询测试

上一页: 服务、故障转移和负载均衡

RAC SQL 和 PLSQL (e)

下一页: RAC 备份和恢复

登录到节点 collabn1 并打开一个到数据库的连接，以 SYSDBA 身份并解锁 SH 用户帐户。也授予其 DBA 访问权限。SQL> alter user sh identified by sh account unlock; 用户已更改。SQL> grant dba to sh; 授予成功。
以sh用户身份，使用RAC1服务名重新连接到数据库。检查您的 SID 和您连接到的服务。SQL> connect sh/sh@RAC1 连接成功。SQL> select sid from v$mystat where rownum=1; SID ---------- 145 SQL> col sid format 9999 SQL> col username format a10 SQL> col program format a40 SQL> col service_name format a20 SQL> set linesize 100 SQL> select sid, username, program, service_name from v$session where username='SH'; SID USERNAME PROGRAM SERVICE_NAME ----- ---------- ---------------------------------------- -------------- 145 SH [email protected] (TNS RAC.vm.ardent
启用跟踪并对 SH 模式运行并行查询。查找跟踪文件以确定从属进程运行的位置。从属进程在哪些节点上运行？exec dbms_session.set_identifier('racpx01'); alter session set tracefile_identifier = 'racpx01'; exec dbms_monitor.client_id_trace_enable(client_id=>'racpx01'); select /*+parallel*/ p.prod_name, sum(s.amount_sold) from products p, sales s where p.prod_id = s.prod_id group by p.prod_name; exec dbms_monitor.client_id_trace_disable(client_id=>'racpx01'); SQL> col value format a60 SQL> select inst_id, value from gv$parameter where name='user_dump_dest'; INST_ID VALUE ---------- ------------------------------------------------------------ 1 /u01/app/oracle/diag/rdbms/rac/RAC1/trace 2 /u01/app/oracle/diag/rdbms/rac/RAC2/trace SQL> host ssh collabn1 ls -sh /u01/app/oracle/diag/rdbms/rac/RAC1/trace/*racpx01.trc SQL> host ssh collabn2 ls -sh /u01/app/oracle/diag/rdbms/rac/RAC2/trace/*racpx01.trc
创建一个仅在节点 2 上运行的新服务，并重复测试（从collabn1）。从属进程现在在哪些节点上运行？这与 Oracle 10g 有什么不同？collabn1:/home/oracle[RAC1]$ srvctl add service -d RAC -s pxtest -r RAC2 -a RAC1 collabn1:/home/oracle[RAC1]$ srvctl start service -d RAC -s pxtest collabn1:/home/oracle[RAC1]$ lsnrctl services 服务摘要... 服务“pxtest.vm.ardentperf.com”具有 1 个实例。实例“RAC2”，状态为 READY，为此服务具有 1 个处理程序... 处理程序：”DEDICATED“已建立：0 已拒绝：0 状态：准备就绪远程服务器（地址=(协议=TCP)(主机=collabn2.vm.ardentperf.com)(端口=1521)）collabn1:/home/oracle[RAC1]$ vi $ORACLE_HOME/network/admin/tnsnames.ora PXTEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = collabn2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PXTEST.vm.ardentperf.com) ) ) collabn1:/home/oracle[RAC1]$ sqlplus sh/sh@pxtest SQL> col sid format 9999 SQL> col username format a10 SQL> col program format a40 SQL> col service_name format a20 SQL> select sid, username, program, service_name from v$session where username='SH'; SID USERNAME PROGRAM SERVICE_NAME ----- ---------- ---------------------------------------- -------------- 124 SH [email protected] (TNS V1-V3) pxtest exec dbms_session.set_identifier('racpx05'); alter session set tracefile_identifier = 'racpx05'; exec dbms_monitor.client_id_trace_enable(client_id=>'racpx05'); select /*+parallel*/ p.prod_name, sum(s.amount_sold) from products p, sales s where p.prod_id = s.prod_id group by p.prod_name; exec dbms_monitor.client_id_trace_disable(client_id=>'racpx05'); host ssh collabn1 ls -sh /u01/app/oracle/diag/rdbms/rac/RAC1/trace/*racpx05.trc host ssh collabn2 ls -sh /u01/app/oracle/diag/rdbms/rac/RAC2/trace/*racpx05.trc