Print Friendly, PDF & Email

 
SYMPTOMS

A datapump import performing through a database link fails when the Database Vault is enabled on a source database.
The datapump import fails with the following error stack

impdp system/password schemas=XXCMD NETWORK_LINK=PROD_SYSTEM DIRECTORY=DROPZONE LOGFILE=XXCMD_refresh_07SEP2020.import.log EXCLUDE=statistics

...

Processing object type SCHEMA_EXPORT/VIEW/TRIGGER
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ASO_ORDER_FEEDBACK_T_S','ASO',1,1,'11.02.00.00.00',newblock)
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9081
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_OKC_AQ_EV_TAB_S','OKC',1,1,'11.02.00.00.00',newblock)
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9081
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_AR_REV_REC_QT_S','AR',1,1,'11.02.00.00.00',newblock)
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9081

...

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_MGMT_LOADER_QTABLE_S','SYSMAN',1,1,'11.02.00.00.00',newblock)
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9081
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [TRIGGER:"XXCMD"."XXCMD_MAT_FLOW_V_T_01"]
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9001
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x8d0da5048     20462  package body SYS.KUPW$WORKER
0x8d0da5048      9028  package body SYS.KUPW$WORKER
0x8d0da5048     10935  package body SYS.KUPW$WORKER
0x8d0da5048      2728  package body SYS.KUPW$WORKER
0x8d0da5048      9697  package body SYS.KUPW$WORKER
0x8d0da5048      1775  package body SYS.KUPW$WORKER
0x8cb9b4338         2  anonymous block
Job "SYSTEM"."SYS_IMPORT_SCHEMA_03" stopped due to fatal error at 14:00:14

 
 
DIAGNOSE
 
The source database has the Database Vault enabled.

COL PARAMETER FOR A30 
COL VALUE FOR A10 
SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER                      VALUE
------------------------------ ----------
Oracle Database Vault          TRUE

 
 
SOLUTION
 
Execute the following statements under the SYS user on a source database where the Database Vault is enabled.

(1) Before providing any grant get the current list of grants

SET LINES 300
SET PAGES 999
COL GRANTEE FOR A20 
COL SCHEMA FOR A20 
COL OBJECT FOR A20
SELECT * FROM DVSYS.DBA_DV_DATAPUMP_AUTH;

no rows selected

 
(2) Grant the Oracle Database Vault Authorization to a user account used for an import. In my example the user is SYSTEM.

-- impdp system/password 
SQL> EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SYSTEM');

PL/SQL procedure successfully completed.

 
(3) Confirm the grant was successfully provided.

SET LINES 300
SET PAGES 999
COL GRANTEE FOR A20 
COL SCHEMA FOR A20 
COL OBJECT FOR A20
SELECT * FROM DVSYS.DBA_DV_DATAPUMP_AUTH;

GRANTEE              SCHEMA               OBJECT
-------------------- -------------------- --------------------
SYSTEM               %                    %

 
After the grant is provided on the source database to the requested user, restart the import on a target database. The import must complete successfully.

 
NOTE: To revoke the Oracle Database Vault Authorization grant

SQL> EXEC DVSYS.DBMS_MACADM.UNAUTHORIZE_DATAPUMP_USER('SYSTEM');

PL/SQL procedure successfully completed.

 
 
REFERENCES
 
EXPDP – ORA-1031 On Full Database Export Of Data Vault Database (Doc ID 1571062.1)

 
 

Version  : 16:16 07.09.2020
Database : 11.2.0.3