12 Replies Latest reply: Jan 3, 2013 10:30 AM by BillW RSS

    Encountering ORA-31685 on datapump import and I don't understand why

    BillW
      Greetings,

      I am attempting to migrate an existing OEM repository to a new server in order to upgrade OEM. The RDMS version is 11.1.0.7 on each server. Due to platform differences (source=SPARC, destination=Linux) I must use export/import. I am using the datapump.

      I precreated the database using the DBCA, created the OPS$ORACLE user, granted it DBA privileges and then initially performed the import as ops$oracle. The import failed to create a materialized view due to error ORA-31685. The view is owned by SYSMAN which the import creates. On the source I see the following -
      SQL> select * from dba_mviews;
      SYSMAN MGMT_ECM_MD_ALL_TBL_COLUMNS
      MGMT_ECM_MD_ALL_TBL_COLUMNS
      SELECT c.METADATA_ID, c.TARGET_TYPE, c.SNAPSHOT_TYPE, c.TABLE_NAME,
      c.NAM
      2036 N

      N NONE DEMAND FORCE DEFERRED NO COMPLETE 02-SEP-10
      FRESH NA N N N N N N VALID N
      0

      This view does not get created on the destination due to error ORA-31865 on import.

      It was suggested to either grant DBA privileges to the user performing the export, the export was done by OPS$ORACLE which has DBA privileges, or grant create user to the user in question, sysman in this case which does have create user in the source database although I am not certain at what step in the process this grant is issued, at creation I expect.

      I then tried running the import as user sys and the same error occurred. Although in this case the failing SQL was not displayed in the log file.
      I found Metalink note 117136.5 but this does not appear to address my situation.

      The export parameter file I am using is -
      USERID=/
      DIRECTORY=DATAPUMP_DIR
      DUMPFILE=expdp_ares_full.dmp
      LOGFILE=expdp_ares_full_1.log
      JOB_NAME=expdp_ares_full
      FULL=Y
      REUSE_DUMPFILES=yes

      and the import parameter file is -
      USERID='sys/<password> as sysdba'
      full=y
      directory=datapump_dir
      dumpfile=expdp_ares_full.dmp
      logfile=imp_oemrepos.log
      remap_datafile=/local/d01/u01/oracle/emrep:/u01/oracle/oemrepos
      remap_datafile=/local/d03/oracle/emrep:/u01/oracle/oemrepos
      table_exists_action=append

      My questions are -

      1) What is the cause of this failure?
      2) Can I resolve this one problem without completely redoing the export/import?

      Any help is greatly appreciated, thank you.
      Bill Wagman
        • 1. Re: Encountering ORA-31685 on datapump import and I don't understand why
          Srini Chavali-Oracle
          Can you retry the export and import using the SYSTEM account instead ? Using SYSDBA should only be done under the guidance of Support. Pl see the first Note sections in the docs

          http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#i1012781
          http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_import.htm#i1012504

          Pl also post the complete error message from the import log. Are the patch levels on both instances exactly the same ?

          HTH
          Srini
          • 2. Re: Encountering ORA-31685 on datapump import and I don't understand why
            BillW
            Srini,

            Thank you for the response. Running the export again as system will annoy my coworkers as the database I am trying to copy is the repository for our OEM and in order to get a consistent export I need to stop the OMS. The export was done as ops$oracle which did have DBA privileges. I realize this is different than running as system. I can redo the import as system and I will do that and report back.

            An oracle analyst offered the suggestion to run as SYS which is why I tried that.

            The error message was short -
            Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
            ORA-31685: Object type failed due to insufficient privileges. Failing sql is:
            Processing object type DATABASE_EXPORT/SCHEMA/JOB

            Interestingly when I ran it as ops$oracle the full sql was displayed.

            No, they are not at the same patch level, I have not applied PSUs to the target database and I realize this could be a problem. If it becomes necessary I will do that.

            I can extract the DDL for the missing materialized view which I may try if the import fails again as system. I will report my findings.

            Thank you.
            Bill Wagman
            • 3. Re: Encountering ORA-31685 on datapump import and I don't understand why
              Dean Gagne
              Hi,

              I don't see how redoing the export will change anything. The problem is that import doesn't have some priv that it thinks it needs. If this is the only object that did not create, I would think that you could create it manually. To get the syntax, you can either get it from the source, or from the dumpfile.

              source:

              select dbms_metadata.get_ddl('MATERIALIZED_VIEW', '<mview_name_here>', '<mview_schema_here>') from sys.dual;

              Dumpfile:

              impdp user/password ... sqlfile=mview.sql include=materialized_view

              This will give you a .sql file with all mviews, just find the one that you want and execute it.

              Without knowing more about the mview, it would be difficult to see what priv was missing.

              Hope this helps.

              Dean
              • 4. Re: Encountering ORA-31685 on datapump import and I don't understand why
                Srini Chavali-Oracle
                If you have an export using an account that has DBA privileges, that export file should be good enough - you do not need to take another export as SYSTEM. What was the complete SQL displayed along with the error message ? I would ensure that all of the patches on the source have also been applied to the target before starting the import

                HTH
                Srini
                • 5. Re: Encountering ORA-31685 on datapump import and I don't understand why
                  BillW
                  Dean and Srini,

                  Thank you both. I agree that it is a privilege issue on import. I have extrzcted the DDL and the DDL for the view in quesiton is present in the export. It is now a question of attempting to recreate it. That may end up shedding light on the problem.

                  As to patching, the source database has been in place for several years and has been patched so many times it looks like the proverbial old innertube. Trying to determine what patches to apply to make them consitent would be a large project in and of itself, onw which I may end up having to attempt.

                  I will try the import one last time as system and if that is unseuccessful proceed with manually attempting to create the mview.

                  Thank you.
                  Bill Wagman
                  • 6. Re: Encountering ORA-31685 on datapump import and I don't understand why
                    BillW
                    Perhaps I could trouble you both with one more question. I reran the import as system and again the ORA-31685 occurs and indeed the MVIEW is not created. I ran the ddl as sysman, the owner of the mview and it fails on a rank command -

                    rank() over (partition by c.metadata_id, c.TABLE_NAME
                    *
                    ERROR at line 6:
                    ORA-01031: insufficient privileges

                    That is the problem so I need to investigate what that is all about.

                    Thank you for your help.
                    Bill Wagman
                    • 7. Re: Encountering ORA-31685 on datapump import and I don't understand why
                      Srini Chavali-Oracle
                      RANK is a aggregate/analytic function - I do not believe it requires any special privileges.

                      http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions129.htm

                      Can you post the complete MVIEW name and definition ?

                      HTH
                      Srini
                      • 8. Re: Encountering ORA-31685 on datapump import and I don't understand why
                        BillW
                        I reran the DDL as SYS and it was successful (which puzzles me further as to why it didn't complete when I ran the import as SYS but I guess that is just Oracle). The DDL follows. Any suggestions appreciated.

                        -- CONNECT OPS$ORACLE
                        ALTER SESSION SET EDITION = "ORA$BASE";
                        -- new object type path: DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
                        -- CONNECT SYSMAN
                        ALTER SESSION SET EDITION = "ORA$BASE";
                        CREATE MATERIALIZED VIEW "SYSMAN"."MGMT_ECM_MD_ALL_TBL_COLUMNS" ("METADATA_ID", "TARGET_TYPE", "SNAPSHOT_TYPE", "TABLE_NAME", "NAME", "UI_NAME", "TYPE", "TYPE_FORMAT", "UI_ON", "COMPARE_ON", "COMPARE_UI_ON", "HISTORY_ON", "HISTORY_UI_ON", "IS_KEY", "IS_CONTEXT", "IS_SUMMARY", "IS_CHILD_LINK", "LINK_COLUMN_NAME", "COL_ORDER", "SOURCE_TABLE_NAME") USING ("MGMT_ECM_MD_ALL_TBL_COLUMNS", (9, 'EMREP.UCDAVIS.EDU', 1, 0, 0, "SYSMAN", "MGMT_ECM_SNAPSHOT_MD_COLUMNS", '2010-09-02 16:17:21', 0, 0, '2010-09-02 16:17:21', '', 0, 562125839, 0, NULL), 2621760, 9, ('2010-09-02 15:40:53', 21, 0, 0, 562125839, 0, 0, 2, NULL, NULL)) REFRESH FORCE AS SELECT c.METADATA_ID, c.TARGET_TYPE, c.SNAPSHOT_TYPE, c.TABLE_NAME,
                        c.NAME, c.UI_NAME, c.TYPE, c.TYPE_FORMAT,
                        c.UI_ON, c.COMPARE_ON, c.COMPARE_UI_ON, c.HISTORY_ON, c.HISTORY_UI_ON,
                        c.IS_KEY, c.IS_CONTEXT, c.IS_SUMMARY,
                        c.IS_CHILD_LINK, c.LINK_COLUMN_NAME,
                        rank() over (partition by c.metadata_id, c.TABLE_NAME
                        order by c.tbl_order asc, c.col_order asc) as COL_ORDER,
                        c.SOURCE_TABLE_NAME
                        FROM (
                        (SELECT m.METADATA_ID, m.TARGET_TYPE, m.SNAPSHOT_TYPE, t.tbl as TABLE_NAME,
                        c.NAME, c.UI_NAME, c.TYPE, c.TYPE_FORMAT,
                        c.UI_ON, c.COMPARE_ON, c.COMPARE_UI_ON, c.HISTORY_ON, c.HISTORY_UI_ON,
                        c.IS_KEY, c.IS_CONTEXT, c.IS_SUMMARY,
                        c.IS_CHILD_LINK, c.LINK_COLUMN_NAME,
                        t.tbl_order, c.col_order,
                        c.table_name as SOURCE_TABLE_NAME
                        FROM mgmt_ecm_snapshot_metadata m,
                        (SELECT anc.metadata_id, anc.name anc, CONNECT_BY_ROOT anc.name tbl,
                        anc.tbl_order
                        FROM mgmt_ecm_snapshot_md_tables anc
                        CONNECT BY anc.name = PRIOR anc.parent_table_name
                        AND anc.metadata_id = PRIOR anc.metadata_id) t,
                        mgmt_ecm_snapshot_md_columns c
                        WHERE m.metadata_id = t.metadata_id
                        AND m.kind = 'P'
                        AND t.metadata_id = c.metadata_id
                        AND c.table_name = t.anc
                        AND c.is_key = 'Y')
                        UNION ALL
                        (SELECT m.METADATA_ID, m.TARGET_TYPE, m.SNAPSHOT_TYPE, t.name as TABLE_NAME,
                        c.NAME, c.UI_NAME, c.TYPE, c.TYPE_FORMAT,
                        c.UI_ON, c.COMPARE_ON, c.COMPARE_UI_ON, c.HISTORY_ON, c.HISTORY_UI_ON,
                        c.IS_KEY, c.IS_CONTEXT, c.IS_SUMMARY,
                        c.IS_CHILD_LINK, c.LINK_COLUMN_NAME,
                        t.tbl_order, c.col_order,
                        t.name as SOURCE_TABLE_NAME
                        FROM mgmt_ecm_snapshot_metadata m,
                        mgmt_ecm_snapshot_md_tables t,
                        mgmt_ecm_snapshot_md_columns c
                        WHERE m.metadata_id = t.metadata_id
                        AND m.kind = 'P'
                        AND t.metadata_id = c.metadata_id
                        AND c.table_name = t.name
                        AND c.is_key != 'Y')
                        ) c;

                        ALTER MATERIALIZED VIEW "SYSMAN"."MGMT_ECM_MD_ALL_TBL_COLUMNS" COMPILE;

                        Thank you.
                        Bill Wagman
                        • 9. Re: Encountering ORA-31685 on datapump import and I don't understand why
                          Dean Gagne
                          In Data Pump, some objects need to be created as the owner of the object. This is because of how things get set up in the database. MVIEWS are such an item, so data pump code actually create the mview as if it were logged in as the owner. If this mview was created as sys, but owned by another schema, then the schema would need extra privs. Here is basically what Data Pump does:

                          connect mview_user/pass1
                          create mview ...
                          connect schema_running_job/password

                          So, if the user that owns the mview does not have all of the necessary privs, the mview won't get created. If you can as a test, try to grant dba to the user owning the mview to see if the problem goes away.

                          Dean
                          • 10. Re: Encountering ORA-31685 on datapump import and I don't understand why
                            BillW
                            Dean,

                            Thank you for the explanation. I'm still a bit confused though. I looked at the DDL for the mview and there are four tables underlying the view (unless I missed something which is possible) and all four tables are owned by SYSMAN -

                            SQL> select owner,table_name from dba_tables
                            2 where table_name in
                            3 ('MGMT_ECM_SNAPSHOT_METADATA','MGMT_ECM_MD_ALL_TBL_COLUMNS',
                            4 'MGMT_ECM_SNAPSHOT_MD_COLUMNS','MGMT_ECM_SNAPSHOT_MD_TABLES')
                            5 /

                            OWNER TABLE_NAME
                            ------------------------------ ------------------------------
                            SYSMAN MGMT_ECM_MD_ALL_TBL_COLUMNS
                            SYSMAN MGMT_ECM_SNAPSHOT_MD_COLUMNS
                            SYSMAN MGMT_ECM_SNAPSHOT_MD_TABLES
                            SYSMAN MGMT_ECM_SNAPSHOT_METADATA

                            Indeed as you suggest no grants have been issued to other users. What is confusing to me is that when I run the script as SYSMAN from sqlplus I encounter the ORA-01013 error but when I run it as SYS the MVIEW is created and compiled successfully. There is something else going on here which I am still not understanding. I also compared role and system privileges between the source and the target and the SYSMAN user in the target has additional privileges when compared to the source.

                            I'm thoroughly confused. Any help is appreciated.

                            Thank you.
                            Bill Wagman
                            • 11. Re: Encountering ORA-31685 on datapump import and I don't understand why
                              User286067
                              Bill,

                              I suspect this mv is created by sys on sysman's behalf. In my installation as well, sysman does not have create mview privilege, but this mview exists. I think during installation sys runs the installation script but sets current_schema to sysman.

                              Raj
                              • 12. Re: Encountering ORA-31685 on datapump import and I don't understand why
                                BillW
                                Raj,

                                That makes sense. It is one of those situations in which I wish I had more time to look into the internals of what is taking place. Fortunately I was able to solve the problem although not in the cleanest way.

                                Thanks all for the input.
                                Bill Wagman