DBA in the Trenches

I've been noticing lately that the database does not seem to be cleaning up after itself and the processes just keep climbing and climbing. Some sessions are more than two weeks old. I thought this was a bit weird and when our monitoring system started alerting to the number of sessions I realised there was a lot of dead session that are inactive but not being cleaned up. This is probably due to pooling and the way the DB is trying to save resources but in the EBS once a user closes a form they will not be using that session again. This sql give me the code to kill the session both in the DB as well as on the server itself: select vp.SPID,vs.username, UPPER(vs.program) program, logon_time,         floor(vs.last_call_et / 60 ) min_inactive        ,floor(vs.last_call_et / 60 /60 ) hr_inactive, vs.status, vs.status        ,'alter system kill session '''||vs.sid||','||vs.serial#||''';'       ...

This is one of those where I'm left scratching my head. During the clone I was getting issues with the LOCAL_LISTENER parameter. The clone would not complete with this value set. Now when I go and create a new adop session using adop phase=prepare I get the following error [ERROR]     Cannot connect to database [ERROR]     Error Message: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (DBD ERROR: OCIServerAttach) I found a note to fix the issue and what does it say. I need to set the LOCAL_LISTENER. So I set the listener and now it runs. Again I'm lost for words with this new system of cloning and patching in R12.2 Reference adop phase=prepare fails with: Error Message : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (Doc ID 2007225.1)

To be able to edit framework (OAF) pages in R12 you need a few profile options set either at site but more realistically at user level. The following profiles should be set as follows for your user level values: FND: Personalization Region Link Enabled Personalize Self-Service Defn Disable Self-Service Personal After setting these above you'll need to log out completely and then re-enter the instance. In some instances I've seen that you need to bounce Apache or clean out cache from browser. So check those if you don't see the option immediately. Reference: Puneet Rajkumar

Database Tier Because this is a cold backup of the database only need to run dbTier and it will do the rest for you. Thanks to Arty for this little jewel. cd $ORACLE_HOME/appsutil/clone/bin perl adcfgclone.pl dbTier Application Tier Run File System In R12.2 it has some check on the oraInst file. Make sure its there with the valid locations. vi /etc/oraInst.loc inventory_loc=/u03/APPLTEST/oraInventory inst_group=dba Remove the oracle inventory directory that you reference in oraInst. You could deregister or start messing around with the xml file but I found it easier to simply delete the directory. rm -rf /u03/APPLTEST/oraInventory Check which is the run file system grep s_file_edition_type /u03/APPLTEST/TEST/fs1/EBSapps/comn/clone/context/apps/TEST_erpapprod.xml grep s_file_edition_type /u03/APPLTEST/TEST/fs2/EBSapps/comn/clone/context/apps/TEST_erpapprod.xml Because this is a snapshot of PROD I have to remove some of the files that aren't required. Oracle...

After aborting a adop session I ran into a bit of a problem with fs_clone. Log files weren't helping so I ran: adop phase=fs_clone loglevel=statement This is what I saw *******FATAL ERROR******* PROGRAM : (/oa01/oradata/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl) TIME    : Mon Jan  4 16:45:03 2016 FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ] MESSAGES: SQLPLUS error: buffer= SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jan 4 16:45:03 2016 Copyright (c) 1982, 2005, Oracle.  All rights reserved. SQL> SQL> Connected. SQL> SELECT clone_status||',' FROM ad_adop_session_patches WHERE status in ('R','F','N') AND bug_number = 'CLONE' AND node_name = 'oraapdev' AND adop_session_id =                                                                   ...

ADOP is in my opinion still pretty half baked. This is a critical tool that just isn't as intuitive as the old adpatch was. However we move with the times and get the hang of the new way of doing things. Along the way you might want to abort or cancel a patching cycling. With the two file systems and db editioning this can be a bit more to manage. I have learnt the hard way so you need to use the full cleanup after aborting a session. adop phase=abort adop phase=cleanup cleanup_mode=full

I needed to run a clone and was running into a really head scratcher. As is Oracle\'s way they sometimes enjoy giving us DBA's a bit of detective work to do. As usual I kept knocking my head into various brick walls instead of tracking back through the logs. Eventually I came up with the problem Start to configure the run filesystem. You can do a dualfs from AD-TXK Delta 7 but for now I just needed to get this up and running: perl adcfgclone.pl appsTier START: Creating FMW Home. Running /u03/APPLUAT/UAT/fs1/EBSapps/comn/clone/FMW/ pasteBinary.sh -javaHome /u03/APPLUAT/UAT/fs1/EBSapps/comn/util/jdk64 -al /u03/APPLUAT/UAT/fs1/EBSapps/comn/clone/FMW/FMW_Home.jar -tl /u03/APPLUAT/UAT/fs1/FMW_Home -invPtrLoc /etc/oraInst.loc -ldl /u03/APPLUAT/UAT/fs1/inst/apps/UAT_erpapp05/admin/log/clone/fmwT2PApply -silent true -debug true -executeSysPrereqs false Script Executed in 21933 milliseconds, returning status 255 ERROR: Script failed, exit code 255 Oracle has left me a cryptic ...

When opening a form the Java Security pop shows but then freezes and you can't continue. The only way to close the form is via Task Manager. Solution Control panel > Java > Advanced > Java Plugin > [Disable] Enable the Next Generation Java Plug-in. Reference: ORACLE FORMS – THE APPLICATION’S DIGITAL SIGNATURE HAS AN ERROR – YOUR FORMS APPLICATION IS FOZEN

In accessing R12 applications, it throws the security warning message "The application's digital signature cannot be verified" In some of the client machine once this warning message pops-up, their IE session is getting hanged. Only option is to kill the session from task manager. This is creating problems with application users and hence provide us a solution to get rid of this warning message.  Solution Control panel > Java > Advanced > Security > Disable verification Reference:  Bug 11847386 : NEED TO SUPRESS R12 HANGING WARNING MESSAGE

During upgrade a user ran into this issue on a cloned instance lp: Forbidden Pasta: Error: Print failed. Command=lp -c -dwms_104  /u03/APPLYES/YES/fs_ne/inst/YES_erpapyes/logs/appl/conc/out/MLM_WMS_LABEL_48805775_1.PDF Pasta: Error: Check printCommand/ntPrintCommand in pasta.cfg Pasta: Error: Preprocess or Print command failed!!! APP-FND-00500: AFPPRN received a return code of failure from routine FDUPRN. Program exited with status 1 Check the CUPS configuration file to see what the group is for running [root@oraaptest ~]# vi /etc/cups/cupsd.conf # Administrator user group... SystemGroup lp Check the users in the group for lp and add the user to the group [root@oraaptest ~]# vi /etc/group lp:x:15:daemonm,appltest Restart cups and retest the issue [root@oraaptest ~]# service cups restart Stopping cups:                                             [...

First create a decruption function that will decrypt the password. create or replace function xx_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'; Create a view used the decryption function: create or replace view xx_user_pass as SELECT user_name,       (SELECT xx_decrypt(UPPER(                               (SELECT (SELECT xx_decrypt(UPPER((SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual)),usertable.encrypted_foundation_password) FROM dual) AS apps_password                               FROM fnd_user usertable                               WHERE usertable.user_name LIKE   ...

Responsibility: System Administrator or Similar Navigation: Profile > System Profile Name: “Self Service Personal Home Page mode” Options: Framework only Framework Simplified Framework Tree No Homepage Reference: How to Change Homepage   Steven Chan

In R12.2 ETCC is used to check for bug fixes on the DB and Middle Tier. It is required to run the ETCC script on the database tier after each clone as adop check's this as one of its preliminary check's so you may as well keep this one up to date. Lastest version can be found at 17537119 . Prepare the files to run them mkdir /stage/install/ETCC chmod 777 /stage/install/ETCC cd /stage/install/ETCC unzip p17537119_R12_GENERIC.zip -d /stage/install/ETCC chmod a+x /stage/install/ETCC/* Run the script on each of the tier's that the services are running on: Database Tier: echo $CONTEXT_FILE | /stage/install/ETCC/checkMTpatch.sh  Middle Tier: echo $CONTEXT_FILE | /stage/install/ETCC/checkDBpatch.sh  Reference:  How To Run The 12.2 EBS Technology Code Level Checker (ETCC) ? (Doc ID 2008451.1) 12.2 E-Business Suite - How to identify patches for listed ETCC missing database and middle tier bugs (Doc ID 2057925.1)

Mount old file system in new server so that you can copy the files over. Replace $[OLD_APPLCSF] ,$[NEW_APPLCSF] and [PROD] with your local settings rsync -a $[OLD_APPLCSF]/log/ $[NEW_APPLCSF]/log/ rsync -a $[OLD_APPLCSF]/out/ $[NEW_APPLCSF]/out/ Need to update the concurrent request table that stores the log file and out file locations update APPLSYS.FND_CONCURRENT_REQUESTS fcr set fcr.LOGFILE_NAME = replace  (FILE_NAME,'$[OLD_APPLCSF]','$[NEW_APPLCSF]') ,fcr.LOGFILE_NODE_NAME= '[PROD]' ,fcr.OUTFILE_NAME = replace  (OUTFILE_NAME,'$[OLD_APPLCSF]','$[NEW_APPLCSF]') ,fcr.outFILE_NODE_NAME= '[PROD]'; For Bi Publisher there is also the additional file that is created and this needs to be updated in FND_CONC_REQ_OUTPUTS. Sneaky one that I later found. update APPLSYS.FND_CONC_REQ_OUTPUTS set FILE_NAME = replace  (FILE_NAME,'$[OLD_APPLCSF]','$[NEW_APPLCSF]') ,FILE_NODE_NAME = '[PROD]';

With R12.2 you now have two file systems (fs): Run and Patch. Both actually being self explanatory. The run fs is where the current instance is being run from ($RUN_BASE) and patch fs is what adop uses to apply patches to before cutting over to use as the run fs ($PATCH_BASE). So one of the issues possibly for developers is that upon opening a ssh session they will need to source the run files system or even for DBA work you normally use the run fs unless specific work is needed on the patch fs. So how too make it easier than sourcing EBSapps.env each time and then entering either Run or Patch.  This is what is needed: source /u01/oracle/EBSapps.env run Now you could call this ever time you log into the instance or you could add it to $HOME/.bash_profile This way you will not have to worry about it and the person logging in will be using the run fs where most work is needed. Reference: How To Automatically Set the Current Run or Patch Edition / File System ...

When trying to setup the Workflow Mailer you might get this above error. It basically means that you are connecting to the mail server but not necessarily to the IMAP server. You need to ask your Mail Admin to give you the IMAP server and not the normal mail server. Also make sure that the account has IMAP access as this may be turned off by default