Recover Oracle 9i Database to multiple points in time

Posted on 2006-05-04
Last Modified: 2009-07-29
We have a database that we need to get point-in-time snapshots from on a daily basis, from about a year ago until today.  We do not have flashback queries turned on, but we do have all the redo logs archived.  What I'd like to do is something like this:

1) Create a standby database whose highest SCN is just prior to the first day we need access to.
2) Use RECOVER DATABASE UNTIL '2005-06-01 17:00:00 -0500'
3) Open the database read-only and do a SELECT on the tables, dump the data as necessary (into text files, or using exp, it doesn't really matter)
4) Shutdown the database, remount it and go back to step (2) for the next day.

Is this possible in Oracle?  Or do I have to completely rebuild the database for each recovery?  Basically, once the database is opened (read-only, so I assume it would be NORESETLOGS), can I close it and continue recovery?
Question by:cpunate9
    LVL 2

    Accepted Solution

    Once you open the database you cannot apply any more logs.

    One solution could be :
    1) Mirror the standby database drives
    2) Split the mirror and mount secondary mirror set, apply log and open the db, do your queries.
    3) Re-enable the mirror, and repeat 2
    LVL 2

    Author Comment

    So we wouldn't have to rebuild the control files each time, and we'd apply the redo logs from the initial point each time?  In other words:

    1) Create a standby database* based on the latest control files/data files prior to the first date from which we wanted to access data.
    2) Mirror the drive (probably will just create a loop-back partition inside a file and set up the data/control files in there, then copy the file and remount it)
    3) Apply the redo logs to the mirror until the point in time that we needed.
    4) Do the select
    5) Repeat from (2)

    * We don't currently have a standby DB, but we could easily create one, as we have the datafiles/control files necessary from the live DB at the point in time.
    LVL 2

    Expert Comment

    Yes,  you don't need to recreate control files every time as the control files would also be there on the primary partition, the primary mirror would always be in standby mode, if you want you can keep applying logs to the standby database so that it is closer to the time when you want to open it.
    LVL 4

    Expert Comment

    There are three incomplete recovery options available: time-based, cancel-based, and change-based recovery. Time-based recovery brings the datafiles up to a given date and time. Cancel-based recovery applies entire archive log files and online redo log files one at a time until the word CANCEL is typed in, so you can't use autorecovery with it. Change-based recovery applies all archive log files and online redo log files up to, but NOT including, a given system change number.  If you have added datafile(s) since the last backup, and you want to recover up to a point before the datafile(s) were added, you will need to restore the control files and use the "using backup controlfile" option of the recover command .  (I don't know of a way to recover up to a point between datafile additions - it's all or none.)   The basic steps are similar to the complete recover database option, except for the incomplete recovery commands, which are shown below (only use one of these):

    SQL> recover automatic database until time '2005-02-14:15:45:00';
    Automatic is similar to Set Autorecovery On. Use 1 second before the time of failure or of the log to exclude, which, for Oracle 8.x and above, could be found using:
    select to_char(a.time,'YYYY-MM-DD:HH24:MI:SS') as time, from (select first_time as time, substr(name,1,58) as name, first_change# as change from v$archived_log union select first_time, 'Redo Log Group ' || to_char(group#), first_change# from v$log) a order by change;
    Or, for Oracle 7.x, could be found using:
    select to_char(a.time,'YYYY-MM-DD:HH24:MI:SS') as time, from (select time, substr(archive_name,1,58) as name, low_change# as change from v$log_history union select first_time, 'Redo Log Group ' || to_char(group#), first_change# from v$log) a order by change;
    SQL> recover database until cancel;
    Accept logs until you type in CANCEL.
    SQL> recover automatic database until change 43047423;
    The system change numbers (SCN) contained in each archive log file is shown in Oracle 8.x and above using: select name,first_change#,next_change# - 1 from v$archived_log;
    Or, for Oracle 7.x, using: select name,low_change#,high_change# from v$log_history;
    For the online redo logs, you could use "select max(first_change# - 1) from v$log;" to find the SCN to use for applying all but the current online redo log file.

    After an incomplete recovery, you must open the database with the RESETLOGS option, as shown below, and then immediately shut down the database and make a BACKUP, since the old backup is unuseable after RESETLOGS is used.

    SQL> alter database open resetlogs;

    Chetan Sachdeva
    LVL 7

    Assisted Solution

    If you are opening the database in readonly mode, you can go back and apply more logs as needed. When you open the database readonly, you do not have to reset logs.

    LVL 22

    Expert Comment

    No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

    I will leave the following recommendation for this question in the Cleanup Zone:
    Accept csachdeva(16612803)

    Any objections should be posted here in the next 4 days. After that time, the question will be closed.

    Experts Exchange Cleanup Volunteer
    LVL 2

    Expert Comment

    csachdeva has not answered the question posed by the author but given the standard recovery scenarios, so it is not correct to accept his answer.

    I had given the correct answer and vishal68 had made a valid addition that, one could create the DB in standby mode, open in read only query you data and apply more logs query again and so on so forth.


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Formatting varchar to numeric 8 58
    Space Delimited Sql File 4 60
    percentage remaining 6 30
    Oracle Database Upgrade 13 25
    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now