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: David Barbour <>
Date: Sun, 5 Nov 2006 21:44:31 -0500
Message-ID: <>

Thank you very much Carel-Jan. I stopped managed recovery on the dataguard instance and did the TSPITR. When it finished, I took an immediate backup of the new tablespace, brought it on-line, then did the 'alter tablespace begin backup' and copied to the proper destination in the standby. When I resarted managed recovery, the logs applied to the point of the TSPITR on the primary and then I received the following in the mrp trace file:

Media Recovery Log /oracle/PR1/oraarch/1_7506.dbf Background Media Recovery terminated with error 1246

ORA-01246: recovering files through TSPITR of tablespace TF70
ORA-01110: data file 390: '/oracle/PR1/sapdata1/tf70_1/tf70.data1'
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 5923836Kb in 1068.43s => 5.41 Mb/sec Longest record: 64Kb, moves: 5504/12312368 (0%) Change moves: 7574926/23690586 (31%), moved: 3232Mb

So I shut down the standby, made a backup controlfile, moved it to the standby, and restarted the standby. Here's the alert.log entry:

Completed: alter database mount standby database Sun Nov 5 21:22:14 2006
alter database recover managed standby database disconnect Attempt to start background Managed Standby Recovery process MRP0 started with pid=14
MRP0: Background Managed Standby Recovery process started Media Recovery Waiting for thread 1 seq# 7506

All I had to do was manually register a number of archive logs that had been registered with the old controlfile and the standby is working just fine.

This list is priceless. Thanks to everyone who posts and has kept this resource active and interesting.

On 11/4/06, Carel-Jan Engel <> wrote:
> 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 Sun Nov 05 2006 - 20:44:31 CST

Original text of this message