RMAN-03009 - RMAN Backup Failure After Applying DB RU Patch: A Resolution Guide

After applying a Database Release Update (DB RU) patch, we encountered an RMAN backup failure. The error messages provided were as follows:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 08/09/2025 14:54:45
ORA-01403: no data found

Issue: Failure While Registering Database
When attempting to register the database with RMAN, the following output was observed:

RMAN> register database;
database registered in recovery catalog
Creating and using snapshot control file for resync
starting full resync of recovery catalog
Control file used records for BACKUP REDOLOG = 6880
Control file used records for DELETED OBJECT = 6544
Control file used records for BACKUP SET = 2720
Control file used records for ARCHIVED LOG = 2384
Control file used records for LOG HISTORY = 2336
Control file used records for BACKUP DATAFILE = 2288
Control file used records for RMAN STATUS = 2256
Control file used records for BACKUP PIECE = 2096
Control file used records for BACKUP SPFILE = 524
Resync in progress: 11000 RMAN OUTPUTrecords resynced
RMAN Command Id : 2025-08-11T14:58:25
RMAN Command Id : 2025-08-11T14:58:25
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMANCAT.RLH_F1) violated - parent key
not found
RMAN Client Diagnostic Trace file :
/oracle/diag/clients/user_oracle/RMAN_3026693161_110/trace/ora_
rman_37312_1.trc
RMAN Server Diagnostic Trace file :
/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_37319.trc

Root Cause: Recovery Catalog Not Current
Upon attempting to connect to the RMAN catalog, we received the following message:

$ rman catalog rmancatusr@CATDB

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 09 01:38:51 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

recovery catalog database Password:
connected to recovery catalog database
PL/SQL package RMANCAT.DBMS_RCVCAT version 19.10.00.00. in RCVCAT database is not current
PL/SQL package RMANCAT.DBMS_RCVMAN version 19.10.00.00 in RCVCAT database is not current

This indicates that the RMAN catalog was not upgraded and was out of sync with the new database release.

Solution: Upgrade the RMAN Catalog
To resolve the issue, we needed to upgrade the RMAN catalog. The solution was to run the following command:

$ rman catalog <catalog user/passwd> @catdb
RMAN> upgrade catalog ;

The issue has been RESOLVED after upgrading the database. The user able to register database and able to run backups without any issues.

Conclusion

This issue was caused by the RMAN catalog being out of sync after applying a DB RU patch. Upgrading the catalog resolved the error, allowing successful database registration and backup operations.

Unlocking the Power of GraphQL in Oracle Database 23ai

Oracle 23ai introduces native support for GraphQL, it’s a modern API query language and helps developers by enabling efficient and flexible data access from the database. Unlike REST API’s, the GraphQL allows clients to extract the data they need by reducing over-fetching, undirecting of data with flexible data access.

By using GraphQL now developers can expose database schemas as GraphQL API’s without any custom code and access real time data securely with seamless integration using modern frontend tools.

GraphQL helps in the following areas

• Modern Web and Mobile Apps rapid development
• Data federation by coming relation data with other sources such as JSON Data
• Microservices architecture in a clean, versioned manner.

In order to use GraphQL, you must install or upgrade to Oracle 23ai and you should use Oracle Rest Data services (ORDS) 23.2 version or later. Note that SQL Developer web gives us a GraphQL editor screen and by using that user can write the queries

requests

For example 1: - Simple query

SQL> CREATE TABLE emp (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
salary number(10),
is_active BOOLEAN);


SQL> CREATE TABLE dept (
dept_id NUMBER PRIMARY KEY,
name VARCHAR2(100);


GraphQL Query:
query {
    emp {
        emp_id
        name
        salary
        is_active
    }
}

Oracle SQL equivalent query:

SQL> Select emp_id, name, salary, is_active from emp;

For example, 2: - Query filtering with arguments


GraphQL Query:

query {
    emp (emp-id:12345) {
        name
        salary
        is_active
    }
}

Oracle SQL equivalent query:

SQL> Select name, salary, is_active from emp where emp_id=12345;

Example 3: Role based access control

If you have HR user and other users, you want only HR users to see the salary info but not others. By using oracle built in security your GraphQL Schema you can have these access controls.

HR User:
query {
    emp {
        name
        salary
    }
}

Other User:
query {
    emp {
        name
    }
}

You can restrict which fields are visible depending upon the users’ roles without writing any custom logic.

Users can use the tools below to test GraphQL

1. postman with GraphQL support
2. GraphQL playground based or local
3. Apollo Studio
4. ORDS GraphQL endpoint tester

When using GraphQL, Oracle translates GraphQL into an optimized SQL Json and returns only the requested fields. The above examples demonstrate how easy it is to use GraphQL to integrate directly into the oracle stack. The Oracle 23ai enables you to build faster, less code, and deliver richer APIs directly from your database.

Exploring the New BOOLEAN Data Type in Oracle Database 23ai

Oracle Database 23ai has introduced many features, and one of the nice additional in SQL is the support for the BOOLEAN data type. The BOOLEN data types are available for many years in Pl/SQL and now it is supported as native data type. This will help developers simplify application logic. Earlier developers often had to rely on CHAR(1) or NUMBER(1) fields to simulate boolean logic in SQL

The BOOLEAN data type represents logical values such as TRUE, FALSE, and NULL.

Advantages of native BOOLEAN support in SQL:

• Improved readability: No more cryptic 'Y', 'N', or 1, 0 values. Developers can use TRUE and FALSE make code more intuitive.
• Better integration: Direct support in SQL helps use BOOLEAN values easier to use in views, constraints, triggers, and queries.
• Less error-prone: removes confusion caused by using characters or numbers to represent boolean values
• Modernization: Aligns Oracle SQL more closely with other RDBMSs such as PostgreSQL and MySQL.

How to use BOOLEAN in Oracle 23ai

1. Creating a Table with a BOOLEAN Column

SQL> CREATE TABLE emp (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
is_active BOOLEAN);

2. Inserting BOOLEAN Values

INSERT INTO emp (employee_id, name, is_active)
VALUES (101, 'Samantha', TRUE);

INSERT INTO emp (employee_id, name, is_active)
VALUES (102, 'Rex', FALSE);

3. Querying BOOLEAN Values

SELECT name FROM emp
WHERE is_active = TRUE;

Or

SELECT name FROM emp WHERE is_active;

4. Using BOOLEAN in CASE Statements

SELECT name,
CASE
WHEN is_active THEN 'Active'
WHEN NOT is_active THEN 'Inactive'
ELSE 'Unknown'
END AS status FROM emp;

Key Considerations: -

• Note that BOOLEAN values can still be NULL, but in conditions where NULL might affect the logic.
• Not all Oracle tools and connectors fully support BOOLEAN yet and it’s user responsibility to check compatibility with client libraries
• Existing old code using CHAR(1) or NUMBER(1) won’t auto-convert for BOOLEAN, user must migrate manually

The BOOLEAN data type in Oracle Database 23ai is a game changer for developers and DBAs. It improves data modeling, simplifies SQL logic, helps developer experience like other RDBMS platforms and consider adopting BOOLEAN fields wherever required.

Thanks & Regards,

https://oracleracexpert.com  

Data Recovery Advisor (DRA) in Oracle Database

I had the opportunity to work with the Data Recovery Advisor (DRA) feature in earlier versions of Oracle Database, and its great tool to automate the recovery process, reducing downtime and recovery time

In Oracle 11g, the Data recovery advisor is introduced, and this tool helps to reduce the recovery time by providing best automated repair option for the database. This tool automatically diagnoses data failures, determine best repair options, executes repairs at user request, it helps to reduce mean time to recover (MTTR).

The Data recovery advisor can help to limit damage caused by corruption as it can detect, analyze and repair failures before database process discovers it. In traditional method user manually determines the impact and repair options, in some cases users needs to determine right sequence of repair as well.

The Data recovery advisor commands are LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE and CHANGE FAILURE.

• LIST FAILURE: Lists the failures that have occurred in the database. 

        RMAN> LIST FAILURE;

• ADVISE FAILURE: Provides the best repair options for the listed failures. 

        RMAN> ADVISE FAILURE;

• REPAIR FAILURE: Executes the repair process based on the user's selection.

        RMAN> REPAIR FAILURE;

• CHANGE FAILURE: Allows users to modify the failure status.

    RMAN> CHANGE FAILURE FAILURE #1 RESOLVED;

 
Deprecation of DRA in Oracle 19c

While the Data Recovery Advisor was a valuable tool in earlier Oracle releases, it was deprecated in Oracle 19c. This means that DRA will no longer be available for use in future versions beyond Oracle 19c. Additionally, the associated RMAN commands (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE, and CHANGE FAILURE) have also been deprecated. As a result, DBAs will no longer have access to these commands in Oracle 19c and beyond, marking the end of an era for this powerful recovery tool.

Thanks & Regards,
https://oracleracexpert.com

MAX_COLUMNS parameter in Oracle Database 23ai

Before Oracle 23ai, the maximum allowed columns in a table is 1000. From Oracle 23ai you can increase this value by modifying a parameter MAX_COLUMNS up to 4096 if you have any use case. This initialization parameter can be set at system level only and in case of PDB you can limit to specific PDB. 

To use this MAX_COLUMN parameter the compatibility should be set to 23.0.0.0 or higher. To increase max allowed colums you must set the MAX_COLUMNS value to “EXTENDED”. Note that you can change MAX_COLUMNS from STANDARD to EXTENDED any time but to change the value back to STANDARD only when any table or view in the database contains 1000 or fewer columns. 

By default, the MAX_COLUMNS initialization parameter is set to STANDARD

SQL>show parameters max_columns
NAME                                TYPE      VALUE
-------------------------------- ----------- -------------------------
max_columns                      string       STANDARD

When user trying to add columns more than 1000 in a table will receive below error

ORA-01792: maximum number of columns in a table or view is 1000

You can change MAX_COLUMNS value using below command that will allow up to 4096 columns

SQL> ALTER SYSTEM set MAX_COLUMNS=EXTENDED scope=spfile;
SQL> shutdown immediate;
SQL> startup

If the database has tables with more than 1000 columns and trying to update the MAX_COLUMNS parameter value back to STANDARD, then user should receive below error

SQL> ALTER SYSTEM SET set MAX_COLUMNS =STANDARD scope=spfile;

ORA-32017: failure in updating SPFILE
ORA-60471: max_columns cannot be set to STANDARD as there are one or more objects with more than 1000 columns

The only way user can change this value by dropping the objects with more than 1000 columns.

Note that older Oracle client versions (before Oracle 23ai) do not support columns more than 1000 in a table and only Oracle 23ai clients support the 4096 column limit.

Thanks & Regards,

https://oracleracexpert.com