Re: Backup optimization and transportable tablespaces

From: Seth Miller <>
Date: Wed, 10 Jun 2015 16:34:40 -0500
Message-ID: <>


Assuming your TTS tablespaces are not being imported into another database for PITR before being imported into the target database, the TTS tablespaces are being set to read only before the copy of the files takes place from the source database. If you were to time the TTS and backup processes so that the TTS process first set the tablespace to read only before the backup process started, then your backup process would be backing up read only tablespaces and it wouldn't matter if both processes were occurring at the same time.

If there are multiple tablespaces being copied with TTS, you could modify your backup process to synchronize with the TTS process. For example, you have three tablespaces that will be copied to another database using TTS -- T1, T2 and T3. Your backup/TTS synchronization process might look like this.

Set T1 read only -> backup tablespace T1 -> TTS T1 -> set T2 read only -> backup tablespace T2 -> TTS T2 -> ...

Seth Miller

On Wed, Jun 10, 2015 at 10:33 AM, Sandra Becker <> wrote:

> OS: Solaris10
> DB: EE
> Current database and backup configuration:
> - 19T database that we backup to tape--disk is not an option for me
> - Level 0 backups roughly once a month, Level 1 weekly, archivelogs
> multiple times per day
> - Backup optimization is OFF
> - No READ ONLY tablespaces
> - TTS process that runs 7 times a week to create new tablespaces, one
> per day, using transportable tablespaces between two databases. The target
> database is the 19T one.
> - Once data has been loaded, it is rarely updated after 90 days
> - Backups and the TTS process are not allowed to run at the same
> time. The people who wrote the very complex backup and TTS process scripts
> left the company over 2 years ago and they are not well documented.
> - Level1 backups are now running long enough that the TTS process
> doesn't run at it's next scheduled time causing us to fall behind and
> customer frustration because the expected data is not in the database
> I am looking at options to improve backup performance. From what I've
> read, I can set most of the tablespaces to READ ONLY and enable backup
> optimization so the tablespaces that don't change do not get backed up
> every week. Given the length of time for a Level 1 backup, schedule of
> Level 0 backups, and tape retention policy, I am currently at risk of not
> being able to restore this database if needed.
> Questions:
> 1. What would be valid reasons for not letting the backups and TTS
> process run at the same time? The TTS process can take up to 4 hours for a
> single day.
> 2. If backups and creating/loading the new tablespaces should not be
> done at the same time, I'm thinking that modifying the schedule would be an
> option I should consider so the TTS process completes before the backup
> kicks off. Does this sound reasonable?
> 3. Is my thinking around optimizing my backups valid? Is there
> another/better way I should consider? We would need to script something to
> change the tablespaces to READ ONLY on a daily or weekly basis.
> 4. If a tablespace is READ ONLY and has not been backed up yet. will
> it get picked up in a Level 0 or 1 backup even with backup optimization on?
> I am new to the transportable tablespace feature and haven't been able to
> figure out everything our TTS scripts are doing or why yet. I appreciate
> any suggestions and insight.
> --
> Sandy

Received on Wed Jun 10 2015 - 23:34:40 CEST

Original text of this message