Sunday, September 29, 2013

Database-- 12C New In-Memory Option -- Flip a switch and all your applications run much faster!

This article was written based on Larry Ellison's speech in Oracle Open World 2013..
Larry Ellison 's keynote was about Oracle Database 12c In-Memory Database, M6 Big Memory Machine, Oracle 's backup system and Oracle 's advantages in Cloud.
You can reach the key note from the following url ;  http://ermanarslan.blogspot.com/2013/09/larry-ellison-oracle-openworld-keynote.html


The reason to prefer the in memory option is to make the system go faster, ofcourse. The design goal should be at least 100x faster.. In database world it primarily means "queries run 100x faster..", as Ellison states.
Accelerating the queries is the purpose that directly and mainly affects the Data warehouse systems, as we use analytical queries to obtain the meaningful data.. Doing things in memory provides the query results at the speed of thought.. That is, the answers are coming back faster than you can come up with the questions...
So, with In Memory Option queries are faster, but also the transactions should be not slowed down, too. As there are bunch of Oltp and Erp applications in Oracle, the design goal should be to speed up the transactions, as well. By using In Memory Option, the transactions running on these Enterprise Level mixed workload type (Oltp/Anaytical/Erp) applications should be at least x2 faster..



 So Oracle 's new solution for In memory Operations is to store the data images in memory in different shapes which fit the type of the operation....The implementation of this idea will accelarate both the queries and transactions. Ofcouse, the implementation of this idea seems so hard because Oracle needed to protect the transaction recovery, logging and consistency while storing the data in memory in two different shapes..

Traditionally, In Rdbms, the data is stored in rows. Storing the data in rows makes processing very fast but for few rows with a lot of columns.. Alternatively, data can be stored in columns, too. This method makes query processing become very fast. But again, this is faster in few columns with a lot of rows..

Oracle 's new In Memory technology is based on a better idea..
In Oracle Database 12c In Memory option, the data is stored in both format, and put in the memory.. There are two stores in this method named Column Store, and Row Store. (One store for the transactions and one for the queries/analytical queries).
The data stored in the Row Store is in Row Format , and the data stored in the Column Store is in column format, which is 90 degree rotated version of the row format.
When you insert a data, Oracle will update row store and column store..
When you update one, you will update the other. The data is consistent. Transactional integrity is protected..
Column store makes x100 query performance. Row store makes transactions go faster..

Following is prepared by Oracle to show the speed of the Oracle 's 12c In Memory Option.
In this example, Oracle runs a query on a table which has 3,167,531,763 rows.. The data in the table is coming from the real life. The data is based on the Wikipedia searches.. The user searches that is done in the last week of August..



The question is "how can transactions go faster?".. This question comes to mind, because in this technology, there are two stores in memory, and both of them should be updated.. So there is an additional work...
Oracle explains this as follows,
Normally we create 2 index for oltp , 10 index (maybe more) for analytic queries. Even, we create the indexes for queries, we speed up them, but then our transactions are slowing down..
Inserting a row into a table means , insert row + update index + update index... + update index..  Maintaining those indexes is a very expensive operation and slows down OLTP..
So, what Oracle suggest here is, actually dropping the analytical indexes, as the column store technology is developed to replace the analytical indexes..
When the analytical indexes are dropped, the need to update these indexes is taken away..
So, no need to update analytical indexes, and this is where Oracle new In-Memory Option takes advantage in OLTP performance in comparison to the traditional In-Memory systems..
In addition, there is no transactional logging for the column store.. Column are highly compressed.
So OLTP runs dramatically faster with these enhancements. When Oracle updates the data, it goes to row store and makes the update there, without updating any indexes..
If Oracle executes a query, it goes to the column store, which by its nature, supplies x100 query performance..
Also, as Larry Ellison states, there is very little overhead in maintaining the column store. The data is put in the column store on startup or first access, but this doesnt mean that we need to store the entire database in memory..  Consider Exadata, there is a memory hierachy on Exadata. Oracle understands the data and put the data in memory or flash or disks, based on the acess frequency.
In addition to that, columns never had any indexes will benefit from that.
The column processing is optimized, as each cpu core scans columns at billions of rows per second. It s parallelized..
Cpu 's are able to do that because they use vector instructions, known as simd (cpu instruction)
So, with this single instruction, Oracle can scan multiple values, speeding up the scan..

Single instruction, multiple data (SIMD), is a class of parallel computers in Flynn's taxonomy. It describes computers with multiple processing elements that perform the same operation on multiple data points simultaneously.
With a SIMD processor there are two improvements in loading the data and operating on it. A number of values can be loaded all at once. Instead of a series of instructions saying "get this, now get the next", a SIMD processor will have a single instruction that effectively says "get n " (where n is a number that varies from design to design). For a variety of reasons, this can take much less time than "getting" each data individually, as with traditional CPU design.


Flynn's taxonomy
Single instructionMultiple instruction
Single dataSISDMISD
Multiple dataSIMDMIMD
SIMD instructions are widely used to process 3D graphics, although modern graphics cards with embedded SIMD have largely taken over this task from the CPU.

Joins are also faster. Joins are converted into fast columns scans..
In addition to that, the compilicated report objects can be created in memory, and fast scans can be used to populate the report objects in memory..
These options make the Database software Cloud-Ready..

To turn on in memory option in Oracle Database 12c,

Set the parameter  -> inmemory_size=XXX GB
alter table | partition ... inmemory;
Drop analytic indexes ...

No changes required in sql, in application..No data migration is required.
Every application ,everything runs without a single change. RAC works..

Larry Ellison also announced the The Big Memory Machine, as the fastest machine in the World for In Memory Databases..
Big Memory Machine has 32 TB DRAM, brand new processor Sparc M6-32 processor (x2 cores of M5). It has 96 threads per processor and interconnect which is made of silicon(3Tbytes per second) and much much faster than infiniband..
It s avaliable today..  It s also available in SuperCluster form connected with infiniband to Exadata IO Subsystem..
You can gather detailed information about M6-32 from the following Oracle links.
M6-32 -> http://www.oracle.com/us/products/servers-storage/servers/sparc/oracle-sparc/m6-32/overview/index.html
M6-32 supercluster -> http://www.oracle.com/us/products/servers-storage/servers/sparc/supercluster/supercluster-m6-32/overview/index.html

Here is a comparison with IBM p795 gathered from the Ellison's speech.





Information based on this article is based on the Larry Ellison's keynote in Open World 2013..
We could not be there, but thanks to Oracle, we are following remotely :)




Friday, September 27, 2013

Enterprise Manager -- Clear warnings appearing on dashboard..

Sometimes, Enterprise Manager alerts can be annoying... Fixing the problems that generate these alerts , actually does not clear these alerts...

Here is the way to clear these events from backend...

Run the following query and execute the output of the query with sysman..

select  'exec em_severity.delete_current_severity(''' ||
t.target_guid || ''',''' ||
metric_guid || ''',''' ||
key_value || ''')' em_severity
from sysman.mgmt_targets t
inner join
sysman.mgmt_current_severity s
on
t.target_guid = s.target_guid;


For example:

exec sysman.em_severity.delete_current_severity('....','....','.....','.....')..

Thursday, September 19, 2013

About Oracle IPC,busy waiting, RDS and RDMA

The following are my inferences from the presentation of Oracle's Sumanta Chatterjee & Richard Frank. It is mostly about the RDS protocol used by Oracle for event based messaging and cluster intercommunication.  In this presentation, they talk about exciting concept used by Oracle internally, like busy waiting, rds,rdma, rda model and etc.. There are good quotes ..
You can watch the presentation video from this link-> Exadata and the EDC


  • Oracle IPC Interface multiple different  Udp -> RDS -> RC and XRC 
  • RDS is a event based message system.
  • All of Oracle's communication based on messages. Even in Exadata..
  • The advantage of RDMA is bypassing the kernel and silently reaching the memory of Remote System, reading data out of it, pushing data into it.
  • In RDMA you can read data, or push data without any interaction with remote process. 
  • Delivering the messages -- bypassing the kernel, not generating those interrupts, using RDMA directly place them into memory..
  • Traditionally (without RDMA), jump into the kernel, send a message over the wire, take interrupt, push it into a socket, wake a process up, read the socket.
  • Oracle is a timesharing environment..
  • If you release the CPU, you dont know when you will get it back.. Context Switches and etc.. So we must not to release the CPU. Busy wait while you are waiting for a response.
  • Acceptable Busy wait less than 5 microseconds...
  • So we should wait busy and do the IO in less than 5 microsecond.. 
  • For busy waiting -> MPI style busy wait as opposed to event based messaging-> stalls the hardware executions, spins on cpu in user mode with for example Intel's pause instruction..
  • RDMA can be used beyond messaging.. 
  • Remote Memory Access Model -> RDMA -> Changes everything ->  Remote host can update local memory while remote reader is reading it -> Local Host can read data while remote host is updating it.
  • PGAS(Partition Global Access Space)-> All nodes can access shmem on all other nodes.
  • RMA provides -> Access data on different nodes, getting the picture of the data,  sending an update of the data..


Monday, September 16, 2013

Potential logging of E-Business Suite passwords after applying patches

Seems like July 2012 CPU patch introduced a security problem. (Reference Oracle Support Doc: 1579709.1)
If you are using EBS R12, you should check the following patches..


Was any of the CPU patches released between the dates July 2012 - April 2013 applied to the system?
Was the patch 10009066  applied to the system?
Was the patch 12832734 applied to the system?

Patch numbers:


R12.0x

(13979375) - CPU PATCH FOR JUL 2012

(14321239) - CPU PATCH FOR OCT 2012
(14782697) - CPU PATCH FOR JAN 2013
(16196189) - CPU PATCH FOR APR 2013

R12.1X

(13979377) - CPU PATCH FOR JUL 2012
(14321237) - CPU PATCH FOR OCT 2012
(14782696) - CPU PATCH FOR JAN 2013
(16196190) - CPU PATCH FOR APR 2013



If at lease one of these patches were applied, then there is a risk on your system.. That is, the EBS user passwords may be logged in the fnd log tables and/or diagnostic log file..
The issue has been addressed in the JULY 2013 CPU... So to fix this security problem, JULY 2013 CPU should be applied to the system.
But, If you dont want to apply JULY 2013 CPU , following trigger can be used to prevent the logging.


CREATE OR REPLACE TRIGGER
FND_LOG_MESSAGES_BI
BEFORE INSERT ON APPLSYS.FND_LOG_MESSAGES
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
WHEN (NEW.module like 'fnd.sso.SecureHttpRequest%')
BEGIN
:NEW.message_text := 'Ignored';
END;
/


Once you have fixed the problem or created the trigger to work around it , the remaining records should be deleted from the diag files and fnd_log_messages table..

FND_LOG_MESSAGES -> 
In SQL*Plus, login as APPS, and do one of the following:

TRUNCATE TABLE FND_LOG_MESSAGES;
or
DELETE FND_LOG_MESSAGES

where MODULE like 'fnd.sso.SecureHttpRequest%.secureParse';

COMMIT;

DIAG FILES->

For a single file

sed -i  -e '/fnd.sso.SecureHttpRequest/d' file_name

or for multiple files

find /some/dir  -name '*.log' -exec  \
 sed -i -e '/fnd.sso.SecureHttpRequest/d' {} \;


Not that , at the last step, you need to change EBS user passwords , because they might be recorded already..

Use the following to force a password change for all EBS users..

UPDATE FND_USER set PASSWORD_DATE = NULL

where nvl(END_DATE, sysdate+1) > sysdate

and USER_NAME not in ('GUEST','AUTOINSTALL','ASADMIN',

'ORACLE12.0.0','ORACLE12.1.0','ORACLE12.2.0','ORACLE12.3.0',

'ORACLE12.4.0','ORACLE12.5.0','ORACLE12.6.0','ORACLE12.7.0',

'ORACLE12.8.0','ORACLE12.9.0');

COMMIT;



EBS -- 12.1.1 to 12.1.3 Upgrade Steps

Normally when you install EBS 12.1.1 with rapidwiz you have the following;

12.1.1 version of EBS.
11.1.0.7 version of Oracle Database.
10.1.3.4 Oracle Home for Oracle IAS
10.1.2.3 Oracle Home for Oracle tools(reports & forms)

In this documents, you can find the steps needed to upgrade EBS version from 12.1.1 to 12.1.3.
Note that, in our installations, we always install 12.1.1 and upgrade it to 12.1.3.. So if the customer requests a db upgrade , we upgrade the database from 11.1.0.7 to 11.2.0.3 just after 12.1.3 upgrade.

  • Stop application services.
  • Apply necessary database patches(if not applied)
For all UNIX/Linux platforms
* 7111245
* 7211965
* 7330434
* 7486407
* 7627743
* 7639602
* 7684818
* 8199107
* 8639653
* 8940108
* 9026927
* 9066130
* 9554727
* 9743057
* 7319922 (rollback ad reapply if applied already..)

You can use the following method to apply (rollback) patches ..

cd to Patch unzipped_location;

to rollback -> opatch rollback -id PATCH_ID

to apply  -> opatch apply


  • Following  Initialization Parameters should be modified  in spfile/pfile before the upgrade;

_disable_fast_validate=TRUE
recyclebin=off



  • 10.1.3 Oracle Home version should be >= 10.1.3.4 and 10.1.2 Oracle Home = 10.1.2.3 before the upgrade.Note that if you install 12.1.1 with rapidwiz, these requirements become already met, but you can check to be sure, anyways.
You can check Oracle Home versions with the following;

To check 10.1.3 home version;
. $INST_TOP/ora/10.1.3/$CONTEXT_NAME.env
$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc $ORACLE_HOME/oraInst.loc | egrep 'Oracle Application Server 10g|Patch of Oracle Application Server 10g'

You need to look the following line in the opatch output:
 "Patch of Oracle Application Server 10g signifies the Patchset on top of it"

To check 10.1.2 home version;
relogin to the operating system with application owner os user. (to source the apps env file.. This is required to ensure the environment is set for the 10.1.2 oracle home)

$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc $ORACLE_HOME/oraInst.loc | egrep 'Oracle Application Server 10g|Patch of Oracle Application Server 10g'
You need to look the following line in the opatch output:
 "Patch of Oracle Application Server 10g signifies the Patchset on top of it"

  • Apply Applications patch (ad.b.delta.3) 9239089 patch .  This patch is the prereq of EBS upgrade patch.

To apply 9239089 do the following;

Create $ORACLE_HOME/appsutil/admin on the database server.

Copy adgrants.sql (UNIX) from this patch directory to $ORACLE_HOME/appsutil/admin.

Set the environment to point to ORACLE_HOME on the database server.

Use SQL*Plus to run the script:

sqlplus /nolog

SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql APPLSYS

Apply u9239089.drv of the patch using adpatch

  • Apply Oracle E-Business Suite Release 12.1.3 (9239090 )
  • Apply the latest consolidated online help (9239095, this is a post requirement)
  • Apply Patch 9817770 (this is a post requirement)
  • Apply mandatory Patch 9966055 (this is a post requirement)
  • Apply hrglobal.drv (note that if you will apply a post patch that requires hrglobal.drv , you can skip this step as you will need to apply hrglobal.drv later..
  • Run autoconfig on application tier
  • Create appsutil on one of apps server and copy it to db tier
$perl $AD_TOP/bin/admkappsutil.pl --> this will create appsutil.zip
Copy or FTP the appsutil.zip file to the RBMS ORACLE_HOME
cd $ORACLE_HOME
unzip -o appsutil.zip

  • Run AutoConfig on the Database Node.
  • Modify Initialization Parameters back to their original values.
_disable_fast_validate=FALSE
recyclebin=ON

  • Start application services 


Friday, September 13, 2013

Database-- Oracle Database 12c -- FAR SYNC

Oracle Dataguard Far Sync instance is a new feature introduced in Oracle Database 12c, which simply works like a archive/redo repeater.. Far sync instance is something different than the Oracle Database instance, as its mission is accepting redo from the primary database and then shipping that redo to other members of the Oracle Data Guard configuration..

Far Sync instance is an instance without a database.. Although; it has controlfile, and capability to read from standby redolog files and write them into its local archive log files, Far Sync instance does not map any database file, in other words, does not have user data files, cannot be opened for access, cannot run redo apply, and can never function in the primary role or be converted to any type of standby database.. By the way; In order to use far sync feature, you need to have Oracle Active Data Guard license.
Supported Protection modes of Far Sync Instance are Maximum Availability and Maximum Performance.

In Maximum Availability mode, Sync transport method is used between primary and far sync instances.
In this configuration, far sync instance is placed close to the primary instance to reduce the network latency and to minimize the application latency caused by sync transport..




So, by the use of the sync transport mechanism, the redo information are transferred to the Far sync instance site with zero data loss. Once the redo is transferred to the Far Sync site, the Async method is used to transfer the redo to the Standby Site. This extends no data loss protection to more guaranteed sites located far away from the production site. (To extend the idea, you can use another Far Sync instance on the standby site like described in the diagram above) 

So by using Far Sync, in a sense; we use the async and sync transform mechanism together, taking their most beneficial parts... We use zero data loss protection of the sync transport, and we use the performance of async transport to meet the business needs...

In Maximum Performance mode, Async transport method is used  both between primary - far sync instance and far sync instance - standby instance..
The main advantage of using this method is that this method has near-zero performance effect.. That is , async destinations has near-zero effect on primary database performance.. Also there is no dependency to the application tolerance..To extend the idea, you can use another Far Sync instance on the standby site like described in the diagram below.


Far Sync instance can even compress the redo to be transferred to minimize the performance effect on the primary. Moreover; if you have more than 1 standby servers, there is no increasing performance effect on primary, because  Far sync instance can service all the standby servers, as the primary server only has to service the far sync instance...

Tuesday, September 10, 2013

EBS 12.1.3 upgrade, OID 11g upgrade, SSO 10gR3 upgrade, EBS-OID integration documents with an implemented case study.

Recently, we have finished an upgrade project in one of the largest companies in Turkey.

Two Integrated mission critic environments, EBS and OID were included in the Upgrade Project.

The project involved the following tasks;
Upgrade EBS from 12.0.6 to 12.1.3 rup5  (2 node app + 1 node db on asm )
Upgrade Oracle DB 11gr2 to psu9 (clustered)
Upgrade OID 10g to 11g (clustered)
Upgrade SSO to 10g to 10gR3 (clustered)
Integrating EBS and the new OID again.

There were 5 iterations for EBS upgrade and 2 iterations for OID,SSO upgrade-integration.











Consequently, we have completed the project successfuly.














Anyways, I have written a lot of posts during this project.. I have created and/or used several documents in the pipe line.
So,  In this article; I will try to collect them into one post to expose the documents that can be used in such a project scenario..

My Documents (Erman Arslan 's Oracle Blog):
You can find direct links to the articles below.

Oracle Support Documents :
Oracle Support Document Titles and Document ID's are below.
  • Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12 (Doc ID 454811.1)
  • Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12 (Doc ID 437878.1)
  • Using Latest Java 6.0 Update With Oracle E-Business Suite Release 12 (Doc ID 455492.1)
  • Upgrading Oracle Configuration Manager (OCM) within E-Business Suite Release 12.0 (Doc ID 727157.1)
  • Oracle E-Business Suite Release 12.1.1 Maintenance Pack Installation Instructions (Doc ID 752619.1) -> 'root document for EBS upgrade'
  • Database Preparation Guidelines for an E-Business Suite Release 12.1 Upgrade (Doc ID 761570.1)
  • Upgrade 12.0.6 To 12.1.1 Failed On Frmxml.odf with Duplicate Keys (Doc ID 1094034.1)
  • Patch Set Update and Critical Patch Update January 2013 Availability Document ID:1502461.1
  • Oracle E-Business Suite Releases 11i and 12 Critical Patch Update Knowledge Document ID: 1499376.1
  • Registering Oracle E-Business Suite Release 12 with Oracle Internet Directory 11gR1 and Single Sign-On [ID 1370938.1]
  • Preparing and Configuring Virtual Hosts on OracleAS 10g HTTP Server (Doc ID 293697.1)
  • Note:292380.1 How to Refresh Cache for OID Parameters
  • Purging Strategies for WebLogic Servers Log Files in Fusion Applications (Doc ID 1430436.1)
  • R12: Error "There are no patches that need to be applied from the patchset Oracle Application Server PatchSet 10.1.3.5.0" (Doc ID 1104363.1)
  • http://docs.oracle.com/cd/E12839_01/install.1111/e12002/sso_das.htm#CIHEGHIG -> 'root document for OID and SSO'
  • R12 EMEA Add-on Localizations - Turkey [ID 472686.1]

Monday, September 9, 2013

Being a skilled DBA or Web Developer is a good thing according to the 2013 Robert Half International Survey.

Oracle University : These days, being a skilled DBA or Web Developer is a good thing according to the 2013 Robert Half International Survey. http://s3.amazonaws.com/DBM/M3/2011/Downloads/SalaryGuide_RobertHalfTechnology_2013.pdf



EBS -- Patching Tips



 In this post, you can find my patching tips.These tips can be evaluated for Oracle EBS 11i and R12.. These tips are written according to my experiences gathered from various patching and upgrade projects I have done. I hope you will find these tips helpful...
  • Record your invalid objects, concurrent manager informations, notification mailer configurations and etc.
  • Check you tablespaces. Ensure they have enough free space.
  • Check you filesystem. Ensure you have enough free space.
  • Disable your custom login triggers.
  • Disable your password verify functions.
  • Stop your database jobs. 
  • Cancel your scheduled backup or maintanence jobs.
  • Analyze all the patches carefully and prepare a patch list with the columns.
    • . module name: The module that the patch is relevant.
    • . merge name : The name of the merge patch ( if you will merge the patch with the other patches)
    • . patch number : self explanatory
    • . prereq/actions: prereq patch numbers and prereq actions that need to be taken prior applying patch
    • . postreq/actions : postreq patch numbers and postreq actions that need to be taken prior applying patch
    • . Nls language patch: specify if NLS version of the patch is available.
    • . Description: Description of the patch elapsed time
    • . Problems&Solutions: If you encounter an error while applying the patch, note it here.. Write the solution also. Give details or link to the detailed documents.
    • . Reference Doc: The Oracle Support document number that points to this patch.. If it s prereq or postreg, write the relevant patch number here.
    • . Elapsed Time: Write down your timings for applying the patch. Consider the overhead of NLS language patches and manual actions too.
  • Download all the patches in to a folder and unzip them beforehand. It s better to have a seperate/specific foler for some patches(like db psu) , as some kind of patches have multiple directory structures.. (like two directories ...)
  • While applying the patches(especially the big ones), always work with a VNC server..
  • Merge the patches if you can.. Remember AD patches can be merged with AD patches only.. Altough it s not recommended, you can merge patches with their NLS patches.. You can do this if you dont have long downtime.. Otherwise; merge US patches and NLS patches seperately.
  • You dont need to create only one merged patch.. Analyze your stylesheet, look the manual actions. If a patch has important manual action, dont merge it with other patches. Create a merge till you come to that patch, and create another merge after that patch. 
  • By doing so, you can take your manual actions, and continue your process without question marks.
  • Analyze your system. Your actual cpu counts, your IO power, and decide adworker count that you will use for applying the patches.(Consider your operating system limits too.)
  • Dont use high parallelism for small patches..
  • Always monitor your archive log dest and tablespace sizes..
  • Always check you environment.. For example, if you are applying a patch using opatch for 10.1.3 home, set your environment to reflect 10.1.3 home.For adpatch you need to be on the default environment. Dont forget to take your environment back to the original, after you applied a patch on 10.1.3 Oracle home.
  • If you have custom partitions or custom modifications on seeded tables, you can encounter error during some patch applications. Control them carefully, and if you believe you can skip that particular work, make the adworker skip that failed job, and take manual actions later.(for example, if adworker can not create index because of custom subpartitions)
  • Use nocompiledb and noautoconfig options if needed.. Analyze and determine this kind of patches in your first iteration.. These options may save you significant time..
  • While applying some patches, you will find adsstats.sql running.. If running this sql takes a long time and prevent you to continue; kill the db session of the adworker and skip the job using adadmin 8th option.. (adsstats.sql: this sql is used for gathering the statistics for relevant tables..)
  • If you have multiple application nodes with Shared Apps filesystem, apply the patches from the admin node only, but if you see patch running autoconfig,manually run autoconfig on other nodes too..
  • Use the defaultsfile option of adpatch, if there are a lot of application patches that you need to apply. By using defaultsfile, you will save time and energy.
  • If you need to stop a patch application and adworkers are running; use adctrl to stop the patch application. In such a scenario, always check the database too.. By stopping a patch application, you can leave some worker sessions working in the database..Kill them too..
  • Always take backups when you reach the milestones..
  • When you finished you patching/upgrade process-> check the invalids and correct them taking necessary actions (apps compile, flexfiled compile and etc), check the file versions, check bug records, check application patch levels..
  • Once you ensure that everyting is okay, enable all the custom logon triggers and password profile functions, that you disabled before. Take any changes that you have done before the upgrade back to the original.
  • Before opening the applcation services, contact the necessary persons, and ask their approval to open the services. This is necessary for your own sake. EBS systems are mission critic systems, there are integrations and sensitive data on them.. Opening the services without controlling them functionally, can leave you in an irrecoverable state.. 
  • Before opening the services, you can take your important and unrestartable concurrent programs on hold. 
  • After you open the application services, check concurrent managers, notification mailers, oaf screens and forms screens.

These are the things that comes to my mind at this moment..
I will update this post from time to time.. 

You can add other statements too.. It will be helpful for all of us.

Oracle Fusion Middleware-- Log File Locations ( with OID component)

Oracle WebLogic Server DOMAIN_HOME/servers/server_name/logs/server-name-diagnostic.log
Oracle Application Development Framework DOMAIN_HOME/servers/server_name/logs/server-name-diagnostic.log
Oracle HTTP Server ORACLE_INSTANCE/diagnostics/logs/OHS/component_name/*.log
Fusion Middleware Control DOMAIN_HOME/sysman/log/emoms.log ,DOMAIN_HOME/sysman/log/emoms.trc
Oracle Internet Directory ORACLE_INSTANCE/diagnostics/logs/OID/component_name/oid*.log,
ORACLE_INSTANCE/diagnostics/logs/OID/tools/*.log
Oracle Virtual Directory ORACLE_INSTANCE/diagnostics/logs/OVD/component_name/diagnostic.log

EBS 12.0.6 to 12.1.3 upgrade -- an implemented patch list


This is an implemented patch list used in a Production Environment with 2 AIX application servers and1 AIX database server(ASM enabled). It s created, and optimized in 5 iterations.
The list was prepared to upgrade an EBS 12.0.6 environment to 12.1.3 (RUP5) ..
It also contains 11.2.0.2 database psu 9 and cpu upgrade actions.
Note that the patches can be different for your platform, your needs and the modules that you are using...

If you have any problems in applying these patches, feel free to contact me . I noted every step of these process and have a detailed roadmap and solutions for the problems you may face..

Before starting disable all your custom login triggers(if you have any) and disable your default password profile functions.


MODULE/Product







PATCH
Oracle AS 12965674
Oracle AS 14825718
Oracle AS 12887068
Oracle AS 12898423
Oracle AS 7140392 or 7140405
Oracle AS 14374587
Oracle AS 13367912
Oracle AS 7458104
Oracle AS 12943520
Oracle AS 14837539
Oracle AS 8339196
Oracle AS 14353879
Oracle AS j632redist.6.0.0.385.bin
Oracle AS cp ttf files
Oracle AS 12848228
Oracle AS cp -RH jdk
Oracle AS 13686664
Oracle AS 14029977
TechStack 13579837
Oracle AS N/A
DB NODE  jre632redist.6.0.0.385.bin
Oracle AS 7634476
Oracle AS 7315871
Oracle AS Homes & Database Homes 5567658
NA 9474320
Applications DBA(AD) 7461070
AOL 8764069(preinstall)
Oracle Deal Management 8601033(preinstall)
RDBMS 12677676
RDBMS 10160615
RDBMS 10229719
RDBMS 12400751
RDBMS 12942119
RDBMS 13001379
RDBMS 13004894
Report Manager 9539105(preinstall)
EBS -- cancel adsstats.sql if  needed  7303030
HR hrglobal.drv
Assets 8706842
Applications 7303031
TechStack 8919489
TechStack 9583541
TechStack 9852070
OAM 8609614
Workflow 8992873
Payroll 9241293
AD 9239089
Applications Updates (12.1.3 update pack) 9239090
Applications Updates (12.1.3 Onlie HELP update) 9239095
Applications Technology Family 9817770
AOL 9966055
AOL 11072961
HR 13418800
RDBMS 14841385
RDBMS 5912518
RDBMS 13705478
Applications Update (APPS CPU) 14782696
CE 14374740
ZX 14277162
FA 15961702
AD 9458153
Fed Financials.  13722747(preinstall mode )
FA 12810804
GL 13939904
GL 14173693
GL 14174724
GL 14172456
GL 14182969
GL 14172204
GL 6952457
GL 11895700
GL 12354478
OIE 10040337
OIE 14098047
Public Sector Applications 12359561
Payments 9756446
AP 14273383
Internet Procurement Enterprise Connector 13598894
AP 13563481
Payables 14466506
AP 13563489
IBY 14208483
AR 14309408
SLA 14284509
Application Framework 14185210
PRC 14254641
Purchasing 12625661
OPM Financials 13626800
Inventory Management 13020024
Inventory Management 13503144
Inv management 14586882
Bills of Material 15844605_TR
Payables 11711468
Fed Financials. 9000001
Fed Financials. 13045654
post req of 13045654 12764015
Bills of Material 14804487
Run Adstats on the background(if you canceled it in 12.1.1 upgrade phase) adstats.sql
Application Report Generator 15839147
Alerts 8674560 
HR 15887532
HR 15850405
Payroll 14666688
HR 14659075
HR 14592133
HR 14552465
HR 14584072
HR 14473460
OLM 16191536
OLM 16018618
OLM 16025845
OLM 14793946/16574224
OLM 15844605
OLM 14600292
Advanced Benefits 14023180
OLM 14134777
OLM 14588438
irecruitment 14750631
HR 14592612
Report manager 8668357
CLE 8640014
EMEA 10164748
AOL 8572732
EMEA 13853103
PON 14760282
Payables 13495766
Application Framework 9527712 redeploy forms if needed
Apply translation sync.

EBS -- REP-0004,REP-0069 java.lang.UnsatisfiedLinkError: net

Full Error stack is like below;

REP-0004: Warning: Unable to open user preference file.
REP-0069: Internal error
java.lang.UnsatisfiedLinkError: net (A file or directory in the path name does not exist.)

This error can be seen after 12.1.1 or 12.1.3 upgrade..
When you first look at the error messages; you may thhink that reports process can not open the user preference file due to a lack of permission or because of an environment problem..
But here is what happens;
look to the java error closely; it says java.lang.UnsatisfiedLinkError: net ,  it actually refers libnet.so here.. 
libnet.so is in java directory.. exactly in this subdirectory --> jre/lib/ppc

Check your  application environment; check your LD_LIBRARY_PATH. It should contain jre/lib/ppc ..

If LD_LIBRARY_PATH does not contain LD_LIBRARY_PATH ;
open the context file and append LD_LIBRARY_PATH with full_path_to_jre/jre/lib/pcc
Run autoconfig..
This action should fix the problem..

EBS-- Cannot Launch Multiple Forms Sessions After Registering with SSO Due to Lock on FND_USER_PREFERENCES

To resolve this error, you need to populate fnd_user_preferences with the necessary OID configuration values..

First, check your OID/SSO registration;

SELECT * FROM fnd_user_preferences
WHERE user_name=’#INTERNAL’ AND module_name= ‘OID_CONF’;

If no rows returned then it means your configuration is missing;
If there are few rows, check them, ensure all the configuration values are present.

Here is an example configuration;

PREFERENCE_NAME
PREFERENCE_VALUE
CREATE_BASE
cn=Users,dc=ofis,dc=erman,dc=com
CREATE_BASE_opt_mode
STATIC
DEFAULT_CREATE_BASE
cn=Users,dc=ofis,dc=erman,dc=com
DEFAULT_CREATE_BASE_opt_mode
STATIC
DEFAULT_REALM
dc=ofis,dc=erman,dc=com
DEFAULT_REALM_opt_mode
STATIC
FIXUP
NONE
FIXUP_opt_mode
STATIC
PLUGIN_VERSION
1.1
RDN
cn
RDN_opt_mode
STATIC
REALM
dc=ofis,dc=erman,dc=com
REALM_opt_mode
STATIC

If you find any missing configuration values;

Execute the following with Apps;
execute fnd_oid_plug.setPlugin;

This will populate your missing values;
Check  fnd_user_preferences again; this time you should see the missing values are present.

Check the following EBS profiles, and ensure they are set like below;

Applications SSO Type: SSWAw/SSO
Applications SSO Auto Link User: Enable
Applications SSO Login Types: Both
Application SSO LDAP Synchronization: Enable
Applications SSO Enable OID Identity Add Event: Enable
Link Applications user with OID user with same username: Enable

Now reregister you EBS to OID;

$FND_TOP/bin/txkrun.pl -script=SetSSOReg -deregister=yes

$FND_TOP/bin/txkrun.pl -script=SetSSOReg

Tested and verified. This should fix the problem..



Thursday, September 5, 2013

OS&Storage -- OS and Storage Alignment

Host file system need to be aligned with a Storage Lun, in order to achieve best performance.
This can be accomplished specifying the correct starting sector or logical block address (LBA) for the partition, and filesystem should be configured to use the same or multiple of the Storage block size..

On Linux , you can configure a partition and filesystem using fdisk (x option)

Alignment is important, and should be considered for Storage IO performance.Otherwise, you can end up with the following;

Misaligned filesystem and LUN:


It is almost equal -> doing 2 Storage IO for 1 OS IO..



Wednesday, September 4, 2013

OID-- Database --Unable to call fnd_ldap_wrapper ORA-20001: fnd_ldap_wrapper.create_user

This error can be seen in EBS and OID integration.

Suppose you integrated OID and EBS, and EBS logins through OID works perfectly. 
On the other hand; you encounter this error while creating an EBS user..

First of all to investigate further ; you need to open FND Debug and get the details about the error;

After enabling the debug option through the FND: Debug% Profiles, reproduce the issue and query the fnd_log_messages table to get the details..

In my case; the error was;
LDAP error : ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.

So it was obvious that ldap session can not be created,  It fails on Inıt.

If that 's the case;

To narrow down your scope, you can test creating a session directly from the EBS database.
Execute the following from the EBS database with the APPS user; (replcase oid_host and port with yours)

DECLARE
retval PLS_INTEGER;
my_session DBMS_LDAP.session;
BEGIN
my_session := DBMS_LDAP.init('oid host','port');
retval := DBMS_LDAP.open_ssl(session1, null, null, 1);
dbms_output.put_line('open_ssl returns: '|| to_char(retval));
retval := DBMS_LDAP.simple_bind_s(my_session,'cn=orcladmin','');
dbms_output.put_line('simple_bind_s returns: '|| to_char(retval));
retval := DBMS_LDAP.unbind_s(my_session);
dbms_output.put_line('unbind_s returns: '|| to_char(retval));
END;
/

If the above will fail with the same error, then you can say that the problem between the EBS database and OID server..
In my case; the problem was the firewall..
The oid port was not open between EBS database server and OID server ..
The OID server/port cannot be reached from the EBS database server. (use telnet to test; telnet oidhost oidport)
Solution: Security team opened the port between EBS database Server and OID server..