Re: Export/Import with Physical Standby

From: David Barbour <david.barbour1_at_gmail.com>
Date: Fri, 2 Oct 2009 10:58:51 -0400
Message-ID: <69eafc3f0910020758j260affd4p37815182243e3f1_at_mail.gmail.com>

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

Received on Fri Oct 02 2009 - 09:58:51 CDT