Oracle Enterprise GeoDatabase force logging vs nologging

128
1
05-22-2019 06:44 PM
Highlighted
New Contributor II

Our Enterprise GeoDatabase running on Oracle has been configured at the database level to force logging. ArcSDE DBTUNE defines many tables and indexes by default to be NOLOGGING for performance reasons. Is force logging not a good approach for geodatabases?

Some context : Our production database runs in archivelog mode. When we do a DB restore of our non-production database from RMAN backups of our production databases, these tables/indexes defined as NOLOGGING in DBTUNE run into block level corruption while the logs are applied after restore. The DBA recommends having force logging turned ON so we don't run into issues while applying the archive logs. Ref : https://chenguangblog.wordpress.com/tag/nologging/

Ref:

https://resources.arcgis.com/en/help/main/10.1/index.html#/What_is_the_DBTUNE_table/002n0000001n000000/ https://www.orafaq.com/wiki/Nologging_and_force_logging

Reply
0 Kudos
1 Reply
Highlighted
New Contributor III

From ArcSDE Configuration and Tuning Guide for Oracle:

"ESRI recommends that you create your indexes with NOLOGGING. Doing so will avoid logging the changes made to the indexes in the Oracle redo log files. Although the index cannot be recovered from the archive log in the event that you should lose the data file the index is stored in, you can easily re-create the index using the ALTER INDEX <index_name> REBUILD command. Therefore, ESRI believes that the ease at which an index may be regenerated outweighs the need to log the changes in the event of a disk failure."

Since this is just a recommendation, if you don't see any significant impact on the Geodatabase's performance by setting the parameter to LOGGING (You may want to give this a try on a testing environment) and your redo log can accommodate that information, I believe you can do so.

Regards,

Leo