Why do the Export and Import utilities exist, and why should you bother using them?
With so many other utilities becoming available and the changes to Oracle Enterprise Manager, you would think that these utilities would slowly disappear. Perhaps in time they will, but it is much more likely that they will be fully incorporated in other products.
For the time being, these utilities do exist, are free, and are part of the Oracle Oracle Certified Professional (OCP) backup and recovery exam.
Backup Types
Let us review some basic information. You can perform two types of backups:
logical and
physical.
Logical backups copy information from your database to a file with no regard to the physical location of data.
Physical backups copy information from your database to a file with physical block location information of the database stored as part of the backup file. Export will perform a logical backup of database objects and/or data to a physical binary file in a special Oracle format.
Import will then read the file created by Export and insert database objects and/or data into an Oracle database.
The following diagram explains the use of Export and Import utilities:
The image represents a database backup architecture labeled as Database - orc 1, which suggests an Oracle database instance. It depicts five key backup and recovery components in the form of stacked disks within a cylindrical database representation. Below is an analysis of each component:
Transportation: Easily transports objects from one database to another.
This refers to data transportability, which enables the movement of database files or tablespaces between different Oracle database instances.
In Oracle, Transportable Tablespaces (TTS) and Data Pump (with EXPDP and IMPDP) are common methods for transporting data efficiently.
Base Level Definition: Create a base level definition of your database.
Likely represents the full database backup, which serves as the foundational backup copy.
In Oracle, this could be implemented using RMAN (Recovery Manager) full backups, ensuring a complete baseline for recovery.
Disk Fragmentation: Eliminate disk fragmentation.
This highlights the issue of disk-level fragmentation, which can impact database performance by causing inefficient I/O operations.
Oracle's Automatic Storage Management (ASM) and tablespace reorganization techniques help in minimizing fragmentation.
Selected File Restore: Quickly restore database components in some situations.
This represents the ability to restore specific files (such as tablespaces, control files, or redo logs) rather than the entire database.
In Oracle, RMAN allows partial restoration, targeting specific components instead of a full database restore.
Data Block Corruption: Detect data block corruption.
Addresses corrupted database blocks, which can result in data loss or inconsistencies.
Oracle provides mechanisms like DBMS_REPAIR, RMAN Block Media Recovery, and Automatic Block Repair to fix corrupted blocks.
Conclusion
This image illustrates essential backup and recovery mechanisms for Oracle databases, ensuring data integrity, transportability, optimized storage management, selective recovery, and corruption handling. These components are crucial for disaster recovery planning and high availability architectures in database environments.
Implementation of Backup and Recovery Components in Oracle 19c
Oracle 19c provides a robust set of tools and techniques for implementing "backup, recovery, and data integrity management".
Below is an in-depth look at how each of the five components is handled:
1. Transportation (Data Movement in Oracle 19c)
Oracle 19c supports several methods for "transporting data" across different database environments:
Easily transports objects from one database to another.
Key Features and Implementation:
Transportable Tablespaces (TTS)
Moves tablespaces between Oracle databases.
Involves making the tablespace read-only, using expdp to export metadata, and importing it into the target database with impdp.
Example:
ALTER TABLESPACE users READ ONLY;
expdp system/password dumpfile=tts.dmp transport_tablespaces=users directory=DATA_PUMP_DIR;
Enables real-time data replication across databases.
Used for zero-downtime migration and high availability.
2. Base Level Definition (Full Backup in Oracle 19c)
A "full backup" provides a "base-level copy" of the database, which serves as the foundation for incremental backups and disaster recovery.
Base level database definition: Create a base level definition of your database.
Key Features and Implementation:
RMAN Full Backup
Used to take a complete backup of the database.
Example:
RMAN> BACKUP DATABASE FORMAT '/backup/DB_%U.bkp';
Incremental Backup Strategy
Allows backing up only changed blocks since the last full backup.
Example:
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
Fast Recovery Area (FRA)
Provides automated space management for backups.
Example:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/oradata/recovery';
3. Disk Fragmentation (Storage Optimization in Oracle 19c)
Fragmentation occurs when database files become scattered, impacting performance. Oracle 19c includes features to optimize
"disk storage" and minimize fragmentation.
Eliminate disk fragmentation. Key Features and Implementation:
Automatic Segment Space Management (ASSM)
Reduces fragmentation by managing space efficiently in tablespaces.
Implemented via AUTOALLOCATE:
CREATE TABLESPACE my_tbs DATAFILE '/u01/oradata/my_tbs.dbf'
SIZE 1G AUTOEXTEND ON SEGMENT SPACE MANAGEMENT AUTO;
Shrink Table to Defragment Space
Reduces fragmentation at the table level.
Example:
ALTER TABLE employees ENABLE ROW MOVEMENT;
ALTER TABLE employees SHRINK SPACE;
Online Tablespace Defragmentation
Reorganizes fragmented tablespaces online using DBMS_REDEFINITION.
4. Selected File Restore (Granular Data Recovery in Oracle 19c)
Oracle 19c allows "selective recovery of specific database files" (datafiles, control files, or archived logs) without affecting the entire database.
Quickly restore database components in some situations.
Recovers the database to a specific timestamp or SCN.
Example:
RMAN> RUN {
SET UNTIL TIME '2025-01-01:12:00:00';
RESTORE DATABASE;
RECOVER DATABASE;
}
Oracle Flashback Technology
Flashback Table (restores specific rows)
Flashback Database (rolls back entire DB)
Example (Flashback a table):
FLASHBACK TABLE employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
5. Data Block Corruption (Block-Level Recovery in Oracle 19c)
Oracle 19c includes "corruption detection and automatic block repair mechanisms" to ensure data integrity.
Detect data block corruption. Key Features and Implementation:
Conclusion: Oracle 19c provides advanced backup, recovery, and corruption handling mechanisms to ensure data integrity, transportability, and performance optimization. Whether it's transporting tablespaces, managing storage fragmentation, or recovering corrupted blocks, Oracle's features allow DBAs to implement reliable disaster recovery solutions.
Oracle Database 11g Release 2 (11.2), database backups can be performed using several methods
For Oracle Database 11g Release 2 (11.2), database backups can be performed using several methods,
Data Pump utilities (expdp and impdp)
physical backup techniques like Recovery Manager (RMAN).
We will begin with the "Data Pump" and also outline other common approaches for a complete picture of backup options in 11g R2.
1. Logical Backup with Oracle Data Pump (expdp and impdp)
Data Pump, introduced in Oracle 10g and fully supported in 11gR2, is a powerful tool for logical backups. It exports database objects
(i.e., tables, schemas, or the entire database) into a dump file, which can later be imported into the same or a different database.
How to Perform a Data Pump Backup:
Export with expdp:
Set up a directory: Create a directory object in the database where the dump file will be stored.
CREATE DIRECTORY dpump_dir AS '/u01/backup';
GRANT READ, WRITE ON DIRECTORY dpump_dir TO <username>;
Run the export: Use the expdp command from the command line. For example, to back up an entire schema:
You can also remap schemas, tablespaces, or other objects during import if needed (e.g., remap_schema=HR:HR_NEW).
Advantages of Data Pump:
Faster than the older exp/imp utilities.
Supports parallel processing (e.g., parallel=4).
Allows filtering (e.g., include=TABLE:"LIKE 'EMP%'") and transformations during export/import.
Limitations:
Logical backups only, this does not capture physical files (e.g., redo logs, control files).
Not ideal for point-in-time recovery (PITR).
2. Physical Backup with RMAN (Recovery Manager)
For comprehensive backups in Oracle 11gR2, RMAN is the recommended tool. It performs physical backups of database files (datafiles, control files, archived redo logs) and supports full, incremental, and point-in-time recovery.
How to Perform an RMAN Backup:
Start RMAN: Connect to the target database.
rman target /
Configure settings (optional): Set up defaults like backup location or retention policy.
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/backup/backup_%U';
Run a full backup:
BACKUP DATABASE PLUS ARCHIVELOG;
PLUS ARCHIVELOG: Includes archived redo logs for consistency.
Add TAG 'FULL_BACKUP_FEB2025' for easy reference.
Incremental backup (optional): Reduces backup size by capturing only changes since the last backup.
BACKUP INCREMENTAL LEVEL 1 DATABASE;
Advantages of RMAN:
Supports full and incremental backups.
Integrates with Oracle’s recovery catalog for tracking.
Enables PITR and disaster recovery.
Can back up to disk or tape.
Example Output Files:
Datafiles: /u01/backup/backup_1q2r3s4t_1_1
Control file: Included with BACKUP DATABASE or explicitly via BACKUP CURRENT CONTROLFILE.
3. Legacy Export/Import (exp and imp)
Though deprecated in 11gR2, the original 1) exp and 2) imp utilities are still available for backward compatibility.
