Home » RDBMS Server » Backup & Recovery » restoring RMAN backup to point in time on another server, different SID & directory structure (Windows, 11.2.0.3)
restoring RMAN backup to point in time on another server, different SID & directory structure [message #685775] Tue, 22 March 2022 15:08 Go to next message
Glenstr
Messages: 6
Registered: July 2015
Location: Canada
Junior Member
Most searches on this just show how to clone a prod DB to another server, which is not what I want to do.

I have a test server, with a test and a dev instances of prod, periodically refreshed by doing a user managed backup (put tablespace in backup - copy file(s) to test server folder(s) - end tablespace backup) that backs up datafiles directly to test servers directory(s), then startup nomount, recreate controlfile with new file locations, new SID, issue recover until cancel, apply an archived log or two, then open resetlogs.

The test server has one less drive than prod server, so all datafiles reside on D:\oracle\oradata\<sid> instead of on D:\oracle\oradata\<sid> and E:\oracle\oradata\<sid> drives

However, I have been asked if I can restore to a point in time prod was at 4 days ago, on prod server I do an RMAN backup with 7 day recovery window on disk, so all necessary backups reside on prod, which is fast recovery area on a Z: drive on prod server.

Test server does not have a Z drive, but I can map a Z drive on test to read the Z drive on prod.

Most articles I have found on this assume starting from scratch on target (test) server, and keeping database SID the same, and involve copying control file and SPFile over, as well as creating access to source DB via tnsnames.

In my case I have a create controlfile script that points to new file locations and SPfile is already there for the dev database, and there is no access to prod database via tnsnames.

Trying to find the simplest way to restore the test DB to a point in time from the RMAN backups on prod server, do I still need to copy controlfile and spfile over from prod then point at new datafile locations from within RMAN?

Any other suggestions?


Thanks in advance..

Report message to a moderator

Re: restoring RMAN backup to point in time on another server, different SID & directory structure [message #685776 is a reply to message #685775] Tue, 22 March 2022 15:17 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
If you start your test instance with a parameter file that uses OMF (which all databases should use) then you don't need to worry about the datafile names.
Duplicating combined with point-in-time recovery is straightforward. For example,

CONNECT TARGET SYS/oracle@prod
CONNECT AUXILIARY SYS/oracle@test
DUPLICATE TARGET DATABASE TO test UNTIL TIME 'SYSDATE-4';

Report message to a moderator

Re: restoring RMAN backup to point in time on another server, different SID & directory structure [message #685777 is a reply to message #685776] Tue, 22 March 2022 15:27 Go to previous messageGo to next message
Glenstr
Messages: 6
Registered: July 2015
Location: Canada
Junior Member
Thanks for reply, unfortunately neither test or prod in this case use OMF files, but when I am going to upgrade from 11.2 to 19.3 in the near future and will definitely be using OMF for the new one.



Report message to a moderator

Re: restoring RMAN backup to point in time on another server, different SID & directory structure [message #685778 is a reply to message #685777] Tue, 22 March 2022 15:32 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
The test DB will use OMF if you set the parameter before duplicating. Time to join the 21st century Smile
If for some bizarre reason you do not wish to do this, then your DUPLICATE command will need to be a bit more complicated to map the source file names to the destination. But still reasonably straightforward. And a lot simpler then messing about with creating your own controlfile.

Report message to a moderator

Re: restoring RMAN backup to point in time on another server, different SID & directory structure [message #685779 is a reply to message #685778] Tue, 22 March 2022 15:48 Go to previous messageGo to next message
Glenstr
Messages: 6
Registered: July 2015
Location: Canada
Junior Member
So if I understand you correctly, if I do this first I can duplicate the database to a point in time using your previously mentioned steps:

alter system set db_create_file_dest=’d:\oracle\oradata\<sid>’ scope=memory;
bounce the database, then shutdown
startup mount

CONNECT TARGET SYS/oracle@prod
CONNECT AUXILIARY SYS/oracle@test
DUPLICATE TARGET DATABASE TO test UNTIL TIME "to_date('03/17/2022 17:06:00 pm','mm/dd/yyyy hh24:mi:ss')";

Yes I do need to update/modernize my refresh procedures, the ones I use are old, but simple and relatively fast. When my 19c upgrades are done I will implement using RMAN to refresh test & dev databases.

Report message to a moderator

Re: restoring RMAN backup to point in time on another server, different SID & directory structure [message #685781 is a reply to message #685779] Wed, 23 March 2022 02:01 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
Quote:
alter system set db_create_file_dest=’d:\oracle\oradata\<sid>’ scope=memory;
bounce the database, then shutdown
startup mount
You have three lines above, and each one is wrong Smile
1. You must not use SCOPE=MEMORY
2. Why do that?
3. You cannot mount because at this stage there is no controlfile to be mounted

Report message to a moderator

Re: restoring RMAN backup to point in time on another server, different SID & directory structure [message #685784 is a reply to message #685779] Wed, 23 March 2022 08:01 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Quote:
alter system set db_create_file_dest=’d:\oracle\oradata\<sid>’ scope=memory;
bounce the database, then shutdown
Why are you setting the parm with scope=memory? Why are you then bouncing the database? What do you think happens to your parm - that exists ONLY in memory - when you bounce the database? Why do you think you need to bounce the database before simply shutting it down?

Report message to a moderator

Re: restoring RMAN backup to point in time on another server, different SID & directory structure [message #685785 is a reply to message #685784] Wed, 23 March 2022 12:08 Go to previous message
Glenstr
Messages: 6
Registered: July 2015
Location: Canada
Junior Member
typo I missed, was in the middle of another task when I answered - meant spfile.

I have not used RMAN to duplicate a DB often, the few DB's I do duplicate regularly I have a scripted procedure using the "old way" that works well & I just haven't got around to changing it to using RMAN

Report message to a moderator

Previous Topic: TABLE POINT IN TIME RECOVERY
Next Topic: restore 11g to 19c using rman
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jan 21 22:16:22 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.