今天一客户说在做impdp导入时，报错Ora-28031。我们看看ora-28031的报错是什么意思：
|
1 2 3 |
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个，一开始，我们用
|
1 |
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进行查询：
|
1 2 3 4 5 |
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的权限
|
1 2 |
select 'alter user sys default role '||wm_concat(granted_role)||';' from dba_role_privs where grantee = 'SYS' and default_role='YES'; |
(2)直接设置：
|
1 |
alter user SYS default roles DBA; |
(3)导出
(4)impdp导入后恢复，用第一步记录的SQL恢复
一般情况下，引起Ora-28031的role的往往是SYS，但是在极端情况下，也有可能是别的role，写了如下一段，可以检查，并备份超过148个role的授权语句。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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; / |
我们来测试一下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
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;然后再检查：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
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并检查：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
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)> |
看到已经恢复。