Upgrading the Protection Mode and Downgrading the Protection Mode

Upgrading the Protection Mode
Use the following steps to upgrade the protection mode from the default of Maximum Performance.


Identify the current configuration:


Primary Database (TNS Service Name): TESTDB_VMLINUX3.IDEVELOPMENT.INFO
Standby Database (TNS Service Name): TESTDB_VMLINUX4.IDEVELOPMENT.INFO
Current Protection Mode: Maximum Performance mode using ARCH

Both of the higher protection modes (Maximum Availability and Maximum Protection) require the use of standby redo logs on the destination standby database. If standby redo logs do not exist for the standby database, create them now.

[Connect to the standby database]

SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba

[Verify if using oracle managed files]

SQL> select value from v$parameter where name = 'db_create_online_log_dest_1';

VALUE
-------------------
/u02/oradata/TESTDB

[Do any standby redo logs exist on the standby database?]

SQL> select group#, bytes, 'online' as type from v$log
2 union
3 select group#, bytes, 'standby' as type from v$standby_log
4 order by 1;

GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE

[Need to first cancel Managed Recovery mode if it is running]

SQL> alter database recover managed standby database cancel;

[Create appropriate number standby redo log files on the standby database]

SQL> alter database add standby logfile group 4 size 100m;
SQL> alter database add standby logfile group 5 size 100m;
SQL> alter database add standby logfile group 6 size 100m;
SQL> alter database add standby logfile group 7 size 100m;

[Put standby database back into Managed Recovery mode]

SQL> alter database recover managed standby database disconnect from session;

[Verify new standby redo logs on standby database]

SQL> select group#, bytes, 'online' as type from v$log
2 union
3 select group#, bytes, 'standby' as type from v$standby_log
4 order by 1;

GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE
4 104857600 STANDBY
5 104857600 STANDBY
6 104857600 STANDBY
7 104857600 STANDBY



Ensure that the attributes for LOG_ARCHIVE_DEST_n are configured on the primary instance to support the desired protection mode. Also note that the destination standby database should be enabled to support the target protection mode as documented in this article.

For the mode of Maximum Protection, the standby database must be up and mounted!




Set the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to support the required protection mode:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba

[Configure log transport services to support the desired protection mode]

SQL> alter system set log_archive_dest_2='service=testdb_vmlinux4.idevelopment.info LGWR SYNC AFFIRM';
SQL> alter system set log_archive_dest_state_2=enable;



The primary database will need to be closed and then placed in the MOUNT stage.

SQL> shutdown immediate
SQL> startup mount



On the primary database, change the protection mode and open the database:

For Maximum Protection mode:

SQL> alter database set standby database to maximize protection;
SQL> alter database open;



For Maximum Availability mode:

SQL> alter database set standby database to maximize availability;
SQL> alter database open;



Query the data dictionary on the primary database to verify the new protection mode:

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE
------------------- ------------------- --------------
MAXIMUM PROTECTION MAXIMUM PROTECTION PRIMARY



(Optional) Although the steps in this section are optional, there are highly recommended so that the primary database can easily and quickly switchover to a standby role without the need for DBA intervention. For example, standby redo logs are only used on the physical standby database; however, creating and having standby redo logs ready to go on the physical primary database makes role transition much easier if the primary would every have to become the standby:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba

[Do any standby redo logs exist on the primary database?]

SQL> select group#, bytes, 'online' as type from v$log
2 union
3 select group#, bytes, 'standby' as type from v$standby_log
4 order by 1;

GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE

[Create appropriate number standby redo log files on the primary database]

SQL> alter database add standby logfile group 4 size 100m;
SQL> alter database add standby logfile group 5 size 100m;
SQL> alter database add standby logfile group 6 size 100m;
SQL> alter database add standby logfile group 7 size 100m;

[Verify new standby redo logs on primary database]

SQL> select group#, bytes, 'online' as type from v$log
2 union
3 select group#, bytes, 'standby' as type from v$standby_log
4 order by 1;

GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE
4 104857600 STANDBY
5 104857600 STANDBY
6 104857600 STANDBY
7 104857600 STANDBY



Also, on the standby database(s), configure the LOG_ARCHIVE_DEST_n parameter attributes so the Data Guard configuration can continue to operate in the new protection mode after a switchover:

[Connect to the standby database]

SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba

SQL> alter system set log_archive_dest_2='service=testdb_vmlinux3.idevelopment.info LGWR SYNC AFFIRM';
SQL> alter system set log_archive_dest_state_2=defer;



Downgrading the Protection Mode
Use the following steps to downgrade the protection mode from the current higher protection mode.


Identify the current configuration:


Primary Database (TNS Service Name): TESTDB_VMLINUX3.IDEVELOPMENT.INFO
Standby Database (TNS Service Name): TESTDB_VMLINUX4.IDEVELOPMENT.INFO
Current Protection Mode: Maximum protection using LGWR SYNC AFFIRM

Configure the attributes for LOG_ARCHIVE_DEST_n on the primary instance to support the new desired protection mode:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba

[Configure log transport services to support the desired protection mode]

SQL> alter system set log_archive_dest_2='service=testdb_vmlinux4.idevelopment.info ARCH';
SQL> alter system set log_archive_dest_state_2=enable;



Shutdown and mount the primary and standby database:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba
SQL> shutdown immediate
SQL> startup mount

[Connect to the standby database]

SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;



Downgrade the primary database to the desired protection mode:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba
[For Maximum Performance mode]

SQL> alter database set standby database to maximize performance;
[For Maximum Availability mode]

SQL> alter database set standby database to maximize availability;



Both of the higher protection modes (Maximum Availability and Maximum Protection) require the use of standby redo logs on the destination standby database. It you were to downgrade from Maximum Protection mode to Maximum Availability mode, you would need to keep the standby redo logs that exist for the standby database. If downgrading to Maximum Performance mode, you can drop the standby redo logs from the standby unless you are going to be configuring log transport services to use LGWR. For this example, I am going to be downgrading to maximum performance mode and using ARCH for log transport services and can therefore drop any standby redo logs from the standby database (and the primary if they exist):

[Connect to the standby database]

SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba

[Drop any standby redo logs from the standby database]

SQL> alter database drop standby logfile group 4;
SQL> alter database drop standby logfile group 5;
SQL> alter database drop standby logfile group 6;
SQL> alter database drop standby logfile group 7;



If standby redo logs exist on the primary and you are downgrading to Maximum Performance mode, they can be removed:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba

[Drop any standby redo logs from the primary database]

SQL> alter database drop standby logfile group 4;
SQL> alter database drop standby logfile group 5;
SQL> alter database drop standby logfile group 6;
SQL> alter database drop standby logfile group 7;



Put the standby database in managed recovery mode and open the primary database with the new protection mode enabled:

[Connect to the standby database]

SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba
SQL> alter database recover managed standby database disconnect from session;

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba
SQL> alter database open;



Query the data dictionary on the primary database to verify the new protection mode:

SQL> select protection_mode, protection_level, database_role from v$database;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE
-------------------- -------------------- --------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY

Different kind of protection modes Oracle offers in a Data Guard Environment

Maximum Protection—This mode offers the highest level of data protection. Data is synchronously transmitted to the standby database from the primary database and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode. If the last standby database configured in this mode becomes unavailable, processing stops on the primary database. This mode ensures no-data-loss.

Maximum Availability—This mode is similar to the maximum protection mode, including zero data loss. However, if a standby database becomes unavailable (for example, because of network connectivity problems), processing continues on the primary database. When the fault is corrected, the standby database is automatically resynchronized with the primary database.

Maximum Performance—This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode. In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database. The commit operation of the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database. If any standby destination becomes unavailable, processing continues on the primary database and there is little effect on primary database performance.