9 Replies Latest reply: Nov 28, 2012 12:26 AM by moreajays RSS

    Recover 2 nodes RAC to another server

    pj*433620*ng
      Hi
      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?

      Pek
        • 1. Re: Recover 2 nodes RAC to another server
          P.Forstmann
          In the RMAN DUPLICATE statement try to use SET UNTIL clause to specify the sequence + 1 and thread with the lowest next change number:

          In your case:
          set until sequence 378 thread 2
          • 2. Re: Recover 2 nodes RAC to another server
            pj*433620*ng
            Hi,
            Thanks for the reply.
            Is that mean the archived log seq 476 from thread 1 is unused even though it is the next archived log?

            Rgds

            vincent
            • 3. Re: Recover 2 nodes RAC to another server
              moreajays
              Hi,

              Leave RMAN , try to pass redolog files from sqlplus

              In Mount stage >>

              alter database recover until cancel using backup controlfile;
              Suply REDO log files one by one until Media Recovery Completed is not appeared
              Thanks,
              Ajay More
              • 4. Re: Recover 2 nodes RAC to another server
                pj*433620*ng
                Hi,
                That was what i did, and it behave ssame as rman

                Thanks
                Pek
                • 5. Re: Recover 2 nodes RAC to another server
                  moreajays
                  Hi,

                  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,
                  count(*)
                  from v$datafile_header
                  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;


                  -Ajay
                  • 6. Re: Recover 2 nodes RAC to another server
                    pj*433620*ng
                    Hi,
                    Here is the result..

                    select status, checkpoint_change#, fuzzy,
                    to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
                    count(*)
                    from v$datafile_header
                    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;

                    STATUS CHECKPOINT_CHANGE# FUZ CHECKPOINT_TIME COUNT(*)
                    ------- ---------------------- --- ----------------------------- ----------
                    ONLINE 10058588406003 NO 25-NOV-2012 19:00:30 16

                    1 row selected.

                    SQL>
                    CHECKPOINT_CHANGE#
                    ----------------------
                    10058588406003

                    1 row selected.

                    SQL>
                    FILE_ID SCN THREAD SEQUENCE STATUS
                    ---------- ---------------- ---------- ---------- ----------
                    1 10058588406003 2 378 8192
                    2 10058588406003 2 378 0
                    3 10058588406003 2 378 0
                    4 10058588406003 2 378 0
                    5 10058588406003 2 378 0
                    6 10058588406003 2 378 0
                    7 10058588406003 2 378 0
                    8 10058588406003 2 378 0
                    9 10058588406003 2 378 0
                    10 10058588406003 2 378 0
                    11 10058588406003 2 378 0
                    12 10058588406003 2 378 0
                    13 10058588406003 2 378 0
                    14 10058588406003 2 378 0
                    15 10058588406003 2 378 0
                    16 10058588406003 2 378 0

                    16 rows selected.

                    SQL>
                    FHSCN
                    ----------------
                    10058588406003

                    SQL> select hxfil file_id, fhtnm tablespace_name from x$kcvfh;

                    FILE_ID TABLESPACE_NAME
                    ---------- ------------------------------
                    1 SYSTEM
                    2 SYSAUX
                    3 UNDOTBS1
                    4 UNDOTBS2
                    5 USERS
                    6 DATA_SE
                    7 INDEX_SE
                    8 DATA_SE
                    9 INDEX_SE
                    10 DATA_SE
                    11 DATA_SE_LOJACK
                    12 INDEX_SE
                    13 CJHIT
                    14 FLOW_1
                    15 DATA_SE
                    16 INDEX_SE

                    16 rows selected.

                    Edited by: pj**** on 26-Nov-2012 16:18
                    • 7. Re: Recover 2 nodes RAC to another server
                      moreajays
                      Hi,

                      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;


                      Thanks,
                      Ajay More
                      • 8. Re: Recover 2 nodes RAC to another server
                        pj*433620*ng
                        Hi
                        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?

                        Rgds

                        Pek
                        • 9. Re: Recover 2 nodes RAC to another server
                          moreajays
                          Hi,

                          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

                          Thanks,
                          Ajay More