Starting and Stopping Oracle Enterprise Manager 11g Grid Control

OMS_HOME and AGENT_HOME you can find /etc/oratab

[oracle@smrgccadmdl1 ~]$ grep -E 'oms|agent' /etc/oratab
*:/d01/oracle/product/Middleware/oms11g:N
*:/d01/oracle/product/Middleware/agent11g:N
so OMS_HOME would be /d01/oracle/product/Middleware/oms11g   &
   AGENT_HOME would be /d01/oracle/product/Middleware/agent11g

NOTE: The Oracle Enterprise Manger 11g Grid Control install process puts a script called gcstartup in /etc/init.d that will stop and start the Oracle Management Service and Agent on OS startup/shutdown. It does not start or stop the repository database. If you would like to stop the automated startup/shutdown of Grid Control services place comments in front of the OMS and AGENT home directories in the /etc/oratab file or remove the script /etc/init.d/gcstartup.

Stopping Oracle Enterprise Manager 11g Grid Control

Stop the Oracle Management Service
From the OMS_HOME directory run the following to stop the OMS and WebTier
export ORACLE_HOME=/d01/oracle/product/Middleware/oms11g
export ORACLE_SID=svrpgcc
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin:$PATH
services[oracle@smrgccadmdl1 ~]$ cd $OMS_HOME/bin

[oracle@smrgccadmdl1 bin]$ emctl stop oms -all (emctl stop oms -force)

Oracle Enterprise Manager 11g Release 1 Grid Control  

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Oracle Management Server Successfully Stopped

Oracle Management Server is Down

Note:

if you do not include the –all flag the HTTP services for the WebLogic Server will not be shutdown. Stop the Oracle Management Agent
From the AGENT_HOME directory run the following to stop the Agent
export ORACLE_HOME=/d01/oracle/product/Middleware/agent11g
export ORACLE_SID=svrpgcc
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin:$PATH

[oracle@smrgccadmdl1 ~ ]$ cd $AGENT_HOME/bin
[oracle@smrgccadmdl1 bin]$pwd
/d01/oracle/product/Middleware/agent11g/bin

[oracle@smrgccadmdl1 bin]$ emctl stop agent

 

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Stopping agent ... stopped.

Stop the repository DataBase:

[oracle@smrgccadmdl1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 28 05:44:14 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Stop the Listener :

[oracle@smrgccadmdl1 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-AUG-2012 05:55:48
 
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.108.101.111)(PORT=1521)))
The command completed successfully
Starting Oracle Enterprise Manager 11g Grid Control :

1). Start the Listener

2). Start the Reposioty DataBase

3). Start teh OMS & WebTier Services(Oracle Management Agent--> emctl start oms)

4). start the AGENT_HOME (emctl start agent)

5). https://smrgccadmdl1.domain:7799/em

Note: check url for installation

http://www.oracledistilled.com/grid-control/installing-oracle-enterprise-manager-grid-control-11gr1-on-linux/

Querys for DBLOCKS & Size of the Table :

------------------------------:To find BDLOCKS :------------------------------------------

SELECT s.inst_id, NVL (s.username, 'Internal') "Database User", m.SID,
s.serial#, p.spid "DB OS Process", m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server", s.process "Apps OS process", m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name", SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;
To know Particilar Table Size :
SQL> SELECT tbl.owner TABLE_OWNER,
tbl.TABLE_NAME ,
tbl.TABLESPACE_NAME,
ceil((BLOCKS*8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)) "Data lower than HWM in MB"
FROM  DBA_TABLES tbl
where table_name like 'XX_EMF_ERROR_DETAILS'; 

-----------------------------------Temp Usage :-----------------------------------

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

---------------------To know the Product  Version & Patchset level---------------------

select decode(nvl(a.APPLICATION_short_name,'Not Found'),
        'SQLAP','AP','SQLGL','GL','OFA','FA',
        'Not Found','id '||to_char(fpi.application_id),
        a.APPLICATION_short_name) apps,
        decode(fpi.status,'I','Installed','S','Shared',
               'N','Inactive',fpi.status) status,
        fpi.product_version,
        nvl(fpi.patch_level,'-- Not Available --') Patchset,
        to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
  and fpi.oracle_id = o.oracle_id(+)
order by 1,2
/
APPS                                               STATUS    PRODUCT_VERSION                PATCHSET                       Update Date
-------------------------------------------------- --------- ------------------------------ ------------------------------ -----------------
AD                                                 Shared    12.0.0                         R12.AD.B.2                     05-Sep-2010

-------------------------To find Conc-req  Completion ------------------------

SELECT fcr.REQUEST_ID,fc.USER_CONCURRENT_PROGRAM_NAME,TO_CHAR(fcr.actual_start_date,'DD-MON HH24:MI:SS') actual_start_date,TO_CHAR(fcr.actual_completion_date,'DD-MON HH24:MI:SS') actual_completion_date,fcr.STATUS_CODE
FROM FND_CONCURRENT_REQUESTS fcr,
                FND_CONCURRENT_PROGRAMS_TL fc
WHERE fcr.phase_code='C'
                AND fc.concurrent_program_id=fcr.concurrent_program_id
                AND fc.USER_CONCURRENT_PROGRAM_NAME='&Conc-req_name' order by fcr.actual_start_date ;

-------------
TOP sqls :
-------------
set linesize 1000
set pagesize 1000
col sql for a50
SELECT sql,buffer_gets, executions
FROM (SELECT hash_value,address,substr(sql_text,1,40) sql,
            buffer_gets, executions, buffer_gets/executions "Gets/Exec"
      FROM V$SQLAREA
      WHERE buffer_gets > 100000 AND executions > 10
      ORDER BY buffer_gets DESC)
      WHERE rownum <= 5
/

HOW TO DO TABLE AND INDEX REORGANIZATION [ID 736563.1]

1). Alter table move (to another tablespace, or same tablespace).
alter table table_name move; This would do the table reorg.

2).
SQL> alter table table_name enable row movement;
Table altered.
There are 2 ways of using this command.
Method 1. Break in two parts: In first part rearrange rows and in second part reset the HWM.
Part 1: Rearrange (All DML's can happen during this time)
SQL> alter table table_name shrink space compact;
Table altered.
Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table table_name shrink space;
Table altered.
Method 2. All in one go:
SQL> alter table table_name shrink space; (Both rearrange and restting HWM happens in one statement)
The above mentioned methods are online reorg and would not cause any impact.
Oracle 10g describes the reorganization operations that are possible using the ONLINE clause in the
SQL> CREATE/ALTER INDEX and TABLE statements.
ALTER TABLE emp MOVE ONLINE;
CREATE INDEX emp.ename.idx ON emp(ename) ONLINE;
ALTER INDEX emp.ename_idx REBUILD ONLINE;
ALTER INDEX emp.ename_idx COALESCE;
Reclaiming Unused Space

==============    Truncate Table   ===================

TRUNCATE TABLE XXASL.XX_EMF_ERROR_DETAILS;
ALTER TABLE XXASL.XX_EMF_ERROR_DETAILS ENABLE ROW MOVEMENT;
ALTER TABLE XXASL.XX_EMF_ERROR_DETAILS SHRINK SPACE;
ALTER INDEX XXASL.IDX$$_2A2D0001 REBUILD;

=============  Indix is Valid / Not    ===================
SQL> select INDEX_NAME,STATUS from dba_indexes where INDEX_NAME='&indx_name' and owner='&Owner';
If  the status is UNUSABLE then fire the below comman then check the status will bechanged to  VALID

SQL> alter index owner.<indx_name> rebuild online;

............................................&&&..................................................

How To activate the Output Post Processor :

The Profile Option "Concurrent: GSM Enabled" must be set to Y
Profile--> System -->Profile : Concurent: GSM Enabled (find)
Then:
1. Login  to Apps with sysadmin responsibility
2. Navigate to:  Concurrent -> Managers -> Define
3. Query for
          Manager = 'Output Post Processor'
    or   Short Name = FNDCPOPP
4. Check the checkbox "Enable" .
5. Click on 'Work Shifts button
6. see Work Shift of the OPP and
    Set
            Processes = 1
    and
            Parameters = oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
    and
            Sleep Second = 30
7. Save
8. Navigate to:_ Concurrent -> Managers -> Administer
9. Make sure the Output Post Processor Status is not 'Deactivate'
    Set Status to 'Activate' if necessary.
10. Bounce The Concurrent Managers using
    adcmctl.sh
-----------------------------------