正德厚生,臻于至善

Oracle EBS R12注册客户化模块-以FTBP为例

xuwanxin1987阅读(49)

Creating a CustomApplication in Oracle E-Business Suite Release 12.2 (Doc ID 1577707.1)
Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)

su - oravis
sqlplus /nolog
conn / as sysdba
select file_name from dba_data_files where tablespace_name='SYSTEM';
/u01/VIS121/db/apps_st/data/system01.dbf
/u01/VIS121/db/apps_st/data/system02.dbf

create tablespace FTBP_TS_TX_DATA datafile '/u01/VIS121/db/apps_st/data/ftbp_ts_tx_data01.dbf' size 10m autoextend on maxsize 8192m;
alter tablespace FTBP_TS_TX_DATA add datafile '/u01/VIS121/db/apps_st/data/ftbp_ts_tx_data02.dbf' size 10m autoextend on maxsize 8192m;

create tablespace FTBP_TS_TX_IDX datafile '/u01/VIS121/db/apps_st/data/ftbp_ts_tx_idx01.dbf' size 10m autoextend on maxsize 8192m;
alter tablespace FTBP_TS_TX_IDX add datafile '/u01/VIS121/db/apps_st/data/ftbp_ts_tx_idx02.dbf' size 10m autoextend on maxsize 8192m;

set lines 200 pages 50000
col file_name for a60
col TABLESPACE_NAME for a30
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_data_files order by 1;
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_temp_files order by 1;

create user FTBP identified by Ftbp2019_Bk default tablespace FTBP_TS_TX_DATA;
alter user FTBP quota unlimited on FTBP_TS_TX_DATA;
alter user FTBP quota unlimited on FTBP_TS_TX_IDX;
GRANT CONNECT TO FTBP;
GRANT RESOURCE TO FTBP;
alter user FTBP default tablespace FTBP_TS_TX_DATA;

select segment_name from dba_segments where tablespace_name like 'FTBP%';
no rows selected

1、下载补丁3636980,拷贝3636980\izu\admin目录下3个.txt文件到/tmp目录下进行修改
如:izuprod.txt改名为ftbpprod.txt
izuterr.txt改名为ftbpterr.txt

2、修改newprods.txt内容
product=izu
base_product_top=*APPL_TOP*
oracle_schema=izu
sizing_factor=100
main_tspace=USER_DATA
index_tspace=USER_IDX
temp_tspace=TEMP
default_tspace=USER_DATA

以上为newprods.txt内容,修改为所要注册模块对应值,如:
product=ftbp
base_product_top=*APPL_TOP*
oracle_schema=ftbp
sizing_factor=100
main_tspace=FTBP_TS_TX_DATA
index_tspace=FTBP_TS_TX_IDX
temp_tspace=TEMP
default_tspace=FTBP_TS_TX_DATA

注意:文件中izu修改为ftbp,IZU修改为FTBP

3、修改ftbpprod.txt文件
替换文件中izu为ftbp,替换prodid 278为ftbp的唯一number
注意:文件中izu修改为ftbp,IZU修改为FTBP

如下SQL,可检查想要设置的prodid是否被占用
select decode(count,0, 'Selected number is Available', 'Selected number already in use') Status,&&enter_custom_applID selected_number
from
(
select count(*) as count from
(
select 'x' from fnd_oracle_userid
where oracle_id= &&enter_custom_applID
union
select 'x' from fnd_application
where application_id=&&enter_custom_applID
)
);
运行上述SQL,输入想要设置的prodid

4、修改ftbpterr.txt文件
替换文件中izu为ftbp,修改Oracle_Support_Diagnostic_Tools为ftbp_custom_app
注意:文件中izu修改为ftbp,IZU修改为FTBP

5、拷贝上述三个.txt文件到$APPL_TOP/admin下

6、运行adsplice命令
adsplice必须在$APPL_TOP/admin下运行
运行adsplice
当遇到如下提示时,默认回车
The defaultdirectory is [/u01/VIS121/apps/apps_st/appl/admin] :
Please enter thename of your AD Splicer control file [newprods.txt] :
Do you wish toregenerate your environment file [Yes] ?

7、确认客户化模块是否在数据库中创建
查看fnd_application中是否有FTBP记录
select * fromfnd_application where application_short_name = 'FTBP';

通过application_id查看fnd_product_installations中是否有记录
select * fromfnd_product_installations where APPLICATION_ID = 5001;
查看数据库用户是否创建成功
select * fromdba_users where username = 'FTBP';

[applvis@ebs admin]$ sqlplus ftbp/ftbp

重新应用环境变量,确认$FTBP_TOP
[applvis@ebs ~]$echo $FTBP_TOP
/u01/VIS121/apps/apps_st/appl/ftbp/12.0.0

[applvis@ebs ~]$ ls$FTBP_TOP
admin log mesg out sql

注意:如果之前使用fnd_oracle_user_pkg.lod_row API注册过模块,因为这些注册的密码将会被加密,注册后运行adpatch、adsplice将出错。

需要使用FNDCPASS来修改密码

如果应用使用shared APPL_TOP那么,只需要在剩余节点运行autoconfig
如果应用使用的是非shared APPL_TOP那么,需要在剩余节点重复上述5、6、7三步操作

以下步骤依据需求操作
1、创建客户化对象
在$FTBP_TOP中,

a)可以为form创建$FTBP_TOP/forms/US和$FTBP_TOP/forms/ZHS目录
b)可以为package创建$FTBP_TOP/admin/sql目录
c)可以为report创建$FTBP_TOP/reports/US和$FTBP_TOP/reports/ZHS
.
.
.

2、将所有客户化data object权限授予APPS用户
SQL> grant all privileges on myTable to apps;

APPS用户下创建相关同义词
SQL>create synonym myTable for FTBP.myTable;>

问题:
1、当下一次使用adop打补丁时,在prepare阶段,将会同步adsplice的操作到另一套FS
如果在同步过程失败,日志$APPL_TOP/admin/$TWO_TASK/log/adsplicelog出现
UPDATEFND_ORACLE_USERID SET READ_ONLY_FLAG = 'A' WHERE ORACLE_ID

解决方法:应用Patch18815526:R12.AD.C,重启prepare phase

The following arecurrently known issues:

 1、If you are anupgrade customer with a custom product top in the context file but have not runadsplice, you must remove the relevant entry from the FND_OAM_CONTEXT_CUSTOMtable and then run AutoConfig on all nodes.

2、The same approach needs to be taken ifyou have any custom context variables with more than eight characters, as acustom schema abbreviation cannot exceed eight charcters.

 After performing either of the above steps,you should run adsplice to add/register the custom product (after applying thelatest AD-TXK RUP).
注册应用
	进入“应用登记”界面,建立客户化应用。
	职责    :系统管理员
	菜单路径:\主菜单\应用产品\注册。
							
应用系统中注册Oracle数据库帐户
	进入“数据库帐户登记”,在应用系统中注册Oracle帐户
	职责    :系统管理员
	菜单路径:\主菜单\安全性\ORACLE\注册。

添加数据组至标准数据组
	职责    :系统管理员
	菜单路径:\主菜单\安全性\ORACLE\数据组。
	查找标准数据组,添加客户化应用至标准数据组

select * from fnd_application where application_short_name = 'FTBP';
select * from fnd_product_installations where APPLICATION_ID = 30000;
select * from dba_users where username = 'FTBP';

EBS Technology Patch Automation Tool for Application Tier (ETPAT-AT)

xuwanxin1987阅读(57)

EBS Technology Patch Automation Tool for Application Tier (ETPAT-AT) (Doc ID 2749774.1)

以220719 EBS Technology Patch为例

1.创建ETPAT-AT目录并将相关patch拷贝至该目录
mkdir -p <NE_BASE>/EBSapps/patch/etpat-at
p17537119_R12_GENERIC.zip        ###最新ETCC补丁
p32208510_R12_GENERIC.zip        ###ETPAT-AT脚本
p34105303_R12_LINUX.zip          ###EBS RELEASE 12.2 CONSOLIDATED FMW FIXES FOR JUL 2022
p32898996_R12_GENERIC.zip        ###WLS Smart Update V4
p33845432_R12_GENERIC.zip        ###ETCC: REHOST 33800106 BSU.SH EHANCEMENT V.5
p34105280_1036_Generic.zip       ###WLS Patch Set Update 10.3.6.0.220719
p6880880_101000_LINUX.zip        ###10.1.2 OPatch
p6880880_111000_Linux-x86-64.zip ###11.1 OPatch

2.运行ETPAT-AT(建议VNC操作)

cd <NE_BASE>/EBSapps/patch/etpat-at
echo $CONTEXT_FILE
unzip -o p32208510_R12_GENERIC.zip
perl etpat_at.pl

Oracle EBS 忘记apps密码(不建议生产使用)

xuwanxin1987阅读(255)

1、创建程序包
CREATE OR REPLACE PACKAGE APPS.cux_fnd_web_sec AUTHID CURRENT_USER
AS
   FUNCTION encrypt (
      KEY   IN VARCHAR2,
      VALUE IN VARCHAR2
   )
      RETURN VARCHAR2;

    FUNCTION decrypt (
      KEY IN VARCHAR2,
      VALUE IN VARCHAR2
   )
      RETURN VARCHAR2;
END;
/

2.创建解密程序
CREATE OR REPLACE PACKAGE BODY APPS.cux_fnd_web_sec
AS
   FUNCTION encrypt (
      KEY   IN VARCHAR2,
      VALUE IN VARCHAR2
   )
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.encrypt(java.lang.String,java.lang.String) return java.lang.String';

   FUNCTION decrypt (
      KEY IN VARCHAR2,
      VALUE IN VARCHAR2
   )
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END;
/
3.获取APPS密码

--取得APPS密码密文
select encrypted_foundation_password from apps.fnd_user_view where user_name='GUEST';

得到一长串加密的APPS密码。

--取得APPS密码明文
将加密的APPS密码放在如下SQL中的第二个参数,得到明文密码。
select apps.cux_fnd_web_sec.decrypt('GUEST/ORACLE','ZHF7348EF3CB15DEFB4B252B720E074F19B5F4FA24EF9A3B673C38B60CFB8473A6110F34A186DB1942A92697B01AE9D084A9') pwd from dual;

Oracle 19c pdb open restricted 处理

xuwanxin1987阅读(290)

Oracle 19c pdb open restricted 处理

在CDB下查询受限的PDB:
set lines 200 pages 50000;
col name for a20;
select con_id, name, open_mode, restricted from v$pdbs;

在PDB中查询相关受限的PDB:
select instance_name, logins, status from gv$instance; (v$containers也可以查询)

cdb下更换 pdb restricted 模式:
alter pluggable database pdb_name close immediate instance=all; (all except pdb1)
alter pluggable database pdb_name open read write instance=all;

alter pluggable database PDB$SEED close immediate;
alter pluggable database PDB$SEED open read write;

pdb下更换 pdb restricted 模式:
alter session set container=pdb_name;
alter pluggable database close immediate;
alter pluggable database open;

在受限模式下,可以授予特定用户 restricted session权限用于临时登录,记得revoke。

alter pluggable database pdb_name open read only;
alter pluggable database pdb_name open upgrade;
alter pluggable database pdb_name open read write;

Oracle EBS Applications system的维护模式

xuwanxin1987阅读(285)

       EBS维护模式是Oracle EBS应用系统 R11.5.10之后的一种新的操作模式,它限制其他用户登录任何的职责,仅供打Patch时用。这为AutoPatch会话提供了最佳的性能和减少时间需求。

1.调度系统停机时间

       管理员可以通过Oracle Applications Manager (OAM)调度'System Downtime' :

Site Map --> Maintenance --> Manage Downtime Schedules

       当系统被排入计划停机时间时,在Restricted Mode使用脚本(adaprstctl.sh)重启Apache,此时,用户登录应用系统将被重定向到系统停机URL ,可以看到类似如下的信息:

Scheduled Downtime Details
Start Time       : 17:30:00 12/11/2004
Expected Up Time : 09:00:00 12/12/2004
For Updates      : famy2u@hotmail.com
The system is currently undergoing a scheduled maintenance.
<Current Status>

This message can be customized with any text message.  If No Downtime has been specified, and the users

try to access the Applications, the following message might also appear:

! Warning
The system has not been taken off maintenance mode completely.
Please contact your System Administrator.

2.维护模式的特点

       可以在adadmin命令菜单下或Oracle Applications Manager中启用(Enabled)和禁用(Disabled)维护模式;

       在disabled的维护模式中,也可以运行AutoPatch,不过性能比较低下;

       当系统启用维护模式时,用户不能登录,会返回一个单独的访问页面,如上。

3.启用(Enabled)和禁用(Disabled)维护模式

       我们每次开始打Patch时,需要启用维护模式,打完之后需禁用维护模式。

在adadmin命令菜单下启用/禁用维护模式;

      adadmin: Options 5, 1   ---Enabled

                      Options 5, 2   ---Disabled

使用$AD_TOP/patch/115/sql/adsetmmd.sql脚本去启用维护模式;

       用adsetmmd.sql脚本是将Profile 的 'Applications Maintenance Mode' (APPS_MAINTENANCE_MODE) 选项设置为 'MAINT',而'NORMAL' 表示禁用维护模式。

可以先查询系统是否启用维护模式:

sqlplus apps/apps

SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual; 

    MAINT  -   启用维护模式.
    NORMAL -   禁用维护模式.

然后使用adsetmmd.sql脚本启用/禁用维护模式:

SQL> @$AD_TOP/patch/115/sql/adsetmmd.sql  ENABLE | DISABLE

4.操作步骤:

1.  Schedule the 'System Downtime' from OAM

OAM: Site Map --> Maintenance --> Manage Downtime Schedules

At the moment of the downtime, do the following:

2.  Shutdown Apache (on Normal Mode):

   adapcctl.sh stop
   or
   adstpall.sh <apps_user>/<apps_pwd>

3.  Enable 'Maintenance Mode' from adadmin

   adadmin: Options 5, 1

4. Start Apache (on Restricted Mode)

   adaprstctl.sh start

5. Apply the Patch with adpatch
6.  Stop Apache (on Restricted Mode)

  adaprstctl.sh stop

7.  Disable 'Maintenance Mode' from adadmin

   adadmin: Options 5, 2

8.  Start Apache (on Normal Mode):

  adapcctl.sh start
  or
  adstrtal.sh <apps_user>/<apps_pwd>

pdb及pdb service自动启动

xuwanxin1987阅读(291)

alter session set container=BZUAT;
exec dbms_service.start_service('ebs_BZUAT');

alter session set container=CDB$ROOT;
alter pluggable database all open;
alter pluggable database all save state;

sqlplus / as sysdba
CREATE OR REPLACE TRIGGER open_all_pdbs
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/

sqlplus / as sysdba
alter session set container=BZUAT;
CREATE OR REPLACE TRIGGER pdb_after_startup_trg
AFTER STARTUP ON PLUGGABLE DATABASE
BEGIN
  dbms_service.start_service('ebs_BZUAT');
  NULL;
END;
/
然后每次重启的时候,可以自动启动service

Troubleshooting ADOP failures in R12.2

xuwanxin1987阅读(346)

原文链接:https://techgoeasy.com/troubleshooting-adop-failures-in-r12-2/

6.Adop apply fails with "Do you wish to apply this patch now [No]"

The following error is reported in the log file
This base patch contains files that may require translation
depending on the languages you currently have installed.
Oracle Corporation recommends that you obtain any translated versions of this
patch for each of your non-US languages after applying this base patch.
Or you may request and apply a Translation Synchronization Patch
to obtain the translation.
Do you wish to apply this patch now [No] ? No

Cause
The adalldefaults.txt was providing the default answer "No" during the patch application.
As per the following output:
$APPL_TOP/admin/${TWO_TASK}/adalldefaults.txt

Start of Defaults Record

%%START_OF_TOKEN%%
MISSING_TRANSLATED_VERSION
%%END_OF_TOKEN%%

%%START_OF_VALUE%%
No
%%END_OF_VALUE%%
End of Defaults Record

Solution
Please change the: $APPL_TOP/admin/${TWO_TASK}/adalldefaults.txt:

## Start of Defaults Record
 %%START_OF_TOKEN%%
  MISSING_TRANSLATED_VERSION
 %%END_OF_TOKEN%%

 %%START_OF_VALUE%%
Yes
 %%END_OF_VALUE%%
## End of Defaults Record

联系我们