Using SAP NetWeaver with Oracle Database In-Memory

[former_member204618]

Hi All,

I am looking at Oracle Database In-Memory as an option for the business and have run into a problem with the IM Advisor.

I have run the IM Advisor perfectly fine on our DEV environment which is running 12c but wanted to know the results from our Production environment by way of exporting and importing AWR Statistics as per the Whitepaper

However when I have imported the AWR stats from production and passed in the start and end snapshot ids from production into dbms_inmemory_advisor.add_hist_statistics but it complained :-

ORA-20001: No such DBA_HIST_SNAPSHOT.SNAP_ID for this database instance:

I've checked DBA_HIST_SNAPSHOT and sure enough those snapshot ids exist but with the DBID from production, which I am assuming is what the error is all about.

Now I am no Oracle DBA but I can follow instructions.  Have I misread these instructions or is there something else that needs to be done?

Thanks

Craig

[stefan_koehler]

Hi Craig,

unfortunately i have not the actual version of dbms_inmemory_advisor at hand, but here is the corresponding code snippet of my version related to your error.

BEGIN
      SELECT DHS.BEGIN_INTERVAL_TIME INTO ADD_HIST_STATISTICS.CAPTURE_WINDOW_START
      FROM   DBA_HIST_SNAPSHOT DHS, IMADVISOR_DBID_INSTANCE_NUM IDIN
      WHERE     TID                 = IDIN.TASK_ID
        AND     DHS.DBID            = IDIN.DBID
        AND     DHS.INSTANCE_NUMBER = IDIN.INSTANCE_NUMBER
        AND     DHS.SNAP_ID         = ADD_HIST_STATISTICS.START_SNAP_ID;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR (-20001, 'No such DBA_HIST_SNAPSHOT.SNAP_ID for this database instance: START_SNAP_ID='||START_SNAP_ID);
      WHEN OTHERS THEN
        RAISE;
END;

BEGIN
      SELECT DHS.END_INTERVAL_TIME INTO ADD_HIST_STATISTICS.CAPTURE_WINDOW_END
      FROM   DBA_HIST_SNAPSHOT DHS, IMADVISOR_DBID_INSTANCE_NUM IDIN
      WHERE     TID                 = IDIN.TASK_ID
        AND     DHS.DBID            = IDIN.DBID
        AND     DHS.INSTANCE_NUMBER = IDIN.INSTANCE_NUMBER
        AND     DHS.SNAP_ID         = ADD_HIST_STATISTICS.END_SNAP_ID;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR (-20001, 'No such DBA_HIST_SNAPSHOT.SNAP_ID for this database instance: END_SNAP_ID='||END_SNAP_ID);
      WHEN OTHERS THEN
        RAISE;
END;

Just cross-check this in your own environment.

Regards

Stefan

[former_member204618]

Hi Stefan,

How would I check this?

Thanks

Craig

[stefan_koehler]

Hi Craig,

look into the corresponding tables (DBA_HIST_SNAPSHOT and IMADVISOR_DBID_INSTANCE_NUM) that Oracle uses for checking. Afterwards you can see where the issue is located.

Regards

Stefan

[former_member204618]

Hi Stefan,

As expected the snapshot id's are there from production but the table IMADVISOR_DBID_INSTANCE_NUM only contains the DBID for the current database not one from production.

So what to do?

Thanks

Craig

[stefan_koehler]

Hi Craig,

as i said i can not troubleshoot this issue as i don't have the SAP scripts and current version of  dbms_inmemory_advisor at hand.

Is table IMADVISOR_DBID_INSTANCE_NUM maybe maintained by script imadvisor_awr_augment_import.sql? Or maybe by PL/SQL calls that are called by this script?

Just look through the post steps after importing the AWR data.

Regards

Stefan

[former_member204618]

Hi Stefan,

I should point out that I am trying to use AWR stats from an Oracle 10g database so we can make a decision before we upgrade to 12c.

Does this have any bearing as I obviously couldn't run script imadvisor_awr_augment_export.sql on the source as it doesn't exist there!

Thanks

Craig