11 Replies Latest reply: Mar 4, 2013 8:33 AM by Dean Gagne-Oracle RSS

    impdp not importing jobs

    user1647017
      Hello,

      We have performed an export/import for a schema with expdp/impdp.

      Everything has worked except the jobs that have not being imported:
      % grep -i SCHEMA_EXPORT/ expdp_icams.log | tail -3
      Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
      Processing object type SCHEMA_EXPORT/JOB

      % grep -i SCHEMA_EXPORT/ impdp_icams.log | tail -3
      Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
      Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX

      There was no warning or error in impdp log file as far as job are concerned.

      Would you know why it is not importing?
      How can I diagnose why the job importation has been ignored?

      Regards,
        • 1. Re: impdp not importing jobs
          asahide
          Hello,

          What is your oracle(and impdp) version?
          And what did you use impdp option ?

          Regards,
          • 2. Re: impdp not importing jobs
            user1647017
            Hello,

            Version for export: Release 10.2.0.3.0 - 64bit on Solaris Sparc
            Version for import: Release 11.2.0.3.0 on Solaris Sparc

            For the impdp options:
            % cat impdp_icams.par
            USERID=xxxx
            SCHEMAS=xxxx,yyyy
            PARALLEL=4
            DUMPFILE=expdp_icams.dmp
            LOGFILE=impdp_icams.log
            DIRECTORY=data_pump_dir

            Thanks in advance for your answer.
            • 3. Re: impdp not importing jobs
              TSharma-Oracle
              Please post the full expdp and impdp object type logs. You do not need to post the tables export and import but all rest.
              While importing , can you see "Processing object type DATABASE_EXPORT/SCHEMA/REFRESH_GROUP" ?
              This could be importing all your jobs.
              • 4. Re: impdp not importing jobs
                user1647017
                Hello,

                There is no DATABASE_EXPORT/SCHEMA/REFRESH_GROUP in the impdp log.

                Here is the information reported
                % grep -i "object type" expdp_icams.log
                Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
                Processing object type SCHEMA_EXPORT/USER
                Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
                Processing object type SCHEMA_EXPORT/ROLE_GRANT
                Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
                Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
                Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
                Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
                Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
                Processing object type SCHEMA_EXPORT/DB_LINK
                Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
                Processing object type SCHEMA_EXPORT/TABLE/TABLE
                Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
                Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                Processing object type SCHEMA_EXPORT/TABLE/COMMENT
                Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
                Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
                Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
                Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
                Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
                Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
                Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
                Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
                Processing object type SCHEMA_EXPORT/VIEW/VIEW
                Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
                Processing object type SCHEMA_EXPORT/VIEW/COMMENT
                Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
                Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
                Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
                Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
                Processing object type SCHEMA_EXPORT/JAVA_RESOURCE/JAVA_RESOURCE
                Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
                Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
                Processing object type SCHEMA_EXPORT/JOB

                % grep -i "object type" impdp_icams.log | grep -v ORA-
                Processing object type SCHEMA_EXPORT/USER
                Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
                Processing object type SCHEMA_EXPORT/ROLE_GRANT
                Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
                Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
                Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
                Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
                Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
                Processing object type SCHEMA_EXPORT/DB_LINK
                Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
                Processing object type SCHEMA_EXPORT/TABLE/TABLE
                Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
                Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
                Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                Processing object type SCHEMA_EXPORT/TABLE/COMMENT
                Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
                Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
                Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
                Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
                Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
                Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
                Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
                Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
                Processing object type SCHEMA_EXPORT/VIEW/VIEW
                Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
                Processing object type SCHEMA_EXPORT/VIEW/COMMENT
                Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
                Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
                Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
                Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
                Processing object type SCHEMA_EXPORT/JAVA_RESOURCE/JAVA_RESOURCE
                Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
                Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
                • 5. Re: impdp not importing jobs
                  User286067
                  is that refresh group owned by the schema you exported ?
                  • 6. Re: impdp not importing jobs
                    Richard Harrison .
                    Hi,
                    I wonder if you are hitting some variation on this bug

                    Bug 6804815 Jobs in incorrect schema after datapump import

                    It affects 10.2.0.3 - maybe there is something odd about the metadata which menas 11.2 impdp ignores it?

                    Can you do an impdp to a sqlfile and see if that gives any clues?

                    Cheers,
                    Harry
                    • 7. Re: impdp not importing jobs
                      user1647017
                      Richard Harrison .  wrote:
                      Hi,
                      I wonder if you are hitting some variation on this bug

                      Bug 6804815 Jobs in incorrect schema after datapump import

                      It affects 10.2.0.3 - maybe there is something odd about the metadata which menas 11.2 impdp ignores it?

                      Can you do an impdp to a sqlfile and see if that gives any clues?

                      Cheers,
                      Harry
                      Hello,

                      If I do an impdp with sqlfile, the jobs are present in the output file.
                      Does this implies that we do not have the Bug 6804815?

                      Thanks in advance for your help.
                      • 8. Re: impdp not importing jobs
                        user1647017
                        rjamya wrote:
                        is that refresh group owned by the schema you exported ?
                        Hello,

                        Could you clarify and detail your question?
                        As far as I know refresh jobs are related to materialised views.

                        How can I know if the refresh group is owned by the exported schema?

                        Thanks in advance for your answer.
                        • 9. Re: impdp not importing jobs
                          Dean Gagne-Oracle
                          A couple of things you can try.

                          1. run the import but add these 2 parameters

                          keep_master=y master_only=y

                          Then you can look at the master table to see if there are any jobs. I don't know why export would show these objects but import would not. Here is a good query:

                          select object_type, object_name, processing_status, processing_state
                          from <schema_running_job>.<master_table_name>
                          where process_order > 0 and duplicate = 0 and object_type = 'JOB';

                          If you can post this I can take a look at it to see if I can see anything.

                          2. If you run your import command with:

                          sqlfile=jobs.sql include=job

                          Do you get anything in your jobs.sql file?

                          Thanks

                          Dean
                          • 10. Re: impdp not importing jobs
                            user1647017
                            Hello,
                            Dean Gagne wrote:
                            A couple of things you can try.

                            1. run the import but add these 2 parameters

                            keep_master=y master_only=y

                            Then you can look at the master table to see if there are any jobs. I don't know why export would show these objects but import would not. Here is a good query:

                            select object_type, object_name, processing_status, processing_state
                            from <schema_running_job>.<master_table_name>
                            where process_order > 0 and duplicate = 0 and object_type = 'JOB';

                            If you can post this I can take a look at it to see if I can see anything.

                            2. If you run your import command with:

                            sqlfile=jobs.sql include=job

                            Do you get anything in your jobs.sql file?

                            Thanks

                            Dean
                            1. I can not run a new import as the system is in production.

                            2. Even without the options include=job, the jobs.sql contains the jobs.
                            This means that the jobs were exported, but not imported.
                            I would like to know what I should do next time to avoid this behaviour.

                            Thanks in advance for your help.
                            • 11. Re: impdp not importing jobs
                              Dean Gagne-Oracle
                              both of the import jobs I asked you to run would not change anything on the running system except create a single table. But I understand why you could not do that.

                              As to why the jobs did not get imported, I don't now. Can you post the results to the jobs.sql file? If not, then it will be difficult to see what was going on. I would need to look at this in a lot more detail, but since you have seen the jobs in the jobs.sql file, but not imported, the Data Pump job somehow skipped them and I can't determine why. I would suggest opening an SR to see why this is happening. There are just too many variables involved and without all of the files uploaded someplace, it will be difficult to see why they got skipped.

                              Sorry I can't be more help, but this one has me puzzled.

                              Dean