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:
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
...status and error of course...! :)
Certainly I don't mind. A worthy edition, thanks, updating mine with your changes.
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
Post a Comment