This discussion is archived
8 Replies Latest reply: Jan 29, 2013 7:51 AM by 987596 RSS

Duplicate backup copies

987596 Newbie
Currently Being Moderated
Hi there

For some reason my RMAN is has created duplicate copy of undotbs database. So there are now 2 files in my backup location but both are exactly same size. Interestingly, the time stamp on one file is 12 Dec where as other one gets updated daily. I guess the file whose last modified date is 12 Dec is also getting updated as it's size changes everyday (same as new file). I have tried to delete 12 Dec file but it gets created again.

Can some please help me in finding out how can I find out if this file is still being used or to delete it.

i have tried crosscheck and other commands but it doesnt list anywhere.

I am not pro in using RMAN but appreciate any help.

If this helps I am using RedHat Enterprise and oracle 11g.

thanks in advance
  • 1. Re: Duplicate backup copies
    mseberg Guru
    Currently Being Moderated
    Hello;

    I believe you are talking about the UNDO tablespace. Can you provide more details?

    What is the exact version of Oracle ( four digits )?

    Where are you backing up to a folder? FRA?

    What is the name of the file being created?

    Can you post the backup script?

    Best Regards

    mseberg
  • 2. Re: Duplicate backup copies
    987596 Newbie
    Currently Being Moderated
    Hi

    Thanks for your prompt reply. Yes I am talking about UNDO tables: Here are the details you have asked:

    What is the exact version of Oracle ( four digits )?

    Oracle database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

    Where are you backing up to a folder? FRA?

    Backup location is windows share mounted as /mnt/DBBackup/

    What is the name of the file being created?

    There are 2 files 01_mf_undotbs1_8djtqt3_.dbf (12 Dec 2012) and 01_mf_undotbs1_87tcyv5m_.dbf (latest)

    Can you post the backup script?

    This is what I ran to setup backup:

    rman target /

    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
    CONFIGURE BACKUP OPTIMIZATION OFF; # default
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/path/oracle/controlfile/%F'; # default
    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
    CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
    CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
    CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/software/oracle/db/dbs/snapcf_kmcs.f'; # default

    This has been working fine since last year. We had an outage on 12 dec 2012 due to SAN ran out of space on volume. The backup file 01_mf_undotbs1_8djtqt3_.dbf has got exact time stamp when the server went down. When it came back it might have created a new file in the night (not sure). If it did then how can i remove the old one.

    thanks for your help.

    Much appreciated.
  • 3. Re: Duplicate backup copies
    mseberg Guru
    Currently Being Moderated
    Hello again;

    Thank you for posting the requested information. These type of files :
    01_mf_undotbs1_87tcyv5m_.dbf
    Don't look like RMAN backup files, they look like database files.

    Can you run this and post the results ?
    set heading off
    set feedback off
    select name from v$datafile;
    Maybe run this instead
    select bytes, name from v$datafile where name like '%UNDO%';
    
    Correction
    
    select bytes, name from v$datafile where name like '%undo%';
    Best Regards

    mseberg

    Edited by: mseberg on Jan 28, 2013 11:46 AM
  • 4. Re: Duplicate backup copies
    987596 Newbie
    Currently Being Moderated
    Hi

    I ran that, it didnt work because of upper case but changing it to lower case returned:

    /data/oracle/oradata/filedata/undotbs01.dbf

    thanks
  • 5. Re: Duplicate backup copies
    mseberg Guru
    Currently Being Moderated
    OK;

    Odd. We need another clue. Before you posted your RMAN Configure which is great, but I'm looking for your backup script.

    Something like this :
    run {
    backup full as compressed backupset 
    database include current controlfile plus archivelog;
    }
    Can you find and post that. I'm thinking you must have a format command in it.

    Best Regards

    mseberg
  • 6. Re: Duplicate backup copies
    987596 Newbie
    Currently Being Moderated
    ok. I do not run a full back every night. I just merge the archivelogs. Here is the script:

    RUN {
    RECOVER COPY OF DATABASE WITH TAG 'incr_backup';
    BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE PLUS ARCHIVELOG;
    DELETE NOPROMPT OBSOLETE;
    DELETE NOPROMPT ARCHIVELOG ALL;
    }


    What I will do is, I will delete the file tonight and I know it will create again. So i will have a look in to logs and will post the result tomorrow. Meanwhile if you can find something.

    Thanks again

    Nilesh
  • 7. Re: Duplicate backup copies
    mseberg Guru
    Currently Being Moderated
    Nilesh;
    I do not run a full back every night. I just merge the archivelogs.
    But your Retention Policy is
    CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
    So your script may not be doing what you think it is.

    Your backup script scares me. I would not use it.

    "BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY"

    This command does not actually always create a level 1 incremental backup.
    If there is no level 0 image copy backup of an particular datafile,
    then executing this command creates an image copy backup of the datafile on disk
    with the specified tag instead of creating the level 1 backup.


    With an image copy backup like this all your eggs are in one basket.

    Image copies are not stored in an RMAN-specific format. They are identical to the results of copying a file with operating system.

    So I'm thinking on Dec 12th something happened to your UNDO.

    So maybe this is a better query after all :

    select name from v$datafile;

    It might shake out why you have two similar files.



    If your database is small enough or you have a test database try running something like what I posted before :
    run {
    backup full as compressed backupset 
    database include current controlfile plus archivelog;
    }
    If you are going to use a Retention Policy you should set it higher than 1.

    These statements :
    DELETE NOPROMPT OBSOLETE;
    DELETE NOPROMPT ARCHIVELOG ALL;
    May make recovery on your system impossible.

    In short I believe you have several issues and I would be testing my RMAN recovery plan in a test system ASAP.


    Please consider a complete review of your backups with RMAN.

    Best Regards

    mseberg
  • 8. Re: Duplicate backup copies
    987596 Newbie
    Currently Being Moderated
    Thanks Mseberg. I will do a recovery test asap. Meanwhile below is what you requested:

    -
    /kmdata/oracle/oradata/kmcs/system01.dbf
    /kmdata/oracle/oradata/kmcs/sysaux01.dbf
    /kmdata/oracle/oradata/kmcs/undotbs01.dbf
    /kmdata/oracle/oradata/kmcs/users01.dbf
    /kmdata/oracle/oradata/kmcs/kmcs_data.dbf
    /kmdata/oracle/oradata/kmcs/kmcs_index.dbf
    /kmdata/oracle/oradata/kmcsmt/kmcsmt_data.dbf
    /kmdata/oracle/oradata/kmcsmt/kmcsmt_index.dbf
    /kmdata/oracle/oradata/kmcs/TST2_data.dbf
    /kmdata/oracle/oradata/kmcs/TST2_index.dbf
    /kmdata/oracle/oradata/kmcs/kmcs_data_expansion_1.dbf


    thanks

Legend

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