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.
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.
I was observing high DFS lock handle wait during Siebel CRM performance testing and i found below SQL very useful to understand the root cause of Watis.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/product/grid/11.2.0
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/product/grid/11.2.0/crs/install/crsconfig_params CRS-4046: Invalid Oracle Clusterware configuration. CRS-4000: Command Create failed, or completed with errors. Failure initializing entries in /var/opt/oracle/scls_scr/host1 /u01/app/product/grid/11.2.0/perl/bin/perl -I/u01/app/product/grid/11.2.0/perl/lib -I/u01/app/product/grid/11.2.0/crs/install /u01/app/product/grid/11.2.0/crs/install/rootcrs.pl execution failed
DBNEWID: Release 11.2.0.2.0 - Production on Sun Mar 20 22:17:55 2011
Keyword Description (Default)
----------------------------------------------------
TARGET Username/Password (NONE)
DBNAME New database name (NONE)
LOGFILE Output Log (NONE)
REVERT Revert failed change NO
SETNAME Set a new database name only NO
APPEND Append to output log NO
HELP Displays these messages NO
To Change Database ID/NAME :
1) Database must be mounted exclusively, if run command in other status you will get the following error
NID-00120: Database should be mounted exclusively
If you running RAC , you need to stop all the nodes and set CLUSTER_DATABASE=FALSE ,before restarting the database instance in mount state.
Oracle 11g has many feature which allow DBA's manage there database more esily and quickly. One of the feature which made DBA's life much easy is build standby database without backup i.e. building from Primary/Active Database.
This feature comes with following benifits.
* No Need to backup
* NO need to sheep backup to Standby server,
* No hessle of Mounting backup to Standby server as nfs or anything like that.
Building standby database is done using RMAN command DUPLICATE which we used before to duplicate database.
Step by Step.
1. Create pfile from spfile on the primay database.
2. scp pfile to new server.
3. edit pfile
4. Create spfile
5. startup nomount;
6. Create listener and Tnsnames.
7. Start standby database using RMAN duplicate database $ rman
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Feb 10 22:18:23 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET SYS/password@DB_DG_SITEB
connected to target database: DBPRD (DBID=39856714)
Deploying Database Vault Administrator to the Database Console OC4J Container
To manually deploy Database Vault Administrator to the Database Console OC4J container:
Stop Oracle Database Console.
UNIX: Go to the $ORACLE_HOME/bin directory and run the following command:
./emctl stop dbconsole
Windows: In the Administrative Services, select the Services utility, and then right-click the OracleDBConsolesid service. Select Stop from the menu.
Create a backup copy and then open the $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_service_name/config/server.xml file.
Add the following line before the element:
On Windows systems, replace $ORACLE_HOME with the absolute path to your Oracle Database home.
Create a backup copy and then open the $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_service_name/config/http-web-site.xml file.
Add the following line before the element:
Restart Oracle Database Console.
UNIX: Go to the $ORACLE_HOME/bin directory and run the following command:
./emctl start dbconsole
Windows: In the Administrative Services, select the Services utility, and then right-click the OracleDBConsolesid service. Select Start from the menu.
After you complete these steps, you can start Oracle Database Vault Administrator by using the following URL:
https://hostname:port/dva
For example:
https://myserver:1158/dva
If you are unsure of the port number, open the ORACLE_HOME/host_sid/sysman/config/emd.properties file and search for REPOSITORY_URL. In most cases, the host name and port number are the same as Oracle Enterprise Manager Database Control.
