Showing posts from January, 2017

Data Guard - Changing IP Addresses

When changing IP of a host we should update/recheck the following places: /etc/hosts or DNSlistener.ora tnsnames.oraDatabase parameters (local_listener, remote_listener)Data Guard configuration This document is also relevant when changing the IP address of the connection between the hosts, other than the original IP addresses we used during the installation. When installation is done using the hostname and not the IP address, most of the changes are not relevant except for /etc/hosts.
In this document, I will describe how to change the Data Guard Broker configuration. Dataguard configuration
Show the environment DGMGRL> show configuration

Configuration - dr

  Protection Mode: MaxAvailability
  pdb7 - Primary database
    fdb7 - Far sync instance
      sdb7 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 351 seconds ago) Show detailed information of the Primary
DGMGRL> show database pdb7 StaticConnect…

Extracting AWR data and loading it in another system

As a DBA, I find myself trying to support customers remotely, who frequently ask for another Automatic Workload Repository (AWR) or a special query. I’ve found a cool feature that enables customer's DBAs to extract the AWR and then load it locally.
Extracting 1. Login as SYS and at the SQL prompt, enter: SQL> @?/rdbms/admin/awrextr.sql 2. Enter your database id or press <Enter> to use the current default. Enter value for dbid:
Using 3342354369 for Database ID 3. Specify the number of days for which you want to list snapshot Ids. Enter value for num_days: 14 4. After the list displays, enter the beginning and ending snapshot ID. Enter value for begin_snap: 2670
Enter value for end_snap: 2672 5. 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 6. Specify the prefix for the default dump file name Using the dump file prefix: awrdat_2670_267…

AWR Generating & Setting

How to display AWR snapshot settingsOracle 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). 
Retention Interval: Amount of time to keep the snapshots.
Snapshot Interval: How often to automatically take snapshots.

SELECT 'Snapshot Interval' "Interval" ,      EXTRACT ( DAY FROM snap_interval ) days ,      EXTRACT ( HOUR FROM snap_interval ) hours ,      EXTRACT ( MINUTE FROM snap_interval ) minutes   FROM dba_hist_wr_control UNION ALL SELECT 'Retention Interval' "Interval" ,      EXTRACT ( DAY FROM retention ) days ,      EXTRACT ( HOUR FROM retention ) hours ,      EXTRACT ( MINUTE FROM retention ) minutes   FROM dba_hist_wr_control;
Adjust the AWR retention and snapshot intervals according to your needs.BEGIN DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings ( interval => 60,       -- minutes retention =&g…