1 2 3 Previous Next 30 Replies Latest reply: Apr 3, 2013 2:27 AM by Hemant K Chitale RSS

    Cold Backup/Recovering A Table

    896971
      I am running 11r2 in a RAC environment. We have a primary plus two standby's (using Active Data Guard). Also, I am a new DBA, so bare with me. :)

      TableX in a certain database is corrupted. The corruption began a month ago due to a web developer's code change. The code change was fixed a week ago.
      Summary: the data in TableX is corrupted up until a week ago.

      Flashback doesn't extend that far into the past, so what I've been thinking of doing is, on our test server:

      1) Copy over relevant files from production to the test server
      2) Backup the original TableX on production
      3) Restore the system, sysaux, undo and the tablespace which contains TableX on test server
      4) Drop all other tablespaces in mount state (or not, if this step isn't necessary?)
      5) Recover the database till the time when the table was dropped or updated
      6) Take an export of TableX from database and import it into production, overwriting the current TableX
      7) Update the imported production TableX with the past week of "good" data from the backup in step 2

      I have 3 questions.
      Does this seem like a good strategy?
      Additionally, I am having difficulty determining what the "relevant files" are in step 1. What would I be copying over? Everything in the ArchiveLog folder? Or Everything in the db_recovery_file_dest folder? Or...?
      Lastly, in step 3, is there a way to perform the restore w/o overwriting preexisting data on the test server?

      Thank you
        • 1. Re: Cold Backup/Recovering A Table
          mseberg
          Hello;

          Difficult to answer your questions without additional information.

          Assuming you have an RMAN backup from before the issue I would use this to create a copy of the database on another server. Then I would data pump the table out and then back into production.
          Make sure you account for the old control file ( auto backup?? )

          If RMAN did the backup go to FRA? and do you have a copy of it on tape. If not RMAN you probably need all the cold backup files from that time or an export/data pump file.



          2 . Backup the original TableX on production - Why not just rename the table? or use CREATE TABLE AS SELECT FROM TableX;

          3) Restore the system, sysaux, undo and the tablespace which contains TableX on test server - Do the whole database
          4) Drop all other tablespaces in mount state (or not, if this step isn't necessary?) - Skip

          Steps 5 to 7 seem OK

          If by chance you do a daily export and have the old export file you could recover just the table in a test database and save yourself a lot of time.

          If its an RMAN cold backup I have an example here :

          http://www.visi.com/~mseberg/rman/rman_cold_backup.html


          Best Regards

          mseberg
          • 2. Re: Cold Backup/Recovering A Table
            896971
            Hi mseberg, thank you for your reply. It appears that RMAN is a red-herring. We apparently only use data guard, not RMAN. There is no "grantee" named RMAN in DBA_ROLE_PRIVS, nor is there a user with the "granted_role" of RECOVERY_CATALOG_OWNER.
            mseberg wrote:
            If not RMAN you probably need all the cold backup files from that time
            Just to be clear, you mean all the files in the "/database/archivelog/2013_03_01" (or whatever date in question) folder? This is the point I'm confused on. If I just pull over that folder into the test server's archivelog folder, wouldn't there need to be some sort of tweaking that would need to occur on test? The test Oracle instance wouldn't just automatically be able to use those logs. As an added hurdle, the test Oracle instance does not use data guard.

            No, we do not appear to do a daily export.

            BTW, I do have My Oracle Support, but I cannot find information on this specific topic.
            • 3. Re: Cold Backup/Recovering A Table
              mseberg
              OK

              This depends
              /database/archivelog/2013_03_01
              On mine it would be
              /u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2013_03_01
              You also have an autobackup from that time that you need.

              Does this make sense?

              On my system I backup to disk and then to tape so I only have a day or two of backups on Disk.

              Best Regards

              mseberg
              • 4. Re: Cold Backup/Recovering A Table
                896971
                mseberg wrote:
                On mine it would be
                /u01/app/oracle/flash_recovery_area/PRIMARY/backupset/2013_03_01
                I was just trying to be succinct earlier. Our directory is:
                /u02/app/oracle/fast_recovery_area/PRIMARY/archivelog/2013_03_01
                There is a "/u02/app/oracle/fast_recovery_area/PRIMARY/backupset" folder, but it is empty.
                You also have an autobackup from that time that you need.

                Does this make sense?
                Yes, but the "/u02/app/oracle/fast_recovery_area/PRIMARY/autobackup" folder is empty. I'm guessing data guard uses some other methodology for referring to it's logs?
                • 5. Re: Cold Backup/Recovering A Table
                  mseberg
                  OK

                  You show an "archivelog" folder which might help, but what you need is your RMAN backup files from March 1.

                  Example

                  o1_mf_annnn_TAG20130330T220007_8oh9os1x_.bkp

                  If you have all the RMAN backups from that date then you can restore them to another server and recover your table. Archive won't help much.

                  Do you have the backups from that time?

                  Best Regards

                  mseberg
                  • 6. Re: Cold Backup/Recovering A Table
                    Hemant K Chitale
                    Drop all other tablespaces in mount state (or not, if this step isn't necessary?)
                    I would suggest taking the datafiles OFFLINE.

                    You can RECOVER the database from a Cold Backup if you have the ArchiveLogs.
                    You would use the SQL command
                    RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL || SEQUENCE || CHANGE  || TIME;
                    and cancel or stop at the approriate archivelog, SCN or time.
                    What would I be copying over?
                    Controlfile and the datafiles for SYSTEM, SYSAUX, UNDO, target_tablespace from the Cold Backup. Plus all archivelogs since the Cold Backup.
                    is there a way to perform the restore w/o overwriting preexisting data on the test server
                    You would have to restore to a different folder and when you ALTER DATABASE MOUNT you ALTER DATABASE RENAME DATAFILE to specify the new locations of the datafiles.


                    Hemant K Chitale
                    • 7. Re: Cold Backup/Recovering A Table
                      896971
                      mseberg wrote:
                      OK

                      You show an "archivelog" folder which might help, but what you need is your RMAN backup files from March 1.

                      Example

                      o1_mf_annnn_TAG20130330T220007_8oh9os1x_.bkp

                      If you have all the RMAN backups from that date then you can restore them to another server and recover your table. Archive won't help much.

                      Do you have the backups from that time?
                      Sorry if I wasn't clear earlier. Apparently Data Guard can or cannot use RMAN (DBA's choice, depending on configuration). In our case, we do not appear to be using RMAN. There are no *.bkp files at all in /u02/app/oracle/fast_recovery_area/ and it's sub-directories.
                      • 8. Re: Cold Backup/Recovering A Table
                        mseberg
                        OK.

                        Thanks. So do you have a cold backup from that time?

                        If yes the files you need would be the results of these queries ( from that date plus the archive )

                        set heading off
                        set feedback off
                        set pagesize 100
                        set linesize 400



                        select a.name as newname from v$datafile a;


                        select a.name as newname from v$controlfile a;

                        Then use Hemant K Chitale's method to recover.

                        Best Regards

                        mseberg
                        • 9. Re: Cold Backup/Recovering A Table
                          896971
                          Hemant K Chitale wrote:
                          is there a way to perform the restore w/o overwriting preexisting data on the test server
                          You would have to restore to a different folder and when you ALTER DATABASE MOUNT you ALTER DATABASE RENAME DATAFILE to specify the new locations of the datafiles.
                          I think the MOUNT is the step that I was missing, though it seems so obvious now...

                          1) Copy over relevant files from production to the test server
                          2) Backup the original TableX on production

                          *2.5) MOUNT the copied data files onto the test server*

                          3) Restore the system, sysaux, undo and the tablespace which contains TableX on test server
                          4) Drop all other tablespaces in mount state (or not, if this step isn't necessary?)
                          5) Recover the database till the time when the table was dropped or updated
                          6) Take an export of TableX from database and import it into production, overwriting the current TableX
                          7) Update the imported production TableX with the past week of "good" data from the backup in step 2

                          Last Question (I think): you both refer to "THE cold backup." We use a diff backup. I assume you mean "copy over all files since the last full backup prior to the date that data corruption began"?
                          • 10. Re: Cold Backup/Recovering A Table
                            mseberg
                            Hello;

                            Given the time difference between Hemant and myself I'm not sure he's about.

                            What I mean by Cold backup is the database is shutdown and the data files, control files etc are copied to a cold backup location.

                            What exactly is a "diff backup" ?

                            Best Regards

                            mseberg
                            • 11. Re: Cold Backup/Recovering A Table
                              896971
                              mseberg wrote:
                              Hello;

                              Given the time difference between Hemant and myself I'm not sure he's about.

                              What I mean by Cold backup is the database is shutdown and the data files, control files etc are copied to a cold backup location.

                              What exactly is a "diff backup" ?

                              Best Regards

                              mseberg
                              Aaah. So there is no way to perform the copy w/o turning off the primary db? I guess I can turn off a standby and copy over the relevant files from it, instead.

                              My apologies. Diff = differential. Though that terminology is not accurate either, as dataguard is performing an incremental backup.
                              • 12. Re: Cold Backup/Recovering A Table
                                mseberg
                                Before you proceed let my ask a few questions and add some comments

                                We are trying to recover a table from a month ago correct?

                                What exactly happened to this table?

                                I guess I'm not 100 percent sure what type of backup is being used. The Cold backup example is just an example, if you are not using this it is a mute point.

                                Just to be clear for file format I posted for RMAN is not an absolute.

                                Before you proceed we need to know exactly what type of backup for the database is being used. Without this we cannot proceed

                                Is this An Incremental RMAN backup??

                                Since you are using Data Guard the Standby does not help with a corrupt table. The table is corrupt there also.

                                Best Regards

                                mseberg

                                Edited by: mseberg on Apr 2, 2013 10:35 AM
                                • 13. Re: Cold Backup/Recovering A Table
                                  EdStevens
                                  893968 wrote:
                                  mseberg wrote:
                                  Hello;

                                  Given the time difference between Hemant and myself I'm not sure he's about.

                                  What I mean by Cold backup is the database is shutdown and the data files, control files etc are copied to a cold backup location.

                                  What exactly is a "diff backup" ?

                                  Best Regards

                                  mseberg
                                  Aaah. So there is no way to perform the copy w/o turning off the primary db? I guess I can turn off a standby and copy over the relevant files from it, instead.

                                  My apologies. Diff = differential. Though that terminology is not accurate either, as dataguard is performing an incremental backup.
                                  DataGuard isn't performing a backup at all. In the classic physical standby, dataguard is performing a continuous recovery, applying the redo as the archivlogs arrive. Much like if you were sitting at an rman prompt, responding 'yes' to every prompt for the next archivelog.
                                  • 14. Re: Cold Backup/Recovering A Table
                                    mseberg
                                    Yes! Thank You.

                                    I like to think of Data Guard, the Standby as the same database in a different mode.

                                    mseberg
                                    1 2 3 Previous Next