Extracting AWR data and loading it in another system

Extracting AWR data and loading it in another system

  As a DBAs I found myself trying to support customers remotely, repeatedly asking for another AWR or a special query. I found out about a cool feature that enable an extraction of the AWR, and later to load it locally. Extracting Login as SYS and at the SQL prompt, enter: SQL> @?/rdbms/admin/awrextr.sql Enter your database id or press <return> to use the current default. Enter value for dbid: Using 3342354369 for Database ID Specify the number of days for which you want to list snapshot Ids. Enter value for num_days: 1 After the list displays, you are prompted for the beginning and ending snapshot Id. Enter value for begin_snap: 2670 Enter value for end_snap: 2672 A list of directory objects is displayed, specify the directory object pointing to the directory where the export dump file will be stored: Enter value for directory_name: DATA_PUMP_DIR Specify the prefix for the default dump file name Using the dump file prefix: awrdat_2670_2672. An export log file and dump file will be created in the directory corresponding to the directory object you specified: /oracle/admin/db/dpdump/awrdat_2670_2672.log /oracle/admin/db/dpdump/awrdat_2670_2672.dmp Loading The dump file should be moved to the target database and located in one of the directories that already defined in the target database, SQL to find them: column dirpath format a50 heading ‘Directory Path’ column dirname format a30 heading ‘Directory Name’ SELECT directory_name dirname, directory_path dirpath FROM DBA_DIRECTORIES ORDER BY directory_name; Login as SYS and at the SQL prompt, enter: SQL> @?/rdbms/admin/awrload.sql A list of directory objects is displayed, specify the directory object pointing to the directory where the dump file is located: Enter value for directory_name: DATA_PUMP_DIR Specify the prefix for the dump file name without the .dmp suffix Enter value for file_name: awrdat_2670_2672 Enter temporary schema for this load or press <return> to use the default: AWR_STAGE Enter value for schema_name: AWR_STAGE Specify the default tablespace for the staging schema or press <return> to use the default: SYSAUX Enter value for default_tablespace: SYSAUX Specify the temporary tablespace for the staging schema: or press <return> to use the default: TEMP Enter value for temporary_tablespace: TEMP After the process completes, the AWR tables now have new data in them! You can query DBA_HIST_SNAPSHOT or any of the other DBA_HIST views (including DBA_HIST_ACTIVE_SESS_HISTORY). The standard AWR and ASH reports use the current database dbid, which won’t be the same as the dbid of the data you have just loaded. Luckily you can just use the ‘i’ versions of the reports for that. These scripts should be invoked when you want to pick a database other than the default. awrrpti.sql  – Workload Repository Report Instance awrgrpti.sql – Workload Repository RAC (Global) Report awrgdrpi.sql – RAC Version of Compare Period Report awrddrpi.sql – Workload Repository Compare Periods Report Yossi Nixon Chief Database Architect

read more »

AWR Generating & Setting

Oracle database is gathering statistics periodically (snapshots), these statistics can be used for analyzing database performance. These statistics are kept in the Automatic Workload Repository (AWR).

read more »
Using Standby as an Alternate for Far Sync (12c): Limitations and Considerations

Using Standby as an Alternate for Far Sync (12c): Limitations and Considerations

The recommended Oracle® Data Guard configuration is in Maximum Availability mode, when using Oracle Far Sync, which is located near the primary site: Primary Database > Far Sync Instance – Network input/output (I/O) is synchronous (Sync). Far Sync Instance > Standby Database – Network I/O is asynchronous (Async). Primary Database > Standby Database  – As an alternate (when Far Sync is not reachable), network I/O is asynchronous (Async).

read more »
Oracle 12c Release 2 New Features for Active Data Guard

Oracle 12c Release 2 New Features for Active Data Guard

At Oracle Open World 2016 I collected the main improvements and changes are going to be implemented in Oracle Database 12 Release 2: Data Guard Creation with dbca, supports automatic creation of Standby database and Far Sync instance not for RAC (at this point) with the command: dbca -silent -createDuplicateDB     -PrimartDBConnectionString myprymary.domin:1523/chicago.domain     -gdbName chicago.domain -sid boston     -initParams instance_name=boston -createAsStandby     -customScripts /tmp/test.sql Multi-Instance Redo Apply, Parallel, multi-instance recovery – when standby is RAC, all its instances will use the MRP0 process for applying Supports Diagnostic Pack on Active Data Guard AWR+ADDM Reports for Standby are kept in the Primary Supports Tuning Pack and SQL Plan Analyzer Fast Failover – Data Guard take over session draining (switchover to dallas_dr wait;) Read-only sessions connected to Active Data Guard Remain connected during the failover / switchover Become Read/Write after Active Data Guard becomes the primary Password file is managed and transported via the Redo mechanism Alternate prioritization – you will be able to group some destinations and give them a priority over some other destinations. This enables you to decide what will happen when the main destination is back (failback) Support In-Memory Column Store – redo data contains all the information needed for the standby to benefit from the In-Memory Column Store feature. Yossi Nixon Chief Database Architect  

read more »
Disaster Recovery Using Cellular Networks – Are These Six Misconceptions Keeping You from Zero Data Loss and Near-Zero RTO?

Disaster Recovery Using Cellular Networks – Are These Six Misconceptions Keeping You from Zero Data Loss and Near-Zero RTO?

Although disaster scenarios may be carefully addressed in the DRP, meeting the overall recovery time objective (RTO)—especially in asynchronous replication environments—is much less assured. This uncertainty is mainly due to data loss. When you enter the realm of data loss, figuring out what you have lost and then trying to reconcile the data can slow data recovery time to a crawl. Even seconds of data loss can entail hours of downtime as you work to recover data—costing your organization dearly in terms of lost revenue, reputation damage, labor costs, and much more.

read more »
Protect Your Company’s Most Critical Data – Without Breaking into a Sweat

Protect Your Company’s Most Critical Data – Without Breaking into a Sweat

You know the story:  99% (usually less) of your data is protected, and you got management to waive 100% protection by using the old “it’s just impossible” or “it’s too expensive” excuse. Even so, you never want to be the one who bears the news, “Sorry, we lost data, and it can’t be recovered…and by the way, it wasn’t just any 1% of data; it was the data that is most critical to our business.” Once disaster strikes, no one remembers those management waivers. They remember names. And guess whose name will be at the top of the list…

read more »
Disaster Recovery – From Practice to Theory?

Disaster Recovery – From Practice to Theory?

I was recently invited to give a talk at a Research institution about the products we are developing in Axxana. This forced me to step back and look at Disaster Recovery from a more “rigorous” point of view. Here are some key observations. First, let me start with a disclaimer. Our initial focus is on transactional environments where data loss translates very directly into lost revenue, reputation damage and in some extreme cases could lead to business closure. We do not focus on large scale systems that provide “eventual consistency,” but on classical environments that have focused on strong consistency models.

read more »
Using Flash-Based Storage Without Compromising Transactions (or Performance)

Using Flash-Based Storage Without Compromising Transactions (or Performance)

Everyone wants fast write access, high performance, a smaller footprint, green IT solutions, and the lowest possible total cost of ownership (TCO). For these reasons, many organizations use solid state drives (SSDs) or other forms of flash memory instead of the traditional hard disk drives (HDDs). Flash-based storage is designed to provide extremely high volumes of input/output (I/O) with very low latency. The typical use cases for flash-based storage are virtual desktop infrastructures (VDIs), which require high I/O speeds when booting up, and databases, which need fast read access.

read more »
Oracle White Paper Puts Axxana Phoenix System in the Spotlight

Oracle White Paper Puts Axxana Phoenix System in the Spotlight

Thanks to the Axxana Phoenix System and Oracle’s Far Sync technology, disaster recovery just got simpler, more reliable, and more affordable for Oracle customers. That’s the takeaway in a recent white paper by Oracle. The global technology leader is teaming with Axxana to offer a best-of-breed solution that combines Oracle Active Data Guard Far Sync with Axxana Phoenix System to ensure zero data loss in any data center topology.

read more »