What are Different types of standby database in oracle data guard
In oracle data guard configuration, you need to setup one or more additional databases beside the primary database. These additional databases are called standby database.
A standby database can be of three types.
1)Physical Standby Database: A physical standby database is an identical copy of the primary database. The disk structures are also identical with primary database. It is kept synchronized with the primary database by Redo Apply- which means the redo data is received from the primary database and then redo is applied to the physical standby database.
Note that as of Oracle Database 11g release 1 (11.1), a physical standby database can receive and apply redo while it is open for read-only access. You can use physical standby database for query and reporting purpose along with data protection.(which we know as ACTIVE DATA GUARD)
2)Logical Standby Database: A logical standby database is the same logical information of the primary database. The physical data structure need not to be same on the standby database. It is kept synchronized with the primary database by SQL Apply- which means the redo data is received from the primary database, transforms redo data into SQL statements and at last executes the SQL statements on the standby database.
You can use logical standby database for query and reporting purpose along with data protection. Also you have to facility to upgrade oracle database software and patch sets along with data protection with help of logical standby database.
3)Snapshot Standby Database: A snapshot standby database is a convertible copy of the physical standby database but the difference from the physical or logical standby database is, the redo data that it received does not apply into it. The redo is applied whenever it is converted back to the physical standby database. You can play with the snapshot standby database and while converting to physical standby database from snapshot standby database these local updates are discarded.
Note that in case of snapshot standby database, the time needed to perform a role transition is directly proportional to the amount of redo data that needs to be applied.
What is fal_client and fal_server parameter in data guard?
FAL Means – Fetch Archive log. FAL_CLIENT and FAL_SERVER parameters are used on standby database for archive gap resolution.
FAL_SERVER: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points to the Database from where the missing ArchiveLog(s) should be requested i.e the tns alias of the primary db.
FAL_CLIENT: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points from FAL_SERVER TO standby db. i.e the tns alias of the standby db.
fal_server = ‘primdb’
fal_client = ‘stdbydb’
What is active dataguard. Does it needs additional licensing??
Active dataguard means, the standby database is open with read only mode, when redo logs are getting applied in real time.
Below are the benefit of using active dataguard.
- Reporting queries can be offloaded to standby database.
- Physical block corruptions are repaired automatically either at primary or physical standby database.
- RMAN backups can be initiated from standby , instead of primary which will reduce cpu load from primary
NOTE – To use active dataguard, you need additional license from oracle
What is the difference between Recovery Point Objective(RPO) and Recovery Time Objective (RTO)?
A) Recovery Point Objective(RPO)
RPO concerns with data. It is the amount of data you are willing to lose when the failure occurs in your database system. Usually people define data loss in terms of time, so possible values can be 5 seconds of data loss, 2 hours of data loss etc.
Remember that each standby database has its own set of attributes and parameters. It means you can mix zero data loss standby databases with minimal data loss standby
databases in the same Data Guard configuration
If you have decided that you want to implement zero data loss strategy, then you should really focus on Networks and Data Loss
B) Recovery Time Objective (RTO)
RTO is defined as how fast you can get back up and running (whereas RPO is concerned with data loss)
So with your RPO strategy you lost say only about 6 seconds of data as you committed to your client but with RTO you need to formulate how fast clients can connect back to the database system after the data loss has occurred.
What is the process to apply a psu patch in dataguard setup.
- Make sure lag between primary and standby is zero.
- Cancel the recovery (MRP) on standby.
- Shutdown standby db and listener.
- Apply patch to binary using opatch apply command.
- Once patch applied to binary , startup the listener and standby in mount stage or OPEN(if active dataguard).
- Now shutdown primary db and listener.
- Apply patch to binary using opatch apply command.
- Once patch applied to binary , startup the listener and prim db in mount OPEN(if active dataguard).
- Start the MRP recovery process on standby .
What Are The Differences Between Physical/logical Standby Databases? How Would You Decide Which One Is Best Suited For Your Environment?
Physical standby DB: As the name, it is physically (datafiles, schema, other physical identity) same copy of the primary database. It synchronized with the primary database with Apply Redo to the standby DB.
Logical Standby DB: As the name logical information is the same as the production database, it may be physical structure can be different.
It synchronized with primary database though SQL Apply, Redo received from the primary database into SQL statements and then executing these SQL statements on the standby DB.
We can open “physical stand by DB to “read only” and make it available to the applications users (Only select is allowed during this period). we can not apply redo logs received from primary database at this time.
We do not see such issues with logical standby database. We can open the database in normal mode and make it available to the users. At the same time, we can apply archived logs received from primary database.
For OLTP large transaction database it is better to choose logical standby database.
What Is DG_CONFIG parameter ?
Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and each standby database in the Data Guard configuration.
The default value of this parameter enables the primary database to send redo data to remote destinations and enables standby databases to receive redo data.
What Is Physical Standby Database In Oracle Data Guard?
Oracle Standby database are divided into physical standby database or logical standby database based on standby database creation and redo log apply method. Physical standby database are created as exact copy i.e block by block copy of primary database. In physical standby database transactions happen in primary database are synchronized in standby database by using Redo Apply method by continuously applying redo data on standby database received from primary database.
Physical standby database can offload the backup activity and reporting activity from Primary database. Physical standby database can be opened for read-only transactions but redo apply won’t happen during that time. But from 11g onwards using Active Data Guard option (extra purchase) you can simultaneously open the physical standby database for read-only access and apply redo logs received from primary database.
What Is Logical Standby Database In Oracle Data Guard?
Oracle Standby database are divided into physical standby database or logical standby database based on standby database creation and redo log apply method. Logical standby database can be created similar to Physical standby database and later you can alter the structure of logical standby database. Logical standby database uses SQL Apply method to synchronize logical standby database with primary database.
This SQL apply technology converts the received redo logs to SQL statements and continuously apply those SQL statements on logical standby database to make standby database consistent with primary database. Main advantage of Logical standby database compare to physical standby database is you can use Logical standby database for reporting purpose during SQL apply i.e Logical standby database must be open during SQL apply.
Even though Logical standby database are opened for read/write mode, tables which are in synchronize with primary database are available for read-only operations like reporting, select queries and adding index on those tables and creating materialized views on those tables. Though Logical standby database has advantage on Physical standby database it has some restriction on data-types, types of DDL, types of DML and types of tables.
What is snapshot standby database?
Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database i. e
we can convert the physical standby database to snapshot standby for testing purpose. On that, we can do all types of testing (BOTH READ/WRITE) or can be used as a development database (which is an exact replication of production ). Once the testing is over we can again convert
the snapshot database to physical standby. Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.
What Is The Usage Of Db_file_name_convert Parameter In Oracle Data Guard Setup?
DB_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. DB_FILE_NAME_CONVERT parameter are used to update the location of data files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database data files location.
What Is The Usage Of Log_file_name_convert Parameter In Oracle Data Guard Setup?
LOG_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. LOG_FILE_NAME_CONVERT parameter are used to update the location of redo log files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database redo log file location.