Thanks for the response
But tom my team is so dumb that they are still using some guys script who left the company & he made use of export instead of datapump.

So I have no option but to use import because they have already done the exporting job and they want me to use the hidden parameter _disable_logging parameter

How fast it will be if I keep the database in noarchive log and do import when compared with using the disable logging parameter.

And what's the exact difference between keeping database in noarchive log mode and that of using _disable_logging parameter. I want a detail comparison between these two.

Thanks in advance.

Kishore,
I agree with Tom...using Data pump is the preferred way to refresh schemas from release 10g and don't even think about tinkering with system parameters. "Your team" or you can easily do a data pump export of the original data and use those files to do a data pump import. Unlike the deprecated export/import, datapump will export a 500GB data in an hour or so...use the parallel parameter in expdp par file for better performance...check help by typing "expdp help=y" or go through the documentation.

100% true - unless the original poster has no chance to export his 500 GB with a datapump. The question was "need to import around 500 GB of data". That may meant that data already exist as an export file. It is not clear if they may be exported once more (this time with a datapump).

Is it the case?

Hi tom,

let me know the Advantage of datapump export(expdm) and import(impdm) over original export(exp)
and import(imp)

Hi tom,

let me know the Advantage of datapump export(expdp) and import(impdp) over original export(exp)
and import(imp)

Thanks Tom....

Since my team insisted on performing the export by setting the _disable_logging to true, I thought if getting some safe steps to perform the import without getting into the problem of database crashing.

But I don't why you are not at all ready to talk about it. Did I asked something that I'm not supposed to ask or else i'm missing something. I really don't the reason for your angry.


Anyway thanks a lot again and this blog is really awesome and I will keep on following it. Good job and I hope you continue to do this service..

@ Oleksandr Alesinskyy 

Exactly oleksandr...what you said is my present case.

Don't know how long it takes and will it be successful. I will keep posted about the import results and the steps I followed to import by enabling the disable logging parameter.

Sorry, forgot to include my name in the above reply. Its me who replied as a reader above.

Kishore,
You mentioned your team as "dumb" in the post above...not sure why you are insisting on not following Tom's advice where he clearly says "do not use _disable_logging, it will corrupt your database"...what part of that statement don't you understand? You seem to follow your team's advice blindly than the advice of a senior technical architect of Oracle Corporation.

You can use the old export/import feature in release 10.2.0.4, and assuming you are importing data into a dev/qa environment it should be ok to put those databases in no archive log mode during the import.

Suriya, here is a link that explains the advantages of Data pump.
http://www.datadisk.co.uk/html_docs/oracle/data_pump.htm

I know that it dangerous to change hidden parameters and it will corrupt the database....but my team is not listening to it. So I lost temper and that's where I used the dumb word.

My team says the last guy has done this way but no body is guarantee whether the steps used by that guy are going to work and still my team to ready to take the risk.

So I thought of asking a senior oracle architecture like Tom whether he knows the exact order of steps to perform the task.

That's what all I expected from Tom or any one on this blog.

I may be wrong that I called my team dumb for which I apologize but I was so helpless that I lost my temper on this topic.

I will be still thankful to any senior architecture like Tom if they know the exact steps to perform this task by that parameter.

I just expected someone will be there who might have not faced any database corruption problem and successfully done the import task.

Either find a New Team or a New Job.
This forum is for pure Technical problems not political ones.
Sir Tom Kyte already gave you the correct way to do things.
Just follow it and set a good example for your Team , then they may listen.
Change is law of Nature.

You have used the word "dumb" in the most appropriate way.

Now 2 questions:
1. Is there a possibility to re-export the data (using datapump)?
2. What is your position in the team (just a rank-and-file developer? DB specialist? Team lead?

If answer to 1 is yes, just go this way.
Else
if answer to 2 is "team lead" or "DB specialist" then disregard what the team is saying and follow the Tom's advice.
else relax.

Hi,

I am trying to use datapump for data migration.
I am able to successfully export data and create dump files using below
expdp system/manager@test schemas=scott directory=EXPORT_DIR parallel=4 dumpfile=export_file_%U.dmp logfile=export2.log

Now I am trying to import these dump files to another schema
impdp system/manager@test1 schemas=A directory=IMPORT_DIR job_name=hr dumpfile parallel=4
But I am getting following error -
ORA-39002: invalid operation
ORA-39165: Schema A was not found.

Don't we have something like remap_schema in transportable tablespace here? What am i doing wrong here?

Hello,

We have two servers sitting on two different data centers. Database export using IMDDP NETWORK_LINK took almost 8 hours to transfer a 25 GB database whereas, the same database with EXPDP to a file, SCP the dmp file and IMPDP took less than hour. This is 8 times difference between the two methods.

In both the cases, the Indexes, constraints and triggers were first disabled before IMPORT ran . What could cause for such a big difference?

A brief note on my IMPDP using network link option:
1. First IMPORTED the schema only (with flashback_scn value and NETWORK_LINK value)
2. Disabled indexes, constraints and triggers
3. IMPDP table data (one table at a time) with parallel 8 (but flashback_scn parameter wasn't used here)
4. Enable Indexes, constraints and triggers

the EXPDP and IMPDP par files are:

directory=wcd
dumpfile=ocsp%U.dmp
filesize=5G
flashback_scn=10915100804148
logfile=ocsp.log
full=y
job_name=WCD
parallel=4
status=300

directory=wcd
dumpfile=ocsp%U.dmp
exclude=statistics
exclude=SCHEMA:"='SYS'"
exclude=SCHEMA:"='SYSTEM'"
exclude=SCHEMA:"='OMNIBACKUP'"
logfile=ocsp.log
full=y
job_name=WCD
parallel=4
status=300


Thanks,

Dear Tom ,

recently involved migration activity. as a part of it , we taken export and imported some schemas into new server

while importing , we needed following details

1) at what speed currently impdp importing : for example how many GB per Hour
2) so far howmuch completed out of total size
3) how many hours need if it runs with current speed

we queried v$session_longops .. but feeling it is not giving correct values.

is there any other way to find out values for above questions perfectly

Appreciate your Help for DBA world

Thanks in advance

Aliyar, this data pump utility is a thing of beauty. I use it all the time, so let me show you an example. I used EXPDP but it works exactly the same way for IMPDP:

1. Start you data pump normally at a command shell, and get the identifier (SYS_EXPORT_FULL_01 in this example):

[oracle@taurus ~]$ export ORACLE_SID=YOURDB
[oracle@taurus ~]$ expdp system/****** directory=BACKUP_DIR dumpfile=test.dmp full=y

Export: Release 10.2.0.5.0 - 64bit Production on Monday, 27 June, 2011 17:01:36

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=BACKUP_DIR dumpfile=test.dmp full=y 
Estimate in progress using BLOCKS method...
(... lots of work here ...)

2. Go to another shell* and do this:

[oracle@taurus ~]$ export ORACLE_SID=YOURDB
[oracle@taurus ~]$ expdp system/****** attach=SYS_EXPORT_FULL_01

It will show you the status right away (see below). Leave it there, and just type "STATUS" whenever you want to see what is happening.

Export: Release 10.2.0.5.0 - 64bit Production on Monday, 27 June, 2011 17:03:11

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_01
  Owner: SYSTEM                         
  Operation: EXPORT                         
  Creator Privs: FALSE                          
  GUID: A6B805164FDE87B3E040A8C0D2010E2B
  Start Time: Monday, 27 June, 2011 17:01:44
  Mode: FULL                           
  Instance: TSTSUP03
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=BACKUP_DIR dumpfile=test.dmp full=y 
  State: EXECUTING                      
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /u03/backup/test.dmp
    bytes written: 4,096
  
Worker 1 Status:
  State: EXECUTING                      
  Object Schema: KIPREV
  Object Name: AF_SE_ENDERECOS
  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE
  Completed Objects: 1,749
  Worker Parallelism: 1

Export> 

* It can be done in the same shell but I am not man enough to hit CTRL+C after hours of run :-)

Hi Tom,

Well, this is a new question, however, it still is related to the topic.

My client has a 10g R1 (10.1.0.4.0) database on AIX 64-bit and needs their current database characterset be changed from US7ASCII to AL32UTF8. They have CLOBs and BLOBs. I plan to use the CSALTER with selective Import. Now for the testing, what I initially did was to use exp/imp, and not Data Pump, as stated in this Metalink Document ID 260192.1:
"Do NOT use Expdp/Impdp when going to (AL32)UTF8 or an other multibyte characterset on ALL 10g versions lower then 10.2.0.4 (including 10.1.0.5). Also 11.1.0.6 is affected.
It will provoke data corruption unless Patch 5874989 is applied on the Impdp side. Expdp is not affected, hence the data in the dump file is correct.Also the "old" exp/imp tools are not affected.
This problem is fixed in the 10.2.0.4 and 11.1.0.7 patch set"


My concern is, just the same as the others, how I can improve my exp/imp process? It took me 3hours to export the selected tables only, and in theory, import would likely take 2x the export time. Since this is a 24x7 production and client would want to minimize the downtime (I gave them an estimate of 14 hours of downtime).

So far, these are the parameters I set:
EXP
DIRECT=Y

IMP
INDEXES=N


Since it contains LOBs, I did not set BUFFER. Also, since it will be exp/imp on the same database, I did not use RECORDLENGTH as well. Kindly enlighten me as to what parameters can I also set to optimize my exp/imp given my current environment.

Thank you so much!