Borndba

This i have seen in one of the forums. Which is something we are looking for sometime.

Not tried yet, but planning to implement soon once we migrate to much robust oem server.

export ORACLE_HOME=${AGENT_HOME}

Stop the agent:
${ORACLE_HOME}/bin/emctl stop agent

Save the file database.xmlp :
cp -p $ORACLE_HOME/sysman/admin/default_collection/database.xmlp $ORACLE_HOME/sysman/admin/default_collection/database.xmlp.orig

search for pattern <CollectionItem NAME=”tbspAllocation”>  or whatever and change the frequency to your requirement

<CollectionItem NAME=”tbspAllocation”>
<ValidIf>
<CategoryProp NAME=”VersionCategory” CHOICES=”pre8;8i;8iR2;9i;9iR2;10gR1;10gR2;10gR203;11gR1;11gR2″/>
</ValidIf>

<Schedule>
<IntervalSchedule INTERVAL=”7″ TIME_UNIT=”Day”/>
</Schedule>

<MetricColl NAME=”tbspAllocation” />
</CollectionItem>

Change the value of INTERVAL from 7 to 1 then save & quit.

Restart the agent :
$ORACLE_HOME}/bin/emctl start agent

The update will be shown in the Grid Control within 24H.

convert this post to pdf.

Our managment decided to give OEM access to development DBA. We were worried that development DBA might share the password to developer. There are 100s of developer in our environment. If everyone login to the OEM, this would create night mare. Since OEM is administration tool, we would prefer to be used by DBA.
How to protect development DBA sharing their pwd with developer. We tried something similar to sessions_per_user profile. Which didn’t work, as it is only for oracle user and not for OEM user.
Other approach is enable auditing on OEM user and if the login is coming from more than one ip, alert the dba.
By default, ip addr and hostname will not be registered in mgmt$audit_log. To enable it follow the metalink note#457382.1

exec mgmt_audit_admin.set_audit(0, null, 0);
shutdown the oms,opmn,agent
update
$OMS_HOME/Apache/Apache/conf/httpd.conf

make these 2 entries
UseWebCacheIp On
HostnameLookups On

restart the OMS.
After which each login in insert into mgmt_user_sessions table along with hostname and ipaddr. If a userid is used in more than one ipaddr, we can easily track.
select EM_USER,
             EM_USER_TYPE,
             EM_USER_HOST_NAME,
             OMS_HOST_NAME ,
             IP_ADDRESS
   from  MGMT_USER_SESSION
order by LOGIN_TIME desc;

But problem is this table might keep growing.
To do the purge,
Stop oms
exec mgmt_audit_admin.set_audit(0,null,2)
exec mgmt_audit_admin.audit_purge(’11-Jan-10′); — Give sysdate+1 to remove all the data

enable auditing
exec mgmt_audit_admin.set_audit(0, null, 0);
start OMS.

convert this post to pdf.

we have a requirement to alert for backup failures. Connecting to each rman repository or db to get this information is difficult. We have OEM, and this how we have implemented. We created UDM and applied to necessary targets.

Monitor DB fullbackup.

select count(*) from (select rownum no,x.status,x.end_time from (select status,end_time from v$rman_status where end_time is not null and object_type=’DB FULL‘ and end_time > sysdate-1 order by end_time desc) x) y where y.no=1 and y.status = ‘COMPLETED’

Monitor DB incr,

select count(*) from (select rownum no,x.status,x.end_time from (select status,end_time from v$rman_status where end_time is not null and object_type=’DB INCR‘ and end_time > sysdate-1 order by end_time desc) x) y where y.no=1 and y.status = ‘COMPLETED’

Archivelog backup.

select count(*) from (select rownum no,x.status,x.end_time from (select status,end_time from v$rman_status where end_time is not null and object_type=’ARCHIVELOG’ and end_time > sysdate-1 order by end_time desc) x) y where y.no=1 and y.status =’COMPLETED’

Other similar query which i came across is

select (sysdate-min(t))*24 from
(
  select max(b.CHECKPOINT_TIME) t
  from v$backup_datafile b, v$tablespace ts, v$datafile f
  where INCLUDED_IN_DATABASE_BACKUP='YES'
  and f.file#=b.file#
  and f.ts#=ts.ts#
  group by f.file#
)

for arch log monitoring,

select (sysdate-max(NEXT_TIME))*24 from v$BACKUP_REDOLOG

convert this post to pdf.