11 Replies Latest reply: Jan 15, 2013 2:44 PM by Srini Chavali-Oracle RSS

    IMPDP ORA-12081/ ORA-01732

    user12263161
      Hi All,

      Getting this weired error while importing 10g dump to 11gR2. I have tried many options but every time I end up with the below error for some of the tables.

      ORA-31693: Table data object "APPS"."HRI_MDP" failed to load/unload and is being skipped due to error:
      ORA-02354: error in exporting/importing data
      ORA-12081: update operation not allowed on table "APPS"."HRI_MDP"
      ------------
      ORA-31693: Table data object "APPS"."ISC_DV"
      failed to load/unload and is being skipped due to error:
      ORA-01732: data manipulation operation not legal on this view
      ------------
      I tried to drop the schema and reimported it. But its the same.

      What is it? Is it some oracle bug? I see the table and view has been exported successfully in export dump log.

      Please suggest.

      Thanks for help
        • 1. Re: IMPDP ORA-12081/ ORA-01732
          user12263161
          No replies!!!!
          • 2. Re: IMPDP ORA-12081/ ORA-01732
            Srini Chavali-Oracle
            Pl post details of OS, database and EBS versions. AFAIK, exporting and importing of EBS databases between different database versions is not supported. Pl elaborate on what you are trying to do.

            Edit - See yout other related post - Re: Importing to 11gR2

            HTH
            Srini
            • 3. Re: IMPDP ORA-12081/ ORA-01732
              Dean Gagne
              I'm not familiar with the table. There is no definition here so that doesn't help. Do you have a trigger on the table? Do you use row level security? What was your export/import command.

              Too many unknowns in this question.

              Dean
              • 4. Re: IMPDP ORA-12081/ ORA-01732
                User286067
                Is "APPS"."HRI_MDP" a MV? if so, it is ok, once impdp is done, you can compile/refresh MV. if it isn't mv, then you need to provide more info before saying 'no replies' :)
                • 5. Re: IMPDP ORA-12081/ ORA-01732
                  user12263161
                  Yes, It is a materialized view. When i drop it and import it again, I am able to get the rows imported. But materialized view logs are not getting created.

                  Apart from the above now i have 100000 invalid objects on my database.

                  importing 10g export dump on 11g is proving to be nightmare. I have ended up recompiling invalid objects.
                  • 6. Re: IMPDP ORA-12081/ ORA-01732
                    User286067
                    user12263161 wrote:
                    Yes, It is a materialized view. When i drop it and import it again, I am able to get the rows imported. But materialized view logs are not getting created.

                    Apart from the above now i have 100000 invalid objects on my database.

                    importing 10g export dump on 11g is proving to be nightmare. I have ended up recompiling invalid objects.
                    Well then

                    1. impdp excluding MV and MV logs,
                    2. after impdp, create MV and MV logs using sql script
                    3. after all objects are imported, recompile using utlrp.

                    It isn't as bad as you think. It is bad when you are unable to compile any objects after trying with utlrp, but not until then.

                    Raj
                    • 7. Re: IMPDP ORA-12081/ ORA-01732
                      Srini Chavali-Oracle
                      user12263161 wrote:
                      Yes, It is a materialized view. When i drop it and import it again, I am able to get the rows imported. But materialized view logs are not getting created.

                      Apart from the above now i have 100000 invalid objects on my database.

                      importing 10g export dump on 11g is proving to be nightmare. I have ended up recompiling invalid objects.
                      As noted in this post and your other post, exporting/importing an EBS database across database versions is not supported. I requested in this post and your other post information on why you are doing this, but you have not responded. Is there a reason you are not using DBUA to upgrade ?

                      Srini
                      • 8. Re: IMPDP ORA-12081/ ORA-01732
                        user12263161
                        We are trying this because we are going to upgrade entire hardware setup as well, along with the database versions. It doesnt make any sense to upgrade old database platform because anyways in the new setup we will have 11GR2.

                        Btw, What do u mean by not supported?? Every product in the world has backward compatibility. while designing 11G database, if oracle ignored 10g then its the biggest flaw in the oracles part.

                        Meanwhile I am keeping an option of RMAN backup from 10g and restore on 11G. Its very tiring to go through each and every line of impdp logs to check what oracle has imported and what not. You can imagine my frustration when I am trying to find errors on 50MB notepad file generated as logfile of impdp :(
                        • 9. Re: IMPDP ORA-12081/ ORA-01732
                          Srini Chavali-Oracle
                          AFAIK, for EBS databases, you can only use export/import for moving the database to the same exact version. Pl see these MOS Docs

                          Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2 [ID 454616.1]
                          10g Release 2 Export/Import Process for Oracle Applications Release 11i [ID 362205.1]

                          What you will need to do is to use export/import the move the database from source to target using the existing database version (10g). Once the import completes on the target, you will need to use DBUA on target to upgrade the database to 11gR2. Pl confirm with Support.

                          Interoperability Notes EBS 12.0 and 12.1 with Database 11gR2 [ID 1058763.1]

                          HTH
                          Srini
                          • 10. Re: IMPDP ORA-12081/ ORA-01732
                            Dean Gagne
                            Srini,

                            I'm not sure if you are right about not being able to upgrade the Oracle version when migrating an EBS database. There is a whitepaper describing the best practices on migrating an ebs database. Here is the title:

                            Migrating the Oracle E-Business Suite Database to Oracle Exadata Database Machine Using Transportable Tablespaces
                            Oracle Maximum Availability Architecture White Paper
                            February 2012

                            There is also reference to this:

                            My Oracle Support (MOS) ID 1133355.1

                            Hope this helps.

                            Dean
                            • 11. Re: IMPDP ORA-12081/ ORA-01732
                              Srini Chavali-Oracle
                              Thanks Dean - in the past, upgrading an EBS database using export/import was not supported - things may have changed since then - hence my reference to get confirmation from Support.

                              Srini