gv$archive_dest_status and standby db

Quote:

---

Originally posted by jmodic
I'm a bit puzzled here. Obviously you are executing this code on the standby database, no? So you must open the standby database in read-only. Do you open it regulary anyway (for some kind of reporting purposes) or is it just to run this piece of code?

---

I am running it from a 3rd instance actually which houses rman , EM repository etc. From that instance I connect to the the primary through the db links to check the standby health based on *log gaps*

tks

steve

Yes, that makes sense.

Quote:

---

Originally posted by anandkl
To access v$ tables & views, standby db does not need to be opened in REAY ONLY MODE, if its in mount stage thats enough.

---

That's nothing new. However I don't know how do you create a database link from your primary to connect to the standby as sysdba (since you can only connect as sysdba if the database is not opened). I've tried several variations of CREATE DATABASE LINK statement without success.

Steve?

Quote:

---

Originally posted by jmodic
That's nothing new. However I don't know how do you create a database link from your primary to connect to the standby as sysdba (since you can only connect as sysdba if the database is not opened). I've tried several variations of CREATE DATABASE LINK statement without success.

Steve?

---

yep i struggled with it for a bit too but there is no link to the standby. If you look at V$ARCHIVED_LOG it seems to do the trick. Specifically I can see the last sequence # that was applied to the standby by looking at this view on the primary.

Perhaps I may not have the internals entirely correct but the primary has to be aware of what the last log to be applied to the standby so it can do gap resolution. By querying the primary I can determine the health of the standby. Based on our redo log generation rate and the time to transfer a log to the remote site I set a threshold of a 5 log gap - if the gap is bigger than 5 logs than page me.

To lessen the confusion on the query it really can be this

Select max(prmy.sequence#)-max(stby.sequence#) into v_gap_thread_1
from V$ARCHIVED_LOG stby, V$ARCHIVED_LOG prmy where stby.applied='YES' and stby.thread#=prmy.thread# and stby.thread#=1 group by stby.thread#;
dbms_output.put_line('thread one has a gap of '|| v_gap_thread_1

I have this in a procedure on another db that it is simply issuing the query against the primary. I like to segreate my db's so my health check procedures, capacity planning etc are not in production.

Quote:

---

Originally posted by stmontgo
Select max(prmy.sequence#)-max(stby.sequence#) into v_gap_thread_1
from V$ARCHIVED_LOG stby, V$ARCHIVED_LOG prmy where stby.applied='YES' and stby.thread#=prmy.thread# and stby.thread#=1 group by stby.thread#;
dbms_output.put_line('thread one has a gap of '|| v_gap_thread_1

---

Hm, more that I look at your query, less sense it makes to me. :confused: Are you sure it is returning the answer you are expecting? I'd bet the query will never ever return you anything different than "0"!

On primary, you are doing self join on V$ARCHIVE_LOG. However joining conditions do not make sence at all to me. You have:

Code:

---

...
and stby.thread# = prmy.thread#
and stby.thread#=1
...

---

which is the same as

Code:

---

...
and prmy.thread# = 1
and stby.thread# = 1
...

---

I other words, you are looking at the first thread (which probably is the one belonging to the primary) in both cases, which means that you are not observing the state of the standby at all! The PRMY and STBY in the query are only two different aliases for the same view V$ARCHIVE_LOGT on the primary database, so the result of
max(prmy.sequence#)-max(stby.sequence#) will allways be equal to 0 with those conditions, won't it?

Quote:

---

Originally posted by jmodic
Hm, more that I look at your query, less sense it makes to me. :confused: Are you sure it is returning the answer you are expecting? I'd bet the query will never ever return you anything different than "0"!

On primary, you are doing self join on V$ARCHIVE_LOG. However joining conditions do not make sence at all to me. You have:

Code:

---

...
and stby.thread# = prmy.thread#
and stby.thread#=1
...

---

which is the same as

Code:

---

...
and prmy.thread# = 1
and stby.thread# = 1
...

---

I other words, you are looking at the first thread (which probably is the one belonging to the primary) in both cases, which means that you are not observing the state of the standby at all! The PRMY and STBY in the query are only two different aliases for the same view V$ARCHIVE_LOGT on the primary database, so the result of
max(prmy.sequence#)-max(stby.sequence#) will allways be equal to 0 with those conditions, won't it?

---

we run rac so acutually I run the query for each thread - typicaly I see the max(prmy.sequence#)-max(stby.sequence#) being around 2 or 3 on the system when it is busy. I was running it ad hoc throughout the day with dbms_output to get an idea as to why my threshold should be

Ah, I confused THREAD# and DEST_ID. :o

Yes, your query works just fine, I just cehcked it. It is the "stby.applied='YES'" part that makes the difference between standby and primary in that query, so yes, you actualy get the "applied gap" (as opposed to "transported gap" that is reported by V$ARCHIVE_GAP on standby).

Quote:

---

Originally posted by jmodic
Ah, I confused THREAD# and DEST_ID. :o

Yes, your query works just fine, I just cehcked it. It is the "stby.applied='YES'" part that makes the difference between standby and primary in that query, so yes, you actualy get the "applied gap" (as opposed to "transported gap" that is reported by V$ARCHIVE_GAP on standby).

---

nice to have the extra validation - the standby is great in that it runs lights out pretty much unattended - the problem is that I get lazy and don't check it so often - the procedure is simply a nice safety net to double check it for me.