Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

I am importing a Oracle dump fill created on a AIX Oracle 9.2 installation with a Full Export option to a Windows Oracle 10.2.0.4 on Windows 2008 R2 x64.

I want to import the file to a clean database. However if I don't create a database first, there's nothing to create. If I create the database, it throws a lot of errors complaining of missing users and tablespaces, even with FULL=Y.

How can I import this dump into a clean Oracle Install?

share|improve this question
Which database user did you run the export as, and which database user are you running the import as? If you create an indexfile from the dump file, can you see statements to create the users and tablespaces? – Alex Poole Mar 22 '12 at 17:50
The export ran under system and the import also. The indexfile does not contain the statements to import the users and tablespaces, however when I run the full import on the original file, IMP tries to create the TABLESPACES (which have incorrect paths, which I have to change, and i know that) but it does not try to create the users, only complaining they don't exist. – Alexandre Sousa Mar 22 '12 at 18:10
The docs say users should be included, as you'd expect, so something doesn't sound right. Can you post the actual export command you used? (Although I'm not 100% sure the user creation would be in the indexfile, now I think about it; long time since I've had to think about it...) – Alex Poole Mar 22 '12 at 18:30
The actual export command user ist exp <a_User>/<a_Password> FULL=Y FILE=/PATHHERE/oradata/Export/expdat.dmp log=exp`date + %d%m%y.%H%M%S`.log consistent=y – Alexandre Sousa Mar 23 '12 at 9:01

2 Answers

up vote 3 down vote

If you are using the old imp method the users and tablespaces must be created first. This includes grants, database links and any advanced queues you may be using. A sample script could look like this in Windows imp a_DBA_user/apassword LOG =C:\dump\logs\import.log file =user01.dmp fromuser=user_name touser=user_name

The newer datapump method works wonderfully. You don't need to create the user but I would create any custom tablespaces you may have on the original.

A sample data pump call requires you to create a database link to the original database and a directory. This creates a directory

CREATE OR REPLACE DIRECTORY 
DATA_PUMP_DIR AS 
'C:\app\Oracle\product\11.2.0\dbhome_1\rdbms\log\';


GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO EXP_FULL_DATABASE;

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO auser;

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO IMP_FULL_DATABASE;

In your script to import set these variables NLS_LANG

ORACLE_SID= your database name

impdp a_user_who_exists/your database name directory=DATA_PUMP_DIR network_link=original database name schemas= user1,user2 LOGFILE=DataPump.log TABLE_EXISTS_ACTION=REPLACE

share|improve this answer
1  
Is it possible to create a Datapump connecting from the "new" Oracle 10.2.0.4 to the "old" Oracle 9.2 on Aix? I thought DP's only worked from 10g on. – Alexandre Sousa Mar 22 '12 at 18:17
Sorry I missed that you had a 9i source. You will be forced to use exp and imp commands and physically copy your dump files from one machine to another – kevinsky Mar 22 '12 at 18:21
1  
Users and tablespaces don't have to be created first with a full export/import, unless you need to change something (as the OP does apparently, as the file paths have changed). – Alex Poole Mar 22 '12 at 18:33
up vote 1 down vote accepted

Eventually I ran a procedure to list all the users and tablespaces, recreated them on the target database and when running the import, used the DESTROY=Y switch. This solved my problem.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.