Vidya Bala's Blog

OC4J ping timeout issues

2007-09-20T14:37:00.000-07:00

We recently went with a Jdev Application using Oacle Application Server 10.1.3. While the deployment went fine, a few days after the deployment we started noticing oc4j ping timeout issues in the opmn.log. After the OC4J ping timeout OC4J tries to restart itself. We checked the max heap size - the max heap size looks good. There is not much activity on the server so we feel the default ping timeout of 20 seconds should be sufficient. I don't believe it can be a JDK version issue either - since out dev/test environment does not have this issue and is on the same version. will be great to know if anybody has run into a similar issue and know of any possible solutions.

Transferring System Statistics to a TEST environment

2007-04-13T12:26:00.000-07:00

Can we transfer system statistics from a Production environment to a Test environment? Most documentation talk about simulating a workload in your test environment and gather system statistics in test before implementing system statistics in Production. We are on Oracle 9i (9.2.0.6) in Production - we have not gathered system statsitics in Production so far. The decision has been made to start gathering System Statistics in Production. Testing this in a test environment prior to implementation seems like a challenge - given that our load test simulation may be very very different from Production.

Is it not possible to Gather System Stats in Production into a Staging Table , export the staging table to the Test environment and then import the Stats into the data dictionary in Test? Even if the above is possible , some key questions still remain while gathering System Stats into a Staging Table in Production -

a)is performance of Production impacted while System Stats is being gathered to a staging table?
b)SQL in the SGA invalidated ? - all documents that I have read so far tell me the answer is "No"
c)Since stats are gathered into a Staging Table , I am assuming no execution plans should change until stats are imported into the data dictionary in Production .

It will be great to know if any one has run into the same issue.

should we change sql to meet ANSI 99 standards

2007-04-11T15:53:00.000-07:00

I should say in all my years as both a Production and Development DBA I have very rarely seen my developers follow the most latest ANSI standard guide during sql development. Considering that most of us are on crunch time always with Application Development. Anyways I had the question posted to me " Shouldnt we change our sql to meet ANSI 99 compliance".

my 2 cents would be "No" - I really see the need to change the SQL if we were migrating say from Oracle to SQL Server for portability reasons. For the most bit Oracle version specific SQL development guides should be ANSI compliant and that should be enough - but then its just the way I have seen things work. Anyone run into the same issue - would be good to know.

A Strange Production Problem!!!

2007-02-26T10:09:00.000-08:00

A Strange Production Problem!!!

I suddenly got a call that the Front end Applications have frozen (those are the worst calls….). I logged on to the database server, was unable to login to the database, at the same time got a call that the ……………….

Network Appliance filer experienced a kernel panic or a low-level system-related lockup. The device then rebooted itself to correct the problem and proceeded normally through the startup process.

The database was a 2node RAC Cluster both accessing the NetApp Device via NFS mount points. After the NetApp rebooted itself:

NodeA on the database looked fine: ORACM was up on the server, could login to the database from NodeA.
NodeB: ORACM was down, Instance on NodeB was down.

Net Result: Application was still unable to connect to either of the Nodes using TAF.

Since the Applications were anyways down, the decision was made to restart the Cluster Manager on both nodes and start both the instances. The above resumed operations fairly quickly (not too much time was spent on roll forward and rollback operations, we did not have any long running transactions at the time of abort).

An SR has been opened to discuss if the above was the expected behavior.

With RAC I would have expected the following to happen:

Each Oracle instance registers with the local Cluster Manager. The Cluster Manager monitors the status of local Oracle instances and propagates this information to Cluster Managers on other nodes. If the Oracle instance fails on one of the nodes, the following events occur:
1. The Cluster Manager on the node with the failed Oracle instance informs the Watchdog daemon about the failure.
2. The Watchdog daemon requests the Watchdog timer to reset the failed node.
3. The Watchdog timer resets the node.
4. The Cluster Managers on the surviving nodes inform their local Oracle instances that the failed node is removed from the cluster.
5. Oracle instances in the surviving nodes start the Oracle9i Real Application Clusters reconfiguration procedure.

The nodes must reset if an Oracle instance fails. This ensures that:
· No physical I/O requests to the shared disks from the failed node occur after the Oracle instance fails.
· Surviving nodes can start the cluster reconfiguration procedure without corrupting the data on the shared disk.

In 9i Cluster Reconfiguring is supposed to be fast remastering resources only if necessary and processes on Node A will be able to resume active work during reconfiguration as their locks and resources need not be moved.

However, this was not the behavior we saw when one node totally crashed in our case – while RAC is great it helps you load balance your requests – does it really help in Disaster Recovery ?

Informatica Step by Step to create a Simple Workflow Run successfully:

2007-01-30T11:38:00.000-08:00

As a follow-up to my previous post
This post will cover a)How to create Repository User accounts and managing security in Informatica
b)Create a mapping , session, workflow and successfully execute a workflow

How to create Repository User accounts and managing security in Informatica

1) Login to Repository Server Admin console.Connect to the Repository Server

Right click and create new Repository
4)give the following :
repository name
db connect string
db username : pcenter1
db password : pcenter1

license key information

when you click Apply the Repository content will get created.

5)Once the Repository is created loginto Repsoitory Manager
You can loginto the Repository either as
a) The Repository username/ password provided in the above step
b) Or Administrator
Go the Security > Manage Users and Privileges

By default 2 grps are created “Administrator” and “Public”
2 users are created “Administrator” and “Repository User”
Privileges tab lists all the privileges that are available. This security window can be used to manage Security and privileges – refer help guide for further information on security and privileges.

Create a mapping , session, workflow and successfully execute a workflow

I will use the HR schema to demonstrate how you can create a mapping, session and workflow. The HR schema has the following tables

COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
COUNTRY_REGION is table that has country_name and region_name.

To populate the country_region table : is a join between the country table and the region table
To create a mapping:

Open Repository Manager – Connect to the Repository and create new folder within the Repository using Repository Manager.
Connect to Designer

Open the folder up and you should see Sources, Targets , Cubes , Dimensions etc.
From the Sources menu import from source Database objects you need: in this case you will import COUNTRIES and REGION
Open Warehouse Designer and Import TargetsImport COUNTRY_REGION from Target Menu.
Sources and Targets Menu should be as above

Open Mapping Designer.
Drop in the sources to the Mapping Designer, Drop in the target as well to Mapping Designer.Include a Join Transform to join appropriately the COUNTRY and REGION table.
Name the mapping as COUNTRY_REGION_MAPPING. While saving the mapping make sure parsing completed with no errors. Errors will be reported on the Output window of the Designer.
Once you have saved the Mapping you can now open up your Workflow Manager to create a session and a workflow.
Tasks - Create - creates a new Session or Task
select the COUNTRY_REGION mapping and save the Repository.
Click on Connections/Relational to create 2 new connections for your Source and Target databases.
once the connections are created Click on the Task and you should see the following properties window open up
click on the Mappings Tab and verify the connections are set appropriately.
When you are ready to create the Workflow – Open Workflow Designer and drag and drop the mapping.
Name the Workflow as COUNTRY_REGION_WORKFLOW
Save Repository and in the Output window verify that the workflow is valid.

Before you start running the workflow make sure to register the Power Center Server

Open Workflow Manager – Server – Server Registrationgive all the Power Center Server Registration Properties and define your PMRootDir

Click OK and , Right Click Server and assign the workflow you want to run using the Server. Once you have assigned the workflow to the server you can start the workflow – right click the workflow and click start

workflow monitor should start indicating the status of the run
Right click the workflow and task and you should be able to view the workflow log and session log. From workflow Manager workflows can also be scheduled.
If you run into any issues running a workflow – feel free to post comments. The next 2 posts will cover a) versioning b)debugging using informatica.

Deployment Options with XML Publisher

2007-01-29T13:18:00.000-08:00

This post will discuss the different ways XML Publisher can be used along with Microsoft Word Template builder to generate Reports.

XML Publisher (also called BI Publisher) has the following Deployment options

1) Oracle Applications (will not be discussed in this post)
2) XML Publisher Desktop Edition
Installs XML Publisher Template Builder in Microsoft word that helps you build templates for your Reports. The templates can be stored as rtf files. Following are the Source Data Options using Template Builder in word
a)XML File
b)SQL Query , needs connection information to source database
c)XML Schema
d)XML generated by Siebel Analytics Answers (I have not been able to get this to work , it may be something that will be available in the next releases and more easily integrated in the next few releases of Siebel Analytics)
3) XML Publisher Enterprise Edition
a)provides a web based console that can be used to publish multiple reports
b)XML Publisher enables you to define your reports and separate the data from the layout of the reports .
c)XML Publisher can run on any J2EE compliant Application Server

XML Publisher Desktop Edition:

If you have installed the Desktop Edition

Template Builder Options will be available from MS Word menu.
Template Builder – Data – Load XML Data (XML File) , XML Schema , Report wizard (lets you give database connect information and the sql to extract the data)
Below will list a quick example on how the Report Wizard can be used (connecting to the hr schema to get a list of Departments)

give the database connect information and sql query for the data that needs to be retrieved.We will choose the Default Template Layout in this example.
preview the Report and then save the RTF file (in this example we save the RTF file as hr_departments.rtfXML Publisher Enterprise Edition

As mentioned XML Publisher enterprise edition can run on any J2EE compliant Application Server.

The Admin and Reports directories are available under
Install_dir/xmlpserver

The following files have the port numbers used by the application.
HTTP Port 15101 install_dir/default-web-site.xml
RMI Port 15111 install_dir/rmi.xml
JMS Port 15121 install_dir/jms.xml

Default URL to access XML Publisher Application http://host:15101/xmlpserver (default username/pwd admin/admin)

create new folder and new Report in the corresponding folder.
Edit the Report to define the following properties:
i)datasource for the Report (new datasources can be created in the Admin window)
ii)Data Model: Define the sql query

iii)New List of Values: If the Report uses LOV’s

iv)Parameters: if any parameters are needed for the Report

v)Layouts: create a new template called hr_departments
upload hr_departments.rtf and tie it to the hr_departments template.View the results
you can see that the template is chosen by default and the different output formats available. The above is a very simple illustration of how XML publisher will let your users design their own Reports(and manage changes to design templates of reports) while IT can focus on the data needed for the Reports and other important tasks.

Oracle Text 9i Bug searching XML Data

2007-01-23T14:57:00.000-08:00

My last post I discussed that there was a 9i (9.2.0.6) bug using section_group_type auto section group . This group type automatically creates a zone section for each start-tag/end-tag pair in a XML document. The section names derived from XML tags are case sensitive as in XML.
Searches with auto_section_group work in 9.2.0.6 but not for attributes within a tag. For example
Book title="A" author="B"
attributes title and author cannot be searched using auto_section_group section type in 9.2.0.6. The bug has been fixed in 9.2.0.8

9i bug using Oracle Text to search XML data

2007-01-11T10:54:00.000-08:00

Using Oracle Text to Search XML Data: XML Data inserted in DATA column in TEST table

Create table test(id integer,DATA CLOB)

Department name="CS"
Employee
Vidya Bala
/Employee
/Department

Step1:
--------
Create an auto section group

begin
ctx_ddl.create_section_group('myautosectiongroup', 'AUTO_SECTION_GROUP');
end;

Step2:
-------
create index test_index on test(DATA)
indextype is ctxsys.context
parameters ('SECTION GROUP myautosectiongroup');

Step3:
---------
SELECT DATA FROM TEST
WHERE CONTAINS(DATA, 'Vidya WITHIN Employee') > 0;
1 Row Returned

SELECT DATA FROM TEST
WHERE CONTAINS(DATA, 'CS WITHIN Department@name') > 0;

0Rows (10g returns 1 row – 9i returns no row – Support is working on getting bug fix for the issue)

OracleXml putxml limitations

2007-01-09T13:07:00.000-08:00

OracleXml putxml limitations:

Example1:xml file(test.xml):

ROWSET
ROW num="2"
ID 15 /ID
/ROW
/ROWSET

(using the java API for XDK) the below command
java OracleXML putXML -user "vidya/vidya" -ignorecase –filename "test.xml" "emp"

will load one row into table emp.

Example2:Xml file with namespaces (test1.xml):

ns:EMP
ns:ITEM
ns:ID 2 /ns:ID
/ns:ITEM
/ns:EMP

(using the java API for XDK) the below command
java OracleXML putXML -user "vidya/vidya" -ignorecase –filename "test1.xml" "emp"

if “OracleXML” cannot identifiy the ROWTAG the above command can be modified as below

java OracleXML putXML -user "vidya/vidya" -ignorecase –rowtag “ITEM” –filename "test1.xml" "emp"

note the above will error as OracleXML doesn’t seem to be working on
a file with namespaces. The only way to probably get around this is by applying a stylesheet.

Siebel Analytics Answers and Dashboards (a quick getting started guide based on HR schema)

2007-01-04T09:31:00.000-08:00

Siebel Analytics Answers and Dashboards (a quick a quick getting started guide based on HR schema)

The last post on Sibel Analytics discussed how we build a physical , Business and presentation layer using Siebel Analytics Administration.
This post will focus on using Answers and Dashboards.
Make sure Siebel Analytics Web is up.
For the purpose of this post , I have demonstrated on how you can build Reports using “Answers” against the “hr” schema.

Physical Layer - Make sure to define the object relationships appropriately in the physical layer
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOB_HISTORY
JOBS
LOCATIONS
REGIONS

Busines Layer - Make sure to define the object relationships appropriately in the Business layer
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOB_HISTORY
JOBS
LOCATIONS
REGIONS

Dimensions:
Department Dimension (3 level Dimension)
-Department
-Region
-Country
Job Dimension
-Job Detail

Presentation Layer defined as follows:
Employee
First Name
Last Name
Email
Phone Number
Hire Date
Department
Department Name
City
Region Name
Country Name
Job
Job Title

Once the Presentation Layer is defined you are ready to move on to Answers to build your Siebel Analytics Reports

If you have all Siebel Client Tools on your Desktop ; Siebel Analytics web will take you to the Analytics web page

The default installation
Default username : AdministratorDefault password: no password
once you have logged in you will see the following Tabs
Dashboards,Answers, Advanced Reports,Marketing,Delivers,Disconnected,Admin,My-Account

Step1:
Before proceeding to answers to create Reports let us first create a Shared Folder named “hr” where we can save our Reports. Click on Answers and on the left hand side you should see My Folder > Shared Folder > Manage Catalog

click on Manage catalog > Shared Folder and you should be able to create a new folder
make sure to Refresh Display to verify that the changes have taken effect

Step2:
Now you are ready to create Reports using Answers
Click on Answers > Answers is basically your web interface to building Reports
on the right handside you should see Subject areas
click on HRDetails
you will now see on the left hand side bar all the Data Items that were defined in the Presentation Layer defined available in Answers
Employee
First Name
Last Name
Email
Phone Number
Hire Date
Department
Department Name
City
Region Name
Country Name
Job
Job Title

Example1 – Report1
To build a Report
First Name,LastName,Department Name,Country Name, Region Name, City
Click on the attributes so that they appear under columns:

click on Results and you should see the Results
save your Report to the “hr” shared folder.

The above Report was HR Employee Demographic Details (List of employees by name,department,city,county)

Example2 – Report 2
Pie Chart to give an overview of number of employees by DepartmentClick on create new request

Edit Column formula to display first name and last name as a single column called “Employee Name”

The next step would be to get a count of employees by DepartmentCriteria Tab add Department name and modify “Employee Name” column formula to “count(Employee."First Name"' ' Employee."Last Name")”
The Results would be as above. Now to covert this to a pie chart (make sure to start Siebel Analytics Java Host)
Click on the Pie Chart
Save the Request as “Employees by Department”

Example 3:We now have 2 Reports

“Employees by Department”
“HR Employee Demographic Details”

lets now add the above 2 Reports to a Dashboard

Click on the Admin Tab

Click on Manage Intelligence Dashboards.

Create Dashboard. Name the dashboard as “HR Details”
you should see the “HR Details” dashboard appear near the “My Dashboard”
Click to Add content and you should be able to add the 2 Reports we created in the “hr” shared folder.
The output would be as below

The layout can be changed by editing properties of sections in the Dashboard

Page Option > Edit Dashboard
Click on the Properties Tab of sections if you want to edit the layout of sections.In the below view we have arranged the Reports Horizontally.
Example 4: Drill down from the Pie Chart to the Report Table in the HR Details Dashboard.

a)To Navigate from Dashboard to Answers easily include the modify link in the Dashboard.Page Options > Edit Dashboard
for each section select Properties > Report Links > Modify. Save and go back to Dashboard now you should see the modify links.
b) To set the navigate link on the Pie Chart.Modify the pie chart > Results > edit view of the pie chart

select Additional Charting Options
Interaction Tab > select Navigate > and give the Navigation Page > “HR Details” Dashboard Page 1
c) Add an is prompted filter to the Table Report

from the dashboard click on Modify link of the Table. This will take you to criteria tab on Answers.Add a filter and set Department Name to “is prompted” and save the filter
d) create a Dashboard Prompt click on Answers
you should see an icon on the Left Menu to create a new Dashboard Prompt
scope=Dashboardselect the column name on which you want to set the prompt to
save the Dashboard Prompt.
Now go to your Dashboard > Edit Page Options > Drop the Dashboard Prompt in the Section you would like to appear in and you should now see the Prompt on your Dashboard

you can drill down by Department Name from the Dashboard Prompt drop down.
e) column sorting on Employee Demographic details
assume you also wanted the ability to sort by columns on the Table Report.Click on the Modify link on the Table Report > go to the Results Tab on Answers> Click on the edit view of the Table and then edit Table properties

check “ enable column sorting on Dashboard”

At this point you have a fully functional Dashboard with Drill down ability both from the Dashboard prompt and from the pie chart to the Table.

The next posts will discuss:
1)XML Publisher and Answers/Dashboards

2)Admin Options with Answers(managing security, ibots etc)

Informatica Eval Install

2007-01-03T08:48:00.000-08:00

I checked out statcounter on my blog to see what the Response was after a long time.While I can see that my blog hitcount is increasing slowly– the most frequently searched key word that led to my blog was “Informatica eval install”. In one of my previous posts I mentioned that I will follow-up on Install Instruction for an Informatica eval install. So here it goes – finally
Step1
Download Informatica eval install software and license keys.
This was not an easy search for me but finally managed to find where the third party software is.Below are the part numbers you need to downloadLogon to edelivery.oracle.com

Download B27745-01 Part 1 of 4 Parts 1 through 4 – Siebel Business Applications. Extract the zip files and you should find the eval software for Informatica. Informatica eval license keys can be found in B27757-01 and B27756-01 documentation.
Once you have downloaded and extracted the zip files, go through setup.exe , make sure to install the Server Components and Client Tools

As discussed in my previous post , Informatica has 4 components to it : Client Tools; Repository Server; Informatica Server and Repository.

At the end of the Install you will have to configure the Repository Server and the Informatica Server.

Configure Repository Server Below:
The following Information needs to be provided:
Server Port Number : 5001 (default can be chosen)
Admin password : enter an Admin Password for the Repos Server
Minimum Port Number
The minimum port number the Repository Server can assign to the Repository Agent process. Default is 5002.
Maximum Port Number
The maximum port number the Repository Server can assign to the Repository Agent process. Default is 65535.
Configuration Directory
The name of the directory in which the Repository Server stores repository configuration information files. You can specify either a relative path or an absolute path. Default is Config.
Backup Directory
The name of the directory in which the Repository Server stores repository backup files. You can specify either a relative path or an absolute path. Default is Backup.
Plugin Directory
The name of the directory in which the Repository Server stores repository plugin files. You can specify either a relative path or an absolute path. Default is Plugin.
Severity Level
The level of error messages written to the Repository Server log. Specify one of the following:
Error. Writes ERROR code messages to the log.
Warning. Writes WARNING and ERROR code messages to the log.
Information. Writes INFO, WARNING, and ERROR code messages to the log.
Tracing. Writes TRACE, INFO, WARNING, and ERROR code messages to the log.
Debug. Writes DEBUG, TRACE, INFO, WARNING, and ERROR code messages to the log.
Output to Event Log
Enable this option if you want to write Repository Server messages to the Windows Event Log. This option is enabled by default.
Output to File
Enable this option if you want to write Repository Server log messages to a file. When you enable this option, enter a file name for the Repository Server log. This option is disabled by default. The default Repository Server log file name is pmrepserver.log. The Repository Server stores the Repository Server log file in the Repository Server installation directory.

Configure Informatica Server (PowereCenter Server) as a Service:
Information that needs to be provided is as below:
Server Tab:
Server Name: The name of the PowerCenter Server to register with the repository. This name must be unique to the repository. This name must also match the name you specify when you use the Workflow Manager to register the PowerCenter Server.
TCP/IP Host Address: The TCP/IP host address as an IP number (such as 123.456.789.1), or a local host name (such as RECDB), or a fully qualified name (such as RECDB.INVOICE.COM). If you leave this field blank, the PowerCenter Server uses the default local host address.
Max No. of Concurrent Sessions: The maximum number of sessions the PowerCenter Server runs at a time. Increase this value only if you have sufficient shared memory. Default is 10.
Shared Memory: The amount of shared memory available for use by the PowerCenter Server Load Manager process. For every 10 sessions in Max Sessions, you need at least 2,000,000 bytes reserved in Load Manager Shared Memory. Default is 2,000,000 bytes.
Error Severity Level for Log Files: The level of error messages written to the PowerCenter Server log. Specify one of the following message levels:
Error. Writes ERROR code messages to the log.
Warning. Writes WARNING and ERROR code messages to the log.
Information. Writes INFO, WARNING, and ERROR code messages to the log.
Tracing. Writes TRACE, INFO, WARNING, and ERROR code messages to the log. Debug. Writes DEBUG, TRACE, INFO, WARNING, and ERROR code messages to the log.
Fail Session if Maximum Number of Concurrent Sessions Reached: Enable this option if you want the PowerCenter Server to fail the session if the number of sessions already running is equal to the value configured for Maximum Number of Concurrent Sessions. If you disable this option, the PowerCenter Server places the session in a ready queue until a session slot becomes available. This option is disabled by default.
Allow mapping/session debugging: If selected, you can run the Debugger. This option is enabled by default.
Time Stamp Workflow Log: Enable this option if you want to append a time stamp to messages written to the workflow log. This option is disabled by default.
Output to Event Log: Enable this option if you want to write PowerCenter Server messages to the Windows Event Log. This option is enabled by default.
Output to File: Enable this option if you want to write PowerCenter Server log messages to a file. When you enable this option, enter a file name for the PowerCenter Server log.

Repository Tab:
Repository Name : The name of the repository to connect to. You create a repository in the Repository Server Administration Console.
Repository User: The account used to access the repository. When you first create a repository, the Repository User is the database username. You create other Repository Users in the Repository Manager.
Repository Password : The password for the Repository User. When you first create a repository, the password is the password for the database user.
Repository Server Host Name: The name of the machine hosting the Repository Server.
Repository Server Port Number: The port number the Repository Server uses to communicate with repository client applications.
Repository Server Timeout: The maximum number of seconds that the PowerCenter Server tries to establish a connection to the Repository Server. If the PowerCenter Server is unable to connect to the Repository Server in the time specified, the PowerCenter Server shuts down. Default is 60 seconds.

Licenses Tab:
Enter the license Key’s and then click update

Compatibility and Database Tab:
PMServer 3.X aggregate compatibility: If selected, the PowerCenter Server handles Aggregator transformations as it did in PowerMart 3.x. This overrides both Aggregate treat nulls as zero and Aggregate treat rows as insert.
If you select this option, the PowerCenter Server treats nulls as zeros in aggregate calculations and performs aggregate calculations before flagging records for insert, update, delete, or reject in Update Strategy expressions. If you do not select this option, the PowerCenter Server treats nulls as nulls and performs aggregate calculations based on the Update Strategy transformation.
PMServer 6.X Joiner source order compatibility: If selected, the PowerCenter Server processes master and detail pipelines sequentially as it did in versions prior to 7.0. The PowerCenter Server processes all data from the master pipeline before starting to process the detail pipeline. Also, if you enable this option, you cannot specify the Transaction level transformation scope for Joiner transformations. If you do not select this option, the PowerCenter Server processes the master and detail pipelines concurrently.
Aggregate Treat Nulls as Zero: If selected, the PowerCenter Server treats nulls as zero in Aggregator transformations. If you do not select this option, the PowerCenter Server treats nulls as nulls in aggregate calculations.
Aggregate Treat Rows as Insert : If selected, the PowerCenter Server performs aggregate calculations before flagging records for insert, update, delete, or reject in Update Strategy expressions. If you do not select this option, the PowerCenter Server performs aggregate calculations based on the Update Strategy transformation.
PMServer 4.0 date handling compatibility: If selected, the PowerCenter Server handles dates as in PowerCenter 1.0/PowerMart 4.0. Date handling significantly improved in PowerCenter 1.5 and PowerMart 4.5. If you need to revert to PowerCenter 1.0 or PowerMart 4.0 behavior, you can configure the PowerCenter Server to handle dates as in PowerCenter 1.0 and PowerMart 4.0.
Treat CHAR as CHAR on Read: If you have PowerCenter Connect for PeopleSoft, you can use this option for PeopleSoft sources on Oracle. You cannot, however, use it for PeopleSoft lookup tables on Oracle or PeopleSoft sources on Microsoft SQL Server.
Max LKP/SP DB Connections: Allows you to specify a maximum number of connections to a lookup or stored procedure database when you start a workflow. If the number of connections needed exceeds this value, session threads must share connections. This can result in a performance loss. If you do not specify a value, the PowerCenter Server allows an unlimited number of connections to the lookup or stored procedure database.
If the PowerCenter Server allows an unlimited number of connections, but the database user does not have permission for the number of connections required by the session, the session fails. A default value is not specified.
Max Sybase Connections: Allows you to specify a maximum number of connections to a Sybase database when you start a session. If the number of connections required by the session is greater than this value, the session fails. Default is 100.
Max MSSQL Connections: Allows you to specify a maximum number of connections to a Microsoft SQL Server database when you start a workflow. If the number of connections required by the workflow is greater than this value, the workflow fails. Default is 100.
Number of Deadlock Retries: Allows you to specify the number of times the PowerCenter Server retries a target write on a database deadlock. Default is 10.
Deadlock Sleep Before Retry (seconds): Allows you to specify the number of seconds before the PowerCenter Server retries a target write on database deadlock. Default is 0 and the PowerCenter Server retries the target write immediately.

Configuration Tab
Data Movement Mode: Choose ASCII or Unicode. The default data movement mode is ASCII, which passes 7-bit ASCII character data. To pass 8-bit ASCII and multibyte character data from sources to targets, use Unicode mode.
Validate Data Code Pages: If you enable this option, the PowerCenter Server enforces data code page compatibility. If you disable this option, the PowerCenter Server lifts restrictions for source and target data code page selection, stored procedure and lookup database code page selection, and session sort order selection. This option is only available when the PowerCenter Server runs in Unicode data movement mode. By default, this option is enabled.
Output Session Log In UTF8: If you enable this option, the PowerCenter Server writes to the session log using the UTF-8 character set. If you disable this option, the PowerCenter Server writes to the session log using the PowerCenter Server code page. This option is available when the PowerCenter Server runs in Unicode data movement mode. By default, this option is disabled.
Warn About Duplicate XML Rows: If you enable this option, the PowerCenter Server writes duplicate row warnings and duplicate rows for XML targets to the session log. By default, this option is enabled.
Create Indicator Files for Target Flat File Output: If you enable this option, the PowerCenter Server creates indicator files when you run a session with a flat file target.
Output Metadata for Flat File Target: If you enable this option, the PowerCenter Server writes column headers to flat file targets. It writes the target definition port names to the flat file target in the first line, starting with the # symbol. By default, this option is disabled.
Treat Database Partitioning As Pass Through: If you enable this option, the PowerCenter Server uses pass-through partitioning for non-DB2 targets when the partition type is Database Partitioning. Enable this option if you specify Database Partitioning for a non-DB2 target. Otherwise, the PowerCenter Server fails the session.
Export Session Log Lib Name: If you want the PowerCenter Server to write session log messages to an external library, enter the name of the library file.
Treat Null In Comparison Operators As: Determines how the PowerCenter Server evaluates null values in comparison operations. Enable one of the following options:
a)Null. The PowerCenter Server evaluates null values as null in comparison expressions. If either operand is null, the result is null. This is the default behavior.
b)High. The PowerCenter Server evaluates null values as greater than non-null values in comparison expressions. If both operands are null, the PowerCenter Server evaluates them as equal. When you choose High, comparison expressions never result in null.
c)Low. The PowerCenter Server evaluates null values as less than non-null values in comparison expressions. If both operands are null, the PowerCenter Server treats them as equal. When you choose Low, comparison expressions never result in null.
WriterWaitTimeOut: In target-based commit mode, the amount of time in seconds the writer remains idle before it issues a commit when the following conditions are true:
a)The PowerCenter Server has written data to the target.
b)The PowerCenter Server has not issued a committed.
The PowerCenter Server may commit to the target before or after the configured commit interval. Default is 60 seconds. If you configure the timeout to be 0 or a negative number, the PowerCenter Server defaults to 60 seconds.
Microsoft Exchange Profile: Microsoft Exchange profile used by the Service Start Account to send post-session email. The Service Start Account must be set up as a Domain account to use this feature.
Date Display Format: If specified, the PowerCenter Server validates the date display format and uses it in session log and server log entries. If the date display format is invalid, the PowerCenter Server uses the default date display format. The default date display format is DY MON DD HH 24:MI:SS YYYY. When you specify a date display format, it displays in the test window. An invalid date display format is marked invalid.
Test Formatted Date: Read-only field that displays the current date using the format selected in the Date Display Format field.

JVM Options Tab:
You can configure JVM options if you run Java-based programs with PowerCenter Connect products, such as PowerCenter Connect for JMS or PowerCenter Connect for webMethods.

HTTP Proxy Tab:

Server Name: Name of the HTTP proxy server.
Server Port: Port number of the HTTP proxy server.
Username: Authenticated user name for the HTTP proxy server. This is required if the proxy server requires authentication.
Password: Password for the authenticated user. This is required if the proxy server requires authentication.
Domain: Domain for authentication.

Once you have completed configuring the Repository Server and Power Center Server – Login to the Repository Server Admin Console and create a new Repository

Once the Repository is created (to create the repository a repository schema needs to be created in the database server); the Repository owner information can be used to Login to Designer, Workflow Manager and Workflow Monitor.

The next few posts will cover the following:
a)How to create Repository User accounts and managing security in Informatica
b)Create a mapping , session, workflow and successfully execute a workflow
c)Version Control in Informatica

d)How to debug mappings in Informatica.

10G SQL Access Advisor and SQL Tuning Advisor

2006-12-28T13:33:00.000-08:00

I was able to take advantage of the holidays to complete a 9i to 10g cross platform migration. While I was quite happy with the migration process (especially on how datapump has made this effort much easier), I was a little bit disappointed on what SQL Access and SQL Tuning Advisor had to offer.

There were a bunch of queries that we have been meaning to tune for quite some time , I thought I could create a tuning task with 10G SQL Tuning advisor and see if I could get some valuable recommendations.

The Recommendations I got were far from anything of significance (eg: add an index to a small lkup table).

I couldn’t help but wonder is there is much success/help using Oracle 10g SQL Access/Tuning Advisor in the industry

ASM and NetApp Filer

2006-12-21T13:10:00.000-08:00

Link to ASM and NetApp

I have been spending the last few days looking into what advantages we would have using ASM on a NFS mount as opposed to having the database files directly on NFS. If your on RAC then ASM is mandatory but for non RAC 10g instances and NetApp - ASM is not mandatory.

The biggest benefit I see is volume management features with ASM.

does this mean I can change volume sizes etc actually online ? may be IO balancing across different volumes an added feature. I am walking into a totally new area (ASM on any kind of direct attach storage I can for sure see it being beneficial on NFS I am not so sure?)

anybody with sucess stories?

Cross Platform Migration 9i to 10g

2006-12-21T12:59:00.000-08:00

Migration Procedure Implemented.

Cross Platform Migration 9.2.0.6(Suse SLES8) Standard Edition to 10g Rel 2 (Solaris 10)

This is a quick overview of a migration procedure I have just finished implementing on a test environment– If you see anything else in the procedure that should be added or should be noted, please feel free to post comments – as always there has been a lot of mutual learning and help from my blog readers.

Step1: Server A :
Clone Production Database to Preprod Environment. (Datafile,Redologfile,controlfiles all on Shared File System NFS).
Database Release : 9.2.0.6
Suse Linix version : SLES 8
Database Size : 50.89 G

Step2: Server B:
Install 10g Release 2 on a new SLES 9 server. Note 10g Release 2 is not supported on SLES8.
Make sure shared file systems on ServerA are mounted on Server B.
Copy parameter file from Server A to Server B . Make appropriate path changes to parameter file on Server B.
Database Release : 10g Release 2
Suse Linix version : SLES9

Step3: Upgrade 9i database to 10G
On Server B upgrade 9.2.0.6 database to 10G

sqlplus /nolog
startup upgrade

CREATE TABLESPACE sysaux DATAFILE ' sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

Set the system to spool results to a log file for later verification of success:

SQL> SPOOL upgrade.log

Run catupgrd.sql:

SQL> @catupgrd.sql

Run utlu102s.sql to display the results of the upgrade:

SQL> @utlu102s.sql

Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Shut down and restart the instance to reinitialize the system parameters for normal operation.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

Verify that all expected packages and classes are valid:

SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';

Exit SQL*Plus.

Total Time to Upgrade Database : 38 minutes

Step 4 – Upgrade 10g Database (using expdp)

Now that we have the database migrated to 10gRel2 on Server B(SLES9), we can export the database using 10g datapump. We will export only the Application Related Tablespaces. Tablespaces excluded are as below

PERFSTAT
SYSAUX
SYSTEM
UNDOTBS1
TEMP
DRSYS
INDX --- no application related objects in this tablespace
TOOLS
USERS
XDB
UNDOTBS2

Before running the export – OWM and OLAP options need to be de-installed if not being used to avoid export errors

If the Oracle Workspace Manager feature is not used in this database: de-install the Workspace Manager:
SQL> CONNECT / AS SYSDBA
SQL> @$ORACLE_HOME/rdbms/admin/owmuinst.plb

clean up AW procedural objects:SQL> conn / as SYSDBASQL> delete from sys.exppkgact$ where package = 'DBMS_AW_EXP';

Afterwards, run the export.

CREATE OR REPLACE DIRECTORY pump_dir AS 'xxxxxxxxxxxxxxxx';

Export only application related tablespaces

$ORACLE_HOME/bin/expdp system/manager tablespaces=$t1,t2 $ directory=pump_dir dumpfile=pump.dmp logfile=pump.log

Full database export of 50+G database took about 80 minutes to export

Step5 – Prepare Target environment – Server C with 10g Release 2

Install 10g Release 2 on Solaris 10 Servers (SERVERC).

Installing Oracle Database 10g Products from the Companion CD
The Oracle Database 10g Companion CD contains additional products that you can install. Whether you need to install these products depends on which Oracle Database products or features you plan to use. If you plan to use the following products or features, then you must complete the Oracle Database 10g Products installation from the Companion CD:
· JPublisher
· Oracle JVM
· Oracle interMedia
· Oracle JDBC development drivers
· Oracle SQLJ
· Oracle Database Examples
· Oracle Text supplied knowledge bases
· Oracle Ultra Search
· Oracle HTML DB
· Oracle Workflow server and middle-tier components

On Server C use DBCA to create database creation scripts. Select the config parameters you need for your database as you go through the DCA wizard.

The scripts will create a standard database with no application related objects yet. Run create scripts to create database.

Tablespaces created (this is assuming none of the additional components were installed)
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS

Make sure the Listener is up.
http://ServerC:1158/em
is the em console for the database

Note the below before proceeding with the em console
Oracle Enterprise Manager 10g Database Control is designed for managing a single database, which can be either a single instance or a cluster database. The following premium functionality contained within this release of Enterprise Manager 10g Database Control is available only with an Oracle license:
t(void 0,'12')
Database Diagnostics Pack
Automatic Workload Repository
ADDM (Automated Database Diagnostic Monitor)
Performance Monitoring (Database and Host)
Event Notifications: Notification Methods, Rules and Schedules
Event history/metric history (Database and Host)
Blackouts
Dynamic metric baselines
Memory performance monitoring
t(void 0,'12')
Database Tuning Pack
SQL Access Advisor
SQL Tuning Advisor
SQL Tuning Sets
Reorganize Objects
t(void 0,'12')
Configuration Management Pack
Database and Host Configuration
Deployments
Patch Database and View Patch Cache
Patch staging
Clone Database
Clone Oracle Home
Search configuration
Compare configuration
Policies

Step6 – Prepare Target environment – Server C with Application related Objects

IMPDP will be used to import Application Related objects into this database.
Before running IMPDP the target database will need to be prepared with the Application Tablespaces and Application Schema’s. This is also a great opportunity to reorg objects if you need to. Scripts to create Application Tablespaces and Schemas are prepared.
This is the most important step in preparing the target environment.

Once the Target environment is prepared – import the dumpfile using the following command.
$ORACLE_HOME/bin/impdp system/manager full=y directory=pump_dir1 dumpfile=pump.dmp logfile=pump_import.log

Before opening the database for public connections
1)Recompile for invalid objects (run utrp.sql)
2)Gather statistics for entire database

There will be a regression tests run at the end of all this to test Application Functionality, long datatypes etc.

Future Direction - 10g Forms/Reports Developer vs JDeveloper

2006-12-20T13:03:00.000-08:00

We are on this new effort to move some legacy rbase programs to Oracle. we are required to evaluate - a)would it be better to go with Oracle 10g forms/reports or with Jdeveloper. The data is going to reside on Oracle database servers. The concern about going with Oracle Forms/Reports was a) will Oracle support it in the future? my instant reaction was ofcourse Oracle will......we have bigger problems if Forms/Reports go away considering that the EBS Suite uses Forms/Reports technology as well.

so the final question while migrating new Apps would it be better to use Oracle Forms/Reports or Jdeveloper????? my 2 cents
1)If the application is database centric without too much business logic involved and if your team has a PL/SQL back ground as opposed to a Java backgrouund then 10g Forms/Reports may be a better bet.
2)If the team is pretty much a J2ee development team then JDeveloper may be the route to go.

I am not too worried about Oracle's strategy to support Forms/Reports (I think they will). The above is just my 2 cents any input from my blog readers will be greatly appreciated.

online reorg options if you are on Standard Edition

2006-12-13T12:10:00.000-08:00

A couple of the online reorg options may not be available if your on Std Edition. Quest Central (Space Management) Live Reorg options should help you get past this problem.

Quest Central Space Management have the following Reorg Options
a)Standard Reorg(offline mode no DML activity allowed on Reorg table)
b)Live Reorg (online mode) - has 2 option
1)TLOCK switch (can be used if your on Std edition - a copy table is created and a trigger based approach to get the copy table in sync, when you are ready the TLOCK switch can be performed - during the switch you will need to have a downtime but will be nothing significant)2)Online Switch(This option needs Oracle Partitioning enabled - pretty much you will need enterprise edition for this option)

Siebel Analytics Install and Siebel Analytics Administration:

2006-12-12T14:30:00.000-08:00

In this post I will review Siebel Analytics Administration Tool. But before we begin you will need to Install the following.
Logon to http://edelivery.oracle.com/Ok finding the Siebel Analytics Download can be tricky on edelivery

Product – pick Oracle Business Intelligence and the appropriate Platform

pick the Business Intelligence media pack
If you plan to evaluate on Windows download
B30721-01 Part 1 of 2 and B30721-01 Part 2 of 2
The above 2 parts will your Sibel Analytics Server, Siebel Analytics Web, Siebel Analytics Scheduler, Siebel Analytics Java Host, Siebel Analytics Cluster

Now if your looking to download third party products like Informatica , Actuate etc you will have to download B27745-01 Parts 1 through 4. This post will focus on the Siebel Analytics Server.

Once you have downloaded B30721-01 Part 1 of 2 and B30721-01 Part 2 of 2 , extract the zip files , find the installer and walk through the install. The install is pretty intuitive. If you run into any issues with the install (post comments on the blog and I can help you out with it).

Once you have completed the install , if you are on windows you will see 5 services created Sibel Analytics Server, Siebel Analytics Web, Siebel Analytics Scheduler, Siebel Analytics Java Host, Siebel Analytics Cluster – these are your key components for the Siebel Analytics Server.
Make sure your Siebel Analytics Server and Siebel Analytics Web service is started.

A couple of Siebel Analytics shortcuts will be installed on your desktop.

The first step in using Siebel Analytics to generate Reports is to define the metadata layer. The metadata layer is defined using the Siebel Analytics Administration tool.Click on the Siebel Analytics Administration Tool.

you can see the Administration Tool has 3 layers. The physical layer, Business Model and Mapping Layer and Presentation Layer.

Step1: Physical Layer:

Define your datasources in this layer. Create an ODBC datasource for the source database. For the purpose of this test we will be connecting to the perfstat schema on a DEV1 Instance.
In the physical layer right click and create your database connection

once you create your database folder import your database objects

select the schema you want to import and click import (choose FK constraints if you want to import the objects with FK constraints) – once you have imported the schema you should see it in the dev1 folder

Perfstat schema has been chosen just for illustration purposes , ideally you want your source database to be a warehouse or a mart , in the abscence of one and oltp system can also be your source (note if an oltp db is your source it will call for more work on the business mappings layer)– however in this post I will attempt to design this schema for Reporting Purposes.

Assuming that the crux of your reporting is Reporting on sql statement statististics :
SQL_STMTS_STATS will be our fact table in the Business Mappings Layer.
Some Dimesions around it will be
Instance Details
Execution Plan Cost Details
This is like a 2 dimensional star schema.

Now let us see how the following objects exist in our physical layer and model it in our business layer.
The 4 objects we will be looking at is
STATS$DATABASE_INSTANCE
STATS$SNAPSHOT
STATS$SQL_PLAN_USAGE
STATS$SQL_SUMMARY
Select the above 4 objects in your physical layer - right click and view physical diagram of the selected objects

Now create a new business model folder and drag and drop the 4 objects to the business model layer.

Once you have dropped the objects in the Business layer you can define the relationship between the 4 objects in the Business Layer(select the objects , right click and define the relationships in the Business Diagram area) - this is where a traditional normalized oltp schema in the physical layer will lend into a star or snowflake schema. in the Business layer.

Now let us also look into what attributes we actually need for the Presentation layer and what dimensions we need.

The Business layer is where I start modeling and maping objects with the Business Model in mind.For instance if STATS$DATABASE_INSTANCE is a good candidate for a dimension then right click on the object in the Business Model Layer and say create dimension

Once I have modeled my Business Layer to the way I want it to be , I can drag and drop objects in the Presentation Layer.

so we started with the perfstat schema and this is what we cameup with in the Presentations Layer

Instance Details
- Instance Name
- Database Name
Sql Stats Details
- Sql Statement
- Fetches
- Executions
- Loads
- Parse Calls
- Disk Reads
Sql Plan Cost
- Hash Value
- Cost

All the underlying relationships and hierarchy is masked at this presentation layer. All you see at this presentation layer is key Presentation Elements that a Business user really care about.

The next post will cover how the presentation layer can be used to build Reports using Siebel Analytics Web Console (typically the power users)

For questions – please feel to free to post them in the comments sections.
Make sure to save your work on the Siebel Analytics Administration Tool –a Repository consistency check is done at the time of saving your work – also check in changes will do a Repository Consistency Check.

expdp of 9i database from 10g Oracle_Home

2006-12-12T09:43:00.000-08:00

for some reason I thought I would be able to use expdp to export a 9i database using 10g expdp - obviously not supported as mentioned below.

Compatibility Matrix for Export & Import Between Different Oracle Versions
Doc ID:
Note:132904.1

Leaves me with the option of either using traditional "exp" can be very slow on a 100G+ database not sure if Cross Plaform Migration is an option with Standard Edition.

Siebel Analytics

2006-12-11T08:38:00.000-08:00

BI Suite Enterprise Edition Getting Started:

I have been spending the last few days setting up BI Reports using BI Suite Enterprise Edition as a proof of concept for Business users to evaluate the Product.

I have liked the Product so far,
(i) very user friendly
(ii)once the metadata layer is defined Business users are masked from underlying tables and relationships they don’t need to know about
(iii)A lot of sleek display features in the Product.
(iv)Skill Level not extremely difficult. If you have worked in the Database and BI world it should be fairly easy to learn how to use the Product.
(v) I will review a step by step evaluation of the Product once I have it installed in my system.

Details on the Product:

Key feature of the Product (mainly Siebel Analytics/Answers):

(i)Has a BI Server ; BI Web Console; BI Admin Client Tool
(ii)BI Server is not integrated with the 10g Application Server (it runs separately and is not a container in your Application Server like the way it was with Discoverer)
(iii)I believe there are claims that with 11G App Server the OEM Console can manage the BI Server as well.
(iv)When a request is sent to the webserver a Logical query is sent to the BI Server – The BI Server then checks if the data is in the Cache – if not in the cache a physical SQL is sent to the database.
(v)All metadata information is stored in flat files as opposed to any repository – so should be easy to move across environments – the metadata flat files also support multiuser capability.
(vi)Security Services available with your BI Server (VPD security)
(vii)BI Admin Tools – has pretty much 3 layers (a) the Physical Layer where datasources are defined; ODBC is used to define the datasources (b) the Business/ Mappings layer where you build your Business mappings and (c) Presentation Layer where define how your data needs to be presented.
(viii)The Webconsole has (a) Answers – this is what is used by your Business users to create Reports using data items available in your presentation layer. (b)Dashboard – is where Reports built using Answers can be published on your Dashboard (c)Admin – to manage user accounts , analytics catalog, dash board permissions etc (d) Delivers – can schedule jobs for web cache refreshes. Also Oracle XML Publisher is a part of the BI EE Suite

I am excited about having all our Reports moved to Siebel Analytics – I will have an end to end sneak preview of the Product posted on my blog soon.

It has not been an easy ride for us on Suse (SLES 8)

2006-12-07T09:08:00.000-08:00

we have been on Oracle 9i SLES 8 for about 4 years now. And somehow or the other I always run into some issues on Suse - at times it makes me feel that we would have been better off with any other Linux flavor or Unix.

the issue this morning was:
we were trying to use the XML parser in the database
java OracleXML getXML -user "ccccc/ccccc" "select * from emp"
Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc9 in java.library.path

CLASSPATH, LD_LIBRARY_PATH have all been set correctly.
I turned around ; added the jars on my Solaris box and ran the command ...........it worked.

so started looking at Suse Mailing Lists - and ofcourse a lot of people have been having problems with jdbc on suse- is there a bug fix? (well I dont know at this point - I have a tar opened).

Things that had me surprised with 10G Standard Edition

2006-12-04T10:27:00.000-08:00

Well we know that Standard edition is a marketing joke - but it is something we cannot avoid considering when we can save about 30K per cpu.

The following features not being supported by standard edition had me surprised(there are a lot more but this caught my attention)

1)Reorgs: Online index build, online table reorg/redef, online index coalesce. Global index maintenance during DDL
(some of the above we may overcome with third party LiveReorg Products)
2)Recovery: block level media recovery, Tablespace point in time recovery.
3)With 10g Std edition incremental backup and recovery is supported but not with 9i Std Edition - think of it which means you always have to do a full bkups.4)Indexes : Bitmap indexes are not supported while Function based indexes are supported in 10G Std edition.

recover database until cancel and online redo's

2006-11-27T04:29:00.000-08:00

on a noarchivelog mode database, recover database until cancel will still prompt for archive logs.
This had me puzzled initially until I looked at the sequence number and realized, the sequence number corresponds to the online redolog (although the recovery command prompted for an archive log). When prompted if the appropriate online redolog is entered the Incomplete recovery will succeed.

10g Release 3 Forms and Report Services?

2006-11-22T12:08:00.000-08:00

while helping a colleague of mine install Forms and Reports Services , I realized that 10g Release 3 although available for download , the Forms and Reports Services component is not available for 10g Release 3 yet (only if I missed something). I was wondering if there was any talk on an ETA for Forms and Reports Services on 10g release 3.

Informatica (PowerCenter) Key Concepts (PartI)

2006-11-22T10:57:00.000-08:00

Informatica (PowerCenter) Key Concepts PartI:

This series will review the fundamental of the PowerCenter(7.1.2) Suite.
The key components of Informatica are PowerCenter Client Tools, Repository Server, Power Center Server and the Repository. The Repository Server and the PowerCenter Server constitute the ETL Layer which does the ETL process.

Below is how the client to server communication flow looks like:
The PowerCenter client sends a request to Repository Server.
The Repository Server verifies the connectivity information of the target database
The Repository Server starts a Repository agent Process
The Repository Server uses a Repository agent process to communicate with the Repository.
The PowerCenter Server process communicates with the repository to run scheduled workflows. When a workflow is run manually the Power Center Server uses the instructions configured in the mapping (stored in the Repository) to read , transform and write data.
(details on what a workflow and mapping is discussed below).

Below are the key PowerCenter Client Tools:

a)Repository Manager: To create and manage repository, create and manage users
b)Repository server admin console: To manage the Repository Server
c)Designer Tools:
(i)Source Analyzer: to import source definitions
(ii)Warehouse designer: to import target definitions
(iii)Transformation developer: a repository object that generates/modifies and parses data. This object can be reused .
(iv)Mapping: a mapping is nothing but a set of source and target definitions linked by transformation objects. A mapping is what represents the data flow from source to target.
Mapping Designer is used to create Mappings.
(v)Mapplet designer: A mapplet is a set of objects that you use to build reusable transformation logic.
(vi)Workflow: a workflow is a set of instructions that tells the Power Center Server how to execute a task. Workflow Manager can be used to create and run workflows. Workflow Monitor can used to monitor the running of workflows.(If you are new to the ETL world the above concepts may be overwhelming in the beginning, but as we go through other chapters you will be able to see how the concepts relate to the ETL process)

Informatica for NewBie Sereis

2006-11-20T15:45:00.000-08:00

with all the rush before the holidays for getting things done - I really wanted to start this series before the Holidays. As mentioned in my earlier post , for somebody with ETL experience it gets difficult to evaluate different tools because not many are available out there to download and evaluate. On the same note except for manuals there is not much information on the web for ETL tools in general. I am hoping this series will help a few Niewbie's to Informatica(to jumpstart their work with Informatica) and ETL concepts explained with Informatica (Thanks to Mark Rittman for pointing out a possible site for an evaluation copy - Mark, finding the eval copy and the eval license codes was a treasure hunt on edelivery).

The series will cover the following Topics
Informatica Fundamentals (PartI and Part II)
Informatica Eval Installation
Informatica Admin Tasks
Informatica Designer Tasks
Informatica getting familiar with Workflow Manager and Workflow Monitor
Other Things to remember about Informatica
things to remember in the ETL world(basic concepts)

If you are following this specific series - please drop in comments and let me know if the series helps you get familiar with the fundamentals of Informatica.

RMAN full db backup vs incremental level 0 backup

2006-11-14T12:39:00.000-08:00

All these years I have believed that an incremental level 0 backup will backup all blocks in a datafile except the ones Oracle has never written to and is pretty much the same as a full database backup except that the level 0 incremental is part of an incremental strategy.

Having said the above, I would expect that after a NOLOGGING bulk load on a table I could do a level 0 incremental backup and when the level 0 backup set is restored I should be able to access the data that was inserted with NOLOGGING OPTION.

We ran into a situation today ..........

-some staging tables were loaded with NOLOGGING OPTION
-a level 0 incremental load was done after that
-level 0 incremental backup set was restored to a test environment
- select * from (table that was loaded with bulk load option)
returns ORA-26040: Data block was loaded using the NOLOGGING option

If a level0 incremental actually copies all the datablocks why would it care about something that was loaded with a nologging option before the backup ??(will a full database backup as opposed to a level 0 incremental get us past this problem)???

if you have any inputs will be great to know , in the meantime I will have to knock support doors as well.......

the fine print always gets you…………

2006-11-13T11:58:00.000-08:00

we were almost ready with our 10g RAC install on Solaris 10 and NetApp Filer. Until I read a small print on the Install guide

“For Standard Edition RAC Installations, ASM is the only supported storage option for database files”

while NFS Filesystem(supported NetApp configuration) is supported for the Cluster software it is not supported for database files.

Also if you look at the link below
http://www.sun.com/bigadmin/features/articles/db_in_containers.pdf

while Solaris Containers model seems to be the way to go (helps you better manage your resources for different applications giving an isolated environment for your applications – “RAC is not supported on a capped container model”

more to come on configuring NAS based files for use in an Automatic Storage Management Disk Group

what’s up with ETL Tools……………….

2006-11-10T11:59:00.000-08:00

Over the last few years I have worked on quite a few ETL Projects. Starting with Sagent in 2000 to Informatica and now with Oracle Warehouse Builder.

With Warehouse Builder I can pretty much download it on technet setup a quick demo for users to demonstrate how this can be used as an ETL tool and even let them try it before we can get the entire Sales force involved for purchase etc ( I would like to get them out of the habit of doing major migrations only with PL/SQL code and maintenance becomes a nightmare)
I have not seen a single ETL tool apart from warehouse builder that I can download and setup a quick demo for, for all other vendor products I will have to contact the vendor setup a sales call , demo etc etc. We have the skills in-house and wish we could get our hands on these products, download it and evaluate them ourselves …………….

get familiar with OPMN……..

2006-11-10T11:38:00.000-08:00

Especially in the 10g Application Server world I see a lot of PROD and DEV DBA’s relying only on the EM console to manage their Applications.
I personally prefer the command line interface. I think it is more robust and can come very handy.
Oracle® Process Manager and Notification Server(OPMN) – below is a quick overview of what you can do with opmn
Opmnctl lets you start/stop/reload/status all your application server components
cd $ORACLE_HOME/opmn/bin
a)opmnctl start: Start opmn daemon without starting opmn managed processes for local ias instance.
b)opmnctl startall:Start both the daemon and managed server processes
c)opmnctl stopall
d)opmnctl startproc ias-component=WebCache process-type=WebCache (start and stop individual Application Server Components)
e)opmnctl stopproc ias-component=WebCache process-type=WebCache
f) opmnctl validate opmn.xml – validates the xml syntax for opmn.xml
g)opmnctl help: Print a brief usage description.
h)opmnctl shutdown: Quickly shutdown opmn daemon and opmn managed processes for local ias instance. This request is similar to a stopall command but waits less time before initiating a forceful termination of the managed processes. After stopping all managed
processes, the opmn daemon will shutdown itself.

Opmn.xml has IAS component specific configuration information is located in ORACLE_HOME/opmn/conf/opmn.xml
For example if your ias component is a Reports Server
ias-component id="rep server name" status="enabled" id-matching="false” : defines the ias component
process-type id="ReportsServer" module-id="ReportsServices": the type of ias component
process-set id="rep_ogmapp4" restart-on-death="true" numprocs="1": helps you define run time arguments for this component

ORACLE_HOME/opmn/logs/ has the ias component specific logs

With release 10.1.3.1 OC4J grouping is allowed defined in OPM.xml as well basically a group of OC4J instances can be managed together

If you are clustered then OPMN.xml will need to be configured for Dynamic Discovery in opmn.xml

How to Deploy a J2ee Application using OC4J in 10g Application Server:

The belw can be done using the EM console but I prefer the command line dcmctl utility (Distributed Configuration Management (DCM) ) to deploy the j2ee Application.

a)Create OC4J component:
dcmctl createComponent -ct oc4j -co myComp
The above command should create a new OC4J component
ias-component process-type
OC4J myComp
b)Remove OC4J component
dcmctl removeComponent -co myComp
c) dcmctl listComponents
list all the components
c)data-sources.xml
cd $ORACLE_HOME/j2ee/mycomp/config
you should find the application.xml and data-sources.xml config file. Add your data-sources connection to data-sources.xml.

data-source
class="com.evermind.sql.DriverManagerDataSource"
name="htestDS"
location="jdbc/htestCoreDS"
xa-location="htestDS"
ejb-location="jdbc/htestDS"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="htest"
password="htest"
url="jdbc:oracle:thin:@localhost:1521:dev2"
inactivity-timeout="30"

d)Start your OC4J component “mycomp”
you should be able to see your new datasource htestDS listed when you use the EM console to look into datasources for mycomp

e)Deploy your ear file
dcmctl deployApplication -file /var/home/oracle/test/j2ee/build/ htest.ear -a htest -co myComp
once the deployment is complete , the application can be accessed
htpp://hostname:7777/appname
4j -co myComponent

f) to start/stop components and undeploy
dcmctl start -co myComp -d -v
dcmctl stop -co myComp -d -v
dcmctl restart -co myComp -d –v
dcmctl undeployApplication -a hrapp -co myComp
dcmctl removeComponent -co myComp

If you have not used the command line interface to manage your App Server environment – the above should get you started.To start and stop your EM agent use emctl ($ORACLE_HOME/bin)

Dynamic Switching on a single 10g Reports Server instance (Part2)

2006-11-09T15:25:00.000-08:00

If you have configured your Reports Server for Dynamic Switching with the ENVID parameter, the next step would be to set the ENVID for the Report Object before running the Report Object.

This can be down as below:
Set_Report_Object_Property(Rep_Id,REPORT_OTHER,'ENVID="test"');

However, the ENVID is not something that we want hardcoded in our forms and must be read from the Application Specific Forms env file.

For more details on the env file :
Cd $ORACLE_HOME/forms90/server/fromsweb.cfg
And envFile to Application specific configuration
# Remittance (RMS) Testing Section
[test]
workingDirectory=/app/oracle/test
form=TEST.fmx
pageTitle=TEST
envFile=test.env

cd $ORACLE_HOME/forms90/server/
cp default.env test.env
edit test.env to include your environmanet variables
eg REPORTS_ENVID=test
now this variable can be read from your form using the TOOL_ENV.GET_VAR function.

The env file can also include application specific authentication information if required.
At this point you have configured your 10g Reports and Forms Server for Multiple Applications.

cannot get more interesting.......

2006-11-08T12:41:00.000-08:00

so I am on a cross platform migration and upgrade (both at one time).

9i RAC Suse to 10g RAC Solaris.....at this point we are in the planning phase and getting all the details together for the Migration Path.

The upgrade path most likely will be unracify,upgrade and then do a cross platform migration. I understand we need to do a endian conversion as we are moving from Linux to Solaris (Little to Big endian). Looks like the conversion procedure for this is documented and all over the place.
When I just thought I have tied all the information for this migration path together, I realized that I will be going from a 32 bit SLES (Intel) Architecture to a 64 bit architecture on Solaris(Sparc). The endian conversion takes care of byte reordering what about going from a 32bit to a 64 bit .......I am still in the process of figuring if there is anything additional I will need to do for this.....

any thoughts????

it is strange how things change.................

2006-11-06T14:43:00.000-08:00

Sometime in 2003 I had to do a RAC install on Suse Linux. I remember spending lots of time on the web and metalink looking to find information on Suse Linux or Linux and all the default documents were on Unix. Most of time I never found answers to any of my questions (configuration with Suse, RAC and Net App) were tricky ; things like is OCFS supported or not on NetApp? oracm versions etc etc.

In an attempt to find answers I started opening tars with support ; the tech support team were supportive but were not able to give me answers immediately and had to do a lot of research on their part to answer some of my questions - I will not say that the process of finding out all the details was frustrating but was very tedious.

Today tables have turned - and I have to do a 10g RAC install on Unix(Solaris 10) - I get on technet and metalink and find a whole lot of information on Linux :) (I am not sure if it is a result of OOW2K6) and I am searching to find information on Unix.

It is strange or probably not so strange that things change .........I guess the only thing that always remains constant is "change"

any thoughts???...9i(RAC) Suse Upgrade to 10GRAC(Solaris)

2006-11-03T15:31:00.000-08:00

This is the next Project I am on 9iRAC(Suse) to 10gRAC(Solaris) - both a version upgrade and cross platform migration at onetime.

I have not given it too much thought yet, but this is the upgrade path I was thinking of(I will need to run it by support in any case to verify that it is supported)

1)Unracify the existing 9i RAC instance on Suse
2)Do a single Instance Upgrade to 10g on Suse
3)Move 10g database over to Solaris(with new 10g Migration Options)
4)RACIFY the 10G instance on Solaris.

any thoughts/inputs on the above will be greatly appreciated.

the power of clearfsimport............

2006-11-03T10:28:00.000-08:00

at my current Customer Site, I have had to wear both hats -db development support and db production support for different projects. Clear Case is used as the Source Control Management tool here.

A lot of folks use rational Clear Case LT explorer on Windows to checkin and checkout files. A few who hate to transfer files around will install their Clear Case client on Unix and work with
"cleartool checkout"
"cleartool checkin"
you can take it to the next step. A huge working directory , you ve worked on several scripts - to checkout and check in each one of them can be cumbersome.

"Clearfsimport" is a powerful utility - you can simply do a Recursive Filesystem compare between your VOB and your working directory--->all the rest is taken care of. ClearfsImport will determine what files are newelements(add them to src control), what has changed and update the changed files without the need to checkout and checkin files.
Works great----especially if you work on a lot of code , you don't want to spend an entire day figuring out what to checkin and checkout.

syntax for clearfsimport:
cd to your VOB dir(note you should be in your VOB dir while running clearfsimport)
clearfsimport -preview -recurse -follow -nsetevent your_working_dir .
(the above command will give you a preview of what is going to happen)

clearfsimport -recurse -follow -nsetevent your_working_dir .
will get your VOB in sync with your working dir.

RSS Feed for Conferences & Events??

2006-11-02T09:42:00.000-08:00

I would call it my sheer laziness to have to visit websites anymore when I have all my RRS feeds setup in my RSS Reader.

OUG Conferences & Events , has anyone seen a RRS Feed for this ? just thought it would be nice to know all what's happening - and if anything interesting pops up , I can make some time for it.

Dynamic Switching on a single 10g Reports Server instance - a small bug with a small workaround

2006-11-01T14:42:00.000-08:00

In order to setup multiple environments on a single 10g Reports Server Instance the following needs to be done -

Step 1 - Define The environment Characteristics (Environment Element)

In the Reports Server's configuration file (eg, .conf) which is found in $ORACLE_HOME/reports/bin/"rep_servername".conf: add the defaultEnvID's.

while invoking the Report pass the parameter ENVID . Should work right??? It works on Windows but not on Unix and Linux.

The small workaround is:
1. Comment REPORTS_PATH entry in $ORACLE_HOME/bin/REPORTS.SH file.

2. If REPORTS_PATH set in REPORTS.SH file is required then create a new Environment ID tag in reports server's Conf.

3. Modify the rwEng tag to default it to the default environment

better now than never – buffer busy wait challenges and “data block class”

2006-10-31T13:33:00.000-08:00

Object contention – Buffer Busy waits ?? ? – Tablespaces have no ASSM set?? On RAC ?? – single freelist group?

I have a RAC instance with a 30million row+ OLTP table that is not Partitioned (this is our most used table and since we have a Standard edition license partitioning right now is not an option until we get budget for Enterprise edition ).

The underlying DATA tablespace for this table is not on ASSM and has a single freelist group. I got called about 3 weeks back for slow performance at certain times during the day. Obviously the first thing I do is jump on Spotlight do my general checks and see my wait events. There was no doubt that my biggest wait event was “Buffer Busy waits”. While I used my performance monitoring tool to get down to this – the below sql will also list your top wait events.

select event,time_waited,
round(time_waited*100/SUM (time_waited) OVER(),2) wait_pct
from(
select event,time_waited
from v$system_event
where event NOT IN
('Null event',
'client message',
'rdbms ipc reply',
'smon timer',
'rdbms ipc message',
'PX Idle Wait',
'PL/SQL lock timer',
'file open',
'pmon timer',
'WMON goes to sleep',
'virtual circuit status',
'dispatcher timer',
'SQL*Net message from client',
'parallel query dequeue wait',
'pipe get'
) UNION
(select name,value from v$sysstat
where name like 'CPU uses when call started'))
order by wait_pct desc

We had this issue 2 days consecutively in the week(and have had similar behavior over the last few months) . We also collect statspack data frequently and the below query was run to identify the buffer busy segments in question for the time interval we had performance problems.

acknowledging source of sql : Author:Tim Gorman (SageLogix, Inc)
spool sp_buffer_busy_waits
clear breaks computes
break on day skip 1 on object_type on report
select yyyymmdd sort0,
daily_ranking sort1,
day,
object_type,
owner,
object_name,
buffer_busy_waits
from (select to_char(ss.snap_time, 'YYYYMMDD') yyyymmdd,
to_char(ss.snap_time, 'DD-MON') day,
o.object_type,
o.owner,
o.object_name,
sum(s.buffer_busy_waits) buffer_busy_waits,
rank () over (partition by to_char(ss.snap_time, 'YYYYMMDD') order by sum(s.buffer_busy_waits) desc) daily_ranking
from (select dbid,
instance_number,
dataobj#,
obj#,
snap_id,
nvl(decode(greatest(buffer_busy_waits,
nvl(lag(buffer_busy_waits) over (partition by dbid, instance_number, dataobj#, obj# order by snap_id),0)),
buffer_busy_waits,
buffer_busy_waits - lag(buffer_busy_waits) over (partition by dbid, instance_number, dataobj#, obj# order by snap_id),
buffer_busy_waits), 0) buffer_busy_waits
from stats$seg_stat) s,
stats$seg_stat_obj o,
stats$snapshot ss
where o.dataobj# = s.dataobj#
and o.obj# = s.obj#
and o.dbid = s.dbid
and ss.snap_id = s.snap_id
and ss.dbid = s.dbid
and ss.instance_number = s.instance_number
and ss.snap_time between (sysdate - &&V_NBR_DAYS) and sysdate
group by to_char(ss.snap_time, 'YYYYMMDD'),
to_char(ss.snap_time, 'DD-MON'),
o.object_type,
o.owner,
o.object_name
order by yyyymmdd, buffer_busy_waits)
where daily_ranking <= 10 order by sort0, sort1; There results showed that my biggest table had the highest buffer busy waits (note it was only on the table object and not on the index objects; the index tablespace had ASSM on them) I knew my options were the below:

(i)alter this object for multiple free list groups (this calls for an object reorg, we all know that reorgs require time, appropriate approvals and planning for it to be implemented in Production).

(ii)recreate the DATA/INDEX tablespace for this object with ASSM and let Oracle manage your freelist contention.
(we are on version 9i, as a DBA you wish your underlying structure are set correctly for a huge application. But then that’s not a realistic world ; most likely you have to pickup the work that was done in the past and get them all together)

(iii)alter storage for the object to add more freelists (this can be done with a simple alter table statement).

Obviously the patch I could submit rightaway to change management was for (iii). Since we have added more freelists; I have not had calls on performance issues – but given that I was on RAC and a single freelist group ....the issue had to be addressed

I mustered all the courage to let Management know that we may have to Reorg our biggest table and given that I will need to reorg the table I rather go with ASSM

While the reorg will need some planning and preparation it can be done. “Better now than never.” My experience with Quest Central for Live Reorg has been fairly good

Below are some quick / important steps to get your Live Reorg rolling
Before you begin install QSA (quest server agent) on your database server. I prefer the manuall install – Install from the GUI keeps failing at the login prompt even if you have rexec enabled.

1.Step1 connect to Space manager(part of Quest Central – Version 6.0.1.289) as an account that has dba privileges.
2) On your Tolls Bar Reorganize > Reorg Manager
3)Find the object you want to reorganize and say Next
4)Choose the Option (i) Optimize for speed (ii) Relocate Tablespace (create a new tablespace with SEGMENT SPACE MANAGEMENT AUTO)
5)Choose default scripting options;default tablespace storage options; Object Options
make sure to check for NoLogging
6)Store the Reorg as job schedule
(Details on the Reorg will be a separate on its own)
While I am in the process of testing my reorg in preprod and had no performance issues after I added multiple freelists to the heavily used table in PRODUCTION, I am still left with one question, will a freelist contention not manifect itself as a “freelist class” in v$waitstat when you are on RAC and have a single freelist group? I have read some posts saying that in all probablility it may manifest itself as a “datablock class” until you add multiple freelist groups.(I will need to run more test to verify this)

October 30th, 2006 - Shakir Sadikali's blog pointed out buffer busy waits as a result of disk write caching disabled..."I did not have to check our batteries" we are on NFS and noac "nocaching is a separate issue that we are dealing with for I/O performance issues"

my everyday struggles with noac……….

2006-10-27T10:51:00.000-07:00

"noac" is a mount attribute that disables attribute caching on the client side. If you are on RAC this option is mandatory for mounting shared files with NetApp. I have proved several times now through several tests that our performance is 3 to 4 times slower with the noac option as opposed to mounting the files without noac. Oracle does support RAC on NetApp – and NetApp requires the noac option for RAC. The bottom line the database is suffering from performance problems due to this option ; the current version is SLES 8 and Oracle 9i. NetApp claims that with SLES 9 we should not have any problems and Oracle says Oracle10g On Solaris should not pose any problems. ….well, I will have to see to believe now …we are in the process of a couple of migration tests to get past this problem

more on NetApp and RAC can be found on the below linkhttp://www.ntap.com/library/tr/3359.pdf

Is VPD a Realistic Solution for existing Production Applications with n tables??

2006-10-26T15:32:00.000-07:00

With security becoming a pressing concern these days, Customers like to enforce multiple levels of security. When the question was put on the table to ; my obvious answer to the Customer and Application team was “the database allows for fine grained access control and we can enforce security using VPD policies at the database level”.

It was not until I started looking at the n(power n) tables in my Database that I started wondering ……….is VPD really going to be a realistic solution? VPD lets you create policy functions that returns the appropriate predicates to be applied to the tables. I have seen it work well for some small Applications where there is a definite set of predicates that are returned. In an Application with many many tables; I think it would be an exhaustive effort just to come up with a list of predicates that need to be applied to the tables……………..”Column Relevance”, is certainly a good feature with 10g but I still keep wondering how one would come up with the list of predicates for many tables linked by many keys and no single data coulumn available to filter data across all tables.

Saving Public connections to Oracle Business Intelligence 10g (10.1.2)…..

2006-10-24T12:40:00.000-07:00

In the middle of all your OOW2K6 blog information, a little peek at the few roadblocks I hit today with BI 10G

The last few hrs I have been cranking out Discoverer Reports for a presentation with Business Users. Business is in the process of evaluating WebFocus and BI Discoverer (the $$$ numbers look better with Discoverer but will it serve the purpose is what we are trying to figure out).
I have Oracle Business Intelligence 10g (10.1.2) up in running with all my workbooks/worksheets ready to go for the demo….hmmm…until I hit one problem.

i)Oracle Business Intelligence 10g (10.1.2) does not have an option to save Viewer Public connections. I know this can be done when integrated with the portal but BI standalone doesn’t seem to give me this ability on the EM console. I have spent sometime now looking for this information on the web and have found no solutions (any solutions?? will be great to know)

(ii) are Analytic Functions available in Discoverer? With the need to develop more and more complex Reports these days , most of our Reports use Analytic Functions ROW_NUMBER(),RANK(),DENSE_RANK() I have had to create views with Analytic Functions and then build Discoverer Reports on the views

Configuring Oracle 10g Reports In-Process Server .......

2006-10-24T07:49:00.000-07:00

I spend a lot of time in the Database World ; but requirements have needed me to work with Oracle Application Server often as well(years back I had to maintain OAS 4.0 - remember the cartridge world!!!; maintaing OAS 10g has made life so much more easier )With 10g OAS most of my work has been with Deploying and maintaining the BI Discoverer container and everything with Discoverer, having said this I was familiar with opmn and all its underying fundamentals to manage the Discoverer Container.Recently I got moved to an effort where my Clients wanted me to set up a 10g Reports server(dev/test and prod instances and start getting reports out the door).

Below are a few key points that got me upto speed fairly fast with setting up my Oracle Reports Server

1)In-Process Server is the default configuration for the Reports Server. In-Process Server Default name = "rep_hostname"

2)Starting your Reports Server cd $ORACLE_HOME/bin/rwserver.sh server="rep_hostaname"

3)Configuring connection information for your reports servercd $ORACLE_HOME/reports/confOpen cgicmd.dat add the following entry: userid=/@ destype= desformat=

4)To maanage your In-Process Report Server from opmn$ORACLE_HOME/bin/addNewServerTarget.sh "rep_hostname". To start and stop your container./opmnctl stopproc ias-component=repsrv904 process-type=ReportsServer

5)Configuring Directory Location for your Reports cd $ORACLE_HOME/reports/conf/rep_hostname .confedit rep_hostname.conf and change sourceDir property to where the Reports are. Remember to remove the comments from the XML Propert

6)Invoking Web based Report from your Forms Forms WEB.SHOW_DOCUMENT Built In to call ReportsSyntax: Web.Show_Document (URL,Target) .Target can be _blank or _self depending on whether the reports needs to open in a new window or not.

While my dev env is almost there, I am sure we will have a little more things to deal with before we go Live. It has been a busy week overall ; keeping up with work and trying to squeeze in time to catchup with all what's happening at OOW2006; my car breakdown has added to the mix as well

Count Down for Oracle Open World (2006)!!

2006-10-20T12:04:00.000-07:00

As a few of my friends leave today for Open World, I have been busy at work and stayed back. I can't wait for the podcast sessions from Open World. Some of the sessions I have been waiting hear are below ..........................and as fast as Oracle can get them out; I will be ready to consume the information

1) Concurrent Manager Queue Overlap Analysis
2) Oracle On Demand Powered By Oracle Database Technology
3) Leveraging Oracle Workflow for Efficient Transaction Processing
4) Oracle Daily Business Intelligence: A Business Intelligence Solution for Oracle E-Business Suite, Providing Relevant, Timely Information to End Users
5) Using Oracle Warehouse Builder 10g Release 2 to Implement Production ETL in a Geospatial Data Warehouse
6) A Hidden Treasure: Oracle E-Business Suite Support Tools
7) Coming Improvements in Oracle Text Performance
8) Oracle Database Secure Configuration: What You Need to Know Today
9) Dell IT: Get Beyond Oracle Real Application Clusters and Start Deploying an Enterprise Grid Architecture
10) Bristol-Myers Squibb: Transparent Application Tuning Using SQL Profiles--Practical Lessons, Tips, and Techniques
11) Continuous Data Protection (CDP) for the Oracle Database
12) To Upgrade or Not to Upgrade? Archiving Strategies for Oracle E-Business Suite Sites
13) Tuning SQL When You Cannot Change the Code
14) Oracle Application Server 10g Release 2: New Features
15) Using Business Intelligence to Maximize Customer Value
16) Oracle Discoverer Future: Protect, Extend, Integrate
17) Oracle Forms Personalization Without Coding
18) MAA Best Practices: Reducing Downtime for Planned Maintenance
19) Operations Using Oracle Database 10g High-Availability Features
20) Composite Fusion Middleware Solutions for Oil and Gas
21) Why Digital Signatures and Oracle Go Hand-in-Hand
22) Oracle Data Mining Case Study: Xerox
23) Demystifying SQL Tuning with Oracle Tuning Pack
24) Welcome to My Nightmare: The Common Performance Errors in Oracle Databases
25) Maximum Availability Architecture Best Practices: Building a Highly
26) Available and Disaster-Proof Architecture, Using Data Guard, Oracle RAC, Automatic Storage Management (ASM), and Flashback27) Storage Area Networks for Your Oracle Applications

Cost Based Optimizer - Let your optimizer do it for you!!!(Easier said than done)

2006-10-16T10:56:00.000-07:00

Easier said than done ! I have a 9i RAC environment (SLES; Suse Linux) - its been up for a while with no statistics updated except when the first roll out happened. I come onboard and start crying we need statistics updated (its a COST Based Optimizer OLTP system); some queries are suffering because of bad execution plans which in turn is because stats are not updated.

All right, so convinced management that we should go ahead and get our stats updated and keep it updated for a COST Based Optimizer system. And guess what while 80% of the queries do better - 20% suffered and started taking a very long time after statistics were updated (In a PreProduction environment).

So the process has not been simple (considering we have n number of Applications on the system) - we have had to go through many test cycles to identify bad sql and tune them. I have not found an easier way to get past this problem ; have not heard of a better solution so far from my friends in the Oracle community as well. I do know that AWR on 10g will make this effort a little easy - are there any solutions for 9i????

Do away with tnsnames.ora - setup your OID proof of concept in less than half hour

2006-10-12T13:57:00.000-07:00

If you are looking to move towards a centralized LDAP server and do away with tnsnames.ora, you can set up a proof of concept with OID fairly fast.

9i Release 2 Database Install comes with OID, do a custom install of OID and all its Managed Server Products.

Once the Installation is successfully completed :
Oracle Net Manager:
The Oracle Net Manager can be used to perform entry management within OID:

Start up the Oracle Net Manager. Expand the Directory node.
Click on Service Naming node and press the + button.
On the Directory Server Authentication dialog enter the correct user and password (cn=orcladmin/password) and click the OK button.
Add the service and test it in the same way you would add a local naming service using the Net Manager.
You can add all your services at this step.
Exit Oracle Net Manager.

Oracle Directory Manager
The Oracle Directory Manager is the main directory administration tool.
In this case we will use it to check that the Oracle Net Manager has entered our connection information into the directory:
Start the Oracle Directory Manager.
Log into the OID Server Instance using the correct user (cn=orcladmin), password, server and port (389).
Expand the Entry Management node. Expand the cn=OracleContext node. Listed under this node will be an entry (cn=Service) for each service configured by the Oracle Net Manager.
Exit the Oracle Directory Manager.

Once the above steps are completed, remove your tnsnames.ora and publish your ldap.ora to your users. ldap.ora should be saved in the $ORACLE_HOME/network/admin directory.

below is an example of an ldap.ora file:

# LDAP.ORA Network Configuration File: C:\oracle\oid\network\admin\ldap.ora# Generated by Oracle configuration tools.
DEFAULT_ADMIN_CONTEXT = ""
DIRECTORY_SERVERS= (xxxxxxxxx:389:636)
DIRECTORY_SERVER_TYPE = OID

your connections now to Oracle is Centralized and you can stop distributing tnsnames.ora and connection information. The flip side to it , you have a single point of failure if your OID server is down - think of having a backup ldap server, can ldap.ora support multiple ldap servers - the last I tried we could not get ldap.ora to support multiple ldap servers......................

Oracle Apps 11i Installation

2006-10-06T13:15:00.000-07:00

Anybody installed Oracle Apps 11i on Windows - I wanted to jumpstart my experience with Apps 11i with Vision Demo.
I had a windows server sitting on my Desk - thought it will be quick to have that setup with 11i Apps.
Its no joke figuring out all the little bits and pieces that need to be in place prior to Installing Oracle Apps 11i on windows.
---cygwin( make sure you get make and gcc),VC++,perl (if you used edelivery to download your software you will not need to go through the staging steps).
might have just been faster to rebuild my box to RedHat and completed the 11i install......more to come...Installation steps Oracle11i Apps on windows finally......

DON’T JUST COLLECT STATSPACK DATA – PUT THEM TO USE

2006-09-29T12:50:00.000-07:00

If your organization is collecting statspack data, don’t just print spreport when you have a performance problem and expect to have the report help you solve all your problems. If you snapshot at frequent intervals through the day – Statspack data has a wealth of information. You can plot trends for specific wait events and through consistent tuning effort you can see your wait events comedown…………………..get more for the data you collect. The below monitoring application was developed just through sql scripts against perfstat schema and using Oracle HTML DB

2006-08-15T08:54:00.000-07:00

Building your Dashboard at work – Oracle HTML DB

I used to spend a lot of time on email/phonecalls - answering some of the frequently asked questions

when was preprod /test/dev1/dev2/test...refreshed?
what Application db patches do we have on the different environments?
when different Application db patches were applied to different environments?
DB release notes for the different patches?
Results of different load and datafix runs?
Schedule for load runs?Schedule for db refreshes?

I decided to build a central dashboad that will answer all the FAQ s realtime - reducing my time on emails and helping me focus on other important db tasks.

Oracle HTML DB did it for me – I had a quick Dashboard Application up in running in 3 hrs (The Application connects to atleast 10 databases and Reports on different information for different instances – based on the FAQs)………..Read More >>>>

DRINK COFFEE AND PLOT AWAY YOUR DATABASE METRICS

Are you required to plot database statistics every Monday Morning to present Reports to Management?
Are you part of a business that cannot pay for Reporting Tools and have to plot your metrics in Excel?

While Excel works great for plots – it is client driven and not server driven and you loose all your configurations everytime you change your client machine and run into the risk of your plots not updating if your DSN or odbc is incorrect/updated .

I found a quick way to plot reports from my database server using gnuplot and then scheduling it as a DBMS job to email me the plot weekly – just drink your coffee Monday morning and send management the attachment you get via email weekly – and the best part everything runs within your database – how cool is that!

It also works great if you want to report statistics on Oracle Applications concurrent programs where start and endate’s of a program’s runtime are recorded in the database. I currently use it to report statistics on my load runs. Automating this process has given me the time to focus on more important aspects of my database.

.......Read More >>>

Test your Oracle Partition Archive/Restore Strategies:

Are we testing our Oracle Partitioned Tables Restore strategies?

Your Partition Archives are no good if they cannot be restored when needed and if your SLA’s require you to restore the partition if needed by the customer.Below are some quick steps you can follow to test your Oracle Partitioning Archive and Restore Strategy.......Read More >>>

No Monitoring Tools to Monitor your Database

Have you been part of an organization that has huge Oracle Databases but no database monitoring tools? It seems to be quite normal – while funding for database Licenses have been approved without a problem it has been quite a challenge to get funding approved for monitoring tools like SpotLight, Foglight, Precise, Hotsos Profiler etc.

So how does a DBA then debug performance issues? While writing shell scripts to monitor space usage, memory usage to page the DBA will help – scripts may not be the best way to solve the performance problem in hand when users are complaining about slow performance.I have worked for organization where we had no third party Products to monitor our databases or drill down into Performance Issues when users are complaining about slow performance. This is where OEM Diagnostic Pack (Performance Manager) has come very handy.....Read More >>>

2006-08-10T09:54:00.000-07:00

Below is an Overview of Oracle HTML db and how you can use it fairly fast to set up a Real Time Application.

Oracle HTML DB renders Real Time Applications from data stored in Tables.

1)Before Installing Oracle HTML DB install Oracle HTTP Server standalone from the Oracle 10g Companion CD

2) To install the Oracle HTML DB software, perform the following steps:

a. Download the HTML DB software from OTN .
b. Unzip htmldb_2.0.zip.
c. Open a terminal window and execute the following commands
cd
sqlplus /nolog
connect / as sysdba
@htmldbins oracle SYSAUX SYSAUX TEMP /i/ orcl

Oracle HTML DB is installed into the database.

3)You need to copy the images that Oracle HTML DB uses to the appropriate directory. Perform the following steps:

a) Open a DOS window and execute the following commands:

cd /Apache/Apache
mkdir images
cd images
xcopy /s /images/*.*
is the directory during the HTTP install where you specified the oracle_home (i.e. c:\oracle\product\10.2.0\http_1)
is the directory where you unzipped the htmldb zip file and made available for install.

4) Configuring HTTP Server to Run Oracle HTML DB
In order to run Oracle HTML DB, you need to configure an application in HTTP Server. Perform the following steps:

(i) Create the \Apache\modplsql\conf\marvel.conf file and add the following statements:

Alias /i/ "\Apache\Apache\images/"
AddType text/xml xbl
AddType text/x-component htc

SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername HTMLDB_PUBLIC_USER
PlsqlDatabasePassword oracle
PlsqlDatabaseConnectString :1521:
PlsqlDefaultPage htmldb
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDocumentPath docs
PlsqlDocumentProcedure wwv_flow_file_manager.process_download
PlsqlAuthenticationMode Basic
PlsqlNLSLanguage AMERICAN_AMERICA.UTF8

Note:
refers to the name of the host where your database is resides. refers to the connect string to your database

(ii)Modify the \Apache\Apache\conf\httpd.conf file and add the following lines to the end:

# Include the Oracle configuration file for HTML DB
include "\Apache\modplsql\conf\marvel.conf"

(iii)Stop and start the HTTP Server with the following commands:

cd \opmn\bin
opmnctl stopall
opmnctl startall

5) Creating a New Workspace
Before you create an application, you need to create a workspace. Perform the following steps:

1. Open your browser and enter the following URL:

http://:7777/pls/htmldb/htmldb_admin
Enter admin as the username and enter your password oracle (or what ever the password you specified during the installation). Then click Login.

2. Under Manage Workspaces, click Create Workspace.

3. Enter a workspace name and then click next

4. For "Re-use Existing Schema?" select No. Enter for the Schema Name and Password, and select Medium: 5 Megabytes for the Space Quota. Then click Next.

5. Enter as the username, and enter your password and your e-mail address. Then click Next. Note: A new administrator user is created in addition to the workspace.

6. Review your workspace request, and then click Create.

7. Your workspace and user is now created. Click Done.

8. You now want to login as the user for the workspace. Click Logout.

6) Loginto your workspace

The first step to creating your Application is logging into your workspace
Login with workspace name,username,password

Once you have logged in – you will be prompted for (Application Builder, SQL Workshop, Administration)

Click on Application Builder and create a new Application say “dbaconsole”
Create Application from scratch
you can add a page to your Application at this point
select a tab level if you want tabs on your page

click the next and go through the option you would like to have for your Application and finish Create Application

The above page will show up – indicating the Application that was just created and showing the page that was added to the Application
click on the page and add the regions you want added to your page – say we want a SQL Report add to our page 1
click add Region and check on Report

enter the sql you need to display your results in the form of a Report
finish by clicking create region

Now Run your application and you will see our data displayed in a Report format

Different Options are available to edit Page Attributes:

-Regions, Buttons,Items (Eg: Checkbox,datepicker,Display only,File Browse etc),Processes like unnamed pl/sql blocks can also be added to a Page. Numerous options are available to edit page display properties.

Oracle HTML DB – is completely UI driven – its great to set up small Applications – like the one I was trying to build – a Realtime Dashboard – that lists the status of all my databases, db patch level on the different databases, Application db patch updates on the different databases, it is great for storing all your documents as well – a central Repository for the work the dba’s do accessible from anywhere at anytime!!!

DRINK COFFEE AND PLOT AWAY YOUR DATABASE METRICS

2006-04-25T14:15:00.000-07:00

How do you do it?

Step1:
Download gnuplot from http://www.gnuplot.info/ to generate your postscriptfile and imageprick to convert your postscript file to gif http://www.imagemagick.org/script/install-source.php
Gnuplot:
Download gnuplot-4.0.0.tar.gz
Move the binaries to your database server
Untar the file ./configure will configure gnuplot for you on your server.
Imagemagick:
gunzip -c ImageMagick.tar.gz tar xvf –
cd ImageMagick-6.?.?
./configure
make
make install

Step2:
Below is an example of a metrics table that records start and end time of a specific load run that is run every evening

LOAD_NAMESTART_TIMEEND_TIMELOAD_DATE
LOAD_NAMESTART_TIMEEND_TIMELOAD_DATE
test4/15/2006 2:20:00 AM4/15/2006 3:00:00 AM4/15/2006
test4/16/2006 2:00:00 AM4/16/2006 3:45:00 AM4/16/2006
test4/14/2006 2:00:00 AM4/14/2006 3:00:00 AM4/14/2006

Step3a:
A small Java stored procedure scheduled as a DBMS job that will read from the table weekly , invokes the unix program gnuplot and generates your plot as an image and emails the plot to the DBA.

Java Stored Procedure called UnixCmd – that will let you execute any host command from the database

1)
--Grant Privileges as SYS to your LOAD_USR
EXEC Dbms_Java.Grant_Permission('LOAD_USR', 'java.io.FilePermission', '<>', 'read ,write, execute, delete');
EXEC Dbms_Java.Grant_Permission('LOAD_USR', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC Dbms_Java.Grant_Permission('LOAD_USR', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC dbms_java.grant_permission( 'LOAD_USR','SYS:java.io.FilePermission', '/bin/sh', 'execute' )
grant execute on p_UnixCmd to LOAD_USR;

2)
--connect as LOAD_USR
--Create Java Class Named UnixCmd
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED UnixCmd AS
import java.io.*;
public class UnixCmd {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}

final Process pr = Runtime.getRuntime().exec(finalCommand);
new Thread(new Runnable() {
public void run() {
try {
BufferedReader br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Process out :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
}
}).start();

new Thread(new Runnable() {
public void run() {
try {
BufferedReader br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Process err :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}

public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}

};
/

--Create a stored procedure that calls your Java Class
CREATE OR REPLACE PROCEDURE p_UnixCmd (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'UnixCmd.executeCommand (java.lang.String)';
/

--Test a Unix Cmd call from your stored procedure
SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
p_UnixCmd (p_command => 'touch /tmp/test1.txt');
END;
/

Step3b:-- Now your ready to create the procedure that plots your metrics and send you the metrics via email

CREATE DIRECTORY WRITEDIR
AS '/tmp';

GRANT READ ON DIRECTORY WRITEDIR TO PUBLIC;

GRANT WRITE ON DIRECTORY WRITEDIR TO LOAD_USR;

CREATE OR REPLACE procedure dump_data
is
cursor c1 is
select ( to_char(load_date,'YYYY/MM/DD') ' '(to_date(to_char(end_time,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')-
to_date(to_char(start_time,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS'))*24*60*60 ) as PLOT from load_metrics1 order by load_date asc;

file_handle UTL_FILE.FILE_TYPE;
file_handle1 UTL_FILE.FILE_TYPE;
i number:=1;
sample_start varchar2(11);
sample_end varchar2(11);
metricsdate date:=sysdate;
BEGIN

file_handle := UTL_FILE.FOPEN('WRITEDIR','LOAD_METRICS.dat','W');

for rec in c1 loop
if (i = 1) then
sample_start:=substr(to_char(rec.PLOT),0,11);
end if;
UTL_FILE.PUT_LINE(file_handle,rec.PLOT);
if (i = c1%rowcount) then
sample_end:=substr(to_char(rec.PLOT),0,11);
end if;
i:=i+1;
end loop;

UTL_FILE.FCLOSE(file_handle);
p_UnixCmd (p_command => '/var/home/oracle/gnuplot.sh');
END dump_data;
/

create or replace procedure dump_data2 as
BEGIN
p_UnixCmd (p_command => '/usr/local/bin/convert /tmp/LOAD_METRICS.ps /tmp/LOAD_METRICS.gif');
p_UnixCmd (p_command => 'mailx -s "Weekly Load Run Report" "xxxxxxxxxxxxxxxxx" < /tmp/LOAD_METRICS.gif'); END; / contents of gnuplot.sh is as follows: gnuplot << !EOF set terminal postscript set output "/tmp/LOAD_METRICS.ps" set title "Load Run Statistics" set xlabel "Date" set ylabel "Duration \n(in s)" set xdata time set timefmt "%Y/%m/%d" plot "/tmp/LOAD_METRICS.dat" using 1:2 with boxes exit !EOF Step4:
As load_usr schedule a dbms_job to exec dump_data and dump_data2 and your resulting gif file will be emailed to you.
above is a sample gif output file obtained as an email attachment

……..now just forward the attachment to those who would like to see your load run statistics.

Test your Oracle Partition Archive/Restore Strategies:

2006-01-13T11:56:00.000-08:00

Test your Oracle Partition Archive/Restore Strategies:

Are we testing our Oracle Partitioned Tables Restore strategies?
Your Partition Archives are no good if they cannot be restored when needed and if your SLA’s require you to restore the partition if needed by the customer.

Below are some quick steps you can follow to test your Oracle Partitioning Archive and Restore Strategy.

Step1

The first thing to do is create a discrete table that is identical to the partition we want to remove. It must have the same columns, data types, constraints and indexes, and exist in the same tablespace as the partition. The index must also exist in the corresponding index tablespace.

create table PNAME_2004Q1 tablespace PNAME_2004Q1NEW

Step2

The new table in Step1contains no rows, and the index is empty. When we EXCHANGE a partition with a discrete table, we literally exchange data segments between the two. That's why they must match in structure and location. No data is actually moved, only internal pointers are changed.

ALTER TABLE TABLE_NAME EXCHANGE PARTITION PNAME_2004Q1
WITH TABLE PNAME_2004Q1 INCLUDING INDEXES;

Check the indexes at this step – you will see that indexes of only the partition you exchanged got UNUSABLE (the partition you exchanged is the partition you want to drop)
Please Note: Both Global Index and Local Indexes for that partition may be unusable.
A Global Index rebuild will affect the entire table and a Local index rebuild will affect only the Local partition.

Step3:

Count the rows in the new table and in the partitioned table before and after the EXCHANGE to convince yourself that the data did actually "move", check the size of the data segments, and the status of the indexes.

SELECT COUNT(*) FROM PARTITION TABLE_NAME(PNAME_2004Q1);
SELECT COUNT(*) FROM PNAME_2004Q1;

Step4:

We no longer need the old partition as it is now empty, so we can drop it.

ALTER TABLE TABLE_NAME DROP PARTITION PNAME_2004Q1;

If the goal was to simply transport this partition without removing it, we could exchange the table back into the partition after transporting the tablespace.

To get ready make the tablespaces we are transporting (data and index) READ ONLY to ensure there are no active transactions, and that none start during transport. The rest of the partitions are unaffected.

ALTER TABLESPACE PNAME_2004Q1NEW READ ONLY;

Use the DBMS_TTS utility to verify the tablespace set is self contained. That is, there are no objects within the set that reference (foreign key, constraint indexes, etc) any object outside of the set. The set can be one or more tablespaces that will be transported together.

EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK(‘PNAME_2004Q1NEW’, TRUE);
SELECT * FROM sys.TRANSPORT_SET_VIOLATIONS;

exp \'sys/sysdba as sysdba\' file= PNAME_2004Q1.dmp transport_tablespace=y tablespaces=PNAME_2004Q1NEW log=PNAME _2004Q1.log
Make sure the unix team backs up the dump file

Drop table PNAME_2004Q1 ;
Alter tablespace PNAME_2004Q1NEW offline;
Drop tablespace PNAME_2004Q1NEW including contents;

Restore the Archived Partition

Step1:

Import the dump file into the target system. Again this is fast because we only import data definitions; the data already exists in the named datafiles. imp USERID='xxxxxx' TRANSPORT_TABLESPACE=y DATAFILES='xxxxxxxxxxxxxxxxx' TABLESPACES= PNAME_2004Q1NEW
FILE= PNAME_2004Q1.dmp

Step2:

By default the new tablespaces will be READ ONLY. In order to create the table partition and EXCHANGE it with the existing discrete table we will have to make them READ WRITE.

ALTER TABLESPACE PNAME_2004Q1NEW READ WRITE;

Step3:

We now have a partitioned table with one empty partition, and our discrete table with data that was transported. To complete the process, simply EXCHANGE data segments between the empty partition and discrete table. Then, count the number of rows, check the size of data segments and status of indexes again.

ALTER TABLE TABLE_NAME EXCHANGE PARTITION PNAME_2004Q1
WITH TABLE PNAME_2004Q1 INCLUDING INDEXES;

After the exchange check the indexes again – you may to rebuild the Local Index after this step if the indexes are unusable.

When you are convinced the exchange has occurred properly and the partitioned table is functional, drop the now empty discrete table and you are done for this year.

DROP TABLE PNAME_2004Q1 PURGE;

2005-11-22T08:56:00.000-08:00

No Monitoring Tools to Monitor your Database?

Have you been part of an organization that has huge Oracle Databases but no database monitoring tools? It seems to be quite normal – while funding for database Licenses have been approved without a problem it has been quite a challenge to get funding approved for monitoring tools like SpotLight, Foglight, Precise, Hotsos Profiler etc.

So how does a DBA then debug performance issues? While writing shell scripts to monitor space usage, memory usage to page the DBA will help – scripts may not be the best way to solve the performance problem in hand when users are complaining about slow performance.

I have worked for organization where we had no third party Products to monitor our databases or drill down into Performance Issues when users are complaining about slow performance. This is where OEM Diagnostic Pack (Performance Manager) has come very handy.

Some of the key things to look for in Performance Manager – that has helped me solve numerous performance issues are below

-Look for Top Waits for Time waited.
-Take a look into the Top Session by CPU/Memory and I/O
-see any one of those sessions could be attributing to the Top wait event

Eg:

If db sequential read is one of your top wait events
Look at yout Top I/O SQL statements - most likely one of them is attributing to your db sequential read wait event
Verify the explain plan of the sql and tune your sql
You will be amazed how tuning the sql will make the wait event dissappear and all the sql statements waiting on the event dissapear

Screenshots below will show you how to look at these using the Diagnostic Pack.

Look at your top waited events above.

Look for your top sessions by I/O,Memory and CPU - look at the sql and analyze how they attribute to your top wait event.
View session details will give you all the detail information you need about the sessions.
Performance Monitoring is a vast subject - what was listed above is just a quick way your DBA can dive into the database and see what exactly is happening. While statspack is great for metrics collections - most likely is not the best tool you have in hand when you have users standing behind you wanting you to solve their performance problem asap.
I strongly recommend using the Diagnostic Pack if your organization has no other monitoring tools inhouse - as you get familiar with the product you will decide its time to put your scripts away to solve performance issues.