Sunday , October 22 2017
Home / Oracle DBA / Exports & Imports

Exports & Imports

Primarily there are 2 backup methods in Oracle.

  1. Logical (using Exports – Only on Logical objects)
  2. Physical (a. COLD-Bkup & b. HOT-Bkup)

Though every night we do Physical backups, it’s very important that you must perform logical backup also, which in a way serves as standby bkup in case of our Physical backup failed (when tried to restore).

Apart from acting as Secondary backup, Export is very important in some cases. For e.g. On Tuesday Night, we performed Physical & Logical backups and on Wed Morning at 10AM, one of the Users dropped a Table accidentally and he wants that Table real bad. Without disturbing other users, if he accepts the Table from our LN-LB, we can simply “Imp” that table. So “Exp” is very important (some times equally or even more imp than our PB).

Reasons why we perform Logical Backups;

Whenever you are migrating from One OS to another OS.(E.g. say our NT server has becomes slow. Since no. of users increased etc. and we are planning to shift to Solaris. Then the only way we can get the database is by Exporting from NT and Importing into Solaris). Here you should observe that Oracle Logical Backup is, platform independent.   Wherever you have oracle, you can take this “Exported Dump-File”, and you can Import the data. (Oracle supports 80 platforms)

Whenever we do Oracle Version Upgrades. E.g. say we upgrading our Oracle from 9.2 to 10.2. In this case, we would like our Oracle to not only upgrade the Server-Software but-also upgrade the Database. (You cannot open a database created under 9.2 using 10.2, since 10.2 server requires it’s own set of BT, which are completely different from our 9.2 BT). So in this case, we would like our Oracle to do the Database upgradation also. But, if it fails, you end-up having down-DB. To avoid such unwanted scenario, before even upgrading our Server we go-ahead and perform Oracle “Exp” on 9.2 Database. Then upgrade the Server to 10.2 including DB- Upgrade. If it is successful well & good. Otherwise, we can create an empty Database (with similar TS structures) and then start importing our “DMP” file. So all logical structure will now be created in 10.2 Database.

Whenever Segments are having too-many extents (more than 20), we have to perform DB- REORG. Usually we do this REORG every 3-4 months. To do REORG, PB can’t help us. Since by restoring PB, again we are back to same number of extents since PB is only copying the Datafiles. We are interested in Exports since what EXP does, it “calculates” how-many extents each segment

has and how much space is required etc.   So in its Exp-Definition, the Segment’s properties are changed (“Initial”) compared to our original Segment’s properties.   At the same time, by doing frequent REORGs, we can also be sure of the Data-Integrity (finding bad-blocks etc).

We do exports whenever we want to take objects from one database to another or from one user to another.If the database is too big, we can also perform “incremental” exports on weekdays, and complete on Weekends.

Read More:
Control File Management
Undo Tablespace
Managing Security for Oracle Database Users



Check Also

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. …

Leave a Reply

Your email address will not be published. Required fields are marked *