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 sqlplus@collabn1.vm.ardentperf.com (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 sqlplus@collabn1.vm.ardentperf.com (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