1 2 Previous Next 24 Replies Latest reply: Mar 7, 2011 9:57 AM by 844914 RSS

    Point in Time recovery

    user4874781
      Hi All,

      If a table is dropped at 12pm and I need to recover it using point in time recovery on say a Oracle 7.3.2. DB on AIX box ; I assume this is the right strategy :

      1. Restore the previous full backup
      2. Make sure all the arch logs are available on disk
      3. startup mount
      4. recover database until time 'YYYY-MM-DD:HH24:MI:SS'

      time being used say : 11:55am - 11:59am ; I should be able to recover the table I'd presume ?

      Is this the right step ? Or should I resort to another recover syntax based on cancel ?

      I cant try on a test server on identical config however I did try get this to work ( i.e. recover the missing table ) on a 10g DB; but wasnt successful.

      Your advice please.

      Edited by: user4874781 on Apr 28, 2009 11:00 PM
        • 1. Re: Point in Time recovery
          Mahesh Menon
          This is an example to recover the database upto 11.59 am, on April 29th 2009... Do please alter the time and date as you need....

          recover database until time to_timestamp(2009-04-29:11:59:00, 'YYYY-MM-DD:HH24:MI:SS');

          Edited by: Mahesh Menon on Apr 29, 2009 10:21 AM

          Edited by: Mahesh Menon on Apr 29, 2009 10:22 AM
          • 2. Re: Point in Time recovery
            26741
            You have the right sequence of steps.

            You'd have to OPEN RESETLOGS before you can actually query the database to see if the table exists.
            What was it in the 10g test that "wasn't successful" ?
            What files did you restore ?
            What SET UNTIL and/or RESTORE and/or RECOVER commands did you specify in
            RMAN or SQLPlus ?
            • 3. Re: Point in Time recovery
              Mahesh Menon
              I gave this below command to him , I believe this is not wrong...If wrong, do please correct me too...

              This is an example to recover the database upto 11.59 am, on April 29th 2009... Do please alter the time and date as you need....

              recover database until time to_timestamp(2009-04-29:11:59:00, 'YYYY-MM-DD:HH24:MI:SS');+
              • 4. Re: Point in Time recovery
                user4874781
                On the 10g I tried to drop the table at 12pm and tried to recover the whole DB to 11:58am status but after "alter database open resetlogs" I still could not see the dropped table ( as I'd expect it to )
                I tried to restore the entire database.

                I used the recover database until time 'time format';
                SQL> alter database open resetlogs;

                Database altered.

                SQL> select * from test1;
                select * from test1
                *
                ERROR at line 1:
                ORA-00942: table or view does not exist

                Used SQLPLUS btw.
                • 5. Re: Point in Time recovery
                  Mahesh Menon
                  Hi ,

                  In 10g, you have a recyclebin to store dropped tables/objects....if flashback feature is enabled, without restoring or recovering , you can simple bring back the table...

                  you can query DBA_RECYCLEBIN and find out the original name of the object and name in recyclebin...

                  If ur table is there in Recyclebin,

                  then issue

                  FLASHBACK TABLE <table_name> TO BEFORE DROP;
                  • 6. Re: Point in Time recovery
                    user4874781
                    Thnx for your reply Mahesh.
                    But the fact is I need to be sure of the recovery process on a Oracle 7 DB but since I cant test on that I am testing on a 10g instead.
                    Wanted to get the concepts straight.
                    • 7. Re: Point in Time recovery
                      26741
                      I tried to drop the table at 12pm and tried to recover the whole DB to 11:58am
                      Was the "12pm" Database Server SystemClock Time ? Or on the basis of the SQLPlus Client (eg Desktop) ? Such that there is a time difference between the Server and the Client ?
                      • 8. Re: Point in Time recovery
                        user4874781
                        "12pm" was the Database Server System Clock Time.
                        But if the steps where correct, I will try this again today and post the contents here.
                        • 9. Re: Point in Time recovery
                          P.Forstmann
                          Syntax for SQL*Plus RECOVER statement is limited: you cannot use any SQL function or even timestamp.

                          Example with Oracle XE:
                          SQL> startup mount
                          ORACLE instance started.
                          
                          Total System Global Area  285212672 bytes
                          Fixed Size                  1287016 bytes
                          Variable Size             100666520 bytes
                          Database Buffers          180355072 bytes
                          Redo Buffers                2904064 bytes
                          Database mounted.
                          SQL> recover database until time_to_timestamp('2009-04-29:09:06:00','YYYY-MM-DD:HH24:MI:SS');
                          ORA-00277: illegal option to the UNTIL recovery flag TIME_TO_TIMESTAMP
                          
                          SQL> recover database until time '2009-04-29 09:06:00';
                          ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                          ORA-01194: file 1 needs more recovery to be consistent
                          ORA-01110: data file 1: 'D:\ORACLEXE\ORADATA\XE\SYSTEM.DBF'
                          
                          
                          SQL> recover database until time '2009-04-29 09:08:00';
                          Media recovery complete.
                          SQL> alter database open resetlogs;
                          
                          Database altered.
                          • 10. Re: Point in Time recovery
                            Mahesh Menon
                            We can do it with RMAN right ? But I am not sure whether RMAN is present in Oracle 7, as per his requirement...
                            I am afraid the command you gave was wrong...its not time_to_timestamp, its simply until time to_timestamp....

                            But timestamp datatypes are available from 9i onwards...so in Oracle 7..ordinary timings should work...

                            Edited by: Mahesh Menon on Apr 29, 2009 11:17 AM
                            • 11. Re: Point in Time recovery
                              P.Forstmann
                              In RMAN you can use SQL functions for the UNTIL clause that will return a DATE type value but not a TIMESTAMP type value:
                              http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta065.htm#i1000331

                              RMAN is available starting from Oracle 8.

                              Even with a better syntax you cannot use to_timestamp function with the RECOVER statement:
                              SQL> recover database until time to_timestamp('2009-04-29:09:20:00','YYYY-MM-DD:HH24:MI:SS');
                              ORA-00285: TIME not given as a string constant
                              Edited by: P. Forstmann on Apr 29, 2009 9:22 AM
                              • 12. Re: Point in Time recovery
                                user4874781
                                Ok, here's what I tried to do. I have a cold backup of the DB which includes 2 tables (*test1 and test2*). Test1 having 1 row and Test2 empty. Then I insert some values into Test2. Archived the log files and dropped the table ( Test2).
                                Then I tried to recover to a previous time to get the new entry in "Test2" using the archive logs and the old backup replaced.

                                Question is : Shouldn't I expect the new entry to show up here ( rolling forward with the Archive logs ? )

                                17:03:37 SQL> startup
                                ORACLE instance started.

                                Total System Global Area 289406976 bytes
                                Fixed Size 1290184 bytes
                                Variable Size 251658296 bytes
                                Database Buffers 29360128 bytes
                                Redo Buffers 7098368 bytes
                                Database mounted.
                                Database opened.
                                17:03:57 SQL> select * from test1;

                                CRAZY
                                ----------
                                12

                                17:04:04 SQL> select * from test2;

                                no rows selected

                                17:04:09 SQL> desc test2
                                Name Null? Type
                                ----------------------------------------- -------- ----------------------------

                                FUNNY NUMBER

                                17:04:26 SQL> insert into test2 values(100);

                                1 row created.

                                17:04:36 SQL> commit;

                                Commit complete.

                                17:04:39 SQL> alter system archive log current;

                                System altered.

                                17:05:09 SQL> alter system switch logfile;

                                System altered.

                                17:05:18 SQL> alter system switch logfile;

                                System altered.

                                17:06:05 SQL> drop table test2;

                                Table dropped.

                                17:06:21 SQL> shu immediate
                                Database closed.
                                Database dismounted.
                                ORACLE instance shut down.


                                17:06:56 SQL>
                                17:08:21 SQL> startup mount
                                ORACLE instance started.

                                Total System Global Area 289406976 bytes
                                Fixed Size 1290184 bytes
                                Variable Size 255852600 bytes
                                Database Buffers 25165824 bytes
                                Redo Buffers 7098368 bytes
                                Database mounted.
                                17:09:35 SQL> recover database until time '2009-04-29:17:05:20';
                                Media recovery complete.
                                17:10:09 SQL> alter database open resetlogs;

                                Database altered.

                                *17:10:34 SQL> select * from test2;*

                                no rows selected

                                Why isnt the new entry that I added shown up here ?

                                Your advice please !
                                • 13. Re: Point in Time recovery
                                  26741
                                  I see SHUTDOWN, STARTUP MOUNT and RECOVER DATABASE commands (and the OPEN RESETLOGS).


                                  Did you restore the Cold Backup before the STARTUP MOUNT and RECOVER ?
                                  Did the restore include the controlfile ?
                                  • 14. Re: Point in Time recovery
                                    user4874781
                                    Yes Hemant,

                                    In between the times specified, I restored the COLD backup which included the "previously taken" datafiles / redo / control files:

                                    *17:06:21* SQL> shu immediate
                                    Database closed.
                                    Database dismounted.
                                    ORACLE instance shut down.

                                    17:06:56 SQL>
                                    *17:08:21* SQL> startup mount
                                    ORACLE instance started.
                                    1 2 Previous Next