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.
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
