Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article we will learn to Changing Data Protection Mode With DGMGRL.
1. We are querying the current Protection Mode.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DGMGRL> show configuration Configuration - Broker_Configuration Protection Mode: MaxPerformance Databases: primary - Primary database standby - Physical standby database logical - Logical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
2. We question the Transport Modes on the Primary and Standby sides.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | DGMGRL> show database verbose primary Database - primary Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): primary1 primary2 Properties: DGConnectIdentifier = 'primary' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' LsbyASkipTxnCfgPr = '0,0,0' LsbyDSkipTxnCfgPr = '0,0,0' LsbyASkipCfgPr = '' LsbyDSkipCfgPr = '' LsbyASkipErrorCfgPr = '' LsbyDSkipErrorCfgPr = '' LsbyMaxEventsRecorded = '0' LsbyPreserveCommitOrder = '' LsbyRecordSkipErrors = '' LsbyRecordSkipDdl = '' LsbyRecordAppliedDdl = '' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '8' LogArchiveMinSucceedDest = '1' DbFileNameConvert = 'standby, primary' LogFileNameConvert = 'standby, primary' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' LsbyParameters = '(monitor)' LsbySkipTxnTable = '(monitor)' LsbySkipTable = '(monitor)' LsbyFailedTxnInfo = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) LsbyMaxSga(*) LsbyMaxServers(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | DGMGRL> show database verbose standby Database - standby Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 72.00 KByte/s Real Time Query: ON Instance(s): primary1 (apply instance) primary2 Properties: DGConnectIdentifier = 'standby' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '8' LogArchiveMinSucceedDest = '1' DbFileNameConvert = 'primary, standby' LogFileNameConvert = 'primary, standby' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | DGMGRL> show database verbose logical Database - logical Role: LOGICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 0 Byte/s Instance(s): primary1 primary2 (apply instance) Properties: DGConnectIdentifier = 'logical' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' LsbyASkipTxnCfgPr = '0,0,0' LsbyDSkipTxnCfgPr = '0,0,0' LsbyASkipCfgPr = '' LsbyDSkipCfgPr = '' LsbyASkipErrorCfgPr = '' LsbyDSkipErrorCfgPr = '' LsbyMaxEventsRecorded = '0' LsbyPreserveCommitOrder = '' LsbyRecordSkipErrors = '' LsbyRecordSkipDdl = '' LsbyRecordAppliedDdl = '' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '8' LogArchiveMinSucceedDest = '1' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' LsbyParameters = '(monitor)' LsbySkipTxnTable = '(monitor)' LsbySkipTable = '(monitor)' LsbyFailedTxnInfo = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) LsbyMaxSga(*) LsbyMaxServers(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS |
3. We change the Redo Transport modes of the Physical Standby and Primary databases.
1 2 3 4 5 | DGMGRL> edit database primary set property 'LogXptMode'='SYNC'; Property "LogXptMode" updated DGMGRL> edit database standby set property 'LogXptMode'='SYNC'; Property "LogXptMode" updated |
[Primary-1]——>ALERT LOG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | Tue Jan 17 13:47:05 2017 ALTER SYSTEM SET log_archive_dest_2='service="standby"','LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="standby" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH; ALTER SYSTEM SWITCH ALL LOGFILE start (primary1) Tue Jan 17 13:47:05 2017 NSS2 started with pid=53, OS id=5030 Tue Jan 17 13:47:08 2017 LGWR: Standby redo logfile selected for thread 1 sequence 582 for destination LOG_ARCHIVE_DEST_2 ALTER SYSTEM SWITCH ALL LOGFILE complete (primary1) Thread 1 advanced to log sequence 582 (LGWR switch) Current log# 2 seq# 582 mem# 0: +DATA/primary/onlinelog/group_2.262.932222811 Tue Jan 17 13:47:12 2017 LNS: Standby redo logfile selected for thread 1 sequence 582 for destination LOG_ARCHIVE_DEST_3 Tue Jan 17 13:47:13 2017 Archived Log entry 2314 added for thread 1 sequence 581 ID 0x697b7314 dest 1: Tue Jan 17 13:47:13 2017 ARC6: Archive log rejected (thread 1 sequence 581) at host 'standby' FAL[server, ARC6]: FAL archive failed, see trace file. ARCH: FAL archive failed. Archiver continuing ORACLE Instance primary1 - Archival Error. Archiver continuing. |
4. We set the Configuration’s Protection Mode to Maximum Availability.
1 2 | DGMGRL> edit configuration set protection mode as maxavailability; Succeeded. |
[Primary-1]——>ALERT LOG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | Tue Jan 17 13:49:59 2017 ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY ALTER SYSTEM ARCHIVE LOG Tue Jan 17 13:50:00 2017 Thread 1 cannot allocate new log, sequence 583 Checkpoint not complete Current log# 2 seq# 582 mem# 0: +DATA/primary/onlinelog/group_2.262.932222811 Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** LGWR: Standby redo logfile selected to archive thread 1 sequence 583 LGWR: Standby redo logfile selected for thread 1 sequence 583 for destination LOG_ARCHIVE_DEST_2 Thread 1 advanced to log sequence 583 (LGWR switch) Current log# 1 seq# 583 mem# 0: +DATA/primary/onlinelog/group_1.261.932222811 Tue Jan 17 13:50:05 2017 LNS: Standby redo logfile selected for thread 1 sequence 583 for destination LOG_ARCHIVE_DEST_3 Tue Jan 17 13:50:06 2017 Archived Log entry 2319 added for thread 1 sequence 582 ID 0x697b7314 dest 1: Tue Jan 17 13:50:25 2017 Thread 1 cannot allocate new log, sequence 584 Checkpoint not complete Current log# 1 seq# 583 mem# 0: +DATA/primary/onlinelog/group_1.261.932222811 Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED LGWR: Standby redo logfile selected to archive thread 1 sequence 584 LGWR: Standby redo logfile selected for thread 1 sequence 584 for destination LOG_ARCHIVE_DEST_2 Thread 1 advanced to log sequence 584 (LGWR switch) Current log# 2 seq# 584 mem# 0: +DATA/primary/onlinelog/group_2.262.932222811 Tue Jan 17 13:50:28 2017 Archived Log entry 2322 added for thread 1 sequence 583 ID 0x697b7314 dest 1: Tue Jan 17 13:50:28 2017 LNS: Standby redo logfile selected for thread 1 sequence 584 for destination LOG_ARCHIVE_DEST_3 |
5. We are querying Protection Mode and Transport Mode.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DGMGRL> show configuration Configuration - Broker_Configuration Protection Mode: MaxAvailability Databases: primary - Primary database standby - Physical standby database logical - Logical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | DGMGRL> show database verbose primary Database - primary Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): primary1 primary2 Properties: DGConnectIdentifier = 'primary' ObserverConnectIdentifier = '' LogXptMode = 'SYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' LsbyASkipTxnCfgPr = '0,0,0' LsbyDSkipTxnCfgPr = '0,0,0' LsbyASkipCfgPr = '' LsbyDSkipCfgPr = '' LsbyASkipErrorCfgPr = '' LsbyDSkipErrorCfgPr = '' LsbyMaxEventsRecorded = '0' LsbyPreserveCommitOrder = '' LsbyRecordSkipErrors = '' LsbyRecordSkipDdl = '' LsbyRecordAppliedDdl = '' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '8' LogArchiveMinSucceedDest = '1' DbFileNameConvert = 'standby, primary' LogFileNameConvert = 'standby, primary' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' LsbyParameters = '(monitor)' LsbySkipTxnTable = '(monitor)' LsbySkipTable = '(monitor)' LsbyFailedTxnInfo = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) LsbyMaxSga(*) LsbyMaxServers(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | DGMGRL> show database verbose standby Database - standby Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 552.00 KByte/s Real Time Query: ON Instance(s): primary1 (apply instance) primary2 Properties: DGConnectIdentifier = 'standby' ObserverConnectIdentifier = '' LogXptMode = 'SYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '8' LogArchiveMinSucceedDest = '1' DbFileNameConvert = 'primary, standby' LogFileNameConvert = 'primary, standby' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | DGMGRL> show database verbose logical Database - logical Role: LOGICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 4 seconds (computed 0 seconds ago) Apply Rate: 13.01 MByte/s Instance(s): primary1 primary2 (apply instance) Properties: DGConnectIdentifier = 'logical' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' LsbyASkipTxnCfgPr = '0,0,0' LsbyDSkipTxnCfgPr = '0,0,0' LsbyASkipCfgPr = '' LsbyDSkipCfgPr = '' LsbyASkipErrorCfgPr = '' LsbyDSkipErrorCfgPr = '' LsbyMaxEventsRecorded = '0' LsbyPreserveCommitOrder = '' LsbyRecordSkipErrors = '' LsbyRecordSkipDdl = '' LsbyRecordAppliedDdl = '' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '8' LogArchiveMinSucceedDest = '1' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' LsbyParameters = '(monitor)' LsbySkipTxnTable = '(monitor)' LsbySkipTable = '(monitor)' LsbyFailedTxnInfo = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) LsbyMaxSga(*) LsbyMaxServers(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS |
6. We are also querying Protection Mode from SQLPLUS.
1 2 3 4 5 | [Primary-1] SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM AVAILABILITY |
1 2 3 4 5 | [Physical-1] SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM AVAILABILITY |
1 2 3 4 5 | [Logical-1] SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE |
