9 Replies Latest reply: Nov 16, 2012 3:03 AM by Me_101 RSS

    how to validate a rman restore?

    Me_101
      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
          select output
          from V$rman_output
          order by stamp;



          Regards
          Asif Kabir
          • 2. Re: how to validate a rman restore?
            Niket Kumar
            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
              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
                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
                  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
                    Check MOS Notes :
                    Oracle10g / 11g - Getting Started with Recovery Manager (RMAN) [ID 360416.1]
                    • 7. Re: how to validate a rman restore?
                      Fran
                      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
                        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
                          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