ORA-01578和ORA-26040--NOLOGGING操作引起的壞塊-錯誤解釋和解決方案(文件ID 1623284.1)

（一）NOLOGGING操作引起的壞塊（ORA-01578和ORA-26040）簡介

如果只是錯誤ORA-01578，而沒有伴隨ORA-26040，那麼這個壞塊是由其它的原因引起的壞塊，可以嘗試使用RMAN的BMR（Block Media Recovery）修復。

如果資料段（表段、索引段）被定義為NOLOGGING屬性，那麼當NOLOGGING加APPEND、UNRECOVERABLE操作修改該資料段或者使用資料泵（DATAPUMP）impdp引數DISABLE_ARCHIVE_LOGGING:Y時，聯機重做日誌只會記錄很少的日誌資訊。如果這些聯機重做日誌或歸檔日誌被用來恢復資料檔案，那麼Oracle會將對應的資料塊標誌為無效（Soft Corrupt），而且下一次訪問這些資料塊時，會報ORA-01578和ORA-26040錯誤。

例如：

			SQL> select * from test_nologging; 
		
			ORA-01578: ORACLE data block corrupted (file # 11,
block # 84) 
		
			ORA-01110: data file 4: '/oradata/users.dbf' 
		
			ORA-26040: Data block was loaded using the
NOLOGGING option

資料字典檢視DBA_TABLES、DBA_INDEXES、DBA_LOBS、DBA_TAB_PARTITIONS、DBA_LOB_PARTITIONS、DBA_TAB_SUBPARTITIONS中的LOGGING列記錄了NOLOGGING屬性。若LOGGING='NO'則表示NOLOGGING。

資料泵DATAPUMP的impdp引數DISABLE_ARCHIVE_LOGGING:Y在執行匯入時會禁止LOGGING定義，而產生NOLOGGING操作。如果相應的datafile被restored和recovered，那麼接下來的涉及到目標表的查詢會報錯ORA-1578和ORA-26040。如果資料庫是FORCE LOGGING模式，那麼DISABLE_ARCHIVE_LOGGING選項不會關閉LOGGING。

impdp使用引數“DISABLE_ARCHIVE_LOGGING:Y”的一個例子:

			impdp scott/tiger directory=DATA_PUMP_DIR
dumpfile=dp transform=disable_archive_logging:y 
		

NOLOGGING導致的壞塊不會導致RMAN備份失敗。一般來說soft corrupt block不會導致RMAN備份失敗，不需要設定MAXCORRUPT。資料庫備份中就會含有soft corrupt block，如果使用這些備份恢復資料，那麼恢復的資料也含有soft corrupt block。

除ORA-26040錯誤之外，當還有一些其他通用資訊出現時，block dump可能會被產生。如果資料塊的block dump內有byte 0xff資訊或者屬於某個段，ORA-1578和ORA-26040會因為介質恢復了NOLOGGING的部分導致了corruption而出現。

（二）利用RMAN、DBV檢測NOLOGGING導致的壞塊

DBV在檢測壞塊時，如果RDBMS版本小於10.2.0.4，那麼DBV列印錯誤DBV-200，如果RDBMS版本大於或等於10.2.0.4，那麼DBV列印錯誤DBV-201：

			DBV-00200: Block, dba 46137428, already marked
corrupted 
		
			DBV-00201: Block, DBA 46137428, marked corrupt for
invalid redo application

RMAN的VALIDATE命令可以用來檢測NOLOGGING資料塊，檢查結果記錄在檢視V$DATABASE_BLOCK_CORRUPTION（小於12c的版本）和V$NONLOGGED_BLOCK（12c及其以上）。

下面的例子中檢查出DATAFILE 4有933壞塊，查詢V$DATABASE_BLOCK_CORRUPTION或者V$NONLOGGED_BLOCK。

			RMAN> VALIDATE DATABASE; 
		
			... 
		
			..... 
		
			File Status Marked Corrupt Empty Blocks Blocks
Examined High SCN 
		
			---- ------ -------------- ------------
--------------- ---------- 
		
			4    OK     933            1           
6401            2275124    
		
			  File Name:
/oracle/dbs/users.dbf

RMAN在檢測壞塊時，如果RDBMS版本小於10.2.0.5和11.1.0.7，RMAN列印如下錯誤：

			10.2.0.4 and lower, 11.1.0.6, 11.1.0.7: 
		
			RMAN validate reports it in
v$database_block_corruption with CORRUPTION_TYPE=LOGICAL

如果RDBMS版本大於或等於10.2.0.5和11.2.0.1，RMAN報告，檢視檢視v$database_block_corruption中CORRUPTION_TYPE=NOLOGGING的記錄。

			10.2.0.5 and 11.2.0.1+: 
		
			RMAN validate reports it in
v$database_block_corruption with CORRUPTION_TYPE=NOLOGGING

在12c及以後版本中，RMAN validate的結果不在檢視v$database_block_corruption中，而是在檢視v$nonlogged_block。從12.2 版本開始，可以使用新的命令：“validate .. nonlogged block”去驗證nologging的block。

在以下的例子中，資料檔案5和6有nologged的block：

			RMAN> validate database nonlogged block; 
		
			Starting validate at ... 
		
			using target database control file instead of
recovery catalog 
		
			allocated channel: ORA_DISK_1 
		
			channel ORA_DISK_1: SID=133 device type=DISK 
		
			channel ORA_DISK_1: starting validation of
datafile 
		
			channel ORA_DISK_1: validation complete, elapsed
time: 00:00:35 
		
			List of Datafiles 
		
			================= 
		
			File Status Nonlogged Blocks Blocks Examined
Blocks Skipped 
		
			---- ------ ---------------- ---------------
-------------- 
		
			1        OK
0                         106363 0 
		
			2        OK
0                          78919 0 
		
			3        OK
0                          96639 0 
		
			4        OK
0                           4991 0 
		
			5       
OK 400                        
2559 0 
		
			6       
OK 569                        
2559 0 
		
			Details of nonlogged blocks can be queried from
v$nonlogged_block view

在告警日誌中會更新以下資訊：

			Started Nonlogged Block Replacement recovery(validate)
on file 5 (ospid 26351 rcvid 10616970560844821494) 
		
			Finished Nonlogged Block Replacement
recovery(validate) on file 5. 400 blocks found 
		
			Started Nonlogged Block Replacement
recovery(validate) on file 6 (ospid 26351 rcvid 10616970560844821494) 
		
			Finished Nonlogged Block Replacement
recovery(validate) on file 6. 569 blocks found

（三）監控NOLOGGING操作

若執行了NOLOGGING操作，並且之後在沒有備份的情況下，RMAN命令“REPORT UNRECOVERABLE”可以查詢出被影響的datafile。

			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 
		
			---- -----------------------
----------------------------------- 
		
			4    full or
incremental     /oracle/dbs/users.dbf

當初始化引數db_unrecoverable_scn_tracking設定為true（預設值，該引數在10g中是不可用的），那麼V$DATAFILE中以下列會被更新；

			SYS@lhr121> select UNRECOVERABLE_CHANGE# ,     
		
			  2  UNRECOVERABLE_TIME    ,  
		
			  3  FIRST_NONLOGGED_SCN , 
		
			  4  FIRST_NONLOGGED_TIME from v$datafile where
file#=6; 
		
			UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME  FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM 
		
			--------------------- -------------------
------------------- ------------------- 
		
2878238 2018-04-10 10:53:47            
2878238 2018-04-10 10:53:47

在11.2.0.4 或12.1.0.2+版本中，設定event 16490的情況下，物理備庫的MRP程式會檢查出NOLOGGING變化，並記錄在alert log。

			ORA-16490 "logging invalidated blocks on
standby due to invalidation redo" 
		
			"INVD_BLKS: Invalidating (file <file
number>, bno <block number>)"     
		
			"fname: 'Datafile name'. rdba: ..."

（四）識別資料塊什麼時候被標誌為NOLOGGING

識別資料塊什麼時候被標誌為NOLOGGING，可以將trace檔案中資料塊SCN或者v$database_block_coruption檢視中CORRUPTION_CHANGE#值轉換為時間：

①　使用trace檔案中資料塊SCN，例如：

			  Start dump
data blocks tsn: 60 file#: 4 minblk 84 maxblk 84 
		
			  buffer
tsn: 3 rdba: 0x02c00054 (11/84) 
		
			  scn:
0x0771.4fa24eb5 seq: 0xff flg: 0x04 tail: 0x4eb500ff

提取SCN值0x0771.4fa24eb5,刪除'.'，然後轉換0x07714fa24eb到十進位制511453045995。

②　使用v$database_block_coruption檢視中CORRUPTION_CHANGE#值

如果執行RMAN validate命令後，v$database_block_coruption檢視中corruption_type='NOLOGGING' (10.2.0.5 和 11.2.0.1+)，那麼CORRUPTION_CHANGE#列的值就是十進位制的SCN值。可以使用下面的方法獲得SCN Timestamp時間：

			select scn_to_timestamp(&&decimal_scn) from
dual; 
		

如果執行RMAN validate:

			select file#, block#,
scn_to_timestamp(CORRUPTION_CHANGE#) 
		
			from v$database_block_corruption 
		
			where CORRUPTION_TYPE='NOLOGGING';

在12c中：

			select file#, block#,
scn_to_timestamp(NONLOGGED_START_CHANGE#) from v$nonlogged_block; 
		

如果查詢gv$archived_log 或 gv$log_history遇到錯誤ORA-08181:

			alter session set nls_date_format = 'DD-MON-YY
HH24:MI:SS'; 
		
			select first_time, next_time 
		
			from gv$archived_log 
		
			where &decimal_scn between first_change# and
next_change#; 
		
			或 
		
			select first_time 
		
			from gv$log_history 
		
			where &decimal_scn between first_change# and
next_change#; 
		
			如果執行RMAN validate: 
		
			alter session set nls_date_format = 'DD-MON-YY
HH24:MI:SS'; 
		
			select file#, block#, first_time, next_time 
		
			from v$archived_log, v$database_block_corruption 
		
			where CORRUPTION_CHANGE# between first_change# and
next_change# 
		
			and CORRUPTION_TYPE='NOLOGGING'; 
		
			或 
		
			select file#,block#,first_time 
		
			from  
v$log_history, v$database_block_corruption 
		
			where 
CORRUPTION_CHANGE# between first_change# and next_change# 
		
			  and
CORRUPTION_TYPE='NOLOGGING'; 
		
			12c: 
		
			alter session set nls_date_format = 'DD-MON-YY
HH24:MI:SS'; 
		
			select file#, block#, first_time, next_time 
		
			from v$nonlogged_block, v$archived_log 
		
			where NONLOGGED_START_CHANGE# between
first_change# and next_change#; 
		
			或 
		
			select file#, block#, first_time 
		
			from v$nonlogged_block, v$log_history 
		
			where NONLOGGED_START_CHANGE# between
first_change# and next_change#;

（五）SYSAUX表空間、AWR、EM等出現NOARCHIVELOG和NOLOGGING問題

如果資料庫版本是11.1.0.6 或 11.1.0.7 或 11.2.0.1，對NOLOGGING物件執行過DIRECT PATH操作，並且後續執行了RECOVER DATABASE命令，即使資料庫FORCE LOGGING是開啟的情況下，會出現ORA-1578和ORA-26040錯誤。這種問題經常發生在SYSAUX表空間中的AWR或EM物件。請參考Note 1071869.1。注意資料庫當前版本可能已經大於11.1 或者 11.2.0.1但是問題可能是在升級之前產生的。這個約束在11.2.0.2以上版本中取消，這個問題在10g不會發生。

RDBMS版本變化：

RDBMS版本	變化
10.2.0.4+	DBverify報告NOLOGGING block錯誤資訊 "DBV-00201: Block, DBA <rdba>, marked corrupt for invalid redo application"
10.2.0.5, 10.2.0.1+	RMAN validate命令檢查NOLOGGING block，在v$database_block_coruption檢視中記錄corruption_type='NOLOGGING'
11g+	引入db_unrecoverable_scn_tracking引數
11.1.0.6 or 11.1.0.7 or 11.2.0.1	NOARCHIVELOG模式資料庫，對NOLOGGING物件執行了DIRECT PATH操作，並且以後手動恢復資料庫，即使開啟了FORCE LOGGING，也會報ORA-1578 和 ORA-26040。這個約束在11.2.0.2以上版本取消，這個問題在10g不會發生。
12c	RMAN validate的結果不在檢視v$database_block_corruption中，而是在檢視v$nonlogged_block
12.2	以下RMAN命令被引入： RMAN> validate [database / datafile] nonlogged block; RMAN> recover [database / datafile] nonlogged block; -> 對於 Standby 資料庫

（六）解決方法

NOLOGGING操作引起的壞塊是不能修復的，比如“Media Recovery”或“RMAN blockrecover”都無法修復這種壞塊。可行的方法是在NOLOGGING操作之後立刻備份對應的資料檔案。

如果錯誤是執行RMAN DUPLICATE 或 RESTORE之後產生的，那麼在源庫開啟FORCE LOGGING，然後再重新執行RMAN DUPLICATE 或 RESTORE。

			alter database force logging; 
		

如果錯誤出現在物理STANDBY資料庫，那麼可以從主庫恢復被影響的資料檔案（只有當主庫沒有這個問題的情況下）。參考文件Doc ID 958181.1。在Oracle 12c中可以使用RMAN選項RECOVER NONLOGGED BLOCK with DATAFILE、TABLESPACE、DATABASE。例如：

			RMAN> RECOVER DATABASE NONLOGGED BLOCK; 
		

為了避免這個問題發生，在主庫強制生產日誌：

			alter database force logging; 
		

如果同一個datafile的資料塊在主庫出現nologging壞塊，但是備庫沒有，可以透過手動跳過(dbms_repair)壞塊或者設定event 10231。主庫出現nologging壞塊可能是由於主庫執行過備份恢復或者之前是備庫，執行了switchover。

如果NOLOGGING資料塊位於空閒資料塊（dba_free_space檢視可以查詢到），那麼DBVerify檢查會發現這個問題，報錯DBV-00201或者在v$database_block_corruption檢視中顯示。對於這種情況，可以等待到這個資料塊被重用時會自動格式化或者手動強制格式化。

如果是索引，那麼可以重新建立（drop/create）索引。如果是表，那麼可以使用儲存過程DBMS_REPAIR.SKIP_CORRUPT_BLOCKS跳過壞塊，然後考慮是否重建表。

在刪除有壞塊的段之後，這個壞塊就處於空閒狀態，後續可以被分配給其他物件或段，當這個壞塊被分配給其它物件或段時，這個資料塊被重新格式化。如果v$database_block_corruption檢視中還是顯示為壞塊，那麼可以手動執行rman validate來清除檢視中的資訊。

如果是LOB，那麼請參考Note 293515.1。

實驗一：DISABLE_ARCHIVE_LOGGING:Y

			  
			RMAN> list backupset of datafile 6; 
		
			List of Backup Sets 
		
			=================== 
		
			BS Key  Type
LV Size       Device Type Elapsed Time
Completion Time     
		
			------- ---- -- ---------- ----------- ------------
------------------- 
		
			12     
Full    352.78M    DISK       
00:03:21     2018-04-09 14:50:59 
		
			        BP
Key: 12   Status: AVAILABLE  Compressed: YES  Tag: TAG20180409T144738 
		
			        Piece
Name:
/u04/flash_recovery_area/LHR121/backupset/2018_04_09/o1_mf_nnndf_TAG20180409T144738_fdp34bfm_.bkp 
		
			  List of
Datafiles in backup set 12 
		
			  File LV
Type Ckp SCN    Ckp Time            Name 
		
			  ---- --
---- ---------- ------------------- ---- 
		
			  6       Full 2865977    2018-04-09 14:47:38
/u04/oradata/lhr121/users01.dbf 
		
			[oracle@rhel6lhr env_oracle]$ impdp scott/tiger
dumpfile=log.dmp tables=T_LOG TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y remap_schema=LHR:SCOTT 
		
			Import: Release 12.1.0.2.0 - Production on Tue Apr
10 10:53:17 2018 
		
			Copyright (c) 1982, 2014, Oracle and/or its
affiliates.  All rights reserved. 
		
			Connected to: Oracle Database 12c Enterprise
Edition Release 12.1.0.2.0 - 64bit Production 
		
			With the Partitioning, OLAP, Advanced Analytics and
Real Application Testing options 
		
			ORA-39002: invalid operation 
		
			ORA-39166: Object SCOTT.T_LOG was not found or
could not be exported or imported. 
		
			[oracle@rhel6lhr env_oracle]$ impdp lhr/lhr
dumpfile=log.dmp tables=T_LOG TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
remap_schema=LHR:SCOTT 
		
			Import: Release 12.1.0.2.0 - Production on Tue Apr
10 10:53:40 2018 
		
			Copyright (c) 1982, 2014, Oracle and/or its
affiliates.  All rights reserved. 
		
			Connected to: Oracle Database 12c Enterprise
Edition Release 12.1.0.2.0 - 64bit Production 
		
			With the Partitioning, OLAP, Advanced Analytics and
Real Application Testing options 
		
			Master table
"LHR"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded 
		
			Starting
"LHR"."SYS_IMPORT_TABLE_02":  lhr/******** dumpfile=log.dmp tables=T_LOG
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y remap_schema=LHR:SCOTT  
		
			Processing object type TABLE_EXPORT/TABLE/TABLE 
		
			Processing object type
TABLE_EXPORT/TABLE/TABLE_DATA 
		
			. . imported
"SCOTT"."T_LOG"                             34.24 KB       9 rows 
		
			Processing object type
TABLE_EXPORT/TABLE/INDEX/INDEX 
		
			Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 
		
			Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
		
			Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER 
		
			Job "LHR"."SYS_IMPORT_TABLE_02"
successfully completed at Tue Apr 10 10:54:14 2018 elapsed 0 00:00:32 
		
			[oracle@rhel6lhr env_oracle]$ rm -rf
/u04/oradata/lhr121/users01.dbf 
		
			[oracle@rhel6lhr env_oracle]$ rman target / 
		
			Recovery Manager: Release 12.1.0.2.0 - Production
on Tue Apr 10 10:55:09 2018 
		
			Copyright (c) 1982, 2014, Oracle and/or its
affiliates.  All rights reserved. 
		
			connected to target database: LHR121
(DBID=3221842516) 
		
			RMAN> restore datafile 6; 
		
			Starting restore at 2018-04-10 10:55:17 
		
			using target database control file instead of
recovery catalog 
		
			allocated channel: ORA_DISK_1 
		
			channel ORA_DISK_1: SID=29 device type=DISK 
		
			channel ORA_DISK_1: starting datafile backup set
restore 
		
			channel ORA_DISK_1: specifying datafile(s) to
restore from backup set 
		
			channel ORA_DISK_1: restoring datafile 00006 to
/u04/oradata/lhr121/users01.dbf 
		
			channel ORA_DISK_1: reading from backup piece
/u04/flash_recovery_area/LHR121/backupset/2018_04_09/o1_mf_nnndf_TAG20180409T144738_fdp34bfm_.bkp 
		
			RMAN-00571:
=========================================================== 
		
			RMAN-00569: =============== ERROR MESSAGE STACK
FOLLOWS =============== 
		
			RMAN-00571:
=========================================================== 
		
			RMAN-03002: failure of restore command at
04/10/2018 10:55:20 
		
			ORA-19870: error while restoring backup piece
/u04/flash_recovery_area/LHR121/backupset/2018_04_09/o1_mf_nnndf_TAG20180409T144738_fdp34bfm_.bkp 
		
			ORA-19573: cannot obtain exclusive enqueue for
datafile 6 
		
			RMAN> startup force mount 
		
			Oracle instance started 
		
			database mounted 
		
			Total System Global Area     658505728 bytes 
		
			Fixed Size                     2927864 bytes 
		
			Variable Size                285213448 bytes 
		
			Database Buffers             364904448 bytes 
		
			Redo Buffers                   5459968 bytes 
		
			RMAN> restore datafile 6; 
		
			Starting restore at 2018-04-10 10:57:02 
		
			allocated channel: ORA_DISK_1 
		
			channel ORA_DISK_1: SID=12 device type=DISK 
		
			channel ORA_DISK_1: starting datafile backup set
restore 
		
			channel ORA_DISK_1: specifying datafile(s) to restore
from backup set 
		
			channel ORA_DISK_1: restoring datafile 00006 to
/u04/oradata/lhr121/users01.dbf 
		
			channel ORA_DISK_1: reading from backup piece
/u04/flash_recovery_area/LHR121/backupset/2018_04_09/o1_mf_nnndf_TAG20180409T144738_fdp34bfm_.bkp 
		
			channel ORA_DISK_1: piece
handle=/u04/flash_recovery_area/LHR121/backupset/2018_04_09/o1_mf_nnndf_TAG20180409T144738_fdp34bfm_.bkp
tag=TAG20180409T144738 
		
			channel ORA_DISK_1: restored backup piece 1 
		
			channel ORA_DISK_1: restore complete, elapsed time:
00:00:03 
		
			Finished restore at 2018-04-10 10:57:06 
		
			RMAN> recover datafile 6; 
		
			Starting recover at 2018-04-10 10:57:13 
		
			using channel ORA_DISK_1 
		
			starting media recovery 
		
			archived log for thread 1 with sequence 39 is
already on disk as file /u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_39_fdp3bvmm_.arc 
		
			archived log for thread 1 with sequence 40 is
already on disk as file
/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_40_fdp3bvoz_.arc 
		
			archived log for thread 1 with sequence 41 is
already on disk as file
/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_41_fdp7bhhl_.arc 
		
			archived log for thread 1 with sequence 42 is
already on disk as file
/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_42_fdp7ccfv_.arc 
		
			archived log for thread 1 with sequence 43 is
already on disk as file
/u04/flash_recovery_area/LHR121/archivelog/2018_04_10/o1_mf_1_43_fdr7xxg4_.arc 
		
			archived log for thread 1 with sequence 44 is
already on disk as file
/u04/flash_recovery_area/LHR121/archivelog/2018_04_10/o1_mf_1_44_fdr7yc13_.arc 
		
			archived log file
name=/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_39_fdp3bvmm_.arc
thread=1 sequence=39 
		
			archived log file
name=/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_40_fdp3bvoz_.arc
thread=1 sequence=40 
		
			archived log file
name=/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_41_fdp7bhhl_.arc
thread=1 sequence=41 
		
			archived log file
name=/u04/flash_recovery_area/LHR121/archivelog/2018_04_09/o1_mf_1_42_fdp7ccfv_.arc
thread=1 sequence=42 
		
			media recovery complete, elapsed time: 00:00:01 
		
			Finished recover at 2018-04-10 10:57:15 
		
			RMAN> alter database open; 
		
			Statement processed 
		
			RMAN>

			SYS@lhr121> select * from v$nonlogged_block; 
		
FILE#     BLOCK#     BLOCKS NONLOGGED_START_CHANGE# NONLOGGED_START_TIM
NONLOGGED_END_CHANGE# NONLOGGED_END_TIME 
RESETLOGS_CHANGE# RESETLOGS_TIME     
OBJECT#                                
REASON      CON_ID 
		
			---------- ---------- ----------
----------------------- ------------------- --------------------- -------------------
----------------- ------------------- ----------------------------------------
------- ---------- 
		
6       1939          1                 2878238                                   2878238                                                          UNKNOWN           0 
		
			SYS@lhr121> select file#, block#,
scn_to_timestamp(NONLOGGED_START_CHANGE#) 
		
			  2  from v$nonlogged_block; 
		
FILE#     BLOCK#
SCN_TO_TIMESTAMP(NONLOGGED_START_CHANGE#) 
		
			---------- ---------- --------------------------------------------------------------------------- 
		
6       1939 10-APR-18
10.52.44.000000000 AM 
		
			SYS@lhr121> SELECT TABLESPACE_NAME, 
		
			  2         SEGMENT_TYPE, 
		
			  3         OWNER, 
		
			  4         SEGMENT_NAME, 
		
			  5         PARTITION_NAME 
		
			  6  FROM  
DBA_EXTENTS 
		
			  7  WHERE 
FILE_ID = 6 
		
			  8  AND   
1939 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1 
		
			  9  ; 
		
			TABLESPACE_NAME    
SEGMENT_TYPE       OWNER               SEGMENT_NAME       PARTITION_NAME 
		
			------------------- ------------------
------------------ ----------------- 
----------------- 
		
			USERS              
TABLE              SCOTT               T_LOG 
		
			SYS@lhr121> SELECT
DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID, 
		
			  2         DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
FILE_ID, 
		
			  3         DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
BLOCK_ID, 
		
			  4         COUNT(1) COUNTS 
		
			  5    FROM scott.t_log 
		
			  6   GROUP BY DBMS_ROWID.ROWID_OBJECT(ROWID), 
		
			  7           
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID), 
		
			  8           
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) 
		
			  9   ORDER BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);  
		
			 OBJECT_ID   
FILE_ID   BLOCK_ID     COUNTS 
		
			---------- ---------- ---------- ---------- 
		
94411          6       1939          9 
		
			SYS@lhr121>  
		
			SYS@lhr121> select * from  scott.t_log; 
		
			select * from 
scott.t_log 
		
			       * 
		
			ERROR at line 1: 
		
			ORA-01578: ORACLE data block corrupted (file # 6,
block # 1939) 
		
			ORA-01110: data file 6:
'/u04/oradata/lhr121/users01.dbf' 
		
			ORA-26040:
Data block was loaded using the NOLOGGING option 
		
			SYS@lhr121> select UNRECOVERABLE_CHANGE# ,     
		
			  2  UNRECOVERABLE_TIME    ,  
		
			  3  FIRST_NONLOGGED_SCN , 
		
			  4  FIRST_NONLOGGED_TIME from v$datafile where
file#=6; 
		
			UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME  FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM 
		
			--------------------- -------------------
------------------- ------------------- 
		
2878238 2018-04-10 10:53:47            
2878238 2018-04-10 10:53:47

實驗二：NOLOGGING+APPEND

			  
			LHR@ora11g > create table test_nologging as
select * from user_tables; 
		
			Table created. 
		
			LHR@ora11g > alter table test_nologging
nologging; 
		
			Table altered. 
		
			LHR@ora11g > select tablespace_name from
user_tables where table_name='TEST_NOLOGGING'; 
		
			TABLESPACE_NAME 
		
			------------------------------ 
		
			USERS 
		
			SYS@ora11g > alter database  no force logging; 
		
			Database altered. 
		
			SYS@ora11g > select force_logging from
v$database; 
		
			FOR 
		
			--- 
		
			NO 
		
			SYS@ora11g > alter database drop  supplemental log data; 
		
			Database altered. 
		
			SYS@ora11g > alter database  drop 
supplemental log data(all,primary key,unique,foreign key) columns; 
		
			Database altered. 
		
			SYS@ora11g > SELECT supplemental_log_data_min
min, 
		
			  2         supplemental_log_data_pk pk, 
		
			  3         supplemental_log_data_ui ui, 
		
			  4         supplemental_log_data_fk fk, 
		
			  5         supplemental_log_data_all allc 
		
			  6    FROM v$database; 
		
			MIN     
PK  UI  FK  ALL 
		
			-------- --- --- --- --- 
		
			NO      
NO  NO  NO  NO 
		
			LHR@ora11g > exit 
		
			Disconnected from Oracle Database 11g Enterprise
Edition Release 11.2.0.3.0 - 64bit Production 
		
			With the Partitioning, OLAP, Data Mining and Real
Application Testing options 
		
			[oracle@rhel6lhr env_oracle]$ rman target / 
		
			Recovery Manager: Release 11.2.0.3.0 - Production
on Tue Apr 10 13:27:25 2018 
		
			Copyright (c) 1982, 2011, Oracle and/or its
affiliates.  All rights reserved. 
		
			connected to target database: ORA11G
(DBID=4270446895) 
		
			RMAN> backup datafile 4; 
		
			Starting backup at 2018-04-10 13:27:37 
		
			using target database control file instead of
recovery catalog 
		
			allocated channel: ORA_DISK_1 
		
			channel ORA_DISK_1: SID=155 device type=DISK 
		
			channel ORA_DISK_1: starting full datafile backup
set 
		
			channel ORA_DISK_1: specifying datafile(s) in
backup set 
		
			input datafile file number=00004
name=/u01/app/oracle/oradata/ora11g/users01.dbf 
		
			channel ORA_DISK_1: starting piece 1 at 2018-04-10
13:27:39 
		
			channel ORA_DISK_1: finished piece 1 at 2018-04-10
13:29:36 
		
			piece handle=/u05/app/oracle/flash_recovery_area/ORA11G/backupset/2018_04_10/o1_mf_nnndf_TAG20180410T132739_fdrltcot_.bkp
tag=TAG20180410T132739 comment=NONE 
		
			channel ORA_DISK_1: backup set complete, elapsed
time: 00:01:58 
		
			Finished backup at 2018-04-10 13:29:37 
		
			RMAN> exit 
		
			Recovery Manager complete. 
		
			[oracle@rhel6lhr oradata]$ sas 
		
			SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr
10 14:14:15 2018 
		
			Copyright (c) 1982, 2011, Oracle.  All rights reserved. 
		
			Connected to: 
		
			Oracle Database 11g Enterprise Edition Release
11.2.0.3.0 - 64bit Production 
		
			With the Partitioning, OLAP, Data Mining and Real
Application Testing options 
		
			SYS@ora11g > conn lhr/lhr 
		
			Connected. 
		
			LHR@ora11g > insert /*+append */ into
TEST_NOLOGGING select * from test_nologging; 
		
			3264 rows created. 
		
			LHR@ora11g > commit; 
		
			Commit complete. 
		
			LHR@ora11g > exit 
		
			Disconnected from Oracle Database 11g Enterprise
Edition Release 11.2.0.3.0 - 64bit Production 
		
			With the Partitioning, OLAP, Data Mining and Real
Application Testing options 
		
			[oracle@rhel6lhr oradata]$ mv /u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf_bk 
		
			[oracle@rhel6lhr oradata]$  
		
			[oracle@rhel6lhr oradata]$ sas 
		
			SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr
10 14:11:34 2018 
		
			Copyright (c) 1982, 2011, Oracle.  All rights reserved. 
		
			Connected to: 
		
			Oracle Database 11g Enterprise Edition Release
11.2.0.3.0 - 64bit Production 
		
			With the Partitioning, OLAP, Data Mining and Real
Application Testing options 
		
			SYS@ora11g > startup force mount 
		
			exit 
		
			ORACLE instance started. 
		
			Total System Global Area  409194496 bytes 
		
			Fixed Size                  2228864 bytes 
		
			Variable Size             322964864 bytes 
		
			Database Buffers           75497472 bytes 
		
			Redo Buffers                8503296 bytes 
		
			Database mounted. 
		
			SYS@ora11g > Disconnected from Oracle Database
11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 
		
			With the Partitioning, OLAP, Data Mining and Real
Application Testing options 
		
			[oracle@rhel6lhr oradata]$  
		
			[oracle@rhel6lhr oradata]$  
		
			[oracle@rhel6lhr oradata]$  
		
			[oracle@rhel6lhr oradata]$  
		
			[oracle@rhel6lhr oradata]$ rman target / 
		
			Recovery Manager: Release 11.2.0.3.0 - Production
on Tue Apr 10 14:11:57 2018 
		
			Copyright (c) 1982, 2011, Oracle and/or its
affiliates.  All rights reserved. 
		
			connected to target database: ORA11G
(DBID=4270446895, not open) 
		
			RMAN> restore datafile 4; 
		
			Starting restore at 2018-04-10 14:12:07 
		
			using target database control file instead of
recovery catalog 
		
			allocated channel: ORA_DISK_1 
		
			channel ORA_DISK_1: SID=133 device type=DISK 
		
			channel ORA_DISK_1: starting datafile backup set
restore 
		
			channel ORA_DISK_1: specifying datafile(s) to
restore from backup set 
		
			channel ORA_DISK_1: restoring datafile 00004 to
/u01/app/oracle/oradata/ora11g/users01.dbf 
		
			channel ORA_DISK_1: reading from backup piece
/u05/app/oracle/flash_recovery_area/ORA11G/backupset/2018_04_10/o1_mf_nnndf_TAG20180410T132739_fdrltcot_.bkp 
		
			channel ORA_DISK_1: piece
handle=/u05/app/oracle/flash_recovery_area/ORA11G/backupset/2018_04_10/o1_mf_nnndf_TAG20180410T132739_fdrltcot_.bkp
tag=TAG20180410T132739 
		
			channel ORA_DISK_1: restored backup piece 1 
		
			channel ORA_DISK_1: restore complete, elapsed time:
00:01:16 
		
			Finished restore at 2018-04-10 14:13:24 
		
			RMAN> recover datafile 4; 
		
			Starting recover at 2018-04-10 14:13:34 
		
			using channel ORA_DISK_1 
		
			starting media recovery 
		
			archived log for thread 1 with sequence 330 is
already on disk as file
/u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_330_fdrm7pm4_.arc 
		
			archived log for thread 1 with sequence 331 is
already on disk as file /u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_331_fdrmly0v_.arc 
		
			archived log for thread 1 with sequence 332 is
already on disk as file
/u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_332_fdrn29bv_.arc 
		
			archived log for thread 1 with sequence 333 is
already on disk as file
/u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_333_fdrnohdf_.arc 
		
			archived log for thread 1 with sequence 334 is
already on disk as file /u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_334_fdrnwqqw_.arc 
		
			archived log file
name=/u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_330_fdrm7pm4_.arc
thread=1 sequence=330 
		
			archived log file
name=/u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_331_fdrmly0v_.arc
thread=1 sequence=331 
		
			archived log file
name=/u05/app/oracle/flash_recovery_area/ORA11G/archivelog/2018_04_10/o1_mf_1_332_fdrn29bv_.arc
thread=1 sequence=332 
		
			media recovery complete, elapsed time: 00:00:01 
		
			Finished recover at 2018-04-10 14:13:37 
		
			RMAN> alter database open; 
		
			database opened 
		
			RMAN> exit 
		
			Recovery Manager complete. 
		
			[oracle@rhel6lhr oradata]$ sas 
		
			SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr
10 14:14:15 2018 
		
			Copyright (c) 1982, 2011, Oracle.  All rights reserved. 
		
			Connected to: 
		
			Oracle Database 11g Enterprise Edition Release
11.2.0.3.0 - 64bit Production 
		
			With the Partitioning, OLAP, Data Mining and Real
Application Testing options 
		
			SYS@ora11g > select count(1) from
lhr.test_nologging; 
		
			select count(1) from lhr.test_nologging 
		
			                         * 
		
			ERROR at line 1: 
		
			ORA-01578: ORACLE data block corrupted (file # 4,
block # 180937) 
		
			ORA-01110: data file 4:
'/u01/app/oracle/oradata/ora11g/users01.dbf' 
		
			ORA-26040: Data block was
loaded using the NOLOGGING option 
		
			SYS@ora11g > select UNRECOVERABLE_CHANGE# ,     
		
			  2         UNRECOVERABLE_TIME    ,  
		
			  3         FIRST_NONLOGGED_SCN , 
		
			  4         FIRST_NONLOGGED_TIME  
		
			  5  from v$datafile  
		
			  6  where file#=4; 
		
			UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME  FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM 
		
			--------------------- -------------------
------------------- ------------------- 
		
60522292 2018-04-10 14:11:22           
60522291 2018-04-10 14:11:22 
		
			SYS@ora11g > select * from
v$database_block_corruption; 
		
			no rows selected 
		
			SYS@ora11g > exit 
		
			Disconnected from Oracle Database 11g Enterprise
Edition Release 11.2.0.3.0 - 64bit Production 
		
			With the Partitioning, OLAP, Data Mining and Real
Application Testing options 
		
			[oracle@rhel6lhr oradata]$ rman target / 
		
			Recovery Manager: Release 11.2.0.3.0 - Production
on Tue Apr 10 14:15:42 2018 
		
			Copyright (c) 1982, 2011, Oracle and/or its
affiliates.  All rights reserved. 
		
			connected to target database: ORA11G
(DBID=4270446895) 
		
			RMAN> validate datafile 4; 
		
			Starting validate at 2018-04-10 14:15:50 
		
			using target database control file instead of
recovery catalog 
		
			allocated channel: ORA_DISK_1 
		
			channel ORA_DISK_1: SID=21 device type=DISK 
		
			channel ORA_DISK_1: starting validation of datafile 
		
			channel ORA_DISK_1: specifying datafile(s) for validation 
		
			input datafile file number=00004
name=/u01/app/oracle/oradata/ora11g/users01.dbf 
		
			channel ORA_DISK_1: validation complete, elapsed
time: 00:00:35 
		
			List of Datafiles 
		
			================= 
		
			File Status Marked Corrupt Empty Blocks Blocks
Examined High SCN 
		
			---- ------ -------------- ------------
--------------- ---------- 
		
			4    OK     103            64922        196829          60543025   
		
			  File Name:
/u01/app/oracle/oradata/ora11g/users01.dbf 
		
			  Block Type
Blocks Failing Blocks Processed 
		
			  ----------
-------------- ---------------- 
		
			  Data       0              19747            
		
			  Index      0              5352             
		
			  Other      0              106779           
		
			Finished validate at 2018-04-10 14:16:26 
		
			RMAN> exit 
		
			Recovery Manager complete. 
		
			[oracle@rhel6lhr oradata]$ sas 
		
			SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr
10 14:16:44 2018 
		
			Copyright (c) 1982, 2011, Oracle.  All rights reserved. 
		
			Connected to: 
		
			Oracle Database 11g Enterprise Edition Release
11.2.0.3.0 - 64bit Production 
		
			With the Partitioning, OLAP, Data Mining and Real
Application Testing options 
		
			SYS@ora11g > select * from
v$database_block_corruption; 
		
FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO 
		
			---------- ---------- ---------- ------------------
--------- 
		
4     180937         15           60522291 NOLOGGING 
		
4     180994         88           60522292 NOLOGGING 
		
			SYS@ora11g > exit 
		
			Disconnected from Oracle Database 11g Enterprise
Edition Release 11.2.0.3.0 - 64bit Production 
		
			With the Partitioning, OLAP, Data Mining and Real
Application Testing options 
		
			[oracle@rhel6lhr oradata]$ rmant target / 
		
			-bash: rmant: command not found 
		
			[oracle@rhel6lhr oradata]$ rman target /  
		
			Recovery Manager: Release 11.2.0.3.0 - Production
on Tue Apr 10 14:21:04 2018 
		
			Copyright (c) 1982, 2011, Oracle and/or its
affiliates.  All rights reserved. 
		
			connected to target database: ORA11G
(DBID=4270446895) 
		
			RMAN> BLOCKRECOVER CORRUPTION LIST; 
		
			Starting recover at 2018-04-10 14:21:08 
		
			using target database control file instead of
recovery catalog 
		
			allocated channel: ORA_DISK_1 
		
			channel ORA_DISK_1: SID=133 device type=DISK 
		
			starting media recovery 
		
			media recovery complete, elapsed time: 00:00:00 
		
			Finished recover at 2018-04-10 14:21:10 
		
			RMAN> exit 
		
			Recovery Manager complete. 
		
			[oracle@rhel6lhr oradata]$ sas 
		
			SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr
10 14:21:17 2018 
		
			Copyright (c) 1982, 2011, Oracle.  All rights reserved. 
		
			Connected to: 
		
			Oracle Database 11g Enterprise Edition Release
11.2.0.3.0 - 64bit Production 
		
			With the Partitioning, OLAP, Data Mining and Real
Application Testing options 
		
			SYS@ora11g > select * from
v$database_block_corruption; 
		
FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO 
		
			---------- ---------- ---------- ------------------
--------- 
		
4     180937         15          
60522291 NOLOGGING 
		
4     180994         88           60522292 NOLOGGING

About Me

.............................................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：

.............................................................................................................................................

● QQ群號：230161599（滿）、618766405

● 微信群：可加我微信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友（646634621），註明新增緣由

● 於 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成

● 最新修改時間：2018-04-01 06:00 ~ 2018-04-31 24:00

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店：

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

● 小麥苗OCP、OCM、高可用網路班：http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

小麥苗的微信公眾號小麥苗的DBA寶典QQ群2 《DBA筆試面試寶典》讀者群小麥苗的微店

.............................................................................................................................................

ORA-01578和ORA-26040--NOLOGGING操作引起的壞塊-錯誤解釋和解決方案

ORA-01578和ORA-26040--NOLOGGING操作引起的壞塊-錯誤解釋和解決方案(文件ID 1623284.1)

實驗一：DISABLE_ARCHIVE_LOGGING:Y

實驗二：NOLOGGING+APPEND

相關文章