2013年5月21日 星期二

升級成IE8 之後,無法登入Oracle ERP Web 畫面

Login 頁面左下角顯示 "網頁發生錯誤",雙擊後錯誤如下:
元素找不到
Common2_2_24_1.js


行: 2722
字元: 1
程式碼: 0
URI: http://<hostname.domain>/OA_HTML/cabo/jsLibs/Common2_2_24_1.js


解決方法:修改Regedit鍵值
路徑
HKEY_CLASSES_ROOT/TypeLib/{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}/1.1/0/win32
值由
C:\WINDOWS\system32\shdocvw.dll 改為
C:\WINDOWS\system32\ieframe.dll

2013年5月9日 星期四

設定TDPO


1.設定tdpo.opt
---------------------
cd /usr/tivoli/tsm/client/oracle/bin64/
copy tdpo.opt.smp to tdpo.opt
修改內容如下:
DSMI_ORC_CONFIG    /usr/tivoli/tsm/client/oracle/bin64/dsm.opt
DSMI_LOG           /tmp
TDPO_NODE          <nodename> TSM設定的node name

2.建立dsm.sys
---------------------
copy /usr/tivoli/tsm/client/api/bin64/dsm.sys.smp to
     /usr/tivoli/tsm/client/oracle/bin64/dsm.sys
修改內容如下:
SErvername  tdpo
CHANGINGRETRIES 0
NODENAME <nodename> TSM設定的node name
PASSWORDACCESS prompt
COMMMethod         TCPip
TCPPort            1500
TCPServeraddress   <address> TSM Server的address

3.建立dsm.opt
---------------------
cd /usr/tivoli/tsm/client/oracle/bin64/
產生dsm.opt,內容如下:
SERVERNAME tdpo

4.在TSM上面註冊TDPO NOde(option)
------------------------------------------------
在TSM上面註冊Oracle Server
TSM:TSM_SERVER> reg node <nodename> orabakpw maxnummp=2 passexp=0

使用RMAN catalog的archiving and expiration設定來控制backup retention
TSM:TSM_SERVER> update node <nodename> backupdelete=yes

5.設定TSM Copy Group Options(option)
------------------------------------------------
因為RMAN每次備份產生的檔案都是唯一的,使得TSM上面備份的檔案永遠不會過期,
所以要設定copy group attribute : verdeleted = 0,讓TDPO可以從TSM backup storage pool
移除RMAN設定成inactive or expired的檔案
TSM:TSM_SERVER> update copygroup standard standard standard verdeleted=0

6.產生TSM Server使用的密碼檔
------------------------------------------
為了不使每次做RMAN backup都必須輸入密碼,我們可以建立密碼檔
先看環境設定:
root@hostname> tdpoconf showenvironment
或是
root@hostname> tdpoconf showenvironment -tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt


IBM Tivoli Storage Manager for Databases:
Data Protection for Oracle
Version 5, Release 3, Level 3.0
(C) Copyright IBM Corporation 1997, 2006. All rights reserved.


ANS1035S (RC406)  Options file '*' could not be found.

如果發生上面的錯誤,請依照下面指示除錯:
copy /usr/tivoli/tsm/client/ba/bin/dsm.opt and dsm.sys to
        /usr/tivoli/tsm/client/api/bin64


設定密碼:
root@hostname> tdpoconf password
或是
root@hostname> tdpoconf password -tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt

7.建立symbolic links in the Oracle library directory
---------------------------------------------------------
oracle@hostname> ln -s /usr/lib/libobk64.a $ORACLE_HOME/lib/libobk.a

8.測試TDPO連線
--------------------------
設定TDPO_OPTFILE環境變數指向tdpo.opt
oracle@hostname> export TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt
oracle@hostname> sbttest test
The sbt function pointers are loaded from libobk.a(shr.o) library.
Return code -1 from sbtinit, bsercoer = 7011, bsercerrno = 106
sbtopen: System error - eg. malloc, fork errors

確保 /usr/tivoli/tsm/client/oracle/bin64/tdpoerror.log 或是 /tmp/tdpoerror.log,
ORACLE用戶要有寫入權限

依照上文的設定,我們改變/tmp/tdpoerror.log 的權限:
chmod 777 /tmp/tdpoerror.log

oracle@hostname> sbttest test
The sbt function pointers are loaded from libobk.a(shr.o) library.
-- sbtinit succeeded
Return code -1 from sbtinit, bsercoer = 0, bsercerrno = 0
Message 0 not found;  product=RDBMS; facility=SBT

99.其他參考文件
-------------------------
IBM TSM:
http://publib.boulder.ibm.com/infocenter/tivihelp/v1r1/index.jsp?topic=%2Fcom.ibm.itsmerp.doc%2Ffbro000017.htm
TDP FOR TSM 單機實作:
http://www.aixchina.net/club/viewthread.php?action=printable&tid=70057

2013年4月29日 星期一

UltraEdit 的15個使用小技巧

轉貼自萬隆小站,http://bruce.idv.tw/forum/viewtopic.php?f=6&t=312

1. UEDIT查找的快捷鍵是Ctrl+F(find)或ALT-F3,替換的快捷鍵是Ctrl+R(replace),再次查找的快捷鍵是F3。

2. 一般尋找與取代功能:

※在尋找/取代內將「正規化」的√給去除,並分別在尋找/取代的框格內輸入所需的文字,執行尋找/取代即可。 
※如果需要尋找Enter鍵,在尋找框中可輸入^p(注意p是小寫)來代替Enter鍵。

Symbol
Function
^^
尋找 ^ 符號 
^s
巨集執行時尋找選擇的字串(反白的字串)
^c
巨集執行時尋找剪貼簿的內容
^b
尋找分頁點
^p
尋找換行(dos/windows模式)
^r
尋找換行(MAC模式)
^n
尋找換行(unix模式)
^t
尋找tab

選擇所有內文之間的游標位置和搜尋目標,按住Shift鍵,在當時的搜尋是開始尋找下一個按鈕。


3. 沒有註冊碼,如何有效使用Ultraedit呢?
很多人立即想到使用crack版,可實際追究起來,那是違法的事情。
實際上,Ultraedit並沒有把路子完全堵死。我們可以使用一些小技巧來躲掉這個問題。
修改ultraedit的快捷方式,將命令行改為:E:\uedit9\UEDIT32.EXE p:,
其中p:必須是你的機器上不存在的磁碟分割,這樣,就不會有試用時間結束的問題了。

4. 如何去掉所編輯文本中包含特定字串的行? 
這則技巧是在UltraEdit的幫助文件裡提到.CTRL+R 叫出取代(Replace)視窗,
選取"正規化";然後用尋找 %*你的字串*^p 替換成空內容即可.
(UltraEdit的正規化引擎,進階->搜尋->正規化引擎->UltraEdit樣式)
如,我現在有個文件,需要去掉所有包含 http://www.baidu.net/ 這個字串的行,
尋找 %*http://www.baidu.net/*^p 替換成空即可.
注意,^p 是 DOS 文件類型的換行.如果是 Unix 類型文件,則用 ^n.

5. 如何在行末加入特定字元,比如逗號? 
有了上面的經驗,CTRL+R 叫出取代(Replace)視窗,
選取"正規化";然後可以尋找 ^p(或者^n,如果是Unix 文件),
用 ,^p(或者,^n)進行"全部取代"即可.補充一點,如果是 MAC(Apple) 類型文件,則換行符號為 ^r .

6. 如何刪除空行?
參考上面兩個例子,查找 ^p$ 然後替換為空即可.

7. 編輯文件如何加入時間戳 ?
F7 快捷鍵即可.你試試看?


8. 為何 複製(Copy)/貼上(Paste)功能不能用了?
UltraEdit有10個剪貼簿(clipboard),分別用Ctrl+0 - Ctrl+9 切換
Ctrl+0 是Windows 的,其他則為用戶自定義的.我在使用的過程中錯調用了 CTRL+n, 
結果內容就有問題了.你遇到過沒?

9. 即使是打開小文件也有遲延?
每次打開文件的時候總有幾秒鐘的耽擱.我的電腦效能可不算差.怎麼回事? 
網絡印表機搞得鬼! 
打開"進階"->"組態"->"編輯器"->"進階",
看看是不是選中了"讀取/還原印表機設定"?
如果是的話,去掉.

10. 刪除當前行內容 CTRL+e

11. 你按一下快捷鍵ALT+c試試,變為列編輯模式,可以一次輸入多行內容,只要你選好行範圍,編輯整齊的表格非常方便。

12. 游標位於某一行,按Ctrl+F2試試,有了標籤了,多設幾個,按住F2,它會往你設定的標籤處跳來跳去,很好玩(Alt+F2回前一個標籤)。

13. 發現沒有,有時候很多文件類型在Ultraedit裡沒有語法高亮度顯示,很麻煩不是。
不要急,從UE的網站上下一些方法的模組,找到你要的文件類型的語法文件,
將其內容追加到wordfile.txt裡頭,注意序號不要重複,一定要在1-20之間。網址是:
http://www.ultraedit.com/index.php?name ... age&pid=40

14.ctrl+b
寫程式的時候,括號一般要一一對應的,但是如果嵌套太多,看花眼了,怎麼辦?
你把游標放在括號開始的地方,按ctrl+b,UE 會幫你找到相對應的括號結尾的地方。
你還可以試試連著多按幾次ctrl+b。 

15.自定義快捷鍵
UE很多功能都有快捷鍵,但不是所有的都有。
UE 11.00有一個Text2html的功能。我工作中遇到了要對大量源始碼進行這種操作的情況。
我就想自己定一個快捷鍵。advanced(進階)/configuration(組態)/key mapping(快速鍵)
在commands(指令)裡面找到你要用的command.
我這裡是HTMLConvertSpecialChars(HTML-轉換特殊字符),然後點 Press new key(新鍵)下面的輸入框,
設置一個自己覺得爽,不衝突的快捷鍵。比如ctrl+alt+s.然後點ok.這樣用常用的功能,可以成倍提高效率。

2013年4月22日 星期一

Common DOS Batch command 常用DOS批次檔指令

轉貼自以下連結:
http://ms101robin.appspot.com/2012/09/03/Common%20DOS%20Batch%20command%20%E5%B8%B8%E7%94%A8DOS%E6%89%B9%E6%AC%A1%E6%AA%94%E6%8C%87%E4%BB%A4


1. ECHO 控制是否列印出指令. 有on或off參數, on表示要印出命令列, off則不印出命令列, (與在指今開頭上加上@符號相同作用)
  Ex:
  echo off 表示資料隱藏
  dir *.com
  echo on
  dir *.txt
  @type apple.bat

2. %n 替換參數. 一個百分號%再跟著一個數字, 數字即為第幾個參數, 最多可使用10個替換參數
Ex:
@echo off
echo .... 測試代換參數指令
echo .... 請稍後
echo .... 完成.....
echo %1 %2 %3 %4

3. PAUSE:是暫停批次檔的執行。當MS-DOS執行到PAUSE時就暫時中止目前的工作,並印出下列訊息:
press any key to contunue ……
Ex:
@echo off
echo .... 測試代換參數指令
echo .... 請稍後
echo .... 完成.....
echo %1 %2 %3 %4
pause
echo %5 %6

四、 REM(REMark)用來在批次檔執行中顯示某些訊息,用法是在批次檔中鍵入rem,後面跟著所要顯示的字串
Ex:
@echo off
echo .... 測試代換參數指令
echo .... 請稍後
echo .... 完成.....
rem 下面指令,開始做代換參數
echo %1 %2 %3 %4
pause
echo %5 %6

五、 IF
IF命令在批次檔中用來測試特定的條件,以決定是否執行某些命令。絛件的型態分為四種:
(1) IF EXIST
此條件是用來檢查某一檔案是否存在,若存在則條件成立,便執行指定的命令
Ex:
@echo off
if exist test1.bat type test1.bat
echo 執行程式結束
(2) IF String1 = = String2(= = 表相等性的比較)
Ex:
@echo off
echo .... 測試IF指令
echo %1
if %1 == a goto first
if %1 == b goto second
if %1 == c goto three
:first
echo 執行first (a)
goto finish
:second
echo 執行second (b)
goto finish
:three
echo 執行three (c)
goto finish
:finish
(3) IF ERRORLEVEL
ERRORLEVEL是由MS-DOS所管理的一個系統變數,目的是監視所有錯誤發生的情況。(類似ErrorMessage)
(4) IF NOT
IF NOT是當測試條件不成立時才執行後面命令
Ex:
@echo off
if not exist test1.bat type test1.bat
echo 執行程式結束

六、 GOTO
GOTO命令在批次檔中用來轉移控制權,可以指示批次檔跳至某一標記(line label)由一個冒號(:)跟著字元符號所組成的;label也可使用代換變數
Ex:
goto first 或 goto %1

七、 FOR
FOR命令允許批次檔中的其他命令,可以重覆地執行
for %%a IN (file1 file2 file3) DO del %%a
虛擬變數必須以兩個百分號(%%)起頭,in後面著參數列,do後面跟著要執行的命令
Ex:
@echo off
echo .... 測試FOR指令
echo .... 請稍後
echo .... 完成.....
echo ....
for %%a IN (test_a.bat test_b.bat test_c.bat) do copy %%a prn

八、 SHIFT
SHIFT命令允許在批次檔的起動命令中,使用超過10以上的實際參數
(%0~%9)

九、 CALL
模組化的程式設計,是將一個完整的程式分割成一個個獨立的模組(module),每個模組負責一項功能
Ex:
@echo off
echo .... 測試CALL指令
echo .... 請稍後
echo %1 %2
call test_b
echo %3 %4
echo 程式模組已執行完畢 (test_a.bat)
@echo off
echo 執行test_b程式
echo 即將執行test_c .....
call test_c
echo 執行test_b程式完畢~~~ (test_b.bat)
@echo off
echo 執行test_c程式
if exist test2.bat type test2.bat
echo 執行test_c程式完畢~~~ (test_b.bat)
執行 test_a

2013年4月16日 星期二

ORACLE日期時間函數


來源:http://www.cnblogs.com/chuncn/archive/2009/04/29/1381282.html

ORACLE日期時間函數大全

  TO_DATE格式(以時間:2007-11-02   13:45:25爲例)
        Year:    
        yy two digits 兩位年                顯示值:07
        yyy three digits 三位年                顯示值:007
        yyyy four digits 四位年                顯示值:2007
        Month:    
        mm    number     兩位月              顯示值:11
        mon    abbreviated 字符集表示          顯示值:11月,若是英文版,顯示nov  
        month spelled out 字符集表示          顯示值:11月,若是英文版,顯示november
        Day:    
        dd    number         當月第幾天        顯示值:02
        ddd    number         當年第幾天        顯示值:02
        dy    abbreviated 當週第幾天簡寫    顯示值:星期五,若是英文版,顯示fri
        day    spelled out   當週第幾天全寫    顯示值:星期五,若是英文版,顯示friday      
        ddspth spelled out, ordinal twelfth
              Hour:
              hh    two digits 12小時進制            顯示值:01
              hh24 two digits 24小時進制            顯示值:13
           
              Minute:
              mi    two digits 60進制                顯示值:45
           
              Second:
              ss    two digits 60進制                顯示值:25
           
              其它
              Q     digit         季度                  顯示值:4
             WW    digit         當年第幾周            顯示值:44
             W    digit          當月第幾周            顯示值:1
           
        24小時格式下時間範圍爲: 0:00:00 - 23:59:59....    
        12小時格式下時間範圍爲: 1:00:00 - 12:59:59 ....
         
1. 日期和字符轉換函數用法(to_date,to_char)
       
selectto_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期轉化爲字符串
selectto_char(sysdate,'yyyy') as nowYear   from dual;   //獲取時間的年
selectto_char(sysdate,'mm')    as nowMonth from dual;   //獲取時間的月
selectto_char(sysdate,'dd')    as nowDay    from dual;   //獲取時間的日
selectto_char(sysdate,'hh24') as nowHour   from dual;   //獲取時間的時
selectto_char(sysdate,'mi')    as nowMinute from dual;   //獲取時間的分
selectto_char(sysdate,'ss')    as nowSecond from dual;   //獲取時間的秒

 
selectto_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual//

2.    
    selectto_char(to_date(222,'J'),'Jsp') from dual    
 
    顯示Two Hundred Twenty-Two  

3.求某天是星期幾    
   selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;    
  星期一    
   selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;    
   monday    
   設置日期語言    
   ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';    
   也可以這樣    
  TO_DATE('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')  

4. 兩個日期間的天數    
    select floor(sysdate -to_date('20020405','yyyymmdd')) from dual;  

5. 時間爲null的用法    
   select id, active_date from table1    
   UNION    
   select 1,TO_DATE(null) from dual;    
 
   注意要用TO_DATE(null)  

6.月份差
   a_date betweento_date('20011201','yyyymmdd') andto_date('20011231','yyyymmdd')    
   那麼12月31號中午12點之後和12月1號的12點之前是不包含在這個範圍之內的。    
   所以,當時間需要精確的時候,覺得to_char還是必要的
   
7. 日期格式衝突問題    
    輸入的格式要看你安裝的ORACLE字符集的類型, 比如: US7ASCII, date格式的類型就是: '01-Jan-01'    
    alter system set NLS_DATE_LANGUAGE = American    
    alter session set NLS_DATE_LANGUAGE = American    
    或者在to_date中寫    
    selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;    
    注意我這只是舉了NLS_DATE_LANGUAGE,當然還有很多,    
    可查看    
    select * from nls_session_parameters    
    select * from V$NLS_PARAMETERS  

8.    
   select count(*)    
   from ( select rownum-1 rnum    
       from all_objects    
      where rownum <=to_date('2002-02-28','yyyy-mm-dd') -to_date('2002-    
       02-01','yyyy-mm-dd')+1    
      )    
  whereto_char(to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )    
        not in ( '1', '7' )    
 
   查找2002-02-28至2002-02-01間除星期一和七的天數    
   在前後分別調用DBMS_UTILITY.GET_TIME, 讓後將結果相減(得到的是1/100秒, 而不是毫秒).  

9. 查找月份
select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1
select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1.03225806451613 10. Next_day的用法
Next_day(date, day) Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D

11
select to_char(sysdate,'hh:mi:ss') TIME from all_objects
注意:第一條記錄的TIME 與最後一行是一樣的
可以建立一個函數來處理這個問題
create or replace function sys_date return date is
begin
return sysdate;
end; select to_char(sys_date,'hh:mi:ss') from all_objects; 12.獲得小時數
extract()找出日期或間隔值的字段值
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
SQL> select sysdate ,to_char(sysdate,'hh') from dual; SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07 SQL> select sysdate ,to_char(sysdate,'hh24') from dual; SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19

13.年月日的處理
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months )
)
) days from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,
newer_date,
older_date
from (
select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date
from emp
)
)

14.處理月份天數不定的辦法
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual

16.找出今年的天數
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual

閏年的處理方法
to_char( last_day( to_date('02' | | :year,'mmyyyy') ), 'dd' )
如果是28就不是閏年

17.yyyy與rrrr的區別
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001

18.不同時區的處理
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
from dual;

19.5秒鐘一個間隔
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
from dual

2002-11-1 9:55:00 35786
SSSSS表示5位秒數

20.一年的第幾天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual310 2002-11-6 10:03:51

21.計算小時,分,秒,毫秒
select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual
)

select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss'); //
floor((date2-date1) /365) 作爲年
floor((date2-date1, 365) /30) 作爲月
d(mod(date2-date1, 365), 30)作爲日.

23.next_day函數 返回下個星期的日期,day爲1-7或星期日-星期六,1表示星期日
next_day(sysdate,6)是從當前開始下一個星期五。後面的數字是從星期日開始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六 --------------------------------------------------------------- select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
日期 返回的是天 然後 轉換爲ss24,round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from dual

25,trunc[截斷到最接近的日期,單位爲天] ,返回的是日期類型
select sysdate S1,
trunc(sysdate) S2, //返回當前日期,無時分秒
trunc(sysdate,'year') YEAR, //返回當前年的1月1日,無時分秒
trunc(sysdate,'month') MONTH , //返回當前月的1日,無時分秒
trunc(sysdate,'day') DAY //返回當前星期的星期天,無時分秒
from dual

26,返回日期列表中最晚日期
select greatest('01-1月-04','04-1月-04','10-2月-04') from dual

27.計算時間差
注:oracle時間差是以天數爲單位,所以換算成年月,日select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual //時間差-年
select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual //時間差-月
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual //時間差-天
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual //時間差-時
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual //時間差-分
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //時間差-秒

28.更新時間
注:oracle時間加減是以天數爲單位,設改變量爲n,所以換算成年月,日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改變時間-年
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual //改變時間-月
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改變時間-日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改變時間-時
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改變時間-分
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改變時間-秒

29.查找月的第一天,最後一天
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM dual;

三. 字符函數(可用於字面字符或數據庫列)

1,字符串截取
select substr('abcdef',1,3) from dual

2,查找子串位置
select instr('abcfdgfdhd','fd') from dual

3,字符串連接
select 'HELLO'||'hello world' from dual;

4, 1)去掉字符串中的空格
select ltrim(' abc') s1,
rtrim('zhang ') s2,
trim(' zhang ') s3 from dual
2)去掉前導和後綴
select trim(leading 9 from 9998767999) s1,
trim(trailing 9 from 9998767999) s2,
trim(9 from 9998767999) s3 from dual;5,返回字符串首字母的Ascii值
select ascii('a') from dual

6,返回ascii值對應的字母
select chr(97) from dual

7,計算字符串長度
select length('abcdef') from dual

8,initcap(首字母變大寫) ,lower(變小寫),upper(變大寫)
select lower('ABC') s1,
upper('def') s2,
initcap('efg') s3
from dual;

9,Replace
select replace('abc','b','xy') from dual;

10,translate
select translate('abc','b','xx') from dual; -- x是1位

11,lpad [左添充] rpad [右填充](用於控制輸出格式)
select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;
select lpad(dname,14,'=') from dept;

12, decode[實現if ..then 邏輯] 注:第一個是表達式,最後一個是不滿足任何一個條件的值
select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
例:
select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo//如果seed爲111,則取1000;爲200,取2000;其它取0
select seed,account_name,decode(sign(seed-111),1,'big seed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,則顯示大;爲200,則顯示小;其它則顯

示相等

13 case[實現switch ..case 邏輯]
SELECT CASE X-FIELD
WHEN X-FIELD < 40 THEN 'X-FIELD 小於 40'
WHEN X-FIELD < 50 THEN 'X-FIELD 小於 50'
WHEN X-FIELD < 60 THEN 'X-FIELD 小於 60'
ELSE 'UNBEKNOWN'
END
FROM DUAL 注:CASE語句在處理類似問題就顯得非常靈活。當只是需要匹配少量數值時,用Decode更爲簡潔。

四.數字函數
1,取整函數(ceil 向上取整,floor 向下取整)
select ceil(66.6) N1,floor(66.6) N2 from dual;

2, 取冪(power) 和 求平方根(sqrt)
select power(3,2) N1,sqrt(9) N2 from dual;

3,求餘
select mod(9,5) from dual;

4,返回固定小數位數 (round:四捨五入,trunc:直接截斷)
select round(66.667,2) N1,trunc(66.667,2) N2 from dual;

5,返回值的符號(正數返回爲1,負數爲-1)
select sign(-32),sign(293) from dual;

五.轉換函數
1,to_char()[將日期和數字類型轉換成字符類型]
1) select to_char(sysdate) s1,
to_char(sysdate,'yyyy-mm-dd') s2,
to_char(sysdate,'yyyy') s3,
to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
to_char(sysdate, 'hh24:mi:ss') s5,
to_char(sysdate,'DAY') s6
from dual;
2) select sal,to_char(sal,'$99999') n1,to_char(sal,'$99,999') n2 from emp

2, to_date()[將字符類型轉換爲日期類型]
insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));3, to_number() 轉換爲數字類型
select to_number(to_char(sysdate,'hh12')) from dual; //以數字顯示的小時數六.其他函數
1.user:
返回登錄的用戶名稱
select user from dual;2.vsize:
返回表達式所需的字節數
select vsize('HELLO') from dual;3.nvl(ex1,ex2):  
ex1值爲空則返回ex2,否則返回該值本身ex1(常用)
例:如果僱員沒有佣金,將顯示0,否則顯示佣金
select comm,nvl(comm,0) from emp;4.nullif(ex1,ex2):
值相等返空,否則返回第一個值
例:如果工資和佣金相等,則顯示空,否則顯示工資
select nullif(sal,comm),sal,comm from emp;5.coalesce:  
返回列表中第一個非空表達式
select comm,sal,coalesce(comm,sal,sal*10) from emp;6.nvl2(ex1,ex2,ex3) :
如果ex1不爲空,顯示ex2,否則顯示ex3
如:查看有佣金的僱員姓名以及他們的佣金
  select nvl2(comm,ename,') as HaveCommName,comm from emp;
七.分組函數
max min avg count sum
1,整個結果集是一個組
1) 求部門30 的最高工資,最低工資,平均工資,總人數,有工作的人數,工種數量及工資總和
select max(ename),max(sal),
min(ename),min(sal),
avg(sal),
count(*) ,count(job),count(distinct(job)) ,
sum(sal) from emp where deptno=30;
2, 帶group by 和 having 的分組
1)按部門分組求最高工資,最低工資,總人數,有工作的人數,工種數量及工資總和
select deptno, max(ename),max(sal),
min(ename),min(sal),
avg(sal),
count(*) ,count(job),count(distinct(job)) ,
sum(sal) from emp group by deptno;2)部門30的最高工資,最低工資,總人數,有工作的人數,工種數量及工資總和
select deptno, max(ename),max(sal),
min(ename),min(sal),
avg(sal),
count(*) ,count(job),count(distinct(job)) ,
sum(sal) from emp group by deptno having deptno=30;3, stddev 返回一組值的標準偏差
select deptno,stddev(sal) from emp group by deptno;
variance 返回一組值的方差差
select deptno,variance(sal) from emp group by deptno;

4, 帶有rollup和cube操作符的Group By
rollup 按分組的第一個列進行統計和最後的小計
cube 按分組的所有列的進行統計和最後的小計
select deptno,job ,sum(sal) from emp group by deptno,job;
select deptno,job ,sum(sal) from emp group by rollup(deptno,job);
cube 產生組內所有列的統計和最後的小計
select deptno,job ,sum(sal) from emp group by cube(deptno,job);

八、臨時表
只在會話期間或在事務處理期間存在的表.
臨時表在插入數據時,動態分配空間
create global temporary table temp_dept
(dno number,
dname varchar2(10))
on commit delete rows;
insert into temp_dept values(10,'ABC');
commit;
select * from temp_dept; --無數據顯示,數據自動清除
on commit preserve rows:在會話期間表一直可以存在(保留數據)
on commit delete rows:事務結束清除數據(在事務結束時自動刪除表的數據)

2013年4月8日 星期一

How to Register a Oracle EBS Custom Report


How to Register a Custom Report [ID 133991.1]

This article provides step by step instructions on how to register a
custom report in Oracle Applications.

1.Login to the System Administrator responsibility.
2.Navigate to: Concurrent > Program > Executable
3.Fill out the form similar to the following:
   Executable: CUSTOM
   Short Name: CUSTOM
   Application: Application Object Library
   Description: My Custom Program
   Execution Method: Oracle Reports
   Execution File Name: CUSTOM
4.Save
5.Navigate to: Concurrent > Program > Define
6.Fill out the form similar to the following:
   Program: My Custom Report
   Short Name: CUSTOM
   Application: Application Object Library
   Description: My custom report
   Executable:
        Name: CUSTOM
        Method: Oracle Reports
   Request:
        Use in SRS
        Restart on System Failure
   Output:
        Format: Text
        Save
        Print
        Columns: 132
        Rows: 45
        Style: Landscape
7.Save
8.Locate the Request Group name.
   a. Login to Sysadmin responsibility.
   b. Navigate to Security > Responsibility > Define
   c. Query up Responsibility.
   d. Request group is located in Request Group > Name frame.
9.Navigate to Security > Responsibility > Request
10.Query up the group of the responsibility that will run the report.
11.Add the report under the "Requests" frame as a Program; the report can queried using
     the Query > Find menu option or by clicking the Flashlight button on the Toolbar.
12.The report can now be run via the responsibility associated with the request group.

2013年4月3日 星期三

遇到 DB 等待事件 CACHE BUFFERS CHAINS,如何找出可能的 Hot Blocks

Step 1
執行以下的語法(多跑幾次),找出 SLEEPS 欄位中最大值所對應的 latch id (即 ADDR 欄位)

select CHILD#  "cCHILD"
     ,      ADDR    "sADDR"
     ,      GETS    "sGETS"
     ,      MISSES  "sMISSES"
     ,      SLEEPS  "sSLEEPS" 
     from v$latch_children 
     where name = 'cache buffers chains'
     order by 5, 1, 2, 3;

Step 2
再執行以下的語法,看一下 TCH 欄位,TCH 值越高表示 DB block 越頻繁地被 access => hot blocks

column segment_name format a35
select /*+ RULE */
   e.owner ||'.'|| e.segment_name segment_name,
   e.extent_id extent#,
   x.dbablk - e.block_id + 1 block#,
   x.tch,
   l.child#
from
   sys.v$latch_children l,
   sys.x$bh x,
   sys.dba_extents e
where
   x.hladdr = '&ADDR' and
   e.file_id = x.file# and
   x.hladdr = l.addr and
   x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc;

參考資料:Metalink Note 163424.1

2013年4月2日 星期二

SAP: 遠端連接至目的地 SAPOSS 發生錯誤:Error when opening an RFC connection

現象:
使用Tcode "SNOTE" 手動匯入SAP correction note,發生以下錯誤訊息:
SAP: 遠端連接至目的地 SAPOSS 發生錯誤:Error when opening an RFC connection

解決方式:
1. 使用sm59,點進SAPOSS,按下connection test








































產生錯誤訊息














2. 進Router 將權限打開
telnet Router 主機
# ps -ef | grep sap ==> 看看是否saprouter process存在,如果存在,就kill process
編輯 saprouttab ( vi saprouttab ) ,增加下面這行參數
P <Router IP> 194.39.131.34 3299
例如:P 10.10.1.10 194.39.131.34 3299
存檔 => 開啟 sap router => 執行 StartUp,內容如下:

#!/bin/ksh
export SECUDIR=/root/SAPROUTER
export SNC_LIB=/root/SAPROUTER/libsapcrypto.so
nohup ./saprouter -r -G login.log -W 50000 -V 2 -S 3299 -K "p:CN=JNSPR01, OU=0001158123, OU=SAProuter, O=SAP, C=DE" &


3. 使用sm59,點進SAPOSS,按下connection test













再使用"SNOTE" 手動匯入SAP correction note 即可成功。

2013年3月20日 星期三

收集SAP table statistics

t-code: db20 (Edit Table Statistics)



















1. (menu) statistics \ create statistics
2. 使用 t-code: db14去確認結果


















或者是telnet到主機上,使用 "ps -ef|grep -v grep|grep brconnect" 去看看是否已經執行完成。
3. 再回到 t-code: db20 去確認結果

2013年2月28日 星期四

Gather Schema Statistics 遇到 ORA-20005: object statistics are locked (stattype = ALL) 的錯誤


Error message:
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= HR percent= 100 degree = 16 internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS:
          object_name=HR.HR_ALL_ORGANIZATION_UNITS
          ***ORA-20005: object statistics are locked (stattype = ALL)***
+---------------------------------------------------------------------------+

Solution:
確認 log 出現的 table 使用以下SQL找的出來:

select owner,table_name
from dba_tab_statistics a
where a.STATTYPE_LOCKED = 'ALL'
and owner <> 'SYS';


執行:
exec dbms_stats.unlock_table_stats('HR', 'HR_ALL_ORGANIZATION_UNITS');

2013年2月21日 星期四

User無法在workflow notification找到單子簽核


###  現象  ###
user收到信之後,會點開信件做簽核,但是user將mail kill了,所以要到workflow notification 畫面上去做簽核,但是user找不到單子

### debug 過程  ###
-- 跟user借帳號,確認真的找不到單子,非user誤動作
-- 查詢user id是否跟epmall相同 => 相同
-- 查詢workflow是否正確 => 正確,停留在發信給主管簽核的workflow
-- 直接查詢wf_notifications table找到此張notification,狀態是open and send,表示尚未簽核,並且有發信給user,但是recipient_role欄位是寄給另一位使用者,非該位主管帳號
-- 查詢帳號,發現這位主管的employee assign給2位ERP User
-- 請該主管使用這2個帳號,將未結單全部簽完,再將他不要使用的那個ERP帳號,將employee連結拿掉,保持employee and fnd_user是一對一關係

### 原因  ###
employee 和erp user是一對多的關係,改成一對一即可

###  解決方式  ###
employee 和erp user是一對多的關係,改成一對一即可

delete item information 效能不彰

###  現象  ###
執行delete item information 發現刪除一顆料需要2~3分鐘,所以做trace,發現瓶頸點是這段 SQL:

SELECT 1
FROM   DUAL
WHERE  :ORGANIZATION_ID =
(SELECT TO_NUMBER(PROFILE_OPTION_VALUE)
FROM   FND_PROFILE_OPTIONS FPO,
FND_PROFILE_OPTION_VALUES FPOV
WHERE   FPO.PROFILE_OPTION_NAME ='SP_ORGANIZATION_ID'    
AND       FPO.PROFILE_OPTION_ID      = FPOV.PROFILE_OPTION_ID
AND      FPO.APPLICATION_ID           = FPOV.APPLICATION_ID
AND      FPOV. LEVEL_ID                  = 10001)
AND EXISTS
(SELECT NULL
FROM   QP_PRICING_ATTRIBUTES
WHERE PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND     PRODUCT_ATTRIBUTE              = 'PRICING_ATTRIBUTE1'
AND     PRODUCT_ATTR_VALUE IS NOT NULL
AND     PRODUCT_ATTR_VALUE <> 'ALL'
AND     PRODUCT_ATTR_VALUE = :ITEM_ID
UNION
SELECT NULL
FROM   QP_PRICING_ATTRIBUTES
WHERE PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND     PRODUCT_ATTRIBUTE              = 'PRICING_ATTRIBUTE1'
AND     PRODUCT_ATTR_VALUE IS NOT NULL
AND     COMPARISON_OPERATOR_CODE =  '='
AND     PRICING_ATTR_VALUE_FROM = :ITEM_ID)

前往metalink尋找到Note: 1063320.1 Performance Problem When Delete Items (BMCDEL)
發現到有相同的SQL語句,建議apply patch 9273708

###  原因  ###
The issue is caused by the Advance Pricing Delete constraint query. The problem is identified in the bug Bug:9273708 . This regression bug was due to the change done as per Bug:7503062

### 解決方式  ###
1. Download and review the readme and pre-requisites for    Patch:9273708

2. Ensure that you have taken a backup of your system before applying the recommended patch. 

3. Apply the patch in a test environment. 

4. Confirm the following file versions: 
$BOM_TOP/patch/115/import/US/bomdelc.ldt should have version 115.14.115100.44


You can use the commands like the following:
strings -a $BOM_TOP/patch/115/import/US/bomdelc.ldt |grep '$Header'.


5. Retest the issue. 

6. Migrate the solution as appropriate to other environments.

###  防範措施  ###
N/A

2013年2月19日 星期二

Oracle ERP收集統計值 (效能調教相關)

OM-PERF: Performance Tuning - FAQ [ID 113573.1]

1. What is CBO?

CBO stands for Cost Based Optimization. The R11i application was written to use the CBO. It determines the most efficient way to execute a SQL statement based on statistical data stored in the data dictionaries. These stored statistics are used to compute the cost of executing the SQL. The execution plan with the lowest cost is the one chosen by the optimizer.

2. Why the switch from RBO to CBO in 11i?

The Rule Based Optimizer (RBO) uses a fixed ranking system to determine the most efficient access method when executing a SQL statement. This method is not as flexible as CBO; which takes into consideration the data distribution characteristics of the database. CBO also offers a number of enhancements which are not available with RBO.

3. Is RBO obsolete in R11i?

No, RBO still exists. By default the 11i apps will use CBO. However, by the use of hints, RBO can still be invoked.

4. How do you gather the statistics necessary for CBO?

In R11i the FND_STATS package is used to generate statistics on the database. FND_STATS is a PL/SQL wrapper around the DBMS_STATS package, which issues the ANALYZE command. However, you should not use the ANALYZE command or DBMS_STATS package directly. Doing so may result in incomplete statistics being generated.

Use the following command to gather schema statistics:

SQL> exec fnd_stats.gather_schema_statistics('ONT') --- For a specific schema
SQL> exec fnd_stats.gather_schema_statistics('ALL') --- For all schemas

You can also run the following concurrent programs from System Administrator
to generate statistics:

Analyze All Index Column Statistics
Backup Table Statistics
Gather All Column Statistics
Gather Column Statistics
Gather Schema Statistics
Gather Table Statistics
Restore Table Statistics

When you gather statistics for a table or a whole schema, it cascades down and gathers also the statistics for all indexes on each table and all columns for that particular table or schema.

5. How can you tell whether the database objects were analyzed?

Use the Verify Stats report to determine whether the current statistics are accurate. This report is a utility provided with FND_STATS, and can be run as follows:

SQL> set server output on
SQL> set long 10000
SQL> exec fnd_stats.verify_stats('ONT', 'ONT.OE_ORDER_HOLDS_ALL');

This procedure takes a schema name and/or a list of comma separated table_names and checks the stats on all these objects. It then spools out a report. Some important columns to look at are LAST_ANALYZED and NUM_ROWS.

6. When slow performance is encountered check that the profile options OM: Debug is set to 'No' and OM: Debug Level is 'null'. If these profile options are set to a value of 'yes' for OM: Debug or other than NULL for OM: Debug Level, then system performance maybe adversely affected.
--------------------------------------------------------------------------------

Reference:http://space.itpub.net/35489/viewspace-748538
Oracle10g或以上版本DB:
exec dbms_stats.gather_schema_stats(ownname => 'DFMS', ptions => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
     -- -- 11g取样值比较大,可以使用samplesize,10g好像是5~20%,可以根据需要调整
method_opt => 'for all columns size auto',
    -- 一般第一次收集的时候使用 'for all columns size 1' 删除所有列上的直方图,之
      后可以使用 size auto , 逐渐调整,稳定后推荐使用 'for all columns size repeat'
cascade=>TRUE,
degree => 8 ) ;

EBS使用的统计信息方式(区别:规定好了哪些列需要直方图):
 Gather All Column Statistics--FND_STATS.GATHER_ALL_COLUMN_STATS()
 Gather Column Statistics--FND_STATS.GATHER_COLUMN_STATS()
 Gather Schema Statistics--FND_STATS.GATHER_SCHEMA_STATS()
 Gather Table Statistics--FND_STATS.GATHER_TABLE_STATS()

Sample:
分析一个SCEHMA (EBS系统) :
exec  fnd_stats.GATHER_TABLE_STATS('PA');

分析一个SCEHMA (一般系统) :
exec dbms_stats.gather_schema_stats(
  ownname          => 'HRM',
  options          => 'GATHER AUTO',
  estimate_percent => dbms_stats.auto_sample_size,
  method_opt       => 'for all columns size repeat', -- 稳定后推荐repeat
      cascade          =>TRUE,
  degree           => 12
    )

2013年1月27日 星期日

How to Create a Second OPP Concurrent Manager in a Node Different Than The Primary Node

Applies to:
Oracle Application Object Library - Version: 11.5.10.2 to 12.0.4 - Release: 11.5 to 12

Solution:

1.  Connect to applications as System Administrator.
2.  Navigate to:
     Concurrent -> Manager -> Define
3.  Enter the new Manager information.
     a.  Be sure to select "Output Post Processor" from the Type list of values.
     b.  Select the Primary and Secondary nodes as needed in the
          "Parallel Concurrent Processing Details" section.
     c.  Be sure that Program Library Name is FNDSVC.
4.  Save the new Manager.
5.  Restart the Concurrent Managers.


Reference:
How to Create a Second OPP Concurrent Manager in a Node Different Than The Primary Node [ID 729883.1]

2013年1月24日 星期四

PCP in RAC

# PCP發生錯誤的種類:
1.The database instance fail
2.The Database node server fail
3.The Applications/Middle-Tier server fail

# CM分類:
Internal Concurrent Manager (FNDLIBR) -
  跟SM溝通
  只能在單機上跑,負責service manager的start/stop/restart/failover
Service Manager (FNDSM process) -
  跟ICM,CM, and non-Manager Service processes溝通
  SM環境變數由APPSORA.env and gsmstart.sh script設定
  TWO_TASK變數名稱要跟gv$instance的instance_name相同
  需要apps_<sid> listener在每個CP node啟動,以支援 SM 跟 local instance 之間的溝通
  每個node都需要啟動SM
Internal Monitor (FNDIMON process) -
  跟ICM溝通
  IM監控ICM,如果ICM fail會將ICM restart
  每個ICM可能存在的node都要起IM
  ICM如果在多個node啟動,只有ICM會存在第一個啟動的node上面,其他node上面的ICM會被終止
Standard Manager (FNDLIBR process) -
  跟SM, Batch and OLTP process溝通
Transaction Manager -
  跟SM, Batch, OLTP and Form process溝通
  See Note:240818.1 regarding Transaction Manager communication and setup requirements for RAC.

# 設定步驟:
1.11.5.8之后的版本使用GSM做設定
2.In <SID>.xml 設定變數s_appldcp = ON
3.設定之前先備份tnsnames.ora,listener.ora and sqlnet.ora,或是COMMON_TOP/admin/scripts/<SID> 之下被異動的檔案
4.執行a_dautocfg.sh on each cluster node
5.執行完之後,每個node的tnsnames.ora會包含其他node的alias
6.確保tnsnames.ora的alias包含在GV$INSTANCE之中,在開啟CM的過程中,SM會依序建立連線
  檔案中包_含的entry會跟變數 TWO_TASK 相同 (in APPSORA.env)
7.確認FNDSM_<SID> entry 已經加入listener.ora
8.AutoConfig 會異動以及重新設定 database profiles
9.確保Applications Listener is active on each node in the cluster
10.確保每個node都有註冊:system administrator > Install > Nodes
11.進入system administrator > Concurrent > Manager > Define,根據server loading,設定每個CM的primary and secondary node names
12.在開Manager processes之前,要先編輯每個node的APPSORA.env,這是為了指定包含INSTANCE_NAME參數的
   TWO_TASK entry
13.進入system administrator > Concurrent > Manager > Administer 確認Service Manager and Internal Monitor are activated on the secondary node. The Internal Monitor should not be active on the primary cluster node.
14.Stop and restart the Concurrent Manager processes on their primary node(s).檢查配制是否合乎規劃,
   並且FNDSM process (the Service Manager)會伴隨著 FNDIMON process (Internal Monitor)出現

# Failover的限制
當node failed時,其他node必須接手failed node的manager,所以必須讀到相同的log file and output file,所以必須使用shared disk
因此 FNDFS_<HOST> entry in tnsnames.ora under the 8.0.6 ORACLE_HOME location 需要設定,local node 放在第一順位
範例如下:

FNDFS_coe-srv5-pc=(DESCRIPTION=
                (ADDRESS_LIST=
                 (ADDRESS=(PROTOCOL=tcp)(HOST=coe_svr5_pc)(PORT=1231))
                 (ADDRESS=(PROTOCOL=tcp)(HOST=coe_svr7_pc)(PORT=1232)))
                  (CONNECT_DATA=(SID=FNDFS))
                )


---------------------------------------------------------------------------------------
Reference:
Concurrent Manager Setup and Configuration Requirements in an 11i RAC Environment [ID 241370.1]

PURPOSE
-----------------------------
Configuring parallel concurrent processing allows you to distribute concurrent
managers, and workload across multiple nodes in a cluster, or networked environment.
PCP can also be implemented in a RAC environment in order to provide automated
failover of workload should the primary (source), or secondary (target) concurrent
processing nodes, or RAC instances fail.  There are several different failure
scenarios that can occur depending on the type of Applications Technology Stack
implementation that is performed.

The basic failure scenarios are:

1. The database instance that supports the CP, Applications,  and Middle-Tier
   processes such as Forms, or iAS can fail.
2. The Database node server that supports the CP, Applications,  and Middle-Tier
   processes such as Forms, or iAS can fail.
3. The Applications/Middle-Tier server that supports the CP (and Applications)
   base can fail.

The concurrent processing tier can reside on either the Applications, Middle-Tier,
or Database Tier nodes.  In a single tier configuration, non PCP environment, a
node failure will impact Concurrent Processing operations due to any of these
failure conditions. In a multi-node configuration the impact of any these types
of failures will be dependent upon what type of failure is experienced, and how
concurrent processing is distributed among the nodes in the configuration. Parallel
Concurrent Processing provides seamless failover for a Concurrent Processing
environment in the event that any of these types of failures takes place.

In an Applications environment where the database tier utilizes Listener
(server) load balancing, and in a non-load balanced environment, there
are changes that must be made to the default configuration generated by
Autoconfig so that CP initialization, processing, and PCP functionality are
initiated properly on their respective/assigned nodes.  These changes are
described in the next section - Concurrent Manager Setup and Configuration
Requirements in an 11i RAC Environment.

The current Concurrent Processing architecture with Global Service Management
consists of the following processes and communication model, where each process
is responsible for performing a specific set of routines and communicating with
parent and dependent processes.

Internal Concurrent Manager (FNDLIBR process) - Communicates with the Service
Manager.

The Internal Concurrent Manager (ICM)  starts, sets the number of active processes,
monitors, and terminates all other concurrent processes through requests made to
the Service Manager, including restarting any failed processes.  The ICM also
starts and stops, and restarts the Service Manager for each node.  The ICM will
perform process migration during an instance or node failure.  The ICM will be
active on a single node.  This is also true in a PCP environment, where the ICM
will be active on at least one node at all times.

Service Manager (FNDSM process) - Communicates with the Internal Concurrent Manager,
Concurrent Manager, and non-Manager Service processes.

The Service Manager (SM) spawns, and terminates manager and service processes (these
could be Forms, or Apache Listeners, Metrics or Reports Server, and any other process
controlled through Generic Service Management).  When the ICM terminates the SM that
resides on the same node with the ICM will also terminate.  The SM is ‘chained’ to
the ICM.  The SM will only reinitialize after termination when there is a function it
needs to perform (start, or stop a process), so there may be periods of time when the
SM is not active, and this would be normal.  All processes initialized by the SM
inherit the same environment as the SM.  The SM’s environment is set by APPSORA.env
file, and the gsmstart.sh script.  The TWO_TASK used by the SM to connect to a RAC
instance must match the instance_name from GV$INSTANCE.  The apps_<sid> listener must
be active on each CP node to support the SM connection to the local instance.  There
should be a Service Manager active on each node where a Concurrent or non-Manager
service process will reside.

Internal Monitor (FNDIMON process) - Communicates with the Internal Concurrent
Manager.

The Internal Monitor (IM)  monitors the Internal Concurrent Manager, and restarts any
failed ICM on the local node.  During a node failure in a PCP environment the IM will
restart the ICM on a surviving node (multiple ICM's may be started on multiple nodes,
but only the first ICM started will eventually remain active, all others will
gracefully terminate).  There should be an Internal Monitor defined on each node
where the ICM may migrate.

Standard Manager (FNDLIBR process) - Communicates with the Service Manager and any
client application process.

The Standard Manager is a worker process, that initiates, and executes client requests
on behalf of Applications batch, and OLTP clients.

Transaction Manager - Communicates with the Service Manager, and any user process
initiated on behalf of a Forms, or Standard Manager request.  See Note:240818.1
regarding Transaction Manager communication and setup requirements for RAC.

SCOPE & APPLICATION
-----------------------------
This article is provided for Applications development, product management, system
architects, and system administrators involved in deploying and configuring Oracle
Applications in a RAC environment. This document will also be useful to field
engineers and consulting organizations to facilitate installations and configuration
requirements of Applications 11i in a RAC environment.

Concurrent Manager Setup and Configuration Requirements in an 11i RAC Environment
-----------------------------
In order to set up Setup Parallel Concurrent Processing Using AutoConfig with GSM,
follow the instructions in the 11.5.8 Oracle Applications System Administrators Guide
under Implementing Parallel Concurrent Processing using the following steps:

1. Applications 11.5.8 and higher is configured to use GSM. Verify the configuration
   on each node (see WebIV Note:165041.1).
2. On each cluster node edit the Applications Context file (<SID>.xml), that resides
   in APPL_TOP/admin, to set the variable <APPLDCP oa_var="s_appldcp"> ON </APPLDCP>.
   It is normally set to OFF.  This change should be performed using the Context
   Editor.
3. Prior to regenerating the configuration, copy the existing tnsnames.ora,
   listener.ora and sqlnet.ora files, where they exist, under the 8.0.6 and iAS
   ORACLE_HOME locations on the each node to preserve the files (i.e./<some_
   directory>/<SID>ora/$ORACLE_HOME/network/admin/<SID>/tnsnames.ora). If any of
   the Applications startup scripts that reside in COMMON_TOP/admin/scripts/<SID>
   have been modified also copy these to preserve the files.
4. Regenerate the configuration by running adautocfg.sh on each cluster node as
   outlined in Note:165195.1.
5. After regenerating the configuration merge any changes back into the tnsnames.ora,
   listener.ora and sqlnet.ora files in the network directories, and the startup
   scripts in the COMMON_TOP/admin/scripts/<SID> directory.  Each nodes tnsnames.ora
   file must contain the aliases that exist on all other nodes in the cluster. When
   merging tnsnames.ora files ensure that each node contains all other nodes
   tnsnames.ora entries.  This includes tns entries for any Applications tier nodes
   where a concurrent request could be initiated, or request output to be viewed.
6. In the tnsnames.ora file of each Concurrent Processing node ensure that there is
   an alias that matches the instance name from GV$INSTANCE of each Oracle instance
   on each RAC node in the cluster.  This is required in order for the SM to establish
   connectivity to the local node during startup.  The entry for the local node will
   be the entry that is used for the TWO_TASK in APPSORA.env (also in the
   APPS<SID>_<HOSTNAME>.env file referenced in the Applications Listener [APPS_<SID>]
   listener.ora file entry "envs='MYAPPSORA=<some directory>/APPS<SID>_<HOSTNAME>.env)
   on each node in the cluster (this is modified in step 12).
7. Verify that the FNDSM_<SID> entry has been added to the listener.ora file under
   the 8.0.6 ORACLE_HOME/network/admin/<SID> directory. See WebiV Note:165041.1 for
   instructions regarding configuring this entry. NOTE: With the implementation of
   GSM the 8.0.6 Applications, and 9.2.0 Database listeners must be active on all PCP
   nodes in the cluster during normal operations.
8. AutoConfig will update the database profiles and reset them for the node from
   which it was last run. If necessary reset the database profiles back to their
   original settings.
9. Ensure that the Applications Listener is active on each node in the cluster where
   Concurrent, or Service processes will execute.  On each node start the database
   and Forms Server processes as required by the configuration that has been
   implemented.
10. Navigate to Install > Nodes and ensure that each node is registered. Use the node
    name as it appears when executing a ‘nodename’ from the Unix prompt on the server.
    GSM will add the appropriate services for each node at startup.
11. Navigate to Concurrent > Manager > Define, and set up the primary and secondary
    node names for all the concurrent managers according to the desired configuration
    for each node’s workload. The Internal Concurrent Manager should be defined on the
    primary PCP node only.  When defining the Internal Monitor for the secondary
    (target) node(s), make the primary node (local node) assignment, and assign a
    secondary node designation to the Internal Monitor, also assign a standard work
    shift with one process.
12. Prior to starting the Manager processes it is necessary to edit the APPSORA.env
    file on each node in order to specify a TWO_TASK entry that contains the
    INSTANCE_NAME parameter for the local nodes Oracle instance, in order to bind
    each Manager to the local instance.  This should be done regardless of whether
    Listener load balancing is configured, as it will ensure the configuration
    conforms to the required standards of having the TWO_TASK set to the instance
    name of each node as specified in GV$INSTANCE.  Start the Concurrent Processes
    on their primary node(s).  This is the environment that the Service Manager
    passes on to each process that it initializes on behalf of the Internal
    Concurrent Manager.  Also make the same update to the file referenced by the
    Applications Listener APPS_<SID> in the listener.ora entry "envs='MYAPPSORA=
    <some directory>/APPS<SID>_<HOSTNAME>.env" on each node.
13. Navigate to Concurrent > Manager > Administer and verify that the Service Manager
    and Internal Monitor are activated on the secondary node, and any other
    addititional nodes in the cluster. The Internal Monitor should not be active on
    the primary cluster node.
14. Stop and restart the Concurrent Manager processes on their primary node(s), and
    verify that the managers are starting on their appropriate nodes. On the target
    (secondary) node in addition to any defined managers you will see an FNDSM
    process (the Service Manager), along with the FNDIMON process (Internal Monitor).

Failover Considerations
-----------------------------
In order to have log and output files available to each node during an extended node
failure, each log and out directory needs to be made accessible to all other CP nodes
in the cluster (placed on shared disk).

In order to view log and output files from a failed node during an outage/node
failure, the FNDFS_<HOST> entry in tnsnames.ora under the 8.0.6 ORACLE_HOME location
on each node should be configured using an ADDRESS_LIST following the example below,
to provide connect time failover.  This entry should be placed in the tnsnames.ora
file on each node that supports Concurrent Processing, with the local node first in
the ADDRESS_LIST entry.

  FNDFS_coe-srv5-pc=(DESCRIPTION=
                (ADDRESS_LIST=
                 (ADDRESS=(PROTOCOL=tcp)(HOST=coe_svr5_pc)(PORT=1231))
                 (ADDRESS=(PROTOCOL=tcp)(HOST=coe_svr7_pc)(PORT=1232)))
                  (CONNECT_DATA=(SID=FNDFS))
                )

See bug 3259441 for issues related to the TNS alias length exceeding 255 characters.

Without configuring connect time failover using the 8.0.6 ORACLE_HOME tnsnames.ora
by specifying the ADDRESS_LIST entry the only other alternative is to perform a manual
update of the fnd_concurrent_requests table for each request, in order to reflect the
change in outfile_node_name, and logfile_node_name from the failed to the surviving
node.

Determine the failed node name, and update the out and log entry to the
surviving node:

SQL> select outfile_node_name from fnd_concurrent_requests
 2  where request_id=166273;

COE-SVR7-PC

SQL> select logfile_node_name from fnd_concurrent_requests
 2* where request_id=166273

COE-SVR7-PC

Update both outfile and logfile_node_name from the failed to the surviving instance:

SQL> update fnd_concurrent_requests set outfile_node_name = 'COE-SVR5-PC'
  2* where request_id =166273

SQL> update fnd_concurrent_requests set logfile_node_name = 'COE-SVR5-PC'
  2* where request_id =166273

Using the ADDRESS_LIST rather than updating fnd_concurrent_reqests is the recommended
method to estabilsh failover access for Concurrent Manager log and output files.


Configuration Examples:
-----------------------------
Finding the instance name -

column host_name format a20;
select host_name, instance_name from gv$instance;

HOST_NAME   INSTANCE_NAME
----------- ----------------
coe-svr7-pc APRA7
coe-svr5-pc APRA5

Modifying the APPSORA.env & APPS<SID>_<HOSTNAME>.env file for node coe-svr5-pc -

> cd $APPL_TOP
> cat APPSORA.env
:
# $Header: APPSORA_ux.env 115.4 2003/03/01 01:02:35 wdgreene ship $
# =============================================================================
# NAME
# APPSORA.env
#
# DESCRIPTION
# Execute environment for Oracle and APPL_TOP
#
# NOTES
#
# HISTORY
#
# =============================================================================
#
# ###############################################################
#
# This file is automatically generated by AutoConfig. It will be read and over.
# If you were instructed to edit this file, or if you are not able to use the ss
# created by AutoConfig, refer to Document 165195.1 for assistance.
#
# ###############################################################
#
. /oralocal/apraora/8.0.6/APRA.env
. /oralocal/apraappl/APRA.env
TWO_TASK=APRA5
export TWO_TASK

TNS alias definition in tnsnames.ora on each node -

cd $TNS_ADMIN
pwd
/oralocal/apraora/8.0.6/network/admin/APRA

APRA5 = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= coe-svr5-pc)(PORT=1523))
(CONNECT_DATA=(INSTANCE_NAME=APRA5)(SERVICE_NAME=apradb))
)
APRA7 = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= coe-svr7-pc)(PORT=1523))
(CONNECT_DATA=(INSTANCE_NAME=APRA7)(SERVICE_NAME=apradb))
)

FNDFS entry in listener.ora on each node -

cd $TNS_ADMIN
pwd
/oralocal/apraora/8.0.6/network/admin/APRA

( SID_DESC = ( SID_NAME = FNDFS )( ORACLE_HOME = /oralocal/apraora/8.0.6 )
( PROGRAM = /oralocal/apraappl/fnd/11.5.0/bin/FNDFS )
( envs='EPC_DISABLED=TRUE,NLS_LANG=AMERICAN_AMERICA.WE8ISO8859
1,LD_LIBRARY_PATH=/usr/dt/lib:/usr/openwin/lib:/oralocal/apraora/8.0.6/lib,SHLI
_PATH=/usr/lib:/usr/dt/lib:/usr/openwin/lib:/oralocal/apraora/8.0.6/lib,LIBPATH
/usr/dt/lib:/usr/openwin/lib:/oralocal/apraora/8.0.6/lib' )
)
)

RELATED DOCUMENTS
-----------------
Oracle Applications Systems Administrators Guide
Oracle Applications 11i with Real Application Clusters Installation & C
Note 240818.1 Concurrent Processing: Transaction Manager Setup and Configuration Requirement in an 11i RAC Environment