AWR Warehouse and Data Guard (Part Three)

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s