Oracle Full EXP/IMP process

Remove Oracle Instance on Windows Completely

Stop all Oracle services

Utilize Oracle Universal Installer remove all installed Oracle Products

Regedit – remove entry HKEY_LOCAL_MACHINESOFTWAREORACLE

Regedit – remove all entries related with Oracle under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices

Regedit – remove all entries related with Oracle under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetEventlogApplication

Remove oracle related entries inside Environment Variable PATH, CLASSPATH

Remove program filesOracle and restart

Remove default Oracle c:oracle

Remove ORACLE.INI, ORADIM73.INI, ORADIM80.INI, ORAODBC.INI

Remove WIN.INI Oracle related field

Delete Oracle related DSN if necessary

Export a Full Database Dump from Source

To make a full database dump, the user needs EXP_FULL_DATABASE privilege. I made the full database using SYS user, maybe not necessary. All the demonstrations below will use the DMP generated by SYS.

exp userid=a/a@scarflocal full=y file=c:full1.dmp log=c:full1.log

Note:

If the file size larger than OS limitation, say 2GB, use file+filesize parameter to span exported files.

After granting EXP_FULL_DATABASE to a regular user and exporting full database, the DMP file size is exactly as same as the one exported by SYS account.

Another interesting thing is when I added DIRECT=Y to the command line, the DMP file size is different from the one generated without DIRECT=Y, little bit smaller, maybe some behavior when importing will be slightly different, but I don’t want to step further on this.

Create a Brand New Instance on Windows Box from Scratch

Set ORACLE_SID environment variable

SET ORACLE_SID = SCARF

Create Windows Oracle Service

oradim –new –sid SCARF –intpwd mypassword –startmode M

After this step, under ORACLE_HOMEdatabase there will be a password file named PWDSCARF.ORA

Create Database Initial Parameter – initSCARF.ora

Default path $ORACLE_HOMEdatabase, the most simplest init file looks like as below

control_files = (d:oracleoradatascarfcontrol01.ctl, d:oracleoradatascarfcontrol02.ctl, d:oracleoradatascarfcontrol03.ctl) undo_management = auto db_name=SCARF db_block_size = 8192

Start Instance

sqlplus /nolog conn sys/pwd@scarf as sysdba startup mount

Create Database

A simplest database creation script

CREATE DATABASE SCARF Logfile group1 (‘d:oracleoradatascarfredo01.log’) size 10M group2 (‘d:oralceoradatascarfredo02.log’) size 10M group3 (‘d:oracleoradatascarfredo03.log’) size 10M character set WE8ISO8859P1 national character set UTF8 datafile ‘d:oracleoradatascarfsystem.dbf’ size 50M autoextend on next 10M maxsize unlimited extent management local undo tablespace UNDOTBS datafile ‘d:oracleoradatascarfundotbs.dbf’ size 10M default temporary tablespace TEMP tempfile ‘d:oracleoradatascarftemp.dbf’ size 10M

After database creation, run the following scripts under $oracle_homerdbmsadmin: catalog.sql, catproc.sql, catexp.sql

Create corresponding tablespaces on target database

In most cases, if the datafile layout/names are different, all the tablespaces should be pre-created on the target database.

Use generated tablespace creation DDL (or freehand SQL) to create all the corresponding tablespaces on target DB.

Import into the Target Database

imp system/pwd@service full=Y IGNORE=Y file=xxx.DMP log=xxx.LOG

After importing finished, almost 100% that you will get “Import terminated successfully with warnings” return message, check the LOG file generated.

Generally speaking, I found the following errors in the log file:

ORA-01917 user or role does not exist

ORA-06550 PL/SQL compilation error

ORA-22275 invalid LOB locator specified

ORA-23327 imported deferred rpc data does not match LGOBAL NAME of importing db

ORA-00001 unique constraint violated

ORA-02264 name already used by an existing constraint

ORA-22337 the type of accessed object has been evolved

ORA-29393 user does not exist or is not logged on

IMP-00061 Warning: Object type “xxx” already exists with a different identifier

IMP-00019 Row rejected

IMP-00041 Object created with compilation warning

IMP-00060 Skip table “xxx” because xxx does not exist or has different identifier

IMP-00075 The nested table may contain partial rows or duplicate rows

We will deals with these warning/error later, fist let’s do some basic checking using dbms_utility.compile_schema. After compiling all these schemas, checking the STATUS filed in ALL_OBJECTS table, all the invalid objects should be taken cared of.

Exceptions

The first time when I did the import, I didn’t know need create all these corresponding tablespaces. The result, of course, I screwed up. And I can’t login to target database as SYSDBA. Either ORA-01031 or ORA-12560, I took the following steps:

I shut down the database;

delete the pwdscarf.ora file under oracle_homedatabase directory.

Using orapwd command to re-assign the sys password again, start database. Everything is back.

orapwd file=d:oracleora92databasepwdscarf.ora password=newsyspwd