Oracle Database Commands and Practical Examples

Configure TAF Basic Configuration on Client-side

Configure TAF Basic Configuration on Client-side

As per below command FAILOVER_METHOD is none \

SQL> select failover_method from dba_services where name='rac';

FAILOVER_METHOD
----------------------------------------------------------------

Add value below on TNSNAMES.ORA file

ctaf =
(DESCRIPTION =(FAILOVER=ON) (LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.81)(PORT=1531))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.82)(PORT=1531))
(CONNECT_DATA =
(SERVICE_NAME = rac)
(FAILOVER_MODE = (TYPE=select)
(METHOD= basic )
(RETRIES=10)
(DELAY=10)))
)

C:\Users\varunyadav>sqlplus sys/system123@ctaf as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 5 18:00:58 2019

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

rac1

SQL> col username format a10

SQL> col service_name format a20

SQL> SELECT INST_ID, USERNAME, SERVICE_NAME, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER FROM GV$SESSION;

INST_ID USERNAME SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI

---------- ---------- -------------------- ------------- ---------- ---

2 SYS rac SELECT BASIC NO

BEFORE KILL SID

SQL> SELECT SYS_CONTEXT('USERENV','INSTANCE_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','INSTANCE_NAME')

--------------------------------------------------------------------------------

rac1

Example for testing
SQL>

SELECT A.OBJECT_NAME FROM ALL_OBJECTS A UNION ALL SELECT B.OBJECT_NAME FROM ALL_OBJECTS B;

In the admin window, crash rac1 instance.

pkill -9 -f ora_pmon_rac1

AFTER KILL SID

SQL> SELECT SYS_CONTEXT('USERENV','INSTANCE_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','INSTANCE_NAME')

--------------------------------------------------------------------------------

rac2

SQL>

SQL> col username format a10

SQL> col service_name format a20

SQL> SELECT INST_ID, USERNAME, SERVICE_NAME, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER FROM GV$SESSION;

INST_ID USERNAME SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI

---------- ---------- -------------------- ------------- ---------- ---

2 SYS rac SELECT BASIC YES

Configure server-side Load Balancing Advisory for connect-time load balancing

Testing server-side connect-time load balancing when LBA is not enabled

[oracle@srv1 ~]$ srvctl add service -database rac -service lbsrv -preferred rac1,rac2

[oracle@srv1 ~]$ srvctl start service -database rac -service lbsrv

[oracle@srv1 ~]$ srvctl config service -d rac -s lbsrv

Service name: lbsrv

Server pool:

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type:

Failover method:

TAF failover retries:

TAF failover delay:

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Pluggable database name:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Session State Consistency:

GSM Flags: 0

Service is enabled

Preferred instances: rac1,rac2

Available instances:

[oracle@srv1 ~]$

[root@srv1 ~]# lsnrctl service | grep lbsrv

Service "lbsrv" has 1 instance(s).

When the GOAL is NONE, it means the LBA is not enabled for the service.

SQL> col name format a20

SQL> SELECT NAME, GOAL, CLB_GOAL FROM DBA_SERVICES WHERE NAME='lbsrv';

NAME GOAL CLB_G

-------------------- ------------ -----

lbsrv NONE LONG

SQL>

SQL> SELECT INST_ID, COUNT(*) FROM GV$SESSION GROUP BY INST_ID ORDER BY INST_ID;

INST_ID COUNT(*)

---------- ----------

1 56

2 57

SQL>

TNSNAMES.ORA

lbsrv =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.91)(PORT = 1531))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = lbsrv)

)

Testing server-side connect-time load balancing when LBA is enabled

modify the lbrsrv service so that the LBA is enabled

[root@srv1 ~]# srvctl modify service -db rac -service lbsrv -rlbgoal service_time -clbgoal short

[root@srv1 ~]# crsctl status resource -t

ora.rac.db

1 ONLINE ONLINE srv1 Open,STABLE

2 ONLINE ONLINE srv2 Open,STABLE

ora.rac.lbsrv.svc

1 ONLINE ONLINE srv1 STABLE

2 ONLINE ONLINE srv2 STABLE

SQL> col name format a20

SQL> SELECT NAME, GOAL, CLB_GOAL FROM DBA_SERVICES WHERE NAME='lbsrv';

NAME GOAL CLB_G

-------------------- ------------ -----

lbsrv SERVICE_TIME SHORT

srvctl stop service -database rac -service lbsrv
srvctl remove service -database rac -service lbsrv

• To enable LBA in a service, set service-level goals for run-time connection load balancing (rlbgoal): SERVICE_TIME: work requests distributed based on response time

THROUGHPUT: work requests distributed based on throughput

• If rlbgoalis not configured (or set to NONE), the LBA is disabled

Connection Load Balancing and TAF

client-side connect-time load balancing

Add entry on tnsnames.ora file, here service name is RAC

SOESRV2=
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.81)(PORT=1531))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.82)(PORT=1531))
)
(CONNECT_DATA=(SERVICE_NAME=rac))
)

C:\app\client\varunyadav\product\12.1.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE=ON) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.81)(PORT=1531)) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.82)(PORT=1531))) (CONNECT_DATA=(SERVICE_NAME=rac)))

OK (20 msec)

C:\Users\varunyadav> sqlplus sys/system123@soesrv2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 5 17:17:34 2019

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','SERVICE_NAME')

--------------------------------------------------------------------------------

rac

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

rac2

By Default Load balancing is done on both server internally, If one database sid is stop due any cause of failure it will automatically move to another server

SQL>

SQL> SELECT INST_ID, COUNT(*) FROM GV$SESSION GROUP BY INST_ID ORDER BY INST_ID;

INST_ID COUNT(*)

---------- ----------

1 57

2 58

SQL>

Oracle Database Commands and Practical Examples

HOME

Thursday 6 June 2019

Configure TAF Basic Configuration on Client-side

Configure server-side Load Balancing Advisory for connect-time load balancing

Connection Load Balancing and TAF