ORACLE Enterprise Manager (EM) 的設定與啟動

Database Control 透過EM 來進行管理，EM的設定步驟如下:

(1)設定參數(db01, db02)

/usr/local/bin/setsedb ORACLE_SID , ORACLE_UNQNAME , ORACLE_HOSTNAME /etc/hosts

(2)設定ORACLE Net(db01, db02)

/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 設定完成以後，使用 lsnrctl start啟動db01及db02的監聽器

(3)刪除舊有的設定(db01)

emca -deconfig dbcontrol db -repos drop

(4)建立新的設定

emca -config dbcontrol db -repos create

有下列參數必須設定妥當:(透過執行SCRIPT: setsedb)

ORACLE_SID

ORACLE_UNQNAME (透過執行 select name, db_unique_name from v$database; 取得 db_unique_name )

ORACLE_HOSTNAME (即是 HOSTNAME)

【PRIMARY (DB01)設定】

主機部分(HOSTNAME)，設定於/etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.80.136 db01

ORACLE設定檔，位置在 /usr/local/bin/setsedb

ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME

ORACLE_SID=sedb; export ORACLE_SID

ORACLE_ALERT=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace; export ORACLE_ALERT

NLS_LANG=AMERICAN_AMERICA.AL32UTF8; export NLS_LANG

NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"; export NLS_DATE_FORMAT

ORACLE_UNQNAME=prisedb;

ORACLE_HOSTNAME=$HOSTNAME;

ORA_NLS33=$ORACLE_HOME/nls/data; export ORA_NLS33

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

PATH=$ORACLE_HOME/bin:/usr/bin:/usr/ucb:/etc:.:$PATH

export PATH

ORACLE監聽器，檔案在 /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = prisedb)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = sedb)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db01)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /oracle/app/oracle

【STANDBY (DB02)設定】

主機部分(HOSTNAME)，設定於/etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.80.133 db02

ORACLE設定檔，位置在 /usr/local/bin/setsedb

ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME

ORACLE_SID=sedb; export ORACLE_SID

ORACLE_ALERT=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace; export ORACLE_ALERT

NLS_LANG=AMERICAN_AMERICA.AL32UTF8; export NLS_LANG

NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"; export NLS_DATE_FORMAT

ORACLE_UNQNAME=stdsedb;

ORACLE_HOSTNAME=$HOSTNAME;

ORA_NLS33=$ORACLE_HOME/nls/data; export ORA_NLS33

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

PATH=$ORACLE_HOME/bin:/usr/bin:/usr/ucb:/etc:.:$PATH

export PATH

ORACLE監聽器，檔案在 /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = stdsedb)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = sedb)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db02)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /oracle/app/oracle

【PRIMARY EM 設定】

【刪除舊有的設定】

[oracle@db01 j2ee]$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Jun 8, 2017 5:56:06 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:

Database SID: sedb

Listener port number:

Listener port number: 1521

Password for SYS user:

Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y

Jun 8, 2017 5:57:48 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /oracle/app/oracle/cfgtoollogs/emca/prisedb/emca_2017_06_08_17_56_05.log.

Jun 8, 2017 5:57:49 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration

WARNING: EM is not configured for this database. No EM-specific actions can be performed.

Jun 8, 2017 5:57:49 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl

WARNING: Error initializing SQL connection. SQL operations cannot be performed

Jun 8, 2017 5:57:49 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Jun 8, 2017 5:57:51 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Enterprise Manager configuration completed successfully

FINISHED EMCA at Jun 8, 2017 5:57:51 PM

【建立新的設定】

[oracle@db01 j2ee]$ emca -config dbcontrol db -repos create

STARTED EMCA at Jun 8, 2017 6:03:46 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:

Database SID: sedb

Listener port number: 1521

Listener ORACLE_HOME [ /oracle/app/oracle/product/11.2.0/dbhome_1 ]:

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /oracle/app/oracle/product/11.2.0/dbhome_1

Local hostname ................ db01

Listener ORACLE_HOME ................ /oracle/app/oracle/product/11.2.0/dbhome_1

Listener port number ................ 1521

Database SID ................ sedb

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: Y

Jun 8, 2017 6:04:30 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /oracle/app/oracle/cfgtoollogs/emca/prisedb/emca_2017_06_08_18_03_46.log.

Jun 8, 2017 6:04:31 PM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) ...

Jun 8, 2017 6:13:47 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully created

Jun 8, 2017 6:13:57 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository

INFO: Uploading configuration data to EM repository (this may take a while) ...

Jun 8, 2017 6:15:08 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Uploaded configuration data successfully

Jun 8, 2017 6:15:13 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib

INFO: Software library configured successfully.

Jun 8, 2017 6:15:13 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary

INFO: Deploying Provisioning archives ...

Jun 8, 2017 6:15:34 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary

INFO: Provisioning archives deployed successfully.

Jun 8, 2017 6:15:34 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Securing Database Control (this may take a while) ...

Jun 8, 2017 6:16:20 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Database Control secured successfully.

Jun 8, 2017 6:16:20 PM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

Jun 8, 2017 6:17:23 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: Database Control started successfully

Jun 8, 2017 6:17:23 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: >>>>>>>>>>> The Database Control URL is https://db01:1158/em <<<<<<<<<<<

Jun 8, 2017 6:17:25 PM oracle.sysman.emcp.EMDBPostConfig invoke

WARNING:

************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /oracle/app/oracle/product/11.2.0/dbhome_1/db01_prisedb/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************

Enterprise Manager configuration completed successfully

FINISHED EMCA at Jun 8, 2017 6:17:25 PM

【設定DB CONTROL CONSOLE】

上述設定完成以後，執行 emctl start dbconsole

但是必定要在前述設定皆完成以後再執行

先執行的結果

[oracle@db01 j2ee]$ emctl start dbconsole

OC4J Configuration issue. /oracle/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_db01_prisedb not found.

【執行成功，會產生的資料夾】

ORACLE的參數檔: 執行個體中的參數

參數檔可以透過以下VIEW查詢 執行中INSTANCE的參數(記憶體中)： V$PARAMETER 其結構如下:

DISK儲存的靜態參數(檔案為SPFILE{$SID}.ora，例如，SID為SEDB，則檔案為SPFILESEDB.ORA)： V$SPPARAMETER 其結構如下:

查詢【基本參數Basic Parameter】的方法： select name, value from v$parameter where isbasic='TRUE' order by name;

查詢SPFILE和記憶體中的基本參數(但是SPPARAMETER的欄位較少)： select s.name, s.value from v$spparameter s join v$parameter p on s.name=p.name where p.isbasic='TRUE' order by name;

欲修改靜態參數，需使用ALTER SYSTEM，並且在命令後面加上改變範圍，將改變範圍設為SPFILE，或是BOTH(預設值)，例如： alter system set log_buffer=6m scope=both; 或是 alter system set log_buffer=6m scope=SPFILE;

與SESSION有關的參數和設定

與SESSION 有關的VIEW為 NLS_SESSION_PARAMETERS 查詢所有與SESSION有關的參數 select * from nls_session_parameters

例如，欲更改幣值 ALTER SESSION set NLS_CURRENCY='GBP';

TO_DATE ORA-01843:not a valid month 錯誤處理

例如，在筆電上，執行

select to_date('25-DEC-2010') from dual;

ORA-01843: 不是有效的月份

這個問題的本質是系統不能識別英文的月簡寫，而能識別中文。

Oracle系統的語言配置主要保存在V$NLS_PARAMETERS數據字典視圖中。查詢該視圖關於語言的設置值。如下：

SQL> select * from v$nls_parameters where parameter like '%DATE%';

SQL> alter session set nls_date_language='american';

Session altered

修改後，參數的日期的語言設定更改如下:

然後，再執行

select to_date('25-DEC-2010') from dual;

結果就正常了

從資料字典中，找出某表格的INDEX資訊

select index_name, column_name, index_type, uniqueness from user_indexes natural join user_ind_columns where table_name='CUSTOMERS';

保險理賠的理與賠

【中視新聞】 "笑"死了... 富邦人壽拒賠意外險敗訴 20141212

三商美邦補拒賠孝子事件

三商美邦騙老榮民

保險業務偽造簽名代投保

腿斷了，一家賠，一家不賠。

大腸癌被拒賠

吼！住院切息肉 新光保險不理賠

路見不平篇

五星級保障，平民化價格

說笑間猝死　富邦人壽拒賠意外險敗訴

富邦逼自家員工買長照險保單

付十年保費，只理賠一年(神經炎)

千萬意外險拒賠，卻勾結理賠黃牛A錢

北部全賠，中南部七成

嗅覺喪失拒賠

關於ORACLE的字元型態和其長度

資料庫儲存字元的常用型態有CHAR 與 VARCHAR2

而在使用這兩個型態的時候

常常很難決定到底要不要加上單位?

定義 varcha2(30) ，varchar2(30 byte)，還是varchar2(30 char)

哪種方式長度才夠? 尤其我們必須處理中文字的時候就很捆擾

在資料庫中，有一個參數 NLS_LENGTH_SEMANTICS

就定義，如果不加上長度單位的時候，那預設的單位為何

(可以用以下SQL去查詢你的定義為何:

select * from v$parameter where name like '%nls_length%'; )

如果不去特別定義，這個參數的預設值是 BYTE

要注意

每個中文字元長度佔 3 bytes

每個英數字長度佔 1 bytes

所以在定義欄位長度時，如果是varchar2

建議使用 char 當作欄位長度的單位

如果不用任何長度單位，那會使用系統預設值，也就是 byte

所以如果定義任何一個欄位長度為 varchar2(3 char)

那它就可以儲存三個中文字

如果定義為 varchar2(3) 則長度為 3 bytes

也就是說，只能儲存一個中文字

幾個常見的字元函數傳回值的型態要注意

length ==>傳回的長度，以char為單位 所以 length('中文字') = 3

lengthb ==>傳回的長度，以byte為單位 所以 length('中文字') = 9

substr 以CHAR為單位，所以 substr('中文字',1,2)，傳回 '中文'

substrb 以BYTE為單位，所以 substrb('中文字',1,6)，傳回 '中文'

至於資料型態 CHAR 與 VARCHAR2 的根本差異

要注意前者是固定長度字串

後者是變動長度字串

前者，在儲存資料時，會將長度不足之處補上空白

例如，假如宣告char(3)，而儲存的字串是'NO'，則在DB裡面會儲存 'NO '，包含一個空白字元

而且在比較的時候，也會把那個空白字元一起拿來比較

後者，varchar2，則是變動長度字串

後者，在儲存資料的時候，不會補上空白字元

在比較的時候，也不會把字元後面的空白拿來一起比

所以，如果宣告varchar2(3)，而儲存的長度是'NO'，則在DB裡面會儲存 'NO'，不含空白字元