Oracle EBS R12 Important Information

Start & Stop the Application Services

Login as the ORACLE user who is the owner of the application file system ( APPL_TOP and Applications Technology Stack )
First, change the directory to APPL_TOP.
[oracle@oracle ~]$ cd /d01/oracle/VIS/apps/apps_st/appl
Then, execute the consolidated applications environment file (APPS<context name>.env) as follows.
[oracle@oracle appl]$ . APPSVIS_oracle.env
Then, change the directory to INST_TOP/admin/scripts.
[oracle@oracle ~]$ cd $INST_TOP/admin/scripts
Then you can start or stop the application services by running below commands through adstrtal.sh and adstpall.sh scripts.

Start Apps

[oracle@oracle scripts]$ sh adstrtal.sh
You are running adstrtal.sh version 120.15
Enter the APPS username: apps
Enter the APPS password:

Stop Apps

[oracle@oracle scripts]$ sh adstpall.sh
You are running adstpall.sh version 120.10
Enter the APPS username: apps
Enter the APPS password:

Check Concurrent Manager Status

[oracle@oracle ~]$ ps -ef|grep FNDLIBR


Start & Stop the Database and Listener

Login as the ORACLE user who is the owner of the database tier file system.
First, change the directory to RDBMS ORACLE_HOME.
[oracle@oracle ~]$ cd /d01/oracle/VIS/apps/apps_st/appl
Then execute the environment file in [RDBMS ORACLE_HOME].
[oracle@oracle ~]$ . VIS_oracle.env
Then, change the directory to ORACLE_HOME/appsutil/scripts/VIS_oracle.
[oracle@oracle ~]$ cd /d01/oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_oracle
Then you can start or stop the database or listener by running below commands through the addlnctl.sh and
addbctl.sh files.


Database Start/Stop

[oracle@oracle VIS_oracle]$ sh addbctl.sh start normal
[oracle@oracle VIS_oracle]$ sh addbctl.sh stop normal

Listener Start/Stop

[oracle@oracle VIS_oracle]$ sh addlnctl.sh start VIS
[oracle@oracle VIS_oracle]$ sh addlnctl.sh stop VIS


Concurrent Requests

The concurrent requests that are executing by all users in the Oracle EBS could be monitored as and when required.

This query could be executed through the database APPS user.

SELECT REQUEST_ID, REQUESTOR, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE,
TRUNC(MOD((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24,24))"Hrs",
TRUNC(MOD((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60,60))"Min",
TRUNC(MOD((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60*60,60))"Sec",
PROGRAM, ARGUMENT_TEXT
FROM FND_CONC_REQ_SUMMARY_V
WHERE (nvl(ACTUAL_COMPLETION_DATE,sysdate)-ACTUAL_START_DATE)*24*60*60 > 1
and ACTUAL_START_DATE like sysdate
--and ACTUAL_START_DATE like to_date('16-JAN-12','DD-MON-YY') -- to get data of exact date
ORDER BY ACTUAL_COMPLETION_DATE DESC


Cancel a Request from Backend

(1)

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

v$process c,

v$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid

AND a.request_id in (16595668)

(2)
SELECT a.request_id, d.sid, d.serial# , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = 19024108
AND a.phase_code = R;

Then kill the SPID from putty login to oracle user.

UPDATE apps.fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code in ('P','R')
and request_id =19024108;

commit;


SELECT PROCESS pid, s.process, s.status, s.username, s.schemaname,s.client_identifier,s.osuser,s.program,s.module, s.machine, s.terminal, sql.sql_text
 FROM v$session s, v$sql sql WHERE sql.sql_id(+) = s.sql_id AND s.type = 'USER';


Oracle Database Growth

select to_char(creation_time, 'RRRR Month') "Month",
       sum(bytes)/1024/1024/1024 "Growth in GB"
  from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')


 SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM    (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;