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

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


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

如果只是錯誤ORA-01578,而沒有伴隨ORA-26040川队,那么這個壞塊是由其它的原因引起的壞塊施无,可以嘗試使用RMAN的BMR(Block Media Recovery)修復(fù)。

如果數(shù)據(jù)段(表段、索引段)被定義為NOLOGGING屬性仰美,那么當NOLOGGING加APPEND妻率、UNRECOVERABLE操作修改該數(shù)據(jù)段或者使用數(shù)據(jù)泵(DATAPUMP)impdp參數(shù)DISABLE_ARCHIVE_LOGGING:Y時,聯(lián)機重做日志只會記錄很少的日志信息盟戏。如果這些聯(lián)機重做日志或歸檔日志被用來恢復(fù)數(shù)據(jù)文件绪妹,那么Oracle會將對應(yīng)的數(shù)據(jù)塊標志為無效(Soft Corrupt),而且下一次訪問這些數(shù)據(jù)塊時柿究,會報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

數(shù)據(jù)字典視圖DBA_TABLES、DBA_INDEXES蝇摸、DBA_LOBS婶肩、DBA_TAB_PARTITIONS办陷、DBA_LOB_PARTITIONS、DBA_TAB_SUBPARTITIONS中的LOGGING列記錄了NOLOGGING屬性狡孔。若LOGGING='NO'則表示NOLOGGING懂诗。

數(shù)據(jù)泵DATAPUMP的impdp參數(shù)DISABLE_ARCHIVE_LOGGING:Y在執(zhí)行導(dǎo)入時會禁止LOGGING定義,而產(chǎn)生NOLOGGING操作苗膝。如果相應(yīng)的datafile被restored和recovered殃恒,那么接下來的涉及到目標表的查詢會報錯ORA-1578和ORA-26040。如果數(shù)據(jù)庫是FORCE LOGGING模式辱揭,那么DISABLE_ARCHIVE_LOGGING選項不會關(guān)閉LOGGING离唐。

impdp使用參數(shù)“DISABLE_ARCHIVE_LOGGING:Y”的一個例子:

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

NOLOGGING導(dǎo)致的壞塊不會導(dǎo)致RMAN備份失敗。一般來說soft corrupt block不會導(dǎo)致RMAN備份失敗问窃,不需要設(shè)置MAXCORRUPT亥鬓。數(shù)據(jù)庫備份中就會含有soft corrupt block,如果使用這些備份恢復(fù)數(shù)據(jù)域庇,那么恢復(fù)的數(shù)據(jù)也含有soft corrupt block嵌戈。

除ORA-26040錯誤之外,當還有一些其他通用信息出現(xiàn)時听皿,block dump可能會被產(chǎn)生熟呛。如果數(shù)據(jù)塊的block dump內(nèi)有byte 0xff信息或者屬于某個段,ORA-1578和ORA-26040會因為介質(zhì)恢復(fù)了NOLOGGING的部分導(dǎo)致了corruption而出現(xiàn)尉姨。

(二)利用RMAN庵朝、DBV檢測NOLOGGING導(dǎo)致的壞塊

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數(shù)據(jù)塊覆致,檢查結(jié)果記錄在視圖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的結(jié)果不在視圖v$database_block_corruption中,而是在視圖v$nonlogged_block赞枕。從12.2?版本開始澈缺,可以使用新的命令:“validate .. nonlogged block”去驗證nologging的block坪创。

在以下的例子中,數(shù)據(jù)文件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


(三)監(jiān)控NOLOGGING操作

若執(zhí)行了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

當初始化參數(shù)db_unrecoverable_scn_tracking設(shè)置為true(默認值项滑,該參數(shù)在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+版本中枪狂,設(shè)置event 16490的情況下危喉,物理備庫的MRP進程會檢查出NOLOGGING變化,并記錄在alert log州疾。

ORA-16490 "logging invalidated blocks on standby due to invalidation redo"


"INVD_BLKS: Invalidating (file , bno )"???

"fname: 'Datafile name'. rdba: ..."

(四)識別數(shù)據(jù)塊什么時候被標志為NOLOGGING

識別數(shù)據(jù)塊什么時候被標志為NOLOGGING辜限,可以將trace文件中數(shù)據(jù)塊SCN或者v$database_block_coruption視圖中CORRUPTION_CHANGE#值轉(zhuǎn)換為時間:

① 使用trace文件中數(shù)據(jù)塊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,刪除'.'严蓖,然后轉(zhuǎn)換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 varchivedlog,varchivedlog,vdatabase_block_corruption

where CORRUPTION_CHANGE# between first_change# and next_change#

and CORRUPTION_TYPE='NOLOGGING';



select file#,block#,first_time

from?? vloghistory,vloghistory,vdatabase_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 vnonloggedblock,vnonloggedblock,varchived_log

where NONLOGGED_START_CHANGE# between first_change# and next_change#;



select file#, block#, first_time

from vnonloggedblock,vnonloggedblock,vlog_history

where NONLOGGED_START_CHANGE# between first_change# and next_change#;


(五)SYSAUX表空間杭措、AWR费什、EM等出現(xiàn)NOARCHIVELOG和NOLOGGING問題

如果數(shù)據(jù)庫版本是11.1.0.6?或?11.1.0.7?或?11.2.0.1,對NOLOGGING對象執(zhí)行過DIRECT PATH操作手素,并且后續(xù)執(zhí)行了RECOVER DATABASE命令鸳址,即使數(shù)據(jù)庫FORCE LOGGING是打開的情況下,會出現(xiàn)ORA-1578和ORA-26040錯誤泉懦。這種問題經(jīng)常發(fā)生在SYSAUX表空間中的AWR或EM對象稿黍。請參考Note 1071869.1。注意數(shù)據(jù)庫當前版本可能已經(jīng)大于11.1?或者?11.2.0.1但是問題可能是在升級之前產(chǎn)生的崩哩。這個約束在11.2.0.2以上版本中取消巡球,這個問題在10g不會發(fā)生。

RDBMS版本變化:

RDBMS版本變化

10.2.0.4+DBverify報告NOLOGGING block錯誤信息?"DBV-00201: Block, DBA , 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參數(shù)

11.1.0.6 or 11.1.0.7 or 11.2.0.1NOARCHIVELOG模式數(shù)據(jù)庫酣栈,對NOLOGGING對象執(zhí)行了DIRECT PATH操作,并且以后手動恢復(fù)數(shù)據(jù)庫汹押,即使打開了FORCE LOGGING矿筝,也會報ORA-1578?和?ORA-26040。這個約束在11.2.0.2以上版本取消棚贾,這個問題在10g不會發(fā)生窖维。

12cRMAN validate的結(jié)果不在視圖v$database_block_corruption中榆综,而是在視圖v$nonlogged_block

12.2以下RMAN命令被引入:

RMAN> validate [database / datafile] nonlogged block;

RMAN> recover [database / datafile] nonlogged block; ->?對于?Standby?數(shù)據(jù)庫


(六)解決方法

NOLOGGING操作引起的壞塊是不能修復(fù)的,比如“Media Recovery”或“RMAN blockrecover”都無法修復(fù)這種壞塊铸史”谴可行的方法是在NOLOGGING操作之后立刻備份對應(yīng)的數(shù)據(jù)文件。

如果錯誤是執(zhí)行RMAN DUPLICATE?或?RESTORE之后產(chǎn)生的琳轿,那么在源庫打開FORCE LOGGING判沟,然后再重新運行RMAN DUPLICATE?或?RESTORE。

alter database force logging;

如果錯誤出現(xiàn)在物理STANDBY數(shù)據(jù)庫利赋,那么可以從主庫恢復(fù)被影響的數(shù)據(jù)文件(只有當主庫沒有這個問題的情況下)水评。參考文檔Doc ID 958181.1。在Oracle 12c中可以使用RMAN選項RECOVER NONLOGGED BLOCK with DATAFILE媚送、TABLESPACE中燥、DATABASE。例如:

RMAN> RECOVER DATABASE NONLOGGED BLOCK;

為了避免這個問題發(fā)生塘偎,在主庫強制生產(chǎn)日志:

alter database force logging;

如果同一個datafile的數(shù)據(jù)塊在主庫出現(xiàn)nologging壞塊疗涉,但是備庫沒有,可以通過手動跳過(dbms_repair)壞塊或者設(shè)置event 10231吟秩。主庫出現(xiàn)nologging壞塊可能是由于主庫執(zhí)行過備份恢復(fù)或者之前是備庫咱扣,執(zhí)行了switchover。

如果NOLOGGING數(shù)據(jù)塊位于空閑數(shù)據(jù)塊(dba_free_space視圖可以查詢到)涵防,那么DBVerify檢查會發(fā)現(xiàn)這個問題闹伪,報錯DBV-00201或者在v$database_block_corruption視圖中顯示。對于這種情況壮池,可以等待到這個數(shù)據(jù)塊被重用時會自動格式化或者手動強制格式化偏瓤。

如果是索引,那么可以重新創(chuàng)建(drop/create)索引椰憋。如果是表厅克,那么可以使用存儲過程DBMS_REPAIR.SKIP_CORRUPT_BLOCKS跳過壞塊,然后考慮是否重建表橙依。

在刪除有壞塊的段之后证舟,這個壞塊就處于空閑狀態(tài),后續(xù)可以被分配給其他對象或段窗骑,當這個壞塊被分配給其它對象或段時女责,這個數(shù)據(jù)塊被重新格式化。如果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

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

● 本文作者:小麥苗辛藻,部分內(nèi)容整理自網(wǎng)絡(luò),若有侵權(quán)請聯(lián)系小麥苗刪除

● 本文在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/

● 數(shù)據(jù)庫筆試面試題庫及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

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

● QQ群號:230161599(滿)、618766405

● 微信群:可加我微信仰禽,我拉大家進群氮墨,非誠勿擾

● 聯(lián)系我請加QQ好友(646634621),注明添加緣由

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

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

● 文章內(nèi)容來源于小麥苗的學(xué)習(xí)筆記吐葵,部分整理自網(wǎng)絡(luò)规揪,若有侵權(quán)或不當之處還請諒解

● 版權(quán)所有,歡迎分享本文温峭,轉(zhuǎn)載請保留出處

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

●?小麥苗的微店:https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

●?小麥苗出版的數(shù)據(jù)庫類叢書:http://blog.itpub.net/26736162/viewspace-2142121/

●?小麥苗OCP猛铅、OCM、高可用網(wǎng)絡(luò)班:http://blog.itpub.net/26736162/viewspace-2148098/

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

使用微信客戶端掃描下面的二維碼來關(guān)注小麥苗的微信公眾號(xiaomaimiaolhr)及QQ群(DBA寶典)凤藏,學(xué)習(xí)最實用的數(shù)據(jù)庫技術(shù)奸忽。

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

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

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市揖庄,隨后出現(xiàn)的幾起案子栗菜,更是在濱河造成了極大的恐慌,老刑警劉巖蹄梢,帶你破解...
    沈念sama閱讀 222,590評論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件疙筹,死亡現(xiàn)場離奇詭異,居然都是意外死亡禁炒,警方通過查閱死者的電腦和手機而咆,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,157評論 3 399
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來齐苛,“玉大人翘盖,你說我怎么就攤上這事“挤洌” “怎么了馍驯?”我有些...
    開封第一講書人閱讀 169,301評論 0 362
  • 文/不壞的土叔 我叫張陵,是天一觀的道長玛痊。 經(jīng)常有香客問我汰瘫,道長,這世上最難降的妖魔是什么擂煞? 我笑而不...
    開封第一講書人閱讀 60,078評論 1 300
  • 正文 為了忘掉前任混弥,我火速辦了婚禮,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘蝗拿。我一直安慰自己晾捏,他們只是感情好,可當我...
    茶點故事閱讀 69,082評論 6 398
  • 文/花漫 我一把揭開白布哀托。 她就那樣靜靜地躺著惦辛,像睡著了一般。 火紅的嫁衣襯著肌膚如雪仓手。 梳的紋絲不亂的頭發(fā)上胖齐,一...
    開封第一講書人閱讀 52,682評論 1 312
  • 那天,我揣著相機與錄音嗽冒,去河邊找鬼呀伙。 笑死,一個胖子當著我的面吹牛添坊,可吹牛的內(nèi)容都是我干的剿另。 我是一名探鬼主播,決...
    沈念sama閱讀 41,155評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼帅腌,長吁一口氣:“原來是場噩夢啊……” “哼驰弄!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起速客,我...
    開封第一講書人閱讀 40,098評論 0 277
  • 序言:老撾萬榮一對情侶失蹤戚篙,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后溺职,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體岔擂,經(jīng)...
    沈念sama閱讀 46,638評論 1 319
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,701評論 3 342
  • 正文 我和宋清朗相戀三年浪耘,在試婚紗的時候發(fā)現(xiàn)自己被綠了乱灵。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 40,852評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡七冲,死狀恐怖痛倚,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情澜躺,我是刑警寧澤蝉稳,帶...
    沈念sama閱讀 36,520評論 5 351
  • 正文 年R本政府宣布,位于F島的核電站掘鄙,受9級特大地震影響耘戚,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜操漠,卻給世界環(huán)境...
    茶點故事閱讀 42,181評論 3 335
  • 文/蒙蒙 一收津、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧,春花似錦撞秋、人聲如沸长捧。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,674評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽唆姐。三九已至,卻和暖如春廓八,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背赵抢。 一陣腳步聲響...
    開封第一講書人閱讀 33,788評論 1 274
  • 我被黑心中介騙來泰國打工剧蹂, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人烦却。 一個月前我還...
    沈念sama閱讀 49,279評論 3 379
  • 正文 我出身青樓宠叼,卻偏偏與公主長得像,于是被迫代替她去往敵國和親其爵。 傳聞我的和親對象是個殘疾皇子冒冬,可洞房花燭夜當晚...
    茶點故事閱讀 45,851評論 2 361

推薦閱讀更多精彩內(nèi)容