Importing a full database…

Many times, we are required to restore a database from an export dmp file. Its a simple task but sometimes there are some issues left like invalid objects or some objects missing, in the newly created database. Following steps, followed in order can help in creating an error free database:

  1. Create a blank database: The very first step is to create a blank database which is to be used as the target database. That can be done using Database Configuration Assistant. (In last step of the DBCA, change redo log file sizes to 500 MB each (or some appropriate values depdening upon the size of the databaes), as during import, lot of redo will be generated, so large redo size helps in that scenario)

  2. Extract DDLs and create tablespaces: Now run the import with show=Y option and create a log of all DDL statements. The main things to be looked for in the log are DDLs to create tablespaces and DB links. You may need to change the create tablespace statements according to the version of the Oracle you are using. If you have the export taken in an older version, where dictionary tablespaces were being used, you will need to change the statements accordingly, to create locally managed tablespaces.
    (If you have the dmp file in compressed (.Z) format check here, to run the import directly from compressed file)

  3. Adjust the size of SYSTEM, TEMP, USERS and UNDO: As SYSTEM, TEMP, USERS and UNDO tablespaces will get created with the database itself, so you can alter the sizes as per the sizes in the old database.

  4. Edit tnsnames.ora and create dblinks: Now edit tnsnames.ora to include all the databases used in the db links and create db links using the statements from DDL log.

  5. Run the import: Finally, run the import with FULL=Y and IGNORE=Y options and after the import finishes, look for any errors in the log. At last, compile all the invalid objects in the database (Here is the link to a script to compile all the invalid objects). (If the import terminates with ORA-01435, then have a look at this post.)

To read about all the options with imp have a look at Original Import & Export Utilities chapter of Oracle Utilities guide.


3 thoughts on “Importing a full database…

Leave a Reply

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