Wednesday, June 27, 2007

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.

11 comments :

Peter McLarty said...

Hi David
This also has implications for backups and database cloning with RMAN. It would have been nice if the nologging had been an option, or was more heavily flagged.

Anonymous said...

So..David, are you saying that the PeopleSoft applications will not run on a Logical Standby Database?

David Kurtz said...

PeopleSoft will run on a physical standby, but you must force logging, otherwise some objects in your secondary database could be corrupted.
Similarly, you can use logical standby, but unless you force logging, you may not replicate all data to the secondary database. Indexes created NOLOGGING are not going to be a problem because the DML is reapplied, but any use of INSERT /*+APPEND NOLOGGING*/ might be.

Anonymous said...

Hi David,

You say in one of your comments that PeopleSoft CAN run on a Physical Standby. How would you go about doing that? A physical standby database can be in one of two states. 1. Open read only or 2. mounted (active recovery)

My understanding is that a PeopleSoft app needs to be able to write to the database in order to run.

Do you also happen to have any metrics on how much of a hit you take by forcing logging?

Thanks
Raji (raji at ucsc dot edu)

David Kurtz said...

Apologies, I didn't phrase that very well. I meant whether you could the standby having made it the primary. You are quite right. If you only open the database read-only, you will not be able to run the PeopleSoft application.

Anonymous said...

I have a Peoplesoft 8.1.7 database that is being kept around for legacy reasons and wanted to know if there is a way to make the database or application read-only... I tried setting the DB to Read-only only to find that PS will not run.

Anonymous said...

Hi, David:

I see some objects outside of Peoplesoft product schema have NOLOGGING there in a Peoplesoft database (like some of SYS or DBSNMP tables). If I use FORCE LOGGING option, would it impact the system level operation?

Michael Liang

David Kurtz said...

The NOLOGGING option at object level only applies to certain operations. Most DML will still be logged.
In my 10g database, all but three of the NOLOGGING tables are also temporary. Redo is not generated for DML on Global Temporary tables.
FORCE LOGGING is a database wide setting, so I would expect it to apply also to these three tables.

Li Li 李力 said...

Hi, David,

From Oracle documentation, Logical Standby doesn't support certain data types (collections, User-defined types, etc). Does PeopleSoft use any of these types? It doesn't support certain DDLs. I am wondering if this would prevent us from implementing a logical standby for a peoplesoft database. Your comment would be greatly appreciated. Thanks!

David Kurtz said...

PeopleSoft does not use collections or any user-defined data types when running on Oracle RDBMS. I don't think it issues any unsupported DDLs. I see no reason why you couldn't implement logical standby for a PeopleSoft system.

Unknown said...

Hi David -

We have a Finance 9.0 running on Logical Standby database. At times we have skipped UPDATES on TAO tables to mitigate a SQL Appy stuck situtation and as approved by functional team. However we wanted to undertand the impact of this on the Application tables that load data by using these TAO tables. Users have not reported any issues , however being a pro-active DBAs we wanted to understand if this causes any bad data on the replicated site.

Thanks

Ankur Shah