Reconcile Standby Gap

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?
xoxomosAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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.
0
xoxomosAuthor Commented:
Actually the filename is on the primary.  I had thought the same so i copied and registered it.
0
xoxomosAuthor Commented:
> alter database register or replace logfile '/u03/archived_logs/1_139498_665266938.arc';

Database altered.

SQL>
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

xoxomosAuthor 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'
0
xoxomosAuthor Commented:
There is some way to link to change number to an actual file name.
0
xoxomosAuthor 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 :-(
0
johnsoneSenior Oracle DBACommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xoxomosAuthor Commented:
Just from the sound, i can tell that's what i'm looking for :-)
thanks
0
xoxomosAuthor 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???
0
xoxomosAuthor 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  
0
johnsoneSenior Oracle DBACommented:
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.
0
xoxomosAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.