Showing posts with label Export and Import. Show all posts
Showing posts with label Export and Import. Show all posts

Wednesday, May 13, 2020

impdp failed at dbms_logrep_imp.instantiate_schema with ORA-01031: insufficient privileges

When execute impdp to import 12.2 database, ORA-01031 occured as following,
[oracle@host01]$ impdp system@pdb01 dumpfile=appdata.dmp schemas=appdata

Import: Release 12.2.0.1.0 - Production on Tue May 12 17:58:56 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_03":  system/********@pdb01 dumpfile=appdata.dmp schemas=appdata
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema APPDATA is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges

Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'DB02', inst_scn=>'386040157882');COMMIT; END;
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-31625: Schema APPDATA is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges

Failing sql is:
GRANT SELECT ON "APPDATA"."WORKLOAD_SEQ" TO "USER01"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-31625: Schema APPDATA is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges

Failing sql is:
GRANT SELECT ON "APPDATA"."LOG_SEQ" TO "USER02"

The user SYSTEM, which is used by impdp to connect to database, does not have BECOME USER privilege, 
system@PDB01> select * from dba_sys_privs where privilege='BECOME USER';

GRANTEE    PRIVILEGE       ADMIN_OPTION COMMON INHERITED
---------- --------------- ------------ ------ ---
SYS        BECOME USER     NO           YES    YES

The healthy database privilege grant looks like,
system@PDB01> select * from dba_sys_privs where privilege='BECOME USER';

GRANTEE              PRIVILEGE    ADMIN_OPTION COMMON INHERITED
-------------------- ------------ ------------ ------ ---
DBA                  BECOME USER  NO           YES    YES
IMP_FULL_DATABASE    BECOME USER  NO           YES    YES
SYS                  BECOME USER  NO           YES    YES

It can be fixed by running grand command
grant BECOME USER to DBA,IMP_FULL_DATABASE;

This privilege is normally granted to roles DBA and IMP_FULL_DATABASE. For some reason, it could be revoked. One of the scenario is that DBUA run script dvu121.sql to revoke BECOME USER privileges from DBA and IMP_FULL_DATABASE roles when upgrading database to 12.2 and Database Vault component is VALID.

Tuesday, January 20, 2015

impdp got 'ORA-01843: not a valid month'

When running impdp to load data, got errors:
ORA-39083: Object type TABLE:"DEVUSER"."TAB01" failed to create with error:
ORA-01843: not a valid month
Failing sql is:
CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000')

Try to run the script manually in SQLPLUS:
devuser@ORADB> CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000');
CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000')
*
ERROR at line 1:
ORA-01843: not a valid month

Got same error. Obviously, the CREATE TABLE is using string constant as default value for data type TIMESTAMP. We know it will work only if the session TIMESTAMP format is same as the given string. Let's see what is the session TIMESTAMP format:
devuser@ORADB> variable partype number;
devuser@ORADB> variable intval number;
devuser@ORADB> variable strval varchar2(255);
devuser@ORADB> exec :partype:=dbms_utility.get_parameter_value('NLS_TIMESTAMP_FORMAT',:intval,:strval);

PL/SQL procedure successfully completed.

devuser@ORADB> col STRVAL for a50
devuser@ORADB> print strval

STRVAL
--------------------------------------------------
DD-MON-RR HH.MI.SSXFF AM

The value of session parameter NLS_TIMESTAMP_FORMAT is  'DD-MON-RR HH.MI.SSXFF AM', which is different from the string constant. Change the session parameter as following:
devuser@ORADB> alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6';

Session altered.

devuser@ORADB> exec :partype:=dbms_utility.get_parameter_value('NLS_TIMESTAMP_FORMAT',:intval,:strval);

PL/SQL procedure successfully completed.

devuser@ORADB> print strval

STRVAL
--------------------------------------------------
YYYY-MM-DD HH24:MI:SS.FF6

Retry the CREATE TABLE statement:
devuser@ORADB> CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000');

Table created.

The issue is fixed. Therefore, the CREATE TABLE can be run properly by setting session parameter NLS_TIMESTAMP_FORMAT. The question is how the impdp utility sets this parameter in its own session? LOGON trigger would be a tentative answer:
devuser@ORADB> create or replace trigger temp after logon on database
2 begin
3 execute immediate 'alter session set nls_timestamp_format=''YYYY-MM-DD HH24:MI:SS.FF6''';
4 end;
5 /

Trigger created.

Try impdp again, it works. However, it may not be a best solution. If possible I would like modify the CREATE TABLE (in source database) as following before runnig expdp to create dumpfile:
CREATE TABLE "DEVUSER"."TAB01"
("ID" NUMBER NOT NULL ENABLE,
"CREATED_TIME" TIMESTAMP (6) DEFAULT TO_TIMESTAMP('1970-01-01 00:00:00.000000','YYYY-MM-DD HH24:MI:SS.FF6'));

It is independent on the session timestamp format.