Oracle NOLOGGING and RMAN backups

On a new production system I was assigned I discovered that the database’s backup was not always in a recoverable state due to NOLOGGING operations. The system is a Data Warehouse so they use NOLOGGING operations to improve performance on bulk loads and transformations of data that doesn’t have to be saved. I also ran an Oracle OraChk and it reported the NOLOGGING issue with suggestions on fixing it, by having a NOLOGGING tablespace. I got the impression that by doing so you wouldn’t have to worry about having inconsistent block issues on a recovery.

The first thing I did was make sure the RMAN backups were scheduled to run and complete after ETL operations completed and before the next ETL operations begun. This would ensure that I could at least recover the database to a point each day prior to the data loads running. My next goal was to figure out if I could use NOFORCELOGGING on a tablespace to ensure that I could recover to any point-in-time during the ETL NOLOGGING operations. From reading the documents it appeared that this was the way to do it, but I couldn’t find anything that clearly stated it would solve the problem of recovery during NOLOGGING operations.

The suggestion was:

  1. Turn off FORCELOGGING at the database level.
  2. Create a tablespace and specify NOLOGGING
  3. Move all objects that have NOLOGGING operations into the NOLOGGING tablespace.

I wasn’t convinced this would actually solve the problem, though conceptually it would make the database layout easier to understand.

So, I did some testing on another database. First, I made sure no FORCELOGGING was specified at the database level.

SQL> alter database no force logging;

alter database no force logging

*

ERROR at line 1:

ORA-12921: database is not in force logging mode

SQL> select force_logging from v$database;

FOR

NO

Next I created three tablespaces, one for forced logging, one for standard logging and one for nologging.

SQL> create tablespace test_forcelogging datafile ‘/oradata/P10BOSS/test_forcelogging_01.dbf’ size 100m;

Tablespace created.

SQL> create tablespace test_logging datafile ‘/oradata/P10BOSS/test_logging_01.dbf’ size 100m;

Tablespace created.

SQL> create tablespace test_nologging datafile ‘/oradata/P10BOSS/test_nologging_01.dbf’ size 100m nologging;

Tablespace created.

SQL> select tablespace_name, logging, force_logging from dba_tablespaces where tablespace_name like ‘TEST%’;

TABLESPACE_NAME                LOGGING   FOR

—————————— ——— —

TEST_FORCELOGGING              LOGGING   NO

TEST_LOGGING                   LOGGING   NO

TEST_NOLOGGING                 NOLOGGING NO

Then I set force logging for the test_forcelogging tablespace, and tried to turn it off for the test_nologging tablespace.

SQL> alter tablespace test_forcelogging force logging;

Tablespace altered.

SQL> alter tablespace test_nologging no force logging;

alter tablespace test_nologging no force logging

*

ERROR at line 1:

ORA-12925: tablespace TEST_NOLOGGING is not in force logging mode

SQL> select tablespace_name, logging, force_logging from dba_tablespaces where tablespace_name like ‘TEST%’;

TABLESPACE_NAME                LOGGING   FOR

—————————— ——— —

TEST_FORCELOGGING              LOGGING   YES

TEST_LOGGING                   LOGGING   NO

TEST_NOLOGGING                 NOLOGGING NO

So, this is my first clue. I can force the test_forcelogging tablespace to log. This is good if I want everything in there logged, but when it came to the test_nologging tablespace I could not “force no logging”, you can only specify that it doesn’t force logging. This lead me to believe that moving nologging tables into this tablespace was not going to allow me to do a backup, perform nologging operations, and be able to recover without a new backup.

So, I ran an RMAN full backup and checked for unrecoverable files.

RMAN> backup database plus archivelog delete input;

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations

File Type of Backup Required Name

—- ———————– ———————————–

<nothing listed>

Then I created my tables with no rows and checked them.

SQL> create table test_forcelogging logging tablespace test_forcelogging as select * from dba_objects where 1=2;

Table created.

SQL> create table test_logging logging tablespace test_logging as select * from dba_objects where 1=2;

Table created.

SQL> create table test_nologging nologging tablespace test_nologging as select * from dba_objects where 1=2;

Table created.

SQL> select table_name, tablespace_name, logging from dba_tables where table_name like ‘TEST%’;

TABLE_NAME                     TABLESPACE_NAME                LOG

—————————— —————————— —

TEST_NOLOGGING                 TEST_NOLOGGING                 NO

TEST_LOGGING                   TEST_LOGGING                   YES

TEST_FORCELOGGING              TEST_FORCELOGGING              YES     — See Post-note#2

I then checked for unrecoverable operations in datafiles via SQL, and did some basic inserts to make sure things seemed normal.

SQL> select file#, name, to_char(unrecoverable_time,’yyyy/mm/dd hh24:mi:ss’), unrecoverable_change# from v$datafile where unrecoverable_time is not null;

no rows selected

SQL> insert into test_forcelogging select * from dba_objects where rownum = 1;

1 row created.

SQL> insert into test_logging select * from dba_objects where rownum = 1;

1 row created.

SQL> insert into test_nologging select * from dba_objects where rownum = 1;

1 row created.

SQL> commit;

Commit complete.

SQL> select file#, name, to_char(unrecoverable_time,’yyyy/mm/dd hh24:mi:ss’), unrecoverable_change# from v$datafile where unrecoverable_time is not null;

no rows selected

RMAN> report unrecoverable

using target database control file instead of recovery catalog

Report of files that need backup due to unrecoverable operations

File Type of Backup Required Name

—- ———————– ———————————–

<nothing listed>

These all looked good. No unrecoverable operations marked and no unrecoverable datafiles in RMAN.

So, next i did an insert into each using the append hint (i.e. nologging).

SQL> insert /*+ append */ into test_forcelogging select * from dba_objects where rownum = 1;

1 row created.

SQL> insert /*+ append */ into test_logging select * from dba_objects where rownum = 1;

1 row created.

SQL> insert /*+ append */ into test_nologging select * from dba_objects where rownum = 1;

1 row created.

SQL> commit;

Commit complete.

SQL> select file#, name, to_char(unrecoverable_time,’yyyy/mm/dd hh24:mi:ss’), unrecoverable_change# from v$datafile where unrecoverable_time is not null;

FILE# NAME                                               TO_CHAR(UNRECOVERAB UNRECOVERABLE_CHANGE#

———- ————————————————– ——————- ———————

8 /oradata/P10BOSS/test_nologging_01.dbf             2015/07/21 11:00:18            1.1093E+13

Now we see an unrecoverable operation in the test_nologging tablespace, but not in the test_logging or test_forcelogging tablespaces.

This makes sense because for NOLOGGING (minimal redo) to occur you must specify a NOLOGGING operation (e.g. /*+ append */) on a NOLOGGING table and the database and tablespace must not be FORCELOGGING. Also, see Post-note#1 at the end of the article.

So, I checked in RMAN too, and saw that the datafile was marked unrecoverable as expected.

RMAN> report unrecoverable

using target database control file instead of recovery catalog

Report of files that need backup due to unrecoverable operations

File Type of Backup Required Name

—- ———————– ———————————–

8    full or incremental     /oradata/P10BOSS/test_nologging_01.dbf

The next task was to make sure I could get the database back into a recoverable state. To do this you simply need to backup the object in question. This could be via a datafile, tablespace, or incremental or full backup. So, I just did a datafile backup.

RMAN> backup datafile 8;

Finished backup at 21-JUL-15

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations

File Type of Backup Required Name

—- ———————– ———————————–

<nothing listed>

Then I checked v$datafile again for unrecoverable operations.

SQL> select file#, name, to_char(unrecoverable_time,’yyyy/mm/dd hh24:mi:ss’), unrecoverable_change# from v$datafile where unrecoverable_time is not null;

FILE# NAME                                               TO_CHAR(UNRECOVERAB UNRECOVERABLE_CHANGE#

———- ————————————————– ——————- ———————

8 /oradata/P10BOSS/test_nologging_01.dbf             2015/07/21 11:00:18            1.1093E+13

Note that I still hade the row for the test_nologging tablespace datafile. That doesn’t mean that the datafile was not recoverable though. The row will always show the last time an unrecoverable operation occurred. Therefore it is best to use RMAN to determine if your database is recoverable. This is why it is so important to manage the times your database is doing unrecoverable operations and backups to ensure they do not overlap because as you can see, if you don’t want to log the operations there is no way to ensure a good backup during the operations. This is why incremental backups are often touted. With those you can do a quick incremental (assuming your DB is small or you have a block change tracking file) after the data loads to get yourself recoverable again.

Post-note #1: Oracle will sometimes break the rules if your NOLOGGING operation is very small – it might actually log full redo, instead of partial. When doing this testing I noticed that sometimes on the single row append insert I would not get an unrecoverable operation. Per an AskTom article I learned that this can happen. If I did a full append insert of the dba_tables data into my test table I would always seems to get unrecoverable operations, but not always with a single row append insert.

Post-note #2: In this case it would have been interesting to make the table a NOLOGGING table. Theoretically it would be over-ridden by the FORCELOGGING on the tablespace.

One response to “Oracle NOLOGGING and RMAN backups

  1. Pingback: Oracle NOLOGGING and RMAN backups | Jed's

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s