Monitoring and Researching Incidents and Errors

With the constant movement of data, problems can occur at various stages of the upload process. The dashboard reports on incidents and errors so that you can trace and resolve issues. Consistent with Enterprise Manager best practices, you can use the existing frameworks to manage incidents, configure notifications, and so forth.

The graphical region of the dashboard provides an at-a-glance view of issues encountered overall during warehouse upload activity. When an incident is raised, a View Incidents link appears; click it to link directly to Incident Manager where you can drill down to research the details. The Guided Resolution section provides links to view any warehouse errors reported and to return to the AWR Warehouse dashboard.

You can proactively identify the points of failure after the AWR warehouse is configured and the ETL process started running between the source database and warehouse, by running a number of tests on the AWR warehouse or a select set of sources and determine the health of the AWR warehouse configuration.

To view errors related to a specific database source, select the database row in the dashboard and click View Errors on the toolbar.

Errors typically break down by activity—AWR Warehouse load, source database extract, transfer. Some of the more common errors and suggested resolutions are described below.

AWR Warehouse Load Errors

When SYSAUX tablespace on the AWR Warehouse is insufficient to accommodate the import of AWR snapshots, the import fails with the following errors:

ORA-20115: Data Pump import encountered error:
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.SYS_IMPORT_FULL_27"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX
ORA-31626: job does not exist

Increase the SYSAUX tablespace to resolve the issue.

Load jobs use Data Pump to import AWR snapshot dumps. Data Pump jobs use a master table to track a job's progress. If an error occurs during import, the master table remains. As errors accumulate so too do master tables, eventually resulting in the following errors:

ORA-20115: Data Pump import encountered error:
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted
ORA-31634: job already exists

The solution is to drop the master tables from the previous failed jobs. Query the dba_datapump_jobs view for jobs in the NOT RUNNING state, as follows:

SELECT job_name
FROM dba_datapump_jobs
WHERE owner_name='SYS'
 AND operation='IMPORT'
 AND job_mode='FULL'
 AND job_name like 'SYS_IMPORT_%'
 AND state='NOT RUNNING';

Caution:

There may be cases where a job name the query returns is in use by an active Data Pump job. Ensure that there are no active Data Pump jobs to avoid mistakenly deleting their master tables.

The patch that enables the AWR Warehouse feature includes a fix for the legacy master tables, so you should not encounter this problem after applying the patch.

When an active Data Pump job exits ungracefully (it aborts or the database shuts down, for example), subsequent jobs fail with the following errors:

ORA-39097: Data Pump job encountered unexpected error -56935
ORA-39065: unexpected master process exception in DISPATCH
ORA-56935: existing datapump jobs are using a different version of time zone data file

To resolve the issue, check database properties for certain values on database startup and take appropriate action, as follows:

SELECT property_name, property_value
FROM sys.database_properties
WHERE property_name in ('DST_UPGRADE_STATE', 'DST_SECONDARY_TT_VERSION');

If the query returns 'DATAPUMP' and '<> 0', respectively, for the named properties, run the following:

exec dbms_dst.unload_secondary();

Note:

This Data Pump error can also happen during source database extraction.

When the source database time zone is ahead of the AWR Warehouse time zone, the following error occurs when importing the latest snapshot dumps:

ORA-20105: Unable to move AWR data to SYS
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 4773
ORA-13555: Message 13555 not found;  product=RDBMS; facility=ORA;
arguments: [end_time is greater than SYSDATE]

No action is necessary. The issue self-corrects when the SYSDATE of the AWR Warehouse advances past the date of the dump file.

Source Database Extract Errors

When SYSAUX tablespace on the source database is insufficient to accommodate the extract of AWR snapshots, the extract fails with the following errors:

ORA-20115: Data Pump export encountered error:
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.SYS_EXPORT_TABLE_08"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 2159
ORA-31626: job does not exist

Increase the SYSAUX tablespace to resolve the issue.

Extract jobs use Data Pump to export AWR snapshot dumps. Data Pump jobs use a master table to track a job's progress. If an error occurs during export, the master table remains. As errors accumulate so too do master tables, eventually resulting in the following errors:

ORA-20115: Data Pump import encountered error:
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted
ORA-31634: job already exists

The solution is to drop the master tables from the previous failed jobs. Query the dba_datapump_jobs view for jobs in the NOT RUNNING state, as follows:

SELECT job_name
FROM dba_datapump_jobs
WHERE owner_name='SYS'
 AND operation='EXPORT'
 AND job_mode='TABLE'
 AND job_name like 'SYS_EXPORT_%'
 AND state='NOT RUNNING';

Caution:

There may be cases where a job name the query returns is in use by an active Data Pump job. Ensure that there are no active Data Pump jobs to avoid mistakenly deleting their master tables.

The patch that enables the AWR Warehouse feature includes a fix for the legacy master tables, so you should not encounter this problem after applying the patch.

See also the Data Pump error under AWR Warehouse Load errors for another potential error during source database extraction.

Transfer Errors

If many dump files from a single source database are waiting to be loaded into the AWR Warehouse and their total size exceeds a threshold value (1 GB), the following error results:

The total size of dump files from the source database exceeds threshold value (size: xxx MB, threshold: xxx MB)

There appears to be an underlying problem loading dump files into the AWR Warehouse, resulting in a backlog of dump files. Check for and resolve any outstanding load errors to enable importing to resume.

If the total size of dump files from all source databases waiting to be loaded into the AWR Warehouse exceeds a threshold value (30 GB), the following error results:

The total size of dump files on AWR Warehouse exceeds threshold value (size: xxx MB, threshold: xxx MB)

Determine why there is a backlog of pending dump files in the load queue. Resolving the backlog issue will enable the load to resume.