Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Oracle Dataguard

In Oracle 8i:
Oracle database Version 8i - Standby Environment
  • And also archive logs to transferred manual for Quick Recover  Process
  • The Standby database Was maintained IN Read-Only Option and allowed it to be used as a reporting database.
  • No Automation
  • For example, when we added a datafile or created a tablespace on the primary database, these changes were not being replicated to the standby database.
  • Database administrators had to take care of this maintenance on the standby database. 
  • No Role Transmission(PROD To STAND again STAND To PROD)
In Oracle 9i:
Oracle8i standby database was renamed to Oracle9i Data Guard. 
  • Oracle dataguard Broker(Automate the configuration, monitoring, and management of Oracle Data Guard)
  • Zero data loss on failover was guaranteed as a configuration option.
  • Database Administrator workload has been reduced(Datafiles  Transfer)
  • if there are missing archived logs on standby, which is called gap; Data Guard detects and transmits the missing logs to standby automatically.
  • Parallel recovery increased recovery performance on the standby database.
In Version 9 i Release 2:
1.Logical standby database 
2.Three data protection modes were ready to use: 
  1. Maximum Protection(SYNC AFFIRM)- primary db will be shutdown if  not log shipping to standby,
  2. Maximum Availability- first protection next Performance if network issues,
  3. Maximum Performance (ASYNC NOAFFIRM)- primary db will not be shutdown if  not log shipping to standby,
3.The Cascade standby database 
In Oracle 10g:
  • The Oracle 10g version again introduced important features of Data Guard
  • One of the most important features of 10 g was the Real-Time Apply. 
  • Flashback Technology Applied in Standby Database 
  • Rac Environment - Dataguard
  • In Oracle 10g ODG, if we open a primary database with resetlogs it was not required to re-create the standby database. Standby was able to recover through resetlogs.
In 10g Release 2 also introduced new features to Data Guard,
  1. Fast-start failover
  2. Guaranteed restore point 
In Oracle 11g :
  • Active Dataguard
  • Snapshot Dataguard
  • The physical standby databases for the rolling upgrades of database software was enabled, aka Transient Logical Standby.
  • RMAN fast incremental backup feature "Block Change Tracking" can be run on an Active Data Guard enabled standby database.
  • In Oracle 11g was Automatic Block Corruption Repair feature that was introduced with 11 g R2
Oracle Data Guard -Architecture:


Oracle Data Guard Background Process:
On the primary location, log transport services use the following processes:
  • Log writer process (LGWR) 
  • Archiver process (ARC) 
  • Fetch archive log (FAL) 
On the standby location, log transport services use the following processes:
  • Remote file server (RFS) 
  • Archiver process (ARC) 
On the standby location, log apply services use the following processes:
  • Managed recovery process (MRP) 
  • Logical standby process (LSP) 
On Both Side, the Data Guard broker uses the following processes:
  • Data Guard broker monitor Process(DMON)
MRP0 (Managed Standby Recovery Process) coordinates the read and apply process of redo in a physical standby database.

RFS (Remote File Server) is responsible for receiving the redo data, which is sent by the primary database to the standby database.


LNS  The Log Writer Network Service (LNS) reads the redo being flushed from the redo buffers by the LGWR and sends the redo over network to the standby database. The main purpose of the LNS process is to free up the LGWR process from performing the redo transport role.


LSP0 (Logical Standby Coordinator Process) coordinates the SQL Apply processes, which are the mining processes and apply processes.


LSP1 (Logical Standby Dictionary Build Process) is used on the logical standby databases when a switchover or failover is in action.


LSP2 (Logical Standby Set Guard Process) is used to operate Database Guard settings. Database Guard specifies which objects will be protected for modification in a logical standby database.


NSAn (Redo Transport NSA1 Process) is used on the primary database to ship redo data to the standby database when ASYNC mode is being used. There may be multiple NSA processes such as NSA1 and NSA2.


NSSn (Redo Transport NSA1Process ) is also used on the primary database to ship redo data to the standby database. However, only when the SYNC mode is being used.


DMON (Data Guard Broker Monitor Process) runs on every instance in a Data Guard broker configuration. It communicates with local database and DMON processes of the remote databases. The broker-related requests and the monitoring information are transferred on this communication channel...


FSFP (Data Guard broker fast-start failover pinger process) is used for the management of fast-start failover status.


DATAGUARD PROTECTION MODES:
Three parameters determine which protections mode should be used in the transmission of the redolog data to the Standby side.
  • AFFIRM: This tells that a transactions is commited, if it arrived at the Standby Redolog. NOAFFIRM would disable this behavior, which means transactions are commited immediately.
  • SYNC: This tells Redolog entries are concurrently written into the local Redolog files and transmitted to the Standby side. With ASYNC the local information is maintained first and just afterwards the Standby Redolog one.
  • NET_TIMEOUT: This parameter is only available with SYNC and AFFIRM and regulates after what time to switch to the NOAFFIRM mode.
MAXIMUM PERFORMENCE:
In the Maximum Protection mode a transaction is only confirmed as “Committed”, when the data has been written both local and into at least one Standby Redolog file. If the Standby Database or the network between the databases breaks down, transactions could not be performed any longer, the primary database shuts down automatically. Oracle recommends to only use Maximum Protection mode, if at least two Standby databases exist.
For the Maximum Protection mode the following parameters must be set for the Redo transport:
  • AFFIRM
  • SYNC
MAXIMUM  AVAILABLITY:
This mode is a compromise between data security and performance. Fist the Maximum Availability mode works just like the Maximum Protection mode. That means all transactions are transmitted simultaneously and the commit of the transaction is only sent, when the transaction is saved both local and on at least one Standby Redolog file. With the 12c Oracle feature “FastSync” the performance can be increased a bit by already confirming the transaction, when the data reached the Standby side, so when it is located in that memory.
Unlike the Maximum Protection mode, the primary database continues working after a short time, if the Standby side breaks down or a network error occurs. By this it switches to the Maximum Performance mode automatically, that means transactions are commited immediately. Once the Standby database is available again it automatically switches back to the Maximum Protection mode.

Following parameters are responsible for the Redolog transport in Maximum Availability mode:
  • AFFIRM
  • SYNC
  • NET_TIMEOUT
 The parameter NET_TIMEOUT (default 30) tells the time (in seconds) after that the database shall switch to the Maximum Performance mode.
Example:
LOG_ARCHIVE_DEST_2=’SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30 
REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)’

MAXIMUM PROTCTION:
The Maximum Performance mode is used, when the primary database must not be compromised. That means transactions are confirmed, once they are saved into the local Redolog files and asynchronously transmitted to the Standby database. So in case of a breakdown of the primary database you can expect a loss of transactions.

Following parameters are responsible for the Redolog transport here:
  • NOAFFIRM
  • ASYNC
# Choose what level of protection you require
sql> alter database set standby to maximize performance;
sql> alter database set standby to maximize availability;
sql> alter database set standby to maximize protection;
STANDBY REDO LOG(SRL) :
Standby Redo Logs (SRL) : is similar to Online Redo Log (ORL) and only difference between two is that Standby Redo Log is used to store redo data received from another database (primary database).
 Standby Redo Logs are only used if you have the LGWR as transport mode to Remote Standby Database.  
Standby Redo Log is required if
  1. standby database is in maximum protection or maximum availability modes. 
  2. If you are using Real-Time Apply on Standby Database.
  3. If you are using Cascaded Destinations
REAL TIME AND REDO APPLY :
Real Time Apply:
When real-time apply is enabled, the log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived.
In this example we are going to use LGWR on the primary for redo transport just to prove that a committed record on the primary without switching a log will show up on the standby. However real-time apply will work with both LGWR and ARCH using SRL's.
- Parameter
log_archive_dest_2='SERVICE=rhclu2p LGWR ASYNC 
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rhclu2p'
For physical standby databases, issue the statement,
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
For logical standby databases, issue the statement,
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE    
Redo Apply :
To start apply services on a physical standby database, ensure the physical standby database is started and mounted and then start Redo Apply using the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement.
You can specify that Redo Apply runs as a foreground session or as a background process, and enable it with real-time apply.
To start Redo Apply in the foreground, issue the following SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
If you start a foreground session, control is not returned to the command prompt until recovery is canceled by another session.
To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

DATGUARG CONFIGURATION

Physical Standby in Oracle 11g:
Production Name:prod
Standby    Name:stand

Implementation notes:
Once you have your primary database up and running these are the steps to follow:
1. Enable Forced Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Primary Database Initialization Parameters

Having followed these steps to implement the Physical Standby you need to follow these steps: 
1. Create a Control File for the Standby Database
2. Backup the Primary Database and transfer a copy to the Standby node.
3. Prepare an Initialization Parameter File for the Standby Database
4. Configure the listener and tnsnames to support the database on both nodes
5. Set Up the Environment to Support the Standby Database on the standby node.
6. Start the Physical Standby Database
7. Verify the Physical Standby Database Is Performing Properly 

PRIMARY STEPS:
1.Enable Force logging :
SQL>Alter database enable Force logging;
Check:
SQL> select force_logging from v$database;
2.Create a Password File:
Note:A password file must be created on the Primary and copied over to the Standby site. The sys password must be identical on both sites
$$$cd $ORACLE_HOME/dbs
$$$orapwd file=orapwprod password=oracle force=y
3.Configure a Standby Redo Log:
 It is important to configure the Standby Redo Logs (SRL) with the same size as the online redo logs.
Check Count:
SQL> select * from v$logfile; 
create standby redo log files, and it should be one extra either then online redo log file:
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 50M;
4.Enable Archiving:
SQL> startup mount;
SQL> alter database archivelog; 
5.Set Primary Database Initialization Parameters:
Add below parameter in PROD pfile
DB_NAME       – PROD                 --Must be same on primary and on all standby
DB_UNIQUE_NAME   – PROD    --Must be different on primary and all standby
LOG_ARCHIVE_CONFIG     -– This parameter includes db_unique_name which are the part of Dataguard configuration
LOG_ARCHIVE_DEST_n   – '/u01/PROD/Archive/' --Define local and remote archive log file location
LOG_ARCHIVE_DEST_STATE_n -– Define state of archiving (ENABLE or DIFER)
STANDBY_ARCHIVE_DEST- '/u01/PROD/Archive/' --same as archive log file location
REMOTE_LOGIN_PASSWORDFILE  – Exclusive     --Must be in EXCLUSIVE mode
FAL_SERVER   – STAND --Use for archivelog gap resolution (required only in physical standby server)
DB_FILE_NAME_CONVERT  – '/u01/STAND/Datafile/','/u01/PROD/datafile/' --Required when directory structure is different datafile
LOG_FILE_NAME_CONVERT  – '/u01/STAND/logfile/','/u01/PROD/logfile/' --Required when directory structure is different logfile
STANDBY_FILE_MANAGEMENT– Auto --Keep auto to create file automatically on standby  

SQL> shutdown immediate;
SQL> startup nomount pfile='/u01/PROD/pfile/initprod.ora';
SQL> create spfile from pfile='/u01/PROD/pfile/initprod.ora.ora';
SQL> shutdown immediate;
SQL> startup; 
6.create standby controlfile and pfile:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS  ‘/u01/stdcontrol.ctl’;
SQL> CREATE PFILE=’/u01/initstd.ora’ from spfile;
7.Configure the listener and tnsnames to support the database on both nodes:
$$$ORACLE_HOME/network/admin/listener.ora
$$$ORACLE_HOME/network/admin/tnsnames.ora
PHYSICAL STANDBY STEPS:
1.Create all required directories for dump directories and archived log destination Like data,log and dumpfile location
2.Prepare an Initialization Parameter File for the Standby Database Copy and edit the primary init.ora to set it up for the standby role
Mandataory Parameters
DB_NAME                    – PROD                 
DB_UNIQUE_NAME             – STAND                                       
LOG_ARCHIVE_DEST_n         – '/u01/STAND/Archive/'                      
STANDBY_ARCHIVE_DEST       - '/u01/STAND/Archive/' 
REMOTE_LOGIN_PASSWORDFILE  – Exclusive            
FAL_SERVER                 – PROD                
DB_FILE_NAME_CONVERT       – '/u01/PROD/datafile/'.'/u01/STAND/Datafile/
LOG_FILE_NAME_CONVERT      – '/u01/PROD/logfile/','/u01/STAND/logfile/'
STANDBY_FILE_MANAGEMENT    – Auto      
3.Backup the Primary Database and transfer a copy to the Standby node.
Copy all datafile to standby(PROD TO STAND)
SQL>Shut immedaite;
$$scp *.dbf STAND:/u01/STAND/DATAFILES/

SQL>Startup;

------------***************((((((( OR )))))))))***************---------------------
Using RMAN:
RMAN>Backup database Plus archivelog;
(((Note:Transfer the backup piece to standby server)))

Copy Standby controlfile to Standby Server(PROD TO STAND):
$$scp /u01/stdcontrol.ctl STAND:/u01/STAND/CONTROL/stdcontrol.ctl

Copy standby pfile to standby Server(PROD TO STAND):
$$scp /u01/initstd.ora STAND:/u01/STAND/PFILE/initstd.ora

Copy password file to Standby server(PROD TO STAND):
$$scp $ORACLE_HOME/dbs/orapwprod STAND:$ORACLE_HOME/dbs/orapwstand
4.Start Physical Standby --  Manual
$export ORACLE_SID=stand
$sqlplus / as sysdba
SQL> startup nomount pfile='/u01/STAND/pfile/initstand.ora'
SQL> create spfile from pfile='/u01/STAND/pfile/initstand.ora';
SQL> shutdown immediate;
SQL> startup mount
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; 
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
Note:- After finishing DG Setup we need to create standby redo log file on standby server, and it should be one extra either then online redo log file.
------------***************((((((( OR )))))))))***************---------------------
Start Physical Standby -- Using RMAN:
$export ORACLE_SID=std
$sqlplus / as sysdba
Sql> create spfile from pfile=’/u01/STAND/pfile/initstand.ora’;

Now exit from SQL prompt and login with RMAN then restore backup

Sql> exit
$rman target=/
RMAN>startup mount
RMAN>catalog start with '/backup/';
RMAN> restore database;
RMAN> exit
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; 
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;

Note:- After finishing DG Setup we need to create standby redo log file on standby server, and it should be one extra either then online redo log file.

                       Oracle Data Guard  12c Feautures

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to Enable/Disable autovacuum on PostgreSQL