Disaster Recovery Planning For Oracle DB

Planning DR is essential for any DBA because DBA will be first one to get under the gun if something goes wrong with database and DBA is not able to recover from it.

You have two options available ,

1. Plan the DR
2. Look for new job

I will focus on first one only as i love to get paid.

Oracle provides number of DR solutions and you need to select few of them as per the business requirement.

DR Solutions are ,

1. Export & Import
   
2. RMAN conventional backups.
3. RMAN database copy
4. Flashback Database
5. Data Guard
6. SAN Snapshot
7. RAC

• Export & Import

Export & Import takes the logical backup means it takes backup of the database objects created but database fails then you need to re-create the database and you need to import it once again.

Consider a case wherein you have 2TB database then export and import would take plenty of hours so its not a feasible solution for business.

• RMAN conventional backups

This is most preferred method of taking the backup as RMAN does not require any downtime.The problem with this solution is if you have large database and then you need to restore the database from backup in case of crash and it will long time ,so this is good solution for small databases but not for large database.

• RMAN database copy

This is awesome solution for large database like 2TB.In this solution you need to keep updated database copy in flash_recovery_area and you can perform the switch to copied database anytime whenever you want.It will take few minutes ( 10 Min ) only as it does not need to restore the datafiles from backup hence only recovery is required not restore.

The problem with this solution is that you need to have double of database size disk space as this will be the copy of database not compressed conventional backup.

One more problem is that datafile location will be changed once you perform the switch ,so your business may not allow it.

Once you perform switch then you must take backup as you will not have anymore valid copies.

• Flashback Database

This is very useful solution in case of point in time recovery.You can do point in time recovery with RMAN conventional backup as well but RMAN need to restore all the files from backup and the perform point in time recovery.

In case of Flash Database enabled , Database can be forced back to point in time very easily and fast with the help of flash back logs.

The problem with solution is again disk space.

• Data Guard

This is the widely used option across the globe.With data guard you can maintain the copy of database on local or remote machine.Both databases will be in sync automatically using log shipping.

Data Guard provides two kind of standby.

Physical Standby

Physical standby will be block by block copy of your source database but it will always be in recovery mode or in read only mode ,so if you need standby database which is also getting updated simultaneously then this is not correct choice for you.

Logical Standby

Logical Standby will be in read write mode ,so it can be updated simulataneously but logical standby has lots of restrictions on datatypes ,so you need to see whether your source’s datatypes are supported or not.

• SAN Snapshot

This is feature of SAN providers like NetApp , EMC etc.In this you can create snapshot of database where database is stored.This is very useful solution for large database because you can put database in begin backup mode and you can create the snapshot without having any impact on OS from which database is running.

• RAC ( Real Application Cluster )

Last but not the least , RAC – RAC is very useful in case of instance failure ,so if the instance running on first machine goes down then you can access database from second instance.

RAC requires good numbers of resources like Private Interconnect , SAN etc.

All in all i would recommend to have RAC with Data Guard implemented for true DR solution.

SDU & TDU Parameter SQL*NET ( Oracle )

SQL*Net Packet Sizes (SDU & TDU Parameters)

Packet Sizes

SDU is the Session Data Unit of the NS layer and regulates the size of the sent and read data to the NT layer. SDU values range from 512 to 32767 bytes with a default of 2048 bytes (this range depends on version). To minimize Oracle Net packet header overhead and message fragmentation, set the SDU size as a multiple of the MSS (Maximum Segment Size of the Network Protocol being used). Keep in mind that the old parameter, TDU, was the same as the Maximum Transmission Unit (MTU) of Ethernet we are now familiar with, yet with older Physical Layer protocols it was not called MTU so Oracle used the term of TDU.

To calculate the MSS:

MSS = MTU - TCP header size - IP header size

For TCP over Ethernet:

MTU (or TDU) - 1500 bytes for Ethernet
TCP - 20 bytes
IP - 20 bytes


For a connection to have an SDU size greater than 2048, the client and the server must specify a value the larger value for the SDU. The database will then select the lower of the 2 values.

SDU Configuration:

To configure the SDU to work, ensure the SDU values appear in all the relevant places. Here are some examples for the 2 main locations:

1.Client’s TNSNAMES.ORA: The parameters must appear in the DESCRIPTION clause.

TEST =
(DESCRIPTION =
(SDU=8192)
(TDU=8192) <- 8.0 TDU position
(ADDRESS =(PROTOCOL = TCP)(HOST = test.oa.com)(PORT = 1521))
(CONNECT_DATA = (SID = test)))


LISTENER.ORA: The parameters must appear in the SID_DESC clause.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 8192) <- Setting SDU to 8192 for this SID
(TDU = 8192) <- 8.0 TDU position
(SID_NAME = test)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0.1)))


2. From releases 9.0.1.5, 9.2.0.4 and 10.2.0.1 onwards, the default SDU size can now be changed for connections using SERVICE_NAME. ie Dynamic registration. Where as example 1, is for SID only, which is static registration

SQLNET.ORA

DEFAULT_SDU_SIZE = 8192

This now means SDU can be set on a per connection basis. Either by adding SDU parameter to local naming configuration file (TNSNAMES.ORA) and the listener configuration file (LISTENER.ORA), or by setting  SDU for all Oracle Net connections with the parameter DEFAULT_SDU_SIZE in the SQLNET.ORA filem on both client and server.

Shared Server Configuration

If using shared server processes, set the SDU size in the DISPATCHERS parameter as follows:

DISPATCHERS="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP))(SDU=8192))"