Data Guard

Primary Database

A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role.

Standby Database

A standby database is a transactionally consistent copy of the primary database.

There are 3 types of standby databases possible in Data Guard Configuration:

1. Physical standby database

2. Logical standby database

3. Snapshot Standby database

Physical standby database:

Provides a physically identical copy of the primary database, with on disk

database structures that are identical to the primary database on a block-for-

block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.

Logical standby database:

Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database through SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.

Snapshot Standby Database:

A snapshot standby database is a fully updatable standby database.

Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database, after first discarding any local updates made to the snapshot standby database.

Data Guard Services

1. Redo Transport Services
2. Apply Services
3. Role Transitions

A physical standby database Data Guard Protection Modes
1. Maximum availability
2. Maximum performance (default)
3. Maximum protection

Actual Steps of configuring a data guard in max performance mode

steps

===

keep 2 terminals open for primary and standby

primary database = prod

standby database = standby

on primary

export ORACLE_SID=prod

sqlplus / as sysdba

startup;

archive log list;

make sure primary is in archive log mode. as we are going to use RMAN to configure dataguard concept.  RMAN works only with archivelog mode.

show parameter spfile;

(to check the system is working with spfile or pfile)

create pfile from spfile;

(will create initprod.ora which can be edited)

shut immediate;

exit;

cd $ORACLE_HOME/dbs

rm spfileprod.ora

vi initprod.ora

add the following parameters:

*.db_unique_name='hyd'

*.instance_name='prod'

*.log_archive_dest_1='location=/u01/prod/arch'

*.log_archive_dest_2='service=topune'

*.standby_file_management=auto

:wq

cp initprod.ora initstandby.ora

create the necessary directory structure for standby database

cd (to go to home directory)

mkdir -p /u01/standby/arch

mkdir -p /u01/app/oracle/admin/standby/adump

cd $ORACLE_HOME/dbs

vi initstandby.ora

replace all "prod" with "standby"

:%s/prod/standby/g

change the following parameters:

db_name='prod' (must be same across all the servers)

db_unique_name='pune'

instance_name='standby'

remove log_archive_dest_2

db_file_name_convert='/u01/prod','/u01/standby'

log_file_name_convert='/u01/prod','/u01/standby'

:wq

on primary

export ORACLE_SID=prod

sqlplus / as sysdba

startup mount;

ALTER DATABASE FORCE LOGGING;
(As dataguard concept works based on redo log, force logging is must)

exit;

cd $ORACLE_HOME/dbs

create password file for both primary and standby

orapwd file=orapwprod password=manager force=y ignorecase=y

orapwd file=orapwstandby password=manager force=y ignorecase=y

sqlplus / as sysdba

select status from v$instance;

(this is primary and must be in mount stage)

exit;

on standby

export ORACLE_SID=standby

sqlplus / as sysdba

startup nomount;

Configure a listener for standby database and a tns entry "topune" for the same.

As this standby will be in pune city so the connection which goes to standby listener will be called as "topune".

But the database name must be same "prod".  While creating tnsname also service name must be prod

Go to Linux terminal

$netmgr

create a listener list_standby for standby database

create a connect string tns service name "topune"

$lsnrctl start list_standby

$tnsping topune

on primary

$rman target / nocatalog auxiliary sys/manager@topune

RMAN> duplicate target database for standby from active database;

exit;

sqlplus / as sysdba

select name, open_mode,database_role,protection_mode from v$database;

col db_unique_name format a15

select db_unique_name, open_mode,database_role,protection_mode from v$database;

alter database open;

on standby

shut immediate;

startup nomount;

alter database mount standby database;

alter database open read only;

alter database recover managed standby database disconnect;
alter database recover managed standby database disconnect from session;

select name, open_mode,database_role,protection_mode from v$database;

select db_unique_name, open_mode,database_role,protection_mode from v$database;

on primary

go to scott user and make some txn

sqlplus / as sysdba

conn scott/tiger

create table t1 as select * from emp;

insert into t1 select * from t1;

/

/

commit;

conn / as sysdba

alter system switch logfile;

archive log list;

note down the latest number

check the same archive log list at standby

both must be same. once we issue the "disconnect" command standby starts getting archive logs from primary and apply them locally. after a couple of minutes based on number of archive files both will be same.

If any files not moved towards standby copy them manually and use the below command. once we apply few others will start applying themself.

SQL> alter database register logfile '/var/arch/arch_1_101.arc';
If we have too many files we cant do it manual. Here we can use the below RMAN command:
rman> catalog start with '/var/arch';

Verify the Physical Standby Database Is Performing Properly

Step 1 Identify the existing archived redo log files.

select sequence#,first_time,next_time from v$archived_log order by 1;

Step 2 Force a log switch to archive the current online redo log file.

SQL> ALTER SYSTEM SWITCH LOGFILE;

Step 3 Verify the new redo data was archived on the standby database.

select sequence#,first_time,next_time from v$archived_log order by 1;

Step 4 Verify that received redo has been applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY 1;

Note: The value of the APPLIED column for the most recently

received log file will be either IN-MEMORY or YES if that log file has been

applied.

Once the apply is uptodate we can keep the standby in read only mode.

alter database recover managed standby database cancel;

Converting from Max Performance Mode to Max Availability Mode

shut both primary and standby

at primary

cd $ORACLE_HOME/dbs

vi initprod.ora

log_archive_dest_2='service=topune lgwr'

:wq

sqlplus / as sysdba

startup mount

alter database set standby database to maximize availability;

alter database open;

select name,open_mode,database_role, protection_mode from v$database;

With this primary is ready with max availability mode.  At standby we need to create 3 redo log files so that primary can write directly into standby logs.

At Standby

sqlplus / as sysdba

startup nomount;

alter database mount standby database;

alter database open read only;

select name,open_mode,database_role, protection_mode from v$database;

select member from v$logfile;

alter database add standby logfile group 4 '/home/oracle/standby/redo04.log'

size 50m;

alter database add standby logfile group 5 '/home/oracle/standby/redo05.log'

size 50m;

alter database add standby logfile group 6 '/home/oracle/standby/redo06.log'

size 50m;

select member from v$logfile;

select group#,status from v$managed_standby;

select group#,status from v$standby_log;

select name,open_mode,database_role, protection_mode from v$database;

shut immediate;

startup nomount;

alter database mount standby database;

alter database open read only;

select name,open_mode,database_role, protection_mode from v$database;

alter database recover managed standby database disconnect using current logfile;

alter database recover managed standby database cancel;


Converting from Max Availability Mode to Max Protection

at primary

shut immediate
exit

cd $ORACLE_HOME/dbs

vi initprod.ora

log_archive_dest_2='service=topune lgwr sync affirm'
:wq

At standby

sqlplus / as sysdba
startup

At Primary

sqlplus / as sysdba

startup mount

alter database set standby database to maximize protection;

alter database open;

select name,open_mode,database_role,protection_mode from v$database;

at standby

select name,open_mode,database_role,protection_mode from v$database;

should be same

shut immediate
startup

alter database recover managed standby database disconnect using current logfile;

alter database recover managed standby database cancel;

Snapshot Standby Database

FRA must be enabled at standby, to configure Snapshot Standby Database.

show parameter recover

shut immediate
exit

startup mount

alter database convert to snapshot standby;

alter database open;

select name,open_mode,database_role,protection_mode from v$database;

now open mode will be read write

make some dml, ddl for testing

shut immediate

startup mount

alter database convert to physical standby;

alter database open;

all the ddl, dml done previously will be lost and standby will come to the position where we converted it to snapshot standby database.