EBS 12.2 -- disabling SEND_ACCESS_KEY

Sending the access key is a feature of Workflow notification mailer. SEND_ACCESS_KEY attribute of the Notification mailer enables/or disables this feature.
When SEND_ACCESS_KEY is set to Y, Notification mailer sends notification emails, which include access keys. This access keys let users to access the Notification Details web page of EBS directly without authenticating..
When SEND_ACCESS_KEY is set to N, users must sign on to access the notification details web page.. So they can not access the notificaiton details web page directly without authenticating..

Anyways, It seems that , setting SEND_ACCESS_KEY to Y creates a Security Vulnerability , and here is the method for setting SEND_ACCESS_KEY to N;

• Stop the Workflow Services (Agent Listeners and Mailer)

• From the Application Tier operating system -- As application owner run -> 

sqlplus apps/apps @$FND_TOP/sql/afsvcpup.sql
Something like the following will be returned from the sql;

Component Id Component Name Component Status Type Coe
------------ ------------------------------ ---------------- --------------- ---
10000 ECX Inbound Agent Listener STOPPED_ERROR WF_AGENT_LISTEN GSM
10001 ECX Transaction Agent Listener STOPPED WF_AGENT_LISTEN GSM
10002 Workflow Deferred Agent Listen STOPPED WF_AGENT_LISTEN GSM
10003 Workflow Deferred Notification STOPPED WF_AGENT_LISTEN GSM
10004 Workflow Error Agent Listener STOPPED WF_AGENT_LISTEN GSM
10005 Workflow Inbound Notifications STOPPED WF_AGENT_LISTEN GSM
10006 Workflow Notification Mailer DEACTIVATED_USER WF_MAILER GSM
10020 Workflow Java Deferred Agent L STOPPED WF_JAVA_AGENT_L GSM
10021 Workflow Java Error Agent List STOPPED WF_JAVA_AGENT_L GSM
10022 Web Services OUT Agent STOPPED WF_DOCUMENT_WEB GSM
10023 Web Services IN Agent STOPPED WF_JAVA_AGENT_L GSM
11020 WF_JMS_IN Listener(M4U) STOPPED WF_JAVA_AGENT_L GSM
11021 Workflow Inbound JMS Agent Lis STOPPED_ERROR WF_AGENT_LISTEN GSM
13020 TESTMLR DEACTIVATED_USER WF_MAILER GSM
14020 WF_IN Test STOPPED WF_AGENT_LISTEN GSM
15020 SYC_PASSWORD_MAILER DEACTIVATED_USER WF_MAILER GSM

• afsvcpup.sql also will wait for input.. enter the ID # corresponding to the Workflow Mailer

Enter 10006 as it is the component id of the Notification mailer as you see above..

After hitting Enter, Sql will return the following output;

Comp Param Id Parameter Name Default Value d
------------- --------------------------------------------- --------------------
10055 Allow Forwarded Response Y Y
10123 Alternate email parser oracle.apps.fnd.wf.NctEmailParser r
10124 Attach images to outbound emails Y Y
10125 Attach stylesheet to outbound email Y Y
10056 Attached URLs WFMAIL:ATTACHED_URLY
10057 Autoclose FYI Y Y
10061 Debug Mail Session N N
10126 Direct response N N
10062 Discard Folder DISCARD N
10063 Email Parser oracle.apps.fnd.wf.NlatedEmailParser r
10141 Enable stylesheet references N Y
10064 Expunge Inbox on Close Y Y
10129 Framework Application ID 1 N
10128 Framework Responsiblity 20420 N
10130 Framework URL timeout 30 N
10127 Framework User 0 N
10065 From Workflow Mailer Y
10066 HTML Agent Y S
10340 HTML delimiter DEFAULT N
10131 HTTP User Agent Mozilla/4.76 Y
10068 Inbound Protocol imap N
10240 Inbound SSL Enabled N N
10069 Inbound Server Name N
10082 Inbound Thread Count 1 Y
10132 Inbound connection time out 120 N
10133 Inbound message fetch size 100 N
10070 Inbox Folder INBOX N
10071 Inline Attachments N Y
10138 Invalid More Information Response Notificatio WFMAIL:OPEN_INVALIDYn
10074 Invalid Response Notification WFMAIL:OPEN_INVALIDY
10219 Invalid address list size 100 N
10060 Log Level 5 Y
10073 Mailer Node Name WFMAIL N
10084 Max Error Count 10 N
10134 Maximum Ignore List size 1000 N
10135 Maximum Response lookup code cache size 100 N
10136 Maximum returned email list size 100 N
10072 Message Formatter oracle.apps.fnd.wf.NficationFormatter r
10077 Open FYI Notification WFMAIL:OPEN_MAIL_FYY
10075 Open Notification WFMAIL:OPEN_MAIL Y
10076 Open Notification (Direct Response Parsing) WFMAIL:OPEN_MAIL_DIY
10139 Open Notification (More Information Request) WFMAIL:OPEN_MORE_INY
10058 Outbound Cancelled Notification WFMAIL:CANCELED Y
10059 Outbound Closed Notification WFMAIL:CLOSED Y
10142 Outbound HTML Summary Notification WFMAIL:SUMHTML Y
10078 Outbound Protocol smtp N
10244 Outbound SSL Enabled N N
10079 Outbound Server Name N
10092 Outbound Summary Notification WFMAIL:SUMMARY Y
10086 Outbound Thread Count 1 Y
10094 Outbound Warning Notification WFMAIL:WARNING Y
10140 Outbound connection time out 120 N
10080 Processed Folder PROCESS N
10088 Processor Close on Read Timeout Y N
10081 Processor Error Loop Sleep 60 N
10085 Processor Max Loop Sleep 60 N
10083 Processor Min Loop Sleep 5 N
10087 Processor Read Wait Timeout 10 N
10220 Processor defer reading event data Y N
10089 Reply-to Address Y
10090 Reset NLS N Y
10243 SSL Trust store NONE N
10091 Send Access Key Y Y
10242 Send e-mails for canceled notifications Y N
10241 Send warning for unsolicited e-mail Y Y
10093 Test Address NONE Y
10137 Unsolicited event threshold 2 N
10054 Username N

• Sql will wait for the following inputs, enter the Comp Param id of the Send Access Key for the Comp Param ID and Enter "N" for the values, as we wanted disable it..

Enter the Comp Param Id to update : 10091
Enter a value for the parameter : N

• Lastly, Restart workflow services and you re done.

Note that same procedure applies for 12.1 and instances > 12.0.3 , as well.

RDBMS -- Using BBED (For Oracle Internal Use Only!)

It was a long time ago, but I find this work interesting and  want to share it in this blog..

In one of our test instance, we encountered ORA-01161: database name ABYSDB in file header does not match given name of ABYSTEST error.
The error message was self explanatory..
Basically we had a datafile with a wrong file header .. We were cloning the database ABYSDB to ABYSTEST , and  the database name in the file header of the database file system01.dbf was remained as ABYSDB..
This error was the result of a failed recovery session and the database was an Oracle 10gR2..

As the problem was in a TEST instance, we didnt have anything to lose. So I have used BBED to correct the file header..
BBED is a tool that can be used as a block editor.
Using BBED, we can update/read data blocks even if our instance is shutdown..
We can corrupt, uncorrupt,change or recover the data we need using this tool. However, BBED tool must not be used by database administrators, and it is stated as "Oracle Internal Use Only".. That's why it is not linked, so can not be used directly..

Anyways, as this was a TEST instance, I have used it and it pretty much helped me for saving the day.

What I did was;

Firstly, I have linked and prepared bbed ;

make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/BBED $ORACLE_HOME/bin/BBED

Then, I started BBED cli ;

 bbed

Password: -- The password is blockedit

BBED: Release 2.0.0.0.0 - Limited Production 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

To see the data that supposed to be written in my problematic datafile, I have dump a proper datafile using BBED, and examined the output..

BBED>set offset 0

BBED> dump

 File: /yedek/oradata/drsys01.dbf (0)

 Block: 1                Offsets:    0 to  511           Dba:0x00000000

------------------------------------------------------------------------

 0ba20000 0100c000 00000000 00000104 320b0000 00000000 0000200a 355e19bb

 41425953 54455354 01000000 000a0000 00200000 03000300 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 ac160000 00000000 fcf35923 6880872d 97e1b348 7a050000 3ce0cf2c

 16767de8 77050000 01000000 fd4e0100 24d5ab2d fc4e0100 16767de8 77050000

 3ce0cf2c 01000000 31020000 02000000 10000000 02000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 000a0000 00000000

 00000000 00000000 00000000 03000000 05004452 53595300 00000000 00000000

 00000000 00000000 00000000 00000000 03000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 5cd3c52c a4614add 77050000 56255229 79050000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 e30f608c 7a050000 12d3ab2d 01000160 bb010000 24090000 10000000

Then I have dumped the problematic datafile and saw the difference.

BBED> dump

 File: /yedek/oradata/system01.dbf (0)

 Block: 1                Offsets:    0 to  511           Dba:0x00000000

------------------------------------------------------------------------

 0ba20000 01004000 00000000 00000104 e9d70000 00000000 0000200a 355e19bb

 41425953 44420000 cd074600 00000300 00200000 01000300 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 79014000 06000000 00000000 e8f35923 5cd3c52c a4614add 77050000 3fe5ab2d

 7d7ea391 7a050000 01000120 72870100 5db5ab2d 71870100 7d7ea391 7a050000

 3fe5ab2d 01000000 7c4b0000 4a0a0200 10000000 02000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000300 00000000

 00000000 00000000 00000000 00000000 06005359 5354454d 00000000 00000000

 00000000 00000000 00000000 00000000 01000000 00000000 00000000 62d3c52c

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0a000a00

 10d0c52c 337749dd 77050000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 7d7ea391 7a050000 3fe5ab2d 01000000 7c4b0000 4a0a0200 10000000

select UTL_RAW.CAST_TO_VARCHAR2('4142595344420000') from dual;

Output: ABYSDB

So 41422953 must be -> ABYS and 44420000  must be -> DB ..

That 'is -> if I could update 44420000 with 54455354(which means TEST), my problem would be fixed.. 

To be able to do that, I put BBED into edit to mode and updated the data , as follows..

BBED> set mode edit

        MODE            Edit

To able to modify  44420000, my offset should be 36,37,38,39.. 

As one line represent 32 bytes and one word contains 4 byte -> One line + one word = 32 +4 = 36

The word TEST was 4 byte so I had to update one word ..

So I updated the data residing on block from offset 36 to offset 39..

BBED> modify /x 54 offset 36

BBED>modify /x 45 offset 37

BBED>modify /x 53 offset 38

BBED> modify /x 54 offset 39

After the update, recomputed the block checksum and dumped the data again to see the data if changed properly..

BBED> sum apply

Check value for File 0, Block 1:

current = 0x84aa, required = 0x84aa

BBED> set offset 0

        OFFSET          0

BBED> dump

 File: /yedek/oradata/system01.dbf (0)

 Block: 1                Offsets:    0 to  511           Dba:0x00000000

------------------------------------------------------------------------

 0ba20000 01004000 00000000 00000104 aa840000 00000000 0000200a 355e19bb

 41425953 54455354 cd074600 00000300 00200000 01000300 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 79014000 06000000 00000000 e8f35923 5cd3c52c a4614add 77050000 3fe5ab2d

 7d7ea391 7a050000 01000120 72870100 5db5ab2d 71870100 7d7ea391 7a050000

 3fe5ab2d 01000000 7c4b0000 4a0a0200 10000000 02000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000300 00000000

 00000000 00000000 00000000 00000000 06005359 5354454d 00000000 00000000

 00000000 00000000 00000000 00000000 01000000 00000000 00000000 62d3c52c

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0a000a00

 10d0c52c 337749dd 77050000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 7d7ea391 7a050000 3fe5ab2d 01000000 7c4b0000 4a0a0200 10000000

Okay, that was it. I updated the dbname in the system01.dbf using BBED.
After the update , I retried the failing operation and succeeded..

Note that: Never use BBED in a Production system.. Also never use it if your test instance is a critical one.. This is just an example.. BBED should not be used without Oracle Support.

ODA-- Vm Manager support on Oracle Database Appliance (X4)

ODA X4 can be used as a  virtualized platform for deploying Oracle VM Servers.
Once the initial configuration is done, you can download and import your Vm templates into the Virutalized ODA platform.. Importing directly the vm machines save us from the excessive time taking installation activities.. (at least for EBS)
The management of the virtualized ODA platform is done by using  Oracle appliance management cli (oakcli)..
Using VM Manager is not possible because of special configuration of ODA. In other words; VM manager is not supported on ODA.
This is a H/W limitation.. We are not be able to use manager for management.
As mentioned above we use the Oracle appliance management cli (oakcli) for VM management on ODA.

Related documents:

http://ermanarslan.blogspot.com.tr/2014/06/ebs-122-ebs-on-virtualized-oda-x4-2.html
http://ermanarslan.blogspot.com.tr/2014/06/odavm-how-to-move-user-vm-from-one-node.html
http://ermanarslan.blogspot.com.tr/2014/06/ovm-oakcli-command-examples.html
http://ermanarslan.blogspot.com.tr/2014/06/oda-oracle-database-appliance-x4-2-node.html
http://ermanarslan.blogspot.com.tr/2014/06/error-inporting-ebs-122-db-template.html

EBS 12.2 -- "APPS.AD_ZD_ADOP", line 543 // Adadmin problem

You may encounter following errors, while trying to use adadmin in EBS 12.2..

AD Administration error:
ORA-20010: ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00229: input source is empty
ORA-06512: at "APPS.AD_ZD_ADOP", line 543
ORA-06512: at line 1

These undocumented error messages are caused by the XML parsing..

When you anaylze the code, you will see the following query which encounters error while trying to get an xml element and the code raises exception accordingly..

  SELECT
                  fn.host node_name ,
                  aat.appl_top_id appl_top_id ,
                  EXTRACTVALUE(XMLType(TEXT),'//shared_file_system') is_shared
        FROM
                  fnd_nodes fn,
                  FND_OAM_CONTEXT_FILES focf,
                  fnd_product_groups fpg,
                  ad_appl_tops aat,
                  ad_releases ar
        WHERE     focf.NAME not in ('TEMPLATE','METADATA','config.txt') and focf.CTX_TYPE='A' and
                  (focf.status is null or upper(focf.status) in ('S','F')) and
                  EXTRACTVALUE(XMLType(focf.TEXT),'//file_edition_type') = 'run' and
                  focf.node_name=fn.host and
                  (fn.support_cp='Y' or fn.support_forms='Y' or
                   fn.support_web='Y' or fn.support_admin='Y') and
                  aat.appl_top_type='R' and aat.applications_system_name=fpg.applications_system_name and
                  aat.active_flag='Y' and
                  fpg.release_name=ar.major_version||'.'||ar.minor_version||'.'||ar.tape_version and
                  fpg.aru_release_name=ar.aru_release_name and
                  aat.name=EXTRACTVALUE(XMLType(focf.TEXT),'//APPL_TOP_NAME');


The function that causing the error is EXTRACTVALUE..
What happens is;
EXTRACTVALUE tries to extract a value from the context file stored in the database , but it finds the input empty.
Context Files were in fnd_oam_context_files tables, they are stored in the TEXT column as clobs..
So basically our query wants to read a CLOB and make some xml processing on it, but it finds the relevant clob empty.

Note that; here are plenty of context file contents stored in fnd_oam_context_files table, but the important one is the one that our query is trying to process..

Note that :
Ref: http://avdeo.com/
while updating context variables using OAM is that what it displays is gotten; by parsing the context file stored in fnd_oam_context_files table (Autoconfig at each run uploads the context file in this table). When we try to update the context file, OAM first update’s the status flag from ‘S’ to ‘H'(History) for our context file record, it then inserts another row for the same context file with status ‘S’. (OAM inserts another row instead of updating the existing row to maintain change history for each context file) It then requests the specific node’s FNDFS listener for updating the file on the file system (autoconfig uses the file on the file system). So we have to make sure that the FNDFS listeners are running on all the nodes before using OAM to update the context file.
When we reformat our query , and extrace the problematic xml operations, we see that;
Our problematic query is actually trying to read the context files that are returned from the following query;

select  * from FND_OAM_CONTEXT_FILES focf where
                  focf.NAME not in ('TEMPLATE','METADATA','config.txt') and focf.CTX_TYPE='A' and
                  (focf.status is null or upper(focf.status) in ('S','F'))

If it is the case This query probably return 2 records..
Open/Read the Text column of the record with status = F .. You will see that the TEXT column of that records is empty, and that is the problem.

Take a backup of the table , and delete that record.. Once the record is deleted, your problem will be fixed.. Then you can retry desired adadmin operation.

Following is a Bonus Track :)

Context Files are stored in the database.. The table that store the context files( historically) is "FND_OAM_CONTEXT_FILES"
Context files in this table is stored in a huge clob column named TEXT..
In TEXT column the context file is stored as is, that is in xml format ..

fnd_oam_context_files
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(512)
VERSION NOT NULL VARCHAR2(30)
PATH NOT NULL VARCHAR2(1024)
LAST_SYNCHRONIZED NOT NULL DATE
TEXT NOT NULL CLOB
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER(15)
LAST_UPDATE_LOGIN NOT NULL NUMBER(15)
NODE_NAME NOT NULL VARCHAR2(30)
STATUS VARCHAR2(1)
SERIAL_NUMBER NUMBER
EDIT_COMMENTS VARCHAR2(2000)
CTX_TYPE NOT NULL VARCHAR2(1)
So by quering the table and using the proper xml functions , the values in context file can be read.

For example, to gather the web port information of the E-Busines Suite system, following query can be used;

SELECT extractvalue(xmltype(text),'//web_port')
FROM fnd_oam_context_files where status='S';


Note that, the query should be modified in order to read the xml tags with childs.

EBS 11-- 11GR2 Upgrade DB Interoperability patches

As stated in the documents, to upgrade an EBS 11i database to 11gR2, we need to prepare our application code level to be able to work with a 11gR2 database. This is done by applying EBS 11gR2 interoperability patch.. So after this patch is applied , our EBS 11i system has the ability to work with an 11gR2 Oracle Database.
But what if we apply this patch and contine using an 10gR2 database?
This question comes to our minds, because we may need to continue using our current database for a while before the upgrade.. Consider such a scenario that  we want prepare our source system for the upgrade today, but actually make the upgrade in next week. This scenario can occur where we have a strict limitation in downtime.. Anyways, this means, we need to continue using our current Database till the upgrade day.
We know Oracle , and we know how critic these kind of operations are..
But we also know that this is just a patch, not an upgrade.. Patches are built to fix the bugs, or bring some new features..  As long as it is not stated, patches must not ruin the current things.. In other words; if not stated, patches must not interfere with the system and must not take an capability from the system..
That's why we have dealed with it. We actually didnt tried it yet, but the question was directed to Community and Oracle Support.
Oracle Support confirmed us,  answered the question as follows;

The code must be in place before the db is upgraded. It should not matter at what point the patch is applied.
Therefore if you wish to apply it and then keep running with a 10g database for a while, that should not cause an issue.

EBS 11i-- Function not available to this responsibility & adding custom top

You may encounter "Function not available to this responsibility" errors using EBS 11i Forms.

These kind of errors are mostly caused by a wrong Custom module setup and can be fixed by taking following actions:  

As this case is for 11i, 
check your $APPL_TOP/admin/topfile.txt file and add your custom module into that file, if it s not present already.

Also check your environment file, especially custom environment file.. It should reflect your custom_top as well..  

Finally, run autoconfig .

--- That's it. Your problem should be fixed..

Oracle also suggests following action plan to fix the error:

Make sure the your custom application is defined in:

1)$APPL_TOP/admin/applcust.txt and $APPL_TOP/admin/topfile.txt
2)Add the custom_top to $APPL_TOP/custom_$CONTEXT_NAME.env
3)If necessary edit your xml file (from APPL_TOP/admin) and make sure that the variable <oa_env_file type="custom" oa_var="s_custom_file" points to the custom environment file
4)Run autoconfig.
Also, following script may help adding custom tops in to an EBS 11i instance...

ORACLE EBS 11i ADD CUSTOM TOP SCRIPT --for linux

Usage Example:

 sh add_custom_top.sh xxefa TEST oracle1 XXEFA XXEFA XXEFA_TBS TEMPPROD /u2/oradata/u2/xxefa_01.dbf 1000M

###ADD CUSTOM TOP SCRIPT BY ERMAN ARSLAN ### Created 6/18/2013

### This script should be executed by the application owner

### Before, executing this script, apps environment should be sourced. APPS{SID}_{hostname}.env

### 1. parameter ->  name of the custom top  (lowercase) example: xxefa

### 2. parameter- >  Ebs database SID 

### 3. parameter ->  System user password

### 4. parameter ->  Db user name that is going to be created for the new application 

### 5. parameter ->  Password of the db user  that is going to be created for the new application 

### 6. parameter ->  Tablespace name that will be created for the new  user.

### 7. parameter ->  Existing Temp tablespace that will be used by the new user

### 8. parameter ->  Datafile full path with datafile name . This datafile will be created while creating the new tablespace

### 9. parameter -> datafile size 1000M

### Checking Command line argument count ###

if test $# -lt 9;

then

echo Lacking argument.. Please provide 9 arguments

echo " Usage :

#######################################################################################################

Script calistirilmadan once application environment source edilmis olmalidir. APPS{SID}_{hostname}.env

ADD CUSTOM TOP SCRIPT BY ERMAN ARSLAN ### Created 6/18/2013

This script should be executed by the application owner

Before, executing this script, apps environment should be sourced. APPS{SID}_{hostname}.env

1. parameter ->  name of the custom top  (lowercase) example: xxefa

2. parameter- >  Ebs database SID 

3. parameter ->  System user password

4. parameter ->  Db user name that is going to be created for the new application 

5. parameter ->  Password of the db user  that is going to be created for the new application 

6. parameter ->  Tablespace name that will be created for the new  user.

7. parameter ->  Existing Temp tablespace that will be used by the new user

8. parameter ->  Datafile full path with datafile name . This datafile will be created while creating the new tablespace

9. parameter -> datafile size 1000M

########################################################################################################

"

exit

fi

echo Script is executed by user =  `whoami`

echo "Continue?  (Y or N)  Y for continue , N for abort"

read Cevap1

if [ $Cevap1 == "N" ]

then

echo Program closed.

exit

fi

echo " Is apps environment set? ( Y or N)   Y for continue , N for abort"

read Cevap2

if [ $Cevap2 == "N" ]

then

echo Program closed

exit

fi

### Creating Directory structure. ####

mkdir $APPL_TOP/$1

mkdir $APPL_TOP/$1/11.5.0

mkdir $APPL_TOP/$1/11.5.0/admin

mkdir $APPL_TOP/$1/11.5.0/admin/sql

mkdir $APPL_TOP/$1/11.5.0/admin/odf

mkdir $APPL_TOP/$1/11.5.0/sql

mkdir $APPL_TOP/$1/11.5.0/bin

mkdir $APPL_TOP/$1/11.5.0/reports

mkdir $APPL_TOP/$1/11.5.0/reports/US

mkdir $APPL_TOP/$1/11.5.0/forms

mkdir $APPL_TOP/$1/11.5.0/forms/US 

mkdir $APPL_TOP/$1/11.5.0/$APPLLIB

mkdir -p $APPL_TOP/$1/11.5.0/$APPLOUT

mkdir -p $APPL_TOP/$1/11.5.0/$APPLLOG

### Adding custom product top to topfile.txt..

cd $APPL_TOP

cd admin

echo $1  $APPL_TOP >> topfile.txt

### Adding new Custom top to the default custom environment file

function toUpper { echo $1 | tr [a-z] [A-Z]; } ## uppercase e cevirme fonksiyonunu tanimladik

echo export `toUpper $1`_TOP=$APPL_TOP/$1/11.5.0 >> $APPL_TOP/custom`echo $2`_`hostname -a`.env

###  Connecting to db and creating tablespace and user 

sqlplus system/$3 << EOF

create tablespace $6 datafile '$8' size $9 default storage(initial 10k next 10k);

create user $4 identified by "$5" default tablespace $6 temporary tablespace $7 quota unlimited on $6 ;

grant connect, resource to $4;

exit;

EOF

### Environment is sourced again

. $APPL_TOP/APPS`echo $2`_`hostname -a`.env

echo"

### Script completed . Check above for the error.. If there are errors produced, run the script after correcting them. Error handling is not done in this script, because this script will not be used by the end users.

"

################

###################################

IMPORTANT: For other systems (not Linux) , this script may fail.. The script will run successfully but you may encounter problems like the following;

adcmctl.sh: XXEFA_TOP=/u01/appappl/xxefa/11.5.0: is not an identifier..

This a shell problem, as /bin/sh in Solaris behaves different than it behaves in Linux.

That is ,it is caused by environment variable declaration in custom environment file.. I mean the syntax..
In solaris an environment variable should be defined as follows;

XXEFA_TOP=/u01/appappl/xxefa/11.5.0
export XXEFA_TOP

not -> export XXEFA_TOP=/u01/appappl/xxefa/11.5.0 .. This is wrong in Solaris /bin/sh....

Rdbms-- Offline Locks/Optimistic locking

Offline lock is a term used for non blocking locks. It is also called as Optimistic Locking, which does not use exclusive/blocking database level locks to protect the concurrently accessed data.
Offline locks relies on version stamps. So we can think that each data record has a version stamp . Lets call it timestamp. So when a session retrieves the data, it also retrieves the version stamp/timestamp of it.. Also when a session posts its changes to the database, it posts the new version stamp/timestamp of the data too.
Another session updating the same data recover will see the version is updated, and then the conflict resolution occurs. The check is made during the update of the data , and conflict resolution methods may differ  according to the needs such as : We may return an error or we re-read the record including the new version stamp and try to apply our updates again and so on. 

Following is a schemantic explanation from dba.stackexchange.com . It explains this type of locking clearly..

Linux -- Redirecting inputs -- same stdin for 2 processes

Just for fun, we tried to execute commands on multiple terminal.
For this, we made a demo.
Lets say, we have pts1 and pts2, which are pseudo terminal connection from 2 ssh clients.

We connect to pts1 and run the following;

bash < /dev/pts/2

So, we redirect pts2 to our bash shell running on my pts (pts1)

So whatever we write to the terminal of pts2 is displayed and executed in pts1..

For example, we write "ls" in my terminal which is connected to pts2, the ls command runs in pts1 and displays the output in pts1.

So far so good. But there is a problem in here.. That is; . The first character/and sometimes the first word, is never catched by pts1.. So for example to write ls and execute it in the /dev/pts/1, we write "lls" in to my /dev/pts2 terminal. Because the first "l" is not catched by pts1, and also it is displayed in pts2.. so lls -> transfferred to the pts 2 as "ls", and ls command is executed in pts1 , as expected. 

Another example of the behaviour is as follows;

We write ls and press Enter -> it s executed in pts2 (where we write)
then, after pressing Enter ;
We write ls again and press Enter -> it s executed in pts1 (as we want)

It seems pts1 has pts2's input as its input(redirected)
and
pts2 has also its own input.
So pts1 reads the input of pts2 , and pts2 also read the input of its own..
So this may be the race condition.

So there seems to be a race condition occuring, but as the input is divided in a systematic way, this should not caused by a race condition, but an algorithm.

To have other opinions, I have started a discussion in Linkedin Group named  Linux Expert.

(Also , started a discussion in http://www.linuxforums.org/  , but it could not get answered.)

By the comments of other engineers in Linkedin, we have conclude that this behaviour should be caused something works in a Round Robin fashion.

It seems, when the stdin devided into 2 processes and the Kernel handles the inputs with a round-robin fashion. Ofcourse the problem is not solved

 it can not be solved, it works that way by design. 

On the other hand, we have found the reason for this at least :)

A suggested method can be using -> script /dev/null | tee -a /dev/pts/2

script makes a typescript of everything printed on your terminal.
Tee command is used to store and view (both at the same time) the output of any other command.
tee -a appends the output to file or typescript, retaining the prior contents.

/dev/null is a virtual-file that can be written to. Data written to this file gets discarded.

So the explanation of the command  above is ;
whatever we write is reflected to the /dev/pts/2 instanty.

Yes, this may be an alternative.. This command will write all the commnands which are inputs of pts/1 to another terminal which is pts/2 in this case ... But it works a little different than "bash < /dev/pts/2".

By using command "bash < /dev/pts/2" , what we do is that, we manage pts2 as another independent terminal, but we able give its inputs from another terminal.. 

On the other hand, the command "script /dev/null | tee -a /dev/pts/2" clones what we write to the terminal pts/2.  So we have 2 terminals executing and displaying the same outputs at the same time.. a Anyways, using script commands  is handy and meet our requirements ,as  we were trying to execute commands on multiple terminals .

POC:
:)

12.2-- AD.C.Delta.5 and TXK.C.Delta.5 is available!

News from Steven Chan; AD.C.Delta.5 and TXK.C.Delta.5 updates are now available.
Applying these cumulative patch updates seems mandatory, as these updates deliver enhancements and fixes for Online patching tools.

Some important Online Patchings related updates are as follows;


New 'standard' cleanup mode - An optimized default cleanup mode, available in addition to the 'quick' and 'full' cleanup modes.  This is my favorite one,  as cleanup takes a long time.. Maybe this will decrease the time to make a cleanup. It is default, and optimized.

Log Analyzer Utility - Analyzes adop log directories for errors and warnings, and displays messages to help you identify any problems in an adop run.

New 'cm_wait' parameter - Allows you to specify how long to wait for concurrent processes to finish, before shutting down the Internal Concurrent Manager.

You can download these Release Update Packs using the following links;

• 18283295: R12.AD.C.Delta.5
• 18288881: R12.TXK.C.Delta.5 

I will write an article about applying these release update packs in next week, when I will have an opportunity to use them in real life..

EBS 12.2 -- Configuring SSL in Oracle E-Business Suite Release 12.2

Lets demonstrate a SSL implementation process in EBS 12.2.
What I mean by SSL implementation is configuring Clients Browser and EBS Web Services communicate through SSL.  EBS uses FMW to communicate over ssl between its components.

Info:

Oracle Fusion Middleware supports SSL version 3, TLS version 1 and JKS-based keystores for components running under Java and Oracle Wallets for other components, such as the Oracle HTTP Server.

In the Web tier, We still use Oracle Wallet to store and manage our certification.. On the other hand; Java keystores can be used for building a secure communication to and from weblogic servers.

Info:

Oracle Fusion Middleware uses JKS keystore (the default JDK implementation of Java keystores used by Oracle WebLogic Server) to store keys and certificates.

Other components (such as Oracle HTTP Server) continue to use the Oracle wallet as their storage mechanism.

Info:

Using Widcard is now certified with EBS 12.2.

Okay lets start;

• We start by creating a Wallet.We open Oracle Wallet Manager by using the Applications's OS user account and ovm command; set your environment first, -- dont use owm from the Oracle 10.1.2 home..We need to use the owm in FMW home.

applmgr> export PATH=$FMW_HOME/webtier/bin:$FMW_HOME/oracle_common/bin:$PATH
applmgr> owm & (open the wallet manager in background .. note that you must have X window privileges , I mean with root xclock + ,and you must have necessary rpms installed on your system for example: LibXrender 32 bit :))

On the Oracle Wallet Manager Menu navigate to Wallet -> New.

Answer No to: “Your default wallet directory doesn't exist. Do you wish to create it now?” 

The new wallet screen will now prompt you to enter a password for your wallet. Be sure to make the password something you will remember. You will need to use the password whenever you open the wallet with Oracle Wallet Manager, or perform operations on the wallet using the Command Line Interface. With auto login enabled processes submitted by the OS user who created the wallet, there is no need to supply the password to access the wallet. Click YES when prompted: “A new empty wallet has been created. Do you wish to create a certificate request at this time?”

• Once we created our wallet, the OWM asks us if we want to create a certificate request.. We answer yes, and fill the form accordingly. ( We use the information that our customer provides, I mean OU, State,Country, Key Size etc..)

• After creating our certificate request, we need to export it.

• While exporting, note the directory name when prompted. This is the default directory of our wallet..

• Declare a name for your csr , for example: server.crs and save.. It will create a server.csr file in your wallet directory: For example under : /apps/fs2/EBSapps/10.1.2/owm/wallets/applmgr. It will report the file as saved as below..

• Okay, now we should send this to our customer , as they should submit this Certificate Authority to request a Server Certificate.

The process should be like this. It is documented like this, but sometimes we need to go beyond the documents..

I mean, if the customer has already acquired certification in .P12 file / format; we need to convert it.

We need convert P12 to JKS and then convert JKS to Wallet ..

Here is the method to do that:

keytool -v -importkeystore -srckeystore yourcert.p12 -srcstoretype PKCS12 -destkeystore yournewkeystore.jks -deststoretype JKS

--You must use the same password for the new jks and the private key = "mypassword"

keytool -import -alias Root -keystore yournewkeystore.jks -trustcacerts -file root.cer
keytool -import -alias Intermediate -keystore yournewkeystore.jks -trustcacerts -file intermediate.cer

mw_home\oracle_common\bin\orapki wallet create -wallet ./ -pwd "mypassword"

mw_home\oracle_common\bin\orapki wallet jks_to_pkcs12 -wallet ./ -pwd "mypassword" -keystore ./yournewkeystore.jks -jkspwd "mypassword"

--Make sure the private key password and the wallet password match = "mypassword"

open the newly created ewallet.p12 with Oracle wallet manager and in wallet menu, tick "autologin", then save. This creates cwallet.sso along side ewallet.p12.

• So once we have a walid wallet (ewallet.p12 and an autologin file (cwallet.sso) ), we can continue with the SSL configuration.
• While we are making our wallet autologin using Wallet Manager,  we should also save our wallet in to the directory -> {s_web_ssl_directory}/Apache
• Note that : curly braces are for the Autoconfig variables. You can find the value of them using ;cat $CONTEXT_FILE |grep variable_name
• Note: If all trusted certificates that make up the chain of Server Certificate are not present in the wallet, adding the certificate will fail. When the wallet was created only the certificates for the most common CA’s were included automatically. Contact your Certificate Authority if you need to add their certificate, and save the provided file (for example as ca.crt) in the wallet directory. If your Certificate Authority provided an intermediate certificate (to complete the chain) then save the provided file (for example as intca.crt), this will need to be imported into Oracle Wallet Manager prior to importing the Server Certificate (server.crt if you used the example name).Okay, then we import our root and intermediate certificates in to the b64InternetCertificate.txt file..
• So in this case; we add our certificates to the related file..
cat ca.crt >> <10.1.2 ORACLE_HOME>/sysman/config/b64InternetCertificate.txt
cat intca.crt >> <10.1.2 ORACLE_HOME>/sysman/config/b64InternetCertificate.txt
• Then we copy cwallet.sso file from {s_web_ssl_directory}/Apache directory(where we saved)
to the following directories ;

--note that : we take a backup of cwallet.sso in that directories before copying/overwriting it..

{s_ohs_instance_loc}/config/OHS/{s_ohs_component}/keystores/default

{s_ohs_instance_loc}/config/OPMN/opmn/wallet
$EBS_DOMAIN_HOME/opmn/{s_ohs_instance}/{s_ohs_component}/wallet
$EBS_DOMAIN_HOME/opmn/{s_ohs_instance}/wallet
$FMW_HOME/webtier/instances/{s_ohs_instance}/config/OHS/{s_ohs_component}/proxy-wallet

• Then, we update cacert file;(Oracle WebLogic Server, Oracle Web Services etc uses this file..)

cd {s_fmw_jdktop}/jre/lib/security

chmod u+w cacerts

keytool -import -alias OHSRootCA -file ca.crt -trustcacerts -v -keystore cacerts
keytool -import -alias OHSIntCA -file intca.crt -trustcacerts -v -keystore cacerts
keytool -import -alias OHSServer -file server.crt -trustcacerts -v -keystore cacerts

On a UNIX system the TCP/IP port numbers below 1024 are special in that only processes with root privileges are allowed to listen on those ports..

So, opening a privileged port requires extra steps, but we dont use privileged port normally.. We choose to have a unknown port which is above port 1024..

So , our cacerts and wallet files are configured. We continue with application configuration to use this files to supply SSL communication;

• We first, start with the Web tier configurations;

Login to Oracle Fusion Middleware Control Console (for example http://<hostname>.<domain>:<AdminServer Port>/em)

Select Web Tier Target under EBS Domain
Select Administration > Advanced Configuration
Select ssl.conf file for edit
Update the Listen <port> and the VirtualHost _default_:<port> directives to SSL port, for example Listen 4443   --> your choice -- ssl port
Click Apply

Then run following command by Application Owner OS 'user with Application environment set..

perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE
Enter the APPS user password:
Enter the WebLogic AdminServer password:

adSyncContext.pl will propogate the changes, we just did from the Oracle Fusion Middleware Control Console to the context file variables.

• Secondly , we open OAM(it can be done using vi editor from the OS) and control & change the following autoconfig variables if necessary;

SSL Related Variables in the Context File
Variable	Non-SSL Value	SSL Value
s_url_protocol	http	https
s_local_url_protocol	http	https
s_webentryurlprotocol	http	https
s_active_webport	same as s_webport	Verify the port, correct if required.
s_webssl_port	not applicable	Verify the port, correct if required.
s_https_listen_parameter	not applicable	Verify the port, correct if required.
s_login_page	url constructed with http protocol and s_webport	Verify the protocol and port, correct if required.
s_external_url	url constructed with http protocol and s_webport	Verify the protocol and port, correct if required.

• Lastly, we shutdown our application services, and run autoconfig to make the system configured by considering the new variable values.. Autoconfig makes the SSL configurations System wide.. When the autoconfig is completed, we start our services..

So that's it.. We configured our EBS 12.2 environment to work with SSL.

Some importants notes about the SSL configuration in EBS 12.2:

After this configuration , we will reach our login page using https://hostname:ssl_port

• Even if we use http://hostname:non_ssl_port to reach our login page, it will be redirected to the ssl enabled url https://hostname:ssl_port..
• That is , Our non-ssl tcp port will be open. Note that : We can close it. We can change httpd.conf and comment out line starting with Listen , and restart Apache to do that.. On the other hand, I dont recommend it , because that port is kept open for those products that need to access some of their pages via the HTTP, and for the Oracle E-Business Suite Help System. 
• So in short , you will not be able to use EBS Help pages, if you close that non-ssl tcp port.
• One last thing; With this configuration, our forms will be working in ssl, too.. It is becuase EBS uses servlet architecture in Forms communication layer .. So we dont need to do anything about the Forms. Forms will be communicating in https...
• Wildcards can be used in the Certificates! .. Tested & Verified. A certificate with a servername *.ermandom.com works very well..
• Oracle E-Business Suite Release 12.2 currently supports securing the communication between the end users browser and the data center. Securing OPMN wallet with Fusion Middleware Control Console is detailed in this document but securing the internal communication (Oracle Weblogic Managed Server and other components with Oracle HTTP Server) will be supported in a later release and detailed in this section when they become available.