[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1048
  • Last Modified:

Recover Oracle 9i Database to multiple points in time

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?
2 Solutions
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
cpunate9Author Commented:
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.
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.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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, a.name 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, a.name 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
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.

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
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now