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 |
