Friday, January 06, 2012

Display only the active archive log destinations

One thing I find annoying is when I want to see the archive log destinations in an oracle database.
I usually want to see only those that are enabled, and have a non null value for the destination.

show parameter log_archive_dest shows more than I care to look at.

Try this:


select name, value
from v$parameter
where name = 'log_archive_dest'
and value is not null
union all
select p.name, p.value
from v$parameter p where
name like 'log_archive_dest%'
and p.name not like '%state%'
and p.value is not null
and 'enable' = (
   select lower(p2.value)
   from v$parameter p2
   where p2.name =  substr(p.name,1,instr(p.name,'_',-1)) || 'state' || substr(p.name,instr(p.name,'_',-1))
)
union all
select p.name, p.value
from v$parameter p
where p.name like 'log_archive_dest_stat%'
and lower(p.value) = 'enable'
and (
   select p2.value
   from v$parameter p2
   where name = substr(p.name,1,16) || substr(p.name,instr(p.name,'_',-1))
) is not null
/


4 comments:

Simon said...

Hello,

Agreed. Totally annoying. Very useful SQL. I hope you don't mind, I've added the ERROR in a join to ARCHIVE_DEST_STATUS, as I fined that useful when doing things with dataguard. I realise of course one could keep adding things to what is a simply check, but the ERROR is one I've found useful. Thanks for sharing your SQL.



NAME VALUE STATUS ERROR
---------------------------------------- ------------------------------------------------------------------------------------------ ---------- ------------------------------
log_archive_dest_1 LOCATION=+FRA DB_UNIQUE_NAME=TOTEM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) VALID
log_archive_dest_2 service=TOTSTBY ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=TOTSTBY VALID

SQL> l
select name, value, null status, null error
from v$parameter
where name = 'log_archive_dest'
and value is not null
union all
select p.name, p.value, s.status, s.error
from v$parameter p , V$ARCHIVE_DEST_STATUS s
where name like 'log_archive_dest%'
and s.dest_name = upper(p.name)
and p.name not like '%state%'
and p.value is not null
and 'enable' = (
select lower(p2.value)
from v$parameter p2
where p2.name = substr(p.name,1,instr(p.name,'_',-1)) || 'state' || substr(p.name,instr(p.name,'_',-1))
)
union all
select p.name, p.value, s.status, s.error
from v$parameter p, V$ARCHIVE_DEST_STATUS s
where p.name like 'log_archive_dest_stat%'
and s.dest_name = upper(p.name)
and lower(p.value) = 'enable'
and (
select p2.value
from v$parameter p2
where name = substr(p.name,1,16) || substr(p.name,instr(p.name,'_',-1))
) is not null

Simon said...

...status and error of course...! :)

Jared said...

Certainly I don't mind. A worthy edition, thanks, updating mine with your changes.

Anonymous said...

Dear Jared Still

i have questions regarding change my career to oracle DBA , can i send my questions to you , if no problem where i can send my questions , i need your email address .

Thanks

nagi yahia hassan