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:
- Backup with users off and database down (Cold Backup)
- Backup with users on and database in BEGIN BACKUP mode (Hot Backup)
- 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:
- 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.
- 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:
- Get users off for month-end
- Put DATA tablespace in READ-ONLY mode.
- Perform an export using TRANSPORT_TABLESPACE=Y to temp location on
disk
- Copy (UNIX cp command) the data files to the new SID locations.
- Perform an import using TRANSPORT_TABLESPACE using the data from the
temp location.
- 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.
Steve
Received on Wed Aug 30 2006 - 15:03:12 CDT
