He mentioned that specifying a shared location for the extract metadata did not work, and was waiting on a fix for bug number 21826657. I checked that this bug should be fixed in db plugins version 13.2.2 and that my test hosts dga and dgb are both running this version.
To test whether the fix was effective, I first updated the extract properties table to specify a location shared between both hosts, /home/oracle/awrw, generated a couple of snapshots (current primary is DGA) and then ran an extract process.
SQL> update dbsnmp.caw_extract_properties 2 set property_value = '/home/oracle/awrw' 3 where property_name = 'dump_dir_1'; 1 row updated. SQL> commit; Commit complete. 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_job_now; PL/SQL procedure successfully completed.
I double-check the files are extracted to /home/oracle/awrw as expected. You can see from the filename that this corresponds to snapshots with ids between 42 and 44.
[oracle@dgb awrw]$ ls -alrt /home/oracle/awrw total 12796 drwx------. 20 oracle oinstall 4096 Apr 8 12:21 .. drwxr-xr-x. 1 oracle oinstall 136 Apr 9 2017 . -rw-r-----. 1 oracle oinstall 13066240 Apr 9 2017 1_4CB89422459F0DF3E0530401A8C065D5_1852644603_42_44.dmp -rw-r--r--. 1 oracle oinstall 31656 Apr 9 2017 1_4CB89422459F0DF3E0530401A8C065D5_1852644603_42_44.log [oracle@dgb awrw]$
I then switch-over to DGB and then performed “Upload Snapshots Now”.
Reviewing the steps for the Transfer Task looks promising, below are the outputs of a couple of the transfer subtask; first of all createArchive:
conn_url: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dgb.localdomain)(PORT=1521)))(CONNECT_DATA=(SID=DG))), user: system, role: normal Connected to database Number of files ready for transfer: 1 Maximum total size of dump files: 5368709120 file: 1_4CB89422459F0DF3E0530401A8C065D5_1852644603_42_44.dmp size(bytes): 13066240 Number of files picked up for transfer: 1 Updated status of dump files, ready to transfer Creating archive file using [/app/oracle/agent/agent_220.127.116.11.0/bin/zip -v 4CA58C2B03FF5F03E0530201A8C0052E.zip 1_4CB89422459F0DF3E0530401A8C065D5_1852644603_42_44.dmp] adding: 1_4CB89422459F0DF3E0530401A8C065D5_1852644603_42_44.dmp . (in=13066240) (out=1116898) (deflated 91%) total bytes=13066240, compressed=1116898 -> 91% savings Created archive 4CA58C2B03FF5F03E0530201A8C0052E.zip Disconnected from database
Extracting dump files from /app/oracle/agent/agent_inst/awr_t/4CA58C2B03FF5F03E0530201A8C0052E.zip Extracted 1_4CB89422459F0DF3E0530401A8C065D5_1852644603_42_44.dmp extract complete Deleted archive 4CA58C2B03FF5F03E0530201A8C0052E.zip conn_url: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))(CONNECT_DATA=(SID=OMR))), user: sys, role: sysdba Connected to database dumpfile: 1_4CB89422459F0DF3E0530401A8C065D5_1852644603_42_44.dmp, emid: 1, dbid: 1852644603, bid: 42, eid: 44, size: 13066240 source target: DGB, type: oracle_database Added dump files to the import queue Disconnected from database
The output of the loadAWR task reveals that 3 snapshots have been successfully loaded to AWRW:
conn_url: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))(CONNECT_DATA=(SID=OMR))), user: sys, role: sysdba Connected to database Loaded 3 snapshots in the AWR Warehouse. Disconnected from database
Reviewing load metadata you can see that the snapshots that were extracted from DGA have been successfully loaded from DGB.
SQL> select dump_id, target_name, begin_snap_id, end_snap_id, status from dbsnmp.caw_load_metadata order by dump_id; SQL> select begin_snap_id, end_snap_id, target_name, status from dbsnmp.caw_load_metadata order by dump_id; 12 38 DGA 3 39 41 DGB 3 42 44 DGB 3 SQL>
So it seems that it is now OK to update the location that the dumpfiles are extracted to. If this is a shared location accessible by both hosts in dataguard configuration, then as we have proved, any data extracted from one host, can subsequently be transferred and loaded from the another host without requiring manual intervention.
This may be of use with AWRW in a RAC configuration (something I haven’t yet investigated).