Now we'll convert Data Guard's configuration from Maximum Performance to Maximum Protection and then to the Maximum Availability mode using SQL*Plus commands. At the end, we'll convert it back to the Maximum Performance mode.
ASYNC
redo transport, without standby redo logs, and does not use Real-Time Apply. We'll try to convert it to the Maximum Protection mode. Let's execute the conversion command in the primary database without any change in the configuration as follows:SQL> SELECT PROTECTION_MODE FROM V$DATABASE; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instance
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; Database altered. SQL> ALTER DATABASE OPEN; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 24904 Session ID: 113 Serial number: 3
LGWR: Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I/O LGWR: Minimum of 1 synchronous standby database required Errors in file /u01/app/oracle2/diag/rdbms/TURKEY_UN/TURKEY/trace/TURKEY_lgwr_24854.trc: ORA-16072: a minimum of one standby database destination is required
LOG_ARCHIVE_DEST_2
parameter, which is used for the physical standby database log transport, is defined with the ASYNC
attribute that is used for the Maximum Performance protection mode. In order to convert the database to Maximum Protection or Maximum Availability, we must change the ASYNC
attribute to SYNC
as follows:SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=INDIA LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INDIA_UN'; System altered.
We should also change the LOG_ARCHIVE_DEST_n
parameter, which is "VALID_FOR = PRIMARY_ROLE"
, in the standby database to the SYNC redo transport mode. If we don't, the protection mode will not operate after a switchover because ASYNC
cannot be used with the Maximum Protection mode. This step needs to be executed whenever changing the protection mode requires a redo transport mode change.
SQL> ALTER DATABASE OPEN; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 25062 Session ID: 113 Serial number: 3
ORA-16086: Redo data cannot be written to the standby redo log LGWR: Error 16086 verifying archivelog destination LOG_ARCHIVE_DEST_2 Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED LGWR: Error 16086 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'INDIA' LGWR: Continuing... LGWR: Minimum of 1 applicable standby database required Errors in file /u01/app/oracle2/diag/rdbms/TURKEY_UN/TURKEY/trace/TURKEY_lgwr_25020.trc: ORA-16072: a minimum of one standby database destination is required
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> alter database add standby logfile group 4 size 52428800; SQL> alter database add standby logfile group 5 size 52428800; SQL> alter database add standby logfile group 6 size 52428800; SQL> alter database add standby logfile group 7 size 52428800;
In Chapter 2, Configuring Oracle Data Guard Physical Standby Database, remember we mentioned that the standby redo log group number must be one more than that of the online redo log group number, and the size of standby redo logfiles must be the same as that of online redo logfiles.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered.
SQL> STARTUP MOUNT SQL> ALTER DATABASE OPEN; Database altered. SQL> SELECT PROTECTION_MODE FROM V$DATABASE; PROTECTION_MODE -------------------- MAXIMUM PROTECTION
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION RFS[5]: Assigned to RFS process 1086 RFS[5]: Identified database type as 'physical standby': Client is LGWR SYNC pid 21839 Primary database is in MAXIMUM PROTECTION mode Changing standby controlfile to MAXIMUM PROTECTION mode
SQL> SHUTDOWN IMMEDIATE ORA-01154: database busy. Open, close, mount, and dismount not allowed now
Attempt to shut down Standby Database Standby Database operating in NO DATA LOSS mode Detected primary database alive, shutdown primary first, shutdown aborted
SMON
process to simulate a failure on the standby database server as follows:$ ps -ef |grep smon_INDIA oracle 7064 1 0 Sep16 ? 00:00:00 ora_smon_INDIA $ kill -9 7064
kill
command. Now try modifying the primary database by inserting data into a table as shown in the following query:SQL> INSERT INTO HR.REGIONS VALUES (102,'TEST'), 1 row created. SQL> COMMIT;
commit
statement will wait and not be executed. At this stage, the primary database will not accept any change because of the Maximum Protection mode's characteristic. Then the instance will be terminated by LGWR
as shown in the following alert log lines:Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED LGWR: All standby destinations have failed ****************************************************** WARNING: All standby database destinations have failed WARNING: Instance shutdown required to protect primary ****************************************************** LGWR (ospid: 21839): terminating the instance due to error 16098 Instance terminated by LGWR, pid = 21839
Mount the standby database and start recovery at this stage.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; Database altered.
Primary database is in MAXIMUM AVAILABILITY mode Changing standby controlfile to MAXIMUM AVAILABILITY mode Standby controlfile consistent with primary
SQL> SHUTDOWN IMMEDIATE ORA-01109: database not open Database dismounted. ORACLE instance shut down.
SQL> INSERT INTO HR.REGIONS VALUES (102,'TEST'), 1 row created. SQL> COMMIT; Commit complete.
LOG_ARCHIVE_DEST_n
attribute to ASYNC
as shown in the following query:SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; Database altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=INDIA LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INDIA_UN'; System altered.
We've seen how to change the data protection mode of a Data Guard configuration using the SQL* Plus command line interface. If you didn't set up Data Guard broker or Cloud Control, this is the only way to change the protection mode.
Another way of performing protection mode changes in Data Guard is using Data Guard broker. If Data Guard broker was configured and being used, then it's recommended to use the broker in order to change the protection mode.