Load Data Warehouse job Full Load is stalling after few minutes

[Yadagiri Kastala]

Hi All,

In our Dev Environment, we found that Load Data Warehouse job Full Load is stalling after few minutes and failing after 10 hours stating the job didn't finish on time.

i.e., it populates few tables and stops proceeding further - Below is the log sample..



We see this issue for last 3 days, before that it was running fine.



Thanks
Giri

[vtleogal]

Are you on SQL by chance? there are several of us (clients) working with Broadcom on this issue.  15.8 patch 1 is  better but still having issues. Please log a case to support and they can probably connect you to the team working on it. 


------------------------------
Lynn
------------------------------

[Yadagiri Kastala]

Thanks Lynn for the response.

We are on Oracle VM and Version is 15.7.1 Patch01

Thanks
Giri

[Suman Pramanik]

Hi Giri

Did you see any anomalies at the AWR report. You said Oracle VM as database. Can you confirm are your running oracle on Virtual Machine?

------------------------------
Thanks & Regards
Suman Pramanik
Sr. Principal Support Engineer | Customer Success & Support, Enterprise Software Division
Broadcom
------------------------------

[Yadagiri Kastala]

Hi Suman,

I will check with DBA to get  AWR report. Yes, we are running oracle on Virtual Machine.

Thanks
Giri​

[Suman Pramanik]

Hi Giri

We have time and again seen multiple issue while running database running on virtual machine specially outside what is mentioned as supported. If you are not seeing the same problem in production then it will be best to tune at the DB level looking at various database reports.

------------------------------
Thanks & Regards
Suman Pramanik
Sr. Principal Support Engineer | Customer Success & Support, Enterprise Software Division
Broadcom
------------------------------

[Yadagiri Kastala]

Thanks Suman,

Currently the Production Database is still on Physical Server. Infrastructure team plan is migrating the databases to virtual machines, as part of that we have migrated few environments and found this issue in a DEV Environment.

We could not capture the error in the logs and unable to find out where it has hung. When I checked with DBA, they also could not find any pending statements on the database too.

Is there any additional logging can be configured for this job.

Thanks
Giri

[Suman Pramanik]

Hi Giri

Can you check if your database is running on any of the platform mentioned in the page http://www.oracle.com/technetwork/database/virtualizationmatrix-172995.html 

If not then i would not risk moving to VM as we have seen multiple performance issues running data base on virtual box.  Can you get the AWR report for the period when the job was running

------------------------------
Thanks & Regards
Suman Pramanik
Sr. Principal Support Engineer | Customer Success & Support, Enterprise Software Division
Broadcom
------------------------------

[Yadagiri Kastala]

Hi Suman,

I got the AWR report. In that the platform was shown as "Linux x86-64"
Please let me what else should I check in that file.

Thanks
Giri

[Nika Hadzhikidi]

Hi Giri

Is Data Warehouse on the same server as Clarity database? 
Can the DBA check for any blocking sessions on the Data Warehouse database and see if they should be removed?
Check the table DWH_CFG_AUDIT and see which table it's stuck on. From the log it appears to be DWH_INV_BOARD, make sure there is no locks on this table.

------------------------------
Nika Hadzhikidi
Sr Principal Support Engineer
Broadcom
------------------------------

[Yadagiri Kastala]

Hi Nika,

It is a Oracle RAC environment. Both NIKU and PPM_DWH are on same database and DB server is on a VM it had two nodes.

Last week I did ask about locks and DBS confirmed there were no locks and no query was running related to this table

I will check about blocking sessions tomorrow

Thanks
Giri

[Nika Hadzhikidi]

Sounds good Giri. You can also try recreating the DWH database from blank template, sometimes I have found a table can get corrupted and cause the job to get stuck.

------------------------------
Nika Hadzhikidi
Sr Principal Support Engineer
Broadcom
------------------------------

[Yadagiri Kastala]

Hi Nika,

DBA could not find any blocking sessions.

We had this job was working fine till 5/8 and later it failed and each time it failed at different table.

I guess there could be some thing on DB VM server is causing..but as we don't see error.

I wonder what questions we need to ask DB VM server team?
Do we have any option to report more detail error info?
( we did kettle job as Full and duh.event to All )

Thanks
Giri

[Nika Hadzhikidi]

Hi Giri
Can you ask the DBA Team to rebuild DWH and see if this helps? If it's the same server there shouldn't normally be connection issues.

------------------------------
Nika Hadzhikidi
Sr Principal Support Engineer
Broadcom
------------------------------

[Yadagiri Kastala]

Hi Nika,

Today we have rebuild DWH, but it did not resolve the problem..

below is the today's error - We could not answer questions from DBA, can we get any additional info for them...
a. what this job is waiting for
b. what is the error

[Nika Hadzhikidi]

Hi Giri -

Are they able to see any blocking sessions or anything specific in the AWR report?

Any other environments on this server that work?

------------------------------
Nika Hadzhikidi
Sr Principal Support Engineer
Broadcom
------------------------------

[Yadagiri Kastala]

Hi Nika,

DBA doesn't see any blocking sessions. I could not find any specific error in AWR report.

It is little strange, we have 4 environments which are running DB on Virtual machine (two environments worked till 5/08 and two environments worked till 5/22 from respective Monday on wards these stopped i.e., 5/11 2 envs stopped 5/25 2 envs stopped) - Currently all  4 environments are not working.

Thanks
Giri

[Yadagiri Kastala]

Thanks All for your suggestions..

Load Data Warehouse job started running successfully after DB VM Server team has made few changes to the VM Server yesterday.

They said they have resolved I/O Disk saturation issue.

I am still not sure why DBA could not see any error on the database logs if there was an I/O issue on VM Server

Regards
Giri

[Nika Hadzhikidi]

Great, thanks a lot for the update Giri!

------------------------------
Nika Hadzhikidi
Sr Principal Support Engineer
Broadcom
------------------------------