Shalok, Fifth Mehl :
Those who do not remember the lord while they are alive, shall mix with the dust when they die. Nanak, the foolish and filthy cynic passes his life engrossed in the world.
14:57 : oracle : rac1.rac.meditate.com : @GSINGH1 : /home/oracle
$ . oraenv
ORACLE_SID = [GSINGH!] ? GSINGH1
The Oracle base has been set to /u01/app/oracle
14:57 : oracle : rac1.rac.meditate.com : @GSINGH1 : /home/oracle
$ srvctl status service -d GSINGH
14:58 : oracle : rac1.rac.meditate.com : @GSINGH1 : /home/oracle
$ srvctl add service -d GSINGH -s PARALLEL -r GSINGH1 -a GSINGH2
15:03 : oracle : rac1.rac.meditate.com : @GSINGH1 : /home/oracle
$
15:07 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl start service -d GSINGH -s PARALLEL
15:07 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl status service -d GSINGH -s PARALLEL
Service PARALLEL is running on instance(s) GSINGH1
15:08 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$
15:08 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl modify service -d GSINGH -s PARALLEL -n -i GSINGH1,GSINGH2
15:09 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl stop service -d GSINGH -s PARALLEL
15:09 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl start service -d GSINGH -s PARALLEL
15:10 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl status service -d GSINGH -s PARALLEL
Service PARALLEL is running on instance(s) GSINGH1,GSINGH2
15:10 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$
===================================================================================================================================
15:10 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 15:10:58 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter parallel servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 40
parallel_min_percent integer 0
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 2
parallel_servers_target integer 16
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SQL>
SQL> conn gurpartap/gurpartap@parallel
Connected.
SQL>
=======================================================================================================
Open another window i.e. window 2 and execute the following query:
set pages 50
col SID_SERIAL format a15
col OSUSER format a12
col module format a25
col action format a20
col machine format a20
col username format a19
col QCINST_ID format 99
set lines 240
SELECT y.inst_id,a.qcinst_id, a.qcsid, a.qcserial# qcser, a.sid||','||a.serial# sid_serial, substr(y.osuser,1,13) osuser, y.username, substr(y.module,1,25) module, substr(y.action,1,20) action, substr(y.machine,8,20) machine, a.degree, a.req_degree, y.inst_id, y.sql_id, y.status, y.logon_time, y.last_call_et lc_et
FROM gv$px_session a, gv$session y
WHERE y.sid = a.sid
and y.inst_id = a.inst_id
order by a.qcsid, y.username, y.sql_id, y.inst_id, y.status
/
SQL> set line 300
SQL> set pages 50
col SID_SERIAL format a15
col OSUSER format a12
col module format a25
col action format a20
col machine format a20
col username format a19
col QCINST_ID format 99
set lines 240
SELECT y.inst_id,a.qcinst_id, a.qcsid, a.qcserial# qcser, a.sid||','||a.serial# sid_serial, substr(y.osuser,1,13) osuser, y.username, substr(y.module,1,25) module, substr(y.action,1,20) action, substr(y.machine,8,20) machine, a.degree, a.req_degree, y.inst_id, y.sql_id, y.status, y.logon_time, y.last_call_et lc_et
FROM gv$px_session a, gv$session y
WHERE y.sid = a.sid
and y.inst_id = a.inst_id
SQL> order by a.qcsid, y.username, y.sql_id, y.inst_id, y.status
/
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6
INST_ID QCINST_ID QCSID QCSER SID_SERIAL OSUSER USERNAME MODULE ACTION MACHINE DEGREE REQ_DEGREE INST_ID SQL_ID STATUS LOGON_TIM LC_ET
---------- --------- ---------- ---------- --------------- ------------ ------------------- ------------------------- -------------------- -------------------- ---------- ---------- ---------- ------------- -------- --------- ----------
1 1 76 7 29,71 oracle SYS sqlplus@rac1.rac.meditate c.meditate.com 2 2 1 8wn8m9gpwnw3s ACTIVE 26-FEB-13 0
1 76 76,7 oracle SYS sqlplus@rac1.rac.meditate c.meditate.com 1 8wn8m9gpwnw3s ACTIVE 26-FEB-13 0
2 1 76 7 80,23 oracle SYS sqlplus@rac1.rac.meditate c.meditate.com 2 2 2 8wn8m9gpwnw3s ACTIVE 26-FEB-13 0
SQL>
SQL> /
Now go back on the original window i.e. window 1 and execute the query in parallel as follows:
select /*+ parallel( a 33) parallel(b 33) */ count(*) from dba_objects a, dba_objects b;
SQL> l
1* select /*+ parallel( a 33) parallel(b 33) */ count(*) from dba_objects a, dba_objects b
SQL> /
Now go back to window 2 and check the parallel sessions from the query we ran earlier on views gv$px_session and gv$session and you will see 33 parallel sessions
on the database as given below and they will have be running on both node1 and node2 as:
SQL>
/
INST_ID QCINST_ID QCSID QCSER SID_SERIAL OSUSER USERNAME MODULE ACTION MACHINE DEGREE REQ_DEGREE INST_ID SQL_ID STATUS LOGON_TIM LC_ET
---------- --------- ---------- ---------- --------------- ------------ ------------------- ------------------------- -------------------- -------------------- ---------- ---------- ---------- ------------- -------- --------- ----------
1 1 56 3 77,3 oracle SYS sqlplus@rac1.rac.meditate c.meditate.com 2 2 1 8wn8m9gpwnw3s ACTIVE 26-FEB-13 0
1 56 56,3 oracle SYS sqlplus@rac1.rac.meditate c.meditate.com 1 8wn8m9gpwnw3s ACTIVE 26-FEB-13 1
2 1 56 3 82,9 oracle SYS sqlplus@rac1.rac.meditate c.meditate.com 2 2 2 8wn8m9gpwnw3s ACTIVE 26-FEB-13 0
1 2 74 53 63,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 64,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 65,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 66,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 67,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 68,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 69,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 70,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 71,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 72,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 73,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 74,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 75,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 62,3 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 61,31 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 53,15 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 55,25 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
2 2 74 53 45,111 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 35,101 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 52,5 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 58,189 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 59,59 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 66,87 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 67,119 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 68,21 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 69,37 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 70,29 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 74 74,53 oracle GURPARTAP SQL*Plus c.meditate.com 2 3z073sy7b8gfa ACTIVE 26-FEB-13 5
2 2 74 53 75,45 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 78,7 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 79,5 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 80,37 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 81,15 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 51,5 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
37 rows selected.
SQL>
INST_ID QCINST_ID QCSID QCSER SID_SERIAL OSUSER USERNAME MODULE ACTION MACHINE DEGREE REQ_DEGREE INST_ID SQL_ID STATUS LOGON_TIM LC_ET
---------- --------- ---------- ---------- --------------- ------------ ------------------- ------------------------- -------------------- -------------------- ---------- ---------- ---------- ------------- -------- --------- ----------
1 1 56 3 77,7 oracle SYS sqlplus@rac1.rac.meditate c.meditate.com 2 2 1 8wn8m9gpwnw3s ACTIVE 26-FEB-13 0
1 56 56,3 oracle SYS sqlplus@rac1.rac.meditate c.meditate.com 1 8wn8m9gpwnw3s ACTIVE 26-FEB-13 0
2 1 56 3 82,13 oracle SYS sqlplus@rac1.rac.meditate c.meditate.com 2 2 2 8wn8m9gpwnw3s ACTIVE 26-FEB-13 0
1 2 74 53 63,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 64,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 65,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 66,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 67,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 68,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 69,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 70,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 71,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 72,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 73,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 74,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 75,1 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 62,3 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 61,31 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 53,15 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
1 2 74 53 55,25 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 1 3z073sy7b8gfa ACTIVE 26-FEB-13 1
2 2 74 53 45,111 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 35,101 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 52,5 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 58,189 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 59,59 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 66,87 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 67,119 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 68,21 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 69,37 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 70,29 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 74 74,53 oracle GURPARTAP SQL*Plus c.meditate.com 2 3z073sy7b8gfa ACTIVE 26-FEB-13 5
2 2 74 53 75,45 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 78,7 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 79,5 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 80,37 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 81,15 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
2 2 74 53 51,5 oracle GURPARTAP SQL*Plus c.meditate.com 33 33 2 3z073sy7b8gfa ACTIVE 26-FEB-13 0
37 rows selected.
Now on this window just stop instance 1 and see that the query in window 1 will error out instead of failing over:
On window 2:
SQL> !srvctl stop instance -d GSINGH -i GSINGH1
SQL>
In window 1:
SQL> select /*+ parallel( a 33) parallel(b 33) */ count(*) from dba_objects a, dba_objects b;
select /*+ parallel( a 33) parallel(b 33) */ count(*) from dba_objects a, dba_objects b
*
ERROR at line 1:
ORA-12805: parallel query server died unexpectedly
SQL>
SQL>
==================================================================================================
Shalok, Fifth Mehl:
Within in my mind, I think thoughts of always rising early, and making the effort. O Lord, my Friend, please bless Nanak with the habit of singing the Kirtan of the Lord's Praises.
No comments:
Post a Comment