There are diferent ways to collect performance related data

• Install Application Workload Repository (AWR) /Active Session History (ASH). Take AWR snapshots an hour or for desired interval
• Install and Configure STRMMON utility if you have 10gR2(Not available in 11g). Use Metalink Article “290605.1″
• Use UTL_SPADV as a alternative to STRMMON for > 11gR1
• Run Streams Healthcheckat each database: before, during , and after performance period of interest, available from Metalink:Article273674.1

Read more…

I have a 2 node RAC -DG setup between 2 remote data centres. After building DataGuard between them I am now coming across stange latency stats. Read more…

Details below are for configuring a schema level replication using API DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS.

Read more…

Use SQL Query below at Grid sysman database to locate growth of tablespaces over time. The returned results data could be used under EXCEL to generate graphs. Read more…

I have often used following procedure to create list of roles & corresponding users mapped into it. This was then used to email business owners as well as for user accesss auditing. Read more…

Capture process is an optional Streams client that is an Oracle background process that scans a database redo log to capture DML and DDL changes made to database objects. Read more…

This post lists sample queries to monitor Streams queues and propagations.

1)       Monitoring ANYDATA Queues and Messaging
2)       Monitoring Buffered Queues
3)       Monitoring Streams Propagations and Propagation Jobs

Read more…

Following shell script will be handy when you are working with Streams Replication.  There are 2 versions, one for 10g:monitor_streams_applyprocess_10g and another for 11g: monitor_streams_applyprocess_11g Read more…

SQL scripts below can display detailed information about error transactions in the error queue in a database.
These scripts are designed to display information about LCRs, but you can extend them to display information about any non-LCR messages used.

Read more…

ls -lt | grep "^-" | awk '{
if($8 ~ /:/)
   $8=2011
Date_count=$6" "$7", "$8
freq[Date_count]++}
END {for (date in freq) printf "%s\t%d\n", date, freq[date] }' | sort

Read more…

The streams contain following processes:

1. Capture

   Read more…

• List databases registered with rman repository

select NAME
     , DBID
  from RC_DATABASE;

Read more…

Management Services and Repository:

Lets you monitor system performance and access diagnostic information for the Oracle Management Services and Management Repository.

Read more…

If you are receiving tonnes of alerts from grid as below, here is a simple solution.

Read more…

Configuration details below are for a 2 Node RAC as Primary (PROD1,PROD2) & 2 node RAC(STDBY1,STDBY2) as Physical Standby.

Read more…

1) Remove the Data Guard Broker Configuration

Using the Commandline DGMGRL
SQL> show parameter dg_broker;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
dg_broker_config_file1               string      /mnt/data/oradata/PROD/dr1PROD.dat
dg_broker_config_file2               string      /mnt/data/oradata/PROD/dr2PROD.dat
dg_broker_start                      boolean     TRUE

Read more…

I generally don’t cut and paste metalink notes. But in case you are using DG , for diagnosis you will have to use 2 metalink scripts, [ID 241438.1] & [ID 241374.1] Read more…

I generally don’t cut and paste metalink notes. But in case you are using DG , for diagnosis you will have to use 2 metalink scripts, [ID 241438.1] & [ID 241374.1] Read more…

If you can’t see directory structure properly, use this file Read more…

Migrating from previous Oracle Enterprise Manager releases to Grid Control is a two-step process: Read more…

List Alerts and number of times they have been raised?

select substr(message_nlsid,1,50),count(*)
 from mgmt_current_severity
 where message_nlsid is not null
 group by message_nlsid
 having count(*) > 10
 order by 2 desc,1 ;

  Read more...

I normally have access to unix/linux systems through ssh (port 22) but firewall access is often disabled for ports like TNS (1521/1526), Emagent(1158), Grid  (5500). How do you connect to those ports if you don’t have direct access through firewall ? … Use SSH tunneling.

Read more…

The availability of the targets you manage is directly affected by the availability of the Management Agents, which gather data about your managed targets, and the host computers on which your targets are installed. As a result, it is important to understand how Enterprise Manager determines host and Management Agent availability.

Read more…

As a DBA,  one can’t look at entire application performance. But there is an easy way to see if database server load is normal when you hit issues . It’s done by creating and comparing baselines/snapshots under 11g Grid.  This option doesn’t work with 10g so you will need 11g database.

Read more…

How Grid Repository Stores Data?

The agents upload data at MGMT_METRIC_RAW table which is retained for 7 days.
Raw data are aggregated by Grid Control based on a predefined policy into 1-hour records, stored in MGMT_METRIC_1HOUR.
After 31 days or a month, 1-hour records in MGMT_METRIC_1HOUR are subject for further aggregation that rolls into 1-day records.
These 1-day records are stored in MGMT_METRIC_1Day for 365 days or 1 year before purged by Grid Control.

Read more…