This discussion is archived
9 Replies Latest reply: Nov 16, 2012 1:03 AM by Me_101 RSS

how to validate a rman restore?

Me_101 Newbie
Currently Being Moderated
hi gurus,

im using 11.2.0.3 oracle database under redhat 5.3.

I'm doing every monday and thursday a restore/recover with a full backup of prod database to test database (in another server).

I think that i can use any view or table to check if the restore was successfull or not with this script:

select end_time, status, input_type
from v$rman_backup_job_details
where input_type <> 'ARCHIVELOG';

For example, i did one restore on 12th and 15th of this week but it's appear:
END_TIME  STATUS                  INPUT_TYPE
--------- ----------------------- -------------
10-NOV-12 COMPLETED               DB FULL
11-NOV-12 COMPLETED               DB FULL
12-NOV-12 COMPLETED               DB FULL
13-NOV-12 COMPLETED               DB FULL
14-NOV-12 COMPLETED               DB FULL
15-NOV-12 COMPLETED               DB FULL
why appears another days if i ommited "ARCHIVELOGS", is there another way to query only the result of the restore/recover of rman backup?
  • 1. Re: how to validate a rman restore?
    asifkabirdba Guru
    Currently Being Moderated
    select output
    from V$rman_output
    order by stamp;



    Regards
    Asif Kabir
  • 2. Re: how to validate a rman restore?
    Niket Kumar Pro
    Currently Being Moderated
    to check whether restore is successfull or not you need to check database scn and compare it with prod database.

    the view you are looking is provide you backup information of database not restore information on another test server.
  • 3. Re: how to validate a rman restore?
    Me_101 Newbie
    Currently Being Moderated
    No "until scn" is provided in the script to restore test database. (i got a controlated error RMAN-03002 and RMAN-06054 when is recovering and they don't found the last archivelog).

    So i trying to find any table/view like provided by asifkabirdba. The problem of this query is that it shows a lot of information and must filter it.
  • 4. Re: how to validate a rman restore?
    asifkabirdba Guru
    Currently Being Moderated
    SELECT *
    FROM $archived_log
    WHERE '9203186102624' BETWEEN first_change# AND next_change#;

    Hope this will help you to find out the archived log sequence number.


    Regards
    Asif Kabir
  • 5. Re: how to validate a rman restore?
    Me_101 Newbie
    Currently Being Moderated
    Thanks,

    i modified a bit your query:
    select output 
    from v$rman_output 
    where upper(output) like '%RESTORE COMPLETE%' OR UPPER(OUTPUT) LIKE '%FINISHED RESTORE%';
    
    OUTPUT
    --------------------------------------------------------------------------------
    channel c1: restore complete, elapsed time: 06:07:18
    Finished restore at 15-NOV-2012 09:14:52
    channel c1: restore complete, elapsed time: 00:01:05
    to know the scn of the last archived log i will use something like this (tell me if i am wrong):
    select scn,to_char(time_dp, 'DD/MM/YYYY HH24:MI:SS') from smon_scn_time WHERE TIME_DP LIKE '15-NOV-12' ORDER BY 2;
  • 6. Re: how to validate a rman restore?
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check MOS Notes :
    Oracle10g / 11g - Getting Started with Recovery Manager (RMAN) [ID 360416.1]
  • 7. Re: how to validate a rman restore?
    Fran Guru
    Currently Being Moderated
    You can filter the output, something like this:
    select OUTPUT,
      case
        when upper(output) like '%RESTORE COMPLETE%' then 'completed'
        when UPPER(OUTPUT) LIKE '%FINISHED RESTORE%' then 'completed'
        else 'error' 
      end 
    from v$rman_output 
    where 
         upper(output) like '%RESTORE COMPLETE%' OR 
         UPPER(OUTPUT) LIKE '%FINISHED RESTORE%'
     OR (
    -- to avoid the controled erros you are having:
                                       SUBSTR(upper(output),1,5) = 'RMAN-'  AND 
                                       SUBSTR(UPPER(OUTPUT),6,5) <> '00571' AND 
                                        SUBSTR(UPPER(OUTPUT),6,5) <> '00569' AND 
                                        SUBSTR(UPPER(OUTPUT),6,5) <> '03002' AND 
                                        SUBSTR(UPPER(OUTPUT),6,5) <> '06054'
         );
  • 8. Re: how to validate a rman restore?
    L-MachineGun Pro
    Currently Being Moderated
    Me_101 wrote:

    ....is there another way to query only the result of the restore/recover of rman backup?
    Why not just do:
    RMAN> RESTORE DATABASE PREVIEW;
    :p
  • 9. Re: how to validate a rman restore?
    Me_101 Newbie
    Currently Being Moderated
    thanks to all for your time ans support.


    @L-MachineGun
    "rman > restore database preview" don't help because it check if with the backups you have actually, can restore the database. Not check if the restored was ok or not :P

Legend

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