I am trying to restore our 2 nodes database to another single instance server.
I successfully restore controlfile, datafile but when I try to recover the database,
rman ask for a archivelog seq from thread 2 even though the next system change # is in thread 1 archivelog.
Please refer to the v$archived_log below
RECID THREAD# SEQUENCE# NEXT_CHANGE# FIRST_CHANGE#
853 1 476 10058590635887 10058588406010
852 1 475 10058588406010 10058586932543
851 2 377 10058588406003 10058586932534
After rman recovered the log seq 475, it keep asking for the next seq 378 in thread 2 which hasn't been archived by thread 2 instance,
the next change actuall in thread# 1, seq 476.
Is that mean I will have to wait thread# 2 archive the seq 378 log before I can recover seq 476 in thread#1?
What was the message when you provided "cancle" to alter database recover ... until cancle command?
can you provide below o/p
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set feedback on
set heading on
set pagesize 1000
set linesize 175
column checkpoint_change# format 999999999999999999999
select status, checkpoint_change#, fuzzy,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
group by status, checkpoint_change#, fuzzy, checkpoint_time
order by status, checkpoint_change#, fuzzy, checkpoint_time;
select distinct checkpoint_change# from v$datafile_header;
select hxfil file_id, fhscn scn, fhthr thread, fhrba_seq sequence, fhsta status from x$kcvfh;
select distinct fhscn from x$kcvfh;
select hxfil file_id, fhtnm tablespace_name from x$kcvfh;
If you have no plan to make this DB run in standby mode, you should move ahead & perform incomplete recovery followed by open resetlogs
recover database using backup controlfile until cancle; << make sure it show "Media recovery complete message & not any errors like system file needs recovery"
<supply cancel when prompted>
alter database open resetlogs;
I had no problem to recover until cancel and open database in resetlogs mode,
what I asked is there is a archivelog sequence 476 generated by thread 1 which should be the next archived log to be used for recovery
but Oracle keep asking for a archivelog seq 378 from thread 2, but at the time I do recovery, seq 378 from thread 2 not yet archived.
Is that mean I am going to lose all the data in archived log sequence 476 thread 1?
If thread 2 sequence is not generated in source itself then it must be thread2 online redo log whihc also you can apply (or may be tried already i guess)
If respective thread 2 not found then all uncommited transaction will be lost , to avoid it you should apply redo log.
Being a online/RMAN backup restore the consistency has to be compromised
So you have only to options left ..
1.Apply redo to recovery <if not done early>
2.Accept few transaction loss & open DB with resetlogs
Also there are less chances of loosing transaction if backup was taken in off peak hour