Eclipsys Blog

Ensuring Database Integrity During Migration: A Production DBA's Challenge with Datapatch Execution (19c PDB : datapatch issue :ORA-01110 )

Written by Chanaka Yapa | Mar 25, 2025 2:39:51 PM

Introduction

In today’s fast-paced data-driven world, data is more valuable than gold, and protecting databases has become a critical priority. As a production DBA, one of my key responsibilities is ensuring that the software stays updated with the latest patches, as Oracle releases these patches every quarter.

Recently, I faced a challenge after migrating a database from ODA to Exadata, upgrading from Oracle version 19.13 to 19.24. During the process, the datapatch execution failed on a pluggable database (PDB) due to a missing PDB temporary file.

 

Before running datapatch, I performed a sanity check to confirm that everything was in good condition, and the results were clean.

SANITY CHECK

Sanity check results : 



[oracle@exanode01 OPatch]$ ./datapatch -sanity_checks
SQL Patching sanity checks version 19.24.0.0.0 on Wed 23 Oct 2024 04:20:17 PM UTC
Copyright (c) 2021, 2024, Oracle.  All rights reserved.

Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sanity_checks_20241023_162017_119323/sanity_checks_20241023_162017_119323.log

Running checks
Use of uninitialized value in concatenation (.) or string at /u02/app/oracle/product/19.0.0.0/dbhome_1/sqlpatch/sqlpatch_sanity_checks.pm line 645.
Use of uninitialized value in concatenation (.) or string at /u02/app/oracle/product/19.0.0.0/dbhome_1/sqlpatch/sqlpatch_sanity_checks.pm line 645.
Use of uninitialized value in concatenation (.) or string at /u02/app/oracle/product/19.0.0.0/dbhome_1/sqlpatch/sqlpatch_sanity_checks.pm line 645.

JSON report generated in /u02/app/oracle/cfgtoollogs/sqlpatch/sanity_checks_20241023_162017_119323/sqlpatch_sanity_checks_summary.json file
Checks completed. Printing report:

Check: Database component status - OK
Check: PDB Violations - OK
Check: Invalid System Objects - OK
Check: Tablespace Status - OK
Check: Backup jobs - OK
Check: Temp file exists - OK
Check: Temp file online - OK
Check: Data Pump running - OK
Check: Container status - OK
Check: Oracle Database Keystore - OK
Check: Dictionary statistics gathering - WARNING
  Patching the database while dictionary statistics are being collected may lead to performance issues.
  Dictionary statistics are currently being collected in the database.
  Consider patching the database when dictionary statistics are not being collected. To check if such operations are currently running, run this query:
    SELECT sofar, totalwork, units, start_time, time_remaining, message
    FROM sys.v$session_longops
    WHERE opname = 'Gather Dictionary Schema Statistics'
    AND time_remaining > 0;
  PDB$SEED:
    |     LATEST      |        OPERATION        |
    |-----------------+-------------------------|
    | 17-JUL-22 09:16 | gather_dictionary_stats |
    |-----------------+-------------------------|
Check: Scheduled Jobs - WARNING
  Execution of scheduler jobs while database patching is running may lead to failures and/or performance issues.
  There are jobs currently running or scheduled to be executed during next hour.
  Consider patching the database when jobs are not running and will not be scheduled to run during patching.
  To check for jobs that are running or scheduled to run:
    SELECT owner as schema_name, job_name, state, next_run_date
    FROM sys.all_scheduler_jobs
    WHERE state = 'RUNNING'
    UNION
      SELECT owner as schema_name, job_name, state, next_run_date
      FROM sys.all_scheduler_jobs
      WHERE state = 'SCHEDULED'
      and cast(next_run_date as date)  sysdate
      and cast(next_run_date as date)  sysdate + 1/24;
  CDB$ROOT:
    |         JOB_NAME         |            NEXT_RUN_DATE            | SCHEMA_NAME |   STATE   |
    |--------------------------+-------------------------------------+-------------+-----------|
    | CLEANUP_ONLINE_IND_BUILD | 23-OCT-24 04.48.21.428584 PM +00:00 |     SYS     | SCHEDULED |
    |--------------------------+-------------------------------------+-------------+-----------|
    |    CLEANUP_ONLINE_PMO    | 23-OCT-24 04.49.01.459102 PM +00:00 |     SYS     | SCHEDULED |
    |--------------------------+-------------------------------------+-------------+-----------|
    |   CLEANUP_TAB_IOT_PMO    | 23-OCT-24 04.48.31.422134 PM +00:00 |     SYS     | SCHEDULED |
    |--------------------------+-------------------------------------+-------------+-----------|
  EGDEV_PDB1:
    |         JOB_NAME         |            NEXT_RUN_DATE            | SCHEMA_NAME |   STATE   |
    |--------------------------+-------------------------------------+-------------+-----------|
    | CLEANUP_ONLINE_IND_BUILD | 23-OCT-24 05.11.03.390152 PM +00:00 |     SYS     | SCHEDULED |
    |--------------------------+-------------------------------------+-------------+-----------|
    |    CLEANUP_ONLINE_PMO    | 23-OCT-24 05.11.43.403227 PM +00:00 |     SYS     | SCHEDULED |
    |--------------------------+-------------------------------------+-------------+-----------|
    |   CLEANUP_TAB_IOT_PMO    | 23-OCT-24 05.11.13.329389 PM +00:00 |     SYS     | SCHEDULED |
    |--------------------------+-------------------------------------+-------------+-----------|
  EGQA_PDB1:
    |         JOB_NAME         |            NEXT_RUN_DATE            | SCHEMA_NAME |   STATE   |
    |--------------------------+-------------------------------------+-------------+-----------|
    | CLEANUP_ONLINE_IND_BUILD | 23-OCT-24 05.11.03.441575 PM +00:00 |     SYS     | SCHEDULED |
    |--------------------------+-------------------------------------+-------------+-----------|
    |    CLEANUP_ONLINE_PMO    | 23-OCT-24 05.11.43.187682 PM +00:00 |     SYS     | SCHEDULED |
    |--------------------------+-------------------------------------+-------------+-----------|
    |   CLEANUP_TAB_IOT_PMO    | 23-OCT-24 05.11.13.443853 PM +00:00 |     SYS     | SCHEDULED |
    |--------------------------+-------------------------------------+-------------+-----------|
Check: GoldenGate triggers - OK
Check: Logminer DDL triggers - OK
Check: Check sys public grants - OK
Check: Statistics gathering running - OK
Check: Optim dictionary upgrade parameter - OK
Check: Symlinks on oracle home path - OK
Check: Central Inventory - OK
Check: Queryable Inventory dba directories - OK
Check: Queryable Inventory locks - OK
Check: Queryable Inventory package - OK
Check: Queryable Inventory external table - OK
Check: Imperva processes - OK
Check: Guardium processes - OK
Check: Locale - OK

Refer to MOS Note 2680521.1 and debug log
/u02/app/oracle/cfgtoollogs/sqlpatch/sanity_checks_20241023_162017_119323/sanity_checks_debug_20241023_162017_119323.log

SQL Patching sanity checks completed on Wed 23 Oct 2024 04:21:18 PM UTC
[oracle@exanode01  OPatch]$

 

Everything looked clean, so I executed datapatch -version to update the registry information. However, it was hanging on for a long time. Upon checking the database alert log, I noticed complaints about the temp files under PDB$SEED.

I decided to kill the session since the temp files hadn't been moved. Now, the issue is that PDB$SEED has moved to the MOUNT state.

 

Datapatch output :



[oracle@exanode01 OPatch]$ pwd/u02/app/oracle/product/19.0.0.0/dbhome_2/OPatch

[oracle@exanode01 OPatch]$ ./datapatch -verboseSQL Patching tool version 19.24.0.0.0 Production on Tue Dec 10 22:15:39 2024
Copyright (c) 2012, 2024, Oracle.  All rights reserved.

Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_380836_2024_12_10_22_15_39/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...Unable to acquire sqlpatch global lock in EXCLUSIVE mode
because another datapatch session is currently running.
Waiting for that session to complete before continuing...



^CError: prereq checks failed!
DBD::Oracle::st execute failed: ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_LOCK", line 92
ORA-06512: at "SYS.DBMS_LOCK", line 108
ORA-06512: at line 9 (DBD ERROR: OCIStmtExecute) [for Statement "DECLARE
           lockmode INTEGER;
         BEGIN
           IF ? = 'SHARED' THEN
             lockmode := dbms_lock.s_mode;
           ELSE
             lockmode := dbms_lock.x_mode;
           END IF;
           ? := dbms_lock.request(?, lockmode, dbms_lock.maxwait, false);
         END;" with ParamValues: :p1='EXCLUSIVE', :p2='1', :p3='10737418441073741844167'] at /u02/app/oracle/product/19.0.0.0/dbhome_2/sqlpatch/sqlpatch.pm line 2134.


Please refer to MOS Note 1609718.1 and/or the invocation log
/u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_380836_2024_12_10_22_15_39/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Tue Dec 10 22:26:39 2024

We migrated this database using the RMAN backup and restore method. However, RMAN does not create temp files during the backup and restore process. As a result, PDB$SEED temp files were missing, which prevented the database from completing its operations in PDB$SEED.

 

Alert Log Error:



PDB$SEED(2):Pluggable database PDB$SEED closing
PDB$SEED(2):Increasing priority of 2 RS
PDB$SEED(2):JIT: pid 141609 requesting stop
PDB$SEED(2):Closing sequence subsystem (601820723513).
PDB$SEED(2):Buffer Cache flush started: 2
PDB$SEED(2):Buffer Cache flush finished: 2
2024-10-23T16:25:13.153882+00:00
Errors in file /u02/app/oracle/diag/rdbms/egdev_exa/DEV1/trace/DEV1_dbw0_59940.trc:
ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file
ORA-01110: data file 1026: '/u02/app/oracle/oradata/DEV/DEV/datafile/temp012024-05-06_05-30-49-610-AM.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2024-10-23T16:25:13.154139+00:00
Errors in file /u02/app/oracle/diag/rdbms/dev_exa/DEV1/trace/DEV1_dbw0_59940.trc:
ORA-01186: file 1026 failed verification tests
ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file
ORA-01110: data file 1026: '/u02/app/oracle/oradata/DEV/DEV/datafile/temp012024-05-06_05-30-49-610-AM.dbf'
2024-10-23T16:25:13.154193+00:00
File 1026 not verified due to error ORA-01157

Error from datapath Log:



[2024-10-23 16:25:12] sql_error_handler called: [2024-10-23 16:25:12] 1 ADD_TO_QUEUE PDB$SEED: DBD::Oracle::st bind_param failed: ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file
ORA-01110: data file 1026: '/u02/app/oracle/oradata/EGDEV/EGDEV/datafile/temp012024-05-06_05-30-49-610-AM.dbf' (DBD ERROR: OCILobCreateTemporary) [for Statement "DECLARE
             rec dba_registry_sqlpatch%ROWTYPE;
           BEGIN
             rec.patch_id := :patch_id;
             rec.patch_uid := :patch_uid;
             rec.patch_type := :patch_type;
             rec.action := :action;
             rec.description := SUBSTR(:description, '1', '100');
             rec.flags := :flags;
             rec.patch_descriptor := :patch_descriptor;
             rec.patch_directory := :patch_directory;
             rec.source_version := :source_version;
             rec.source_build_description := :source_build_description;
             rec.source_build_timestamp :=
               TO_TIMESTAMP(:source_build_timestamp, 'YYMMDDHH24MISS');
             rec.target_version := :target_version;
             rec.target_build_description := :target_build_description;
             rec.target_build_timestamp :=
               TO_TIMESTAMP(:target_build_timestamp, 'YYMMDDHH24MISS');
             sys.dbms_sqlpatch.set_patch_metadata(rec);

 

Resolving PDB$SEED in MOUNT State to Create Tempfiles.

After encountering an issue where PDB$SEED moved to the MOUNT state, preventing access to create the necessary tempfiles, I applied the following method to regain access to PDB$SEED and proceed with tempfile creation.


SQL> alter session set container=PDB$SEED;
SQL> alter session set "_oracle_script"=TRUE;
SQL> alter pluggable database pdb$seed close immediate instances=all;
SQL> alter pluggable database pdb$seed OPEN READ ONLY;

 
 

Recreating Tempfiles in PDB$SEED

To resolve the issue with missing temp files in PDB$SEED, follow these steps:

  1. Create New Tempfiles: Start by creating new tempfiles to ensure that the database functions as expected.
  2. Handle Old Tempfiles: For the old tempfiles that were not created, you can take them offline and drop them once they're no longer needed.

This approach ensures the smooth creation of temp files and the proper cleanup of any old or unnecessary ones.



SQL> alter database tempfile '/u02/app/oracle/oradata/DEV/DEV/datafile/temp012024-05-06_05-30-49-610-AM.dbf' offline;

Database altered.

SQL>  alter database tempfile '/u02/app/oracle/oradata/DEV/DEV/datafile/temp012024-05-06_05-30-49-610-AM.dbf' drop including datafiles;

Database altered.

SQL>

 
Once all the necessary changes have been made and everything is in order, you can proceed to rerun the datapatch -version command to confirm that the patching process has been completed successfully.


[oracle@exadevdb02-tsgb71 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.24.0.0.0 Production on Wed Oct 23 16:53:31 2024
Copyright (c) 2012, 2024, Oracle.  All rights reserved.

Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_258820_2024_10_23_16_53_31/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...

done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of interim SQL patches:
Interim patch 34086870 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)):
  Binary registry: Not installed
  PDB CDB$ROOT: Applied successfully on 17-JUL-22 09.18.13.470863 AM
  PDB DEV_PDB1: Applied successfully on 17-JUL-22 09.18.24.040293 AM
  PDB QA_PDB1: Applied successfully on 17-JUL-22 09.18.24.040293 AM
  PDB PDB$SEED: Applied successfully on 17-JUL-22 09.18.24.040293 AM
Interim patch 36414915 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)):
  Binary registry: Installed
  PDB CDB$ROOT: Not installed
  PDB DEV_PDB1: Not installed
  PDB QA_PDB1: Not installed
  PDB PDB$SEED: Not installed

Current state of release update SQL patches:
  Binary registry:
    19.24.0.0.0 Release_Update 240627235157: Installed
  PDB CDB$ROOT:
    Applied 19.16.0.0.0 Release_Update 220703022223 successfully on 17-JUL-22 09.18.13.460370 AM
  PDB DEV_PDB1:
    Applied 19.16.0.0.0 Release_Update 220703022223 successfully on 17-JUL-22 09.18.24.036238 AM
  PDB QA_PDB1:
    Applied 19.16.0.0.0 Release_Update 220703022223 successfully on 17-JUL-22 09.18.24.036238 AM
  PDB PDB$SEED:
    Applied 19.16.0.0.0 Release_Update 220703022223 successfully on 17-JUL-22 09.18.24.036238 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED DEV_PDB1 QA_PDB1
    The following interim patches will be rolled back:
      34086870 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870))
    Patch 36582781 (Database Release Update : 19.24.0.0.240716 (36582781)):
      Apply from 19.16.0.0.0 Release_Update 220703022223 to 19.24.0.0.0 Release_Update 240627235157
    The following interim patches will be applied:
      36414915 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915))

Installing patches...

Patch installation complete.  Total patches installed: 12

Validating logfiles...done
Patch 34086870 rollback (pdb CDB$ROOT): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/34086870/24803071/34086870_rollback_DEV_CDBROOT_2024Oct23_16_54_23.log (no errors)
Patch 36582781 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_DEV_CDBROOT_2024Oct23_16_54_24.log (no errors)
Patch 36414915 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/36414915/25700138/36414915_apply_DEV_CDBROOT_2024Oct23_16_54_24.log (no errors)
Patch 34086870 rollback (pdb PDB$SEED): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/34086870/24803071/34086870_rollback_DEV_PDBSEED_2024Oct23_16_58_32.log (no errors)
Patch 36582781 apply (pdb PDB$SEED): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_DEV_PDBSEED_2024Oct23_16_58_59.log (no errors)
Patch 36414915 apply (pdb PDB$SEED): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/36414915/25700138/36414915_apply_DEV_PDBSEED_2024Oct23_16_58_59.log (no errors)
Patch 34086870 rollback (pdb DEV_PDB1): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/34086870/24803071/34086870_rollback_DEV_DEV_PDB1_2024Oct23_16_58_32.log (no errors)
Patch 36582781 apply (pdb DEV_PDB1): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_DEV_DEV_PDB1_2024Oct23_16_58_34.log (no errors)
Patch 36414915 apply (pdb DEV_PDB1): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/36414915/25700138/36414915_apply_DEV_DEV_PDB1_2024Oct23_16_58_33.log (no errors)
Patch 34086870 rollback (pdb QA_PDB1): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/34086870/24803071/34086870_rollback_DEV_QA_PDB1_2024Oct23_16_58_31.log (no errors)
Patch 36582781 apply (pdb QA_PDB1): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_DEV_QA_PDB1_2024Oct23_16_58_33.log (no errors)
Patch 36414915 apply (pdb QA_PDB1): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/36414915/25700138/36414915_apply_DEV_QA_PDB1_2024Oct23_16_58_33.log (no errors)
SQL Patching tool complete on Wed Oct 23 17:04:31 2024

Conclusion

In conclusion, migrating a database and applying patches is a complex process that requires careful attention to detail. Although the sanity check showed no immediate issues, the missing PDB temporary file caused a failure during the datapath execution. This experience reinforced the importance of verifying all database components especially temp files before patching. By addressing the missing temp files and following the proper steps to recreate and clean them, I was able to resolve the issue and successfully complete the patching process. As a production DBA, these challenges are part of the job, and being proactive and methodical ensures smooth database operations and minimal downtime.

 
 
View full post