Dataguard overview
Maximum Availability
This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to 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 mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.
This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.
Maximum Performance
This protection mode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).
This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.
This is the default protection mode.
Maximum Protection
This protection mode ensures that zero data loss occurs if a primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, 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.
Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.
Data guard Configuration:
$>dgmgrl
DGMGRL for Solaris: Version 10.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> show configuration
not logged on
DGMGRL> connect sys / sh4rk1@ProdseP;
Connected.
DGMGRL> show configuration
Configuration
Name: prodg
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ProdsePR - Primary database
ProdseDG - Physical standby database
Current status for "prodg":
SUCCESS
DGMGRL>
> dgmgrl
DGMGRL for Solaris: Version 10.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sh4rk1@ProdseP
Connected.
DGMGRL> show configuration
Configuration
Name: prodg
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ProdsePR - Physical standby database
ProdseDG - Primary database
Current status for "prodg":
SUCCESS
DGMGRL> show database 'ProdseDG';
Database
Name: ProdseDG
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
ProdseP
Current status for "ProdseDG":
SUCCESS
DGMGRL> show database 'ProdsePR';
Database
Name: ProdsePR
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
ProdseP
Current status for "ProdsePR":
SUCCESS
DGMGRL> swtichover to 'ProdsePR';
Unrecognized command "swtichover", try "help"
DGMGRL> switchover to 'ProdsePR';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "ProdseP" on database "ProdseDG"
Shutting down instance "ProdseP"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "ProdseP" on database "ProdsePR"
Shutting down instance "ProdseP"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ProdseP" on database "ProdseDG"
Starting instance "ProdseP"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "ProdseP" on database "ProdsePR"
Starting instance "ProdseP"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ProdsePR"
=========================================================
Automatic failover standby
By default, protection mode of the Data Guard configuration is set to MAXIMUM PERFORMANCE
DGMGRL> connect sys/***@db1
Connected.
DGMGRL> show configuration
Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
db1 - Primary database
stdb - Physical standby database
Current status for "DRTest":
SUCCESS
Enabling Fast-Start Failover requires the following pre-requisites to be met:
Flashback Database feature is enabled on both the primary and the standby database.
The protection mode of the configuration must be set to MAXIMUM AVAILABILITY
tnsnames.ora in the ORACLE_HOME where the observer runs must be set to see both databases, the primary and the standby.
DGMGRL must be available on the observer host.
Enable Flashback Database
I will enable flashback database feature on both databases. This assumes that the flash recovery area is configured.
The flashback database feature provides ability for an easy reinstatement of the failed primary database to new standby database.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1261348 bytes
Variable Size 100663516 bytes
Database Buffers 176160768 bytes
Redo Buffers 7127040 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
Set the protection mode to MAXIMUM AVAILABILITY
The Fast-Start Failover can be enabled only if the protection mode is set to MAXIMUM AVAILABILITY.
LogXptMode has to be set to 'SYNC'
DGMGRL> edit database 'db1'
> set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database 'stdb'
> set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit configuration set protection mode as MaxAvailability;
Succeeded.
DGMGRL> show configuration
Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
db1 - Primary database
stdb - Physical standby database
Current status for "DRTest":
SUCCESS
SQL> conn sys/***@db1 as sysdba
Connected.
SQL> select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> conn sys/***@stdb as sysdba
Connected.
SQL> select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL>
I ensured that tnsnames.ora are set correctly as well DGMGRL is installed.
Now, since all the prerequisites are met, lets move forward and enable the FSFO.
Before enabling it, make sure each of the databases in the configuration has set a fast start failover target. This is achieved by setting the FastStartFailoverTarget parameter.
DGMGRL> edit database 'db1' set property faststartfailovertarget='stdb';
Property "faststartfailovertarget" updated
DGMGRL> edit database 'stdb' set property faststartfailovertarget='db1';
Property "faststartfailovertarget" updated
Another important parameter that has to be set is FastStartFailoverThreshold. This parameter specifies the amount of time (in seconds) the observers attempts to reconnect to the primary database before starting the fast-start failover to the standby database. The default value is set to 30 seconds. In the example I set this parameter to 120 seconds.
DGMGRL> edit configuration set property FastStartFailoverThreshold=120;
Property "faststartfailoverthreshold" updated
Now lets enable the fast start failover:
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
If you check the configuration at this moment you may find the following warning: ORA-16608: one or more databases have warnings.
If you check the status of one of the databases, you can see this warning: ORA-16819: Fast-Start Failover observer not started
DGMGRL> show configuration
Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
db1 - Primary database
stdb - Physical standby database
- Fast-Start Failover target
Current status for "DRTest":
Warning: ORA-16608: one or more databases have warnings
DGMGRL> show database verbose 'db1'
Database
Name: db1
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
db1
Properties:
InitialConnectIdentifier = 'db1.mydomain.com'
LogXptMode = 'SYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'stdb, db1'
LogFileNameConvert = 'stdb, db1'
FastStartFailoverTarget = 'stdb'
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'miki-laptop'
SidName = 'db1'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=miki-laptop)(PORT=1521))'
StandbyArchiveLocation = 'dgsby_db1'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "db1":
Warning: ORA-16819: Fast-Start Failover observer not started
So lets start the observer. I will repeat again, the observer should run on a different host, however for the sake of this example it will run on the same machine as the databases.
In order to start the observer one should start DGMGRL utility and login to the data guard configuration.
Once logged in, issue START OBSERVER command. This will start the observer.
Optionally you can set a log file destination while invoking DGMGRL utility and specify name for the observer configuration file (for more information check Data Guard Command-Line Interface Reference).
Once the observer is started, the control is not returned to the user until the observer is stopped.
DGMGRL for Linux: Version 10.2.0.3.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/***@db1
Connected.
DGMGRL> start observer;
Observer started
So lets check the configuration now.
$ dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/***@db1
Connected.
DGMGRL> show configuration verbose;
Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
db1 - Primary database
stdb - Physical standby database
- Fast-Start Failover target
Fast-Start Failover
Threshold: 120 seconds
Observer: miki-laptop
Current status for "DRTest":
SUCCESS
DGMGRL>
As of this moment my configuration has FSFO enabled.
Now lets test if the FSFO really works.
One should be aware of the conditions that must be satisfied for the observer to attempt FSFO.
The full list of conditions can be found in Data Guard Broker user guide under 5.5.2.1 What Happens When Fast-Start Failover and the Observer Are Running?
I will issue SHUTDOWN ABORT to the primary database (SHUTDOWN NORMAL/IMMEDIATE/TRANSACTIONAL would not trigger the failover).
Once I crash the database, the status of the configuration will return Error: ORA-16625: cannot reach the database. Be aware that since the primary database is down, the only way to check the configuration is to connect using the standby database credentials.
SQL> conn sys/***@db1 as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL>
DGMGRL> connect sys/***@stdb
Connected.
DGMGRL> show configuration
Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
db1 - Primary database
stdb - Physical standby database
- Fast-Start Failover target
Current status for "DRTest":
Error: ORA-16625: cannot reach the database
After waiting for two minutes (FSFO threshold was set to 120 seconds), I checked the observer log file and found out that it started the failover.
[W000 08/09 17:58:49.75] Observer started.
18:05:38.31 Saturday, August 09, 2008
Initiating fast-start failover to database "stdb"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "stdb"
18:06:16.82 Saturday, August 09, 2008
DGMGRL> show configuration verbose
Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
db1 - Physical standby database (disabled)
- Fast-Start Failover target
stdb - Primary database
Fast-Start Failover
Threshold: 120 seconds
Observer: miki-laptop
Current status for "DRTest":
Warning: ORA-16608: one or more databases have warnings
DGMGRL>
So the observer started the fast-start failover and has successfully converted the former standby to new primary database.
Once the failover is done the observer will attempt to automatically reinstate the former primary database to new physical standby. Certain requirements have to be met for the automatic reinstatement to take place. If not, the database can be manually reinstated. Once the reinstatement is successful you can see that the former primary database became new physical standby.
DGMGRL> show configuration
Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
db1 - Physical standby database
- Fast-Start Failover target
stdb - Primary database
Current status for "DRTest":
SUCCESS
DGMGRL>
Finally, you can do switchover to perform role change and to place the configuration in its initial state.
===============================
Manual switch over steps
Most of you whizkids might know this but this is quite a useful thing to know .
If your standby is broken and you need to rebuild it then
On the primary site
rman target / auxiliary sys/password@standby
RMAN> RUN {
ALLOCATE CHANNEL d1 TYPE DISK;
ALLOCATE CHANNEL d2 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL cnv1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL cnv2 TYPE DISK;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
}
Once this is done , its likely that the standby redo log files will not be copied / applied due to differences in naming conventions , so simply ftp the standby online redo from primary to standby site .
With the above the standby should have been restored.
One that is done you can do the steps listed below
11g allows the database to be in READ Only and managed recovery mode.
Now you can bring up the standby database in read-only mode and continue to have the standby database updated from the primary. Here are the steps to achieve that:
1. Stop the managed recovery process on standby database:
In DB11GDG ( Standby database )
SQL > alter database recover managed standby database cancel;
Database altered.
2. Open the DB11GDG – standby database as read-only:
SQL > alter database open read only;
Database altered.
3. Restart the managed recovery process on the standby database:
SQL > alter database recover managed standby database using current logfile disconnect;
Now the standby database is being updated, but it is simultaneously open for read-only access—this is the essence of Oracle Active Data Guard.
