This discussion is archived
4 Replies Latest reply: Oct 8, 2012 4:37 AM by 690951 RSS

Cant rename dbfs...

690951 Newbie
Currently Being Moderated
Hi to everybody, this is my issue:

11.2.0.3 win 2003
have direct aim to restore database from inc0+inc1 backup + recover up to latest archives made after inc1.

Steps have to be done:


1. SQLPLUS> startup nomount from pfile='......';
2. RMAN> restore controlfile from ...
3. SQLPLUS > alter database mount;
4. RMAN> run
{
......
set newname for datafile 'E:\DATABASE\WORK\......DBF' to 'F:\oracle\oradata\workstdb\......DBF';
......
}
4.1 (already try with db_file_name_convert='E:\database\work','F:\oracle\oradata\workstdb') made no sense.




5. RMAN> restore database;

Starting restore at 0CT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=235 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=467 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to E:\DATABASE\WORK\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to E:\DATABASE\WORK\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to E:\DATABASE\WORK\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00006 to E:\DATABASE\WORK\IDX_LARGE7.DBF
channel ORA_DISK_1: restoring datafile 00009 to E:\DATABASE\WORK\IDX_LARGE2.DBF
channel ORA_DISK_1: restoring datafile 00010 to E:\DATABASE\WORK\IDX_LARGE3.DBF
channel ORA_DISK_1: restoring datafile 00013 to E:\DATABASE\WORK\IDX_MEDIUM.DBF
channel ORA_DISK_1: restoring datafile 00014 to E:\DATABASE\WORK\IDX_MEDIUM1.DBF
channel ORA_DISK_1: restoring datafile 00020 to E:\DATABASE\WORK\USER_MEDIUM2.DBF
channel ORA_DISK_1: restoring datafile 00022 to E:\DATABASE\WORK\USER_SMALL.DBF
channel ORA_DISK_1: restoring datafile 00023 to E:\DATABASE\WORK\USERS02.DBF
channel ORA_DISK_1: restoring datafile 00024 to E:\DATABASE\WORK\UNDOTBS02.DBF
channel ORA_DISK_1: reading from backup piece F:\BACKUP\ORA_DF795326431_S759_S1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00004 to E:\DATABASE\WORK\USERS01.DBF
channel ORA_DISK_2: restoring datafile 00005 to E:\DATABASE\WORK\IDX.DBF
channel ORA_DISK_2: restoring datafile 00007 to E:\DATABASE\WORK\IDX_LARGE.DBF
channel ORA_DISK_2: restoring datafile 00008 to E:\DATABASE\WORK\IDX_LARGE1.DBF
channel ORA_DISK_2: restoring datafile 00011 to E:\DATABASE\WORK\IDX_LARGE4.DBF
channel ORA_DISK_2: restoring datafile 00012 to E:\DATABASE\WORK\IDX_LARGE5.DBF
channel ORA_DISK_2: restoring datafile 00015 to E:\DATABASE\WORK\IDX_SMALL.DBF
channel ORA_DISK_2: restoring datafile 00016 to E:\DATABASE\WORK\REC_DATA.DBF
channel ORA_DISK_2: restoring datafile 00017 to E:\DATABASE\WORK\USER_LARGE.DBF
channel ORA_DISK_2: restoring datafile 00018 to E:\DATABASE\WORK\USER_LARGE2.DBF
channel ORA_DISK_2: restoring datafile 00019 to E:\DATABASE\WORK\USER_MEDIUM.DBF
channel ORA_DISK_2: restoring datafile 00021 to E:\DATABASE\WORK\USER_MEDIUM3.DBF
channel ORA_DISK_2: reading from backup piece F:\BACKUP\ORA_DF795326432_S760_S1
channel ORA_DISK_1: ORA-19870: error while restoring backup piece F:\BACKUP\ORA_
DF795326431_S759_S1
ORA-19504: failed to create file "E:\DATABASE\WORK\UNDOTBS01.DBF"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

channel ORA_DISK_2: ORA-19870: error while restoring backup piece F:\BACKUP\ORA_
DF795326432_S760_S1
ORA-19504: failed to create file "E:\DATABASE\WORK\IDX_LARGE1.DBF"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

failover to previous backup

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/08/2012 13:30:20
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore


#######################################################################

RMAN> list backupset of datafile 1,2,3,4;


BS Key Type LV Size
------- ---- -- ----------
759 Incr 0 17.44G
List of Datafiles in backup set 759
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\SYSTEM01.DBF
2 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\SYSAUX01.DBF
3 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\UNDOTBS01.DBF

Backup Set Copy #1 of backup set 759
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:58:55 30-SEP-12 YES BACKUP_WORK_000001_093012040007

List of Backup Pieces for backup set 759 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
759 1 AVAILABLE F:\BACKUP\ORA_DF795326431_S759_S1

Backup Set Copy #2 of backup set 759
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:58:55 04-OCT-12 YES BACKUP_WORK_000001_093012040007

BS Key Type LV Size
------- ---- -- ----------
760 Incr 0 27.13G
List of Datafiles in backup set 760
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\USERS01.DBF
........................
another inc1 backups
........................

List of Backup Pieces for backup set 785 Copy #5
BP Key Pc# Status Piece Name
------- --- ----------- ----------
822 1 AVAILABLE F:\BACKUP\ORA_DF795758428_S784_S1

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
791 Incr 1 8.25G DISK 00:27:21 05-OCT-12
BP Key: 827 Status: AVAILABLE Compressed: YES Tag: BACKUP_WORK_000002_100512040009
Piece Name: F:\BACKUP\ORA_DF795844832_S791_S1
List of Datafiles in backup set 791
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\SYSAUX01.DBF
4 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\USERS01.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
792 Incr 1 19.79G DISK 00:55:27 05-OCT-12
BP Key: 828 Status: AVAILABLE Compressed: YES Tag: BACKUP_WORK_000002_100512040009
Piece Name: F:\BACKUP\ORA_DF795844833_S792_S1
List of Datafiles in backup set 792
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\SYSTEM01.DBF
3 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\UNDOTBS01.DBF

#################################################################



RMAN> restore database preview;

Starting restore at 0CT-12
using channel ORA_DISK_1
using channel ORA_DISK_2


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
759 Incr 0 17.44G DISK 00:58:55 30-SEP-12
BP Key: 759 Status: AVAILABLE Compressed: YES Tag: BACKUP_WORK_00000
1_093012040007
Piece Name: F:\BACKUP\ORA_DF795326431_S759_S1
List of Datafiles in backup set 759
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\SYSTEM01.DBF
2 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\SYSAUX01.DBF
3 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\UNDOTBS01.DBF
6 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\IDX_LARGE7.DBF
9 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\IDX_LARGE2.DBF
10 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\IDX_LARGE3.DBF
13 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\IDX_MEDIUM.DBF
14 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\IDX_MEDIUM1.DBF
20 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\USER_MEDIUM2.DBF
22 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\USER_SMALL.DBF
23 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\USERS02.DBF
24 0 Incr 17587902988 30-SEP-12 E:\DATABASE\WORK\UNDOTBS02.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
792 Incr 1 19.79G DISK 00:55:27 05-OCT-12
BP Key: 828 Status: AVAILABLE Compressed: YES Tag: BACKUP_WORK_00000
2_100512040009
Piece Name: F:\BACKUP\ORA_DF795844833_S792_S1
List of Datafiles in backup set 792
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\SYSTEM01.DBF
3 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\UNDOTBS01.DBF
6 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\IDX_LARGE7.DBF
9 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\IDX_LARGE2.DBF
10 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\IDX_LARGE3.DBF
13 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\IDX_MEDIUM.DBF
14 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\IDX_MEDIUM1.DBF
15 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\IDX_SMALL.DBF
16 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\REC_DATA.DBF
20 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\USER_MEDIUM2.DBF
23 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\USERS02.DBF
24 1 Incr 17793275461 05-OCT-12 E:\DATABASE\WORK\UNDOTBS02.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
791 Incr 1 8.25G DISK 00:27:21 05-OCT-12
BP Key: 827 Status: AVAILABLE Compressed: YES Tag: BACKUP_WORK_00000
2_100512040009
Piece Name: F:\BACKUP\ORA_DF795844832_S791_S1
List of Datafiles in backup set 791
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\SYSAUX01.DBF
4 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\USERS01.DBF
5 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\IDX.DBF
7 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\IDX_LARGE.DBF
8 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\IDX_LARGE1.DBF
11 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\IDX_LARGE4.DBF
12 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\IDX_LARGE5.DBF
17 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\USER_LARGE.DBF
18 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\USER_LARGE2.DBF
19 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\USER_MEDIUM.DBF
21 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\USER_MEDIUM3.DBF
22 1 Incr 17793275448 05-OCT-12 E:\DATABASE\WORK\USER_SMALL.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
760 Incr 0 27.13G DISK 01:25:42 30-SEP-12
BP Key: 760 Status: AVAILABLE Compressed: YES Tag: BACKUP_WORK_00000
1_093012040007
Piece Name: F:\BACKUP\ORA_DF795326432_S760_S1
List of Datafiles in backup set 760
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\USERS01.DBF
5 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\IDX.DBF
7 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\IDX_LARGE.DBF
8 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\IDX_LARGE1.DBF
11 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\IDX_LARGE4.DBF
12 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\IDX_LARGE5.DBF
15 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\IDX_SMALL.DBF
16 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\REC_DATA.DBF
17 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\USER_LARGE.DBF
18 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\USER_LARGE2.DBF
19 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\USER_MEDIUM.DBF
21 0 Incr 17587903446 30-SEP-12 E:\DATABASE\WORK\USER_MEDIUM3.DBF


List of Backup Sets
===================


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
795 108.13M DISK 00:00:16 05-OCT-12
BP Key: 831 Status: AVAILABLE Compressed: YES Tag: BACKUP_WORK_00000
2_100512040009
Piece Name: F:\BACKUP\ORA_DF795849092_S796_S1

List of Archived Logs in backup set 795
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 8406 17793029616 05-OCT-12 17793326018 05-OCT-12
Media recovery start SCN is 17793275448
Recovery must be done beyond SCN 17793323014 to clear datafile fuzziness
Finished restore at 0CT-12
#####################






Questions:

1. Why it ignoring "set new name" and db_file_name_convert??
2. Why no backup or copy of datafile 1,2,3,4 found to restore, it can seee it clearly through all its commands?

Edited by: ESirenko on 08.10.2012 4:22
  • 1. Re: Cant rename dbfs...
    KuljeetPalSingh Guru
    Currently Being Moderated
    5. RMAN> restore database;
    put "set newname for datafile " in one block with restore cmd.
    like
    run
    {
    set newname for datafile .....
    restore database;
    switch datafile all;
    recover database;
    }
  • 2. Re: Cant rename dbfs...
    Shivananda Rao Guru
    Currently Being Moderated
    set newname for datafile 'E:\DATABASE\WORK\......DBF' to 'F:\oracle\oradata\workstdb\......DBF';
    Refer this http://shivanandarao.wordpress.com/2012/04/19/duplicating-primary-database-to-a-new-host-without-connecting-to-the-primary-database-in-oracle-10g11g/ for the steps
    RMAN> run{
    2> set newname for datafile 1 to ‘<desired location>system01.dbf’;
    3> set newname for datafile 2 to ‘<desired location>sysaux.dbf’;
    4> set newname for datafile 3 to ‘<desired location>undotbs1.dbf’;
    5> set newname for datafile 4 to ‘<desired location>users.dbf’;
    .
    .
    .
    6> RESTORE DATABASE;
    7> SWITCH DATAFILE ALL;
    8> RECOVER DATABASE;
    9> }
  • 3. Re: Cant rename dbfs...
    690951 Newbie
    Currently Being Moderated
    Guys, will be in my lands, dinner's on me!!
  • 4. Re: Cant rename dbfs...
    690951 Newbie
    Currently Being Moderated
    guys will be in my lands, dinner's on me!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points