impdp迁移数据至19c，处理TRIGGER时报错，提示insufficient privileges

1、故障概述

客户一套11.2.0.4版本的数据库，由于数据只有500G左右，打算使用expdp/impdp的方式进行数据迁移。在迁移测试的过程中，提示如下错误。

18-MAR-25 09:23:43.147: ORA-31685: Object type TRIGGER:"ECARDV5"."YDB_LOGIN_INFO" failed due to insufficient privileges. Failing sql is:
CREATE TRIGGER ydb_login_info
after logon on database
begin
insert into ydb_logon_log
select
to_char(sysdate,'yyyymmddhh24miss'),
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid
......
略

2、故障处理过程

2.1 由于网络限制，无法使用plsql developer工具，只能使用命令行获取该trigger的具体内容。

set pagesize 9999
set long 9999
col owner for a20
col object_name for a30
select dbms_metadata.get_ddl('TRIGGER','YDB_LOGIN_INFO','ECARDV5') from dual;

2.2 手动执行该trigger的创建语句，同样报ORA-01031错误。

SQL> CREATE OR REPLACE TRIGGER "ECARDV5"."YDB_LOGIN_INFO"
2 after logon on database
3 begin
4 insert into ydb_logon_log
5 select to_char(sysdate, 'yyyymmddhh24miss'),
6 SYS_CONTEXT('USERENV', 'TERMINAL') terminal,
7 SYS_CONTEXT('USERENV', 'LANGUAGE') language,
8 SYS_CONTEXT('USERENV', 'SESSIONID') sessionid,
9 SYS_CONTEXT('USERENV', 'INSTANCE') instance,
10 SYS_CONTEXT('USERENV', 'ENTRYID') entryid,
11 SYS_CONTEXT('USERENV', 'ISDBA') isdba,
12 SYS_CONTEXT('USERENV', 'NLS_TERRITORY') nls_territory,
13 SYS_CONTEXT('USERENV', 'NLS_CURRENCY') nls_currency,
14 SYS_CONTEXT('USERENV', 'NLS_CALENDAR') nls_calendar,
15 SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') nls_date_format,
16 SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language,
17 SYS_CONTEXT('USERENV', 'NLS_SORT') nls_sort,
18 SYS_CONTEXT('USERENV', 'CURRENT_USER') current_user,
19 SYS_CONTEXT('USERENV', 'CURRENT_USERID') current_userid,
20 SYS_CONTEXT('USERENV', 'SESSION_USER') session_user,
21 SYS_CONTEXT('USERENV', 'SESSION_USERID') session_userid,
22 SYS_CONTEXT('USERENV', 'PROXY_USER') proxy_user,
23 SYS_CONTEXT('USERENV', 'PROXY_USERID') proxy_userid,
24 SYS_CONTEXT('USERENV', 'DB_DOMAIN') db_domain,
25 SYS_CONTEXT('USERENV', 'DB_NAME') db_name,
26 SYS_CONTEXT('USERENV', 'HOST') host,
27 SYS_CONTEXT('USERENV', 'OS_USER') os_user,
28 SYS_CONTEXT('USERENV', 'EXTERNAL_NAME') external_name,
29 SYS_CONTEXT('USERENV', 'IP_ADDRESS') ip_address,
30 SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') network_protocol,
31 SYS_CONTEXT('USERENV', 'BG_JOB_ID') bg_job_id,
32 SYS_CONTEXT('USERENV', 'FG_JOB_ID') fg_job_id,
33 SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') authentication_type,
34 'login'
35 from dual;
36 commit;
37 end;
38 /
CREATE OR REPLACE TRIGGER "ECARDV5"."YDB_LOGIN_INFO"
*
ERROR at line 1:
ORA-01031: insufficient privileges

2.3 怀疑是该用户没有创建trigger的权限，手动赋权试试。

SQL> grant create trigger to ECARDV5;

2.4 手动赋予create trigger权限后，仍然报错。

2.5 最终找到《ORA-1031 despite having Administer Database Trigger Privilege (Doc ID 2275535.1)》，在该文章中，这一故障现象，被官方认定为是预期行为。

This is an expected behavior.

In 12.2 , direct grant of "administer database trigger" is needed for the trigger owner.

解决办法是：SQL> grant administer database trigger to ECARDV5;

最终 ，故障解决。

3、针对这个问题，附上官方的测试案例：

SQL>create role r1;
SQL>create user u1 identified by u1;
SQL>create user u2 identified by u2;
SQL>grant create session, create any trigger to u1;
SQL>grant administer database trigger to r1;
SQL>grant r1 to u1, u2;

conn u1/u1

SQL> create or replace trigger u2.afterlogin
after logon on database
begin
null;
end;
/ 2 3 4 5 6
create or replace trigger u2.afterlogin
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

SQL>grant administer database trigger to U2;

SQL> conn u1/u1
Connected.

SQL> create or replace trigger u2.afterlogin
after logon on database
begin
null;
end;
/

Trigger created.