I was doing the migration for a huge database from HP-UX to Linux. My strategy is to export the dump from source database and import into the target database.
Out of the sudden, Oracle prompted this error during the import.
In the alert log, we found the DM00 process died which was import job is failed.
Throughout the trace files, we can see a lot of error messages stated
ORA-28031: maximum of 148 enabled roles exceeded
Description of the Problem
When a new session is initiated Oracle will attempt to enable all the default roles of the account if their number will be less than ( (value of the MAX_ENABLED_ROLES parameter) – 2 ). The default value of MAX_ENABLED_ROLES is 150. If the account is granted (directly or recursively) more than 148 roles then the session creation will fail with ORA-28031.
SQL> show parameter max_enabled_roles
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_enabled_roles integer 150
Solution
Check amount of roles is tight with SYS account (or other account run the import).
SQL> SELECT * FROM dba_role_privs WHERE Grantee='SYS' AND default_role='YES';
SQL> ALTER USER SYS DEFAULT ROLE CONNECT,EXP_FULL_DATABASE,JAVAUSERPRIV,OLAP_DBA, ...
(Recommendation for normal user, alter user <username> default role none;)
Cross check if there is data pump job still running or idle.
SQL> SELECT * FROM dba_datapump_jobs;
no rows selected
<
SQL> SET lines 200
SQL> COL owner_name FORMAT a10;
SQL> COL job_name FORMAT a20
SQL> COL state FORMAT a12
SQL> COL operation LIKE state
SQL> COL job_mode LIKE state
SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2; 2 3 4 5
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ ----------- ------------ ----------- ------------
SYS SYS_IMPORT_FULL_01 IMPORT FULL NOT RUNNING 0
SQL> SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------------- --------------------------------------------------
VALID 47842 TABLE SYS.SYS_IMPORT_FULL_01
>
If you find the orphaned data pump jobs which the job status is not running.
Please follow the Oracle document for the step.
How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)
Run again the import and see the output
$ nohup sh import.sh > output.txt &
Pretty job Done!
