Upgrade Oracle Database from 10g to 11g with Data Pump

As described in “Oracle Database Upgrade Guide 11g Release 1 (11.1)“, there are three upgrade methods offered while you want to upgrade database from 10g to 11g.

1. Database Upgrade Assistant (DBUA)
2. Manual Upgrade (Oracle provided scripts)
3. Export/Import (exp/imp or expdp/impdp)

Besides benefits mentioned in above upgrade document, I’m thinking we are beneficial from using export/import approach. The reasons we decided using this method are:

• We want to set up identical testing databases on development server first
• We want to keep original production databases unchanged anyway for safety reason

With this approach, we first installed Oracle 11g software on development server and created empty database by using provided standard template New_Database.dbt, which could be found at $ORACLE_HOME/assistants/dbca/templates. After that, we followed the following steps to move data from 10g database to newly created 11g database.

Step 1: Pre-create tablespaces on target 11g database

• Using the Generate_Create_Tablespaces.sql to generate tablespace script
• Editing generated script to remove entries for SYSTEM, USERS tablespaces

Step 2: Export full database of source 10g database

expdp system@database directory=DATA_PUMP_DIR full=y dumpfile=export.dmp logfile=export.log

Step 3: Copy dumpfile over network to development server

Step 4: Grant IMPORT_FULL_DATABASE system privilege to user SYSTEM of target 11g database

Step 5: Import full database to target 11g database

Note: If you have co-existing oracle home/version on development server, please make sure switching to Oracle 11g environment, which will assure to use data pump at 11g version to import data.

Impdp system@database directory=DATA_PUMP_DIR full=y dumpfile=export.dmp logfile=import.log