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