DataGuard

                  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 diskdatabase 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 = std

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;

(it created initprod.ora)

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_2='service=topune'

*.standby_file_management=auto

:wq

cp initprod.ora initstd.ora

create the necessary directory structure for standby database

cd  /u01(to go to u01 directory)

mkdir -p /u01/std/arch

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

cd $ORACLE_HOME/dbs

vi initstd.ora

replace all "prod" with "std"

:%s/prod/std/g

change the following parameters:

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

db_unique_name='pune'

instance_name='std'

remove log_archive_dest_2

*.db_file_name_convert='/u01/prod','/u01/std'

*.log_file_name_convert='/u01/prod','/u01/std'

:wq

on primary

export ORACLE_SID=prod

sqlplus / as sysdba

startup mount;

ALTER DATABASE FORCE LOGGING;

exit;

cd $ORACLE_HOME/dbs

create password file for both

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

orapwd file=orapwstd 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 sid name is std.

Go to Linux terminal

$netmgr

create a listener list_std for standby database

$lsnrctl start list_std

on primary server create a connect string tns service name "topune"

$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 file 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;

column member format a30
select member,type from v$logfile;

alter database add standby logfile group 4 '/u01/std/redo04.log' size 50m;

alter database add standby logfile group 5 '/u01/std/redo05.log' size 50m;

alter database add standby logfile group 6 '/u01/std/redo06.log' size 50m;

select member,type 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

Going back to physical standby

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.

DGMGRL (Dataguard Broker Utility)

One of the prerequisites for using DGMGRL is that a primary database and any standby databases must already exist. The DG_BROKER_START initialization parameter must be set to TRUE for all databases in the configuration. You must use a server parameter file with the broker. If an instance was not started with a server parameter file, then you must shut down the instance and restart it using the server parameter file.

show parameter spfile;

We must use oracle enterprise edition to use dgmgrl utility.

select banner from v$version;

compatibility atleast 10.2.0.1.0

show parameter compatible

Here we are configuring both primary and standby in single machine. So single listener is enough.  But we need to create 2 tns service names one to primary(toprod)  and one to standby(topune).

Primary database name = prod
SID name          = prod
db_unique_name    = hyd
tns service name      = toprod

Standby database name = std
SID name          = std
db_unique_name    = pune
tns service name      = topune

$lsnrctl start

$tnsping toprod

$tnsping topune

both must be ok.

$hostname
m2.dba.com

here m2 is my vm machine name and dba.com is my domain.

set this domain value to the parameter db_domain

alter system set db_domain=dba.com scope=spfile;

in listener we must do static regstration of the db. this must be done 2 times. one for normal prod db and another for dgmgrl.

2 entries everything is same but global db name should be like this

at Primary

normal
Global Database Name = prod

dgmgrl
Global Database Name  = hyd_DGMGRL.dba.com
systax is DbUniqueName_DGMGRL.domainName

at Standby

normal
Global Database Name = std

dgmgrl
Global Database Name  = pune_DGMGRL.dba.com

At Primary and standby both

show parameter dg_broker

alter system set dg_broker_start = true;

Before starting actual database we must add standby redo log files on both sides.  This is required if we want to configure MaxAvailability or MaxProtection modes. Both sides we need because we may also need switchover or failover in future.

alter database add standby logfile group 4 '/u01/prod/redo04.log' size 50m;

alter database add standby logfile group 5 '/u01/prod/redo05.log' size 50m;

alter database add standby logfile group 6 '/u01/prod/redo06.log' size 50m;
select group#, type from v$logfile order by 1;

alter database add standby logfile group 4 '/u01/std/redo04.log' size 50m;

alter database add standby logfile group 5 '/u01/std/redo05.log' size 50m;

alter database add standby logfile group 6 '/u01/std/redo06.log' size 50m;
select group#, type from v$logfile order by 1;

Actual DGMGRL configuration

dgmgrl>

connect sys/manager@toprod

To create the broker configuration, you first define the configuration including a profile for the primary database, which in this case is called prod. In a later command, you will add a profile for the standby database, std.

create configuration 'dgb'
as primary database is 'hyd'
connect identifier is toprod;

Here 'hyd' is the db_unique_name of prod database.

we can confirm the above change using the following command:
show configuration;

To add a standby database to the 'dgb' configuration, use the ADD DATABASE command to create a broker configuration profile for the standby database.

add database 'pune' as
connect identifier is topune
maintained as physical;

Here 'pune' is the db_unique_name of the standby database std.

show configuration;
show database verbose hyd;
show database verbose pune;

Setting Database Properties

After you create the configuration with DGMGRL, you can set database properties at any time.

EDIT DATABASE 'pune' SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc';

Property "LogArchiveFormat" updated.

EDIT DATABASE 'pune' SET PROPERTY 'StandbyArchiveLocation'='/u01/std/arch/';

Property "StandbyArchiveLocation" updated.

Use the SHOW DATABASE VERBOSE command to view all properties and their values for a database.

show database verbose pune;

You can change a property if the database is enabled or disabled. However, if the database is disabled when you change a property, the change does not take effect until the database is enabled.

Enabling the Configuration and Databases

So far, the DGB configuration is disabled, which means it is not under the control of the Data Guard broker. When you finish configuring the databases into a broker configuration and setting any necessary database properties, you must enable the configuration to allow the Data Guard broker to manage it.

You can enable:

·       The entire configuration, including all of its databases.

·       A standby database

Enable the entire configuration.

You can enable the entire configuration, including all of the databases, with the following command:

enable configuration;

Show the configuration.

Use the SHOW command to verify that the configuration and its databases were successfully enabled:

show configuration;

Enable the database.

This step is unnecessary except if the standby database was previously disabled with the DISABLE DATABASE command. Normally, enabling the configuration also enables the standby database.

DGMGRL> ENABLE DATABASE 'pune';

show database 'pune';
show databae hyd;

On Standby

dgmgrl>
connect sys/manager@topune
show configuration;

should be same as primary.

Setting the Configuration Protection Mode

You can change the protection mode of the configuration at any time. However, it is best if you do this when there is no activity occurring in the configuration if you are moving to the maximum protection or maximum availability modes.

If the protection mode to be set is maximum protection mode, the broker automatically restarts the primary database.

This scenario sets the protection mode of the configuration to the MAXAVAILABILITY mode. Note that this protection mode requires that there be at least one standby database configured to use standby redo log files, with its LogXptMode configurable database property set to SYNC.

Step 1   Configure standby redo log files, if necessary.

Step 2   Set the LogXptMode configurable database property appropriately.

DGMGRL> EDIT DATABASE 'pune' SET PROPERTY 'LogXptMode'='SYNC';

The broker will not allow this command to succeed unless the standby database is configured with standby redo log files in the configuration.

Step 3   Change the overall protection mode for the configuration.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Step 4   Verify the protection mode was changed.

DGMGRL> SHOW CONFIGURATION;

CHANGING THE MODES

At primary side only:

    >edit configuration set protection mode as ‘MaxAvailability’;

    >edit configuration set protection mode as ‘MaxProtection’;

    >edit configuration set protection mode as ‘MaxPerformance’;

Performing Routine Management Tasks

Changing Properties and States

Alter a Database Property

DGMGRL> EDIT DATABASE 'hyd' SET PROPERTY 'LogArchiveTrace'='127';

Alter the State of a Standby Database

You might want to temporarily stop Redo Apply on a physical standby.

DGMGRL> EDIT DATABASE 'pune' SET STATE='APPLY-OFF';

Redo data is still being received when you put the physical standby database in the APPLY-OFF state.

Alter the State of a Primary Database

EDIT DATABASE hyd SET STATE=TRANSPORT-OFF;

EDIT DATABASE hyd SET STATE=TRANSPORT-ON;

Disable a Configuration

DGMGRL> DISABLE CONFIGURATION;

Disable a Standby Database

You use the DISABLE DATABASE command when you temporarily do not want the broker to manage and monitor a standby database.

DGMGRL> DISABLE DATABASE 'pune';

Removing the Configuration or a Standby Database

When you use either the REMOVE CONFIGURATION or REMOVE DATABASE command, you effectively delete the configuration or standby database profile from the broker configuration file, removing the ability of the Data Guard broker to manage the configuration or the standby database, respectively.

Step 1   Remove a standby database from the configuration.

DGMGRL> SHOW CONFIGURATION;

DGMGRL> REMOVE DATABASE 'pune';

When operating under either maximum protection mode or maximum availability mode, the broker prevents you from deleting the last standby database that supports the protection mode.

Step 2   Remove the broker configuration.

DGMGRL> REMOVE CONFIGURATION;

SHOW CONFIGURATION;

Performing a Switchover Operation

You can switch the role of the primary database and a standby database using the SWITCHOVER command. Before you issue the SWITCHOVER command, you must ensure:

·       The state of the primary and standby databases are TRANSPORT-ON and APPLY-ON, respectively.

·       All participating databases are in good health, without any errors or warnings present.

·       The standby database properties were set on the primary database, so that the primary database can function correctly when transitioning to a standby database (shown in the following examples in boldface type).

·       Standby redo log files on the primary database are set up, and the LogXptMode configurable database property is set to SYNC if the configuration is operating in either maximum availability mode or maximum protection mode.

·       If fast-start failover is enabled, you can perform a switchover only to the standby database that was specified as the target standby database.The state of the primary and standby databases are TRANSPORT-ON and APPLY-ON, respectively.

Step 1   Check the primary database.

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database, as follows:

DGMGRL> SHOW DATABASE VERBOSE 'hyd';

Important properties to look for are

LogXptMode                       = 'SYNC'  

DbFileNameConvert               = 'dbs/bt, dbs/t'

LogFileNameConvert              = 'dbs/bt, dbs/t'

StandbyArchiveLocation        = '/archfs/arch/'

LogArchiveFormat                   = 'db1r_%d_%t_%s_%r.arc'

Step 2   Check the standby database that is the target of the switchover.

DGMGRL> SHOW DATABASE VERBOSE 'pune';

here also look for the same above properties.

Step 3   Issue the switchover command.

Issue the SWITCHOVER command to swap the roles of the primary and standby databases.

DGMGRL> switchover to 'pune';

Step 4   Show the configuration.

After the switchover completes, use the SHOW CONFIGURATION and SHOW DATABASE commands to verify that the switchover operation was successful.

Converting a Physical Standby to a Snapshot Standby

If you have a physical standby database that you would like to convert to a snapshot standby database, use the DGMGRL CONVERT DATABASE command. Redo data will continue to be received by the database while it is operating as a snapshot standby database, but it will not be applied until the snapshot standby is converted back into a physical standby database.

Note that the Flashback Database feature is required to create a snapshot standby database. If Flashback database is disabled, it is automatically enabled during conversion to a snapshot standby database. The broker automatically restarts the database to the mounted state if it had been opened with Flashback Database disabled. No user action is required.

DGMGRL> CONVERT DATABASE 'pune' to SNAPSHOT STANDBY;

DGMGRL> SHOW CONFIGURATION;

When you are ready to revert the database back to a physical standby database, use the DGMGRL CONVERT DATABASE command again as follows. Any updates made to the database while it was operating as a snapshot standby database will be discarded. All accumulated redo data will be applied by Redo Apply services after the database is converted back to a physical standby database.

DGMGRL> CONVERT DATABASE 'pune' to PHYSICAL STANDBY;

Monitoring a Data Guard Configuration

The scenario in this section demonstrates how to use the SHOW command and monitorable database properties to identify and resolve a failure situation.

Step 1   Check the configuration status.

The status of the broker configuration is an aggregated status of all databases and instances in the broker configuration. You can check the configuration status first to determine whether or not any further action needs to be taken. If the configuration status is SUCCESS, everything in the broker configuration is working fine. However, if you see the following error, it means something is wrong in the configuration:

DGMGRL> SHOW CONFIGURATION;

Step 2   Check the database status.

To identify which database has the failure, you need to go through all of the databases in the configuration one by one.

DGMGRL> SHOW DATABASE 'hyd';

DGMGRL> SHOW DATABASE 'pune';

Step 3   Check the StatusReport monitorable database property.

When you see message ORA-16810, you can use the StatusReport monitorable database property to identify each of the errors or warnings:

DGMGRL> SHOW DATABASE 'hyd' 'StatusReport';

Step 4   Check the LogXptStatus monitorable database property.

You see error ORA-16737 in the previous status report in Step 3. To identify the exact log transport error, you can use LogXptStatus monitorable database property:

DGMGRL> SHOW DATABASE 'hyd' 'LogXptStatus';

LOG TRANSPORT STATUS

PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS

              sales1             DR_Sales ORA-12541: TNS:no listener

Now you know the exact reason why redo transport services failed. To fix this error, start the listener for the physical standby database pune.

Step 5   Check the InconsistentProperties monitorable database property.

You also see warning ORA-16714 reported in Step 3. To identify the inconsistent values for property LogArchiveTrace, you can use the InconsistentProperties monitorable database property:

DGMGRL> SHOW DATABASE 'hyd' 'InconsistentProperties';

INCONSISTENT PROPERTIES

   INSTANCE_NAME   PROPERTY_NAME    MEMORY_VALUE    SPFILE_VALUE    BROKER_VALUE

          prod                     LogArchiveTrace           255                   0                 0

It seems that the current database memory value (255) is different from both the server parameter file (SPFILE) value (0) and Data Guard broker's property value (0). If you decide the database memory value is correct, you can update Data Guard broker's property value using the following command:

DGMGRL> EDIT DATABASE 'hyd' SET PROPERTY 'LogArchiveTrace'=255;

Property "LogArchiveTrace" updated

In the previous command, Data Guard broker also updates the spfile value for you so that value for LogArchiveTrace is kept consistent.

Step 6 Check the InconsistentLogXptProps monitorable database property.

Another warning you see in the status report returned in Step 3 is ORA-16715. To identify the inconsistent values for the redo transport configurable database property, ReopenSecs, you can use the InconsistentLogXptProps monitorable database property.

DGMGRL> SHOW DATABASE 'hyd' 'InconsistentLogXptProps';

INCONSISTENT LOG TRANSPORT PROPERTIES

   INSTANCE_NAME    STANDBY_NAME   PROPERTY_NAME    MEMORY_VALUE    BROKER_VALUE

          std             pune         ReopenSecs                      600                            300  

The current database memory value (600) is different from the Data Guard broker's property value (300). If you think the broker's property value is correct, you can fix the inconsistency by re-editing the property of the standby database with the same value, as shown in the following example:

DGMGRL> EDIT DATABASE 'pune' SET PROPERTY 'ReopenSecs'=300;

Property "ReopenSecs" updated

While these changes are happening we can monitor alert_prod.log file for live changes happening in this db.

go to alert log folder

ls -ltr drc*

$tail -f drcorcl.log
$tail -f alert_prod.log