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.
|
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
|
Create Windows Oracle Service
|
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
|
Start Instance
|
Create Database
A simplest database creation script
|
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
|
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.
|