5 Replies Latest reply on Mar 7, 2020 7:08 AM by Gaz in Oz

    migration from 11.2 to 12cr2 - import errors

    3510875

      Dear Experts,

       

      Need your help

       

      [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

       

       

      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

       

      Thank you