Oracle Dataguard - DR Disaster Server:
-------------------------------------------------
Oracle Data guard ensures high availabilty data protection,and disaster recovery for enterprise data,datagurad provides a comprehensive set of services that create,maintain,manage and monitor one or more stand by databases to enable production oracle databases to survive disaster and data corruptions,datgurad physical standbysetup using the dataguard broker in oracle database 12c release 1 Dataguard is the name for oracle standby database solution,used for disaster recovery and high availability
LNS process of primary database captures redo from redolog buffer send it to RFS .RFS process of stand by database through ORACLE NET ,RFS process then writes that redo information to standby redolog files,MRP applies information from the archived redologs to the standby database,when performing managed receovery operations,log apply services automatically apply archived redologs to mainatained transactional synchronization with primary database
Primary system :
-------------------
configure standby server up to 30 for a single primary
standby replica of primary
DDL / DML changes in primary will replicate to standby
Features - standby :
-----------------------
called as dataguard from 9i
prior to 9i , called as standby system
media failure/disk failure/power/disaster -
purpose is to protect primary database
Two types:
-------------
Physical Standby - using redo apply with archives are shipped
Logical Standby - using sql apply
11g introduced - snapshot standby
Standalone system
Primary Database
For Disaster Purpose
any media failure
For that we need a standby setup for Primary
The standby environment is same has Primary.
Using Primary Archives - shipped to Standby Server.
In Standby - RFS will receive and MRP will apply the shipped archives.
RFS - Remote file server process
MRP - Managed recovery Process.
In Primary , LNS wil send the changes made to standby.
LNS ---- >Log network service
From 9i
Primary -> LNS -> Ship -> standby ->RFS receive -> MRP apply
--------------------------------------------------
Prior to Oracle 9i - we called as standby
From 9i - its Dataguard
In 8i , a DBA intervention is required to manage archive shipping from primary to standby by using a crontab script
with a SCP Command.
From 9i , the LNS service will ship the archives.The gap automatically resolved.
-------------------------------------------------------------------------------------------------
Dataguard license is only availiable with
Enterprise Edition/SE2
But not with standard edition
Standby Database Types :
---------------------- ---------
Physical Standby - using redo apply
Logical Standby - using sql apply
Physical Standby ---> Using archives (a copy of block) is going
to apply in standby mount stage - Media recovery mode
Logical Standby -- >using sql statements
degrades the performance of primary.
Not recommended.
A logical standby database works in a different manner which keeps in sync with the primary by transforming redo data received from the primary database into logical SQL statements and then executes those SQL statements against the standby database.
With a logical standby database, the standby remains open for user access in read/write mode while still receiving and applying logical records from the primary.
---------------------------------
Dataguard Setup - Physical Standby
---------------
Primary Configuration - Min down time is needed to configure.
------------------------------------------------------------------------------
1. Enable Archivelog
sql>archive log list
2. Create Password file - OS
cd $ORACLE_HOME/dbs
ls -ltr
$ mv orapwprod orapwprod_old
An encrypted sys password stored in OS Password file.
$orapwd file=orapwprod password=sys123
For standby DB - Copy paswd file (must be same)
$cp orapwprod orapwstan
3. Enable Forced Logging
In case, developer enable table nologging for faster inserts/updates of bulk changes. with this no redo will generate on that table. For that we are missing changes in archives. So will enable globally force logging, so those tables are logged forcebly.
sql>ALTER DATABASE FORCE LOGGING;
SQL> select force_logging from v$database;
Parameter file configuration - Primary
---------------------------------------
Lets add few parameters
sql>create pfile from spfile;
sql>shutdown immediate
sql>exit
cd $ORACLE_HOME/dbs
vi initprod.ora
#append the parameters - following
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,stan)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/archives/prod VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
*.LOG_ARCHIVE_DEST_2='SERVICE=stan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stan'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.db_unique_name='prod'
#remove existing log_archive_dest_1
Save it
start the DB with pfile
sqlplus '/as sysdba'
sql>startup pfile='$ORACLE_HOME/dbs/initprod.ora'
sql>create spfile from pfile;
sql>shutdown immediate
sql>startup
Q)Why nologging option is needed in DG config for primary ?
A) nologging option on those tables - they are inserting/updating/delete
using nologging , redo will not generate for that table
if no redo , missing changes in archives...
missing - will not sync with standby from primary
We enable - globally
force logging
will log forcebly the changes to archives...
----------------------------------------
Standby Configuration:
-----------------------------
In stanby Server , we dont have database.
Using RMAN, Will duplicate/Cloning the database from primary to standby.
Preparation
--------------
1. password file - orapwstan (copy from orapwprod)
must be same as primary.
2. Create pfile from primary
Node1
------
$export ORACLE_SID=prod
$sql>create pfile from spfile;
$cd $ORACLE_HOME/dbs
$cp initprod.ora initstan.ora
$export ORACLE_SID=stan
$echo $ORACLE_SID
Edit the pfile for stan
$vi initstan.ora
#do following changes.
#also change the path for audit,controlfiles
*.audit_file_dest='/u01/app/oracle/admin/stan/adump'
*.control_files='/oradata/stan/control01.ctl','/u01/app/oracle/fast_recovery_area/stan/control02.ctl'
#Verify following parameters as follows.
-----------------------------------------
*.db_name='prod' (Must be same on both nodes)
*.db_unique_name='stan' (not similar)
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stanXDB)'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(stan,prod)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/archives/stan VALID_FOR=
(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stan'
*.LOG_ARCHIVE_DEST_2='SERVICE=prod LGWR ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'
#Append additional parameters for stanby
----------------------------------------------------
*.db_file_name_convert='/oradata/prod/','/oradata/stan/'
*.log_file_name_convert='/oradata/prod/','/oradata/stan/'
*.fal_server='prod'
*.fal_client='stan'
*.standby_file_management='auto'
*.instance_name='stan'
*.standby_archive_dest='/archives/stan/'
#remove memory_target if on same node (use comment#)
Save the file...
Create following directories for Stan.
---------------------------------------------
mkdir -p /u01/app/oracle/admin/stan/adump
mkdir -p /u01/app/oracle/oradata/stan
mkdir -p /u01/app/oracle/fast_recovery_area/stan
mkdir -p /oradata/stan/
mkdir -p /archives/stan
------------------------------------------------
Network Configuration
----------------------
register the stan in Listener and tnsnames.
$netmgr
Click Listener - > - Database services
Add Database -> stan
Click Service name - > Edit - Create - Service name
stan...
save
$lsnrctl start LISTENER
$tnsping prod
$tnsping stan
---------------------------------------------------------
Make sure - primary is up and running . ps -ef | grep pmon
----------------------------------------
OPen New Terminal - Another - for stan
$export ORACLE_SID=stan
$echo $ORACLE_SID
sqlplus '/as sysdba'
sql>startup nomount
sql>exit
---------------------------------------------------------
Connect using rman to primary and auxiliary standby instance.
$rman target sys/sys123@prod auxiliary sys/sys123@stan
Use duplicate command to create standby DB.
------------------------------------------
This can be used for Cloning a DB with SID (instead of standby)
rman>
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
rman>exit
$export ORACLE_SID=stan
$sqlplus '/as sysdba'
sql>select open_mode,name,database_role from v$database;
mounted physical_standby
Keep in managed recovery mode - so RFS and MRP will start.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Verify - v$managed_standby - Monitor
sql>select process,sequence#,status from v$managed_standby;
sql>select * from v$archive_gap;
or
v$archive_log,v$log_history
Finding errors in log for standyby:
SQL> select message from v$dataguard_status;
Shutdown - Standby
--------------------------
$export ORACLE_SID=stan
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
sql>shutdown immediate
For startup - standby
--------------------------
$export ORACLE_SID=stan
sql>startup mount
sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
sql>select process,sequence#,status from v$managed_standby;
Process started,receiving and applying using v$managed_standby.
-----------------------------------
Protection Modes
-----------------------
Three protection Modes
Max Performance Mode - default
Max Availability Mode
Max Protection Mode
sql>select database_role,protection_mode from v$database;
Maximum Protection -
--------------------------
Zero Dataloss ,Two sided Protected , Sync,AFFIRM,DB_UNIQUE_NAME
Transactions do not commit until written in atleast one standby server. Wait
for acknowledgement from standby
If standby is down, primary will also down.
Maximum Availability-
----------------------------
Zero Dataloss,Single sided Protected , Sync,AFFIRM,DB_UNIQUE_NAME
Transactions do not commit until written in atleast one standby server.Wait for
acknowledgement from standby.
If standby is down, primary will change the mode to performance mode.
Maximum Performance(default) -
-----------------------------------------
least Dataloss,Async,NOAFFIRM,DB_UNIQUE_NAME
Transaction will commit then transfer to standby to avoid performance issue. will not Wait for acknowledgement from standby.
-------------------------------------
Execute the following SQL statement on the primary database:
On Prod - Node1
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
eg:
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL> SELECT PROTECTION_MODE FROM V$DATABASE;
SQL> ALTER DATABASE OPEN;
------------------------------------------
Two New Features in Standby - 11g
Active Dataguard - read only - reporting db
Snapshot dataguard - read/write - for test cases
Active Dataguard
=================
In Mount, we cannot read/write the data.
To run the reports, can use Active dataguard.
Converting physical standby to Active DG.
ADG will be in read only mode.
The archives logs will not apply in read only mode.
To switch the standby database into read-only mode, do the following.
On Node2 - stan
----------------------
sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE OPEN READ ONLY;
SQL> select open_mode,database_role,protection_mode from v$database;
OPEN_MODE
------------------
READ ONLY
To resume managed recovery, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Snapshot dataguard - read/write
----------------------------------------
For test cases, we can use physical standby converting to snapshot dataguard.
The changes can be made and rollbacked after keeping in managed recovery mode.
Converting - ? Mount to read write.-> mount
sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
sql>ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
SQL> select DATABASE_ROLE,open_mode,protection_mode from v$database;
sql>alter database open;
sql>select open_mode from v$database;
SQL> select DATABASE_ROLE,open_mode,protection_mode from v$database;
To convert in to physical standby from snapshot
----------------------------------------------------------
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
alter database convert to physical standby;
shutdown immediate
startup mount
sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
sql>select process,sequence#,status from v$managed_standby;
startup/shutdown steps - stan - cancel-shut
for start- mount-last disconnect
v$managed_standby - process started-MRP/RFS
v$archive_log - finding the gap - applied
v$archive_gap - finding the gap
Protection modes - max-avai,per,pro
Snapshot - read write - will undo after - con-phy
ADG - readonly
rman - auxiliary-only instance
duplicate db from active -
If too many archives found gap ? how to fix ?
RMAN incremental backup from prod - catalogue and apply in standby.
In case no archives - but still too much gap?
rebuild the standby
Brief - Prod and Stan - parameters
Primary is Live production server - changes happen
Physical standby - is Standby system - where changes from primary to standby will receive by archives and applied in mount stage
Active Dataguard - is a standby system converted to read only to read/run reports
Snapshot dataguard - is a standby , converted to read/write for test cases
once converted to physical standby,the changes are rollbacked.
Primary - read/write
Physical - Mounted
Active Dataguard - read-only
Snapshot DG - read/write
NOTE: info on dataguard it may differ on your environment like production,testing,development
THANKS FOR VIEWING MY BLOG FOR MORE UPDATES VIST MY BLOG
