Mike, September 21, 2018 - 5:30 am UTC
this is some of the error's i encountered during import. based from my understanding, roles are not yet present in my target database. does that mean i have to create every role in my source to my target db? btw i am using the PGAPRPHP newly created user for the import command as this user is also the owner of my source.
impdp userid=PGAPRPHP/mypassword full=Y directory=temp_dir dumpfile=fulldatapump2.dmp logfile=fulldatapumpimp.log
;;;
Import: Release 10.2.0.1.0 - Production on Friday, 21 September, 2018 13:15:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "PGAPRPHP"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
ORA-39154: Objects from foreign schemas have been removed from import
Starting "PGAPRPHP"."SYS_IMPORT_FULL_02": userid=PGAPRPHP/******** full=Y directory=temp_dir dumpfile=fulldatapump2.dmp logfile=fulldatapumpimp.log
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"PGAPRPHP" already exists
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01919: role 'ADMIN' does not exist
Failing sql is:
GRANT "ADMIN" TO "PGAPRPHP" WITH ADMIN OPTION
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01919: role 'AGENTS_ROLE' does not exist
Failing sql is:
GRANT "AGENTS_ROLE" TO "PGAPRPHP" WITH ADMIN OPTION
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01919: role 'DOUBLE_RISK_ROLE' does not exist
Failing sql is:
GRANT "DOUBLE_RISK_ROLE" TO "PGAPRPHP" WITH ADMIN OPTION
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01919: role 'TREASURY_ROLE' does not exist
To Mike
J. Laurindo Chiappa, September 24, 2018 - 1:35 pm UTC
Hi, Mike : the main point in Connor´s answer was :
expdp system/password full=Y ....
ie, the EXPORT must be done in FULL MODE : only doing so all the ROLEs, public objects, user GRANTs and alike will be contained in the dump file... Do you did this when running the export ?? If not, the "full=y" in your impdp command is bogus, ok ??
Regards,
Chiappa
To J. Laurindo Chiappa
Mike, September 25, 2018 - 3:16 am UTC
Hi,
yes i have included the full=y in my expd. during export, i did not get any errors. that is why i am curious why it is looking for roles and such when executing the import.
here is my exact export command.
expdp userid=PGAPRPHP/mypassword full=Y directory=temp_dir dumpfile=fulldatapump2.dmp logfile=fulldatapump2.log
September 26, 2018 - 1:31 am UTC
Sorry - I can't replicate that behaviour. I did this on my 11.2.0.4 instance - I though it might depend on who creates the role, so I did this
C:\>sqlplus system/oracle
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 26 09:12:26 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create role created_as_dba_user;
Role created.
SQL> conn / as sysdba
Connected.
SQL> create role created_as_sysdba_user;
Role created.
--
-- and then full export
--
C:\Users\comcdona>expdp system/oracle full=y directory=temp dumpfile=full_db11.dmp
Export: Release 11.2.0.4.0 - Production on Wed Sep 26 08:55:48 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y directory=temp dumpfile=full_db11.dmp
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 541.6 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
...
...
...
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
C:\TEMP\FULL_DB11.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 1 error(s) at Wed Sep 26 09:15:06 2018 elapsed 0 00:02:06
--
-- then full import into a freshly created (with dbca) database
--
C:\>sqlplus system/newdb
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 26 09:29:42 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory tempx as 'c:\temp';
Directory created.
;;;
Import: Release 11.2.0.4.0 - Production on Wed Sep 26 09:18:55 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** full=y directory=temp dumpfile=full_db11.dmp logfile=impdp_db11x.log
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
ORA-31684: Object type TABLESPACE:"EXAMPLE" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file 'C:\ORACLE\ORADATA\DB11\UNDO01.DBF'
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists
Failing sql is:
CREATE UNDO TABLESPACE "UNDO01" DATAFILE 'C:\ORACLE\ORADATA\DB11\UNDO01.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 52428800 MAXSIZE 20000M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ORDDATA" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
...
...
...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 7269 error(s) at Wed Sep 26 09:21:25 2018 elapsed 0 00:02:22
and then at the end, I checked my roles in the new database
SQL> select role from dba_roles order by 1;
ROLE
------------------------------
ADM_PARALLEL_EXECUTE_TASK
APEX_ADMINISTRATOR_ROLE
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
AUTHENTICATEDUSER
CAN_USE_FILESTORE_INDEX
CONNECT
CREATED_AS_DBA_USER <============
CREATED_AS_SYSDBA_USER <============
CSW_USR_ROLE
CTXAPP
CWM_USER
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
DBA
DBFS_ROLE
DELETE_CATALOG_ROLE
EJBCLIENT
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
GLOBAL_AQ_USER_ROLE
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_ROLE
HS_ADMIN_SELECT_ROLE
IMP_FULL_DATABASE
JAVADEBUGPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVAUSERPRIV
JAVA_ADMIN
JAVA_DEPLOY
JMXSERVER
LOGSTDBY_ADMINISTRATOR
MGMT_USER
OEM_ADVISOR
OEM_MONITOR
OLAP_DBA
OLAP_USER
OLAP_XS_ADMIN
ORDADMIN
OWB$CLIENT
OWB_DESIGNCENTER_VIEW
OWB_USER
RECOVERY_CATALOG_OWNER
RESOURCE
SCHEDULER_ADMIN
SELECT_CATALOG_ROLE
SPATIAL_CSW_ADMIN
SPATIAL_WFS_ADMIN
WFS_USR_ROLE
WM_ADMIN_ROLE
XDBADMIN
XDB_SET_INVOKER
XDB_WEBSERVICES
XDB_WEBSERVICES_OVER_HTTP
XDB_WEBSERVICES_WITH_PUBLIC
57 rows selected.
Hi tom
Mike, September 26, 2018 - 9:14 am UTC
so, i returned my linux vm to a snapshot with a fresh install of oracle. just wanted to try it again. but still, no luck.
here are the steps i made
source:
$expdp userid=system/mypassword full=Y directory=transport_tablespace dumpfile=fulldatapump3.dmp logfile=fulldatapump3.log
no errors encountered during export. dmp file is 13G in total.
target:
SQL> CREATE OR REPLACE DIRECTORY transport_tablespace AS '/u01/transport_tablespace/';
$impdp userid=system/mypassword full=Y directory=transport_tablespace dumpfile=fulldatapump3.dmp logfile=fulldatapump3imp.log
so this is what i get as results of the import. it seems that in the attempt to create a tablespace, it looks for the source datafile path which ifcourse is not present in my source. do i have to create them first with the exact path?
;;;
Import: Release 10.2.0.1.0 - Production on Wednesday, 26 September, 2018 10:52:55
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": userid=system/******** full=Y directory=transport_tablespace dumpfile=fulldatapump3.dmp logfile=fulldatapump3imp.log
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u02/oradata/ITTEST/drsys01.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "DRSYS" DATAFILE '/u02/oradata/ITTEST/drsys01.dbf' SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u02/oradata/ITTEST/tools01.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "TOOLS" DATAFILE '/u02/oradata/ITTEST/tools01.dbf' SIZE 52428800 AUTOEXTEND ON NEXT 327680 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
ORA-31684: Object type TABLESPACE:"USERS" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u03/oradata/ITTEST/xdb01.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "XDB" DATAFILE '/u03/oradata/ITTEST/xdb01.dbf' SIZE 52428800 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u03/oradata/ITTEST/newphxdata01s.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "PHXDATA01_S" DATAFILE '/u03/oradata/ITTEST/newphxdata01s.dbf' SIZE 2097152000,'/u03/oradata/ITTEST/phxdata01s.dbf' SIZE 1073741824,'/u03/oradata/ITTEST/phxdata02s.dbf' SIZE 10737418240 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MAN
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u05/oradata/ITTEST/phxindx01s.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "PHXINDX01_S" DATAFILE '/u05/oradata/ITTEST/phxindx01s.dbf' SIZE 1610612736 AUTOEXTEND ON NEXT 209715200 MAXSIZE 5210M,'/u02/oradata/ITTEST/newphxindx01s.dbf' SIZE 209715200 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL U
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u04/oradata/ITTEST/newphxdata11.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "PHXDATA11_M" DATAFILE '/u04/oradata/ITTEST/newphxdata11.dbf' SIZE 2097152000 AUTOEXTEND ON NEXT 524288000 MAXSIZE 30000M,'/u04/oradata/ITTEST/newphxdata11m.dbf' SIZE 2097152000 AUTOEXTEND ON NEXT 524288000 MAXSIZE 30000M,'/u04/oradata/I
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u03/oradata/ITTEST/phxindx11m.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "PHXINDX11_M" DATAFILE '/u03/oradata/ITTEST/phxindx11m.dbf' SIZE 1073741824,'/u03/oradata/ITTEST/newphxindx11m.dbf' SIZE 2097152000 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4194304 SEGMENT SPACE MANAGEME
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u02/oradata/ITTEST/phxblob01s.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "PHXBLOB01_S" DATAFILE '/u02/oradata/ITTEST/phxblob01s.dbf' SIZE 536870912 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 SEGMENT SPACE MANAGEMENT MANUAL
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u03/oradata/ITTEST/euldata01s.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "EULDATA01_S" DATAFILE '/u03/oradata/ITTEST/euldata01s.dbf' SIZE 536870912 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 SEGMENT SPACE MANAGEMENT MANUAL
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u02/oradata/ITTEST/eulindx01s.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "EULINDX01_S" DATAFILE '/u02/oradata/ITTEST/eulindx01s.dbf' SIZE 536870912 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 SEGMENT SPACE MANAGEMENT MANUAL
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u02/oradata/ITTEST/spkdata01s.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "SPKDATA0101_S" DATAFILE '/u02/oradata/ITTEST/spkdata01s.dbf' SIZE 268435456 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 SEGMENT SPACE MANAGEMENT MANUAL
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/dbvisit/oradata/tablespace/dbvisit.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Failing sql is:
CREATE TABLESPACE "DBVISIT" DATAFILE '/dbvisit/oradata/tablespace/dbvisit.dbf' SIZE 524288000 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
ORA-39082: Object type PASSWORD_VERIFY_FUNCTION created with compilation warnings
ORA-31684: Object type PASSWORD_VERIFY_FUNCTION already exists
Processing object type DATABASE_EXPORT/PROFILE
ORA-39083: Object type PROFILE failed to create with error:
ORA-28004: invalid argument for function specified in PASSWORD_VERIFY_FUNCTION VER_PASS_FUNCTION
Failing sql is:
CREATE PROFILE "PGA_PROF" LIMIT COMPOSITE_LIMIT DEFAULT SESSIONS_PER_USER DEFAULT CPU_PER_SESSION DEFAULT CPU_PER_CALL DEFAULT LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL DEFAULT IDLE_TIME 15 CONNECT_TIME DEFAULT PRIVATE_SGA DEFAULT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LIFE_TIME 7776000/86400 PASSWORD_REUSE_TIME UNLIMITED PAS
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ANONYMOUS" already exists
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'DRSYS' does not exist
Failing sql is:
CREATE USER "WKSYS" IDENTIFIED BY VALUES '69ED49EE1851900D' DEFAULT TABLESPACE "DRSYS" TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'DRSYS' does not exist
Failing sql is:
CREATE USER "WKPROXY" IDENTIFIED BY VALUES 'B97545C4DD2ABE54' DEFAULT TABLESPACE "DRSYS" TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'PHXDATA01_S' does not exist
Failing sql is:
CREATE USER "PGAPRPHP" IDENTIFIED BY VALUES '5E0290C75720E95E' DEFAULT TABLESPACE "PHXDATA01_S" TEMPORARY TABLESPACE "TEMP"
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'EULDATA01_S' does not exist
Failing sql is:
CREATE USER "PGAPRPHPEUL" IDENTIFIED BY VALUES 'BCE486317603A029' DEFAULT TABLESPACE "EULDATA01_S" TEMPORARY TABLESPACE "TEMP" ACCOUNT LOCK
ORA-39083: Object type USER failed to create with error:
ORA-02380: profile PGA_PROF does not exist
Failing sql is:
CREATE USER "RCONP00O" IDENTIFIED BY VALUES 'F9801C72486FF6AB' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE "PGA_PROF" PASSWORD EXPIRE ACCOUNT LOCK
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'SPKDATA0101_S' does not exist
Failing sql is:
CREATE USER "PERFSTAT" IDENTIFIED BY VALUES 'AC98877DE1297365' DEFAULT TABLESPACE "SPKDATA0101_S" TEMPORARY TABLESPACE "TEMP" ACCOUNT LOCK
ORA-31684: Object type USER:"TSMSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SYSMAN" already exists
ORA-31684: Object type USER:"MGMT_VIEW" already exists
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'DBVISIT' does not exist
Failing sql is:
CREATE USER "DBVISITPOC" IDENTIFIED BY VALUES '12330930D481F257' DEFAULT TABLESPACE "DBVISIT" TEMPORARY TABLESPACE "TEMP"
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"LOGSTDBY_ADMINISTRATOR" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"CTXAPP" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"MGMT_USER" already exists
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS while calling KUPP$PROC.CHANGE_USER [PROC_SYSTEM_GRANT]
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded
ORA-06512: at "SYS.KUPW$WORKER", line 5168
ORA-06512: at "SYS.KUPW$WORKER", line 12639
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.KUPW$WORKER", line 6248
----- PL/SQL Call Stack -----
object line object
handle number name
0x3f18f868 14916 package body SYS.KUPW$WORKER
0x3f18f868 6300 package body SYS.KUPW$WORKER
0x3f18f868 12279 package body SYS.KUPW$WORKER
0x3f18f868 3279 package body SYS.KUPW$WORKER
0x3f18f868 6889 package body SYS.KUPW$WORKER
0x3f18f868 1262 package body SYS.KUPW$WORKER
0x3f182160 2 anonymous block
Job "SYS"."SYS_IMPORT_FULL_01" stopped due to fatal error at 10:53:27
September 27, 2018 - 2:35 am UTC
OK, a couple of things to fix here
1) ORA-28031: maximum of 148 enabled roles exceeded
You'll need to "max_enabled_roles" parameter in your target instance. Its possibly set in your source database, but not necessarily because over time people may have created roles and then revoked them from users. During the import, we temporarily might have roles assigned to people (and hence enabled) that will be revoked later.
2) Tablespaces
If you are moving to new file locations, you need to let the database know! So your options would be:
a) pre-create the tablespaces in the target. You'll then get warnings about them existing, but the import will proceed
or
b) use the REMAP_DATAFILE parameter to dynamically change the path in the import file to reflect the new location
To Mike
J. Laurindo Chiappa, September 26, 2018 - 1:35 pm UTC
First thing, the line :
"ORA-28031: maximum of 148 enabled roles exceeded"
must be ONE of the culprits : makes sense roles not being created IF the maximum limit is reached....
Second, the tablespaces WILL be created in the SAME path/directories, the lines :
"
CREATE TABLESPACE "PHXBLOB01_S" DATAFILE '/u02/oradata/ITTEST/phxblob01s.dbf' SIZE 536870912 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 SEGMENT SPACE MANAGEMENT MANUAL
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/u03/oradata/ITTEST/euldata01s.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
"
must be part of the problem, too : of course, if the tablespaces are not created, the OBJECTS inside will NOT be created....
Third, if you have PUBLIC objects to be imported (such as public synonyms, say) the user connected in impdp MUST have the permission to create them : normally SYSTEM have it, but you can try to connect with SYS, SYS surelly have all the needed privs...
And last but not least, some schemas will NOT be needed in the dest database, so exclude them :
impdp full=y .... exclude=schema:"in ('SYS', 'SYSTEM', 'DBVISIT'..., ...)
Fix these things....
Regards,
Chiappa
Follow this step
Niraj Bhaskarbhai Jani, September 26, 2018 - 4:41 pm UTC
Hi,
First of all,
Source:
1)Check what different tablespace you have.
Use this:
select * from v$tablespace;
OR
select tablespace_name
from dba_tablespaces
where tablespace_name not in ('SYSTEM', 'SYSAUX') and
contents not in ('UNDO', 'TEMPORARY');
2) Once you know what different tbs you have in Source.
Run below Query in source to create script:
SELECT 'CREATE TABLESPACE ' || TABLESPACE_NAME || ' datafile ''/oradata/' || LOWER(name) || '/datafile/' || TABLESPACE_NAME || '.dbf'' SIZE ' || bytes/1024/1024 || 'M AUTOEXTEND ' || REPLACE(AUTOEXTENSIBLE, 'YES', 'ON') || ' NEXT 100M MAXSIZE ' || MAXBYTES/1024/1024 || 'M;'
from dba_data_files, v$database
where autoextensible = 'YES' and tablespace_name not LIKE 'UNDO%' AND tablespace_name not LIKE 'SYS%'
GROUP by tablespace_name, file_name, bytes/1024/1024, autoextensible, maxbytes/1024/1024, name UNION
SELECT 'CREATE TABLESPACE ' || TABLESPACE_NAME || ' datafile ''/oradata/' || LOWER(name) || '/datafile/' || TABLESPACE_NAME || '.dbf'' SIZE ' || bytes/1024/1024 || 'M AUTOEXTEND ' || REPLACE(AUTOEXTENSIBLE, 'NO', 'ON') || ' NEXT 100M MAXSIZE ' || MAXBYTES/1024/1024 || 'M;'
from dba_data_files, v$database
where autoextensible = 'NO' and tablespace_name not LIKE 'UNDO%' AND tablespace_name not LIKE 'SYS%'
GROUP by tablespace_name, file_name, bytes/1024/1024, autoextensible, maxbytes/1024/1024, name;
Above query will give you script to create tablespace and and associated datafile in destination.
Destination :
1) Create database
2) Run that "Create tablespace" script.
Once you are done with this, just verify your source and destination have same tablespace and associated datafiles.
Mean time check how many Temp tablespace and Undo tablespace you have in source, Try to mimic the same:
You can use this to check:
SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 from dba_temp_files;
SQL> select tablespace_name from dba_tablespaces where tablespace_name like ('%UNDO%');
My suggestion is to keep the name of Temp and Undo same as it is in Source. sometimes, in source you have some weird name for temp or undo like, temp5 or temp1 or undotbs1, undotbs3, undots. Compare source and destination. Once you're satisfied with you both environment.
Go to Source:
Create dump directory where you want to store your dump file after expdp.
Run :
expdp "'/ as sysdba'" full=Y exclude=schema:\"IN \(\'WMSYS\'\,\'XDB\'\,\'TSMSYS\'\,\'MDSYS\'\,\'ORDSYS\'\,\'DBSNMP\'\,\'OLAPSYS\'\)\" directory=directory_name dumpfile=export_file_name.dmp logfile=log_file_name.log;
you can ignore exclude clause from above script, but I would keep it because it will minimize errors while impdp.
Once you have dump file, transfer it to Destination.
Create directory where you put your dump file.
Run :
impdp "'/ as sysdba'" full=Y directory=destination_directory_name dumpfile=export_file_name.dmp logfile=log_file_name.log;
If you take great care while creating tablespace and datafiles, your impdplog will have errors which you can ignore.
If you're seeing :
ORA-31684
ORA-39151
ORA-39111
ORA-39082
in your impdp log, You can ignore them. They are just saying that those objects are already there.
You can compare your objects in source and destination :
select count(*),
owner
from dba_objects
group by owner;
If you follow these steps, I am 100 % certain, you will have successful migration.
Thank You.
September 27, 2018 - 2:35 am UTC
nice input
To Connor
J. Laurindo Chiappa, September 27, 2018 - 12:23 pm UTC
Hi : sure, pre-creating the tablespaces one will avoid errors regarding tablespaces not being created due to lack of space, different mountpoints and alike, right... But waht about the :
"ORA-28031: maximum of 148 enabled roles exceeded"
?? To me, it implies in a wrongly parameterized database OR a lot of extra roles created in the destination db and not in the origin db, right ?
Regards,
Chiappa
September 30, 2018 - 7:29 am UTC
My hypothesis is that someone has more than 148 roles granted to them, but not all are enabled (ie, default). But perhaps during a datapump import we temporarily are enabling them and having issue.
Precreating the roles could possbily resolve this.
To J. Laurindo Chiappa, TOM, Niraj Bhaskarbhai Jani
Mike, September 28, 2018 - 6:33 am UTC
first of all i would like to thank you for replying to my questions and would like to apologies as if i was able to send my import logs errors earlier, you would have known how to help me.
so, i pre-created the tablespaces before importing like what Niraj suggested. upon importing, seems different as i dont see errors for a couple of minutes. until i encountered this
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"LOGSTDBY_ADMINISTRATOR" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"CTXAPP" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"MGMT_USER" already exists
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS while calling KUPP$PROC.CHANGE_USER [PROC_SYSTEM_GRANT]
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded
ORA-06512: at "SYS.KUPW$WORKER", line 5168
ORA-06512: at "SYS.KUPW$WORKER", line 12639
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.KUPW$WORKER", line 6248
----- PL/SQL Call Stack -----
object line object
handle number name
0x3f18f868 14916 package body SYS.KUPW$WORKER
0x3f18f868 6300 package body SYS.KUPW$WORKER
0x3f18f868 12279 package body SYS.KUPW$WORKER
0x3f18f868 3279 package body SYS.KUPW$WORKER
0x3f18f868 6889 package body SYS.KUPW$WORKER
0x3f18f868 1262 package body SYS.KUPW$WORKER
0x3f182160 2 anonymous block
Job "SYS"."SYS_IMPORT_FULL_01" stopped due to fatal error at 10:53:27
then, i googled how to increase max roles. i get this error when increasing.
SQL> ALTER SYSTEM SET max_enabled_roles=200 SCOPE=SPFILE;
ALTER SYSTEM SET max_enabled_roles=200 SCOPE=SPFILE
*
ERROR at line 1:
ORA-00068: invalid value 200 for parameter max_enabled_roles, must be between 1
and 148
SQL>
i guess the max is 148. how should i proceed?
September 30, 2018 - 7:31 am UTC
See my other view - try pre-creating the roles.
In terms of "SYS not being accessible"....the only time I've seen that is when data vault is in use.
If that is the case, see the docs:
https://docs.oracle.com/cd/E11882_01/server.112/e23090/dba.htm#DVADM70314 for instruction on how to use datapump in that circumstance. If not, then that would suggest some fundamental with the way the target database has been created (or modified)
To Mike
J. Laurindo Chiappa, September 28, 2018 - 12:53 pm UTC
Well, the first point here is : to be able to import a dump file, one MUST create a database first and later the dump file is imported into this newly created database, right ? See, normally this new database MUST be created Exactly Equal the original database, ie, with the SAME install options, the SAME features, the SAME init parameters...
My first guess is : the new database was NOT created exactly equal the original one, so maybe parameters such like MAX_ENABLED_ROLES are different, or some features/options not present in the original db ARE present in the new db... IF you are unsure, my recommendation would be : DROP this database and recreate it using the SAME parameters and install only the very same options/features present in origin...
Another possibility for the error regarding roels would be bugs or abnormal situations such like reported in the metalink/My Oracle Support note "DataPump Import Errors With Repeated ORA-39083 ORA-31625 And ORA-28031 During OBJECT_GRANTS" (Doc ID 1101035.1) : it is referring version 11.2.0.x but Maybe it could occurs in your 10g version : ask for a DBA to check this....
The second point was about the lines :
"ORA-31625: Schema SYS is needed to import this object, but is unaccessible"
maybe this one is related with the first point (say, the RDBMS is unable to create the roles neeeded by impdp to create objects in other schemas), but Other possibilities exist : for example,
https://www.dbarj.com.br/en/2015/03/impdp-failing-with-ora-31625-and-ora-01031-insufficient-privileges-with-database-vault/ speaks about a situation with DataVault in action,
http://ioracle-dba.blogspot.com/2014/02/impdp-does-not-createduserschema-until.html speaks about IMP_FULL_DATABASE role not receiving the CREATE USER privilege....
I say, first solve the ROLEs creation issue (asking for a DBA to check the new database, and recreating it if needed), and later try a new import connecting as SYS and excluding the internal schemas....
Best regards,
Chiappa
You can do this
Niraj Bhaskarbhai Jani, October 02, 2018 - 10:18 pm UTC
Everyone gave Very nice input on this.
Now for the issues you're getting with roles and sys tbs, I would recommend to delete the newly created database and Start it from fresh.
> Create the database on destination server.
TAKE BACKUP OF SPFILE BEFORE CHANGING ANYTHING.
> Grab the Pfile from source and destination server.
> Compare both the pfiles. Since Your db version is different,and you're getting issues with roles, You should use same parameter as you have in source pfile. It is not necessary though.
To do this:
run below in source and destination:
Create pfile from spfile;
you can provide full path where you want to store pfile.
Once you're done with destination pfile modification,
Shut the destination database and start it again with modified pfile.
SQL> Shutdown immediate;
SQL> Startup pfile='Location_of_pfile';
once your database is up and running, create spfile from pfile.
SQL> create spfile from pfile='location_of_pfile';
shut the database again and startup.
SQL> shutdown immediate;
SQL> startup;
Now, it will bring up your database with updated spfile.
Once your destination database is stable,
You can follow my past post for migration.
If you want, while creating tablespaces and datafiles before hand, you can add some buffer so that while import, you will not run into any errors.
Thank You and hope you will not run into any more issues now.
To Niraj
Mike, October 03, 2018 - 3:17 am UTC
Source Pfile:
ITTEST.__db_cache_size=3355443200
ITTEST.__java_pool_size=67108864
ITTEST.__large_pool_size=16777216
ITTEST.__shared_pool_size=771751936
ITTEST.__streams_pool_size=33554432
*._gby_hash_aggregation_enabled=FALSE
*._hash_join_enabled=TRUE
*._optimizer_cost_based_transformation='OFF'
*._unnest_subquery=false
*.aq_tm_processes=1
*.audit_file_dest='/u05/AUDIT_LOGS'
*.audit_sys_operations=TRUE
*.audit_trail='DB_EXTENDED'
*.background_dump_dest='/u01/app/oracle/admin/ITTEST/bdump'
*.commit_write='NOWAIT'
*.compatible='10.2.0'
*.control_file_record_keep_time=15
*.control_files='/u02/oradata/ITTEST/control01.ctl','/u03/oradata/ITTEST/control02.ctl','/u04/oradata/ITTEST/control03.c
tl'#Restore Controlfile
*.core_dump_dest='/u01/app/oracle/admin/ITTEST/cdump'
*.db_block_size=8192
*.db_cache_size=0
*.db_domain=''
*.db_file_multiblock_read_count=128
*.db_name='ITTEST'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='/u05/oradata/ITTEST/'
*.db_unique_name='ITTEST'
*.db_writer_processes=4
*.fast_start_mttr_target=300
*.hash_area_size=2097152
*.java_pool_size=0
*.job_queue_processes=10
*.large_pool_size=0
*.log_archive_dest_2='SERVICE=PGADR LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PGADR'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='enable'
*.log_archive_format='PGAPRPHP_%s_%t_%r.ARC'
*.log_archive_max_processes=4
*.nls_date_format='DD-MON-YYYY:HH24:MI:SS'
*.open_cursors=1000
*.optimizer_index_caching=80
*.optimizer_index_cost_adj=20
*.pga_aggregate_target=3221225472
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.session_max_open_files=20
*.sga_max_size=4294967296
*.sga_target=4294967296
*.shared_pool_size=0
*.sort_area_size=524288
*.standby_file_management='AUTO'
*.streams_pool_size=0
*.undo_management='AUTO'
*.undo_retention=10800## orig
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/ITTEST/udump'
-bash-3.2$
Target Pfile:
PGAPRPHP.__db_cache_size=423624704
PGAPRPHP.__java_pool_size=4194304
PGAPRPHP.__large_pool_size=4194304
PGAPRPHP.__shared_pool_size=167772160
PGAPRPHP.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PGAPRPHP/adump'
*.background_dump_dest='/u01/app/oracle/admin/PGAPRPHP/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/PGAPRPHP/control01.ctl','/u01/app/oracle/oradata/PGAPRPHP/control02.ctl','/u01/app/oracle/oradata/PGAPRPHP/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/PGAPRPHP/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='PGAPRPHP'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PGAPRPHPXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=201326592
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=605028352
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/PGAPRPHP/udump'
[oracle@OracleLinux5 dbs]$
what exactly should i copy from source? i tried changing all parameters that has number values and copying what the source has, upon starting the database, i get the "ORA-27102: out of memory" error. maybe because source has a higher physical memory than my target?
October 03, 2018 - 4:02 am UTC
See my other review - I don't think you should be worried about parameters
To Connor
Mike, October 03, 2018 - 3:21 am UTC
Hi Connor,
Since roles are a lot, is there a way to make an export of it and import it to my target db? would that export include grants or just the roles being created? creating them one by one would take a lot of time. sorry but im still new oracle.
regards,
Mike
October 03, 2018 - 4:01 am UTC
You can use datapump, but perhaps just as easy would be:
SQL> select 'create role '||role||';' r
2 from dba_roles
3 where oracle_maintained = 'N';
R
---------------------------------------------
create role SQLDEV_AUTOTRACE;
create role CAN_USE_FILESTORE_INDEX;
create role XXX;
or *similar (ie, check the columns in DBA_ROLES on your source to see how roles are being used) and run that into your environment before import.
To Chiappa
Mike, October 03, 2018 - 3:24 am UTC
Hi,
how can i get the parameters from my source db and copy it when creating a new DB in may target? what specific parameters should i look into? Sorry as im still pretty new with oracle.
Regards,
Mike
October 03, 2018 - 3:57 am UTC
I wouldnt be concerned with parameters here
To Connor
Mike, October 03, 2018 - 9:10 am UTC
Hi Connor,
while studying Data Pump in an Oracle Database Vault Environment as you suggested, i tried precreating roles and did the import. i still got the error of 148 roles exceeded. is this related to the database vault environment?
[oracle@OracleLinux5 ~]$ impdp "'/ as sysdba'" full=Y directory=data_pump dumpfile=fulldatapump3.dmp logfile=fulldatapump3.log
Import: Release 10.2.0.1.0 - Production on Wednesday, 03 October, 2018 13:02:10
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_IMPORT_FULL_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 389
ORA-39077: unable to subscribe agent KUPC$A_1_20181003130214 to queue "KUPC$C_1_20181003130211"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 248
ORA-25448: rule SYS.KUPC$C_1_20181003130211$1 has errors
ORA-28031: maximum of 148 enabled roles exceeded
[oracle@OracleLinux5 ~]$
October 04, 2018 - 12:49 am UTC
Take a look at MOS Note 778785.1
You might need to set some reduced set of default roles before exporting
To Mike
Niraj Bhaskarbhai Jani, October 03, 2018 - 4:27 pm UTC
If this is production and Critical environment, Parameter matching between source and destination is advisable in order to reduce performance issue in near future. But It is not required.
I would Agree with Connor McDonald not to worry about parameter right now.
My first preference here will be to reduce those number of roles assigned to user either by some one or by some process.
If I were you, I would first find out that user.
Use this:
select "Grantee", count(*) "Role Number" from
(select distinct connect_by_root grantee "Grantee", granted_role
from dba_role_privs
connect by prior granted_role=grantee)
group by "Grantee"
having count(*)>=148
order by count(*),"Grantee","Role Number";
Once you know which is causing an issue, Try to figure out roles assigned to it. If it is possible, drop some of the roles which are not necessary. It is not possible to increase that MAX_ENABLED_ROLES parameter above 150. So the option you have is to bring that number down to 148. You can always create roles again in destination later on.
Hope this will help you.
To Niraj
Mike, October 04, 2018 - 12:46 am UTC
So i ran the script you gave and got no rows as result. tried changing the count and found out 1 user to have 137 roles.
SQL> select "Grantee", count(*) "Role Number" from (select distinct connect_by_root grantee "Grantee", granted_role from
dba_role_privs connect by prior granted_role=grantee) group by "Grantee" having count(*)>=137 order by count(*),"Grante
e","Role Number";
Grantee
--------------------------------------------------------------------------------
Role Number
-----------
PGAPRPHP
137
SQL>some users have 30 roles or less in them. does the error 148 roles exceeded counted as all roles granted to the users or for just a single user? because from my count all roles granted to all users must be 300+.
is there a way to exclude certain roles for PGAPRPHP during import rather than dropping them in my source database? i wouldn't want to drop them and have problems with my source.
Additional for Niraj
Mike, October 04, 2018 - 12:52 am UTC
For now i am doing this with my backup database or testing database as my source. eventually if i make this work, will be doing it to our production database. so yes all steps needed to make for a problem free db in the future is welcome. but my question is, what parameters are those. i have sent my pfiles both source and target and unsure what to copy.
To Connor
Mike, October 04, 2018 - 3:07 am UTC
im not sure if this would help identify the problem. i tried excluding the user PGAPRPHP during the import with this command but got same error
impdp userid=\'/ as sysdba\' full=Y directory=data_pump dumpfile=fulldatapump3.dmp logfile=fulldatapump3.log exclude=user:"PGAPRPHP"
tried exluding the roles itself and still got the same error.
impdp userid=\'/ as sysdba\' full=Y directory=data_pump dumpfile=fulldatapump3.dmp logfile=fulldatapump3.log exclude=roles
is it safe to assume that the problem is not just the roles exceed itself? since i already excluded them during import?
October 05, 2018 - 3:39 am UTC
As I said, take a look at MOS Note 778785.1
Perhaps a way forward would be:
- set users to a limited set of default roles (as per the note)
- expdp
- impdp
To Connor
Mike, October 05, 2018 - 5:10 am UTC
unfortunately, i cant see MOS Note 778785.1 in the internet. would you have a direct link or could you post the steps needed to make?
thanks!
Regards,
Mike
October 13, 2018 - 4:36 am UTC
What to Check When Dealing With Ora-28031: Maximum Of 148 Enabled Roles Exceeded? (Doc ID 778785.1) To BottomTo Bottom
In this Document
Goal
Solution
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.
Checked for relevance on 12-Mar-2014
GOAL
How to investigate and eliminate the ORA-28031 error?
SOLUTION
#1. One must check first the number of roles granted to a user or role. It is useful to know that, with the sole exception of IDENTIFIED GLOBALLY roles, as documented, the roles created by a user are automatically granted to that user.
Check the number of roles that have been granted to all users and roles, using the following query:
This query should indicate the number of roles that have been granted to all users, including roles that have been granted via roles.
select "Grantee", count(*) "Role Number" from
(
select distinct connect_by_root grantee "Grantee", granted_role
from dba_role_privs
connect by prior granted_role=grantee
)
group by "Grantee"
order by "Grantee","Role Number"
/
Equally helpful could be the following query, returning the roles that have been granted to a specific user:
select distinct connect_by_root grantee,granted_role
from dba_role_privs
connect by prior granted_role=grantee
start with grantee='<user>';
Keeping in mind, as seen at:
MAX_ENABLED_ROLES
documentation page, that the number of enabled roles for a user cannot be larger than 148, if any of the above users has more than 148 roles enabled, some policy should be envisaged so that the roles are not be enabled all at once.
Of course, the easiest thing that can be done is to reset the number of default roles for the user, as indicated at the ALTER USER documentation page:
alter user <username> default roles <list of roles>;
The list of roles can be: an explicit list of roles, all roles or all roles except <list of roles>.
Sometimes, it happens that sessions running as SYS (this is mostly common in job sessions) fail with the ORA-28031 and the user SYS has indeed more than 148 roles. However, normal sessions don't have any problems connecting and running commands in the database.
The SYS user does not require all the roles that have been defined in the database. Simply set DBA as the only default role for sys:
alter user SYS default roles DBA;
and this should eliminate the error when running statistics taking jobs or advisories.
The most difficult scenario to address situation is when the application enables or disables roles and, for some reasons, the number of jobs defined for a user surpasses 148. In this situation, the application must be checked and commands like:
set roles all;
or
execute dbms_session.set_role('ALL');
princems, November 30, 2018 - 5:36 pm UTC
Hi,
is there any document for solaris vs linux commands. thanks.
December 03, 2018 - 3:30 am UTC
princems, November 30, 2018 - 10:54 pm UTC
We are migrating Oracle from Solaris to RHEL 7 and looking for Solaris equivalent commands for Linux. Could you please help me here by proving any info .
Here is my current salaries system details.
Machine hardware: sun4v
OS version: 5.10
Processor type: sparc
Hardware: sun4v
thanks.
December 03, 2018 - 3:19 am UTC
A reader, December 03, 2018 - 11:43 pm UTC
Hi sir,
Thank you for your reply...We are using 500+ KSH scripts in Solaris ..so, i am looking for Journal RHEL commands which are using in Solaris.. Please help me here by providing any link. Thanks.
December 04, 2018 - 5:39 am UTC
You can use ksh in Linux too
A reader, October 04, 2019 - 7:25 pm UTC