Printable View
According to ref guide for V$ARCHIVE_DEST_STATUS
APPLIED_SEQ# ...
Identifies the log sequence number of the most recent applied redo log received at the destination
So why do I get more than one row like the following
SQL> select applied_seq#, applied_thread#
2 from gv$archive_dest_status
3 where applied_thread# > 0;
APPLIED_SEQ# APPLIED_THREAD#
------------ ---------------
6625 1
6620 1
SQL>
From the ref guide I would expect to get *the most recent*
Can you paste a "select *" instead?
here it is - thanksQuote:
Originally posted by Axr2
Can you paste a "select *" instead?
steve
Please format output and repost. "set pages 2000"
Axr2 probably ment SET LINESIZE 2000
No, I was more concerned about the pagesize, than the linesize. The repeated headers made it unreadable more than anything. I don't know what his defaults are set to in the login.sql..
Ah, OK. But with large enough LINESIZE you would get quite normaly readable output (no repeating headers, each reccord in one line), while only by eliminating repeated headers the output would still be barely readable.
thanks all, i was able to find out what i was looking for with the followingQuote:
Originally posted by jmodic
Ah, OK. But with large enough LINESIZE you would get quite normaly readable output (no repeating headers, each reccord in one line), while only by eliminating repeated headers the output would still be barely readable.
Select max(prmy.sequence#)-max(stby.sequence#) into v_gap_thread_1
from V$ARCHIVED_LOG@hr stby, V$ARCHIVED_LOG@hr 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
this lets me know the gap and it from there I can do 'if gap is too big then page dba' type of thing
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?
Hi,
To access v$ tables & views, standby db does not need to be opened in REAY ONLY MODE, if its in mount stage thats enough.
Yes, if you want to access any application related table it needs to be opened in READ ONLY MODE.
regards
anandkl
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*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?
tks
steve
Yes, that makes sense.
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.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.
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.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?
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.
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"!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
On primary, you are doing self join on V$ARCHIVE_LOG. However joining conditions do not make sence at all to me. You have:
which is the same asCode:...
and stby.thread# = prmy.thread#
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 ofCode:...
and prmy.thread# = 1
and stby.thread# = 1
...
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 beQuote:
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:
which is the same asCode:...
and stby.thread# = prmy.thread#
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 ofCode:...
and prmy.thread# = 1
and stby.thread# = 1
...
max(prmy.sequence#)-max(stby.sequence#) will allways be equal to 0 with those conditions, won't it?
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.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).