How to clean FND_NODES table

Step 1:   Always apply the latest cloning patches to avoid all the bugs and fixes
Step 2:  SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

It will delete all old data from the table.

Step 3:  Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers to repopulate the required system tables.

Adding Responsibility through Backend

BEGIN
FND_USER_RESP_GROUPS_API.LOAD_ROW
(X_USER_NAME => 'SATYA',
X_RESP_KEY => 'APPLICATION_DEVELOPER',
X_APP_SHORT_NAME => 'FND',
X_SECURITY_GROUP => 'STANDARD',
X_OWNER => 'SYSADMIN',
X_START_DATE => Trunc(sysdate),
X_END_DATE => NULL,
X_DESCRIPTION => NULL,
X_LAST_UPDATE_DATE => Trunc(sysdate));
COMMIT;
END;

To check whether the instance is RAC enabled or not

SQL> sho parameter CLUSTER_DATABASE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     4
SQL>
SQL> select * from v$active_instances;

INST_NUMBER
-----------
INST_NAME
--------------------------------------------------------------------------------
          1
Stage1

          2
Stage2

          3
Stage3


INST_NUMBER
-----------
INST_NAME
--------------------------------------------------------------------------------
          4
Stage4

Note: Here if the parameter value shows "TRUE" means that the node is RAC.
and if the above select command shows rows ..that means RAC is enabled to that instance.

Or else you can check in init.ora file from $ORACLE_HOME/dbs

*.cluster_database=false

Note: If the cluster database set to "false" --RAC is disabled.

How to Enable and Disable the archive log

To Disable the Archive Log:

change the following value to false in the init.ora file
log_archive_start=false

shutdown the database:
shutdown immediate

disable the archivelog:
startup mount
alter database noarchivelog;
alter database open;
archive log list;

To Enable the Archive Log:

change the following value to true in the init.ora file
log_archive_start=true

shutdown the database:
shutdown immediate

enable the archivelog:
startup mount
alter database archivelog;
alter database open;
archive log list;
archive log all;

Compiling library files(.pll) in Oracle Apps

1. Logon as applmgr to the application server where the forms are installed.

2. Change directory to $AU_TOP/resource where all the .pll reside.

cd $AU_TOP/resource

3. Syntax for the f60gen command is as follows:

f60gen module=.pll userid=
password> output_file=/resource/.plx
module_type=library batch=no compile_all=special

4. Examples:

cd $AU_TOP/forms/US

Example 1:

f60gen module=FNDCONC.pll userid=APPS/APPS output_file=$AU_TOP/resource/FNDCONC.plx module_type=library batch=no compile_all=special

Example 2:
f60gen module=FAXMAD.pll userid=FA/FA output_file=$AU_TOP/resource/FAXMAD.plx module_type=library batch=no compile_all=special

How to Find out database growth in a year

SQL> select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024
"Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month'); 
Month                            Growth in Meg
-------------------------------- -------------
2010 August                              18760
2010 December                            16200
2010 July                                36600
2010 November                            29740
2010 October                             10800
2010 September                            8900
2011 April                               12160
2011 February                            39500
2011 January                             18000
2011 June                                 1740
2011 March                               10800

Month                            Growth in Meg
-------------------------------- -------------
2011 May                             37808.875

12 rows selected.

SQL>

To Check whether the Workflow Components are Up & Running?

SQL> col component_name for a45;
col component_status for a30;
set pagesize 200;
select component_name,component_status from fnd_svc_components;SQL> SQL> SQL>

COMPONENT_NAME                                COMPONENT_STATUS
--------------------------------------------- ------------------------------
ECX Inbound Agent Listener                    RUNNING
ECX Transaction Agent Listener                RUNNING
Workflow Deferred Agent Listener              RUNNING
Workflow Deferred Notification Agent Listener RUNNING
Workflow Error Agent Listener                 RUNNING
Workflow Inbound Notifications Agent Listener RUNNING
Workflow Notification Mailer                  STOPPED
Web Services OUT Agent                        RUNNING
Web Services IN Agent                         RUNNING
Workflow Java Deferred Agent Listener         RUNNING
Workflow Java Error Agent Listener            RUNNING
Workflow Inbound JMS Agent Listener           STOPPED
WF Deferred Agnt Listener1                    RUNNING
WF Deferred Agnt Listener2                    RUNNING
WF Deferred Agnt Listener3                    RUNNING
WF Deferred Agnt Listener4                    RUNNING

16 rows selected.

How to know the Frontnd url from backnd?

From SQL Query:
--------------
For Ex:-

SQL> select home_url from apps.icx_parameters;

HOME_URL
---------------------------------------------
https://testinstance.com

From Command Prompt:
-------------------
For Ex:-

$ env |grep http
FORMS60_MAPPING=https://testinstance.com

or

$ grep FORMS60_MAPPING Test_Instance11.xml
                        <FORMS60_MAPPING oa_var="s_f60map">https://testinstance.com/OA_TEMP</FORMS60_MAPPING>
$

Note: Here https://testinstance.com is the url.

Unzip all files at a time -Linux

unzip \*.zip

To get the OUTPUT of SQL in Excel Sheet

set pagesize 30000
set echo off
set colsep ','
set heading on
SET LINESIZE 999 VERIFY OFF FEEDBACK OFF
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
set pages 1000
SPOOL output.xls

<<here give the sql command>>

SPOOL OFF
SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT ON
SET LINESIZE 80 VERIFY ON FEEDBACK ON

How to check PCP enabled or Not

Check in Profile Option from Frontnd.
--------------------------------------
Concurrent:PCP Instance Check%
It should be set to ON
Note:If it is set to ON.that means PCP is enabled.


From Backnd
----------------
$ grep -i appldcp $CONTEXT_FILE
         <APPLDCP oa_var="s_appldcp">OFF</APPLDCP>

Here "OFF" means ..PCP is disabled.

How to check GSM is Enabled or Not

SQL> select DECODE(b.profile_option_value, 'Y', 'Enabled', 'Disabled') DETAILS
from fnd_profile_options a, fnd_profile_option_values b
where a.APPLICATION_ID = b.APPLICATION_ID
and a.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID
and a.PROFILE_OPTION_NAME = 'CONC_GSM_ENABLED';  2    3    4    5

DETAILS
--------
Enabled

SQL> select CONCURRENT_QUEUE_NAME, ENABLED_FLAG, MAX_PROCESSES, RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'FNDSM%';   2    3

CONCURRENT_QUEUE_NAME          E MAX_PROCESSES RUNNING_PROCESSES
------------------------------ - ------------- -----------------
FNDSM_AUOHSGECA78              Y             1                 1

Note: Here Flag "Y"  means GSM Enabled.

How to confirm if Report Server is Up and Running ?

Report Server is started by executable rwmts60 on concurrent manager Node
and this file is under $ORACLE_HOME/bin .execute command on your server like
For Ex:-

$ ps -ef | grep rwmts60
aptest 10929     1  0 Jul03 ?        00:00:00 rwmts60 name=REP60_TEST
aptest 10931 10929  0 Jul03 ?        00:00:00 rwmts60 name=REP60_TEST
aptest 10932 10931  0 Jul03 ?        00:00:00 rwmts60 name=REP60_TEST
aptest 10933 10931  0 Jul03 ?        00:00:00 rwmts60 name=REP60_TEST
aptest 10934 10931  0 Jul03 ?        00:00:00 rwmts60 name=REP60_TEST
aptest 10935 10931  0 Jul03 ?        00:00:00 rwmts60 name=REP60_TEST
aptest 10936 10931  0 Jul03 ?        00:00:00 rwmts60 name=REP60_TEST
aptest 10937 10931  0 Jul03 ?        00:00:00 rwmts60 name=REP60_TEST
aptest  2193  2074  0 14:23 pts/6    00:00:00 grep rwmts60
$

In 11g when a table is dropped ..we can retriew back the following table

using FLASHBACK query.

Restore the table with the following command:

FLASHBACK TABLE <TABLE_NAME> TO BEFORE DROP;

For Example:-

SQL> create table oracle (name varchar2(20));

Table created.

SQL> insert into oracle values('Satya');

1 row created.

SQL> select * from oracle;

NAME
--------------------
Satya

SQL> drop table oracle;

Table dropped.

SQL> select * from oracle;
select * from oracle
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Note: Now the above table "oracle is dropped .
Table does not exists...we can see the table in recyclebin by following sql command:-

SQL> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;

OBJECT_NAME                    ORIGINAL_NAME                    TYPE
------------------------------ -------------------------------- -------------------------
BIN$pyfjVjvV3OHgQFWMdMhiQg==$0 ORACLE                           TABLE
BIN$pyfjVjvU3OHgQFWMdMhiQg==$0 SS                               TABLE
BIN$pxeahCChqOTgQFWMdMgheQ==$0 SS                               TABLE
BIN$prBPuubG8P7gQFWMdMgPLA==$0 SR_12345_                        TABLE
BIN$piofHuXuVWDgQFWMdMgC+A==$0 BKP_INVALID_AKI                  TABLE
BIN$piakkjwShjXgQFWMdMgm0A==$0 INVALIDS_20JUN2011               TABLE
BIN$piX8k2KQfLzgQFWMdMheXA==$0 SR_12345_                        TABLE
BIN$paZUgalvhhXgQFWMdMhzlQ==$0 SR_1234_                         TABLE
BIN$pZDIBAqsLYDgQFWMdMgwow==$0 SR_1234_                         TABLE
BIN$pZAjrDASg8bgQFWMdMgIbQ==$0 SR_1234_                         TABLE
BIN$pSCWykSWosTgQFWMdMgxXw==$0 BKP_INVALID_AKI                  TABLE
BIN$pKfzjeIaO6zgQFWMdMgLVA==$0 SR_123_                          TABLE

12 rows selected.

SQL>

Now we can get the table from Recyclebin by using FLASHBACK query:

SQL> FLASHBACK TABLE oracle to before drop;

Flashback complete.

SQL>

Now check the table "oracle".it will exits:-

SQL> select * from oracle;

NAME
--------------------
Satya