Re: Datapump export, incremential only?

From: <japplewhite_at_austinisd.org>
Date: Wed, 10 Aug 2011 16:56:59 -0500
Message-ID: <OF21E1496E.4B8E8496-ON862578E8.00780A83-862578E8.007899AA_at_austinisd.org>



Chris,

I don't know of a manageable way to accomplish what you want with your main DB, though I'm certainly no DataPump guru. You might consider setting up a Physical Standby for the DB in question, script the opening of it in Read-Only mode for overnight backups, doing a guaranteed consistent export without the time constraints, then putting it back into Managed Recovery mode to catch back up with the Primary during the day. That would take the main DB out of the picture for you. A Logical Standby would work, too, but they're much more of a maintenance burden, IMHO, dealing with both.

Jack C. Applewhite - Database Administrator Austin I.S.D. - MIS Department
512.414.9250 (wk) / 512.935.5929 (pager)

From: "Grabowy, Chris" <chris.grabowy_at_lmco.com> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Date: 08/10/2011 04:46 PM

Subject:        Datapump export, incremential only?
Sent by:        oracle-l-bounce_at_freelists.org



We have been struggling with having Datapump exports complete every night. Sometimes with execution times. Other cases with dump file sizes.  

We added the compress option and that helped with generating smaller dump files with no obvious impact to the execution time, but that hasn’t really helped with the overall execution time.  

We have played with the parallel option with some success.  

Anyway one of the DBAs updated the script to generate a datapump parm file that contains a list of the tables that changed in the last two days. Even though we execute datapump every night he felt it would be safer to generate a list of changed tables for the last two days. He generates the list of changed tables by querying dba_tab_modifications. With this change, the datapump exports are obviously faster and the dump files much smaller.  

We still do a weekly full datapump export on Sundays.  

Anyway, were kind of scratching our heads and trying to figure out if this could come back and bite us somehow. Paranoia is a required DBA trait…  

We do understand that when importing we might have to go back to the Sunday save to import a table since it might have not been saved in any of the incrementals. We are saving/organizing the log files to easily grep for the desired table.  

I know that incremental was not a stable/valid option in the old export tool, but you would think that Oracle would have figured out how to do incremental datapump exports by now…using some sort of defined criteria. At least, looking thru the doc I did not find such an option. Am I just being naïve here and missing the bigger picture on the viability of a datapump/export incremental feature?  

Thoughts? Suggestions? Insults?  

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 10 2011 - 16:56:59 CDT

Original text of this message