2011年8月30日 星期二

Excel日期強制語言格式

Excel可以指定日期的格式。譬如2011/10/1的資料使用dddd定義他的顯示格式的時候,就會出現 "星期六" 或 "Saturday"。如果格式設為mmmm則會顯示"十月"或"October"。要顯示中文還是英文都要看作業系統的預設語言及Excel版本來決定。
如果要強制設為中文月份可以使用以下定義:

[$-404]dddd


如果指定顯示英文如下:

[$-409]dddd


其中404代表繁體中文(台灣),409則代表英文(美國)。

2011年8月24日 星期三

Oracle 10G更改IP位置或電腦名稱後,dbconsole無法啓動的解決方法

很久很久以前~ 就有發生過AP開不起來,錯誤訊息是:

weblogic.security.service.SecurityServiceException:
com.bea.common.engine.ServiceInitializationException:
org.apache.openjpa.util.InternalException:
There was an error when invoking the static getInstance method on the named
factory class "kodo.jdbc.kernel.KodoJDBCBrokerFactory".
See the nested exception for details..
....叭啦叭啦

Caused By: java.lang.RuntimeException:
There were errors initializing your configuration:
org.apache.openjpa.util.StoreException:
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor


超不熟的ERROR MSG
The realm "myrealm" failed to be loaded -- ???
openjpa-1.1.1-SNAPSHOT-r422266:807362 fatal internal error -- ??? maven
error when invoking the static getInstance method on the named factory class "kodo.jdbc.kernel.KodoJDBCBrokerFactory". -- ??? kodo
雖然一霧水,但後來重開AP就莫明其妙好了 = =,就也沒理它了
昨天終於如願掛點了,AP再也開不了
後來終於找到重點問題:
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
ORACLE的一個服務 (OracleDBConsoleorcl) ,沒辦法打開
控制台/系統管理工具/服務 裡,去啟動 還會出現 [ Windows 無法在本機電腦上啟動OracleDBConsoleorcl ] 的錯誤訊息

上網查了一下 OracleDBConsoleorcl是什麼:
安裝完oracle後,在服務的地方會看到oracleDBConsoleOracleService兩個服務
oracleDBConsoleEM的服務
oracleService+(Servicename) 才是資料庫實體的服務
我們只要啟動OracleService,就可以利用PL/SQL對資料庫進行連結操作。
oracle10g之後,會有更改IP地址或者機器名之後要重新配置EM資料庫,不然啟動dbconsole會失敗oracleDBConsole如果不啟動的話,http://localhost:PORT是打不開的

總之:是oracleem開不了,而且這個問題,看來還是

Oracle 10G認證考試常用問題..

10G中更改IP地址或者機器名之後要重新配置EM資料庫,不然啟動dbconsole會失敗

以下是我做的「復健之過程」:
step_1_將自已nb本機的使用者,加入ora的群組

到:開始功能列 / 控制台 / 使用者帳戶 / 管理使用者帳戶,在跳出的對話框,
選「進階」的頁籤,,在進階使用者管理,按「進階」的按鈕
ora_dba點兩下,按 「新增」,把你這台電腦的使用者,加入oracle dba的群組。

step_2_ 用系統管理員身分開「指令行」點它按滑鼠右鍵就會出現
「以系統管理員身分執行」
2.1 drop configuration files and repository run
key指令:emca -deconfig dbcontrol db -repos drop ( 記得不要打分號 )
後面還需要key資料:
資料庫SIDORCL (大寫)
監聽器連接埠號碼:1521
(要稍微等一下,做完關掉指令行,再用同樣的方法重新開一次)
2.2 sys的密碼 (因為我忘了 = =)
key sqlplus /nolog
連線:connect /as sysdba
改密碼語法:ALTER USER sys IDENTIFIED BY new_password;
這樣就有sys的密碼是sys了。
( 關掉 指令行)

step_3_ 滑鼠點「SQL Plus」按右鍵,會跳出選項,選「以系統管理員身分執行」
step_4_Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and mangement objects:
SQL Plus先登入,再drop從網路上查到的 user或同義字
a. drop user sysman cascade;
b. drop role MGMT_USER;
c. drop user MGMT_VIEW cascade;
d. drop public synonym MGMT_TARGET_BLACKOUTS;
e. drop public synonym SETEMVIEWUSERCONTEXT;
我試過之後,發現後面在執行create時,還是會碰到
ORA-00955: 此一名稱已被一個現有物件使用,
這是錯誤訊息是因為oracle建了很多預設的usersynonym
所以,為了一勞永易,就全把這些oracle自已產生的,全殺了吧,看附件 del_user.sql

如果,執行有碰到這些使用者,無法刪除的情況:就run這個附件:drop-repo.sql
<<備註:如何在SQL Plus執行預先寫好的sql,不用一個一個打指令>>
指令:edit 123.sql
(會開啟編器,把你要執行的sqlcopy pastsav然後關掉)

指令:@123.sql
(執行,RUN;記得要等一下,做完,最好commit; 有卡密有保庇,真的= =)
step_5_ Create configuration files and repository run
同樣用系統管理員身分開「指令行」,打
emca -config dbcontrol db – repos create (不要分號)
後面還需要key資料:
資料庫SIDORCL (大寫)
監聽器連接埠號碼:1521

然後,還是要等一下。如果建成功了,會出現「建置成功」的訊息。
如果建失敗了。像下面的,就要去找LOG檔看錯誤訊息
LOG檔位置:我是是在 C:\app\Administrator\cfgtoollogs\emca\orcl
記得,LOG檔裡面,還有一個記錄 CREATELOG,如: emca_repos_create_2011()_08()_25()_09()_15()_58().log
這個很重要,如果建置失敗,真正的原因,會寫在這裡
如果建失敗了,記得,所有的步驟都要從頭開始,因為即始建失敗,他也沒有ROLL BACK
所以那些USER都在 = =,搞了老半天= =

附件:drop-repo.sql
DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM ' || r1.owner || '.' ||r1.name;
END IF;
END LOOP;
END;
DROP USER mgmt_view CASCADE;
DROP ROLE mgmt_user;
DROP USER sysman CASCADE;
ALTER SYSTEM DISABLE RESTRICTED SESSION;

附件:del_user.sql
drop user SYSMAN cascade;
drop user MGMT_VIEW cascade;
drop role MGMT_USER;
drop public synonym MGMT_TARGET_BLACKOUTS;
drop public synonym SETEMVIEWUSERCONTEXT;
drop user sysman cascade;
drop public synonym MGMT_ADMIN;
drop public synonym MGMT_AS_ECM_UTIL;
drop public synonym MGMT_AVAILABILITY;
drop public synonym MGMT_COLLECTION_PROPERTIES;
drop public synonym MGMT_CREDENTIAL;
drop public synonym MGMT_CURRENT_AVAILABILITY;
drop public synonym MGMT_CURRENT_METRICS;
drop public synonym MGMT_CURRENT_METRIC_ERRORS;
drop public synonym MGMT_CURRENT_SEVERITY;
drop public synonym MGMT_DELTA;
drop public synonym MGMT_DELTA_ENTRY;
drop public synonym MGMT_DELTA_ENTRY_VALUES;
drop public synonym MGMT_DELTA_IDS;
drop public synonym MGMT_DELTA_ID_VALUES;
drop public synonym MGMT_DELTA_VALUE;
drop public synonym MGMT_DELTA_VALUES;
drop public synonym MGMT_GLOBAL;
drop public synonym MGMT_GUID_ARRAY;
drop public synonym MGMT_GUID_OBJ;
drop public synonym MGMT_IP_TGT_GUID_ARRAY;
drop public synonym MGMT_JOB;
drop public synonym MGMT_JOBS;
drop public synonym MGMT_JOB_EXECPLAN;
drop public synonym MGMT_JOB_EXECUTION;
drop public synonym MGMT_JOB_EXEC_SUMMARY;
drop public synonym MGMT_JOB_OUTPUT;
drop public synonym MGMT_JOB_PARAMETER;
drop public synonym MGMT_JOB_SCHEDULE;
drop public synonym MGMT_JOB_TARGET;
drop public synonym MGMT_LOG;
drop public synonym MGMT_LONG_TEXT;
drop public synonym MGMT_MESSAGES;
drop public synonym MGMT_METRICS;drop public synonym MGMT_METRICS_1DAY;
drop public synonym MGMT_METRICS_1HOUR;
drop public synonym MGMT_METRICS_COMPOSITE_KEYS;
drop public synonym MGMT_METRICS_RAW;
drop public synonym MGMT_METRIC_COLLECTIONS;
drop public synonym MGMT_METRIC_ERRORS;
drop public synonym MGMT_METRIC_THRESHOLDS;
drop public synonym MGMT_NAME_VALUE;
drop public synonym MGMT_NAME_VALUES;
drop public synonym MGMT_PREFERENCES;
drop public synonym MGMT_SEVERITY;
drop public synonym MGMT_SEVERITY_ARRAY;
drop public synonym MGMT_SEVERITY_OBJ;
drop public synonym MGMT_STRING_METRIC_HISTORY;
drop public synonym MGMT_TARGET;
drop public synonym MGMT_TARGETS;
drop public synonym MGMT_TARGET_BLACKOUTS;
drop public synonym MGMT_TARGET_MEMBERSHIPS;
drop public synonym MGMT_TARGET_PROPERTIES;
drop public synonym MGMT_TYPE_PROPERTIES;
drop public synonym MGMT_USER;
drop public synonym MGMT_VIEW_UTIL;
drop public synonym MGMT$DELTA_ORACLE_HOME;
drop public synonym MGMT$DELTA_OS_COMPONENTS;
drop public synonym MGMT$DELTA_OS_COMP_DETAILS;
drop public synonym MGMT$DELTA_OS_KERNEL_PARAMS;
drop public synonym MGMT$DELTA_PATCHSETS;
drop public synonym MGMT$DELTA_PATCHSET_DETAILS;
drop public synonym MGMT$DELTA_TABLESPACES;
drop public synonym MGMT$DELTA_VENDOR_SW;
drop public synonym MGMT$DELTA_VIEW;
drop public synonym MGMT$DELTA_VIEW_DETAILS;
drop public synonym MGMT$ECM_CURRENT_SNAPSHOTS;
drop public synonym MGMT$ECM_VISIBLE_SNAPSHOTS;
drop public synonym MGMT$GROUP_DERIVED_MEMBERSHIPS;
drop public synonym MGMT$GROUP_FLAT_MEMBERSHIPS;
drop public synonym MGMT$GROUP_MEMBERS;
drop public synonym MGMT$HA_BACKUP;
drop public synonym MGMT$HA_FILES;
drop public synonym MGMT$HA_INFO;
drop public synonym MGMT$HA_INIT_PARAMS;
drop public synonym MGMT$HA_MTTR;
drop public synonym MGMT$HA_RMAN_CONFIG;
drop public synonym MGMT$HW_NIC;
drop public synonym MGMT$METRIC_COLLECTION;
drop public synonym MGMT$METRIC_CURRENT;
drop public synonym MGMT$METRIC_DAILY;
drop public synonym MGMT$METRIC_DETAILS;
drop public synonym MGMT$METRIC_HOURLY;
drop public synonym MGMT$MISSING_TARGETS;
drop public synonym MGMT$MISSING_TARGETS_IN_GROUPS;
drop public synonym MGMT$OS_COMPONENTS;
drop public synonym MGMT$OS_FS_MOUNT;
drop public synonym MGMT$OS_HW_SUMMARY;
drop public synonym MGMT$OS_KERNEL_PARAMS;
drop public synonym MGMT$OS_PATCHES;
drop public synonym MGMT$OS_SUMMARY;
drop public synonym MGMT$SOFTWARE_COMPONENTS;
drop public synonym MGMT$SOFTWARE_COMPONENT_ONEOFF;
drop public synonym MGMT$SOFTWARE_COMP_PATCHSET;
drop public synonym MGMT$SOFTWARE_DEPENDENCIES;
drop public synonym MGMT$SOFTWARE_HOMES;
drop public synonym MGMT$SOFTWARE_ONEOFF_PATCHES;
drop public synonym MGMT$SOFTWARE_OTHERS;
drop public synonym MGMT$SOFTWARE_PATCHES_IN_HOMES;
drop public synonym MGMT$SOFTWARE_PATCHSETS;
drop public synonym MGMT$TARGET;
drop public synonym MGMT$TARGET_COMPONENTS;
drop public synonym MGMT$TARGET_COMPOSITE;
drop public synonym MGMT$TARGET_PROPERTIES;
drop public synonym MGMT$TARGET_TYPE;
drop PUBLIC SYNONYM SMP_EMD_AVAIL_OBJ;
drop public synonym EMD_MNTR;
drop public synonym SMP_EMD_AVAIL_OBJ;
drop public synonym SMP_EMD_DELETE_REC_ARRAY;
drop public synonym SMP_EMD_INTEGER_ARRAY;
drop public synonym SMP_EMD_INTEGER_ARRAY_ARRAY;
drop public synonym SMP_EMD_NVPAIR;
drop public synonym SMP_EMD_NVPAIR_ARRAY;
drop public synonym SMP_EMD_STRING_ARRAY;
drop public synonym SMP_EMD_STRING_ARRAY_ARRAY;
drop public synonym SMP_EMD_TARGET_OBJ;
drop public synonym SMP_EMD_TARGET_OBJ_ARRAY;
drop public synonym ECM_UTIL;
drop role mgmt_user;
DROP USER sysman CASCADE;
DROP USER mgmt_view CASCADE;
DROP ROLE MGMT_USER;
ALTER USER dbsnmp ACCOUNT UNLOCK;
ALTER USER dbsnmp identified by dbsnmp;
DROP PUBLIC SYNONYM SETEMVIEWUSERCONTEXT;
DROP PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;
DROP PUBLIC SYNONYM MGMT_AVAILABILITY;
DROP PUBLIC SYNONYM MGMT_CURRENT_AVAILABILITY;
DROP PUBLIC SYNONYM MGMT_SEVERITY_OBJ;
DROP PUBLIC SYNONYM MGMT_SEVERITY_ARRAY;
DROP PUBLIC SYNONYM MGMT_GUID_OBJ;
DROP PUBLIC SYNONYM MGMT_GUID_ARRAY;
DROP PUBLIC SYNONYM SMP_EMD_AVAIL_OBJ;
DROP PUBLIC SYNONYM SMP_EMD_TARGET_OBJ;
DROP PUBLIC SYNONYM SMP_EMD_TARGET_OBJ_ARRAY;
DROP PUBLIC SYNONYM SMP_EMD_DELETE_REC_ARRAY;
DROP PUBLIC SYNONYM MGMT_METRICS;
DROP PUBLIC SYNONYM MGMT_TARGETS;
DROP PUBLIC SYNONYM MGMT_TYPE_PROPERTIES;
DROP PUBLIC SYNONYM MGMT_TARGET_PROPERTIES;
DROP PUBLIC SYNONYM MGMT_METRICS_RAW;
DROP PUBLIC SYNONYM MGMT_CURRENT_METRICS;
DROP PUBLIC SYNONYM MGMT_STRING_METRIC_HISTORY;
DROP PUBLIC SYNONYM MGMT_LONG_TEXT;
DROP PUBLIC SYNONYM MGMT_METRICS_COMPOSITE_KEYS;
DROP PUBLIC SYNONYM MGMT_METRICS_1HOUR;
DROP PUBLIC SYNONYM MGMT_METRICS_1DAY;
DROP PUBLIC SYNONYM MGMT_METRIC_ERRORS;
DROP PUBLIC SYNONYM MGMT_CURRENT_METRIC_ERRORS;
DROP PUBLIC SYNONYM EMD_MNTR;
DROP PUBLIC SYNONYM MGMT_METRIC_COLLECTIONS;
DROP PUBLIC SYNONYM MGMT_COLLECTION_PROPERTIES ;
DROP PUBLIC SYNONYM MGMT_METRIC_THRESHOLDS;
DROP PUBLIC SYNONYM SMP_EMD_NVPAIR;
DROP PUBLIC SYNONYM SMP_EMD_NVPAIR_ARRAY;
DROP PUBLIC SYNONYM SMP_EMD_STRING_ARRAY;
DROP PUBLIC SYNONYM SMP_EMD_STRING_ARRAY_ARRAY;
DROP PUBLIC SYNONYM SMP_EMD_INTEGER_ARRAY;
DROP PUBLIC SYNONYM SMP_EMD_INTEGER_ARRAY_ARRAY;
DROP PUBLIC SYNONYM MGMT_DELTA_IDS;
DROP PUBLIC SYNONYM MGMT_DELTA_ID_VALUES;
DROP PUBLIC SYNONYM MGMT_DELTA_ENTRY;
DROP PUBLIC SYNONYM MGMT_DELTA_ENTRY_VALUES;
DROP PUBLIC SYNONYM MGMT_DELTA_VALUE;
DROP PUBLIC SYNONYM MGMT_DELTA_VALUES;
DROP PUBLIC SYNONYM MGMT_NAME_VALUE;
DROP PUBLIC SYNONYM MGMT_NAME_VALUES;
DROP PUBLIC SYNONYM ECM_UTIL;
DROP PUBLIC SYNONYM MGMT_DELTA;
DROP PUBLIC SYNONYM MGMT_TARGET_MEMBERSHIPS;
DROP PUBLIC SYNONYM MGMT_JOB;
DROP PUBLIC SYNONYM MGMT_JOB_PARAMETER;
DROP PUBLIC SYNONYM MGMT_JOB_TARGET;
DROP PUBLIC SYNONYM MGMT_JOB_SCHEDULE;
DROP PUBLIC SYNONYM MGMT_JOB_EXECPLAN;
DROP PUBLIC SYNONYM MGMT_JOB_EXEC_SUMMARY;
DROP PUBLIC SYNONYM MGMT_JOB_OUTPUT;
DROP PUBLIC SYNONYM MGMT_JOB_EXECUTION;
DROP PUBLIC SYNONYM MGMT_LOG;
DROP PUBLIC SYNONYM MGMT_ADMIN;
DROP PUBLIC SYNONYM MGMT_GLOBAL;
DROP PUBLIC SYNONYM MGMT_TARGET;
DROP PUBLIC SYNONYM MGMT_CREDENTIAL;
DROP PUBLIC SYNONYM MGMT_JOBS;
DROP PUBLIC SYNONYM MGMT_PREFERENCES;
DROP PUBLIC SYNONYM MGMT_USER;
DROP PUBLIC SYNONYM MGMT_SEVERITY;
DROP PUBLIC SYNONYM MGMT_CURRENT_SEVERITY;
DROP PUBLIC SYNONYM MGMT_PAF_PROCS_LATEST;
DROP PUBLIC SYNONYM MGMT_PAF_JOBS;
DROP PUBLIC SYNONYM MGMT_PAF$PROCEDURES;
DROP PUBLIC SYNONYM MGMT_PAF$INSTANCES;
DROP PUBLIC SYNONYM MGMT_PAF$STATES;
DROP PUBLIC SYNONYM MGMT_PAF$APPLICATIONS;
DROP PUBLIC SYNONYM MGMT_VIEW_UTIL;
DROP PUBLIC SYNONYM MGMT_MESSAGES;


2011年8月23日 星期二

如何在Windows 7安裝informix


win 7本身有權限的設定,而informix安裝時,又會和作業系統的權限綁在一起

所以,安裝過程中,會碰到一些 是安裝在 window XP 沒有的問題

我把解決方法,記下來

,如果有碰到問題,可以參考一下

<<注意事項一>>

Informix要灌時的時候

密碼要先用自已 登入電腦的密碼 ( 就是電腦的開機密碼 )

不然的話,informix instance 會起不來


要檢查 Informix instance,有沒有被 startup

可以看 windows的工作管理員,有沒有多一個 oninit.exe

這個是 Informix instance server的執行服務

另外,也可以看 控制台/使用者帳戶/管理使用者帳戶

是不是有多一個 [ informix ] 的使用者名稱


<<注意事項二>>

如果一直灌了又殺殺了又灌,ol_informix1700 instance name 會一直增加編號

請到 C:\Windows\System32\drivers\etc\service

檔案打開,最下面會有被用掉的 instance num, for example..

ol_informix1170 9088/tcp

dr_informix1170 9089/tcp

ol_informix1170_1 9088/tcp

dr_informix1170_1 9089/tcp

把它刪掉存檔就可以了,

記得,如果要重灌的話,也要去刪掉

注意:如果 service 這個檔案,一開始會有「無法存檔」的情況

滑鼠點 檔案 按右鍵/內容/安全性,然後點「編輯」,選你目前的使用者名稱

在「允許」的checkbox,打勾,再重開一次檔案,就可以儲存檔案了



<<注意事項三>>

我是因為專案的需要,所以要額外設定一個統一帳號和密碼

Informix是和微軟的作業系統的權限綁在一起,所以,要先從 微軟這邊建一個帳號

微軟建新的使用者帳號:從控制台/使用者帳號,這邊新增是不會成功的

請看下面微軟官網建帳號:

建立新的使用者 http://windows.microsoft.com/zh-TW/windows7/Create-a-user-account

將使用者加入群組 http://windows.microsoft.com/zh-TW/windows7/Add-a-user-account-to-a-group

(ps。群組選 Informix-Admin)

然後,還要再進 Informix ol_informix1170service instance 去做兩件事情

1,create Database

2,grant dba的權限給 你指定的user

<<指令>>

database 你建的dbName;

grant dba to 你指定的userName;


打完後,按 esc,選run,就完成了

要有出現 “permission granted” 才是有grant成功

補充:還要 grant resource才有辦法執行新增table

Grant resource to public;

grant RESOURCE to portal;