The Oracle Instructor

The last couple of weeks I was very busy and had not much time to post therefore. I recall some interesting things from my recent courses, though. One of them was the outcome of NOLOGGING operations, should the affected datafiles get damaged before we manage to take a backup of them. That seems to be a popular question, since it is asked many times in my courses

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?

I setup a demo scenario for that:

SQL> grant dba to nolog identified by nolog;
Grant succeeded.

SQL> connect nolog/nolog
Connected.

SQL> create tablespace tbs
 datafile '/u01/app/oracle/oradata/orcl/tbs01.dbf'
 size 100m;

Tablespace created.

SQL> create table t tablespace tbs as
 select rownum as id, 'Just some text' as col
 from dual connect by level <= 1e5;
Table created.

RMAN> backup tablespace tbs;

Starting backup at 15-SEP-09
[...]
Finished backup at 15-SEP-09

No NOLOGGING yet, as v$datafile shows. But then I create an index with NOLOGGING:

SQL>  select file#,to_char(UNRECOVERABLE_TIME,'yyyy-mm-dd:hh24:mi:ss')
 from v$datafile where file#=18;

 FILE# TO_CHAR(UNRECOVERAB
---------- -------------------
 18

SQL> create index i on t(id) tablespace tbs nologging;

Index created.

SQL> select file#,to_char(UNRECOVERABLE_TIME,'yyyy-mm-dd:hh24:mi:ss')
 from v$datafile where file#=18;

 FILE# TO_CHAR(UNRECOVERAB
---------- -------------------
 18 2009-09-15:16:21:18

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
---- ----------------------- -----------------------------------
18   full or incremental     /u01/app/oracle/oradata/orcl/tbs01.dbf

I pretend the datafile 18 is damaged now, set if offline, restore it from backup and recover it. Of course, the index does not get recovered since we have no redo protocol from the modified index blocks in the archive logs or online logs!

RMAN> sql "alter database datafile 18 offline";

using target database control file instead of recovery catalog
sql statement: alter database datafile 18 offline

RMAN> restore datafile 18;

Starting restore at 15-SEP-09
[...]
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 15-SEP-09

RMAN> recover datafile 18;

Starting recover at 15-SEP-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 15-SEP-09

RMAN>  sql "alter database datafile 18 online";

sql statement: alter database datafile 18 online

If now the index gets used, an error message is raised. But attention: The status of the index does not get UNUSABLE!

SQL> select * from t where id=42;
select * from t where id=42
 *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 18, block # 397)
ORA-01110: data file 18: '/u01/app/oracle/oradata/orcl/tbs01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

SQL> select index_name,status,logging
 from user_indexes where tablespace_name='TBS';

INDEX_NAME                     STATUS   LOG
------------------------------ -------- ---
I                              VALID    NO

We can find out what index is causing the problem with the LOGGING column of USER_INDEXES as above. Again attention: We can not simply REBUILD the index now!

SQL> alter index i rebuild nologging;
alter index i rebuild nologging
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 18, block # 397)
ORA-01110: data file 18: '/u01/app/oracle/oradata/orcl/tbs01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

Only after setting the index to UNUSABLE, we can REBUILD it:

SQL> alter index i unusable;

Index altered.

SQL> alter index i rebuild nologging;

Index altered.

SQL> select file#,to_char(UNRECOVERABLE_TIME,'yyyy-mm-dd:hh24:mi:ss')
 from v$datafile where file#=18;

 FILE# TO_CHAR(UNRECOVERAB
---------- -------------------
 18 2009-09-15:16:30:36

If we take a RMAN backup now, still v$datafile is not affected, but RMAN does no longer report the file as UNRECOVERABLE:

RMAN> backup tablespace tbs;

Starting backup at 15-SEP-09
[...]
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-SEP-09

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

"No file shown"

How does the table t behave if I now do an INSERT with NOLOGGING into it?

SQL> alter table t nologging;

Table altered.

SQL> insert /*+ append */ into t select * from t;

100000 rows created.

SQL> commit;

Commit complete.

SQL> select file#,to_char(UNRECOVERABLE_TIME,'yyyy-mm-dd:hh24:mi:ss')
 from v$datafile where file#=18;

 FILE# TO_CHAR(UNRECOVERAB
---------- -------------------
 18 2009-09-15:16:34:07

RMAN> sql "alter database datafile 18 offline";

using target database control file instead of recovery catalog
sql statement: alter database datafile 18 offline

RMAN> restore datafile 18;

Starting restore at 15-SEP-09
[...]
Finished restore at 15-SEP-09

RMAN> recover datafile 18;

Starting recover at 15-SEP-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 15-SEP-09

RMAN>  sql "alter database datafile 18 online";

sql statement: alter database datafile 18 online

I again pretended the file was damaged, restored it from backup and recovered it. As soon as rows are now demanded that got into the table with the direct load via the NOLOGGING operation, error messages are returned:

SQL> select count(*) from t where rownum<=1e5;

 COUNT(*)
----------
 100000

SQL> select count(*) from t where rownum<=1e5+1;
select count(*) from t where rownum<=1e5+1
 *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 18, block # 368)
ORA-01110: data file 18: '/u01/app/oracle/oradata/orcl/tbs01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

There is no way to get these rows back (at least not with recovery) since we suppressed the redo protocol during their generation. That is the price we pay for the speedup of the insert here…