IMPDB with REMAP_SCHEMA may create(import) Trigger on table in Wrong Schema

Funny situation was discovered recently by one of our customers. Actually, it was an incident with Core Banking System, so, in reality, it wasn’t Funny….

I have checked and found that this issue reproduces not only with customer’s database using software version 11.2.0.4, but with version 12.1.0.2 with latest Oracle Database Proactive Bundle Patch(160719) installed.

To reproduce issue you need:

  • table with trigger on it;
  • trigger MUST be create on table with SCHEMA NOTATION(hard coded schema reference), like ON SCHEMA_NAME.TABLE_NAME;
  • export table using expdp, specifying TABLES=xxx;
  • import exported table into THE SAME database, but into ANOTHER SCHEMA, specifying impdp’s parameter REMAP_SCHEMA;
  • trigger imported into ANOTHER SCHEMA, but it was created on table in ORIGINAL SCHEMA – which is WRONG SCHEMA for this trigger – REMAP_SCHEMA wasn’t processed correctly;
  • depending on privileges of ANOTHER SCHEMA you may face situation with stopping data processing on table in ORIGINAL SCHEMA(because of presence of invalid trigger on it in ANOTHER SCHEMA) or having WRONG RESULTS or EXCESSIVE DATA PROCESSING, that may be detected much later, because of there actually are no errors…

So, lets check it with simple testcase – we will emulate Core Banking System with schema CORE and it’s archive inside the same database with schema name ARCHIVE:

--DROP USER core CASCADE;
CREATE USER core IDENTIFIED BY c;
GRANT CONNECT,RESOURCE,CREATE SESSION,ALTER SESSION TO core;
ALTER USER core QUOTA 1g ON users;
--DROP USER archive CASCADE;
CREATE USER archive IDENTIFIED BY a;
GRANT CONNECT,RESOURCE,CREATE SESSION,ALTER SESSION TO archive;
ALTER USER archive QUOTA 1g ON users;

CONNECT core/c
CREATE TABLE t(ID NUMBER);
CREATE TABLE t_log(ID NUMBER,TRIGGER_NAME VARCHAR2(99));
--simplest trigger - will cause no issue - no schema references
CREATE OR REPLACE TRIGGER trigger_1 BEFORE INSERT ON t FOR EACH ROW
BEGIN
INSERT INTO t_log VALUES(:NEW.id,'trigger_1');
END;
/
--first trigger with issue - "hidden logging" behaviour
CREATE OR REPLACE TRIGGER CORE.trigger_2 BEFORE INSERT ON CORE.t FOR EACH ROW
BEGIN
INSERT INTO t_log VALUES(:NEW.id,'trigger_2');
END;
/
--second trigger with issue - will stop production processing
CREATE OR REPLACE TRIGGER CORE.trigger_3 BEFORE INSERT ON CORE.t FOR EACH ROW
BEGIN
INSERT INTO CORE.t_log VALUES(:NEW.id,'trigger_3');
END;
/
INSERT INTO t VALUES(1);
SELECT * FROM t_log;
ID TRIGGER_NAME
----- ------------
1 trigger_1
1 trigger_2
1 trigger_3
COMMIT;
EXIT

Let’s export these two table and import them into schema ARCHIVE

echo '/ as sysdba' | expdp \
DIRECTORY=data_pump_dir \
DUMPFILE=t.dmp \
TABLES='CORE.T,CORE.T_LOG'
Export: Release 12.1.0.2.0 - Production on Fri Aug 12 11:06:53 2016
...
. . exported "CORE"."T"                                  5.054 KB       1 rows
. . exported "CORE"."T_LOG"                              5.531 KB       3 rows
...
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 12 11:07:50 2016 elapsed 0 00:00:55

echo '/ as sysdba' | impdp \
DIRECTORY=data_pump_dir \
DUMPFILE=t.dmp \
TABLES='CORE.T,CORE.T_LOG' \
REMAP_SCHEMA=CORE:ARCHIVE
Import: Release 12.1.0.2.0 - Production on Fri Aug 12 11:08:24 2016
...
. . imported "ARCHIVE"."T"                               5.054 KB       1 rows
. . imported "ARCHIVE"."T_LOG"                           5.531 KB       3 rows
...
ORA-39082: Object type TRIGGER:"ARCHIVE"."TRIGGER_3" created with compilation warnings
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Fri Aug 12 11:09:48 2016 elapsed 0 00:01:21

Here we have interesting message ORA-39082 “…created with compilation warnings”, so nevermind – it’s only a warning… 😉

Lets check original schema:

sqlplus core/c
INSERT INTO t VALUES(2);
*
ERROR at line 1:
ORA-04098: trigger 'ARCHIVE.TRIGGER_3' is invalid and failed re-validation

UPS! data processing in production schema(CORE) has been stopped!!!

Lets check why:

CONNECT archive/a
SELECT owner,trigger_name,table_owner,table_name,t.status,o.status
FROM all_triggers t, user_objects o
WHERE t.owner=USER
AND t.trigger_name=o.object_name
ORDER BY trigger_name;
OWNER      TRIGGER_NAME    TABLE_OWNER     TABLE_NAME      STATUS     STATUS
---------- --------------- --------------- --------------- ---------- ----------
ARCHIVE    TRIGGER_1       ARCHIVE         T               ENABLED    VALID
ARCHIVE    TRIGGER_2       CORE            T               ENABLED    VALID
ARCHIVE    TRIGGER_3       CORE            T               ENABLED    INVALID

Funny situation – we have only TRIGGER_1 create on correct table(schema ARCHIVE)
TRIGGER_2 and TRIGGER_3 were create on ORIGINAL table in schema CORE
Lets resume processing in schema CORE

DROP TRIGGER archive.trigger_3;
Trigger dropped.

CONNECT core/c
INSERT INTO t VALUES(2);
1 row created.
COMMIT;
SELECT * FROM t_log ORDER BY id,trigger_name;
ID TRIGGER_NAME
----- ---------------
1 trigger_1
1 trigger_2
1 trigger_3
2 trigger_1
2 trigger_2
2 trigger_3

CONNECT archive/a
SELECT * FROM t;
ID
-----
1
SELECT * FROM t_log ORDER BY id,trigger_name;
ID TRIGGER_NAME
----- ---------------
1 trigger_1
1 trigger_2
1 trigger_3
2 trigger_2 <<<--- new PHANTOM record, no original record(ID=2) in table t

SELECT owner,trigger_name,table_owner,table_name,status
FROM all_triggers
WHERE owner='ARCHIVE';
OWNER      TRIGGER_NAME    TABLE_OWNER     TABLE_NAME      STATUS
---------- --------------- --------------- --------------- ----------
ARCHIVE    TRIGGER_1       ARCHIVE         T               ENABLED
ARCHIVE    TRIGGER_2       CORE            T               ENABLED

So, we have trigger TRIGGER_2 in schema ARCHIVE, but on table in schema CORE

SELECT description FROM all_triggers WHERE owner='ARCHIVE';
DESCRIPTION
---------------------------------------------------------------------
trigger_1 BEFORE INSERT ON t FOR EACH ROW
"ARCHIVE"."TRIGGER_2"  BEFORE INSERT ON CORE.t FOR EACH ROW

So, here we have:

  • simple trigger trigger_1 which works correctly and didn’t require remapping during import by impdp;
  • problem trigger TRIGGER_2, which has REMAPped trigger name  to “ARCHIVE”.”TRIGGER_2″ and which has NOT REMAPed schema name(ON CORE.) in DESCRIPTION columns, which seems to be used for trigger’s DDL, but(that part of DESCRIPTION) wasn’t processed by REMAP_SCHEMA logic of impdp
  • problem trigger TRIGGER_3 was dropped to resume processing in schema CORE

Actually, Oracle has NOTE:750783.1(IMPDP Reports ORA-942 and ORA-39083 During Importing Schema Objects), that describes almost similar situation, but with simple exception – importing was done into different database and finished with next errors:

ORA-39083: Object type TRIGGER failed to create with error:
ORA-00942: table or view does not exist

that is absolutely expected behaviour, like it was mentioned in NOTE, but behaviour discussed here is completely different.

Conclusion:

  • always be ready to meet something funny and buggy even in latest software versions even when using old good-known and good-working features…
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s