Oracle Data Pump enhancement

🚀 Take Control of Oracle Data Pump Imports with New Enhancements! ORACLE AI Database

Oracle Data Pump just got even more powerful, making migrations to Oracle Autonomous Database (ADB) or on-premises smoother, faster, and more flexible.
Here are some key highlights

Smarter Control and Compression
• Specify index compression on import using
TRANSFORM=INDEX_COMPRESSION_CLAUSE:COMPRESS ADVANCED LOW
• Fine-tune space usage and performance during migrations.

Flexible Object Inclusion
• Include/exclude objects in the same export or import job: expdp hr DIRECTORY=dpump_dir1 DUMPFILE=exp%u.dmp SCHEMAS=hr,oe INCLUDE=table EXCLUDE=statistics
• The INCLUDE parameter runs first, then EXCLUDE removes specific objects.

Simplified Cloud Imports
Import from Oracle Object Storage directly to ADW / ATP (Shared or Dedicated) with parallelism, transforms, and object-level control.

Performance & Parallelism
• Use PARALLEL=n for inter-table and intra-table parallelism
• Parallelize TTS metadata operations, resume jobs, and import JSON natively
• Optimize with accurate DBMS_STATS gathering before and after operations

Compression, Security, and Efficiency
• Use COMPRESSION=ALL and COMPRESSION_ALGORITHM=MEDIUM for balanced performance
• Verify data integrity with Data Pump checksum (21c and above)
• Import over database links — no dumpfiles required!

Best Practices for Autonomous Database
• Use SCHEMA or TABLE mode exports for ADB
• Set proper STREAMS_POOL_SIZE and MAX_DATAPUMP_JOBS_PER_PDB
• Prefer SecureFile LOBs for parallel I/O and compression
• Use AL32UTF8 character set for maximum compatibility

Bottom line:
Oracle Data Pump continues to evolve with every release, offering more control, scalability, and efficiency for your migrations. Whether you’re moving to Oracle Cloud or optimizing on-premises, following these best practices will make your Data Pump jobs smoother and faster.

Oratop utility, A power tool for DBA

 oratop Command in Oracle

• Alireza Kamrani

Overview

The oratop command is a real-time Oracle database performance monitoring tool — similar to the Linux top command, but designed specifically for Oracle Database environments. It provides a dynamic, continuously refreshing view of key performance metrics at the instance, session, and SQL levels, making it an essential utility for DBAs who need quick situational awareness without the overhead of complex diagnostics tools like Enterprise Manager.

Details:

Tool name: oratop

Purpose: Real-time performance monitoring for Oracle Database

Source: Developed by Oracle Support (MOS script)

Availability: Downloadable from My Oracle Support (MOS Note 1500864.1)

Supported Versions: Oracle Database 11g and higher (including 19c and 23ai)

Installation & Setup:

oratop is a Perl-based script that connects to the database through SQL*Plus.

You can download it from My Oracle Support (MOS Note 1500864.1).

Steps:

         1. Download the latest version (oratop_latest.zip) from MOS.

         2. Unzip the file:

unzip oratop_latest.zip

3. Make it executable:

chmod +x oratop

4. Ensure sqlplus is in your PATH and environment variables (ORACLE_HOME, ORACLE_SID) are set.

Usage Examples

Local connection:

oratop / as sysdba

Remote connection:

oratop user/password@tns_alias

For RAC:

oratop /@service_name inst#

Example: oratop /@orcl1 2

Interface Overview

Once launched, oratop displays a refreshing console interface divided into several sections:

1. Header Section — Instance summary (database name, version, uptime, load, sessions, wait classes)

2. Activity Section — Real-time CPU, session count, I/O, TPS

3. Top Sessions / SQL Section — SID, username, SQL ID, event, CPU, I/O usage

Interactive commands:

s – Session view

i – Instance summary

q – Quit

Common Options

oratop / as sysdba 5 10 → Refresh every 5 seconds, 10 iterations

-f → Flat file mode (output to text)

-n → Non-interactive batch mode

-i → Instance number for RAC

-a → Display all RAC instances

-h → Help and usage options

Example Output:

oratop:  Release 15.0 Oracle 19.18.0.0.0

DBNAME  INST_ID  HOST  UP: 3 days  Load: 2.3

CPU: 85% Busy  IO: 15% Wait  Sess: 120  Active: 12

Event: "log file sync"               Waits: 123/s

Event: "db file sequential read"     Waits: 98/s

Top Active Sessions:

SID  USERNAME SQL_ID EVENT                      CPU%  WAIT%

123  APPUSER 2gq1b78xj..  db file sequential read     30    20

345  SYS     5j3fkw2h9..  log file sync     25    15

Licensing and Benefits

oratop does not require the Oracle Diagnostic Pack license since it uses only standard dynamic performance views (v$, gv$). It is safe for production environments.

Benefits:

• Lightweight and agentless

• Real-time performance visibility

• RAC-aware monitoring

• Ideal for quick triage

• Customizable refresh and filters

Summary Table

Feature	Description
Type	Command-line real-time monitor
Language	Perl
Connection	SQL*Plus
Supports RAC	Yes
License	Free (no Diagnostic Pack needed)
Best for	Quick real-time instance and session performance analysis

Conclusion

oratop is an essential command-line tool for Oracle DBAs who need immediate insights into system performance without heavy GUI overhead. It bridges the gap between command-line simplicity and Enterprise Manager’s diagnostic depth — empowering database administrators to act fast, diagnose bottlenecks, and maintain high system performance.

About Consistent and Inconsistent RMAN Backups

Use the RMAN BACKUP command to create both consistent and inconsistent backups.

The RMAN BACKUP command supports backing up the following types of files:

• Data files and control files
• Server parameter file
• Archived redo logs
• RMAN backups

Although the database depends on other types of files, such as network configuration files, password files, and the contents of the Oracle home, you cannot back up these files with RMAN. Likewise, some features of Oracle Database, such as external tables, may depend upon files other than the data files, control files, and redo log. RMAN cannot back up these files. Use general-purpose backup software such as Oracle Secure Backup to protect files that RMAN does not support.

When you execute the BACKUP command in RMAN, the output is always either one or more backup sets or one or more image copies. A backup set is an RMAN-specific proprietary format, whereas an image copy is a bit-for-bit copy of a file. By default, RMAN creates backup sets.

 

About Consistent RMAN Backups

A consistent backup occurs when the database is in a consistent state. You can use the BACKUP command to make consistent backups of the database.

A database is in a consistent state after being shut down with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands. A consistent shutdown guarantees that all redo has been applied to the data files. If you mount the database and make a backup at this point, then you can restore the database backup later and open it without performing media recovery. 

.But you will, of course, lose all transactions that occurred after the backup was created.

 

About Inconsistent RMAN Backups

Any database backup that is not consistent is an inconsistent backup. A backup made when the database is open is inconsistent, as is a backup made after an instance failure or SHUTDOWN ABORT command.

When a database is restored from an inconsistent backup, Oracle Database must perform media recovery before the database can be opened, applying changes from the redo logs that took place after the backup was created.

Note:

RMAN does not permit you to make inconsistent backups when the database is in NOARCHIVELOG mode. If you employ user-managed backup techniques for a NOARCHIVELOG database, then you must not make inconsistent backups of this database.

If the database runs in ARCHIVELOG mode, and you back up the archived redo logs and data files, inconsistent backups can be the foundation for a sound backup and recovery strategy. Inconsistent backups offer superior availability because you do not have to shut down the database to make backups that fully protect the database.

 

About Online Backups and Backup Mode

You can create RMAN backups or user-managed backups.

When performing a user-managed backup of an online tablespace or database, an operating system utility can back up a data file at the same time that the database writer (DBWR) is updating the file. It is possible for the utility to read a block in a half-updated state, so that the block that is copied to the backup media is updated in its first half,

 

while the second half contains older data. This type of logical corruption is known as a fractured block, that is, a block that is not consistent with an SCN. If this backup must be

restored and the block requires recovery, then recovery fails because the block is not usable.

 

For third-party snapshot technologies, you must use one of the following techniques to eliminate the risk of creating fractured blocks:

• Ensure that the snapshot technology complies with Oracle requirements for online backups
• Take the database or data files offline
• Place the database in backup mode before using a third-party snapshot backup

The RECOVER…SNAPSHOT TIME method of recovering a database to a point in time using a particular snapshot is desupported in Oracle AI Database 26ai.

Instead of RECOVER…SNAPSHOT TIME, Oracle recommends that you use ALTER DATABASE BEGIN/END BACKUP before and after creating the storage snapshot of the data files and then use RECOVER …. UNTIL TIME to a specific timestamp or system change number (SCN) after the END BACKUP completion time. Oracle recommends that ALTER DATABASE BEGIN/END BACKUP always be used when performing snapshots on a running database to ensure data recovery integrity. Archived log redo logs must be separately backed up and restored for recovery operations.

Unlike user-managed tools, RMAN does not require extra logging or backup mode because it knows the format of data blocks. RMAN is guaranteed not to back up fractured blocks. During an RMAN backup, a database server session reads each data block and checks whether it is fractured by comparing the block header and footer. If a block is fractured, then the session rereads the block. If the same fracture is found, then the block is considered permanently corrupt. Also, RMAN does not need to freeze the data file header checkpoint because it knows the order in which the blocks are read, which enables it to capture a known good checkpoint for the file.

About Backup Sets

When you execute the BACKUP command in RMAN, you create one or more backup sets or image copies. By default, RMAN creates backup sets regardless of whether the destination is disk or a media manager.

Note:

Data file backup sets are typically smaller than data file image copies and take less time to write.

 

Ø  how RMAN can take a consistent backup of a running (open) database even when heavy DML is happening??

 

1. Key idea — “Consistent image through SCN”

Every Oracle block (in datafiles, undo, etc.) carries a System Change Number (SCN) — a monotonically increasing version stamp of the database at that moment.
RMAN uses these SCNs to guarantee that every block in the backup reflects a consistent point in time (a checkpoint-consistent image).

When you run:

BACKUP DATABASE;

on an open database, RMAN is really performing an online (hot) backup, meaning users can continue DMLs (INSERT, UPDATE, DELETE, COMMIT).

 2. RMAN works with read-consistent block images

When RMAN reads a block from a datafile:

• If that block has already been modified after the backup started (its SCN > backup checkpoint SCN),
• RMAN does not use that dirty block directly,
• Instead, it asks Oracle’s block recovery mechanism (via the DBWR and redo apply engine) to reconstruct the block image as of the backup SCN using the redo + undo information.

This is done by Oracle’s “read-consistent block” mechanism.

So, each block RMAN writes to the backup set is the version that was valid at the backup’s checkpoint SCN — even if users were modifying it at that very moment.

3. How redo logs ensure recoverability

During online backup:

• Oracle continuously writes redo entries for all DMLs.
• RMAN keeps track of the lowest SCN of all datafile backups (the checkpoint SCN) and also ensures all redo up to that SCN is included in the backup.

So, when you later restore and recover:

1.      RMAN restores the datafiles from the backup sets (consistent as of checkpoint SCN).

2.      Then applies archived + online redo logs to bring the database forward to the desired point in time (for example, until last committed transaction).

4. Backup checkpoint concept

Each datafile backup has a checkpoint SCN (start/end):

• Checkpoint SCN = point in time that file content is consistent with.
• RMAN records these in the controlfile and catalog.

During restore:

• Oracle uses these SCNs to know which redo must be applied to make all files consistent with each other.

5. Internal protection (CKPT, LGWR, DBWR, SMON)

Internally:

• CKPT updates headers with checkpoint SCNs.
• DBWR writes dirty buffers to disk regularly.
• LGWR ensures redo is flushed before commit.
• SMON coordinates instance recovery if needed.

RMAN leverages all of this to maintain backup consistency — it does not “freeze” datafiles, but logically guarantees consistency via SCN management and redo.

6. Summary – The consistency triangle

Component	Role	Purpose
SCN	Global timestamp	Defines backup consistency point
Redo	Transaction log	Replays changes to make blocks consistent
Undo	Old versions	Used to reconstruct consistent read image
RMAN	Orchestrator	Reads blocks, manages checkpoint SCNs, invokes kernel consistency logic

7. Example of timeline

T1: RMAN begins backup, notes checkpoint SCN = 1000

T2: User commits a transaction (SCN 1005)

T3: RMAN reads block that was updated at SCN 1005

     -> Oracle reconstructs block as it was at SCN 1000

T4: RMAN writes the consistent block to backup set

So final backup is a point-in-time image as of SCN 1000, even though DML continued during the backup.

8. Notes:

• This mechanism is much safer than user-managed hot backups (which require BEGIN/END BACKUP mode).
• Works only in ARCHIVELOG mode.
• RMAN uses block-level media recovery and backup optimization to minimize overhead.

Tuning and Controlling I/O for Oracle RMAN

Are you having trouble with I/O throughput on the network for transferring RMAN backups? How to adjust RMAN phases?

 

How to controlling RMAN pressure:

What is the RATE parameter in Oracle RMAN?

RATE Channel Parameter:

You can use the RATE parameter to set an upper limit for bytes read so that RMAN does not consume excessive disk bandwidth and degrade online performance. Essentially, RATE serves as a backup throttle.

In the ALLOCATE and CONFIGURE CHANNEL commands, the RATE parameter specifies the bytes per second that are read on a channel. For example, if you set RATE 1500K, and if each disk drive delivers 3 megabytes per second, then the channel leaves some disk bandwidth available to the online system.

 

RATE is a channel parameter in RMAN (Recovery Manager) that limits the I/O throughput of that channel — specifically, it sets a maximum data transfer rate for backup, restore, or recovery operations.

It’s expressed in bytes per second (B/s), kilobytes per second (K/s), megabytes per second (M/s), or gigabytes per second (G/s).

 

Syntax

You use it inside the ALLOCATE CHANNEL or CONFIGURE CHANNEL command.

RMAN> ALLOCATE CHANNEL c1 DEVICE TYPE DISK RATE 50M;

This example limits the channel c1 to a maximum of 50 MB/s of I/O throughput.

 

 Why DBA uses the RATE parameter

It’s not for performance tuning, but for I/O throttling and workload control.

Common use cases:

1.      Avoid I/O contention

o    When backups run on a production system during business hours, limiting the rate prevents RMAN from consuming too much disk or network bandwidth.

o    Example: Run incremental backups during daytime with RATE 10M.

2.      Prevent network saturation

o    For tape or network backups, you can throttle backup traffic to avoid impacting other network operations.

3.      Control load on standby or DR sites

o    When restoring or duplicating databases over network links, RATE ensures stable throughput and predictable recovery time.

4.      Testing and Benchmarking

o    DBAs can simulate slower disk or network speeds to test restore performance, or tune parallelism more safely.

 

How RATE interacts with CHANNELS

• RATE applies per channel, not globally.
• So, if you have multiple channels, total throughput ≈ RATE × number of channels.

Example:

RUN {

  ALLOCATE CHANNEL c1 DEVICE TYPE DISK RATE 100M;

  ALLOCATE CHANNEL c2 DEVICE TYPE DISK RATE 100M;

  BACKUP DATABASE;

}

→ Total potential throughput ≈ 200 MB/s.

 

Notes:

Behavior	Description
Upper limit only	RATE defines a maximum. RMAN may transfer less if resources are unavailable.
Does not affect compression or encryption CPU time	Only I/O bandwidth is throttled. CPU use for compression/encryption is unaffected.
Use with caution	Over-throttling can unnecessarily increase backup or restore time.
In Data Guard environments	When doing DUPLICATE FROM ACTIVE DATABASE, RATE can control how much redo or backup traffic flows across the network.

  

Example use cases:

Example 1: Controlled Network Restore

RUN {

  ALLOCATE CHANNEL c1 DEVICE TYPE sbt RATE 25M;

  RESTORE DATABASE;

}

→ Useful for restoring over slow network (25 MB/s cap).

Example 2: Configure Default Rate

CONFIGURE CHANNEL DEVICE TYPE DISK RATE 75M;

→ All future channels will inherit this limit automatically.

 

When not to use it

• High-performance Exadata / Flash-based systems where storage bandwidth is already managed and backups run during low-load windows.
• When tuning — use parallelism, buffer size (MAXPIECESIZE, FILES PER SET), or BACKUP AS COMPRESSED BACKUPSET instead.

 

Summary:

Parameter	Purpose	Typical Use
RATE	Limits I/O throughput per channel	Control load, avoid I/O or network contention
Default	Unlimited	RMAN uses full available bandwidth
Syntax	ALLOCATE CHANNEL … RATE 50M;	Limits to 50 MB/s per channel

 

Removing the RATE Parameter from Channel Settings

The RATE parameter on a channel is intended to reduce, rather than increase, backup throughput so that more disk bandwidth is available for other database operations. If the backup is not streaming to tape, then confirm that the RATE parameter is not set.

To remove the RATE parameter:

1.      Examine your backup script.

2.      Do one of the following:

·         If the backup is in a RUN command, then remove the RATE parameter, if it is specified, from the ALLOCATE command. Skip the remaining steps.

·         If the backup is not in a RUN command, then start RMAN, connect to the target database, and proceed to the next step.

3.      Execute the SHOW ALL command to show the currently configured settings.

4.      Remove the RATE parameter, if it is set, from the CONFIGURE CHANNEL command.

 

********* Tuning **********  

Throughput Vs. Bandwidth  

Distinction between throughput and bandwidth is critical in understanding and tuning RMAN backup performance. Let’s compare them in the specific context of Oracle RMAN I/O operations (whether disk or tape, or network-based like Data Domain or SBT channels).

Definition and Core Difference

Concept	Definition	Focus	Measurement
Bandwidth	The maximum capacity of a communication or storage path (disk, network, or SAN) to transfer data per unit time.	Capability / limit of path	MB/s or Gb/s
Throughput	The actual data rate achieved during the RMAN backup.	Real performance / achieved transfer	MB/s

 

So,

Bandwidth = Potential,
Throughput = Reality.

 

Example in RMAN Backup Context

Scenario:                                                                                     

• You back up an Oracle database using RMAN to a NAS target.
• The network interface is 10 GbE (≈ 1,250 MB/s bandwidth).
• RMAN reports 400 MB/s backup speed.

Metric	Value	Meaning
Bandwidth	1,250 MB/s	Physical or network channel capacity
Throughput	400 MB/s	Actual RMAN data transfer rate achieved
Utilization	32%	Indicates potential bottlenecks (CPU, disk I/O, RMAN compression, etc.)

 

Factors That Limit Throughput (While Bandwidth is High)

Layer	Possible Limiter	Example
RMAN / CPU	CPU compression overhead	Using BACKUP AS COMPRESSED BACKUPSET
Disk I/O	Slow source or destination disk	Spinning disks or overloaded ASM diskgroup
Channel Parallelism	Too few RMAN channels	e.g. only 1 channel on a 16-core system
Network	Latency or TCP window issues	Especially over WAN
Media Manager (SBT)	Slow tape or DD Boost plugin limits	Deduplication or encryption overhead
OS Buffering	Insufficient I/O buffers	Check LARGE_POOL_SIZE and db_writer_processes impact

 

Be careful that RMAN has 3 Phases and I/O can be bottleneck on any phases:

1.      Read Phase

A channel reads blocks from disk into input I/O buffers.

2.      Copy Phase

A channel copies blocks from input buffers to output buffers and performs additional processing on the blocks.

3.      Write Phase

A channel writes the blocks from output buffers to storage media. The write phase can take either of the following mutually exclusive forms, depending on the type of backup media:

 

How RMAN Reports Throughput

RMAN backup logs show:

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:30

channel ORA_DISK_1: throughput 200 MB/s

This “throughput” is effective transfer speed across all I/O layers combined.

You can also query:

SELECT INPUT_BYTES_DISPLAY, OUTPUT_BYTES_DISPLAY, OUTPUT_BYTES_PER_SEC_DISPLAY FROM V$BACKUP_ASYNC_IO;

to analyze detailed I/O throughput per file.

 

Tuning Goal

• Bandwidth cannot be increased by RMAN itself (it’s hardware/network capacity).
• Throughput can be improved by optimizing parallelism, compression, and I/O path efficiency.

 

Quick Tips to Increase RMAN Throughput:

Area	Recommendation
Channels	Use multiple RMAN channels (ALLOCATE CHANNEL or DEVICE TYPE DISK PARALLELISM N, controlling by RATE param)
Compression	Disable compression for performance benchmarking
Buffers	Increase LARGE_POOL_SIZE or use BACKUP_DISK_IO_SLAVES, use Async I/O
ASM/Filesystem	Separate read/write I/O paths
Network	Enable jumbo frames and optimize TCP buffers
Target Device	Use DD Boost or fast disk staging

 

Simple Analogy

Concept	Analogy
Bandwidth	The width of a highway (how many lanes)
Throughput	The number of cars actually passing per second
Bottleneck	A traffic jam (one slow lane blocks others)

  

Summary

Metric	Definition	Controlled By	RMAN Relevance
Bandwidth	Maximum data capacity of I/O path	Hardware / Network	Defines upper limit of backup performance
Throughput	Actual achieved data rate	RMAN + OS + I/O stack	Real indicator of backup efficiency
Goal	Throughput ≈ Bandwidth	Tune RMAN, parallelism, buffers	Optimize backup performance

 

 

********************Alireza Kamrani***********************