Re: Oracle Backup to new SID

steven_nospam_at_yahoo.ca wrote:
> Hi All,
>
> BACKGROUND:
> ============
> 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...)
>
> One of the problem areas we have are the backup routines. In our
> present setup, we have three options we have set up using native AIX
> commands:
>
> 1) Backup with users off and database down (Cold Backup)
> 2) Backup with users on and database in BEGIN BACKUP mode (Hot Backup)
> 3) Export/Import using TRANSPORT_TABLESPACE=Y
>
> 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.
>
> 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?
>
>
>
> 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 don't think RMAN will be faster than a cp, since rman has to both extract the data to the backup area and then write it to the new database for duplicate database, while the cp just has to write it to where the duplicate database can see it.

But I'm wondering about 4 hours for 10G, that seems awfully slow for cp. I guess you have some really old hardware, perhaps from the 8i days? My RMAN backup takes about 2 hours, versus 1/2 hour for a DIRECT=Y export. My export is 24G (note that that export does not include indices aside from create statements, and takes 10x longer to import). I haven't looked to see how long a cp takes lately, but it is certainly less than rman.

I've seen this hanlded by getting new or refurbished last-generation hardware. For one customer, this meant the end of month processing was so much faster it is no longer considered a batch operation (they had originally hired me to make one report finish on time - I'm a software guy, not a hardware guy). Risc hardware + SAN cost less than a DBA sometimes.

For the time window issue, you might try making a dblink and using Create Table As Select over the dblink, and nologging operations for creating indices. Yes, even if you have lots of tables (look and see how many you are actually using).

A physical standby spreads the pain over the month. I don't know that a logical standby before 10g is a good idea, simply because it is new in 9.

RMAN is much better than hot backups, if for no other reason than it can generate less redo by not having to worry about the fractured block problem (see
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96519/backup.htm#1004914 if you don't know about that). I'm with those who say RMAN is the only way to go for backups these days (since 8174). Your specific EOP issues may be an exception, and one where I might say it is only temporary until you get everything to a proper sized system.

jg

--
@home.com is bogus.
Note the amount of free hard drive space required:
http://www.thenewyorkerstore.com/product_details.asp?mscssid=4FTRK2WKU5B78J7M6K9ESKVF9AQD8C87&sitetype=1&sid=122751&did=8&section=gifts?TNYS06_CAPC5&link_id=hdlink