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

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

--使用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体系结构)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏SpringBoot 核心技术

第三十九章:基于SpringBoot & Quartz完成定时任务分布式单节点持久化

752100
来自专栏数据和云

实战课堂:为什么更换存储之后一切正常但RAC集群启动不了?

这是一次来自生产实践的真实案例,某客户核心生产库由于进行新老存储替换变更操作后,Oracle RAC 两个节点均无法打开,数据库遭遇严重故障。

14430
来自专栏乐沙弥的世界

MHA 清理relay log(purge_relay_logs)

    MySQL数据库主从复制在缺省情况下从库的relay logs会在SQL线程执行完毕后被自动删除,但是对于MHA场景下,对于某些滞后从库的恢复依赖于其他...

17600
来自专栏张戈的专栏

gh-ost:在线DDL修改MySQL表结构工具

在之前,我分享过一次 pt-online-schema-change 在线 DDL 的工具实践记录,在实际使用过程中,发现部门的很多老系统大量使用了触发器,从而...

1.1K70
来自专栏王磊的博客

Spring Boot 最佳实践(五)Spring Data JPA 操作 MySQL 8

JPA(Java Persistence API)Java持久化API,是 Java 持久化的标准规范,Hibernate是持久化规范的技术实现,而Spring...

30920
来自专栏一个会写诗的程序员的博客

8.7 Spring Boot集成日志小结

Java日志框架众多,常用的有java.util.logging, log4j, logback,commons-logging等。

49320
来自专栏杨建荣的学习笔记

sysdba登录报错insufficient privileges的原因分析(r7笔记第64天)

今天碰到一个奇怪的小问题,看起来是一个非常简单的问题。 我先说一下问题背景。有一台DB服务器,因为需要搭建备库,现在找了一台已有的机器临时用一下,这台临时的服务...

36460
来自专栏乐沙弥的世界

基于Innobackupex的全备恢复

    对于MySQL数据库的热备,xtrabackup是大多数DBA朋友们的选择。xtrabackup内嵌了一个innobackupex可用于热备MySQL数...

44320
来自专栏菩提树下的杨过

IBM WebSphere MQ 7.5基本用法

一、下载7.5 Trial版本 http://www.ibm.com/developerworks/downloads/ws/wmq/ 这是下载网址,下载前先必...

44880
来自专栏挖掘大数据

通过Sqoop将MySQL数据导入到HDFS/HBase

本文将利用Sqoop将MySQL海量测试数据导入到HDFS和HBase。

38800

扫码关注云+社区

领取腾讯云代金券