Lesson 8

Export and Import Utilities Conclusion

In this module, we briefly reviewed the Export and Import utilities with a view to a backup and recovery strategy. These utilities have many other uses that go beyond this short discussion. Using the Export and Import utilities should not be your sole backup and recovery strategy. These utilities may have a place in a larger backup and recovery plan. In Oracle 23c, a robust backup and recovery strategy should go well beyond the use of 1) Export (`expdp`) and 2) Import (`impdp`) utilities. While Data Pump is valuable for logical backups and data movement, it is not designed for point-in-time recovery, redo-based recovery, or handling large transactional systems.
Here is a layered approach with complementary backup strategies you should consider:

🔐 Oracle Recovery Manager (RMAN) – Primary Physical Backup Tool
- Purpose: Backs up the database at the physical level (datafiles, control files, archived redo logs).
- Capabilities:
  - Point-in-time recovery
  - Block-level recovery
  - Incremental backups
  - Encrypted backups
  - Integration with Oracle Secure Backup or cloud storage
- Command Example:
```
          RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
        
```
☁️ 2. Oracle Cloud Autonomous Backup (for OCI-managed databases)
- For Autonomous Database or Base Database Service, backups are automated and stored in Oracle-managed Object Storage.
- User-managed backups can also be scheduled and retained using Recovery Service (formerly OCI Backup Service).
🔄 3. Flashback Technologies – Logical Error Recovery
- Oracle's Flashback features allow you to recover from logical errors without restoring backups:

Flashback Feature	Use Case
`FLASHBACK TABLE`	Undo changes to a specific table
`FLASHBACK DATABASE`	Roll back entire DB to a past point-in-time
`FLASHBACK DROP`	Recover dropped tables from the Recycle Bin
`FLASHBACK QUERY`	View data as of a prior time

Data Guard / Active Data Guard
- Data Guard: Maintains a synchronized standby database (physical or logical).
- Use for disaster recovery, failover, or offload backup operations.
- Active Data Guard allows read-only queries on the standby.
Snapshots and Clone Features (Oracle Multitenant)
- In Oracle 23c, if using a Container Database (CDB), you can use:
  - Clone PDB for dev/test
  - Snapshot Copy with Copy-on-Write storage
- Useful for rapid environment provisioning or rollbacks.
Archive Log Backups (Essential for Point-in-Time Recovery)
- Ensure ARCHIVELOG mode is enabled.
- Archive logs are essential to restore changes made after the last full/incremental backup.
Database Export (Logical) – Data Pump
- Already mentioned, but fits into:
  - Data migration
  - Subset export/import
  - Cross-version compatibility
- Useful, but not sufficient for media failure recovery

✅ Recommended Combined Strategy

Tier	Strategy	Purpose
1	RMAN	Physical full/incremental backups
2	ARCHIVELOG + Flashback	Point-in-time and logical error recovery
3	Data Pump	Logical backups, migrations, versioning
4	Data Guard	High Availability / Disaster Recovery
5	Snapshots/Clones	Testing, dev ops, quick rollback points

Comprehensive checklist for automating Oracle 23c Backup Strategies

Here is a comprehensive checklist for automating Oracle 23c backup strategies, structured to cover physical, logical, high availability, and disaster recovery layers. You can use this for both on-premise and Oracle Cloud Infrastructure (OCI) environments.
1. Pre-Backup Configuration

Task	Description	Status
Enable ARCHIVELOG mode	Required for point-in-time recovery
Set up fast recovery area (FRA)	Configure `DB_RECOVERY_FILE_DEST` and size
Configure RMAN catalog (optional)	Use recovery catalog for backup metadata
Define backup retention policy	Use `CONFIGURE RETENTION POLICY` in RMAN
Verify DB_BLOCK_CHECKING and DB_LOST_WRITE_PROTECT	Helps detect corruption early

🛠️ 2. RMAN-Based Physical Backups

Task	Description	Status
Schedule full database backup	`BACKUP DATABASE PLUS ARCHIVELOG;`
Automate incremental level 1 backups	Daily differentials using `INCREMENTAL LEVEL 1`
Automate archived redo log backups	`BACKUP ARCHIVELOG ALL` daily or hourly
Configure backup compression/encryption	Use `CONFIGURE COMPRESSION` and `ENCRYPTION`
Validate backups weekly	Use `RESTORE VALIDATE` and `BACKUP VALIDATE`
Store backups on OCI Object Storage (optional)	Use RMAN with `Oracle Secure Backup` or `Recovery Service`

🧮 3. Data Pump Logical Backups

Task	Description	Status
Schedule daily schema exports	`expdp SCHEMAS=your_schema ...`
Automate full database exports weekly	`expdp FULL=Y ...`
Use versioned dump file naming	e.g., `backup_full_YYYYMMDD.dmp`
Compress and move dump files to long-term storage	OCI Bucket / NFS mount
Purge old dump files automatically	Use a cron job or OS script

🧠 4. Flashback and Point-in-Time Recovery

Task	Description	Status
Enable and monitor `FLASHBACK DATABASE`	Configure `DB_FLASHBACK_RETENTION_TARGET`
Periodically test `FLASHBACK TABLE` and `DROP`	Practice restoring dropped/modified data
Configure guaranteed restore points (for major changes)	`CREATE RESTORE POINT ... GUARANTEED;`

🌐 5. Data Guard / Disaster Recovery

Task	Description	Status
Configure a physical standby database	Use `DUPLICATE TARGET DATABASE FOR STANDBY`
Automate log shipping and apply	Enable `ARCHIVELOG` and `FAL_SERVER` config
Test switchover/failover regularly	Document process, test quarterly
Use Active Data Guard for read-only offload (optional)	Useful for reporting workloads

🧪 6. Testing and Validation

Task	Description	Status
Automate periodic restore tests to dev/test environment	Verify backup integrity
Document recovery procedures	Create runbooks for RMAN, Flashback, Data Pump
Monitor backup logs and alerts	Integrate with OEM or third-party tools

☁️ 7. OCI-Specific Tasks (If applicable)

Task	Description	Status
Enable automatic backups for OCI Base Database or ADB	Use Console or `dbaascli` for config
Schedule manual backups if automation is disabled	Use OCI CLI or DB Console
Export backup metadata to Object Storage	For long-term archival
Review retention and cost policies	Adjust lifecycle policies in OCI Buckets

Now that you have completed this module, you should be able to:

Describe the functions of the Export and Import utilities
Explain how to use Export
Use EXPORT to perform a complete backup
Use Export for incremental and cumulative backups
Discuss the direct path method of using Export
Use Import to restore database information

For further insight into the possible uses of Export and Import, read the Oracle documentation set. You will also need to review the documentation for all the possible parameter options that can be used with Export and Import.

New Terms

The following terms were introduced in this module:

Full mode: An export of the entire database. There are three options: complete, cumulative and incremental.
Logical backup: Where you copy information from your database to a file with no regard to the physical location of data.
Physical backup: Where you copy information from your database to a file with physical block location information stored in the backup file.

In the next module, you will learn about one of the main tools for backup and recovery - the Recovery Manager, also known as RMAN.

Using Oracle Import

The (imp) import utility reads files generated by the export utility and loads the objects and data into the database. After the tables are created, their data is loaded, and the indexes are built. Following these objects, triggers are imported, constraints are enabled, and bitmap indexes are created. This sequence is important for several of reasons.

Rows are inserted before triggers are enabled to prevent the firing of the triggers for each new row.
Constraints are loaded last because of referential integrity relationships and dependencies among tables.
If each EMPLOYEE row required a valid SECTION row and no rows were in the SECTION table, errors would occur.
If both of these tables are present, the constraints should be disabled during the import and enabled after import.

Oracle Data Pump Import (`impdp`) does not use `INCTYPE`, instead, incremental and full imports are handled through metadata filters, remapping, and export planning.
✅ Assumptions:

You are performing full database imports.
The dump files were re-exported using expdp, not legacy exp (since impdp can't read old .dmp files from Oracle 8).
System user has IMP_FULL_DATABASE privilege.

🔁 Rewritten Oracle 23c Import Commands:

impdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=exp0712.dmp LOGFILE=imp_exp0712.log
impdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=exp0701.dmp LOGFILE=imp_exp0701.log
impdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=exp0706.dmp LOGFILE=imp_exp0706.log
impdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=exp0710.dmp LOGFILE=imp_exp0710.log
impdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=exp0711.dmp LOGFILE=imp_exp0711.log
impdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=exp0712.dmp LOGFILE=imp_exp0712_repeat.log

🧩 Key Notes:

Element	Description
`FULL=Y`	Performs a full database import (excluding SYS-owned objects)
`DIRECTORY=dpump_dir`	A pre-created Oracle directory object pointing to OS-level dump file location
`DUMPFILE=...`	Refers to the `.dmp` file previously exported using `expdp`
`LOGFILE=...`	Records messages, errors, and status info from the import process

🛠️ Example to Create Directory Object (if not done yet):

CREATE OR REPLACE DIRECTORY dpump_dir AS '/u01/oracle/dpump';
GRANT READ, WRITE ON DIRECTORY dpump_dir TO system;

🧠 Additional Options (If Needed):

Disable constraints during import:
```
      impdp system/password FULL=Y ... EXCLUDE=CONSTRAINT
    
```
Then enable constraints manually after import.
Import order control (dependencies): Oracle automatically uses metadata ordering to:

Create tables
Load data
Rebuild indexes
Enable constraints
Create triggers

So there is "no need for manual sequencing" like in Oracle 8’s `imp80`.

Export Import - Quiz

Before you move on to the next module, click the Quiz link below to complete a module conclusion quiz.
Export Import - Quiz