Version related commands/scripts on appln side

Version related commands/scripts on appln side

To check the oracle application framework version
http://hostname.domain.com:8000/OA_HTML/OAInfo.jsp

Execute the following command to determine the version of OA.jsp:
ident $FND_TOP/html/OA.jsp and
ident $OA_HTML/OA.jsp

Attention: The version of OA.jsp in $FND_TOP/html should be the same as the one in $OA_HTML if the version of OA.jsp does not match do the following :

Run adadmin.
Choose the option called Maintain Application Files.
Choose the option called Copy files to destination.

If the ident command return any version information for OA.jsp then you have not installed the Oracle Applications Self Service Framework.
Note: ident is not available on SUN & AIX. In this instance please use:

adident Header $FND_TOP/html/OA.jsp and
adident Header $OA_HTML/OA.jsp

How to check whether GSM is enabled or not thru the back end
select PROFILE_OPTION_VALUE from fnd_profile_option_values where PROFILE_OPTION_ID=(select PROFILE_OPTION_ID from fnd_profile_options where PROFILE_OPTION_NAME like '%GSM%');
check for FNDSM process ps -ef|grep FND

Friday, August 27, 2010

TEMP tablespace droping and recreating

TEMP tablespace droping and recreating
SQL> DROP TABLESPACE temp;
DROP TABLESPACE temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> select * from database_properties
2 where property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP

Because this is the default temporary tablespace Oracle won't let you drop it. Here are the steps needed to drop your default temporary tablespace.

1) Create a New Temporary Tablespace
First, you will need to create a new temporary tablespace...in this example I've called the tablespace "TEMPX". Note that I purposefully made the tablespace small in size (10mb). This is because we will drop this tablespace at the end of this process.

SQL> CREATE TEMPORARY TABLESPACE TEMPX
2 TEMPFILE '/data/tempx.dbf' SIZE 10M AUTOEXTEND OFF
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
4 SEGMENT SPACE MANAGEMENT MANUAL
/

Tablespace created.


2) Make Your New Temporary Tablespace the Default Temporary Tablespace
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempx;

Database altered.


3) Drop Your Original Temporary Tablespace
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.


4) Recreate Your 'TEMP' Tablespace
SQL> CREATE TEMPORARY TABLESPACE TEMP
2 TEMPFILE '/data/temp.dbf' SIZE 1000M AUTOEXTEND OFF
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
4 SEGMENT SPACE MANAGEMENT MANUAL
/
or
"SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/d23/oraptch/ptchdata/temp01.dbf' SIZE 50M
2 AUTOEXTEND ON NEXT 50M MAXSIZE 2048M,
3 '/d23/oravis/visdata/temp02.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 2048M,
4 '/d23/oravis/visdata/temp03.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 2048M,
5 '/d23/oravis/visdata/temp04.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 2048M
6 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M BLOCKSIZE 8K;


5) Make Your 'TEMP' Tablespace the Default Temporary Tablespace Again
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


6) Drop Your 'TEMPX' Tablespace Now That It Is Not Needed
SQL> DROP TABLESPACE tempx INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Thursday, August 19, 2010

Tkprof and Explain plan and traceanalyzer

Tkprof and Explain plan

SHHO PARAMETER user_dump_dest(copy and save this parameter)


ALTER SYSTEM SET user_dump_dest='/ngs/app/rsrt/personal/';


ALTER SESSION SET timed_statistics=TRUE;


ALTER SESSION SET sql_trace=TRUE;


EXEC


RUN SQL Statements


ALTER SYSTEM SET user_dump_dest=;


ALTER SYSTEM SET timed_statistics=FALSE;


ALTER SESSION SET sql_trace =FALSE;


N.B : ORADBA ACCESS/ALTER SYSTEM Privelege Required for ALTER SYSTEM

trace will have spid in file name, from sid of session can get spid from v$process

tkprof filename.trc filename.txt sys=no explain=username/password

or

tkprof filename.trc output_filename.out sys=no explain=apps/password
sort='(prsela,exeela,fchela)'

The above mentioned command sorts the SQL by placing the worst performing
SQL statement at the top of the output file


USING AUTOTRACE
SET AUTOT ON -- Runs the SQL and gives the execution plan and statistics

SET AUTOT TRACE -- Runs the SQL but does not show the result and gives the execution plan and statistics

SET AUTOT TRACE EXP -- Only gives the execution plan without running the Query

SET AUTOT OFF -- Switching off Autotrace facility


To get trace file from a concurrent request id :
select oracle_process_id from fnd_concurrent_requests where request_id=38156196;

ORACLE_PROCESS_ID
------------------------------


trace file name will have oracle process id in its name


Create a TraceAnalyzer File
A trace file is a raw set of data produced by the Oracle Database.
TraceAnalyzer reformats the raw data so that it is easier to review. It is
a more powerful tool than TKPROF. It translates things like bind
variables and make them easy to find.
a. Download TraceAnalyzer from Note.224270.1
b. Install trace analyzer in SQL: @TRCACREA.sql;
c. Retrieve the trace file.
d. In SQL*Plus, issue a command like the following to create a TraceAnalyzer
version of the trace file: @TRCANLZR.sql UDUMP vis015_ora_22854.trc

Form

Form Trace:
To turn
trace on at the form level, go to the toolbar and navigate to
HELP -> TOOLS -> TRACE. By clicking on the word trace, a checkmark will appear
next to it, indicating that trace has been turned on. Reproduce the problem.
Navigate to HELP -> TOOLS -> TRACE, and click on the word trace to uncheck
trace and turn it off.


How will you take the forms trace?
http://hostname:port/dev60cgi/f60cgi?play=&record=collect&log=filename

Loging into forms directly
You should able to start application services without any error. If you are able to start application services without any error but still you are not able to login then you should check direct forms login.

For Release 11i : http://:/dev60cgi/f60cgi
For Release 12: http://:/forms/frmservlet
For direct forms logging, below parameter in CONTEXT file should be set to OFF. If it is not set to OFF then make below changes and run autoconfig.

OFF

Once you are able to login in forms mentioned in step 4 but still personal home page login is not working then you can confirm that issue is now with personel home page login only and no issue with APPS password.

Run AOL/J Test. Use below URL to run AOL/J Test:
http://:/OA_HTML/jsp/fnd/aoljtest.jsp


To set up forms loadbalancing:
What settings are required in each node's xml files?

s_methost
s_leastloadedhost

Are there any other variables that need to be set?

RESEARCH
=========
(Note: This is INTERNAL ONLY research. No action should be taken by the customer on this information.
This is research only, and may NOT be applicable to your specific situation.)

Note 217368.1 Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i
----> Option 2.4. Forms Server / Metrics Layer Load Balancing
------>Option 2.4.2. When using Forms Metric Server


ANSWER
=======

Please note that you need to run autoconfig after each step as given in the Note.

I. On each Web server node, run the AutoConfig Context Editor and set the following.

1. update the .xml file value
Forms Server FND_TOP context value (s_formsfndtop in $APPL_TOP/admin/.xml) to the location of FND_TOP on the Forms Server nodes.

If you have multiple Forms Server nodes, they must all have the same directory structure.

2.set the Metrics Server Load Balancing Host context value (s_leastloadedhost in $
APPL_TOP/admin/.xml) to %LeastLoadedHost%

3.Metrics Server Error URL context value (s_meterrorurl in $APPL_TOP/admin/.xml) to your default Metrics Se
rver error page, if any


II. On all Forms Metrics Client Nodes, run the AutoConfig Context Editor and set the following.

set the Metrics Server Host context value (s_methost in $APPL_TOP/admin/.xml) to the hostname that runs the Pri
mary Forms Metrics Server Process.

For example, if the Primary Forms Metrics Server Process is running on host "formsmetrics.oracle.com", then Metrics Server
Host = formsmetrics.oracle.com


TO DISABLE:
===========
Option 2.4.3. Disabling Forms Metric ServerIf you have multiple Forms Server nodes in your configuration and you use the Primary Forms Metrics Server Process and Forms Metrics

Clients to do Forms Load Balancing, and wish to disable this functionality perform the following steps.

On all Forms Metrics Client Nodes, run the AutoConfig Context Editor .
In the Context Detail screen, set the Metrics Server Load Balancing Host context value (s_leastloadedhost in $APPL_TOP/admin/.xml) to the value of "%s_hostname%" context

variable:

Metrics Server Load Balancing Host = myserver
[ where "myserver" is the name of this host and value of %s_hostname%. ]

Save your changes.
Generate new configuration files by running AutoConfig as described in MetaLink Note 165195.

Tablespace, datafiles, tables, index related scripts

The following is the query to find the total space, used space and free space for TEMP tablespace.
SELECT TABLESPACE_NAME "Tablespace", (TOTAL_BLOCKS * 8192) "Total Size", (USED_BLOCKS * 8192 ) "Used" ,(FREE_BLOCKS * 8192) "Free" , (MAX_USED_BLOCKS * 8192 ) "Max Used" FROM V$SORT_SEGMENT;



To find temporary tablespace usage query
"SELECT s.sid ""SID"",s.username ""User"",s.program ""Program"", u.tablespace ""Tablespace"",
u.contents ""Contents"", u.extents ""Extents"", u.blocks*8/1024 ""Used Space in MB"", q.sql_text ""SQL TEXT"",
a.object ""Object"", k.bytes/1024/1024 ""Temp File Size""
FROM v$session s, v$sort_usage u, v$access a, dba_temp_files k, v$sql q
WHERE s.saddr=u.session_addr
and s.sql_address=q.address
and s.sid=a.sid
and u.tablespace=k.tablespace_name;

To find free space,alloated space in TEMP tablespace
select TABLESPACE_NAME, BYTES_USED/1024/1024,BYTES_FREE/1024/1024 from v$temp_space_header;
TABLESPACE_NAME BYTES_USED/1024/1024
BYTES_FREE/1024/1024
------------------------------ --------------------
--------------------
TEMP 1100
900


select TABLESPACE_NAME,BYTES/1024/1024 from dba_temp_files where TABLESPACE_NAME ='%TEMP%';
select TABLESPACE_NAME,BYTES/1024/1024 from dba_temp_files;
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
TEMP 1100

find if it is autoextensible:
select substr(file_name,1,35),AUTOEXTENSIBLE,bytes/1024/1024 from dba_temp_files;
SUBSTR(FILE_NAME,1,35) AUT
BYTES/1024/1024
--- ---------------
/ge2vis/oradata/data01/temp01.dbf NO
1100

add tempfile or resize:
ALTER TABLESPACE TEMP ADD TEMPFILE '/dbtemp/PROD/temp01.dbf'
SIZE 2000M REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 2000M;

or
alter database tempfile '/ge2vis/oradata/data01/temp01.dbf' resize 2000m ;

How can one see who is using a temporary segment?
For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.
All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment.

If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT

From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples:
select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from sys.v_$session s, sys.v_$sort_usage u
where s.saddr = u.session_addr
/

select s.osuser, s.process, s.username, s.serial#,
sum(u.blocks)*vp.value/1024 sort_size
from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp
where s.saddr = u.session_addr
and vp.name = 'db_block_size'
and s.osuser like '&1'
group by s.osuser, s.process, s.username, s.serial#, vp.value
/

How would you check if the tablespace is in backup mode ?- Check in v$backup
SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b
WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;


Creating dictionary mangaed temporary tablespsce
create tablespace temp datafile '/d14/oracle/proddata/temp01.dbf' size 2048M temporary default storage (initial 5m next 5m pctincrease 0);


To find ablespace free size
set pagesize 0 linesize 200 heading off feedback off
column total_bytes format 999999999999
column free_bytes format 999999999999
select d.tablespace_name,
d.num_files num_files,
f.bytes free_bytes,
-- d.file_bytes file_bytes,
d.maxbytes bytes ,
round((d.file_bytes/d.maxbytes)*100,0) percent_used
from (select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) f,
(select tablespace_name, count(distinct(file_id)) num_files,
sum(bytes) file_bytes,
sum(decode(autoextensible,'YES',maxbytes,bytes)) maxbytes
from dba_data_files group by tablespace_name) d
where d.tablespace_name = f.tablespace_name
and d.tablespace_name not like 'DW%'
and d.tablespace_name not like 'RBS%'
and d.tablespace_name not like '%UNDO%'
and d.tablespace_name not like '%TEMP%'
order by d.tablespace_name;
quit;

To find free space , available space and % of used space in a tablespace use
SELECT tba, free_spc, avail_spc, free_spc/avail_spc "free %"
FROM
( SELECT tablespace_name TBA,
SUM ( bytes ) free_spc
FROM dba_free_space
GROUP BY tablespace_name ),
( SELECT tablespace_name TBB,
SUM ( bytes ) avail_spc
FROM dba_data_files
GROUP BY tablespace_name )
where TBA='APPS_TS_INTERFACE'

SELECT tablespace_name ,SUM ( bytes ) free_spc
FROM dba_free_space
where tablespace_name ='APPS_TS_INTERFACE'
group by tablespace_name ;

select a.tablespace_name, b.tot_space, a.free_space from ( select tablespace_name, sum (bytes) free_space from dba_free_space group by tablespace_name) a, ( select tablespace_name, sum( bytes) tot_space from dba_data_files group by tablespace_name )b where a.tablespace_name = b.tablespace_name and ( a.free_space / b.tot_space ) * 100 < 10;

select tba,free_spc,avail_spc, free_spc/avail_spc "free%" FROM
(select tablespace_name TBA, SUM(bytes) free_spc FROM dba_free_space GROUP BY tablespace_name),
(select tablespace_name TBB, SUM(bytes) avail_spc FROM dba_data_files GROUP By tablespace_name)
WHERE TBB=TBA(+) AND free_spc/avail_spc <.2 ;

To check which all tablespaces are less than 15 % free etc
select d1.tablespaces_name , sum(d2.bytes)/sum(d1.bytes) “free”
from dba_data_files d1, dba_free_space d2
where d1.tablespace_name=d2.tablespace_name
group by d1.tablespace_name having ( sum(d2.bytes)/sum(d1.bytes) ) < 0.15;

Table move to new tablespace command
rebuild is new tablespace created before and added 10 GB space in that
alter TABLE owner.table_name move tablespace REBUILD storage (initial 500m next 500m minextents 5 maxextents unlimited pctincrease 0);
alter index owner.name rebuild online tablespace REBUILD parallel 2;

alter table AP.AP_SELECTED_INVOICES_ALL move tablespace reorg;
"SQL> alter table AP.AP_SELECTED_INVOICES_ALL move tablespace reorg;
alter table AP.AP_SELECTED_INVOICES_ALL move tablespace reorg
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'REORG'

SQL> ALTER USER AP QUOTA unlimited on reorg;
User altered."

Set auto extend on for all data files in the database with a maxsize of 2GB
Login as Oracle user
You can run the following SELECT to generate auto extend script for all data files and spool it to a file.
$ sqlplus system/xxxx
SQL> set pagesize 0
SQL> set linesize 140
SQL> set feedback off
SQL> spool autoextfile.sql
SQL> select 'alter database datafile '||chr (39)||file_name||chr (39)||' autoextend on next 10M maxsize 2000m;' from dba_data_files;
SQL> exit;
Edit the file autoextfile.sql appropriately and then run it.
$ sqlplus system/ @autoextfile.sql


Database Corrupted Block Information
select * from v$database_block_corruption;
select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=&file_no and &block_number between block_id and block_id + blocks -1;

TO Know tablespaces which have less than 20% freee space
SQL> select tba, free_spc, avail_spc, free_spc/avail_spc "free%" from
2 (select tablespace_name TBA, sum(bytes) free_spc from dba_free_space group by tablespace_name),
3 (select tablespace_name TBB, sum(bytes) avail_spc from dba_data_files group by tablespace_name)
4 where TBB=TBA(+) and free_spc/avail_spc<.2;


command to rebuild the QP_QUALIFIERS_N1 and the QP_QUALIFIERS_N2 indexes and gather the stats on the QP_QUALIFIERS table by logging as APPS
alter session set db_file_multiblock_read_count = 32;
alter index QP.QP_QUALIFIERS_N2 rebuild;
alter index QP.QP_QUALIFIERS_N1 rebuild;
exec fnd_stats.gather_table_stats(OWNNAME=>'QP',TABNAME=>'QP_QUALIFIERS',percent => '100', granularity => 'ALL', partname => NULL,cascade =>true, degree => 4 );
alter session set db_file_multiblock_read_count = 8;


To find HWM , blocks is HWM
SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name ='AP_SELECTED_INVOICE_CHECKS_ALL';

Command to Rebuild index and gather stats
spool ind.sql
select 'alter index '||index_name||' rebuild nologging parallel 8;' from user_indexes
where index_name not like 'SYS%' and index_name not like '%_FK_%' and index_name not like '%_PK';
spool off
few more eg:
alter index GL.GL_INTERFACE_CONTROL_N1 rebuild parallel 4 nologging;
alter index APPLSYS.FND_HELP_TARGETS_N1 rebuild online nologging parallel 8;
select 'alter index '||owner||'.'||index_name||' rebuild online parallel tablespace nologging;' from dba_indexes where owner=;

exec fnd_stats.gather_table_stats(owner=>'GL',tabname=>'GL_INTERFACE',percent=>10);


command to move table, index, table partition, index partition of owner = MSC
o/p like
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_0 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_1023 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_2023 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_SUPPLIES move partition ALLOC_SUPPLIES_0 tablespace APPS_TS_TX_DATA;

.
.
.
.
.
.
alter INDEX MSC.MSC_X_EXCEPTION_DETAILS_U1 rebuild online tablespace APPS_TS_TX_IDX;
alter INDEX MSC.MSC_ZONE_REGIONS_N1 rebuild online tablespace APPS_TS_TX_IDX;
alter INDEX MSC.MSC_ZONE_REGIONS_N2 rebuild online tablespace APPS_TS_TX_IDX;


Set pages 0
Set trimspool on
Set lines 300
Spool moveobj.sql
select 'alter ' || decode( segment_type, 'TABLE', 'TABLE', 'INDEX','INDEX','TABLE PARTITION','TABLE','INDEX PARTITION','INDEX')
|| ' ' || owner||'.'|| segment_name || decode(segment_type, 'TABLE',' move ','INDEX',' rebuild online ','TABLE PARTITION',' move partition ','INDEX PARTITION',' rebuild partition ') || decode (segment_type, 'TABLE PARTITION',partition_name,'INDEX PARTITION',partition_name||' online ') || ' tablespace '||tablespace_name ||';' from dba_segments
where segment_type in ( 'TABLE', 'INDEX', 'TABLE PARTITION','INDEX PARTITION') and owner = 'MSC' and segment_name not like '%MSC_ERRORS%' order by segment_type desc, segment_name ASC
/

How to create index on a table: As Apps Database user in PROD, run the following sql statements:
SQL> drop index po.PO_HEADERS_N5 ;Make sure the ""index dropped "" message comes through.
SQL> CREATE INDEX PO.PO_HEADERS_N5 ON PO.PO_HEADERS_ALL
(WF_ITEM_TYPE, WF_ITEM_KEY)
LOGGING
TABLESPACE PO_IDX
PCTFREE 0
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 48K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 4
FREELIST GROUPS 4
BUFFER_POOL DEFAULT
)
NOPARALLEL;
Make sure the ""index created"" message comes through.

To take backup of table:
create table XYZ as select * from orig_name ;

Find if all datafiles are autoextensible
select distinct AUTOEXTENSIBLE from dba_data_files ;
o/p is ‘NO’ or ‘YES’

script to create all tablespaces with diffrent mount locations

set pages 0
set feedback off
set lines 200
spool tbscreate.sql
select 'create tablespace '||tablespace_name||' datafile ''/topusi/oradata/data01/'||lower(tablespace_name)||'01.dbf'' size '|| bytes *1.0 || ' autoextend on maxsize 1999m;'
from dba_data_files
order by tablespace_name
/
select 'create tablespace '||tablespace_name||' tempfile ''/topusi/oradata/data01/'||lower(tablespace_name)||'01.dbf'' size '|| bytes *1.0 || ' autoextend on maxsize 1999m;'
from dba_temp_files
order by tablespace_name
/
spool off


To create a tablespace: eg XDO
create tablespace xdod datafile '/d01/oradata/data03/xdod01.dbf' size 20m autoextend on maxsize 2000m;

To autoextend all datafiles
Ensure all datafile are autoextensible: Spool & Run this script & fix it for specific datafiles of size more than 4000M: -
select 'alter database datafile '||''''||file_name||''''||' autoextend on next 50M maxsize 4000M;' from dba_data_files;

You can run the following SELECT to generate auto extend script for all data files and spool it to a file
Edit the file autoextfile.sql appropriately and then run it. $sqlplus system/ @autoextfile.sql
$ sqlplus system/
SQL> set pagesize 0
SQL> set linesize 140
SQL> set feedback off
SQL> spool autoextfile.sql
SQL> select 'alter database datafile '||chr(39)||file_name||chr(39)||' autoextend on next 10M maxsize 2000m;' from dba_data_files;
SQL> exit;


Before resizing tablespace check :to make sure that there is space or not left for the tablespace and then resize or add datafile
select tablespace_name, max(bytes/1024/1024) from dba_free_space
where tablespace_name ='APPLSYSD' group by tablespace_name;


usn corresponds to segment_id in dba_rollback_segs;
select usn , optsize,extents,hwmsize from v$rollstat where usn=2;
select SEGMENT_NAME,OWNER,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_rollback_segs where SEGMENT_ID=2;

To list all tables
select * from tab where tname like “%…%’;

To check constraint name in a table
select constraint_name from dba_constraints;

Adding a datafile
alter tablespace CNX add datafile '/data/oravis/visdata/cnx_03.dbf' size 1200M AUTOEXTEND ON NEXT 20M MAXSIZE 2000M;
alter tablespace GLD add datafile '/data/oravis/visdata/gld02.dbf' size 1000M; (if dbf > 1.5 GB)
alter database datafile '/data/oravis/visdata/xkbx02.dbf' resize 1500M;(if dbf < 1.5 GB)

TO FIND THE DATAFILE MOUNT POINT LOCATION ADN THE TOTAL SIZE OF DATABASE
select substr(file_name,6,9),sum(bytes)/1024 from dba_data_files group by substr(file_name,6,9);
change (6,9) accordingly
select substr(file_name,1,35),AUTOEXTENSIBLE,sum(bytes)/1024/1024 from dba_data_files where TABLESPACE_NAME='APPS_TS_INTERFACE';

SUBSTR(FILE_NAME,1,35)
--------------------------------------------------------------------------------
AUT BYTES/1024/1024
--- ---------------
/d01/oradata/data03/a_int01.dbf
YES 1800


To create a datafiel for a schema which use 16k databalock
Set the parameter db_16k_cache_size=250MB in the init.ora parameter
Create a tablespace with 16k data block size by using the following command
Create tablespace schemaname datafile ‘/d03/visdata/data01.dbf’ size 500M autoextend on maxsize 8000M blocksize 16k;

Wednesday, August 18, 2010

Statspack

To implement statspack:

alter session set tracefile_identifier=
Note ids 149124.1, 149121.1 , 149113.1
Steps for installing statspack:
1) Bring down application services
2) Check for invalid objects
3) Log on as sysdba (sqlplus "/ as sysdba")
4) Run the following script:
@ $ORACLE_HOME/rdbms/admin/sprecreate.sql
Provide the perfstat user password (keep it same as username) and the default tablespace (PERFSTAT - already created) and temporary tablespace (TEMP) when prompted.
5) Check the spcreate.lis file to if there are any errors
6) Check to see if there any more invalids than there were before installing. If there are then recompille the invlaid objects.
7) Log on as the perfstat user
8) Execute the following package:
SQL> exec statspack.snap();
9) Make sure the package completes without errors.
10) Bring the application services back up

How to install statspack
Shut down Applications
Create the PERFSTAT tablespace ( Ensure perfstat user’s default tablespace is set to this tablespace during install)
Install the statspack utility
Verify Install
bring up the Application Services
The detailed level steps for statspack install is below:
SQL> CREATE TABLESPACE perfstat
DATAFILE '/d05/oracle/visdata/perfstat02.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K max size 4096m
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT
ONLINE;
Install Statspack. At the prompt specify PERFSTAT password. Keep it same as username. Also give
default tablespace as perfstat tablespace and temporary tablespace as TEMP.
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
Verfiy its been installed properly
SQL> connect perfstat/perfstat
SQL> exec statspack.snap(i_snap_level=>10);


steps for dropping perfstat user and tablespace:

DROP the PERFSTAT USER by running the following sql from the DATABASE ORACLE HOME
sqlplus '/as sysdba';
@$ORACLE_HOME/rdbms/admin/spdrop.sql

2.drop package body SYS.STATSPACK_EXT_NEW;

3.drop trigger SYS.LOG_ERRORS_TRIG;

4.DROP TABLESPACE PERF_EXT including contents and datafiles;

Issues

To identify table and column name from where OAM (Oracle Application Manager) was populating “Application System Name” in Applications Dashboard & Site Map

SELECT table_name, column_name
FROM all_tab_columns
–WHERE column_name LIKE ‘%’


SELECT table_name, column_name
FROM all_tab_columns
WHERE column_name LIKE ‘APPLICATION%SYSTEM%NAME%’;

and output was

TABLE_NAME                     COLUMN_NAME
—————————— ——————————
AD_APPL_TOPS                   APPLICATIONS_SYSTEM_NAME
FND_PRODUCT_GROUPS             APPLICATIONS_SYSTEM_NAME
BIN$TQikJhLucVHgRAADuqIcxA==%content APPLICATIONS_SYSTEM_NAME

APPLICATIONS_SYSTEM_NAME in FND_PRODUCT_GROUPS was pointing to source instance where as it should be same as SID.

Fix was simply to update table FND_PRODUCT_GROUPS like

Backup existing table as
SQL>create table FND_PRODUCT_GROUPS_BAK as select * from FND_PRODUCT_GROUPS;

Update table with target name
SQL> update FND_PRODUCT_GROUPS
set APPLICATIONS_SYSTEM_NAME = ‘’ ;
commit;


Legal statement on login page is not apprearing correctly.

1) Login to VIS Apps servers and navigate to the $OA_HTML directory
2) Make a copy of the AppsLocalLogin.jsp file
3) Open the AppsLocalLogin.jsp in vi
4) search for the keyword "legal". You should see the following line

StyledTextBean legalMessage = new StyledTextBean();

5) Replace the word "StyledTextBean" with "RawTextBean"
6) Please note that the replace needs to made to 2 places on the same line
7) Stop the Apache
8) Navigate to the $OA_HTML/.. directory
9) Delete the contents of the directory _pages
10) Start the Apache and login to the database to see if the legal message appeara correctly.


Rebuild/recreate help search index
To recreate the Index FND_LOBS_CTX, we can do any of these two although 1st one is preferable. Please perform this steps once whole patching gets completed.

1> Log on with the System Administrator responsibility, and run the concurrent program ""Rebuild Help Search Index"".
2> conn as apps/
SQL>@$FND_TOP/sql/aflobbld.sql applsys apps


To reproduce ora-600 error and to see the trace file generated in udump
ORA-0600 [17035] using this while connected as APPS:
select fnd_web_sec.URLEncrypt('hello', 'world') from dual;

To make login authentication off for direct login to http://hostname.domainname/dev60cgi/f60cgi
update fnd_nodes set server_id='OFF' where node_name='AUTHENTICATION';

Error:“ORA-1653: unable to extend table APPLSYS.FND_ENV_CONTEXT”
First find out all the next_extent parameters for FND_ENV_CONTEXT
select partition_name, initial_extent, next_extent, pct_increase
from dba_tab_partitions
where table_name = ‘FND_ENV_CONTEXT’;

For any of these that are over 5 MB run the following..
alter table applsys.FND_ENV_CONTEXT
modify partition
storage (next 1M pctincrease 0);

also check resize the tablespace corresponding to this


Yellow bar isse resolution
take a backup of these files:
$APPL_TOP/admin/out/adcert.txt
$APPL_TOP/admin/adsign.txt
$APPL_TOP/admin/appltop.cer
$HOME/identitydb.obj .

copy the above four files from another apps node

make sure these files have the same sizes on both

then from adadmin generate jar files with force option
before doing this, make sure all the processes are down
if some process does not come down, then kill it
clean up the _pages and move the apache logfiles and jserv logfiles


The following steps need to be completed to manually generate the tnsnames.ora and listener.ora files due to a bug with ATG:
cd $TNS_ADMIN
ls -l
total 8
-rw-r--r-- 1 applvis dba 6821 Oct 3 09:53 tnsnames.ora
$
$AD_TOP/bin/adgentns.pl contextfile=$APL_TOP/VIS_hostname.xml
ls -l
total 8
-rw-r--r-- 1 applvis dba 1939 Oct 5 11:55 listener.ora
-rw-r--r-- 1 applvis dba 2891 Oct 5 11:55 tnsnames.ora

Sunday, August 15, 2010

Apache or web service

Verify that both httpd.conf and httpd_pls.conf have the same location for LockFile parameter
cd $IAS_ORACLE_HOME/Apache/Apache/conf" "grep LockFile httpd*conf" and this should return the LockFile location as /var/Apache/VIS/ and not NFS.


Load balance setup:
Following Documents have been followed to enable the HTTP Layer Hardware Balance.
1.) HTTP Layer Hardware Balancer 217368.1
In the Context file (XML File) the following configuration values are set as part of the Hardware Load Balancer
Web entry point Host to the HTTP load-balancer machine name (prod) -------- the name of the Load Balancer device
Web entry point Domain to the HTTP load-balancer domain name (abc.com)
Web entry protocol to the HTTP load-balancer protocol e.g. "http" or "https" (http)
Active Web Port to the value of the HTTP load-balancer's external port (8000)
Login Page to include <"Web entry protocol">://<"Web Host entry point">.<"Web domain entry point">:<"Active Web Port">
should like below
http://prod.abc.com:8000/oa_servlets/AppsLogin


Forms Listener Servlet 201340.1 (Requirement in Step 1)
Enable/Disable the Forms Listener Servlet
Edit the context file ($APPL_TOP/admin/.xml)
Locate the following two variables:


to enable set to /forms/formservlet
eg: /forms/formservlet
to disable set to blank
eg:

to enable set to blank
eg:
to disable set to #

Steps for dropping and creating undo tablespace:

Comment out the undo setting in the init.ora. Shutdown the database normal and startup. Drop the UNDO tablespace, remove the UNDO datafiles and recreate with five datafilies with initial size of 2048M for a total of 10GB. Uncomment the undo parameters and restart the instance.

su - opreprod
sqlplus '/as sysdba';

set lines 1000
column file_name format a50
set pages 100

1.select FILE_NAME,TABLESPACE_NAME from dba_data_files where tablespace_name like 'APPS_UNDOTS1';

2. Create a new undo tablespace:

create undo tablespace second_undo datafile '/d03/opreprod/preproddata/s_undo01.dbf' size 100M autoextend on maxsize 2000M;

3. Make this tablespace default undo tablespace:

alter system set undo_tablespace= second_undo;

4. Drop the old undo tablespace which came from production:

drop tablespace APPS_UNDOTS1 including contents and datafiles;

(check whether all the datafiles that are returned by select statement in step 1 are deleted from their respective locations, if not delete them manually)

5.Recreate the tablespace APPS_UNDOTS1 with required space.

create undo tablespace APPS_UNDOTS1 datafile '/d03/opreprod/preproddata/undo01.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo02.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo03.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo04.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo05.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo06.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo07.dbf' size 500M autoextend on maxsize 8000M;

6. make APPS_UNDOTS1 undo tablespace a default one:

alter system set undo_tablespace= APPS_UNDOTS1;

7. drop ‘second_undo’ undo tablespace:

drop tablespace second_undo including contents and datafiles;

Procedure to drop and recreate log files

Perform following DDL/DCL commands after loging to database with OS command sqlplus "/ as sysdba".

ALTER DATABASE ADD LOGFILE GROUP 6 ('/orafiles/vis/redo/redo06a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 7 ('/orafiles/vis/redo/redo07a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 8 ('/orafiles/vis/redo/redo08a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 9 ('/orafiles/vis/redo/redo09a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 10 ('/orafiles/vis/redo/redo10a.dbf') SIZE 150m;

reapeat "alter system switch logfile;" command till CURRENT log switch reaches group 6 or 7 or 8
or 9 or 10 and old redo log group members 1-5 reaches in INACTIVE status (not in CURRENT or ACTIVE status)

alter system switch logfile;

set lines 200
select * from v$log;

The status should be something like this

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 ..... 15728640 1 YES INACTIVE 1.9618E+12 04-AUG-08
2 1 ..... 15728640 1 YES INACTIVE 1.9618E+12 04-AUG-08
3 1 ..... 10485760 1 YES INACTIVE 1.9618E+12 04-AUG-08
4 1 ..... 15728640 1 YES INACTIVE 1.9618E+12 04-AUG-08
5 1 ..... 15728640 1 YES INACTIVE 1.9618E+12 04-AUG-08
6 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
7 1 ..... 157286400 1 YEs INACTIVE 1.9618E+12 04-AUG-08
8 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
9 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
10 1 ..... 157286400 1 YES CURRENT 1.9618E+12 04-AUG-08


if redo log group 1 to 5 are INACTIVE drop them, make surw ecorresponding OS file is removed from relevant
directory too and then recreate dropped groups back (A GROUP HAS TO BE DROPPED WHEN IT INACTIVE)

...check group 1 is INACTIVE the drop it
ALTER DATABASE DROP LOGFILE GROUP 1;

...check group 2 is INACTIVE the drop it
ALTER DATABASE DROP LOGFILE GROUP 2;

...check group 3 is INACTIVE the drop it
ALTER DATABASE DROP LOGFILE GROUP 3;

...check group 4 is INACTIVE the drop it
ALTER DATABASE DROP LOGFILE GROUP 4;

...check group 5 is INACTIVE the drop it
ALTER DATABASE DROP LOGFILE GROUP 5;

After dropping an online redo log group, make sure that the drop completed successfully, and then use
the appropriate operating system command to delete the dropped online redo log files.

rm -f /orafiles/vis/redo/redo01a.dbf
rm -f /orafiles/vis/redo/redo02a.dbf
rm -f /orafiles/vis/redo/redo03a.dbf
rm -f /orafiles/vis/redo/redo04a.dbf
rm -f /orafiles/vis/redo/redo05a.dbf

The add them again with redo log memeber size 150M

ALTER DATABASE ADD LOGFILE GROUP 1 ('/orafiles/vis/redo/redo01a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/orafiles/vis/redo/redo02a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/orafiles/vis/redo/redo03a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/orafiles/vis/redo/redo04a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/orafiles/vis/redo/redo05a.dbf') SIZE 150m;

The final status should be somthing like this...
set lines 200
select * from v$log;

The status should be something like this
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 ..... 157286400 1 NO UNUSED 1.9618E+12 04-AUG-08
2 1 ..... 157286400 1 NO UNUSED 1.9618E+12 04-AUG-08
3 1 ..... 157286400 1 NO UNUSED 1.9618E+12 04-AUG-08
4 1 ..... 157286400 1 NO UNUSED 1.9618E+12 04-AUG-08
5 1 ..... 157286400 1 NO UNUSED 1.9618E+12 04-AUG-08
6 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
7 1 ..... 157286400 1 YEs INACTIVE 1.9618E+12 04-AUG-08
8 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
9 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
10 1 ..... 157286400 1 YES CURRENT 1.9618E+12 04-AUG-08

The perform multilple switch logs till all files come out of UNUSED status...something like the following..

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

The status should be something like this
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
2 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
3 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
4 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
5 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
6 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
7 1 ..... 157286400 1 YEs INACTIVE 1.9618E+12 04-AUG-08
8 1 ..... 157286400 1 YES CURRENT 1.9618E+12 04-AUG-08
9 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
10 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08

RAC

For Oracle RAC installations:
a. Start the database as follows, where db_name is the database name:

$ srvctl start database -d db_name


To stop and start 10g rac database (10g crs )
Database:
server1: login as oraprod
srvctl stop database -d PROD
lsnrctl stop LISTENER_server1

server2: login as oraprod
lsnrctl stop LISTENER_server2

server3: login as oraprod
lsnrctl stop LISTENER_server3

CRS:

server1, server2, server3: login as root
/d02/oracrs/prodcrs/10.2.0/bin/crsctl stop crs

-Repeat above steps on all servers indicated above

start procedure
CRS:

server1, server2, server3: login as root
/d02/oracrs/prodcrs/10.2.0/bin/crsctl start crs

-Repeat above steps on all servers indicated above


Database:

server1: login as oraprod
---> DO NOT START MANUALLY, CRS SHOULD START IT UP AUTOMATICALLY.
---> IF CRS FAILS TO STARTUP THE DB, EXECUTE: srvctl start database -d PROD
lsnrctl start LISTENER_server1

server2: login as oraprod
lsnrctl start LISTENER_server2

server3: login as oraprod
lsnrctl start LISTENER_server3

crs commands
crs_stat -t :Show HA resource status (hard to read)
crsstat :Ouptut of crs_stat –t formatted better
ps -ef|grep d.bin :crsd.bin evmd.bin ocssd.bin
crsctl check crs :css, crs, evm appear healthy
crsctl stop crs :Stop crs and all other services
crsctl disable crs :Prevent crs from starting on reboot
crsctl enable crs :Enable crs start on reboot
crs_stop -all :Stop everything
crs_start -all :Start everything

Go to following location and check the current CRS version.
cd /CRS/app/10.2.0/bin
./crsctl query crs softwareversion
./crsctl query crs activeversion



To configure AutoConfig to generate the failover aliases
To generate the failover aliases use the database tier context variable s_alt_service_instances.
Specify a comma separated list of "servicename:instance" to use connect time failover management.

For example 'SERVICE_NAME:INSTANCE_NAME1,SERVICE_NAME:INSTANCE_NAME2' will generate a TNS Alias in the tnsnames.ora file that fails over to INSTANCE_NAME1 when the current instance is not available. If INSTANCE_NAME1 is not available it fails over to INSTANCE_NAME2.

So Update the context variable s_alt_service_instances in the database tier context file applying the failover rules as described above.
Run AutoConfig on all database tiers.
Run AutoConfig on all application tiers.
These steps will generate tns aliases _FO with description lists as configured in s_alt_service_instances. These aliases will still not be used anywhere. Have to set the two task variables like s_tool_twotask to actually use these aliases.

Note: On database versions that are 8.1.7.4 or higher the generated alias _FO can only be used for failover. On 8.0.6 the generated alias can't be used for failover. However, it can be used for load balancing.

undo for rac
RAC01.undotablespace=undotbs3
RAC02.undotablespace=undotbs2
alter system set undo_tablespace=undotbs1 sid='RAC01';

Threads in rac
RAC01.thread=1
RAC02.thread=2
alter database add logfile thread 2 group 4
alter databse add logfile thread 2 group 5
alter database enable thread 2


srvctl command
srvctl start instance -d RACDB -i RACDB1,RACDB2
srvctl stop instance -d RACDB -i RACDB1,RACDB2
srvctl start database -d RACDB -o open

srvctl start|stop instance -d -i
[-o open|mount|nomount|normal|transactional|immediate|abort]
[-c connect_str | -q]

srvctl start|stop database -d
[-o open|mount|nomount|normal|transactional|immedaite|abort]
[-c connect_str | -q]

srvctl modify database -d RACDB -y manual

Here are some of the commands for managing database-related services:
srvctl start instance -d orcl -i orcl2 :Start an instance
srvctl start database -d orcl :Start all instances
srvctl stop database -d orcl :Stop all instances and close database
srvctl stop instance -d orcl -i orcl2 :Stop an instance
srvctl start service -d orcl -s rac :Start a service
srvctl stop service -d orcl -s rac :Stop a service
srvctl status service -d orcl :Check status of a service
srvctl status instance -d orcl -i orcl1 :Check an individual instance
srvctl status database -d orcl :Check status of all instances
srvctl start nodeapps -n server1 :Start gsd, vip, listener, and ons.
srvctl stop nodeapps -n server2 :Stop gsd, vip, listener, and ons
For help with all commands:
srvctl -h

For Detailed Help:
srvctl start database -h
srvctl add service -h
srvctl relocate service –h


Check out current configuration information:
srvctl config database
Displays the configuration information of the cluster database.

srvctl config service
Displays the configuration information for the services.

srvctl config nodeapps
Displays the configuration information for the node applications.

srvctl config asm
Displays the configuration for the ASM instances on the node.

To look for all the registered services for a database:
srvctl config service -d DBATOOLS

To get VIP information:
srvctl config nodeapps -n xxxx708 -a

VIP exists.: /xxxx708-vip/xx.xxx.xx.xx/xxx.xxx.xxx.0/eth4

or

ifconfig -a

Look for interface with two IPs
When node 1 comes back up, it will start it’s CRS stack. It will take back
it’s VIP. In order to fail the service back, you would need to run
srvctl relocate
The following example is relocating the service from node 2 back to node 1:

srvctl relocate service -d V10SN -s SRV_AVAIL -i V10SN2 -t V10SN1

To Forcefully remove a database from the OCR

srvctl remove database with -f (force option):
Then use belwo to add again
srvctl add database -d DB -o $­ORACLE_HOME



alter parameter on rac db
alter system set dpname SCOPE=MEMORY sid='sid|*';
alter system RESET dpname SCOPE=MEMORY sid='sid';


Stop/Start Oracle RAC

1. Stop Oracle 10g on one of RAC nodes.
$ export ORACLE_SID=RAC1
$ srvctl stop instance -d RAC -i RAC1
$ srvctl stop asm -n orarac1
$ srvctl stop nodeapps -n orarac1

2. Start Oracle 10g on one of RAC nodes.
$ export ORACLE_SID=RAC1
$ srvctl start nodeapps -n orarac1
$ srvctl start asm -n orarac1
$ srvctl start instance -d RAC -i RAC1

3. Stop/start Oracle 10g on all RAC nodes.
$ srvctl stop database -d RAC
$ srvctl start database -d RAC


Check status of Oracle RAC
$ srvctl status database -d RAC
Instance RAC1 is running on node orarac1
Instance RAC2 is running on node orarac2

$srvctl status instance -d RAC -i RAC1
Instance RAC1 is running on node orarac1

$srvctl status asm -n orarac1
ASM instance +ASM1 is running on node orarac1

$srvctl status nodeapps -n orarac1
VIP is running on node: orarac1
GSD is running on node: orarac1
PRKO-2016 : Error in checking condition of listener on node: orarac1
ONS daemon is running on node: orarac1


Check Oracle Listener on nodes
$ srvctl config listener -n orarac1
orarac1 LISTENER_ORARAC1
$ lsnrctl start LISTENER_ORARAC1

Check configuration of ORACLE RAC,ASM,nodeapps
$srvctl config database -d RAC
orarac1 RAC1 /space/oracle/product/10.2.0/db_2
orarac2 RAC2 /space/oracle/product/10.2.0/db_2

$srvctl config asm -n orarac2
+ASM2 /space/oracle/product/10.2.0/db_2

$srvctl config nodeapps -n orarac1 -a -g -s -l
VIP exists.: /orarac1-vip.abc.lan/xx.xx.xx.xx/xxx.xxx.xxx.x/Public
GSD exists.
ONS daemon exists.
Listener exists.


Check, backup, restore OCR
$ocrconfig -showbackup

$ocrconfig -export /data/backup/rac/ocrdisk.bak

To restore OCR, it must stop Clusterware on all nodes before.
$ocrconfig -import /data/backup/rac/ocrdisk.bak

$cluvfy comp ocr -n all //verification

$ocrcheck //check OCR disk usage

stats, reorg, index rebuild

Index rebuild commands
APPS.sql
REM select 'alter index '||owner||'.'||INDEX_NAME||' rebuild online ;'
REM from dba_indexes where owner in ('APPS','APPLSYS') order by owner;
set time on;
set serverout on ;
spool APPS.lst
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
alter index APPLSYS.AD_APPLIED_PATCHES_N1 rebuild online ;
alter index APPLSYS.AD_APPLIED_PATCHES_N2 rebuild online ;


HR.sql
REM select 'alter index '||owner||'.'||INDEX_NAME||' rebuild online ;'
REM from dba_indexes where owner in 'HR' order by owner;
set time on;
set serverout on ;
spool HR.lst
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
alter index HR.AME_ACTIONS_FK1 rebuild online ;
alter index HR.AME_ACTIONS_PK rebuild online ;
alter index HR.SYS_IOT_TOP_2009617 rebuild online ;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
spool off;
REM No of Rows selected 2941
alter index HR.SYS_IOT_TOP_2009617 rebuild online ;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
spool off;
REM No of Rows selected 2941


CUSTOM.sql
REM select 'alter index '||owner||'.'||INDEX_NAME||' rebuild online ;'
REM from dba_indexes where
REM owner in ('MIGRATE','FOGLIGHT','NOETIX_SYS','RX_AM_AGENT',
REM 'IWATCH','USIDBA','MONITOR','CUST','SPOTLIGHT_APPS','XXCONV','TTEC_AUS')
REM order by owner;
set time on;
set serverout on;
spool CUSTOM.lst
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
alter index CUST.TTEC_H_ES_CONTRACT_N1 rebuild online ;
alter index CUST.TTEC_H_ES_COST rebuild online ;
alter index XXCONV.XXTTEC_CONV_ERRORS_U1 rebuild online ;

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
spool off;

OTHER.sql

REM select 'alter index '||owner||'.'||INDEX_NAME||' rebuild online ;'
REM from dba_indexes where owner not in
REM ('MIGRATE','FOGLIGHT','NOETIX_SYS','RX_AM_AGENT',
REM 'IWATCH','USIDBA','MONITOR','CUST','SPOTLIGHT_APPS',
REM 'XXCONV','APPS','HR','TTEC_AUS','SYS','SYSTEM','CTXSYS','APPLSYS')
REM order by owner;
set time on;
set serverout on;
spool OTHER.lst
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
alter index ABM.ABM_CALC_PARAMS_LOG_N8 rebuild online ;
alter index ABM.ABM_CALC_PARAMS_LOG_N9 rebuild online ;
alter index ABM.ABM_CO_DAT_N1 rebuild online ;
alter index XXEIS.EIS_BALANCE_ASG_ID_IDX rebuild online ;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
spool off;
REM Total No Of 36002 rows selected.

one eg command to compute statistics at the time of creation of index
create index applsys.WF_ITEMS_N3 on applsys.WF_ITEMS (ITEM_KEY, ITEM_TYPE, END_DATE)
tablespace APPS_TS_TX_IDX
compute statistics;


Gather statistics for SYS schema and dictionary tables
Use SQL*Plus to connect to the database as sysdba and gather the statistics for the SYS schema using the following commands:

SQL> execute dbms_stats.gather_schema_stats
('SYS',cascade=>TRUE,degree=>20);

"As of Oracle Database 10g Release 10.1, Oracle recommends that you use the
$ sqlplus '/as sysdba'
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;



For Oracle8i and Oracle9i, use the DBMS_STATS.GATHER_SCHEMA_STATS procedure to gather statistics.
Backup the existing statistics as follows:

$ sqlplus '/as sysdba'
SQL>spool sdict

SQL>grant analyze any to sys;

SQL>exec dbms_stats.create_stat_table('SYS','dictstattab');

SQL>exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');

SQL>spool off
This data is useful if you want to revert back the statistics

For example, the following PL/SQL subprograms import the statistics for the SYS schema after deleting the existing statistics:

exec dbms_stats.delete_schema_stats('SYS');
exec dbms_stats.import_schema_stats('SYS','dictstattab');

To gather statistics run this script, connect to the database AS SYSDBA using SQL*Plus.
$ sqlplus '/as sysdba'

SQL>spool gdict

SQL>grant analyze any to sys;

SQL>exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('DMSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SI_INFORMTN_SCHEMA',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>spool off

RMAN

Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.

From sqlplus:

shutdown immediate
startup mount
exit
From unix:

nid target=/
NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:

shutdown immediate
startup mount
alter database open resetlogs
/


Script to check the satus of Rman backup and recovery
set lines 130
set pages 60
column handle format a65
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK

select s.sid,s.serial#, p.spid ,module,s.status from v$session s,v$process p where s.paddr=p.addr and s.sid=&sid;



query for DR recovery
select THREAD#, SEQUENCE#, to_char (COMPLETION_TIME,'DD-MON-YYYY HH: MI: SS') from v$archived_log where 2615561921 between FIRST_CHANGE# and NEXT_CHANGE#;
also one can check to apply nearest firstchange# which matches scn number from command prompt using below query
SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP#


To find SCN
select COMPLETION_TIME,THREAD#,SEQUENCE#,first_change# first,next_change# next from rc_backup_redolog where trunc(completion_time) >= trunc(sysdate-1) order by 3,4,2;
15-OCT-06 1 27046 10064111842 10064523210
15-OCT-06 2 21661 10064505076 10064523222
15-OCT-06 3 22644 10064453457 10064523109


select DB_NAME,to_char(COMPLETION_TIME,'MM/DD/YYYY HH24:MI:SS'),THREAD#,SEQUENCE#,first_change# first,next_change# next from rc_backup_redolog where trunc(completion_time) >= trunc(sysdate-1) and DB_NAME='VIS' order by 3,4,2;


Rman recover command

rman catalog rmanprod/xxxxxi@rman target sys/xxx@prod auxiliary /
cmdfile='/porabackup/vis/scripts/duplicate_vis.rman' log='/porabackup/vis
/scripts/rman_duplicate.log'

Target tns entry eg
RMAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = demo.abc.local)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RMAN)
)
)
RMAN listener is running in source catalog db

run {

allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;

allocate AUXILIARY CHANNEL a1 type disk;
allocate AUXILIARY CHANNEL a2 type disk;
allocate AUXILIARY CHANNEL a3 type disk;
allocate AUXILIARY CHANNEL a4 type disk;
allocate AUXILIARY CHANNEL a5 type disk;
allocate AUXILIARY CHANNEL a6 type disk;
allocate AUXILIARY CHANNEL a7 type disk;
allocate AUXILIARY CHANNEL a8 type disk;

set newname for datafile 1 to '/pdata02/vis/system01.dbf';
set newname for datafile 2 to '/pdata02/vis/system02.dbf';
.
.
.
.set newname for tempfile 5 to '/pdata02/vis/temp05.dbf';
set newname for tempfile 6 to '/pdata02/vis/temp06.dbf';
set until scn 1141383124;
duplicate target database to 'vis'
LOGFILE
GROUP 1 ('/pdata03/vis/log1a.dbf',
'/pdata03/vis/log1b.dbf') SIZE 100M,
GROUP 2 ('/pdata03/vis/log2a.dbf',
'/pdata03/vis/log2b.dbf') SIZE 100M,
GROUP 3 ('/pdata03/vis/log3a.dbf',
'/pdata03/vis/log3b.dbf') SIZE 100M;
}



To add thread 2
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 4 (
'/pdata03/vis/log04a.dbf',
'/pdata03/vis/log04b.dbf'
) SIZE 100M REUSE,
GROUP 5 (
'/pdata03/vis/log05a.dbf',
'/pdata03/vis/log05b.dbf'
) SIZE 100M REUSE,
GROUP 6 (
'/pdata03/vis/log06a.dbf',
'/pdata03/vis/log06b.dbf'
) SIZE 100M REUSE;

alter database enable public thread 2;


An eg of RMAN script screation

To set new name for datafiles
set heading off
set pagesize 9999
spool ptch
select 'set newname for datafile ' || file# || ' to "' || name || '" ;' from v$datafile order by file#;
spool off


Execute the following script from target server

rman TARGET sys/xxx@PROD CATALOG rman/xxxxx@RMAN auxiliary / cmdfile=/mcdev1/app/orvis/rman_duplicate_vis.rman trace=/mcdev1/app/orvis/rman_duplicate_vis.log

PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = lenovo.abc.org)(PORT = 1541))
(CONNECT_DATA =
(SID = PROD)
)
)

RMAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host = demo.abc.org)(Port = 1541))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RMAN)
)
)

Make sure that date format is correct if you are using set until time clause. Export the NLS_DATE_FORMAT in the target server:

export
NLS_DATE_FORMAT=’DD-MON-YY:HH24:MI:SS’

echo $NLS_DATE_FORMAT

rman_duplicate_vis.rman
run{
allocate AUXILIARY CHANNEL c1 type disk;
allocate AUXILIARY CHANNEL c2 type disk;
allocate AUXILIARY CHANNEL c3 type disk;
allocate AUXILIARY CHANNEL c4 type disk;

set newname for datafile 1 to '/mcdev0/app/visoradata/data04/system01.dbf';
set newname for datafile 2 to '/mcdev0/app/visoradata/data04/system02.dbf';
set newname for datafile 3 to '/mcdev0/app/visoradata/data04/system03.dbf';
set newname for datafile 4 to '/mcdev0/app/visoradata/data04/system04.dbf';
set newname for datafile 5 to '/mcdev0/app/visoradata/data04/system05.dbf';
set newname for datafile 6 to '/mcdev0/app/visoradata/data05/ctxd01.dbf';
set newname for datafile 7 to '/mcdev0/app/visoradata/data05/owad01.dbf';
set newname for datafile 8 to '/mcdev0/app/visoradata/data05/abmd01.dbf';
set newname for datafile 9 to '/mcdev0/app/visoradata/data03/abmx01.dbf';
set newname for datafile 10 to '/mcdev0/app/visoradata/data05/akd01.dbf';
.
.
.
set newname for datafile 491 to '/mcdev0/app/visoradata/data05/apps_undots13.dbf';
set until scn 5968861770423;
duplicate target database to "vis"
logfile
group 1 ('/mcdev0/app/visoradata/data06/log1_1.dbf','/mcdev0/app/visoradata/data06/log2_1.dbf') size 500m,
group 2 ('/mcdev0/app/visoradata/data07/log1_2.dbf','/mcdev0/app/visoradata/data07/log2_2.dbf') size 500m;
}


To create catalog:
SQL> create user rmanprod identified by xxxxx default tablespace RMAN temporary tablespace TEMP
2 /

User created

SQL> grant connect, resource to RMANprod
2 /

Grant succeeded.

SQL> grant recovery_catalog_owner to RMANprod
2 /

Grant succeeded.

$ rman catalog rmanprod/xxxxxx

Recovery Manager: Release 10.2.0.3.0 - Production on Fri Oct 5 02:35:09 2007

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

connected to recovery catalog database


RMAN> create catalog

recovery catalog created


register the prod database by connecting as RMANprod. The command will be:
register database;


To find number of archives generated on each day
select 'Number of Archives generated on xxxxxx server: ' || to_char(COMPLETION_TIME,'DD-MON-YY'), count(*) from v$archived_log where to_char(COMPLETION_TIME,'DD-MON-YY')>=(sysdate-5) group by to_char(COMPLETION_TIME,'DD-MON-YY') order by 1;


To find archive timing
select trunc(to_date(COMPLETION_TIME),'DAY'),count(*) from v$archived_log where trunc(to_date(COMPLETION_TIME),'DAY')>=(sysdate-1) group by trunc(to_date(COMPLETION_TIME),'DAY') order by 1;
select count(*) from v$archived_log where COMPLETION_TIME>=(sysdate-1);

Use the below query to know the closest edologfile.
SELECT * FROM gv$archived_log ORDER BY completion_time;

WORKFLOW

Purge WF_NOTIFICATION_OUT queue
cd $FND_TOP/patch/115/sql
sqlplus apps/ @wfntfqup.sql APPS (appspwd) APPLSYS
e.g sqlplus apps/apps @wfntfqup.sql APPS apps APPLSYS

This purges the WF_NOTIFICATION_OUT queue and rebuilds it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications
waiting in the queue to be sent. It will then populate the queue with the current data in the WF_NOTIFICATIONS table.
Only notifications with mail_status = 'MAIL' and status = 'OPEN' will be re-enqueued in the WF_NOTIFICATION_OUT queue and sent by the mailer.

Workflow TEST Address Update
sqlplus apps/ @$FND_TOP/sql/afsvcpup.sql

Enter Component Id: 10006

Enter the Comp Param Id to update : 10093

Enter a value for the parameter : WFdevUsers@abc.com

Setup Test address/override address for WF
Below is the script to update the override address from backend. You do not need the verification code to set the override address using the below script

update fnd_svc_comp_param_vals
set    parameter_value = '&EnterEmailID'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address'
);


Workflow From Address Update :
sqlplus apps/ @$FND_TOP/sql/afsvcpup.sql

Enter Component Id: 10006

Enter the Comp Param Id to update : 10065

Enter a value for the parameter : Lenovo DEV Workflow Mailer"

set overriding address:
update FND_SVC_COMP_PARAM_VALS
set parameter_value = 'Configuration.Workflows@abc.com'
where PARAMETER_ID = 10057;


Scipt to see workflow configuration
SQL> select p.parameter_id,p.parameter_name,v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;


SQL to monitor (check status) of Workflow Notification Mailer (Java)

SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS where COMPONENT_ID=10006;

sqlplus -s system/${PASSWD}@${ORACLE_SID} << SQLEND >${script}/apps/wf_status.out
set linesize 90
col COMPONENT_NAME format a50
col COMPONENT_status format a20
spool ${script}/apps/wf_status.lst
select COMPONENT_ID,COMPONENT_NAME,COMPONENT_STATUS from apps.fnd_svc_components
where COMPONENT_ID not in (10000,10001,10020,10021,10022) and COMPONENT_STATUS <> 'RUNNING';

select mail_status, count(*) from apps.wf_notifications
where status = 'OPEN'
and mail_status in ('MAIL','ERROR')
group by mail_status;
spool off"



To see error message for a workflow notification
SQL> select ERROR_MESSAGE from wf_item_activity_statuses_v WHERE NOTIFICATION_ID = 7377659;

Here are steps/events for Oracle Workflow Notification Outbound Processing(eMail from Oracle Applications Workflow to Users)

1.When workflow Engine determines that a notification message must be sent, it raises an event in BES (Business Event System) oracle.apps.wf.notifications.send
Event is raised with Notification ID (NID) as event key
2. There is seeded subscription to this Event
3. Event is placed on WF_DEFERRED agent
4.Event is dequeued from WF_DEFERRED and subscription is processed
5. Subscription places event message to WF_NOTIFICATION_OUT agent.
6.Notification Mailer dequeues message from WF_NOTIFICATION_OUT agent and
6.1convert XML representation of notification into MIME encoded message (Multi-purpose Internet Mail Extensions) and
6.2 Send message by SMTP (Simple Mail Transfer Protocol) to intended user (If Test Address/Override Address is set then email is sent to Test Address

E-Mail Notification is sent if all below conditions are true
a) Notification status is OPEN or CANCELED and
b) Notification mail_status is MAIL or INVALID and
c) Recipient Role has a valid e-mail address and Notification Preference is in the format MAIL%
d) Workflow Deferred Agent Listener is running
e) Workflow Notification Mailer is running

To check a) & b) run below query
SELECT status, mail_status FROM wf_notifications WHERE notification_id = ‘&NID’;

mail_status >> SENT means mail has gone out of mailer to user

To check c) run below query
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(’&recipient_role’);

To check d) & e) Use OAM (Oracle Application Manager)


Workflow version embedded in 11i
************************
Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like
TEXT
-----------------------
2.6.0
Which means you are on Workflow Version 2.6.0

You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.

To Configure Workflow Notification Mailer need below information

PARAMETER_NAME VALUE
------------------------------ ------------------------------
Inbound Server Name wfmailer.abc.com
Username wftst
Password oracle
Outbound Server Name wfmailer.abc.com
Reply-to Address wftst@abc.com


Taking a backup of the workflow configuration
cd $HOME/
sqlplus “/ as sysdba”
spool wf_mailer.log
set lines 130
set pages 200
col value format a30
select p.parameter_id,p.parameter_name,v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;


To schdule Gather schema stats and purge workflow runtime data

Login into application as sysadmin and schedule below Requests.
Gather Schema Statistics (ALL , 20% , degree 6 ) to run every SUN and WED
Workflow Background Process to run every 10 mts and Apply the interval as :From the completion of the prior run
Purge Obsolete Workflow Runtime data - Every week
Workflow Control Queue Cleanup - Every 12 hours


To update WF notification status
IMPORTANT STEP ! Connect to SQL*PLUS as APPS user and do the following steps
update applsys.wf_notifications
set status ='CLOSED', mail_status ='SENT', end_date ='01-JAN-01'
where mail_status='MAIL';
update wf_notifications set status=’CLOSED’;
commit;"
Select distinct status from wf_notification" this should return only one value CLOSED.
or simple do
update wf_notifications set status = 'CLOSED', mail_status = 'SENT';
commit;


Update workflow a/c pwd from backend
set define off
update APPLSYS.FND_SVC_COMP_PARAM_VALS set PARAMETER_VALUE= '_@#0@##^90@#!4^86#!$^68$#9$4#@@$6!!!9#0@`$B9+}*0&9&@0&8#|'
where
PARAMETER_ID=
( select parameter_id from APPLSYS.FND_SVC_COMP_PARAMS_B where parameter_name = 'INBOUND_PASSWORD');

To check WF mail status
select count(*), mail_status
from wf_notifications
where begin_date > sysdate - 1
group by mail_status;

There are 141 messages with status `MAIL¿, this status should remain very short and then progress to status `SENT¿.
: OAM Login > Site Map > Workflow > Service Components.
These same detail are also given in the output from script $FND_TOP/sql/wfver.sql


To check the WF status from backend

check the status of Agent Listeners:

set pagesize 400
set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;


Typical output from this

COMPONENT_NAME STARTUP_MODE COMPONENT_STATU
--------------------------------------------- --------------- ---------------
WF_JMS_IN Listener(M4U) AUTOMATIC RUNNING
Workflow Deferred Agent Listener AUTOMATIC RUNNING
Workflow Deferred Notification Agent Listener AUTOMATIC RUNNING
Workflow Error Agent Listener AUTOMATIC RUNNING
Workflow Inbound Notifications Agent Listener AUTOMATIC RUNNING
Workflow Java Deferred Agent Listener AUTOMATIC RUNNING
Workflow Java Error Agent Listener AUTOMATIC RUNNING
Workflow Notification Mailer AUTOMATIC RUNNING
ECX Inbound Agent Listener MANUAL STOPPED
ECX Transaction Agent Listener MANUAL STOPPED
Web Services IN Agent MANUAL STOPPED
Web Services OUT Agent MANUAL STOPPED
Workflow Inbound JMS Agent Listener MANUAL STOPPED

13 rows selected.


Steps to start/stop notification mailer

1.a Check workflow mailer service current status
sqlplus apps/

select running_processes
from fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC';

Number of running processes should be greater than 0

1.b Find current mailer status
sqlplus apps/

select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');

Possible values:
RUNNING
STARTING
STOPPED_ERROR
DEACTIVATED_USER
DEACTIVATED_SYSTEM

2. Stop notification mailer
sqlplus apps/

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Stop Mailer
--------------
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/

3. Start notification mailer
sqlplus apps/

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Start Mailer
--------------
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/


TO configure workflow
Workflow Mailer Notification settings.
Log on to OAM
Click on Workflow Administrator -> Business Event Local System
Change VIS.ABC.COM to PROD.ABC.COM

Click on Workflow Manager ->Notification Mailer -> Workflow Notification Mailer -> Edit
Edit outbound Email Account (SMTP) -> Server Name =Concurrent Tier
IMAP Server -> Server Name = xxx.xxx.x.xx
Username/password: wfvis/xxxxx :
Reply-To Address: wfvis@abc.com

Click on Advance -> Next->Next
Under General Tab
Mailer Node =WFVIS
Under IMAP: Enter server IP address, username and password of wfvis
Under Outbound Email Account
Outbound Server Name= concurrent tier
Click Next –>Next….Save and Apply

Workflow Version with Apps
Connect to Database as apps user
SQL> select TEXT Version from WF_RESOURCES where NAME = ‘WF_VERSION’;
Output like 2.6.0 means workflow version 2.6.0


Workflow logfile location
$APPLCSF/$APPLLOG with filename like FNDCPGSC[pid].txt

After executing autoconfig make sure Workflow System Administrator is NOT set to SYSADMIN .
Please set this to ""Workflow Administrator Web (New)""
Following script will take care of this .

sqlplus apps/""pwd""
SQL> update wf_resources set text = 'FND_RESP|FND|FNDWF_ADMIN_WEB_NEW|STANDARD' where name = 'WF_ADMIN_ROLE';

1 row updated.

SQL> commit;

Commit complete.

or xml changes can be done link this.
$ grep -i s_wf_admin_role PROD_mdsxaorit.xml
username oa_var=""s_wf_admin_role"">FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD


To set the Workflow Administrator from Sys Admin to Workflow Administrator
Navigate to Responsibility -- Workflow Administarator
then Administrator Workflow -- Administration

On top we will find Workflow Configuration :

Workflow System Administrator :: This value we need to change to Workflow Administrator

Or we can update from backend
Update wf_resources set text='FND_RESP|FND|FNDWF_ADMIN|STANDARD' where name = 'WF_ADMIN_ROLE';

Or can be set in xml,
grep -i s_wf_admin_role TRAIN2_vcosxaor09.xml username oa_var=""s_wf_admin_role"">FND_RESP|FND|FNDWF_ADMIN|STANDARD


modify this profile option to have the value of an actual App Server
WF: Workflow Mailer Framework Web Agent : http://hostname.domainname:8000

Make sure following parameters are set for Workflow :
As system administrator –
Oracle Applications Manager
Workflow Manager

Inbound Server Name wfmailer.domain.com
Outbound Server Name wfmailer.domain.com
From The lenovo PROD Workflow Mailer
Reply-to Address wfvis@domain.com


Workflow smtp port 25 and imap port 143
[root@hostname]# telnet wfmailer 143

Application commands

Generating fmb files manually in Rel 12

frmcmp_batch module=$AU_TOP/forms/US/MSDDEMRH.fmb userid=apps/(appspass) output_file=$MSD_TOP/forms/US/MSDDEMRH.fmx module_type=form batch=no compile_all=yes

Maintain Snapshot Information:
This will sync up your application tier with database.
http://oracleappstechnology.blogspot.com/2008/05/appltop-snapshot-feature.html

Run the following command to locate the properties file that you want to customize to preserve your settings:
R11i10

sh $AD_TOP/bin/adtmplreport.sh contextfile=$CONTEXT_FILE target=$IAS_ORACLE_HOME/Apache/Jserv/etc/xmlsvcs.properties

R12

sh $AD_TOP/bin/adtmplreport.sh contextfile=$CONTEXT_FILE target=$INST_TOP/ora/10.1.3/j2ee/oafm/config/oc4j.properties


Navigate to the location of the template directory and create a custom subdirectory to store to store your customization:

cd $FND_TOP/admin/template

mkdir custom

Copy the template file to the custom directory. and edit to add custom entries and run autoconfig"

What is staging area ?
Staging Area is special directory structure where you can dump 11i installation software so that you don’t have to insert CD’s during install and these disks will automatically be picked by Installer.
Q. How you set up staging area ?
Use adautostg.pl to create staging area orcreate required directory manually for staging like (following directories under Stage11i - startCD, oraApps, oraDB, oraiAS, oraAppsDB, oraNLS and inside these directories Disk1, Disk2…).


AOLJTEST
http://hostname:portno/OA_HTML/jsp/fnd/aoljtest.jsp

Check Jserv with the following url http://:port/servlets/Hello

Increase number of JVM to 2:
1. Comment out this line in the jserv.conf file:
ApJServGroup OACoreGroup 1 1 /d01/oracle/prodora/iAS/Apache/Jserv/etc/jserv.properties

2. Uncomment this line in the jserv.conf file:
#ApJServGroup OACoreGroup 2 1 /d01/oracle/prodora/iAS/Apache/Jserv/etc/jserv.properties

Bounce apache


To change paswords for all modules:
select 'FNDCPASS apps/xxx 0 Y system/xxx ORACLE '||oracle_username||' xxxxx' from fnd_oracle_userid where oracle_username in (select username from dba_users) and oracle_username not in ('APPS','APPLSYS','APPLSYSPUB');

PASSWORD CREATION USING FNDCPASS
FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME
edit pwd is wdbsvr.app and cat $806ORACLE_HOME/reports60/server/CGIcmd.dat


alter user system identified by xxx;
alter user sys identified by xxx;

FNDCPASS apps/xxx 0 Y system/xxxxxx ALLORACLE xxxxx

WFLOAD syntax
WFLOAD 0 Y {UPLOAD | DOWNLOAD | UPGRADE | FORCE} [@:] []

EXECUTION OF LDT FILE
FNDLOAD &ui_apps 0 Y UPLOAD @FND:patch/115/import/affaudit.lct @GMA:patch/115/import/US/gmaausch.ldt

download from one instance and upload to another instance
FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct
TPCOCUSTMAP.ldt PROGRAM
CONCURRENT_PROGRAM_NAME="TPCOCUSTMAP"
APPLICATION_SHORT_NAME="XBOL"

FNDLOAD APPS/password 0 Y UPLOAD @FND:patch/115/import/afcpprog.lct
TPCOCUSTMAP.ldt

backup menus, Responsibility, user using below FNDLOAD commands( ****IMPORTANT STEP****)
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct PATCH_SUB_MENU.ldt MENU MENU_NAME=""WH_ENQ""
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct PATCH_MENU.ldt MENU MENU_NAME=""WH_ENQ_MAIN""
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct PATCH_RESP.ldt FND_RESPONSIBILITY RESP_KEY=""MFG US ATP WH MGMT""
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct PATCH_USER.ldt FND_USER USER_NAME=""MOBILE_USER""

Upload Menus, Responsibility and User using below FNDLOAD commands
cd $backup_dir ** directory in which files were backed up thru step#4
FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct PATCH_SUB_MENU.ldt
FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct PATCH_MENU.ldt
FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct PATCH_RESP.ldt
FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct /tmp/PATCH_USER.ldt


PACKAGE CREATION USING PKH and PKB FILES
sqlplus apps/apps @******.pkh --- this will create the package header or specification
sqlplus apps/apps @******.pkb --- this will create the package body

ODF FILE EXECUTION
adodfcmp userid=cn/cn mode=TABLES odffile=cntrx02.odf touser=apps/apps priv_schema=apps/apps logfile=cn.log changedb=YES
adodfcmp userid=/ odffile=afsec.odf touser=apps_appfnd/xxx logfile=/tmp/a2.lst changedb=Yes priv_schema=apps_appfnd/xxx mode=tables

Execute the following command to determine the version of OA.jsp:
ident $FND_TOP/html/OA.jsp and
ident $OA_HTML/OA.jsp
(ie: Header: OA.jsp 115.26 2002/01/18 14:49:41 pkm ship)

Attention: The version of OA.jsp in $FND_TOP/html should be the same as the one in $OA_HTML if the version of OA.jsp does not match do the following :
Run adadmin.
Choose the option called Maintain Application Files.
Choose the option called Copy files to destination.

Note: If the ident command returns a no such file error or does not return any version information for OA.jsp then you have not installed the Oracle Applications Self Service Framework. Please use this metalink document to install the Self Service Framework.

Note: ident is not available on SUN & AIX. In this instance please use:
adident Header $FND_TOP/html/OA.jsp and
adident Header $OA_HTML/OA.jsp


How to compile JSP in Oracle Apps ?
You can use ojspCompile.pl perl script shipped with Oracle apps to compile JSP files. This script is under $JTF_TOP/admin/scripts. Sample compilation method is
perl ojspCompile.pl --compile --quiet

compiling few jsp's
Clear the following files from $OA_HTML/../_pages/_oa__html
rm _qot*.*
rm _xx*.*
ojspCompile.pl --compile -s '%qot%.jsp' -log $HOME/err.log --flush
ojspCompile.pl --compile -s '%xxdhr%.jsp' -log $HOME/err.log –flush
Confirm _qot*.* and _xx*.* files exist (after running the above Perl script)
in $OA_HTML/../_pages/_oa__html directory

Compile JSPs (~ 45 mts)
cd $FND_TOP/patch/115/bin
perl ojspCompile.pl --compile --flush -p 2

JSP compilation
$ cd $JTF_TOP/admin/scripts
$ ./ojspCompile.pl --compile
perl -x $JTF_TOP/admin/scripts/ojspCompile.pl --compile --quiet
perl -x $JTF_TOP/admin/scripts/ojspCompile.pl --compile --quiet -log /tmp/ojspc_error.log 341205.1


cd $FND_TOP/patch/115/bin
$ ls -rlt ojsp*
-rwxr-xr-x 1 appldev2 dba 85192 Apr 24 06:23 ojspCompile.pl
$ perl -x ojspCompile.pl --compile --quiet
logfile set: $INST_TOP/logs/appl/rgf/ojsp/ojspc_error.log
For r12


How to convert pll to pld file or pld file to pll ?
Pll->Pld f60gen module=MSCOSCW3.pll module_type=library userid=apps/ module_access=file output_file=MSCOSCW1.pld script=yes
Pld -> pll f60gen module=MSCOSCW3.pld userid=apps/ module_type=library module_access=file output_file=MSCOSCW1.pll parse=y batch=yes compile_all=special

How to convert pll to pld file or pld file to pll ?
Ans: Pll->Pld f60gen module=MSCOSCW3.pll module_type=library userid=apps/ module_access=file output_file=MSCOSCW1.pld script=yes

Pld -> pll f60gen module=MSCOSCW3.pld userid=apps/ module_type=library module_access=file output_file=MSCOSCW1.pll parse=y batch=yes compile_all=special

FORM file compilation
The following environment setting should be part of the Forms compilation to include the standard oracle library files.

export PATH=$PATH:$AU_TOP/forms/US:$AU_TOP/resource:$AU_TOP/resource/US

export FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US:$AU_TOP/resource:$AU_TOP/resource/US

f60gen module=$AU_TOP/forms/US/INVTOMAI.fmb userid=apps/xxx output_file=$INV_TOP/forms/US/INVTOMAI.fmx module_type=form batch=yes compile_all=special

Temporarily resetting FORMS_PATH to compile language forms

$AU_TOP/resource:$AU_TOP0/forms/F:$AU_TOP/resource/F

FORM file compilation
f60gen module=$AU_TOP/forms/F/ASXTRLKP.fmb userid=apps/apps output_file=$AS_TOP/forms/F/ASXTRLKP.fmx module_type=form batch=yes compile_all=special

To complie .fmb , .pll in R12
$ frmcmp_batch.sh module=$AU_TOP/resource/CUSTOM.pll userid=apps/xxxx output_file=$AU_TOP/resource/CUSTOM.plx module_type=library compile_all=special

frmcmp_batch.sh module=$AU_TOP/forms/US/CLX_CTG_FORM.fmb userid=apps/xxxx output_file=$AU_TOP/forms/US/CLX_CTG_FORM.fmx module_type=form compile_all=special


To compile pll file
f60gen module=$AU_TOP/resource/CUSTOM.pll userid=apps/apps output_file=$AU_TOP/resource/CUSTOM.plx module_type=library batch=yes compile_all=special

Playing with CUSTOM.pll
To convert from CUSTOM.pll to CUSTOM.pld
f60gen module_type=LIBRARY module=CUSTOM script=YES userid=apps/apps

To convert back from CUSTOM.pld to CUSTOM.pll ( after having edited the text pld file )
f60gen module_type=LIBRARY module=CUSTOM parse=YES userid=apps/apps

To convert from CUSTOM.pll to CUSTOM.plx
f60gen module_type=LIBRARY module=CUSTOM userid=apps/apps

REPORT file comilation

rwcon60 userid=APPS/xxx source=$BIL_TOP/reports/US/BILPHP01.rdf dest=$APPL_TOP/admin/CNV1/out/tmp001.rdf stype=rdffile dtype=rdffile logfile=$APPL_TOP/admin/CNV1/out/adrep001.txt overwrite=yes batch=yes compile_all=yes

rwcon60 userid=APPS/xxx source=BILPHP01.rdf dest=BILPHP01.rex stype=rdffile dtype=rexfile logfile=BILPHP01.txt overwrite=yes batch=yes

rwcon60 userid=apps/apps source=JEFRDVER.rdf stype=rdffile dtype=rexfile dest=JEFRDVER.rex overwrite=yes batch=yes


Compiling all application flexfields.
$FND_TOP/bin/fdfcmp APPS/***** 0 Y

TO start dataInstaller
jre oracle.apps.per.DataInstall apps pwd thin dbt11.unix.flarock.com:1551:OABT

jre oracle.apps.per.DataInstall apps thin ::

hrglobal.drv
PER_TOP/patch/115/driver

To check to make sure the correct data was installed run the following script this script can also be used to check if datainstaller was run successfully:
select application_short_name, Legislation_code, status, action, last_update_date
from hr_legislation_installations
where application_short_name in ('PER','PAY');

To check the version of the iAS 1.0.2.2.2
cd /Apache/Apache
httpd -version


To check the version of the forms
f60gen


To relink manually
adrelink.sh force=Y ranlib=Y "AD"
./adrelink.sh force=y ranlib=y "WPS WPCFSQ"

To complile forms and reports library
cd $ORACLE_HOME/forms/lib32
Note: if this directory does not exist: cd $ORACLE_HOME/forms/lib
$ make -f ins_forms.mk install

cd $ORACLE_HOME/reports/lib32
Note: if this directory does not exist: cd $ORACLE_HOME/reports/lib
$ make -f ins_reports.mk install

Relink Procedure Builder, Forms, Graphics and Reports:
cd $ORACLE_HOME/procbuilder60/lib; make -f ins_procbuilder.mk install
cd $ORACLE_HOME/forms60/lib; make -f ins_forms60w.mk install
cd $ORACLE_HOME/graphics60/lib; make -f ins_graphics60w.mk install
Reports has both link-time and run-time dependency with libjava.so so you need to append $ORACLE_HOME/network/jre11/lib/linux/native_threads in

$LD_LIBRARY_PATH before linking Reports. The same $LD_LIBRARY_PATH should be used at run-time.
cd $ORACLE_HOME/reports60/lib; make -f ins_reports60w.mk install


Relink iAS and Oracle Home using
$IAS_ORACLE_HOME/bin/adlnkiAS.sh and $ORACLE_HOME/bin/adlnk806.sh


To fin perl version
$ perl -version

To find java version
$ java -version

To find JDK version:
From appl_top, xml file
/amtoac/applmgr/common/util/java/1.4/j2sdk1.4.2_04


Discoverer launcher and viewer urls
http://hostname.oracle.com:10000/discwb4/html/discolaunch.htm)
http://hostname.oracle.com:10000/discoverer4i/viewer
Plus--http://hostname.domain:port/discwb4/html/english/welcome.htm
To find Version
$ string –a dis4pr | grep –i ‘discoverer version’
The location for looking for dis4pr is $ORACLE_HOME/diswb4/bin

To start discoverer
Script to start discoverer server is addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME
-- This script in turn start discoverer processes which can be start by startall.sh under $ORACLE_HOME/discwb4/util (This script is different from adstrtal.sh)
-- startall.sh calls scripts like startgatekeeper.sh, startlocator.sh , startoad.sh, startosagent.sh to start gatekeeper, locator, oad & osagent resp.
In Apps two major way to access EUL is via Discoverer view & Discoverer Plus edition (You can use Discoverer Administration Edition as well)


to generate xml file on apps and db tier
ad_top/bin
adbldxml.sh tier=apps appsuser= appspasswd=
Source the environment before runnibg

Go to /appsutil/bin  
perl adbldxml.pl tier=db appsuser= appspasswd=



Make command
make -f $APPL_TOP/admin/train/out/link_inv_1040580.mk $INV_TOP/bin/INCOIN

restart apache without killing current web sessions
usellx41.corp.invacare.us:support_conf> $SCRIPTS/adapcctl.sh graceful
adapcctl.sh version 115.48
Apache Web Server Listener :httpd ( pid 16249 ) is running.
Gracefully restarting the Apache Web Server Dedicated HTTP Listener..
Apache Web Server Listener (PLSQL) :httpd ( pid 16379 ) is running.
Gracefully restarting the Apache Web Server Dedicated PLSQL Listener..
adapcctl.sh: exiting with status 0


enabling Autoconfig on DB tier
As applmgr Copy AutoConfig templates to the RDBMS ORACLE_HOME
---------------------------------------------------------------------------
Create appsutil.zip file by: perl $AD_TOP/bin/admkappsutil.pl
sandbox806> perl $AD_TOP/bin/admkappsutil.pl
Starting the generation of appsutil.zip
Log file located at /ORACLE/apps/sandbox/admin/log/MakeAppsUtil_09061407.log
output located at /ORACLE/apps/sandbox/admin/out/appsutil.zip
MakeAppsUtil completed successfully.
sandbox806>
Copy appsutil.zip file to the $RDBMS_ORACLE_HOME
sandbox> id
uid=300(oracle) gid=200(dba) groups=1(staff),202(oaa),203(move),13(oinstall)
sandbox> cp /ORACLE/apps/sandbox/admin/out/appsutil.zip $ORACLE_HOME
sandbox> ls -l $ORACLE_HOME/appsutil.zip
-rw-r--r-- 1 oracle dba 2814669 Sep 06 14:13 /ORACLE/sandbox/9.2.0/appsutil.zip
unzip -o appsutil.zip # say A if being prompted for file over-writing

# Generate the database context file:
------------------------------------
sandbox> pwd
/ORACLE/sandbox/9.2.0/appsutil/bin
sandbox> id
uid=300(oracle) gid=200(dba) groups=1(staff),202(oaa),203(move),13(oinstall)
sandbox> perl adbldxml.pl tier=db appsuser=apps
Starting context file generation for db tier..
Using JVM from /ORACLE/sandbox/9.2.0/jdk/bin/java to execute java programs..
APPS Password: apps
The log file for this adbldxml session is located at:
/ORACLE/sandbox/9.2.0/appsutil/log/adbldxml_09061436.log
Enter the value for Display Variable: hostname:1
Context File /ORACLE/sandbox/9.2.0/appsutil/sandbox_hostname.xml
already exists.
Overwriting Context File may cause loss of existing settings, hence
backing it up as: /ORACLE/sandbox/9.2.0/appsutil/sandbox_hostname.xml.bak
The context file has been created at:
/ORACLE/sandbox/9.2.0/appsutil/sandbox_hostname.xml

# Take a backup of all the crucial configuration files and Generate and apply AutoConfig configuration file:
------------------------------------------------------------------------------------------------------------

a) Backup $TNS_ADMIN/*.ora and $ORACLE_HOME/dbs/*.ora
b) run Autoconfig on source tier to generate the context specific files:

cd /appsutil/bin
adconfig.sh contextfile= appspass=
c) Restore the $TNS_ADMIN/*.ora and $ORACLE_HOME/dbs/*.ora Immediately
d) Do a reload of the listener.ora to make sure other instances using /var/opt/oracle/listener.ora do not get affected:

$ lsnrctl reload qa
$ lsnrctl reload sandbox


The command used to check for the Forms patchset level
( you should be 17 or above)
`f60gen | grep '(Form Compiler)' | grep -v Release | awk -F. '{print $5}' `


How to find JDBC thin driver that your iAS 1.0.2.2.2 is using ?
A simple java program & procedure to compile it that will give your jdbc thin driver version.
Create a file with name JDBCVersion.java in your middle tier (Application Tier)
---------
import java.sql.*;
import oracle.jdbc.driver.*;
class JDBCVersion
{
public static void main (String args[])
throws SQLException
{
// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Get a connection to a database
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=YES)(FAILOVER=YES) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST= ) (PORT= )))(CONNECT_DATA=(SID=)))" ,"apps","");
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = conn.getMetaData();
// gets driver info:
System.out.println("JDBC driver version is " + meta.getDriverVersion());
}
}
-----------
Replace following parameters
1. with your database hostname or IP address
2. with your database port no.
3. with SID for your database
4. with your apps password
After changing save it with name JDBCVersion.java in your middle tier & execute command
javac JDBCVersion.java
This will create class file in your workign directory. Include your current directory into your classpath like
export CLASSPATH=$CLASSPATH:/
then execute
java JDBCVersion
you should see output like
JDBC driver version is 9.2.0.6.0
Which means you are using jdbc thin driver version 9.2.0.6.0.


How to check if Apps 11i System is Autoconfig enabled ?
Under $AD_TOP/bin check for file adcfginfo.sh & if this exists use
adcfginfo.sh contextfile= show=enabled
If this file is not there , look for any configuration file under APPL_TOP if system is Autoconfig enabled then you will see entry like
# AutoConfig automatically generates this file. It will be read and .......


Context Editor
Application /util/editcontext/
Database /appsutil/editcontext/
"To run the Context Editor:
On UNIX
editcontext
If the [COMMON_TOP]/util/editcontext (for Unix) or [COMMON_TOP]\util\editcontext (for Windows) directory does not
exist, please refer to MetaLink Note 165195.1 titled ""Using AutoConfig to Manage System Configurations with Oracle
Applications 11i"".
Copy the Context Editor meta data file adctxinf.tmp to the common top directory under util/editcontext/etc and rename
it as adctxinf.xml:
on UNIX:
cp $AD_TOP/admin/template/adctxinf.tmp [COMMON_TOP]/util/editcontext/etc/adctxinf.xml

EDITING CONTEXT FILE
Editing Context file(xml file) in Oracle apps R12 using Context editor.
It is a mandatory thing that you should not edit the context file or xml file manually .
Context editor should be used for this(This can be used for updating ports or to change any configuration)
of application Tier or database tier.
follow these steps to install context editor in your existing oracle apps R12 system.
Apply patch 2873456 from metalink
after applying you will not be able to invoke the editcontext executable and you will get a error. this is because jre 1.1.8 which is
supplied along with this patch is no longer supported.
So download jre1.5.0_10 from sun.java.com and unpack it inside the path editcontext/jre/Linux/
and rename the old 1.1.8 jre inside that
then go to the bin directory inside the editcontext directory
open the file runWizard.sh
and accordingly replace the jre directory and also replace the jre executable with java in the same file ..
and invoke it from
$RDBMS_ORACLE_HOME/appsutil for editing database context(xml) file.
$COMMON_TOP/util for editing Application context(xml) file.
Syntax is
./editcontext
Before editing shutdown the application tier and after editing run autoconfig.sh to update the configuration files to reflect the new value.

The URL for ext web node is
JTF Login Page for customers using CRM (Check for each MT using real host names).
http://:port/OA_HTML/jtflogin.jsp

start Apache in SSL mode as root (on web025) using following cmd:

. //APPSORA.env

$IAS_ORACLE_HOME/Apache/Apache/bin/httpdsctl startssl.

URL to access OAM
access http://.:/servlets/weboam/oam/oamlogin


How will you identify if you are using Forms in Socket or Servlet mode in Oracle Applications 11i or R12 ?

You can find out the form are in Socket or Servlet mode in form configuration file it is in $COMMON_TOP/html/bin and the file name is “appsweb_SID_hostname.cfg”.

s_forms_servlet_serverurl If value is blank that means this is not servlet else if value is “/forms/formservlet” this indicates forms servlet.

In the context file, unde OA variable, ‘frmConnectMode’

repackage the jinit using the following command :
adjbuild.sh $COMMON_TOP/util/jinitiator $COMMON_TOP/util/jinitiator/j2se15012.exe 1.5.0.12


CPU patch log location on iAS HOME/d01/oratst/preprodora/iAS/cpu/CPUJul2005/install-Jun-09-2008-07-11-34.log


Apache config test
$ pwd
/d01/oratst/preprodora/iAS/Apache/Apache/bin

$ ./apachectl configtest
Syntax error on line 239 of /d01/oratst/preprodora/iAS/Apache/Apache/conf/httpd.conf:
Cannot load /d01/oratst/preprodora/iAS/Apache/Apache/libexec/mod_log_config.so into server: ld.so.1: httpd: fatal: relocation error: file /d01/oratst/preprodora/iAS/Apache/Apache/libexec/mod_log_config.so: symbol ap_escape_logitem: referenced symbol not found


To check X-display is working
Access page at http://hostname.domainnmae:8000/OA_HTML/testXServer.jsp. If the display setting is inappropriate you will see an exception page


Edit the AutoConfig context file.
If you are currently using E-Business Suite level 11.5.9 or above, or have applied FND.G patchset (or above), you will use Oracle Applications Manager to edit your context file. For all previous versions, you will use the AutoConfig Context Editor tool.
To edit the context file using Oracle Applications Manager, login to Oracle Applications Manager and navigate to Site Map > AutoConfig > Edit Parameters.
To edit the context file using the AutoConfig Context Editor tool, enter the following commands:

% cd [COMMON_TOP]/util/editcontext/
% ./editcontext"


To convert host specific url to user friendly url

Make host entries for ebus and make the following xml file changes and run autoconfig.

$ grep evis VIS_hostname.xml
ebus
http://evis.corpdom.local:8000/oracle_smp_chronos/oracle_smp_chronos_sdk.gif
evis.corpdom.local
http://evis.corpdom.local:8000
evis
http://evis.corpdom.local:8000/pls/VIS_portal30/portal30.home
http://evis.corpdom.local:8000/OA_HTML/US/ICXINDEX_VIS_hostname.htm
http://evis.corpdom.local:8000/OA_TEMP
$



MWA boucne procedure on 11 i

cd $MWA_TOP/bin
mwactl.sh start 10200
mwactl.sh start 10210
mwactl.sh start 10220
mwactl.sh start 10230
mwactl.sh start 10240
nohup mwactl.sh start_dispatcher & Port for Dispatcher is 10300

Procedure to Shutdown Dispatcher
mwactl.sh -login sysadmin/xxx stop_force 10200
mwactl.sh -login sysadmin/xxx stop_force 10210
mwactl.sh -login sysadmin/xxx stop_force 10220
mwactl.sh -login sysadmin/xxx stop_force 10230
mwactl.sh -login sysadmin/xxx stop_force 10240

nohup mwactl.sh stop_dispatcher &

1. Autoconfig. In autoconfig-enbled environment configuration file mwa.cfg is written by autoconfig. So it would be good to modify any configuration setting that you want to be permanent via AutoConfig editor in OAM. For all MWA server configuration variables OA_VAR begins from s_mwa. There is also a description for each parameter.
2. R12. While in 11i mwa.cfg is located in $MWA_TOP/secure, in R12 it’s in $INST_TOP/admin/install. Startup script mwactl.sh in R12 is located in $INST_TOP/admin/scripts
3. Dispatcher. If we are going to run multiple servers (specified by mwa.TelnetPortNumber / s_mwaTelnetPortNo) then we most likely will also use dispatcher to load balance client requests between MCSA telnet servers. We use mwa.Dispatcher=host:port to set MCSA dispatcher (in AutoConfig there is s_mwaDispatcherPort for the port setting). To start|stop dispatcher we can use:
mwactl.sh start_dispatcher | stop dispatcher.
Dispatcher is MWADIS binary within $MWA_TOP/bin directory. We can check whether it’s running using:
ps -ef | grep MWADIS
4. OAM. There are Generic Services (MWA MSCA Telnet Server & MWA MSCA Telnet Dispatchers) in OAM that corresponds to MSCA telnet server & dispatcher respectively and can be used to start/stop them automatically.
5. Server shutdown. When we use
mwactl.sh -login / stop [port]
- it’s so called graceful shutdown. MCSA Telnet Server won’t stop unless there are no clients currently using it. If you need to force shutdown, use stop_force instead of stop
6. Tracing. Setting mwa.LogLevel could be very useful for trobleshooting issues with MWA In 11.5.10/R12 this will usually produce serveral files within MWA log directory:
.system.log
.INV.log
.WMS.log (optionally)
But for performance tuning we can also use SQL tracing. It can be enabled by setting mwa.LogLevel (s_mwaLogLevel) to performance. SQL trace file will be put into usual user_dump_dest directory on database server.

The steps that needs to be done to disable MWA are as follows:
1. Modify the $IAS_ORACLE_HOME/Apache/Jserv/etc/mobile.properties file by commenting out the following line:
servlets.startup=oracle.apps.mwa.wap.engine.WapServlet

2. Comment out the following lines in the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties file:
zones=mobile

3. Comment out the following lines in the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf file:
ApJServGroupMount /mobile balance://OACoreGroup/mobile



Check if tcfsocket server is running
ps -ef|grep jre|grep comn

appltst 6466 6226 0 14:04:07 ? 0:03 /d01/oratst/testcomn/util/jre/1.1.8/bin/../bin/sparc/native_threads/jre -Ddbcfi
appltst 7969 6226 0 14:12:59 ? 0:03 /d01/oratst/testcomn/util/jre/1.1.8/bin/../bin/sparc/native_threads/jre -Ddbcfi
appltst 6496 6226 0 14:04:13 ? 0:03 /d01/oratst/testcomn/util/jre/1.1.8


Generating jar files
Generating product JAR files in JAVA_TOP -
/d01/oratst/testcomn/java with command:

adjava -mx512m -nojit oracle.apps.ad.jri.adjmx @/d01/oratst/testappl/admin/TEST02/out/genjars.cmd


The AutoConfig check utility

   The AutoConfig check utility is located at Target Database Tier /appsutil/bin
i) Check the AutoConfig configuration files by executing the following command
adchkcfg.sh contextfile=.xml  appspass= – This report provides information about All the profile options that get changed during the next normal execution of AutoConfig.
– For every profile option there is information about the current value in the database, the changed value and the AutoConfig script that changes the profile option.
– The script will also create a zip file report “ADXcfgcheck.zip” that contains all the files and reports mentioned above, so that you can copy the ADXcfgcheck.zip to a local desktop PC and view the html report from there without breaking the hyper-links in the report.

Check Java Cache in your Env :
login to URL http://: /OA_HTML/jtflogin.jsp
Login as sysadmin and navigate to Performance -> Components.


How to change all Applications user passwords
In apps, there are times when we want to force all the application users to change their passwords. In apps 11i, we have a concurrent request under System Administrator->

CP SQL*Plus Expire FND_USER Passwords

This concurrent request basically sets the fnd_user.password_date to null for all apps users. The users are thus prompted to change their passwords on next login


Responsibility attached to a users
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);

Query useful when user wants to get application wise responsibility list
SELECT (SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id) application,
frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;


####### Script to create LENOVO User ###################
####### Please copy this script in a file and execute as apps user #####
REM XX_CREATE_USER.SQL
REM Script to create a user and add
REM responsibility. This script will run as part of
REM post-clone processes in a non-PROD instance.

SET serveroutput ON

DECLARE
l_appl_short_name fnd_application.application_short_name%TYPE;
l_resp_key fnd_responsibility.responsibility_key%TYPE;
BEGIN
SAVEPOINT create_user;

DBMS_OUTPUT.put_line('Creating user ...'||CHR(10));

FND_USER_PKG.CreateUser ( x_user_name => 'LENOVO'
,x_owner => 'SEED'
,x_unencrypted_password => 'welcome1'
,x_description => 'Consultant'
);

DBMS_OUTPUT.put_line('User created successfully.'||CHR(10));

SELECT application_short_name
,responsibility_key
INTO l_appl_short_name
,l_resp_key
FROM fnd_responsibility_vl resp
,fnd_application app
WHERE app.application_id = resp.application_id
AND responsibility_name = 'System Administrator';

DBMS_OUTPUT.put_line('Adding Responsibility...'||CHR(10));

FND_USER_PKG.addresp( username => 'DELOITTE'
,resp_app => l_appl_short_name
,resp_key => l_resp_key
,security_group => 'STANDARD'
,description => ''
,start_date => SYSDATE - 1
,end_date => SYSDATE + 364
);

DBMS_OUTPUT.put_line('Repsonsibility added sucessfully.');

COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SUBSTR(SQLERRM,1,200));
ROLLBACK TO create_user;
END;
/
####### Script to create LENOVO User ###################


Using following tables we can get the end-user access details:
APPLSYS.FND_LOGINS
APPLSYS.FND_LOGIN_RESPONSIBILITY
APPLSYS.FND_LOGIN_RESP_FORMS
APPLSYS.FND_UNSUCCESSFUL_LOGINS
FND_CONCURRENT_REQUESTS
ICX.ICX_FAILURES

Clients requires to restrict duplicate user sessions usually, so in previous project we setted ICX_SESSIONS of DISABLED_FALG=Y to restrict the duplicate session of E-buss suite user. { This can obtain by patch 21228669 }

Requirement:

Client want to secure login policy to more robust for users of 11i.
> The password contains at least one letter and at least one number
> The password does not contain the username
> If user tried more than 3 attempts , password should lock
> If user unable to login more than 2 attempted, need weekly report of those users.

After doing a bit R & D, recommanded for the client the following:

1. Change the profile option " Signon Password Failure limit" to 3, defalt is None.
2. Change the profile option " Signon Password Hard to guess" to YES, defalt is NO.
3. We are sheduling a con.program weekly using the tables FND_UNSUCCESSFUL_LOGINS and ICX.ICX_FAILURES to reports on login attempts.


JDK version userd by AD Utilities
AD Utilities use jdk 1.3 or jdk 1.4. You can detect the version used by the AD Utilities with the command $ADJVAPR