Oracle RAC load balancing and failover

LOAD BALANCING: The Oracle RAC system can distribute the load over many nodes this feature called as load balancing.

There are two methods of load balancing
1.Client load balancing
2.Server load balancing

Client Load Balancing distributes new connections among Oracle RAC nodes so that no one server is overloaded with connection requests and it is configured at net service name level by providing multiple descriptions in a description list or multiple addresses in an address list. For example, if connection fails over to another node in case of failure, the client load balancing ensures that the redirected connections are distributed among the other nodes in the RAC.

Configure Client-side connect-time load balancing by setting LOAD_BALANCE=ON in the corresponding client side TNS entry.

TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = testdb.oracleracexpert.com))
)

Server Load Balancing distributes processing workload among Oracle RAC nodes. It divides the connection load evenly between all available listeners and distributes new user session connection requests to the least loaded listener(s) based on the total number of sessions which are already connected. Each listener communicates with the other listener(s) via each database instance’s PMON process.

Configure Server-side connect-time load balancing feature by setting REMOTE_LISTENERS initialization parameter of each instance to a TNS name that describes list of all available listeners.

TESTRAC_LISTENERS =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1)(PORT = 1521)))
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2)(PORT = 1521))))
)

Set *.remote_listener= TESTRAC_LISTENERS’ initialization parameter in the database’s shared SPFILE and add TESTRAC_LISTENERS’ entry to the TNSNAMES.ORA file in the Oracle Home of each node in the cluster.

Once you configure Server-side connect-time load balancing, each database’s PMON process will automatically register the database with the database’s local listener as well as cross-register the database with the listeners on all other nodes in the cluster. Now the nodes themselves decide which node is least busy, and then will connect the client to that node.

FAILOVER:

The Oracle RAC system can protect against failures caused by O/S or server crashes or hardware failures. When a node failure occurs in RAC system, the connection attempts can fail over to other surviving nodes in the cluster this feature called as Failover.

There are two methods of failover
1. Connection Failover
2. Transparent Application Failover (TAF)

Connection Failover - If a connection failure occurs at connect time, the application failover the connection to another active node in the cluster. This feature enables client to connect to another listener if the initial connection to the first listener fails.

Enable client-side connect-time Failover by setting FAILOVER=ON in the corresponding client side TNS entry.

TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = testdb.oracleracexpert.com))
)

If LOAD_BALANCE is set to on then clients randomly attempt connections to any nodes. If client made connection attempt to a down node, the client needs to wait until it receives the information that the node is not accessible before trying alternate address in ADDRESS_LIST.

Transparent application Failover (TAF) – If connection failure occurs after a connection is established, the connection fails over to other surviving nodes. Any uncommitted transactions are rolled back and server side program variables and session properties will be lost. In some case the select statements automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the failover.

TESTRAC =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = testdb.oracleracexpert.com)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))
)
)

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Delete duplicate rows from Oracle Table:

There are several techniques to delete duplicate rows from a table, but the most effective way is to join the table against itself. Always make sure to select the duplicate data before you delete using below queries.

1. Delete duplicate rows based on one column value using ROWID

SQL> delete from dup_table t1 where rowid > ( select min(rowid) from dup_table t2 where t1.ename = t2.ename);

-- or --
SQL> delete from dup_table t1 where rowid < ( select max(rowid) from dup_table t2 where t1.ename = t2.ename);

2. Use the below query to delete the rows suing Oracle analytic functions

SQL> Delete from dup_table

where rowid in ( select rowid from

( select rowid , row_number() over (partition by col1 order by upper col1 ) row_num from dup_table ) where rno > 1 );

3. You must specify all columns that make the row duplicate in the query, use the below query to delete duplicate records based on two columns or composite unique key

SQL> delete from dup_table t1

where rowid > (select min(rowid) from dup_table t2

where upper(t2.col1) = upper(t1.col1)

and upper(t2.col2) = upper(t1.col2)

);

-- or --

SQL> delete from dup_table t1

where rowid < (select max(rowid) FROM dup_table t2

where t1.col1=t2.col1 AND t1.col2=t2.col2 );

-- or --

SQL> delete from dup_table t1

where rowid <> ( select max(rowid) from dup_table t2

where t2.col1 = t1.col1

and t2.col2 = t1.col2 )

4. If the fields match on the NULL value then duplicate fails to remove the duplicate rows. In this situation add a null check

SQL> delete from dup_table t1
where t1.rowid > ANY (select t2.rowid FROM dup_table t2
where (t1.col1 = t2.col1 OR (t1.col1 is null AND t2.col1 is null))
and
(t1.col2 = t2.col2 OR (t1.col2 is null AND t2.col2 is null))
);

If the table contains duplicate data in upper case and lower case, use below query to delete to delete the data

SQL> delete from dup_table

where rowid in ( select rid from ( select rowid rid, row_number() over (partition by upper(col1) order by upper(col2)) rno from dup_table )

where rno > 1

);

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com