impdp errors during migration

[john]

Hello Erman,

our source db version 11gr2--expdp
Target db version12cr2--impdp

impdp errors:

[oracle@node012 expdp_bkp]$ cat impdp_fulldb.log
Import: Release 12.2.0.1.0 - Production on Mon Feb 17 15:01:50 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

 

;;; **************************************************************************

 

;;; Parfile values:

 

;;;  parfile:  logfile=impdp_fulldb.log

 

;;;  parfile:  dumpfile=expdp_full_uat_db.dmp

 

;;;  parfile:  directory=impdp_full

 

;;; **************************************************************************

 

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

 

Starting "SYS"."SYS_IMPORT_FULL_01":  userid="/******** AS SYSDBA" parfile=impdp_fulldb.par

 

Processing object type DATABASE_EXPORT/TABLESPACE

 

ORA-31684: Object type TABLESPACE:"SYSAUX" already exists

 

ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists

 

ORA-39083: Object type TABLESPACE:"TABS" failed to create with error:

 

ORA-02494: invalid or missing maximum file size in MAXSIZE clause

 

 
Failing sql is:

 

CREATE TABLESPACE "TABS" DATAFILE '/test/datafile/TABS_1_.dbf' SIZE 1073741824 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M,'/test/datafile/TABS_02_.dbf' SIZE 34359721984 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO

 

ORA-31684: Object type TABLESPACE:"SAI" already exists

 

ORA-31684: Object type TABLESPACE:"USERS" already exists

 

===I came across note id when i search the issue and found below solution

 

CAUSE

 

When a data file is manually resized above the size specified by the MAXSIZE clause of the AUTOEXTEND option, via an alter database data file '...' resize command. Then the dump file generated by a full export will contain a create tablespace statement where the data file size is bigger than the size specified for the MAXSIZE clause.

 

 

 

 

 

SOLUTION

 

1. Precreate the tablespace in the destination database and run the import with the ignore=y option.

 

--OR--

 

2. Alter the tablespace/data file in the source database to contain a MAXSIZE value that is above the actual data file size. The next time you run the full export the create tablespace will no longer contain an invalid value.

 


But in my source,my datafile bytes is less than max bytes.please find the below output from source for TABS tablespace.please correct me if i am wrong

 


SQL> select TABLESPACE_NAME,FILE_NAME,maxbytes/1024/1024/1024,bytes/1024/1024/1024 from dba_data_files where tablespace_name='TABS';

 

TABLESPACE_NAME      FILE_NAME                                MAXBYTES/1024/1024/1024 BYTES/1024/1024/1024

 

-------------------- ---------------------------------------- ----------------------- --------------------

 

TABS                /dev/datafile/TABS_1_.dbf                     31.9999847           31.9667969

 

TABS                /dev/datafile/TABS_02_.dbf                    31.9999847           4.25878906

 

 
Below output is in bytes

 

SQL> select TABLESPACE_NAME,FILE_NAME,to_char(maxbytes),to_char(bytes) from dba_data_files where tablespace_name='ACCOM';
 
TABLESPACE_NAME                FILE_NAME                                TO_CHAR(MAXBYTES)                        TO_CHAR(BYTES)
  ------------------------------ ---------------------------------------- ---------------------------------------- ----------------------------------------
  ACCOM                          /oracastage/datafile/accom_1_.dbf        34359721984                              34324086784
  ACCOM                          /oracastage/datafile/accom_02_.dbf       34359721984                              4572839936
 

 


Thank you

[ErmanArslansOracleBlog]

During your import run, your table is created with 34359721984bytes  -> 32.767,984375 M in size.
Your max size is also 32767M. So probably that's why you are getting that error. because of the difference - 0,984375

Let's do the things suggested in that document and update me.
Especially this one -> Alter the tablespace/data file in the source database to contain a MAXSIZE value that is above the actual data file size. The next time you run the "full export" the create tablespace will no longer contain an invalid value. So make that MAXSIZE 32768 and retry the exp-imp.