Script Monitor CPU usage

#usr/bin/sh!
test=`ps -eaf -o pid|sort -r |head -200`
test=`echo $test | tr " " ","`;

tme=`date +'%d-%m-%y %H:%M'`
echo $tme

${ORACLE_HOME}/bin/sqlplus -s << @@EOF@@
/ as sysdba
SELECT n.username,s.SID,
TRUNC(((s.VALUE * 100) / (SELECT VALUE
FROM v\$sysstat
WHERE NAME = 'CPU used by this session')
),
2
) "% CPU Usage",
a.sql_text
FROM v\$sesstat s, v\$statname t, v\$session n, v\$process p, v\$sqlarea a
WHERE p.spid IN ($test)
AND s.statistic# = t.statistic#
AND n.SID = s.SID
AND DECODE (n.sql_address,
n.sql_address, n.prev_sql_addr,
n.sql_address
) = a.address
AND DECODE (n.sql_hash_value,
n.sql_hash_value, n.prev_hash_value,
n.sql_hash_value
) = a.hash_value
AND n.paddr = p.addr
AND t.NAME = 'CPU used by this session'
AND s.VALUE <> 0
ORDER BY s.VALUE DESC;

exit

Setting .profile

# Setting ORACLE_SID
ORACLE_SID=test
export ORACLE_SID

#Setting ORACLE_HOME
ORACLE_HOME=/export/home/oracle1/product/10.2.0/db_1
export ORACLE_HOME

#Setting TNS_ADMIN
TNS_ADMIN=/export/home/oracle1/product/10.2.0/db_1/network/admin
export TNS_ADMIN=/export/home/oracle1/product/10.2.0/db_1/network/admin

#Setting PATH
PATH=:$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/usr/lib:/bin
export PATH

Script to generate Analyze Schema, Coalesce Tablespace and Compile Invalid Objects

To Generate Coalesce Tablespace:

SELECT 'ALTER TABLESPACE ' || NAME || ' COALESCE;' FROM V$TABLESPACE
WHERE NAME <> 'TEMP'
AND NAME <> 'SYS'
AND NAME <> 'SYSTEM'
AND NAME <> 'UNDOTBS1'
ORDER BY NAME

To Generate Analyze Schema:

SELECT 'execute DBMS_UTILITY.ANALYZE_SCHEMA('''|| username ||''',''COMPUTE'');' FROM dba_users
WHERE USERNAME <> 'SYS' AND USERNAME <> 'SYSTEM'
ORDER BY USERNAME;

To Generate Invalid Objects:

SELECT ' ALTER VIEW '|| owner||'.' || OBJECT_NAME || ' COMPILE;' FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE='VIEW';

SELECT ' ALTER PROCEDURE '|| owner||'.' || OBJECT_NAME || ' COMPILE;' FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE='TRIGGER'

To Generate Index Rebuild:

SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||tablespace_name||' ONLINE;'
FROM DBA_INDEXES
WHERE OWNER NOT IN ('SYS','SYSTEM');

ORA-01031 Insufficient privileges Error in EXECUTE IMMEDIATE Statement

Error Description 1:
Noticed while using EXECUTE IMMEDIATE with TRUNCATE TABLE Statement in Procedure, error message as ORA-01031: insufficient privileges.

Work Around:

SQL> EXECUTE IMMEDIATE 'TRUNCATE TABLE TestSchema.T1';
ORA-01031: insufficient privileges

Solution:
SQL>Grant drop any table to TestSchema;

Error Description 2:
Noticed while using EXECUTE IMMEDIATE with ANALYZE TABLE statement in Procedure, error message as ORA-01031: insufficient privileges.

Work Around:
SQL> EXECUTE IMMEDIATE 'ANALYZE TABLE TestSchema.T1';
ORA-01031: insufficient privileges

Solution:
SQL>Grant Analyze Any to TestSchema;

Error Description 3:
Noticed while using EXECUTE IMMEDIATE with MOVE TABLESPACE statement in Procedure, error message as ORA-01031: insufficient privileges.

Work Around:
SQL> EXECUTE IMMEDIATE 'ANALYZE TABLE TestSchema.T1';
ORA-01031: insufficient privileges

Solution:
SQL>Grant ALTER any TABLE to TestSchema;

OC4J Configuration issue in Oracle Enterprise Manager (OEM)

Error Description:

Noticed while starting EM dbconsole "OC4J Configuration issue".

$emctl start dbconsole
OC4J Configuration issue. $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_testmachine_testdb not found.

testmachine - Host Name
testdb - Database SID

Solution:

Manually configure EM dbconsole using the below command:
$emca -config dbcontrol db

STARTED EMCA at Sep 25, 2009 12:30:45 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: testdb
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ $ORACLE_HOME

Database hostname ................ testmachine
Listener port number ................ 1521
Database SID ................ testdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 25, 2009 12:39:17 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /$ORACLE_HOME/cfgtoollogs/emca/testdb/emca_2009-08-25_12-30-45-PM.log
Sep 25, 2009 12:39:29 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...

Sep 25, 2009 12:41:15 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Sep 25, 2009 12:41:15 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://testmachine:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 25, 2009 12:50:25 PM

Now you check the emctl Status using the below command:

$emctl status dbconsole

Oracle Enterprise Manager 10g Database Control Release 10.2.0.2.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://testmachine:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory $ORACLE_HOME/testmachine_testdb/sysman/log

Use the below link to check Oracle EM Database Control:
http://testmachine:1158/em/

Commands for EM database control:

To Start DB Console:
$emctl start dbconsole

To Stop EM DB Console:
$emctl stop dbconsole

To Know the status of EM DB Console:
$emctl status dbconsole

Statspack Configuration

Step 1: Login as sysdba and execute spcreate.sql ($ sqlplus "/as sysdba")
Step 2: SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
Step 3: Enter value for perfstat_password
Step 4: Choose the Default tablespace for the PERFSTAT user
Step 5: Choose the Temporary tablespace for the PERFSTAT user

To Take Snapshots:

Step 1: Login to perfstat user (sqlplus perfstat/perfstat@dbname)
Step 2: Execute statspack.snap (ie. exec statspack.snap)
Step 3: Execute statspack.snap with 15 minutes interval.
Step 4: Check the list of snapshot using
SELECT snap_id, snap_time
FROM stats$snapshot;
Step 5: Execute spreport.sql to take report(SQL>@rdbms/admin/spreport.sql)
It Prompts for two snapshot Id and report location. You can use the above query to find snap_id.

Steps to Drop Statspack Configuration:
Step 1: Login as sysdba ($sqlplus "/as sysdba")
Step 2: Execute spdrop.sql (i.e SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql)

ORA-12641: Authentication service failed to initialize

test@linux1:~>sqlplus test/test@testdb

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 1 17:05:04 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12641: Authentication service failed to initialize

Solution:
Step 1: check sqlnet.ora ($ORACLE_HOME/network/admin/sqlnet.ora) file
Step 2: Check SQLNET.AUTHENTICATION_SERVICES value
Step3: If SQLNET.AUTHENTICATION_SERVICES value is ALL (i.e. SQLNET.AUTHENTICATION_SERVICES=(ALL))
Step 4: Set the value for SQLNET.AUTHENTICATION_SERVICES is NONE (i.e. SQLNET.AUTHENTICATION_SERVICES=(NONE))