MASTER NODE IN RAC 11G R2

In this post, I will demonstrate three methods to identify the oracle clusterware’s master node. Pls note that clusterware master is different from Resource master in oracle database instance. To know about how to find the resource master,

Importance of master node in a cluster:
- Master node has the least Node-id in the cluster. Node-ids are  assigned to the nodes in the same order as the nodes join the cluster. Hence, normally the node which joins the cluster first is the master node.

- CRSd process on the Master node is responsible to initiate the OCR backup as per the backup policy

- Master node  is also responsible to sync OCR cache across the nodes

- CRSd process oth the master node reads from and writes to OCR on disk

- In case of node eviction, The cluster is divided into two sub-clusters. The sub-cluster containing fewer no. of nodes is evicetd. But, in case both the sub-clusters have same no. of nodes, the sub-cluster having the master node survives whereas the other sub-cluster is evicted.

Oracle ClusterWare master’s  information can be found 

- by scanning ocssd logs from various nodes

- by scanning  crsd logs from various nodes. 

- by identifying the node which  takes the backup of the OCR.

If master node gets evicted/rebooted, another node becomes the master.

I have a 3 node setup. I check the ocssd logs on the 3 nodes for the string ‘master node’ and note that node 3 is the master node.

[grid@host01 root]$ cat $ORACLE_HOME/log/host01/cssd/ocssd.log |grep ‘master node’ |tail -1

2012-11-23 10:14:36.949: [    CSSD][2778696592]clssgmCMReconfig: reconfiguration successful, incarnation 248954981 with 3 nodes, local node number 1, master node number 3

[root@host02 cssd]# cat $ORACLE_HOME/log/host02/cssd/ocssd.log |grep ‘master node’ |tail -1

2012-11-23 10:14:36.953: [    CSSD][778696592]clssgmCMReconfig: reconfiguration successful, incarnation 248954981 with 3 nodes, local node number 2, master node number 3

[root@host03 ~]# cat $ORACLE_HOME/log/host03/cssd/ocssd.log |grep ‘master node’ |tail -1

2012-11-23 10:14:37.001: [    CSSD][778700688]clssgmCMReconfig: reconfiguration successful, incarnation 248954981 with 3 nodes, local node number 3, master node number 3

If I take the OCR backup right now, it will be taken by node3 (master node).

 [root@host02 cssd]# ocrconfig -manualbackup

host03     2012/11/24 09:54:48    /u01/app/11.2.0/grid/cdata/cluster01/backup_20121124_095448.ocr

Let us check crsd logs of various nodes, looking for the string OCR MASTER.Note that node3 is the master node presently.

cat /u01/app/11.2.0/grid/log/host01/crsd.log| grep -i 'ocr master'

[grid@host01 crsd]$ cat /u01/app/11.2.0/grid/log/host01/crsd/crsd.log |grep ‘OCR MASTER’ | tail -1

2012-11-23 10:15:01.403: [  OCRMAS][2877356944]th_master: NEW OCR MASTER IS 3

[root@host02 crsd]# cat /u01/app/11.2.0/grid/log/host02/crsd/crsd.log |grep ‘OCR MASTER’ | tail -1

2012-11-23 10:15:03.561: [  OCRMAS][876976016]th_master: NEW OCR MASTER IS 3

[root@host03 crsd]#  cat /u01/app/11.2.0/grid/log/host03/crsd/crsd.log |grep ‘OCR MASTER’ | tail -3

2012-11-23 10:11:18.499: [  OCRMAS][877467536]th_master:13: I AM THE NEW OCR MASTER at incar 44. Node Number 3

Let me reboot node3 and check which node is assigned the mastership now.

[root@host03 ~]# init 6

check the ocssd logs on the remaining two nodes (node1 and node2) for the string ‘master node’ and note that node 1 is the master node.

[grid@host01 root]$ cat $ORACLE_HOME/log/host01/cssd/ocssd.log |grep ‘master node’ |tail -1

2012-11-24 10:09:23.522: [    CSSD][2778696592]clssgmCMReconfig: reconfiguration successful, incarnation 248954982 with 2 nodes, local node number 1, master node number 1

[root@host02 cssd]# cat $ORACLE_HOME/log/host02/cssd/ocssd.log |grep ‘master node’ |tail -1

2012-11-24 10:09:23.502: [    CSSD][778696592]clssgmCMReconfig: reconfiguration successful, incarnation 248954982 with 2 nodes, local node number 2, master node number 1

As can be seen from ocssd logs of the remaining two nodes, node1 has become the master now.

Now If I take the OCR backup, it is taken by node1 while earlier backups were taken by node3 which was the then master.

[root@host02 cssd]# ocrconfig -manualbackup

host01     2012/11/24 10:12:29    /u01/app/11.2.0/grid/cdata/cluster01/backup_20121124_101229.ocr

host03     2012/11/24 09:54:48    /u01/app/11.2.0/grid/cdata/cluster01/backup_20121124_095448.ocr

Let us check crsd logs of various nodes, looking for the string OCR MASTER

[grid@host01 crsd]$ cat /u01/app/11.2.0/grid/log/host01/crsd/crsd.log |grep ‘OCR MASTER’ | tail -1

2012-11-24 10:08:45.884: [  OCRMAS][877356944]th_master:13: I AM THE NEW OCR MASTER at incar 47. Node Number 1

[root@host02 crsd]# cat /u01/app/11.2.0/grid/log/host02/crsd/crsd.log |grep ‘OCR MASTER’ | tail -1

2012-11-24 10:08:45.364: [  OCRMAS][876976016]th_master: NEW OCR MASTER IS 1

[root@host03 crsd]#  cat /u01/app/11.2.0/grid/log/host03/crsd/crsd.log |grep ‘OCR MASTER’ | tail -1

2012-11-24 10:12:20.282: [  OCRMAS][877422480]th_master: NEW OCR MASTER IS 1

2^nd

$ocrconfig –showbackup

3^rd

select *from gv$gcs_resource;

How to set Compatible Parameter in Oracle ?

backward compatibility with an earlier release. This is helpful if it becomes necessary to revert to the earlier release.

This parameter specifies the release with which Oracle must maintain compatibility. It allows you to take advantage of the maintenance improvements of a new release immediately in your production systems without testing the new functionality in your environment. Some features of the release may be restricted.

When using a standby database, this parameter must have the same value on both the primary and standby databases.

RAC Multiple instances must have the same value.

like Ora-00600 else some other errors.

--How this is possible a bug which is fixed in lower version still exists in higher version ?

The reason was very simple, the compatible parameter of database was set to 11.2.0.2, That is the reason online dba was still seeing this bug.

Command to check compatible parameter

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

Steps to change compatible parameter of database

After the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for Oracle Database.

1. Change parameter value

 SQL> ALTER SYSTEM SET COMPATIBLE = '11.0.0' SCOPE=SPFILE; 

2. Shutdown database

SQL> SHUTDOWN IMMEDIATE 

3. Start database

SQL> Startup

4. Cross check for parameter for new value

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

Note: Please take your database backup before changing this parameter, because after changing this parameter database will become incompatible with earlier releases of Oracle Database.

Oracle Database Release DefaultValue MinimumValue MaximumValue

Oracle8i release 8.1.7          8.0.0 8.0.0.0.0                  8.1.7.x.x

Oracle9i release 9.0.1          8.1.0 8.1.0.0.0                  9.0.1.x.x

Oracle9i release 9.2                  8.1.0 8.1.0.0.0                  9.2.0.x.x

Oracle Database 10g release 10.1          10.0.0 9.2.0.0.0                  10.1.0.x.x

Oracle Database 10g release 10.2          10.2.0 9.2.0.0.0                  10.2.0.x.x

Oracle 11g Release 11.1          11.0.0 10.0.0.0.0          11.1.0.x.x

Oracle 11g Release 11.2          11.0.0 10.0.0.0.0          11.2.0.x.x

SQL FLOW

----Oracle Transaction.


1. User requests a connection to oracle

2. A new dedicated server process is started for the user

3. User executes a statement to insert data in to a table

4. Oracle checks the users privileges, it first checks the library cache (cache hit) for the information and if not found retrieve it from disk.

5. Check to see if the SQL statement has been parsed before (library cache) if it has then this is called a soft parse, otherwise the code has to be compiled a hard parse.

6. Oracle creates a private SQL area in the users session's PGA

7. Oracle checks to see if the data is in the buffer cache, otherwise perform a read from the data file

8. Oracle will then apply row-level locks where needed to prevent others changing the row (select statements are still allowed on the row)

9. Oracle then writes the change vectors to the redo log buffer

10. Oracle then modifies the row in the data buffer cache

11. The user commits the transaction making it permanent, the row-level locks are released

12. The log writer process immediately writes out the changed data in the redo log buffers to the online redo log files, in other words the change is now recoverable.

13. Oracle informs the user process that the transaction was completed successfully

14. It may be sometime before the data buffer cache writes out the change to the data files.

Note: if the users transaction was an update then the before update row would have been written to the undo buffer cache, this would be used if the user rollsback the change of if another user run's a select on that data before the new update was committed.

TAF / FAN / FCF/ ONS ORACLE RAC

What the differences and relationship among TAF/FAN/FCF/ONS?

1 Definition

1) TAF
a feature of Oracle Net Services for OCI8 clients. TAF is transparent application failover which will move a session to a backup connection if the session fails. With Oracle 10g Release 2, you can define the TAF policy on the service using dbms_service package. It will only work with OCI clients. It will only move the session and if the parameter is set, it will failover the select statement. For insert, update or delete transactions, the application must be TAF aware and roll back the transaction. YES, you should enable FCF on your OCI client when you use TAF, it will make the failover faster.

Note: TAF will not work with JDBC thin.

2) FAN
FAN is a feature of Oracle RAC which stands for Fast Application Notification. This allows the database to notify the client of any change (Node up/down, instance up/down, database up/down). For integrated clients, inflight transactions are interrupted and an error message is returned. Inactive connections are terminated. 
FCF is the client feature for Oracle Clients that have integrated with FAN to provide fast failover for connections. Oracle JDBC Implicit Connection Cache, Oracle Data Provider for .NET (ODP.NET) and Oracle Call Interface are all integrated clients which provide the Fast Connection Failover feature.

3) FCF
FCF is a feature of Oracle clients that are integrated to receive FAN events and abort inflight transactions, clean up connections when a down event is received as well as create new connections when a up event is received. Tomcat or JBOSS can take advantage of FCF if the Oracle connection pool is used underneath. This can be either UCP (Universal Connection Pool for JAVA) or ICC (JDBC Implicit Connection Cache). UCP is recommended as ICC will be deprecated in a future release. 

4) ONS

http://forums.oracle.com/forums/thread.jspa?messageID=3566976

ONS is part of the clusterware and is used to propagate messages both between nodes and to application-tiers

ONS is the foundation for FAN upon which is built FCF.

RAC uses FAN to publish configuration changes and LBA events. Applications can react as those published events in two way :
- by using ONS api (you need to program it)
- by using FCF (automatic by using JDBC implicit connection cache on the application server)

you can also respond to FAN event by using server-side callout but this on the server side (as their name suggests it)

Rodrigo Mufalani

"ONS send/receive messages about failures automatically. It is a daemon process that runs on each node notifying status from components of database, nodeapps.

If listener process fails on node1 his failure is notified by EVMD, then local ONS communicates the failure to remote ONS in remote nodes, then local ONS on these nodes notifying all aplications about failure that occurred on node1."

2 Relationship
ONS --> FAN --> FCF
ONS -> send/receive messages on local and remote nodes.
FAN -> uses ONS to notify other processes about changes in configuration of service level
FCF -> uses FAN information working with conection pools JAVA and others.
http://forums.oracle.com/forums/thread.jspa?messageID=3566976

3 To use TAF/FAN/FCF/ONS, do you need to configure/install in server or client side?

4 Does ONS automatically send messages ? 
or is there any settings to be done ?
Does ONS only broadcast msgs ?

http://forums.oracle.com/forums/thread.jspa?messageID=3566976

ONS is part of the clusterware and is used to propagate messages both between nodes and to application-tiers

ONS is the foundation for FAN upon which is built FCF.

RAC uses FAN to publish configuration changes and LBA events. Applications can react as those published events in two way :
- by using ONS api (you need to program it)
- by using FCF (automatic by using JDBC implicit connection cache on the application server)

you can also respond to FAN event by using server-side callout but this on the server side (as their name suggests it)

Rodrigo Mufalani

"ONS send/receive messages about failures automatically. It is a daemon process that runs on each node notifying status from components of database, nodeapps.If listener process fails on node1 his failure is notified by EVMD, then local ONS communicates the failure to remote ONS in remote nodes, then local ONS on these nodes notifying all aplications about failure that occurred on node1."

5 Are TAF and FAN mutually exclusive? or if TAF and FCF are mutually exclusive?

No. You can use both TAF and FAN at the same time, or both TAF and FCF, it depends on what you want to achieve with it. 

6 TAF Basic Configuration with FAN: Example

Oracle Database 10g Release 2 supports server-side TAF with FAN. 

To use server-side TAF:

1) create and start your service using SRVCTL

$ srvctl add service -d RACDB -s AP -r I1,I2

$ srvctl start service -d RACDB -s AP

2) configure TAF in the RDBMS by using the DBMS_SERVICE package.

execute dbms_service.modify_service ( ,-

service_name => 'AP' ,-

aq_ha_notifications => true ,-

failover_method => dbms_service.failover_method_basic ,-

failover_type => dbms_service.failover_type_session ,-

failover_retries => 180, failover_delay => 5 ,-

clb_goal => dbms_service.clb_goal_long);

3) When done, make sure that you define a TNS entry 

for it in your tnsnames.ora file. 

AP =

(DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=ON)

(ADDRESS=(PROTOCOL=TCP)(HOST=N1VIP)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=N2VIP)(PORT=1521))

(CONNECT_DATA = (SERVICE_NAME = AP)))

Note that this TNS name does not need to specify TAF parameters 

as with the previous slide.

7 TAF Basic Configuration without FAN: Example

1) 

Before using TAF, it is recommended that you create and start a service that is used during 

connections. 

By doing so, you benefit from the integration of TAF and services. When you want to 

use BASIC TAF with a service, you should have the -P BASIC option when creating the service.

After the service is created, you simply start it on your database.

$ srvctl add service -d RACDB -s AP -r I1,I2  -P BASIC

$ srvctl start service -d RACDB -s AP

2) 

Then, your application needs to connect to the service by using a connection descriptor similar to the 

one shown in the slide. The FAILOVER_MODE parameter must be included in the CONNECT_DATA 

section of your connection descriptor.

AP =

(DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=ON)

(ADDRESS=(PROTOCOL=TCP)(HOST=N1VIP)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=N2VIP)(PORT=1521))

(CONNECT_DATA =

(SERVICE_NAME = AP)

(FAILOVER_MODE =

(TYPE=SESSION)

(METHOD=BASIC)

(RETRIES=180)

(DELAY=5))))

Note: If using TAF, do not set the GLOBAL_DBNAME parameter in your listener.ora file.

8 Metalink notes

• --Understanding Transparent Application Failover (TAF) and Fast Connection Failover (FCF) [ID 334471.1]

• --How To Verify And Test Fast Connection Failover (FCF) Setup From a JDBC Thin Client Against a 10.2.x RAC Cluster [ID 433827.1]

• --Fast Connection Failover (FCF) Test Client Using 11g JDBC Driver and 11g RAC Cluster [ID 566573.1]

• --Questions about how ONS and FCF work with JDBC [ID 752595.1]

• --Configuring ONS For Fast Connection Failover

• --How To Implement (Fast Connection Failover) FCF Using JDBC driver ? [ID 414199.1]

• --How to Implement Load Balancing With RAC Configured System Using JDBC [ID 247135.1]

Duplicate Record Removes

----- Total number of duplicate records.

select A.vehregno,count(A.vehregno)
from scanlog A
where exists ( select *
               from  scanlog B
                where A.rowid <> B.rowid and
                       B.VEHREGNO = A.VEHREGNO and
                      B.SCANDATE = A.SCANDATE and
B.FILENAME=A.FILENAME )
group by A.VEHREGNO having count(A.VEHREGNO) > 1 order by count(A.VEHREGNO);



----- Total number of records after eliminating Duplicate record.

SELECT count(1)
     FROM scanlog A
    WHERE ROWID IN (SELECT rid
                      FROM (SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY vehregno, scandate, filename ORDER BY ROWID) rn
                              FROM scanlog)
                     WHERE rn=1) order by vehregno;



----- Create Table After Eliminating the duplicate record.

CREATE  TABLE scanlog NOLOGGING
   AS
   SELECT A.*
     FROM scanlog A
    WHERE ROWID IN (SELECT rid
                      FROM (SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY vehregno, scandate, filename ORDER BY ROWID) rn
                              FROM scanlog)
                     WHERE rn=1);



----- Find Duplicate Record.
 select *   from
    (select d.* , count(*) over(partition by binwardno) cnt
     from biolic d  )
    where cnt > 1;