FreeLists / oracle-l / Re: Standby database - problem with SEQUENCE#

[Tanel Põder <tanel.poder.003@xxxxxxx>]

Hi,

Did you copy ALL your tablespaces using your method or just some?

The v$log_history contents are read from (standby) controlfile.

When you start recovery, Oracle checks the checkpoint_change# from 
v$datafile_header and deducts the first archivelog needed from there (matching 
the appropriate log with checkpoint scn for that datafile).

So for some reason you seem to have some old files in your standby location 
(the ones requiring logseq# 546). 

Do a select file#, checkpoint_change# from v$datafile_header on both of your 
databases and see whether the primary one still has way older 
checkpoint_change# for some datafiles. As long as those datafiles aren't part 
of read only tablespaces, you shouln't (or cannot - depending on number of your 
online redolog groups) have some files lagging way behind with their checkpoint 
information.

Tanel.

  ----- Original Message ----- 
  From: Luc Demanche 
  To: oracle-l@xxxxxxxxxxxxx 
  Sent: Wednesday, January 04, 2006 2:54 PM
  Subject: Standby database - problem with SEQUENCE#


  Hi all,

  Here is the way I have created my standby database
  On the primary site:
  1- alter tablespace ... begin backup
  2- copy the datafiles to the standby site
  3- alter tablespace ... end backup
  4- copy the redo log files
  5- alter database create standby controlfile as ....
  6- copy the standby controlfile to the standby site

  On the standby site:
  7- startup nomount
  8- alter database mount standby database

  Everything is fine.

  From the primary db
  - select max(sequence#) from v$log_history = 599

  From the standby db:
  - select max(sequence#) from v$log_history = 598

  I'm ready to apply the archived log #599 on the standby db
  When I issue
  - recover standby database, he ask me for the archive log #546 !!!!

  SQL> recover standby database;
  ORA-00279: change 82413819 generated at 12/24/2005 23:02:55 needed for thread 
1
  ORA-00289: suggestion : G:\ARCH\PROD\PROD001546.ARC
  ORA-00280: change 82413819 for thread 1 is in sequence #546 

  In my alert log file I have:
  ALTER DATABASE RECOVER  standby database  
  Wed Jan 04 15:36:36 2006
  Media Recovery Start
  Starting datafile 1 recovery in thread 1 sequence 588
  Datafile 1: 'G:\ORACLE\ORADATA\STBY\SYSTEM01.DBF'
  Starting datafile 2 recovery in thread 1 sequence 588 
  Datafile 2: 'G:\ORACLE\ORADATA\STBY\UNDOTBS01.DBF'
  Starting datafile 3 recovery in thread 1 sequence 588
  Datafile 3: 'G:\ORACLE\ORADATA\STBY\DRSYS01.DBF'
  Starting datafile 4 recovery in thread 1 sequence 588
  Datafile 4: 'G:\ORACLE\ORADATA\STBY\EXAMPLE01.DBF'
  Starting datafile 5 recovery in thread 1 sequence 546
  Datafile 5: 'H:\ORACLE\ORADATA\STBY\INDX01.DBF'
  Starting datafile 6 recovery in thread 1 sequence 588
  Datafile 6: 'G:\ORACLE\ORADATA\STBY\ODM01.DBF' 
  Starting datafile 7 recovery in thread 1 sequence 546
  Datafile 7: 'H:\ORACLE\ORADATA\STBY\TOOLS01.DBF'
  Starting datafile 8 recovery in thread 1 sequence 546
  Datafile 8: 'H:\ORACLE\ORADATA\STBY\USERS01.DBF'
  Media Recovery Log 
  ORA-279 signalled during: ALTER DATABASE RECOVER  standby database  ...
  Wed Jan 04 15:40:11 2006
  ALTER DATABASE RECOVER    CANCEL  

  The archive log with sequence 546 was created 2 weeks ago ......
  My question is:
  Why he needs the archived log file #546 ....  that archived log file is on 
tape offsite
  Even if I recreated my standby db, he always ask for that archived log file 
#546

  What can I do on the primary db ?

  Thanks
  -- 
  Luc Demanche
  Oracle DBA
  (514) 867-9977