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

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


  1. 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.
    


  2. 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
    


  3. 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
    


  4. 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