聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

impdp时报错Ora-28031

2013-08-15 09:11 浏览: 2582833 次 我要评论(0 条) 字号:

今天一客户说在做impdp导入时,报错Ora-28031。我们看看ora-28031的报错是什么意思:

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded

从报错信息看,是SYS用户的role超过148个,一开始,我们用

select count(*) from dba_role_privs where grantee = 'SYS';

进行,查询,发现远远不到148个,后来,查询到文档 DataPump Import Errors With Repeated ORA-39083 ORA-31625 And ORA-28031 During OBJECT_GRANTS (Doc ID 1101035.1) 发现,原来不仅仅是直接的role,级联的role也会被计算在内,即需要用下面的sql进行查询:

select  lpad (' ', 2*level, ' ')||granted_role
from    dba_role_privs
where   default_role = 'YES'
start   with grantee = 'SYS'
connect by prior granted_role = grantee;

我们看到,如果算级联的role的话,SYS就有超过148个的role,因此再用impdp导入的时候,会报错ora-28031。

根据文档建议,可以在导出前,在源库删除部分role,或者导入时 EXCLUDE=ROLE。

另外,我们还可以这样做,
(1)备份原来default role的权限

select 'alter user sys default role '||wm_concat(granted_role)||';' from dba_role_privs
where grantee = 'SYS' and default_role='YES';

(2)直接设置:

alter user SYS default roles DBA;

(3)导出

(4)impdp导入后恢复,用第一步记录的SQL恢复

一般情况下,引起Ora-28031的role的往往是SYS,但是在极端情况下,也有可能是别的role,写了如下一段,可以检查,并备份超过148个role的授权语句。

set serveroutput on
set line 5000
DECLARE
  v_count varchar2( 20);
  v_sql   varchar2(2000 );
BEGIN
  DBMS_OUTPUT.put_line( '###################### REPORT START HERE #######################');
  DBMS_OUTPUT.put_line( chr(9 ));
  DBMS_OUTPUT.put_line( chr(9 ));
  DBMS_OUTPUT.put_line( '============= The number of recursive role ================');
  FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
    select count (lpad( ' ', 2 * level, ' ') || granted_role)
      into v_count
      from dba_role_privs
     where default_role = 'YES'
     start with grantee = employee_rec.GRANTEE
    connect by prior granted_role = grantee;
    if v_count >= 148 then
      DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
                           lpad(v_count, 5 , '.') ||
                           '
<=== Cause ORA-28031');
    
else
      
DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
                          
lpad(v_count, 5 , '.'));
    
end if ;
 
END LOOP;
 
DBMS_OUTPUT.put_line( chr(9 ));
 
DBMS_OUTPUT.put_line( '================== Use SQL to backup =====================');
 
FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
    
select count (lpad( ' ', 2 * level, ' ') || granted_role)
      
into v_count
      
from dba_role_privs
    
where default_role = 'YES'
    
start with grantee = employee_rec.GRANTEE
    
connect by prior granted_role = grantee;
    
if v_count >= 148 then
      select 'alter user ' || employee_rec.GRANTEE ||' default role ' || wm_concat(granted_role) || ';' ||chr( 9)
        into v_sql
        from dba_role_privs
       where grantee = employee_rec.GRANTEE
         and default_role = 'YES' ;
      DBMS_OUTPUT.put_line( substr(v_sql,1 ,2000));
    end if ;
  END LOOP;
  DBMS_OUTPUT.put_line( chr(9 ));
  DBMS_OUTPUT.put_line( chr(9 ));
  DBMS_OUTPUT.put_line( '######################## REPORT END HERE #######################');
END;
/

我们来测试一下:

sys@ORA11G(192.168.1.37)> set serveroutput on
sys@ORA11G(192.168.1.37)> set line 5000
sys@ORA11G(192.168.1.37)> DECLARE
 
2    v_count varchar2( 20);
 
3    v_sql   varchar2(2000 );
 
4  BEGIN
 
5    DBMS_OUTPUT.put_line( '###################### REPORT START HERE #######################');
 
6    DBMS_OUTPUT.put_line( chr(9 ));
 
7    DBMS_OUTPUT.put_line( chr(9 ));
 
8    DBMS_OUTPUT.put_line( '============= The number of recursive role ================');
 
9    FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
 
10      select count (lpad( ' ', 2 * level, ' ') || granted_role)
 
11        into v_count
 
12        from dba_role_privs
 
13       where default_role = 'YES'
 
14       start with grantee = employee_rec.GRANTEE
 
15      connect by prior granted_role = grantee;
 
16      if v_count >= 148 then
 
17        DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
 
18                             lpad(v_count, 5 , '.') ||
 
19                             ' <=== Cause ORA-28031');
 
20      else
 
21        DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
 
22                             lpad(v_count, 5 , '.'));
 
23      end if ;
 
24    END LOOP;
 
25    DBMS_OUTPUT.put_line( chr(9 ));
 
26    DBMS_OUTPUT.put_line( '================== Use SQL to backup =====================');
 
27    FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
 
28      select count (lpad( ' ', 2 * level, ' ') || granted_role)
 
29        into v_count
 
30        from dba_role_privs
 
31       where default_role = 'YES'
 
32       start with grantee = employee_rec.GRANTEE
 
33      connect by prior granted_role = grantee;
 
34      if v_count >= 148 then
 
35        select 'alter user ' || employee_rec.GRANTEE ||' default role ' || wm_concat(granted_role) || ';' ||chr( 9)
 
36          into v_sql
 
37          from dba_role_privs
 
38         where grantee = employee_rec.GRANTEE
 
39           and default_role = 'YES' ;
 
40        DBMS_OUTPUT.put_line( substr(v_sql,1 ,2000));
 
41      end if ;
 
42    END LOOP;
 
43    DBMS_OUTPUT.put_line( chr(9 ));
 
44    DBMS_OUTPUT.put_line( chr(9 ));
 
45    DBMS_OUTPUT.put_line( '######################## REPORT END HERE #######################');
 
46  END;
 
47  /
###################### REPORT START HERE #######################
 
 
=============
The number of recursive role ================
APEX_030200...........................................2
CTXSYS................................................2
DATAPUMP_EXP_FULL_DATABASE............................5
DATAPUMP_IMP_FULL_DATABASE...........................10
DBA..................................................41
DBSNMP................................................3
EXECUTE_CATALOG_ROLE..................................1
EXFSYS................................................1
EXP_FULL_DATABASE.....................................4
HS_ADMIN_ROLE.........................................2
IMP_FULL_DATABASE.....................................4
JAVASYSPRIV...........................................1
LOGSTDBY_ADMINISTRATOR................................1
MDDATA................................................2
MDSYS.................................................2
MGMT_VIEW.............................................1
OEM_MONITOR...........................................2
OLAPSYS...............................................2
OLS_TEST..............................................4
OPS$JIMMY.............................................1
ORDSYS................................................1
OUTLN.................................................1
OWBSYS...............................................10
ROLE_A................................................2
ROLE_B................................................2
ROLE_C...............................................42
ROLE_D...............................................49
SCOTT.................................................2
SELECT_CATALOG_ROLE...................................1
SPATIAL_CSW_ADMIN_USR.................................3
SPATIAL_WFS_ADMIN_USR.................................3
SYS.................................................224 <=== Cause ORA-28031
SYSMAN................................................4
SYSTEM...............................................44
TEST.................................................44
TT1...................................................1
WMSYS.................................................3
XDB...................................................3
 
==================
Use SQL to backup =====================
alter user SYS default role ROLE_D,XDB_SET_INVOKER,XDBADMIN,JMXSERVER,IMP_FULL_DATABASE,APEX_ADMINISTRATOR_ROLE,JAVADEBUGPRIV,JAVAIDPRIV,RECOVERY_CATALOG_OWNER,DELETE_CATALOG_ROLE,CSW_USR_ROLE,OLAP_XS_ADMIN,DBA,WFS_USR_ROLE,XDB_WEBSERVICES_OVER_HTTP,AUTHENTICATEDUSER,JAVAUSERPRIV,ADM_PARALLEL_EXECUTE_TASK,ROLE_C,ROLE_A,OWB_DESIGNCENTER_VIEW,CWM_USER,OLAP_DBA,ORDADMIN,HS_ADMIN_ROLE,HS_ADMIN_SELECT_ROLE,DATAPUMP_IMP_FULL_DATABASE,EXP_FULL_DATABASE,ROLE_B,OWB$CLIENT,MGMT_USER,SPATIAL_CSW_ADMIN,CTXAPP,JAVA_ADMIN,OEM_MONITOR,HS_ADMIN_EXECUTE_ROLE,DATAPUMP_EXP_FULL_DATABASE,AQ_ADMINISTRATOR_ROLE,DBFS_ROLE,SPATIAL_WFS_ADMIN,OLAP_USER,XDB_WEBSERVICES,JAVA_DEPLOY,EJBCLIENT,JAVASYSPRIV,SCHEDULER_ADMIN,AQ_USER_ROLE,LOGSTDBY_ADMINISTRATOR,EXECUTE_CATALOG_ROLE,RESOURCE,OWB_USER,OLAPI_TRACE_USER,XDB_WEBSERVICES_WITH_PUBLIC,OEM_ADVISOR,GATHER_SYSTEM_STATISTICS,SELECT_CATALOG_ROLE,CONNECT;
 
 
######################## REPORT END HERE #######################
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.09
sys@ORA11G(192.168.1.37)>

然后我们执行第(2)步,alter user SYS default roles DBA;然后再检查:

sys@ORA11G(192.168.1.37)> alter user SYS default roles DBA;
 
User altered.
 
Elapsed: 00:00:00.06
 
 
--运行上面的程序:
###################### REPORT START HERE #######################
 
 
=============
The number of recursive role ================
APEX_030200...........................................2
CTXSYS................................................2
DATAPUMP_EXP_FULL_DATABASE............................5
DATAPUMP_IMP_FULL_DATABASE...........................10
DBA..................................................41
DBSNMP................................................3
EXECUTE_CATALOG_ROLE..................................1
EXFSYS................................................1
EXP_FULL_DATABASE.....................................4
HS_ADMIN_ROLE.........................................2
IMP_FULL_DATABASE.....................................4
JAVASYSPRIV...........................................1
LOGSTDBY_ADMINISTRATOR................................1
MDDATA................................................2
MDSYS.................................................2
MGMT_VIEW.............................................1
OEM_MONITOR...........................................2
OLAPSYS...............................................2
OLS_TEST..............................................4
OPS$JIMMY.............................................1
ORDSYS................................................1
OUTLN.................................................1
OWBSYS...............................................10
ROLE_A................................................2
ROLE_B................................................2
ROLE_C...............................................42
ROLE_D...............................................49
SCOTT.................................................2
SELECT_CATALOG_ROLE...................................1
SPATIAL_CSW_ADMIN_USR.................................3
SPATIAL_WFS_ADMIN_USR.................................3
SYS.................................................126
SYSMAN................................................4
SYSTEM...............................................44
TEST.................................................44
TT1...................................................1
WMSYS.................................................3
XDB...................................................3
 
==================
Use SQL to backup =====================
 
 
######################## REPORT END HERE #######################
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.17
sys@ORA11G(192.168.1.37)>

好,此时可以安全做导出导入。待导入完后,恢复default role并检查:

sys@ORA11G(192.168.1.37)> alter user SYS default role ROLE_D,XDB_SET_INVOKER,XDBADMIN,JMXSERVER,IMP_FULL_DATABASE,APEX_ADMINISTRATOR_ROLE,JAVADEBUGPRIV,JAVAIDPRIV,RECOVERY_CATALOG_OWNER,DELETE_CATALOG_ROLE,CSW_USR_ROLE,OLAP_XS_ADMIN,DBA,WFS_USR_ROLE,XDB_WEBSERVICES_OVER_HTTP,AUTHENTICATEDUSER,JAVAUSERPRIV,ADM_PARALLEL_EXECUTE_TASK,ROLE_C,ROLE_A,OWB_DESIGNCENTER_VIEW,CWM_USER,OLAP_DBA,ORDADMIN,HS_ADMIN_ROLE,HS_ADMIN_SELECT_ROLE,DATAPUMP_IMP_FULL_DATABASE,EXP_FULL_DATABASE,ROLE_B,OWB$CLIENT,MGMT_USER,SPATIAL_CSW_ADMIN,CTXAPP,JAVA_ADMIN,OEM_MONITOR,HS_ADMIN_EXECUTE_ROLE,DATAPUMP_EXP_FULL_DATABASE,AQ_ADMINISTRATOR_ROLE,DBFS_ROLE,SPATIAL_WFS_ADMIN,OLAP_USER,XDB_WEBSERVICES,JAVA_DEPLOY,EJBCLIENT,JAVASYSPRIV,SCHEDULER_ADMIN,AQ_USER_ROLE,LOGSTDBY_ADMINISTRATOR,EXECUTE_CATALOG_ROLE,RESOURCE,OWB_USER,OLAPI_TRACE_USER,XDB_WEBSERVICES_WITH_PUBLIC,OEM_ADVISOR,GATHER_SYSTEM_STATISTICS,SELECT_CATALOG_ROLE,CONNECT;
 
User altered.
 
Elapsed: 00:00:00.07
 
--运行上面的程序:
###################### REPORT START HERE #######################
 
 
=============
The number of recursive role ================
APEX_030200...........................................2
CTXSYS................................................2
DATAPUMP_EXP_FULL_DATABASE............................5
DATAPUMP_IMP_FULL_DATABASE...........................10
DBA..................................................41
DBSNMP................................................3
EXECUTE_CATALOG_ROLE..................................1
EXFSYS................................................1
EXP_FULL_DATABASE.....................................4
HS_ADMIN_ROLE.........................................2
IMP_FULL_DATABASE.....................................4
JAVASYSPRIV...........................................1
LOGSTDBY_ADMINISTRATOR................................1
MDDATA................................................2
MDSYS.................................................2
MGMT_VIEW.............................................1
OEM_MONITOR...........................................2
OLAPSYS...............................................2
OLS_TEST..............................................4
OPS$JIMMY.............................................1
ORDSYS................................................1
OUTLN.................................................1
OWBSYS...............................................10
ROLE_A................................................2
ROLE_B................................................2
ROLE_C...............................................42
ROLE_D...............................................49
SCOTT.................................................2
SELECT_CATALOG_ROLE...................................1
SPATIAL_CSW_ADMIN_USR.................................3
SPATIAL_WFS_ADMIN_USR.................................3
SYS.................................................224 <=== Cause ORA-28031
SYSMAN................................................4
SYSTEM...............................................44
TEST.................................................44
TT1...................................................1
WMSYS.................................................3
XDB...................................................3
 
==================
Use SQL to backup =====================
alter user SYS default role ROLE_D,XDB_SET_INVOKER,XDBADMIN,JMXSERVER,IMP_FULL_DATABASE,APEX_ADMINISTRATOR_ROLE,JAVADEBUGPRIV,JAVAIDPRIV,RECOVERY_CATALOG_OWNER,DELETE_CATALOG_ROLE,CSW_USR_ROLE,OLAP_XS_ADMIN,DBA,WFS_USR_ROLE,XDB_WEBSERVICES_OVER_HTTP,AUTHENTICATEDUSER,JAVAUSERPRIV,ADM_PARALLEL_EXECUTE_TASK,ROLE_C,ROLE_A,OWB_DESIGNCENTER_VIEW,CWM_USER,OLAP_DBA,ORDADMIN,HS_ADMIN_ROLE,HS_ADMIN_SELECT_ROLE,DATAPUMP_IMP_FULL_DATABASE,EXP_FULL_DATABASE,ROLE_B,OWB$CLIENT,MGMT_USER,SPATIAL_CSW_ADMIN,CTXAPP,JAVA_ADMIN,OEM_MONITOR,HS_ADMIN_EXECUTE_ROLE,DATAPUMP_EXP_FULL_DATABASE,AQ_ADMINISTRATOR_ROLE,DBFS_ROLE,SPATIAL_WFS_ADMIN,OLAP_USER,XDB_WEBSERVICES,JAVA_DEPLOY,EJBCLIENT,JAVASYSPRIV,SCHEDULER_ADMIN,AQ_USER_ROLE,LOGSTDBY_ADMINISTRATOR,EXECUTE_CATALOG_ROLE,RESOURCE,OWB_USER,OLAPI_TRACE_USER,XDB_WEBSERVICES_WITH_PUBLIC,OEM_ADVISOR,GATHER_SYSTEM_STATISTICS,SELECT_CATALOG_ROLE,CONNECT;
 
 
######################## REPORT END HERE #######################
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.15
sys@ORA11G(192.168.1.37)>

看到已经恢复。



网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复