Recovering database past controlfile SCN

Scenario

Restore the database and recover up to the last available archived log sequence.Auto Controlfile backup is generated before sometime disaster happened (lost all controlfiles, datafiles and redologfiles).After the autobackup of controlfile generation there had been few archive logs generated but not backed up. 

Steps
Restore the controlfile
Restore the datafiles
Catalogue the archived logs manually of which controlfile is not aware of
recover the database
open the database with resetlogs
Connect to rman and set dbid

RMAN> restore controlfile from '/u02/bkp/TESTCLONE/bkpcontrl_c-1057140170-20130324-01'

Starting restore at 25-MAR-2013 14:32:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=358 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u04/oracle/TESTclonedata/cntrl01.dbf
output file name=/u04/oracle/TESTclonedata/cntrl02.dbf
output file name=/u04/oracle/TESTclonedata/cntrl03.dbf
Finished restore at 25-MAR-2013 14:32:11

Let's do a listing of archived log files

RMAN> alter database mount
database mounted
released channel: ORA_DISK_1

RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
101     1    1       A 24-MAR-2013 12:05:46
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_1_810907546.dbf
102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf


This lists only two archive logs (these were the only available archive logs  at the time of controlfile autobackup happened), whereas we have three more archive logs generated after this control file backup was generated.

Let’s see in sqlplus

SQL> select sequence#,first_change#,next_change# from v$log order by 1;

 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
         0             0            0
         0             0            0
         0             0            0
         0             0            0
         0             0            0
         1     425593057    425626797
         2     425626797    425628778
         3     425628778   2.8147E+14

The controlfile says sequence# 3 is the current one

SQL> select archived,first_change#,next_change#,sequence# from v$archived_log order by first_time


ARC FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#
--- ------------- ------------ ----------
YES     425593057    425626797          1
YES     425626797    425628778          2

Now let’s try to recover to a specific scn

RMAN>  recover database until scn 425628778 preview ;

Starting recover at 25-MAR-2013 10:56:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=358 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=375 device type=DISK
using channel ORA_DISK_1
using channel ORA_DISK_2

List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf

Media recovery start SCN is 425626797
Recovery must be done beyond SCN 425628742 to clear datafile fuzziness
Finished recover at 25-MAR-2013 10:56:27

Lets try to recover to a later scn

RMAN>  recover database until scn 425646710 preview ;

Starting recover at 25-MAR-2013 10:57:10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_1
using channel ORA_DISK_2

List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf

Media recovery start SCN is 425626797
Recovery must be done beyond SCN 425628742 to clear datafile fuzziness
Finished recover at 25-MAR-2013 10:57:10

Recovering even to a later point is using same archive log file(the controlfile does not know about the other archive log files)


But  We have three more archive logs at the archive location, lets catalog the archive log information to the restored controlfile
RMAN> cataloged archived log
rchived log file name='/u04/arch/TESTCLONE/TESTclone_arc_1_3_810907546.dbf' ;

cataloged archived log
archived log file name=/u04/arch/TESTCLONE/TESTclone_arc_1_3_810907546.dbf RECID=103 STAMP=811006615

RMAN> cataloged archived log
rchived log file name='/u04/arch/TESTCLONE/TESTclone_arc_1_4_810907546.dbf ' ;
cataloged archived log
archived log file name=/u04/arch/TESTCLONE/TESTclone_arc_1_4_810907546.dbf RECID=104 STAMP=811008283

RMAN> cataloged archived log
rchived log file name='/u04/arch/TESTCLONE/TESTclone_arc_1_5_810907546.dbf ' ;
cataloged archived log
archived log file name=/u04/arch/TESTCLONE/TESTclone_arc_1_5_810907546.dbf RECID=105 STAMP=811008400

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------

102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf

103     1    3       A 24-MAR-2013 12:22:50
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_3_810907546.dbf

104     1    4       A 24-MAR-2013 14:55:50
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_4_810907546.dbf

105     1    5       A 24-MAR-2013 15:10:34
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_5_810907546.dbf

Lets see in sqlplus
SQL> select archived,first_change#,next_change#,sequence# from v$archived_log order by first_time ;

ARC FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#
--- ------------- ------------ ----------
YES     425593057    425626797          1
YES     425626797    425628778          2
YES     425628778    425646710          3
YES     425646710    425648285          4
YES     425648285    425650656          5

Now controlfile knows about the other archive log files .  v$archived_log shows the newly added archive log files details.

 Lets see what happens when we run same recover commands (with different scn) we ran before


RMAN> recover database until scn 425628778 preview ;

Starting recover at 25-MAR-2013 10:56:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=358 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=375 device type=DISK
using channel ORA_DISK_1
using channel ORA_DISK_2

List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf

Media recovery start SCN is 425626797
Recovery must be done beyond SCN 425628742 to clear datafile fuzziness
Finished recover at 25-MAR-2013 10:56:27


RMAN> recover database until scn 425646710 preview ;

Starting recover at 25-MAR-2013 11:14:48
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_1
using channel ORA_DISK_2

List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf

103     1    3       A 24-MAR-2013 12:22:50
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_3_810907546.dbf

Media recovery start SCN is 425626797
Recovery must be done beyond SCN 425628742 to clear datafile fuzziness
Finished recover at 25-MAR-2013 11:14:48

Unlike previous run this time the newly added archived logs are selected for applying.



RMAN> recover database until scn 425650656
Starting recover at 25-MAR-2013 16:37:45
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /u04/arch/ERPCLONE/erpclone_arc_1_2_810907546.dbf
archived log for thread 1 with sequence 3 is already on disk as file /u04/arch/ERPCLONE/erpclone_arc_1_3_810907546.dbf
archived log for thread 1 with sequence 4 is already on disk as file /u04/arch/ERPCLONE/erpclone_arc_1_4_810907546.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u04/arch/ERPCLONE/erpclone_arc_1_5_810907546.dbf
archived log file name=/u04/arch/ERPCLONE/erpclone_arc_1_2_810907546.dbf thread=1 sequence=2
archived log file name=/u04/arch/ERPCLONE/erpclone_arc_1_3_810907546.dbf thread=1 sequence=3
archived log file name=/u04/arch/ERPCLONE/erpclone_arc_1_4_810907546.dbf thread=1 sequence=4
archived log file name=/u04/arch/ERPCLONE/erpclone_arc_1_5_810907546.dbf thread=1 sequence=5
media recovery complete, elapsed time: 00:00:14
Finished recover at 25-MAR-2013 16:38:01

RMAN>  Alter database open with resetlogs;


Tested on 11g(11.2.0.3)

Comments

  1. Aravind16 December 2016 at 14:17

    Hi,
    It is very old post .. however it helped with a restore I am going to do this weekend. Thank you very much for sharing.. All the best !!!
    Regards
    Aravind

    ReplyDelete

Post a Comment

Popular posts from this blog

APP-FND-01436: List of Values cannot find any values for you to choose error from concurrent request program parameter (R12 12.1.3)

Image
APP-FND-01436: List of Values cannot find any values for you to choose error from concurrent request program parameter After upgrading oracle application 11i(11.5.10.2) to R12(12.1.3) running concurrent program request caused this issue When concurrent program is run for a concurrent request after upgrading to R12 12.1.3 the lov, which is attached to a parameter, gives APP-FND-01436: “List of Values cannot find any values for you to choose”. When the same request is run from oracle applications 11i 11.5.10.2 the same value set is working fine. The value set is based on a table. If you go and test the value set query from the value set definition screen it returns complete successful message yet if you go to the parameter of the concurrent request program form where the particular value set is used it gives you the above error. After searching google and oracle support I found metalink note 1400963.1, EBS R12.1: Parameter for Concurrent Request Using Table Based LOV G...
Read more

WEB ADI - issues encountered during initial access

Image
On accessing create document link under Desktop Integrator responsibility browser was showing following error Set BNE UIX Physical Directory profile value to proper directory at site level Location = $COMMON_TOP/html/cabo/  (11i) Location = $COMMON_TOP/webapps/oacore/html/cabo/  (R12) As this was a cloned instance the value of this profile setting was pointing to a wrong location. set as BNE UIX Physical Directory /u03/oracle/prodcomn/html/cabo/ Access to functions are controlled by function security so to access and work with web adi the user should have proper access. You should have access to particular integrator to access and work with it Find out responsibility which is created to work with web adi check the main menu for this responsibility. Modify the responsibility or Menu if default is not fitting to your needs. For instance Desktop Integration Menu has create document, Define Lay out, Define Mapping, Manage Documents, Setup options Note : you...
Read more

Error processing request - Contact your application administrator apex 20.1

Image
After upgrading 11.2.0.4 to 19c with apex 20.1 accessing apex_admin/apex pages was throwing “Error processing request” Contact your application administrator. There were three invalid objects for APEX_200100 WWV_FLOW_SESSION_RAS WWV_FLOW_WIZARD_API WWV_FLOW_DATA_LOADER Tried compiling these objects but compile was completing with PL/SQL: ORA-00942: table or view does not exist To resolve the issue following rant needs to be given then compile (ran both grant and compile as sysdba) grant select on SYS.DBA_XS_DYNAMIC_ROLES to APEX_200100; grant select on SYS.DBA_TAB_IDENTITY_COLS to APEX_200100; alter package APEX_200100.WWV_FLOW_SESSION_RAS compile body; alter package APEX_200100.WWV_FLOW_WIZARD_API compile body; alter package APEX_200100.WWV_FLOW_DATA_LOADER compile body; Returned 0 invalid object for APEX_200100  select * from dba_objects where status = 'INVALID' and owner = 'APEX_200100' ; Now both admin and apex pages ar...
Read more