ORATOP

前言：
隨著PC Server的規格及速度愈來愈快，大多數的公司摒棄大型主機，進而選擇PC Server的趨勢愈來愈盛，雖然在可靠度上仍然是大型主機占優勢，但是大型主機的維護費用高昂，這也是讓一般公司望之卻步的主要因素。
在目前PC Server的可靠度尚待提升的當下，其實，Virtual Machine的選擇可以彌補PC Server可靠度的不足，目前三大虛擬平台逐漸成形，分別是Vmware、Hyper-V以及Oracle VM。
如果各位使用PC Server，將Oracle Database安裝在PC Server上，大概就只有Linux可以選擇了。Linux上面要即時監控系統狀況，"top" 指令是系統管理員常用的，但是我們使用 "top" 找到了 Top Process之後，往往還需要將Process ID轉換成Database SID，才能找出關鍵性的Session，進而解決效能問題，不過，Oracle最近有一項工具叫做 "oratop"，可以及時監控Linux上的Database Process狀況，讓系統管理員省去不少時間，找出 Top Session。

目的：
oratop是類似 top 的工具，可以針對Oracle Database Performance做全面性的檢視，如果搭配 top 使用，會得到更完整的系統效能資訊。

適用版本：
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Linux x86-64
Linux x86

使用方式：
1. 使用oracle 使用者將下載的oratop.RDBMS_11.2_LINUX_X64  ftp 到資料庫主機上，如果是RAC環境，選定其中一個node上傳即可。
2. cd 到 oratop 程式所在目錄
3. 更名oratop程式

1. $ mv oratop* oratop

4. 更改oratop權限

1. $ chmod 755 oratop

5. 在.bash_profile 環境變數檔中，設定環境變數(如果尚未設定的話)

1. $ export TERM=xterm  #or vt100
   

   $ export ORACLE_HOME=<11.2 database home>

   $ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

   $ export PATH=$ORACLE_HOME/bin:$PATH

   $ export ORACLE_SID=<local 11.2 database SID to be monitored>  #only needed if connecting to a local database

6. 執行oratop (每10秒更新一次)

1. $ ./oratop -i 10 / as sysdba

7. 針對遠端資料庫執行oratop (每10秒更新一次)

1. $ ./oratop -i 10 system/manager@tns_alias

8. 結束oratop
    按下 "q"，或是 CTRL-C

參考畫面：

指令介紹：
1. 語法

1. $ oratop [Options] [Logon]

2. Options
    a) Help，Displays usage or output information.
        預設: N/A

1. $ oratop -h[elp]  # runtime mode 按下h

    b) Top 5 Wait Events
        預設: 累計
        選項: 即時呈現

1. $ oratop -d  # runtime mode 按下d

    c) 等待物件
        預設: Event/Latch
        選項: File#:Block#

1. $ oratop -k  # runtime mode 按下k

    d) session行為
        預設: 是Username/Program
        選項: 是Module/Action

1. $ oratop -m  # runtime mode 按下m

    e) SQL mode
        預設: Process mode
        選項: SQL display

1. $ oratop -s  # runtime mode 按下s

    f) Database Service centric mode
        預設: Connection mode
        選項: N/A

1. $ oratop -c  # runtime mode:N/A

    g) Long format, (132 columns)
        預設:  short (80 columns)
        選項:  long format for header & process section.

1. $ oratop -f  # runtime mode: 按下f

    h) Database Process mode
        預設:  Process mode
        選項: process display

1. $ oratop -p  # runtime mode: 按下p

    i) Batch mode operation
        預設: Text-based user interface
        選項: N/A

1. $ oratop -b  # runtime mode: N/A

    j) Maximum Number of iterations.   (requires a number)
        預設: infinite
        選項: the maximum number of iterations, or frames

1. $ oratop -n  # runtime mode: N/A

   k)  Displays tablespace information
        預設: N/A
        選項: tablespace information

1. # runtime mode: 按下t

    l) Displays  ASM diskgroup information.  (no op for non-ASM)
        預設: N/A
        選項: ASM diskgroup information

1. # runtime mode: 按下a

    l) Displays SQL execution plan table. Requires input of the active SQL sql_id value
        預設: 5 seconds
        選項: the delay between update refresh

1. $ oratop -c  # runtime mode: 按下

    m) Interval delay time. ( requires value in seconds)
         預設: Connection mode
         選項: N/A

1. $ oratop -v  # runtime mode: N/A

參考文件：
oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

SQLHC

介紹：
SQLHC (SQL Health Check) 是Database診斷工具之一，目的是快速取得SQL效能診斷資訊，你可以將它視為SQLT (SQLTXPLAIN) 的精簡版，不過和SQLT不同的是，SQLHC不需要安裝，所以也不會異動到Database，但是精簡版的工具當然有所限制，例如它無法在Data Guard的環境之下使用，也無法針對PL/SQL Procedure進行分析等等。

適用環境：
Oracle Database 10.2.0.1以後的版本

設定：
無須任何設定

執行條件：
需要在SQL*Plus執行sqlhc.sql，並且使用SYS，DBA，或是對Data Dictionary views有權限存取的User

執行步驟：
首先，先登入到database Server
# sqlplus / as sysdba
SQL> @sqlhc.sql
接著會出現參數1詢問：
Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
請輸入T
接著會出現參數2詢問：
A valid SQL_ID for the SQL to be analyzed (required)
請輸入SQL_ID

或是直接輸入參數也可以
SQL> @sqlhc.sql T djkbyr8vkc64h

執行結果：
執行完之後會在Database Server上產生sqlhc_{timestamp}_{sql_id}.zip，解壓縮之後會產出以下檔案：
1_health_check.html
2_diagnostics.html
3_execution_plans.html
4_sql_detail.html
5_sql_monitor.zip
6_10053_trace_from_cursor.trc
8_sqldx.zip
9_log.zip

各位是不是覺得 "7" 怎麼不見了?? 但是產出結果就是如此，不用再去鑽牛角尖，畢竟這不是重點。
第一份的health check會給出 Oracle建議項目，各位可以自身經驗參考，如下圖所示：

第二份的diagnostics會直接從AWR and ASH Reprot 抓出和該SQL相關的數值。
第三份execution plan對各位來說比較有感覺，裡頭會詳述該 SQL 的執行計畫，有經驗的Programer看了執行計畫應該就知道哪個地方該被tuning。
第四份sql detail是以圖形化的方式直接呈現該SQL對各項資源的效能指標。

如果沒有太多時間，前4份文件給的資訊就足以tuning SQL statement，如果可以的話，其他文件會讓你發現對於該SQL相關的其他更微小的細節，

參考文件：
SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

2.3 Space Management

1. 在Oracle ERP中，沒有針對空間做歷史紀錄，建議自己做空間的歷史紀錄，才能得知長時間的空間變化趨勢，以當作資料生命週期，或是年度採買Storage使用。
2. 因為Oracle ERP物件過多，所以我不會所有物件都納入紀錄範疇。我鎖定超過10 MB的物件每日作記錄，但是如此一來，Tablespace 空間匯總資訊就會失真，所以Tablespace 建議額外處理。
3. 資料收集好之後，排 JOB 每日檢查，例如單一 Tablespace 空間增幅超過100 MB就發警告信，單一 Table 空間增幅超過50 MB就發警告信。
4. 如果收集統計值的工作都有定期執行的話 (至少一星期一次)，那麼就可以列出 High Water Mark (HWM) 和實際 Size 差異過大的 Table List，接著擬定 Table Reorganization 計畫，排定每一季或是每半年做一次 Table Reorganization。

未完待續.................

使用 utl_mail 遇到 error：ORA-24247: network access denied by access control list (ACL)

從Oracle 10g開始，我們直接使用 UTL_MAIL 來作為發信的 Package，而非以往使用 UTL_SMTP 自行撰寫PL/SQL Code的方式，但是我們在 Oracle 11.2.0.3.0 (x86-64 Oracle Linux 6.2) 遇到了以下的錯誤：

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2

原因：
我們參考了Oracle Doc ID 1209644.1，從Oracle 11gR1 之後由於安全性的提升，我們想要使用 DBMS_NETWORK_ACL_ADMIN package 來操作 Access Control Lists (ACL)，用以存取 External Network Services 就會發生這個錯誤訊息。

解決方式：
使用DBA權限執行下面PL/SQL，請記得要將 SCOTT 換成你想要賦予帳號的帳號，帳號有大小寫限制：

BEGIN

  -- Only uncomment the following line if ACL "network_services.xml" has already been created
  --DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'network_services.xml',
    description => 'NETWORK ACL',
    principal => 'SCOTT',
    is_grant => true,
    privilege => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'network_services.xml',
    principal => 'SCOTT',
    is_grant => true,
    privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'network_services.xml',
    host => '*');

  COMMIT;

END;
/

如果有其他使用者要加入權限，只要執行以下 PL/SQL 即可：

BEGIN

  -- For EACH additional user, execute the following code

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'network_services.xml',
    principal => 'ANOTHER_USER',
    is_grant => true,
    privilege => 'resolve');

  COMMIT;

END;
/

參考資料：
ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP And UTL_MAIL With 11gR1 Or Later 
(Doc ID 1209644.1)

SAP BRSPACE Online Reorganizations

1. Which situations a table reorganization be useful ?

• Reduction of table size
• Cleanup of table fragmentation
• Change of table storage parameters (e.g. INITRANS)
• Move of table to a different tablespace
• Reduction of chained and migrated rows
• Reduction of number of allocated extents
• Transition from LONG to LOB columns (Oracle >= 10g)
• Reduction of hot spots on disk level
• Activation of Transparent Data Encryption
• Change of table structure

2. Which situations  a tablespace reorganization be useful ?

• Activation of LMTS
• Activation of ASSM
• Cleanup of freespace block corruption
• Reduction of database size
• Reduction of backup size
• Reduction of filesystem size
• Reduction of fragmentation
• Reduction of number of datafiles
• Increase of number of datafiles
• Transition to new tablespace layout
• Other change to tablespace layout
• Optimization of filesystem layout

3. How does a BRSPACE online reorganization work?

• Determination of CREATE commands for target table and indexes based on DBMS_METADATA.GET_DDL
• Creation of target table with naming convention <source_table>#$
• Export of source table CBO statistics based on DBMS_STATS.EXPORT_TABLE_STATS
• Copy of source table data to target table based on DBMS_REDEFINITION.START_REDEF_TABLE. This package will implicitly create a materialized view log MLOG$_<source_table> to track all changes that are performed on the source table while the reorganization is running.
• Creation of target indexes with naming convention <source_index>#$
• Import of source table CBO statistics to target table based on DBMS_STATS.IMPORT_TABLE_STATS
• Finalizing of the online reorganization using DBMS_REDEFINITION.FINISH_REDEF_TABLE. In this step all changes in the materialized log are applied to the target table and the names of source and target table are exchanged.
• The source table is dropped.
• The ?#$“ suffix of the target table indexes is removed.

4. What are the advantages of a BRSPACE Online Reorganization?

• It uses the advantages of DBMS_REDEFINITION:

          a. The reorganization can be performed in parallel to production operation.
              This means the table is accessible and records can be inserted / updated / deleted.

„          b. If anything goes wrong during the reorganization the source table still 

              exists unchanged and no restore / recovery is necessary.
          c.„ Compared to an offline reorganization via export / import all data has to be 

              copied only once and not twice

• „ It provides possibilities for client and server side parallelism in order to speed up the reorganization if sufficient system resources exist and the reorganization should finish within a specific time window.
• „ It has already proven to be a reliable reorganization tool at many customers.
• „ It performs a lot of useful activities implicitly that would require a lot of manual efforts and impose the risk of errors if DBMS_REDEFINITION is used directly.

5. What are the restrictions of a BRSPACE Online Reorganization?

• It is only available as of Oracle 9i.
• It cannot handle tables with LONG or LONG RAW columns. As of 10g it is possible to convert them during an online reorganization on the fly to LOB columns (if SAP >= 6.40 is used).
• Temporarily twice the space is needed because both source and target table and indexes exist in parallel.
• If a table has no primary key constraint (or no unique index with NOT NULL columns) a ROWID based online reorganization is necessary that requires overhead (ROWID index, ROWID column).
• The setup of the materialized view is an overhead that can negatively impact the performance of the reorganization of many very small tables.
• Structure changes on the source table and its indexes must not be performed during the reorganization (as they would be lost).

6. How is a normal BRSPACE Online Reorganization started?

• Table reorganization:

          brspace -f tbreorg -t <table_name>

• Tablespace reorganization:

          brspace -f tbreorg -s <source_tsp> -t "*" -n <target_tsp>

• Additional useful options exist, e.g.:

         a) -p <degree>

             => BRSPACE parallelism degree

         b) -e <degree>

             => PX parallelism degree

         c) -l <category>

             => Initial extent size category

         d) -i <target_ind_tsp>

             => Definition of target index tablespace (if different from target table tablespace)

7. Reference:

    Note 646681 and the BRSPACE online documentation.

2.4 Form Management

目前絕大多數的公司已經採用Sun Jre的方式進入Oracle ERP，但是我們家的環境因為特殊原因，所以一直無法升級，仍然是沿用Oracle Jinitiator的方式進入ERP，而我們家Jinitiator目前的版本是1.3.1.21，但是當IE升級到IE8之後，發生了form無法開啟的悲劇，幸運的是，我在網路上依然找到了類似的事故，以下是我處理的方式：

1. IE瀏覽器

1.1  下載Sun JDK 1.5的最新版本，解壓縮之後，到 "C:\Program Files\Java\jdk1.5.0_22\jre\bin\server" 擷取 jvm.dll

1.2  關閉ERP

1.3  開始 => 設定 => 控制台 => Jinitiator 1.3.1.21 => 代理程式 => "使用瀏覽器設定值" 取消打勾

1.4  copy jvm.dll 到 C:\Program Files\Oracle\JInitiator 1.3.1.21\bin\hotspot\ 

1.5  開啟ERP 

2. Firefox瀏覽器

2.1  下載Sun JDK 1.5的最新版本，解壓縮之後，到 "C:\Program Files\Java\jdk1.5.0_22\jre\bin\server" 擷取 jvm.dll

2.2  關閉ERP
2.3  開始 => 設定 => 控制台 => Jinitiator 1.3.1.21 => 代理程式 => "使用瀏覽器設定值" 取消打勾
2.4  copy jvm.dll 到 C:\Program Files\Oracle\JInitiator 1.3.1.21\bin\hotspot\
2.5  copy C:\Program Files\Oracle\JInitiator 1.3.1.21\bin\NPJinit13121.dll  to  C:\Program Files\Mozilla Firefox\plugins\
2.6  開啟ERP 

如果公司有使用Oracle Application Server 10g，也可以使用相同方式解決。

大多數人可能會遇到 form的 Timeout 問題，以及Application Server Sizing問題，先說Sizing問題。

我的經驗是，每個session給15M，假設有200個user同時上線，可能會有600個session，那就是600*15MB=9000MB，再加上可能有30個Concurrent processing，一個占用300MB，那就是30*300MB=9000MB，所以total給個24GB的記憶體應該是夠用了。

再來是 timeout問題。
1. 設定system profile
system administrator(responsibility) > profile > system > profile輸入ICX:Session Timeout >
輸入60，表示Invalid session經過60 min後過期
2. 設定session.timeout in zone.properties
在檔案 $IAS_ORACLE_HOME/Apache/Jserv/etc/zone.properties 中搜尋session.timeout
數值須和 ICX:Session Timeout 設定的相同 3600000 (millisecond)
3. 修改xml file
在檔案 $APPL_TOP/admin/$CONTEXT_NAME.xml 搜尋 s_sesstimeout 將數值設定成3600000 (millisecond)

2.1 Start and Stop Oracle ERP

假設環境是AP Server有ap1 and ap2，Database Server有 db1 and db2，其中db1 and db2是RAC架構，SID是PROD

1. Start Database
login db1 as oracle user
開啟Database
# srvctl start database -d PROD
檢查 listener 是否開啟，如果沒有，請開啟 listener
# srvctl start listener -n db1
# srvctl start listener -n db2
如果是非RAC的環境
# sqlplus "/as sysdba"
SQL> startup
開啟db console
# emctl start dbconsole
檢查db console
# emctl status dbconsole

2. Start Oracle ERP
login ap1 as applmgr user
# cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
# adstrtal.sh.sh

3. Stop Oracle ERP
login ap1 as applmgr user
# cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
# adstpall.sh

4. Stop Database

login db1 as oracle user
關閉db console
# emctl stop dbconsole
檢查db console
# emctl status dbconsole

關閉Database
# srvctl stop listener -n db1
# srvctl stop listener -n db2
# srvctl stop database -d PROD -o immediate
如果是非RAC的環境
# sqlplus "/as sysdba"
# shutdown immediate

使用指令 ipcs 檢查是否memory有殘存oracle的 process，如果有，使用指令 ipcrm 清除
# ipcrm -m <number>
# ipcrm -s <number>

執行AP服務啟動時，我相信大多數人不會像上面一樣所有的服務全開，但是關的時候下adstpall.sh 是快速且偷懶的做法。

像我們家的系統，就會這麼作：
1. Pre-Action
提早2小時通知user不要跑執行過久的報表

2. Stop AP
先關閉 Concurrent Manager
login ap1 as applmgr user
# cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
# adcmctl stop apps/<password>

再關閉 Form Service
login ap1 and ap2 as applmgr user
# adalnctl.sh stop APPS_PROD  (Application 服務的 Oracle Net8 Listener)
# adapcctl.sh stop  (Apache 服務，如果Form是走 servlet 架構，該指令會連 Form Service一併關閉)
# adfrmctl.sh stop  (如果Form是走 CGI 架構，必須下該指令關閉 Form 服務)
# adtcfctl.sh stop  (TCF Socket 服務)

3. Start AP
先開啟 Form Service
login ap1 and ap2 as applmgr user
# cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
# adalnctl.sh start APPS_PROD
# adapcctl.sh start  (如果Form是走 servlet 架構，該指令會連 Form Service一併帶起)
# adfrmctl.sh start  (如果Form是走 CGI 架構，必須下該指令開啟 Form 服務)
# adtcfctl.sh start

再開啟 Concurrent Manager

login ap1 as applmgr user

# cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
# adcmctl start apps/<password>

4. 檢查服務

  4.1  開啟 Form 是否正常

  4.2  檢查 Concurrent Manager 服務是否全數開啟

  4.3  執行 "Active User" 是否能正常執行

  4.4  "View Log" and "View Output" 是否正常

  4.5  "Copy File" 功能是否能夠正常使用

  4.6  Form Export 功能是否能夠正常使用

  4.7  Menu 樹狀結構展開功能是否能夠正常使用

建議在做維護時，要有一份執行計畫，如下圖所示，如此才不會手忙腳亂。

淺談Oracle ERP 系統管理

最近在做對新人的Oracle ERP系統管理教育訓練，也趁此機會review自己所知道的東西是否有遺漏。

基本上我會分成以下這幾個項目：

1. Profile (系統側寫)

2. Administration (系統管理)

3. Patch (修補程式)

4. Purge (資料生命週期管理)

5. Clone (系統複製)

6. Printer (印表機處理)

7. Workflow (工作流程管理)

8. Alert (警示系統管理)

9. Privilege Control (權限管理)

10. Program Release (程式上版)
98. Event Monitor (監控機制)

99. Health Check (健檢報告)

其中 2. Administration (系統管理) 可分成：

2.1  Start and Stop Oracle ERP

2.2  Backup and Recovery

2.3  Space Management

2.4  Form Management

2.5  Concurrent Manager Management