Unable to start MT services using the script adstrtal.sh

Issue

$ adstrtal.sh
You are running adstrtal.sh version 120.13.12000000.4
Enter the APPS username: apps
Enter the APPS password:
adstrtal.sh: Database connection could not be established. Either the database is down or the APPS
credentials supplied are wrong.
USAGE:  adstrtal.sh <appsusername/appspassword>
adstrtal.sh: exiting with status 1

Solution:

Workaround
-->apps password is correct and DB is up and running,but wrong entries in adstrtall.sh
-->Added a hash to the following :

#sqlplus -s /nolog > /dev/null 2>&1 <<EOF
#whenever sqlerror exit failure
#connect apps/apps
#exit success
#EOF

-->Now MT services are started properly

How to check whether the update/delete/drop statement is running or not Example

The below script is running from long time,so by using the below steps we can check the progress of the statement

update mtl_material_transactions
set costed_flag = 'N',
transaction_group_id = NULL,
transaction_set_id = NULL
where costed_flag = 'E' or costed_flag = 'N';

On DB node run the below command to check the session SID

col module for a20
select inst_id,sid,module,process,status from gv$session where  status ='ACTIVE'  and username ='APPS' ;

Example

SQL> col module for a20
select inst_id,sid,module,process,status from gv$session where  status ='ACTIVE'  and username ='APPS' ;SQL>

INST_ID        SID    MODULE               PROCESS      STATUS

1             2046    FNDGSCST                   5564         ACTIVE
1             2073    WSHINTERFACE         17385        ACTIVE
1             2096    SQL*Plus                       31927        ACTIVE

1       2096 SQL*Plus             31927        ACTIVE  ==>this is the session for the running statement

Execute the below command to confirm the running statement with the SID from above command

SQL> select SQL_TEXT from v$sqltext where ADDRESS in (select SQL_ADDRESS from v$session where SID=2096) order by piece;

SQL_TEXT
----------------------------------------------------------------
update mtl_material_transactions set costed_flag = 'N', transact
ion_group_id = NULL, transaction_set_id = NULL where costed_flag
= 'E' or costed_flag = 'N'

SQL>

Execute the below command to check the session is active or not

REM checking Timing details, Client PID of associated oracle SID
REM ============================================================
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

Example

SQL> REM checking Timing details, Client PID of associated oracle SID
REM ============================================================
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ........................
SQL> Details of SID / SPID / Client PID
SQL> ==================================
SQL>    ......................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMAN  AME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));  
Enter value for sid: 2096
Enter value for spid:
Enter value for clientpid:

Session  Id.............................................: 2096
Serial Num..............................................: 26063
User Name ..............................................: APPS
Session Status .........................................: ACTIVE
Client Process Id on Client Machine ....................: *31927*
Server Process ID ......................................: 3498
Sql_Address ............................................: 000000023C9B9FC0
Sql_hash_value .........................................: 3476221953
Schema Name ..... ......................................: APPS
Program  ...............................................: sqlplus@test15 (TNS V1-V3)
Module .................................................: SQL*Plus
Action .................................................:
Terminal ...............................................: pts/8
Client Machine .........................................: test15
LAST_CALL_ET ...........................................: 2689
S.LAST_CALL_ET/3600 ....................................: .746944444444444444444444444444444444444
SQL>

Session Status is Active

How to findout all locked objects?

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM   dba_objects a,
v$locked_object b
WHERE  a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14
SET VERIFY ON

Users Cannot Access Forms Based Applications in R12. Receive"FRM-92050: Failed to connect to Server: /forms/servlet-1" (Doc ID 1086477.1)

Solution

To implement the solution, please execute the following steps:

Step 1:- Stop all services on ALL forms tiers, set the application environment and relink forms executable files

cd $ORACLE_HOME/forms/lib
make -f ins_forms.mk sharedlib

cd $ORACLE_HOME/forms/lib
make -f ins_forms.mk install

adrelink.sh force=y "fnd fndfmxit.so"
adrelink.sh force=y "fnd fndrwxit.so"

Step 2:- Start all services on ALL forms tiers

Step 3:- Retest the issue.

How to check the database size

select round ((a.data_size+b.temp_size+c.redo_size)/1024/1024/1024,2) "DB_size_in_GB"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;

Error while running Autoconfig after Refresh Activity

 Got Error while running autoconfig at Refresh
==============================================
On MT Node

Autoconfig Lof file Error...
---------------------------------------------------------------
                   ADX Database Utility
---------------------------------------------------------------

getConnection() -->
    sDbHost    : abcgemmsd57
    sDbDomain  : contappssourcing.com
    sDbPort    : 10929
    sDbSid     : STAGE
    sDbUser    : APPS
    Trying to connect using SID...
getConnectionUsingSID() -->
    JDBC URL: jdbc:oracle:thin:@abcgemmsd57.contappssourcing.com:10929:STAGE
   Exception occurred: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
    Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=abcgemmsd57.contappssourcing.com)(PORT=10929))(CONNECT_DATA=(SERVICE_NAME=STAGE)))
    Exception occurred: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
    Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=abcgemmsd57.contappssourcing.com)(PORT=10929))(CONNECT_DATA=(SERVICE_NAME=STAGE.contappssourcing.com)))
    Exception occurred: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
    Connection could not be obtained; returning null

-------------------ADX Database Utility Finished---------------
        Verifying connection to the Database   : Could not be stablished
        No Restore Profile file created.

Restore Profile utility ran successfully
===========================================================================

Solution
=========

Here abcgemmsd57 is DB Host.
As per the Error i found that on DB Host abcgemmsd57 ..Listener was down
Bring up the Listener on Host 57 and run autoconfig again on MT Node..it will works

Autoconfig Log File Location:

=====================
Log File --> $APPL_TOP/admin/sid/log/timestamp/adconfig.log

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