8 Replies Latest reply: Jan 29, 2013 9:51 AM by 987596 RSS

    Duplicate backup copies

    987596
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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