Wednesday, August 24, 2011

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;