Trace & Kill User session in R12

How to trace user sessions from User Monitor Screen in Oracle Application (R12)and kill the session

Navigate to System Administrator-->  Security --> User --> Monitor

Note the User Name and Oracle Process ID

Now run this sql as 

&PID=Oracle Process ID

&USER_NAME=User Name

SELECT SUBSTR(d.user_name,1,30) "User Name" 

             ,      a.pid 

             ,      b.sid 

             ,      b.serial# 

             FROM   v$process a, v$session b, fnd_logins c, fnd_user d 

             WHERE  a.pid = c.pid 

             AND    c.pid = &PID 

             AND    d.user_name = UPPER('&USER_NAME') 

             AND    d.user_id = c.user_id 

             AND    a.addr = b.paddr 

             AND    c.end_time IS NULL

  Note the SID and SERIAL#     and pass the SID and SERIAL# in next sql within single quote.    

             

ALTER SYSTEM KILL SESSION '123, 1234';       --   <'SID, SERIAL#'>

Query to find all current Oracle Application user logged in

SELECT DISTINCT ic.disabled_flag,
  fu.user_name User_Name,
  fr.RESPONSIBILITY_KEY Responsibility,
  fu.user_id,
  fu.description,
  fu.employee_id,
  ic.responsibility_application_id,
  ic.responsibility_id,
  ic.org_id,
  ic.function_type,
  ic.counter,
  ic.first_connect,
  ic.last_connect,
  ic.nls_territory,
  ic.time_out,
  fr.menu_id,
  fr.responsibility_key
FROM fnd_user fu,
  fnd_responsibility fr,
  icx_sessions ic
WHERE fu.user_id          = ic.user_id
AND fr.responsibility_id  = ic.responsibility_id
AND ic.disabled_flag      ='N'
and IC.RESPONSIBILITY_ID is not null
AND ic.last_connect       > sysdate - (ic.time_out/60)/96

FNDLOAD

Understanding FNDLOAD utility: 

The Generic Loader (FNDLOAD) is a concurrent program that can download data from an application entity into a portable,editable text file. This file can then be uploaded into any other database to copy the data. 

FNDLOAD utility Modes of Operation:

The FNDLOAD(Generic Loader) utility operates in 2 modes:

1)Download mode or

2)Upload mode

In the download mode data is downloaded from a database according to a configuration (.lct) file and then converts the data into a Data (.ldt) file. This data file can be uploaded to a different database.In both downloading and uploading, the structure of the data involved is described by a configuration file. 

The configuration file describes the structure of the data and also the access methods use to copy the data into or out of the database. The same configuration file may be used for both uploading and downloading. When downloading,the Generic Loader creates a second file, called the data file that contains the structured data selected for downloading. The data file has a standard syntax for representing the data that has been downloaded.

When uploading,the Generic Loader reads a data file to get the data that it is to upload.In most cases, the data file was produced by a previous download, but may have come from another source. The data file cannot be interpreted without the corresponding configuration file available.

FNDLOAD utility syntax:

DOWNLOAD COMMAND SYNTAX:

FNDLOAD 0 Y DOWNLOAD <${FND_TOP}/patch/115/import/ 

UPLOAD COMMAND SYNTAX:

FNDLOAD 0 Y UPLOAD <${FND_TOP}/patch/115/import/ 

FNDLOAD Usage Details:

FNDLOAD can be used to migrate the following system administrator objects between instances

1.Printer Styles

2.Lookup Types and codes

3.Descriptive Flexfield (DFF)

4.Key Flexfield (KFF)

5.Concurrent programs with the parameters

6.Request Sets (when the programs are not triggered based on success)

7.Value Sets and Value set Values

8.Profiles

9.Request Groups

10.Responsibilities

11.Forms

12.Functions

13.Menus

14.Messages

Merits of FNDLOAD utility:

1.Need to maintain a baseline environment (Source for Clone) and update it on a regular basis

2.Base environment can have issues and the cloning strategy made totally ineffective. In such a case every new environment created will have to be updated with a lot of changes

3.Cloning /Refresh not possible in short intervals

4.Selective replication of setups and AOL objects not possible with cloning

5.Environments not delivered as per timeline and affecting the Testing schedules

6.Manually maintaining Environments at different level of configuration is tedious

7.Time consuming to manually update multiple environment with defect fixes and error prone

8.FNDLOAD is fully supported and recommended by Oracle for migration of FND objects. Requires 0 learning curve and 0 investment.

Demerits of FNDLOAD utility:

1.This utility can be only used for FND (System administrator) objects only.

2.Application Patching mechanisms use FNDLOAD heavily. There is a possibility of negative impact.

3.There is no validation of sensitive data that is being migrated by the FNDLOAD tool itself.

Examples of FNDLOAD utility:

Download: FNDLOAD apps/$pwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_aris.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="CN" LOOKUP_TYPE="XXTEST_TECHOPS_ARIS_SITES"

Download: FNDLOAD apps/$pwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_points.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="CN" LOOKUP_TYPE="XXTEST_TECHOPS_POINTS_SITES"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_procedure.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_PRCS_EAST"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_EAST_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_MW_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_mttr_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_MTTR_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_mttr_process.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_MTTR_DATA_PROCES"

Upload: FNDLOAD apps/$pwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_aris.ldt 

Upload: FNDLOAD apps/$pwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_points.ldt 

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_procedure.ldt 

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_load.ldt 

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_load.ldt 

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_mttr_load.ldt 

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_mttr_process.ldt 

OCR and Voting Disk location

The location of OCR and Voting disks can be found as follows:

OCR location:

[oracle@dbcl1n1 AUCS1 ~]$ cat /etc/oracle/ocr.loc
ocrconfig_loc=/export/ocw/oracle/ocr1
ocrmirrorconfig_loc=/export/ocw/oracle/ocr2
local_only=FALSE

Voting disk location:

[oracle@dbcl1n1 AUCS1 ~]$ cd $ORA_CRS_HOME/bin
[oracle@dbcl1n1 AUCS1 bin]$ ./crsctl query css votedisk
0. 0 /export/ocw/oracle/vote1
1. 0 /export/ocw/oracle/vote2
2. 0 /export/ocw/oracle/vote3

Recover corrupt OCR without backup

We all know Oracle takes backups of OCR after every 4 hours,but what if we don't have backup  and OCR is corrupted,we cannot go for installing whole clusterware which could be a lengthy process.

If we are on 10g R2 and later version then this can be done without re-installing Clusterware ( if you have backup of root.sh or it’s not overwritten by any subsequent patch ) I tested this on my test machines.

We can get Current Voting Disk location

[root@racnode1 ~]# crsctl query css votedisk
 0. 0 /OCFS/VOT

located 1 votedisk(s).

Current OCR Files location

[root@racnode1 ~]# ocrcheck

Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 4344
Available space (kbytes) : 257800
ID : 601339441

Device/File Name : /OCFS/OCR
Device/File integrity check succeeded

Device/File Name : /OCFS/OCR2
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Output of CRS_STAT

[root@racnode1 ~]# crs_stat -t

Name Type Target State Host
————————————————————
ora….SM1.asm application ONLINE ONLINE racnode1
ora….C1.lsnr application ONLINE ONLINE racnode1
ora.racnode1.gsd   application ONLINE ONLINE racnode1
ora.racnode1.ons   application ONLINE ONLINE racnode1
ora.racnode1.vip   application ONLINE ONLINE racnode1
ora….SM2.asm application ONLINE ONLINE racnode2
ora….C2.lsnr application ONLINE ONLINE racnode2
ora.racnode2.gsd   application ONLINE ONLINE racnode2
ora.racnode2.ons   application ONLINE ONLINE racnode2
ora.racnode2.vip   application ONLINE ONLINE racnode2
ora.test.AP.cs application ONLINE ONLINE racnode1
ora….st1.srv application ONLINE ONLINE racnode1
ora.test.db    application ONLINE ONLINE racnode2
ora….t1.inst application ONLINE ONLINE racnode1
ora….t2.inst application ONLINE ONLINE racnode2

I stopped clusterware on both nodes and removed OCR & Voting Disks.

[root@racnode1 ~]# ls -lrt /OCFS/*

-rw-r–r– 1 root root 399507456 Jun 29 14:05 /OCFS/OCR2
-rw-r—– 1 root oinstall 10485760 Jun 29 14:05 /OCFS/OCR
-rw-r–r– 1 oracle oinstall 10240000 Jun 29 14:05 /OCFS/VOT

[root@racnode1 ~]# rm -fr /OCFS/*

Tried again to start Cluster

[root@racnode1 ~]# crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly



Clusterware could not startup.

[root@racnode1 ~]# crsctl check crs

Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM

Thrown error in /tmp/crsct.* file about OCR

[root@racnode1 ~]# cat /tmp/crsc*

OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]

 Here, I lost all my OCR & Voting disk.

Below procedure can be used for recovery.

1) Execute rootdelete.sh script from All Nodes.
2) Execute rootdeinstall.sh from Primary Node.
3) Run root.sh from Primary node.
4) Run root.sh from all remaining nodes.
5) Execute remaining configurations (ONS,netca,register required resources)

1) Executing rootdelete.sh on all nodes, this script can be found under $ORA_CRS_HOME/install/

[root@racnode1 ~]# /u01/app/oracle/product/crs/install/rootdelete.sh
Shutting down Oracle Cluster Ready Services (CRS):
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down…
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in ‘/etc/oracle/scls_scr’

[root@racnode2 ~]# /u01/app/oracle/product/crs/install/rootdelete.sh
Shutting down Oracle Cluster Ready Services (CRS):
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down…
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in ‘/etc/oracle/scls_scr’

OCR initialization error can be safely ignored.

2) Execute rootdeinstall.sh on Primary Node, this script can also be found under $ORA_CRS_HOME/install

[root@racnode1 ~]# /u01/app/oracle/product/crs/install/rootdeinstall.sh
Removing contents from OCR mirror device
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.031627 seconds, 332 MB/s
Removing contents from OCR device
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.029947 seconds, 350 MB/s

3) Run root.sh on Primary node, this will create VOT & OCR files.

[root@racnode1 ~]# $ORA_CRS_HOME/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
“/OCFS/VOT” does not exist. Create it before proceeding.
Make sure that this file is shared across cluster nodes.
1

I had to touch this file to proceed

[root@racnode1 ~]# touch /OCFS/VOT
[root@racnode1 ~]# $ORA_CRS_HOME/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
assigning default hostname racnode1 for node 1.
assigning default hostname racnode2 for node 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: racnode1 racnode1-priv racnode1
node 2: racnode2 racnode2-priv racnode2
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Now formatting voting device: /OCFS/VOT
Format of 1 voting devices complete.
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
racnode1
CSS is inactive on these nodes.
racnode2
Local node checking complete.
Run root.sh on remaining nodes to start CRS daemons.

4) Run root.sh from all remaining nodes.

[root@racnode2 crs]# ./root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
assigning default hostname racnode1 for node 1.
assigning default hostname racnode2 for node 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: racnode1 racnode1-priv racnode1
node 2: racnode2 racnode2-priv racnode2
clscfg: Arguments check out successfully.

NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
racnode1
racnode2
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps

Creating VIP application resource on (2) nodes…
Creating GSD application resource on (2) nodes…
Creating ONS application resource on (2) nodes…
Starting VIP application resource on (2) nodes…
Starting GSD application resource on (2) nodes…
Starting ONS application resource on (2) nodes…

Done.

Clusterware is up and running

[root@racnode2 crs]# crs_stat -t

Name Type Target State Host
————————————————————
ora.racnode1.gsd application ONLINE ONLINE racnode1
ora.racnode1.ons application ONLINE ONLINE racnode1
ora.racnode1.vip application ONLINE ONLINE racnode2
ora.racnode2.gsd application ONLINE ONLINE racnode2
ora.racnode2.ons application ONLINE ONLINE racnode2
ora.racnode2.vip application ONLINE ONLINE racnode2

5) Remaining Configuration

       a) Configuring Server side ONS

[root@racnode1 crs]# $ORA_CRS_HOME/bin/racgons add_config racnode1:6200 racnode2:6200

         b) Listener Configuration usint netca

we might want to remove listener.ora from both nodes as entries may exist already. Take backup or orignial listener.ora  and use netca to configure &
register with OCR. Till 10g, we can not register listener using srvctl

Renaming orginal listener.ora

[oracle@racnode1 ~]$ mv $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora.orig
[oracle@racnode1 ~]$ ssh racnode2 mv $ORACLE_HOME/network/admin/lstener.ora $ORACLE_HOME/network/admin/listener.ora.orig

          c)Adding ASM, Instance, Database

[oracle@racnode1 ~]$ srvctl add asm -i +ASM1 -n racnode1 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@racnode1 ~]$ srvctl add asm -i +ASM2 -n racnode2 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@racnode1 ~]$ srvctl add database -d test -o /u01/app/oracle/product/10.2.0/db_1
[oracle@racnode1 ~]$ srvctl add instance -d test -i test1 -n racnode1
[oracle@racnode1 ~]$ srvctl add instance -d test -i test2 -n racnode2

I restarted both nodes, got everything back. Yes, Services can be re-created.

[oracle@racnode1 ~]$ crs_stat -t

Name Type Target State Host
————————————————————
ora….SM1.asm application ONLINE ONLINE racnode1
ora….C1.lsnr application ONLINE ONLINE racnode1
ora.racnode1.gsd   application ONLINE ONLINE racnode1
ora.racnode1.ons   application ONLINE ONLINE racnode1
ora.racnode1.vip   application ONLINE ONLINE racnode1
ora….SM2.asm application ONLINE ONLINE racnode2
ora….C2.lsnr application ONLINE ONLINE racnode2
ora.racnode2.gsd   application ONLINE ONLINE racnode2
ora.racnode2.ons   application ONLINE ONLINE racnode2
ora.racnode2.vip   application ONLINE ONLINE racnode2
ora.test.db    application ONLINE ONLINE racnode2
ora….t1.inst application ONLINE ONLINE racnode1
ora….t2.inst application ONLINE ONLINE racnode2

Note : That’s why it’s recommended to take backup of root.sh after fresh install as subsequent patches can
overwrite root.sh script.

This is described on Metalink Note: 399482.1