Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RMAN TSPITR and Dataguard

Re: RMAN TSPITR and Dataguard

From: Carel-Jan Engel <>
Date: Sat, 04 Nov 2006 23:55:07 +0100
Message-Id: <>

Hi David,

I'm not sure whether the newly 'imported' datafile will receive a new file-number or not.
Whatever: you can always do a 'alter tablespace begin backup', copy the datafiles to the standby, and 'alter tablespace end backup'. If you stop the managed recovery process at the standby before archives created at the TSPITR time get processed (or set a DELAY at the LOG_ARCHIVE_DEST_n long enought to copy the datafiles to the standby) the managed recovery process will find the datafile where it is expected as the DDL around the TSPITR arrives, and it will catch up. If that gives a problem, you can still create a new standby controlfile on the primary, copy it over to the standby, start it and start the managed recovery process. I don't think (although I haven't tested your particular case) youwill end up in reinstantiating the standby.

Besides, you are prepared pretty well already. What is the time pressure? Can you afford create a small test tablespace, make a backup, run some test load on it, and try your procedure with that tablespace? You just added another reason to my list te advocate having a small test database at your production system. I like to have the smallest database you can create available at production, setup with Data Guard in the same way as the production database. This small thing allows you to test whatever you need to do with the complete technology stack of production. I've run into problems (firewalls blocking ports, test server available but no test-standby etc.) too often. The small test database helps you to confirm that any part in the stack is configured in a way you can perform switch-overs/fail-overs when you need to, without additional problems.

Best regards,

Carel-Jan Engel

If you think education is expensive, try ignorance. (Derek Bok) ===

On Sat, 2006-11-04 at 16:55 -0500, David Barbour wrote:

> Of course, since it's the weekend .....
> I need to restore a tablespace in my production instance. I'm using
> RMAN and have full backups, logs, etc. Not a problem . Except I'm
> running a dataguard instance and I'm not sure what the effect is going
> to be. Here's my plan (pretty much already inplace):
> 1. The tablespace to be recovered has no dependencies on any other
> within the database.
> 2. I've created an auxiliary instance called 'clone' and entered
> parameters in
> tnsnames.ora and listener.ora referencing the instance. Re-loaded the
> listener.
> 3. I've created a new init.ora file called initclone.ora that has the
> following important parameters:
> db_name = PR1
> lock_name_space = clone
> log_archive_start = false
> remote_login_passwordfile = exclusive
> control_files = ( /sappr1mig/cntrl/cntrlclone.dbf)
> DB_FILE_NAME_CONVERT=('/oracle/PR1/','/sappr1mig/')
> LOG_FILE_NAME_CONVERT=('/oracle/PR1/','/sappr1mig/')
> 4. I'v created a directory in the tf70_1 directory
> in /oracle/PR1/sapdata2/ on
> both the target and the standby (Dataguard) databases.
> 5. I can startup nomount the clone, and connect to the clone, the
> target (PR1) and the catalog.
> 6. If I run the following script
> #!/usr/bin/ksh
> export ORACLE_HOME=/oracle/PR1/920_64
> export ORACLE_SID=clone
> export PATH=$PATH:$ORACLE_HOME/bin
> rman <<EOJ
> connect target sys/<password>@pr1
> connect auxiliary sys/<password>@clone
> connect catalog rmanadmin/<password>@rcat
> run
> {
> set newname for datafile '/oracle/PR1/sapdata1/tf70_1/tf70.data1'
> TO
> '/oracle/PR1/sapdata2/tf70_1/tf70.data1';
> recover tablespace tf70 until time '2006-11-03:19:00:00';}
> exit
> Then RMAN will
> A. Take the tf70 tablespace to be recovered offline.
> B. Restore the datafile to the auxiliary instance.
> C. Recover the restored datafile to the specified time.
> D. Open the auxiliary database with the RESETLOGS option.
> E. Export the dictionary metadata about objects in the recovered
> tablespaces—the DDL to create the objects along with pointers to the
> physical
> locations of those in the recovered datafile—to the target database.
> F. Shut down the auxiliary instance.
> G. Issue SWITCH command so that the target control file now points to
> the datafile in the recovery set that was just recovered at the
> auxiliary database.
> H. Import the dictionary metadata that was exported from the auxiliary
> instance, allowing the recovered object to be accessed.
> And my question: Is the dataguard instance is going to pick up the
> fact there is a newly recovered datafile in a different location and
> get that completely copied?
> I've created datafiles in the past with no problem, trucated tables
> and reloaded, etc. without issue, just not sure how the new datafile,
> which is being restored to the primary (target), is going to get
> copied up to the standby. It's not really a create tablespace or add
> datafile type command, or is it?

Received on Sat Nov 04 2006 - 16:55:07 CST

Original text of this message