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.
- Database Upgrade Assistant (DBUA)
- Manual Upgrade (Oracle provided scripts)
- 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