Skip to main content

Posts

Showing posts from 2009

PCP -- Parallel Concurrent Processing

I often get queries about PCP(Parallel Concurrent Processing) and its configuration. I will try to include all such details in this blog which will help configure PCP step by step.


PCP makes sense on a RAC environment else there is no point having PCP atleast for me.

POINT1: Check if you require PCP to load balance or to be used as failover.

If both the nodes then, Change the names of the concurrent managers say SM:Node A and Assign PRIMARY and SECONDARY Nodes to that manager.

Then duplicate the record and change the name to SM:Node B for the 2nd manager and change the PRIMARY and SECONDARY Nodes
Note: Ensure the hostname should be only the name of the host from uname -a command, not the virtual hostname.

Do it for all the managers in the instance including tx mgr, internal mgr EXCEPT Internal Monitor.

Following configuration details are from metalink document 362135.1 section 3.12.

Check PCP Prerequisites

It is assumed that you have more than one Concurrent processing tiers in your environmen…

ORA-01950: no privileges on tablespace

SQL> create table mith( n number primary key, l varchar2(60));
create table mith( n number primary key, l varchar2(60))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Whenever a new user is created it requires certain previlages like connect, create any table, view etc along with grant on table space. To get rid of this error you can use the following sq,

1) GRANT UNLIMITED TABLESPACE TO MITH;

OR

2) ALTER USER MITH QUOTA 50M ON USERS;

Text Index -- Printjoins

I am trying to create a test case for one of my clients having issues with text index.

Text index comes with lot of options for language, lexer etc. It is not as simple as creating a normal index. Through my experience i have seen multiple issues with text indexes and one of which is with printjoins.

Details of Text index and its options can be found at,

http://download.oracle.com/docs/cd/B19306_01/text.102/b14218.pdf

and look at my other post on text index,

http://mithunashok.blogspot.com/2009/10/oracle-text-index-real-time-experience.html

User complains that when they search with . character it does not consider . as character. Look at the example below(tested on 10.2.0.1).


create table mith( n number primary key, l varchar2(60));

insert into mith values( 1, 'nisha.mithun');
insert into mith values( 2, 'nisha mithun');
insert into mith values( 3, 'nisha mithun nisha mithun');
insert into mith values( 4, 'hello-world');
insert into mith values( 5, 'hello@world…

Oracle Text Index -- Real time implementation

Problem:

Create a context index on a table which has around 100 million records adds upto a size of 120GB. Index creation takes more than 20 days to complete. Along with 100 million records there are around 50 million more records which will be added to this table which makes the table grow to a size of 180GB and with this data index creation is expected to take even more time.

Following steps were being used to create the index.
set timing on time onexec Ctx_Ddl.Create_Preference('SCB', 'BASIC_WORDLIST'); exec ctx_ddl.set_attribute('SCB', 'wildcard_maxterms',1 5000) ;exec ctx_ddl.set_attribute('SCB', 'substring_index', 'TRUE') ;execute CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/oat_lg/rrotmedb/archive/ctx/'); execute CTXSYS.CTX_OUTPUT.START_LOG('g_SCB_IDX_gmis.LOG'); exec ctx_output.add_event(CTX_OUTPUT.EVENT_INDEX_PRINT_ROWID); drop index NORKOM56.SCB_TRANS_IDX force;CREATE INDEX NORKOM56.s…

JVM Performance tuning with 11i

Following are the questions answered to one of the mailing lists. Here it is shared.....

Usually Performance issues with JVM's are related either to,

1. JDK Bugs
2. JVM thread deadlocks and memory leaks 3. Application request taking too long to process ex. long running query, poor sql, db issues or java coding 4. Poor performing Garbage collection
5. Incorrect JVM startup settings

The amount of memory that should be allocated to each JVM depends on the number of JVM's, the number of users on the system, the amount of physical memory on the middle tiers, the habits of the users and the version of the JDK used by the system. THe version of JDK being used makes a large difference to how the memory and the number of JVM's should be sized. JDK 1.3 performs better with more JVM's and less heap while JDK 1.4/1.5 performs better when there are less JVM's and more heap. Thumb rule is to have 1JVM per 2 cpu's with increased heap parameter for better performance. As a minimum …

Forms Socket/Servlet

Following are the queries answered by me in one of the forums.

1. Will it be possible that the forms tier load also will be balanced by Hardware load balancer ? Before we know the answer for this question, i guess its better to know the forms architecture. Forms in Oracle Applications can be configured in 2 modes.

a. Socket Mode and
b. Servlet Mode.

a. Socket Mode: By default forms 6i comes with Socket Mode install. If customer hasn't done any changes then he would still be using Socket Mode. In socket mode then forms cannot be loadbalanced using hardware load balancer. In this case you need to use Forms Metric Server Load Balancing.

For configuration details refer to document: 217368.1 Section 2.4. Forms Server / Metrics Layer Load Balancing.
Forms socket mode connections are always faster compared to servlet mode(if hardware resources are infinite then this should be of little concern). This is the only reason why customers use forms socket mode else Servlet mode is…

Concurrent user logins in 11i

Following query will give you number of concurrent users logged into the system.

select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)


Run the following queries:-
This will give the number of users on the system in the past 1 hour.

select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';

This will give the number of users on the system in the past 1 day.

select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';



This will show the activity in the last 15 minutes.

select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time",
user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;


Check the No…

SAP and EBS

Recently i posted my views for the questions on a forum, having it here for discussion.....


Following are my understanding and only my personal view.


1. Can anyone please clarify what's the diff in SAP and EBS.

SAP and EBS are ERP solutions as you know, business wise its just a comfort factor which makes customer to choose either SAP or Oracle. To be straight SAP doesnt allow you to do customizations as it can be done in Oracle, this is what makes SAP tightly integrated less error prone. Both use their own way of programming which is the answer for your second question below. SAP has its own standards and Oracle too has its legacy. For techincal details you can refer to the following link,

http://oreilly.com/catalog/sapadm/chapter/ch01.html


2. In SAP ABAP is used for programming purposes. what is equivalent in EBS to that.?

ABAP is a programing language created by SAP and it is similar to any other programming language. Its quite similar to COBOL. This was first used as a reportin…

Auditing

How to Setup Auditing
~~~~~~~~~~~~~~~~~~~~~
Do the following to set up auditing on your database:

1. Modify the "init.ora" file, usually located in the "$ORACLE_HOME/dbs"
directory to enable the AUDIT_TRAIL parameter and stop/start the instance to
make the parameter effective.

AUDIT_TRAIL can be set to one of the following four values:

--> DB/TRUE enables systemwide auditing where audited records are written to
the database audit trail, the SYS.AUD$ table
--> OS enables systemwide auditing where audited records are written to the
operating system's audit trail

--> DB_EXTENDED enables systemwide auditing as DB/TRUE does; in addition, it
populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table

NOTE :
-----
DB_EXTENDED can be used starting with 10g only. More informations can be found in
Note 249438.1 - 10G New Value DB_EXTENDED for the AUDIT_TRAIL Parameter

--> NONE/FALSE disables auditing. This is the default value.

Example:
--------
AUDIT_TRAIL = DB

2. If yo…

DB Health Check

Table of Contents
-----------------

1. Introduction
2. Parameter file
3. Controlfiles
4. Redolog files
5. Archiving
6. Datafiles
6.1 Autoextend
6.2 Location
7. Tablespaces
7.1 SYSTEM Tablespace
7.2 SYSAUX Tablespace
7.3 Locally vs Dictionary Managed Tablespaces
7.4 Temporary Tablespace
7.5 Tablespace Fragmentation
8. Objects
8.1 Number of Extents
8.2 Next extent
8.3 Indexes
9. AUTO vs MANUAL undo
9.1 AUTO Undo
9.2 Manual undo
10. Memory Management
10.1 Pre-Oracle 9i
10.2 Oracle 9i
10.3 Oracle 10g
10.4 Oracle 11g
11. Logging & Tracing
11.1 Alert File
11.2 Max_dump_file_size
11.3 User and core dump size parameters
11.4 Audit files
11.5 Sqlnet



1. Introduction
---------------
This article explains how to perform a health check on the database. General
guidelines are given on what areas to investigate to get a better overview on
how the database is working and evolving. These guidelines will reveal common
issues regarding configuration as well as problems that may occur in the future.

The areas investigated here are mostly b…

India Localization

India Localization is a major part of any Indian company using Oracle ERP.


What is this India Localization(IL). Why is it so important.?


This is the module for Payrol/payables/receiving or for any transaction which comes under Local Indian taxasion rules. This is the module through which TDS (Tax Deduction at source) is collected wrt Indian taxation rules. This module belongs to JA_TOP for which the development work is done in India and it has its own library stack and its own way of applying patches. Since taxasion comes under India Regulatory act which makes it important to manage/implement this module without any flaws which makes it very hard on the client to test each and every scenario.


In 11i most of the readers would know that the patch details are kept in ad_applied_patches and ad_bugs but for IL it is kept under JAI_APPLIED_PATCHES.


India Localization patches are applied to IL Patch application tool which comes with the patch,

"6491231 REF : 6059025 - MULTINODE : PATCH TOOL …

What are Flexfields

Flexfields are a major part of Oracle Applications. Rather than knowing what a flexfield is, it is better to start with different types of flexfields and know what each type of flexfield mean.

There are 2 types of flex fields

Key Flexfield
Descriptive Flexfield

Key Flexfield:
A Key Flexfield looks like a normal text field on a form.


Descriptive Flexfield:
A descriptive flexfield appears on the form within 2 brackets [] with a character length 2.

When opened both types appear as a pop up window that contains sub fields.


So a flex field is a field made of sub fields(segments, values, valuesets).
Each segment has an unique name and a set of valid values and their descriptsion. A segment is represented as a single table column in a table.

Concurrent Requests Purge Program -- What happens?

The following tables will be purged:
.
FND_CONCURRENT_REQUESTS
This table contains a complete history of all concurrent requests.
.
FND_RUN_REQUESTS
When a user submits a report set, this table stores information about the
reports in the report set and the parameter values for each report.
.
FND_CONC_REQUEST_ARGUMENTS
This table records arguments passed by the concurrent manager to each program
it starts running.
.
FND_DUAL
This table records when requests do not update database tables.
.
FND_CONCURRENT_PROCESSES
This table records information about Oracle Applications and operating system
processes.
.
FND_CONC_STAT_LIST
This table collects runtime performance statistics for concurrent requests.
.
FND_CONC_STAT_SUMMARY
This table contains the concurrent program performance statistics generated by
the Purge Concurrent Request and/or Manager Data program. The Purge Concurrent
Request and/or Manager Data program uses the data in FND_CONC_STAT_LIST to
compute these statistics.

FND_CONC_PP_ACTIONS
Stores the post requ…

eMail Center -- What is it?

Oracle eMail Center is a comprehensive solution for managing high volumes of
inbound and outbound e-mails. Oracle eMail Center reduces the cost per email
interaction by automatically replying to certain email inquiries as well as routing
others to a skilled set of agents and providing them with a full featured console
with cross application functionality.

Oracle eMail Center increases customer satisfaction and reduces customer attrition
by providing quick, accurate and consistent responses. It also increases agent’s
efficiency through the use of a full featured, eMail Center agent console thereby
reducing agent turnover.


Oracle Email Center provides agents with the ability to create business objects such
as a service request and at the same time provides message composing/viewing
capabilities to the business users such that every email interaction is recorded and
archived.

Email Center provides its agents the ability to create a service request from within
the Message Component, while viewing and r…

Did you know --- Oracle Database 9i

1. Oracle 9i can have maximum of 256 column for a row.
2. For a partitioned table, each partition has a data segment. Each cluster has a data
segment. The data of every table in the cluster is stored in the cluster's data segment.
3. A tablespace can be online (accessible) or offline (not accessible). A tablespace is taken
offline to make a portion of the database unavailable while allowing normal access to the
remainder of the database.