OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » expdp & impdp (11.2, Windows 2012 R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

expdp & impdp [message #664803]

Sat, 05 August 2017 06:42

jkingqst
Messages: 23
Registered: August 2017

Junior Member

Hi All

I'm currently using version 11.2 and we are looking at testing v12.2. I have done an exdp using the following command

expdp username/password DIRECTORY=E_DataPump DUMPFILE=Export_Full.dmp LOGFILE=EXP_Export_Full.log FULL=Yes

We have installed v12.2 on the same server as 11.2 (its a test server not prod) obviously using a different dbhome_1 directory and when I created the new db instance I have used a different SID and location for the db files. Im no Oracle export and I'm having trouble with the impdp, the command I'm using is:

impdp username/password DIRECTORY=E_DataPump DUMPFILE=Export_Full.dmp LOGFILE=IMP_Import_Full.log FULL=Yes

It seems to be failing on the impdp with access denied to creating the tablespaces. I have manually created the folder structure but the folder on the D drive is different (11.2 was in D:\Oradata and 12.2 is D:\Oradata_122) I tried using the remap_tablespaces using the full path that didn't something similar to

REMAP_TABLESPACE=D:\oradata\TEST\DATFILES:D:\oradata_122\TEST122\DATAFILES

So my question is how do I use impdp to recreate the instance like for like but the file system structure location is different.

Thank you for your help in advance.

Cheers, Jason

Report message to a moderator

Re: expdp & impdp [message #664804 is a reply to message #664803]

Sat, 05 August 2017 07:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://www.google.com/search?hl=en&site=webhp&source=hp&q=oracle+impdp+remap_tablespace

What are tablespace_names in old DB?
What are tablespace_names in new DB?
Do all the tablespace_name now exist in new DB?

Report message to a moderator

Re: expdp & impdp [message #664805 is a reply to message #664803]

Sat, 05 August 2017 07:15

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

REMAP_TABLESPACE is to remap the objects in tablespace.
I would pre-create the tablespaces manually in target.
Just get the DDL from source, run in target. Much more control.
Also, please post the complete error message instead of describing it.

Report message to a moderator

Re: expdp & impdp [message #664806 is a reply to message #664804]

Sat, 05 August 2017 07:28

jkingqst
Messages: 23
Registered: August 2017

Junior Member

Hi BlackSwan

Thank you for the reply.

I have googled and read about the REMAP function, but when I used the FULL from the expdp command I thought it would re-create everything that was my confusion.

Currently I have 2 tablespaces with approx 90 datafiles within them so I should create the tablespaces first with the same name and 1 datafile?

But I'm not just sure where the 180 data files would end up?

Cheers, Jason

Report message to a moderator

Re: expdp & impdp [message #664808 is a reply to message #664806]

Sat, 05 August 2017 07:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Why should I answer your questions when you refuse to answer my questions?

I only know what you post here.
I don't know what is in old DB.
I don't know what is in NEW DB.
So I can't be sure what needs to be done.

post results from SQL below from both DBs

SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

Report message to a moderator

Re: expdp & impdp [message #664809 is a reply to message #664806]

Sat, 05 August 2017 07:48

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Get the size of tablespace.
Create a tablespace with one or more datafiles (depending on how you want manage it. This being a test
instance I would, go with one) with the required space. I am not a fan of auto-extending datafiles.

Report message to a moderator

Re: expdp & impdp [message #664813 is a reply to message #664806]

Sat, 05 August 2017 11:10

EdStevens
Messages: 1377
Registered: September 2013

Senior Member

Look at the SQLFILE option of impdp. Instead of actually importing, it will generate a sql file with all of the sql statements it would have executed. Edit that file down to just the CREATE TABLESPACE statements. Adjust them as need to correct the data file location. You don't need to match the number or size of the original data files. Just make sure the final result is large enough to hold the data. You can then use that file to pre-create the tablespaces before actually running the import. When the import runs, it will throw a warning on the CREATE TABLESPACE, but otherwise run just fine.

I answered this same question earlier this week on OTN. Have you been posting it on multiple forums?

Report message to a moderator

Re: expdp & impdp [message #664815 is a reply to message #664808]

Sat, 05 August 2017 18:04

jkingqst
Messages: 23
Registered: August 2017

Junior Member

Thanks BlackSwan

I didn't mean not to answer your questions, I thought they were more statements to look at.

Output below:

OLD DB - TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
LNDAT
LNIDX

New DB - TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

I haven't created any tanlespaces in the new DB and that was what my concern was, I thought the impdb would create everything for me, it seems to be clear it was my lack of understanding of the process.

Kind Regards
Jason

Report message to a moderator

Re: expdp & impdp [message #664816 is a reply to message #664815]

Sat, 05 August 2017 18:08

jkingqst
Messages: 23
Registered: August 2017

Junior Member

Thanks Mahesh and EdStevens
I will try to do that first, I will look running the sqlfile option of impdb to help create the tablespaces and then try the import.

Also nope this was/is my first post.

Thanks all for helping.

Cheers, Jason

Report message to a moderator

Re: expdp & impdp [message #664817 is a reply to message #664816]

Sat, 05 August 2017 18:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I thought the impdb would create everything for me, it seems to be clear it was my lack of understanding of the process.
IMPDP would CREATE TABLESPACE but only to the exact location where they existed on old DB.
If you manually make LNDAT & LNIDX tablespaces, then it will import objects that reside in those tablespaces.
BTW, nothing is gained from a performance standpoint by placing tables & indexes into separate tablespaces.

Report message to a moderator

Re: expdp & impdp [message #664819 is a reply to message #664817]

Sat, 05 August 2017 19:53

jkingqst
Messages: 23
Registered: August 2017

Junior Member

OK, if I create the two tablespaces and the datafiles with the same name, do I need to use the REMAP function?
Also good to know regarding the separate tablespaces for the tables and the indexes, I think this was more the application specific setup they wanted it done.

Report message to a moderator

Re: expdp & impdp [message #664821 is a reply to message #664819]

Sat, 05 August 2017 21:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>do I need to use the REMAP function?
No, since old tablespace name will exist for impdp.

Report message to a moderator

Re: expdp & impdp [message #664828 is a reply to message #664819]

Mon, 07 August 2017 09:15

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

jkingqst wrote on Sat, 05 August 2017 20:53

OK, if I create the two tablespaces and the datafiles with the same name, do I need to use the REMAP function?

You will get errors that the tablespaces exists during the impdp, but they can be ignored. You do not need to create the datafiles with the same name. Only the logical name is needed.

Report message to a moderator

Re: expdp & impdp [message #664890 is a reply to message #664828]

Thu, 10 August 2017 03:24

jkingqst
Messages: 23
Registered: August 2017

Junior Member

Thank you all for your input. I have been able to successfully import the DB after running the the import with the sqlfile switch and then creating the tablespaces followed by the actual import.

Report message to a moderator

Previous Topic:	Data Pump export - ORA-39126 and ORA-12899
Next Topic:	Master child sequence load in sql loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 31 11:05:44 CST 2026