Home » RDBMS Server » Server Administration » Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward (Oracle 11.2.0.3.0, Windows server 2008)
Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629588] Thu, 11 December 2014 06:49 Go to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
Hi
On the primary&standby databases of a customer, I had this intitial problem to investigate-databases not on synch.
So initially the status on primary was showing an "unresolvable gap" as switchover status-[select database_role,switchover_status from v$database;]
Max(sequence#) considerably differed in both.There were several hundreds of archive log with applied "NO" on standby and some were missing so decided to do incremental backup from primary.
This was resolved by applying an incremental backup from primary(starting from the latest current scn of standby that time) and applied on standby.
This (standby roll forward)worked fine ..aftet the recovery,Max(sequence#) is same in both databases[select max(sequence#) from v$log_history;]also until all the latest archive logs shown as "applied"-[SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG order by sequence# desc;]
But on the standby site, under flash recovery area\archiovelog location, all the archivelog files are getting stored(after I did the incremental backup).Using RMAN, delete expired doesnt delete them.If they all have been applied, why it doesnt get deleted.

[select thread#,max(sequence#) from v$archived_log
where applied = 'YES'
group by thread#]-SAME RESULT ON BOTH.
[select * from v$archive_gap;]-NO ROWS SELECTED

The v$managed_standby view shows that the MRP0 status is "WAIT_FOR_LOG"
Also, switchover_status on PRIMARY is shown as "Failed destination"
Standby DB now opened in read only mode.
This is surely not on sync because Current scns are also still different on each other (standby is 800 less than primary)

PLEASE guide me what all areas and queries I should check now and how to make in sync.
Cant figure out the actual problem(s) also.

Many thanks

[Updated on: Thu, 11 December 2014 06:57]

Report message to a moderator

Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629697 is a reply to message #629588] Mon, 15 December 2014 04:56 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
I was expecting a quick reply from this forum but very disappointed after seeing no replies.
Is there anything unclear in my post?
Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629699 is a reply to message #629588] Mon, 15 December 2014 05:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I did see your post, but it is so confused that I could not see how to make a reply. However, to take just one of your many questions:
Quote:
Using RMAN, delete expired doesnt delete them.If they all have been applied, why it doesnt get deleted.

The DELETE EXPIRED command doesn't delete anything. It will remove references in the RMAN repository to archivelogs that have already been deleted. It has nothing to do with whether an archivelog has been applied.

It looks as though you are quite a long way out of your depth with this. But if you can post again with a more structured question, describing the situation more precisely, perhaps you will get some advice. If you use copy/paste to show what you actually see, that will be better than your verbal descriptions.

[Updated on: Mon, 15 December 2014 05:04]

Report message to a moderator

Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629710 is a reply to message #629699] Mon, 15 December 2014 08:57 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
Yes I am managing the standby DB for the first time.
In the alert folder, log.xml shows the following kind of message repeatedly(Primary database):
<msg time='2014-12-13T23:14:24.013+00:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='CEPGPO02-V' host_addr='10.8.4.212' module=''
pid='2652' version='1'>
<txt> Current log# 1 seq# 213789 mem# 0: E:\COREHR\REDO\REDO01A.LOG
</txt>
</msg>
<msg time='2014-12-13T23:14:24.013+00:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='CEPGPO02-V' host_addr='10.8.4.212' module=''
pid='2652'>
<txt> Current log# 1 seq# 213789 mem# 1: G:\COREHR\REDO\REDO01B.LOG
</txt>
</msg>
<msg time='2014-12-13T23:14:27.180+00:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='CEPGPO02-V' host_addr='10.8.4.212' module=''
pid='2652'>
<txt>WARNING: Managed Standby Recovery started with USING CURRENT LOGFILE
</txt>
</msg>
<msg time='2014-12-13T23:14:27.180+00:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='CEPGPO02-V' host_addr='10.8.4.212' module=''
pid='2652'>
<txt> DELAY 1440 minutes specified at primary ignored
</txt>
</msg>
<msg time='2014-12-13T23:14:28.443+00:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='CEPGPO02-V' host_addr='10.8.4.212' module=''
pid='2652'>
<txt>LGWR: Failed to archive log 2 thread 1 sequence 213790 (16086)
</txt>
</msg>
<msg time='2014-12-13T23:14:28.459+00:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='CEPGPO02-V' host_addr='10.8.4.212' module=''
pid='2652'>
<txt>Thread 1 advanced to log sequence 213790 (LGWR switch)
</txt>
</msg>
<msg time='2014-12-13T23:14:28.459+00:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='CEPGPO02-V' host_addr='10.8.4.212' module=''
pid='2652'>
<txt> Current log# 2 seq# 213790 mem# 0: E:\COREHR\REDO\REDO02A.LOG
</txt>
</msg>
<msg time='2014-12-13T23:14:28.459+00:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='CEPGPO02-V' host_addr='10.8.4.212' module=''
pid='2652'>
<txt> Current log# 2 seq# 213790 mem# 1: G:\COREHR\REDO\REDO02B.LOG
</txt>


Error 12518 received logging on to the standby
*** 2014-11-24 13:37:05.567 915 krsu.c
Error 12518 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'CORE_DMC'
Error 12518 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'CORE_DMC'


corehr_LGWR_2652 TRACE:
Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
*** 2014-11-24 12:36:20.137 2682 krsl.c
No standby database destinations have been configured
as being archived by the LGWR process
This instance will operate at a reduced protection mode until
network connectivity to the standby databases is restored and
all archivelog gaps have been resolved.

*** 2014-11-24 12:36:32.742
*** 2014-11-24 12:36:32.742 7723 krsu.c
Reusing NSS2 ...
Subscribing to KSR Channel [id=32]
success!
Indicating recv buffer for KSR Channel [id=20]
success
*** 2014-11-24 12:36:32.742 7890 krsu.c
Netserver NSS2 [pid 3264] for mode SYNC has been re-initialized
Performing a channel reset to ignore previous responses
Connecting as publisher to KSR Channel [id=20]
Successfully reused NSS2 [pid 3264] for dest CORE_DMC mode SYNC ocis=0x000000001897AB10
*** 2014-11-24 12:36:32.867 3561 krsu.c
upiahm connect done status is 0

*** 2014-11-24 12:37:30.383
LGWR found NSS2 alive.. waiting for msg

corehr_arc_4132 trace file
===========================
FAL[server]: Selected inactive destination; resetting temporarily
Redo shipping client performing standby login
OCIServerAttach failed -1
.. Detailed OCI error val is 12518 and errmsg is 'ORA-12518: TNS:listener could not hand off client connection
'
OCIServerAttach failed -1
.. Detailed OCI error val is 12518 and errmsg is 'ORA-12518: TNS:listener could not hand off client connection
'
OCIServerAttach failed -1
.. Detailed OCI error val is 12518 and errmsg is 'ORA-12518: TNS:listener could not hand off client connection
'
*** 2014-11-24 13:37:05.567 4320 krsh.c
Error 12518 received logging on to the standby
*** 2014-11-24 13:37:05.567 915 krsu.c
Error 12518 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'CORE_DMC'
Error 12518 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'CORE_DMC'

---------------------------------------------------------------------
Standby server
==================
Error from trace file (E:\Oracle\Product\11.2\diag\rdbms\core_dmc\corehr\trace\corehr_rfs_9236.trc):
No space in recovery area for active standby redo logs
The primary database is operating in MAXIMUM PROTECTION
or MAXIMUM AVAILABILITY mode, and the standby database
does not contain adequate disk space in the recovery area
to safely archive the contents of the standby redo logfiles.
(I have physical space more than 150 GB and logical space used is only 22%)

corehr_rfs_11144 trace
=====================
WARNING: Managed Standby Recovery started with REAL TIME APPLY
DELAY 1440 minutes specified at primary ignored
DDE rules only execution for: ORA 312

The primary database is operating in MAXIMUM PROTECTION
or MAXIMUM AVAILABILITY mode, and the standby database does
not contain any viable standby redo logfiles.

[Updated on: Mon, 15 December 2014 11:18]

Report message to a moderator

Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629748 is a reply to message #629710] Mon, 15 December 2014 15:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Please post information from the the alert log in the trace directory, not that XML formatted stuff. And please enclose the post in [code] tags to make it readable.
However, it looks to me as though you have filled your fast recovery area. There are a few references to this in what you just posted.

Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629807 is a reply to message #629748] Tue, 16 December 2014 04:21 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
Hi John thanks for trying to help..!
I dont understand either how the db_recovery area got fully filled because there is enough physical disk space and logical disk space.
I also increased the logical space to 150 GB now(before it was 100 gb)..
For finding the logical space(in standby DB), I ran the foll.query, and I am only getting that PCT_FULL=14.3%
select 
   name,
   to_char(space_limit, '999,999,999,999') as space_limit,
   to_char(space_limit - space_used + space_reclaimable,
   '999,999,999,999') as space_available,
   round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from 
   v$recovery_file_dest;

Also the alert log errors-please see below:(alert_corehr.log in the trace folder):
PRIMARY
=======
Tue Dec 16 10:07:33 2014
Archived Log entry 350657 added for thread 1 sequence 217277 ID 0xd63d6ae5 dest 1:
Tue Dec 16 10:08:28 2014
WARNING: Managed Standby Recovery started with USING CURRENT LOGFILE
  DELAY 1440 minutes specified at primary ignored
WARNING: Managed Standby Recovery started with USING CURRENT LOGFILE
  DELAY 1440 minutes specified at primary ignored
Tue Dec 16 10:08:30 2014
Thread 1 cannot allocate new log, sequence 217279
Checkpoint not complete
  Current log# 2 seq# 217278 mem# 0: E:\COREHR\REDO\REDO02A.LOG
  Current log# 2 seq# 217278 mem# 1: G:\COREHR\REDO\REDO02B.LOG
WARNING: Managed Standby Recovery started with USING CURRENT LOGFILE
  DELAY 1440 minutes specified at primary ignored
LGWR: Failed to archive log 3 thread 1 sequence 217279 (16086)
Thread 1 advanced to log sequence 217279 (LGWR switch)
  Current log# 3 seq# 217279 mem# 0: E:\COREHR\REDO\REDO03A.LOG
  Current log# 3 seq# 217279 mem# 1: G:\COREHR\REDO\REDO03B.LOG
Tue Dec 16 10:08:35 2014
Archived Log entry 350659 added for thread 1 sequence 217278 ID 0xd63d6ae5 dest 1:
Tue Dec 16 10:09:28 2014
WARNING: Managed Standby Recovery started with USING CURRENT LOGFILE
  DELAY 1440 minutes specified at primary ignored
WARNING: Managed Standby Recovery started with USING CURRENT LOGFILE
  DELAY 1440 minutes specified at primary ignored
Tue Dec 16 10:09:29 2014
Thread 1 cannot allocate new log, sequence 217280
Checkpoint not complete
  Current log# 3 seq# 217279 mem# 0: E:\COREHR\REDO\REDO03A.LOG
  Current log# 3 seq# 217279 mem# 1: G:\COREHR\REDO\REDO03B.LOG
WARNING: Managed Standby Recovery started with USING CURRENT LOGFILE
  DELAY 1440 minutes specified at primary ignored
LGWR: Failed to archive log 4 thread 1 sequence 217280 (16086)
Thread 1 advanced to log sequence 217280 (LGWR switch)
  Current log# 4 seq# 217280 mem# 0: E:\COREHR\REDO\REDO04A.LOG
  Current log# 4 seq# 217280 mem# 1: G:\COREHR\REDO\REDO04B.LOG
Tue Dec 16 10:09:34 2014
Archived Log entry 350661 added for thread 1 sequence 217279 ID 0xd63d6ae5 dest 1:

Standby
=======
Tue Dec 16 10:17:36 2014
Managed Standby Recovery started with USING CURRENT LOGFILE
Ignoring previously specified DELAY 1439 minutes for thread 1 sequence 217286
Media Recovery Log G:\ORACLE\FLASH_RECOVERY_AREA\COREHR\ARCHIVELOG\ARC0000217286_0829387109.0001
Media Recovery Waiting for thread 1 sequence 217287
Tue Dec 16 10:17:43 2014
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
WARNING: Managed Standby Recovery started with REAL TIME APPLY
  DELAY 1440 minutes specified at primary ignored
Standby controlfile consistent with primary
RFS[30542]: Assigned to RFS process 4428
RFS[30542]: No standby redo logfiles created for thread 1 
RFS[30542]: No standby redo logfiles selected (reason:7)
Tue Dec 16 10:18:37 2014
WARNING: Managed Standby Recovery started with USING CURRENT LOGFILE
  DELAY 1440 minutes specified at primary ignored
RFS[30543]: Assigned to RFS process 7344
RFS[30543]: Opened log for thread 1 sequence 217287 dbid -700612123 branch 829387109
Archived Log entry 24334 added for thread 1 sequence 217287 rlc 829387109 ID 0xd63d6ae5 dest 2:
RFS[30543]: Archive log thread 1 sequence 217287 available in 1439 minute(s)
Tue Dec 16 10:18:38 2014
Managed Standby Recovery started with USING CURRENT LOGFILE
Ignoring previously specified DELAY 1439 minutes for thread 1 sequence 217287
Media Recovery Log G:\ORACLE\FLASH_RECOVERY_AREA\COREHR\ARCHIVELOG\ARC0000217287_0829387109.0001
Media Recovery Waiting for thread 1 sequence 217288
Tue Dec 16 10:18:45 2014
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
WARNING: Managed Standby Recovery started with REAL TIME APPLY
  DELAY 1440 minutes specified at primary ignored
Standby controlfile consistent with primary
RFS[30544]: Assigned to RFS process 1712
RFS[30544]: No standby redo logfiles created for thread 1 
RFS[30544]: No standby redo logfiles selected (reason:7)


Thanks

[Updated on: Tue, 16 December 2014 04:37]

Report message to a moderator

Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629812 is a reply to message #629807] Tue, 16 December 2014 04:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You are not looking at the alert log, are you? If you did, you would notice that you are no longer getting messages regarding the FRA being full. You are however getting messages regarding the fact that you have not created any standby log files. So you need to create them. Furthermore, it is clear that whoever configured this environment included an apply delay of 1440 minutes. You are trying to over-ride this by using real-time apply. You need to devide what you want to do: run apply with a delay, or not.

Generally speaking, are you sure that you want to do this yourself, rather than ask for professional help? Your client is running a standby database (which has cost him vast amounts of money in licence fees) because he does not want to lose any data. You shoud consider whether are competent to administer this.
Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629816 is a reply to message #629812] Tue, 16 December 2014 04:44 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
Thanks for the reply..Its alright,
they have RMAN backup as well.
I have told them I am doing the standby database for the first time so its a practise for me.
YES I definitely want to complete this, myself(Make both in synch) so please help me.
I would like to keep the 1440 delay in standby (their original setting).
Please tell me the commands to apply from now on to the standby,to cancel the real time apply and recover until latest archivelog and make synching with primary, in step by step commands.
Also how to create standby redo logs?
If I do the above 2 steps, will the standby automatically pick up and both will be in synch?

[Updated on: Tue, 16 December 2014 05:03]

Report message to a moderator

Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629825 is a reply to message #629816] Tue, 16 December 2014 05:25 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need to read the manuals or attend a course, and tell you client that you are not competent. I'm not going to fix all this for you for free.
Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629834 is a reply to message #629825] Tue, 16 December 2014 06:14 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
Well I am attending the 11g DBA workshop in 2 months

Would you tell the client you are not competent if you cant fix a thing, I won't staright away,until I exhaust till the end.That has not happened yet so I will only keep trying experimenting.
Anyway, you also don't have to give me that advice FOR FREE either if you cant help further.
Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629836 is a reply to message #629834] Tue, 16 December 2014 06:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Well I am attending the 11g DBA workshop in 2 months

Would you tell the client you are not competent if you cant fix a thing, I won't staright away,until I exhaust till the end.That has not happened yet so I will only keep trying experimenting.
It would seem that you are at the beginning of your career. So perhaps you will take some advice - though it is more likely that you reply wih something rude.

Never pretend to have knowledge that you do not have. I have told clients that I was not competent to do something, and needed assistance. The response was invariably been positive. I have also, many times, employed staff without necessary skills. Those that did not inform me of this immediately, I fired. Those that explained the help they needed, were given it.
This is not only business ethics, it is practical: if you are always honest, you will succeed; if you ever hide anything, you will fail.
Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629837 is a reply to message #629836] Tue, 16 December 2014 06:56 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
Here also I am only asking help, isnt it?If I ever pretended I have all the knowledge, I would never have been asking a question in this forum.
When you say, you cant give me the answer for free,that means you know the answer how to fix my issue but you cannot give the information to me.how RUDE is that?

I am not in the beginning of my career,but have to deal with standby databases for the first time.Unfortunately I didnt ask for this but was given this task.
You dont know everybody's situation right,so dont give blind sugegstions.This is an in-house project so I am not exactly dealing with an external client but I need to fix this somehow.I dont know how.
Imagine yourself in that situation and try to give technical advice if possible.
I am almost nearer to the solution,but can you atleast say,will the foll.steps work:
->Stop real time apply on standby
->Apply the delay of 1440 min in standby
->shutdown&startup in mount mode
(at the moment the standby is in read write mode)
Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629839 is a reply to message #629837] Tue, 16 December 2014 07:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is my last comment on this topic.

You have stated this,
Quote:
(at the moment the standby is in read write mode)

If that is correct, you have already destroyed the configuration. If it is incorr4ect, there is no point inb attempting to assist until you have tread the Data Guard manuals.
Or perhaps someone else is prepared to assist.

Goodbye.
Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629840 is a reply to message #629839] Tue, 16 December 2014 07:18 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
Really sorry,I meant "READ ONLY" mode.
My typing mistake to say "READ WRITE"
select open_mode from v$database;==READ ONLY WITH APPLY

Dataguard broker is disabled.
OK if you cant give technical commands step by step, atleast give a guideline for recovery now?
Or can someone else please help.

[Updated on: Tue, 16 December 2014 07:20]

Report message to a moderator

Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #629903 is a reply to message #629840] Wed, 17 December 2014 03:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I'll have one more try. But there is no point in poroceeding unless you read the Data Guard manuals: the User's Guide and the Reference.

First, note that READ ONLY WITH APPLY requires the Active Data Guard option. Unless you have bought that (and I am certain you have not, or you would not be operating your standby with a 1440 minute delay) you are operating illegally.
Second, as I have already told you, you need to create standby logs.
Third, shutdown the standby and start in mount mode.
Fourth, start redo apply but NOT real time apply.
Fifth, start redo propagation. You'll need to check the status of your archivelog destinations in v$archive_dest, and enable them if they are in error.

Before you do anything, read the manuals. And do not wait two months to do those introductory 11g Workshop courses. You say that you are not a beginner, so those elementary courses will be useless. Unless you are a beginner, in whch case you should be attending them before you even log on to a production system.

Re: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward [message #630000 is a reply to message #629903] Thu, 18 December 2014 09:13 Go to previous message
aviana
Messages: 106
Registered: July 2007
Senior Member
I am not asking any more query about this topic as I had an awful experience after starting this topic.
I dont think the 11g workshop2 course will be useless as I need the certification from that for the OCP exam.

[Updated on: Thu, 18 December 2014 09:15]

Report message to a moderator

Previous Topic: error in view while moving schema from db1 to db2
Next Topic: Refresh Production data to Development Schema
Goto Forum:
  


Current Time: Thu Mar 28 13:46:22 CDT 2024