CODEWITHSUNDEEP
oracle-databasedatabase-backups
OakenShield
OakenShield

Reputation: 13

Difference between export/import and backup/restore in Oracle Database

Can someone explain what are the differences between export/import and backup/restore in Oracle DB? And when should I use each one of them? And why does "import database dump" process take very long time to execute?

Upvotes: 0

Views: 3827

Answers (2)

EdStevens
EdStevens

Reputation: 3872

RMAN backups create physical backups of the data files by copying them block by block. It doesn't care what's actually in a given block, though it will copy only those blocks that have or have had data, and ignore the empty blocks. It also keeps metadata about those blocks, so that on a RESTORE it can correctly reconstruct the data files from the backed-up blocks. RMAN backups can be used to restore/recover a database that has completely gone up in smoke. It can also process incremental backups and archivelogs to completely recover to any point in time covered by those incrementals and archivelogs.

Export creates a binary file that is essentially a bunch of CREATE and INSERT statements, to reproduce what was exported. The accompanying IMPORT will then read that dump file and replay all of the CREATE and INSERT statements contained therein. Import requires an operating database to receive and process those statements. And it is strictly a 'single point in time - the time at which the export was executed.

RAMAN backups are for disaster recovery. Export/import is for saving selected data/objects at a given point in time.

Upvotes: 2

wolφi
wolφi

Reputation: 8361

You are right, there are similarities between export/import and backup/restore, but also big differences.

If you think not of a database, but your laptop (or other local computer), you would "restore" the data in case a disaster happens from "backup"s you hopefully have made. And you would "export" some or more data to an USB stick to "import" it to some other computer, or keep it as a snapshot for a long time.

Both tools are specialised for their purpose. For instance, you can say exactly what schema, users, tables, even rows should be exported and where they will be imported agein. For instance, backups are normally split in a usually weekly full backup und usually daily backups that store only the latest changes, which makes it very fast to backup and to recover data.

Frankly, I always am puzzled about the slow speed of an import. Basically it is only slightly faster than replaying all the inserts that ever happened. However, there are tricks to make it faster, look into parallel loading, transportable tablespaces etc.

Upvotes: 2

Related Questions