Oracle Point, Oracle Life.

Recently, we have new version of oracle enterprise manager 10g release 4 installed in our environment. The process of installation is pretty smooth and the only problem is that we lost monitoring of one database in our production database server through OEM. It seems that there is improper configuration about the database entry somewhere.

In order to figure out this problem, we intuitively go find the configuration file in folder /sysman/emd directly under the folder where OEM agent is installed. We opened a file targets.xml there and found that we missed a entry for that missing database. Simply add it and then reload agent (we execute $emctl reload agent) and that database is popped up on OEM. That’s quite straightforward.

After quick glance of the OEM monitoring page, we found another problem indicating “Metric Collection Error” as following figure.

We doubt that the password for DBSNMP is not presented correctly in file targets.xml. Therefore, we log into Grid Control, click the Target tab->All Targets sub-tab. Select the missing database target’s radio button, then click the Configure button on the top left. Enter the DBSNMP password for the database. (Note: This may be the same as the SYS password if you chose that option when you created the database.)

After minutes, the error is gone away and all databases have been presented properly with availability status “Up”.

Popularity: 10% [?]

In order to perform bunch of data loading from third-party vendor, I was working against our data warehouse yesterday. Unfortunately, errors were experiencing throughout the loading.

For instance,we tried to create a temp table as following:

CREATE TABLE DMPQA_PRO_LIST_TEMP AS
SELECT SSR_PRO_INS_CODE, SSR_PRO_CODE, SSR_PRO_EFF_DATE, MAX(DECODE(SIGN(REGISTRATION_STATUS_DATE-TO_DATE(’31-MAR-2003′,’DD-MON-YYYY’))
,1,’Yes’, ‘No’)) IS_ACTIVITY_FTE
FROM DDEF2000.STUDENT_COURSE_REGISTRATIONS
WHERE NVL(CONTINUOUS_ENROLL_START_DATE, CSEC_START_DATE) > TO_DATE(’01-JAN-2000′, ‘DD-MON-YYYY’)
group by SSR_PRO_INS_CODE, SSR_PRO_CODE, SSR_PRO_EFF_DATE;

Running of this statement always hit error message Ora-01843: not a valid month

Also, when we ran the following statement we got error message: ORA-01855: AM/A.M. or PM/P.M. required

INSERT INTO STDRPT_DATE_PERIODS ( PERIOD, PERIOD_TYPE, START_DATE, END_DATE, BIRTH_DATE, PERIOD_ID )
VALUES ( ‘FY 2003-04′, ‘Fiscal Year’, TO_DATE(’04/01/2003 12:00:00 AM’, ‘MM/DD/YYYY HH12:MI:SS AM’), TO_DATE(’03/31/2004 12:00:00 AM’, ‘MM/DD/YYYY HH12:MI:SS AM’), TO_DATE(’09/30/2003 12:00:00 AM’, ‘MM/DD/YYYY HH12:MI:SS AM’), ‘A’ ) ;

We doubted that setting on windows registry of NLS_LANG is improperly set. We all know that Setting NLS_LANG correctly allows proper conversion from the client operating system code page to the database character set. When these settings are the same, Oracle assumes that the data being sent or received is encoded in the same character set as the database character set, so no validation or conversion is performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary.
Therefore, we made wide search on registry subkey NLS_LANG on home of HKEY_LOCAL_MACHINE. And, apply the update for each of Oralce homes (we have many oracle software installed on this workstation) to AMERICAN_AMERICA . After those changes, the above two statement can be executed successfully without error at all.

For more information about NLS_LANG with Oracle, please refer to Oracle Officail NLS_LANG FAQ.

Popularity: 7% [?]