9 Replies Latest reply: Apr 30, 2012 1:04 AM by Nonuday RSS

    Full export dump to import schema

    Nonuday
      Hi,

      I have an exp dump(datapump) of database, the command use for this:

      +/******* directory=expdump DUMPFILE=HYRT.dmp logfile=HYRT_exp.log FULL=y+*

      Now, I have imported the dump with the below command:

      T24OWNER/******** directory=dor dumpfile=HYRT.dmp logfile=DGFRdmp.impdp.log REMAP_SCHEMA=USER:OWNER REMAP_TABLESPACE=DATAL:BDATA,INDEL:INDEX*

      I have got several errors.

      Is this errors occured because of the import command where I used imported a schema alone?

      If I use full =y parfile in the import command, will these errors can be avoided.


      Thanks in advance,

      nonuday

      Edited by: Nonuday on Apr 29, 2012 10:56 PM

      Edited by: Nonuday on Apr 29, 2012 10:57 PM
        • 1. Re: Full export dump to import schema
          Think_dba
          897910 wrote:
          Hi,

          I have an exp dump(datapump) of database, the command use for this:

          +/******* directory=expdump DUMPFILE=SGTMUKP2.dmp logfile=SGTMUKP2_exp.log FULL=y+*

          Now, I have imported the dump with the below command:

          T24OWNER/******** directory=mydir dumpfile=SGTM.dmp logfile=DBSGTMdmp.impdp.log REMAP_SCHEMA=T24USER:T24OWNER REMAP_TABLESPACE=GLOBUSDATAXML:DBDATA,GLOBUSINDEXXML:DBINDEX*

          I have got several errors.

          Is this errors occured because of the import command where I used imported a schema alone?

          If I use full =y parfile in the import command, will these errors can be avoided.


          Thanks in advance,

          Karthik
          Can you post the errors you got?

          Vishwanath
          • 2. Re: Full export dump to import schema
            Nonuday
            >
            Can you post the errors you got?

            Vishwanath
            The import command which I gave is still in process.
            I have previous imp log which is take with the same exp dump, am getting the same errors now also as the old import. I have post the errors alone below:

            ------------
            ORA-27040: file create error, unable to create file
            BM AIX RISC System/6000 Error: 2: No such file or directory
            Failing sql is:
            CREATE TABLESPACE "XML" DATAFILE '/data/oracle/HJUH54/data01/HJUYHR53_xml.dbf' SIZE 1073741824 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 SEGMEN
            ------------


            Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
            ORA-31684: Object type PASSWORD_VERIFY_FUNCTION already exists
            ORA-31684: Object type PASSWORD_VERIFY_FUNCTION already exists
            ORA-31684: Object type PASSWORD_VERIFY_FUNCTION already exists
            ORA-31684: Object type PASSWORD_VERIFY_FUNCTION already exists
            ORA-31684: Object type PASSWORD_VERIFY_FUNCTION already exists
            ORA-31684: Object type PASSWORD_VERIFY_FUNCTION already exists
            ORA-31684: Object type PROFILE:"PWM_BKND_PROFILE" already exists
            ORA-31684: Object type PROFILE:"APHO_ITIDBA_PROFILE" already exists
            ORA-31684: Object type PROFILE:"DB_DEFAULT_SYSTEM_PROFILE" already exists
            Processing object type DATABASE_EXPORT/SYS_USER/USER
            Processing object type DATABASE_EXPORT/SCHEMA/USER
            ORA-31684: Object type USER:"CSMIG" already exists
            ORA-31684: Object type USER:"DESPWM" already exists
            ORA-31684: Object type USER:"DESDBA" already exists
            ORA-31684: Object type USER:"ECO_IRM" already exists
            ORA-31684: Object type USER:"ANONYMOUS" already exists
            ORA-31684: Object type USER:"IB_LIMITED_USER" already exists
            ORA-31684: Object type USER:"DBA_MON" already exists
            Processing object type DATABASE_EXPORT/ROLE
            ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
            ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
            ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
            ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
            ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
            ORA-31684: Object type ROLE:"LOGSTDBY_ADMINISTRATOR" already exists
            ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
            ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
            ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
            ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
            ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
            ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
            ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
            ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
            ------------

            Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
            ORA-31684: Object type DB_LINK:"OWNER"."JUYTGH" already exists
            Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
            ORA-39083: Object type TRUSTED_DB_LINK failed to create with error:
            ORA-06550: line 1, column 7:
            PLS-00201: identifier 'DBMS_DISTRIBUTED_TRUST_ADMIN' must be declared
            ORA-06550: line 1, column 7:
            PL/SQL: Statement ignored
            These are the errors and there are many in the same kind.

            Can you tell me is these errors caused because of the import cmd taken with schema parfile.

            I have done import previously but never faced this much of errors.



            Thanks in advance

            NONUDAY

            Edited by: Nonuday on Apr 29, 2012 10:58 PM
            • 3. Re: Full export dump to import schema
              Think_dba
              In your export command dumpfile name is SGTMUKP2.dmp
              */******** directory=expdump DUMPFILE=SGTMUKP2.dmp logfile=SGTMUKP2_exp.log FULL=y*
              While importing you are using dumpfile SGTM.dmp

              T24OWNER/******* directory=mydir dumpfile=SGTM.dmp logfile=DBSGTMdmp.impdp.log REMAP_SCHEMA=T24USER:T24OWNER REMAP_TABLESPACE=GLOBUSDATAXML:DBDATA,GLOBUSINDEXXML:DBINDEX*
              Here you are using the wrong dumpfile while importing then how can u expect correct resullts

              Use the below command and let me know
              T24OWNER/******* directory=mydir dumpfile=SGTMUKP2.dmp logfile=DBSGTMdmp.impdp.log REMAP_SCHEMA=T24USER:T24OWNER REMAP_TABLESPACE=GLOBUSDATAXML:DBDATA,GLOBUSINDEXXML:DBINDEX*
              Incase u find it correct or helpful mark it
              • 4. Re: Full export dump to import schema
                Hemant
                Hi ,
                U should use
                table_exists_action=replace
                to avoid this error.
                regards
                hemant
                • 5. Re: Full export dump to import schema
                  Nonuday
                  Vishwanath wrote:
                  In your export command dumpfile name is SGTMUKP2.dmp
                  >
                  >
                  While importing you are using dumpfile SGTM.dmp

                  >
                  Here you are using the wrong dumpfile while importing then how can u expect correct resullts

                  Use the below command and let me know
                  >
                  Incase u find it correct or helpful mark it
                  Yes, they are different because they are different import.

                  I was not able to post the the imp log of below command:



                  Its still in the import process not yet completed. So I posted the above import error which I got previously from the same export dump.

                  Can you tell the reason why am getting this now.


                  Thanks,

                  nonuday



                  The above import error was taken by previous import

                  Edited by: Nonuday on Apr 29, 2012 11:01 PM
                  • 6. Re: Full export dump to import schema
                    Nonuday
                    875319 wrote:
                    Hi ,
                    U should use
                    table_exists_action=replace
                    to avoid this error.
                    regards
                    hemant
                    The above parfile can solve only existing files, what about remaining error files?

                    Regards,
                    nonuday

                    Edited by: Nonuday on Apr 29, 2012 11:02 PM
                    • 7. Re: Full export dump to import schema
                      Think_dba
                      897910 wrote:
                      Vishwanath wrote:
                      In your export command dumpfile name is SGTMUKP2.dmp
                      */******** directory=expdump DUMPFILE=SGTMUKP2.dmp logfile=SGTMUKP2_exp.log FULL=y*
                      While importing you are using dumpfile SGTM.dmp

                      T24OWNER/******* directory=mydir dumpfile=SGTM.dmp logfile=DBSGTMdmp.impdp.log REMAP_SCHEMA=T24USER:T24OWNER REMAP_TABLESPACE=GLOBUSDATAXML:DBDATA,GLOBUSINDEXXML:DBINDEX*
                      Here you are using the wrong dumpfile while importing then how can u expect correct resullts

                      Use the below command and let me know
                      T24OWNER/******* directory=mydir dumpfile=SGTMUKP2.dmp logfile=DBSGTMdmp.impdp.log REMAP_SCHEMA=T24USER:T24OWNER REMAP_TABLESPACE=GLOBUSDATAXML:DBDATA,GLOBUSINDEXXML:DBINDEX*
                      Incase u find it correct or helpful mark it
                      Yes, they are different because they are different import.

                      I was not able to post the the imp log of below command:
                      T24OWNER/******* directory=mydir dumpfile=SGTM.dmp logfile=DBSGTMdmp.impdp.log REMAP_SCHEMA=T24USER:T24OWNER REMAP_TABLESPACE=GLOBUSDATAXML:DBDATA,GLOBUSINDEXXML:DBINDEX*
                      Its still in the import process not yet completed. So I posted the above import error which I got previously from the same export dump.

                      Can you tell the reason why am getting this now.


                      Thanks,

                      Karthik



                      The above import error was taken by previous import
                      Provide your current(expdp and impdp) statements and log files NOT previous ones.
                      • 8. Re: Full export dump to import schema
                        Nonuday
                        >
                        >
                        Provide your current(expdp and impdp) statements and log files NOT previous ones.
                        Since the import is in progress, I will post the import log once it is done.

                        Thanks for your input..


                        nonuday

                        Edited by: Nonuday on Apr 29, 2012 11:03 PM
                        • 9. Re: Full export dump to import schema
                          Nonuday
                          Hi Vishwanath,

                          The import got completed.


                          I have got sum of 16164 errors and I have posted each of its kind below.

                          1
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          abcd
                          9
                          bf d6
                          0cfc6e4Sm6mfaMYwsbW2JygBepcwg/BKmJ4VZy/pO06UXsVUMejsissTcGWYR4qeK4TPqfDK
                          q7UPH+SmKP6nW9zmxMZnuK1VDzM0Iv9O4E4SvvsnHWn+EPF9hR+oBFe3fEro6RQ5R5Ejd1nr
                          e+fAK010dExf76gg/c6ZB3YxGPHDOqkGI4lV6HNsd57gKLwTd0bxan5UwJriIpt7Vjc=
                          ------------
                          Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
                          ORA-31685: Object type FUNCTION:"SYSTEM"."WM$_CHECK_INSTALL" failed due to insufficient privileges. Failing sql is:
                          CREATE FUNCTION "SYSTEM"."WM$_CHECK_INSTALL" return boolean is begin return false ; end;
                          Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
                          ORA-31685: Object type PROCEDURE:"OUTLN"."ORA$GRANT_SYS_SELECT" failed due to insufficient privileges. Failing sql is:
                          CREATE PROCEDURE "OUTLN"."ORA$GRANT_SYS_SELECT" as
                          begin
                          EXECUTE IMMEDIATE 'GRANT SELECT ON OUTLN.OL$ TO SELECT_CATALOG_ROLE';
                          EXECUTE IMMEDIATE 'GRANT SELECT ON OUTLN.OL$HINTS TO SELECT_CATALOG_ROLE';
                          EXECUTE IMMEDIATE 'GRANT SELECT ON OUTLN.OL$NODES TO SELECT_CATALOG_ROLE';
                          end;
                          ORA-31685: Object type PROCEDURE:"SYSTEM"."ORA$_SYS_REP_AUTH" failed due to insufficient privileges. Failing sql is:
                          CREATE PROCEDURE "SYSTEM"."ORA$_SYS_REP_AUTH" as
                          begin
                          EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.repcat$_repschema TO SYS ' ||
                          'WITH GRANT OPTION';
                          EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.repcat$_repprop TO SYS ' ||
                          'WITH GRANT OPTION';
                          EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_aqcall TO SYS ' ||
                          'WITH GRANT OPTION';
                          EXECUTE
                          Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
                          ORA-31685: Object type ALTER_FUNCTION:"SYSTEM"."WM$_CHECK_INSTALL" failed due to insufficient privileges. Failing sql is:

                          ALTER FUNCTION "SYSTEM"."WM$_CHECK_INSTALL" COMPILE PLSQL_OPTIMIZE_LEVEL= 2 PLSQL_CODE_TYPE= INTERPRETED PLSQL_DEBUG= FALSE REUSE SETTINGS TIMESTAMP '2010-11-01 16:05:09'
                          Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
                          ORA-31685: Object type ALTER_PROCEDURE:"OUTLN"."ORA$GRANT_SYS_SELECT" failed due to insufficient privileges. Failing sql is:

                          ALTER PROCEDURE "OUTLN"."ORA$GRANT_SYS_SELECT" COMPILE PLSQL_OPTIMIZE_LEVEL= 2 PLSQL_CODE_TYPE= INTERPRETED PLSQL_DEBUG= FALSE REUSE SETTINGS TIMESTAMP '2010-11-01 16:05:09'
                          ------------------
                          ORA-31685: Object type ALTER_PROCEDURE:"SYSTEM"."ORA$_SYS_REP_AUTH" failed due to insufficient privileges. Failing sql is:

                          ALTER PROCEDURE "SYSTEM"."ORA$_SYS_REP_AUTH" COMPILE PLSQL_OPTIMIZE_LEVEL= 2 PLSQL_CODE_TYPE= INTERPRETED PLSQL_DEBUG= FALSE REUSE SETTINGS TIMESTAMP '2010-11-01 16:05:09'
                          Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
                          ORA-31685: Object type VIEW:"SYSTEM"."MVIEW_WORKLOAD" failed due to insufficient privileges. Failing sql is:
                          CREATE FORCE VIEW "SYSTEM"."MVIEW_WORKLOAD" ("WORKLOADID", "IMPORT_TIME", "QUERYID", "APPLICATION", "CARDINALITY", "RESULTSIZE", "LASTUSE", "FREQUENCY", "OWNER", "PRIORITY", "QUERY", "RESPONSETIME") AS select
                          a.collectionid# as workloadid,
                          a.collecttime as import_time,
                          a.queryid# as queryid,
                          a.application,
                          a.cardinality,
                          a.resultsize,
                          a.qdate as lastuse,
                          a.frequency,
                          a.uname as o
                          -------------
                          Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
                          ORA-39112: Dependent object type OBJECT_GRANT:"SYSTEM" skipped, base object type VIEW:"SYSTEM"."MVIEW_WORKLOAD" creation failed
                          -------------
                          Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
                          Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY
                          ORA-39083: Object type PASSWORD_HISTORY failed to create with error:
                          ORA-06550: line 1, column 33:
                          PLS-00201: identifier 'SYS.DBMS_PSWMG_IMPORT' must be declared
                          ORA-06550: line 1, column 10:
                          PL/SQL: Item ignored
                          -------------



                          These are the errors in the log..

                          I have done import from many client export dump before but they are schema level export only.

                          This one is full database export where I have imported only the schema from the full exp dump. Is this the reason why am getting so many errors or anything else can you throw some light.


                          Thanks in advance.


                          nonuday

                          Edited by: Nonuday on Apr 29, 2012 11:03 PM