RAC Attack - Oracle Cluster Database at Home/Parallel Query Test

From Wikibooks, open books for an open world
Jump to navigation Jump to search


  2. Login to the node collabn1 and open a connection to the database as SYSDBA and unlock the SH user account. Also grant it DBA access. SQL> alter user sh identified by sh account unlock; User altered. SQL> grant dba to sh; Grant succeeded.
  3. Reconnect to the database as the sh user with the RAC1 service name. Check your SID and the service you're connected to. SQL> connect sh/sh@RAC1 Connected. 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
  4. Enable tracing and run a parallel query on the SH schema. Look for the trace files to determine where the slaves ran. Which nodes to the slaves run on? 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
  5. Create a new service which only runs on node 2 and repeat the test (from collabn1). Which nodes to the slaves run on now? How is this different from 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 Services Summary... Service "pxtest.vm.ardentperf.com" has 1 instance(s). Instance "RAC2", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready REMOTE SERVER (ADDRESS=(PROTOCOL=TCP)(HOST=collabn2.vm.ardentperf.com)(PORT=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


Retrieved from "https://en.wikibooks.org/w/index.php?title=RAC_Attack_-_Oracle_Cluster_Database_at_Home/Parallel_Query_Test&oldid=2509020"