DEV Community: Kumaran Murugan

datapatch Fails in sqlpatch.pm: Fixing a Corrupted PDB$SEED Tempfile in Oracle 19c

Kumaran Murugan — Wed, 04 Nov 2026 06:13:35 +0000

The symptom

datapatch -verbose stopped with:

Unsupported named object type for bind parameter at $ORACLE_HOME/sqlpatch/sqlpatch.pm line 6153.

That message says very little. The useful clue was in the database alert log at the same time:

Read of datafile '/u01/oradata/CDB1/pdbseed/temp01<timestamp>.dbf' (fno 202) header failed with ORA-01206
ORA-01122: database file 202 failed verification check
ORA-01206: file is not part of this database - wrong database id
PDB$SEED(2): Cannot re-create tempfile /u01/oradata/CDB1/pdbseed/temp01<timestamp>.dbf, the same name file exists

What the alert log is telling you

ORA-01206 (wrong database id): the header of the PDB$SEED tempfile belongs to a different database.
"Cannot re-create tempfile... the same name file exists": when PDB$SEED opened, Oracle tried to rebuild the bad tempfile on its own. It couldn't, because a file with that name was already on disk.
The datapatch error: datapatch needs a working temporary tablespace in every container, including the seed. With none available, it hit an unhandled exception in sqlpatch.pm.

Likely cause

The tempfile name carries a timestamp from 2024, but PDB$SEED had already been patched successfully in an earlier cycle, so the header problem probably appeared after that, for example following a storage-level copy or a refresh of the database files between patch cycles. I can't prove when or how it happened, so treat this as the most likely explanation, not a confirmed one.

This is a useful lesson in itself: a seed that patched fine once can still break later.

The fix

I replaced the bad tempfile in PDB$SEED with a new one. This uses a hidden parameter, so read the cautions below first.

sqlplus / as sysdba

-- allow changes to the seed (session level only)
ALTER SESSION SET "_oracle_script" = TRUE;

ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE;

ALTER SESSION SET CONTAINER = PDB$SEED;

-- remove the corrupted tempfile
ALTER DATABASE TEMPFILE '/u01/oradata/CDB1/pdbseed/temp01<timestamp>.dbf' DROP INCLUDING DATAFILES;

-- add a clean one
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/CDB1/pdbseed/temp01.dbf'
  SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2G;

-- put the seed back as it was
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;

EXIT;

Then rerun datapatch:

$ORACLE_HOME/OPatch/datapatch -verbose

Verify

After the fix, the rerun patched all three containers, including the seed:

$ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 19.32.0.0.0 Production

Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED <PDB_NAME>
    The following interim patches will be rolled back:
      37847857 (OJVM RELEASE UPDATE: 19.28.0.0.250715)
    Patch 39472050 (Database Release Update : 19.32.0.0.260721):
      Apply from 19.28.0.0.0 to 19.32.0.0.0
    The following interim patches will be applied:
      39222882 (OJVM RELEASE UPDATE: 19.32.0.0.260721)

Patch installation complete.  Total patches installed: 9

Patch 37847857 rollback (pdb CDB$ROOT): SUCCESS
Patch 39472050 apply (pdb CDB$ROOT): SUCCESS
Patch 39222882 apply (pdb CDB$ROOT): SUCCESS
Patch 37847857 rollback (pdb PDB$SEED): SUCCESS
Patch 39472050 apply (pdb PDB$SEED): SUCCESS
Patch 39222882 apply (pdb PDB$SEED): SUCCESS
Patch 37847857 rollback (pdb <PDB_NAME>): SUCCESS
Patch 39472050 apply (pdb <PDB_NAME>): SUCCESS
Patch 39222882 apply (pdb <PDB_NAME>): SUCCESS

Then check the tempfiles across all containers:

SELECT con_id, file_id, file_name, tablespace_name, status
FROM cdb_temp_files ORDER BY con_id;

CON_ID 2 (the seed) should show the new temp01.dbf. Confirm the seed is back in its normal state:

SELECT name, open_mode, restricted FROM v$pdbs;

Finally, check that the patch was recorded:

SELECT patch_id, action, status, description
FROM dba_registry_sqlpatch ORDER BY action_time;

Cautions

_oracle_script is an underscore parameter. Oracle's own scripts use it, but using it by hand to change the seed is not something I'd do without a backup and a check of the relevant My Oracle Support note. In production, I'd raise a Service Request first.
I set it at session level only. ALTER SESSION means it was gone when I exited and never touched the spfile.
Take a backup first and try this in a non-production database.
Read-only afterwards. Always put PDB$SEED back in READ ONLY once you're done.

What I learned

A Perl-level error can hide a storage problem. When datapatch fails inside sqlpatch.pm, read the alert log at the same moment.
Check the seed's tempfile, not just the root. A bad tempfile in PDB$SEED can block patching even when everything else looks healthy.
Open your PDBs before running datapatch. It only patches PDBs that are open, and it skips closed ones.
Files can change between patch cycles. A seed that patched fine before can still break after copies or refreshes, so it's worth checking tempfile headers before a patching window.

Have you seen this error after patching a cloned or refreshed CDB? I'd like to hear whether the cause matched.

Kumaran Murugan is an Oracle Apps DBA and OCI Cloud Administrator with 5+ years of experience in Oracle E-Business Suite, database migrations, and OCI infrastructure. Connect on LinkedIn.

EBS 12.2.15: Fixing "ERROR: This env should be sourced from apps !" When Sourcing the Patch File System

Kumaran Murugan — Sun, 25 Oct 2026 15:50:06 +0000

I recently built a fresh Oracle E-Business Suite 12.2.15 environment from the OCI Marketplace image, following Oracle's installation document (KB849587). The services came up cleanly. The problem appeared the first time I tried to prepare for patching.

The symptom

When I sourced the environment file and chose the patch file system, it failed immediately:

Please choose the environment file you wish to source [R/P]:P
  Sourcing the PATCH File System ...
ERROR: This env should be sourced from apps !
[oracle@ebsdemo12-2-15 ~]$

The application was up and running, so the environment itself was healthy. Only the patch file system would not source.

Background: two file systems

EBS 12.2 uses online patching with two application file systems, a run file system and a patch file system. When you source the environment file, you choose which one you want:

R sets up the environment for the run file system, which is what users are connected to.
P sets up the environment for the patch file system, which adop needs before you apply a patch.

If the patch file system won't source, you can't start a patching cycle.

What fixed it

I ran AutoConfig directly against the patch file system's context file, from the AD_TOP/bin directory:

perl adconfig.pl contextfile=/u01/install/APPS/fs2/inst/apps/EBSDB_apps/appl/admin/EBSDB_apps.xml

Once AutoConfig finished, I sourced the environment file again, chose P, and the patch environment loaded normally.

Why it happens

The error is raised by the environment-sourcing logic, before any patch is involved. So this is an environment configuration problem, not a patch problem.

AutoConfig regenerates the environment and configuration files for a file system from its context file. Running it against the patch file system's context file was the only change I made, and it resolved the error. The most likely cause is that the environment files for the patch file system were stale or incomplete after the initial build, while the run file system had been fully configured.

A Marketplace environment is provisioned onto a new host, so the two file systems don't necessarily get configured at the same time. I haven't proven this from the internals, so treat it as the most likely explanation and verify it on your own system with the checks below.

How to check your own system

Rule out the simple cause. Run whoami and ls -ld on the patch file system directory. If your install uses a separate application OS user, make sure you are sourcing as that user.
Compare the context files. Check that key values, such as the hostname, match between the run and patch context files:

   grep -i s_hostname <run context file>
   grep -i s_hostname <patch context file>

Check the AutoConfig log. After the run, look in the patch file system's admin/log directory for a clean adconfig.log.
Compare file timestamps. Look at the environment files in the patch file system before and after the run to see what AutoConfig regenerated.

What I learned

Read the error as an environment problem. The message appears before adop starts, so look at the file system's configuration first.
Run AutoConfig on both file systems. On a new or cloned environment, I now treat this as a pre-patching checklist item, not just a fix for when something breaks.
Don't force the environment. Regenerating it from the context file is cleaner than editing generated files by hand.

Have you hit this error on a Marketplace or cloned EBS environment? I'd like to know whether the cause was the same.

Kumaran Murugan is an Oracle Apps DBA and OCI Cloud Administrator with 5+ years of experience in Oracle E-Business Suite, database migrations, and OCI infrastructure. Connect on LinkedIn.

Provisioning an Oracle Autonomous AI Database and Connecting Securely with a Cloud Wallet

Kumaran Murugan — Mon, 19 Oct 2026 06:54:16 +0000

Spinning up a database used to mean planning hardware, installing software, and configuring backups. With Oracle Autonomous AI Database on OCI, you can have a working database in minutes. The step that trips most people up isn't creating it, though. It's connecting to it securely from a client tool.

In this walkthrough, I provision a Serverless Autonomous AI Database, connect to it from SQL Developer using a Cloud Wallet, and then stop and terminate it so it doesn't keep consuming resources.

What you'll need

An OCI account or a LiveLabs sandbox
SQL Developer installed on your machine

Step 1: Start the create wizard

In the OCI Console, open Oracle Database → Autonomous AI Database and click Create. Choose the Serverless deployment and give the database a display name and a database name.

Step 2: Choose workload type and size

Pick the workload type that matches your use case (data warehouse, transaction processing, JSON, or APEX). Then set the compute (ECPU count) and storage. Autonomous Database handles patching, tuning and scaling for you, so sizing is the main decision.

Step 3: Set the ADMIN password

The wizard creates an ADMIN user. Choose a strong password and store it somewhere safe. You will need it when you connect from SQL Developer.

Step 4: Set network access and backups

For a learning environment, I used Secure access from everywhere. For anything real, use allowed IPs and VCNs or a private endpoint instead. I also reviewed the backup retention setting here, since it is chosen at creation time.

Add a contact email for operational notifications, then click Create.

Step 5: Wait for Available

The database moves from Provisioning to Available. This usually takes a few minutes.

Step 6: Download the Cloud Wallet

From the database details page, open Database connection and download the wallet. The wallet is a zip file with the credentials and connection details your client needs. Set a wallet password when prompted.

Step 7: Connect from SQL Developer

Create a new connection and enter:

Username: admin
Password: your ADMIN password
Connection Type: Cloud Wallet
Configuration File: your downloaded wallet zip
Service: <dbname>_high

Click Test. You should see Status: Success.

Which service name?

Each database exposes several services. As a rule of thumb:

_high: highest priority and resources, fewest concurrent sessions
_medium: a balance of the two
_low: lowest priority, most concurrency

Step 8: Stop the database

When you're done, open More actions → Stop. Stopping pauses compute billing while keeping the database definition.

Step 9: Terminate it

For a lab database you no longer need, choose More actions → Terminate and type the database name to confirm. The name confirmation exists to prevent accidental deletion.

What I learned

The wallet is the key concept. A client connects with the wallet and a service name, not a host and port.
Service names are a workload choice. _high, _medium and _low let you trade throughput against concurrency.
Network access is easy to leave too open. "Secure access from everywhere" is fine in a sandbox, but restrict it for real workloads.
Stop and terminate idle databases. Cleanup is part of the workflow, not an afterthought.

If you're preparing for the OCI Autonomous Database certification, this is a good exercise to run end to end at least once.

What do you use to connect to Autonomous Database: SQL Developer, Database Actions, or something else?

Kumaran Murugan is an Oracle Apps DBA and OCI Cloud Administrator with 5+ years of experience in Oracle E-Business Suite, database migrations, and OCI infrastructure. Connect on LinkedIn.

Flashback Database with a Guaranteed Restore Point

Kumaran Murugan — Wed, 14 Oct 2026 05:17:17 +0000

Before a patch, an upgrade, or a risky schema change, the most useful question a DBA can ask is: "How fast can I get back if this goes wrong?" A full RMAN restore can take hours. Flashback Database with a guaranteed restore point can take minutes.

In this walkthrough, I create a guaranteed restore point, simulate an accidental loss, and flash the database back to the exact state it was in before.

Why a guaranteed restore point?

A normal restore point can age out as the flashback retention window moves forward. A guaranteed restore point tells Oracle to keep every flashback log needed to return to that point, however long it takes. The trade-off is that those logs consume space in the Fast Recovery Area (FRA) until you drop the restore point.

Prerequisites

Flashback Database needs:

The database in ARCHIVELOG mode
A configured and sized Fast Recovery Area
Flashback logging enabled

Step 1: Enable ARCHIVELOG mode

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ARCHIVE LOG LIST;

ARCHIVE LOG LIST should now show Archive Mode with automatic archival enabled.

Step 2: Configure the FRA and enable Flashback

SELECT FLASHBACK_ON FROM V$DATABASE;   -- NO

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/path/to/fast_recovery';
ALTER DATABASE FLASHBACK ON;

SELECT LOG_MODE, FLASHBACK_ON, NAME, OPEN_MODE FROM V$DATABASE;

Set the size parameter before the destination parameter. Size the FRA for your change rate. Flashback logs live here, and a full FRA can stall the database.

Step 3: Create the guaranteed restore point

CREATE TABLESPACE tbs1 DATAFILE 'tbs1_data.dbf' SIZE 1m;

CREATE RESTORE POINT demo2 GUARANTEE FLASHBACK DATABASE;

This is the checkpoint. Everything after this moment can be undone.

Step 4: Simulate the accident

I dropped the test tablespace, then confirmed it was gone:

DROP TABLESPACE tbs1;
SELECT * FROM V$TABLESPACE;   -- 20 rows, TBS1 missing

Step 5: Flash the database back

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT demo2;
ALTER DATABASE OPEN READ ONLY;

Opening read-only first lets you check the result before committing to it:

SELECT * FROM V$TABLESPACE;   -- 21 rows, TBS1 is back

Once you're satisfied, finish the recovery:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;

Step 6: Clean up

ALTER DATABASE FLASHBACK OFF;
DROP RESTORE POINT demo2;

Don't skip this step. A guaranteed restore point keeps holding FRA space until it is dropped.

What I learned

Flashback is much faster than a full restore. It applies flashback logs rather than restoring datafiles.
Read-only first is a cheap safety check. You can verify the recovered state before opening with RESETLOGS, which is irreversible.
FRA sizing is the real operational risk. An undersized FRA is the most common way a restore point goes wrong.
Clean up after yourself. A forgotten guaranteed restore point quietly eats storage.

I now treat a guaranteed restore point as a standard step in change windows for patching and upgrades, because it gives you a fast, rehearsed way back.

Kumaran Murugan is an Oracle Apps DBA and OCI Cloud Administrator with 5+ years of experience in Oracle E-Business Suite, database migrations, and OCI infrastructure. Connect on (http://www.linkedin.com/in/kumaranmurugan).

How to Restore and Clone an OCI DB System from an Automatic Backup

Kumaran Murugan — Thu, 24 Sep 2026 07:31:57 +0000

If you're running Oracle databases on OCI's Base Database Service, one of the most useful recovery skills to have is knowing how to spin up a completely new DB system from an existing automatic backup — whether you're recreating a test environment, recovering from an accidental termination, or just validating that your backups actually work.

In this walkthrough, I'll take you through the full process: terminating an existing DB system, and rebuilding it from scratch using nothing but an automatic backup.

Why this matters

OCI decouples compute and storage lifecycle from your actual data. That means even if a DB system is gone, your data isn't — as long as a backup exists, you can rebuild the entire compute/storage/networking stack around it. This is a pattern worth practicing before you need it in a real incident.

Step-by-step walkthrough

1. Locate your source backup
Navigate to your DB system's Backups tab and review the available automatic incremental backups, each with its own start/end timestamp.

2. Start the restore
Select the backup you want and choose "Create database" from its action menu — this launches the Create Database from Backup wizard.

3. Choose what to restore
Decide whether to include all PDBs from the backup, or select specific ones.

4. Pick your target compartment
The new DB system doesn't have to go back into the same compartment — useful if you're rebuilding into a test/dev compartment instead of production.

5. Configure compute shape
Choose your shape series (AMD, Intel, or Ampere) and OCPU count.

6. Configure storage
Pick your storage management software (Oracle Grid Infrastructure or Logical Volume Manager) and performance tier.

7. Add SSH access and licensing
Paste or upload your SSH key, and choose License Included or Bring Your Own License.

8. Set up networking
Select your VCN, subnet, and hostname prefix.

9. Set the database name and credentials
Name your database, then provide the SYS password — plus the source database's TDE wallet/RMAN password, since that's required to unlock the backup.

10. Submit and monitor
The whole operation runs as a single Work Request, so you can track provisioning, networking setup, and the actual database restore in one place, right through to "Available."

What I learned

A few things stood out to me doing this hands-on:

The TDE wallet/RMAN password is non-negotiable — without the source database's original password, the backup can't be unlocked, so document it defensively.
Recovery area storage is read-only and auto-derived from the backup — you don't configure it directly.
You get real flexibility on where the new system lands — different compartment, VCN, even subnet — which makes this a genuinely practical way to spin up isolated test/dev copies of production data without touching the original.

If you're studying for the OCI Architect certifications or just want to build confidence in your OCI story, I'd recommend running through this exact exercise once in a sandbox — it's the kind of thing that's much better to have already practiced before you need it under pressure.

Kumaran Murugan is an Oracle Apps DBA and OCI Cloud Administrator with 5+ years of experience in Oracle E-Business Suite, Database migrations, and OCI infrastructure. Connect on www.linkedin.com/in/kumaranmurugan.