--==========================================

-- 使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 實例

--==========================================

/*

一、管理組件

??? Oracle 10g 數據庫管理包含三個組件 , 本文主要介紹 Database Control,SQL*Plus 及 iSQL*Plus

??? ?? Database instance( 數據庫實例 ) ? --> 系統使用的后臺進程

??? ?? Listener ( 監聽器 ) ???? ????????? --> 監聽客戶端連接到數據庫

??? ?? Management interfance

??? ?????? Database Control ?????????? -->OEM

?????? ?? Management agent (when using Grid Control) ? -- 網格管理

二、使用 OEM 管理 Oracle ????? ??

??? 1. 關于 OEM ： Oracle 從 i 開始就隨軟件提供企業管理器（ OEM ）

??? ??? 在 g 中， oracle 在 dbca 建庫過程中會出現四個用戶： sys,system,dbsnap,sysman

?????? (--dbsnap 用戶用于 OEM 智能代理管理工作， sysman 用戶則是 OEM 的管理員帳號）

?????? 如果在 dbca 建庫過程中沒有選擇安裝 OEM, 則沒有 dbsnap 和 sysman 用戶，如果在 dbca 建庫時選擇啟用 database control 管理數據庫，

?????? 則需要在數據庫中建立一個 sysman 的 schema ，用于保存 OEM 的一些數據，這個就是 OEM 的資料檔案庫（ repository) ，在 G 版本中，

?????? 這個庫存儲在 sysaux 表空間中

??????

??? 2.OEM 的啟動和關閉

??? 啟動： emctl start dbconsole ?? 啟動后用瀏覽器 http://IP:1158/em ?? 來登陸 OEM ，進行圖形化操作數據庫

?????? 關閉： emctl stop dbconsole

?????? 狀態： emctl status dbconsole ???????????????????? */

?????? -- 下面演示 EM 啟動到停止的過程

?????? [oracle@robinson scripts]$ emctl start dbconsole ? -- 啟動

??????? TZ set to PRC

??????? Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 ?

??????? Copyright (c) 1996, 2005 Oracle Corporation. ? All rights reserved.

??????? http://robinson.com:1158/em/console/aboutApplication

??????? Starting Oracle Enterprise Manager 10g Database Control ...................... started.

??????? ------------------------------------------------------------------

??????? Logs are generated in directory /u01/app/oracle/10g/robinson.com_orcl/sysman/log

??????? [oracle@robinson scripts]$ emctl status dbconsole ??? -- 查看狀態

??????? TZ set to PRC

??????? Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 ?

??????? Copyright (c) 1996, 2005 Oracle Corporation. ? All rights reserved.

??????? http://robinson.com:1158/em/console/aboutApplication

??????? Oracle Enterprise Manager 10g is running.

??????? ------------------------------------------------------------------

??????? Logs are generated in directory /u01/app/oracle/10g/robinson.com_orcl/sysman/log

??????? [oracle@robinson scripts]$ emctl stop dbconsole ????? -- 停止

??????? TZ set to PRC

??????? Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 ?

??????? Copyright (c) 1996, 2005 Oracle Corporation. ? All rights reserved.

??????? http://robinson.com:1158/em/console/aboutApplication

??????? Stopping Oracle Enterprise Manager 10g Database Control ...

??????? ... ? Stopped.

??????? /*

???????

??? 3. 重建 OEM

　　　　由于 EM 需要 J2EE 環境支持，正常創建后，在 $ORACLE_HOME 目錄下將會建立一個

　　　　類似 $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid> 的目錄，如果此目錄不存在，則 EM 將無法啟動。

　　　　另外，如果服務器修改過主機名等信息，導致 OEM 可能無法啟動，此時需要重新配置 EM 。 ???

??????? 使用 emca 命令來重建 EM 檔案庫，關于 emca 的用戶，使用 emca -h 來查看詳細說明 */

??????

?????? [oracle@robinson scripts]$ emca - config dbcontrol db - repos recreate

?

?????? STARTED EMCA at Apr 21, 2010 3:09:04 PM

?????? EM Configuration Assistant, Version 10.2.0.1.0 Production

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

?

?????? Enter the following information:

?????? Database SID: orcl

?????? Database Control is already configured for the database orcl

?????? You have chosen to configure Database Control for managing the database orcl

?????? This will remove the existing configuration and the default settings and perform a fresh configuration

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

?????? Listener port number: 1521

?????? 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 ................ /u01/app/oracle/10g

?

?????? Database hostname ................ robinson.com

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

?????? Database SID ................ orcl

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

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

?

?????? -----------------------------------------------------------------

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

?????? Apr 21, 2010 3:09:24 PM oracle.sysman.emcp.EMConfig perform

?????? INFO: This operation is being logged at /u01/app/oracle/10g/cfgtoollogs/emca/orcl/emca_2010-04-21_03-09-04-PM.log.

?????? Apr 21, 2010 3:09:25 PM oracle.sysman.emcp.util.DBControlUtil stopOMS

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

?????? Apr 21, 2010 3:09:32 PM oracle.sysman.emcp.EMReposConfig dropRepository

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

?????? Apr 21, 2010 3:11:41 PM oracle.sysman.emcp.EMReposConfig invoke

?????? INFO: Repository successfully dropped

?????? Apr 21, 2010 3:11:41 PM oracle.sysman.emcp.EMReposConfig createRepository

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

?????? Apr 21, 2010 3:16:01 PM oracle.sysman.emcp.EMReposConfig invoke

?????? INFO: Repository successfully created

?????? Apr 21, 2010 3:16:08 PM oracle.sysman.emcp.util.DBControlUtil startOMS

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

?????? Apr 21, 2010 3:17:55 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

?????? INFO: Database Control started successfully

?????? Apr 21, 2010 3:17:55 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

?????? INFO: >>>>>>>>>>> The Database Control URL is http://robinson.com:1158/em <<<<<<<<<<<

?????? Enterprise Manager configuration completed successfully

?????? FINISHED EMCA at Apr 21, 2010 3:17:55 PM

?????? [oracle@robinson scripts]$ emctl status dbconsole

?????? TZ set to PRC

?????? Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 ?

?????? Copyright (c) 1996, 2005 Oracle Corporation. ? All rights reserved.

?????? http://robinson.com:1158/em/console/aboutApplication

?????? Oracle Enterprise Manager 10g is running.

?????? ------------------------------------------------------------------

?????? Logs are generated in directory /u01/app/oracle/10g/robinson.com_orcl/sysman/log

?????? /*

?????? 注意：如果啟動 EM 有問題，確保監聽器已經啟動

?????? 打開瀏覽器，使用 sysman 用戶登陸，也可以使用 sys 用戶登陸，當使用 sys 用戶登陸時，注意幾點：

?????? a. 口令文件存在并配置正常

?????? b. 監聽啟動

?????? c.remote_login_passwordfile 設置不能為 NONE

?????? 重建 EM 成功后， ORACLE 主要創建的相關目錄有個，分別是 :

??????? a.$ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>

??????? b.$ORACLE_HOME/<hostname>_<sid>

??????

??? 4. 查看默認的端口號 ?? ?? */

??? ?? [oracle@robinson scripts]$ cat $ORACLE_HOME/install/portlist.ini

??????? iSQL*Plus HTTP port number =5560

??????? Enterprise Manager Console HTTP Port (orcl) = 1158

??????? Enterprise Manager Agent Port (orcl) = 3938

??? ? /* ???

??????

??? 5. 建議

??? ??? OEM 管理和維護直觀簡單，但并不建議使用 OEM 來進行管理，因為 JAVA 的界面通常消耗的資源較多，

?????? 加上網絡連接的因素， OEM 界面很容易失去響應，從而可能導致很多意外，通過命令行操作可以讓

?????? 我們更加熟悉 ORACLE 的本質，又可以減少低級的意外錯誤。

?

三、使用 SQL*Plus 及 iSQL*Plus 管理 Oracle

??? SQL*Plus 及 iSQL*Plus 同樣可以完成 Oracle 的管理工作，能夠通過命令的方式來執行查詢，更新，刪除等操作

??????

??? 1.SQL*Plus 使用相關賬戶登陸到到實例，啟動數據庫，完成相關的管理任務 ? */

? ?? ? [oracle@robinson scripts]$ sqlplus / as sysdba

?

????? SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 21 15:38:54 2010

?

????? Copyright (c) 1982, 2005, Oracle. ? All rights reserved.

?

???? Connected to:

???? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

???? With the Partitioning, OLAP and Data Mining options

?

???? SQL> ?

??? ? /*

??? ?

??? 2.iSQL*Plus( 從 i 開始出現的基于 web 的 sqlplus 工具 )

????? 10g 下 iSQL*Plus 的啟動與關閉

????? 啟動： isqlplusctl start

??? ? 關閉： isqlplusctl stop ??????????????????? */

??? ? [oracle@robinson scripts]$ isqlplusctl start

????? perl: warning: Setting locale failed.

????? perl: warning: Please check that your locale settings:

????????? LANGUAGE = (unset),

????????? LC_ALL = (unset),

????????? LANG = "en"

????? are supported and installed on your system.

????? perl: warning: Falling back to the standard locale ("C").

????? iSQL*Plus 10.2.0.1.0

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

????? Starting iSQL*Plus ...

????? iSQL*Plus started.

?

??? ? [oracle@robinson scripts]$ isqlplusctl stop

????? perl: warning: Setting locale failed.

????? perl: warning: Please check that your locale settings:

????????? LANGUAGE = (unset),

????????? LC_ALL = (unset),

????????? LANG = "en"

????? are supported and installed on your system.

???? perl: warning: Falling back to the standard locale ("C").

???? iSQL*Plus 10.2.0.1.0

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

???? Stopping iSQL*Plus ...

???? iSQL*Plus stopped.

?

????? /*

??? ? 修改 iSQL*Plus 默認端口號

??? ? [oracle@robinson scripts]$ cat $ORACLE_HOME/oc4j/j2ee/isqlplus/config/http-web-site.xml |grep 5560

????? <web-site port="5560" display-name="OC4J Java HTTP Web Site"> ? -- 將改為其他端口號即可

??? ?

??? ? 啟動 isqlplus, 然后通過瀏覽器登陸 http://IP:5560/isqlplus

??? ? 注意：默認情況下， sysdba 用戶無法通過 isqlplus 登陸 ?

??? ?

四、更多 ??????? */

?

??? SQL/PLSQL 基礎

???

??? Oralce 10g 使用 DBCA 創建數據庫

???

??? 使用 Uniread 實現 SQLplus 翻頁功能

???

??? Linux (RHEL 5.4) 下安裝 Oracle 10g R2

???

??? VmWare6.5.2 下安裝 RHEL 5.4 （配置 Oracle 安裝環境）

???

??? Oracle 相關

?

??? Oracle 實例和 Oracle 數據庫 (Oracle 體系結構 )

??? ??

?

?

使用OEM,SQL*Plus,iSQL*Plus 管理Oracle實例