Interesting question on FNDSM with SQL*Loader relation
- From: "Shastry(DBA)" <shastry17@xxxxxxxxx>
- To: ora-apps-dba@xxxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>, Jurijs Veļikanovs <J.Velikanovs@xxxxxxxx>
- Date: Fri, 21 Dec 2012 09:56:40 +0530
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:*
* *
a. *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.
Didnt 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
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/.nfs0000000003506920000001d6
(rcdn9-40b-filer14b:/vol/local2/c6-a1-oa1-istg_reports) ---Stale info
FNDSM 22049 oaTMCstg 2w REG 0,22 369 55601440
/apps/reports/TMCSTG/log/.nfs0000000003506920000001d6
(rcdn9-40b-filer14b:/vol/local2/c6-a1-oa1-istg_reports)
FNDSM 22049 oaTMCstg 3w REG 0,22 369 55601440
/apps/reports/TMCSTG/log/.nfs0000000003506920000001d6
(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)
test-axx-cbz-100 TMCSTG_BALANCE
/apps/reports/TMCSTG/log >
* *
*When zipped in advance the request errorred as below:*
* *
Arguments
------------
/apps/TMCstg/apps/apps_st/appl/xxTMC/12.0.0/data/in/TESTsSweeney1.csv
------------
SQL*Loader-128: unable to begin a session
ORA-01017: invalid username/password; logon denied
SQL*Loader: Release 10.1.0.5.0 - Production on Wed Nov 28 05:55:49 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-128: unable to begin a session
ORA-01017: invalid username/password; logon denied
Thanks,
-An
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Interesting question on FNDSM with SQL*Loader relation - Shastry(DBA)
