Introduction
Oracle RAC and Oracle Active Data Guard provide high availability and disaster recovery for the Oracle Database. During maintenance operations such as patching and upgrades or in case of failures and outages, applications can reconnect to another active Oracle RAC instance or to the Data Guard standby database.
Oracle Application Continuity extends the high availability to the application layer. It masks the database session interruption from end-users and applications by automatically reconnecting the session to an available database instance, restoring the session state, and replaying interrupted in-flight requests transparently to end-users and applications.
There might be cases when replay cannot be enabled, e.g., when the default database service or deprecated JDBC classes are being used. This is where the Application Continuity Protection Check (ACCHK) utility comes in, introduced in Oracle Database version 12.1, with a new release in version 19.11 that uses tables for mining similar to AWR.
ACCHK
ACCHK reports the coverage percentage value for your workload, the number of operations performed, the amount of time protected as a proportion of the work done, and the number of protected and unprotected operations, providing guidance on the level of protection for each application that uses Application Continuity and assists you in increasing the protection level for your application.
ACCHK records the events, statistics, and warnings of an application using an Application Continuity-enabled service during a specific period in tables in the database. In the case of a read-only database, the information is stored in trace files. The information can be accessed through ACCHK reports or mined by querying the ACCHK views directly.
Application Continuity collects statistics from the system, the session, and the database service, enabling you to monitor the protection level of your application. These statistics are stored in the Automatic Workload Repository (AWR) and are available in AWR reports. Check the documentation and this blog post for more details about protection-level statistics.
Step 1: Create the ACCHK Views
Before using ACCHK for the first time, create the ACCHK role and views in your database:
SQL> execute dbms_app_cont_admin.acchk_views();
The command can safely be re-executed even if the role and views already exist. The COMPATIBLE parameter needs to be set to 12.2.0 or higher.
The database role ACCHK_READ will be created alongside the following ACCHK views:
- DBA_ACCHK_EVENTS
- DBA_ACCHK_EVENTS_SUMMARY
- DBA_ACCHK_STATISTICS
- DBA_ACCHK_STATISTICS_SUMMARY
Step 2: Enable and Disable ACCHK
ACCHK is not enabled by default. You need to enable ACCHK to generate reports to check the protection level for your application.
Grant read access to the users who will access the ACCHK report and views, using the ACCHK_READ role that was generated in step 1:
SQL> GRANT ACCHK_READ TO <user_name>;
ACCHK uses Application Continuity traces to collect coverage for a workload and provides detailed information as per your request. Enable Application Continuity tracing to collect coverage before you run a database workload:
SQL> execute dbms_app_cont_admin.acchk_set(true);
By default, ACCHK is disabled automatically after 600 seconds. You can use a specific time range from 60 to 86400 seconds (one minute to one day) if needed. For example, to enable ACCHK and automatically disable it after one hour:
SQL> execute dbms_app_cont_admin.acchk_set(true, 3600);
To disable ACCHK manually before the period specified:
SQL> execute dbms_app_cont_admin.acchk_set(false);
Step 3: Execute your Workload
After ACCHK is enabled, run your application workload to be validated by ACCHK and to get guidance for the level of protection, reason for incomplete protection, and methods to increase the protection level.
Step 4: Generate the ACCHK Report
ACCHK generates reports only for the Application Continuity sessions. To generate the report:
SQL> SET SERVEROUTPUT ON FORMAT WRAPPED;
SQL> SET pages 0
SQL> set lines 300
SQL> execute dbms_app_cont_report.acchk_report();
The data is derived from the ACCHK views.
You can provide a specific report level when generating the report:
- SUMMARY: summary report only (default).
- WARNING: summary and warnings.
- FULL: summary and all workload capture, including where there are no warnings.
You can also create the report only for sessions connected to a specific service name. If no service name is provided, the report will be generated for sessions connected to all services. For example:
SQL> execute dbms_app_cont_report.acchk_report(DBMS_APP_CONT_REPORT.FULL);
SQL> execute dbms_app_cont_report.acchk_report(DBMS_APP_CONT_REPORT.FULL, 'tacsrv271023.subnetpublic.vcnfra.oraclevcn.com');
If ACCHK was not enabled or the services used by the application were not enabled for Application Continuity, an empty report will be generated:
--------------------------------------
------------ ACCHK Report ------------
--------------------------------------
Nothing to report.
An example report when Application Continuity is enabled:
--------------------------------------
------------ ACCHK Report ------------
--------------------------------------
CON_ID : 3
CON_UID : 596064952
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Fail Protected Protected Avg Avg Protected Avg Avg Protected Max Max Protected
Service over calls % time % Requests calls/request calls/request time/request ms time/request ms calls/request calls/request
------------------------- ----- --------- --------- ------------- ------------- ------------- --------------- --------------- --------------- ---------------
oracle_srvc1.regress.rdbm TRANS 87.2 78.7 2 23.5 20.5 45.04 35.535 24 21
End of report.
ACCHK Notes:
Protected calls % refers to the best achievable failover protection and under
certain system environment conditions this ratio could be lower.
For further details, refer to Oracle Application Continuity documentation.
PL/SQL procedure successfully completed.
Step 5: Evaluate the ACCHK Report
The ACCHK report includes a header followed by the CON_ID and CON_UID of the reported services in the PDB, the statistics recorded for the services, and notes at the end of the report.
The event type and corresponding error codes for unsuccessful replays will be reported. The following event types can be reported:
- DISABLED: The event caused capture or replay to be disabled. Refer to the ERROR_CODE column for more information.
- NEVER ENABLED: Neither Application Continuity nor Transparent Application Continuity was enabled for the session when the event occurred.
- NOT REENABLING: The event describes why the database could not reenable protection after protection was disabled. Refer to the ERROR_CODE column for more information.
- REPLAY FAILED: The event describes why the session did not failover. This event only occurs if Application Continuity could not failover. Refer to the ERROR_CODE column for more information.
- REPLAY SUCCEEDED: The event describes a successful failover.
- STATEFUL PACKAGE: The event describes the statefulness of a package, the package name and the owner are displayed in the line below of this event only if the package is stateful. Refer to the ERROR_CODE column for more information.
- SIDE EFFECT: The event describes which package recorded a side effect. The information is displayed below the event.
- WARNING: The event informs about the mutable and sequences that cannot be kept if the KEEP privilege is not granted or the capture limit has been reached.
Step 6: Enhance the Protection of your Application
Use the information provided by ACCHK to enhance the protection level of your application, depending on the event type and error code.
Action for DISABLE (41409) and NOT_REENABLING (41459)
Event Type Error Code Program Module Action SQL_ID Call Total
DISABLE 41409 oracle_prgrm1 oracle_mdlnm1 COMMIT 2
NOT_REENABLING 41459 oracle_prgrm1 oracle_mdlnm1 COMMIT 2
After committing the transaction, it is not possible to failover with Application Continuity (set FAILOVER_TYPE=TRANSACTION at the database service level) until the next request.
Consider using Transparent Application Continuity (set FAILOVER_TYPE=AUTO for the database service), which can enable replay after disable such as commit.
When writing new applications, do not execute multiple commits in a request, but rather one per request. Aim for the COMMIT to be the last operation of the request.
Action for NOT_REENABLING (41461)
Event Type Error Code Program Module Action SQL_ID Call Total
NOT_REENABLING 41461 oracle.program oracle.module COMMIT 1
Non SSS Cursors are open not reenabling.
The application is using Transparent Application Continuity (FAILOVER_TYPE=AUTO) and has disabled midway through a request. Transparent Application Continuity is unable to reenable replay because there are cursors left open.
Close the cursors when FETCH is completed. (Note: SSS=Session State Stable)
In Oracle Database 23c, Transparent Application Continuity allows cursors that remain open when reenabling after a DISABLE event.
Action for STATEFUL_PACKAGE and SIDE_EFFECT events
Event Type Error Code Program Module Action SQL_ID Call Total
STATEFUL_PACKAGE 41460 db.program db.module 5r76ystk31gsh SQL/PLSQL Execu 1
Package state cannot be restored for package name: UTL_TCP Owner: SYS
SIDE_EFFECT 0 db.program db.module 5r76ystk31gsh SQL/PLSQL Execu 1
Side effects may be replayed for package name: UTL_TCP_LIB Owner: SYS
The application is using Transparent Application Continuity (FAILOVER_TYPE=AUTO) which does not allow a side effect to be replayed. Check the package name and owner in the info line below SIDE_EFFECT event for more information.
You may consider using Application Continuity (FAILOVER_TYPE=TRANSACTION) which allows side effects to be replayed.
In Oracle Database 23c, DBMS_APP_CONT_ADMIN.APPLY_REPLAY_RULE procedure allows side effects to be marked replayable.
Action for NEVER_ENABLED (41462) event
Event Type Error Code Program Module Action SQL_ID Call Total
NEVER_ENABLED 41462 oracle.program 18
Session has not been used, connection pool may be oversized.
This error is reported by ACCHK if Application Continuity is being used to protect user calls. If a session was created and no user calls were received, then a NEVER_ENABLED 41462 is recorded. This event is recorded to verify if the application is not using an Application Continuity service.
Protection % indicators and user calls % indicators are not affected by NEVER_ENABLED 41462 events.
Please check your replay driver and your connection pool settings to ensure that the pools are not oversized.
Action for NEVER_ENABLED (41463) event
Event Type Error Code Program Module Action SQL_ID Call Total
NEVER_ENABLED 41463 oracle.program 1
Session received user calls, but Application Continuity did not enable.
Your application is using either Application Continuity or Transparent Application Continuity.
This error is reported by ACCHK when a session receives user calls, but no calls were protected by Application Continuity during the session. This event is used to verify if the application is not using an Application Continuity service.
There could be sessions receiving user calls and Application Continuity could not protect them, then NEVER_ENABLED 41463 is recorded and this situation could affect protection % and user calls % indicators, reducing protected user calls coverage %.
In general, NEVER_ENABLED 41463 is an indicator for sessions having workloads without protection. These sessions may need attention from the developer.
In 23c and a later 19RU JDBC consolidated data source will automatically enable replay for you to avoid this issue. If your application is using Java, make sure you are using the replay driver.
Action for WARNING events
Event Type Error Code Program Module Action SQL_ID Call Total
WARNING 0 program3 module3 cn3g6zgx59qg2 SQL/PLSQL Execu 1
CURRENT_TIMESTAMP capture limit reached and its value cannot be kept.
WARNING 0 program3 module3 cn3g6zgx59qg2 SQL/PLSQL Execu 1
CURRENT_TIMESTAMP value cannot be kept because user has no KEEP DATE TIME privilege granted.
WARNING 0 program3 module3 a931cadxzy78x SQL/PLSQL Execu 1
SYSDATE capture limit reached and its value cannot be kept.
WARNING 0 program3 module3 a931cadxzy78x SQL/PLSQL Execu 1
SYSDATE value cannot be kept because user has no KEEP DATE TIME privilege granted.
WARNING 0 program3 module3 8ryaa68us1v3b SQL/PLSQL Execu 1
SYSTIMESTAMP capture limit reached and its value cannot be kept.
In this case, the KEEP privilege is not granted, and capture limits are reached.
Action for WARNING notes
This kind of warning is displayed before the ACCHK notes section.
...
End of report.
WARNING for service: servicetest34_ac.regress.rdbms.dev.us.oracle.com
To restore your session state at failover, set the attribute FAILOVER_RESTORE on your database service to LEVEL1 or LEVEL2. NONE is not a recommended value.
ACCHK Notes:
Protected calls % refers to the best achievable failover protection and under
certain system environment conditions this ratio could be lower.
For further details, refer to Oracle Application Continuity documentation.
PL/SQL procedure successfully completed.
In this case, failover restore is set to NONE and thus the application will not fail over the session.
Set the database service attribute FAILOVER_RESTORE to LEVEL1 or LEVEL2.
Step 7: Purge ACCHK data
After running and evaluating the application workload, you might want to clear the ACCHK data before running a new workload:
SQL> execute dbms_app_cont_admin.acchk_purge(purge_all=>true);
-- alternatively:
SQL> execute dbms_app_cont_admin.acchk_purge(NULL, NULL, TRUE);
SQL> execute dbms_app_cont_admin.acchk_purge('', '', TRUE);
After that, the report is empty:
SQL> execute dbms_app_cont_report.acchk_report(DBMS_APP_CONT_REPORT.FULL);
--------------------------------------
------------ ACCHK Report ------------
--------------------------------------
Nothing to report.
PL/SQL procedure successfully completed.
Conclusion
The ACCHK utility is a post-processing tool to report detailed Application Continuity coverage. ACCHK identifies which application configuration is best protected to help you make an informed decision about which configuration to use for maximum protection for failover and if desired how to increase the protection level for your application.
Further Reading
- Application Continuity Protection Check – documentation
- Configuring Continuous Availability for Applications – documentation
- How to Use AWR to Check if Your Application is Protected by Application Continuity
- How Draining and Application Continuity Work for Maintenance with Oracle RAC
- How Application Continuity Works for Unplanned Interruption in Oracle RAC Environments
Database Heartbeat 