Cheat sheet for Apps DBA!

Search FND_PROFILES for Hard-Coded Path
As part of Windows to Linux Upgrade/Migrate project. I wrote the following query to pull all the profiles that has a hard-coded Windows Path

-- Start Script

SELECT fpo.profile_option_name PROFILE, fpov.profile_option_value VALUE,
DECODE (fpov.level_id,10001, 'SITE', 10002, 'APPLICATION',10003, 'RESPONSIBILITY',10004, 'USER') "LEVEL",
fa.application_short_name app, fr.responsibility_name responsibility,
fu.user_name "USER"
FROM apps.fnd_profile_option_values fpov,
apps.fnd_profile_options fpo,
apps.fnd_application fa,
apps.fnd_responsibility_vl fr,
apps.fnd_user fu,
apps.fnd_logins fl
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fa.application_id(+) = fpov.level_value
AND fr.application_id(+) = fpov.level_value_application_id
AND fr.responsibility_id(+) = fpov.level_value
AND fu.user_id(+) = fpov.level_value
AND fl.login_id(+) = fpov.last_update_login
AND ( LOWER (fpov.profile_option_value) LIKE 'f:%'
OR LOWER (fpov.profile_option_value) LIKE 'd:%'
OR LOWER (fpov.profile_option_value) LIKE 'c:%'
)
ORDER BY 2

-- End Script
Posted by Shyam Enuganti at 9:49 PM 0 comments
Search LONG Column for a string
I am working on Windows to Linux upgrade project. As part of it I was looking for Hard-Coded Paths in Profiles, Packages, Procedures, Functions, Views and Triggers.

Searching for the hard-coded path in DBA_VIEWS and DBA_TRIGGERS posed some challenges. So described is the SQL which I used created to search DBA_VIEWS, same thing can be modified for DBA_TRIGGERS.

(Gave up with formating the code)

-- START Script
SELECT 'Views containing WINDOWS path' WINPATH FROM DUAL;

SET SERVEROUTPUT ON

DECLARE
/* This program will retrive the Hard-coded WINDOWS path used in dba_views; The same program can be used to search in dba_triggers by changing SELECT clause. This script can also be used to search a LONG datatype column by changing required clauses.
*/
v_path varchar2(10);
p_path varchar2(10) := ' ';
v_view dba_views.view_name%TYPE;
v_own dba_views.owner%TYPE;

-- For search in long datatype
cursor1 INTEGER;
ignore INTEGER;
STATEMENT VARCHAR2 (1000);
out_val VARCHAR2 (1000);
out_length INTEGER;
num_bytes INTEGER := 1000;
offset INTEGER;
num_fetches INTEGER;
row_count INTEGER;
out_view VARCHAR2 (30);
out_own VARCHAR2 (30);

CURSOR c1
IS
SELECT 'F:' winpath FROM dual Union
SELECT 'C:' winpath FROM dual Union
SELECT 'D:' winpath FROM dual;

BEGIN
BEGIN
FOR c1_rec IN c1
LOOP
v_path := c1_rec.winpath;

--Search in TRIGGER Body
BEGIN
STATEMENT := 'select text ,view_name,owner from dba_views where owner not in (''SYS'',''SYSTEM'')';
cursor1 := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cursor1, STATEMENT, DBMS_SQL.native);
DBMS_SQL.define_column_long (cursor1, 1);
DBMS_SQL.define_column (cursor1, 2, v_view, 30);
DBMS_SQL.define_column (cursor1, 3, v_own, 30);
ignore := DBMS_SQL.EXECUTE (cursor1);

LOOP
IF DBMS_SQL.fetch_rows (cursor1) > 0
THEN
row_count := row_count + 1;
offset := 0; num_fetches := 1;
DBMS_SQL.COLUMN_VALUE (cursor1, 2, out_view);
DBMS_SQL.COLUMN_VALUE (cursor1, 3, out_own);

LOOP
DBMS_SQL.column_value_long (cursor1, 1, num_bytes, offset, out_val,out_length );

IF (INSTR (UPPER (out_val), v_path) > 0)
THEN
IF (p_path = v_path)
THEN
NULL;
ELSE
DBMS_OUTPUT.put_line ( 'Windows path '
|| v_path
|| ' found in view '
|| out_own
|| '.'
|| out_view
);
p_path := v_path;
END IF;

offset := offset + num_bytes;
num_fetches := num_fetches + 1;
ELSE
EXIT;
END IF;

IF out_length < num_bytes
THEN
EXIT;
END IF;
END LOOP;
ELSE
EXIT;
END IF;
END LOOP;

DBMS_SQL.close_cursor (cursor1);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);

IF DBMS_SQL.is_open (cursor1)
THEN
DBMS_SQL.close_cursor (cursor1);
END IF;
END;
END LOOP; -- End of c1_rec loop
END;
END;
/


--END Script
Posted by Shyam Enuganti at 9:30 PM 0 comments
Labels: dba_triggers, dba_view, long, search long
Tuesday, September 26, 2006
UPGRADING wanna reduce DOWNTIME by couple of Hours!
In May we upgraded from 11.5.9 to 11.5.10.2. It took only 18 Hrs for Patching. (800+GB Database, 3 Nodes with 6 NLS languages, followed STAGED Patching). Never heard of STAGED Patching then check Metalink Note:242480.1

During our practice builds we were looking for time consuming jobs. And the following 2 jobs took couple of Hours. By following the below PRE-TASKS we saved couple of Hours and 60+GB of disk space.


Pre Task FIX for Job ICXDLTMP.sql
******
truncate table icx.icx_sessions;
truncate table icx.icx_session_attributes;
truncate table icx.icx_text;
truncate table icx.icx_requisitioner_info;
truncate table icx.icx_transactions;

begin
fnd_bc4j_cleanup_pkg.delete_transaction_rows(SYSDATE - 4/24);
fnd_bc4j_cleanup_pkg.delete_control_rows(SYSDATE - 4/24);
end;
/
******
Pre Task FIX for Job afupdfmt.sql
******
update applsys.fnd_lobs set FILE_FORMAT = 'IGNORE'
where nvl(program_name,'attach') != 'FND_HELP';

sql> @$FND_TOP/sql/aflobbld.sql APPLSYS APPS
******

Bump the values of ADJREOPTS & ADJRIOPTS tags to atlease 512MB or 1GB in contextfile "XML file" and run ADCONFIG - During patching ADJAVA cmd will load XML Files, Jar Files into DB and setting these to higher values will reduce the downtime.


Good Luck and Cheers!


Insight into AutoConfig
I am going to give you some insight or inner working of this tool. Thanks to Harminder Singh (Try at your own risk)

For detailed information on AUTOCONFIG refer to the following metalink Note:165195.1, Note:218089.1, Note:270519.1 and Note:217368.1.

Wanna create CONTEXT file manually, try this ... and then copy the generated XML to $APPL_TOP/admin/host_SID.xml
adbldxml.pl tier=apps appsuser=apps appspass=pswd log=$HOME/admin/log/adconfig_`date '+%m%d%y_%H%M%S'`.log out=$HOME/admin/SID_`date '+%m%d%y_%H%M%S'`.xml servername=nodename

Wanna Update the tags in CONTEXT File, try this to update tag s_appsEnvName to SHYAM
java -classpath "${CLASSPATH}:${CMDDIR}/java/adconfig.zip" oracle.apps.ad.context.UpdateContext ${HOME}/admin/SID.xml s_appsEnvName "SHYAM"

Wanna try to instantiate configuration files from custom driver and templates files based on the values from CONTEXT FILE.
java -classpath "${CLASSPATH}:${CMDDIR}/java/adconfig.zip" oracle.apps.ad.autoconfig.InstantiateFile -e $HOME/admin/${TWO_TASK}.xml -d /d01/sid/admin/custom.drv -log /d01/sid/admin/$CONTEXT_NAME.xxcinstantiate.log -bacdir /d01/sid/admin/bak/$CONTEXT_NAME -pwd NOP




Check if Concurent Managers can start without really Starting!

Do you want to know, without starting Concurrent managers if they can start without any issues. I personally ran into this issue many times both in the single node and in multi-node (Parallel Concurrent Processing) Solaris environments.

The following command can be used to check if the Concurrent Managers can be started without really starting. If it comes up with " Internal heap Dump" then there will be no issues, but if it core dumps or hangs then there is some issue with LISTENER. Try stopping listener it hangs, only way is to kill the process.

sqlplus apps/pswd@FNDSM_NODE1_SID
sqlplus apps/pswd@FNDSM_NODE2_SID
sqlplus apps/pswd@FNDSM_NODE3_SID

When we are on 11.5.9 I had to really push Oracle to fix bug #3617132. In the manager log file you normally see the following error "CONC-SM TNS FAIL". I even saw the same issue in 11.5.10 instance also.

Only way is modifying the listener.ora on the appsTier OR start listener with debug mode enabled.
APPS_SID =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= TCP)(Host= hostname)(Port= 1522))
(ADDRESS= (PROTOCOL= IPC)(Host= hostname)(Port= 15221)(KEY=SIDKEY1))
(ADDRESS= (PROTOCOL= IPC)(Host= hostname)(Port= 15222)(KEY=SIDKEY2))
(ADDRESS= (PROTOCOL= IPC)(Host= hostname)(Port= 15223)(KEY=SIDKEY3))
)

Need more details, shoot me an email

Good Luck and Cheers!


For detailed discussion about SSLConfigTool please refer to Oracle Doc

If you are installing Infrastructure in a cluster environment you have an option to enable SSL during installation. In Non-Cluster environment you have to enable SSL using manual method or by using SSLConfigTool(Introduced in 10.1.2.)
Where as on the Middle Tier you don't have an option to install in SSL mode. We have to use SSLConfigTool to enable SSL (For single node or multi node)

Enabling SSL manually is bit tricky and also takes lot of time. Using this tool it can be enabled in less than 5 min.

The SSLConfigTool command is used as follows:

SSLConfigTool ( -config_w_prompt
| -config_w_file
| -config_w_default
| -rollback )
[-dry_run]
[-wc_for_infra]
[-secure_admin]
[-opwd ]
[-ptl_dad ]
[-ptl_inv_pwd ]

Commands I used to Enable Rollback SSL.

On Infrastructure Tier:

SSLConfigTool -config_w_prompt -opwd orcladminpswd

On Middle Tier:
1) SSLConfigTool -config_w_prompt -opwd orcladminpswd -ptl_inv_pwd orcladminpswd
2) Go to Webcacheadmin and change the settings in Site Definitions (Make SSL site Default) & in Site-to-Server Mapping change the Origin Server to to use SSL Port
To Rollback:
SSLConfigTool -rollback -opwd orcladminpswd


Good Luck and Cheers!

Convert openssl to oracle wallet certificate Oracle Wallet certificate is different from openssl certificate. If you already have a openssl certificate for yourcompany.com and want to use the same certificate for 10gAS then follow the steps to convert openssl certificate to Oracle Wallet certificate. Thanks to my colleague Jim Kennedy he has put the steps together.

For detailed explanation refer to metalink Note:340178.1

1) Copy the following files to a directory (e.g /var/opt/oracle/walletcert)

server.crt = Standard server certificate (received from Verisign)
server.key = Standard key (generated with openssl - 1024.key)
ca.crt = Combination of intermediate certificate and root certificate concatenated


2) Generate wallet certificate

$IAS_HOME/Apache/open_ssl/bin/openssl pkcs12 -export -descert -in server.crt -inkey server.key -certfile ca.crt -name server_wallet.crt(can be anyname) -out ewallet.p12


3) Copy/ftp the walletcert directory to the server where 10gAS is installed.

4) Open and save the certificate using owm executable.

Good Luck and Cheers
Posted by Shyam Enuganti at 3:51 PM 3 comments
Wanna DUMP JVM threads or check GC size
This scripts will be handy if you wanna check the Garbage Collection Size of OACore JVMs or DUMP them to check for any locking/waiting issue


#
# scripts Check the Garbage Collection Size of OACore JVMs
#
echo " GC size This one -| on "`date`
echo " V"
for i in `ls -1 $HOME/ora/iAS/Apache/Jserv/logs/jvm/OA*out`;do
which_jproces=`fuser $i 2>/dev/null `
which_jvm=`basename $i|awk -F\. '{print $2}'`
echo "Jvm # $which_jvm running on pid$which_jproces" "\c"; tail -1 $i
done|sort +9 -n

#
# scripts to dump OACore JVMs
#

for pids in `ps -fu $LOGNAME|grep -i verbose|grep -v grep|awk '{print $2}'`;do
echo "Running 3 samples 5 sec apart for pids : $pids "
(kill -3 $pids ; sleep 5 ; kill -3 $pids ; sleep 5 ; kill -3 $pids ) &
done


JVM & OC4J Size in 11i Apps & 10gAS The default values for the JVM/OC4J do not suffice and are not ideal for performance, these values are typically too low and may not be applicable for medium or large scale applications.

After lot of internal discussions and with Oracle Support opinion we had sized our JVM and OC4J. These values are set in our environment for Oracle Apps running on 11.5.10.2 and 10gAS 10.1.2.0.2

In jserv.properties

wrapper.bin.parameters=-verbose:gc -Xmx1024M -Xms256M -XX:MaxPermSize=256M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+PrintClassHistogram -XX:-UseParallelGC


In jserv.conf (By default only 1 JVM is started, so edit or change the value in Context file and run autoconfig)

ApJServGroup OACoreGroup 8 1 /d01/sid/ora/iAS/Apache/Jserv/etc/jserv.properties



In 10gAS for Portal on Middle Tier set the value by editing in opmn.xml or using enterprise-manager console. The values has to be set for java-options and numprocs. Ref:Oracle Doc

The following values need to be editied in OC4J_Portal section. It starts with " process-type id="OC4J_Portal" module-id="OC4J"

"-server -Dnetworkaddress.cache.ttl=30 -Djava.security.policy=/d01/sid/as10g/j2ee/OC4J_Portal/config/java2.policy -Djava.awt.headless=true -Xms512m -Xmx1024m"
...
process-set id="default_island" numprocs="4"