欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-08-16 12:38:03
isql*plus介绍 isql*plus是从oracle 9i开始提供的新功能,是sql*plus的web形式(oracle 11g已经不支持)。是基于三层结构设计的。...

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

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

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。如下所示:

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

3:修改isql*plus端口

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

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

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

问题锦集

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

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

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

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

解决办法

[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]$

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

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

 

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

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