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 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.
