Restore datafile to a new location

If the disk storing datafile is broken, the data file may be restored to another disk and then restored.

Before restoration, check whether db_create_file_dest is set. If this parameter is set, RMAN restores datafile to the directory specified by db_create_file_dest by default, at this time, the disk of the directory specified by db_create_file_dest is damaged, which may cause the recovery to fail.

In this case, we can remove the db_create_file_dest parameter and use the following sample script to restore the data file to a new directory.

Run
{
Set newname for datafile 1 to 'C:/Oracle/product/10.2.0/oradata/Robinson/datafile2/o1_mf_system_5h1xwsc9_.dbf '; ---- if the db_create_file_dest parameter is not disabled, this statement is invalid
Set newname for datafile 2 to 'C:/Oracle/product/10.2.0/oradata/Robinson/datafile2/o1_mf_undotbs1_5h1xwsnj_.dbf ';
Set newname for datafile 3 to 'C:/Oracle/product/10.2.0/oradata/Robinson/datafile2/o1_mf_sysaux_5h1xwsf1_.dbf ';
Set newname for datafile 4 to 'C:/Oracle/product/10.2.0/oradata/Robinson/datafile2/o1_mf_users_5h1xwsod_.dbf ';
Set newname for datafile 5 to 'C:/Oracle/product/10.2.0/oradata/Robinson/datafile2/o1_mf_robinson_5h248ywc_.dbf ';
Restore database;
Switch datafile all;
Recover database;
Alter database open;
}

You can also change the db_create_file_dest parameter, specify it to a new disk directory, and use the following script to restore the disk.

Run
{
Set newname for datafile 1 to new; --- by using the new keyword, RMAN will restore datafile 1 to the specified directory db_create_file_dest
Set newname for datafile 2 to new;
Set newname for datafile 3 to new;
Set newname for datafile 4 to new;
Set newname for datafile 5 to new;
Restore database;
Switch datafile all;
Recover database;
Alter database open;
}

The test procedure is as follows:

SQL> select name from V $ datafile; --- data files are stored in the C:/Oracle/product/10.2./storage/Robinson/datafile/directory.

Name
--------------------------------------------------------------------------------
C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_system_5h26ljmp_.dbf
C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_undotbs1_5h26ljqb_.dbf
C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_sysaux_5h26ljny_.dbf
C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_users_5h26ljv1_.dbf
C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_robinson_5h26ljpy_.dbf

SQL> show parameter db_create_file_dest

Name type value
-----------------------------------------------------------------------------
Db_create_file_dest string C:/Oracle/product/10.2.0/storage

RMAN> shutdown immediate;

Database closed
Database uninstalled
Oracle instance disabled

RMAN> startup Mount;

The database has been started.

RMAN> RUN
2> {
3> set newname for datafile 1 to 'C:/Oracle/product/10.2.0/oradata/Robinson/datafile/ow.mf_system_5h1xwsc9_.dbf ';
4> set newname for datafile 2 to 'C:/Oracle/product/10.2.0/oradata/Robinson/datafile/ow.mf_undotbs1_5h1xwsnj_.dbf ';
5> set newname for datafile 3 to 'C:/Oracle/product/10.2.0/oradata/Robinson/datafile/ow.mf_sysaux_5h1xwsf1_.dbf ';
6> set newname for datafile 4 to 'C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users_5h1xwsod_.dbf ';
7> set newname for datafile 5 to 'C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson_5h248ywc_.dbf ';
8> Restore database;
9> switch datafile all;
10> recover database;
11> alter database open;
12>}

Executing command: Set newname
Use the target database control file to replace the recovery directory

Executing command: Set newname

Executing command: Set newname

Executing command: Set newname

Executing command: Set newname

Start the restore from April 11 to April 9
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 155 devtype = Disk

Channel ora_disk_1: Recovering data file 00001
Input data file copy recid = 56 stamp = 702050992 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_system_5h267
Data File 00001 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_system_5h1xwsc9_.dbf

Channel ora_disk_1: Recovering data file 00002
Input data file copy recid = 57 stamp = 702050992 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_undotbs1_5h2
Data File 00002 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_undotbs1_5h1xwsnj_.dbf

Channel ora_disk_1: Recovering data file 00003
Input data file copy recid = 58 stamp = 702050993 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_sysaux_5h267
Data File 00003 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_sysaux_5h1xwsf1_.dbf

Channel ora_disk_1: Recovering data file 00004
Input data file copy recid = 59 stamp = 702050993 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users_5h2678
Data File 00004 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users_5h1xwsod_.dbf

Channel ora_disk_1: Recovering data file 00005
Input data file copy recid = 60 stamp = 702050993 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson_5h2
Data File 00005 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson_5h248ywc_.dbf

Failover to the previous backup

Channel ora_disk_1: Recovering data file 00001
Input data file copy recid = 21 stamp = 702049822 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_system_5h254
Data File 00001 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_system_5h1xwsc9_.dbf

Channel ora_disk_1: Recovering data file 00002
Input data file copy recid = 22 stamp = 702049822 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_undotbs1_5h2
Data File 00002 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_undotbs1_5h1xwsnj_.dbf

Channel ora_disk_1: Recovering data file 00003
Input data file copy recid = 23 stamp = 702049822 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_sysaux_5h254
Data File 00003 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_sysaux_5h1xwsf1_.dbf

Channel ora_disk_1: Recovering data file 00004
Input data file copy recid = 24 stamp = 702049823 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users_5h2542
Data File 00004 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users_5h1xwsod_.dbf

Channel ora_disk_1: Recovering data file 00005
Input data file copy recid = 25 stamp = 702049823 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson_5h2
Data File 00005 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson_5h248ywc_.dbf

Failover to the previous backup

Channel ora_disk_1: Recovering data file 00001
Input data file copy recid = 41 stamp = 702050592 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile2/o1_mf_system_5h1x
Data File 00001 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_system_5h1xwsc9_.dbf

Channel ora_disk_1: Recovering data file 00002
Input data file copy recid = 42 stamp = 702050592 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile2/o1_mf_undotbs1_5h
Data File 00002 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_undotbs1_5h1xwsnj_.dbf

Channel ora_disk_1: Recovering data file 00003
Input data file copy recid = 43 stamp = 702050592 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile2/o1_mf_sysaux_5h1x
Data File 00003 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_sysaux_5h1xwsf1_.dbf

Channel ora_disk_1: Recovering data file 00004
Input data file copy recid = 44 stamp = 702050593 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile2/o1_mf_users_5h1xw
Data File 00004 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users_5h1xwsod_.dbf

Channel ora_disk_1: Recovering data file 00005
Input data file copy recid = 45 stamp = 702050593 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile2/o1_mf_robinson_5h
Data File 00005 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson_5h248ywc_.dbf

Failover to the previous backup

Channel ora_disk_1: Recovering data file backup set
Channel ora_disk_1: Specifies the data file to be recovered from the backup set.
Restoring data file 00001 to C:/Oracle/product/10.2.0/oradata/Robinson/datafile/ow.mf_system_5h1xwsc9_.dbf
Restoring data file 00002 to C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_undotbs1_5h1xwsnj_.dbf
Restoring data file 00003 to C:/Oracle/product/10.2.0/oradata/Robinson/datafile/ow.mf_sysaux_5h1xwsf1_.dbf
Restoring data file 00004 to C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users_5h1xwsod_.dbf
Restoring data file 00005 to C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson_5h248ywc_.dbf
Channel ora_disk_1: Reading the backup section C:/Oracle/product/10.2.0/flash_recovery_area/Robinson/backupset/2009_11_04/ow.mf_nnndf
Channel ora_disk_1: recovered backup segment 1
Segment handle = C:/Oracle/product/10.2.0/flash_recovery_area/Robinson/backupset/2009_11_04/o1_mf_nnndf_robinson_5h24d5qy_.bkp
Channel ora_disk_1: Recovery completed, time: 00:01:35
The restore is completed from April 11 to April 09.

Data File 1 has been converted to a data file copy
Input data file copy recid = 66 stamp = 702052443 file name = C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_system_5h27z
Data File 2 has been converted to a data file copy
Input data file copy recid = 67 stamp = 702052443 file name = C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_undotbs1_5h2
Data File 3 has been converted to a data file copy
Input data file copy recid = 68 stamp = 702052443 file name = C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_sysaux_5h27z
Data File 4 has been converted to a data file copy
Input data file copy recid = 69 stamp = 702052443 file name = C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_users_5h27zw
Data File 5 has been converted to a data file copy
Input data file copy recid = 70 stamp = 702052443 file name = C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_1_son_5h2

Start recover from April 11 to April 09
Use channel ora_disk_1

Restoring media...
Media Recovery completed. Time: 00:00:05

The recover is completed from month to month 09.

Database opened

SQL> select name from V $ datafile ;--We can see that because db_create_file_dest is not changed, the datafile cannot be restored to the specified directory.

Name
--------------------------------------------------------------------------------
C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_system_5h27zw3t_.dbf
C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_undotbs1_5h27zw7j_.dbf
C:/Oracle/product/10.2.0/storage/Robinson/datafile/ow.mf_sysaux_5h27zw51_.dbf
C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_users_5h27zwcp_.dbf
C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_robinson_5h27zw72_.dbf

SQL> alter system set db_create_file_dest = 'C:/Oracle/product/10.2.0/oradata ';

The system has been changed.

RMAN> shutdown immediate;

Database closed
Database uninstalled
Oracle instance disabled

RMAN> startup Mount;

Connected to the target database (not started)
Oracle instance started
Database loaded

The system's global region consists of 612368384 bytes.

Fixed size 1292036 bytes
Variable Size 213911804 bytes
Database buffers: 390070272 bytes
Redo buffers: 7094272 bytes

RMAN> RUN
2> {
3> set newname for datafile 1 to new;
4> set newname for datafile 2 to new;
5> set newname for datafile 3 to new;
6> set newname for datafile 4 to new;
7> set newname for datafile 5 to new;
8> Restore database;
9> switch datafile all;
10> recover database;
11> alter database open;
12>}

Executing command: Set newname

Executing command: Set newname

Executing command: Set newname

Executing command: Set newname

Executing command: Set newname

Start the restore from April 11 to April 9
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 156 devtype = Disk

Channel ora_disk_1: Recovering data file 00001
Input data file copy recid = 71 stamp = 702052444 file name = C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_system_5h26ljmp_.dbf
Data File 00001 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_system _ % u_.dbf
Channel ora_disk_1: copies of data file 00001 copied
Output file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_system_5h28m6wt_.dbf recid = 76 stamp = 702053000
Channel ora_disk_1: Recovering data file 00002
Input data file copy recid = 72 stamp = 702052444 file name = C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_undotbs1_5h26ljqb_.dbf
Data File 00002 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_undotbs1 _ % u_.dbf
Channel ora_disk_1: copies of data file 00002 copied
Output file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_undotbs1_5h28nb1x_.dbf recid = 77 stamp = 702053006
Channel ora_disk_1: Recovering data file 00003
Input data file copy recid = 73 stamp = 702052444 file name = C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_sysaux_5h26ljny_.dbf
Data File 00003 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_sysaux _ % u_.dbf
Channel ora_disk_1: copies of data file 00003 copied
Output file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_sysaux_5h28nk85_.dbf recid = 78 stamp = 702053047
Channel ora_disk_1: Recovering data file 00004
Input data file copy recid = 74 stamp = 702052444 file name = C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_users_5h26ljv1_.dbf
Data File 00004 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users _ % u_.dbf
Channel ora_disk_1: copies of data file 00004 copied
Output file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users_5h28pkvw_.dbf recid = 79 stamp = 702053074
Channel ora_disk_1: Recovering data file 00005
Input data file copy recid = 75 stamp = 702052444 file name = C:/Oracle/product/10.2.0/storage/Robinson/datafile/o1_mf_robinson_5h26ljpy_.dbf
Data File 00005 recovery target: C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson _ % u_.dbf
Channel ora_disk_1: copies of data file 00005 copied
Output file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson_5h28pm03_.dbf recid = 80 stamp = 702053090
The restore is completed from April 11 to April 09.

Data File 1 has been converted to a data file copy
Input data file copy recid = 81 stamp = 702053100 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_system_5h28m6wt_.dbf
Data File 2 has been converted to a data file copy
Input data file copy recid = 82 stamp = 702053100 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_undotbs1_5h28nb1x_.dbf
Data File 3 has been converted to a data file copy
Input data file copy recid = 83 stamp = 702053100 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_sysaux_5h2885_.dbf
Data File 4 has been converted to a data file copy
Input data file copy recid = 84 stamp = 702053101 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users_5h28pkvw_.dbf
Data File 5 has been converted to a data file copy
Input data file copy recid = 85 stamp = 702053101 file name = C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson_5h28pm03_.dbf

Start recover from April 11 to April 09
Use channel ora_disk_1

Restoring media...
Media Recovery completed. Time: 00:00:04

The recover is completed from month to month 09.
Database opened

SQL> select name from V $ datafile;--- We can see that the data file has been resotore to another directory.

Name
--------------------------------------------------------------------------------
C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_system_5h28m6wt_.dbf
C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_undotbs1_5h28nb1x_.dbf
C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_sysaux_5h28nk85_.dbf
C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_users_5h28pkvw_.dbf
C:/Oracle/product/10.2.0/oradata/Robinson/datafile/o1_mf_robinson_5h28pm03_.dbf

 

Because the length is too long, the test process will not be pasted.