Oracle Apps and Oracle Database: 03/03/11

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

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

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

---------------------------------------------------------------

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" 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

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

);

Oracle Apps and Oracle Database

Thursday, March 3, 2011

Manually Configure Enterprise Manager Database Control in 11.2.0.1

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

Followers

About Me