Tag: Multitenant

  • Upgrading Oracle Database 19c to Oracle AI Database 26ai with AutoUpgrade: A Home-Lab Run (and Why 19.3 Would Not Install on Oracle Linux 9)

    Alhamdulillah. This is a write-up of a home-lab exercise: upgrading an Oracle Database 19c container database to Oracle AI Database 26ai (23.26.1) with AutoUpgrade. The upgrade itself was the easy part. Getting a 19c source database onto the lab took longer, because the base 19.3 release would not link on Oracle Linux 9. I have kept that part in, because it is the kind of thing that costs an evening if you have not seen it before.

    The idea came from a community post on the same upgrade path (Oracle 19c to 26ai Database Upgrade). Everything below is from my own lab run. Hostnames and IP addresses are masked.

    Lab environment

    ItemValue
    HostWindows desktop, VirtualBox, 64 GB RAM
    Upgrade VMlabhost19, Oracle Linux 8.10, 4 vCPU, 12 GB RAM, 100 GB disk
    SourceOracle Database 19c EE 19.3 (base release, no RU), CDB19 with PDB19
    TargetOracle AI Database 26ai EE 23.26.1 (public download)
    ToolAutoUpgrade 26.7.2 (build 261111)
    Source home/u01/app/oracle/product/19.0.0/dbhome_1
    Target home/u01/app/oracle/product/26ai/dbhome_1

    Only public downloads were used: the 19.3 and 23.26.1 zips from oracle.com and the AutoUpgrade jar. No My Oracle Support account was involved, which matters for the first problem.

    Part 1: Why 19.3 would not install on Oracle Linux 9

    My first plan was to install a 19c home next to the existing 26ai home on an Oracle Linux 9.8 VM. The software-only install failed at the relink stage every time:

    [FATAL] Error in invoking target 'libasmclntsh19.ohso libasmperl19.ohso client_sharedlib' of makefile
    '/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/ins_rdbms.mk'.

    The installer message is generic. The actual linker error is in $ORACLE_HOME/install/make.log, so that is the first file to check.

    Attempt 1: gcc was not installed

    $ gcc --version | head -1
    bash: gcc: command not found...

    Neither the 26ai nor the 19c preinstall RPM pulled in gcc on this OL9 VM. I installed it, but the relink still failed.

    dnf -y install gcc gcc-c++ make glibc-devel elfutils-libelf-devel ksh

    Attempt 2: libpthread_nonshared.a

    $ grep -n -i -E "error|undefined|cannot find" $ORACLE_HOME/install/make.log
    136:/usr/bin/ld: cannot find /usr/lib64/libpthread_nonshared.a
    ...
    genclntsh: Failed to link libclntshcore.so.19.1

    The 19.3 home ships its own linker stubs. The libpthread.so stub is a linker script that still points at a file Oracle Linux 9 does not have:

    $ cat $ORACLE_HOME/lib/stubs/libpthread.so
    /* GNU ld script
    Use the shared library, but some functions are only in
    the static library, so try that secondarily. */
    OUTPUT_FORMAT(elf64-x86-64)
    GROUP ( libpthread.so.0 /usr/lib64/libpthread_nonshared.a )

    OL9 uses glibc 2.34, where libpthread was merged into libc and libpthread_nonshared.a is gone. The base 19.3 stubs were built for an older glibc.

    Attempts 3 to 5: each stub fix moved the failure

    • Moving all stubs aside gave cannot find -lgcc_s. The link needs the other stubs.
    • Restoring the stubs and removing only libpthread.so gave cannot find -lclntshcore. That one was self-inflicted: an earlier failed genclntsh run had already deleted libclntshcore.so, so the home was damaged.
    • On a fresh unzip with only the libpthread.so stub removed, the client libraries linked, and the failure moved to the Pro*C link with undefined pthread_* symbols.
    /usr/bin/ld: .../lib//libclntsh.so: undefined reference to `pthread_sigmask'
    /usr/bin/ld: .../lib//libclntshcore.so: undefined reference to `pthread_mutexattr_settype'
    /usr/bin/ld: .../lib//libclntsh.so: undefined reference to `pthread_join'
    make[1]: *** [.../precomp/lib/ins_precomp.mk:118: .../precomp/lib/proc] Error 1

    At that point I stopped patching stubs. Oracle’s supported route for 19c on Oracle Linux 9 is a newer Release Update applied at install time (runInstaller -applyRU), and RUs need a support account. Even if a hand-edited 19.3 home had linked in the end, it would not have been a sound base for an upgrade test. I built a separate Oracle Linux 8.10 VM instead, where 19.3 installs without workarounds.

    One lesson from this part: after a relink failure, do not retry in the same home. Wipe it completely, including hidden folders such as .patch_storage, and unzip again.

    Part 2: Building the 19c source on Oracle Linux 8.10

    OS packages and directories, as root:

    dnf -y install oracle-database-preinstall-19c gcc gcc-c++ make glibc-devel elfutils-libelf-devel ksh unzip
    mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1 /u01/app/oracle/product/26ai/dbhome_1 /u01/stage /u01/app/oracle/fra
    chown -R oracle:oinstall /u01
    chmod -R 775 /u01

    I kept one environment file per home so the profile never points at the wrong one. A mistake worth mentioning: the first time, the env file did not exist, $ORACLE_HOME was empty, cd $ORACLE_HOME went to the oracle user’s home directory and the 19c zip started extracting there. Check pwd before every unzip.

    cat > ~/cdb19.env <<'EOF'
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
    export ORACLE_SID=CDB19
    export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/usr/bin:/bin
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    EOF
    . ~/cdb19.env
    cd $ORACLE_HOME && pwd

    Software-only install. CV_ASSUME_DISTID=OL7 gets the base 19.3 installer past its OS version check on OL8:

    unzip -q /u01/stage/LINUX.X64_193000_db_home.zip
    export CV_ASSUME_DISTID=OL7
    ./runInstaller -silent -ignorePrereqFailure -waitforcompletion \
    oracle.install.option=INSTALL_DB_SWONLY \
    UNIX_GROUP_NAME=oinstall \
    INVENTORY_LOCATION=/u01/app/oraInventory \
    ORACLE_HOME=$ORACLE_HOME \
    ORACLE_BASE=$ORACLE_BASE \
    oracle.install.db.InstallEdition=EE \
    oracle.install.db.OSDBA_GROUP=dba \
    oracle.install.db.OSOPER_GROUP=oper \
    oracle.install.db.OSBACKUPDBA_GROUP=backupdba \
    oracle.install.db.OSDGDBA_GROUP=dgdba \
    oracle.install.db.OSKMDBA_GROUP=kmdba \
    oracle.install.db.OSRACDBA_GROUP=racdba \
    DECLINE_SECURITY_UPDATES=true
    [WARNING] [INS-13014] Target environment does not meet some optional requirements.
    ...
    Successfully Setup Software with warning(s).

    No linking errors on OL8. After orainstRoot.sh and root.sh, I created the listener and the database. ARCHIVELOG mode and a recovery area are needed because AutoUpgrade creates a guaranteed restore point before it starts.

    netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp
    dbca -silent -createDatabase -templateName General_Purpose.dbc \
    -gdbName CDB19 -sid CDB19 -createAsContainerDatabase true \
    -numberOfPDBs 1 -pdbName PDB19 \
    -sysPassword <password> -systemPassword <password> -pdbAdminPassword <password> \
    -datafileDestination /u01/app/oracle/oradata -characterSet AL32UTF8 \
    -totalMemory 3072 -enableArchive true \
    -recoveryAreaDestination /u01/app/oracle/fra -recoveryAreaSize 20480 \
    -emConfiguration NONE

    Before the upgrade I saved the PDB open state and created a small test table to compare after the upgrade:

    SET LINESIZE 150 PAGESIZE 100
    COL name FORMAT a10
    COL log_mode FORMAT a12
    COL open_mode FORMAT a12
    COL version FORMAT a12
    SELECT d.name, d.log_mode, d.open_mode, i.version FROM v$database d, v$instance i;
    ALTER PLUGGABLE DATABASE ALL SAVE STATE;
    ALTER SESSION SET CONTAINER=PDB19;
    CREATE USER lab IDENTIFIED BY <password> QUOTA UNLIMITED ON users;
    GRANT CREATE SESSION, CREATE TABLE TO lab;
    CREATE TABLE lab.upg_test AS SELECT * FROM dba_objects;
    SELECT COUNT(*) FROM lab.upg_test;
    NAME LOG_MODE OPEN_MODE VERSION
    ---------- ------------ ------------ ------------
    CDB19 ARCHIVELOG READ WRITE 19.0.0.0.0
    COUNT(*)
    ----------
    72356

    Saving the PDB state matters here. The post that inspired this run hit “PDB is MOUNTED and will not be processed” in AutoUpgrade. With the state saved, both PDBs were picked up.

    I took a VirtualBox snapshot at this point, so the upgrade could be repeated from a clean 19c source.

    Part 3: Installing the 26ai home

    # as root
    dnf -y install oracle-ai-database-preinstall-26ai
    # as oracle
    . ~/26ai.env
    cd $ORACLE_HOME && pwd
    unzip -oq /u01/stage/LINUX.X64_2326100_db_home.zip
    ./runInstaller -ignorePrereq -waitforcompletion -silent \
    -responseFile $ORACLE_HOME/install/response/db_install.rsp \
    installOption=INSTALL_DB_SWONLY \
    UNIX_GROUP_NAME=oinstall \
    INVENTORY_LOCATION=/u01/app/oraInventory \
    ORACLE_HOME=$ORACLE_HOME \
    ORACLE_BASE=$ORACLE_BASE \
    installEdition=EE \
    OSDBA=dba OSOPER=oper OSBACKUPDBA=backupdba \
    OSDGDBA=dgdba OSKMDBA=kmdba OSRACDBA=racdba

    Note that the 26ai installer uses shorter parameter names than 19c: installOption instead of oracle.install.option, installEdition, and OSDBA rather than oracle.install.db.OSDBA_GROUP. On OL8 the preinstall package also pulled in compat-openssl10. The install finished with the same optional-prerequisite warning (INS-13014), followed by root.sh.

    Part 4: AutoUpgrade analyze

    The VM had internet access, so I downloaded the latest jar instead of using the copy bundled in the target home, and ran it with the JDK from the 26ai home:

    mkdir -p /u01/app/oracle/autoupgrade/logs
    cd /u01/app/oracle/autoupgrade
    curl -sSL -o autoupgrade.jar https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar
    JAVA=/u01/app/oracle/product/26ai/dbhome_1/jdk/bin/java
    $JAVA -jar autoupgrade.jar -version
    build.version 26.7.2.261111
    build.date 2026/11/11 04:27:02 +0000
    build.supported_target_versions 12.2,18,19,21,23,26
    build.type production

    Configuration file (autoupg.cfg):

    global.global_log_dir=/u01/app/oracle/autoupgrade/logs
    upg1.sid=CDB19
    upg1.source_home=/u01/app/oracle/product/19.0.0/dbhome_1
    upg1.target_home=/u01/app/oracle/product/26ai/dbhome_1
    upg1.restoration=YES
    upg1.timezone_upg=YES
    upg1.log_dir=/u01/app/oracle/autoupgrade/logs/CDB19
    • restoration=YES: AutoUpgrade creates a guaranteed restore point before the upgrade.
    • timezone_upg=YES: the time zone file is upgraded as part of the post-upgrade fixups.
    $JAVA -jar autoupgrade.jar -config autoupg.cfg -mode analyze -noconsole
    1 CDB(s) plus 2 PDB(s) will be analyzed
    Job 100 database cdb19
    ...
    Job 100 completed
    ------------------- Final Summary --------------------
    Number of databases [ 1 ]
    Jobs finished [1]
    Jobs failed [0]
    Checks Report
    +------------+------+-----------+---------+-------+-------+
    | Job Number | INFO | RECOMMEND | WARNING | ERROR | Total |
    +------------+------+-----------+---------+-------+-------+
    | 100 | 26 | 16 | 12 | 0 | 54 |
    +------------+------+-----------+---------+-------+-------+

    Zero errors. The 12 warnings were four distinct checks, each reported for CDB$ROOT, PDB$SEED and PDB19:

    WarningStageWhat I did
    Convert traditional audit to unified audit policiesPRECHECKSNothing in the lab. On a client system this needs planning before the upgrade, because traditional auditing does not carry on by default.
    Run outside the automatic maintenance windowsPRECHECKSNothing. The run started in the afternoon and finished well before the default window.
    Upgrade the time zone file (DBMS_DST)POSTCHECKSAUTOFIXUP, covered by timezone_upg=YES
    Recompile objects with timestamp mismatchPOSTCHECKSAUTOFIXUP

    Part 5: Deploy

    I ran deploy under nohup so that a dropped PuTTY session would not kill the job:

    nohup $JAVA -jar autoupgrade.jar -config autoupg.cfg -mode deploy -noconsole > deploy.out 2>&1 &
    tail -8 /u01/app/oracle/autoupgrade/deploy.out

    Progress on this VM:

    TimeStageMessage
    16:49PRECHECKSJob 101 started, 1 CDB plus 2 PDBs
    ~16:52PREFIXUPSExecuting fixups (restore point already created)
    17:00DBUPGRADE0% Upgraded CDB$ROOT
    17:13DBUPGRADE41% Upgraded CDB$ROOT
    18:17POSTFIXUPSExecuting fixups (time zone upgrade, recompile)
    ~18:26POSTUPGRADECreating final SPFILE, job completed

    About 1 hour 37 minutes in total. During POSTFIXUPS the alert log repeatedly reported slow online redo log writes. On a VirtualBox disk under upgrade load that is expected, and it did not affect the result.

    Job 101 completed
    ------------------- Final Summary --------------------
    Number of databases [ 1 ]
    Jobs finished [1]
    Jobs failed [0]
    Jobs restored [0]
    Jobs pending [0]
    ---- Drop GRP at your convenience once you consider it is no longer needed ----
    Drop GRP from CDB19: drop restore point AUTOUPGRADE_9212_CDB19193000

    Part 6: Post-upgrade verification

    AutoUpgrade had already updated /etc/oratab to the 26ai home. I pointed my env file at the new home and checked the database:

    sed -i 's#product/19.0.0/dbhome_1#product/26ai/dbhome_1#' ~/cdb19.env
    . ~/cdb19.env
    grep CDB19 /etc/oratab
    CDB19:/u01/app/oracle/product/26ai/dbhome_1:N
    SET LINESIZE 160 PAGESIZE 100
    COL version_full FORMAT a15
    COL comp_id FORMAT a12
    COL status FORMAT a10
    COL name FORMAT a40
    SELECT version_full FROM v$instance;
    SHOW PDBS
    SELECT comp_id, NVL(version_full,'-') version_full, status FROM dba_registry ORDER BY 1;
    SELECT version FROM v$timezone_file;
    SELECT name FROM v$restore_point;
    SHOW PARAMETER compatible
    ALTER SESSION SET CONTAINER=PDB19;
    SELECT COUNT(*) FROM lab.upg_test;
    SELECT COUNT(*) invalid_objs FROM dba_objects WHERE status='INVALID';
    VERSION_FULL
    ---------------
    23.26.1.0.0
    CON_ID CON_NAME OPEN MODE RESTRICTED
    ---------- ------------------------------ ---------- ----------
    2 PDB$SEED READ ONLY NO
    3 PDB19 READ WRITE NO
    COMP_ID VERSION_FULL STATUS
    ------------ --------------- ----------
    APS 23.26.1.0.0 VALID
    CATALOG 23.26.1.0.0 VALID
    CATJAVA 23.26.1.0.0 VALID
    CATPROC 23.26.1.0.0 VALID
    CONTEXT 23.26.1.0.0 VALID
    DV 23.26.1.0.0 VALID
    JAVAVM 23.26.1.0.0 VALID
    OLS 23.26.1.0.0 VALID
    OWM 23.26.1.0.0 VALID
    RAC 23.26.1.0.0 OPTION OFF
    SDO 23.26.1.0.0 VALID
    XDB 23.26.1.0.0 VALID
    XML 23.26.1.0.0 VALID
    XOQ 23.26.1.0.0 VALID
    VERSION
    ----------
    43
    NAME
    ----------------------------------------
    AUTOUPGRADE_9212_CDB19193000
    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    compatible string 19.0.0
    COUNT(*)
    ----------
    72356
    INVALID_OBJS
    ------------
    0
    • Version 23.26.1.0.0, PDB19 open READ WRITE.
    • All components VALID. RAC shows OPTION OFF, which is expected on a single instance.
    • Time zone file upgraded to version 43.
    • The test table still has 72,356 rows, and PDB19 has no invalid objects.
    • COMPATIBLE is still 19.0.0, so the restore point could still be used to go back.

    Part 7: Listener, restore point and COMPATIBLE

    The listener was still running from the 19c home. I stopped it, copied the network files to the 26ai home, updated the paths and started it from the new home:

    /u01/app/oracle/product/19.0.0/dbhome_1/bin/lsnrctl stop
    cp /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/{listener.ora,sqlnet.ora,tnsnames.ora} $ORACLE_HOME/network/admin/
    sed -i 's#product/19.0.0/dbhome_1#product/26ai/dbhome_1#g' $ORACLE_HOME/network/admin/*.ora
    lsnrctl start

    The listener showed “supports no services” at first. local_listener was set to the alias LISTENER_CDB19, which dbca had written into tnsnames.ora, so copying that file to the new home mattered. After ALTER SYSTEM REGISTER the services appeared:

    Service "CDB19" has 1 instance(s).
    Instance "CDB19", status READY, has 1 handler(s) for this service...
    Service "CDB19XDB" has 1 instance(s).
    Instance "CDB19", status READY, has 1 handler(s) for this service...
    Service "pdb19" has 1 instance(s).
    Instance "CDB19", status READY, has 1 handler(s) for this service...

    With the checks done, I dropped the restore point so it would stop holding flashback logs in the recovery area:

    DROP RESTORE POINT AUTOUPGRADE_9212_CDB19193000;
    SELECT COUNT(*) FROM v$restore_point;

    I left COMPATIBLE at 19.0.0. Raising it enables the new release’s features, but it cannot be lowered again, which rules out a downgrade. In a real project that decision comes after application testing. In the lab I took a VirtualBox snapshot first, so I can raise it later and still roll back the VM.

    Lessons learned

    • Check the certification of the exact release on the exact OS. “19c on OL9” and “base 19.3 on OL9” are not the same thing.
    • When runInstaller reports a relink FATAL, read $ORACLE_HOME/install/make.log. The installer log only repeats the generic message.
    • After a failed relink, start from a fresh unzip. Retrying in a damaged home produces new errors that have nothing to do with the original cause.
    • Put a time limit on workarounds. After a few rounds of stub fixes, moving to a certified platform was faster and gave a cleaner result.
    • Save the PDB open state before AutoUpgrade so every PDB is processed.
    • Run deploy under nohup (or screen/tmux) and follow deploy.out and status.log.
    • Moving the listener is not part of the AutoUpgrade job. Copy tnsnames.ora too if local_listener uses an alias.
    • Keep the guaranteed restore point until verification is done, and treat COMPATIBLE as a separate, later decision.

    ما شاء الله, a clean result in the end: 0 errors in analyze, 0 failed jobs in deploy, and the test data intact on 26ai. Next in this lab is Oracle APEX on the 26ai database.

  • Oracle 19c Data Guard: Recovering UNNAMED Datafiles on a Multitenant Physical Standby

    Alhamdulillah.

    Oracle Data Guard physical standbys keep redo synchronized automatically — until the primary adds a datafile that the standby can’t place on disk. When STANDBY_FILE_MANAGEMENT=MANUAL, the standby refuses to guess where the new file should live. Managed Standby Recovery stops instead, leaving the control file ahead of the actual datafiles and the standby parked at a consistent SCN.

    This post walks through a real 19c multitenant incident: MRP0 hit ORA-01274 after a new datafile couldn’t be added, recovery stalled, and the fix required creating the missing file inside the correct pluggable database container, then resuming and verifying redo apply. In this incident, the missing datafile belonged to PROD. Creating the file from within the owning PDB resolved the placeholder, after which Redo Apply could be restarted from CDB$ROOT.

    Failure Diagnosis

    The alert log during redo apply:
    Recovery of Online Redo Log: Thread 1 Group 4 Seq 105902 Reading mem 0
    Mem# 0: /uora/PROD/db/product/19.0.0/dbs/broken3
    2025-05-12T08:49:56.027319-04:00
    PROD(3):File #433 added to control file as ‘UNNAMED00433’ because
    PROD(3):the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
    PROD(3):The file should be manually created to continue.
    PR00 (PID:2796228): MRP0: Background Media Recovery terminated with error 1274
    2025-05-12T08:49:56.092976-04:00
    Errors in file /uora/PROD/db/diag/rdbms/proddr/cdbPROD/trace/cdbPROD_pr00_2796228.trc:
    ORA-01274: cannot add data file that was originally created as
    ‘/uora/PROD/data/proddata/users02.dbf’
    PR00 (PID:2796228): Managed Standby Recovery not using Real Time Apply
    Recovery interrupted!
    2025-05-12T08:49:57.933554-04:00
    Recovery stopped due to failure in applying recovery marker (opcode 17.30).
    Datafiles are recovered to a consistent state at change 36278361292
    but controlfile could be ahead of datafiles.
    Stopping change tracking


    Three things happening in sequence:

    1. **Control file registered a file that doesn’t exist.** The redo stream carried a datafile creation from the primary. The standby’s control file logged it — but because STANDBY_FILE_MANAGEMENT=MANUAL, the standby recorded it as UNNAMED00433 instead of creating it.

    2. **Recovery stopped on the recovery marker.** Opcode 17.30 is the file operation that couldn’t be satisfied. The standby parked at SCN 36278361292 — a point where datafiles are internally consistent, but the control file believes file 433 exists when it doesn’t.

    3. **MRP0 exited.** The managed standby process shut down cleanly. The alert log shows “not using Real Time Apply,” indicating recovery was interrupted and the standby could not continue applying the current redo stream.

    **Root cause:** Configuration, not corruption. STANDBY_FILE_MANAGEMENT=MANUAL means every new primary datafile must be created manually on the standby. With AUTO and proper DB_FILE_NAME_CONVERT / PDB_FILE_NAME_CONVERT setup, new files appear automatically. This environment chose MANUAL, so a routine tablespace addition on the primary became a recovery outage on the standby.

    ## Recovery Workflow

    MRP0 stops → Query UNNAMED files → Switch to PDB → Create datafile → Resume MRP → Verify

    ## Step 1 — Locate the UNNAMED File

    Confirm the standby’s role and find the placeholder. Run from CDB$ROOT:


    SET ECHO OFF PAGESIZE 60 LINESIZE 120 TRIMSPOOL ON
    COL NAME FORMAT A50
    SQL> SELECT name, open_mode, database_role FROM v$database;
    NAME OPEN_MODE DATABASE_ROLE
    ——— ——————– —————-
    CDBSTBY MOUNTED PHYSICAL STANDBY
    SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE NAME LIKE ‘%UNNAMED%’;
    FILE# NAME
    ———- ————————————————–
    433 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00433


    File number is 433. Which PDB owns it? Use CON_ID:


    COL CON_ID FORMAT 9999
    COL FILE# FORMAT 9999
    COL NAME FORMAT A50
    SQL> SELECT con_id, file#, name FROM V$DATAFILE WHERE file# = 433;
    CON_ID FILE# NAME
    ———- —— ————————————————–
    3 433 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00433


    CON_ID = 3 is PROD. That’s where the fix must run.

    ## Step 2 — PDB Recovery: Create the File in the Right Container

    From CDB$ROOT, the command appeared to succeed:


    SQL> ALTER DATABASE CREATE DATAFILE ‘UNNAMED00433’
    AS ‘/uora/PROD/data/proddrdata/users02.dbf’;
    Database altered.
    “`

    But checking from the PDB showed the placeholder still registered:


    SQL> ALTER SESSION SET CONTAINER = PROD;
    Session altered.
    SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE NAME LIKE ‘%UNNAMED%’;
    FILE# NAME
    ———- ————————————————–
    433 /uora/PROD/db/product/19.0.0/dbs/UNNAMED00433


    After switching to the PROD container and reissuing the command:


    SQL> ALTER DATABASE CREATE DATAFILE ‘UNNAMED00433’
    AS ‘/uora/PROD/data/proddrdata/users02.dbf’;
    Database altered.
    SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE NAME LIKE ‘%UNNAMED%’;
    no rows selected


    **Important:** Validate this behavior against Oracle 19c documentation and test in non-production before applying to your standby.

    ## Step 3 — Redo Apply Resumption


    SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
    Session altered.
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
    USING CURRENT LOGFILE DISCONNECT FROM SESSION;
    Database altered.
    SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY;
    PROCESS STATUS THREAD# SEQUENCE#
    ———- ————— ——- ———
    MRP0 APPLYING_LOG 1 105902
    ARCH CLOSING 1 105905
    RFS IDLE 1 105906


    MRP0 is back in APPLYING_LOG.

    ## Step 4 — Expect More: The Second UNNAMED File

    Recovery resumed and encountered another placeholder:

    “`sql
    SQL> ALTER SESSION SET CONTAINER = PROD;
    SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE NAME LIKE ‘%UNNAMED%’;
    FILE# NAME
    ———- ————————————————–
    435 /uora/PROD/db/product/19.0.0/dbs/UNNAMED00435
    SQL> ALTER DATABASE CREATE DATAFILE ‘UNNAMED00435’
    AS ‘/uora/PROD/data/proddrdata/transaction_table_16.dbf’;
    Database altered.


    Expect a loop. Every datafile added on the primary after the gap will appear as another UNNAMED entry. Grep the alert log for the full backlog first:


    grep -i ‘unnamed’ alert_cdbstby.log | head -20
    grep ‘ORA-01274’ alert_cdbstby.log | wc -l


    ## Recovery Verification

    Once placeholders are gone, resume recovery:


    ALTER SESSION SET CONTAINER = CDB$ROOT;
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
    USING CURRENT LOGFILE DISCONNECT FROM SESSION;
    “`

    Verify:


    SELECT con_id, file#, name FROM V$DATAFILE WHERE name LIKE ‘%UNNAMED%’;
    SELECT process, status, thread#, sequence# FROM V$MANAGED_STANDBY WHERE process LIKE ‘MRP%’;
    SELECT * FROM V$DATAGUARD_STATS;


    Clean results: zero UNNAMED rows, MRP0 in APPLYING_LOG, apply lag returning to normal.

    ## Alternative: Restore from Backup

    If redo is unavailable or purged:

    1. Identify primary backup with missing file
    2. Restore to standby location
    3. Register with control file via RMAN
    4. Resume recovery

    Follow Oracle RMAN and Data Guard documentation for your configuration.

    ## Lessons Learned

    **Decide STANDBY_FILE_MANAGEMENT deliberately.** MANUAL works for locked-down environments. AUTO prevents this class of manual intervention when file naming and storage support it. ما شاء الله — the infrastructure, when well-designed, removes recovery work.

    **Session container context can affect recovery steps.** In this incident, CREATE DATAFILE from CDB$ROOT appeared to succeed but didn’t resolve the placeholder until run from PROD PDB. Validate against Oracle 19c documentation and test in non-production.

    **Expect a loop.** Every datafile added after the gap arrives as another UNNAMED entry. Grep the alert log for full backlog first.

    **Verify before closing.** Confirm zero UNNAMED files, MRP0 running, settled lag before closing incident.

    ## Conclusion

    ORA-01274 on a physical standby is a configuration boundary, not data loss. This incident resolved via CREATE DATAFILE in the PROD PDB, followed by managed recovery restart from CDB$ROOT. Before applying: Verify steps against Oracle 19c documentation, test in non-production, verify standby status and lag.