Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Time for action – changing the protection mode with SQL*Plus

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.

We have a physical standby configuration, which is in the Maximum Performance mode (by default) with 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
```

It's not possible to convert a standby in the Maximum Performance mode to the Maximum Protection and Maximum Availability modes when the primary database is open. We need to put the primary in a mount state in order to make this change. We can use use the following query:

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

We've restarted the primary database in the mount mode and changed the protection mode. However, when we tried to open it, we encountered an ORA-03113 error. We can see why the database raised this error in the alert logfile as follows:

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

The 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.
```
Tip
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
```

We encountered the same error. Let's check the alert log again, shown as follows:

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

In order to set Maximum Protection or Maximum Availability modes, we must create standby redo logfiles in the standby database. Stop Redo Apply, create standby redo logs and start Redo Apply again as Real-Time Apply on the standby database:
```
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;
```
Tip
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.
```

Start the primary database and query the data protection mode as follows:

SQL> STARTUP MOUNT
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM PROTECTION

We can see from the following code that the mode changes the information on the standby database alert log also:

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

Now try to shut down the standby database as shown in the following query:

SQL> SHUTDOWN IMMEDIATE
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

As you can see, it's not possible to shut down a standby database in the Maximum Protection mode if it's the only standby database alive. We'll see the following lines in the standby database alert log when we try to shut it down:
```
Attempt to shut down Standby Database
Standby Database operating in NO DATA LOSS mode
Detected primary database alive, shutdown primary first, shutdown aborted
```

Now kill the 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

The Oracle instance will be terminated in the standby database after the 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;
```

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

Now let's try to change the data protection mode to Maximum Availability as shown in the following query:
```
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.
```
It's possible to perform this protection mode change without putting the primary database in the mount state. We can see the change in the standby database alert log as follows:
```
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
```

Try to shut down the standby database as shown in the following query:

SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

It's possible to shut down the standby database in the Maximum Availability mode. It's also possible to modify the primary database when there is no standby alive, as shown in the following query:
```
SQL> INSERT INTO HR.REGIONS VALUES (102,'TEST'),

1 row created.

SQL> COMMIT;

Commit complete.
```

In this step, we'll change the protection mode back to Maximum Performance. Don't forget to set the 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.

What just happened?

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for Time for action – changing the protection mode with SQL*Plus

Create new playlist

Sign In

Sign Up

Time for action – changing the protection mode with SQL*Plus

Tip

Tip

What just happened?

Table of Contents for
Time for action – changing the protection mode with SQL*Plus