The price we pay for the speedup of the insert

The Great artical by uhesse

The outcome of NOLOGGING operations, should the affected datafiles get damaged before we manage to take a backup of them.

As you probably know, we are allowed to suppress the usual redo protocol generation for certain statements, especially for
CREATE TABLE AS SELECT, INSERT INTO … SELECT and CREATE INDEX. If we can take a backup of the related datafiles after these
operations, everything is ok. We simply saved time during the above mentioned operations. But what if the datafiles actually
get damaged?

Hands On

Oracle Data Guard – OTN Thread
NOLOGGING tables and recovery of database (and standby)

Even if you have tables with NOLOGGING minimal logging with respect to table structure changes are recorded, so in the case
of recovery, you can recover the database with nologging tables from the last full backup but data changes after the full
backup can not be recovered on the nologging tables.

You should avoid this situation by saying

SQL> alter database force logging;

on the Primary – best before creation of the Standby. Else you will lose the objects created with nologging in case of a failover.
If nologging operations have been done on the Primary already, but no damage there yet – better recreate the Standby after force
logging on the Primary.

1. So basically, if we are using Dataguard, NOLOGGING is not a good idea.
2. What actually happens there? does the standby is not maintained at all?
No. It is maintained.
3. only the NOLOGGING objects will be in a corrupt/unusable state?
4. Will the standby have the DDL changes on NOLOGGING objects?
5. Is it possible to find out such corrupt objects (if we are switching to the standby)?
You use them and get error messages.
6. Also, even if we are FORCE LOGGING, if we truncate a table or partition, will we brake the standby?
no. the truncate is reproduced on the standby.

How to Recover !!!!!
No way, still last valid datafile backup

13.4 Recovering After the NOLOGGING Clause Is Specified

In some SQL statements, the user has the option of specifying the NOLOGGING clause, which indicates that the database operation is not logged in the online redo log file. Even though the user specifies the clause, a redo record is still written to the online redo log file. However, there is no data associated with this record. This can result in log application or data access errors at the standby site and manual recovery might be required to resume applying log files.

To avoid these problems, Oracle recommends that you always specify the FORCE LOGGING clause in the CREATE DATABASE or ALTER DATABASE statements. See the Oracle Database Administrator’s Guide.

13.4.1 Recovery Steps for Logical Standby Databases

For logical standby databases, when SQL Apply encounters a redo record for an operation performed on an interesting table with the NOLOGGING clause, it stops with the following error: ORA-16211 unsupported record found in the archived redo log.

To recover after the NOLOGGING clause is specified, re-create one or more tables from the primary database, as described in Section 10.5.5.

In general, use of the NOLOGGING clause is not recommended. Optionally, if you know in advance that operations using the NOLOGGING clause will be performed on certain tables in the primary database, you might want to prevent the application of SQL statements associated with these tables to the logical standby database by using the DBMS_LOGSTDBY.SKIP procedure.
13.4.2 Recovery Steps for Physical Standby Databases

When the archived redo log file is copied to the standby site and applied to the physical standby database, a portion of the datafile is unusable and is marked as being unrecoverable. When you either fail over to the physical standby database, or open the standby database for read-only access, and attempt to read the range of blocks that are marked as UNRECOVERABLE, you will see error messages similar to the following:

ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)
ORA-01110: data file 1: ‘/oracle/dbs/stdby/tbs_1.dbf’
ORA-26040: Data block was loaded using the NOLOGGING option

To recover after the NOLOGGING clause is specified, you need to copy the datafile that contains the missing redo data from the primary site to the physical standby site. Perform the following steps:

Step 1 Determine which datafiles should be copied.

Follow these steps:

Query the primary database:


        NAME                                                  UNRECOVERABLE
        ----------------------------------------------------- -------------
        /oracle/dbs/tbs_1.dbf                                       5216
        /oracle/dbs/tbs_2.dbf                                          0
        /oracle/dbs/tbs_3.dbf                                          0
        /oracle/dbs/tbs_4.dbf                                          0
        4 rows selected.

Query the standby database:


        NAME                                                  UNRECOVERABLE
        ----------------------------------------------------- -------------
        /oracle/dbs/stdby/tbs_1.dbf                                 5186
        /oracle/dbs/stdby/tbs_2.dbf                                    0
        /oracle/dbs/stdby/tbs_3.dbf                                    0
        /oracle/dbs/stdby/tbs_4.dbf                                    0
        4 rows selected.

Compare the query results of the primary and standby databases.

Compare the value of the UNRECOVERABLE_CHANGE# column in both query results.
If the value of the UNRECOVERABLE_CHANGE# column in the primary database is greater than the same column in the standby database, then the datafile needs to be copied from the primary site to the standby site.

In this example, the value of the UNRECOVERABLE_CHANGE# in the primary database for the tbs_1.dbf datafile is greater, so you need
to copy the tbs_1.dbf datafile to the standby site.

Step 2 On the primary site, back up the datafile you need to copy to the standby site.

Issue the following SQL statements:

    SQL> EXIT;

    Copy the needed datafile to a local directory.


Step 3 Copy the datafile to the standby database.

Copy the datafile that contains the missing redo data from the primary site to a location on the physical standby site where files related to recovery are stored.

Step 4 On the standby database, restart Redo Apply.

Issue the following SQL statement:


You might get the following error messages (possibly in the alert log) when you try to restart Redo Apply:

ORA-00308: cannot open archived log ‘standby1’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/oracle/dbs/stdby/tbs_1.dbf’

If you get the ORA-00308 error and Redo Apply does not terminate automatically, you can cancel recovery by issuing the following SQL statement from another terminal window:


These error messages are returned when one or more log files in the archive gap have not been successfully applied. If you receive these errors, manually resolve the gaps, and repeat Step 4. See Section for information about manually resolving an archive gap.

13.4.3 Determining If a Backup Is Required After Unrecoverable Operations

If you performed unrecoverable operations on your primary database, determine if a new backup operation is required by following these steps:

Query the V$DATAFILE view on the primary database to determine the system change number (SCN) or the time at which the Oracle database generated the most recent invalidated redo data.

Issue the following SQL statement on the primary database to determine if you need to perform another backup:


If the query in the previous step reports an unrecoverable time for a datafile that is more recent than the time when the datafile was last backed up, then make another backup of the datafile in question.

See Oracle Database Reference for more information about the V$DATAFILE view.


Nologging operations affect standby database