Jemmy Walker

　　交接要離開的同事手上工作，可好，有個Smart Query需要在.NET下運行．很久沒遇到微軟的東東，用ODBC連Oracle．就在PC裝個Oracle XE練習．建ODBC比較常見的作法是從[控制台]去建，另一個方式是建立一個檔案，副檔名是udl，再double click就可以設定ODBC．如下： 　　在桌面建置這樣的ODBC連Oracle XE是OK，但透過Smart Query連卻不行，出現ORA-12514的錯誤訊息，直到我把資料來源從localhost改成： localhost:1521/XE 　　這樣Smart Query才連成功．而也意外搜到如何設定XE的ftp port及disable http和ftp port方式，一併記錄．取自http://daust.blogspot.com/2006/01/xe-changing-default-http-port.html

SQL> select dbms_xdb.gethttpport as "HTTP-Port"
, dbms_xdb.getftpport as "FTP-Port" from dual;
HTTP-Port  FTP-Port
---------- ----------
8080       0

　　以上是取得目前port值，設定的話是： dbms_xdb.sethttpport('80'); dbms_xdb.setftpport('2100'); 　　若想disable它們，將參數改為0即可．

　　壓力測試過程發現程式仍有Bug。OGG的Update有兩種DO_UPDATE_FILECOMP和DO_UPDATE_FILECOMP_PK(另兩種和Tandem有關，不予討論)。參考前一篇：http://tsaijemmy.spaces.live.com/blog/cns!4AD532D0CD18A21D!1693.entry。假設Table Foo的是複合key：ID、Name，而非key欄位叫CDate。這時好玩的地方就在下表DsColumn的method： 當執行Update Foo set CDate=sysdate where ID=? and Name=?，則觸發DO_UPDATE_FILECOMP。 欄位 \ method isMissing isChanged isValueNull ID false true false Name false true false CDate false true false 而Update PK欄位值：Update Foo set Name=? where ID=?，則觸發DO_UPDATE_FILECOMP_PK。 欄位 \ method isMissing isChanged isValueNull ID false true 待補上 Name false false 掛了 CDate true false 待補上 當時令我驚訝的是，被Update的PK欄位，isChanged回傳竟是false，而該欄位若呼叫isValueNull，會莫名其妙中斷Thread也不丟出Exception。 此外DO_UPDATE_FILECOMP_PK裡，每個PK欄位都會回傳有beforeValue和afterValue，即便前後都是一樣的值，不會Missing。反觀DO_UPDATE_FILECOMP，PK欄位值只會回傳afterValue。

　　Oracle Golden Gate(簡稱OGG)是Oralce出品的資料即時抄寫工具，它讀取redo.log來進行抄寫避免影響正在運作的Oracle的效能，目前可同步抄寫到Oracle、MySQL和SQL Server。OGG本身是一支standalone程式，也提供一個Java Adapter方便開發者客製抄寫作業。怎麼安裝自有Consultant處理，但實作Java Adapter還真的有些難度。這主題目前是我寫過最難寫的。 　　OGG啟動後，可以切換到OGG Home目錄執行ggsci執行檔，會出帶出ggsci>的提示。下達info all命令後，可以work的狀況下，會顯示三類Extract(不是三個)，Extract之於OGG類似於Process之於OS。其實extract就是process，可以用ps -ef | grep ogg觀察。這三類Extract分別是：

Manager：OGG的主控程式extract，是必須的。

ext1：不一定叫ext1。屬OGG for Oracle，是由User建立，負責讀取redo.log。若只單純同步其它DB，應只需這類Extract，通常一個Extract對映一個DB(Oracle的SID)，可以指定抄寫等定的table。

javaue：則是OGG的Java Adapter特定的extract，也是本篇Blog主題。

　　info all會帶出每個extract的status，分別是：RUNNING、STOP和ABENDED。ABENDED查好久，是異常中止的意思。也就在javaue底下寫Java adpater若寫得不好，狀態就是ABENDED。 　　未談OGG for Java之前，先略述OGG for Oracle的運行架構，在OGG Home目錄下有個重要的子目錄：dirprm，裡面放的.prm檔即是上述的extract的參數檔，需手動編輯，除了mgr.prm很簡單的只設定PORT number之外，而ext1.prm內容長得像這樣：

extract ext1                # 宣告extract name
userid ogg, password oracle # 連Oracle的ID/PWD
EXTTRAIL ./dirdat/je        # OGG放置redo.log路徑，檔名je開頭
table jemmy.FOO;            # 允許Schema Jemmy的FOO table
table orcl.BAR;             # 允許Schema ocrl的BAR table

　　設定完後，在ggsci>下start ext1，它就會讀取jemmy.FOO和table.orcl有異動到redo.log寫到./dirdat/je。至於怎麼直接抄寫到不同的DB，要另外找找設定，這裡是要給OGG for Java讀取使用。 　　OGG for Java安裝後，在OGG Home目錄下會多出一個ggjava的子目錄，即是javaue extract運行的地方。一樣在/dirprm子目錄有個javaue.prm，其內容後面再討論。另外還需利用defgen.prm做code gen產出javaue.def，作為javaue與ext1之間的mapping，在Home目錄執行：./defgen PARAMFILE dirprm/defgen.prm。所參考defgen.prm的內容，和ext1.prm內容頗為類似

userid ogg, password oracle
defsfile ./dirprm/javaue.def # 產出javaue.def檔
table jemmy.*;
table ocrl.*;

　　而javaue.prm則是設定OGG for Java Adapter運作的環境及參數：

Extract javaue

SetEnv (JAVA_HOME = "/usr/java5_64")
SetEnv (LIBPATH = "/usr/java5_64/jre/bin/j9vm:/usr/java5_64/jre/bin:/oracle10/lib:/ogg/ogg_sync")
SetEnv (GGS_USEREXIT_CONF = "dirprm/cuserexit.properties" ) # OGG for Oracle的設定
SetEnv (GGS_JAVAUSEREXIT_CONF = "dirprm/javaue.properties") # OGG for Java的核心設定

CUserExit Java5_UserExit.so CUSEREXIT PassThru IncludeUpdateBefores # CUserExit是OGG for Oracle核心程式

GetUpdateBefores
NoCompressDeletes
NoCompressUpdates

Table jemmy.*;
Table orcl.*;

　　講了上述不甚完全的配置後，現在才要進入如何開發的階段。OGG for Java的客製化限制還真的很OOXX的多。

第一個，不是做成Jar檔放進去，而是在/dirprm子目錄下放classes的內容，如OGG-Home/dirprm/com/mycompany/…。所以先前有個困惑是不應該放在ggjava目錄下嗎？目前無解。

第二個更OX了，log4j.properties以ggjava下的設定為主，而ggjava也import Spring的jar，版本是2.5.6，所以也有它的config檔，因不想複雜化，不用Spring。

寫好的OGG for Java程式放到dirprm目錄下後，如果有引額外的jar怎麼處理，這需要從dirprm下的javaue.properties做配置了。ggjava本身就有提供一個透過Velocity產出redo.log資料的sample，開發過程式可以和這sample並行對照驗證。以下是節錄的部份：

#gg.handlerlist=sample_test # sample_test是ggjava提供的用Velocity讀取redo.log的處理程式
#gg.handlerlist=test_jms,sample_test
gg.handlerlist=sample_test,ogg_adapter

# OGG Adapter
gg.handler.ogg_adapter.type=com.mycompany.OggHandler
gg.classpath=/ogg/ogg_tprs33a/ggjava/resources/lib/NCSO.jar,/ogg/ogg_tprs33a/ggjava/resources/lib/Notes.jar

　　javaue.properties的配置觀念與log4j類似。

• gg.handlerlist：後面接的是處理程式代名，可以多個，用逗號分隔。和log4j指定appender方法一樣。
• gg.handlerlist.程式代名.type：是指這程式代名實際對應的類別，需要在/dirprm/com/mycompany下找到OggHandler這個類別。該類別實作容後再敍。
• gg.classpath：import額外的jar檔，用逗號分隔。
• gg.handlerlist.程式代名.自訂屬性：ggjava好像進步到可以該程式代名需要的屬性。

如何開發OGG for Java：由上述的javaue.properties得知，啟動的入口在com.mycompany.OggHandler這支，它需要繼承OGG for Java提供抽象類別AbstractHandler，原程式碼如下：

package com.mycompany;

import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.goldengate.atg.datasource.AbstractHandler;
import com.goldengate.atg.datasource.DsConfiguration;
import com.goldengate.atg.datasource.DsEvent;
import com.goldengate.atg.datasource.DsOperation;
import com.goldengate.atg.datasource.DsTransaction;
import com.goldengate.atg.datasource.GGDataSource.Status;
import com.goldengate.atg.datasource.meta.DsMetaData;

public class OggHandler extends AbstractHandler { // 繼承它
    private static Logger logger = LoggerFactory.getLogger(OggHandler.class);
    private List<DsOperation> rows = new ArrayList<DsOperation>(); // DsOperation表示一筆增改刪動作

    @Override
    public void init(DsConfiguration arg0, DsMetaData arg1) {
        super.init(arg0, arg1);
        logger.debug("[1]init");
    }

    @Override
    public Status operationAdded(DsEvent e, DsTransaction tx, DsOperation op) {
        logger.debug("[4]operationAdded");
        this.rows.add(op); // 增加一筆record至list
        return Status.OK;
    }

    @Override
    public Status transactionBegin(DsEvent e, DsTransaction tx) {
        logger.debug("[3]transactionBegin");
        return Status.OK;
    }

    @Override
    public Status transactionCommit(DsEvent e, DsTransaction tx) {
        logger.info("[5]transactionCommit size={}", this.rows.size());
        OggThread poster = new OggThread();
        poster.setOps(this.rows); // 傳該transaction所有record過去(即一個List)
        poster.setDsMetaData(this.getMetaData()); // 傳該DataSource的中介資訊
        Thread t = new Thread(poster);
        t.start();
        this.rows = null;
        this.rows = new ArrayList<DsOperation>();
        return Status.OK;
    }

    @Override
    public void destroy() {
        logger.debug("[7]destory");
    }

    @Override
    public Status metaDataChanged(DsEvent e, DsMetaData meta) {
        logger.debug("[2]metaDataChanged");
        return Status.OK;
    }

    @Override
    public String reportStatus() {
        logger.debug("destory[6]");
        return "status report...";
    }
}

　　我在Log裡標注每個method的號碼，是OGG for Java執行順序：init—>metaDataChanged—>transactionBegin—>operationAdded—>transactionCommit—>reportStatus—>destroy。

init：在ggsci>start javaue後，init只會被執行第一次。

reportStatus：在ggsci>stop javaue後，reportStatus只被執行一次，回傳字串記錄於log4j.properties所寫的Log。

destroy：在ggsci>stop javaue後，只被執行一次。

在每一筆交易被commit後，metaDataChanged—>transactionBegin—>operationAdded—>transactionCommit均被執行一輪，最重要的method是operationAdded和transactionCommit，由operationAdded是取得每一筆record(增改刪資訊)，即DsOperation物件，加到一個List；到transactionCommit就取得transaction裡所有record，在OGG for Java預設作法就分出thread去處理。所以了解DsOperation構造就很重要。

解析DsOperation還需由AbstractHandler取得DsMetaData，以下是OggThread片段：

for (DsOperation op : this.ops) { // 取得每筆record
    TableName tableName = op.getTableName(); // 該record所屬table
    TableMetaData tableMetaData = this.dsMetaData.getTableMetaData(tableName); // 取得該table中介資訊
    List<ColumnMetaData> keyList = tableMetaData.getKeyColumns(); // 取得該table的primary key欄位
    DsOperation.OpType opType = op.getOperationType();  // 取得該record的操作(增|刪|改)
    for(DsColumn dsCol: op.getColumns()) {              // 取得該record每個欄位
        ColumnMetaData cMeta = tableMetaData.getColumnMetaData(colNum); // 取得該欄位中介資訊
        String colName = cMeta.getColumnName();         // 取得欄位名稱
        String afterValue = dsCol.getAfterValue();      // 取得欄位修改後值
        String beforeValue = dsCol.getBeforeValue();    // 取得欄位修改前值
        // ...
    }
}

　　DsOperation的Type一共有八種，目前會常用的是四種： OpType  \ Value getBeforeValue getAfterValue Description DO_INSERT null Yes All fields values DO_DELETE Yes null 只會帶PK的before值 DO_UPDATE_FIELDCOMP null Yes 不含PK值欄位的update DO_UPDATE_FIELDCOMP_PK Yes Yes 含PK值欄位的update 　　要注意的是，Update都不會帶所有欄位，只有被影響的欄位值，而DO_UPDATE_FIELDCOMP_PK只有PK欄位有After和Before值。 　　另外四種Type會在什麼情況出現還不知，分別是：DO_UPDATE_AC、DO_UPDATE_AC、DO_LOB和DO_TRUNCATE。要使truncate table可以作用到OGG的DO_TRUNCATE還需另外加裝script。而不解的是DO_UPDATE和DO_UPDATE_AC各代表什麼東東？

　　這是個令人詬病的錯誤代碼，Oracle的Sequence物件提供nextval和currval兩個method，nextval取得下一個序號值返回，而顧名思義，currval應當是取得目前序號值返回。事實上錯，執行currval之前必須先執行過nextval才能生效，否則就丟出ORA-08002錯誤代碼，而currval需保持在同一個Session才有效用。Oracle這樣設計的用意…，只能逼得我改變insert策略，在insert的UI時先顯示nextval的值，按儲存後，原本SQL要insert id欄位可以用nextval變成抓UI的ID值來insert。女子火闌。

　　有別於PostgreSQL可以直接把欄位定義為陣列型態，Oracle是透過自訂型態的方式達成目標，飯粒如下： create or replace Type varray_phone as varray(3) of varchar2(20) 　　上述是定義一個名為varray_phone的Type，是維度為三個varchar2(20)的資料型態。所以varray是宣告陣列維度，of後接的不止Oracle的資料型態度，也可以接更複雜的Oracle Object型態，複雜的部份就不討論。而其Create、Insert的用法很簡單，如下： create table employee (id number(10) primary key, name varchar2(10), phone varray_phone) Insert into employee values (1, 'Jemmy', varray_phone('0911-222333', '0922-33344')) 　　這篇文章http://www.devx.com/tips/Tip/22034描述如何用JDBC存取Array型態，就不贅述，此處列出如何用Spring的JDBC Template存取Array型態飯粒。

import javax.sql.DataSource;

import oracle.sql.ARRAY;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.systex.rdf4.util.AnnotationConfigContextLoader;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(loader = AnnotationConfigContextLoader.class,
        value = "com.foo.bar.DSConfig")
public class TestVArray {
    private static Logger logger = LoggerFactory.getLogger(TestVArray.class);

    private SimpleJdbcTemplate simpleJdbcTemplate;

    @Autowired
    public void init(DataSource datasource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(datasource);
    }

    @Test
    public void testVArray() {
        String sql = "insert into employee values (?, ?, varray_phone(?, ?, ?))";
        this.simpleJdbcTemplate.update(sql, 2, "Tom", "12345", "67890", "09876");
        sql = "select * from employee";
        List<Map<String, Object>> rsList = this.simpleJdbcTemplate.queryForList(sql);
        for (int i=0, n=rsList.size(); i<n; i++) {
            System.out.println("====== Line #" + i + " ======");
            Map<String, Object> map = rsList.get(i);
            System.out.println(map.get("NAME"));
            ARRAY array = (ARRAY) map.get("PHONE");
            try {
                String[] values = (String[]) array.getArray();
                if (values != null) {
                    for (String phone : values) {
                        System.out.println(phone + ", ");
                    }
                } else {
                    System.out.print("phone is null");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

　　Oracle XE是Oracle 10g免費版本，只能建一個SID叫XE，預設http port和其它的測試用的Web Server都一樣是8080。現在找到解法，這是在Windows如下： 1.執行Oracle XE提供的SQL Plus的Console（捷徑名是：執行SQL命令）。 2.SQL> connect system/password@XE;　連到XE 3.SQL> exec dbms_xdb.sethttpport(8181); 改變預設http port為8181

　　同事詢問我修改過的Stored Procedure裡，Return和Exit有什麼不一樣，一時我也搞不清楚。後來看了一下，原來Exit指令是用於跳出Loop往下執行，例如： If (v_count > 0 Then 　　Exit; End If; 或者是 Fetch cur_table into v_table Exit When cur_table%NotFound;  // 當cursor已移到最後一筆找不到時跳出。 　　而Return則是直接結束Stored Procedure不再往下執行。

在Oracle的Stored Procedure執行truncate table怎麼做，無法直接下達指令，語法如下： execute immediate 'truncate table table_name’;

　　市面上最有名的DB Tool應該是要收錢的TOAD，但免費的話有SQuirrel SQL Client，係由Java編寫，使用JDBC連結。而若專用於Oracle的免費工具，是Sql Developer，也是使用JDBC，不用像TOAD要事先裝Oracle Client。這樣的工具雖然方便，但要透過VPN連遠端DB，效能是非常的低落，不得已也只能回到最原始的使用Terminate，下Script產出結果後用FTP傳回。 　　預先寫個Oracle的export script，叫exp.sql好了，內容如下： set echo off
set feedback off
set linesize 9999
set pagesize 0
set sqlprompt ''
set trimspool on
spool log.csv select log_id||','||
  log_date||','||
  log_memo
from log_tablewhere order by sp_id; spool off 　　如此，在VPN連上遠端的Terminate後，su - oracle切換到oracle帳號，將該script傳到其home目錄下， 下達：sqlpulse [id]/[pwd]@[sid] @exp.sql，就會根據exp.sql的指定寫到log.csv檔了。 　　上述是目前進行專案遇到stored procedure的效能瓶頸，所以在procedure加insert到log的指令查看是瓶頸所在，所以匯出log.csv後，該怎麼分析呢？我還是比較擅長使用Perl，微軟轉投資的ActiveState的Komodo IDE是Windows下最好的script IDE，可惜要錢，現在也跟上免費的潮流，出了免費的Komodo Edit，試用了一下，需要像UltraEdit一樣事先config命令，不然沒有像Komodo IDE一樣有個play的按鍵，也難怪叫Edit啦，試用結果還不是很順手，使用EditPlus run Perl較多。 　　分析的話，在Perl裡面怎麼寫，我通常把log.csv純文字內容貼在__DATA__之後，再用while (<DATA>) {…}逐筆取出，使用split以逗號分隔進行解析。 while (<DATA>) {     chomp;     @comma = split/,/;     … } 　　甚至組成一行行的insert sql寫進MySQL做查詢，比如我可多出一個欄位計算這筆記錄和上筆記錄時間差，若沒有運算的需要，其實直接在exp.sql組成insert sql就OK了。 　　做系統開發到現在，我從不奢求之前寫code或現在合作的對象，程式能力有多好，但就是有人常常寄厚望某個萬能工具能為自己做解析、改善。其實是有這個萬能工具，如IBM的Rational Rose，除了要價不斐，更需要去學它的方法論以及貫徹執行才有它的value；當然也可以用於重構，可是重構層次也是到程式技巧，和部份的Design Pattern重構，遇到架構層次的議題，目前應該還做不到。 　　就像蔡學鏞曾列出一條Anti-Pattern，想做產品一勞永逸的賣，但實際卻必須接專案維持財務運作。一樣的道理，就有同事很天真想買一套EA(Enterprise Architecture)就從此靠它了，這遇到新案或可有作為，遇舊案需要後續維護，誰願接手。這也是我老用Perl寫些臨時小工具的緣故，快又即時，也因此不必去想把Perl寫成Super Tool。

資料來源: 永遠的Unix (寫得很好, 只好轉貼) 在oracle中sequence就是所謂的序列號，每次取的時候它會自動增加，一般用在需要按序列號排序的地方。 
1、Create Sequence 
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE權限， 
CREATE SEQUENCE emp_sequence 
    INCREMENT BY 1  -- 每次加幾個 
    START WITH 1    -- 從1開始計數 
    NOMAXVALUE      -- 不設置最大值 
    NOCYCLE         -- 一直累加，不循環 
    CACHE 10; 
一旦定義了emp_sequence，你就可以用CURRVAL，NEXTVAL 
CURRVAL=返回 sequence的當前值 
NEXTVAL=增加sequence的值，然返回 sequence 值 
比如： 
  emp_sequence.CURRVAL 
  emp_sequence.NEXTVAL 
可以使用sequence的地方： 
- 不包含子查詢、snapshot、VIEW的 SELECT 語句 
- INSERT語句的子查詢中 
- NSERT語句的VALUES中 
- UPDATE 的 SET中   
可以看如下例子： 
INSERT INTO emp VALUES  
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20); 
SELECT empseq.currval     FROM DUAL; 
但是要注意的是： 
- 第一次NEXTVAL返回的是初始值；隨的NEXTVAL會自動增加你定義的INCREMENT BY值，然返回增加的值。CURRVAL 總是返回當前SEQUENCE的值，但是在第一次NEXTVAL初始化之才能使用CURRVAL，否則會出錯。一次NEXTVAL會增加一次SEQUENCE的值，所以如果你在同一個語句裡面使用多個NEXTVAL，其值就是不一樣的。明白？ 
- 如果指定CACHE值，ORACLE就可以預先在內存裡面放置一些sequence，這樣存取的快些。cache裡面的取完，oracle自動再取一組到cache。 使用cache或許會跳號， 比如數據庫突然不正常down掉（shutdown abort),cache中的sequence就會丟失. 所以可以在create sequence的時候用nocache防止這種情況。 
2、Alter Sequence 
你或者是該sequence的owner，或者有ALTER ANY SEQUENCE 權限才能改動sequence. 可以alter除start至以外的所有sequence參數.如果想要改變start值，必須 drop  sequence 再 re-create . 
Alter sequence 的例子 
ALTER SEQUENCE emp_sequence 
    INCREMENT BY 10 
    MAXVALUE 10000 
    CYCLE    -- 到10000從頭開始 
    NOCACHE ; 
影響Sequence的初始化參數： 
SEQUENCE_CACHE_ENTRIES =設置能同時被cache的sequence數目。  
可以很簡單的Drop Sequence 
DROP SEQUENCE order_seq; 
好吧，就到這裡。