This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Mar 7, 2011 7:57 AM by 844914 RSS

Point in Time recovery

user4874781 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    "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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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