Thursday, November 18, 2010

RMAN restore fails with RMAN-06023 but there are backups available

I must admit - today I learned new thing about RMAN restore and this is good enough reason for a blog post.

Part of my daily tasks is to perform RMAN backup and recovery scenarios in my test environment (as I said beore), to be familiar with every possible situation that can happen.

So today, very simple task of restoring database to another machine took me over 2 hours...


Demo case:
Environment : Oracle 10.2.0.4 on Linux 64bit


I have backup files of the database:
$ ls -l /oracle/backup/*
ARCH_OTP_20101117_0tlt6lum_s8221_p1
controlf_OTP_0ult6mop_8222_20101117
FULL_OTP_0rlt6ip4
spfile_OTP_0vlt6mos_8223_20101117

Restore controlfile from backup and mount database:
$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Nov 18 08:32:09 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database (not started)

RMAN> startup nomount;
Oracle instance started
Total System Global Area     838860800 bytes
Fixed Size                     2087672 bytes
Variable Size                750781704 bytes
Database Buffers              67108864 bytes
Redo Buffers                  18882560 bytes

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/oracle/backup/controlf_OTP_0ult6mop_8222_20101117';
5> }

Starting restore at 18.11.2010 08:40:51

channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:00:01
output filename=/oracle/product/10.2.0/db_1/dbs/control01.ctl
output filename=/oracle/product/10.2.0/db_1/dbs/control02.ctl
Finished restore at 18.11.2010 08:40:52
released channel: c1

RMAN> alter database mount;
database mounted

I will delete all EXPIRED backups from RMAN repository:

RMAN> crosscheck backup;
RMAN> delete noprompt expired backup;

RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;

For this case it is very important to mention that I’ve had enabled controlfile autobackup (CONFIGURE CONTROLFILE AUTOBACKUP ON) in older incarnations. With this parameter enabled RMAN automatically takes backup of controlfile and server parameter file whenever the database structure metadata in the control file changes or whenever a backup or copy operation is performed using RMAN.

List backup command shows that there are two old autobackup files in flash recovery area.
RMAN> list backup;


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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8199    Full    7.89M      DISK        00:00:00     16.06.2010 14:50:28
        BP Key: 8199   Status: AVAILABLE  Compressed: NO  Tag: TAG20100616T145028
        Piece Name: /oracle/oradata/archive/flash_recovery_area/OTP/autobackup/2010_06_16/o1_mf_s_721839028_61kl4o2y_.bkp
  Control File Included: Ckp SCN: 43471045788   Ckp time: 16.06.2010 14:50:28
  SPFILE Included: Modification time: 16.06.2010 13:15:03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8200    Full    7.89M      DISK        00:00:00     16.06.2010 14:49:49
        BP Key: 8200   Status: AVAILABLE  Compressed: NO  Tag: TAG20100616T144949
        Piece Name: /oracle/oradata/archive/flash_recovery_area/OTP/autobackup/2010_06_16/o1_mf_s_721838989_61kl3hhq_.bkp
  Control File Included: Ckp SCN: 43471045571   Ckp time: 16.06.2010 14:49:49
  SPFILE Included: Modification time: 16.06.2010 13:15:03


To continue with my restore process I will catalog my backup files noted before.
RMAN> catalog start with '/oracle/backup/';

searching for all files that match the pattern /oracle/backup/

List of Files Unknown to the Database
=====================================
File Name: /oracle/backup/FULL_OTP_0rlt6ip4
File Name: /oracle/backup/ARCH_OTP_20101117_0tlt6lum_s8221_p1
File Name: /oracle/backup/controlf_OTP_0ult6mop_8222_20101117

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/backup/FULL_OTP_0rlt6ip4
File Name: /oracle/backup/ARCH_OTP_20101117_0tlt6lum_s8221_p1
File Name: /oracle/backup/controlf_OTP_0ult6mop_8222_20101117

Check backup of database.
RMAN> list backup of database;


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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8196    Incr 0  11.18G     DISK        00:53:38     17.11.2010 01:53:58
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: WEEKLY_FULL
        Piece Name: /oracle/backup/FULL_OTP_0rlt6ip4
  List of Datafiles in backup set 8196
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/system/otp/system01.dbf
  2    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/data02/otp/users02.dbf
  3    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/system/otp/sysaux01.dbf
  4    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/data02/otp/users01.dbf
  5    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/index/otp/tools01.dbf
  6    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/data02/otp/users03.dbf
  7    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/system/otp/undotbs01.dbf
  8    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/system/otp/cluser_tbs01.dbf

Restore database files from backup to another location.
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO '/oracle/u02/oradata/system/otp/system01.dbf';
5> SET NEWNAME FOR DATAFILE 2 TO '/oracle/u02/oradata/data02/otp/users02.dbf';
6> SET NEWNAME FOR DATAFILE 3 TO '/oracle/u02/oradata/system/otp/sysaux01.dbf';
7> SET NEWNAME FOR DATAFILE 4 TO '/oracle/u02/oradata/data02/otp/users01.dbf';
8> SET NEWNAME FOR DATAFILE 5 TO '/oracle/u02/oradata/index/otp/tools01.dbf';
9> SET NEWNAME FOR DATAFILE 6 TO '/oracle/u02/oradata/data02/otp/users03.dbf';
10> SET NEWNAME FOR DATAFILE 7 TO '/oracle/u02/oradata/system/otp/undotbs01.dbf';
11> SET NEWNAME FOR DATAFILE 8 TO '/oracle/u02/oradata/system/otp/cluser_tbs01.dbf';
12>
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=321 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18.11.2010 08:46:21

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/18/2010 08:46:22
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 1 found to restore

Hm...
For me this was unexpected error because I had database backup available.

RMAN> list backup of datafile 4;


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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8196    Incr 0  11.18G     DISK        00:53:38     17.11.2010 01:53:58
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: WEEKLY_FULL
        Piece Name: /oracle/backup/FULL_OTP_0rlt6ip4
  List of Datafiles in backup set 8196
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  4    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/data02/otp/users01.dbf

RMAN> list backup of datafile 3;


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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8196    Incr 0  11.18G     DISK        00:53:38     17.11.2010 01:53:58
        BP Key: 8201   Status: AVAILABLE  Compressed: YES  Tag: WEEKLY_FULL
        Piece Name: /oracle/backup/FULL_OTP_0rlt6ip4
  List of Datafiles in backup set 8196
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  3    0  Incr 48852276752 17.11.2010 01:00:20 /u02/oradata/system/otp/sysaux01.dbf

…
…
…

After two hours of searching for answers and performing more tests I ran to the Metalink document 965122.1.

The problem were those autobackups in FRA that I showed before. That files belonged to different incarnation than the available backups current incarnation.

[From Metalink document]
If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined, RMAN execute and implicit crosscheck and catalog of all the objects in the Flash Recovery Area.

RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURRENT incarnation in the controlfile then changes controlfile CURRENT incarnation to the one found in the file that is being cataloged.

This prevents database from restoring backups that belong to old CURRENT incarnation.
RMAN considers backup availble for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.



So I’ve decided to temporary disable FRA during recovery process commenting db_recovery_file_dest and db_recovery_file_dest_size parameters in parameter file.

RMAN> shutdown abort;

Oracle instance shut down

$ vi $ORACLE_HOME/dbs/initotp.ora
...
#*.db_recovery_file_dest='/oracle/oradata/archive/flash_recovery_area'
#*.db_recovery_file_dest_size=2147483648
…
...

$ sqlplus "/as sysdba"

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initotp.ora';
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             750781704 bytes
Database Buffers           67108864 bytes
Redo Buffers               18882560 bytes

Restore and recover database.
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/oracle/backup/controlf_OTP_0ult6mop_8222_20101117';
5> }
…
…
…
RMAN> alter database mount;
database mounted

RMAN> catalog start with '/oracle/backup';
…
…

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO '/oracle/u02/oradata/system/otp/system01.dbf';
5> SET NEWNAME FOR DATAFILE 2 TO '/oracle/u02/oradata/data02/otp/users02.dbf';
6> SET NEWNAME FOR DATAFILE 3 TO '/oracle/u02/oradata/system/otp/sysaux01.dbf';
7> SET NEWNAME FOR DATAFILE 4 TO '/oracle/u02/oradata/data02/otp/users01.dbf';
8> SET NEWNAME FOR DATAFILE 5 TO '/oracle/u02/oradata/index/otp/tools01.dbf';
9> SET NEWNAME FOR DATAFILE 6 TO '/oracle/u02/oradata/data02/otp/users03.dbf';
10> SET NEWNAME FOR DATAFILE 7 TO '/oracle/u02/oradata/system/otp/undotbs01.dbf';
11> SET NEWNAME FOR DATAFILE 8 TO '/oracle/u02/oradata/system/otp/cluser_tbs01.dbf';
12>
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=321 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18.11.2010 09:01:48

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/u02/oradata/system/otp/system01.dbf
restoring datafile 00002 to /oracle/u02/oradata/data02/otp/users02.dbf
restoring datafile 00003 to /oracle/u02/oradata/system/otp/sysaux01.dbf
restoring datafile 00004 to /oracle/u02/oradata/data02/otp/users01.dbf
restoring datafile 00005 to /oracle/u02/oradata/index/otp/tools01.dbf
restoring datafile 00006 to /oracle/u02/oradata/data02/otp/users03.dbf
restoring datafile 00007 to /oracle/u02/oradata/system/otp/undotbs01.dbf
restoring datafile 00008 to /oracle/u02/oradata/system/otp/cluser_tbs01.dbf
channel c1: reading from backup piece /var/umoracle/otp/fullbkp_dir/FULL_OTP_0rlt6ip4
channel c1: restored backup piece 1
failover to piece handle=/mnt/l01dbdev-s01storage1/oracle/backup/FULL_OTP_0rlt6ip4 tag=WEEKLY_FULL
channel c1: restore complete, elapsed time: 01:28:51
Finished restore at 18.11.2010 10:30:40

datafile 1 switched to datafile copy
input datafile copy recid=9 stamp=735388241 filename=/oracle/u02/oradata/system/otp/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=10 stamp=735388241 filename=/oracle/u02/oradata/data02/otp/users02.dbf
datafile 3 switched to datafile copy
input datafile copy recid=11 stamp=735388241 filename=/oracle/u02/oradata/system/otp/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=12 stamp=735388241 filename=/oracle/u02/oradata/data02/otp/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=13 stamp=735388241 filename=/oracle/u02/oradata/index/otp/tools01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=14 stamp=735388242 filename=/oracle/u02/oradata/data02/otp/users03.dbf
datafile 7 switched to datafile copy
input datafile copy recid=15 stamp=735388242 filename=/oracle/u02/oradata/system/otp/undotbs01.dbf
datafile 8 switched to datafile copy
input datafile copy recid=16 stamp=735388242 filename=/oracle/u02/oradata/system/otp/cluser_tbs01.dbf

Starting recover at 18.11.2010 10:30:43

starting media recovery

channel c1: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=33610
channel c1: reading from backup piece /var/otp/fullbkp_dir/ARCH_OTP_20101117_0tlt6lum_s8221_p1
channel c1: restored backup piece 1
failover to piece handle=/oracle/backup/ARCH_OTP_20101117_0tlt6lum_s8221_p1 tag=ARCHIVELOGS
channel c1: restore complete, elapsed time: 00:11:44
archive log filename=/oracle/oradata/archive/arch_1_33610_586538926.arc thread=1 sequence=33610
unable to find archive log
archive log thread=1 sequence=33611
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/18/2010 10:50:16
RMAN-06054: media recovery requesting unknown log: thread 1 seq 33611 lowscn 48852472202
RMAN> exit


$ sqlplus "/as sysdba"

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 48852472202 generated at 11/17/2010 01:54:16 needed for
thread 1
ORA-00289: suggestion :
/oracle/oradata/archive/arch_1_33611_586538926.arc
ORA-00280: change 48852472202 for thread 1 is in sequence #33611

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.

I'm glad that this problem occurred now in test environment because those two hours spent on searching for answers could be critical in recovery of a production database.

You never know :)


REFERENCES
Metalink - [ID 965122.1]

Written November 18, 2010 by Marko Sutic

   47 comments

47 comments:

  1. AnonymousNovember 18, 2010 at 10:55 PM

    If you're restoring to a new box, shouldn't the flash_recovery_area be empty?

    ReplyDelete
  2. Marko SuticNovember 19, 2010 at 8:41 AM

    This was my testing machine so everything is possible there. I've tested various scenarios on that machine so it's always possible to ran on leftovers from previous incarnations.

    Regards,
    Marko

    ReplyDelete
  3. AnonymousFebruary 21, 2011 at 1:50 PM

    Thanks dude ! you saved my life ;=)

    In fact, in my case, no need to disable FRA, just physically delete the old backup in FRA, run a crossheck and delete expired backup.

    It works fine !

    ReplyDelete
  4. Marko SuticFebruary 21, 2011 at 6:36 PM

    Heh I'm glad that blog post saved your time :)

    Temporary disabling FRA first came to my mind and it worked - your solution is better...thx ;)

    Enjoy!

    ReplyDelete
  5. PHuggerApril 7, 2011 at 9:30 PM

    Thanks Marko!
    This was a big help to me.
    Thanks for posting.

    Best regards,
    PCH

    ReplyDelete
  6. Marko SuticApril 8, 2011 at 1:27 PM

    You're welcome! ;)

    ReplyDelete
  7. AnonymousFebruary 3, 2012 at 4:55 PM

    This is a perfect solution for a Disaster Recovery type database restore. All steps are listed and explained A to Z.
    Thanks!!!!

    ReplyDelete
  8. AnonymousApril 23, 2012 at 6:01 PM

    Hi,
    I followed your instructions and was able to use RMAN to recover just fine without using "SET UNTIL TIME". However, if I used "SET UNTIL TIME "TO_DATE('18-APR-2012 10:00:00 AM', 'DD-MON-YYYY HH:MI:SS:AM')";", I got the data file not found error below. Do you experience the same problem?

    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

    ReplyDelete
  9. Marko SuticApril 23, 2012 at 10:14 PM

    Do you have backups older than the time you specified?

    I think that your backups are too new. Either you don't have older backups or you must catalog backupsets.

    Otherwise you have the problem that is described in this blog post.

    Regards,
    Marko

    ReplyDelete
  10. AnonymousApril 24, 2012 at 9:35 PM

    You're right. My backups are too new. I need to figure out what other older backup files are needed to recover using the specified timestamp. Thank you so much for your help.

    ReplyDelete
  11. Marko SuticApril 24, 2012 at 11:05 PM

    No problem ;-)

    Regards,
    Marko

    ReplyDelete
  12. AnonymousAugust 15, 2012 at 4:39 PM

    I just ran into this problem. After hours of looking for a solution I saw this blog. Thanks.
    Regards
    Fredo

    ReplyDelete
  13. Marko SuticAugust 17, 2012 at 11:02 AM

    Thanks for comment Fredo ;-)

    Regards,
    Marko

    ReplyDelete
  14. AnonymousAugust 22, 2012 at 8:03 PM

    Another thank you - saved me significant time!

    ..john

    ReplyDelete
  15. i am jorisAugust 24, 2012 at 4:32 AM

    Thanks Marko... 2-3 times i was perplexed with this problem until i find this blog. You made me feel free a lot. Also the presentation with complete commands was undoubtfully undoubtful to the topic!!

    ReplyDelete
  16. Marko SuticAugust 28, 2012 at 9:29 AM

    John, Joris - thanks for comments. I'm glad that this post saved your time.

    Regards,
    Marko

    ReplyDelete
  17. Joe KSeptember 20, 2012 at 3:43 PM

    Thank you...this problem was driving me crazy!!

    ReplyDelete
  18. AnonymousDecember 9, 2012 at 7:20 AM

    Excellent post ! I was baffling over this problem since yesterday. But what if I want to have point in time recovery? It's going through only if we remove the set SCN command during restore.

    ReplyDelete
  19. Marko SuticDecember 17, 2012 at 3:02 PM

    I don't understand question very well. If you want point in time recovery you should be able to do it with specifying SCN (until SCN).

    Of course you must have sufficiently old backups of datafiles and needed archive logs for recovery.

    What error you get?

    Regards,
    Marko

    ReplyDelete
  20. AnonymousJanuary 1, 2013 at 9:55 AM

    This is really Helpful. Many Thanks Marco!!!!!


    Bezabih

    ReplyDelete
  21. AnonymousJanuary 3, 2013 at 2:40 PM

    Thanks! Its was really helpful.
    After 2 hours of search, I finally found your blog and has the answer.
    I just deleted all the files from FRA (Its a test Database) then the restore starts working.

    ReplyDelete
  22. AnonymousJanuary 15, 2013 at 8:20 PM

    Excellent! Thank you very much.

    ReplyDelete
  23. AnonymousJanuary 19, 2013 at 12:43 PM

    Dear Marko, thank you for sharing this! You saved me couple of hours,now I can go out and make a snowman with my kids. THANKS!
    Natasa

    ReplyDelete
  24. Ramniwas ChaurasiaApril 6, 2013 at 3:08 PM

    Thanks Marko. I faced the same situation today and got your post while searching for the solution. It was very helpful!

    ReplyDelete
  25. AnonymousSeptember 4, 2013 at 7:55 PM

    GImme a break, RTFM

    More simple solution without touching FRA:

    reset incarnation

    http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta036.htm

    CONNECT TARGET / NOCATALOG


    # step 1: start and mount a control file that knows about the incarnation to which
    # you want to return. Refer to the RESTORE command for appropriate options.
    STARTUP NOMOUNT;
    RESTORE CONTROLFILE FROM AUTOBACKUP;
    ALTER DATABASE MOUNT;



    # step 2: obtain the primary key of old incarnation
    LIST INCARNATION OF DATABASE trgt;


    List of Database Incarnations
    DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
    ------- ------- -------- ------------- ------- ---------- ----------
    1 2 TRGT 1334358386 PARENT 154381 OCT 30 2007 16:02:12
    1 116 TRGT 1334358386 CURRENT 154877 OCT 30 2007 16:37:39



    # step 3: in this example, reset database to incarnation key 2
    RESET DATABASE TO INCARNATION 2;

    or RTFM.

    ReplyDelete
  26. Marko SuticSeptember 5, 2013 at 2:56 PM

    Hello Anonymous,

    I wrote in the post - “After two hours of searching for answers and performing more tests I ran to the Metalink document 965122.1.”

    So, I've spent about two hours to find solution for my problem. I've checked documentation and searched through Metalink notes and found quick WORKING solution.

    If I knew that I could solve this problem with resetting incarnation I would do that - but then I didn't know.

    I also noted at the beginning of this post - "today I learned new thing about RMAN restore". This new thing I learned was - implicit crosscheck.

    My blog is probably full of mistakes and I'm glad when someone corrects me. This improves my knowledge and I’m grateful for any feedback.

    So thanks for your feedback.


    Please, share a piece of your vast knowledge with others and write blog post or two.
    I am sure that many of us can learn from you many useful things.

    And stand behind your words with your real name while you’re commenting anything.

    Regards,
    Marko Sutic

    ReplyDelete
  27. UnknownOctober 12, 2013 at 3:30 AM

    I was getting the same error while performing restore and recover in test environment . I didn't had FRA setup in dev environment .
    Here what I have performed
    1) initiated a hot backup from production system
    2)scp the backup set along with archive logs backup sets to DEV environment
    in DEV
    1) startup nomount pfile=
    2) set the env ORACLE_HOME and ORACLE_SID
    3) rman target /
    4) restore controlfile from ''
    5) alter database mount
    6) catalog the backup set
    7) list backup of archivelog all;
    from the backup set info took the SCN no ,
    8) prepared the rcv file , tail of the file is something like this
    SET UNTIL SEQUENCE 84;

    # restore the database and switch the datafile names
    RESTORE DATABASE;
    SWITCH DATAFILE ALL;

    # recover the database
    RECOVER DATABASE;
    }
    however when I run the .rcv file I get the same error saying file
    RMAN-06023: no backup or copy of datafile 1 found to restore

    list backup of datafile 1 shows the datafile info in backup set .

    ReplyDelete
  28. Marko SuticOctober 12, 2013 at 10:40 AM

    Hello Amarnath,

    maybe the problem is in "SET UNTIL SEQUENCE 84".
    Try using "SET UNTIL SCN xxxx" with correct SCN which is not too new.

    All the steps look fine to me and this would be the line which I would check twice.

    btw
    Version of your Oracle database is?

    Regards,
    Marko

    ReplyDelete
  29. UnknownFebruary 4, 2014 at 9:01 AM

    Dude, thank you soo much yar. i literally wasted 2 days of my time looking for a solution for this problem. Today i visited your blog and solved the problem , i commented those 2 parameters , then it went through.. thanks soooo much.

    ReplyDelete
  30. UnknownApril 14, 2014 at 7:38 AM

    Go through the below link.
    http://goldparrot.wordpress.com/2011/05/16/how-to-find-exact-scn-number-for-oracle-restore/

    Get the fuzzy SC using the rman command:
    select max(absolute_fuzzy_change#)+1, max(checkpoint_change#)+1 from v$backup_datafile;

    It gives two SCNs, pick the greatest of the 2.

    Restore the database. :)

    ReplyDelete
  31. NelsonAugust 27, 2014 at 8:01 PM

    after commenting db_recovery_file_dest and db_recovery_file_dest_size parameters in parameter file, the rman console allow me restore database however when i'm executing this sentences "recover database;" on RMAN console, I have this error.

    RMAN> recover database;

    Starting recover at 2014/08/28 02:47:40
    using channel ORA_DISK_1

    starting media recovery
    media recovery failed
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 08/28/2014 02:47:40
    ORA-00283: recovery session canceled due to errors
    RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
    start until cancel using backup controlfile
    ORA-00283: recovery session canceled due to errors
    ORA-19909: datafile 1 belongs to an orphan incarnation
    ORA-01110: data file 1: '/u02/app/oracle/oradata/OASIST/datafile/o1_mf_system_9zw393r8_.dbf'

    ReplyDelete
  32. Marko SuticAugust 28, 2014 at 12:34 PM

    Hello Nelson,

    you have to set correct incarnation. You're probably using backup of earlier incarnation.
    Check this blog post:
    http://pro-oracle.blogspot.com/2014/03/oracle-rman-ora-19909-datafile-1.html

    Regards,
    Marko

    ReplyDelete
  33. AnonymousJanuary 5, 2015 at 2:35 PM

    Great One ! Thx a lot !

    ReplyDelete
  34. StrangeMartianMarch 15, 2015 at 12:03 PM

    Ran into this on a live customer migration to new data centre. Found this blog and fixed issue much to my relief.

    ReplyDelete
  35. Marko SuticMarch 15, 2015 at 12:21 PM

    Thanks for the comment. I'm glad that blog post helped you ;-)

    Regards,
    Marko

    ReplyDelete
  36. UnknownApril 2, 2015 at 2:09 PM

    Many Thanks for the proper solution.

    Regards,
    Swaminath

    ReplyDelete
  37. UnknownApril 7, 2015 at 10:55 PM

    Could not have done it without your post!! Cheers!! by the way... do you know how to correct it so it does not happen again?

    ReplyDelete
  38. Marko SuticApril 8, 2015 at 9:11 AM

    Thanks Uri for comment.
    If you have backups from more incarnations in your FRA then implicit crosscheck can always change current incarnation. You could set correct incarnation, temporary disable FRA or just delete unnecessary backup files from FRA.

    Regards,
    Marko

    ReplyDelete
  39. AnonymousJune 22, 2015 at 10:15 PM

    Thanks. Very helpful post!

    Mark

    ReplyDelete
  40. GrootJanuary 31, 2016 at 12:12 AM

    Thanks marko.. this helped me a lot :)

    ReplyDelete
  41. Suz OlliverNovember 10, 2016 at 2:33 AM

    Mate, you are a bloody hero. I don't know how many people this blog has saved the bacon for, but you have managed to lower my blood pressure by a good 20 points.

    ReplyDelete
  42. AnonymousJanuary 10, 2017 at 1:47 PM

    Hi Marko, I ready your blogs. It really helped me and resolved my issue.

    Thanks a lot buddy.
    PC

    ReplyDelete
  43. UnknownMay 22, 2017 at 9:28 PM

    Hi Marko,

    I am getting the same error RMAN-06023: no backup or copy of datafile N found to restore

    But, situation is different.. I am able to do full restore.. but, not pointing time restore

    Here, I am looking to do pointing time recovery.. Can you please check and let me whether below situation is possible or not.


    06:00 AM I have control file backup (No backup pieces of DB(datafile))

    10:00 AM I have control file and DB backup



    Can I restore my DB to 09:00 AM with the old control file? or with new control file?

    ReplyDelete
  44. UnknownJuly 26, 2018 at 5:47 PM

    You're a genius Marko!
    This post helped me a lot.

    ReplyDelete
  45. AnonymousOctober 8, 2018 at 10:33 PM

    -I will like to add my own one cent to this brilliant post.

    -I had this same issue below
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-06023: no backup or copy of datafile 1 found to restore.
    RMAN-06023: no backup or copy of datafile 26 found to restore
    RMAN-06023: no backup or copy of datafile 24 found to restore
    RMAN-06023: no backup or copy of datafile 22 found to restore
    RMAN-06023: no backup or copy of datafile 21 found to restore

    -My database had no FRA configured!! After applying some of the solution here it didn't resolved my issue. When i list backup everything was available. My issue was icarnation but here wasthe problem.

    rman target /

    restore controlfile from '/u01/backups/controlfile';

    startup mount;

    After restoring the controlfile, i had to list incarnation to check the current incarnation before catalogging the location of our old backups.

    1)RMAN> list incarnation;

    List of Database Incarnations
    DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
    ------- ------- -------- ---------------- --- ---------- ----------
    1 1 PRODDB 208461022 PARENT 2220377946274 08-MAY-09
    2 2 PRODDB 208461022 CURRENT 2247015011964 14-OCT-12

    catalog start with '/u01/backups/restor/prod';--------------new backup location(where i kept my backup old backups)


    catalog start with '/u01/backups/restor/arch/prod'; ----------- archivelog location(where i kept my old archivelogs)

    list incarnation;-----This is my turning point to resolving my problem

    After catalogging, i check incarnation to make sure i am using the same incarnation in the restored controlfile but to my dismair it was different so i had to reset incarnation to the current incarnation before catalogging the old backups and archivelogs;

    SOLUTION
    ========
    2)RMAN> list incarnation;


    List of Database Incarnations
    DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
    ------- ------- -------- ---------------- --- ---------- ----------
    1 1 PROD 208461022 PARENT 2220377946274 08-MAY-09
    2 2 PROD 208461022 PARENT 2247015011964 14-OCT-12
    4 4 PROD 208461022 ORPHAN 2285176539526 18-APR-18
    ------------3 3 PROD 208461022 CURRENT 2286678526923 02-JUN-18 different from step 1

    -----> 3)RMAN> reset database to incarnation 2;

    database reset to incarnation 2

    -----> 4)RMAN> list incarnation;


    List of Database Incarnations
    DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
    ------- ------- -------- ---------------- --- ---------- ----------
    1 1 PROD 208461022 PARENT 2220377946274 08-MAY-09
    ------------2 2 PROD 208461022 CURRENT 2247015011964 14-OCT-12 same with step
    4 4 PROD 208461022 ORPHAN 2285176539526 18-APR-18
    3 3 PROD 208461022 ORPHAN 2286678526923 02-JUN-18

    -----> RMAN> run
    {
    set until time "to_date('03-SEP-2018 23:53:00','DD-MON-YYYY hh24:mi:ss')";
    restore database;
    recover database;
    }

    executing command: SET until clause

    Starting restore at 08-OCT-18
    using channel ORA_DISK_1
    using channel ORA_DISK_2
    using channel ORA_DISK_3
    using channel ORA_DISK_4

    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 +ASM_diskgroup_name/prod/datafile/system.269.796619731
    channel ORA_DISK_1: restoring datafile 00021 to +ASM_diskgroup_name/prod/datafile/baniam.278.796619735

    archived log file name=/u01/backups/restor/arch/prod/arch1_SID_5912337_796622688.log thread=1 sequence=5912337
    media recovery complete, elapsed time: 00:28:42
    Finished recover at 08-OCT-18

    RMAN> alter database open resetlogs;

    Statement processed

    ReplyDelete
  46. UnknownFebruary 26, 2019 at 5:49 AM

    very nice research and thanks a lot.

    ReplyDelete
  47. AmicMay 24, 2019 at 9:36 AM

    Can't thank you enough!

    ReplyDelete

Subscribe to: Post Comments (Atom)