Prepare phase failed ORA-00904: "TABTYPE": invalid identifier

[satish]

Hello Erman,

We are on R12.2.5 and database version 19.17
OS RHEL 7.9

Prepare phase failed while running autoconfig.
From the log,we could see below error for all the below scripts.

ORA-00904: "TABTYPE": invalid identifier
ORA-06512: at "APPS.ADX_PRF_PKG", line 81
ORA-06512: at line 6

Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/txkappsprf.sql
Error encountered while running afadmprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/afwebprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/amscmprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/amswebprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/wshadmprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/clnadmprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/oksfrmprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/csfadmprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/cncmprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/csiadmprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/cseadmprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/eamadmprf.sql
Error encountered while running /u02/SUPPDBINST/fs1/inst/apps/SUPPDB_erpsupport/admin/install/fteadmprf.sql
[applsupp@erpsupport ~]$


We tried below action plan,but didnt work

1. Run adadmin–>Recreate Grants and Synonyms
2. Compile Apps Schema

Any help please?

Thank You,
SG

[ErmanArslansOracleBlog]

What is written in line 81 of APPS.ADX_PRF_PKG?
Does TABTYPE exist in your DB?
What is the story of this environment? Any big changes before running this failing autoconfig?

[satish]

Hi erman,

Line 81: select tname into tablename from tab where tname ='ADX_PRE_AUTOCONFIG' and tabtype in ('TABLE', 'SYNONYM');
       


Table not existing in test environment ADX_PRE_AUTOCONFIG.
In prod,it is there.
Can we use export and import it from prod.How can we recreate it.pls suggest.

Thanks,
SG

[ErmanArslansOracleBlog]

The issue is not related with "ADX_PRE_AUTOCONFIG"..  Table ADX_PRE_AUTOCONFIG will be created when the autoconfig is run..
So it is not that.. The cause is the tabtype..
The problem is related with the "tab" table.. IT is a dictionary table, and probably, tabtype column is not present in the definition of that table.

What columns do that "tab" table have in this problematic env?

What columns do you see when you run select * from tab (with apps user)

"Tab" should be a dictionary view.. I suspect there is a missing grant issue here..

Also compare the following;

*APPS privileges between your problematic and problem-free environments
*dictionary table "tab" definition between your problematic and problem-free environments

These comparisons and check will tell you something..

[satish]

Hi Erman,

Looks like there is no table TAB in test instance where we are facing the issue.
Can you please suggest what can be done in this case?

TEST:
======

SQL> desc tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNO                                                NUMBER(38)
 NAME                                               CHAR(20)
 CITY                                               CHAR(10)
 EMP                                                CHAR(3)


SQL> sho user
USER is "APPS"
SQL>

SQL> select dbms_metadata.get_ddl('TABLE','TAB') from dual;

DBMS_METADATA.GET_DDL('TABLE','TAB')
--------------------------------------------------------------------------------

  CREATE TABLE "APPS"."TAB"
   (    "SNO" NUMBER(*,0),
        "NAME" CHAR(20),
        "CITY" CHAR(10),
        "EMP" CHAR(3)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "APPS_TS_TX_DATA"


OBJECT_NAME                    OWNER                          OBJECT_TYPE
------------------------------ ------------------------------ -----------------------
TAB                            SYS                            VIEW
TAB                            PUBLIC                         SYNONYM
TAB                            SYSTEM                         SYNONYM
TAB                            APPS                           TABLE

SQL>




PROD:
======

SQL> desc tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TNAME                                     NOT NULL VARCHAR2(128)
 TABTYPE                                            VARCHAR2(13)
 CLUSTERID                                          NUMBER

SQL> select dbms_metadata.get_ddl('TABLE','TAB') from dual;
ERROR:
ORA-31603: object "TAB" of type TABLE not found in schema "APPS"
ORA-06512: at "SYS.DBMS_METADATA", line 6731
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2588
ORA-06512: at "SYS.DBMS_METADATA", line 2827
ORA-06512: at "SYS.DBMS_METADATA", line 3608
ORA-06512: at "SYS.DBMS_METADATA", line 5008
ORA-06512: at "SYS.DBMS_METADATA", line 5336
ORA-06512: at "SYS.DBMS_METADATA", line 6702
ORA-06512: at "SYS.DBMS_METADATA", line 9734
ORA-06512: at line 1

no rows selected


OBJECT_NAME                    OWNER                          OBJECT_TYPE
------------------------------ ------------------------------ -----------------------
TAB                            SYS                            VIEW
TAB                            PUBLIC                         SYNONYM
TAB                            SYSTEM                         SYNONYM

SQL>


Thank You

[ErmanArslansOracleBlog]

TAB is owned by SYS.
You need to concentrate on that.
You issue is related with the access... A synonym may be missing.

When you use "desc tab" command on TEST, you see a different "tab", but on PROD you see the correct "tab".. So probably grants and synonyms are missing on the problematic TEST env.

You already mentioned that you recreated grants and synonyms from adadmin.
But, the issue still seems highly correlated with the synonym issue. check the synonyms using dba_synonyms, compare it with PROD...

* an extra synonym (a custom one) can also create this issue.. You run "desc tab" on TEST, as you see .. And you got a table there. What is it? Do you have an extra synonym mapped to a table name "tab" on TEST?

I think you got this.. Lets see what you can do with that..