<<Oracle Applications DBA 基础（第二期）>>Week 10 exercise

1. 下载并安装 Oracle 10g BI Publisher desktop 10.1.3.4.1.
Try to create a simple XML file by yourself and create a RTF template for it, to test the process of building reports by BI publisher.

1.1 create xml file (MYDEPT.xml):

<dataTemplate name="tmplname" description="tmpl desc" version="1.0">  
<dataQuery>
<sqlStatement name="Q1">
select deptno,dname,loc from scott.dept
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G1" source="Q1">
<element name="deptno" value="deptno"/>
<element name="dname" value="dname"/>
<element name="loc" value="loc"/>
</group>
</dataStructure>
</dataTemplate>

1.2 register MYDEPT Data Definitions in XML Publisher

1.3 create program MYDEPT with XDODTEXE as executable, and add program to request group

1.4 submit MYDEPT program and pull down xml data

1.5 create RTF template using BI/XML Publisher client tool

1.6 create Templates associated with MYDEPT Data Definitions, and upload the RTF template, select PDF as Default Output Type

1.7 re-submit MYDEPT program and we'll see the final output as below.

All sources have been attached as week10.01.zip.

--EOF--

2. Create a simple custom application. you can follow the steps in the powerpoint with the following difference.

In the MYERP user,

connect MYERP/MYERP
create table t1(c1 number);
insert into t1 values(100);
commit;

The SQL to be used in the custom application is :

select * from t1;

(hint: the table t1 is created in the MYERP user, you need to grant the permission to apps user and create synonym in apps user)
2.1 Create directory structure for the custom application
[appldev@oel57dev ~]$ cd $APPL_TOP
[appldev@oel57dev appl]$ mkdir -p myerp/12.0.0/{admin/sql,bin,forms/US,help,html,lib,log,mds,media,mesg,out,patch,reports/US,sql,xml}
[appldev@oel57dev appl]$ find  myerp/
myerp/
myerp/12.0.0
myerp/12.0.0/forms
myerp/12.0.0/forms/US
myerp/12.0.0/patch
myerp/12.0.0/bin
myerp/12.0.0/xml
myerp/12.0.0/help
myerp/12.0.0/admin
myerp/12.0.0/admin/sql
myerp/12.0.0/html
myerp/12.0.0/reports
myerp/12.0.0/reports/US
myerp/12.0.0/log
myerp/12.0.0/lib
myerp/12.0.0/mesg
myerp/12.0.0/out
myerp/12.0.0/media
myerp/12.0.0/sql
myerp/12.0.0/mds
[appldev@oel57dev appl]$
2.2 Include the custom module into the EBS environment
[appldev@oel57dev scripts]$ vim $CONTEXT_FILE
[appldev@oel57dev scripts]$ grep MYERP $CONTEXT_FILE
        /u01/apps/apps_st/appl/myerp/12.0.0
[appldev@oel57dev scripts]$

cd $ADMIN_SCRIPTS_HOME
./adstpall.sh apps/apps
./adautocfg.sh
./adstrtal.shapps/apps

[appldev@oel57dev scripts]$ cd $APPL_TOP
[appldev@oel57dev appl]$ . APPSDEV_oel57dev.env
[appldev@oel57dev appl]$ echo $MYERP_TOP
/u01/apps/apps_st/appl/myerp/12.0.0
[appldev@oel57dev appl]$
2.3 CREATE tablespace
create tablespace MYERP datafile '/u04/db/apps_st/data/MYERP01.dbf' size 10m;
2.4 Create User
create user MYERP identified by MYERP default tablespace MYERP temporary tablespace TEMP quota unlimited on MYERP;
grant connect,resource to MYERP;
2.5 Register the custom Oracle Application
2.6 Register Oracle User
2.7 Add Application to a Data Group
2.8 Create custom request group
2.9 Create custom Menu
2.10 Create new responsibility
2.11 Add responsibility to user
2.12 Develop the application
cd $MYERP_TOP/sql
vim myconcperf.sql
[appldev@oel57dev sql]$ cat myconcperf.sql
select deptno,dname,loc from scott.dept;
2.13 Develop the concurrent executable
select SQL*PLUS as Execution Method
2.14 Develop the concurrent program
2.15 Add the concurrent program "MYERP Conc Perf report" to request groups "MYERP Request Group"
2.16 login as 'sysadmin' user to "MYERP Custom" responsibility to run the conc program“ MYERPConc Perf report”

最终执行效果如图:

If encountered error "ORA-20002: [WF_NO_ROLE]" while adding responsibility to user after application update, please check below link.
http://oracler12facts.blogspot.com/2011/11/ora-20002-wfnorole-namefndrespicxxxicxs.html

--EOF--

3. 请在 你的学习 系统中 作如下 测试练习：

a. 运行 “ACTIVE USER” 的 Conccurrent request ,查看运行结果，以确定一切正常。
b. kill -9 掉 应用层的listener. ( 第四周PPT的第53页 介绍了 应用层的listener ）  
c. 再运行 “ACTIVE USER” 的 Conccurrent request。你会看到什么 错误。

d. 如果不告诉你是 应用层的listener 停掉了，你会怎么去解决？ 你会查看什么 log file ?

再运行Active User时, Concurrent能正常结束, 不过在查看request的output时会报如下错误:

首先检查Report日志
[appldev@oel57dev logs]$ cd $LOG_HOME/
[appldev@oel57dev logs]$ find ora/10.1.2/ -exec ls -lrt {} \; | tail
-rw-r--r-- 1 appldev dba 6955 Apr 12 10:15 ora/10.1.2/reports/cache/o384330Nl1kVdCD.txt
-rw-r--r-- 1 appldev dba 6955 Apr 12 10:29 ora/10.1.2/reports/cache/o384333bnN9CQVf.txt
-rw-r--r-- 1 appldev dba 6955 Apr 12 10:09 ora/10.1.2/reports/cache/o384324CfjLRg8u.txt
-rw-r--r-- 1 appldev dba 6955 Apr 12 10:10 ora/10.1.2/reports/cache/o384325RSOYQXAu.txt
-rw-r--r-- 1 appldev dba 6955 Apr 12 10:13 ora/10.1.2/reports/cache/o384327a7HFloN6.txt
-rw-r--r-- 1 appldev dba 5404 Apr 12 10:18 ora/10.1.2/reports/cache/o384332kyDSo7Ty.txt
-rw-r--r-- 1 appldev dba 6955 Apr 12 10:17 ora/10.1.2/reports/cache/o384331ZycfcauE.txt
total 8
-rw-r--r-- 1 appldev dba 8080 Apr 12 10:07 apps_dev.log
-rw-r--r-- 1 appldev dba 8080 Apr 12 10:07 ora/10.1.2/network/apps_dev.log
[appldev@oel57dev logs]$ tail -n10 ora/10.1.2/network/apps_dev.log

Started with pid=5625
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel57dev.example.com)(PORT=1626)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
12-APR-2014 10:02:20 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=oel57dev.example.com)(USER=appldev))(COMMAND=status)(ARGUMENTS=64)(SERVICE=APPS_DEV)(VERSION=168822016)) * status * 0
12-APR-2014 10:03:21 * ping * 0
12-APR-2014 10:03:22 * (CONNECT_DATA=(SID=FNDSM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.220)(PORT=24269)) * establish * FNDSM * 0
12-APR-2014 10:05:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=oel57dev.example.com)(USER=appldev))(COMMAND=status)(ARGUMENTS=64)(SERVICE=APPS_DEV)(VERSION=168822016)) * status * 0
12-APR-2014 10:07:33 * (CONNECT_DATA=(SID=FNDFS)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.220)(PORT=64768)) * establish * FNDFS * 0
[appldev@oel57dev logs]$

#未发现异常后检查MANAGER日志
[appldev@oel57dev log]$ cd $LOG_HOME/appl/conc/log
[appldev@oel57dev log]$ ls -lrt | tail -5
-rw-r--r-- 1 appldev dba  1192 Apr 12 10:34 w7852.mgr
-rw-r--r-- 1 appldev dba  1871 Apr 12 10:34 l384320.req
-rw-r--r-- 1 appldev dba 10418 Apr 12 10:34 javacache.log
-rw-r--r-- 1 appldev dba  8986 Apr 12 10:43 FNDOPP7845.txt
-rw-r--r-- 1 appldev dba 21469 Apr 12 10:53 DEV_0412.mgr
[appldev@oel57dev log]$ tail -n10 DEV_0412.mgr

                     Process monitor session ended : 12-APR-2014 10:51:30

                   Process monitor session started : 12-APR-2014 10:53:30
CONC-SM TNS FAIL
Call to PingProcess failed for FNDOPP
CONC-SM TNS FAIL
Call to StopProcess failed for FNDOPP

                     Process monitor session ended : 12-APR-2014 10:53:30
[appldev@oel57dev log]$
#从这已经可以看出TNS FAIL了

#再通过检查TNS进程进行确认:
[appldev@oel57dev log]$ ps -ef | grep tns
root        13     2  0 09:39 ?        00:00:00 [netns]
oradev    5167     1  0 10:00 ?        00:00:00 /u03/db/tech_st/11.1.0/bin/tnslsnr DEV -inherit
appldev   9766  5300  0 10:55 pts/2    00:00:00 grep tns
[appldev@oel57dev log]$

#确认问题后重启APP的监听:
[appldev@oel57dev log]$ adalnctl.sh start

adalnctl.sh version 120.3

Checking for FNDFS executable.
Starting listener process APPS_DEV.

adalnctl.sh: exiting with status 0


adalnctl.sh: check the logfile /u02/inst/apps/DEV_oel57dev/logs/appl/admin/log/adalnctl.txt for more information ...  

[appldev@oel57dev log]$ ps -ef | grep tns
root        13     2  0 09:39 ?        00:00:00 [netns]
oradev    5167     1  0 10:00 ?        00:00:00 /u03/db/tech_st/11.1.0/bin/tnslsnr DEV -inherit
appldev   9798     1  0 10:56 ?        00:00:00 /u01/apps/tech_st/10.1.2/bin/tnslsnr APPS_DEV -inherit
appldev   9804  5300  0 10:56 pts/2    00:00:00 grep tns
[appldev@oel57dev log]$

#然后再去打开output时就可以正常查看了
#中止APP端监听会导致查看output报错是因为查看output需要调用FNDFS - Report Review Agent(RRA) ,而FNDFS服务又注册在应用层的监听APPS_DEV里面.
[appldev@oel57dev log]$ adalnctl.sh status

adalnctl.sh version 120.3

Checking status for listener process APPS_DEV.

LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 12-APR-2014 11:01:51

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=oel57dev)(Port=1626))
STATUS of the LISTENER
------------------------
Alias                     APPS_DEV
Version                   TNSLSNR for Linux: Version 10.1.0.5.0 - Production
Start Date                12-APR-2014 10:56:19
Uptime                    0 days 0 hr. 5 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/inst/apps/DEV_oel57dev/ora/10.1.2/network/admin/listener.ora
Listener Log File         /u02/inst/apps/DEV_oel57dev/logs/ora/10.1.2/network/apps_dev.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel57dev.example.com)(PORT=1626)))
Services Summary...
Service "FNDFS" has 1 instance(s).
  Instance "FNDFS", status UNKNOWN, has 1 handler(s) for this service...
Service "FNDSM" has 1 instance(s).
  Instance "FNDSM", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

adalnctl.sh: exiting with status 0


adalnctl.sh: check the logfile /u02/inst/apps/DEV_oel57dev/logs/appl/admin/log/adalnctl.txt for more information ...  

[appldev@oel57dev log]$


--EOF--

 

附件:

week10.01.zip