This discussion is archived
8 Replies Latest reply: Nov 26, 2012 11:43 AM by 975346 RSS

restore db directly from datafile backup...

975346 Newbie
Currently Being Moderated
Hi guys,

if i backup the db using

...
run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
CROSSCHECK BACKUP;
BACKUP AS COMPRESSED BACKUPSET incremental level 0 DATABASE FORMAT '/oracle/bkup/databasefiles_0_%d_%u_%s_%T';
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT '/oracle/bkup/controlfile_%d_%u_%s_%T';
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL RMAN_BACK_CH01;
}
...

say, I dont backup archivelogs at all.



Then, I try to restore db based on the above backup:

...
RUN
{
set until time "to_date('20121119 15:30:00', 'yyyymmdd hh24:mi:ss')";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
...


So, yeah, it failed like "RMAN-06023: no backup or copy of datafile 1 found to restore". My question is can I do it that way (not backup archivelogs in first step)? If so, how can I restore db later?

Thanks a lot.

- Bill
  • 1. Re: restore db directly from datafile backup...
    EdStevens Guru
    Currently Being Moderated
    user12975303 wrote:
    Hi guys,

    if i backup the db using

    ...
    run {
    ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
    CROSSCHECK BACKUP;
    BACKUP AS COMPRESSED BACKUPSET incremental level 0 DATABASE FORMAT '/oracle/bkup/databasefiles_0_%d_%u_%s_%T';
    sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
    BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT '/oracle/bkup/controlfile_%d_%u_%s_%T';
    CROSSCHECK BACKUP;
    DELETE NOPROMPT OBSOLETE;
    DELETE NOPROMPT EXPIRED BACKUP;
    RELEASE CHANNEL RMAN_BACK_CH01;
    }
    ...

    say, I dont backup archivelogs at all.



    Then, I try to restore db based on the above backup:

    ...
    RUN
    {
    set until time "to_date('20121119 15:30:00', 'yyyymmdd hh24:mi:ss')";
    RESTORE DATABASE;
    SWITCH DATAFILE ALL;
    RECOVER DATABASE;
    }
    ...


    So, yeah, it failed like "RMAN-06023: no backup or copy of datafile 1 found to restore". My question is can I do it that way (not backup archivelogs in first step)? If so, how can I restore db later?

    Thanks a lot.

    - Bill
    You cannot recover to a point in time without the redo necessary to reach that point in time. YOu can do it without backing up the archivelogs, but that would mean you would have to maintain your archivelogs until such time as they were no longer needed.

    Which begs the question ... why are you not backing up the archivelogs?
  • 2. Re: restore db directly from datafile backup...
    JohnWatson Guru
    Currently Being Moderated
    It looks as though you made a backup today (20 Nov) and are trying to restore to take te database back to yesterday (19 Nov)? That is impossible. Your error is not to do with archivelogs, it is because you do not have a backup from yesterday.
  • 3. Re: restore db directly from datafile backup...
    Aman.... Oracle ACE
    Currently Being Moderated
    user12975303 wrote:
    Hi guys,

    if i backup the db using

    ...
    run {
    ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
    CROSSCHECK BACKUP;
    BACKUP AS COMPRESSED BACKUPSET incremental level 0 DATABASE FORMAT '/oracle/bkup/databasefiles_0_%d_%u_%s_%T';
    sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
    BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT '/oracle/bkup/controlfile_%d_%u_%s_%T';
    CROSSCHECK BACKUP;
    DELETE NOPROMPT OBSOLETE;
    DELETE NOPROMPT EXPIRED BACKUP;
    RELEASE CHANNEL RMAN_BACK_CH01;
    }
    ...

    say, I dont backup archivelogs at all.



    Then, I try to restore db based on the above backup:

    ...
    RUN
    {
    set until time "to_date('20121119 15:30:00', 'yyyymmdd hh24:mi:ss')";
    RESTORE DATABASE;
    SWITCH DATAFILE ALL;
    RECOVER DATABASE;
    }
    ...


    So, yeah, it failed like "RMAN-06023: no backup or copy of datafile 1 found to restore". My question is can I do it that way (not backup archivelogs in first step)? If so, how can I restore db later?
    But don't you think that your reported error is about the backup not available rather than of the archivelogs?

    Aman....
  • 4. Re: restore db directly from datafile backup...
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    1. You MUST backup archivelogs or have them available. Else, although you will be able to RESTORE DATABASE, you will not be able to RECOVER DATABASE -- meaning that the restored database cannot be opened and is unusable.

    2. Oracle is looking for a backup that is older than "to_date('20121119 15:30:00', 'yyyymmdd hh24:mi:ss')"


    Hemant K Chitale
  • 5. Re: restore db directly from datafile backup...
    975346 Newbie
    Currently Being Moderated
    Thanks a lot, guys.

    The reason I try to do this is there are lots of archivelogs backup files generated every day, so very easily to reach the disk limit. I thought I can just backup datafiles then and when necessary recover the database from the list of datafile backups. seems it is a wrong idea.

    Any suggestions to reduce archivelogs backups?

    Thanks again.

    - Bill
  • 6. Re: restore db directly from datafile backup...
    EdStevens Guru
    Currently Being Moderated
    user12975303 wrote:
    Thanks a lot, guys.

    The reason I try to do this is there are lots of archivelogs backup files generated every day, so very easily to reach the disk limit. I thought I can just backup datafiles then and when necessary recover the database from the list of datafile backups. seems it is a wrong idea.

    Any suggestions to reduce archivelogs backups?

    Thanks again.

    - Bill
    Yeah, back them up and delete them:
    rman> backup archivelog delete all input;
    Should be part of your daily backup routine, in the same script as backup of the database itself. Depending on db activity and space available, you may need a separate job just for the archivelogs and run it several times a day.

    I'd suggest you get Hart and Freeman's book on RMAN Backup and Recovery and start reading. Then create a test system and start practicing. Less than 30 minutes ago I finished having to run an rman recovery on a production database. It was drop-dead simple, but it's the first one I'd had to do in YEARS and the pucker factor going in was not fun. Reminded me that I need to get a regular recovery exercise on my calendar.
  • 7. Re: restore db directly from datafile backup...
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Technically, you should backup all the archivelogs --- so, really, you cannot "reduce" archivelog backups.

    However, some sites may choose to backup only archivelogs that relate the database backup. Thus, if a database backup runs from 1am to 4am, the minimum you need is all the archivelogs generated between 1am and 4am plus the first archivelog generated after 4am. In theory (but bad in practice), you can choose to not backup the archivelogs that were generated outside this window. Not backing up archivelogs means that you cannot do a point in time recovery to a specific time. Your first attempt shows that you want to be able to do a point in time recovery -- which means that you must backup all archivelogs.

    Let me repeat : you should backup all the archivelogs.


    Hemant K Chitale
  • 8. Re: restore db directly from datafile backup...
    975346 Newbie
    Currently Being Moderated
    Thanks a lot, my friends. All your inputs are appreciated.

Legend

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