Query to find Pending Requests Against Output Post Processor

1 comment
Below query can be used to find the pending requests again OPP.

Query:

select REQUEST_ID,PHASE_CODE,STATUS_CODE from fnd_concurrent_requests
where request_id in (
select concurrent_request_id
from fnd_conc_pp_actions
where action_type >= 6
and processor_id is null)
and PHASE_CODE!='C';

1 comment :

Post a Comment

Oracle Apps Login page not coming in R12.2

No comments
On Oracle Applications 12.2.4 and higher , login page is not coming and  errors with HTTP 500

Port number in WebLogicCluster parameter specified in httpd.conf is not an integer less than 65535, or servers are not specified correctly. Cannot

The issue can be reproduced at will with the following steps:
1. Log into Applications.
2. Error occurs.


CAUSE
An incorrect NULL or wrong entry in the mod_wl_ohs.conf.

SOLUTION
1. Please compare following files with a working instance:
{ORACLE_INSTANCE}/config/${COMPONENT_TYPE}/${COMPONENT_NAME}/mod_wl_ohs.conf

Especially for section:

  SetHandler weblogic-handler
  WLCookieName JsessionIDOAFM
  WebLogicCluster null
  WLTempDir ${ORACLE_INSTANCE}/tmp
  
 2. Change the WebLogicCluster to the correct value and re-test.

No comments :

Post a Comment

Stopping and Starting RAC Services

No comments



STOP ORACLE RAC

1. emctl stop dbconsole

2. srvctl stop listener -n racnode1

3. srvctl stop database -d RACDB

4. srvctl stop asm -n racnode1 -f

5. srvctl stop asm -n racnode2 -f

6. srvctl stop nodeapps -n racnode1 -f

7. crsctl stop crs

 

START ORACLE RAC

1. crsctl start crs

2. crsctl start res ora.crsd -init

3. srvctl start nodeapps -n racnode1

4. srvctl start nodeapps -n racnode2

5. srvctl start asm -n racnode1

6. srvctl start asm -n racnode2

7. srvctl start database -d RACDB

8. srvctl start listener -n racnode1

9. emctl start dbconsole



Stop Oracle Real Application Clusters 

1. Stop Oracle Home LISTENER

[grid@racnode1 bin]$ srvctl stop listener -n racnode1

[grid@racnode1 bin]$ srvctl status listener -n racnode1
Listener LISTENER is enabled on node(s): racnode1
Listener LISTENER is not running on node(s): racnode1


2. Shutdown RAC Database Instances on all nodes

Syntax: srvctl stop database -d {databasename}

[oracle@racnode2 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node racnode1
Instance racdb2 is running on node racnode2
[oracle@racnode2 ~]$ srvctl stop database -d racdb

[oracle@racnode2 ~]$ srvctl status database -d racdb
Instance racdb1 is not running on node racnode1
Instance racdb2 is not running on node racnode2

Execute one command from any one of the server having database and it will stop all database instances on all servers. 


3. Shutdown All ASM instances from all nodes

Syntax: srvctl stop asm -n {node}

[grid@racnode2 oracle]# srvctl stop asm -n racnode1 -f

[grid@racnode2 oracle]# srvctl stop asm -n racnode2 -f

Check Status

[grid@racnode2 oracle]# srvctl status asm -n racnode1
ASM is not running on racnode1

[grid@racnode2 oracle]# srvctl status asm -n racnode2
ASM is not running on racnode2

Sometimes, we may face some issues in stopping ASM instance, In that case use "-f" option to forcefully shutdown ASM instances.


4. Shutdown Node applications running on nodes

[grid@racnode2 oracle]#  srvctl stop nodeapps -n racnode1 -f

[grid@racnode2 oracle]# srvctl status nodeapps -n racnode1 
VIP racnode1-vip is enabled
VIP racnode1-vip is running on node: racnode1
Network is enabled
Network is running on node: racnode1
GSD is disabled
GSD is not running on node: racnode1
ONS is enabled
ONS daemon is running on node: racnode1

Repeat same command for all nodes one by one. If you face any issue in stopping node applications use "-f" as force option to stop applications.


5. Shut down the Oracle Clusterware or CRS In this command all CRS related process will be stopped. 
This  needs to be executed by "root" user on all database nodes.

[root@racnode1 bin]# crsctl check cluster -all

**************************************************************
racnode1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

*************************************************************
racnode2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

[root@racnode1 bin]# crsctl stop crs

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1'
CRS-2673: Attempting to stop 'ora.crsd' on 'racnode1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'racnode1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'racnode1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'racnode1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'racnode1'
CRS-2673: Attempting to stop 'ora.racnode2.vip' on 'racnode1'
-------------------------------------------------
-------------------------------------------------
-------------------------------------------------
CRS-2677: Stop of 'ora.cssd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'racnode1'
CRS-2677: Stop of 'ora.gipcd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'racnode1'
CRS-2677: Stop of 'ora.gpnpd' on 'racnode1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[root@racnode1 bin]# crsctl check cluster -all

CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Check failed, or completed with errors.


------------------------------------------------------------------------------------------------------------------------

Start Oracle Real Application Clusters Database 



1. Start Oracle Clusterware or CRS: CRS starts automatically when you start or restart Server
This  needs to be executed by "root" user on all nodes.

[root@racnode1 bin]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started 
[root@racnode2 bin]# crsctl check cluster -all
**************************************************************
racnode1:
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

racnode2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

If we see "CRS-4639: Could not contact Oracle High Availability Services" or "CRS-4535: Cannot communicate with Cluster Ready Services" messages. Wait 5 minutes and then again check with "crsctl check cluster -all" command. This time Database administrator will get "CRS-4537: Cluster Ready Services is online". If still same issue DBA can start ora.crsd process to resolve this issue. Below is the command

[root@racnode1 bin]# crsctl start res ora.crsd -init

[root@racnode1 bin]# crsctl check cluster -all
**************************************************************
racnode1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

racnode2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

Now, Cluster is up and running. DBA can also use "ps -ef | grep d.bin" to check cluster status.


2. Start Node applications running on nodes

[grid@racnode1 bin]$ srvctl start nodeapps -n racnode1

[grid@racnode1 bin]$ srvctl status nodeapps -n racnode1
VIP racnode1-vip is enabled
VIP racnode1-vip is running on node: racnode1
Network is enabled
Network is running on node: racnode1
GSD is disabled
GSD is not running on node: racnode1
ONS is enabled
ONS daemon is running on node: racnode1

Execute this command for each node to start RAC Cluster database. 


3. Start All ASM instances from all nodes

[grid@racnode1 bin]$ srvctl start asm -n racnode1

[grid@racnode1 bin]$ srvctl status asm -n racnode1
ASM is running on racnode1

DBA has to start ASM instance on all database nodes.


4. Start RAC Database Instances on all nodes

[grid@racnode1 bin]$ srvctl start database -d racdb

[grid@racnode1 bin]$ srvctl status database -d racdb
Instance racdb1 is running on node racnode1
Instance racdb2 is running on node racnode2

Now database is up and running on both DB nodes.


5. Start Oracle Home LISTENER 

[grid@racnode1 bin]$ srvctl start listener -n racnode1

[grid@racnode1 bin]$ srvctl status listener -n racnode1
Listener LISTENER is enabled on node(s): racnode1
Listener LISTENER is running on node(s): racnode1

No comments :

Post a Comment

Query to find blocking sessions in RAC Database

No comments
Query:

select blocking_session,blocked_session,script from 
( select distinct 
s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session,
s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session,
decode(s1.type,'USER','alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' ,null)
script ,
count(*) over (partition by s1.inst_id,s1.sid) blocked_cnt
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.block > 0 and l2.request > 0
and l1.id1 = l2.id1 and l1.id2 = l2.id2 )
order by blocked_cnt desc;

No comments :

Post a Comment

Can Not Login To The Applications: oracle.apps.fnd.framework.OAException: FND_NO_TRANSACTION_ID error

1 comment
Error:

Login page not coming  in R12 and In oacore log I observed below error

<[136] oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_NO_TRANSACTION_ID.
        at oracle.apps.fnd.framework.webui.OAJSPHelper.getTransactionId(OAJSPHelper.java:352)
        at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:133)
        at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:78)
        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1324)
        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:567)
        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:455)


To implement the solution, execute the following steps:

Solution 1:

1. Check for any invalid objects in the database instance, by running for instance the following sql statements as a DBA user in sqlplus:

select owner, object_type, count(*)
from dba_objects
where status='INVALID'
group by owner, object_type;

select owner, object_name
from dba_objects
where status='INVALID'
order by owner;


Please reference Note 1325394.1 to address any invalid objects.


2. Run adadmin to compile the APPS schema:


3. Check for tablespaces free space, by running for instance the following sql statement as a DBA user in sqlplus:


select Total.name "Tablespace Name", nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space, total_space
from
 (select tablespace_name, sum(bytes/1024/1024) Free_Space
  from sys.dba_free_space
  group by tablespace_name) Free,
  (select b.name, sum(bytes/1024/1024) TOTAL_SPACE
   from sys.v_$datafile a, sys.v_$tablespace B
   where a.ts# = b.ts#
   group by b.name) Total
where Free.Tablespace_name(+) = Total.name
order by Total.name;


4. Ask the DBA to add space for the corresponding full tablespaces.

OR

Solution 2
Per Bug 17162246 : RANDOM ERROR FND_NO_TRANSACTION_ID WHILE OPENING VIEW DOCUMENT PAGE
 Please follow the below steps to schedule a concurrent request to purge the
 inactive sessions periodically (once in a week for high traffic instances):
 1. Log in using sysadmin.
 2. Navigate to Self Service Web Applications Manager, Vision Enterprises -->
 Requests --> Run
 3. In the Form that opens run/schedule "Purge Inactive Sessions" request.   This internally clears of the icx_transactions table entries also.



It is generally recommended to schedule "Purge Inactive Sessions" concurrent request to run at least weekly, but some high volume customers find it necessary to run on a more regular basis - nightly. This request runs the script $ICX_TOP/sql/ICXDLTMP.sql which deletes any information in ICX_SESSIONS (and its dependent tables) that is more than 4 hours old and alleviates the performance issues associated with letting these tables grow with obsolete data.


If the "Purge Inactive Sessions" request does not exist in your R12 instance, please review Note: 397118.1 Where Is 'Delete Data From Temporary Table' Concurrent Program - ICXDLTMP.SQL


Ref:Can Not Login To The Applications: oracle.apps.fnd.framework.OAException: FND_NO_TRANSACTION_ID error (Doc ID 737960.1)

1 comment :

Post a Comment

Database Installation and Setup - RAC 12c Setup Step by Step on Oracle Virtual Box with Oracle Enterprise Linux(OEL) - Final Chapter

3 comments
In this post, we are going to install the database and create a database using dbca.

Database Installation

Login as root user on racnode1
Execute below commands


Xhost +
Switch to oracle user
su oracle
Set the environment
. .bash_profile
  • Go to the database software directory.
  • Run the Oracle Universal Installer from database software directory.
           ./runInstaller

  • Once the OUI wizard would open and you see the following welcome screen.

  • Click Next.









Click on setup to make ssh connectivity between both nodes











Run the script from root user on both racnode1 and racnode2



Create Database

make sure you are logged in as oracle.
Go to oracle home/bin
Run ./dbca









This completes our 2 nodes RAC setup.




3 comments :

Post a Comment

GRID Installation - RAC 12c Setup Step by Step on Oracle Virtual Box with Oracle Enterprise Linux(OEL) - Chapter 5

No comments
In this post, we are going to configure grid.

I have created a shared location between my RAC nodes and the host system.I have downloaded the 12c grid software and database software and placed at the shared location.







Unzip the grid software from root user on racnode1

  • Navigate to the grid/rpm directory.
[root@racnode1 rpm]# pwd
/media/sf_RAC_Software/grid/rpm

  • Locate the cvqdisk* rpm.
  • Install the cvqdisk* rpm as root user.
[root@racnode1 rpm]# ls -ltr
total 12
-rwxrwx---. 1 root vboxsf 8976 Jul  1  2014 cvuqdisk-1.0.9-1.rpm
[root@racnode1 rpm]# rpm -ivh cvuqdisk-1.0.9-1.rpm
Preparing...                ########################################### [100%]
Using default group oinstall to install package
   1:cvuqdisk               ########################################### [100%]
[root@racnode1 rpm]#

Xhost +
Su grid
  • Now as grid user navigate to the grid directory on node1
[grid@racnode1 ~]$ . .bash_profile
[grid@racnode1 ~]$ echo $ORACLE_SID
+ASM1
[grid@racnode1 ~]$
[grid@racnode1 ~]$ cd /media/sf_RAC_Software/grid/

  • And then start the Oracle Installer.

./runInstaller







  • Ensure both the nodes are added and then mention the Username as “grid” and specify the password for grid user.
  • Then Click on SSH Connectivity if you haven’t configured passwordless connectivity or press Next and then click on setup.






Select file system as ASM.












Run both the script on racnode1 and racnode2 as root user. Once both are completed click ok.




  • Check that the Grid software has been installed successfully.
  • Run the following command on both nodes.

crsctl stat res -t



No comments :

Post a Comment