Tables are missing in SAP ME WIP Synonyms

[former_member206397]

Hi All,

We have two different ME environment, QA and Production. Suddenly we found error in Production Order download in MEINT (in Production environment)and error is saying that table or view does not exist. After analysis I found that no table is created under the Synonyms folder in Production SAP ME WIP DB. But in quality system we have all those tables under Synonyms of SAP ME WIP. Currently we are running with 6.1.4.9 (QA and production both are in same version).

Now I need to create those table within the Synonyms folder in Production SAP ME WIP. Can anybody tell me how I can proceed for the solution.

Thanks in Advance

Chandan

[Former Member]

Hi Chandan,

You can try the following steps:

1)Create a DB link.For e.g:

             CREATE DATABASE LINK dblinkname

             CONNECT TO userid IDENTIFIED BY password

             USING '(DESCRIPTION=

(ADDRESS=

             (PROTOCOL=TCP)

             (HOST=ipaddress)

             (PORT=port no))

             (CONNECT_DATA=

(SID=Oracle SID)))'

2)Once  the db link is created you can create  synonyms for the tables which you want to access:

create synonym synonym_name for  remoteDB_sid.TABLENAME@DBLINK NAME

Let me know whether it works.

Thanks & Regards.

Prasanta

[Former Member]

Prasanta

Whilst this is a valid Oracle solution to the problem, it's not compliant with the way SAP ME works.

If you re-execute the CTC, which is required for some SAP ME upgrades, the CTC will drop the existing database link, and recreate it with the syntax contained in the CTC wizard itself, which uses the hostname only to identify the destination.

So, this will then create an invalid DB Link once more for Chandan. It's best if he get the tnsnames sorted out.

[alexander_teslyuk]

Hi Chandan,

Just to add to what Torsten suggested: re-running CTC wizard tasks is safe, so if you ever have an error against DB configuration it is worth to update CTC component and re-run it.

Br, Alex.

[former_member206397]

Hi Alex,

Thanks for your reply. One important thing is that we are facing issue for production system and the same system is used for daily production process. If I run the CTC wizard for DB installation then will it not be risky from production data perspective. What is your suggestion here? Will we go for CTC wizard run or any manual process to recreate those tables?

Thanks in advance

Chandan

[Former Member]

Chandan

It is safe to re-execute the CTC even on a live environment. The CTC script will only re-create objects (except the DB link which it drops and re-creates). Likely however, you will find it's the DB link that gets created incorrectly, and you will need to fix this manually.

As the WIP user execute this SQL:

select * from all_db_links;

If the host name to ODS looks like 'ODS.' (with the trailing period) then it is wrong and CTC will only re-create it incorrectly like this each time you run it. You need to drop and re-create this db_link manually after running CTC.

You should consider updating to a later 6.1.4 patch level as this was fixed in a later release.

[former_member206397]

Hi Alex,

I checked the CTC Log and I found that all entries are failed. The failed took place at the beginning of synonyms creation part. Please find below the part.

DROP TABLE VM$_TMP_ODS_TABLES

STATUS: PASSED

QUERY:

CREATE TABLE VM$_TMP_ODS_TABLES AS SELECT TABLE_NAME, sysdate DATE_LAST_RUN

                                                   FROM  USER_TABLE@ODS

                                                   WHERE TABLE_NAME LIKE 'AL_%'

STATUS: FAILED

MESSAGE: ORA-12154: TNS:could not resolve the connect identifier specified

This is the end of script execution. After that no scripts are executed for 'AL_%', 'AR_%', 'ODS_%'. But in quality system I checked the same CTC log and all the 'AL_%', 'AR_%', 'ODS_%' scripts are successful.

I am planning to run only those synonyms scripts manually in DB. what do you think about it?

Thanks in Advance

Chandan

[Former Member]

Chandan

See my post above. Creating the synonyms by manually executing the scripts still won't work because your database link is invalid. Fix that first, then run these scripts.

[former_member206397]

Hi Stuart,

I have executed the query (select * from all_db_links) as you suggested and it is returning the Host name which is correct. It is not returning name like "ODS". So, Can I execute those script manually?

Thanks in Advance

Chandan

[sergiy_katerinich]

Hi,

SAP Note 1917586 describes the procedure of manual re-creation of WIP synonyms.

If you have ODS symomnys for BObj Reporting, then you should come up with similar appoarch yourselves.

Regards,

Sergiy

[former_member206397]

Hi Sergiy,

Thank you so much for providing the SAP Note.

I have checked the note and here I have a question.

In my production system I have WIP and ODS in same IP address but two different instances. Two different SID for WIP and ODS. Now when I am running the query to create a ODS DB Link in WIP then I need to pass ODS User, ODS Password and ODS Alias. For ODS Alias what I have seen is that it is the SID for ODS (That I have seen in my Quality system, In QA system everything was successful because WIP and ODS was in same DB with different schema).

Now if I pass the ODS SID as ODS Alias then ODS DB link will be created successfully but at the time of synonyms creation how the WIP will communicate with ODS through DB link without host or IP address. I think that's why  the error occurred (MESSAGE: ORA-12154: TNS:could not resolve the connect identifier specified) by CTC.

Could you please help me to get clear about that point.

Thank you so much,

Chandan

[Former Member]

Chandan

You should get your Oracle DBA to address that issue for you. The WIP instance needs to have a tnsnames entry for the ODS instance on the same server. That is why the DB link fails to create.

[former_member206397]

Hi Stuart,

Thank you so much for your suggestion. I am discussing with DBA person for TNSNAMES entry as one of value for NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

Thanks again

Chandan

[former_member206397]

Hi Stuart,

Thank you so much for your suggestion. I am discussing with DBA person for TNSNAMES entry as one of value for NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

Thanks again

Chandan

[Former Member]

Hi Chandan,

I had the same error when trying to install ME 15. Here is the related discussion:

Link

So you have to execute the wizard like written in the installation documentation.

For me the error was solved by installing SP1.

Best regards,

Torsten