TKProf alternatives

Leave a comment

A recent thread on oracle-l listed several alternatives to the venerable tkprof tool. All of these alternatives have been around for several years, but they were new to me.

I haven’t used any of them yet, but I’m posting this so that I have them at the ready when needed!

trcanlzr.sql -> a tool available on My Oracle Support (MOS) at Doc ID 224270.1. It’s a souped up version of tkprof, apparently.

SQLTXPLAIN -> On MOS at Doc ID 215187.1. It’s a tool that analyzes one SQL statement at a time.

TVD$XTAT -> http://antognini.ch/top/downloadable-files. Created by a gentleman named Christian Antognini.

OraSRP -> http://www.oracledba.ru/orasrp. Created by a fellow named Egor Starostin.

Method-R profiler -> This is a commercial tool available at http://www.method-r.com/software/profiler from the Method-R company.

Managing OEM Repository Growth

Leave a comment

In my shop, we completed installing OEM agents on all the nodes to be monitored a few months back. However, the MGMT_TABLESPACE continues to grow. I figured that could be reasonable since I didn’t know the details of data retention parameters that govern when data gets purged. If a purge parameter was set to 6 months, but we had only reached our high number of targets 3 months ago, then naturally the database would keep growing.

I found these two links that explain purge parameters nicely.
How to change retention parameters for metrics history
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=430830.1
How to change retention parameters for job history
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=604978.1

There are three tables that contain the bulk of the data in the OEM tablespace MGMT_TABLESPACE. They are MGMT_METRICS_RAW, MGMT_METRICS_1HOUR and MGMT_METRICS_1DAY. They contain partitions that are named after time intervals, and OEM takes care of adding and dropping them. The default retention period for the raw data is 7 days, for the 1hour aggregate is 31 days and for the 1day aggregate is 365 days.

If you want to change the 7 day default period for MGMT_METRICS_RAW to 3 days, you must insert a record into the MGMT_PARAMETERS table to add the parameter mgmt_raw_keep_window.
INSERT INTO MGMT_PARAMETERS (PARAMETER_NAME, PARAMETER_VALUE) VALUES (‘mgmt_raw_keep_window’,’3′);
The companion parameters for the other two tables are mgmt_hour_keep_window and mgmt_day_keep_window.

Make Your Reservation for that Port

Leave a comment

Not a port of call, not port wine, but computer ports!

A colleague of mine tracked this down on the microsoft site.

To ensure that your Oracle Listener or SQL Server database starts correctly, you want to avoid other software starting up and grabbing the port needed by Oracle or SQL Server. In my shop, we’ve seen backup software, among others, grab a port and then prevent SQL Server from starting.

Here’s the link on how to do this:
http://support.microsoft.com/kb/812873/en-us

It involves making a registry edit to the key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters and then adding a new ReservedPorts. The port numbers added here will only be used if specifically requested by software.

This will not prevent conflicts if you configure two separate programs to request the same port. But it will save you from Windows automatically assigning the port number in question to software that did not specifically request a port.