In today’s article we will learn to Changing Data Protection Mode From SQLPLUS.
1. We learn whether Data Guard Broker is used or not.
|
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 |
2. We query the Redo Transport modes of the Databases to see the changes, both after changing the Protection Mode and after ENABLE the Configuration.
|
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: 72.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 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 query the LOG_ARCHIVE_DEST_n parameters to see the changes both after changing the Protection Mode and after ENABLE the Configuration.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[Primary-1] SQL> column name format a30 [Primary-1] SQL> column value format a94 [Primary-1] SQL> set linesize 9000 [Primary-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary log_archive_dest_2 service="standby", LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_con nections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(all_logfiles,primary _role) log_archive_dest_3 service="logical", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_ connections=1 reopen=300 db_unique_name="logical" net_timeout=30, valid_for=(all_logfiles,prim ary_role) |
|
1 2 3 4 5 6 7 |
[Physical-1] SQL> column name format a30 [Physical-1] SQL> column value format a94 [Physical-1] SQL> set linesize 9000 [Physical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby |
|
1 2 3 4 5 6 7 8 |
[Logical-1] SQL> column name format a30 [Logical-1] SQL> column value format a94 [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> select name, value from v$parameter where lower(name) like '%log_archive_dest%' and lower(name) not like '%log_archive_dest_state%' and value is not null; NAME VALUE ------------------------------ ---------------------------------------------------------------------------------------------- log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logical |
4. We DISABLE the configuration.
|
1 2 |
DGMGRL> disable configuration Disabled. |
5. We update the LOG_ARCHIVE_DEST_n parameter in accordance with the Maximum Protection to the Logical Standby Database.
|
1 2 3 |
[Primary-1] SQL> alter system set log_archive_dest_3='service="logical", LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="logical", valid_for=(all_logfiles,primary_role)' scope=both sid='*'; System altered. |
6. We set the Data Protection Modes of the Standby Databases to Maximum Protection.
|
1 2 3 |
[Primary-1] SQL> alter database set standby database to maximize protection; Database altered. |
7. We move on to post-change controls.
We question whether the Protection Modes have changed.
|
1 2 3 4 5 |
[Primary-1] SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PROTECTION |
|
1 2 3 4 5 |
[Physical-1] SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PROTECTION |
|
1 2 3 4 5 |
[Logical-1] SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PROTECTION |
We’re looking for lag.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[Physical-1] SQL> set linesize 9000 [Physical-1] SQL> column name format a25 [Physical-1] SQL> column value format a20 [Physical-1] SQL> column time_computed format a25 [Physical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:00:00 01/17/2017 14:43:08 apply lag +00 00:00:00 01/17/2017 14:43:08 apply finish time +00 00:00:00.000 01/17/2017 14:43:08 estimated startup time 29 01/17/2017 14:43:08 |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[Logical-1] SQL> set linesize 9000 [Logical-1] SQL> column name format a25 [Logical-1] SQL> column value format a20 [Logical-1] SQL> column time_computed format a25 [Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:00:00 01/17/2017 14:42:39 apply lag +00 00:00:08 01/17/2017 14:42:39 apply finish time 01/17/2017 14:42:39 estimated startup time 25 01/17/2017 14:42:39 |
Although it is seen that there is no lag, an existing table is deleted and it is seen whether the transactions are reflected on the Standby side.
|
1 2 3 4 5 6 7 8 9 |
[Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK LOCATIONS_YEDEK REGIONS_YEDEK DEPARTMENTS_YEDEK JOB_HISTORY_YEDEK |
|
1 2 3 4 5 6 7 8 9 |
[Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK LOCATIONS_YEDEK REGIONS_YEDEK DEPARTMENTS_YEDEK JOB_HISTORY_YEDEK |
|
1 2 3 4 5 6 7 8 9 |
[Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK EMPLOYEES_YEDEK LOCATIONS_YEDEK DEPARTMENTS_YEDEK JOB_HISTORY_YEDEK |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[Primary-1] SQL> drop table test.JOB_HISTORY_YEDEK; Table dropped. [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK LOCATIONS_YEDEK REGIONS_YEDEK DEPARTMENTS_YEDEK |
|
1 2 3 4 5 6 7 8 |
[Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK LOCATIONS_YEDEK REGIONS_YEDEK DEPARTMENTS_YEDEK |
|
1 2 3 4 5 6 7 8 |
[Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK EMPLOYEES_YEDEK LOCATIONS_YEDEK DEPARTMENTS_YEDEK |
8. The configuration is enabled and it is seen that all changes are back to the settings before the configuration was disabled.
