Data Guard Implications of NOLOGGING operations from PeopleTools 8.48

Stuff changes. I keep finding more changes in PT8.48.

From PeopleTools 8.48, PeopleSoft builds all indexes with the NOLOGGING option, and thne it alters them to logged objects. This is done to reduce redo generation during the index build process. It can save time by reducing redo, and can be useful in development environments.

However, this has implications when running Oracle Data Guard. If an operation is not logged on the primary, it will not be replicated to the standby databases.

Here is a simple test. We will create a table on the primary database, but the index will be created NOLOGGING, which exactly what PeopleSoft does. I have deliberately set the database to allow NOLOGGING operations

ALTER DATABASE NO FORCE LOGGING;

CREATE TABLE t (a number);

INSERT INTO t 
SELECT rownum FROM dba_objects 
WHERE ROWNUM <= 1000;  

CREATE UNIQUE INDEX t_idx ON t (a) NOLOGGING;    

ALTER INDEX t_idx LOGGING;      

ALTER SYSTEM SWITCH LOGFILE;

Switching the log file forces the changes to be replicated to the secondary database. Now open the secondary database in read only:

SELECT /*+ FULL(t)*/ COUNT(a) FROM t;

 COUNT(A)
----------
     1000

So there are 1000 rows in the table, but if I force Oracle to count the rows in the index using a hint I get a corrupt block error:

SELECT /*+ INDEX_FFS(t t_idx)*/ COUNT(a) FROM t
                                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 134419)
ORA-01110: data file 1: '/u01/oradata/hcm89/system01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

This could happen every time you build an index using a script generated by the application designer. The status on the corrupt index is still VALID, so you would have no way of knowing whether this problem existed before you open your standby and run on it (because you can't run PeopleSoft on a standby database in read only mode).

So how can you prevent this from happening in the first place?

You could alter the DDL models to remove the NOLOGGING option from the CREATE INDEX DDL model. But the same argument applies to any NOLOGGING operation performed in the database. You are likely to get corrupt objects in your standby database. Although, I know of no other NOLOGGING operations in a vanilla PeopleSoft database, if any are introduced during development then DBAs are unlikely to be able to change them.

It was for exactly this reason that Oracle provided the ability to force NOLOGGING operations to generate redo information. It is set with the following command:

ALTER DATABASE FORCE LOGGING;

Now the database will log all operations regardless and all objects and operations will be fully replicated to the standby database. However, nothing is free. NOLOGGING is often used to improve performance by reducing redo, for example when populating working storage tables which will never need to be recovered. These performance improvements will be lost when logging is forced.