This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 19, 2013 8:31 PM by Hemant K Chitale RSS

How to restore specific backup file from RMAN?

1002173 Newbie
Currently Being Moderated
Hi,

I am just begginer with Oracle DB so I do not have a lot knowledge.

I created two backups at April the 8th and April the 10th.

I did following from RMAN.

shutdown immediate
startup mount
configure channel 1 device type disk format '/opt/oracle/backup/std_%U';
backup database
startup

output was:
including current control file in backupset
including current SPFILE in backupset



it created two files: std-0so6l0vn(2 GB) and std_0to6l106 (7 MB).
First question - first file is backup and what is the second file???

Today I created also backup and it also created two files: std_0uo6morg (2GB) and std_0vo6morv (7MB)

Now I want to return backup from 2 days ago (*std-0so6l0vn*) but can't;

I do the the following:
shutdown immediate
startup mount
restore database from '/opt/oracle/backup/std_0so6l0vn'


but getting error - only SPFILE or control file can be restored from autobackup.

How to restore my backup from two days ago? It looks to me so complicated and hardly understandable.

What command to execute from RMAN to succeed in restore?

Thank you
  • 1. Re: How to restore specific backup file from RMAN?
    moreajays Pro
    Currently Being Moderated
    Hi,

    refer Sample rman backup / restore method as below

    http://www.moreajays.com/2013/01/rman-backup-restoration-example.html

    If no autobackup configured on source db then manual pfile/spfile can be created to get DB on nomount stage & controlfile can be restore from particular backup piece/set

    Thanks,
    Ajay More
    http://www.moreajays.com
  • 2. Re: How to restore specific backup file from RMAN?
    Honza Newbie
    Currently Being Moderated
    Is the first backup still available on disk?
    Please provide the RMAN output of "show retention policy". You might want to configure the retention to something higher that the default 1.
    Could you also provide a more complete log file of those backups?
  • 3. Re: How to restore specific backup file from RMAN?
    1002173 Newbie
    Currently Being Moderated
    Hi,

    this is my whole backup output:


    Starting backup at 10-APR-13
    configuration for DISK channel 2 is ignored
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=157 devtype=DISK
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    input datafile fno=00006 name=/opt/oracle/product/10.2.0/dbs/C:oracleproduct10.2.0oradatactginst1maxdata1.dbf
    input datafile fno=00002 name=/opt/oracle/oradata/ctginst1/undotbs01.dbf
    input datafile fno=00001 name=/opt/oracle/oradata/ctginst1/system01.dbf
    input datafile fno=00003 name=/opt/oracle/oradata/ctginst1/sysaux01.dbf
    input datafile fno=00005 name=/opt/oracle/oradata/ctginst1/example01.dbf
    input datafile fno=00004 name=/opt/oracle/oradata/ctginst1/users01.dbf
    channel ORA_DISK_1: starting piece 1 at 10-APR-13
    channel ORA_DISK_1: finished piece 1 at 10-APR-13
    piece handle=/opt/oracle/backup/std_0uo6morg_1_1 tag=TAG20130410T030936 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    including current control file in backupset
    including current SPFILE in backupset
    channel ORA_DISK_1: starting piece 1 at 10-APR-13
    channel ORA_DISK_1: finished piece 1 at 10-APR-13
    piece handle=/opt/oracle/backup/std_0vo6morv_1_1 tag=TAG20130410T030936 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
    Finished backup at 10-APR-13
  • 4. Re: How to restore specific backup file from RMAN?
    1002173 Newbie
    Currently Being Moderated
    I do not know if autobackup is configured or not unfortunately I am not oracle expert.


    show retention policy output is:

    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; #default

    Is it enough to provide me with restore command which I will be able to execute?
  • 5. Re: How to restore specific backup file from RMAN?
    Honza Newbie
    Currently Being Moderated
    I would try the following:

    1. Determine the first backup tag. Here is an example extract from RMAN "list backup":
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    8       Incr 0  10.02M     DISK        00:00:03     12-FEB-13      
            BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20130212T130817
            Piece Name: /nfsmount/oraflash/h91dev/H91DEV/backupset/2013_02_12/o1_mf_ncsn0_TAG20130212T130817_8kndd2on_.bkp
      SPFILE Included: Modification time: 12-FEB-13
      SPFILE db_unique_name: H91DEV
      Control File Included: Ckp SCN: 48994017     Ckp time: 12-FEB-13
    The tag here is TAG20130212T130817

    2. Startup nomount + restore controlfile from a backup piece which contains the control file from that time.
    startup nomount
    restore controlfile from '<location>';
    3. Startup mount and restore the database
    alter database mount
    restore database tag='<tag from previous step>';
    I hope this helps and that it's correct, I'm not used to cold backups after all.
  • 6. Re: How to restore specific backup file from RMAN?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    including current control file in backupset
    including current SPFILE in backupset
    channel ORA_DISK_1: starting piece 1 at 10-APR-13
    channel ORA_DISK_1: finished piece 1 at 10-APR-13
    piece handle=/opt/oracle/backup/std_0vo6morv_1_1 tag=TAG20130410T030936 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
    The BackupPiece std_0vo6morv_1_1 contains the controlfile and spfile. Not the database.
    The database is in std_0uo6morg_1_1

    To see autobackup configuration (and other settings) issue a "SHOW ALL" at the RMAN command prompt.


    Hemant K Chitale
  • 7. Re: How to restore specific backup file from RMAN?
    Martin Kucera Explorer
    Currently Being Moderated
    Hello.

    It can be easier to set a point in time to which you want to restore the database and let RMAN decide what backupset to use and how. That will also immediately show you, whether your retention policy allows to restore the database to that point in the past. It is only possible, when all the required backupsets are still available.

    RMAN> set until time "to_date('08.04.2013 00:00:00', 'DD.MM.YYYY HH24:MI:SS')";
    RMAN> restore database;
    RMAN> recover database;
    RMAN> alter database open resetlogs;


    This is especially useful when dealing with online backups, that need further recovery from archivelogs. Otherwise, RMAN always wants to restore and recover the database to the most recent point in time. In your case, since you have an offline backup, there won't be any archivelog recovery.

    Kind regards,
    Martin
  • 8. Re: How to restore specific backup file from RMAN?
    1002173 Newbie
    Currently Being Moderated
    @Martin what if I have on one day in one hour couple of backups. How will RMAN decide what to choose between them? I suppose I must give some more specific estimated period
  • 9. Re: How to restore specific backup file from RMAN?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    The SET UNTIL TIME can be specified to a granularity of seconds.

    Alternatively, you can SET UNTIL CHANGE for a particular SCN or SET UNTIL SEQUENCE for a particular log file sequence#


    Hemant K Chitale
  • 10. Re: How to restore specific backup file from RMAN?
    1002173 Newbie
    Currently Being Moderated
    I still can not restore database; I tried Honza method.

    i try

    startup nomount
    restore controlfile from 'restore controlfile from /opt/oracle/backup/sss_11o7efd9_1_1';

    And getting message:

    no autobackup found or specified handle is not a valid copy or piece


    list backup returns:
    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    19 Full 6.80M DISK 00:00:01 19-APR-13
    BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20130419T025522
    Piece Name: /opt/oracle/backup/sss_11o7efd9_1_1
    Control File Included: Ckp SCN: 3178467 Ckp time: 19-APR-13
    SPFILE Included: Modification time: 02-APR-13



    show all returns:
    RMAN configuration parameters are:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
    CONFIGURE BACKUP OPTIMIZATION OFF; # default
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
    configuration for DISK channel 2 is ignored
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/opt/oracle/backup/sss_%U';
    CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/opt/oracle/backup/sss_%U';
    CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
    CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/10.2.0/dbs/snapcf_ctginst1.f'; # default


    Please help I really must restore database but can't do it.
  • 11. Re: How to restore specific backup file from RMAN?
    swapnil kambli Journeyer
    Currently Being Moderated
    what do you get for :
    restore controlfile from tag TAG20130410T030936 ;
  • 12. Re: How to restore specific backup file from RMAN?
    1002173 Newbie
    Currently Being Moderated
    Hello, thank you for answer

    but still can not RESTORE database.


    RESTORE CONTROLFILE FROM TAG TAG20130419T025522;


    control file or SPFILE must be restored using FROM AUTOBACKUP

    I think my default configuration was AUTOBACKUP OFF

    I will appreciate help to finally restore database.
  • 13. Re: How to restore specific backup file from RMAN?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    If you are not using a Catalog schema and have not configured AUTOBACKUP, you must SET DBID before issuing the RESTORE CONTROLFILE FROM 'backuppiece'

    The DBID would be visible in the log file of the last RMAN Backup run.


    Hemant K Chitale
  • 14. Re: How to restore specific backup file from RMAN?
    swapnil kambli Journeyer
    Currently Being Moderated
    ls -ltr /opt/oracle/backup/std_0vo6morv_1_1

    shutdown abort
    startup nomount

    set dbid= <dbid>;
    restore controlfile from '/opt/oracle/backup/std_0vo6morv_1_1';

    you will get dbid from the top section of your backup log.
1 2 Previous Next

Legend

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