11gOAS系统管理员手册.doc
工程管理系统系统管理员手册中国长江三峡工程开发总公司三峡高科信息技术有限责任公司二一三年十月文档控制文档编号：TGPMS-KMJC-02-007日期作者版本备注2008-6顾伟1.02008-6谢玄1.02013-12罗宵1.111g数据库配10gAS目 录1概述12数据库的安装与设置12.1 Oracle 11g 数据库的安装12.2 进入数据库管理器72.3 创建表空间92.4 创建角色122.5 创建用户132.6数据的导入173应用服务器的安装与设置183.1 Oracle 10g AS的安装183.2 Oracle 10g AS的设置243.2.1 Form的设置243.2.2 Report的设置273.2.3 Webutil的设置324 WEB查询系统的安装365管理与维护425.1数据库管理435.2应用服务器管理445.3 Excel导出和导入455.3.1 Excel导出453.3.2 Excel导入47工程管理系统管理员手册1概述在WIN7平台（如果你的机器是固定IP地址可以直接无视）安装数据库之前需要配置网络。在Windows 7 中手动安装Microsoft 环回适配器，请按照下列步骤操作： 1.右击“计算机”，中下拉菜单中单击“管理”。 2.在管理视图中，请单击左窗格中的“设备管理器”。 3.点击上面的“操作”菜单，然后在下拉菜单单击“添加过时硬件”。 4.在“添加硬件”菜单下方，单击“下一步”。 5.单击“安装我手动从列表选择的硬件”，然后单击“下一步”。 6.单击“网络适配器”，然后单击“下一步”。 7.在“制造商”框中，单击“Microsoft”。 8.在“网络适配器”框中，单击“Microsoft LoopBack Adapter（Microsoft 环回适配器）”，然后单击“下一步”。 9.单击“完成”。成功安装该适配器后，与其他任何适配器一样，您可以手动配置其选项。这时候就会看到网络连接里多了一个“本地连接2”IP地址随便设置吧，10.10.10.10 ，子网掩码255.255.255.0，网关留空。修改C:WINDOWSsystem32driversetc 文件夹中的hosts文件，在127.0.0.1 localhost之后添加10.10.10.10 yourcomputer，其中“yourcomputer”为主机名。本文档的内容分为两个部分：1、OAS平台的安装与设置；2、OAS的维护。2数据库的安装与设置2.1 Oracle 11g 数据库的安装OAS使用Oracle 11g数据库。下面先描述数据库的安装。1）打开Oracle 11g数据库安装程序文件夹，双击其中的setup.exe（如图2.1）：图2.1 2）等待安装程序进行先决条件的检查（如图2.2）：图2.23）先决条件检查通过后，出现如下界面（如图2.3）：图2.34）在上图所示界面中选择“基本安装”，选择安装类型：“企业版”或者“标准版”，将“创建启动数据库”旁的勾选上，在“全局数据库名”中为安装的数据库定义一个名字，输入数据库的口令（如图2.4）：图2.4 5）点击“下一步”，开始产品特点的先决条件检查（如图2.5）：图2.5 6）在上图界面中点击“下一步”，等待安装程序进行产品特定的先决条件检查，完成检查后点击“下一步”7）点击“安装”，开始进行数据库程序的安装（如图2.7）：图2.78）程序文件安装完成后，将自动进行数据库的创建和设置（如图2.8）：图2.89）数据库创建结束后，出现如下界面（如图2.9）：图2.910）点击“口令管理”，修改sys和system两个数据库管理用户的口令（如图2.10）：图2.1011）完成口令修改后，点击“确定”，回到如下界面（如图2.11）：图2.1112）点击“确定”，出现如下界面（如图2.12）：图2.12点击“退出”，结束Oracle 11g数据库的安装。注意：如果无法连接数据库，请测试监听，如果监听测试正常，请在运行输入“CMD”然后path 查看环境变量OCI库指向是否有： 数据库安装目录product11.1.0db_1BINoci.dll，如果没有请添加。2.2 进入数据库管理器1）在IE地址栏输入Database Control URL 即http:/tght:1158/em，进入数据库管理器（如图2.13）：图2.132）输入用户名：sys，连接身份选择 SYSDBA（如图2.14）：图2.143）点击管理菜单（如图2.15）：图2.154）进入管理界面（如图2.16）：图2.162.3 创建表空间1）点击表空间菜单，进入表空间管理界面（如图2.17）：图2.172）点击创建菜单，创建新表空间。输入表空间名称：TGPMS（如图2.18）：图2.183）点击添加菜单，添加数据文件（如图2.19）：图2.194）输入数据文件名称：TGPMS，表空间文件大小输入1024MB，数据文件自动扩展选择20MB。输入完成后，点击继续（如图2.20）：图2.205）点击确定完成表空间建立（如图2.21）：图2.216）按以上方法，创建表空间TGPMS_IDX（如图2.21）：SQL语言方式创建表空间：Create tablespace TGPMS datafile 'D:appRorschachoradataorclTGPMS.dbf' size 1024M autoextend on next 20M;Create temporary tablespace TGPMS_TEMP tempfile 'D:appRorschachoradataorclTGPMS_TEMP.dbf' size 1024M autoextend on next 20M;2.4 创建角色1）点击角色菜单，进入角色管理界面（如图2.23）：图2.232）点击创建菜单，创建新角色（如图2.24）：图2.243）输入角色名称：TGPMS_ROLE，点击确定，完成角色创建（如图2.25）：图2.254）按以上方法，创建角色：WEB_QUERY。SQL语言方式创建角色：create role TGPMS_ROLE;create role FINANCIAL_ADMIN; create role APPL_USER;create role APPL_ADMIN;create role QUERY_USER;create role WEB_QUERY;2.5 创建用户1）点击用户菜单，进入用户管理界面（如图2.26）：图2.262）点击创建菜单，创建新用户（如图2.27）：图2.273）输入用户名称：TGPMS。默认表空间选择：TGPMS。临时表空间选择：TGPMS_TEMP（如图2.28）：图2.284）点击角色菜单，进入角色修改界面，选择以下角色（如图2.29）：图2.295）点击系统权限菜单，进入系统权限修改界面，选择以下系统权限（如图2.30）：图2.306）点击应用，完成此用户建立（如图2.31）：图2.317）按以上方法建立用户：WEBQUERYUSER，角色选择：WEB_QUERY，无系统权限。SQL语言方式创建用户：create user tgpms identified by 123456 Profile default default tablespace tgpms temporary tablespace tgpms_temp Account unlock;create user investapp identified by investapp Profile default default tablespace tgpms temporary tablespace tgpms_temp Account unlock;create user workflow identified by workflow Profile default default tablespace tgpms temporary tablespace tgpms_temp Account unlock;create user NC identified by NC Profile default default tablespace tgpms temporary tablespace tgpms_temp Account unlock;create user webqueryuser identified by webqueryuser Profile default default tablespace tgpms temporary tablespace tgpms_temp Account unlock;SQL语言将角色赋予用户：Grant CONNECT, DBA,EXP_FULL_DATABASE,EXECUTE_CATALOG_ROLE,IMP_FULL_DATABASE,TGPMS_ROLE,RESOURCE,SELECT_CATALOG_ROLE to TGPMS; SQL语言将系统权限赋予用户：GRANT ADMINISTER ANY SQL TUNING SET TO TGPMS;GRANT ADMINISTER DATABASE TRIGGER TO TGPMS;GRANT ADMINISTER SQL MANAGEMENT OBJECT TO TGPMS;GRANT ADMINISTER SQL TUNING SET TO TGPMS;GRANT ALTER ANY ASSEMBLY TO TGPMS;GRANT ALTER ANY CLUSTER TO TGPMS;GRANT ALTER ANY CUBE TO TGPMS;GRANT ALTER ANY CUBE DIMENSION TO TGPMS;GRANT ALTER ANY DIMENSION TO TGPMS;GRANT ALTER ANY EDITION TO TGPMS;GRANT ALTER ANY EVALUATION CONTEXT TO TGPMS;GRANT ALTER ANY INDEX TO TGPMS;GRANT ALTER ANY INDEXTYPE TO TGPMS;GRANT ALTER ANY LIBRARY TO TGPMS;GRANT ALTER ANY MATERIALIZED VIEW TO TGPMS;GRANT ALTER ANY MINING MODEL TO TGPMS;GRANT ALTER ANY OPERATOR TO TGPMS;GRANT ALTER ANY OUTLINE TO TGPMS;GRANT ALTER ANY PROCEDURE TO TGPMS;GRANT ALTER ANY ROLE TO TGPMS;GRANT ALTER ANY RULE TO TGPMS;GRANT ALTER ANY RULE SET TO TGPMS;GRANT ALTER ANY SEQUENCE TO TGPMS;GRANT ALTER ANY SQL PROFILE TO TGPMS;GRANT ALTER ANY TABLE TO TGPMS;GRANT ALTER ANY TRIGGER TO TGPMS;GRANT ALTER ANY TYPE TO TGPMS;GRANT ALTER DATABASE TO TGPMS;GRANT ALTER PROFILE TO TGPMS;GRANT ALTER RESOURCE COST TO TGPMS;GRANT ALTER ROLLBACK SEGMENT TO TGPMS;GRANT ALTER SESSION TO TGPMS;GRANT ALTER SYSTEM TO TGPMS;GRANT ALTER TABLESPACE TO TGPMS;GRANT ALTER USER TO TGPMS;GRANT ANALYZE ANY TO TGPMS;GRANT ANALYZE ANY DICTIONARY TO TGPMS;GRANT AUDIT ANY TO TGPMS;GRANT AUDIT SYSTEM TO TGPMS;GRANT BACKUP ANY TABLE TO TGPMS;GRANT BECOME USER TO TGPMS;GRANT CHANGE NOTIFICATION TO TGPMS;GRANT COMMENT ANY MINING MODEL TO TGPMS;GRANT COMMENT ANY TABLE TO TGPMS;GRANT CREATE ANY ASSEMBLY TO TGPMS;GRANT CREATE ANY CLUSTER TO TGPMS;GRANT CREATE ANY CONTEXT TO TGPMS;GRANT CREATE ANY CUBE TO TGPMS;GRANT CREATE ANY CUBE BUILD PROCESS TO TGPMS;GRANT CREATE ANY CUBE DIMENSION TO TGPMS;GRANT CREATE ANY DIMENSION TO TGPMS;GRANT CREATE ANY DIRECTORY TO TGPMS;GRANT CREATE ANY EDITION TO TGPMS;GRANT CREATE ANY EVALUATION CONTEXT TO TGPMS;GRANT CREATE ANY INDEX TO TGPMS;GRANT CREATE ANY INDEXTYPE TO TGPMS;GRANT CREATE ANY JOB TO TGPMS;GRANT CREATE ANY LIBRARY TO TGPMS;GRANT CREATE ANY MATERIALIZED VIEW TO TGPMS;GRANT CREATE ANY MEASURE FOLDER TO TGPMS;GRANT CREATE ANY MINING MODEL TO TGPMS;GRANT CREATE ANY OPERATOR TO TGPMS;GRANT CREATE ANY OUTLINE TO TGPMS;GRANT CREATE ANY PROCEDURE TO TGPMS;GRANT CREATE ANY RULE TO TGPMS;GRANT CREATE ANY RULE SET TO TGPMS;GRANT CREATE ANY SEQUENCE TO TGPMS;GRANT CREATE ANY SQL PROFILE TO TGPMS;GRANT CREATE ANY SYNONYM TO TGPMS;GRANT CREATE ANY TABLE TO TGPMS;GRANT CREATE ANY TRIGGER TO TGPMS;GRANT CREATE ANY TYPE TO TGPMS;GRANT CREATE ANY VIEW TO TGPMS;GRANT CREATE ASSEMBLY TO TGPMS;GRANT CREATE CLUSTER TO TGPMS;GRANT CREATE CUBE TO TGPMS;GRANT CREATE CUBE BUILD PROCESS TO TGPMS;GRANT CREATE CUBE DIMENSION TO TGPMS;GRANT CREATE DATABASE LINK TO TGPMS;GRANT CREATE DIMENSION TO TGPMS;GRANT CREATE EVALUATION CONTEXT TO TGPMS;GRANT CREATE EXTERNAL JOB TO TGPMS;GRANT CREATE INDEXTYPE TO TGPMS;GRANT CREATE JOB TO TGPMS;GRANT CREATE LIBRARY TO TGPMS;GRANT CREATE MATERIALIZED VIEW TO TGPMS;GRANT CREATE MEASURE FOLDER TO TGPMS;GRANT CREATE MINING MODEL TO TGPMS;GRANT CREATE OPERATOR TO TGPMS;GRANT CREATE PROCEDURE TO TGPMS;GRANT CREATE PROFILE TO TGPMS;GRANT CREATE PUBLIC DATABASE LINK TO TGPMS;GRANT CREATE PUBLIC SYNONYM TO TGPMS;GRANT CREATE ROLE TO TGPMS;GRANT CREATE ROLLBACK SEGMENT TO TGPMS;GRANT CREATE RULE TO TGPMS;GRANT CREATE RULE SET TO TGPMS;GRANT CREATE SEQUENCE TO TGPMS;GRANT CREATE SESSION TO TGPMS;GRANT CREATE SYNONYM TO TGPMS;GRANT CREATE TABLE TO TGPMS;GRANT CREATE TABLESPACE TO TGPMS;GRANT CREATE TRIGGER TO TGPMS;GRANT CREATE TYPE TO TGPMS;GRANT CREATE USER TO TGPMS;GRANT CREATE VIEW TO TGPMS;GRANT DEBUG ANY PROCEDURE TO TGPMS;GRANT DEBUG CONNECT SESSION TO TGPMS;GRANT DELETE ANY CUBE DIMENSION TO TGPMS;GRANT DELETE ANY MEASURE FOLDER TO TGPMS;GRANT DELETE ANY TABLE TO TGPMS;GRANT DROP ANY ASSEMBLY TO TGPMS;GRANT DROP ANY CLUSTER TO TGPMS;GRANT DROP ANY CONTEXT TO TGPMS;GRANT DROP ANY CUBE TO TGPMS;GRANT DROP ANY CUBE BUILD PROCESS TO TGPMS;GRANT DROP ANY CUBE DIMENSION TO TGPMS;GRANT DROP ANY DIMENSION TO TGPMS;GRANT DROP ANY DIRECTORY TO TGPMS;GRANT DROP ANY EDITION TO TGPMS;GRANT DROP ANY EVALUATION CONTEXT TO TGPMS;GRANT DROP ANY INDEX TO TGPMS;GRANT DROP ANY INDEXTYPE TO TGPMS;GRANT DROP ANY LIBRARY TO TGPMS;GRANT DROP ANY MATERIALIZED VIEW TO TGPMS;GRANT DROP ANY MEASURE FOLDER TO TGPMS;GRANT DROP ANY MINING MODEL TO TGPMS;GRANT DROP ANY OPERATOR TO TGPMS;GRANT DROP ANY OUTLINE TO TGPMS;GRANT DROP ANY PROCEDURE TO TGPMS;GRANT DROP ANY ROLE TO TGPMS;GRANT DROP ANY RULE TO TGPMS;GRANT DROP ANY RULE SET TO TGPMS;GRANT DROP ANY SEQUENCE TO TGPMS;GRANT DROP ANY SQL PROFILE TO TGPMS;GRANT DROP ANY SYNONYM TO TGPMS;GRANT DROP ANY TABLE TO TGPMS;GRANT DROP ANY TRIGGER TO TGPMS;GRANT DROP ANY TYPE TO TGPMS;GRANT DROP ANY VIEW TO TGPMS;GRANT DROP PROFILE TO TGPMS;GRANT DROP PUBLIC DATABASE LINK TO TGPMS;GRANT DROP PUBLIC SYNONYM TO TGPMS;GRANT DROP ROLLBACK SEGMENT TO TGPMS;GRANT DROP TABLESPACE TO TGPMS;GRANT DROP USER TO TGPMS;GRANT EXECUTE ANY ASSEMBLY TO TGPMS;GRANT EXECUTE ANY CLASS TO TGPMS;GRANT EXECUTE ANY EVALUATION CONTEXT TO TGPMS;GRANT EXECUTE ANY INDEXTYPE TO TGPMS;GRANT EXECUTE ANY LIBRARY TO TGPMS;GRANT EXECUTE ANY OPERATOR TO TGPMS;GRANT EXECUTE ANY PROCEDURE TO TGPMS;GRANT EXECUTE ANY PROGRAM TO TGPMS;GRANT EXECUTE ANY RULE TO TGPMS;GRANT EXECUTE ANY RULE SET TO TGPMS;GRANT EXECUTE ANY TYPE TO TGPMS;GRANT EXECUTE ASSEMBLY TO TGPMS;GRANT EXPORT FULL DATABASE TO TGPMS;GRANT FLASHBACK ANY TABLE TO TGPMS;GRANT FLASHBACK ARCHIVE ADMINISTER TO TGPMS;GRANT FORCE ANY TRANSACTION TO TGPMS;GRANT FORCE TRANSACTION TO TGPMS;GRANT GLOBAL QUERY REWRITE TO TGPMS;GRANT GRANT ANY OBJECT PRIVILEGE TO TGPMS;GRANT GRANT ANY PRIVILEGE TO TGPMS;GRANT GRANT ANY ROLE TO TGPMS;GRANT IMPORT FULL DATABASE TO TGPMS;GRANT INSERT ANY CUBE DIMENSION TO TGPMS;GRANT INSERT ANY MEASURE FOLDER TO TGPMS;GRANT INSERT ANY TABLE TO TGPMS;GRANT LOCK ANY TABLE TO TGPMS;GRANT MANAGE ANY FILE GROUP TO TGPMS;GRANT MANAGE FILE GROUP TO TGPMS;GRANT MANAGE SCHEDULER TO TGPMS;GRANT MANAGE TABLESPACE TO TGPMS;GRANT MERGE ANY VIEW TO TGPMS;GRANT ON COMMIT REFRESH TO TGPMS;GRANT QUERY REWRITE TO TGPMS;GRANT READ ANY FILE GROUP TO TGPMS;GRANT RESTRICTED SESSION TO TGPMS;GRANT RESUMABLE TO TGPMS;GRANT SELECT ANY CUBE TO TGPMS;GRANT SELECT ANY CUBE DIMENSION TO TGPMS;GRANT SELECT ANY DICTIONARY TO TGPMS;GRANT SELECT ANY MINING MODEL TO TGPMS;GRANT SELECT ANY SEQUENCE TO TGPMS;GRANT SELECT ANY TABLE TO TGPMS;GRANT SELECT ANY TRANSACTION TO TGPMS;GRANT UNDER ANY TABLE TO TGPMS;GRANT UNDER ANY TYPE TO TGPMS;GRANT UNDER ANY VIEW TO TGPMS;GRANT UPDATE ANY CUBE TO TGPMS;GRANT UPDATE ANY CUBE BUILD PROCESS TO TGPMS;GRANT UPDATE ANY CUBE DIMENSION TO TGPMS;GRANT UPDATE ANY TABLE TO TGPMS;：2.6数据的导入1）运行CMD，进入命令窗口（如图2.32）：图2.322）输入导入命令：imp tgpms/tgpmsgw fromuser=tgpms touser=tgpms file=kmjc.dmp log=kmjc.log（如图2.33）：图2.333）导入完成（如图2.34）：图2.34注意：导入数据完成后要记得“编译无效数据”3 OAS的安装与设置3.1 Oracle 10g AS的安装1）打开Oracle 10g AS安装程序，运行其中的setup.exe（如图3.1）：图3.12）出现如下界面（如图3.2）：图3.23）稍等后，出现如下界面（如图3.3）：图3.34）点击“下一步”，出现如下界面。在“目标”中，可以修改默认的安装产品的名称以及安装产品的完整路径（如图3.4）：图3.45）设置完安装产品名称和路径后，点击“下一步”，出现如下界面（如图3.5）：图3.56）点击“下一步”，出现如下界面（如图3.6）：图3.67）点击“下一步”，出现如下界面（如图3.7）：图3.78）点击“下一步”，出现如下界面。输入实例名称、口令（如图3.8）：图3.89）点击“下一步”，出现如下界面（如图3.9）：图3.910）点击“安装”，出现如下界面（如图3.10）：图3.1011）成功完成安装后，出现如下界面（如图3.11）：图3.1112）点击“退出”，结束Oracle 10g AS的安装。13）接下来，我们来验证Oracle 10g AS的安装是否正确。打开浏览器程序，输入地址http:/机器名:18100,输入用户名ias_admin和密码。出现以下画面确认应用服务器安装已正确完成（如图3.12）：图3.12监听配置：复制数据库的tnsnames.ora文件里面的监听内容copy到AS的tnsnames.ora文件里面。3.2 Oracle 10g AS的设置创建应用程序文件夹，拷贝相应的文件。创建以下文件夹：E:tgpmsappfmx；E:tgpmsappplx；E:tgpmsapphelp；E:tgpmsapprdf；E:tgpmsappreport；E:tgpmsappupload。3.2.1 Form的设置1）打开$ORACLE_HOMEformsserverformsweb.cfg文件，增加以下部分：tgpmspageTitle=TGPMS align=leftborder=0width=120%height=130%userid=tgpmsseparateFrame=Truelookandfeel=Oraclelogo=tgpms_logo.gifworkingDirectory=d:tgpmsappfmx (此处为fmx文件的路径)splashScreen=nobackground=nocolorScheme=tealenvFile=tgpms.envform=M0001.fmxarchive_jini=frmall_jinit.jar,icons.jar#,/forms90/webutil/webtuil.jar,/forms90/webutil/jacob.jar,/forms90/webutil/lib/uploadclient.jarimageBase=codebaseheartbeat=2networkRetries=30prestartRuntimes=trueprestartInit=5prestartMin=3prestartIncrement=2prestartTimeout=1800WebUtilArchive=frmwebutil.jar,jacob.jar,image.jarWebUtilLogging=offWebUtilLoggingDetail=normalWebUtilErrorMode=AlertWebUtilDispatchMonitorInterval=5WebUtilTrustInternal=trueWebUtilMaxTransferSize=16384baseHTML=base.htmbaseHTMLjinitiator=basejini.htmbaseHTMLjpi=basejpi.htm 2）复制$ORACLE_HOMEformsserverdefault.env文件，另存为$ORACLE_HOMEformsservertgpms.env,修改FORMS_PATH键值为form文件、plx文件的绝对路径，例如：FORMS_PATH=E:tgpmsappfmx;E:tgpmsappplx;D:orac10gasforms把FORMS_RESTRICT_ENTER_QUERY=TRUE改成FORMS_RESTRICT_ENTER_QUERY=FALSE。3）复制icons.jar、image.jar文件至$ORACLE_HOME formsjava目录下。4）复制fmrwebzhs.res、fmrweb.res文件至 $ORACLE_HOME forms目录下。(覆盖)5)还要把tns.ora文件也复制到NETWORK目录下面；FBLIB.plx文件要放在TGPMSAPP下的plx目录下。3.2.2 Report的设置1）打开$oracle_homereportsconf存在report_server.conf文件（report_server是报表服务名，默认情况是 rep+服务器名称）。添加参数jvmOptions=" -Xms512m -Xmx512m -Xoss2048k"（Xms：初始值大小；Xmx：最大值，默认值256m；Xoss：java堆栈大小），指定报表路径，临时目录，示例如下：<engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1" maxEngine="1" minEngine="0" engLife="50" maxIdle="30" callbackTimeOut="60000" jvmOptions="-Xms512m -Xmx512m -Xoss2048k "><property name="sourceDir" value="E:tgpmsapprdf"/></engine>注意：property name="sourceDir"中sourceDir固定不变，后面的value根据实际情况修改。2）打开$oracle_homereportsconfrwservlet.properties文件。修改如下信息：将“#SINGLESIGNON=YES”前的“#”删除，将“YES”修改为“NO”。然后把STSongStd-Light-Acro.otf 和report_logo.gif两个文件放到“E:tgpmsapprdf”目录下； STSongStd-Light-Acro.otf是生成PDF时使用的字体文件， report_logo.gif是生成报表时使用的LOGO；修改$oracle_hometoolscommonuifont.ali文件设置字体，在PDF节中增加：.ZHS16GBK = "STSongStd-Light-Acro"3）报表参数设置打开浏览器程序，输入地址http:/机器名:18100,输入用户名ias_admin和密码，出现以下画面（如图3.13）：图3.13点击“Repor
