Interesting question on FNDSM with SQL*Loader relation

From: Shastry(DBA) <"Shastry>
Date: Fri, 21 Dec 2012 09:56:40 +0530
Message-ID: <CAFQkUXSDnR4eA5+tTVji5Kt_yMBOY1Y5Ft6iWCemFfg2GfEiEQ_at_mail.gmail.com>

Hi All,

We have been running the custom concurrent programs which used SQL*Loader utility to load the flat file to database and is requested to run as a Concurrent Program. Recently one of our custom purging cron zipped the FNDSM<>.mgr log file which created a mess for the subsequent concurrent requests failing with invalid username/password. It was hard to figure what went wrong in our system, later we bounced the entire CM services to resolve the issue. Below find my stage instance testings which could reveal that only our custom programs are writing the logs in FNDSM.mgr log file and not other standard requests or any of the other programs which uses SQL*Loader. We need to understand why the request has to write it to FNDSM.mgr logfile and whereas not in its Concurrent manager Log file? Please share your thoughts.

Also the big question which Oracle also finding it difficult to explain the relationship between FNDSM (Service managers) and SQL*Loader utility.

*Case1:*

When requests were running, we zipped both the FNDSM*.mgr files. Request completed successfully without any error. It means the Service manager log file is required only during authentication.

*Case2:*

*
1. *We gunzip FNDSM files and ask Apps team re run SQL*LOADER conc program . (Result : Conc Prog should be failed)*

*Request failed since the FNDSM process id becomes stale like*

Commit point reached - logical record count 8026

Commit point reached - logical record count 8030

Commit point reached - logical record count 8034

*Commit tail: FNDSM3273213.mgr: Stale NFS file handle*

*tail: no files remaining*

* b. *DBA should bounce Service Managers (both PCP nodes) concurrent managers and ask Apps team re run SQL*LOADER conc program .*

*Here we just did the Service managers bounce (both PCP nodes) and only dependent Concurrent Manager only (TESTING TMC Compensation Critical). Request Ran again and completed successfully.*

* c. *DBA should bounce whole concurrent managers and ask Apps team re run SQL*LOADER conc program . (Result : Conc Prog should be Passed ) *

*Here we felt, we need more understanding from experts on FNDSM and SQL*Loader relation since custom programs were only writing the log in FNDSM*.mgr file and not other standard Concurrent programs. I would like to know How and what parameter/scenarios SQL*Loader requires Service manager log files to authenticate and write commit check points. One thing noticed here is though active log file gets moved/zipped the ongoing request can complete successfully, but not the new forthcoming requests.*

*FNDSM logfile info:*

Reqid = 123456 - XXTMCG_TEST_LOADER

OIC JOBS IN FNDSM mgr logs,

Commit point reached - logical record count 174258
Commit point reached - logical record count 174262
Commit point reached - logical record count 174266
Commit point reached - logical record count 174270
Commit point reached - logical record count 174274
Commit point reached - logical record count 174278
Commit point reached - logical record count 174282
Commit point reached - logical record count 174286
Commit point reached - logical record count 174290
Commit point reached - logical record count 174294

Reqid = 43950400 - TESTING TM Run OTM Engine Trx Loader - 30 seconds to execute 86000+ records.

Didn�t touched the FNDSM*.mgr file and able to process successfully.

test-axx-cbz-100 TMCSTG_BALANCE

/apps/reports/TMCSTG/log > ps -ef | grep -i FNDSM

oaTMCstg 13110 9967 0 02:58 pts/11 00:00:00 tail -100f FNDSM3273214.mgr

oaTMCstg 22049 1 0 05:09 ? 00:00:00 FNDSM

oaTMCstg 25144 10639 0 05:51 pts/12 00:00:00 grep -i FNDSM

test-axx-cbz-100 TMCSTG_BALANCE

/apps/reports/TMCSTG/log > lsof -p 22049

COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME FNDSM 22049 oaTMCstg cwd DIR 0,22 10403840 28079858
/apps/reports/TMCSTG/log

(rcdn9-40b-filer14b:/vol/local2/c6-a1-oa1-istg_reports)

FNDSM 22049 oaTMCstg rtd DIR 8,1 4096 2 /

FNDSM 22049 oaTMCstg txt REG 0,24 1065124 165051749
/apps/TMCstg/apps/apps_st/appl/fnd/12.0.0/bin/FNDSM
(rcdn9-40a-filer14a:/vol/local1/c6-a1-oa1-istg_apps_ts2TMCstg)

FNDSM 22049 oaTMCstg mem REG 8,1 112212 147351 /lib/ ld-2.3.4.so

FNDSM 22049 oaTMCstg mem REG 8,1 1547588 147352 /lib/tls/ libc-2.3.4.so

FNDSM 22049 oaTMCstg mem REG 8,1 16748 147355 /lib/ libdl-2.3.4.so

FNDSM 22049 oaTMCstg mem REG 8,1 214060 145538 /lib/tls/ libm-2.3.4.so

FNDSM 22049 oaTMCstg mem REG 8,1 107928 145557 /lib/tls/ libpthread-2.3.4.so

FNDSM 22049 oaTMCstg mem REG 8,1 101748 147358 /lib/ libnsl-2.3.4.so

FNDSM 22049 oaTMCstg mem REG 8,3 217016 260100
/var/db/nscd/passwd

FNDSM 22049 oaTMCstg mem REG 8,3 217016 260102
/var/db/nscd/hosts

FNDSM 22049 oaTMCstg mem REG 0,24 2151291 19444925
/apps/TMCstg/apps/tech_st/10.1.2/lib/libnnz10.so
(rcdn9-40a-filer14a:/vol/local1/c6-a1-oa1-istg_apps_ts2TMCstg)

FNDSM 22049 oaTMCstg mem REG 0,24 13685788 115920002
/apps/TMCstg/apps/tech_st/10.1.2/lib/libclntsh.so.10.1
(rcdn9-40a-filer14a:/vol/local1/c6-a1-oa1-istg_apps_ts2TMCstg)

FNDSM 22049 oaTMCstg 0w CHR 1,3 2373 /dev/null

FNDSM 22049 oaTMCstg 1w REG 0,22 369 55601440
/apps/reports/TMCSTG/log/FNDSM3273265.mgr
(rcdn9-40b-filer14b:/vol/local2/c6-a1-oa1-istg_reports)

FNDSM 22049 oaTMCstg 2w REG 0,22 369 55601440
/apps/reports/TMCSTG/log/FNDSM3273265.mgr
(rcdn9-40b-filer14b:/vol/local2/c6-a1-oa1-istg_reports)

FNDSM 22049 oaTMCstg 3w REG 0,22 369 55601440
/apps/reports/TMCSTG/log/FNDSM3273265.mgr
(rcdn9-40b-filer14b:/vol/local2/c6-a1-oa1-istg_reports)

FNDSM 22049 oaTMCstg 4r REG 0,24 806912 70583667
/apps/TMCstg/apps/apps_st/appl/fnd/12.0.0/mesg/US.msb
(rcdn9-40a-filer14a:/vol/local1/c6-a1-oa1-istg_apps_ts2TMCstg)

FNDSM   22049 oaTMCstg    5u  IPv4 133215805                UDP

localhost.localdomain:20694

FNDSM   22049 oaTMCstg    6u  IPv4 133215806                TCP

c6-a1-oa1-istg-34:38875->server-100: 1546 (ESTABLISHED)

FNDSM   22049 oaTMCstg   11u  IPv4 120139934                TCP

c6-a1-oa1-istg-34:47812->c6-a1-oa1-istg-34:smux (CLOSE_WAIT)

FNDSM   22049 oaTMCstg   12u  IPv4 133215761                TCP

c6-a1-oa1-istg-34:1657->c6-a1-oa1-istg-34:38874 (ESTABLISHED)

/apps/reports/TMCSTG/log > ls -ltr FNDSM*

-rw-r--r-- 1 oaTMCstg dba 396 Oct 11 23:09 FNDSM3268699.mgr

-rw-r--r-- 1 oaTMCstg dba 396 Oct 11 23:09 FNDSM3262217.mgr

-rw-rw-r-- 1 oaTMCstg dba 1681 Nov 2 02:19 FNDSM3269225.mgr

-rw-rw-r-- 1 oaTMCstg dba 2130 Nov 2 02:20 FNDSM3269216.mgr

-rw-rw-r-- 1 oaTMCstg dba 369 Nov 2 02:20 FNDSM3268972.mgr

-rw-rw-r-- 1 oaTMCstg dba 8698 Nov 28 01:53 FNDSM3268973.mgr_28Nov2012

-rw-rw-r-- 1 oaTMCstg dba 8698 Nov 28 01:53 FNDSM3268973.mgr

-rw-rw-r-- 1 oaTMCstg dba 37428302 Nov 28 02:16 FNDSM3272476.mgr_28Nov2012

-rw-rw-r-- 1 oaTMCstg dba 37428302 Nov 28 02:16 FNDSM3272476.mgr

-rw-rw-r-- 1 oaTMCstg dba 226 Nov 28 02:43 FNDSM3273214.mgr.Z

-rw-rw-r-- 1 oaTMCstg dba 2518432 Nov 28 04:18 FNDSM3273213.mgr

-rw-rw-r-- 1 oaTMCstg dba 227 Nov 28 05:10 FNDSM3273265.mgr.Z

-rw-rw-r-- 1 oaTMCstg dba 176843 Nov 28 05:35 FNDSM3273264.mgr.Z

test-axx-cbz-100 TMCSTG_BALANCE

/apps/reports/TMCSTG/log >

test-axx-cbz-100 TMCSTG_BALANCE

/apps/reports/TMCSTG/log > ps -ef | grep -i FNDSM

oaTMCstg 22049 1 0 05:09 ? 00:00:00 FNDSM

oaTMCstg 25235 10639 0 05:52 pts/12 00:00:00 grep -i FNDSM