Re: Oracle Backup to new SID

steven_nos..._at_yahoo.ca wrote:

Hi Steve,

> We have seven Oracle systems that have been running on Oracle 8i
> (8.1.6.3) Enterprise Edition with AIX 4 32-bit. They are slowly
> migrating to Oracle 9i (9.3) on AIX 5L 64-bit. (Our software has not
> been tested or certified for 10g yet...)
>

My guess is you mean 9.2.3?

> PROBLEM:
> ========
> We want to be able to take a copy of the production database and be
> able to load this data into an alternate ORACLE_SID environment for
> month-end reporting requirements. Only option 3 allows us to do this,
> but an export and import even with transportable tablespaces takes a
> long time. Ideally we want to try and get the transfer of the data and
> establishment of the mirrored environment down to a point that we
> reduce downtime for users to about 2 hours maximum for about 10GB of
> data.
>
> TABLESPACE_NAME SIZE
> --------------- -----
> DATA_PROD1 9437184000
> RBS_PROD1 3670016000
> TEMP_PROD1 838860800
> SYSTEM 157286400
>
> Two questions:
>
> 1) Other than breaking the hot standby database and using that for
> month-end reporting, does anyone have other ways of doing their backups
> for their Oracle data? In this case, Speed, portability, and
> reliability are the highest factors.

I would suggest reading up on logical (rather than physical) standby databases, as this may meet your need. It has some funky restrictions on datatypes, but it may not affect you.

>
> 2) Has anyone used the RMAN product from Oracle and how are its speeds?
> Is it able to reload a backup of a database into an alternate
> environment quite easily?
>

It's the only thing we use, and is really slick/fast, especially when you use multiple channels. You can use the DUPLICATE functionality within RMAN to create a copy of a database to another host.

>
> Presently what we do is the following:
>
> 1) Get users off for month-end
> 2) Put DATA tablespace in READ-ONLY mode.
> 3) Perform an export using TRANSPORT_TABLESPACE=Y to temp location on
> disk
> 4) Copy (UNIX cp command) the data files to the new SID locations.
> 5) Perform an import using TRANSPORT_TABLESPACE using the data from the
> temp location.
> 6) Change BOTH databases back to READ-WRITE mode and let users back on.
>
> This takes about 4 hrs presently and we need to try and cut this down
> to half. We don't let users back on between steps because after the
> data is copied we have one last step which is to roll the accounting
> period in our legacy software. If we do that step before reloading and
> we have no valid copy in the month-end SID, we cannot do our end of
> period reporting.
>
> Any ideas on how others handle this would be appreciated.

I would still suggest taking a look at a logical standby database. See tahiti.oracle.com for more information.

Regards,

Steve Received on Wed Aug 30 2006 - 15:15:35 CDT