Wednesday 19 April 2017

How To Compile JSP Manually in R12

Hi DBA-Mates,
We almost time face an issue such as EBS R12 Login Page showing a blank page. This case we face almost after cloning or refresh. As we open the URL of the R12, its loading for a while and goes to a blank page, after few continuous attempts, it may show the login page or it may not also.

Just, some days ago one of our friend asked me about JPS compile details in R12. He was not clear about compiling JSP pages as how to do and when to do but hope these points may clear here. This is also a type of clearing cache in application EBS R12.

Actually this issue is caused by either missing or corrupted class files under EBS Cache $COMMON_TOP/_pages.

We can check this error on the oacore application.log file
javax.servlet.ServletException: oracle.classloader.util.AnnotatedClassNotFoundException:
Missing class: _AppsLocalLogin
Dependent class: oracle.jsp.runtimev2.JspPageInfo
Loader: oc4j:10.1.3
Code-Source: /ORA-DATA/apps/tech_st/10.1.3/j2ee/home/lib/ojsp.jar
Configuration: in META-INF/boot.xml in /ORA-DATA/apps/tech_st/10.1.3/j2ee/home/oc4j.jar

It was initiated at oacore.web.html.jspxxxxx:0.0.0 using the loadClass() method.
And the missing class is not available from any code-source or loader in the system.
html: Error initializing servlet java.lang.NoClassDefFoundError: Could not initialize class oracle.apps.fnd.profiles.Profiles

For this above issue or problem, we can try this below work-around which has worked in real time.

Please follow the below action plan steps:

1. We need to login to apps server and su to apps user (applmgr).

2. Then we need to Shutdown the application services.

3. Now, compile the jsp's manually by using the command as shown below:
$cd $COMMON_TOP/
$rm _pages   [here, either you can remove or take backup]
$mkdir _pages
$chmod 755 _pages  [this may be automatically]

$perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2

4. Then check whether all the jsp's are getting compiled successfully.

5. Restart the application services.

6. Open IE or your web browser and clear the browser cache.


7. Then again retest the issue. 

How to check ojspCompile.pl version:
$ adident Header ojspCompile.pl
ojspCompile.pl:
$Header ojspCompile.pl 120.10 2007/11/16 04:50:02 sbandla ship $


Some more useful links:
R12.2 Concepts and Architecture
Jobs Tips
Real Time Experiences
I was busy a lots today but I promised for today to post, So I am doing this night time.
Hope above points may helpful and useful. For any kind information or suggestions, Please write to either in comment box or contact us @https://ora-data.blogspot.in/
Thanks,

Friday 14 April 2017

Oracle Deadlocks


Deadlock in oracle
In a database, when a session (e.g. ora) wants a resource held by another session (e.g. data), but that session (data) also wants a resource which is held by the first session (ora). There can be more than 2 sessions involved also but idea will be the same.
Actually, Deadlocks prevent some transactions from continuing to work.
For example:
Suppose, ORA-DATA holds lock A and requests lock B
And SKU holds lock B and requests lock A.
There are two types of deadlocks:
i.e. a potential deadlock or an actual deadlock.
Potential deadlock: It does not occur necessarily in a given run but it can occur in any execution of program which depending on the scheduling of the thread and timing of locks requests by the threads.
Actual deadlock: It is one of deadlock which occurs during the execution of a program. Because of this deadlock processes hang, it may hang either whole processes or one process.

 

Deadlock Avoidance

Deadlock can be reduce or avoid by avoid locking contention. We should also make sure that all programs access the data in orders. Sometimes if database application design is not proper that may also cause this issue, so database design should be proper way.

From Alert Log:
Mon Apr 10 19:40:59 2017
ORA-00060: Deadlock detected. More info in file /opt/SKUdb/9.2.0/admin/SKU/udump/ora-data_ora_172589.trc.
Mon Apr 10 20:07:21 2017

From Trace File:
DEADLOCK DETECTED ( ORA-00060 )
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-008f0003-0008de0d       190     286     X             25     585           X
TX-00fc0012-0008bc4c        25     585     X            190     286           X
session 286: DID 0001-00B4-0000116F     session 585: DID 0001-001A-00000A4E
session 585: DID 0001-001A-00000A4E     session 286: DID 0001-00B4-0000116F
Rows waited on:
Session 585: obj - rowid = 0015E796 - AAnDcRALTAAByX3AAp
  (dictionary objn - 1435542, file - 723, block - 468471, slot - 42)
Session 486: obj - rowid = 0015E78F - AAnDZGAJfAACPikAAz
  (dictionary objn - 1435535, file - 607, block - 587940, slot - 52)
Information on the OTHER waiting sessions:
Session 585:
  pid=11 serial=1943 audsid=1423567819 user: 192/APPS
  O/S info: user: kmisadm, term: , ospid: 253647, machine: ora-data
            program:
  client info: 1920                                                   0
  application name: ora-data.blogspot.com, hash value=1925436782
  action name: Concurrent Request, hash value=1925436782
  Current SQL Statement:

Checking:
To check the deadlock details, Please find the below points:
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORA-DATA         OPEN

SQL> set lines 32
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
  id1, id2, lmode, request, type
 FROM V$LOCK
WHERE (id1, id2, type) IN
 (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

no rows selected

SQL> /

no rows selected

SQL> /
In above command, there is no row selected, it means there is no locking or deadlock at present. It may clear automatically. So, no need to worry about this.
And if we have still active session or getting output from above query then there is simple work-around or solution as:
Identify the particular user and session details which causing the blocking and then find the details about the session as what session is running or doing then contact to particular user and ask for killing the session if nothing important session is working from below command:
SQL>sqlplus ‘/as sysdba’
SQL>ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’;
SQL>
Then again check the details from above command for locks or waits.
Some more useful link:
Hope above information useful and helpful. Please let us know for any concerns or suggestions either in comment box or contact us @https://ora-data.blogspot.in/

Thanks,

Tuesday 11 April 2017

How a user interact with an Oracle Database

Hi DBA-Mates,
If we want to learn and understand about oracle database, we should know how the user and database interact with each others. How a user interacting with an Oracle Database?

This example will describes the Oracle database operations at the most basic level. It illustrates an Oracle database configuration in which the user and associated server process are on separate computers, connected through a network.

1. Suppose, an instance has started on a node where Oracle Database is installed, that is also called the database server or host.

2. And a user starts an application spawning or start a user process. The application attempts to establish a connection to the server. This connection can be a local, client/server, or a three-tier connection from a middle tier.

3. Now, the server runs a listener that has the appropriate Oracle Net Services handler. And as we know that the listener detects the connection request from the application and creates a dedicated server process on behalf of the user process.

4. Suppose, a user runs a DML statement command and then commits the transaction. As example, the user changes the salary of a customer in a table and commits the changes.

5. Here, the server process receives the statement and checks the shared pool (an SGA component) for any shared SQL area that contains an identical SQL statement.
If a shared SQL area is found, then the server process checks the user’s access privileges to the requested data, and the existing shared
SQL area is used to process the statement.
And if a shared SQL area is not found, a new shared SQL area is allocated for the statement so that it can be parsed and processed.

6. After checking above details, the server process retrieves any necessary data values, either from the actual data file (table) or from values stored in the Database buffer cache.

7. Then the server process modifies data in the SGA. Because the transaction is committed, the LogWriter process (LGWR) immediately records the transaction in the redo log file.
As we all know that Database Writer process (DBWn) writes modified blocks permanently to disk when it is efficient to do so.

8. Now, if the transaction is successful, the server process sends a message across the network to the application. And if the transaction is not successful, an error message is transmitted.

For kind information, we have mentioned only top level orimportant steps and because throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users’ transactions and Prevents contention between transactions that request the same data.


Hope these may also helpful:







Hope above points is cleared. Please let us know for any concerns or suggestions either through comments or contact @ora-data.blogspot.com.
Remember, we would love to hear from you.

Thanks,

Tuesday 4 April 2017

Crontab Concepts and details with examples

                               Crontab Concepts and details:
Hey BDA-Mates,
As we know that CRON is a Linux program and it is very helpful and useful for a programmer or DBA.

Crontab allows a task to be run automatically in background at mentioned date/time.
For example: 
We can schedule cron for backups, schedules update, synchronize files, Getting messages for completion tasks, getting alerts such as tablespace size details, blocking sessions, temp usage etc.

There are 5 entries in crontab.

For example:
1 2 3 4 5 /bin/ora-data.blogspot.com/scriptname.sh

Here:
1 : Minute (from 0 to 59)
2 : Hour (from 0 to 23)
3 : Date of the month ( from 1 to 31)
4 : Month ( from 1 to 12)
5 : Day of week (from 0 to 7, here 0 and 7 are Sunday)

If we want to check which crontabs are currently running on our server? We have to login to our server and run the below command:

$hostname;id
ora-data.blogspot;ora-data

$crontab -l
1 2 3 4 5 /bin/ora-data.blogspot.com/scriptname.sh

If you want to make changes or edit in crontab, follow below command:

$crontab -e
1 2 3 4 5 /bin/ora-data.blogspot.com/scriptname.sh

From above command, we will get all the crontab entries in which we have to find out the script name and edit or change the date and time as per your requirements, Save and then exit.

Note: Always be careful during edit the crontab on PROD.

Please find the below examples for references:

1. Suppose we want to execute any scripts every minute then we can use below commands:

* * * * * /bin/ora-data.blogspot.com/scriptname.sh

Here, * asterisk or start means every. So, this above command will run for every minute, every hour, and every date of month, every month and every day of week.

2. Example for execute on Friday at 7 AM, each date and every month.

0 7 * * 5 /bin/ora-data.blogspot.com/ora-data.sh

3. Suppose, we want to execute our scripts only on Friday and Saturday at 5 AM.

0 5 * * 5-6 /bin/ora-data.blogspot.com/ora-data.sh

4. Suppose, we want to execute at every 10 min.

0,10,20,30,40,50 * * * * /bin/ora-data.blogspot.com/ora-data.sh

5. Suppose, we want to check a particular running crontab.

$crontab -l |grep crontabname

Some more useful links:

Hope this will be useful and helpful. For any suggestion please write to us either in comment box or @Contact us: https://ora-data.blogspot.in/

Regards,
ora-data Team

Monday 3 April 2017

ORA-609 with TNS-12537: TNS:connection closed



          ORA-609 with TNS-12537: TNS:connection closed
We almost time receive an error such as ora-609 which shows in alert log as

TNS- TNS-12537: TNS:connection closed
        ns secondary err code: 12560
        nt main err code: 0
        nt secondary err code: 0
        nt OS err code: 0
    opiodr aborting process unknown ospid (12345) as a result of ORA-609

This error means could not attach to incoming connection, so our database process was aborted or closed because incoming connection was not passed to it by listener. In our case we found sqlnet error stack as shown below
 TNS-12537: TNS:connection closed

It means the dedicated process didn’t have a client connection anymore to work.

As we all know the process of client connections:

1. First Client initiates a connection to the database so it connects to the listener.

2. Then listener starts a dedicated DB process that will receive this connection or session.

3. After that, this dedicate process is started and listener passes the connection from the client to this process.

4. Then the sever process takes the connection from the listener to continue the handshake with the client.

5. After that Server process and client exchange messages or information required for establishing a session or user logon.

6. Then the session is opened now.


In our case, it may be when the dedicated process tries to communicate with the client it finds that connection was closed.

Solution or Work around:

We can eliminate this error by increasing the value of SQLNET.INBOUND_CONNECT_TIMEOUT in sqlnet.ora file. 

By default the SQLNET.INBOUND_CONNECT_TIMEOUT value is 60 seconds.

The sqlnet.ora file can be found in RDBMS_HOME/network/admin.

For example:

$ cd $ORACLE_HOME/network/admin

$vi sqlnet.ora

And change the value of SQLNET.INBOUND_CONNECT_TIMEOUT as:

SQLNET.INBOUND_CONNECT_TIMEOUT=300

Then save the file.

There are many more possibilities which can cause this error such as:

1. This can happen suppose our client closed the connection immediately after initiating it.

2. This may occur when firewall kill the connection.

3. Sometimes, it may happen because of Database, OS or network performance problem.

4. This error may occur when some client crashes.


Hope this may also useful here:
Hope this may useful and helpful. Please let us know for any concerns either by below comments or write to @contact us: https://ora-data.blogspot.in/
Regards,
ora-data Team