BR0301E SQL error -980 at location stats_tab_validate-2, SQL

[Former Member]

Good morning,

Please could you give me an advice about following error in DB verify check?

BR0301E SQL error -980 at location stats_tab_validate-2, SQL statement:

'ANALYZE TABLE "SYS"."WRH$_SGASTAT" VALIDATE STRUCTURE CASCADE ONLINE'

ORA-00980: synonym translation is no longer valid

Oracle 11.2.0.2

SAP EHP 1 for SAP NetWeaver 7.1

When I went through Note 650796 - Composite Note: ORA-00980

There are 5 reasons mentioned:

1. SAPUSER
2. <sap_user>.USER_IND_COLUMNS, SYSTEM.USER_IND_COLUMNS
3. <sap_user>.USER_INDEXES
4. Enterprise Portal / J2EE: SDBAH, SDBAD
5. Other synonyms

So mine errors are related just to point 5 as I understand.

Cannot find anything by this command

SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME = '<synonym>';

Not sure if I am putting something wrong, but even I would find something, there is written:

"You can determine the owner and name of the referenced object from the TABLE_OWNER and TABLE_NAME columns. You must then determine why this object is incorrect"

Is there necessary use catalog.sql, or catproc.sql or something like this to rid off these messages?

Really thank you for any comment.

[Former Member · Accepted solution]

The error message 'ORA-00980' seems to be misleading;

actually "SYS"."WRH$_SGASTAT" shouldn't be a synonym, but a partitioned table. There may be problems with another synonym though.

Just to make sure, try this in sqlplus:

select owner, object_type from all_objects

where object_name = 'WRH$_SGASTAT';

A SAP notes search with keyword stats_tab_validate-2 might give you an idea.

Besides that, what is your operating system, and as which Oracle user did you try?

You could type the following command from sqlplus, and see if you get more helpful error messages. And please try as various Oracle users (/ as sysdba, system, …)

ANALYZE TABLE "SYS"."WRH$_SGASTAT" VALIDATE STRUCTURE CASCADE ONLINE;

hope this helps

[Former Member · Accepted solution]

SQL> select owner, object_type from all_objects where object_name = 'WRH$_SGASTAT';

OWNER                          OBJECT_TYPE

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

SYS                            TABLE PARTITION

SYS                            TABLE PARTITION

SYS                            TABLE PARTITION

SYS                            TABLE PARTITION

SYS                            TABLE PARTITION

SYS                            TABLE PARTITION

SYS                            TABLE PARTITION

SYS                            TABLE PARTITION

SYS                            TABLE PARTITION

SYS                            TABLE PARTITION

SYS                            TABLE PARTITION

OWNER                          OBJECT_TYPE

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

SYS                            TABLE

12 rows selected.

Operation system is SunOS  5.10 Generic_142909-17 sun4u sparc SUNW,Netra-T12

Regarding stats_tab_validate-2

I see just three SAP notes

Earlier I tried to solve this problem by this note (It was asking it when I was checking DBACOCKPIT tcode

Note 706927 - Oracle DB-Mon: Installation script for ST04N/DBA Cockpit

After run create_11_2.sql

From synonyms errors it switched to errors described in this note  (one of what I found under stats_tab_validate-2)

Note 514178 - ORA-14508 when validating partitioned tables

I followed it and after all I was getting again same errors with ORA-00980

I was getting these errors when I runned it from BRTools (so user SYSTEM),

now I tried just manually run your query with sysdba:

SQL> ANALYZE TABLE "SYS"."WRH$_SGASTAT" VALIDATE STRUCTURE CASCADE ONLINE;

Table analyzed.

Do I need do it with all errors manually?

Or I need change privileges for SYSTEM somehow to let it run as usual from brtools?

[Former Member · Accepted solution]

Thanks,

I think it is a bit clearer now.

So you created table INVALID_ROWS as described in the notes?

It seems privileges have to be adjusted.

Please enter this in sqlplus:

select owner, object_type from all_objects

where object_name = 'INVALID_ROWS';

By the way, why are you executing brtools as user system?

According to https://service.sap.com/sap/support/notes/651812 '-u /' should be used.

And perhaps this might solve your problem ...

regards

[Former Member · Accepted solution]

SQL> select owner, object_type from all_objects where object_name = 'INVALID_ROWS';

OWNER                          OBJECT_TYPE

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

SYS                            TABLE

OPS$PIDADM                     SYNONYM

OPS$ORAPID                     SYNONYM

To be honest with SYSTEM user it is my guess due command line in log file

"/usr/sap/<SID>/SYS/exe/uc/sun_64/brconnect -p initPID.sap -l E -U -f stats -v cascade -t all -p 1"

There is just -U not -u /

and its then log automatically as SYSTEM user if I will try to run it manually.

Sorry for confusing if its normal behavior. This system was recently took over not used to this setting.

Currently running again DB verify to know if I rid off errors for synonyms. And update it when it finish.

[Former Member · Accepted solution]

Hello,

first, your guess is correct, as you can verify by typing brconnect -h

If there isn't a -u, then user system with default password will be used,

And imho this is a strong argument for not using user system. Why let everybody know that your system has default password?

And why default anyway?

Then, the query shows that user system can't access table INVALID_ROWS. The way you created it is not compatible with the way you are going to use it.

One solution could be creating the table again from scratch, after having thoroughly read the corresponding SAP note.

Or a quick workaround could be, in sqlplus / as sysdba:

create synonym system.invalid_rows for sys.invalid_rows;

hope this helps

PS:

And as already suggested, the best solution would be starting brconnect with option -u /

Message was edited by: Joe Bo.

[Former Member · Accepted solution]

Thank you Joe, everything was fixed by your workaround. Now I will change user for brtools.