Listing only active archive log destinations


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

NAME VALUE STATUS ERROR
-------------------------------------------------------------------------------- ------------------------------ --------- -----------------------------------------------------------------
log_archive_dest_1 LOCATION=/n02/oraarch1/orq152a VALID
 valid_for=(ONLINE_LOGFILES,al
 l_roles) db_unique_name=ORQ152
 A

log_archive_dest_2 SERVICE=orq152b LGWR ASYNC AFF VALID
 IRM REOPEN=10 NET_TIMEOUT=30 v
 alid_for=(online_logfiles,prim
 ary_role) db_unique_name=orq15
 2b


NAME VALUE STATUS ERROR
-------------------------------------------------------------------------------- ------------------------------ --------- -----------------------------------------------------------------
log_archive_dest_3 LOCATION=/n02/oraarch1/orq152a VALID
 _standby valid_for=(STANDBY_LO
 GFILES,STANDBY_ROLE) DB_UNIQUE
 _NAME=orq152a

reference: http://jkstill.blogspot.in/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s