5 Replies Latest reply: Jan 25, 2013 9:59 AM by 926398 RSS

    backup single table partiton question?

    926398
      HI we are using oracle 11.2.0.3 on linux platform and was wanting to know if there is a way to backup just a single table partiiton using rman? If so does the database need to be runnnig in archivelog mode in order to do this with the database online?
      This is a database that has one single huge table that is partitioned by day and we are attempting to export out daily partitions using datapump export so that we can arhive them to tape but the disk system we have is not able to keep up and the export of roughly 128gb is taking over 8 hours and is failing beciase the undo is aging out.
      We would like to bypass writing the partitions out to disk and just stream them to tape if at all possible?
      Any help or suggestions are greatly appreciated.
      Thanks.
        • 1. Re: backup single table partiton question?
          Seberg
          Almost the exact same question asked here about 7 weeks ago.

          Re: RMAN selective backup
          • 2. Re: backup single table partiton question?
            Hemant K Chitale
            You'd have to move the partition to a dedicated tablespace and then use RMAN's BACKUP TABLESPACE command.

            You also must ensure that you backup the corresponding archivelogs !!


            Hemant K Chitale
            • 3. Re: backup single table partiton question?
              926398
              HI, thank you for your reply. I have come to this conclusion also that we could move the partition to another tablespace and then backup just that tablespace. One of my problems here is that the database is not in archivelog mode because it was writing out so many archive logs and was causing a perfromance problem on the database because the storage could not keep up.
              This is a database that it's only purpose is this one huge table that is partitioned by day and they are writing about 128gb per day into the partitions. The one table itself is over 10tb at this point in time and we are struggling with how to manage it.
              We got the business to agree that we will only keep 3 months worth of data in the live table and we can archive the rest but the current method we had in place which was a datapump export and then backup the .dmp file to tape.
              That method is no longer working beause the export is taking too long now because of the amount of data that is getting inserted on a daily basis and the export now is running too long and failing because it is taxing the I/O system on the server.
              So thats why we are trying to use rman to write directly to tape.
              The requirement from the businesss is that we may need to restore a partition from as far back as one year and not sure how this can be done if we would need to restore the tablespace from a backup that is almost a year old? I don;t think it is possible without all of the archive logs.
              Any suggestions are welcomed...
              Thanks.
              • 4. Re: backup single table partiton question?
                Hemant K Chitale
                One of my problems here is that the database is not in archivelog mode
                You need to have the database in ARCHIVELOG mode to take RMAN backups.

                You could set the target tablespace (containing the single partition) READ ONLY before you run a backup. However, if you use the same tablespace for the (new each day) daily partitions, you'd need to keep making it READ WRITE. In that case, you need all the archivelogs from the backup point to the current point. The tablespace will keep growing.

                If you create a separate tablespace each day, you can make it READ ONLY and even OFFLINE. Then, you don't need 1 year's archivelogs -- only the archivelogs covering the tablespace backup plus the first one after the backup.


                Hemant K Chitale
                • 5. Re: backup single table partiton question?
                  926398
                  Hi thanks for the great replies, I agree the database should be in archivelog mode so they will have to invest in faster I/O subsystem to make this happen.