If you need to recover your database to a point in time by scn, sequence or time, you can use the following query to see the relation between time-scn-sequence, after restoring your database from a proper backup.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YY HH24:MI:SS';


SQL> select NAME, SEQUENCE#, THREAD#, FIRST_TIME, FIRST_CHANGE#, NEXT_TIME, NEXT_CHANGE# from v$archived_log where SEQUENCE# > 166;

Sample Output:

NAME SEQUENCE# THREAD# FIRST_TIME FIRST_CHANGE# NEXT_TIME NEXT_CHANGE#
------------------------------ --------- ------- ---------- ----------------- --------- ----------------
/arch/1_166_593039.arc 166 1 10-11-08 06:31:15 34516912
10-11-08 06:31:36 34521645
/arch/1_167_593039.arc 167 1 10-11-08 06:31:36 34521645
10-11-08 06:31:56 34527024
/arch/1_168_593039.arc 168 110-11-08 06:31:56 34527024
10-11-08 06:32:10 34532094
/arch/1_169_593039.arc 169 1 10-11-08 06:32:10 34532094
10-11-08 06:32:35 34537223
...

You can modify the where clause depending on your needs. SEQUENCE# gives the sequence number of the archive log. FIRST_CHANGE# and NEXT_CHANGE# specify the first and last System Change Number (SCN); FIRST_TIME and NEXT_TIME specify the starting and ending time of that archivelog. regarding to these information you can decide any of the following recover operations:

RMAN> recover database until sequence 162280;
RMAN> recover database until SCN 34527024;
RMAN> recover database until time '10-11-08 06:31:15'

,or if you want to manually control recover process with specifying archive logs one by one, you can use "until cancel" clause in SQL. This recovery process continues until you cancel. If your archive logs are not on their default path you can specify the full path of the archive logs in this recovery process.

SQL > recover database until cancel;

9 Responses so far.

  1. Brooke Kay says:

    Marvelous work.Just wanted to drop a comment and say I am new to your blog and really like what I am reading.

  2. Jerome says:

    This is a great post thanks for sharing.Data recovery service is the place where you can resolve from the physical creepiness into the media. Under this, the data recovery experts into manually check slate, replace the damaged parts & eventually lead the recovery.

  3. Anonymous says:

    Simple and Clear.Good Job.

  4. Unknown says:

    For recover database until sequence 162280;
    Let us know from where the sequence 162280 was calculated?

    For recover database until SCN 34527024;
    Can we take SCN number 34537223, as it is the last scn of archive log?

    Same wise,

    For recover database until time '10-11-08 06:31:15';
    Can we take time 11-08 06:32:35, as it is the last timing of archive log?

    NAME SEQUENCE# THREAD# FIRST_TIME FIRST_CHANGE# NEXT_TIME NEXT_CHANGE#
    ------------------------------ --------- ------- ---------- ----------------- --------- ----------------
    /arch/1_166_593039.arc 166 1 10-11-08 06:31:15 34516912 10-11-08 06:31:36 34521645
    /arch/1_167_593039.arc 167 1 10-11-08 06:31:36 34521645 10-11-08 06:31:56 34527024
    /arch/1_168_593039.arc 168 110-11-08 06:31:56 34527024 10-11-08 06:32:10 34532094
    /arch/1_169_593039.arc 169 1 10-11-08 06:32:10 34532094 10-11-08 06:32:35 34537223

    Thanks,
    Zaheer.

  5. Manoj says:

    Use SQL Database Recovery Software is the advance and a brilliant tool to recover both primary and secondary (MDF and NDF) database. This SQL MDF repair tool recovers the overall existing SQL database objects like Table, Triggers, Stored Procedure, Views, Rules and XML data types also.

    Get more info:- http://www.softmagnat.com/sql-database-recovery.html

  6. I may not say that this was the information i was actually looking for, but it has given me an insight of something that i never had an idea about. This is such a great and nice post a page that i find easy to understand. You may need professional consultation before Offering Architectural and Interior Design to a Private Developer, services that we offer at any given time at very affordable rates. let us know what you need our help,just by checking the link and communicating to us.

  7. harsha says:

    Really thanks for sharing such a wonderful blog.
    Oracle Fusion Financials Online Training

  8. Anonymous says:

    A befuddling web diary I visit this blog, it's incredibly grand. Strangely, in this present blog's substance made motivation behind fact and sensible. The substance of information is instructive
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

  9. nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.

    Spark and Scala Online Training
    Spark Scala Training
    Hyderabad

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -