By Mouhamadou Diaw

With Oracle 18c database nologging has been extended with two new modes: Standby Nologging for Load Performance and Standby Nologging for Data Availability. These modes provide better support for use in an Oracle Active Data Guard environment without significantly increasing the amount of redo generated. In the documentation we can find following definitions:

FORCE LOGGING mode prevents any load operation from being performed in a nonlogged manner. This can slow down the load process because the loaded data must be copied into the redo logs.

STANDBY NOLOGGING FOR DATA AVAILABILITY mode causes the load operation to send the loaded data to each standby through its own connection to the standby. The commit is delayed until all the standbys have applied the data as part of running managed recovery in an Active Data Guard environment.

STANDBY NOLOGGING FOR LOAD PERFORMANCE is similar to the previous mode except that the loading process can stop sending the data to the standbys if the network cannot keep up with the speed at which data is being loaded to the primary. In this mode it is possible that the standbys may have missing data, but each standby automatically fetches the data from the primary as a normal part of running managed recovery in an Active Data Guard environment.

In this Blog I am doing a test with the mode STANDBY NOLOGGING FOR LOAD PERFORMANCE. I am using two virtual machines.
This mode is enabled in the primary database using following command.

1
2
3
4
5
6
7
8
9
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;
Database altered.
SQL> select db_unique_name,force_logging from v$database;
DB_UNIQUE_NAME                 FORCE_LOGGING
------------------------------ ---------------------------------------
CONT18C_SITE                   STANDBY NOLOGGING FOR LOAD PERFORMANCE

And then we build a Data Guard environment (steps not shown). Below the configuration of the Data Guard.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DGMGRL> show configuration;
Configuration - CONT18C_DR
  Protection Mode: MaxPerformance
  Members:
  CONT18C_SITE  - Primary database
    CONT18C_SITE1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 3 seconds ago)
DGMGRL>

Now that the Data Guard is build, let’s do some nologging operation in the primary and let’s see if there are replicated on the standby

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> create tablespace TBS_NOLOG datafile '/u01/app/oracle/oradata/CONT18C/PDB1/tbs_nolog01.dbf' size 5M nologging;
Tablespace created.
SQL> create table testnlog nologging tablespace TBS_NOLOG as select * from hr.EMPLOYEES;
Table created.
SQL>

In the standby when we select the query we got following errors

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> select db_unique_name,force_logging,open_mode from v$database;
DB_UNIQUE_NAME  FORCE_LOGGING                           OPEN_MODE
--------------- --------------------------------------- --------------------
CONT18C_SITE1   STANDBY NOLOGGING FOR LOAD PERFORMANCE  READ ONLY WITH APPLY
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> select count(*) from testnlog;
select count(*) from testnlog
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 13, block # 163)
ORA-01110: data file 13: '/u01/app/oracle/oradata/CONT18C/PDB1/tbs_nolog01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

Why the replication did not work. In fact in this licencing document https://docs.oracle.com/en/database/oracle/oracle-database/18/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87

We can verify that the feature Oracle Data Guard—Automatic Correction of Non-logged Blocks at a Data Guard Standby Database is available on Oracle Database Enterprise Edition on Engineered Systems (EE_ES) and in the Cloud.

So let’s simulate an Exadata using the hidden parameter “_exadata_feature_on” on both servers (do not in production).

1
2
3
SQL> alter system set "_exadata_feature_on"=true scope=spfile;
System altered.

After both databases restart let’s ty again
On the primary database

1
2
3
4
5
6
7
8
9
SQL> drop table testnlog;
Table dropped.
SQL> create table testnlog nologging tablespace TBS_NOLOG as select * from hr.EMPLOYEES;
Table created.
SQL>

On the standby, we can see that nologged data are now replicated

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> select db_unique_name,force_logging,open_mode from v$database;
DB_UNIQUE_NAME  FORCE_LOGGING                           OPEN_MODE
--------------- --------------------------------------- --------------------
CONT18C_SITE1   STANDBY NOLOGGING FOR LOAD PERFORMANCE  READ ONLY WITH APPLY
SQL>
SQL> select count(*) from testnlog;
  COUNT(*)
----------
       107
SQL>

Conclusion :
With Oracle 18c, we can now configure database with no-logging mode. But this feature is only supported on following editions :

Oracle Database Enterprise Edition on Engineered Systems On-Premises EE-ES Oracle Database Enterprise Edition software installed on an on-premises engineered system (Oracle Exadata Database Machine or Oracle Database Appliance).
Oracle Database Cloud Service Enterprise Edition Cloud DBCS EE Includes Oracle Database Enterprise Edition software.
Oracle Database Cloud Service Enterprise Edition – High Performance Cloud DBCS EE-HP Includes Oracle Database Enterprise Edition software plus many Oracle Database options and Oracle management packs.
Oracle Database Cloud Service Enterprise Edition – Extreme Performance Cloud DBCS EE-EP Includes Oracle Database Enterprise Edition software plus all Oracle Database options and Oracle management packs that are appropriate for use in Oracle Database Cloud Service.
Oracle Database Exadata Cloud Service Cloud ExaCS Includes Oracle Database Enterprise Edition software plus all Oracle Database options and Oracle management packs that are appropriate for use in Oracle Database Exadata Cloud Service.

The licensing policies for ExaCS also apply to Oracle Database Exadata Cloud at Customer.
Post Views: 1,396
Thumbnail [60x60]
by
Oracle Team