Home » RDBMS Server » Server Utilities » expdp & impdp (11.2, Windows 2012 R2)
expdp & impdp [message #664803] Sat, 05 August 2017 06:42 Go to next message
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
Re: expdp & impdp [message #664804 is a reply to message #664803] Sat, 05 August 2017 07:08 Go to previous messageGo to next message
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?
Re: expdp & impdp [message #664805 is a reply to message #664803] Sat, 05 August 2017 07:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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.

Re: expdp & impdp [message #664806 is a reply to message #664804] Sat, 05 August 2017 07:28 Go to previous messageGo to next message
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
Re: expdp & impdp [message #664808 is a reply to message #664806] Sat, 05 August 2017 07:41 Go to previous messageGo to next message
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;
Re: expdp & impdp [message #664809 is a reply to message #664806] Sat, 05 August 2017 07:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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.
Re: expdp & impdp [message #664813 is a reply to message #664806] Sat, 05 August 2017 11:10 Go to previous messageGo to next message
EdStevens
Messages: 1376
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?
Re: expdp & impdp [message #664815 is a reply to message #664808] Sat, 05 August 2017 18:04 Go to previous messageGo to next message
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
Re: expdp & impdp [message #664816 is a reply to message #664815] Sat, 05 August 2017 18:08 Go to previous messageGo to next message
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
Re: expdp & impdp [message #664817 is a reply to message #664816] Sat, 05 August 2017 18:17 Go to previous messageGo to next message
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.
Re: expdp & impdp [message #664819 is a reply to message #664817] Sat, 05 August 2017 19:53 Go to previous messageGo to next message
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.
Re: expdp & impdp [message #664821 is a reply to message #664819] Sat, 05 August 2017 21:59 Go to previous messageGo to next message
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.
Re: expdp & impdp [message #664828 is a reply to message #664819] Mon, 07 August 2017 09:15 Go to previous messageGo to next message
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.
Re: expdp & impdp [message #664890 is a reply to message #664828] Thu, 10 August 2017 03:24 Go to previous message
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.
Previous Topic: Data Pump export - ORA-39126 and ORA-12899
Next Topic: Master child sequence load in sql loader
Goto Forum:
  


Current Time: Thu Mar 28 04:48:27 CDT 2024