Handling ORDS error for database outages

 When handling database outages in an Oracle REST Data Services (ORDS) API, it's essential to implement graceful failure handling to ensure the API remains responsive and provides meaningful error messages. Here’s how you can manage a database outage effectively:

---

1. Implement Exception Handling in PL/SQL

If your ORDS API is backed by a PL/SQL procedure, modify it to catch database errors and return a proper HTTP response.

Example PL/SQL Exception Handling

CREATE OR REPLACE PROCEDURE my_api_procedure (
    p_input   IN  VARCHAR2,
    p_output  OUT VARCHAR2,
    p_status  OUT NUMBER
)
AS
BEGIN
    -- Your database logic here
    SELECT some_value INTO p_output FROM my_table WHERE some_column = p_input;

    -- Success status
    p_status := 200;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_output := 'Data not found';
        p_status := 404;
    WHEN OTHERS THEN
        p_output := 'Database error occurred';
        p_status := 503; -- Service Unavailable
END my_api_procedure;

• Use 503 Service Unavailable for database connection issues.
• Use 404 Not Found for missing data.

---

2. Configure ORDS Error Handlers

ORDS allows custom error handling using error-handlers in ords-config.xml.

Example: ORDS Custom Error Handler

Modify the ORDS configuration to return JSON errors instead of exposing internal database errors.

1. Locate defaults.xml (inside ORDS config directory).
2. Add a custom error mapping:

   <entry key="error-mapping">
       <error>
           <status-code>503</status-code>
           <message>Database temporarily unavailable</message>
       </error>
   </entry>
   

3. Restart ORDS:

   java -jar ords.war standalone

---

3. Use Connection Pooling & Timeout Settings

If the database is slow or unreachable, configure ORDS connection pooling settings to avoid API timeouts.

Modify ords-config.xml:

<entry key="jdbc.InitialLimit">5</entry>
<entry key="jdbc.MinLimit">5</entry>
<entry key="jdbc.MaxLimit">50</entry>
<entry key="jdbc.InactivityTimeout">30</entry> <!-- Closes idle connections -->
<entry key="jdbc.AbandonedConnectionTimeout">60</entry> <!-- Reclaims lost connections -->

---

4. Implement a Health Check Endpoint

Create a simple ORDS endpoint to check if the database is up.

Example: Health Check SQL Query

SELECT 'OK' AS status FROM dual;

• If the database is down, the API should return a 503 Service Unavailable.

---

5. Enable ORDS Standalone Mode with Connection Fallback

If your ORDS API is running in standalone mode, configure a fallback response.

Modify standalone.properties:

standalone.failure.message = "Database is currently unavailable. Please try again later."
standalone.failure.status = 503

---

6. Monitor ORDS Logs & Set Alerts

• Enable ORDS Logging: Logs are available in ords.log
• Use Oracle APEX or Prometheus for monitoring.
• Set alerts in Oracle Cloud or a monitoring tool like Nagios.