This entry continues with the setup configured in parts one and two. I stated that ideally you should perform “Upload Snapshots Now” on the primary database before performing switch-over to prevent remove the possibility of missing snapshots in AWRW. We will see what happens if you don’t, either because you forgot, or it was not possible due to fail-over, and review some options to resolve the situation.
Do your own due diligence before updating any internal AWR data, be prepared to accept the consequences if you break things.
Primary database is currently DGA, I generate a couple of snapshots and perform a manual extract (note during the normal course of operations, this extract runs independently of the transfer and load components, so it is possible for there to be snapshots which have been extracted, but not transferred to AWRW host). The extract metadata table shows one extraction with status 2, the data has been extracted but has not yet been transferred to AWRW.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> exec dbsnmp.mgmt_caw_extract.run_extract;
PL/SQL procedure successfully completed.
SQL> select extract_id, begin_snap_id, end_snap_id, status from dbsnmp.caw_extract_metadata order by extract_id;
EXTRACT_ID BEGIN_SNAP_ID END_SNAP_ID STATUS
---------- ------------- ----------- ----------
1 1 10 5
22 11 13 5
41 14 15 5
61 16 18 5
81 19 24 2
I perform switch-over so that DGB is primary. Because the extract metadata references a file on host dga, the transfer task on dga will fail (as the database is in standby mode), and the one on dgb will also fail because the extract file is actually on dga. As no additional extracts have taken place, this ‘orphan’ extract record can be deleted (backup the table first if you are playing it safe). The next extract on DGB will re-extract the missing snapshots.
SQL> delete from dbsnmp.caw_extract_metadata where extract_id = 81; 1 row deleted. SQL> commit; Commit complete.
Sure enough, when I run “Upload Snapshots Now” against DGB, the job succeeds, and reviewing the load metadata table reveals that the snapshots initially extracted from DGA have been re-extracted successfully from DGB (along with some other snapshots that have since taken place).
SQL> select dump_id, target_name, begin_snap_id, end_snap_id, status from dbsnmp.caw_load_metadata order by dump_id; DUMP_ID TARGET_NAM BEGIN_SNAP_ID END_SNAP_ID STATUS ---------- ---------- ------------- ----------- ---------- 4 DGA 1 10 4 5 DGB 11 13 4 6 DGB 14 15 4 7 DGA 16 18 4 8 DGB 19 28 4
However if you don’t do this in time, subsequent snapshots may get extracted, transferred and loaded successfully creating a gap in the snapshots held in AWRW. To reproduce this situation, I create some more snapshots and kick of the extract process.
Notice this time that the run_extract doesn’t actually do anything (in the output we can see that as it has run recently so it doesn’t actually do anything). This is part of the design of the ETL process to have minimal impact on the source database. I override this behavior by calling the procedure run_job_now.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> set serveroutput on size unlimited
SQL> exec dbsnmp.mgmt_caw_extract.run_extract;
Starting CAW Extract...
Fetched CAW properties.
Retrying most recent failure...
No recent failure to retry.
Preparing for new Extract...
Upload interval is 24 hr.
Time elapsed since last successful extract: 0 hr.
Not enough time elapsed. Quitting.
No non-recoverable extracts to retry.
SQL> exec dbsnmp.mgmt_caw_extract.run_job_now;
run_job_now completed.
PL/SQL procedure successfully completed.
SQL> select extract_id, begin_snap_id, end_snap_id, status from dbsnmp.caw_extract_metadata order by extract_id;
EXTRACT_ID BEGIN_SNAP_ID END_SNAP_ID STATUS
---------- ------------- ----------- ----------
1 1 10 5
22 11 13 5
41 14 15 5
61 16 18 5
101 19 28 5
121 29 30 2
6 rows selected.
I then switch-back (primary reverts to DGA), create a couple more snapshots and perform “Upload Snapshots Now”. The process completes successfully, however checking the load metadata reveals that some snapshots have indeed been missed.
SQL> select target_name, dbid, begin_snap_id, end_snap_id from dbsnmp.caw_load_metadata order by dump_id; TARGET_NAM DBID BEGIN_SNAP_ID END_SNAP_ID ---------- ---------- ------------- ----------- DGA 1852644603 1 10 DGB 1852644603 11 13 DGB 1852644603 14 15 DGA 1852644603 16 18 DGB 1852644603 19 28 DGA 1852644603 31 32 6 rows selected.
Checking the extract metadata in reveals the status of this extract is 5 (transferred successfully). (I don’t know why; the load metadata showed that it has not been received into AWRW).
SQL> select extract_id, BEGIN_SNAP_ID, END_SNAP_ID, STATUS from dbsnmp.caw_extract_metadata order by extract_id;
EXTRACT_ID BEGIN_SNAP_ID END_SNAP_ID STATUS
---------- ------------- ----------- ----------
1 1 10 5
22 11 13 5
41 14 15 5
61 16 18 5
101 19 28 5
121 29 30 5
141 31 32 5
7 rows selected.
To resolve this situation, I update the extract metadata record to error status, to force these snapshots to be are re-extracted.
SQL> update dbsnmp.caw_extract_metadata set status = 3, e_retry_count = 0 where extract_id = 121; 1 row updated. SQL> commit; Commit complete.
After running Upload Snapshots Now, reviewing the extract metadata reveals that the missing snapshots have been re-extracted (the record has been updated to status 5).
SQL> select extract_id, BEGIN_SNAP_ID, END_SNAP_ID, STATUS from dbsnmp.caw_extract_metadata order by extract_id;
EXTRACT_ID BEGIN_SNAP_ID END_SNAP_ID STATUS
---------- ------------- ----------- ----------
1 1 10 5
22 11 13 5
41 14 15 5
61 16 18 5
101 19 28 5
121 29 30 5
141 31 32 5
7 rows selected.
Reviewing load metadata reveals that the missing snapshots have finally been loaded successfully into AWRW, the gap has been eliminated.
SQL> select target_name, dbid, begin_snap_id, end_snap_id from dbsnmp.caw_load_metadata order by dump_id; TARGET_NAM DBID BEGIN_SNAP_ID END_SNAP_ID ---------- ---------- ------------- ----------- DGA 1852644603 1 10 DGB 1852644603 11 13 DGB 1852644603 14 15 DGA 1852644603 16 18 DGB 1852644603 19 28 DGA 1852644603 31 32 DGA 1852644603 29 30 7 rows selected.
In summary even though out of the box, AWR Warehouse does not support data-guard configuration, with a little bit of hacking it is possible to get it to work reasonably well.
Here’s hoping that in a later release these issues will have been worked out, and it will work out of the box.