Ensuring Real-Time Query Accuracy on Oracle Standby Servers: Requirements and Best Practices
PART#3
Summary:
Troubleshooting and Tuning SQL on Oracle Active Data Guard (Read-Only Standby)
This article
explains how to troubleshoot, tune, and monitor slow SQL queries on an Oracle
Active Data Guard (ADG) physical standby database, which operates in read-only
mode. Because standby databases cannot perform write operations, many
Oracle tuning and diagnostic tools require special handling using database
links, primary database interactions, and UMF/RMF configurations.
Part 1 —
Running SQL Tuning Advisor on a Read-Only Standby Database
Problem
Running SQL
Tuning Advisor directly on a standby database fails with:
ORA-13792:
This operation requires a database link
This happens
because:
- SQL Tuning Advisor needs to
write tuning data
- Standby databases are read-only
- Oracle requires tuning metadata
to be stored on the primary database
Solution
1. Create
a Database Link from Standby to Primary
Use the
SYS$UMF user:
create database
link lnk_to_pri
connect to "SYS$UMF"
identified by "oracle90"
using 'DXTPRI';
Verify
connectivity:
select
db_unique_name from v$database@lnk_to_pri;
2. Create
a SQL Tuning Task on the Standby
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
task_name =>
'TEST_sql_tuning_task1',
database_link_to => 'lnk_to_pri');
3.
Execute the Tuning TaskEXEC DBMS_SQLTUNE.execute_tuning_task(
task_name =>
'TEST_sql_tuning_task1',
database_link_to => 'lnk_to_pri');
4.
Generate the Tuning Report
select
dbms_sqltune.report_tuning_task(
'TEST_sql_tuning_task1',
database_link_to => 'lnk_to_pri')
from dual;
Result
Oracle
successfully generates SQL tuning recommendations for queries running on the
standby database by storing advisory data on the primary database.
Typical
recommendations include:
- Accepting a SQL Profile
- Improved execution plans
- Reduced full table scans
Part 2 —
Creating SQL Profiles for Slow SQL on Standby
Problem
Custom SQL
Profiles generated using coe_xfr_sql_profile.sql fail on standby databases
with:
ORA-00600
because SQL
Profiles require write access.
Scenario
A slow SQL
query had multiple execution plans:
- Some plans were efficient
- One plan caused major slowdowns
due to plan flipping
Goal:
- Force the optimizer to use the
good plan
Solution
1.
Generate the SQL Profile Script on Standby
Using:
coe_xfr_sql_profile.sql
2. Move
the Generated Script to the Primary Database
Using
SCP/FTP/etc.
3.
Execute the Script on the Primary Database
The script
calls:
DBMS_SQLTUNE.IMPORT_SQL_PROFILE
This creates
the SQL Profile on the primary.
4. Allow
Data Guard to Replicate the Profile
The SQL
Profile automatically propagates to the standby database through redo apply.
Result
- The standby database receives
the SQL Profile
- The optimizer consistently
chooses the better execution plan
- Query performance stabilizes
Part 3 —
Using SQLT (SQLTXPLAIN) with Standby Databases
Problem
SQLT tools
like:
- SQLTXTRACT
- SQLTXECUTE
require
write access to store repository data.
Standby
databases are read-only.
Solution — Use SQLTXTRSBY
Oracle
provides:
sqltxtrsby.sql
which allows
SQLT analysis against standby databases via database links.
Steps
1.
Install SQLT on the Primary Database
Run:
sqcreate.sql
DDL changes
replicate to standby.
2. Create
Database Link to Standby
create
public database link dblink_tostandby
connect to sqltxplain
identified by oracle90
using 'DIXITSTAN';
3. Run
the Problematic SQL on Standby
Capture the
SQL ID:
select
sql_id from v$sqlarea;
4. Run
SQLTXTRSBY from the Primary
@sqltxtrsby
<sql_id> DBLINK_TOSTANDBY
Result
SQLT
generates a ZIP report containing:
- SQL execution details
- Explain plans
- Performance data
Limitations:
- No 10053 trace
- No SQL Profile scripts
- No SQL Tuning Advisor reports
because
standby remains read-only.
Part 4 —
Generating AWR Reports for a Standby Database
Problem
AWR
snapshots normally require write access, so AWR scripts fail on standby
databases.
Solution — Configure RMF/UMF Remote AWR
Oracle
supports remote AWR collection using:
- RMF (Remote Management
Framework)
- UMF (Unified Management
Framework)
Main
Steps
1. Unlock
and Configure SYS$UMF
alter system
set "_umf_remote_enabled"=TRUE;
Required on
both primary and standby.
2. Create
Bidirectional Database Links
- Primary → Standby
- Standby → Primary
using
SYS$UMF.
3.
Configure UMF Nodes
Primary:
dbms_umf.configure_node('primary_site');
Standby:
dbms_umf.configure_node(
'standby_site',
'STANDBY_TO_PRIMARY_DBLINK');
4. Create
UMF Topology
DBMS_UMF.create_topology('Topology_1');
5.
Register the Standby Node
DBMS_UMF.register_node(...)
6.
Register the Standby for AWR
DBMS_WORKLOAD_REPOSITORY.register_remote_database(
node_name=>'standby_site');
7. Create
Remote AWR Snapshots
From
primary:
dbms_workload_repository.create_remote_snapshot(
'standby_site');
8.
Generate AWR Report
Run:
@?/rdbms/admin/awrrpti.sql
and choose:
- Standby DBID
- Instance number
- Snapshot range
Result
You can
successfully generate AWR reports for a physical standby database, including:
- DB Time
- CPU usage
- Logical/physical reads
- IO statistics
- SQL activity
even though
the standby is read-only.
Overall Key Takeaways
Oracle
ADG Read-Only Limitation
Many tuning
tools require write access and therefore cannot run directly on standby
databases.
Database
Links Are Essential
Most
solutions rely on:
- Standby → Primary DB links
- SYS$UMF user
- Remote execution/storage
SQL
Profiles Must Be Created on Primary
SQL Profiles
cannot be imported directly into standby databases.
SQLT
Supports Standby Through SQLTXTRSBY
This enables
deep SQL diagnostics for standby workloads.
AWR
Reporting Requires RMF/UMF Setup
Remote AWR
snapshots enable performance analysis on standby systems.
Core
Oracle Features Used
- DBMS_SQLTUNE
- SQL Profiles
- SQLT / SQLTXPLAIN
- Active Data Guard
- Database Links
- RMF / UMF
- AWR Remote Snapshots
- SYS$UMF
- DBMS_WORKLOAD_REPOSITORY
More:
No comments:
Post a Comment