Re: Transportable tablespace

Hot backup.... copy the backed up database files over, copy all the archived log files over, apply the archived log files... just before you want the sync point, do an "alter system archive log current" on the production database. Copy any unapplied archived logs, as well as that one you just created to the reporting db server. Apply those logs (recover database using backup controlfile until cancel), once that last one is applied, cancel the recovery and open database resetlogs.

You can do the backup of the database and the copy whenever you want, and just keep applying logs -- manual version of a standby database.

Actually -- you can do this using standby since you are on 8.1.6 -- create your standby database (the reporting one)... continue to apply archived log files throughout the month. Again, just before you want to sync the databases, do the alter system archive log current. Copy and apply that one. Then open your standby database in read-only mode (your temporary tablespace will have to be a locally managed tablespace because you can't write to the temp tablespace in a read-only standby db since that updates the data dictionary).

Once you are done with the reporting, return the db to standby and continue to apply archived logs throughout the next month.

This means you only have to do the full db backup once (you can add new datafiles to your standby when you add them to production). Added benefit, you have a fairly up to date copy of production should your production server fail.

Two things at once.

Woo hoo! I do love backup and recovery and the cool stuff you can do with it!

Rachel

>From: "DEMANCHE Luc (Cetelem)" <luc.demanche_at_cetelem.fr>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Transportable tablespace
>Date: Thu, 18 Jan 2001 02:52:24 -0800
>
>Hi DBAs,
>
>Oracle 8.1.6.2
>Sun solaris 2.6
>
>Every month (generally on the 15th) we have to duplicate our production
>DB on a production-reporting DB. It's not a replication case, it's a
>copy of one DB to another on a specific date. We did it using "insert
>as select" through a database link. We have to synchronize all changes
>(add new datafiles, create new tablespace, and so on) on the production
>DB to the production-reporting DB before the load. This requires a lot
>of time. And the load takes about 48 hours.
>
>I would like to use transportable tablespaces. I will transfer all my
>tablespaces (56 tablespaces) on my production-reporting DB.
>
>Does someone have a other ideas? Or a bad history about transportable
>tablespaces.
>I could do a cold backup, transfer the backup to a production-reporting
>disk and recreate the control file to change the DB name.
>
>What is the better solution?
>
>TIA
>
>
>-----------------
>Luc Demanche
>CETELEM
>Tél.: 01-46-39-14-49
>Fax : 01-46-39-59-88
>