Oracle Adaptive Access Manager(OAAM) 11g R1 Upgrade issue

In OAAM 11g Upgraded environment you might see below error message frequently in <<Managed_Server>>.OUT file.

java.sql.SQLSyntaxErrorException: ORA-00904: "T1"."PATTERN_ID": invalid identifier"

In upgradded OAAM 11g database PATTERN_ID field missed in VT_WF_HOURS, VT_WF_DAYS, VT_WF_MONTHS and VT_WF_YEARS table.

Solution:

Environment: OAAM 11g 11.1.1.5.0 (Upgraded from OAAM 10g 10.1.4.5.0 BP07), Windows 2008 Server

Pre-Requesits: Stop all OAAM services.

Fix:

Login the OAAM Oracle Database.

Execute below SQL scripts.

alter table VT_WF_HOURS add (PATTERN_ID NUMBER(16,0));

DROP INDEX VT_WF_HOURS_IDX0;

CREATE INDEX VT_WF_HOURS_IDX0 ON VT_WF_YEARS(PATTERN_ID,ENTITY_DEF_ID,FPRINT_ID,OBJECT_ID,UPDATE_TIME) LOGGING TABLESPACE &&indx_tbs INITRANS 16 MAXTRANS 255 NOPARALLEL;

alter table VT_WF_DAYS add (PATTERN_ID NUMBER(16,0));

DROP INDEX VT_WF_DAYS_IDX0;

CREATE INDEX VT_WF_DAYS_IDX0 ON VT_WF_YEARS(PATTERN_ID,ENTITY_DEF_ID,FPRINT_ID,OBJECT_ID,UPDATE_TIME) LOGGING TABLESPACE &&indx_tbs INITRANS 16 MAXTRANS 255 NOPARALLEL;

alter table VT_WF_MONTHS add (PATTERN_ID NUMBER(16,0));

DROP INDEX VT_WF_MONTHS_IDX0;

CREATE INDEX VT_WF_MONTHS_IDX0 ON VT_WF_YEARS(PATTERN_ID,ENTITY_DEF_ID,FPRINT_ID,OBJECT_ID,UPDATE_TIME) LOGGING TABLESPACE &&indx_tbs INITRANS 16 MAXTRANS 255 NOPARALLEL;

alter table VT_WF_YEARS add (PATTERN_ID NUMBER(16,0));

DROP INDEX VT_WF_YEARS_IDX0;

CREATE INDEX VT_WF_YEARS_IDX0 ON VT_WF_YEARS(PATTERN_ID,ENTITY_DEF_ID,FPRINT_ID,OBJECT_ID,UPDATE_TIME) LOGGING TABLESPACE &&indx_tbs INITRANS 16 MAXTRANS 255 NOPARALLEL;

Now restart the services you shouldn't get the error log. It worked in my environment.

Cheers!!

Siva Pokuri.