Re: Export/Import with Physical Standby

On Fri, Oct 2, 2009 at 11:07 AM, Sweetser, Joe <JSweetser_at_icat.com> wrote:

> One thing that might have an impact depending on the volatility of the
> data as it is being exported….I have been burned by not specifying
> flashback_time, which is the data pump “equivalent” of export’s
> consistent=Y. And you wouldn’t know there was an issue until you did the
> impdp. The line in your parfile could look something like this:
>
>
>
> flashback_time="to_timestamp('28-09-2009 02:05:31', 'DD-MM-YYYY
> HH24:MI:SS')"
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *David Barbour
> *Sent:* Friday, October 02, 2009 8:59 AM
> *To:* Ian Cary
> *Cc:* oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
> *Subject:* Re: Export/Import with Physical Standby
>
>
>
> Thank you. I was wondering about that parameter. I really haven't used
> datapump much - no real need and the exports I've done over the past three
> years or so have all been for 3rd party vendors who haven't been real
> comfortable with Oracle, let alone something 'new' like datapump.
>
> After doing some research, I think that is the way I'm going to tackle
> this.
>
> Regarding some eother questions/observations, I have identified the tables
> and scripted the whole thing with 6 parfiles (that pesky 3600K limit on the
> parfile size thing).
>
> By running parallel=2, I was able to export all the data in 25 minutes. I
> can live with that. I can't practice the import, not even on our
> development system, but I've got an 8-hour window for this.
>
> We have really good bandwith to our standby site. We're using arch for
> apply, and have a three-hour apply delay (in case of corruption on the
> Primary). We've got 16 redologs at 500MB each, and writing them to a RAID 1
> array with lots of high RPM drives (about the only thing that works really
> well in our SAN). Deferring the send makes sense though. I can (and have)
> caught up several hundred logs in a relatively short time. For bigger lots,
> I've srcipted the compress/send/uncompress/register process so that if it
> becomes necessary .....
>
> So the new plan looks like:
>
> Stop the application - defer standby log dest
> Create the new tablespaces and datafiles --> this should propagate to the
> standby
> Export tables using datapump
> Drop tables and indexes --> this should propagate to the standby
> Import tables using datapump with remap_tablespace option --> this should
> propagate to the standby
> Start application and test
> Enable standby log dest
>
> Unless there are other issues, I really want to thank everyone for their
> input. Everybody had good points and they forced me to re-evaluate and
> re-think the entire exercise looking at a variety of options. With Oracle,
> there is generally more than one way to accomplish a given task.
>
> Much appreciated.
>
>
>
> On Fri, Oct 2, 2009 at 4:57 AM, Ian Cary <ian.cary_at_ons.gsi.gov.uk> wrote:
>
> Just a couple of questions regarding you approach.
>
> 1. Why not use the REMAP_TABLESPACE option in datapump rather than having
> to extract the DDL and manually recreate the objects.
> 2. If you do use the DDL approach I'd suggest just creating the tables
> first, then import the data only and then create indexes/constraints as
> this should quicker.
>
> Another possiblty is also to use the COPY command which will cope with your
> LONG columns and then rename the objects afterwards rather than using
> expdp/impdp.
>
> Cheers,
>
> Ian
>
>
>
> |---------+----------------------------->
> | | david.barbour1_at_gma|
> | | il.com |
> | | Sent by: |
> | | oracle-l-bounce_at_fr|
> | | eelists.org |
> | | |
> | | |
> | | 01/10/2009 22:44 |
> | | Please respond to |
> | | david.barbour1 |
> | | |
> |---------+----------------------------->
>
> >--------------------------------------------------------------------------------------------------------------|
> |
> |
> | To: oracle-l_at_freelists.org
> |
> | cc:
> |
> | Subject: Export/Import with Physical Standby
> |
>
> >--------------------------------------------------------------------------------------------------------------|
>
>
>
>
>
> Good Morning,
>
> I've got a requirement to move about 300GB of table and index data from the
> current tablespaces into new tablespaces. There is a physical standby in
> place. The standby is located in another city. The database is 7TB in
> size. I am running Oracle 10.2.0.4 on AIX 6.1.
>
> The standby is currently mounted with the STANDBY_FILE_MANAGEMENT
> initialization parameter is set to AUTO. We are not using ASM.
>
> I'd like to get a sanity check on the plan I've put together.
>
> Stop the application
> Create the new tablespaces and datafiles --> this should propagate to the
> standby
> Get table and index DDL using dbms_metadata.get_ddl
> Export tables using datapump
> Drop tables and indexes --> this should propagate to the standby
> Edit DDL to recreate tables and indexes in new tablespaces
> Run create scripts --> this should propagate to the standby
> Import tables using datapump --> this should propagate to the standby
>
> All tables will remain in the same schema.
>
> Comments appreciated.
>
>
>
> This email was received from the INTERNET and scanned by the Government
> Secure Intranet anti-virus service supplied by Cable&Wireless in
> partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) In
> case of problems, please call your organisation’s IT Helpdesk.
> Communications via the GSi may be automatically logged, monitored and/or
> recorded for legal purposes.
>
> For the latest data on the economy and society consult National Statistics
> at http://www.statistics.gov.uk
>
>
> *********************************************************************************
>
>
> Please Note: Incoming and outgoing email messages are routinely monitored
> for compliance with our policy on the use of electronic communications
>
> *********************************************************************************
>
>
> Legal Disclaimer : Any views expressed by the sender of this message are
> not necessarily those of the Office for National Statistics
>
> *********************************************************************************
>
>
> The original of this email was scanned for viruses by the Government Secure
> Intranet virus scanning service supplied by Cable&Wireless in partnership
> with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On leaving the GSi
> this email was certified virus free.
> Communications via the GSi may be automatically logged, monitored and/or
> recorded for legal purposes.
>
>
>

--
http://www.freelists.org/webpage/oracle-l