Thursday, September 29, 2011

Oracle APPS : Workflow Tables

Oracle Applications - Workflow Tables (Quick Review)

Important WF Tables:

wf_user_role_assignments
wf_user_roles
wf_roles
wf_items
wf_item_attributes
wf_item_attribute_values
wf_item_attributes_tl
wf_activities
wf_activities_tl
wf_activity_attributes
wf_activity_attributes_tl
wf_activity_transitions
wf_deferred--wf_control

WF_NOTIFICATION_ATTRIBUTES
WF_MESSAGES
WF_MESSAGES_TL
WF_MESSAGE_ATTRIBUTES
WF_MESSAGE_ATTRIBUTES_TL
WF_ETS
WF_PROCESS_ACTIVITIES


WF_ACTIVITIES table stores the definition of an activity. Activities can be Processes, notifications, functions or folders. A process activity is a modelled workflow process, which can be included as an activity in other processes to represent a subprocess. A notification activity sends a message to a performer. A functions activity performs an automated function that is written as a PL/SQL stored procedure. A folder activity is not part of a process, it provides a means of grouping activities.

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system.

WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data.

WF_NOTIFICATIONS holds the runtime information about a specific
instance of a sent message. A new row is created in the table each time a message is sent.

Thursday, September 15, 2011

DBA_Objects shows identical object on same schema?

MATERIALIZED VIEW
=================

Whenever a Materialized view created, you should see 2 entries in dba_objects with same name. Because, it creates a table(segment) and a materialized view.
So, We should NOT get confused that why 2 objects has same name on same schema.

SQL> create materialized view mv1 as select * from i;
Materialized view created.

SQL>select object_name,object_id,data_object_id,object_type,status from dba_objects where object_name='MV1'

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
------------------------------ ---------- -------------- ------------------- -------
MV1 4838577 4838577 TABLE VALID
MV1 4838579 MATERIALIZED VIEW VALID


SQL> alter MATERIALIZED VIEW MV_MOVEMENT_PROCESSES compile;

SQL> execute dbms_mview.refresh('MINE1.MV_MOVEMENT_PROCESSES');
PL/SQL procedure successfully completed.

Sunday, August 21, 2011

Im Back!!

Hi All,

I m back to my Blog after a long gap.
You will see my intensive knowledge sharing all my experience in the recent past.

Your Suggestions & Contributions are most welcome!!!


Happy Knowledge Sharing.....

Thursday, January 8, 2009

Why there is a diff btwen DBA_SEGMENTS & DATAPUMP of particular schema size

Why there is a difference between DBA_SEGMENTS & DataPUMP of size of particular schema
======================================================================================

1. SELECT SUM(bytes)/1024/1024/1024 AS size_gig FROM DBA_SEGMENTS WHERE OWNER= 'XXX';

2. $ expdp estimate_only=y schemas=xxx

In Common, data pump export would show less value than the size computed from dba_segments/extents


ESTIMATE_ONLY - Use this parameter to estimate the required export file size without starting an actual export job.
ESTIMATE - The ESTIMATE parameter will tell you how much space your new export job is going to consume.
Estimate_only shows the export dump file size not a schema size.

Also, DBA_SEGMENTS contains INDEX segments too,

So, The following query may give you close result

select sum(bytes)/1024/1024/1024 as size_gig, segment_type
from dba_segments
where owner='ENDUR01'
group by segment_type;

Wednesday, January 7, 2009

How to get the Schema/User size

In SQLPLUS run any of the following scripts to find the total size occupied by a particular schema.


1.

SQL> SELECT sum(bytes)/1024/1024 FROM user_segments;


The output above query would be similar as follows:


SUM(BYTES)/1024/1024
——————–
2552.75


-- OR --

2.

SQL> SELECT tablespace_name, Sum(bytes)/1024/1024 AS total_size_mb
FROM dba_segments
WHERE owner = Upper(&User_Name)
GROUP BY owner, rollup(tablespace_name)


-- OR --

3. To be more precise on particular table which excluding index,lob segments etc

SQL> SELECT sum(bytes)/1024/1024/1024 as size_gig, segment_type
FROM dba_segments
WHERE owner='XXX'
GROUP BY segment_type;

Wednesday, December 31, 2008

RAC for Beginners

Real Application Clusters (RAC)


Oracle RAC, introduced with Oracle9i, is the successor to Oracle Parallel Server (OPS). Oracle RAC allows multiple instances to access the same database (storage) simultaneously. RAC provides fault tolerance, load balancing, and performance benefits by allowing the system to scale out, and at the same time since all nodes access the same database, the failure of one instance will not cause the loss of access to the database.

Oracle RAC 10g is a shared disk subsystem. All nodes in the cluster must be able to access all of the data, redo log files, control files and parameter files for all nodes in the cluster. The data disks must be globally available in order to allow all nodes to access the database. Each node has its own redo log file(s) and UNDO tablespace, but the other nodes must be able to access them (and the shared control file) in order to recover that node in the event of a system failure.

The difference between Oracle RAC and OPS is the addition of Cache Fusion. With OPS a request for data from one node to another required the data to be written to disk first, then the requesting node can read that data. With cache fusion, data is passed along a high-speed interconnect using a sophisticated locking algorithm.

With Oracle RAC 10g, the data files, redo log files, control files, and archived log files reside on shared storage on raw-disk devices, a NAS, ASM, or on a clustered file system

Oracle RAC is composed of two or more database instances.
They are composed of Memory structures and background processes same as the single instance database.

Oracle RAC instances use two processes

==> GES(Global Enqueue Service)
==> GCS(Global Cache Service) this enable cache fusion.

Oracle RAC instances are composed of following background processes:

ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor


LMON

The background Global Enqueue Service Monitor (LMON) monitors the entire cluster to manage global resources. LMON manages instance and process failures and the associated recovery for the Global Cache Service (GCS) and Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources. LMON-provided services are also known as cluster group services (CGS)

This process monitors global enques and resources across the cluster and performs global enqueue recovery operations.This is called as Global Enqueue Service Monitor.


LCKx

The LCK process manages instance global enqueue requests and cross-instance call operations. Workload is automatically shared and balanced when there are multiple Global Cache Service Processes (LMSx).

This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as libry and row cache requests.

LMSx

The Global Cache Service Processes (LMSx) are the processes that handle remote Global Cache Service (GCS) messages. Current Real Application Clusters software provides for up to 10 Global Cache Service Processes. The number of LMSx varies depending on the amount of messaging traffic among nodes in the cluster. The LMSx handles the acquisition interrupt and blocking interrupt requests from the remote instances for Global Cache Service resources. For cross-instance consistent read requests, the LMSx will create a consistent read version of the block and send it to the requesting instance. The LMSx also controls the flow of messages to remote instances.

This process is called as Global Cache service process.This process maintains statuses of datafiles and each cahed block by recording information in a Global Resource Dectory(GRD).This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances.This processing is a part of cache fusion feature.


LMDx

The Global Enqueue Service Daemon (LMD) is the resource agent process that manages Global Enqueue Service (GES) resource requests. The LMD process also handles deadlock detection Global Enqueue Service (GES) requests. Remote resource requests are requests originating from another instance.

This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.

DIAG

The diagnose daemon is a Real Application Clusters background process that captures diagnostic data on instance process failures. No user control is required for this demo.

ACMS

ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates are globally committed on success or globally aborted in event of a failure.

GTX0-j

The process provides transparent support for XA global transactions in a RAC environment.The database autotunes the number of these processes based on the workload of XA global transactions.

RMSn

This process is called as Oracle RAC management process.These pocesses perform managability tasks for Oracle RAC.Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.

RSMN

This process is called as Remote Slave Monitor.This process manages background slave process creation andd communication on remote instances. This is a background slave process.This process performs tasks on behalf of a co-ordinating process running in another instance.

CRS

CRS (Cluster Ready Services) is a new feature for 10g Real Application Clusters that provides a standard cluster interface on all platforms and performs new high availability operations not available in previous versions. CRS manages cluster database functions including node membership, group services, global resource management, and high availability. CRS serves as the clusterware software for all platforms. It can be the only clusterware or run on top of vendor clusterware such as Sun Cluster, HP Serviceguard, etc.


CRS automatically starts the following resources:
· Nodeapps
o Virtual Internet Protocol(VIP) address for each node
o Global Services Daemon
o Oracle Net Listeners
o Oracle Network Services (ONS)
· Database Instance
· Services


Oracle Clusterware (Cluster Ready Services in 10g/ Cluster Manager in 9i) - provides infrastructure that binds multiple nodes that then operate as single server. Clusterware monitors all components like instances and listeners. There are two important components in Oracle clusterware, Voting Disk and OCR (Oracle Cluster Registry)

OCR & Voting Disk

Oracle, 10g RAC, provided its own cluster-ware stack called CRS. The main file components of CRS are the Oracle Cluster Repository (OCR) and the Voting Disk.
The OCR contains cluster and database configuration information for RAC and Cluster Ready Services (CRS). Some of this information includes the cluster node list, cluster database instance-to-node mapping information, and the CRS application resource profiles. The OCR contains configuration details for the cluster database and for high availability resources such as services, Virtual Inerconnect Protocoal (VIP) addresses.

The Voting Disk is used by the Oracle cluster manager in various layers. The Node Monitor (NM) uses the Voting Disk for the Disk Hearbeat, which is essential in the detection and resolution of cluster "split brain".


Cache Fusion:-

Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block,it is easy to get the block image from the insatnce which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cahce fusion

Cache Fusion and Global Cache Service (GCS)
Memory-to-memory copies between buffer caches over high-speed interconnects

· fast remote access times
· memory transfers for write or read access
· transfers for all types (e.g data, index, undo, headers )
· Cache coherency across the cluster
· globally managed access permissions to cached data
· GCS always knows whether and where a data block is cached
· a local cache miss may result in remote cache hit or disk read

@@@ Article is still under edit... will be adding more info @@@@

Monday, December 29, 2008

11g RAC



Oracle Real Application Clusters

Learn More about 11g RAC and its New Features, Refer the following ORACLE official Site for 11g RAC

Oracle Real Application Clusters 11g

Friday, December 26, 2008

The Courseware,materials * Guides needed for OCA/OCP/OCE Exam Preparation

Oracle Certifications are one of most recognized and value certification to acquire to add more weightage to your profile.

These Certifications not only provides you knowledge and also gives better scope to get good opportunities in the Industry

The experience and recognition i am getting on these certifications is immensive.
I am certified on Oracle 9i and 10g OCA/OCP/OCE and 10g OCM on Oracle Database Product.
So, The following information on these certification may useful to many people who aspire to build career in ORACLE Technology.

The following tips may be useful to attain these certifications:

1. First and Foremost thins is, You need to attend the Oracle University Course to
understand the technology and concepts of Oracle Database for particular Version.

2. It is suggested to use the Oracle official Material/Student Guide to get better
understanding of the concepts and features

3. The Course Material / Oracle ITL materials have set of Theory & Practical
questions which would help you understand & Test your knowledge on the product
before proceeding for OCP/OCE Certifications.

4. The Oracle course ware are made in simple english and very easy to understand,
and have very précised and handy information.

5. You may also practice exams of Self Test Software and Transcender are
recommended for preparing the OCA/OCP/OCE Exams.

6. The OCA/OCP/OCE Exams are Objective Type, so it is easy to choose the right
answer for each question

7. Except OCM, remaining all Oracle Certifications are objective type which are
binded with some practical related questions but there is no practical involved

8. The OCP/OCE questions are very tricky and confusing one each answers, so it is
advised to understand the concepts better to choose the right answer

9. DO NOT mug up or Copy the practice exam questions and answers, instead try
understanding the question and the answer.

Here is link for Oracle Student Guides/ILT (Instructor Led Training) Materials

# Where to get Student Guides/ILT (Instructor Led Training) Materials

# To know more about ORACLE Database OCA/OCP/OCE on all Database versions

# ORACLE Suggests ==> Self Test Software and Transcended Test engines

# For any suggestions, tips, experience of Others on OCA/OCP/OCE
Refer OCP Blog ==> ORACLE CERTIFIED PROFESSIONALS

# OTN Forum for Oracle Certifications

# Oracle Magazine Inside OCP Columns

# Oracle Certification Preparation (OCP)/Hidden Treasures

All the Best !! for better career through ORACLE!
Blog me anytime for any tips/suggestions :)

Oracle Database OCA OCP OCE OCM




Oracle Certification Program - Oracle Database

Oracle Database 11g
Oracle Database 10g
Oracle9i Database
News and Information

Database and Grids

The Courses and Certifications Offered for Database and Grids

Select a topic to view a course listing and its related categories.

* Oracle Database 11g
* Oracle Database 10g
* 9i Database
* 8i Database
* Oracle Enterprise Manager 10g

* Oracle Enterprise Manager 9i
* Oracle Application Testing Suite
* 9i Lite
* Oracle Berkeley
* Oracle TimesTen


For More Details, Refer the Official ORACLE Education Link ==> Database and Grids

Oracle Certification

The following Link is official Oracle Site for all ORACLE Product Certification and courses offered by Oracle University

ORACLE UNIVERISTY - Courses & Certifications on all ORACLE Products