oracle iSQL*PLUS配置设置图文说明

isql*plus介绍

isql*plus是从oracle 9i开始提供的新功能，是sql*plus的web形式(oracle 11g已经不支持)。是基于三层结构设计的。其client、middle、server可以位于同一台机器上也可以位于不同的机器。isqlplus不需要单独安装，通过isqlplus，用户可以不需要安装任何oracle客户端，就能够通过浏览器方式的使用sql*plus进行数据操作与数据库管理。普通的数据库用户可以直接通过isqlplus的网址http://ip:port/isqlplus登陆,进入该网址后会直接进入数据库用户登陆界面，使用数据库中的普通用户即可登陆；但如果是dba用户登陆isqlpus，则需要首先配置isql*plus dba的用户和口令，然后输入网址http://ip:port/isqlplus/dba，进入该网址后首先会弹出一个登陆框，要求先输入isql*plus dba的用户和密码，注意这里不是数据库用户，而是isql*plus应用服务器要求的用户和密码，然后才能出现isql*plus登陆界面，此时可以输入sys或者system用户，登陆数据库进行管理。要以dba身份登陆isqlplus，必须先配置好oc4j用户。采用xml配置文件认证的方式。该配置文件位于$oracle_home/oc4j/j2ee/isqlplus/application-deployments/isqlplus/config但是该配置文件中的密码是加密过的，所以我们不能手动修改该文件，而是通过jazn(java authorization)来配置。jazn是oracle提供的一个jass(java authentication and authorization service)工具.

如何进入jazn命令环境？

1.进入到目录$oracle_home/oc4j/j2ee/isqlplus/application-deployments/isqlplus/下，如下所示：

[oracle@db-server isqlplus]$ pwd

/database/product/dbhome_1/oc4j/j2ee/isqlplus/application-deployments/isqlplus

[oracle@db-server isqlplus]$ ls

application.log config isqlplus orion-application.xml

2.确保java_home环境变量指向了正确的jdk（需要1.4以上）路径，可以使用oracle自带的jdk，位于$oracle_home/jdk

3.执行以下命令

[oracle@db-server isqlplus]$ $oracle_home/jdk/bin/java -djava.security.properties=$oracle_home/oc4j/j2ee/home/config/jazn.security.props -jar $oracle_home/oc4j/j2ee/home/jazn.jar -user "isql*plus dba/admin" -password welcome -shell

其中realm=isql*plus dba，user=admin，这些可以从xml配置文件中看到，admin的默认密码是welcome，但是admin用户默认没有webdba权限，不能直接用于登陆isqlplus。通过jazn，可以完成以下任务

1:新建用户kerry 密码设置为etl123!@#

jazn:> adduser "isql*plus dba" kerry etl123!@#

2:列出用户

查看这个组的用户

jazn:> listusers "isql*plus dba"

admin

kerry

jazn:> listusers

isql*plus dba/admin

isql*plus dba/kerry

3.授予用户kerry dba的权限

jazn:> grantrole webdba "isql*plus dba" kerry

4.撤销用户kerry dba的权限

jazn:> revokerole webdba "isql*plus dba" kerry

5.查看角色

jazn:> listroles

isql*plus dba/webdba

isql*plus dba/admin

6.删除用户

jazn:> remuser "isql*plus dba" kerry

7.修改用户密码

jazn:> setpasswd "isql*plus dba" kerry etl123!@# 123456

8.退出jazn命令环境

jazn:> exit

上面我们创建了一个用户kerry，密码为etl123!@#，并且已经授予webdba权限。接下来重新启动isqlplus应用服务器isqlplusctl stop、isqlplusctl start

再进入网址http://ip:5560/isqlplus/dba，在弹出的对话框中输入kerry和etl123!@#，就可以进入到数据库登陆界面了，选择以sysdba或sysoper身份登陆了。

 

启动isql*plus

 

如果登录isqlplus页面打不开（报错）： http://ip:5560/isqlplus/，首先检查isqlplus服务启动没有，然后检查一下端口

[oracle@db-server ~]$ isqlplusctl start

isql*plus 10.2.0.1.0

copyright (c) 2003, 2005, oracle. all rights reserved.

getnameinfo failed

getnameinfo failed

starting isql*plus ...

isql*plus started.

[oracle@db-server ~]$ netstat -an | grep 5560

tcp 0 0 0.0.0.0:5560 0.0.0.0:* listen

停止isql*plus

[oracle@db-server ~]$ isqlplusctl stop

isql*plus 10.2.0.1.0

copyright (c) 2003, 2005, oracle. all rights reserved.

getnameinfo failed

getnameinfo failed

isql*plus instance on port 5560 is not running ...

 

日志信息

 

isql*plus使用的是log4j，如下所示：

[oracle@db-server isqlplus]$ cd $oracle_home/oc4j/j2ee/oc4j_applications/applications/isqlplus/isqlplus/web-inf/classes

[oracle@db-server classes]$ ls

log4j.properties oracle

[oracle@db-server classes]$ more log4j.properties

# log4j configuration file.

# set root logger level and its only appender to a1.

#log4j.rootlogger=all, a1

#log4j.rootlogger=debug, a1

#log4j.rootlogger=info, a1

#log4j.rootlogger=warn, a1

#log4j.rootlogger=error, a1

log4j.rootlogger=fatal, a1

#log4j.rootlogger=off, a1

# a1 is set to be a consoleappender.

log4j.appender.a1=org.apache.log4j.consoleappender

# a1 uses patternlayout.

log4j.appender.a1.layout=org.apache.log4j.patternlayout

log4j.appender.a1.layout.conversionpattern=%d{iso8601} %-5.5p [%t] %-20.20c{2} - %m%n

# r is a rolling log file appender

log4j.appender.r=org.apache.log4j.rollingfileappender

log4j.appender.r.file=isqlplus.log

log4j.appender.r.maxfilesize=100kb

# keep one backup file

log4j.appender.r.maxbackupindex=1

log4j.appender.r.layout=org.apache.log4j.patternlayout

log4j.appender.r.layout.conversionpattern=%d{iso8601} %-5.5p [%t] %-20.20c{2} - %m%n

[oracle@db-server classes]$

 

在oracle 10g的$oracle_home/oc4j/j2ee/isqlplus/log目录下，你可以看到如下一些日志：

日志文件位于$oracle_home/oc4j/j2ee/isqlplus/application-deployments/isqlplus/application.log

[oracle@db-server web-inf]$ cd $oracle_home/oc4j/j2ee/isqlplus/application-deployments/isqlplus

[oracle@db-server isqlplus]$ ls

application.log config isqlplus orion-application.xml

[oracle@db-server isqlplus]$ more application.log

12/04/22 13:31:07 started

12/04/22 13:31:09 isqlplus: jsp: init

12/04/22 13:31:09 isqlplus: config: init

12/04/22 13:31:09 isqlplus: uix: init

12/04/22 13:31:09 isqlplus: 9.0.4.1.0 started

12/04/22 14:07:13 isqlplus: config: destroy

12/04/22 14:07:13 isqlplus: 9.0.4.1.0 stopped

12/04/22 14:07:13 stopped (jvm termination)

12/09/27 17:23:21 started

12/09/27 17:23:29 isqlplus: jsp: init

12/09/27 17:23:30 isqlplus: config: init

12/09/27 17:23:31 isqlplus: uix: init

12/09/27 17:23:31 isqlplus: 9.0.4.1.0 started

帮助的日志记录在：

$oracle_home/oc4j/j2ee/isqlplus/application-deployments/isqlplushelp/application.log

[oracle@db-server log]$ ls

global-application.log http-web-access.log rmi.log server.log

[oracle@db-server log]$ more rmi.log

12/04/22 13:31:07 9.0.4.1.0 started

12/04/22 14:07:13 9.0.4.1.0 stopped (jvm termination)

12/09/27 17:23:19 9.0.4.1.0 started

12/11/08 17:43:38 9.0.4.1.0 started

12/11/08 17:54:01 9.0.4.1.0 stopped (shutdown executed by jazn.com/admin from 127.0.0.1 (localhost), 2)

12/11/08 17:55:42 9.0.4.1.0 started

[oracle@db-server log]$ more server.log

12/04/22 13:31:07 9.0.4.1.0 started

12/04/22 13:31:07 9.0.4.1.0 started

12/04/22 14:07:13 9.0.4.1.0 stopped (jvm termination)

12/09/27 17:23:19 9.0.4.1.0 started

12/09/27 17:23:21 9.0.4.1.0 started

12/11/08 17:43:37 9.0.4.1.0 started

12/11/08 17:43:38 forced or abrupt (crash etc) server shutdown detected, starting recovery process...

12/11/08 17:43:38 recovery completed, 0 connections committed and 0 rolled back...

12/11/08 17:43:38 9.0.4.1.0 started

12/11/08 17:54:01 9.0.4.1.0 stopped (shutdown executed by jazn.com/admin from 127.0.0.1 (localhost), 2)

12/11/08 17:55:42 9.0.4.1.0 started

12/11/08 17:55:42 9.0.4.1.0 started

[oracle@db-server log]$ more http-web-access.log

172.20.61.185 - - [27/sep/2012:17:24:39 +0800] "get /isqlplus/ http/1.1" 200 9154

172.20.61.185 - - [27/sep/2012:17:24:39 +0800] "get /isqlplus/cabo/styles/cache/blaf-a0-zh_cn-ie-windows.css http/1

.1" 200 13850

172.20.61.185 - - [27/sep/2012:17:24:39 +0800] "get /isqlplus/cabo/images/cache/c-ghss.gif http/1.1" 200 97

172.20.61.185 - - [27/sep/2012:17:24:39 +0800] "get /isqlplus/images/logo.gif http/1.1" 200 7361

172.20.61.185 - - [27/sep/2012:17:24:39 +0800] "get /isqlplus/cabo/images/t.gif http/1.1" 200 85

172.20.61.185 - - [27/sep/2012:17:24:39 +0800] "get /isqlplus/images/help.gif http/1.1" 200 288

172.20.61.185 - - [27/sep/2012:17:24:40 +0800] "get /isqlplus/cabo/jslibs/marlincorea4.js http/1.1" 200 26036

172.20.61.185 - - [27/sep/2012:17:24:40 +0800] "get /isqlplus/cabo/images/cache/c-ghsc.gif http/1.1" 200 72

172.20.61.185 - - [27/sep/2012:17:24:40 +0800] "get /isqlplus/cabo/images/cache/c-ghe.gif http/1.1" 200 85

172.20.61.185 - - [27/sep/2012:17:24:40 +0800] "get /isqlplus/cabo/images/cache/c-ghse.gif http/1.1" 200 219

172.20.61.185 - - [27/sep/2012:17:24:40 +0800] "get /isqlplus/cabo/images/cache/c-ghc.gif http/1.1" 200 133

172.20.61.185 - - [27/sep/2012:17:24:40 +0800] "get /isqlplus/cabo/images/cache/zhs/b-login.gif http/1.1" 200 715

172.20.61.185 - - [27/sep/2012:17:24:40 +0800] "get /isqlplus/cabo/images/cache/c-skir.gif http/1.1" 200 66

172.20.61.185 - - [27/sep/2012:17:24:43 +0800] "get /favicon.ico http/1.1" 404 135

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "post /isqlplus/login.uix http/1.1" 200 11618

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/cabo/images/t.gif http/1.1" 304 0

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/images/logo.gif http/1.1" 304 0

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/cabo/jslibs/marlincorea4.js http/1.1" 304 0

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/images/logout.gif http/1.1" 200 360

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/cabo/styles/cache/blaf-a0-zh_cn-ie-windows.css http/1

.1" 304 0

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/cabo/images/cache/c-ghss.gif http/1.1" 304 0

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/cabo/images/cache/zhs/tb-56-0.gif http/1.1" 200 971

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/images/help.gif http/1.1" 304 0

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/cabo/images/cache/c-ghsc.gif http/1.1" 304 0

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/cabo/images/cache/c-ghc.gif http/1.1" 304 0

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/cabo/images/cache/c-ghse.gif http/1.1" 304 0

172.20.61.185 - - [27/sep/2012:17:24:52 +0800] "get /isqlplus/cabo/i

[oracle@db-server log]$ more global-application.log

12/04/22 13:31:07 started

12/04/22 13:31:08 defaultwebapp: jsp: init

12/04/22 13:31:08 defaultwebapp: 9.0.4.1.0 started

12/04/22 14:07:13 defaultwebapp: 9.0.4.1.0 stopped

12/04/22 14:07:13 stopped (jvm termination)

12/09/27 17:23:20 started

12/09/27 17:23:27 defaultwebapp: jsp: init

12/09/27 17:23:27 defaultwebapp: 9.0.4.1.0 started

12/11/08 17:43:38 started

12/11/08 17:43:39 defaultwebapp: jsp: init

12/11/08 17:43:39 defaultwebapp: 9.0.4.1.0 started

12/11/08 17:54:01 defaultwebapp: 9.0.4.1.0 stopped

12/11/08 17:54:01 stopped (shutdown executed by jazn.com/admin from 127.0.0.1 (localhost), 2)

12/11/08 17:55:42 started

12/11/08 17:55:43 defaultwebapp: jsp: init

12/11/08 17:55:43 defaultwebapp: 9.0.4.1.0 started

配置问题

1：变更session的有效期：

在$oracle_home/oc4j/j2ee/oc4j_applications/applications/isqlplus/isqlplus/web-inf下的web.xml文件中。

设置：

<session-config>

<session-timeout>15</session-timeout>

</session-config>

为分钟状态。

2：限制登录

[oracle@db-server web-inf]$ vi web.xml

<?xml version = '1.0' encoding = 'windows-1252'?>

<!doctype web-app public "-//sun microsystems, inc.//dtd web application 2.3//en" "http://java.sun.com/dtd/web-app_2_3.dtd">

<web-app>

<display-name>isql*plus</display-name>

<description>isql*plus configuration file</description>

<servlet>

<servlet-name>config</servlet-name>

<servlet-class>oracle.sqlplus.iplus.core.config</servlet-class>

<init-param>

<param-name>isqlplusallowusermarkup</param-name>

<param-value>none</param-value>

<description>valid values are: none | all</description>

</init-param>

<init-param>

<param-name>isqlplusconnectidlist</param-name>

<param-value>wgods;bics;orcl</param-value>

<description>the database(s) to which isql*plus users are restricted. the list should contain the oracle sids or service names

, separated by a semicolon (;). if there are no entries, database access is not restricted through isql*plus.</description>

</init-param>

其中的<param-value>代表$oracle_home/network/admin/tnsnames.ora中的名字，如果设置服务名后，只能从下拉框选择。

如果<param-value>没有设置值，那么可以手动输入sid。如下所示：

3：修改isql*plus端口

转到$oracle_home/oc4j/j2ee/isqlplus/config下，编辑http-web-site.xml文件

问题锦集

1：中文环境，按钮字体出现乱码。可以选址语言。切换到英文环境即可看到按钮的字体

解决办法：

[oracle@db-server zhs]$ cd $oracle_home/jdk/jre/lib

[oracle@db-server lib]$ ls

audio font.properties.ja.redhat6.2 font.properties.zh_cn.redhat2.1 images

charsets.jar font.properties.ja.redhat7.2 font.properties.zh_cn.sun jce.jar

cmm font.properties.ja.redhat7.3 font.properties.zh_cn_utf8.sun jsse.jar

content-types.properties font.properties.ja.redhat8.0 font.properties.zh_hk.sun jvm.hprof.txt

ext font.properties.ja.turbo font.properties.zh_hk_utf8.sun jvm.jcov.txt

flavormap.properties font.properties.ja.turbo6.0 font.properties.zh_hk_utf8.sun2003 locale

font.properties font.properties.ko_kr.sun font.properties.zh.turbo logging.properties

font.properties_back font.properties.ko_kr_utf8.sun font.properties.zh_tw.redhat plugin.jar

font.properties.ja font.properties.ko_kr_utf8.sun2003 font.properties.zh_tw.redhat2.1 psfontj2d.properties

font.properties.ja_jp.sun font.properties.ko.redhat font.properties.zh_tw.sun psfont.properties.ja

font.properties.ja_jp.sun2003 font.properties.ko.redhat2.1 font.properties.zh_tw_utf8.sun rt.jar

font.properties.ja_jp_utf8.sun font.properties.redhat6.1 font.properties.zh_tw_utf8.sun2003 security

font.properties.ja_jp_utf8.sun2003 font.properties.redhat8.0 fonts sunrsasign.jar

font.properties.ja.redhat3 font.properties.suse8.0 i386 zi

font.properties.ja.redhat6.1 font.properties.zh_cn.redhat im

[oracle@db-server lib]$

备份font.properties

[oracle@db-server lib]$ cp font.properties font.properties_back

[oracle@db-server lib]$ cp font.properties.zh_cn.redhat font.properties

查看font.properties文件，确认文件中指定的字体文件zysong.ttf在系统内存在。若不存在需要修改此文件或安装这个字体

[oracle@db-server lib]$ tail -10 font.properties

fontset.dialoginput.plain=\

-b&h-luxi mono-medium-r-normal--*-%d-*-*-m-*-iso8859-1,\

-isas-song ti-medium-r-normal--*-%d-*-*-c-*-gb2312.1980-0

fontset.default=\

-b&h-luxi sans-medium-r-normal--*-%d-*-*-p-*-iso8859-1,\

-isas-song ti-medium-r-normal--*-%d-*-*-c-*-gb2312.1980-0

filename.-misc-zysong18030-medium-r-normal--*-%d-*-*-c-*-iso10646-1=/usr/share/fonts/zh_cn/truetype/zysong.ttf

[oracle@db-server lib]$

转到/usr/share/fonts目录下，发现没有zh_cn目录，新建目录zh_cn/truetype，去网上下载字体zysong.ttf放在/usr/share/fonts/zh_cn/truetype目录下。

然后

[oracle@db-server fonts]$ cd $oracle_home/oc4j/j2ee/oc4j_applications/applications/isqlplus/isqlplus/cabo/images/cache/zhs/

[oracle@db-server zhs]$ ls

b-0dac.gif b-54.gif b-cancel.gif b-execute.gif b-login.gif b-save.gif tb-56-0.gif

b-0dac.imx b-54.imx b-cancel.imx b-execute.imx b-login.imx b-save.imx tb-56-0.imx

[oracle@db-server zhs]$ rm *

[oracle@db-server zhs]$ isqlplusctl stop

isql*plus 10.2.0.1.0

copyright (c) 2003, 2005, oracle. all rights reserved.

getnameinfo failed

getnameinfo failed

stopping isql*plus ...

isql*plus stopped.

[oracle@db-server zhs]$ isqlplusctl start

isql*plus 10.2.0.1.0

copyright (c) 2003, 2005, oracle. all rights reserved.

getnameinfo failed

getnameinfo failed

starting isql*plus ...

isql*plus started.

[oracle@db-server zhs]$

2：以dba账号登录时，如果不指定连接标识符，则会报如下错误：ora-12154：tns：could not resolve the connect identifier specified.