ORA-39171 ORA-01659 impdp

1
votes

I'm trying to restore my Oracle backup version 11.2.0.1 to another server version 11.2.0.4.

When I run the impdp, the ORA-39171 and ORA-01659 erros are shown.

impdp hosp/xxxx file=HOSP3001.DMP logfile=loghosp3001.dmp version=latest schemas=HOSP

In the .dmp file I have the following TABLESPACES and DATAFILES:

DATAFILE                                          | TABLESPACE

/u01/app/oracle/product/11.2.0/db_1/dbs/HOSP      | HOSP
/u01/app/oracle/product/11.2.0/db_1/dbs/HOSPDATA  | HOSPDATA
/u01/app/oracle/product/11.2.0/db_1/dbs/HOSPDATA2 | HOSPDATA
/u01/app/oracle/product/11.2.0/db_1/dbs/HOSPDATA3 | HOSPDATA

I have created in the new server the follwing TABLESPACES and DATAFILES. 

CREATE TABLESPACE HOSP DATAFILE 'HOSP' SIZE 1024M ;
ALTER DATABASE DATAFILE 'HOSP' RESIZE 1024M ;
ALTER DATABASE DATAFILE 'HOSP' AUTOEXTEND ON MAXSIZE UNLIMITED ;

CREATE TABLESPACE HOSPDATA DATAFILE 'HOSPDATA' SIZE 1024M ;
ALTER DATABASE DATAFILE 'HOSPDATA' RESIZE 1024M ;
ALTER DATABASE DATAFILE 'HOSPDATA' AUTOEXTEND ON MAXSIZE UNLIMITED ;

In this new scenario I intend to keep just one DATAFILE for the TABLESPACE "HOSPDATA".

3
oracleoracle11g
Which tablespace is the ORA-01659 referring to? It's generally helpful to include the whole error stack from running the command as part of the question. – Alex Poole
I don't know which one tablespace, there are two in the .dmp file as I mentioned. I know the error appears when the impdp is creating the indexes. The console does not specify which index is being compiled. – WellingtonD
The ORA-01659 should tell you which tablespace it can't allocate minextents for. I'm not sure why you aren't including the console output in the question so we can see the errors you are getting. – Alex Poole
Well, I haven't included the console output because it does not show which tablespace has the error and any details about what's going on. At least it should have a logfile with more details, but I don't know if it exists. – WellingtonD

3 Answers

1
votes

Check your alert.log, most probably you will find the answer there. It looks like you do not have free space in the tablespace.

0
votes

ORA- 39171:is usually caused by a non expandable tablespace running out of space leading to stall of the Data Pump job. Adding datafiles to the tablespace running out of space will usually solve the problem.

ORA-01659: Explains the actual cause of ORA- 39171. In this specific case, oracle failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created. Again, the solution will still be adding a datafile to that tablespace.

If you still want to import the data into a single DATAFILE, oracle provides a remapping utility as follows: REMAP_DATAFILE=source_datafile:target_datafile

0
votes

you can use impdp with option "transform=segment_attributes:n"