Home » RDBMS Server » Server Utilities » impdp not successfull (Oracle 10g Version 10.2.1.0.1 Windows 7)
impdp not successfull [message #581893] Thu, 11 April 2013 12:03 Go to next message
ashussain
Messages: 35
Registered: March 2011
Location: Saudi Arabia
Member

Hello!

I have exported a schema (gscc) from a Test Server
running Oracle 10g version 10.2.0.5.0 on Windows 2003 SP2, 2GB RAM.
Instance name : gscc and schema name: gscc

The export dump was successfully created as the last line of the export log shows:

"Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at 02:24:04"

I want to import this dumpfile on a stand-alone dedicated pc running Oracle 10g versoin 10.2.0.1.0
on Windows 7 OS with 2GB RAM. Oracle binaries are installed on this system in C-drive whereas I have
created the necessary tablespaces on E-drive as C-drive was not having sufficient free space (around 150GB).

On the target-pc instance name is "test" and I have created a schema for import as below:

SQL> create user jct identified by jct default tablespace PROD temporary tablespace TEMP;
SQL> grant unlimited tablespace to jct;
SQL> grant dba to jct;
SQL> grant exp_full_database to jct;
SQL> grant imp_full_database to jct;
SQL> alter user jct default role all;


Then tried to Import (from 'gscc' schema to 'jct' schema) as follows:

impdp jct/jct directory=dpump dumpfile=gssc-dump.dmp remap_schema=gscc:jct

When I start this job of importing, its taking too much time (more than 4-5 hours) only to finish
unsuccessfully.

=========================================================
here are a few initial lines from the import log file.
=========================================================
Import: Release 10.1.0.4.2 - Production on Wednesday, 10 April, 2013 14:48

Copyright (c) 2003, 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 "JCT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JCT"."SYS_IMPORT_FULL_01": jct/******** directory=dpump dumpfile=gssc-dump.dmp remap_schema=gscc:jct
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JCT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01919: role 'EXPRESS_ROLES' does not exist
Failing sql is:
GRANT "EXPRESS_ROLES" TO "JCT"
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'SEQUENCE_ROLE' does not exist
Failing sql is:
GRANT SELECT ON "JCT"."ACCOUNT_GKEY" TO "SEQUENCE_ROLE"

==================================================
here are last lines from the import log file.
==================================================
GRANT DEBUG ON "JCT"."ARCH_TRUCK_VISITS" TO "TABLE_ROLE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'TABLE_ROLE' does not exist
Failing sql is:
GRANT FLASHBACK ON "JCT"."ARCH_TRUCK_VISITS" TO "TABLE_ROLE"
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Job "JCT"."SYS_IMPORT_FULL_01" stopped due to fatal error at 11:07:22
Job SYS_IMPORT_SCHEMA_01 has been reopened at Thursday, 11 April, 2013 11:09
Job "SYS"."SYS_IMPORT_SCHEMA_01" stopped due to fatal error at 11:09:50
Job SYS_IMPORT_FULL_01 has been reopened at Thursday, 11 April, 2013 11:10
Job "SYS"."SYS_IMPORT_FULL_01" stopped due to fatal error at 11:10:30

==================================================

I think there may be thousands of errors. With my little knowledge, I can understand that all these errors are
only because of "ORA-01919: role 'EXPRESS_ROLES' does not exist" error, as this role only contain SEQUENCE_ROLE, PROCEDURE_ROLE,TABLE_ROLE,VIEW_ROLE etc.

Now my question is, when the export was successfully completed (as per the log file), does it not export all those roles
which were part of exported schema (in this case, gscc)?

Please help me how to solve this issue.


Thanks in advance

Aijaz
Re: impdp not successfull [message #581894 is a reply to message #581893] Thu, 11 April 2013 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer depends on the export command you failed to post.
In short, if you tell Oracle to export it then there is no reason it exports it.

Regards
Michel
Re: impdp not successfull [message #582007 is a reply to message #581894] Fri, 12 April 2013 08:58 Go to previous messageGo to next message
ashussain
Messages: 35
Registered: March 2011
Location: Saudi Arabia
Member

Michel, Thanks a lot for your reply. My export command was like this:

expdp sys/******** AS SYSDBA directory=pump dumpfile=gssc-dump.dmp logfile=gsclog.log schemas=gscc
Re: impdp not successfull [message #582011 is a reply to message #582007] Fri, 12 April 2013 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you only export the schema then you don't export the roles.
A role is not part of a schema.

Regards
Michel

[Updated on: Fri, 12 April 2013 09:15]

Report message to a moderator

Re: impdp not successfull [message #582034 is a reply to message #582011] Fri, 12 April 2013 11:53 Go to previous messageGo to next message
ashussain
Messages: 35
Registered: March 2011
Location: Saudi Arabia
Member

Then, Plz. Michel tell me how to import the roles alongwith schema.

Thanks
Aijaz
Re: impdp not successfull [message #582035 is a reply to message #582034] Fri, 12 April 2013 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Michel tell me how to import the roles alongwith schema.
since ROLEs were NOT exported, it is not possible to import them.

some, many, most software professionals maintain application source code within Code Repository; like SUBVERSION.
simply extract desired DDL from it & apply to the new DB
Re: impdp not successfull [message #582058 is a reply to message #582035] Fri, 12 April 2013 15:12 Go to previous messageGo to next message
ashussain
Messages: 35
Registered: March 2011
Location: Saudi Arabia
Member

If I want to export it again alongwith ROLEs, then what will be the syntax of expdp, will it be like the following

expdp user/password directory=<dir.name> dumpfile=<dumpfile.dmp> logfile=<logfile.log> schemas=<schemaname> GRANTS=Y

If not, plz. tell me the correct "expdp" syntax to export a schema including all the ROLEs granted to objects

Thanks
Re: impdp not successfull [message #582062 is a reply to message #582058] Fri, 12 April 2013 15:21 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
expdp help=yes

where do you see GRANTS=Y?
Previous Topic: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat
Next Topic: How to load date filed along with time stamp using sqlldr
Goto Forum:
  


Current Time: Thu Mar 28 10:30:20 CDT 2024