Rman, restore database to another host with a different disk layout.
When restoring a database backup to another system, you can specify the new locations of the datafiles to be restored to using the AUXFILE option in Rman, defining the old location and the new location for the files.
Because redo is applied against the datafile number effectively, this new database can still be restored to the current point in time as required.
Process.
Create the rman backup from the source database.
- Copy the rman backup to the destination machine.
- List the datafiles on the source database.
- Edit the parameter file to reflect the new environment.
- Configure SQL*Net if restoring from a catalog
- Set up the password file
- Startup nomount
- Restore the control files.
- Mount the database.
- Specify the new file locations in the rman session.
- Restore the database.
- Recover the database to the last archive log from the source machine.
- Alter database open.
Create the pfile from the backup pfile.
Take the backup pfile, place it in the dbs (or wherever you keep it and soft link to it).
Update the CONTROL_FILE location as required.
Startup nomount, and restore the controlfile from the backup.
RMAN> restore controlfile from '/opt/oracle/bkup/ctrl_c-4035512619-20070730-03';
Starting restore at 30-JUL-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/oracle/product/9.2.0/oradata/EBUSPROD/control01.ctl
output filename=/opt/oracle/product/9.2.0/oradata/EBUSPROD/control02.ctl
output filename=/opt/oracle/product/9.2.0/oradata/EBUSPROD/control03.ctl
Finished restore at 30-JUL-07
Delete the existing password file and re-create.
orapwd file=orapwEBUSPROD entries=5 password=changeme
RMAN> alter database mount;
Edit the pfile to represent the new layout of the server.
Startup nomount
Restore the control file backups from the backup set.
Ensure that the datafile names are unique on the source database. They should be.
select substr(name, instr(name,'/',-1),length(name)-instr(name,'/',-1)), count(*)
from v$datafile
group by substr(name, instr(name,'/',-1),length(name)-instr(name,'/',-1))
having count(*) > 1
/
If any datafile has the same name, then edit the path and name combination to ensure that they are unique for recovery.
Get the list of the source database filenames.
1 /oradata/system01.dbf
2 /oradata/system02.dbf
3 /oradata/system03.dbf
4 /oradata/system04.dbf
5 /oradata/system05.dbf
6 /oradata/ctxd01.dbf
7 /oradata/configdb001.dbf
8 /oradata/xx_datamartdb001.dbf
9 /oradata/xx_isdb001.dbf
10 /oradata/logdb001.dbf
11 /oradata/xx_odsdb001.dbf
12 /oradata/xx_ucsarchivedb001.dbf
13 /oradata/xx_ucsmaindb001.dbf
14 /oradata/APPS_TS_MEDIA02.dbf
15 /oradata/xx_odsdb002.dbf
16 /oradata01/APPS_TS_QUEUES02.dbf
17 /oradata01/APPS_TS_TX_DATA06.dbf
18 /oradata01/proddata/customerdb001.dbf
19 /oraindex/APPS_TS_TX_IDX08.dbf
20 /oradata01/proddata/xx_ucsmaindb002.dbf
21 /oraindex02/APPS_TS_TX_IDX09.dbf
22 /oradata01/APPS_TS_TX_DATA07.dbf
23 /oraindex02/APPS_TS_TX_IDX10.dbf
24 /oradata/system13.dbf
25 /oradata/system12.dbf
26 /oradata01/APPS_TS_QUEUES03.dbf
27 /oraindex02/APPS_TS_TX_IDX11.dbf
288 /oradata/system10.dbf
295 /oradata/system06.dbf
314 /oradata/portal01.dbf
351 /oradata/system07.dbf
352 /oradata/system09.dbf
353 /oradata/system08.dbf
354 /oradata/system11.dbf
379 /oraundo/proddata/undo01.dbf
392 /oradata01/proddata/xxcustom01.dbf
393 /oradata01/proddata/xxcustom02.dbf
394 /oradata/tools001.dbf
403 /oraundo/proddata/undo02.dbf
404 /oraundo/proddata/undo03.dbf
406 /oradata/discoverer01.dbf
408 /oradata01/proddata/xxcustom03.dbf
411 /oradata01/proddata/xxcustom04.dbf
412 /oradata01/proddata/xxcustom05.dbf
413 /oradata01/proddata/xxcustom06.dbf
414 /oradata01/proddata/xxcustom07.dbf
416 /oradata01/proddata/xxcustom08.dbf
417 /oradata01/proddata/xxcustom09.dbf
420 /oraundo/proddata/undo04.dbf
421 /oraundo/proddata/undo05.dbf
422 /oradata/tools002.dbf
425 /oradata/APPS_TS_TX_DATA01.dbf
426 /oraindex/APPS_TS_TX_IDX01.dbf
427 /oradata/APPS_TS_SEED01.dbf
428 /oradata/APPS_TS_INTERFACE01.dbf
429 /oradata/APPS_TS_SUMMARY01.dbf
430 /oradata/APPS_TS_NOLOGGING01.dbf
431 /oradata/APPS_TS_ARCHIVE01.dbf
432 /oradata/APPS_TS_QUEUES01.dbf
433 /oradata/APPS_TS_MEDIA01.dbf
434 /oradata/APPS_TS_TX_DATA02.dbf
435 /oradata/APPS_TS_TX_DATA03.dbf
436 /oradata/APPS_TS_TX_DATA04.dbf
437 /oradata/APPS_TS_TX_DATA05.dbf
438 /oraindex/APPS_TS_TX_IDX02.dbf
439 /oraindex/APPS_TS_TX_IDX03.dbf
440 /oraindex/APPS_TS_TX_IDX04.dbf
441 /oraindex/APPS_TS_TX_IDX05.dbf
442 /oraindex/APPS_TS_TX_IDX06.dbf
443 /oraindex/APPS_TS_TX_IDX07.dbf
444 /oradata/interim.dbf
And the logfile information.
GROUP# MEMBER
———- —————————————-
7 /oralogA_N1/proddata/hwh_redo07A.dbf
7 /oralogB_N1/proddata/hwh_redo07B.dbf
5 /oralogA_N1/proddata/hwh_redo05A.dbf
5 /oralogB_N1/proddata/hwh_redo05B.dbf
3 /oralogA_N1/proddata/hwh_redo03A.dbf
3 /oralogB_N1/proddata/hwh_redo03B.dbf
1 /oralogA_N1/proddata/hwh_redo01A.dbf
1 /oralogB_N1/proddata/hwh_redo01B.dbf
Set the commands to rename the new file. From the source database, execute the sql command;
select 'set newname for datafile '||file#||' to ''/opt/oracle/oradata/EBUSPROD'||substr(name, instr(name,'/',-1),length(name)-instr(name,'/',-1))||''';' newname
from v$datafile
/
This will output something like:
set newname for datafile 1 to '/opt/oracle/oradata/EBUSPROD/system01.db';
set newname for datafile 2 to '/opt/oracle/oradata/EBUSPROD/system02.db';
set newname for datafile 3 to '/opt/oracle/oradata/EBUSPROD/system03.db';
set newname for datafile 4 to '/opt/oracle/oradata/EBUSPROD/system04.db';
set newname for datafile 5 to '/opt/oracle/oradata/EBUSPROD/system05.db';
set newname for datafile 6 to '/opt/oracle/oradata/EBUSPROD/ctxd01.db';
set newname for datafile 7 to '/opt/oracle/oradata/EBUSPROD/configdb001.db';
set newname for datafile 8 to '/opt/oracle/oradata/EBUSPROD/xx_datamartdb001.db';
set newname for datafile 9 to '/opt/oracle/oradata/EBUSPROD/xx_isdb001.db';
set newname for datafile 10 to '/opt/oracle/oradata/EBUSPROD/logdb001.db';
set newname for datafile 11 to '/opt/oracle/oradata/EBUSPROD/xx_odsdb001.db';
set newname for datafile 12 to '/opt/oracle/oradata/EBUSPROD/xx_ucsarchivedb001.db';
set newname for datafile 13 to '/opt/oracle/oradata/EBUSPROD/xx_ucsmaindb001.db';
set newname for datafile 14 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_MEDIA02.db';
set newname for datafile 15 to '/opt/oracle/oradata/EBUSPROD/xx_odsdb002.db';
set newname for datafile 16 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_QUEUES02.db';
set newname for datafile 17 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA06.db';
set newname for datafile 18 to '/opt/oracle/oradata/EBUSPROD/customerdb001.db';
set newname for datafile 19 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX08.db';
set newname for datafile 20 to '/opt/oracle/oradata/EBUSPROD/xx_ucsmaindb002.db';
set newname for datafile 21 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX09.db';
set newname for datafile 22 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA07.db';
set newname for datafile 23 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX10.db';
set newname for datafile 24 to '/opt/oracle/oradata/EBUSPROD/system13.db';
set newname for datafile 25 to '/opt/oracle/oradata/EBUSPROD/system12.db';
set newname for datafile 26 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_QUEUES03.db';
set newname for datafile 27 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX11.db';
set newname for datafile 288 to '/opt/oracle/oradata/EBUSPROD/system10.db';
set newname for datafile 295 to '/opt/oracle/oradata/EBUSPROD/system06.db';
set newname for datafile 314 to '/opt/oracle/oradata/EBUSPROD/portal01.db';
set newname for datafile 351 to '/opt/oracle/oradata/EBUSPROD/system07.db';
set newname for datafile 352 to '/opt/oracle/oradata/EBUSPROD/system09.db';
set newname for datafile 353 to '/opt/oracle/oradata/EBUSPROD/system08.db';
set newname for datafile 354 to '/opt/oracle/oradata/EBUSPROD/system11.db';
set newname for datafile 379 to '/opt/oracle/oradata/EBUSPROD/undo01.db';
set newname for datafile 392 to '/opt/oracle/oradata/EBUSPROD/xxcustom01.db';
set newname for datafile 393 to '/opt/oracle/oradata/EBUSPROD/xxcustom02.db';
set newname for datafile 394 to '/opt/oracle/oradata/EBUSPROD/tools001.db';
set newname for datafile 403 to '/opt/oracle/oradata/EBUSPROD/undo02.db';
set newname for datafile 404 to '/opt/oracle/oradata/EBUSPROD/undo03.db';
set newname for datafile 406 to '/opt/oracle/oradata/EBUSPROD/discoverer01.db';
set newname for datafile 408 to '/opt/oracle/oradata/EBUSPROD/xxcustom03.db';
set newname for datafile 411 to '/opt/oracle/oradata/EBUSPROD/xxcustom04.db';
set newname for datafile 412 to '/opt/oracle/oradata/EBUSPROD/xxcustom05.db';
set newname for datafile 413 to '/opt/oracle/oradata/EBUSPROD/xxcustom06.db';
set newname for datafile 414 to '/opt/oracle/oradata/EBUSPROD/xxcustom07.db';
set newname for datafile 416 to '/opt/oracle/oradata/EBUSPROD/xxcustom08.db';
set newname for datafile 417 to '/opt/oracle/oradata/EBUSPROD/xxcustom09.db';
set newname for datafile 420 to '/opt/oracle/oradata/EBUSPROD/undo04.db';
set newname for datafile 421 to '/opt/oracle/oradata/EBUSPROD/undo05.db';
set newname for datafile 422 to '/opt/oracle/oradata/EBUSPROD/tools002.db';
set newname for datafile 425 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA01.db';
set newname for datafile 426 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX01.db';
set newname for datafile 427 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_SEED01.db';
set newname for datafile 428 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_INTERFACE01.db';
set newname for datafile 429 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_SUMMARY01.db';
set newname for datafile 430 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_NOLOGGING01.db';
set newname for datafile 431 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_ARCHIVE01.db';
set newname for datafile 432 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_QUEUES01.db';
set newname for datafile 433 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_MEDIA01.db';
set newname for datafile 434 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA02.db';
set newname for datafile 435 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA03.db';
set newname for datafile 436 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA04.db';
set newname for datafile 437 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA05.db';
set newname for datafile 438 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX02.db';
set newname for datafile 439 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX03.db';
set newname for datafile 440 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX04.db';
set newname for datafile 441 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX05.db';
set newname for datafile 442 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX06.db';
set newname for datafile 443 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX07.db';
set newname for datafile 444 to '/opt/oracle/oradata/EBUSPROD/interim.db';
Each datafile can then be recovered in a single run brace, or the whole database recovered.
run
{
set newname for datafile 1 to '/opt/oracle/oradata/EBUSPROD/system01.db';
restore datafile 1;
}
or
run
{
set newname for datafile 1 to '/opt/oracle/oradata/EBUSPROD/system01.db';
set newname for datafile 2 to '/opt/oracle/oradata/EBUSPROD/system02.db';
set newname for datafile 3 to '/opt/oracle/oradata/EBUSPROD/system03.db';
set newname for datafile 4 to '/opt/oracle/oradata/EBUSPROD/system04.db';
set newname for datafile 5 to '/opt/oracle/oradata/EBUSPROD/system05.db';
set newname for datafile 6 to '/opt/oracle/oradata/EBUSPROD/ctxd01.db';
set newname for datafile 7 to '/opt/oracle/oradata/EBUSPROD/configdb001.db';
set newname for datafile 8 to '/opt/oracle/oradata/EBUSPROD/xx_datamartdb001.db';
set newname for datafile 9 to '/opt/oracle/oradata/EBUSPROD/xx_isdb001.db';
set newname for datafile 10 to '/opt/oracle/oradata/EBUSPROD/logdb001.db';
set newname for datafile 11 to '/opt/oracle/oradata/EBUSPROD/xx_odsdb001.db';
set newname for datafile 12 to '/opt/oracle/oradata/EBUSPROD/xx_ucsarchivedb001.db';
set newname for datafile 13 to '/opt/oracle/oradata/EBUSPROD/xx_ucsmaindb001.db';
set newname for datafile 14 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_MEDIA02.db';
set newname for datafile 15 to '/opt/oracle/oradata/EBUSPROD/xx_odsdb002.db';
set newname for datafile 16 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_QUEUES02.db';
set newname for datafile 17 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA06.db';
set newname for datafile 18 to '/opt/oracle/oradata/EBUSPROD/customerdb001.db';
set newname for datafile 19 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX08.db';
set newname for datafile 20 to '/opt/oracle/oradata/EBUSPROD/xx_ucsmaindb002.db';
set newname for datafile 21 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX09.db';
set newname for datafile 22 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA07.db';
set newname for datafile 23 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX10.db';
set newname for datafile 24 to '/opt/oracle/oradata/EBUSPROD/system13.db';
set newname for datafile 25 to '/opt/oracle/oradata/EBUSPROD/system12.db';
set newname for datafile 26 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_QUEUES03.db';
set newname for datafile 27 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX11.db';
set newname for datafile 288 to '/opt/oracle/oradata/EBUSPROD/system10.db';
set newname for datafile 295 to '/opt/oracle/oradata/EBUSPROD/system06.db';
set newname for datafile 314 to '/opt/oracle/oradata/EBUSPROD/portal01.db';
set newname for datafile 351 to '/opt/oracle/oradata/EBUSPROD/system07.db';
set newname for datafile 352 to '/opt/oracle/oradata/EBUSPROD/system09.db';
set newname for datafile 353 to '/opt/oracle/oradata/EBUSPROD/system08.db';
set newname for datafile 354 to '/opt/oracle/oradata/EBUSPROD/system11.db';
set newname for datafile 379 to '/opt/oracle/oradata/EBUSPROD/undo01.db';
set newname for datafile 392 to '/opt/oracle/oradata/EBUSPROD/xxcustom01.db';
set newname for datafile 393 to '/opt/oracle/oradata/EBUSPROD/xxcustom02.db';
set newname for datafile 394 to '/opt/oracle/oradata/EBUSPROD/tools001.db';
set newname for datafile 403 to '/opt/oracle/oradata/EBUSPROD/undo02.db';
set newname for datafile 404 to '/opt/oracle/oradata/EBUSPROD/undo03.db';
set newname for datafile 406 to '/opt/oracle/oradata/EBUSPROD/discoverer01.db';
set newname for datafile 408 to '/opt/oracle/oradata/EBUSPROD/xxcustom03.db';
set newname for datafile 411 to '/opt/oracle/oradata/EBUSPROD/xxcustom04.db';
set newname for datafile 412 to '/opt/oracle/oradata/EBUSPROD/xxcustom05.db';
set newname for datafile 413 to '/opt/oracle/oradata/EBUSPROD/xxcustom06.db';
set newname for datafile 414 to '/opt/oracle/oradata/EBUSPROD/xxcustom07.db';
set newname for datafile 416 to '/opt/oracle/oradata/EBUSPROD/xxcustom08.db';
set newname for datafile 417 to '/opt/oracle/oradata/EBUSPROD/xxcustom09.db';
set newname for datafile 420 to '/opt/oracle/oradata/EBUSPROD/undo04.db';
set newname for datafile 421 to '/opt/oracle/oradata/EBUSPROD/undo05.db';
set newname for datafile 422 to '/opt/oracle/oradata/EBUSPROD/tools002.db';
set newname for datafile 425 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA01.db';
set newname for datafile 426 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX01.db';
set newname for datafile 427 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_SEED01.db';
set newname for datafile 428 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_INTERFACE01.db';
set newname for datafile 429 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_SUMMARY01.db';
set newname for datafile 430 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_NOLOGGING01.db';
set newname for datafile 431 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_ARCHIVE01.db';
set newname for datafile 432 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_QUEUES01.db';
set newname for datafile 433 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_MEDIA01.db';
set newname for datafile 434 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA02.db';
set newname for datafile 435 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA03.db';
set newname for datafile 436 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA04.db';
set newname for datafile 437 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_DATA05.db';
set newname for datafile 438 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX02.db';
set newname for datafile 439 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX03.db';
set newname for datafile 440 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX04.db';
set newname for datafile 441 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX05.db';
set newname for datafile 442 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX06.db';
set newname for datafile 443 to '/opt/oracle/oradata/EBUSPROD/APPS_TS_TX_IDX07.db';
set newname for datafile 444 to '/opt/oracle/oradata/EBUSPROD/interim.db';
restore database;
}
run
{
recover database;
}
Database recovery will require the archive logs, and the location defined in the pfile to restore them to.
RMAN> alter database open resetlogs;
Note: this will create the online redo logs in the same location as that on host A. If this directory location does not exist, then this will fail with: ora-344 : unable to recreate online log <name>
The workaround is to rename the logfiles prior to opening the database:
SQL> alter database rename file '<Source host location>' to '<Destination host location>';
Alternatively, the logfile groups can be dropped and recreated. However, attempts to drop the current logfile group will fail. The current logfile must be renamed.
