Oracle Data Guard Version 19.3 |
---|
General Information | |||||||||||||||||||||||||||||||
Library Note |
|
||||||||||||||||||||||||||||||
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. Larry 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) |
|||||||||||||||||||||||||||||||
Acronyms |
|
||||||||||||||||||||||||||||||
Data Dictionary Objects for Physical DataGuard |
|
||||||||||||||||||||||||||||||
Data Dictionary Objects for Logical DataGuard |
|
||||||||||||||||||||||||||||||
Docs |
|
||||||||||||||||||||||||||||||
Data Guard Broker | |||||||||||||||||||||||||||||||
Start Data Guard Manager | $ DGMGRL |
||||||||||||||||||||||||||||||
Add a standby database to the broker configuration | ADD DATABASE <database_name> [as connect identifier is <connect-identifier>]; |
||||||||||||||||||||||||||||||
DGMGRL> add database proda; |
|||||||||||||||||||||||||||||||
Connect to the database via the broker | CONNECT username[@connect-identifier] |
||||||||||||||||||||||||||||||
DGMGRL> connect sys/syspwd |
|||||||||||||||||||||||||||||||
Convert or revert a standby between physical and snapshot | CONVERT <db_unique_name> TO <SNAPSHOT | PHYSICAL> STANDBY; |
||||||||||||||||||||||||||||||
DGMGRL> CONVERT DATABASE prodb TO snapshot standby; |
|||||||||||||||||||||||||||||||
Create a broker configuration | CREATE CONFIGURATION <configuration-name> AS |
||||||||||||||||||||||||||||||
DGMGRL> CREATE CONFIGURATION 'dg_prod_cfg' AS |
|||||||||||||||||||||||||||||||
Disable a configuration | DISABLE CONFIGURATION; |
||||||||||||||||||||||||||||||
DGMGRL> DISABLE CONFIGURATION; |
|||||||||||||||||||||||||||||||
Disable a database | DISABLE DATABASE <database-name>; |
||||||||||||||||||||||||||||||
DGMGRL> DISABLE DATABASE prodb; |
|||||||||||||||||||||||||||||||
Disable Fast-Start Failover | DISABLE FAST_START FAILOVER [FORCE]; |
||||||||||||||||||||||||||||||
DGMGRL> DISABLE FAST_START FAILOVER FORCE; |
|||||||||||||||||||||||||||||||
Disable Fast-Start Failover Condition | DISABLE FAST_START FAILOVER CONDITION <value>; |
||||||||||||||||||||||||||||||
DGMGRL> DISABLE FAST_START FAILOVER CONDITION "corrupted controlfile"; |
|||||||||||||||||||||||||||||||
Edit a configuration property | EDIT CONFIGURATION SET PROPERTY <property_name> = <value>; |
||||||||||||||||||||||||||||||
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold=60; |
|||||||||||||||||||||||||||||||
Edit configuration (protection mode) | EDIT CONFIGURATION SET PROTECTION MODE AS <MAXPROTECTION | MAXAVAILABILITY | MAXPERFORMANCE>; |
||||||||||||||||||||||||||||||
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY; |
|||||||||||||||||||||||||||||||
Edit database property | EDIT DATABASE <database-name> SET PROPERTY=<value>; |
||||||||||||||||||||||||||||||
DGMGRL> EDIT DATABASE prodb SET PROPERTY='ArchiveLagTarget'=900; |
|||||||||||||||||||||||||||||||
Edit database rename | EDIT DATABASE <database-name> RENAME TO =<new-database-name>; |
||||||||||||||||||||||||||||||
DGMGRL> DISABLE DATABASE prodb; |
|||||||||||||||||||||||||||||||
Edit database state | EDIT DATABASE <database-name> SET STATE=<database state>
|
||||||||||||||||||||||||||||||
DGMGRL> EDIT DATABASE prodb SET STATE=apply-off; |
|||||||||||||||||||||||||||||||
Edit Instance | EDIT INSTANCE <instance-name> [ON DATABASE <database-name>] |
||||||||||||||||||||||||||||||
DGMGRL> EDIT INSTANCE 'prodb' ON DATABASE 'prodb' SET AUTO PFILE='initprodb.ora'; |
|||||||||||||||||||||||||||||||
Enable Configuration | ENABLE CONFIGURATION; |
||||||||||||||||||||||||||||||
DGMGRL> ENABLE CONFIGURATION; |
|||||||||||||||||||||||||||||||
Enable Database | ENABLE DATABASE <database-name>; |
||||||||||||||||||||||||||||||
DGMGRL> ENABLE DATABASE prodb; |
|||||||||||||||||||||||||||||||
Enable Fast_Start Failover | ENABLE FAST_START FAILOVER; |
||||||||||||||||||||||||||||||
DGMGRL> ENABLE FAST_START FAILOVER; |
|||||||||||||||||||||||||||||||
Enable Fast_Start Failover Condition | ENABLE FAST_START FAILOVER CONDITION <value>; |
||||||||||||||||||||||||||||||
DGMGRL> ENABLE FAST_START FAILOVER CONDITION "Corrupted Dictionary"; |
|||||||||||||||||||||||||||||||
Exit the Data Guard Manager program | EXIT; |
||||||||||||||||||||||||||||||
DGMGRL> EXIT; |
|||||||||||||||||||||||||||||||
Change a standby database to be the primary database | FAILOVER TO <database-name> [IMMEDIATE]; |
||||||||||||||||||||||||||||||
DGMGRL>FAILOVER TO prodb IMMEDIATE; |
|||||||||||||||||||||||||||||||
Display description and syntax for a command | HELP; |
||||||||||||||||||||||||||||||
DGMGRL> HELP; |
|||||||||||||||||||||||||||||||
Exit the program | QUIT; |
||||||||||||||||||||||||||||||
DGMGRL> QUIT; |
|||||||||||||||||||||||||||||||
Change a disabled database into a viable standby database | REINSTATE DATABASE <database_name>; |
||||||||||||||||||||||||||||||
DGMGRL>REINSTATE DATABASE prodb; |
|||||||||||||||||||||||||||||||
Comment (remark) to be ignored by DGMGRL | rem <remark_string>; |
||||||||||||||||||||||||||||||
DGMGRL>REM 'this is a comment'; |
|||||||||||||||||||||||||||||||
Removes config info, including all DB profiles and terminates management of all of associated databases | REMOVE CONFIGURATION [PRESERVE DESTINATIONS]; |
||||||||||||||||||||||||||||||
DGMGRL> SHOW CONFIGURATION; |
|||||||||||||||||||||||||||||||
Removes the specified standby database's profile from the broker configuration and terminates broker management of the standby database | REMOVE DATABASE <database_name> [PRESERVE DESTINATIONS]; |
||||||||||||||||||||||||||||||
DGMGRL> REMOVE DATABASE proda PRESERVE DESTINATIONS; |
|||||||||||||||||||||||||||||||
Removes the specified standby database's profile from the broker configuration and terminates broker management of the standby database | REMOVE INSTANCE <instance-name> [ON DATABASE <database-name>]; |
||||||||||||||||||||||||||||||
DGMGRL> SHOW CONFIGURATION; |
|||||||||||||||||||||||||||||||
Display information about a configuration, database, and/or instance | SHOW CONFIGURATION [VERBOSE | <property_name>]; |
||||||||||||||||||||||||||||||
DGMGRL> SHOW CONFIGURATION VERBOSE; |
|||||||||||||||||||||||||||||||
Display information about a database | SHOW DATABASE [VERBOSE] <database-name> [<property-name>]; |
||||||||||||||||||||||||||||||
DGMGRL> SHOW DATABASE VERBOSE; |
|||||||||||||||||||||||||||||||
Display information about a fast_start failover configuration | SHOW FAST_START FAILOVER; |
||||||||||||||||||||||||||||||
DGMGRL> SHOW FAST_START FAILOVER; |
|||||||||||||||||||||||||||||||
Display information about an instance | SHOW INSTANCE [VERBOSE] <instance-name> [ON DATABASE <database-name>]; |
||||||||||||||||||||||||||||||
DGMGRL> SHOW INSTANCE VERBOSE; |
|||||||||||||||||||||||||||||||
Shutdown a currently running Oracle instance | SHUTDOWN [<ABORT | IMMEDIATE | NORMAL>]; |
||||||||||||||||||||||||||||||
DGMGRL> SHUTDOWN IMMEDIATE; |
|||||||||||||||||||||||||||||||
Start Fast-Start Failover observer | START OBSERVER [FILE=observer_configuration_filename]; |
||||||||||||||||||||||||||||||
DGMGRL> START OBSERVER '/u01/oracle/admin/orabase/dgbroker/orabase_dgcfg'; |
|||||||||||||||||||||||||||||||
Start an Oracle database instance | STARTUP [FORCE] [RESTRICT] [PFILE=<filename>] |
||||||||||||||||||||||||||||||
DGMGRL> STARTUP; |
|||||||||||||||||||||||||||||||
Stop the Fast_Start Failover Observer | STOP OBSERVER; |
||||||||||||||||||||||||||||||
DGMGRL> STOP OBSERVER; |
|||||||||||||||||||||||||||||||
Switch roles between the primary database and a standby database | SWITCHOVER TO <database-name>; |
||||||||||||||||||||||||||||||
DGMGRL> SWITCHOVER TO prodc; |
|||||||||||||||||||||||||||||||
Grid Infrastructure Data Guard | |||||||||||||||||||||||||||||||
Role Swap On Physical Standby | srvctl modify database -d <physStandby> -s open -r primary |
||||||||||||||||||||||||||||||
srvctl modify database -d TSTC -s open -r primary |
|||||||||||||||||||||||||||||||
Role Swap On Primary | srvctl modify database -d <Primary> -s mount -r physical_standby |
||||||||||||||||||||||||||||||
srvctl modify database -d TSTA -s open -r physical_standby |
|||||||||||||||||||||||||||||||
12cR1 New Features | |||||||||||||||||||||||||||||||
Far Sync | A Far Sync instance is a remote Data Guard destination that accepts redo from the primary database and then ships that redo to other members of the Data Guard configuration. Manages a control file, receives redo into standby redo logs (SRLs), and archives those SRLs to local archived redo logs. A Far Sync instance does not have user data files, cannot be opened for access, cannot run redo apply, and can never function in the primary role or be converted to any type of standby database. Far Sync instances are part of the Active Data Guard Far Sync feature, which requires an Active Data Guard license. |
||||||||||||||||||||||||||||||
TBD | |||||||||||||||||||||||||||||||
FastSync | LOG_ARCHIVE_DEST_n attributes SYNC and NOAFFIRM can be used together enabling a synchronous standby to be deployed at a greater distance from the primary site. | ||||||||||||||||||||||||||||||
TBD | |||||||||||||||||||||||||||||||
Operating System Installation | |||||||||||||||||||||||||||||||
As root: Create DBA Group and Oracle User (both servers) | $ /usr/sbin/groupadd oinstall |
||||||||||||||||||||||||||||||
As root: Create Installation Directories (both servers) | mkdir -p /app/oracle |
||||||||||||||||||||||||||||||
As root: Alter Kernel Parameters in sysctl.conf by appending the highlighted text (both servers) |
cd /etc |
||||||||||||||||||||||||||||||
As root: Set Shell Limits (both servers) |
cd /etc/security |
||||||||||||||||||||||||||||||
As root: Change Default Profile (both servers) |
cd /etc |
||||||||||||||||||||||||||||||
As root: Append servers to hosts file (both servers) |
cd /etc |
||||||||||||||||||||||||||||||
Modify .bashrc (both servers) | cd /home/oracle |
||||||||||||||||||||||||||||||
initproda.ora | proda.__db_cache_size=687865856 |
||||||||||||||||||||||||||||||
initprodb.ora | prodb.__db_cache_size=687865856 |
||||||||||||||||||||||||||||||
On omega1 create a password file | $ cd $ORACLE_HOME/dbs |
||||||||||||||||||||||||||||||
On omega2 create a password file | $ cd $ORACLE_HOME/dbs |
||||||||||||||||||||||||||||||
omega1: listener.ora | -- Note: SDU needs to be a multiple of MTU |
||||||||||||||||||||||||||||||
omega2: listener.ora | # listener.ora Network Configuration File: /app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora |
||||||||||||||||||||||||||||||
both servers: sqlnet.ora | NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) |
||||||||||||||||||||||||||||||
both servers: tnsnames.ora | # tnsnames.ora Network Configuration File: /app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora |
||||||||||||||||||||||||||||||
Physical Data Guard | |||||||||||||||||||||||||||||||
dg_proda.ora Note: Set net_timeout between 10-30 sec. |
remote_login_passwordfile='EXCLUSIVE' |
||||||||||||||||||||||||||||||
dg_prodb.ora | remote_login_passwordfile='EXCLUSIVE' |
||||||||||||||||||||||||||||||
Prepare omega1 for backup | SQL> startup mount; |
||||||||||||||||||||||||||||||
Create RMAN catalog and backup omega1 | -- as oracle on bigdog |
||||||||||||||||||||||||||||||
Duplicate database for Standby on omega2 | SQL> conn / as sysdba |
||||||||||||||||||||||||||||||
On both servers create a password file | $ cd $ORACLE_HOME/dbs |
||||||||||||||||||||||||||||||
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 ALTER DATABASE ADD STANDBY LOGFILE ( |
||||||||||||||||||||||||||||||
Physical Standby Commands | |||||||||||||||||||||||||||||||
Complete applying all available redo in preparation for a failover. Use the FINISH clause only in the event of primary database failure. | ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE [FINISH]; |
||||||||||||||||||||||||||||||
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE FINISH; |
|||||||||||||||||||||||||||||||
Create standby logfile | ALTER DATABASE ADD STANDBY LOGFILE '<path_and_file_name>' SIZE <integer> <M | G>; |
||||||||||||||||||||||||||||||
ALTER DATABASE ADD STANDBY LOGFILE '/app/oracle/product/dbs/stdbylog01a.log' SIZE 50G; |
|||||||||||||||||||||||||||||||
Switchover of a primary database to standby | ALTER DATABASE <PREPARE | COMMIT> TO SWITCHOVER TO PHYSICAL STANDBY [WITH SESSION SHUTDOWN]; |
||||||||||||||||||||||||||||||
SELECT switchover_status FROM v$database; |
|||||||||||||||||||||||||||||||
Convert a primary to a standby | ALTER DATABASE CONVERT TO <PHYSICAL | SNAPSHOT> STANDBY; |
||||||||||||||||||||||||||||||
ALTER DATABASE CONVERT TO PHYSICAL STANDBY; |
|||||||||||||||||||||||||||||||
Start redo apply as a foreground process | ALTER DATABASE RECOVER MANAGED STANDBY DATABASE; |
||||||||||||||||||||||||||||||
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE; |
|||||||||||||||||||||||||||||||
Stop redo apply | ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; |
||||||||||||||||||||||||||||||
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; |
|||||||||||||||||||||||||||||||
Start redo apply as a background process | ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT [FROM SESSION]; |
||||||||||||||||||||||||||||||
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; |
|||||||||||||||||||||||||||||||
Cancel an apply time delay | ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY; |
||||||||||||||||||||||||||||||
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY; |
|||||||||||||||||||||||||||||||
Start real-time apply as a foreground process | ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE; |
||||||||||||||||||||||||||||||
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE; |
|||||||||||||||||||||||||||||||
Start real-time apply as a background process | ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE [DISCONNECT FROM SESSION]; |
||||||||||||||||||||||||||||||
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; |
|||||||||||||||||||||||||||||||
From the standby manually register log files from a failed primary | ALTER DATABASE REGISTER [OR REPLACE] [PHYSICAL] LOGFILE <file_path_and_name> |
||||||||||||||||||||||||||||||
ALTER DATABASE REGISTER LOGFILE '/u01/dest1/stdbylog01a.log'; |
|||||||||||||||||||||||||||||||
Change the protection mode | ALTER DATABASE SET STANDBY DATABASE TO <AVAILABILITY | PERFORMANCE | PROCTECTION>; |
||||||||||||||||||||||||||||||
SHUTDOWN IMMEDIATE; |
|||||||||||||||||||||||||||||||
Switchover of a primary database to standby | ALTER DATABASE <PREPARE | COMMIT> TO SWITCHOVER TO ....; |
||||||||||||||||||||||||||||||
SELECT switchover_status FROM V$database; |
|||||||||||||||||||||||||||||||
At failover send unsent redo to the standby | ALTER SYSTEM FLUSH REDO SQL |
||||||||||||||||||||||||||||||
ALTER SYSTEM FLUSH REDO SQL; |
|||||||||||||||||||||||||||||||
Active Data Guard | |||||||||||||||||||||||||||||||
Description | 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 | -- stop redo application |
||||||||||||||||||||||||||||||
Snapshot Standby | |||||||||||||||||||||||||||||||
Description | 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 |
||||||||||||||||||||||||||||||
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 |
||||||||||||||||||||||||||||||
Data Guard Monitoring | col hostname format a30 |
||||||||||||||||||||||||||||||
Switchover Demo | SELECT name, current_scn, guard_status, switchover_status, database_role, checkpoint_change#, archive_change#,
archivelog_change#, db_unique_name, platform_name |
||||||||||||||||||||||||||||||
Diagnose Logical Data Guard Failures | -- determine replication lag |
This site is maintained by Dan Morgan. Last Updated: | This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved | |||||||||
|
||||||||||