ora-semsem

Sunday, May 21, 2023

Monday, March 20, 2023

Sunday, August 7, 2022

certificate Import Apache Tomcat

cd /opt/tomcat/bin

./shutdown.sh

cd /opt/tomcat/certificate

cp -rp keystore.jks keystore.jks_bkp

mv start_***.com.eg.pfx start_****.com.eg.pfx_bkp

copy new certificate start_****.com.eg.pfx in the same path

keytool -importkeystore -srckeystore start_***.com.eg.pfx -srcstorepass ******* -srcstoretype pkcs12 -destkeystore keystore.jks -deststoretype jks -deststorepass ******

cd /opt/tomcat/bin

startup.sh

#to check password in server.xml

cd /opt/tomcat/conf

vi server.xml

Fix Output Post-processor issues

select DEVELOPER_PARAMETERS from FND_CP_SERVICES

where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES

where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

#production

J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m

#upgrade env

update FND_CP_SERVICES

set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx4096m'

where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES

where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

#production worked on production with higher value mx4096m

update FND_CP_SERVICES

set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx3072m'

where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES

where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

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

#fix

CONC-POST-PROCESSING RESULTS into error "GC Overhead Limit Exceeded" (Doc ID 2573449.1)

Then, in System Administrator resp, navigate to Profile > System

- set Concurrent:OPP Process Timeout = 43200

- set Concurrent:OPP Response Timeout = 43200

(these values are for 12 hours in seconds)

#old

#CONC-POST-PROCESSING RESULTS into error "GC Overhead Limit Exceeded" (Doc ID 2573449.1)

https://community.oracle.com/mosc/discussion/2796412/java-lang-outofmemoryerror-gc-overhead-limit-exceeded

#R12: Various Programs End With Warning: The Output Post-processor is Running But Has Not Picked up This Request (Doc ID 1224684.1)

Reclaim Oracle Database free space

set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"

column savings format 999,990 heading "Poss.|Savings"

break on report

compute sum of savings on report

column value new_val blksize

select value from v$parameter where name = 'db_block_size'

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

Enable Active Dataguard In Physical Standby Database

#How To Enable Active Dataguard In Physical Standby Database

https://dbaclass.com/article/enable-active-dataguard-physical-standby-database/

# 1- Cancel the media recovery on physical standby.

SQL> alter database recover managed standby database cancel;

# 2- Open the database[PHYSICAL STANDBY]

SQL> alter database open;

Database altered

# 3- Start media recovery with real-time log apply[PHYSICAL STANDBY]

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

#4- Check the database status:[PHYSICAL STANDBY]

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

PRODDB READ ONLY WITH APPLY

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#

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

ARCH CONNECTED 0

RFS IDLE 0

RFS RECEIVING 510

RFS IDLE 0

RFS IDLE 4178

MRP0 APPLYING_LOG 510 --->>>> MRP PROCESS

Oracle Dataguard Monitor

# dataguard monitor

# DR notification

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME >= (SYSDATE - 1) GROUP BY THREAD# ORDER BY THREAD#;

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES' and COMPLETION_TIME

>= (SYSDATE - 1) and DEST_ID=2 GROUP BY THREAD# ORDER BY THREAD#;

#select database role

SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE OPEN_MODE

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

PHYSICAL STANDBY MOUNTED

#status of background processes in a standby database (on standby)

select process, client_process,thread#,sequence#,status from v$managed_standby;

#FIND THE ARCHIVE LAG BETWEEN PRIMARY AND STANDBY: monitor dataguard

select LOG_ARCHIVED-LOG_APPLIED "LOG_GAP" from

(SELECT MAX(SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),

(SELECT MAX(SEQUENCE#) LOG_APPLIED

FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');

#example (Normal)

LOG_GAP

----------

#LAST SEQUENCE RECEIVED AND LAST SEQUENCE APPLIED:

SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM

(select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change#

FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history

WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;

#CHECK THE MESSAGES/ERRORS IN STNADBY DATABASE: ( Run on standby)

set pagesize 2000

set lines 2000

col MESSAGE for a90

select message,timestamp from V$DATAGUARD_STATUS where timestamp > sysdate - 1/6;

#check lag ( on standby)

set lines 1000

select name,value from v$dataguard_stats;

#Example

NAME VALUE

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

transport lag +00 00:00:00

apply lag +00 00:00:00

apply finish time +00 00:00:00.000

estimated startup time 8

#which archive sequence number comes from the Primary database lastly and which is last applied in Standby ( on standby)

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last in Sequence", APPL.SEQUENCE# "Last Applied Sequence", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

#Example

Thread Last in Sequence Last Applied Sequence Difference

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

1 19642 19642 0

# archive gap in dataguard

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

#applied archivelogs in dataguard,

select thread#,sequence#,first_time,next_time,applied from gv$archived_log where applied='YES';

# max sequence of dataguard

select thread#,max(sequence#) from gv$archived_log group by thread#;

#To see all parameters of Oracle dataguard, you can execute following query.

set linesize 500 pages 0

col value for a80

col name for a50

select name, value from v$parameter

where name in ('db_name','db_unique_name','log_archive_config',

'log_archive_dest_1','log_archive_dest_2','log_archive_dest_3',

'log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3', 'remote_login_passwordfile',

'log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert',

'log_file_name_convert', 'standby_file_management') order by 1;

##################################troubleshooting#############################################################################

#stop dataguard

alter database recover managed standby database cancel;

#start dataguard

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

#CHECK THE NUMBER OF ARCHIVES GETTING GENERATING ON HOURLY BASIS:

SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",

TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"

FROM V$LOG_HISTORY

GROUP BY TRUNC(FIRST_TIME)

ORDER BY TRUNC(FIRST_TIME) DESC

#Register Data Guard standby redo log file tips( this will register all arch logs in this path )

http://www.dba-oracle.com/t_register_standby_redo_log_files.htm

LOCALERP - Primary database

Error: ORA-16724: cannot resolve gap for one or more members

LOCALERPST - Physical standby database

Warning: ORA-16809: multiple warnings detected for the member

alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40863_1077804461.arc';

alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40864_1077804461.arc';

alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40865_1077804461.arc';

alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_40866_1077804461.arc';

alter database register logfile '/oradata/proddata19/LOCALERP/archive/1_62960_1077804461.arc';

1_62960_1077804461.arc

rman> catalog start with '/oradata/proddata19/LOCALERP/archive';

catalog start with '/oradata/proddata19/LOCALERP/archive/';

10 16 * * * /bin/sh /home/oraprod/del_arch.sh > /home/oraprod/1

How to recover standby database from a missing archive log gap

02-08-2022

#how to recover standby database from a missing archive log gap

#Hossam Note

#if there is a Gap on DG there is 2 scenarios

#1- missing archivelogs on standby but it exist on prod this can be fixed by using rsync and catalog start with

#2-missing archivelogs on standby but its NOT exist on prod this can be fixed by below

https://techgoeasy.com/recovering-standby-database-fro/

#On the standby database check the current SCN

SQL> col current_SCN for 99999999999999999999;

SQL> select current_scn from v$database;

CURRENT_SCN

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

111050674405375

#On the primary database create the needed incremental backup from the above SCN

rman target /

RMAN>

{

allocate channel c1 type disk;

BACKUP INCREMENTAL FROM SCN 111050674405375 DATABASE FORMAT '/tmp/inc_standby_%U';

}

#Cancel managed recovery at the standby database

SQL> alter database recover managed standby database cancel;

Database altered

#scp the backup files to standby server to /tmp folder.

scp -rp oraprod@

#Apply the Incremental Backup to the Standby Database

rman target /

RMAN>RECOVER DATABASE NOREDO;

#Put the standby database back to managed recovery mode.

qlplus "/ as sysdba"

SQL>recover managed standby database disconnect;

Media recovery complete.

#On the primary create new standby controlfile

alter database create standby controlfile as '/tmp/standby01.ctl';

#Copy the standby control file to the standby site. Shutdown the standby database and replace the standby control files and restart the standby database in the managed recovery mode using the below command

RMAN> SHUTDOWN IMMEDIATE ;

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/standby01.ctl';

#Mount the standby

RMAN> ALTER DATABASE MOUNT;

#On STANDBY database, clear all standby redo log groups:

select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;

GROUP#

----------

ALTER DATABASE CLEAR LOGFILE GROUP 10;

ALTER DATABASE CLEAR LOGFILE GROUP 11;

ALTER DATABASE CLEAR LOGFILE GROUP 12;

ALTER DATABASE CLEAR LOGFILE GROUP 13;

#if dont clear restart standby db

#Now you can start the MRP

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

#if needed

cd /oradata/proddata19/LOCALERP/archive/

rsync -avzhe ssh --progress oraprod@10.143.250.164:/oradata/proddata19/LOCALERP/archive/ .

rman> catalog start with '/oradata/proddata19/LOCALERP/archive';

Sunday, January 20, 2019

install react native problem using npm

install React Native

fist install Node.js

https://nodejs.org/en/download/

2- install react native using npm

https://facebook.github.io/react-native/docs/getting-started

problem :

H:\>npm install -g expo-cli
npm ERR! code SELF_SIGNED_CERT_IN_CHAIN
npm ERR! errno SELF_SIGNED_CERT_IN_CHAIN
npm ERR! request to https://registry.npmjs.org/expo-cli failed, reason: self signed certificate in certificate chain

npm ERR! A complete log of this run can be found in:
npm ERR! C:\Users\username\AppData\Roaming\npm-cache\_logs\2019-01-20T11_00_33_490Z-debug.log

fix :

H:\>npm config set prefix "D:\Program Files\nodejs\cash\AppData\Local\npm"

H:\>npm config set cache "D:\Program Files\nodejs\cash\AppData\Local\npm"

H:\>npm config set strict-ssl false

H:\>npm install -g expo-cli

to install on Ubuntu

https://websiteforstudents.com/install-the-latest-node-js-and-nmp-packages-on-ubuntu-16-04-18-04-lts/

Saturday, November 10, 2018

linux ubuntu slow internet fix

sudo nano /etc/gai.conf

#unhach this line

precedence ::ffff:0:0/96 100

Wednesday, October 17, 2018

Oracle® Fusion Middleware System Requirements and Specifications Red Hat Linux 7

#Oracle® Fusion Middleware System Requirements and Specifications

https://docs.oracle.com/en/middleware/lifecycle/12.2.1.3/sysrs/unix-operating-system-requirements.html#GUID-880D655D-F9FB-439B-8001-10AA78D0FC57

#1- Viewing the Number of Currently Open Files
/usr/sbin/lsof | wc -l

#2- Setting the Operating System Open File and Processes Limit

vi /etc/security/limits.conf

Add the following lines to the limits.conf file.
* soft nofile 4096
* hard nofile 65536
* soft nproc 2047
* hard nproc 16384

Reboot the host computer.

# 3- Minimum Requirements for the Linux Operating System

# Oracle Linux 7 (UL0+) and Red Hat Linux 7 (UL0+)

Oracle Linux 7 (UL0+) and Red Hat Linux 7 (UL0+)

binutils-2.23.52.0.1
compat-libcap1-1.10
compat-libstdc++-33-3.2.3.x86_64
compat-libstdc++-33-3.2.3.i686
gcc-4.8.2
gcc-c++-4.8.2
glibc-2.17.x86_64
glibc-2.17.i686
glibc-devel-2.17.x86_64
libaio-0.3.109.x86_64
libaio-devel-0.3.109.x86_64
libgcc-4.8.2.x86_64
libgcc-4.8.2.i686
libstdc++-4.8.2.x86_64
libstdc++-4.8.2.i686
libstdc++-devel-4.8.2.x86_64
ksh
make-3.82
sysstat-10.1.5
numactl-2.0.9 for x86_641
numactl-devel-2.0.9 for x86_642
motif-2.3.4-7 for x86_643
motif-devel-2.3.4-7 for x86_644
redhat-lsb-4.1-27.0.1.el7 for x86_64
redhat-lsb-core-4.1-27.0.1.el7 for x86_64

-- you cand add yum install ocfs2-tools -y fot another oracle document

Installing Oracle 11g on RHEL 7

#Quick Installation Guide DB 11g Release 2 (11.2) for Linux x86-64 Red Hat Enterprise Linux 7

https://docs.oracle.com/cd/E11882_01/install.112/e24326/toc.htm#CIHFICFD

# following or later version of packages for Oracle Linux 7, and Red Hat Enterprise Linux 7 must be installed:

binutils-2.23.52.0.1-12.el7.x86_64
compat-libcap1-1.10-3.el7.x86_64
compat-libstdc++-33-3.2.3-71.el7.i686
compat-libstdc++-33-3.2.3-71.el7.x86_64
gcc-4.8.2-3.el7.x86_64
gcc-c++-4.8.2-3.el7.x86_64
glibc-2.17-36.el7.i686
glibc-2.17-36.el7.x86_64
glibc-devel-2.17-36.el7.i686
glibc-devel-2.17-36.el7.x86_64
ksh
libaio-0.3.109-9.el7.i686
libaio-0.3.109-9.el7.x86_64
libaio-devel-0.3.109-9.el7.i686
libaio-devel-0.3.109-9.el7.x86_64
libgcc-4.8.2-3.el7.i686
libgcc-4.8.2-3.el7.x86_64
libstdc++-4.8.2-3.el7.i686
libstdc++-4.8.2-3.el7.x86_64
libstdc++-devel-4.8.2-3.el7.i686
libstdc++-devel-4.8.2-3.el7.x86_64
libXi-1.7.2-1.el7.i686
libXi-1.7.2-1.el7.x86_64
libXtst-1.2.2-1.el7.i686
libXtst-1.2.2-1.el7.x86_64
make-3.82-19.el7.x86_64
sysstat-10.1.5-1.el7.x86_64

#Oracle ODBC Drivers

download from http://www.unixodbc.org

unixODBC-2.3.1-6.el7.x86_64 or later

unixODBC-2.3.1-6.el7.i686 or later

unixODBC-devel-2.3.1-6.el7.x86_64 or later

unixODBC-devel-2.3.1-6.el7.i686 or later

#create the oinstall and dba group

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba

#If the oracle user does not exist

/usr/sbin/useradd -g oinstall -G dba oracle

#Configuring Kernel Parameters and Resource Limits

/etc/sysctl.conf
/sbin/sysctl -p
Enter the command /sbin/sysctl -a to confirm that the values are set correctly.

#Check Resource Limits for the Oracle Software Installation Users

limits.conf

mkdir -p /u01/app/
chown -R oracle:oinstall /u01/app/
chmod -R 775 /u01/app/

#Enterprise Manager
http://host.domain:1158/em/

Tuesday, July 31, 2018

List of Running Requests

set lines 130

set pages 200

col os form A7 head AppProc

col spid form a6 head SPID

col program form A50 trunc

set pages 38

col time form 99999.99 head Elapsed

col "Req Id" form 99999999

col "Parent" form a8

col "Prg Id" form 9999999

col serial# form 999999 head Serial#

col qname head "Manager" format a10 trunc

col sid format 9999 head SID

col user_name form A12 head User trunc

set recsep off

select

q.concurrent_queue_name qname

,f.user_name

,a.request_id "Req Id"

,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"

,a.concurrent_program_id "Prg Id"

,a.phase_code,a.status_code

-- ,b.os_process_id "OS"

-- ,vs.sid

-- ,vs.serial# "Serial#"

-- ,vp.spid

,a.oracle_process_id "spid"

,(nvl(a.actual_completion_date,sysdate)-a.actual_start_date)*1440 "Time"

,c.concurrent_program_name||' - '||

c2.user_concurrent_program_name||' '||a.description "Program"

from APPLSYS.fnd_Concurrent_requests a

,APPLSYS.fnd_concurrent_processes b

,applsys.fnd_concurrent_queues q

,APPLSYS.fnd_concurrent_programs_tl c2

,APPLSYS.fnd_concurrent_programs c

,APPLSYS.fnd_user f

-- ,v$session vs

-- ,v$process vp

where

a.controlling_manager = b.concurrent_process_id

and a.concurrent_program_id = c.concurrent_program_id

and a.program_application_id = c.application_id

and c2.concurrent_program_id = c.concurrent_program_id

and c2.application_id = c.application_id

and a.phase_code in ('I','P','R','T')

and a.requested_by = f.user_id

and b.queue_application_id = q.application_id

and b.concurrent_queue_id = q.concurrent_queue_id

and c2.language = 'US'

-- and vs.process (+) = b.os_process_id

-- and vs.paddr = vp.addr (+)

order by 9 desc;

List of pending Jobs waiting for managers

set lines 130
col USER_CONCURRENT_QUEUE_NAME format a39
SELECT b.USER_CONCURRENT_QUEUE_NAME , count(*)
FROM apps.FND_CONCURRENT_WORKER_REQUESTS a,apps.FND_CONCURRENT_QUEUES_VL b
WHERE (Phase_Code = 'P' ) and a.hold_flag != 'Y'
and a.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND
STATUS_CODE IN ('I'))) and a.CONCURRENT_QUEUE_ID!=1003
and a.CONCURRENT_QUEUE_ID=b.CONCURRENT_QUEUE_ID
group by b.USER_CONCURRENT_QUEUE_NAME ,status_code;

col program_description format a60
col user_concurrent_queue_name format a40

LIST E-BUSINESS SUITE PROFILE OPTIONS FOR ALL VALUES

select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, decode(to_char(v.level_value2), '-1', 'Responsibility',
decode(to_char(v.level_value), '-1', 'Server',
'Server+Resp')),
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,
decode(to_char(v.level_value), '-1',
(select node_name from fnd_nodes
where node_id = v.level_value2),
(select node_name from fnd_nodes
where node_id = v.level_value2)||'-'||rsp.responsibility_key)),
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(p.profile_option_name) in ( select profile_option_name
from fnd_profile_options_tl
where upper(user_profile_option_name)
like upper('%&user_profile_name%'))
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, user_profile_option_name, level_id, level_set;

R12 Finding Trace File of a Concurrent Program

Finding Trace File of a Concurrent Program

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

R12 Finding SID of a Concurrent Request

Finding SID of a Concurrent Request

select b.sid, oracle_session_id, oracle_process_id, os_process_id

from fnd_concurrent_requests a ,

v$session b

where a.request_id=&request_id and

a.ORACLE_SESSION_ID = b.AUDSID

R12 FIND PRODUCTS INSTALLED

FIND PRODUCTS INSTALLED

select a.oracle_id, a.last_update_date, a.product_version,a.patch_level, decode(a.status, 'I', 'Installed', 'S', 'Shared', 'N', 'Not Installed',a.status) Status, a.industry, b.application_name, c.application_short_name from fnd_product_installations a, fnd_application_tl b, fnd_application c where a.application_id = b.application_id and a.application_id = c.application_id and b.language = 'US' order by c.application_short_name;

R12 Cost Manager

--SQL Query to find out uncosted transactions

SELECT * FROM mtl_material_transactions WHERE costed_flag = 'N';

--Look for costing errors

select
count(*),organization_id,costed_flag
from apps.mtl_material_transactions
where 1=1
and costed_flag is not null
group by organization_id,costed_flag

--

Oracle R12 concurrent manager monitor Scripts

1.How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';

2.Concurrent manager status for a given sid?

col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE, s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';

3. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';

4.To find sid,serial# for a given concurrent request id?

set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';

5.To find concurrent program name,phase code,status code for a given request id?

SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'), completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;

6.To find the sql query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and ses.sid='&oracle_sid'
/

7. To find child requests

set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20

SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';

8. Cancelling Concurrent request :

update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;

9. Kill sessions program wise

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where MODULE like '';

10 .Concurrent Request running by SID

SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;

11. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';

12. Oracle Concurrent Request Error Script (requests which were error ed out)

SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;

13. Request submitted by User

SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;

14.Concurrent Program enable with trace

col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID