We help IT Professionals succeed at work.

Reconcile Standby Gap

xoxomos
xoxomos asked
on
I ran out of space for archived logs on standby and somehow got hosed.  Now when I am trying to recover standby database I get

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00326: log begins at change 3416251864706, need earlier change
3416250739202
ORA-00334: archived log: '/u03/archived_logs/1_139498_665266938.arc'


How do i query for the os file name for the earlier change 3416250739202?
Comment
Watch Question

johnsoneSenior Oracle DBA

Commented:
The file name is in the message.  You should be looking for a file named  1_139498_665266938.arc

Since it most likely isn't on your standby, you'll have to look for it on the primary.  If it isn't there, then you'll have to restore it.

Author

Commented:
Actually the filename is on the primary.  I had thought the same so i copied and registered it.

Author

Commented:
> alter database register or replace logfile '/u03/archived_logs/1_139498_665266938.arc';

Database altered.

SQL>

Author

Commented:

SQL> alter database register or replace logfile '/u03/archived_logs/1_139498_665266938.arc';

Database altered.

SQL> recover standby database until cancel;
ORA-00279: change 3416250739202 generated at 12/05/2011 03:33:48 needed for
thread 1
ORA-00289: suggestion : /u03/archived_logs/1_139498_665266938.arc
ORA-00280: change 3416250739202 for thread 1 is in sequence #139498


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00326: log begins at change 3416251864706, need earlier change
3416250739202
ORA-00334: archived log: '/u03/archived_logs/1_139498_665266938.arc'

Author

Commented:
There is some way to link to change number to an actual file name.

Author

Commented:
Looks like it's not so bad, just tedious. Have to send logs from yesterday to different location on standby and have it look in the new location each time.  There must be an easier way than keying in this new location 200 + times :-(
Senior Oracle DBA
Commented:
You can reconsile a change number to an archive sequence with V$LOG_HISTORY.

Strange that the database is suggesting the wrong log.  The names between primary and standby don't change.

Author

Commented:
Just from the sound, i can tell that's what i'm looking for :-)
thanks

Author

Commented:
Turns out it was not the wrong name. Although the log with the correct name  was in the correct place i needed to go back to primary and send it over again.  Initially I thought I had to send to different location, register then apply, but towards the end I was just copying into the original location.  
Sometimes i got 'archive too small'.  Forgot to get the ORA- number on that.  I would see the file,
recover standby, get an error, copy the file manually, recover standby and advance to the next archive???

Author

Commented:
Found one.  Here the file was in /u03/archived_logs
I hit return and it says too small
I then copy from primary to another directory and try again successfully.

 
08:40:39: Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
08:40:57:
08:40:57: ORA-00332: archived log is too small - may be incompletely archived
08:40:57: ORA-00334: archived log: '/u03/archived_logs/1_139847_665266938.arc'
08:40:57:
08:40:57:
08:41:47: SQL> recover standby database;
08:41:47: ORA-00279: change 3416268054087 generated at 12/06/2011 04:52:45 needed for
08:41:47: thread 1
08:41:47: ORA-00289: suggestion : /u03/archived_logs/1_139847_665266938.arc
08:41:47: ORA-00280: change 3416268054087 for thread 1 is in sequence #139847
08:41:47:
08:41:47:
08:41:47: Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
08:42:16: /archivelog_bkup/1_139847_665266938.arc  
johnsoneSenior Oracle DBA

Commented:
Sounds like your copies across the network are being aborted by a timeout.

You shouldn't have to copy them to a different directory, but you certain can if you want to.

Author

Commented:
My sanity check.  I see someone else had the same problem.

https://forums.oracle.com/forums/thread.jspa?threadID=918938
***************************************************************************************************************************
Re: ORA-00332: archived log is too small - may be incompletely archived
Posted: Jun 23, 2009 10:00 PM     in response to: user10702579              
                   Reply
As I hv already mentioned, refering to 726580.1 note,Tried copying the archivelogs from primary to standby, but it didn't work.Tried copying the same file from Primary but didn't work.Please guide if the same will have to be recreated from a backup after the sequence was generated.
***************************************************************************************************************************


I read somewhere to copy the archives to a different location then did the register or replace command from SQLPlus after which it applied.