Tuesday, January 10, 2017

AWR Generating & Setting

How to display AWR snapshot settings

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). 

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 => 20160    -- minutes (14 days)
        );
END;
/

The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable statistics gathering by the AWR. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including the AWR, and is not recommended.

Here’s a list of AWR related main scripts from oracle 11g, 12c.
Located at $ORACLE_HOME/rdbms/admin

Generic

awrextr.sql  - Extracts the AWR data into a Data Pump export file.
awrload.sql  - Load the extracted AWR data
awrinfo.sql  - Output general AWR information

NON RAC

awrrpt.sql   - Basic AWR report (generates an HTML or text report that displays statistics for a range of snapshot Ids.)
awrddrpt.sql - Period diff on current instance (generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods)

RAC

awrgrpt.sql  - AWR Global Report (RAC)
awrgdrpt.sql - AWR Global Diff Report (RAC)
awrinfo.sql  - Script to output general AWR information

Generating an AWR report from the SQL*Plus command line:

1. Creating Snapshot
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
2. Generate AWR Reports
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:
SQL> @?/rdbms/admin/awrrpt.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Specify the number of days for which you want to list snapshot Ids.
Enter value for num_days: 2
After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.
Enter value for begin_snap: 95
Enter value for end_snap: 97
Next, accept the default report name or enter a report name. The default name is accepted in the following example:
Enter value for report_name:
Using the report name awrrpt_1_95_97
The workload repository report is generated.

Default location of output file (awrrpt_1_95_97.txt or awrrpt_1_95_97.html) is located at the directory you where before entered SQL*Plus

Generating an AWR Compare report (Non-RAC) from the SQL*Plus command line:

1. Generate AWR Reports
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/awrddrpt.sql
2. First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: html
3. Specify the number of days for which you want to list snapshot Ids for the First Pair
Enter value for num_days: 1
4. After the list displays, you are prompted for the beginning and ending snapshot Id for the First Pair workload repository report.
Enter value for begin_snap: 22665
Enter value for end_snap: 22666
5. Specify the number of days for which you want to list snapshot Ids for the Second Pair
Enter value for num_days2: 1
6. After the list displays, you are prompted for the beginning and ending snapshot Id for the Second Pair workload repository report.
Enter value for begin_snap2: 22667
Enter value for end_snap2: 22668
7. Next, accept the default report name or enter a report name. The default name is accepted in the following example:
The default report file name is awrdiff_1_22665_1_22667.html 
Report written to awrdiff_1_22665_1_22667.html

Thursday, November 17, 2016

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

Based on Oracle 12.1

The Recommended Data Guard configuration is in Maximum Availability mode, when using Far Sync which is located near the Primary:

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).

Using SQL

If you do not use Data Guard Broker (dgmgrl), use the parameters specified below.

·        Primary

On the Primary Database

Mandatory Parameters

Parameter
Value
Remarks
LOG_ARCHIVE_DEST_2

point to Far Sync service
Alternate
log_archive_dest_3
Switches to the standby destination, after the number of failures specified in the next parameter is reached.
max_failure
1
This value must be higher than 0 (infinite number of retries), but should not be too high, because the connection must fail after n attempts and fail over to the next destination, specified in the alternate parameter.
SYNC





LOG_ARCHIVE_DEST_STATE_2
enable

LOG_ARCHIVE_DEST_3

point to Standby service
Alternate
log_archive_dest_2
Establishes connection with the Far Sync host after the connection with log_dest_3 (standby) failed. Once Standby is up and available again, connection with the standby host is re-established.
max_failure
0
Establishes connection infinitely; never stops retrying.
ASYNC


LOG_ARCHIVE_DEST_STATE_3
alternate


Example:
ALTER SYSTEM SET log_archive_dest_2='service="[FARSYNC_INST]", SYNC  max_failure=1 db_unique_name="[FARSYNC_INST]" alternate=LOG_ARCHIVE_DEST_3 valid_for=(online_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=enable;
ALTER SYSTEM SET log_archive_dest_3='service="[STANDBY_INST]", SYNC max_failure=0 db_unique_name="[STANDBY_INST]" alternate=LOG_ARCHIVE_DEST_2 valid_for=(online_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=alternate;

·        On the Far Sync Instance

Mandatory parameters

Parameter
Value
Remarks
LOG_ARCHIVE_DEST_2

point to Standby service
max_failure
0
This value must be higher than 0 (infinite number of retries), but should not be too high, because the connection must fail after n attempts and fail over to the next destination, specified in the alternate parameter.
ASYNC


NOAFFIRM


LOG_ARCHIVE_DEST_STATE_2
enable

Example:
ALTER SYSTEM SET log_archive_dest_2='service="[STANDBY_INST]", ASYNC max_failure=0 db_unique_name="[STANDBY_INST]" valid_for=(standby_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=enable;
ALTER SYSTEM SET log_archive_dest_3='service="[STANDBY_INST]", SYNC max_failure=0 db_unique_name="[STANDBY_INST]" net_timeout=30 alternate=LOG_ARCHIVE_DEST_2 valid_for=(online_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=alternate;

·        On Standby Database

Mandatory parameters

Parameter
Value
Remarks
LOG_ARCHIVE_DEST_2

Point to Far Sync service only if you have local/near Far Sync. Otherwise, points to the original primary service.
Note:
Set this parameter only when using switchover.
Alternate
log_archive_dest_3
Switches to original primary destination after the number of failures specified in the next parameter is reached.
Note:
Set this only when using switchover and only if Far Sync is used as the service destination.
max_failure
1
This value must be higher than 0 (infinite number of retries), but should not be too high, because the connection must fail after n attempts and fail over to the next destination, specified in the alternate parameter.
SYNC





LOG_ARCHIVE_DEST_STATE_2
enable
Set this only when using switchover.
LOG_ARCHIVE_DEST_3

Points to the original Primary service.
Note:
Set this parameter only when using switchover and if Far Sync is used as service destination.
Alternate
log_archive_dest_2
Establishes connection with the Far Sync destination after the connection with the Primary service failed. Once the Primary service is up and available again, connection with the Primary host is re-established.
Note:
Set this parameter only when using switchover and if Far Sync is used as service destination.
max_failure
0
Establishes connection infinitely; never stops retrying.
ASYNC


LOG_ARCHIVE_DEST_STATE_3
alternate
Set this parameter only when using switchover.

Example:

ALTER SYSTEM SET log_archive_dest_2='service="[FARSYNC_INST]", SYNC max_failure=1 db_unique_name="[FARSYNC_INST]" alternate=LOG_ARCHIVE_DEST_3 valid_for=(online_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=enable;
ALTER SYSTEM SET log_archive_dest_3='service="[PRIMARY_INST]", SYNC max_failure=0 db_unique_name="[PRIMARY_INST]" alternate=LOG_ARCHIVE_DEST_2 valid_for=(online_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=alternate;

Using Broker

Must have parameters

Parameter
Value
Remark
Target Type
MaxFailure
0

Primary + Standby
MaxFailure
1

Far Sync
RedoRoutes
'([PRIMARY_INST] : [STANDBY_INST] ASYNC)([STANDBY_INST] : [PRIMARY_INST] ASYNC)'

Far Sync
RedoRoutes
'(LOCAL : [FARSYNC_INST] SYNC ALT =( [PRIMARY_INST] ASYNC FALLBACK))'

Standby
RedoRoutes
'(LOCAL : [FARSYNC_INST] SYNC ALT =( [STANDBY_INST] ASYNC FALLBACK))'

Primary


Example:
EDIT FAR_SYNC [FARSYNC_INST] SET PROPERTY RedoRoutes = '([PRIMARY_INST] : [STANDBY_INST] ASYNC)([STANDBY_INST] : [PRIMARY_INST] ASYNC)';

EDIT DATABASE [PRIMARY_INST] SET PROPERTY MaxFailure = 0;
EDIT FAR_SYNC [FARSYNC_INST] SET PROPERTY MaxFailure = 1;
EDIT DATABASE [STANDBY_INST] SET PROPERTY MaxFailure = 0;
EDIT DATABASE [STANDBY_INST] SET PROPERTY RedoRoutes = '(LOCAL : [FARSYNC_INST] SYNC ALT =( [PRIMARY_INST] ASYNC FALLBACK))';   

EDIT DATABASE [PRIMARY_INST] SET PROPERTY RedoRoutes = '(LOCAL : [FARSYNC_INST] SYNC ALT =( [STANDBY_INST] ASYNC FALLBACK))';

EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;