The nologging option is advantageous from the performance perspective, but from the recover perspective if it is not properly managed this will lead to sever errors. Nologging means no changes are recorded in the redo log files, so there is no way the information can be retrieved from the redo or archive mechanism in case of need.
In the following scenario an index is created using the NOLOGGING option.
The first step prepares the scenario. A demonstration table is created. It uses a conventional path to create the table using a Create Table As Select(CTAS) syntax (A). Then the index is created (B), this command utilizes the NOLOGGING
clause. This will only record information about the object creation. Data dictionary information is always recorded, but it won't record the changes on the data blocks, thus provoking data corruption.
An error is created on purpose (C). The datafile related to the USERS tablespace is removed, thus requiring a recovery process to be...