Friday, February 10, 2017

Far Sync Best Practice Recommendations (for Oracle 12.1)

Hi,
This post is a continuation of my post, Using Standby as an Alternate for Far Sync (12c): Limitations and Considerations. It is for database administrators who want to set up Far Sync for the first time.
Creating a Far Sync instance requires a number of manual steps and there are many parameters to consider or set. I recently ran numerous scenarios using Oracle™ Active Data Guard with a Far Sync instance. Our goals were to:
• Check Far Sync functionality and reliability
• Validate that Far Sync has a low footprint under heavy load



After running a large number of benchmark scenarios, where we changed one parameter at a time, here are my recommendations for using Active Data Guard with Far Sync:


  1. The primary database, the standby database, and the Far Sync instance should all be using the same software version and patch level; if not, upgrade the database(s) to the latest version with the latest patch sets.


  2. Consider changing the following database initialization parameters to FULL:
  3. DB_BLOCK_CHECKING=FULL
    This parameter specifies whether Oracle performs block checking for database blocks. Changing to FULL can prevent memory and data corruption, but typically adds 1 – 10% overhead.
    This parameter determines whether the database calculates a checksum. Changing to FULL allows Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems; it adds 4 – 5% overhead.
    Changing this parameter to FULL enables detection of lost writes.


  4. The standby database and the Far Sync instance standby redo logs (SRLs) should have the same number of redo log groups as the primary database plus one for each thread.


  5. All online and SRL files should be the same size on any primary and standby databases and the Far Sync instance.


  6. Primary and standby databases and the Far Sync instance should have SRL files that have the same number of threads as redo log files.


  7. For Oracle RDBMS Version 12.1, set the parameter “_redo_transport_stall_time”=60 in all instances within the configuration; doing so enables the best return to synchronization after a node outage with an ALTERNATE Far Sync configuration (Source: Oracle Active Data Guard Far Sync Zero Data Loss at Any Distance, an Oracle white paper).


  8. Consider not to multiplex SRLs.


  9. Using the RMAN deletion policy, delete logs after ensuring that they were applied on all standby destinations; use the following configuration:
  10. CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;


  11. MAA tests determined that a 300MB SGA with CPU_COUNT=1 on Linux was sufficient for Far Sync.


  12. Practice is important for ensuring a positive outcome in the following scenarios:

    1. Failover
    2. Switchover
    3. Resolving Gaps in Data Guard
    4. Recreating Standby or Far Sync
Please feel free to comment here or contact me by mail: ynixon@gmail.com or Twitter @YossiNixon

Tuesday, January 31, 2017

DataGuard - Changing IPs

When changing IP of a host we should update/recheck the following places:
  1. /etc/hosts or DNS
  2. listener.ora
  3. tnsnames.ora
  4. Database parameters (local_listener, remote_listener)
  5. Dataguard configuration

This document relevant also when changing the IP of the connection between the hosts - new IP, other than the IPs we used in the Installation.
When installation is done using hostname and not IP – 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
  Members:
  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 StaticConnectIdentifier

  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.15.1.60)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb7_DGMGRL)(INSTANCE_NAME=pdb7)(SERVER=DEDICATED)))'
Since there is an old IP in the configuration we must change it
DGMGRL> edit database pdb7 set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.15.2.112)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb7_DGMGRL)(INSTANCE_NAME=pdb7)(SERVER=DEDICATED)))';

Property "staticconnectidentifier" updated
I recommend to do the same change with hostname instead of the IP
DGMGRL> edit database pdb7 set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=e15lfs2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb7_DGMGRL)(INSTANCE_NAME=pdb7)(SERVER=DEDICATED)))';

Property "staticconnectidentifier" updated
Show detailed information of the Standby
DGMGRL> show database sdb7 StaticConnectIdentifier

  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=e15rfs2.axxana.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sdb7_DGMGRL)(INSTANCE_NAME=sdb7)(SERVER=DEDICATED)))'
We see that this configuration has hostname and not IP, so we don't need to change anything in the Standby
DGMGRL> show far_sync fdb7 StaticConnectIdentifier

  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.15.1.63)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=fdb7_DGMGRL)(INSTANCE_NAME=fdb7)(SERVER=DEDICATED)))';
We can't change Far Sync configuration, it is not supported yet
DGMGRL> edit far_sync fdb7 set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.15.2.111)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=fdb7_DGMGRL)(INSTANCE_NAME=fdb7)(SERVER=DEDICATED)))';

Error: ORA-16831: operation not allowed on this member

Failed.
We have to remove Far Sync configuration from the broker and add it again LL

Monday, January 23, 2017

Extracting AWR data and loading it in another system

As a DBA 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

1. Login as SYS and at the SQL prompt, enter:
SQL> @?/rdbms/admin/awrextr.sql
2. Enter your database id or press 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: 1
4. 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
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_2672.
7. 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;
1. Login as SYS and at the SQL prompt, enter:
SQL> @?/rdbms/admin/awrload.sql
2. 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
3. Specify the prefix for the dump file name without the .dmp suffix
Enter value for file_name: awrdat_2670_2672
4. Enter temporary schema for this load or press to use the default: AWR_STAGE
Enter value for schema_name: AWR_STAGE
5. Specify the default tablespace for the staging schema or press to use the default: SYSAUX
Enter value for default_tablespace: SYSAUX
Specify the temporary tablespace for the staging schema: or press 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

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;