What is Oracle RAC
Oracle RAC is a cluster technology which provide database high availability. It’s a hardware configuration where multiple interconnected servers appear as one to end users and applications, Oracle RAC is a complex topic, I will try to keep it simple and focus on OBIEE connection, query execution and High availability.
Query Execution in RAC Database
In this section, I will explain how Oracle RAC handle queries from OBIEE. If query is running in NOPARALLEL mode, it will only run on one of Oracle RAC instance. But If query is running in parallel mode, it can run 1 or multiple instances. It depend on database parameter setting called PARALLEL_FORCE_LOCAL (true or false).
With 11gr2 Oracle introduces a new feature called In-Memory Parallel Execution, which leverages shared memory cache (SGA) . Oracle uses the aggregated memory cache of all the servers in a Real Application Clusters (RAC) environment to deterministically cache objects distributed across the memory of all nodes. This ensures that subsequent parallel queries will read data out of cache from all nodes that can speed up processing times enormously instead of reading data from storage.
High Availability with Oracle RAC
It is best practice to use services to connect Oracle RAC databases. Services enable you to group database workloads and route work to the optimal instances that are assigned to offer the service. Services make the work load more manageable, measurable, tunable and recoverable. You services to connect one or more instances to for load balancing. In a nut shell Oracle RAC will distribute OBIEE connection evenly to multiple instances. you can configure services to run on multiple node simultaneously or one instance at time (failover to second instance if node fails).
Example: Creating Services
srvctl add service -s OBIEE_CONNECTION –a Node1 –r Node2 -d RACDB
srvctl add service -s OBIEE_CONNECTION –a Node1, Node2 -d RACDB
Example: Connection String