8 Replies Latest reply: Jul 27, 2012 12:06 AM by Shivananda Rao RSS

    Rman recovery is losing data - advice welcome

    652155
      Hi there,

      I really need some help here if anyone is a n RMAN guru.


      Problem
      ------------

      The system that I recover using the rman backup below does not include the table that I created after the backup had completed , the database has been recovered however somehow the creation and population of a table after the backup completed have been lost. Can anyone see what I have done wrong here, script is based on an existing script that I need to change as little as this is being used in Prod dystsems elsewhere

      DB is :- 11.2.0.1


      Please adsvise where I am going wrong ...


      $ORACLE_HOME/bin/rman target / log=$BACKUP_LOG <<EOF
      run {
      delete noprompt obsolete device type disk;
      allocate channel d1 device type disk format '$ORACLE_BACKUP_DIR_LOC/%Y_%M_%D/database_%U';
      allocate channel d2 device type disk format '$ORACLE_BACKUP_DIR_LOC/%Y_%M_%D/database_%U';
      backup as compressed backupset database include current controlfile;
      release channel d1;
      release channel d2;
      SQL 'alter system archive log current';
      allocate channel a1 device type disk format '$ORACLE_BACKUP_DIR_LOC/%Y_%M_%D/archivelog_%U';
      backup as compressed backupset archivelog all;
      release channel a1;
      delete noprompt obsolete device type disk;
      }
      list backup ;
      exit
      EOF


      What I did
      ------------------

      Create a table and insert a row first (pre backup) :-

      create table rman_test(empname varchar2(20),city varchar2(20));

      insert into rman_test values('azar','riyadh');
      insert into rman_test values('jabar','chennai');
      commit;

      select count(*) from rman_test ;
      2 rows

      Run the backup script as follows :-
      cd /u01/scripts
      sh hot_backup_db.bash emrep /u01/backup /u01/scripts
      Parameter 1 - ORACLE SID
      Parameter 2 - Directory where rman backups go
      Parameter 3 - Directory where rman logs go

      Create a table after backup so we can test that this data is not lost

      create table rman_test1(empname varchar2(20));
      insert into rman_test1 values('kareem');
      insert into rman_test1 values('syed');
      commit;
      select count(*) from rman_test1 ;
      2 rows

      Shutdown and rename the db folders to impersonate a systems crash

      Shutdown the database`
      Rename /u01/oradata/emrep to emrep-ren

      Renamed the spfile :-
      cd $ORACLE_HOME/dbs
      mv spfileemrep.ora spfileemrep.ora_ren

      Prepare for recovery

      Look at the rman backuplog :-

      Look for DBID :- e,g,           connected to target database: EMREP (DBID=4012088007)

      Look for where the spfile and controlfile backups are :-
      Piece Name: /u01/backup/2012_07_25/database_2jnh03ua_1_1
      Control File Included: Ckp SCN: 1344967 Ckp time: 25-JUL-12 16:16:10
      Piece Name: /u01/backup/2012_07_25/database_2knh03uc_1_1
      SPFILE Included: Modification time: 25-JUL-12 14:27:55

      Write down the path and name of the backup pieces in the backup location for the last backup.

      Recover the database (everything nuked)
      ----------------------------------------------------------

      rman target /
      set dbid 4012088007

      startup nomount

      restore spfile from '/u01/backup/2012_07_25/database_2knh03uc_1_1’;

      restore controlfile from '/u01/backup/2012_07_25/database_2jnh03ua_1_1';
      Starting restore at 24/07/2012 10:32:24
      using channel ORA_DISK_1
      channel ORA_DISK_1: restoring control file
      channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
      output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/cntrlemrep.dbf
      Finished restore at 24/07/2012 10:32:25

      shutdown immediate
      startup nomount

      restore controlfile from '/u01/app/oracle/product/11.2.0/dbhome_/dbs/cntrlemrep.dbf';
      alter database mount ;

      catalog backuppiece '/u01/backup/2012_07_25/database_2hnh03u3_1_1';
      catalog backuppiece '/u01/backup/2012_07_25/database_2inh03u3_1_1';
      catalog backuppiece '/u01/backup/2012_07_25/database_2jnh03ua_1_1';
      catalog backuppiece '/u01/backup/2012_07_25/database_2knh03uc_1_1';
      catalog backuppiece '/u01/backup/2012_07_25/archivelog_2lnh03vg_1_1';

      restore database ;

      recover database ;

      Experience this :-

      archived log thread=1 sequence=49
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03002: failure of recover command at 07/25/2012 13:42:54
      RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 49 and starting SCN of 1330997


      alter database open resetlogs ;

      select count(*) from rman_test ;
      select count(*) from rman_test1 ;


      QL> select count(*) from rman_test ;
      select count(*) from rman_test1 ;

      COUNT(*)
      ----------
      2

      SQL> select count(*) from rman_test1
      *
      ERROR at line 1:
      ORA-00942: table or view does not exist

      How do I recover everything ?
      Welcome any advice I can get. select count(*) from rman_test1 should return 2 rows.

      cheers, Rob Ford, Oracle DBA new to rman
        • 1. Re: Rman recovery is losing data - advice welcome
          kuljeet singh -
          somehow the creation and population of a table after the backup completed have been lost. Can anyone see what I have done wrong here,
          you also have to take backup of archivelogs which contain the changes that you made in database.
          apply these archivelogs and you will see your table again which was created after backup.
          • 2. Re: Rman recovery is losing data - advice welcome
            Shivananda Rao
            create table rman_test(empname varchar2(20),city varchar2(20));

            insert into rman_test values('azar','riyadh');
            insert into rman_test values('jabar','chennai');
            commit;

            select count(*) from rman_test ;
            2 rows

            Run the backup script as follows :-
            cd /u01/scripts
            sh hot_backup_db.bash emrep /u01/backup /u01/scripts
            Parameter 1 - ORACLE SID
            Parameter 2 - Directory where rman backups go
            Parameter 3 - Directory where rman logs go

            Create a table after backup so we can test that this data is not lost

            create table rman_test1(empname varchar2(20));
            insert into rman_test1 values('kareem');
            insert into rman_test1 values('syed');
            commit;
            select count(*) from rman_test1 ;
            2 rows

            Shutdown and rename the db folders to impersonate a systems crash
            Hi,

            After you inserted rows into rman_test table, you have taken the backup (an archivelog is created while taking the backup). But did you generate an archivelog once you inserted rows into rman_test1 table ?

            Commit does not write data to the datafiles, it writes to the log files. So, after committing the row inserted into rman_test1 table, generate an archivelog using "alter system switch logfile". Then shutdown the database and follow with the steps you have done. You would get back both the tables (rman_test and rman_test1) after you restore,recover and open the database in resetlogs.
            • 3. Re: Rman recovery is losing data - advice welcome
              fjfranken
              Shivananda Rao wrote:
              Commit does not write data to the datafiles, it writes to the log files. So, after committing the row inserted into rman_test1 table, generate an archivelog using "alter system switch logfile". Then shutdown the database and follow with the steps you have done. You would get back both the tables (rman_test and rman_test1) after you restore,recover and open the database in resetlogs.
              And since you apparently did not do this, you can fake the recover action searching for the archivelog by pointing it to the most recently used redo-log, you copied away, as the redo-entries are still in there.
              It is a dirty solution, but it works


              Cheers
              FJFranken
              • 4. Re: Rman recovery is losing data - advice welcome
                Shivananda Rao
                And since you apparently did not do this, you can fake the recover action searching for the archivelog by pointing it to the most recently used redo-log, you copied away, as the redo-entries are still in there.
                It is a dirty solution, but it works

                Cheers
                FJFranken
                In that case, what if an archive is not generated after the data has been inserted to the second table and the database crashes or if that archive or its backup is unavailable ?
                Would you be in a position to get the table back without having the archive ?

                If this is a dirty solution, then please provide the right solution so that I can update my knowledge
                • 5. Re: Rman recovery is losing data - advice welcome
                  652155
                  My goal here is to use this backup to recreate/restore the database to the last time of backup and then roll forward until point of failure.

                  I backed up the database using script, created the table and committed, shutdown the db and renamed folders to simiulate a failure ...

                  I am pretty sure the logs in my archive log folder are untouched so i should be able to roll forward to the point of failure but somehow after recover database the table is not there ...

                  I hope I am making sense.

                  Cheers, Rob
                  • 6. Re: Rman recovery is losing data - advice welcome
                    vlethakula
                    RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 49 and starting SCN of 1330997

                    This sequence could not have been archived, and it could be your redolog file with status CURRENT.

                    While recovering , give the path of your CURRENT redolog file when it asks for sequence 49.

                    sql> recover database using backup controlfile until cancel;

                    <paste your current redo log file compelete path>

                    Edited by: vreddy on Jul 26, 2012 2:49 PM
                    • 7. Re: Rman recovery is losing data - advice welcome
                      EdStevens
                      Shivananda Rao wrote:
                      And since you apparently did not do this, you can fake the recover action searching for the archivelog by pointing it to the most recently used redo-log, you copied away, as the redo-entries are still in there.
                      It is a dirty solution, but it works

                      Cheers
                      FJFranken
                      In that case, what if an archive is not generated after the data has been inserted to the second table and the database crashes or if that archive or its backup is unavailable ?
                      Would you be in a position to get the table back without having the archive ?

                      If this is a dirty solution, then please provide the right solution so that I can update my knowledge
                      If you are in archivelog mode
                      AND
                      If the change has not been written to an archvielog
                      THEN
                      the change must still be the online redo logs.

                      Which is one reason it is so important to multiplex the online redo across physically separate disks.

                      Sometimes the 'dirty' solution is the one that's needed. Especially if you haven't taken proper precautions ahead of time. Like making sure you do a log swithc and backup the archivelogs along with the database.
                      • 8. Re: Rman recovery is losing data - advice welcome
                        Shivananda Rao
                        That's right EdStevens. Let us look out for OP's response.