This discussion is archived
8 Replies Latest reply: Jul 26, 2012 10:06 PM by Shivananda Rao RSS

Rman recovery is losing data - advice welcome

652155 Newbie
Currently Being Moderated
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
    KuljeetPalSingh Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    That's right EdStevens. Let us look out for OP's response.

Legend

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