You are allowed to suppress redo generation for certain statements, especially for CREATE TABLE AS SELECT, INSERT INTO … SELECT and CREATE INDEX. If backups have been taken of the related datafiles after these operations, everything is ok. You simply saved time during the above mentioned operations. But what if these datafiles actually get damaged before a successful backup?
I setup a demo scenario for that:
SQL> grant dba to adam identified by adam; Grant succeeded. SQL> connect adam/adam 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 archived 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…
Pickleball spielen 
#1 von Anand am September 15, 2009 - 20:14
Nice one again.Easy and simple to understand 🙂
Regards,
Anand
#2 von Gary am 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 von Uwe Hesse am 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 von coskan am 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 von Richard am 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 von Uwe Hesse am 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 von Anand am 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 von Jagjeet Singh am Oktober 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 von Uwe Hesse am Oktober 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 von Rahul am Oktober 20, 2009 - 13:06
Hi,
Nice discussion.
#11 von barry flynn am Februar 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 von Uwe Hesse am Februar 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 von PAB am Dezember 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 von Pavan Kumar N am Dezember 14, 2010 - 12:11
Hi,
That’s a nice demonstration of nologging concept… 😉
Welldone.. !!
#15 von Uwe Hesse am Dezember 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 von balakrishnan chandrasekaran am Februar 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 von Uwe Hesse am Februar 10, 2011 - 08:52
You’re welcome 🙂
Glad that it was helpful to you.
#18 von Victor am Dezember 28, 2011 - 07:23
During recovery will any message about corrupt block in the alert log ?
#19 von Uwe Hesse am Dezember 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 von Mike McGee am September 12, 2012 - 18:28
Excellent article, that is exactly the information i was looking for.
#21 von Jos van den Oord am Oktober 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 von Mahir M. Quluzade am Februar 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 von azhar syed am Dezember 31, 2013 - 09:43
Uwe ,
Great article as always but since the tablespace is in logging mode wouldnt the data be recoverable ?
#24 von Uwe Hesse am Januar 9, 2014 - 14:53
azhar syed, the nologging clause on the statement level takes priority over the tablespace logging attribute.
#25 von Amit Saraswat am Juni 29, 2015 - 15:33
Hi Uwe,
First of all thanks for sharing a nice demo!!
I wonder how to reset FIRST_NONLOGGED_SCN / FIRST_NONLOGGED_TIME / UNRECOVERABLE_CHANGE# / UNRECOVERABLE_TIME for v$datafile view. I have tried using some operation on segment in nologging mode and took full backup of database. Still this view showing these values.
Tried with turning that segment into logging mode tried some operations and still not able to get those old values reset/clear.
Thanks
Amit S.
#26 von Vinay Kumar am Dezember 28, 2016 - 10:39
Hi Uwe,
I have one small doubt, If we create materialized views with NOLOGGING option (complete refresh method) on a dedicated tablespace ( No other objects will use this table space). In case of any media failure, this won’t create any issue while restoring / recovering the database right ?
FYI, we don’t care even if we are not able to recover these materialized views as we can re-build them online once the database is up.
Thanks,
Vinay