Use Load Balancing Advisory (continued)

Practices for Lesson 8

Practice 8-2: Use Load Balancing Advisory (continued)

# Must be executed on NODE1 !!!!!!!!!!

y=`cat /home/oracle/nodeinfo | sed -n '1,1p'`

z=`cat /home/oracle/nodeinfo | sed -n '2,2p'`

DBNAME=`ps -ef | grep dbw0_RDB | grep -v grep | grep -v

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

Oracle Database 11g: RAC Administration A - 87

ssh $z "cat /home/oracle/solutions/less08/tnslba.ora >>

/u01/app/oracle/product/11.1.0/asm_1/network/admin/tnsnames.or a"

[oracle@vx0306 less08]$

[oracle@vx0306 less08]$ cat wrong_tnslba.ora SNOLBA =

3) From a terminal window, execute the createfan.sh script. This script creates a simple table used by the following scripts in this practice. This script is located in your

$HOME/solutions/less08 directory.

[oracle@vx0306 less08]$ cat createfan.sh

#!/bin/ksh

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

y=`cat /home/oracle/nodeinfo | sed -n '1,1p'`

z=`cat /home/oracle/nodeinfo | sed -n '2,2p'`

DBNAME=`ps -ef | grep dbw0_RDB | grep -v grep | grep -v callout1 | awk '{ print $8 }' | sed 's/1/''/' | sed 's/ora_dbw0_/''/'`

I1NAME=$DBNAME"1"

I2NAME=$DBNAME"2"

export ORACLE_SID=$I1NAME

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

$ORACLE_HOME/bin/sqlplus -s /NOLOG <<EOF

connect / as sysdba drop user JFV cascade;

create user JFV identified by jfv default tablespace users temporary tablespace temp;

grant connect, resource, dba to JFV;

connect jfv/jfv

create table fan(c number);

EOF

[oracle@vx0306 less08]$

[oracle@vx0306 less08]$ ./createfan.sh

User dropped.

User created.

Grant succeeded.

Table created.

[oracle@vx0306 less08]$

4) Make sure you keep your terminal window (referred to as TW1) open. You use it later.

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

Oracle Database 11g: RAC Administration A - 89

6) Create a new terminal window from where you will execute the following SQL statement as SYSDBA under SQL*Plus: select inst_id,count(*) from gv$session where username='JFV' group by inst_id order by inst_id;

This statement count the number of sessions connected as user JFV on both instances.

This terminal window is referred to as TW3.

[oracle@vx0306 less08]$ cat sol_08_02_06.sh

#!/bin/ksh

y=`cat /home/oracle/nodeinfo | sed -n '1,1p'`

z=`cat /home/oracle/nodeinfo | sed -n '2,2p'`

DBNAME=`ps -ef | grep dbw0_RDB | grep -v grep | grep -v

[oracle@vx0306 less08]$ cat sol_08_02_06_00.sql connect / as sysdba

host echo "execute sol_08_02_06.sql from here"

[oracle@vx0306 less08]$

[oracle@vx0306 less08]$ cat sol_08_02_06_00.sql connect / as sysdba

host echo "execute sol_08_02_06.sql from here"

[oracle@vx0306 less08]$ cat sol_08_02_06.sql

select inst_id,count(*) from gv$session where username='JFV' group by inst_id order by inst_id;

[oracle@vx0306 less08]$

[oracle@vx0306 less08]$ ./sol_08_02_06.sh

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Nov 15 05:02:47 2007

Connected.

execute sol_08_02_06.sql from here

SQL> @sol_08_02_06 no rows selected SQL>

7) Create two additional terminal windows from where you will run the primes executable to generate CPU load on your first node. These terminal windows are called TW4 and TW5, respectively.

8) In TW2, execute the startfanload.sh script using SNOLBA as the first argument to the command.

[oracle@vx0306 less08]$ cat startfanload.sh /home/oracle/solutions/less08/fan.sh $1 &

[oracle@vx0306 less08]$ cat fan.sh

#!/bin/ksh

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

Oracle Database 11g: RAC Administration A - 91 UNPW="jfv/jfv@"$1

ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 export ORACLE_HOME

while [ $x -le $y ] do

/u01/app/oracle/product/11.1.0/db_1/bin/sqlplus -s $UNPW

@fan.sql done

[oracle@vx0306 less08]$

[oracle@vx0306 less08]$ cat fan.sql begin

for i in 1..1000 loop

insert into fan select sid from v$mystat where rownum<2;

commit;

delete fan where c in (select sid from v$mystat where rownum<2);

[oracle@vx0306 less08]$ ./startfanload.sh SNOLBA [oracle@vx0306 less08]$ begin

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 5

begin

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 5

begin

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 5

begin

ERROR at line 1:

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 5

PL/SQL procedure successfully completed.

begin

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 5

PL/SQL procedure successfully completed.

9) Start the primes executable in both TW4 and TW5.

a) In TW4, you can see:

[oracle@vx0306 less08]$ ./primes DBG: Got primestore ptr is 0xb1ba7008 DBG: Initialized.

next_prime=0xb1ba7010,cur_prime=0xb1ba700c,*next_prime=0,*cur_

prime=3 pp_10K=1700 pp_10K=1422 pp_10K=1353 pp_10K=1312 pp_10K=1275 pp_10K=1258 pp_10K=1218 pp_10K=1222 pp_10K=1199

pp_10K=1203 pp_100K=13162 pp_10K=1178

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

Oracle Database 11g: RAC Administration A - 93 DBG: Initialized.

10) Monitor TW3 by repeating the execution of the SQL statement. What do you observe?

a) You should see approximately the same number of connections on both nodes—

perhaps a bit more on the second node because the first node has much more load than the second. Wait at least three minutes before using figures for your analysis.

This is the time necessary before the LBA algorithm takes place.

b) In TW3, you can see:

[oracle@vx0306 less08]$ ./sol_08_02_06.sh

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Nov 15 05:02:47 2007

Connected.

execute sol_08_02_06.sql from here

SQL> @sol_08_02_06

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

SQL> /

INST_ID COUNT(*) --- --- 1 8 2 10 SQL> /

INST_ID COUNT(*) --- --- 1 9 2 9 SQL> /

INST_ID COUNT(*) --- --- 1 8 2 10 SQL> /

INST_ID COUNT(*) --- --- 1 9 2 9 SQL> /

INST_ID COUNT(*)

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

Oracle Database 11g: RAC Administration A - 95

11) Stop primes generation by pressing [Ctrl] + [C] in both corresponding terminal windows: TW4 and TW5.

a) In TW4, you can see

12) In TW4, execute stopfanload.sh script to stop the workload.

[oracle@vx0306 less08]$ cat stopfanload.sh

#!/bin/ksh

# stopfanload.sh

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

# Must be executed on NODE1 !!!!!!!!!!

y=`cat /home/oracle/nodeinfo | sed -n '1,1p'`

z=`cat /home/oracle/nodeinfo | sed -n '2,2p'`

DBNAME=`ps -ef | grep dbw0_RDB | grep -v grep | grep -v callout1 | awk '{ print $8 }' | sed 's/1/''/' | sed 's/ora_dbw0_/''/'`

I1NAME=$DBNAME"1"

I2NAME=$DBNAME"2"

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

ps -ef | grep "fan.sh" | awk '{print "kill -9 " $2 }' >

/home/oracle/solutions/less08/x.sh

chmod 777 /home/oracle/solutions/less08/x.sh /home/oracle/solutions/less08/x.sh

ps -ef | grep "fan.sql" | awk '{print "kill -9 " $2 }' >

/home/oracle/solutions/less08/x.sh

chmod 777 /home/oracle/solutions/less08/x.sh

/home/oracle/solutions/less08/x.sh

$ORACLE_HOME/bin/sqlplus system/oracle1@$I1NAME

@/home/oracle/solutions/less08/kill_fan.sql

$ORACLE_HOME/bin/sqlplus system/oracle1@$I1NAME

@/tmp/drop_fan.sql rm /tmp/drop_fan.sql

$ORACLE_HOME/bin/sqlplus system/oracle1@$I2NAME

@/home/oracle/solutions/less08/kill_fan.sql

$ORACLE_HOME/bin/sqlplus system/oracle1@$I2NAME

@/tmp/drop_fan.sql

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

Oracle Database 11g: RAC Administration A - 97 set head off

select 'exit;' from dual;

spool off exit;

[oracle@vx0306 less08]$

[oracle@vx0306 less08]$ ./stopfanload.sh

/home/oracle/solutions/less08/x.sh: line 19: kill: (17917) - No such process

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Nov 15 05:11:55 2007

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

alter system kill session '96,12499';

alter system kill session '101,17760';

alter system kill session '102,13977';

alter system kill session '103,12859';

alter system kill session '106,4508';

alter system kill session '109,12588';

alter system kill session '111,13128';

alter system kill session '114,27665';

exit;

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Nov 15 05:11:55 2007

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

System altered.

alter system kill session '111,13128'

ERROR at line 1:

ORA-00030: User session ID does not exist.

alter system kill session '114,27665'

ERROR at line 1:

ORA-00030: User session ID does not exist.

…

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

Oracle Database 11g: RAC Administration A - 99 [oracle@vx0306 less08]$

13) Repeat steps 8-9-10-11-12 using the command “startfanload SLBA” in step 8. What do you observe this time? Wait for at least five minutes before doing your analysis.

This leaves enough time for the LBA to take place.

a) You should see that the listener distributes more connections to the second instance.

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

SQL> /

INST_ID COUNT(*) --- --- 1 4 2 15 SQL>

14) In TW1, look at the generated LBA FAN events that were sent during the second period while using the SLBA service. What do you observe?

a) You should see that percentages are different for both instances after you started using the SLBA workload.

[oracle@vx0306 less08]$ cat sol_08_02_14.sh

#!/bin/ksh

# sol_08_02_03.sh

# Must be executed on NODE1 !!!!!!!!!!

y=`cat /home/oracle/nodeinfo | sed -n '1,1p'`

z=`cat /home/oracle/nodeinfo | sed -n '2,2p'`

DBNAME=`ps -ef | grep dbw0_RDB | grep -v grep | grep -v callout1 | awk '{ print $8 }' | sed 's/1/''/' | sed 's/ora_dbw0_/''/'`

I1NAME=$DBNAME"1"

I2NAME=$DBNAME"2"

export ORACLE_SID=$I1NAME

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

$ORACLE_HOME/bin/sqlplus /NOLOG @sol_08_02_14.sql

[oracle@vx0306 less08]$

[oracle@vx0306 less08]$ cat sol_08_02_14.sql connect / as sysdba

Oracle Internal & Or acle Academy Use Only

Practice 8-2: Use Load Balancing Advisory (continued)

Oracle Database 11g: RAC Administration A - 101 [oracle@vx0306 less08]$

[oracle@vx0306 less08]$ ./sol_08_02_14.sh

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Nov 15 05:54:24 2007

Connected.