Oracle Dataguard Conceptual View
Hi, This post is going to describe the Oracle Data Guard concepts and followed by the steps to implement the Physical Standby.
Before going to this, let us dig what is dataguard and its background.
Oracle Data Guard ensures high availability, data protection, and disaster recovery technique for the mission-critical database. DG switchover to standby database if production DB becomes unavailable of planned /unplanned outage by minimizing the downtime. It also Improves performance by offloading backup and reporting operations to standby DB.
DG configurations
Typically model is one production database(aka primary instance) and one or more standby instances connected by oraclenet in different geolocation.
DG can be managed by SQL/DGMGRL/EM interfaces.
Primary database – standalone / RAC database – transmit and apply redo data to the standby
Standby database – standalone / RAC database - a backup copy of primary. It can create up to 30 standby DBS.
- Physical standby database
a physically identical copy of the primary database/database structures&schema&indexes is identical to primary on a block-for-block basis. and keep synchronized with the primary database, through Redo Apply
It applies the redo when it can be active and it is open for read-only using active dataguard option
This method is mainly used for data protection and reporting
- Logical standby database
It contains the same logical information as the production database, although the physical organization and structure of the data can be different.
This synchronized with the primary database through SQL apply
SQL statements transferred from primary to standby and executed on standby.
It can be used for data protection, reporting, and database upgrades.
Although the logical database is opened in read-write mode, target tables are available for read-only operations.
- Snapshot standby database
It is a fully updatable standby database.
The Redo data does not apply until the snapshot standby is converted back into a physical standby database
first, discard any local updates made to the snapshot standby database.
It is used for a temporary updatable snapshot of a physical standby database.
The time needed to recover from a primary database failure is directly proportional to the amount of redo data that needs to be applied.
DG Services
- Redo Transport Service
It Controls the automated transfer of redo data from primary to standby. and manage the archive log gaps. Automatically detects missing/corrupted archive logs & replace it from primary/other standby.
- Apply Services
Apply redo data on standby automatically. It also allows read-only access to the data.
Physical standby – redo apply. Read-only access to the data.
Logical standby – SQL applies. Read/write transactions
- Role Transitions
This ensures no data loss when switchover activity occurs.
Failover can happen when primary unavailable.
It can be done by using SQL statements/ DGMGRL/OEM.
DG Broker
- A distributed management framework that automates the creation, maintenance, and monitoring of Data Guard configurations that contain RAC/standalone – primary/standby.
- Performs Redo service/apply service.
- Simplify switchover/failover tasks by using a single command
- Enable fast-start failover to failover with no need for DBA intervention.
Protection Modes
Maximum availability
Transactions do not commit until all redo data been written to the online redo log and to the standby redo log on at least one synchronized standby database.
If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance Data loss happens in double faults. (primary failure after failure of standby).
Maximum performance(default)
Minimal impact on primary database performance allowing transactions to commit to the online log also written to one or more standby databases asynchronously. Performance is unaffected by delays in writing redo data to the standby database(s).
Maximum protection
It ensures that no data loss will occur if the primary database fails.
Redo data must be written to both the online redo log and to the standby redo log on at least one synchronized standby database before the transaction commits.
The primary database will shut down, rather than continue processing transactions if it cannot write its redo stream to at least one synchronized standby database.
DG & Complementary technologies
RAC
RAC and Data Guard together provide the benefits of both system-level (RAC- such as node failures and instance crashes,) site-level (DG - disasters and data corruption), and data-level protections.
Flashback database
*Fast recovery from logical data corruption and user errors.
*Eliminates the need to restore a backup and roll forward changes up to the time.
*Rollback an Oracle database to a previous point-in-time, without restoring datafiles.
*Avoids the need to completely re-create the original primary database after a failover. The failed primary database can be flashed back to a point in time before the failover and converted to be a standby database for the new primary database.
*Eliminates the need to restore a backup and roll forward changes up to the time.
*Rollback an Oracle database to a previous point-in-time, without restoring datafiles.
*Avoids the need to completely re-create the original primary database after a failover. The failed primary database can be flashed back to a point in time before the failover and converted to be a standby database for the new primary database.
RMAN
- The DUPLICATE command to create a standby database from backups of your primary database
- Reduce the production load by taking backups of the standby database. Can take backup even in redo apply.
- Automatically deleting the archived redo log files used for input after performing a backup.
HW &SW requirements
- May have different architecture, operating systems or oracle binaries. (32/64)
- But the same release should be there in both primary and standby.
- Standard edition will not support it. Supports only Enterprise edition.
- COMPATIBLE database initialization parameter must be set to the same value on all databases. when using a logical standby database, which can have a higher COMPATIBLE setting than the primary database.
- the primary database must run in ARCHIVELOG mode.
- Primary can be single/rac &standby can be single/rac and mix of physical, logical & snapshot.
- primary database and standby database must have its own control file.
- If primary & standby in the same system, then standby archival directories should be a different location. Otherwise, the standby will overwrite the primary.
- Turn on FORCE LOGGING at the primary database before creating the standby.
- User account to manage primary & standby instances should have sysdba privilege.
- If ASM & OMF method is followed in primary, the same setup should be there in standby.
Create a Physical Standby database
Preparation:
- Enable force logging
Alter database force logging;
Select force_logging from v$database;
can take a considerable amount of time to complete, because it waits for all unlogged direct-write I/O to finish. Record all the changes except temp tables & segments changes.
- Configure redo transport authentication
Oracle net services and up-to-date remote login password file.
Orapwd file=orapwprim password=oracle entries=5 force=y
Create a PWD file in prim and transfer it to stand. Sys pwd must be identical on both sites.
Select * from v$pwfile_users;
Start the listener on both sites and do tnsping with each other.
- Configure the primary database to receive redo data
Optionally, configure primary DB to receive redo, helps to make it standby quickly.
- Set primary database initialization parameters
Spfile is mandatory to manage dg efficiently.
Example Primary Database: Primary Role Initialization Parameters
DB_NAME=prim #same in both prim & stand
DB_UNIQUE_NAME=prim #unique for each db, and role changes not affect this
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stand)' #specify all dbs in DG, tells the participants in DG
CONTROL_FILES='/arch1/prim/control1.ctl', '/arch2/prim/control2.ctl'
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/prim/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim' #archive the redo to specified loc. If loc not mentioned, use db_recovery_file_dest
LOG_ARCHIVE_DEST_2='SERVICE=stand ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stand' #valid for prim role. Dest transmits redo data to stand loc.
LOG_ARCHIVE_DEST_STATE_1=ENABLE # enable redo transport service
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE # only for this db to authenticate
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc #format thread, sequenceno, resetlogs id
FAL_SERVER=stand # service name of request db. In prim db, mention stand name
DB_FILE_NAME_CONVERT='stand','prim' # prim to stand file convert
LOG_FILE_NAME_CONVERT=
'/arch1/stand/','/arch1/prim/','/arch2/stand/','/arch2/prim/'
STANDBY_FILE_MANAGEMENT=AUTO # when dbfiles added/droped changes made auto in stand
There are still a lot of additional parameters for the configuration of the data guard. I will list out a few of them with its usage very precisely. Please refer to the complete list of parameters in this link.
Log writer does archival. Archiver transfer the redo to standby. Remote File Server receives the redo and hands over to standby. And will be written to standby specified in location. Sync will be used in max protection/availability. Affirm- log writer writes in prim & standby and gets confirm from standby then commit on the primary.
Please continue to read this post for implementing the Standby configurations.