使用OEM,SQLPlus,iSQLPlus 管理Oracle例項

分類： Oracle 其它特性 2010-04-26 11:55 487人閱讀評論(0) 舉報

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

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

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

/*

一、管理元件

Oracle 10g資料庫管理包含三個元件,本文主要介紹Database Control,SQL*Plus及iSQL*Plus

Database instance(資料庫例項) --&gt系統使用的後臺程式

Listener (監聽器) --&gt監聽客戶端連線到資料庫

Management interfance

Database Control --&gtOEM

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 啟動後用瀏覽器來登陸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

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

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

Stopping Oracle Enterprise Manager 10g Database Control ...

... Stopped.

/*

3.重建OEM

　　　　由於EM需要J2EE環境支援，正常建立後，在$ORACLE_HOME目錄下將會建立一個

　　　　類似$ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole__的目錄，如果此目錄不存在，則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

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 <<<<<<<<<<<

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

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__

b.$ORACLE_HOME/_

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

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

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

Stopping iSQL*Plus ...

iSQL*Plus stopped.

/*

修改iSQL*Plus預設埠號

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

--將改為其他埠號即可

啟動isqlplus,然後透過瀏覽器登陸

注意：預設情況下，sysdba使用者無法透過isqlplus登陸

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