Thursday, March 3, 2011

Manually Configure Enterprise Manager Database Control in 11.2.0.1



Pre-requisite Steps

export ORACLE_HOME=
export ORACLE_SID=
export ORACLE_BASE=
export ORACLE_DBUNIQUENAME=

Check in spfile or pfile
Remote_password_file = ‘EXCLUSIVE’

Check $ORACLE_HOME/dbs
Password file exists or not
If not then create password file using orapwd utility:

$orapwd file=orapwd$ORACLE_SID password=sys entries=5 FORCE=Y

Then run :

$ emca -config dbcontrol db -repos create

STARTED EMCA at Mar 1, 2011 5:13:53 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: PROD
Listener port number: 1521
Listener ORACLE_HOME [ /u02/prod/db/tech_st/11.2.0 ]:
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 ................ /u02/prod/db/tech_st/11.2.0

Local hostname ................ learn.xyz.com
Listener ORACLE_HOME ................ /u02/prod/db/tech_st/11.2.0
Listener port number ................ 1521
Database SID ................ PROD
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 1, 2011 5:14:13 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u02/prod/cfgtoollogs/emca/PROD/em
Mar 1, 2011 5:14:14 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 1, 2011 5:21:12 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Mar 1, 2011 5:21:15 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 1, 2011 5:41:28 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 1, 2011 5:42:28 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToReposi                                                                             tory
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 1, 2011 5:45:55 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 1, 2011 5:46:05 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Mar 1, 2011 5:46:05 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Mar 1, 2011 5:48:30 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Mar 1, 2011 5:48:31 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Mar 1, 2011 5:48:43 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 1, 2011 5:48:43 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 1, 2011 5:50:36 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 1, 2011 5:50:37 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://learn.xyz.com:1158/em <<<<<<<<<<<
Mar 1, 2011 5:51:04 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /u02/prod/db/tech_st/11.2.0/erpdbs.msp.com_PROD/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 1, 2011 5:51:04 PM


 $ emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://learn.xyz.com:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u02/prod/db/tech_st/11.2.0/learn.xyz.com_PROD/sysman/log

$ emctl status agent

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 10.2.0.4.2
OMS Version       : 10.2.0.4.2
Protocol Version  : 10.2.0.4.2
Agent Home        : /u02/prod/db/tech_st/11.2.0/learn.xyz.com_PROD
Agent binaries    : /u02/prod/db/tech_st/11.2.0
Agent Process ID  : 17624
Parent Process ID : 17613
Agent URL         : https://learn.xyz.com:3938/emd/main
Repository URL    : https://learn.xyz.com:1158/em/upload/
Started at        : 2011-03-01 17:48:47
Started by user   : orclprod
Last Reload       : 2011-03-01 17:48:47
Last successful upload                       : 2011-03-01 17:56:09
Total Megabytes of XML files uploaded so far :     9.68
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    77.65%
Data channel upload directory                : /u02/prod/db/tech_st/11.2.0/learn.xyz.com_PROD/sysman/recv
Last successful heartbeat to OMS             : 2011-03-01 17:55:24
---------------------------------------------------------------
Agent is Running and Ready
$


Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade



Gather Schema Statistics" program reported following errors in request log files :

+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP
stats on table FND_CP_GSM_IPC_AQTBL is locked
stats on table FND_SOA_JMS_IN is locked
stats on table FND_SOA_JMS_OUT is locked
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

 Action :

To Check which objects or tables are locked 

sql > SELECT OWNER,TABLE_NAME,STATTYPE_LOCKED
         FROM DBA_TAB_STATISTICS
         WHERE STATTYPE_LOCKED IS NOT NULL;



TO Unlock all the tables in a schema at once :

sql> exec dbms_stats.unlock_schema_stats('schema_owner');
e.g : sql> exec dbms_stats.unlock_schema_stats('apps');

TO Unlock all Individual  tables in a schema at once 

sql> exec dbms_stats.unlock_schema_stats('table_owner','table_name');
e.g : sql > exec dbms_stats.unlock_schema_stats('AR','AR_REV_REC_QT');



There are two reasons for that error message:

1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and it fails with ora-20001 errors.

Following SQL should have returned one row , not two.

SQL> select a.column_name, nvl(a.hsize,254) hsize
from FND_HISTOGRAM_COLS a
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254

2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.

Solution:
Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

-- identify duplicate rows
select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;


-- Use above results on the following SQL to delete duplicates
delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and  column_name = '&COLUMN_NAME'
and rownum=1;


-- Use following SQL to delete obsoleted rows
delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
  (
   select hc.table_name, hc.column_name
   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
   where hc.table_name  ='&TABLE_NAME'
   and hc.table_name= tc.table_name (+)
   and hc.column_name = tc.column_name (+)
   and tc.column_name is null
  );