How do NOLOGGING operations affect RECOVERY?

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…

About these ads

,

  1. #1 by Anand on September 15, 2009 - 20:14

    Nice one again.Easy and simple to understand :)

    Regards,
    Anand

  2. #2 by Gary on September 15, 2009 - 23:55

    Which is why the DBA should have FORCELOGGING set so that no dumb developer can write a SQL statement that buggers up the ability to restore from a backup. And, seriously, this is the sort of thing that should require an explicitly granted privilege. Yes, I am a developer. Hopefully not dumb.

  3. #3 by Uwe Hesse on September 16, 2009 - 07:41

    @Anand: Thank you, I appreciate your feedback, especially as this is one of my major goals: To be easy to understand.
    @Gary: In an ideal world, developers talk with DBAs after such actions, so that the DBA can take a backup immediately. Actually, that would be a good thing if DBAs & developers would communicate :-)

  4. #4 by coskan on September 19, 2009 - 16:05

    I think it could be nice if EM can alert this to the administrator otherwise getting report from RMAN as mail is a good idea for a DBA during the day in case you dont set forceloggin on.

    Thank you for the clear as day explanation Uwe

  5. #5 by Richard on September 20, 2009 - 11:59

    So what should I do, if I using feature like direct path insert and don’t want to lose data from recovery when database corrupt ?

  6. #6 by Uwe Hesse on September 21, 2009 - 09:28

    @Coskan: Thanks for your feedback & for referring to the article from your site! You may send that as an enhancement request (sending an alert after NOLOGGING operations) to Oracle :-)

    @Richard: The point is to take a backup of the related datafiles after the NOLOGGING operation is done. If you can not do that and if the changes are not reproducable – don’t go with NOLOGGING.

  7. #7 by Anand on September 21, 2009 - 16:29

    @Richard: Put the database in FORCE LOGGING mode.That is what is the first step when you have to setup standby db, as corruptions doesn’t occur in standby when switchover is done.

    Regards,
    Anand

  8. #8 by Jagjeet Singh on October 2, 2009 - 07:15

    Thanks Uwe for nice post. “report unrecoverable”
    is a nice feature.

    Till today I was believing on “unrecoverable time”
    column of v$datafile for the databases which are not in foce logging mode.

    Do you see any diff. in the output of “report unrecoverable” and “unrecoverable_Time from v$datafile”

    If both shows correct then we can put alert easily using EM as expressed by Coskan.

    Regards,
    Js

  9. #9 by Uwe Hesse on October 2, 2009 - 08:44

    Jagjeet, the difference is that RMAN is so smart to no longer report files as unrecoverable if you have taken a backup of them after the nologging operation. The entry in v$datafile does not disappear, though.

    Kind regards
    Uwe

  10. #10 by Rahul on October 20, 2009 - 13:06

    Hi,

    Nice discussion.

  11. #11 by barry flynn on February 12, 2010 - 18:47

    Uwe, if i need to do a point in time recovery that is
    before the nologging operation, does the recover just not recover the tables or indexes that were nologged and fully recover all other object up to the entered point in time or does the recover stop as soon as it identifies the nologging operation and even object that were not nologging do not get recovered???

  12. #12 by Uwe Hesse on February 12, 2010 - 21:47

    Barry,
    if you are doing a Database Point In Time Recovery, it will succeed even if you reach a point in time where a nologging operation was done. The segment that was affected by the nologging operation will be (at least in part) unusable, though. Everything else will be recovered. You may then take care for the affected segments by recreating them resp. reproducing the insert that you did with nologging.

  13. #13 by PAB on December 2, 2010 - 10:06

    Hi,
    I am not able to produce nologging operation for index creation:-
    (It is oracle 10.2)
    Index is created on file# 4, Still the UNRECOVERABLE_TIME remains unchanged.

    SQL> SELECT TO_CHAR(SYSDATE,’DD-MON-YY HH:MI’) FROM DUAL;

    TO_CHAR(SYSDATE
    —————
    02-DEC-10 02:30

    SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

    FOR

    NO

    SQL> CREATE INDEX IND ON TEST(COLUMN_NAME) NOLOGGING;

    Index created.

    SQL> select file#,UNRECOVERABLE_CHANGE#,TO_CHAR(UNRECOVERABLE_TIME,’DD-MON-YY HH:MI’) from v$datafile;

    FILE# UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOV
    ———- ——————— —————
    1 0 16-APR-10 07:17
    2 0
    3 0
    4 108700996 02-DEC-10 02:22
    5 0
    6 0
    7 0
    8 0
    9 0
    10 0
    11 0

  14. #14 by Pavan Kumar N on December 14, 2010 - 12:11

    Hi,

    That’s a nice demonstration of nologging concept… ;-)
    Welldone.. !!

  15. #15 by Uwe Hesse on December 21, 2010 - 16:56

    @PAB,
    the size of the table matters. If you do a create index nologging on a tiny table, the nologging is just ignored.

    @Pavan Kumar N,
    thank you for the nice feedback! Much appreciated :-)

  16. #16 by balakrishnan chandrasekaran on February 9, 2011 - 20:01

    Thx for this post. i was testing nologging operation to be clear about how it shows up after reocvery etc and this post came in very handy and time saving.

  17. #17 by Uwe Hesse on February 10, 2011 - 08:52

    You’re welcome :-)
    Glad that it was helpful to you.

  18. #18 by Victor on December 28, 2011 - 07:23

    During recovery will any message about corrupt block in the alert log ?

  19. #19 by Uwe Hesse on December 28, 2011 - 09:24

    Victor, I don’t think so. We don’t know that we are missing the suppressed Redo Protocol during Recovery.

  20. #20 by Mike McGee on September 12, 2012 - 18:28

    Excellent article, that is exactly the information i was looking for.

  21. #21 by Jos van den Oord on October 2, 2012 - 16:47

    Hi Uwe Hesse,

    What if nologing operation was happening during the backup is taken with RMAN.
    In my opinion we can still get a FUZZY database, when opening the database after a recovery of the database.

    My strategy is, only when it it possible, to place the database in force logging mode before backup and disable after backup. or keep it in force logging.

    Point of attention: Keep in mind of running current operations on the database and your redo, during the force logging statement for the Database.

  22. #22 by Mahir M. Quluzade on February 18, 2013 - 14:47

    Hi Uwe ,

    Thanks for sharing your valuable experiences, you answered again,
    why force logging need for Primary database on data guard configuration.

    Mahir

  23. #23 by azhar syed on December 31, 2013 - 09:43

    Uwe ,

    Great article as always but since the tablespace is in logging mode wouldnt the data be recoverable ?

  24. #24 by Uwe Hesse on January 9, 2014 - 14:53

    azhar syed, the nologging clause on the statement level takes priority over the tablespace logging attribute.

  1. Blogroll Report 11/09/2009 – 18/09/2009 « Coskan’s Approach to Oracle
  2. data recovery
  3. Top 10 postings in 2012 « The Oracle Instructor
  4. Speed up Import with TRANSFORM=DISABLE_ARCHIVE_LOGGING in #Oracle 12c | The Oracle Instructor

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

Follow

Get every new post delivered to your Inbox.

Join 2,644 other followers

%d bloggers like this: