Oracle 11g Dataguard best Practice

Summary of Oracle recommended best practice in Oracle Documentation.

Oracle Data Guard Configuration Best Practices


Use Redo Transport Services Best Practices:

Use SYNC + STANDBY REDO LOG FILE for Zero data loss
Use ASYNC + STANDBY REDO LOG FILE for minimal data loss
Optimize Network Performance


Use RMAN to build Standy

1) With backup availablility on Standby node

Duplicate database for STandby;

2) Without Backup Availablity on STandby node

DUPLICATE DATABASE FOR STANDBY FROM ACTIVE DATABASE;


uSE flashback for Avoid REbuild of Standby after Failover


LOGGING L
USE Force Logging for physical Standby

ALTER DATABASE FORCE LOGGING

USE Supplimental Logging for Logical Standby
ALTER DATABASE FORCE LOGGING;
> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;


Specify full log archive format :-
T for thread
S for sequence
R for resetlog id

LOG_ARCHIVE_FORMAT=arch_%T_%S_%R.arc , format might ignored when Flash recovery area is used.



always use VALID_FOR Attribute in Log shipping to avod any changes after switchover.


VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLES) DB_UNIQUE_NAME=SALES_stby'


* Always create STandby Redo log files

No of Standby redo log files will be based on formuala.


maximum logfile group +1 * maximum number of thread


for 5 node RAC with 5 groups each node you will need

6 group for each thraed x 5 = 30 Groups.




Set the LOG_ARCHIVE_MAX_PROCESSES Parameter
LOG_ARCHIVE_MAX_PROCESSES = sum(remote_destinations) + count(threads)



Set the Network Configuration and Highest Network Redo Rates

Properly Configure TCP Send / Receive Buffer Sizes:

To achieve high network throughput, especially for a high-latency, high-bandwidth network, the minimum recommended setting for the sizes of the TCP send and receive socket buffers is the bandwidth-delay product (BDP) of the network link between the primary and standby systems. Settings higher than the BDP may show incremental improvement

For example, on Linux, the parameters net.core.rmem_max and net.core.wmem_max limit the socket buffer size and must be set larger than RECV_BUF_SIZE and SEND_BUF_SIZE

Set the send and receive buffer sizes at either the value you calculated or 10 MB, whichever is larger. For example, if bandwidth is 622 Mbits and latency is 30 ms, then you would calculate the minimum size for the RECV_BUF_SIZE and SEND_BUF_SIZE parameters as follows: 622,000,000 / 8 x 0.030 = 2,332,500 bytes. Then, multiply the BDP 2,332,500 x 3 for a total of 6,997,500.

In this example, you would set the initialization parameters as follows:

RECV_BUF_SIZE=6,997,500

SEND_BUF_SIZE=6,997,500


Set TCP.NODELAY to YES



Determine When to Use Redo Transport Compression : only if required and sufficient CPU is available

COLUMN   type ON
FORMAT   a20

COLUMN   UNITS ON
FORMAT   a20

COLUMN   item ON
FORMAT   a30

COLUMN   TIMESTAMP ON
FORMAT   999999999999999999

COLUMN   COMMENTS ON
FORMAT   a20


select * from v$recovery_progress where item like '%Appl%' order by 1

START_TIM TYPE                 ITEM                           UNITS                     SOFAR      TOTAL TIMESTAMP COMMENTS
--------- -------------------- ------------------------------ -------------------- ---------- ---------- --------- --------------------
23-MAY-12 Media Recovery       Last Applied Redo              SCN+Time                      0          0 23-MAY-12 SCN: 66053893415
23-MAY-12 Media Recovery       Average Apply Rate             KB/sec                        7          7
23-MAY-12 Media Recovery       Apply Time per Log             Seconds                       3          3
23-MAY-12 Media Recovery       Active Apply Rate              KB/sec                       13         13
23-MAY-12 Media Recovery       Maximum Apply Rate             KB/sec                       13         13
24-MAY-12 Media Recovery       Last Applied Redo              SCN+Time                      0          0 28-MAY-12 SCN: 66078162035
24-MAY-12 Media Recovery       Average Apply Rate             KB/sec                      626        626
24-MAY-12 Media Recovery       Apply Time per Log             Seconds                      44         44
24-MAY-12 Media Recovery       Active Apply Rate              KB/sec                     2129       2129
24-MAY-12 Media Recovery       Maximum Apply Rate             KB/sec                    22888      22888
24-MAY-12 Media Recovery       Standby Apply Lag              Seconds                      11         11
24-MAY-12 Media Recovery       Redo Applied                   Megabytes                220939     220939
28-MAY-12 Media Recovery       Active Apply Rate              KB/sec                       70          0
28-MAY-12 Media Recovery       Apply Time per Log             Seconds                      56          0
28-MAY-12 Media Recovery       Average Apply Rate             KB/sec                      587          0
28-MAY-12 Media Recovery       Last Applied Redo              SCN+Time                      0          0 30-MAY-12 SCN: 66088268976
28-MAY-12 Media Recovery       Redo Applied                   Megabytes                 88572          0
28-MAY-12 Media Recovery       Maximum Apply Rate             KB/sec                    15961          0
28-MAY-12 Media Recovery       Standby Apply Lag              Seconds                       0          0





Use DB_BLOCK_CHECKING=OFF and Set DB_BLOCK_CHECKSUM=FULL ( if recovery rate meet the minimum requirement)


Set DB_CACHE_SIZE to a Value Greater than on the Primary Database


 Because media recovery does not require DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE or require a large SHARED_POOL_SIZE, the memory can be reallocated to the DB_CACHE_SIZE. but reducing the Cache size can also be helpful where cache is size big.