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@(protected) 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 !!!!
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 (See ORA-279.ora-code.com) 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 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> <META content="MSHTML 6.00.2900.2802" name=GENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=#ffffff> <DIV><FONT face=Arial size=2>Hi,</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>Did you copy ALL your tablespaces using your method or just some?</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>The v$log_history contents are read from (standby) controlfile.</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>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).</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>So for some reason you seem to have some old files in your standby location (the ones requiring logseq# 546). </FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>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.</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>Tanel.</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <BLOCKQUOTE dir=ltr style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV style="FONT: 10pt arial">-- -- Original Message -- -- </DIV> <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B> <A title=lucdemanche@(protected) href="mailto:lucdemanche@(protected)">Luc Demanche</A> </DIV> <DIV style="FONT: 10pt arial"><B>To:</B> <A title=oracle-l@(protected) href="mailto:oracle-l@(protected)">oracle-l@(protected)</A> </DIV> <DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, January 04, 2006 2:54 PM</DIV> <DIV style="FONT: 10pt arial"><B>Subject:</B> Standby database - problem with SEQUENCE#</DIV> <DIV><BR></DIV> <DIV>Hi all,</DIV> <DIV> </DIV> <DIV>Here is the way I have created my standby database</DIV> <DIV>On the primary site:</DIV> <DIV>1- alter tablespace ... begin backup</DIV> <DIV>2- copy the datafiles to the standby site</DIV> <DIV>3- alter tablespace ... end backup</DIV> <DIV>4- copy the redo log files</DIV> <DIV>5- alter database create standby controlfile as ....</DIV> <DIV>6- copy the standby controlfile to the standby site</DIV> <DIV> </DIV> <DIV>On the standby site:</DIV> <DIV>7- startup nomount</DIV> <DIV>8- alter database mount standby database</DIV> <DIV> </DIV> <DIV>Everything is fine.</DIV> <DIV> </DIV> <DIV>From the primary db</DIV> <DIV>- select max(sequence#) from v$log_history = 599</DIV> <DIV> </DIV> <DIV>From the standby db:</DIV> <DIV> <DIV>- select max(sequence#) from v$log_history = 598</DIV> <DIV> </DIV> <DIV>I'm ready to apply the archived log #599 on the standby db</DIV> <DIV>When I issue</DIV> <DIV>- recover standby database, he ask me for the archive log #546 !!!!</DIV> <DIV> </DIV> <DIV>SQL> recover standby database;<BR>ORA-00279 (See ORA-00279.ora-code.com): change 82413819 generated at 12/24/2005 23:02:55 needed for thread 1<BR>ORA-00289 (See ORA-00289.ora-code.com): suggestion : G:\ARCH\PROD\PROD001546.ARC<BR>ORA-00280 (See ORA-00280.ora-code.com): change 82413819 for thread 1 is in sequence #546 </DIV> <DIV> </DIV> <DIV>In my alert log file I have:</DIV> <DIV>ALTER DATABASE RECOVER standby database <BR>Wed Jan 04 15:36:36 2006<BR>Media Recovery Start<BR>Starting datafile 1 recovery in thread 1 sequence 588<BR>Datafile 1: 'G:\ORACLE\ORADATA\STBY\SYSTEM01.DBF'<BR>Starting datafile 2 recovery in thread 1 sequence 588 <BR>Datafile 2: 'G:\ORACLE\ORADATA\STBY\UNDOTBS01.DBF'<BR>Starting datafile 3 recovery in thread 1 sequence 588<BR>Datafile 3: 'G:\ORACLE\ORADATA\STBY\DRSYS01.DBF'<BR>Starting datafile 4 recovery in thread 1 sequence 588<BR>Datafile 4: 'G:\ORACLE\ORADATA\STBY\EXAMPLE01.DBF'<BR>Starting datafile 5 recovery in thread 1 sequence 546<BR>Datafile 5: 'H:\ORACLE\ORADATA\STBY\INDX01.DBF'<BR>Starting datafile 6 recovery in thread 1 sequence 588<BR>Datafile 6: 'G:\ORACLE\ORADATA\STBY\ODM01.DBF' <BR>Starting datafile 7 recovery in thread 1 sequence 546<BR>Datafile 7: 'H:\ORACLE\ORADATA\STBY\TOOLS01.DBF'<BR>Starting datafile 8 recovery in thread 1 sequence 546<BR>Datafile 8: 'H:\ORACLE\ORADATA\STBY\USERS01.DBF'<BR>Media Recovery Log <BR>ORA-279 (See ORA-279.ora-code.com) signalled during: ALTER DATABASE RECOVER standby database ...<BR>Wed Jan 04 15:40:11 2006<BR>ALTER DATABASE RECOVER CANCEL <BR clear=all></DIV> <DIV>The archive log with sequence 546 was created 2 weeks ago ......</DIV> <DIV>My question is:</DIV> <DIV>Why he needs the archived log file #546 .... that archived log file is on tape offsite</DIV> <DIV>Even if I recreated my standby db, he always ask for that archived log file #546</DIV> <DIV> </DIV> <DIV>What can I do on the primary db ?</DIV> <DIV> </DIV> <DIV>Thanks<BR>-- <BR>Luc Demanche<BR>Oracle DBA<BR>(514) 867-9977 </DIV></DIV></BLOCKQUOTE></BODY></HTML>
