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;

8 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. WOW....Nice information and Thanks for sharing this valuble infomation it is very seful for all.
    http://www.datarecoveryathome.in/services/

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

  6. telasmosquiteira-sp.com.br

    telas mosquiteira
    telas mosquiteiro

    As telas mosquiteira sp , telas mosquiteiro sp garantem ar puro por toda casa livrando-a completamente dos mosquitos e insetos indesejáveis. As telas mosquiteira garantem um sono tranquilo a toda família, livrando e protegendo-nas dos mais diversos insetos. Muitos destes insetos são transmissores de doenças e a tela mosquiteira é indispensável no combate a mosquitos transmissores de doenças.

    A dengue, por exemplo, já matou centenas de pessoas só na capital de São Paulo e um pequeno investimento em nossas telas mosquiteiras podem salvar vidas. As telas mosquiteiras também impedem a entrada de insetos peçonhentos como as aranhas e os escorpiões, estes insetos também oferecem risco, pois seu veneno em poucos minutos podem levar uma criança a morte.
    telas mosquiteira jundiai
    telas mosquiteiro jundiai
    telas mosquiteira aplhaville
    telas mosquiteiro alphaville
    telas mosquiteira granja viana
    telas mosquiteiro granja vinana
    telas mosquiteira cotia
    telas mosquiteiro cotia
    telas mosquiteira tambore
    telas mosquiteiro tambore

    A chegada da temporada Primavera/Verão traz consigo a elevação da temperatura e a maior ocorrência de chuvas. Mas não é só isso. As estações mais quentes do ano causam muita dor de cabeça e muitos zumbidos indesejáveis em função das pragas urbanas – pernilongos, baratas, cupins e outros insetos -, que afetam todas as regiões brasileiras.

    Nossa missão é oferecer telas mosquiteiras de qualidade a um preço acessível, fazendo com que as telas mosquiteiras sejam uma opção viável para muitas pessoas.

    telas mosquiteiras Jundiaí
    telas mosquiteiro Jundiai
    telas mosquiteiras jundiai
    telas mosquiteiro industria
    telas mosquiteira restaurante
    telas mosquiteiro restaurante
    telas mosquiteira empresa
    telas mosquiteiro empresa

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

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

Powered by Blogger.

Page Views

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