How to enable trace for an already running database session?

There are many cases when we are troubleshooting a long running database session.

We may want to enable trace for the already running session to identify what is causing it to run such long.

We will need to first find the SPID for the session we want to enable trace.

Query the spid from v$process using the addr from v$session for the SID.

Once you have the SPID, use the below commands to enable trace for it.

sqlplus "/ as sysdba"

oradebug setospid [SPID]

oradebug unlimit

oradebug dump errorstack 3

The above commands will generate the trace for the session. We can identify the exact name of trace file from v$process.

MWA Multiple Dispatchers Not Working And MWA Server Manager Shows Stopped Status

We notice that, all MWA servers are registering with the first Dispatcher and ignoring any other dispatchers which are up and running.

While starting, we might see messages like below:

MWA Telnet Server Release: 1.0.8.4 [December 12th 2002]
Port Number Dispatcher Will Be Started On: 10763
Port to use for this dispatcher: 10763
Port has probably been taken.
Error starting the Dispatcher.

Or The dispatcher logs might show like below:

Log from first Dispatcher
-------------------------
[Wed Oct 28 16:19:38 2020] runRegistrar: server registry full
[Wed Oct 28 16:19:38 2020] runRegistrar: server registry full
[Wed Oct 28 16:19:38 2020] runRegistrar: server registry full

 Log from second Dispatcher
-----------------------------
[Wed Oct 28 16:17:08 2020] Port to use for this dispatcher: 10300
[Wed Oct 28 16:17:08 2020] main: got local host name 'atgmwa.us.oracle.com'
[Wed Oct 28 16:17:08 2020] main: listener server socket created
[Wed Oct 28 16:17:08 2020] main: created worker threads with connections
[Wed Oct 28 16:17:08 2020] main: created registrar thread
[Wed Oct 28 16:17:08 2020] runRegistrar: registrar server socket created
[Wed Oct 28 16:17:39 2020] main: no servers available
[Wed Oct 28 16:19:42 2020] main: no servers available
[Wed Oct 28 16:19:47 2020] main: no servers available

 It could be happening when you try to configure multiple dispatchers.

Solution:

 1) Shut down all the MWA services including dispatcher

2) Navigate to $ADMIN_SCRIPTS_HOME

cd $ADMIN_SCRIPTS_HOME

3) Edit the mwactl.sh as follows:

FROM:
${MWA_TOP}/bin/MWADIS

TO:
${MWA_TOP}/bin/MWADIS -p "$2"

4) Start MWA services and start dispatchers as below

mwactl.sh start_dispatcher  <port#> &  

How to Configure multiple (MSCA) MWA dispatchers in EBS?

At times, we get a requirement to configure multiple dispatchers or additional ports for (MSCA) MWA in EBS. We can follow below steps to do the same.

Identify the file mwa.cfg located under $INST_TOP/admin/install.

Update the following fields in mwa.cfg file.

#Update the following parameter with the MWA ports you want to use for telnet. Example below:

mwa.TelnetPortNumber=10220,10222,10224,10226,10228,10230,10232,10234,10236,10238,10240,10242,10244,10246,10248,10250,10252,10254,10256,10258

# Specify the port number and machine for running the dispatcher
# For eg. mwa.Dispatcher = ap100sun:2300
mwa.Dispatcher=locahost:10753,localhost:10763

We need to enter the dispatcher ports we want to use in the mwa.Dispatcher parameter in mwa.cfg file as mentioned in above example.

Now, once this is done, go to $ADMIN_SCRIPTS_HOME and modify the mwactl.sh file as below:

FROM:
${MWA_TOP}/bin/MWADIS

TO:
${MWA_TOP}/bin/MWADIS -p "$2"

Note: If we do not update mwactl.sh with above change, dispatcher will start on only one port. 

To retain these changes and to avoid being overwritten during autoconfig, perform the below.

Go to $MWA_TOP/admin/template

create a folder called custom 

copy the files mwa.cfg and mwactl.sh to custom folder.

Edit them as mentioned in above steps. This will make sure the autoconfig would still retain the changes we have done.

Wipro and Oracle which is the top most companies?

I came across this question as one of the most asked questions.

Wipro and Oracle which is the top most companies.

Wipro is having good brand value and reputation in many countries. But when compared to Oracle, it may be small.

The reason is Oracle is a product based company and its product is more widely used. While Wipro has many divisions. It has product development, other manufacturing and Services selling.

In terms of revenue and number of employees etc, Oracle is more in number than Wipro. 

Moreover, Oracle is a global brand and Wipro is a domestic(Indian) brand. 

Each will have its own set of policies, procedures etc in terms of operations, employment, business etc.

Hope this answers.

How to find apps password in Oracle Apps 11i?

In Oracle 11i, there are 2 files where the Apps user password in hard-coded in the file system.

In case you have access to the middle tier, navigate to below files and look for them.

1) Navigate to $IAS_ORACLE_HOME/Apache/modplsql/cfg

 Look for wdbsvr.app file.

[DAD_DBNAME]
connect_string  = DBNAME
password        = apps_password
username        = APPS
default_page    = fnd_web.ping
document_table  = APPS.fnd_lobs_document
document_path   = docs

As highlighted above, the password will be stored in this file under the header [DAD_DBNAME].

2) Other location where you can find is; Navigate to $ORACLE_HOME/reports60/Server.

Look for file named CGIcmd.dat 

When we open the file, we should see a line like below where you can see apps password hard-coded.

DBNAME_APPS:server=REP60_DBNAME userid=APPS/apps_password@DBNAME destype=cache desformat=html tolerance=0 traceopts=trace_prf tracefile='' errfile='' profile='' logfile='' keyout='' copies=1 cellwrapper='' delimiter='' distribute=no save_rdf=null customize=null cmdfile=null term=null rundebug=no keyin=null %*

Note:

Remember this article is applicable only for Oracle EBS 11i. Not applicable for R12.

How to prepare for Oracle Apps DBA interview?

Many times I see that, whenever people changing job/organization they are confused where to start preparing for interview and how to start and what all should I read.

Going through this article will help to get an answer to your questions.

Anyone who wants to prepare for interview, first read and understand each and every point you mentioned in your profile.

For ex. I see most of the DBAs they mention such as "Worked on Dataguard". 

Here you should understand what all a interviewer can ask you related to Dataguard. Say, 

1) What is Dataguard?

2) What is it used for?

3) What is Dataguard Broker?

4) How to configure Dataguard broker?

Similarly, one would mention "Worked on Database Upgrade". 

In this case, you should know what all things needs to be done (Pre-Upgrade, Upgrade, Post-Upgrade) in detail. If you say, I used DBUA and it upgraded by itself, then there is nothing you learned. 

You should even know how to upgrade manually (incase you are not able to use DBUA).

Basically, you need to go through each and every point you mentioned in your profile and read and understand the points you mentioned in your profile.

By the time, you prepare yourself with all the points you mentioned in your profile you pretty much covered everything. 

So this means that, the starting point of your interview preparation should be from what you have written in your Resume/Profile.

Hope this helps.

How to join as a contractor in Oracle?

I would say, Joining Oracle as a contractor is little easier than joining as a full time employee. Usually Oracle hires contractors for temporary assignments most of the time (for example, migration or upgrade project which lasts for 6-9 months).

People wonder why companies hire contractors. 

The first and foremost reason, contractors are easy to hire, faster on boarding with less formalities and easy to get rid off as soon as the project/assignment completes for which they were hired for.

Now coming to the original question, how to join Oracle as a contractor. 

1) Profile must be updated regularly on the job sites/career portals.

2) Prepare well for the interview (Technically and Professionally)

3) Keep all the relevant and required documents ready for faster on-boarding process.

4) Keep in contact with the Vendor on whose payrolls you are hired for the process.

Keep in mind that once hired as a contractor, if you showcase your skills there are high chances of getting into permanent roles of Oracle easily.

Types of Database objects an Oracle DBA should know

I have been working as a Oracle DBA / Oracle Apps DBA for more than 15 years for now. Offlate I have realized that many of the DBAs dont know the basic things such as different object types available in Oracle Database.

When I ask any of the DBAs; what are the different types of objects that they know in Oracle, most people mention about Tables and Indexes and they stop. A few go a little beyond and mention about Views and Packages. A few talk about synonyms. But most of them just know their (object) names but do not know what is its purpose. We will talk about it a little today in this post.

Most Widely used objects that we use everyday as a DBA:

1) Tables -Basic unit of data storage in Database

2) Indexes - Contains an entry for each value that appears in indexed column of the table

3) Synonyms - Synonyms are alias for other objects. Usually we create synonyms to access objects which we have access to; without a prefix.

4) Views - In simple terms, we can define view as a virtual object which does not occupy space, it does not store anything. We can create a view to query specific columns from multiple tables and display.

5) Materialized View - MV is similar to a view where specific columns from multiple tables is queried and it stores data within it. It will have data as of the creation time. To get latest data from tables into MV, we refresh them.

6) Procedure - A procedure is a group of PL/SQL statements that you can call by name. 

7) Function - Similar to Procedure, its a subprogram (group of PL/SQL statements) which computes and must return a value.

8) Package - A package is a schema object that contains definitions for a group of related functionalities. A package includes variables, constants, cursors, exceptions, procedures, functions, and subprograms. It is compiled and stored in the Oracle Database. Typically, a package has a specification and a body.

There are more object types. But the above mentioned are the very basic every DBA must be aware of.