Part 19 covered the RAC interconnect and showed why network health alone isn’t enough. We also saw an important performance principle:
Where an application runs inside RAC can directly affect Cache Fusion traffic.
That brings us to RAC Services.
Services are one of the most powerful mechanisms for controlling how applications use a RAC database.
A well-designed RAC environment doesn’t simply distribute sessions randomly across nodes.
It deliberately controls:
Application
↓
Service
↓
RAC Instance
↓
Workload
↓
Data locality
↓
Cache Fusion
1. What Is a RAC Service?
A RAC service is a logical database workload entity.
For example:
SALES_APP REPORTING BATCH ETL ERP PAYMENTS
Instead of applications connecting directly to:
rac1 rac2
they connect through a service:
salesdb.wadhahdaouehi.tn |
↓
SALES_APP
|
+----+----+
| |
RAC1 RAC2
The service determines where that workload can run.
2. Why Services Matter
Imagine:
RAC1 ├── SALES └── PAYMENTS RAC2 ├── REPORTING └── BATCH
Now the workloads are separated.
Compare this with:
RAC1 ├── SALES ├── PAYMENTS ├── REPORTING └── BATCH RAC2 ├── SALES ├── PAYMENTS ├── REPORTING └── BATCH
The second design may provide excellent distribution, but it can also create more cross-instance data access.
The first design can improve workload locality when the data access patterns support it.
3. Services Are More Than Connection Strings
A common mistake is to think:
“A service is just another database name.”
No.
A service can influence:
- Connection routing
- Load balancing
- Failover
- Application behavior
- Workload isolation
- Resource management
- Application Continuity
- RAC availability
Services are an important architecture layer.
4. Preferred and Available Instances
Suppose:
SALES_APP
has:
Preferred: RAC1 Available: RAC2
Conceptually:
SALES_APP
|
+------+------+
| |
Preferred Available
| |
RAC1 RAC2
Normal workload:
SALES_APP → RAC1
If RAC1 becomes unavailable:
RAC1 X | ↓ SALES_APP | ↓ RAC2
The service can relocate/fail over according to its configuration.
5. Why Not Put Everything Everywhere?
You could create:
SALES_APP Preferred: RAC1,RAC2
and allow both nodes to process everything.
But ask:
Does the workload benefit from being distributed?
If both instances constantly modify the same blocks:
RAC1 ↔ RAC2
|
Cache Fusion
|
gc current request
you may create unnecessary global cache traffic.
6. The Principle of Workload Locality
Think about this:
Application
↓
Service
↓
Instance
↓
Data
If those four layers align well:
SALES_APP
↓
RAC1
↓
SALES tables
you can potentially reduce cross-instance block transfers.
This is called workload locality.
7. But Don’t Over-Isolate
There is another mistake:
“I’ll put every application on a different node.”
For example:
RAC1 → SALES RAC2 → PAYMENTS RAC3 → REPORTING RAC4 → BATCH
This can create poor resource utilization.
If SALES consumes 90% CPU:
RAC1 → 90% CPU RAC2 → 15% RAC3 → 20% RAC4 → 10%
You haven’t necessarily designed a balanced RAC system.
So the goal is:
Balance workload while preserving locality where it matters.
8. Check Existing Services
Start with:
srvctl status service -db PROD
You might see:
Service SALES_APP is running on instance(s) RAC1 Service REPORTING is running on instance(s) RAC2 Service BATCH is running on instance(s) RAC2
Then:
srvctl config service -db PROD
This is extremely important.
It shows the configured service properties.
9. Service Configuration
A more targeted command:
srvctl config service -db PROD -service SALES_APP
Depending on the exact Grid Infrastructure release, the output can include properties related to:
- Preferred instances
- Available instances
- Service role
- Management policy
- Connection load balancing
- Failover
- Notification settings
- Edition
- PDB association
Always check the syntax available in your installed 19c version with:
srvctl -help
10. Creating a Service
For example:
srvctl add service \ -db PROD \ -service SALES_APP \ -preferred RAC1 \ -available RAC2
Then:
srvctl start service \ -db PROD \ -service SALES_APP
Verify:
srvctl status service \ -db PROD \ -service SALES_APP
11. Important Production Warning
Don’t run:
srvctl add service ...
against production without first checking whether the service already exists.
Use:
srvctl config service -db PROD
first.
And before modifying an existing service:
srvctl config service \ -db PROD \ -service SALES_APP
Document the current configuration.
12. Service Design Example
Suppose you have:
RAC1 RAC2
and four workloads:
SALES ERP REPORTING BATCH
A possible design:
PROD
|
+---------+---------+
| |
RAC1 RAC2
| |
+----+----+ +----+----+
| | | |
SALES ERP REPORTING BATCH
But perhaps REPORTING also needs access to SALES data.
Then you need to consider whether:
REPORTING → RAC2 SALES → RAC1
creates excessive cross-instance block access.
This is where AWR/ASH becomes important.
13. Don’t Design Services by Guessing
The correct process is:
Current workload
↓
Measure
↓
Identify SQL
↓
Identify objects
↓
Identify instances
↓
Identify GC waits
↓
Design service placement
↓
Measure again
This is much better than:
“Let’s move SALES to RAC1 because RAC1 has fewer sessions.”
Session count alone is not enough.
14. Session Count Is a Bad Load Metric
Imagine:
RAC1 → 500 sessions RAC2 → 100 sessions
You might think:
RAC1 is overloaded.
But:
RAC1 500 mostly idle sessions RAC2 100 CPU-intensive sessions
RAC2 could be much busier.
Look at:
- DB CPU
- DB Time
- Active sessions
- I/O
- GC waits
- Service-level workload
not simply connection count.
15. Service-Level Monitoring
ASH can help:
SELECT service_name,
instance_number,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 10:00:00'
AND TIMESTAMP '2026-08-08 11:00:00'
GROUP BY service_name,
instance_number
ORDER BY samples DESC;
This gives you an idea of where active workload is occurring.
16. Find Top SQL by Service
SELECT service_name,
sql_id,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 10:00:00'
AND TIMESTAMP '2026-08-08 11:00:00'
AND sql_id IS NOT NULL
GROUP BY service_name,
sql_id
ORDER BY samples DESC
FETCH FIRST 50 ROWS ONLY;
Now you can say:
SALES_APP ↓ SQL_ID 8a12abc ↓ High DB activity
17. Service + GC Waits
This is particularly useful for RAC:
SELECT service_name,
instance_number,
event,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 10:00:00'
AND TIMESTAMP '2026-08-08 11:00:00'
AND event LIKE 'gc %'
GROUP BY service_name,
instance_number,
event
ORDER BY samples DESC;
Example:
SERVICE INSTANCE EVENT ----------- -------- ----------------------- SALES_APP 2 gc current request SALES_APP 2 gc current block busy BATCH 1 gc current request
Now you have a direct relationship between:
Service ↓ Instance ↓ GC activity
18. Connection Load Balancing
Oracle RAC can distribute new connections intelligently.
This is known as:
Connection Load Balancing
The goal is not necessarily:
50% / 50%
Instead, Oracle can consider instance workload.
For example:
RAC1 CPU = 20% RAC2 CPU = 80%
New connections can favor RAC1.
19. Client-Side vs Server-Side Load Balancing
There are multiple mechanisms involved.
At a high level:
Client | ↓ SCAN | ↓ Listener | ↓ Service | ↓ Instance
Oracle RAC uses listener and service information to help direct connections.
20. CLB_GOAL
One important service attribute is:
CLB_GOAL
Connection Load Balancing Goal.
Typical concepts include:
SHORT LONG
The appropriate setting depends on the workload.
For example:
- Short transactions may benefit from connection-aware load balancing.
- Long-running sessions may need different considerations.
Don’t blindly choose a value because it appears in another RAC environment.
21. Runtime Load Balancing
RAC can also provide runtime workload information to applications.
This is related to:
RLB_GOAL
Runtime Load Balancing Goal.
Conceptually:
RAC | +-- RAC1 → 20% workload | +-- RAC2 → 80% workload | ↓ Application receives workload advice
A connection pool can then use this information when selecting an instance.
22. FAN
FAN means:
Fast Application Notification
The basic idea:
RAC1 X | ↓ FAN event | ↓ Application / Connection Pool | ↓ Stop sending work to RAC1
Without appropriate event handling, an application may continue trying to use a failed instance.
With FAN-aware infrastructure, applications can react much faster.
23. FAN Architecture
Conceptually:
RAC
|
+--------+--------+
| |
RAC1 RAC2
|
Failure
|
↓
FAN
|
↓
Connection Pool
|
↓
Stop using RAC1
This becomes especially important in large enterprise applications.
24. Application Continuity
Now we move into a more advanced feature.
Suppose:
Application
|
↓
RAC1
|
X
The application may lose its connection.
Application Continuity can allow supported applications to recover certain in-flight requests by replaying them under appropriate conditions.
Conceptually:
Application
|
↓
RAC1
X
|
↓
RAC2
|
↓
Replay / continue
This can dramatically improve application availability.
But it requires:
- Compatible application architecture
- Appropriate Oracle configuration
- Correct connection pool support
- Careful transaction semantics
25. Transparent Application Continuity
Transparent Application Continuity, or TAC, builds on the same general objective:
Make applications more resilient to database service failures without requiring extensive application changes.
However, you should not treat TAC as:
“Oracle automatically fixes every failed transaction.”
There are restrictions around:
- Non-replayable operations
- External side effects
- Transaction semantics
- Application behavior
This needs to be tested with the actual application.
26. Service Failover
Suppose:
SALES_APP Preferred: RAC1 Available: RAC2
If RAC1 fails:
Before: SALES_APP → RAC1
After:
RAC1 X SALES_APP → RAC2
The service is therefore an important unit of RAC availability.
27. Planned Maintenance
Services also help with maintenance.
Suppose you need to patch RAC1.
You can move:
SALES_APP
to RAC2 before maintenance.
Conceptually:
Before: SALES_APP → RAC1 Maintenance After: SALES_APP → RAC2
This can reduce application disruption when the application architecture supports it.
28. Service Relocation
You can relocate a service with:
srvctl relocate service \ -db PROD \ -service SALES_APP \ -oldinst RAC1 \ -newinst RAC2
Before doing this in production:
srvctl config service \ -db PROD \ -service SALES_APP
and confirm the service topology and failover behavior.
29. Checking Service Status
Use:
srvctl status service \ -db PROD \ -service SALES_APP \ -verbose
This can provide more detailed runtime information depending on your 19c version.
30. Service Management Policy
A service can be configured with management behavior such as:
AUTOMATIC MANUAL
The idea is straightforward.
AUTOMATIC
Clusterware manages service startup according to configuration.
MANUAL
You have more explicit control over when the service is started.
For production application services, automatic management is commonly appropriate, but the correct choice depends on operational requirements.
31. Services and Database Roles
Now consider Data Guard.
You might have:
PRIMARY | ↓ Standby
Services can be configured according to database role.
For example:
SALES_APP Role = PRIMARY
The service should normally run only when the database is primary.
After failover:
Old Primary
↓
Standby
↓
Becomes Primary
↓
SALES_APP starts
This is an important concept for RAC + Data Guard architectures.
32. Role-Based Services
You can inspect service configuration with:
srvctl config service \ -db PROD \ -service SALES_APP
Look for role-related configuration.
Typical roles include:
PRIMARY PHYSICAL_STANDBY LOGICAL_STANDBY SNAPSHOT_STANDBY
The exact supported options depend on the configuration.
33. RAC + Data Guard Architecture
A mature Oracle architecture can look like:
Production
+-------------+
| RAC PRIMARY |
| |
| RAC1 RAC2 |
+------+------+
|
Data Guard
|
↓
+-------------+
| RAC STANDBY |
| |
| RAC1 RAC2 |
+-------------+
Services then provide the application abstraction:
Application
|
↓
SERVICE
|
↓
Current Primary
This is much cleaner than hard-coding individual database instances.
34. Resource Manager + Services
Now we can combine services with another powerful Oracle feature:
Database Resource Manager
Suppose:
SALES_APP REPORTING BATCH
all run on the same database.
You can use Resource Manager to control resource consumption.
Conceptually:
Database | Resource Manager | +-- SALES → high priority +-- REPORTING → medium +-- BATCH → controlled
This is extremely useful in mixed OLTP/reporting environments.
35. Services + Resource Manager
This gives us:
Application
|
Service
|
Resource Manager
|
↓
CPU / Parallelism / I/O management
This is much more powerful than simply distributing sessions across RAC nodes.
36. Example Production Architecture
Imagine an insurance database:
PROD | +-- POLICY_APP | +-- CLAIMS_APP | +-- REPORTING | +-- ETL
Possible service topology:
RAC1 RAC2 POLICY_APP CLAIMS_APP CLAIMS_APP POLICY_APP REPORTING REPORTING ETL ETL
This is only an example.
The actual placement should be driven by:
- CPU
- I/O
- data access
- GC waits
- application behavior
- availability requirements
37. Service Placement and Cache Fusion
Let’s connect this to Part 18.
Suppose:
POLICY_APP → RAC1
and most policy data is accessed by that workload.
Then:
POLICY_APP
↓
RAC1
↓
Local blocks
Potentially:
Less cross-instance traffic
But if CLAIMS_APP constantly updates the same blocks:
RAC2 | ↓ Same POLICY blocks | ↓ Cache Fusion
then service separation alone isn’t enough.
You still need to investigate the SQL and data model.
38. Service Placement Is Not a Magic Fix
This is critical.
If you see:
gc current request
and move a service from RAC1 to RAC2 without understanding the workload, you may simply move the problem.
Always measure before and after.
39. Before/After Measurement
Before changing service placement:
SELECT instance_number,
service_name,
event,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
GROUP BY instance_number,
service_name,
event
ORDER BY samples DESC;
After the change, run the same analysis over a comparable workload period.
You want to know:
GC waits ↓ ? DB time ↓ ? CPU balanced ? Application latency ↓ ?
40. Connection Distribution
Check current sessions:
SELECT inst_id,
service_name,
COUNT(*) sessions
FROM gv$session
WHERE username IS NOT NULL
GROUP BY inst_id, service_name
ORDER BY service_name, inst_id;
Example:
SERVICE INST SESSIONS ------------ ---- -------- SALES_APP 1 220 SALES_APP 2 12 REPORTING 1 5 REPORTING 2 80
This tells you where connections currently live.
But remember:
Session count is not the same as workload.
41. Active Sessions by Service
A better measure:
SELECT inst_id,
service_name,
COUNT(*) active_sessions
FROM gv$session
WHERE status = 'ACTIVE'
GROUP BY inst_id, service_name
ORDER BY active_sessions DESC;
Now you see active workload rather than simply connections.
42. DB Time by Service
For historical analysis, ASH can approximate workload by service:
SELECT service_name,
instance_number,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
GROUP BY service_name,
instance_number
ORDER BY ash_samples DESC;
ASH samples are a powerful way to compare service workload.
43. Service + SQL + Instance
One of my favorite RAC diagnostic queries:
SELECT service_name,
instance_number,
sql_id,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
AND sql_id IS NOT NULL
GROUP BY service_name,
instance_number,
sql_id
ORDER BY samples DESC
FETCH FIRST 50 ROWS ONLY;
This answers:
Which SQL is consuming workload, through which service, on which RAC instance?
44. Service + GC + Object
Now combine everything:
SELECT ash.service_name,
ash.instance_number,
ash.sql_id,
ash.event,
o.owner,
o.object_name,
COUNT(*) samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects o
ON o.object_id = ash.current_obj#
WHERE ash.sample_time BETWEEN
:start_time AND :end_time
AND ash.event LIKE 'gc %'
GROUP BY ash.service_name,
ash.instance_number,
ash.sql_id,
ash.event,
o.owner,
o.object_name
ORDER BY samples DESC
FETCH FIRST 50 ROWS ONLY;
This is where our previous parts start connecting.
45. RAC Workload Investigation Model
We now have:
APPLICATION
|
SERVICE
|
+--------+--------+
| |
RAC1 RAC2
| |
SQL SQL
| |
OBJECT OBJECT
| |
BLOCK BLOCK
\ /
\ /
Cache Fusion
|
GCS/GES
|
Interconnect
This is the complete RAC performance chain we’ve been building throughout Parts 17–20.
46. Practical Service Design Example
Let’s design a two-node production RAC.
Nodes
RAC1 RAC2
Applications
OLTP REPORTING BATCH
Requirements
OLTP is business-critical.
Reporting can tolerate some delay.
Batch should not consume all CPU during business hours.
Possible design:
OLTP Preferred: RAC1 Available: RAC2 REPORTING Preferred: RAC2 Available: RAC1 BATCH Preferred: RAC2 Available: RAC1
Then Resource Manager can further control:
OLTP → high priority REPORTING → medium BATCH → limited
47. Why This Is Better
Instead of:
Everything → everything
we now have an intentional architecture:
OLTP ↓ RAC1 REPORTING ↓ RAC2 BATCH ↓ RAC2
with failover capability.
This can improve:
- Predictability
- Resource management
- Troubleshooting
- Availability
- Workload isolation
48. But Test Failover
A service configuration isn’t production-ready until you’ve tested failure.
Test:
RAC1 failure
Then verify:
SALES_APP
↓
RAC2
Also verify:
Application reconnect Connection pool Transactions FAN Application Continuity
if those features are part of your architecture.
49. Planned Service Relocation Test
Before maintenance:
srvctl status service \ -db PROD \ -service SALES_APP
Then relocate:
srvctl relocate service \ -db PROD \ -service SALES_APP \ -oldinst RAC1 \ -newinst RAC2
Verify:
srvctl status service \ -db PROD \ -service SALES_APP
Then test the application.
Don’t consider the test complete until the application team confirms behavior.
50. What to Capture During the Test
Capture:
Application response time Connection errors Active sessions Service status Listener status GC waits CPU I/O
For example:
SELECT inst_id,
service_name,
COUNT(*)
FROM gv$session
GROUP BY inst_id, service_name
ORDER BY inst_id, service_name;
Then compare before/after.
51. A Senior DBA’s Service Checklist
For every important RAC service, document:
Identity
- Service name
- Application
- Business owner
Placement
- Preferred instances
- Available instances
Availability
- Failover behavior
- Restart policy
- Database role
Performance
- CLB goal
- RLB goal
- GC behavior
- Resource Manager plan
Application
- FAN support
- Connection pool
- Application Continuity
- TAC where applicable
Operations
- Start/stop procedure
- Relocation procedure
- Failure test
- Monitoring
52. Service Troubleshooting Script
Here’s a practical starting point:
#!/bin/bash
DB=PROD
SERVICE=SALES_APP
echo "======================================"
echo "RAC SERVICE HEALTH CHECK"
echo "======================================"
echo
echo "SERVICE CONFIGURATION"
srvctl config service \
-db "$DB" \
-service "$SERVICE"
echo
echo "SERVICE STATUS"
srvctl status service \
-db "$DB" \
-service "$SERVICE"
echo
echo "VIP STATUS"
srvctl status vip
echo
echo "SCAN STATUS"
srvctl status scan
echo
echo "SCAN LISTENER STATUS"
srvctl status scan_listener
echo
echo "CLUSTER STATUS"
crsctl check cluster -all
echo
echo "======================================"
Again, adapt the command syntax to your exact Grid Infrastructure version.
53. Database Service Monitoring Script
SET LINESIZE 220
SET PAGESIZE 100
PROMPT =========================================
PROMPT SESSIONS BY SERVICE AND INSTANCE
PROMPT =========================================
SELECT inst_id,
service_name,
COUNT(*) sessions
FROM gv$session
WHERE username IS NOT NULL
GROUP BY inst_id, service_name
ORDER BY service_name, inst_id;
PROMPT =========================================
PROMPT ACTIVE SESSIONS
PROMPT =========================================
SELECT inst_id,
service_name,
COUNT(*) active_sessions
FROM gv$session
WHERE status = 'ACTIVE'
GROUP BY inst_id, service_name
ORDER BY active_sessions DESC;
PROMPT =========================================
PROMPT GC WAITS BY INSTANCE
PROMPT =========================================
SELECT inst_id,
event,
COUNT(*) sessions
FROM gv$session
WHERE status = 'ACTIVE'
AND event LIKE 'gc %'
GROUP BY inst_id, event
ORDER BY sessions DESC;
54. Production Architecture We Are Building
At this point in the series, our architecture is becoming:
USERS
|
↓
SCAN
|
↓
SERVICE
|
+------------+------------+
| |
RAC1 RAC2
| |
Workload A Workload B
| |
SQL SQL
| |
Objects Objects
| |
+-----------+-------------+
|
Cache Fusion
|
GCS
|
Interconnect
|
Clusterware
Each layer has a different troubleshooting methodology.
55. What You Should Know After Part 20
You should now understand why:
RAC ≠ simply two Oracle instances
A production RAC architecture is:
Clusterware + Network + Storage + Cache Fusion + Services + Application + Workload Management
And services are the bridge between:
RAC infrastructure
and:
Application workload.
56. The Most Important Lesson
If you remember only one thing from this part:
Do not design RAC services around node names. Design them around business workloads.
For example:
SALES_APP PAYMENTS REPORTING BATCH
are meaningful service boundaries.
Then decide:
Where should they run? How should they fail over? How should they be load-balanced? How much resource should they receive? How much cross-instance traffic do they generate?
That is RAC workload engineering.
Part 20 Summary
We’ve covered:
- RAC Services
- Preferred instances
- Available instances
- Service placement
- Workload locality
- Connection Load Balancing
CLB_GOAL- Runtime Load Balancing
RLB_GOAL- FAN
- Application Continuity
- Transparent Application Continuity
- Service failover
- Service relocation
- Role-based services
- RAC + Data Guard services
- Resource Manager + Services
- Service-level ASH analysis
- Service + SQL + object analysis
- Production service architecture
Google+
Facebook
