Request Warning Message : The Report will be submitted with default or preexisting values for request parameter

When I run any concurrent Request e.g active users I am getting warning when I click on Submit button

Warning : The Report will be submitted with default or preexisting values for request parameters,

completion options and report language.Press Ok to proceed with request submission

Press cancel to change the default request parameter values , to specify completion

options or to specify report language.

Solution : Note ID - 180092.1

This is an enchancement to alert the user that they are about to submit a request

with all the default values.

To fix this issue :

Turn off profile option -- CONCURRENT:VALIDATE REQUEST SUBMISSION to No

Now you will not get any warning message..

Create OraInventory.

Steps are described below:

Create the oraInst.loc File

If you plan to install Oracle products using the Installer in silent or suppressed mode, you must manually create the oraInst.loc file if it does not already exist. This file specifies the location of the Oracle Inventory directory where the Installer creates the inventory of Oracle products installed on the system.

Note:

If Oracle software has been installed previously on the system, the oraInst.loc file might already exist. If the file does exist, you do not need to create a new file.

To create the oraInst.loc file, follow these steps:

1. Switch user to root:

         $su - root

2. On HP-UX, Solaris, Tru64 UNIX, and zSeries Linux, create the /var/opt/oracle directory if it does not exist:

# mkdir -p /var/opt/oracle

3. Change directory as follows, depending on your operating system:

             AIX, Linux x86, or Linux Itanium:

·         # cd /etc

             Other operating systems:

·         # cd /var/opt/oracle

4.  Use a text editor to create the oraInst.loc file, containing the following lines:

e.g vi oraInst.loc

inventory_loc=ORACLE_BASE/oraInventory

inst_group=groupname e.g dba

In this example, ORACLE_BASE is the path of the Oracle base directory, for example, /u01/app/oracle.

5. Enter the following commands to set the appropriate owner, group, and permissions on the oraInst.loc file:

E.g

# chown oracle: dba oraInst.loc

# chmod 664 oraInst.loc

Adpatch Fails due to Pre-req patch..

If Adpatch Fails due to pre-requisite Patch or for some other reason and (for e.g) 60 % of the applied has been applied and it took around 1 hour. Instead of applying this patch from begining you need to do the followong steps in order to save the time you spend while applying the patch.

1. Using the adctrl utility, shutdown the workers.

    a. adctrl

    b. Select option 3 “Tell worker to shutdown/quit”

  2. Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema

    a. sqlplus applsys/<password>

    b. create table fnd_Install_processes_back

       as select * from fnd_Install_processes;

    c. The 2 tables should have the same number of records.

       select count(*) from fnd_Install_processes_back;

       select count(*) from fnd_Install_processes;

3.  Backup the AD_DEFERRED_JOBS table.

    a. sqlplus applsys/<password>

    b. create table AD_DEFERRED_JOBS_back

       as select * from AD_DEFERRED_JOBS;

    c. The 2 tables should have the same number of records.

       select count(*) from AD_DEFERRED_JOBS_back;

       select count(*) from AD_DEFERRED_JOBS;

4.  Backup the .rf9 files located in $APPL_TOP/admin/<SID>/restart directory.

    At this point, the adpatch session should have ended and the cursor should

    be back at the Unix prompt.

    a. cd $APPL_TOP/admin/<SID>

    b. mv restart restart_back

    c. mkdir restart

5.  Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.

    a. sqlplus applsys/<password>

    b. drop table FND_INSTALL_PROCESSES;

    c. drop table AD_DEFERRED_JOBS;

6.  Apply the new patch ( say pre-req patch).

      Once this Pre-req Patch is applied successfully.

7.  Restore the .rf9 files located in $APPL_TOP/admin/<SID>/restart_back

    directory.

    a. cd $APPL_TOP/admin/<SID>

    b. mv restart restart_<patchnumber>

    c. mv restart_back restart

8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS

    schema.

    a. sqlplus applsys/<password>

    b. create table fnd_Install_processes

       as select * from fnd_Install_processes_back;

    c. The 2 tables should have the same number of records.

       select count(*) from fnd_Install_processes;

       select count(*) from fnd_Install_processes_back;

9. Restore the AD_DEFERRED_JOBS table.

    a. sqlplus applsys/<password>

    b. create table AD_DEFERRED_JOBS

       as select * from AD_DEFERRED_JOBS_back;

    c. The 2 tables should have the same number of records.

       select count(*) from AD_DEFERRED_JOBS_back;

       select count(*) from AD_DEFERRED_JOBS;

10. Re-create synonyms

    a. sqlplus apps/apps

    b. create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;

    c. create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;

11. Start adpatch, it will resume where it stopped previously.

Inactive Form Sessions

Inactive Form Sessions

set heading on

set line 9999

set pages 9999

col status for a8

col prog for a10

col username for a8

col module for a15

select p.sid,p.serial#, p.status,p.username,p.action,p.module,p.logon_time

from

(select distinct b.sid,b.serial# ,b.status,b.program,b.username,b.action,b.module,

to_char( b.logon_time, 'dd-MON-yyyy hh24:mi:ss' ) logon_time,

trunc( sysdate-b.logon_time ) "Dy",

trunc( mod( (sysdate-b.logon_time)*24, 24 ) )  "Hr",

trunc( mod( (sysdate-b.logon_time)*24*60, 60 ) )  "Mi",

trunc( mod( (sysdate-b.logon_time)*24*60*60, 60 ) ) "Sec"

from V$access a,v$session b, v$process c

where a.sid=b.sid

and b.paddr=c.addr

and b.status='INACTIVE'

and (b.action like '%FRM%' or b.action like '%frm%' or b.program like '%TOAD%' or b.program like '%toad%' or b.program like

'SQL%' or b.program like '%sql%' or b.program like '%FRM%'

or b.program like '%frm%' or b.action like 'SQL%' or b.action like 'sql%' or b.action like 'TOAD%' or b.action like 'toad%')

and (trunc( mod( (sysdate-b.logon_time)*24,24)) >=12 or trunc( sysdate-b.logon_time )>=1)) p order by p.logon_time;

Inactive Sessions and Active Sessions in Oracle

Inactive Sessions

Inactive for more than 1 hour and kill those sessions.

The sql to be executed is given below

Sql> select 'alter system kill session '||''''||sid||','||serial#||''' immediate;',(sysdate-logon_time)/24

        from v$session

        where username is not null and status='INACTIVE' and (sysdate-logon_time)/24 > .08333333;

   Find Time :
2 hour
sql >  select 2/24 from dual;
o/p : .08333333

sql > select 'alter system kill session '''||sid||','||serial#||''' immediate;'

         from v$session

        where status='INACTIVE' and machine='your hostname.domainname';

Active Sessions

/* ckactmt.sql

     check server tiers active sessions

     note: state of WAITING means session is currently waiting

*/

col usersrvr format a12 trunc head ServerName

col logonat format a12 head LoggedOn

col osuser format a8 trunc

col event head Current_Wait_Event format a25 trunc

col state format a7 trunc

col sessprog format a12 head SessProg trunc

col spid format a8 head DBProc

col LastCallET     format a11

col holdinglatch format a20 trunc head HoldingLatch

col sid format 99999

break on usersrvr skip 1 on report

compute count of sid on usersrvr

compute count of sid on report

select replace(s.machine,'HEADLANDS\',null) usersrvr, s.sid,

to_char(s.logon_time,'mm/dd hh24:mi') logonat,

   floor(last_call_et/3600)||':'||

   floor(mod(last_call_et,3600)/60)||':'||

   mod(mod(last_call_et,3600),60) "LastCallET",

s.osuser, nvl(s.module,p.program) sessprog, w.event, w.state, p.spid,

decode(h.sid,null,'None',h.name) holdinglatch

from v$session s, v$session_wait w, v$process p, v$latchholder h

where type = 'USER'

and status = 'ACTIVE'

and s.sid = w.sid

and s.paddr = p.addr

and s.osuser is not null

and s.sid = h.sid (+)

order by 1,4

//

Find Invalid,Unusable Indexes.Rebuild Index.

Check the Status of Indexes:

SELECT status,count(*) from USER_INDEXES GROUP BY STATUS;

SELECT status,count(*) from ALL_INDEXES GROUP BY STATUS;

SELECT status,count(*) from DBA_INDEXES GROUP BY STATUS;

Solution

   1. Drop the specified index and/or recreate the index

   2. Rebuild the specified index

   3. Rebuild the unusable index partition

Rebuilding UNUSABLE indexes online, querying to USER_INDEXES,ALL_INDEXES,DBA_INDEXES view.

Sql>  SELECT count(*),status FROM all_indexes GROUP BY status;

Sql>  select 'alter index '||owner||'.'||index_name||' rebuild online ;' from dba_indexes 
          where status = 'UNUSABLE' ;

Output :

 'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINE;'
--------------------------------------------------------------------------------
alter index APPLSYS.FND_COLUMNS_U4 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_U2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_PK rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_N2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_N1 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U3 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U1 rebuild online ;