Allappsdba Blog....Powered by DOYENSYS

Non ASM to ASM Conversion

2012-01-27T20:30:00.002+05:30

Before Proceeding to process, please know what is ASM and how to use it.

1. Set the ASM environment and create diskgroup
oracle@AIX01]. oraenv
ORACLE_SID] +ASM1

sqlplus /as sysasm

sql> Create diskgroup DATA EXTERNAL REDUNDANCY DISK '/dev/rhdiskpower47','/dev/rhdiskpower48';

2. set the db environment and shutdown the database

oracle@AIX01]. oraenv
ORACLE_SID] ORCL

oracle@AIX01] sqlplus '/as sysdba'
sql> shu immediate

3. Make the database to nomount state through RMAN
$ Rman target /
RMAN>STARTUP NOMOUNT

4. move controlfile to asm through RMAN
RMAN> restore controlfile to ‘+DATA’ from ‘/oracledb/control1/ctrl_1.ctl’;

5. set the asm environment and find the controlfile from asm diskgroup
$ . oraenv
+ASM1
$ asmcmd
ASMCMD> find --type CONTROLFILE +DATA *
+DATA/ORCL/CONTROLFILE/current.256.734177857
ASMCMD>exit
--Copy the name and full path of controlfile

6. change the control_files parameter from non-asm to asm and restart the database post the db parameter changes.

SQL> alter system set control_files='+DATA/ORCL/CONTROLFILE/current.256.734727317' scope=spfile;
SQL>shutdown immediate;
SQL>startup mount;

7. Now check the control file
SQL> select name from v$controlfile;
+DATA/ORCL/CONTROLFILE/current.256.734727317

8. Now moving the Datafiles to ASM

. oraenv
ORCL

$ rman target /
RMAN>configure device type disk parallelism 4;
RMAN> backup as copy database format '+DATA';
RMAN>switch database to copy ;
RMAN> ALTER DATABASE OPEN;

9. Connect to sqlplus as sysdba and check for all data files

SQL> select file_name from dba_data_files;

10. Now move the Redo Log files to ASM

Please check for which logfile group is active and proceed with caution
NOTE :In this case Logfile group 6 is in active/current state so switching logfile so that it becomes inactive.
SQL> alter database drop logfile group 1;
SQL> alter database add logfile group 1 ('+DATA) size 300M;
SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2 ('+DATA') size 300M;
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 ('+DATA') size 300M;

11. Move SPfile to ASM
Connect to sqlplus as sysdba
Sqlplus “ / as sysdba”
SQL> create pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpfile.ora' from spfile;
SQL> shutdown immediate;
Edit the pfile to change the data file creation place
*.db_create_file_dest=+DATA
SQL>startup pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpfile.ora';
SQL> CREATE SPFILE='+DATA' FROM PFILE='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpfile.ora';

12. find the spfile and Modify the pfile point to spfile
. oraenv
+ASM1

$asmcmd
ASMCMD> find --type parameterfile +DATA *
+DATA/ORCL/PARAMETERFILE/spfile.354.734189649

.oraenv
ORCL

##remove all entries and add the spfile parameter
$cd $ORACLE_HOME/dbs
vi initpfile.ora
spfile='+DATA/ORCL/PARAMETERFILE/spfile.354.734189649'

13. startup with pfile
sqlplus '/as sysdba'
shu immediate
startup pfile=?/dbs/initpfile.ora

14. moving/creating temporary tablespace in ASM

Creating TEMPFILEs In ASM

SQL> select name, bytes from v$tempfile;
SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;
SQL> alter database default temporary tablespace temp1;
SQL> drop tablespace temp including contents;
SQL> create temporary tablespace temp tempfile SIZE 10120M extent management local uniform size 1M;
SQL> alter database default temporary tablespace temp;
SQL> drop tablespace temp1 including contents;
SQL> select name from v$tempfile;

ACL Creation and Access in 11g

2012-01-27T19:58:00.004+05:30

1. To Drop ACL

exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL('utlmailpkg.xml');

2. Create and Assign ACL TO DBUSER and Assign it all host.
-- BELOW CODE WILL CREATE NETWORK_SERVICES.xml ACL file and REQUESTS DBUSER AS INPUT, TO WHICH ACL ACCESS NEEDS TO BE PROVIDED

BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL

( acl =>'NETWORK_SERVICES.xml',

description => 'ACL Access',

principal => '&&DBUSER',

is_grant => true,

privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE

( acl => 'NETWORK_SERVICES.xml',

principal => '&DBUSER',

is_grant => true,

privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL

( acl => 'NETWORK_SERVICES.xml',

host => '*');

COMMIT;

END;

3. Adding ACL privilege to ANOTHER DBUSER
-- BELOW CODE WILL REQUEST FOR ANOTHER DBUSER NAME TO WHICH ACL ACCESS NEEDS TO BE PROVIDED

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'NETWORK_SERVICES.xml', principal => '&&ANOTHERDBUSER', is_grant => true, privilege => 'connect');

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'NETWORK_SERVICES.xml', principal => '&ANOTHERDBUSER', is_grant => true, privilege => 'resolve');

4. smtp_out_server parameter settings
alter system set smtp_out_server='localhost:25' scope=BOTH;

5. To Test the ACL Access
conn DBUSER/
SQL> select utl_http.request('http://www.oracle.com') from dual;

UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM')
--------------------------------------------------------------------------------
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

--------------------------------------------------------------------------------
var ssUrlPrefix = "/us/"; var ssUrlType = "2"; var g_navNode_Path = new Array(); g_navNode_Path[0] = '8'; var g_ssSourceNodeId = "8"; var g_ssSourceSiteId = "ocomen";

UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM')
--------------------------------------------------------------------------------

6. it should provide the above result,

if it is failure, then it throws
ORA-24247: network access denied by access control list (ACL)

7. In case you get ORA-29278: SMTP transient error: 421 Service not available.
The error shows that the smtp port (25) is not accessible.

( as per parameter smtp_out_server=localhost:25 )
When you do telnet 25 port , it will throw connection refused error..

[prod@linux dbhome_1]$ telnet localhost 25

Trying 127.0.0.1...telnet: connect to address 127.0.0.1: Connection refused

telnet: Unable to connect to remote host: Connection refused

IF that is the case Then, For linux ,please start sendmail service, from root
[root@linux ~]# service sendmail start
Once it is started you should be able get response from telnet and ORA-29278 error will be recovered
[prod@linux dbhome_1]$ telnet localhost 25

Trying 127.0.0.1...Connected to linux.mydb.com (127.0.0.1).

Escape character is '^]'.

220 linux.mydb.com ESMTP Sendmail 8.13.1/8.13.1; Thu, 26 Jan 2012 07:10:21 -0800

Enabling HTTP Header Trace from Firefox

2012-01-27T16:57:00.002+05:30

Live HTTP headers is a public add-in for Mozilla browsers that captures HTTP information during navigation, including URLs, cookies, and other headers. Information gathered using this tool can be saved to a file, ready for upload to Oracle Support.

LiveHTTP headers is available here as an xpi, under the installation tab: http://livehttpheaders.mozdev.org/

a:- Install the downloaded executable
b:- Open a new Mozilla Firefox session c:- Enable LiveHTTPheaders by choosing "Tools / LiveHTTPHeaders".

This will bring up a Window of HTTP header data that will accumulate as you navigate from place to place.

d:- Replicate the problem.

e:- Click the "Save All" button , Analyse the issue / upload the file created to service request.

Enabling HTTP Header Trace from Internet Explorer

2012-01-27T16:38:00.003+05:30

ieHTTPHeaders is an Explorer Bar for Microsoft Internet Explorer that will display the HTTP Headers sent and received by Internet Explorer as you surf the web. It can be useful in debugging various web-development problems related to cookies, caching, redirection, proxy servers, etc. It is nicely integrated with Microsoft Internet Explorer so you'll be able to see the HTTP headers as they are sent and received from the web server, without any need to fire up an extra tool set. Information gathered using this tool can be saved to a file, ready for upload to Oracle Support.

Please download ieHTTPHeaders from here: http://www.blunck.se/iehttpheaders.html

a:- Install the downloaded executable in the Client PC

b:- Open a new Internet Explorer session

c:- Activate the IEHTTPHeader window by selecting "View/Explorer Bar/ieHTTPHeaders"
From your Internet Explorer menu bar. This will bring up a Window of HTTP header data that will accumulate as you navigate from place to place.

d:- Replicate the problem.

e:- Click the "HEADERS VIEW" tab in the ieHTTPHeaders window

f:- Right click the "HEADERS VIEW" page and choose save

e:- Analyse the Issue/Upload the file to SR.

Enabling IAS debug in R12 Applications

2012-01-27T16:29:00.003+05:30

1. cd $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config
1. vi oc4j.properties
2. Add: values
AFLOG_ENABLED=true
AFLOG_LEVEL=statement
AFLOG_MODULE=%
AFLOG_FILENAME=/tmp/1.log
2. Copy the $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf to a backup file.
3. Edit the $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf and change Apache to run in debug.
Example:
=======
# LogLevel: Control the number of messages logged to the error_log.
# Possible values include: debug, info, notice, warn, error, crit, # alert, emerg.
#
LogLevel debug
=======
4. Restart the Apache and OACore service ($ADMIN_SCRIPTS_HOME/adoacorectl.sh).
5. The following logs will be generated
->$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
->$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/log.xml
->$APPLRGF/javacache.log
->$LOG_HOME/ora/10.1.3/Apache/access_log
->$LOG_HOME/ora/10.1.3/Apache/error_log
->$LOG_HOME/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.err
->$LOG_HOME/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.out
->/tmp/1.log

Procedure for generating the DDL statements of the database objects.

2012-01-18T23:08:00.001+05:30

There are two parameters used in the specified object,

1. Object Type        ==> eg: FUNCTION,PROCEDURE,PACKAGE,TABLE,VIEW etc.,
2. Directory Name ==> Specify the directory name where the output to be stored.

Usage - DS_GET_DDL_STATEMENT(<Object type>,<Directory Name>)

Objects to be created:
----------------------
CREATE TABLE "SMF_APEXRPS"."DS_ERROR_TABLE"
   (    "COMNAME" VARCHAR2(200 BYTE),
    "COMDDDL" LONG
   );

Create or Replace
PROCEDURE DS_GET_DDL_STATEMENT(P_OBJECTTYPE VARCHAR2,PDIR VARCHAR2)
AS
V_DDL LONG;
V_ERRMSG VARCHAR2(500);
vInHandle utl_file.file_type;
CURSOR C1 IS
SELECT * FROM USER_OBJECTS WHERE object_type=P_OBJECTTYPE;
BEGIN
FOR I IN C1 LOOP
select
dbms_metadata.get_ddl(P_OBJECTTYPE,I.OBJECT_NAME) INTO V_DDL
from dual;
vInHandle := utl_file.fopen(PDIR, I.OBJECT_NAME||'.txt', 'W');
IF utl_file.is_open(vInHandle) THEN
   utl_file.put_line(vInHandle, V_DDL, FALSE);
    utl_file.fflush(vInHandle);
    utl_file.fclose_all;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
V_ERRMSG:=SQLERRM;
INSERT INTO DS_ERROR_TABLE VALUES('ERROR',V_ERRMSG);
END;

How to Tell if the Oracle Software is 32-Bit or 64-Bit

2012-01-06T00:26:00.001+05:30

How to find if the Oracle Client Software is 32-Bit or 64-Bit ?

here you can have some ways to find it.

You can find whether Oracle Client Software is 32-Bit or 64-Bit by using the following :

1.Check the sqlplus binary

% file $ORACLE_HOME/bin/sqlplus

64-bit will show 64-bit.
32-bit will show 32-bit or not specify wordsize

2.Check for the directories :

$ORACLE_HOME/lib32
$ORACLE_HOME/lib

% ls -l $ORACLE_HOME/lib32
% ls -l $ORACLE_HOME/lib

If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib exist, then it is 64 bit client.
If you have only $ORACLE_HOME/lib you need to use method 1 as there are client versions (11.2)where $ORACLE_HOME/lib32 directory does not exist on 64-bit client installations.

Refernce:
How To Find If The Oracle Client Software Is 32-Bit Or 64-Bit on UNIX platforms [ID 434295.1]

NOTE:119707.1 - How to Tell if the Oracle Software is 32-Bit or 64-Bit

DATA GUARD LOG SHIPPING FAILS WITH ERROR ORA-16191 IN 11G

2012-01-06T00:17:00.000+05:30

Issue:

PING[ARC1]: Heartbeat failed to connect to standby ‘STANDBY’. Error is 16191.
Sat Aug 06 02:06:52 2011
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191

Things Verified :

1. Create password file for sys in both primary and standby
2. Login as a sys from primary to standby and vice versa
Sqlplus sys@STANDBY as sysdba
3. tnsping also works from primary to standby and vice versa

Solution :

In 11g oracle comes with the strong password authentication, so we have to use ignorecase parameter while creating password file in both primary and standby, otherwise we have to set SEC_CASE_SENSITIVE_LOGON=FALSE (Dynamic parameter).

1. orapwd file=orapw$ORACLE_SID password=***** entries=5 ignorecase=Y
2. alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=both;

REP-3000: Internal error starting Oracle Toolkit

2012-01-06T00:12:00.001+05:30

Issue:

Recently we had issue with Concurrent requests which having print format of PDF or XML, and the exact
error is "REP-3000: Internal error starting Oracle Toolkit"

Solution:
1. Log on as root on the UNIX box.
2. Start The VNC server:
Example:
$ vncserver :0
3. Set the DISPLAY environment variable for Oracle Reports by performing the
following steps:
a. $ set DISPLAY=hostname:0; export DISPLAY
b. $ xhost +
c. Set the DISPLAY parameter in adcmctl.sh, adrepctl.sh, adfrmctl.sh and gsmstart.sh to
hostname:0
d. Restart concurrent manager and report server

How to Verify:
1. Login with system administrator responsibility
2. Concurrent – program-Define-Query Active Users-Change Print format to PDF- save
3. Run the Active Users Concurrent Request
4. It should be completed without any issue.

Provides a report on the top segments (in bytes) grouped by Segment Type

2012-01-02T17:32:00.003+05:30

-- +==================================================================+
-- PURPOSE: Provides a report on the top segments (in bytes) grouped by Segment Type
-- +==================================================================+

SET LINESIZE 155
SET PAGESIZE 9999
SET VERIFY   OFF

BREAK ON segment_type SKIP 1
COMPUTE SUM OF bytes ON segment_type

COLUMN segment_type        FORMAT A20                HEADING 'Segment Type'
COLUMN owner               FORMAT A15                HEADING 'Owner'
COLUMN segment_name        FORMAT A30                HEADING 'Segment Name'
COLUMN partition_name      FORMAT A30                HEADING 'Partition Name'
COLUMN tablespace_name     FORMAT A20                HEADING 'Tablespace Name'
COLUMN bytes               FORMAT 9,999,999,999,999 HEADING 'Size (in bytes)'
COLUMN extents             FORMAT 999,999,999        HEADING 'Extents'

SELECT
    a.segment_type      segment_type
, a.owner             owner
, a.segment_name      segment_name
, a.partition_name    partition_name
, a.tablespace_name   tablespace_name
, a.bytes             bytes
, a.extents           extents
FROM
    (select
         b.segment_type
       , b.owner
       , b.segment_name
       , b.partition_name
       , b.tablespace_name
       , b.bytes
       , b.extents
     from
         dba_segments b
     order by
         b.bytes desc
    ) a
WHERE
    rownum < 101
ORDER BY
    segment_type, bytes desc, owner, segment_name
/

To check for index fragmentation

2012-01-02T17:30:00.001+05:30

-- +------------------------------------------------------------------------------+
-- | PURPOSE : To check for index fragmentation. As a rule of thumb if 10-15% |
-- |            of the table data changes, then you should consider rebuilding the index |
-- +-------------------------------------------------------------------------------+

ANALYZE INDEX &&index_name VALIDATE STRUCTURE;

COL name         HEADING 'Index Name'          FORMAT a30
COL del_lf_rows HEADING 'Deleted|Leaf Rows'   FORMAT 99999999
COL lf_rows_used HEADING 'Used|Leaf Rows'      FORMAT 99999999
COL ibadness     HEADING '% Deleted|Leaf Rows' FORMAT 999.99999

SELECT
    name
, del_lf_rows
, lf_rows - del_lf_rows lf_rows_used
, TO_CHAR( del_lf_rows /(DECODE(lf_rows,0,0.01,lf_rows))*100,'999.99999') ibadness
FROM   index_stats
/

prompt
prompt Consider rebuilding any index if % of Deleted Leaf Rows is > 20%
prompt

undefine index_name

Backup Schedule Template

2012-01-02T17:01:00.000+05:30

Report free space fragmentation

2012-01-02T16:44:00.005+05:30

-- +----------------------------------------------------------------------------+
-- | PURPOSE : Report free space fragmentation.                               |
-- |            THIS SCRIPT MUST BE RUN AS THE SYS USER!!!                  |
-- +----------------------------------------------------------------------------+

connect / as sysdba

CREATE OR REPLACE VIEW free_space (
    tablespace
, pieces
, free_bytes
, free_blocks
, largest_bytes
, largest_blks
, fsfi
, data_file
, file_id
, total_blocks
)
AS
SELECT
    a.tablespace_name
, COUNT(*)
, SUM(a.bytes)
, SUM(a.blocks)
, MAX(a.bytes)
, MAX(a.blocks)
, SQRT(MAX(a.blocks)/SUM(a.blocks))*(100/SQRT(SQRT(count(a.blocks))))
, UPPER(b.file_name)
, MAX(a.file_id)
, MAX(b.blocks)
FROM
    sys.dba_free_space a
, sys.dba_data_files b
WHERE
    a.file_id = b.file_id
GROUP BY
    a.tablespace_name, b.file_name
/

CLEAR COLUMNS
SET LINESIZE 120
SET PAGESIZE 9999
SET FEEDBACK off
SET VERIFY    off

BREAK ON tablespace SKIP 2 ON REPORT

COMPUTE SUM OF total_blocks ON tablespace
COMPUTE SUM OF free_blocks   ON tablespace
COMPUTE SUM OF free_blocks   ON report
COMPUTE SUM OF total_blocks ON report

COLUMN tablespace     HEADING "Tablespace"    FORMAT a15
COLUMN file_id        HEADING File#           FORMAT 99999
COLUMN pieces         HEADING Frag            FORMAT 9999
COLUMN free_bytes     HEADING 'Free Byte'
COLUMN free_blocks    HEADING 'Free Blk'      FORMAT 999,999,999
COLUMN largest_bytes HEADING 'Biggest Bytes'
COLUMN largest_blks   HEADING 'Biggest Blks' FORMAT 999,999,999
COLUMN data_file      HEADING 'File Name'     FORMAT a45
COLUMN total_blocks   HEADING 'Total Blocks' FORMAT 999,999,999

SELECT
    tablespace
, data_file
, pieces
, free_blocks
, largest_blks
, file_id
, total_blocks
FROM
    free_space
/

DROP VIEW free_space
/

List all currently connected user sessions ordered by current PGA size

2012-01-02T16:42:00.002+05:30

-- +-------------------------------------------------------------------------------------+
-- | PURPOSE : List all currently connected user sessions ordered by current PGA size |
-- +-------------------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid                     FORMAT 99999          HEADING 'SID'
COLUMN serial_id               FORMAT 999999         HEADING 'Serial#'
COLUMN session_status          FORMAT a9             HEADING 'Status'          JUSTIFY right
COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid                  FORMAT 9999999        HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING 'Session Program' TRUNC
COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory      FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'

prompt
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Current PGA Size          |
prompt +----------------------------------------------------+

SELECT
    s.sid                sid
, s.serial#            serial_id
, lpad(s.status,9)     session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9)     os_username
, lpad(p.spid,7)       os_pid
, s.program            session_program
, lpad(s.machine,8)    session_machine
, sstat1.value         session_pga_memory
, sstat2.value         session_pga_memory_max
, sstat3.value         session_uga_memory
, sstat4.value         session_uga_memory_max
FROM
    v$process p
, v$session s
, v$sesstat sstat1
, v$sesstat sstat2
, v$sesstat sstat3
, v$sesstat sstat4
, v$statname statname1
, v$statname statname2
, v$statname statname3
, v$statname statname4
WHERE
      p.addr (+)            = s.paddr
AND s.sid                 = sstat1.sid
AND s.sid                 = sstat2.sid
AND s.sid                 = sstat3.sid
AND s.sid                 = sstat4.sid
AND statname1.statistic# = sstat1.statistic#
AND statname2.statistic# = sstat2.statistic#
AND statname3.statistic# = sstat3.statistic#
AND statname4.statistic# = sstat4.statistic#
AND statname1.name        = 'session pga memory'
AND statname2.name        = 'session pga memory max'
AND statname3.name        = 'session uga memory'
AND statname4.name        = 'session uga memory max'
ORDER BY session_pga_memory DESC
/

List all currently connected user sessions ordered by Logical I/O

2012-01-02T16:38:00.001+05:30

-- +--------------------------------------------------------------------------------+
-- | PURPOSE : List all currently connected user sessions ordered by Logical - I/O |
-- +--------------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid               FORMAT 99999            HEADING 'SID'
COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'
COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a18              HEADING 'Session Program' TRUNC
COLUMN session_machine   FORMAT a8               HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN logical_io        FORMAT 999,999,999,999 HEADING 'Logical I/O'
COLUMN physical_reads    FORMAT 999,999,999,999 HEADING 'Physical Reads'
COLUMN physical_writes   FORMAT 999,999,999,999 HEADING 'Physical Writes'

prompt
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Logical I/O               |
prompt +----------------------------------------------------+

SELECT
    s.sid                sid
, s.serial#            serial_id
, lpad(s.status,9)     session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9)     os_username
, lpad(p.spid,7)       os_pid
, s.program            session_program
, lpad(s.machine,8)    session_machine
, sstat1.value
    + sstat2.value         logical_io
, sstat3.value         physical_reads
, sstat4.value         physical_writes
FROM
    v$process p
, v$session s
, v$sesstat sstat1
, v$sesstat sstat2
, v$sesstat sstat3
, v$sesstat sstat4
, v$statname statname1
, v$statname statname2
, v$statname statname3
, v$statname statname4
WHERE
      p.addr (+)            = s.paddr
AND s.sid                 = sstat1.sid
AND s.sid                 = sstat2.sid
AND s.sid                 = sstat3.sid
AND s.sid                 = sstat4.sid
AND statname1.statistic# = sstat1.statistic#
AND statname2.statistic# = sstat2.statistic#
AND statname3.statistic# = sstat3.statistic#
AND statname4.statistic# = sstat4.statistic#
AND statname1.name        = 'db block gets'
AND statname2.name        = 'consistent gets'
AND statname3.name        = 'physical reads'
AND statname4.name        = 'physical writes'
ORDER BY logical_io DESC
/

List all currently connected user sessions ordered by CPU time.

2012-01-02T16:26:00.000+05:30

2011-12-29T12:53:00.000+05:30

The script given below identifies which manager is going to execute a given Concurrent program:

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl fcp, apps.fnd_concurrent_queue_content fcqc, apps.fnd_concurrent_queues_tl cq
WHERE
fcqc.type_application_id(+) = fcp.application_id AND
fcqc.type_id(+) = fcp.concurrent_program_id AND
fcqc.type_code(+) = 'P' AND
fcqc.include_flag(+) = 'I' AND
fcp.LANGUAGE = 'US' AND
fcp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND
NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id AND
NVL (cqc.queue_application_id, 0) = cq.application_id AND
cq.LANGUAGE = 'US'

To identify long running concurrent requests for more than 1 hour.

2011-12-28T10:26:00.000+05:30

# To identify the long running concurrent requests for more than 1 hour
Mailid=<List of Mail ids>
<Set the Environment>
sqlplus -s 'apps/<apps password><<EOF >> /usr/tmp/logli1.log
spool /usr/tmp/longrunco.log
set line 200
set pagesize 500
col PROGRAM_NAME format a30
col concreq format a8
col Username format a10
col opid format a4
col dbuser format a6
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
           fcp.USER_CONCURRENT_PROGRAM_NAME "Program_Name",
                fu.user_name "Username",
               round((sysdate - actual_start_date) * 24 ,2) "Running_Hrs",
           SUBSTR(proc.os_process_id,1,15) clproc,
           SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
           SUBSTR(vsess.username,1,10) dbuser,
           SUBSTR(vproc.spid,1,10) svrproc,
           vsess.sid sid,
           vsess.serial# serial#
    FROM   fnd_concurrent_requests req,
           fnd_concurrent_processes proc,
           fnd_lookups look,
           fnd_lookups look1,
           V\$process vproc,
           V\$session vsess,
           fnd_concurrent_programs_vl fcp,
        fnd_user fu
    WHERE req.controlling_manager = proc.concurrent_process_id(+)
    AND    req.status_code = look.lookup_code
    AND    look.lookup_type = 'CP_STATUS_CODE'
    AND    req.phase_code = look1.lookup_code
    AND    look1.lookup_type = 'CP_PHASE_CODE'
    AND    look1.meaning = 'Running'
    and    req.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
    AND    proc.oracle_process_id = vproc.pid(+)
    AND    vproc.addr = vsess.paddr(+)
    AND    fu.user_id = req.requested_by
    AND    round((sysdate - actual_start_date) * 24) > 1;
spool off
quit;
EOF
echo `date` >> $ORACLE_HOME/admin/$CONTEXT_NAME/udump/longrun_concurrent.log
cat /usr/tmp/longrunco.log >> $ORACLE_HOME/admin/$CONTEXT_NAME/udump/longrun_concurrent.log
cat /usr/tmp/longrunco.log |mailx -s "Alert:Long Running Concurrent requests Check" $Mailid
rm -rf /usr/tmp/longrunco.log

Apex Listener Configuration for Apex

2011-12-24T16:23:00.004+05:30

1) Check java version after completion of glassfish installation
. /java -version

2) Go to location /oracle/glassfishv3/jdk/bin and

Run ./java -jar /oracle/apex/apex.war,
(where /oracle/apex/ is the location of apex listener software )
will get the below screenshot
And provide the preferred username and password for adminlistener and managerlistener
Username and password for the above can be anything.

Output

bash-3.00$ /oracle/glassfishv3/jdk6/jdk/bin/java -jar apex.war
INFO: Starting: /oracle/glassfishv3/apex.war
See: 'java -jar apex.war --help' for full range of configuration options
INFO: Extracting to: /oracle/.apex/8080
Enter the path to the directory containing the APEX static resources
Example: /Users/myuser/apex/images
or press Enter to skip: /oracle/apex_4.0.2_en/apex/images
INFO: Using classpath: file:/oracle/.apex/8080/apex/____embedded/start.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/commons-fileupload-1.2.1.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/ojdbc6.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/ojmisc.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/poi-3.6-20091214.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/ucp.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/apex.jar:
INFO: Starting Embedded Web Container in: /oracle/.apex/8080
Enter a username for the APEX Listener Administrator [adminlistener]: admin123
Enter a password for admin123:admin321
Confirm password for admin123:admin321
Enter a username for the APEX Listener Manager [managerlistener]: manager
Enter a password for manager:manager123
Confirm password for manager:manager123
Apr 21, 2011 2:45:50 PM ____bootstrap.Deployer deploy
INFO: Will deploy application path=/oracle/.apex/8080/apex/WEB-INF/web.xml
Apr 21, 2011 2:45:53 PM ____bootstrap.Deployer deploy
INFO: deployed application path=/oracle/.apex/8080/apex/WEB-INF/web.xml
Apr 21, 2011 2:45:53 PM com.sun.grizzly.Controller logVersion
INFO: Starting Grizzly Framework 1.9.18-o - Thu Apr 21 14:45:53 PDT 2011
INFO: Please complete configuration at: http://localhost:8080/apex/listenerConfigure

In the below screen, Provide username as APEX_PUBLIC_USER , password of apex_public_user as per Section 1,1.ii, hostname & port of apex database , SID of Apex database
And click on apply button which will appear below in the firfox screen.

Installation of Glassfish for configuring apex frontend

2011-12-24T15:56:00.011+05:30

1. Download the Glassfish
http://www.oracle.com/technetwork/java/javaee/downloads/java-ee-sdk-6u3-jdk-6u26-downloads-439816.html
Example File name : java_ee_sdk-6u1-jdk-solaris-sparc.sh

2. Download the Apex listener : apex_listener.1.1.2 http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html

3) Reset the password for APEX_040000 and apex_public_user
i. Unlock the Apex user Acount. SQL> ALTER USER APEX_040000 ACCOUNT UNLOCK; User altered

ii. Change password for Apex public user.
ALTER USER APEX_PUBLIC_USER identified by ;
User altered.
2) Start VNCServer and Start installation in VNC
3) Run the script, sh java_ee_sdk-6u1-jdk-solaris-sparc.sh (/oracle/apex)

Apex Installation for 11g Instance

2011-12-24T15:45:00.002+05:30

Pre Installation Tasks:

1. Create Tablespace for Apex Data and Files.
SQL> create tablespace apex datafile '//apexd01.dbf' size 500m;
Tablespace created.

2. Ensure Shared pool size is atleast 100m (suggested 250m)

3. Download the Apex Installation Software from the below Link and move the software to server
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
Oracle Application Express 4.0.2 - English language only
4. Unzip the apex Installation software and change directory into extracted folder

Installation Tasks:
1. Login as sysdba: sqlplus ‘/as sysdba’
2. Installation Command: @apexins.sql apex apex temp /i/
3. Admin Username Password Change:
SQL> @apxchpwd
Enter a value below for the password for the Application Express ADMIN user.
Enter a password for the ADMIN user []
Session altered.
...changing password for ADMIN
PL/SQL procedure successfully completed.
Commit complete.

Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite

2011-12-24T15:13:00.002+05:30

Generic Tips

1) Sleep Seconds - is the number of seconds your Concurrent manager waits between checking the list of pending concurrent requests (concurrent requests waiting to be started).

Tip: Set the sleep time to be very brief during periods when the number of requests submitted is expected to be high. Otherwise set the sleep time to a high number (e.g. 2 minutes) . This avoids constant polls to check for new requests.

2) Increase the cache size (number of requests cached) to at least twice the number of target processes.
For example, if a manager's work shift has 1 target process and a cache value of 3, it will read three requests, and try to run those three requests before reading any new requests.

Tip: Enter a value of 1 when defining a manager that runs long, time-consuming jobs, and a value of 3 or 4 for managers that run small, quick jobs.

3) Create specialized concurrent managers to dedicate certain process either short or long running programs to avoid queue length.

4) To maximize throughput consider reducing the sleep time of the Conflict Resolution Manager (CRM). The default value is 60 seconds. You can consider setting to 5 or 10 seconds.

5) Avoid enabling an excessive number of standard or specialized managers. It can degrade the performance due polling on queue tables
(FND_CONCURRENT_REQUESTS...). You need to create specialized managers only if there is a real need.

6) Set the system profile option "Concurrent: Force Local Output File Mode" to "Yes" if required . You need to apply patch 7530490 for R12 (or) 7834670 for 11i to get this profile.
Refer Note.822368.1: Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance

Note:- The profile option "Concurrent: Force Local Output File Mode" is set to "No" by default. After applying the patch, set the profile option to YES will cause FNDCPPUR to always access files on the local file system, hence FNDCPPUR will remove the OS files faster.To enable this feature, All Concurrent Manager nodes must be able to access the output file location via the local filesystem

7) Truncate the reports.log file in log directory. Refer Note.844976.1 for more details
Truncation of file "reports.log" is a regular maintenance work of Application DBA. Make sure that reports log file size should not increase to its maximum limit of 2 GB. There is no purge program to truncate file "reports.log". This maintenance needs to be done manually and regularly depending on number of concurrent program which uses "reports.log". You can safely truncate "reports.log". The "reports.log" file can be located under $APPLCSF/$APPLLOG.

8) Ensure "Purge Concurrent Request and/or Manager Data, FNDCPPUR," is run at regular intervals with "Entity" parameter as "ALL". A high number of records in FND_CONCURRENT tables can degrade the performance.

Additionally, the following are very good methods to follow for optimizing the process:

• Run the job in hours with low workload. Doing this after hours will lessen the contention on the tables from running against your daily processing.
• To get the requests under control, run the FNDCPPUR program with Age=20 or Age=18 would be a good method. That means, all requests older than 18 or 20 days will be purged.
• Once the requests are under control, run the FNDCPPUR program with Age=7 to maintain an efficient process. This would solely depend on the level of processing that is performed at your site

9) Ensure that the log/out files are removed from the locations shown below as you run "Purge Concurrent Request and/or Manager Data program".

$APPLCSF/$APPLLOG
$APPLCSF/$APPLOUT

In the event that it does not remove the log/out files, over a period of time it will slow down the performance. Please refer to the following note which suggests the patch which fixes it.

Note.822368.1: Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance

10) Defragment the tables periodically to reclaim unused space / improve performance
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CRM_HISTORY
FND_ENV_CONTEXT
FND_TEMP_FILES

HOW TO DEFRAGMENT

10.1) alter table . move;
10.2) Note that, some indexes might become unusable after table is moved, check the index status from dba_indexes for the table moved and rebuild them too as explained in next bullet.

select owner, index_name, status from dba_indexes where table_owner = upper('&OWNER') and table_name = upper('&SEGMENT_NAME');

10.3) alter index . rebuild online;

Note: Ensure the tablespace in which the object currently exists has got sufficient space before you move/defragment . Always take backup of the tables before moving the data. It is recommended to perform thsaction on Test instance initially then testing thoroughly before performing it on Production instance.

10.4) You will need to collect the statistics for the tables.

For example:
exec fnd_stats.gather_table_stats ('APPLSYS','FND_CONCURRENT_REQUESTS',PERCENT=>99);

Transaction Manager (TM)

11 ) Profile Concurrent:Wait for Available TM - Total time to wait for a TM before switchover to next available TM. Consider setting this to 1 (second).
12) Ensure enough TMs exist to service the incoming request load.
13) When the load is high, set the following profile to optimum values to achieve better results.
PO: Approval Timeout Value - Total time for workflow call (When initiated from Forms) to time out.
14) Set the sleep time on the Transaction Manager to a high number (e.g. 10 minutes), this avoids constant polls to check for shutdown requests.

Parallel Concurrent Processing (PCP) Environment

15) If the failover of managers is taking too long refer to Note:551895.1: Failover Of Concurrent Manager Processes Takes More than 30 Minutes

16) Set profile option 'Concurrent: PCP Instance Check' to 'OFF' if instance-sensitive failover is not required. Setting it to 'ON' means that concurrent managers will fail over to a secondary application tier node if the database instance to which it is connected goes down.

17) Transaction Manager uses DBMS_PIPE to communicate with application session prior to 11i.ATG_PF.H RUP3. DBMS_PIPE in turn uses OS Pipe.We might use Advance Queue(AQ) with 11i.ATG_PF.H RUP3 by setting System Profile "Concurrent: TM Transport Type" to
'QUEUE'

Note Pipes are more efficient but require a Transaction Manager to be running on each DB Instance (RAC). So you might want to use "Queue" for easy maintenance.

18) Add these parameters depends on your Database version
+ _lm_global_posts=TRUE
+ _immediate_commit_propagation=TRUE (11g RAC)
+ max_commit_propagation_delay=0 (9i RAC)

Concurrent Processing Server Tuning

1. There are way too many site specific factors that needs to be considered for optimum CP throughput: from machine hardware, to user request volume, to required Work
Shifts, to programs run time characteristics (long / short running)--not to mention also testing and benchmarking. Such a tasks, is beyond the scope of ATG Support.

2. The "Tuning Concurrent Processing" chapter of the white paper "A Holistic Approach To Performance Tuning Oracle Applications Systems Release 11 and 11i" Note 69565.1 may provide some basic insight. Also reference the "Defining Concurrent Managers" and the "Setting Up and Starting Concurrent Managers" chapters of the "Oracle Applications System Administrator's Guide - Configuration".

3. As per Note 69565.1 "A Holistic Approach to Performance Tuning Oracle Applications Systems", "50% of concurrent processing performance tuning is in the business!"

4. Visit the Concurrent Processing Product Information Center (PIC) Note 1304305.1 for additional performance and setup documentation.

Start Tuning....

2011-12-24T15:09:00.002+05:30

How to Start tuning: Server Level (Focus from Oracle Database)

Whenever there is server level issue, The tuning starts from the server
.. Just remember the phrase “The person who has the problems has the symptoms for solutions” So let us start the tuning from server..

1. First we need to find out what the server utilization is and who is utilizing the most.

How to achieve it: we can use os specific commands like Topas in AIX,TOP in linux environments,Prstat/topas in solaris, task manager in windows.

What you need: once you found what is the utilization and who is doing that..
Then you need to get the server process id (spid) from the os level of the specific process who is utilizing the most.

2. After that login to any user session who can access v$ views.

Execute: ( I attached my queries ,, you can add the columns you need to add )

Select module,action,machine,sql_address,sql_id,status from v$session vs
Where paddr=(select addr from v$process where spid=’&spid’);

3. find out what the sql which is consuming resource.

Select sql_text,sql_fulltext,optimizer_cost from v$sql where sql_id=’&sql_id’;

4. Now you got the details of the sessions,, if you want to do kill the session .. go to your server console as the specific user and then kill the session

How should I do: use ‘’kill -9 ”

5. Don’t stop here .. tune the query and take complete solution to the issue .

How to Start tuning: Session Level (focus from oracle Database)

Refer to the post… How to Start tuning: Server Level (Focus from Oracle Database)
When we found that the specific session taking more bottleneck towards performance. Then The best step to be taken is the tracing the session (ie getting more details) before killing it.
For Example FNDWFBG is taking more cpu usage.. what we need to take step is..
1. As per the guidelines in the post.. first we need to take the Os process id.

2. Login as sysdba

3. i. oradebug setospid &spid
ii. oradebug unlimit
iii. oradebug Event 10046 trace name context forever, level 12
Trace file will be generated with the process id in its name in User dump location.

4. Once the session got completed then execute the below command in the sysdba session where you initiated the oradebug.

oradebug Event 10046 trace name context off;

5. What next.. Generate tkprof for the session dump file to find the expensive query.

Suggested to use as below:

Tkprof explain=/ sys=no sort='(prsela, exeela, fchela)'

Example: tkprof PROD_ora_950460.trc PROD_ora_950460.tkp explain=apps/apps sys=no
sort='(prsela, exeela, fchela)'

The above tkprof options are very usefull.. it will give us the query details in the order of its expensiveness.

6. Beyond this.. DBA needs to work with the respective technical teams to tune the expensive query.

How to start tuning: Code Level

Refer to the post… How to Start tuning: Session Level (focus from oracle Database)
Once we found the most expensive query, we need to tune the query and test it again..
Always advisable to tune and test in your test instance.

Follow the below steps to test and find out whether it is tuned or not…

1. Login to your Application user through which you can execute the statements

2. alter session set tracefile_identifier='10046';

3. alter session set timed_statistics = true;

4. alter session set statistics_level=all;

5. alter session set max_dump_file_size = unlimited;

6. alter session set events '10046 trace name context forever,level 12';

7. Execute the expensive statement

8. select * from dual; --- to ensure the previous cursor is closed

9. alter session set events '10046 trace name context off';

10. Generate the tkprof

Generate the tkprof as explained in the post How to Start tuning: Session Level (focus from oracle Database).. itll the give the details in the order of its expensiveness.

How to start tuning: Oracle Ebs Application Session

As per the previous posts.. we need to trace the session to find out what is the problem.
For E-Business Suite Session .. Here we go.
1. Navigate Responsibility: System Administrator > Profile > System >Query

User: User submitting the Journal entries Report
Profile: Initialization SQL Statement - Custom

2. Click on User column - Edit Field and enter

begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS=''''''10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'''''); end;

3. Save.

4. Reproduce the problem through the user session to find out cause for performance problem.

5 Trace file will be generated with the process id in its name.
6 Generate tkprof for the trace file.

Generate the tkprof as explained in the post How to Start tuning: Session Level (focus from oracle Database).. itll the give the details in the order of its expensiveness.

2011-12-24T11:50:00.000+05:30

-- This is to list the AppsUser who are assigned a particular responsibility

select a.user_name,b.responsibility_name

from

fnd_user a,fnd_responsibility_vl b ,FND_USER_RESP_GROUPS c

where

a.user_id = c.user_id

and

c.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID

and lower(b.responsibility_name) like lower('&Responsibility_name%')

and a.END_DATE is null

and

(c.END_DATE is null

c.end_date > sysdate)

group by a.USER_NAME,b.RESPONSIBILITY_NAME

List Responsibilities That Can Run a Given Concurrent Program

2011-12-24T11:18:00.004+05:30

Purpose

-------

This script helps in identifying the list of responsibilities that can run a given concurrent request

REM Save this file as whocanrun.sql

REM Usage: sqlplus apps/apps @whocanrun

REM or: sqlplus apps/apps @whocanrun

REM

REM Implemented remark 64545.1 to modify script for use with 11i

REM changed to use fnd_concurrent_programs_vl, and fnd_responsibility_vl

REM in place of fnd_concurrent_programs, and fnd_responsibility.

REM

set verify off

set pagesize 1000

column RN format A40 heading "Responsibility Name"

prompt

prompt &1 can be run by:

SELECT responsibility_name RN

FROM fnd_request_groups frg,

fnd_request_group_units frgu,

fnd_concurrent_programs_vl fcpv,

fnd_responsibility_vl frv

WHERE frgu.request_unit_type = 'P'

AND (UPPER(fcpv.concurrent_program_name) = UPPER('&1')

UPPER(fcpv.user_concurrent_program_name) = UPPER('&1'))

AND frgu.request_group_id = frg.request_group_id

AND frgu.request_unit_id = fcpv.concurrent_program_id

AND frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name

Show the top 10 longest-active user sessions

2009-04-07T17:31:00.000+05:30

/* top10active.sql
shows the top 10 longest-active user sessions
*/
col osuser format a10 trunc
col LastCallET format 99,999
col sid format 9999
col spid formar 999999
col username format a10 trunc
col uprogram format a25 trunc
col machine format a10 trunc
set linesize 132
set verify off
select * from (
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram,
s.machine, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and module is not null
and s.status = 'ACTIVE'
order by 4 desc)
where rownum < 11;

Currently Active Sessions

2009-04-07T17:18:00.000+05:30

/* active.sql
show info about active user sessions */

set linesize 200
col sid format 999
col username format a8 trunc
col osuser format a10 trunc
col logged_on format a13 head LOGON_TIME
col machine format a17 trunc
col command format A15
--col program format a20 trunc
col proginfo format a25 trunc
set verify off
select s.sid, s.status,s.username,s.osuser,s.machine,s.sql_hash_value,
decode(s.command,
1,'Create table' , 2,'Insert',
3,'Select' , 6,'Update',
7,'Delete' , 9,'Create index',
10,'Drop index' ,11,'Alter index',
12,'Drop table' ,13,'Create seq',
14,'Alter sequence' ,15,'Alter table',
16,'Drop sequ.' ,17,'Grant',
19,'Create syn.' ,20,'Drop synonym',
21,'Create view' ,22,'Drop view',
23,'Validate index' ,24,'Create procedure',
25,'Alter procedure' ,26,'Lock table',
42,'Alter session' ,44,'Commit',
45,'Rollback' ,46,'Savepoint',
47,'PL/SQL Exec' ,48,'Set Transaction',
60,'Alter trigger' ,62,'Analyse Table',
63,'Analyse index' ,71,'Create Snapshot Log',
72,'Alter Snapshot Log' ,73,'Drop Snapshot Log',
74,'Create Snapshot' ,75,'Alter Snapshot',
76,'drop Snapshot' ,85,'Truncate table',
0,'No command', '? : '||s.command) command
,s.process, p.spid,
to_char(logon_time,'mm/dd hh:miAM') logged_on
--s.module || ' - ' || s.program proginfo
from v$session s, v$process p
where s.username is not null
and s.paddr = p.addr
and s.status='ACTIVE'
/

Upgradation of Discoverer from 4i to 10g for Applications 11i CU2 (11.5.10.2)

2008-11-20T15:09:00.001+05:30

Upgradation of Discoverer from 4i to 10g for Applications 11i CU2 (11.5.10.2)
Environment Details : Operating system : Solaris Sparc 64 Bit (5.10)
Applications : 11i CU2 (11.5.10.2)
Database : 10.2.0.2.0
Oracle AS (Infra and Disc) Installation in TEST Applications
Infra Structure installation in TEST:
1. login to root
2. xhost +
3. echo set semsys:seminfo_semmni=300 >>/etc/system
4. echo set semsys:seminfo_semmns=1024 >>/etc/system
5. echo set semsys:seminfo_semmsl=256 >>/etc/system
6. echo set shmsys:shminfo_shmmax=4294967295 >>/etc/system
7. echo set shmsys:shminfo_shmmin=1 >>/etc/system
8. echo set shmsys:shminfo_shmmni=512 >>/etc/system
9. echo set shmsys:shminfo_shmseg=150 >>/etc/system
10. cd /testapps01/
11. mkdir –p testdisc
12. mkdir –p testdisc/testinfra
13. mkdir –p testdisc/testdis
14. chown –R apptstmgr:dba ./testdisc
15. Login to OS application owner (apptstmgr).
16. cd /Disk1/
17. DISPLAY=192.168.73.168:0.0;export DISPLAY
18. ./runInstaller
19. click next on welcome screen
20. set ORACLE_HOME as /testapps01/testdisc/testinfra and click next in “Specify file
location” screen.
21. Select “Oracle Application Server Infrastructure 10g” and click next in “Select a product
to Install” Screen.
22. Select “Identity Management and Metadata Repository” and click next in “Select
Application Type” Screen.
23. Select “Root Privileges” and click next in “Confirm Pre-Installation Requirements” Screen.
24. Select “Oracle Internet Directory, Oracle Application Single Sign-on, Oracle Application
Server Delegated Administrator” and click next in “Select configuration Options” screen.
25. select “Suggested Namespace” with default values (dc=dummy,dc=co,dc=in) and click
next in “Specify Namespace in Internet Directory” Screen
26. select “Automatic” and click next in “Specify Port Configuration Options” screen
27. Provide Global Database Name = “testinfr.dummy.co.in” , SID=testinfr , select database
character set as English US7ASCII , specify Database File Location
/testapps01/testdisc/oradata/ and click next in “Specify Database Configuration Options”
Screen
28. Select “Use the same password for all the accounts” and enter the password as tester1
And click next in “specify Database Schema Passwords” Screen.
29. Enter the Instance Name as “testinfr”, ias_admin password as tester1, confirm password
as tester1 and click next on “Specify Instance Name and ias_admin Password” screen.
30. click Install on “Summary Screen”
31. After Setup Completion, Installer request to run /testapps01/testdisc/testinfra/root.sh
from root.
32. login to root through another terminal
33. sh /testapps01/testdisc/testinfra/root.sh
34. Accept all the default values prompted by root.sh scripts
35. click ok on installer screen prompted to run /testapps01/testdisc/testinfra/root.sh script
36. Installer will proceed for configuration of database and As Components.
37. After Installation is completed click exit & yes to exit installer.
Details of TEST AS Infrastructure after completion of Installation and configuration of
AS Components
The following information is available in:
/testapps01/testdisc/testinfra/install/setupinfo.txt
Oracle Application Server 10g (10.1.2.0.2) Usernames and Default password information:
Please refer to Oracle Application Server 10g Administrator Guide for more information.
Install Type: Identity Management and Metadata Repository
Configured Components: Oracle Application Server Metadata Repository Oracle HTTP Server
Oracle Application Server Containers for J2EE Oracle Internet Directory Oracle Application
Server Single Sign-On Oracle Application Server Delegated Administration Service Oracle
Application Server Directory Integration and Provisioning
New Database created with these properties:
Database File Location: /testapps01/testdisc/oradata/
Database Global Name: testinfr.dummy.co.in
Database SID:testinfr
Database Name:testinfr
Character Set: -characterset US7ASCII
Use the following URL to access the Oracle HTTP Server and the Welcome Page:
http://ebstest01.dummy.co.in:7778
-----------------------------------------
Use the following URL to access the Oracle Enterprise Manager Application Server Control:
http://ebstest01.dummy.co.in:1156
Instance Name: testinfr.ebstest01.dummy.co.in
Discoverer Structure installation in TEST:
1. login to root
2. xhost +
3. echo set rlim_fd_max=65536 >>/etc/system
4. Login to OS application owner (apptstmgr).
5. cd /Disk1/
6. DISPLAY=192.168.73.168:0.0;export DISPLAY
7. ./runInstaller
8. click next on welcome screen
9. set ORACLE_HOME as /testapps01/testdisc/testdis and click next in “Specify file location”
screen.
10. Select “Oracle Application Server 10g” and click next in “Select a product to Install”
Screen.
11. Select “Business Intelligence and Forms” and click next in “Select Application Type”
Screen.
12. Select “Root Privileges” and click next in “Confirm Pre-Installation Requirements” Screen.
13. Select “Oracle Application Server 10g Discoverer” and click next in “Select configuration
Options” screen.
14. select “Automatic” and click next in “Specify Port Configuration Options” screen
15. Provide password as tester1 for username: cn=orcladmin in “Specify OID Login” Screen.
16. Click next on “Select Oracle Application Server 10g Metadata Repository” Screen.
17. Click next on “Provide Outgoing Mail Server Information” screen (SMTP can be null).
18. Enter the Instance Name as “testdis”, ias_admin password as tester1, confirm password
as tester1 and click next on “Specify Instance Name and ias_admin Password” screen.
19. click Install on “Summary Screen”
20. After Setup Completion, Installer request to run /testapps01/testdisc/testdis/root.sh from
root.
21. login to root through another terminal
22. sh /testapps01/testdisc/testdis/root.sh
23. Accept all the default values prompted by root.sh scripts
24. click ok on installer screen prompted to run /testapps01/testdisc/testdis/root.sh script
25. Installer will proceed for configuration of database and As Components.
26. After Installation is completed click exit & yes to exit installer.
Details of TEST Discoverer after completion of Installation and Configuration of AS
Components
The following information is available in:
/testapps01/testdisc/testdis/install/setupinfo.txt
Oracle Application Server 10g 10.1.2.0.2 Usernames and Default password information: Please
refer to the Oracle Application Server 10g Administrator Guide for more information.
Install Type: All Services
Configured Components: Oracle HTTP Server, Oracle Application Server 10g Containers for J2EE,
Oracle Application Server 10g Web Cache, Oracle Application Server 10g Discoverer,
Registered with Oracle Internet Directory: ebstest01.dummy.co.in:389
Use only SSL connections with this Oracle Internet Directory:No
Database Connection String:
ebstest01.dummy.co.in:1521:testinfr.dummy.co.in:testinfr.dummy.co.in
Use the following URL to access the Oracle HTTP Server and the Welcome Page:
http://ebstest01.dummy.co.in:7779
-----------------------------------------
Use the following URL to access the Oracle Enterprise Manager Application Server Control:
http://ebstest01.dummy.co.in:1810
Instance Name: testdis.ebstest01.dummy.co.in
/testapps01/testdisc/testdis/discoverer/util/pref.txt
Configuration of TEST Discoverer and integrating Oracle Application 11i with 10g
Discoverer
1. login to apptstmgr ( applications manager )
2. scp –rp /testapps01/testappl/fnd/11.5.0/secure/TEST_ebstest01/test.dbc
/testapps01/testdisc/testdis/discoverer/secure/
3. cd /testapps01/testdisc/testdis/network/admin
4. vi tnsnames.ora
5. add the following tns entry in tnsnames.ora
a. TEST=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebstest01.dummy.co.in)(PORT=1572))
(CONNECT_DATA=
(SERVICE_NAME=test)
(INSTANCE_NAME=test)
)
)
16. login to apptstmgr in another terminal to ebstest01
17. source applications 11i environment file
18. cd $APPL_TOP/admin
19. backup the xml file : scp –rp TEST_ebstest01.xml
TEST_ebstest01_bkp__.xml
20. vi TEST_ebstest01.xml
21. change the following parameters as below:
a. s_disco_standalone = true
b. s_disco_machine = ebstest01.dummy.co.in
c. s_disco_port = 7779
d. s_disco_protocol = http
e. s_disco_ver_comma = 10,1,2
f. s_disco_eul_prefix = EUL
g. s_discostatus = disabled
22. cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
23. stop application services : sh adstpall.sh apps/apps
24. ensure concurrent managers stop completely : ps –efgrep FNDLIBRgrep –v grepwc –l
25. execute autoconfig : sh adautocfg.sh
26. provide the apps schema password : apps
27. After Autoconfig completed successfully, Startup application services :
sh adstrtal.sh apps/apps
28. Login to sysadmin through front end applications
29. Navigate to system administrator responsibility.
30. In System administrator, Navigate profiles à system form. Query the %Discoverer%
profile options. verify the following ICX profile options in Oracle Applications:
a. ICX_DISCOVERER_LAUNCHER =
http://ebstest01.dummy.co.in:7779/discoverer/plus?Connect=[APPS_SECURE]
b. ICX_DISCOVERER_VIEWER_LAUNCHER =
http://ebstest01.dummy.co.in:7779/discoverer/viewer?Connect=[APPS_SECURE]
c. ICX_DISCOVERER_RELEASE = 10
d. ICX_DISCOVERER_USE_VIEWER = Yes
e. ICX_DEFAULT_EUL = EUL
f. ICX_DISCOVERER_EUL_LANGUAGE = American English
g. ICX_DISCOVERER_PARAMETERS =
exitURL=http://ebstest01.dummy.co.in:8051/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE.

Issue in Preclone process after upgrading 11i with 10g R2

2008-08-09T13:06:00.002+05:30

Dear all,

This post is relating to following issue we face after upgrading 11i with 10g R2 in Preclone process of dbTier.

ERROR: The version of the OUI found in $ORACLE_HOME/appsoui () is too low.Please make sure that you have applied the latest OUI patch required by Rapid Clone.

Please apply patch 4766508 Application manager patch following the below process:

i. Enable Maintainence Mode through adadmin
ii. Apply u4766508 patch through adpatch
iii. Disable Maintainence mode through adadmin
iv. Cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME/
v. Sh adautocfg.sh
vi. Provide apps password
vii. $ADPERLPRG $AD_TOP/bin/admkappsutil.pl
viii. cp $APPL_TOP/admin/out/appsutil.zip $RDBMS_ORACLE_HOME ( 10g )
ix. unzip -o $ORACLE_HOME/appsutil.zip
x. cd $ORACLE_HOME/appsutil/scripts
xi. sh adautocfg.sh
xii. provide apps password
xiii. perl adpreclone.pl dbTier

The Same process was also updated in upgrade process document.... you can also refer the same in that post...

Ref Metalink Doc ID: 370136.1

https://metalink.oracle.com/metalink/plsql/f?p=130:14:6769826403964055797::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,370136.1,1,1,1,helvetica

Thanks & Regards,
Narasimha Rao.K

Some ADADMIN Activities Shortcuts

2008-08-08T11:35:00.005+05:30

Dear all,
This post is to share some shortcuts for adadmin activities.

To Enable and disable maintainence mode
1. Enable Maintainence mode
sqlplus -s apps/<`apps password`> @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

2. Disable Maintainence Mode
sqlplus -s apps/<`apps password`> @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

Will share more in future posts....

Thanks & Regards,
Narasimha Rao.K

Oracle EBS Apps in MS-Vista

2008-08-02T15:57:00.003+05:30

Dear All,

This post is relating to using Vista in EBS.

Info: Vista is certified in Ebs 11i.

Permenant solution : Upgrade Sun Java Plug-in JRE 1.5.0_12

Certified Configurations

Release 11i -- 11.5.10.CU2
Applications 11.5.10 plus Maintenance Pack 11.5.10 CU2 or later
Oracle Developer 6i Patchset 18 (6.0.8.27.x) or later (Metalink Note 125767.1 https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=125767.1)
Oracle Applications Technology 11i.ATG_PF.H Rollup 4 http://blogs.oracle.com/schan/2006/08/latest_ebusiness_suite_technol_1.html (RUP 4) (patch 4676589) or later
Microsoft Vista desktop clients running Internet Explorer 7
Sun Java plug-in (JRE) 1.5.0_12

Release 11i -- 11.5.9.CU2
Applications 11.5.9 plus Maintenance Pack 11.5.9 CU2 or later
Oracle Developer 6i patchset 18 (6.0.8.27.x) or later (Metalink Note 125767.1 https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=125767.1 )
Oracle Applications Technology 11i.ATG_PF.H Rollup 4 http://blogs.oracle.com/schan/2006/08/latest_ebusiness_suite_technol_1.html (RUP 4) (patch 4676589) or later
Microsoft Vista desktop clients running Internet Explorer 7
Sun Java plug-in (JRE) 1.5.0_12

Release 12
Oracle Applications Release 12 with R12.ATG_PF.A.DELTA.3 http://blogs.oracle.com/schan/2007/10/apps_technology_1203_update_no.html (patch 6077669) or later
Microsoft Vista desktop clients running Internet Explorer 7
Sun Java plug-in (JRE) 1.5.0_12.

Temporary solution:
1 - Install JAVA jdk1.6.0_02 - http://java.sun.com/javase/downloads/index.jsp
2 - Put first on PATH variable: C:\Program Files\Java\jre1.6.0_02\bin;
Now a little problem...
In my machine just this not solved the problem, but in another machine was enought.
Then I proceeded the step 3:
Copy the jvm.dll file under C:\Program Files\Java\jre1.6.0_02\jre\bin\client to C:\Program Files\Oracle\Jinitiator\bin\hotspot, where is the jinit version.
The directory bin\client is hidden but exists... :)
* Don't forget to backup the original file.

It worked for me.

IMP:** The Above informations are consolidated information from Internet. and Temporary solution has been displayed after testing.**

Thanks & Regards,
Narasimha Rao.K

Sending Mails through Os( Unix ) with Mailx

2008-07-30T09:54:00.012+05:30

Dear All,

This Post is relating to Useful Operating system techniques
to send mails using command : Mailx

1. To send mails with body and without attachments
syntax : cat <`file's full path and name`> mailx -s “<`Subject`>” <`Recipient`>
Example:
cat /home/myname/logs/mylog1.log mailx -s “Test Alert 1” xyz@gmail.com

2. To Send Mails without body and with attachments
syntax :
uuencode <`file's full path and name`> <`file's alias name`> mailx -s “<`Subject`>” <`Recipient`>
Example :
uuencode /home/myname/logs/mylog1.log alert1.log mailx -s “Test Alert 2” xyz@gmail.com

3. To Send Mails with body and attachments
syntax :
( cat <`file's full path and name`> ; uuencode <`file's full path and name`> <`file's alias name`> )
mailx -s “<`Subject`>” <`Recipient`>
Example :
( cat /home/myname/logs/mylog1.log ; uuencode /home/myname/logs/mylog1.log alert1.log )
mailx -s “Test Alert 3” xyz@gmail.com

Thanks & Regards,
Narasimha Rao.K

R12 New Features

2008-06-16T10:11:00.002+05:30

Hi,

Please find the Release 12 New features
through the below link.

http://beginapps.blogspot.com/2008/06/release-content-document-release-12.html

Thanks & Regards,
Narasimha Rao.K

Complete Guide for upgrading oracle applications 11i with 10g R2

2008-06-05T11:10:00.010+05:30

Dear All,

Sub: Upgrading oracle applications 11i with 10g R2.
( This ll be the complete guide for upgradation )

1. Complete the Installation of Oracle Apps EBS 11.5.10 ( with 9.2.0.6.0 ) (Production Source).

2. Upgrade AD from Ad.I to AD.I.6 (Apply 6502082 (AD.I.6)).
(a) Cd $JAVA_TOP
(b) unzip -o apps.zip
(c) Convert to multi org
(d) enable Maintainence mode through adadmin
(e) apply the u6502082.drv patch through adpatch
(f) disable Maintainence mode
(g) cp $APPL_TOP/admin/adgrants.sql to $ORACLE_HOME/appsutil/admin/
(h) switch to oracle user
(i) sqlplus '/as sysdba' @$ORACLE_HOME/appsutil/admin/adgrants.sql applsys

3. Upgrade Developer Suite 6i Patchset from Patchset 17 to 18
(document id :125767.1 )

(a) download the latest developer 6i patch (4948577) and interoperability patch (4888294
(b) download the additional developer 6i patches ( 5713544,4261542,5216496,5753922,6195758,5928515)
(c) cd $APPLCSF/scripts/$CONTEXT_NAME/
(d) sh adstpall.sh apps/
(e) apply the 4948577 patch through adpatch.

4. apply the additional developer 6i patches (Cont. Upgrade Developer 6i Process )
I. application of 5713544 patch.
(a) Cd to patch directory
(b) cp genshlib to $ORACLE_HOME/bin
(c) sh patch.sh
(d) adrelink.sh force=y “fnd f60webmx”

II. application of 4261542 patch

(a) unzip the patch in $ORACLE_HOME
(b) cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class
$ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG4261542
(c) cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
$ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class.PRE_BUG4261542
(d)cd $ORACLE_HOME/4261542/oracle/forms/engine
(e)cp Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
(f) cd $ORACLE_HOME/4261542/oracle/forms/handler
(g) cp AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class
(h) Generate Jar Files through adadmin [adadmin - Generate Applications Files - Generate product JAR files ].

III. application of 5216496 Patch.

(a) cd $ORACLE_HOME
(b) unzip the 5216496 patch
(c) cd 5216496
(d) cp genshlib $ORACLE_HOME/bin
(e) cd $ORACLE_HOME/lib
(f) cp libiff.a libiff.a.ORG
(g) cp libiff.so.0 libiff.so.0.ORG
(h) cp libiffw.a libiffw.a.ORG
(i) cp libiffw.so.0 libiffw.so.0.ORG
(j) cd $ORACLE_HOME/5216496/opt
(k) ar -rv $ORACLE_HOME/lib/libiff.a ifzprs.o
(l) cd $ORACLE_HOME/5216496/optweb
(m) ar -rv $ORACLE_HOME/lib/libiffw.a ifzprs.o
(n) cd $ORACLE_HOME/lib
(o) $ORACLE_HOME/bin/genshlib iffw 0
(p) $ORACLE_HOME/bin/genshlib iff 0
(q) cd $ORACLE_HOME/forms60/lib
(r) make -f cus_forms60w.mk install

IV. application of 5753922 patch.

(a) Cd 5753922
(b) sh patch.sh
V. application of 6195758 Patch.
(a). cd $ORACLE_HOME
(b). unzip -o p6195758_600_GENERIC.zip
(c). cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class
$ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class.PRE_BUG6195758
(d). cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class
$ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class.PRE_BUG6195758
(e). cd $ORACLE_HOME/6195758/oracle/forms/handler
(f). cp UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class
(g). cp ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class
(h). Generate Jar Files [ adadmin à Generate Application Files à Generate Product Jar Files ]

Vi. application of 5938515 patch.

(a) cd 5938515
(b) sh patch.sh
(c) adrelink.sh force=y “fnd f60webmx”

5. Relink Application Executables.

(a). adadmin - Maintain Application Files Menu - Relink Application Program

(b). Enter list of products to link ('all' for all products)[all] : fnd
(c). Generate specific executables for each selected product [No] ? y
(d). Relink with debug information [No] ? n
(e). Enter executables to relink, or enter 'all' [all] : f60webmx ar60run ar60runb ar60rund

6. Apply Oracle Applications 11i Developer Interoperability patch

a. Enable the Maintainence Mode through adadmin
b. Apply the u4888294.drv through adpatch
c. Disable the Maintainence Mode

7. Application of Patches for known issues.

(a) cd $ORACLE_HOME
(b) unzip p3182153_600_GENERIC.zip
(c) cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/TextComponentItem.class
$ORACLE_HOME/forms60/java/oracle/forms/handler/TextComponentItem.class.PRE_BUG3182153
(d) cd $ORACLE_HOME/3182153/oracle/forms/handler.

(e) cp TextComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/TextComponentItem.class

(f) Generate Jar Files
[adadmin → Maintain Application File Menu → Generate Product Jar Files ]

8. Application of 6372396 ( TXK AUTOCONFIG AND TEMPLATES ROLLUP PATCH S (APRIL/MAY 2008).

(a) source the appplications environment file
(b) cd $XPATCH_TOP/patches/6372396/fnd/patch/115/bin
(c) ./txkprepatchcheck.pl -script=ValidateRollup
-outfile=$APPLTMP/txkValidateRollup.html
-appspass=apps
(d) Review the txkValidateRollup.html
(e) cd $XPATCH_TOP/patches/6372396
(f) enable maintainence mode
(g) apply u6372396.drv patch with adpatch
(h) cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME/
(i) sh adautocfg.sh
(j) $ADPERLPRG $AD_TOP/bin/admkappsutil.pl
(k) cp $APPL_TOP/admin/out/appsutil.zip $RDBMS_ORACLE_HOME
(l) vi $OA_HTML/bin/appsweb.cfg ,and copy the content from “BEGIN CUSTOMIZATION” AND “END CUSTOMIZATION”
(m) vi appsweb_.cfg and paste the copied content.
(n) 'mv $IAS_ORACLE_HOME/Apache/Apache/logs/sec_audit.log $IAS_ORACLE_HOME/Apache/Apache/logs/sec_audit_.log
(o) sh adstpall.sh apps/
(p) sh adstrtal.sh apps/

9. 11.5.10 INTEROP PATCH FOR 10GR2 (Application of 4653225 )
(a) enable Maintainence mode through adadmin
(b) apply the u4653225.drv through adpatch
(c) disable Maintainence Mode through adadmin
Installation of 10GR2 Base and Companion binaries

10. Pre Checks for 10GR2 Installation

(1) Set the environment variable ORACLE_HOME=
(2) execute : export PATH=$PATH;$ORACLE_HOME/bin <>
(3) xhosts +

10G R2 Software Installation

11. Start 10G Base (10.2.0.1) Software Installation

(1) cd database/Disk1
(2) ./runinstaller

(a) Provide the Oracle home Path that distinct from 9i Oracle home.
(b) Install only software ( No New Database Creation Required).
(c) Dont Upgrade database at the time of software installation.

10G R2 Companion CD Installation

12. Install the 10g Database Products by installing 10G R2 (10.2.0.1)

(1) cd database/Disk1
(2) ./runinstaller

Upgrade Oracle 10G database base software to latest oracle 10G database Software
10G R2 (10.2.0.3 )

13. Mandatory Pre Installation Tasks

Set PATH=$PATH:$ORACLE_HOME/bin;$ORACLE_HOME/perl/bin
set LD_LIBRARY_PATH=$ORACLE_HOME/lib
set PERL5LIB=$ORACLE_HOME/perl/lib/;$ORACLE_HOME/perl/lib/site_perl/

14. Installation of Latest Oracle Database Software

(a). Unzip the 10.2.0.3 Software
(b). cd database/Disk1
(c). ./runinstaller

15. Create nls/data/9idata Directory

(a). perl $ORACLE_HOME/nls/data/old/cr9idata.pl
(b). export $ORA_NLS10=$ORACLE_HOME/nls/data/9idata

16. Additional 10.2.0.3 RDBMS Patches

i.Apply 5257698 Patch
ii.Apply 5892355 Patch
iii.Apply 5871314 Patch
iv.Apply 5755471 Patch

17. Pre Database Upgrade Steps – Database (Perspective )

1.Export ORACLE_HOME=<10g>.
2.Export ORACLE_SID=
3.cp $ORACLE_HOME/rdbms/admin/utlu102i.sql /tmp
4.cd /tmp
5.sqlplus “/as sysdba”
6.startup db
7.spool info.log
8.run $utlu102i.sql
9.spool off
10.Check info.log for any errors and recover the same

18. Pre Database Upgrade steps -- Applications 11i ( Perspective )
appsutil creation
check whether 9i database is up in running else startup the database
a. login to applmgr
b. $ADPERLPRG $AD_TOP/bin/admkappsutil.pl
c. cp $APPL_TOP/admin/out/appsutil.zip $RDBMS_ORACLE_HOME ( 10g )
d. unzip -o $ORACLE_HOME/appsutil.zip

Listener and tnsnames ( Network ) Resolution

a. login to oracle user
b. cp the tnsnames.ora and listener.ora from 9i TNS_ADMIN to 10g TNS_ADMIN
c. Change the database home in the tnsnames.ora and listener.ora

19. Upgrade 9i Database to 10gR2

Shutdown the database listener if it is up in running

a. cd $ORACLE_HOME/bin
b. ./dbua
c. specify the 9i Database ( to be upgraded )
d. Specify the datafile path for creating sysaux tablespace
e. Remove check box for compiling PL/SQL Objects
f. Give the Enterprise Manager Options
g. Check whether the database information and components details
h. Proceed for upgrade
i. Finish the upgrade process
j. Modify the initialization parameters

I. vi $ORACLE_HOME/dbs/init<$ORACLE_SID>.ora
II. compatible = 10.2.0
III. sga_target = 1G
IV. shared_pool_size = 400M
V. shared_pool_reserved_size = 40M
VI. nls_length_semantics = BYTE
VII.undo_management = AUTO
VIII. undo_tablespace = APPS_UNDOTS1
IX. pga_aggregate_target = 1G
X. workarea_size_policy = AUTO
XI. olap_page_pool_size = 4194304
XII.open_cursors = 600
XIII. session_cached_cursors = 500
XIV. plsql_optimize_level = 2
XV. plsql_code_type = INTERPRETED
XVI. _b_tree_bitmap_plans = FALSE
XVII. optimizer_secure_view_merging = FALSE
XVIII. _kks_use_mutex_pin=TRUE

Remove the Following Parameters

i. _always_anti_join
ii. _always_semi_join
iii. _complex_view_merging
iv. _index_join_enabled
v. _new_initial_join_orders
vi. _optimizer_cost_based_transformations
vii. _optimizer_cost_model
viii. _optimizer_mode_force
ix. _optimizer_undo_changes
x. _or_expand_nvl_predicate
xi. _ordered_nested_loop
xii. _push_join_predicate
xiii. _push_join_union_view
xiv. _shared_pool_reserved_min_alloc
xv. _sortmerge_inequality_join_off
xvi. _table_scan_cost_plus_one
xvii. _unnest_subquery
xviii. _use_column_stats_for_function
xix. always_anti_join
xx. always_semi_join
xxi. db_block_buffers
xxii. db_cache_size
xxiii. enqueue_resources
xxiv. event="10932 trace name context level 32768"
xxv. event="10933 trace name context level 512"
xxvi. event="10943 trace name context forever, level 2"
xxvii. event="10943 trace name context level 16384"
xxviii. event="38004 trace name context forever, level 1"
xxix. hash_area_size
xxx. java_pool_size
xxxi. job_queue_interval
xxxii. large_pool_size
xxxiii. max_enabled_roles
xxxiv. optimizer_dynamic_sampling
xxxv. optimizer_features_enable
xxxvi. optimizer_index_caching
xxxvii. optimizer_index_cost_adj
xxxviii. optimizer_max_permutations
xxxix. optimizer_mode
xl. optimizer_percent_parallel
xli. plsql_compiler_flags
xlii. query_rewrite_enabled
xliii.row_locking
xliv. sort_area_size
xlv. undo_retention
xlvi. undo_suppress_errors

k. Compile Invalid objects through $ORACLE_HOME/rdbms/admin/utlrcmp.sql
command : sqlplus '/as sysdba' @$ORACLE_HOME/rdbms/admin/utrlcmp.sql

20. Post Upgrade steps

1. Fix Korean Lexers
i. Apply the 5005469 Patch
ii. sqlplus "/ as sysdba" @$ORACLE_HOME/ctx/sample/script/drkorean.sql

2. Startup the listener of upgraded database name
i. lsnrctl start

3. Run adgrants.sql

i. cp $APPL_TOP/admin/adgrants.sql $ORACLE_HOME/ <<10g>>
ii. sqlplus "/ as sysdba" @adgrants.sql APPLSYS

4. Grant create procedure privilege on CTXSYS

i. Login to applmgr
ii. sqlplus apps/ @$AD_TOP/patch/115/sql/adctxprv.sql CTXSYS

5. Gather statistics for SYS schema

i. cp $APPL_TOP/admin/adstats.sql $ORACLE_HOME
ii. login TO Oracle user
iii. cd $ORACLE_HOME
iv. $ sqlplus "/ as sysdba"
v. SQL> shutdown normal;
vi. SQL> startup restrict;
vii. SQL> @adstats.sql
viii. SQL> shutdown normal;
ix. SQL> startup;
x. SQL> exit;

21. Implement and Execute Autoconfig in Database Tier

Notes : Ensure no environment variables has been set either manually or in .profile and database is up in running.if environment variable revoke such variables.

i. Export ORACLE_HOME =<10g>
ii.Export LD_LIBRARY_PATH = <10g>
iii.Export ORACLE_SID = instance name running on this database node.
iv. Export PATH=$PATH;$ORACLE_HOME/bin;
v. Export TNS_ADMIN = $ORACLE_HOME/network/admin/$CONTEXT_NAME
vi. sqlplus '/as sysdba'
vii. create pfile from spfile;
viii. shu immediate
ix. startup pfile=$ORACLE_HOME/dbs/init<$ORACLE_SID>.ora
x. exit
xi. lsnrctl start $ORACLE_SID
xii. cd $ORACLE_HOME/appsutil/bin
xiii. perl adbldxml.pl tier=db appsuser=apps appspasswd=
xiv. cp $ORACLE_HOME/dbs/init<$ORACLE_SID>.ora
xv. $ORACLE_HOME/dbs/init<$ORACLE_SID>_.ora
xvi. cd $ORACLE_HOME/appsutil/bin
xvii. perl adconfig.pl
xviii. Provide the created context file name as input for adconfig.pl script and check $ORACLE_HOME/appsutil/log after completion
xix. shutdown and startup database using addbctl.sh

22. Execute Autoconfig on Application Tier

i. cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
ii. sh adautocfg.sh

Notes: Ensure Applications is locating to 10g database

23. Startup Applications

i. cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME/
ii. sh adstrtal.sh apps/

24. Apply Oracle Receivables 5753621 Patch

i. Enable Maintainence Mode through adadmin
ii. apply u5753621 patch through adpatch
iii. disable Maintainence Mode through adadmin

25. Apply Application Manager 4766508 Patch [ For issue in preclone process in dbtier ]

i. Enable Maintainence Mode through adadmin
ii. Apply u4766508 patch through adpatch
iii. Disable Maintainence mode through adadmin
iv. Cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME/
v. Sh adautocfg.sh
vi. Provide apps password
vii. $ADPERLPRG $AD_TOP/bin/admkappsutil.pl
viii. cp $APPL_TOP/admin/out/appsutil.zip $RDBMS_ORACLE_HOME ( 10g )
ix. unzip -o $ORACLE_HOME/appsutil.zip
x. cd $ORACLE_HOME/appsutil/scripts
xi. sh adautocfg.sh
xii. provide apps password
xiii. perl adpreclone.pl dbTier

Thanks & Regards,
Narasimha Rao.K

2008-06-05T11:03:00.001+05:30

How to configure "Forgot your password" link

2008-06-05T10:41:00.003+05:30

Dear All,
After a very long time,im updating this blog. Sorry for that.
Subject :How to enable the “Forgot Your Password” in oracle applications login page :
1. Login to the system administrator responsibility
2. navigate to profile à system
3. In the profile text box : enter “ Local Login Mask”
4. If you need to enable only Forgot your password Link in login page
then enter value = 8 ;
if you want to enable password hint and forgot your password link
then enter value = 10.
( Suggested to have password hint + forgot your password link)
5. save the profile option
6. logout & login again and test the option

Thanks & Regards,
Narasimha Rao.K

Registering a Shell Script in Oracle Applications 11i

2008-01-16T14:02:00.000+05:30

Hi all,

This post is to discuss : How to register a Host concurrent program or Shell Script in Oracle Applications 11i.

Follow Document Id : 156636.1

https://metalink.oracle.com/metalink/plsql/f?p=130:14:546696868847448757::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,156636.1,1,1,1,helvetica

Questions ( if any ) can be raised through comments.

Thanks & Regards,
Narasimha Rao.K

Create a new custom application in Oracle Application 11i

2008-01-14T17:34:00.002+05:30

Hi All,

This Post is to : Create a new custom application in Oracle Application 11i :

For Creating a new Custom Application

Please follow Document Id : 216589.1

https://metalink.oracle.com/metalink/plsql/f?p=130:14:192254477538037712::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,216589.1,1,1,1,helvetica

Questions ( if any ) can be raised through comments.

Thanks & Regards,
Narasimha Rao.K

OCFS Space Management

2008-01-14T11:11:00.001+05:30

Another useful website for Oracle technologists:
http://oss.oracle.com/

OCFS: Oracle Cluster File System

> What is OCFS?

http://oss.oracle.com/projects/ocfs/

> How do you extend datafiles on an OCFS?

Check the available extend sizes using ocfsextfinder and allocate the datafile size appropriately.

Click here to download the user guide for using OCFS

Tools for Oracle Apps DBA

2008-01-10T09:44:00.001+05:30

Hi ,

This is to share basic tools for Oracle Apps DBA s.

Thanks & Regards,
Allappsdba Blog Team

1. Free Tool for telnet / ssh client [ name : putty ]

http://the.earth.li/~sgtatham/putty/latest/x86/putty.exe

2. Free Tool for FTP / SFTP Client [ name : winscp]

http://downloads.sourceforge.net/winscp/winscp406.exe