Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article we will talk about a problem about Maximum Protection Mode in DataGuard.
If the Physical Standby database cannot be accessed somehow while Maximum Protection mode is selected, a problem occurs in the operations made from the Primary.
Let’s Test
1. We check the current Data Guard Configuration.
1 2 3 4 5 6 7 8 9 10 11 12 13 | DGMGRL> show configuration Configuration - primary Protection Mode: MaxProtection Members: physical - Primary database primary - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 54 seconds ago) |
2. It is desirable to shut down the physical Standby database regularly.
1 2 | [Primary-01-03-2017 22:22-56] SQL> shu immediate; ORA-01154: database busy. Open, close, mount, and dismount not allowed now |
The reason we got this error is because the Protection mode is Maximum Protection mode.
3. We force-close the physical Standby database.
1 2 | [root@primary ~]# ps -ef | grep ora_smon | grep -v grep oracle 14514 1 0 16:01 ? 00:00:00 ora_smon_primary |
1 | [root@primary ~]# kill -9 14514 |
4. It is desired to create a new table in the primary database.
1 | [Physical-01-03-2017 22:22-53] SQL> create table test.locations_yedek as select * from hr.locations; |
At this stage, the database does not take any action and waits.
5. We open the Physical Standby database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [oracle@primary ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 1 22:26:43 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. [Primary-01-MAR-17] SQL> startup; ORACLE instance started. Total System Global Area 3472883712 bytes Fixed Size 2930272 bytes Variable Size 822086048 bytes Database Buffers 2634022912 bytes Redo Buffers 13844480 bytes Database mounted. Database opened. |
As soon as the physical Standby database is MOUNT, the table in the Primary database is created.
6. We check whether the table occurs in both databases.
1 2 3 4 5 6 7 8 9 10 11 12 | [Physical-01-03-2017 22:27-26] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- REGIONS_YEDEK JOBS_YEDEK EMPLOYEES_YEDEK JOBHISTORY_YEDEK DEPARTMENTS_YEDEK LOCATIONS_YEDEK 6 rows selected. |
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-01-03-2017 22:28-00] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- REGIONS_YEDEK JOBS_YEDEK EMPLOYEES_YEDEK JOBHISTORY_YEDEK DEPARTMENTS_YEDEK LOCATIONS_YEDEK 6 rows selected. |
