As I’m playing with the AWR Warehouse in EM13c, I noticed a few changes that may send up a red flag and wanted to assist in removing those. As many know, the AWR Warehouse is very dependent upon EM Jobs…even more so now that we’ve moved to EM13c. The EM Job System has gone through quite an overhaul, so let’s go through how this may impact what you’re common to seeing when adding a database to the AWR Warehouse.
After adding a database to the AWR Warehouse in EM13c, you’ll note within just a few minutes, the database will come up in the console as not uploading snapshots, which can alarm those that monitor this feature.
Now in the previous, EM12c, this was a sign that something was missing in the configuration, not enough space for the AWR snapshot dump files in the $AGENT_HOME directory or preferred credentials not set up. The quickest way to troubleshoot it would be to highlight the database in question and click on Actions –> Upload Snapshots Now. This results in a job that runs from beginning to finish, skipping all DBMS local jobs and having the logging be performed via the EM Job System, making it easy to monitor the progress.
Now the latter is still in place. The jobs still log the process and perform all steps via an EM Job, but there is something that I’ll show you as we go through these steps in traditional troubleshooting.
Let’s say we did run the manual “Upload Snapshots Now”. We would then proceed to Job Activity to view the job and the status of each step:
We can quickly notice our CAW, (Centralized AWR Warehouse) job for the Run ETL Now, or CAW_RUN_ETL_NOW and we’ll click on it to view it’s progress, as it’s actively running.
We can see in the left hand screen that the extract from the source database is executing and in the right, we can now see the log. This is a more efficient view than the previous EM12c job screen that required a click on tabs and a refresh of the complete screen to process an updated view.
We can highlight each step to see Elapsed Time for the process and see what timestamps are stated for the start and end time. The completion graph shows us the amount of time resourced to each step in the process, too.
And we can see that the job has completed successfully, otherwise any step failure would show a red “X” and we can dig into the job details or download the log if we want. If you notice, up at the top right, you can switch to Classic View, but I’m not sure why anyone would want to. The new Job System is easier to work with, as well as monitor jobs with.
Now, how many of you noticed that there were TWO OTHER jobs back on the job list with a naming convention of CAW*? Yes, that’s the key to this challenge. Even though our job does speed up the AWR snapshot loads and then ensures that the data is available immediately, the real reason the databases were shown as “Without Recent Uploads” is that the jobs are scheduled for outside business hours for it’s initial load.
This means, that in the morning, the database shown as out of date would be caught up, but it’s not an immediate job system run as the job isn’t going to start until 5:53PM for the timezone of the EM environment.
So what you learned from this post is-
- A database added to the AWR Warehouse won’t load immediately and you should look at your jobs scheduled in the next 24 hrs with a CAW* naming convention to estimate time of availability.
- You can load the data immediately by clicking on the database, click on Actions and “Upload Snapshots Now.”
- The error handling in the EM Job System is still the best first step to troubleshoot any AWR Warehouse extract, transfer and load issues.
- The Enhancements to the Enterprise Manager 13c Job System ROCKS!