Question: I am getting g the ORA-16243
error when doing logical standby replication in Data Guard:
APPLIER 16116 ORA-16116:
no work available
BUILDER 16243 ORA-16243: paging out 2816 bytes of memory to disk
COORDINATOR 16116 ORA-16116: no work available
How can we identify the table/SQL to skip & instantiate the
table rows later?
Answer: That is NOT
supported, since holding-back a table might cause logical corruption
(e.g. orphan rows) in constraints. The docs note that the
ORA-16243 error simply notes that it has paged out to disk:
ORA-16243: paging out string
bytes of memory to disk
Cause: Builder
process is paging out memory to free up space in Logical Change
Record (LCR) cache.
Action: No action
necessary, this informational statement is provided to record the
event for diagnostic purposes.
Your root cause solution is to
tune the logical standby to get the transactions applied faster.
The LCRs for Data Guard SQL Apply are staged in the shared pool
of the system global area (SGA), in a heap that is known as the
"Logical Change Record cache" or LCR cache. The ORA-16243
error happens when you don't allocate enough RAM to the SQL Apply
process.
To increase the SQL Apply LCR cache size, follow
these steps:
1 - Start by increasing shared_pool_size (or
sga_max_size if using AMM)
2 - Increase the size of the LCR
cache memory. This will double your existing LCR cache size:
execute dbms_logstdby.apply_set('MAX_SGA',4800);
3 - Look at parallelization of the LCR. To allocate 30
parallel query servers for logical standby log apply services, enter
the following statement:
execute
dbms_logstdby.apply_set('max_servers', 30);
For complete tuning details for Oracle Data Guard, see the book
Oracle Data Guard.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
