ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
firstname.lastname@example.org. Request a Workshop for
your organization today.
Data Guard is a technology built into
Oracle Database. It is not a download or an add-on and, with the exception of Active Data Guard, fully included, in the basic Enterprise Edition license.
This is true also of Data Guard features such as FarSync and FastSync.
The following was posted by DG Product Manager Larry Carpenter to the OTN forums on 1-Jun-2010. A small amount of editing has been done for consistency.
Maximum Protection and Maximum Availability are Data Guard's Zero Data Loss protection modes.
The only difference is that in Maximum Protection Data Guard will abort the Primary (not allowing a transaction to be reported as committed to the application) if the redo cannot be written to a SYNC standby.
In Maximum Availability it is allowed for a transaction to be committed even if there are no SYNC standby destinations available. That would be the 1st failure, the standby is not reachable.
So when the Primary failed after that (the 2nd failure) you would of course lose data, the data that was generated while the standby was not reachable before the Primary failed.
However, this is not your situation. What you are experiencing is a misunderstanding of how the databases work.
In Oracle Versions prior to 11.2 the SYNC redo transport works as follows.
1. User says COMMIT.
2. LGWR writes the redo to the Online Redo Log.
3. The LNS processes grabs the same redo from the Log Buffer AFTER the LGWR has written the redo to the Online redo log file, and sends it to their standby.
4. When the SYNC standbys all report back that the redo has been received and written to the standby redo log file OR they fail to respond within NET_TIMEOUT seconds is the client told that the transaction has in fact committed.
What you are experiencing is the failure is happening between step 2 and step 3.
The redo is written to the online redo log file at the primary but the client has not been told that the transaction has actually committed.
When you failover and do the select count(*) from the Primary you have the exact count of transactions that the client was actually told had been committed.
When you then restarted the Primary what does it do? Crash recovery! Which will recover the redo that was actually written to the online redo log file which is that 'last' row which was the current transaction but was never actually 'committed' to the application.
Your did not actually lose that row as the application was never told that it was committed.
In a real situation you would have failed over and then when the Primary came back you would have reinstated it as a standby and not opened it.
Try your test where your test application prints out the counter AFTER the commit has finished and then pull the plug, failover and see if the count in the table on the standby matches the counter in the application.
Final result, you didn't lose any data that the application was told was committed.
P.S. As of 11.2 the write to the Online Redo Log file by the LGWR and the send to the SYNC standby happens in parallel so you will see even less of what you saw when you try your test in the same way in 11.2)
Archiver Process on Local Server
Data Guard Monitor
Fetch Archive Log
Logical Change Record
Network Server Process
Logical Standby Process (SQL Apply for Logical DG)
Managed Recovery Process (Redo Apply for Physical DG)
# define archive log naming format
Prepare omega1 for backup
SQL> startup mount;
SQL> show parameter %archive%
SQL> SELECT inst_id, log_mode FROM v$database;
SQL> alter database archivelog;
SQL> alter database force logging;
SQL> alter database add supplemental log data;
SQL> alter database open;
SQL> archive log list;
SQL> alter system switch logfile;
SQL> archive log list;
SQL> SELECT dest_id, valid_type, valid_role, valid_now
Create RMAN catalog and backup omega1
-- as oracle on bigdog
$ sqlplus / as sysdba
-- using OMF
CREATE TABLESPACE cat_tbs;
CREATE USER repoomega#
IDENTIFIED BY repoomega#
DEFAULT TABLESPACE cat_tbs
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON cat_tbs;
GRANT create session, recovery_catalog_owner TO repoomega#;
GRANT execute ON dbms_stats TO repoomega#; -- for class only
Duplicate database for Standby on omega2
SQL> conn / as sysdba
SQL> startup nomount;
$ rman target sys/oracle1@proda auxiliary /
RMAN> duplicate target database for standby nofilenamecheck;
On both servers create a password file
$ cd $ORACLE_HOME/dbs
$ ls -la
orapwd file=orapwprodb password=oracle1
set linesize 121
col name format a30
col value format a50
SELECT name, value
WHERE name LIKE '%remote_login%';
Step 3: Configure Standby Redo Logs
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database.
However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database.
Use the following equation to determine an appropriate number of standby redo log file groups:
(max. number of logfiles for each thread + 1) * max. number of threads
Using this equation reduces the likelihood that the primary instance's log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database.
For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database.
By default Oracle has 1 thread with three log files so: (3+1) * 1 = 4
Active Data Guard isn't a type of standby. It is the option that allows you to open a physical standby read only and restart the apply.
It also includes the right to turn on block change tracking. In 11.2 it also includes automatically repairing corrupt blocks. This option requires that your purchase an additional license.
Steps to convert a physical standby into an Active Data Guard standby
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.
A snapshot standby database receives and archives, but does not apply, redo data from a primary database.
Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.
A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received.
Local updates to the snapshot standby database will cause additional divergence.
The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.
A snapshot standby database provides disaster recovery and data protection benefits that are similar to those of a physical standby database.
Snapshot standby databases are best used in scenarios where the benefit of having a temporary, updatable snapshot of the primary database justifies increased time to recover from primary database failures.
Snapshot standby does not require any extra licensing as it is part of Data Guard in the EE license.
Steps to convert a physical standby into a Snapshot standby
-- on the physical standby stop the recovery process
alter database recover managed standby database cancel;
-- if RAC shut down all but one instance the database should now be mounted but not open. Open the database in read-only mode
alter database open read only;
-- verify that a fast recovery area has been configured
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Convert a snapshot to physical standby
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Related SQL Statements
Examine Database Characteristics
SELECT inst_id, open_resetlogs, open_mode, protection_mode, protection_level
SELECT inst_id, remote_archive, activation#, switchover#, database_role
SELECT inst_id, archivelog_compression, switchover_status, dataguard_broker, guard_status
SELECT inst_id, supplemental_log_data_min, supplemental_log_data_pk,
col name format a20
col db_unique_name format a20
SELECT inst_id, current_scn, db_unique_name, name, standby_became_primary_scn
col fs_failover_observer_host format a20
SELECT fs_failover_status, fs_failover_current_target,
Data Guard Monitoring
col hostname format a30
SELECT db_name, hostname, log_archived, log_applied, applied_time,
log_archived-log_applied AS GAP
SELECT name AS DB_NAME
SELECT UPPER(SUBSTR(host_name, 1, (DECODE(INSTR(host_name,'.'), 0,
LENGTH(host_name), (INSTR(host_name,'.')-1))))) HOSTNAME
SELECT MAX(sequence#) AS LOG_ARCHIVED
WHERE dest_id = 1
AND archived = 'YES'),
SELECT MAX(sequence#) AS LOG_APPLIED
WHERE dest_id = 2
AND applied = 'YES'),
SELECT TO_CHAR(MAX(completion_time),'DD-MON/HH24:MI') AS APPLIED_TIME
WHERE dest_id = 2
AND applied = 'YES');