溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

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

發(fā)布時(shí)間：2020-07-08 20:13:40 來(lái)源：網(wǎng)絡(luò) 閱讀：1302 作者：小麥苗best 欄目：關(guān)系型數(shù)據(jù)庫(kù)

（一）NOLOGGING操作引起的壞塊（ORA-01578和ORA-26040）簡(jiǎn)介

如果只是錯(cuò)誤ORA-01578，而沒(méi)有伴隨ORA-26040，那么這個(gè)壞塊是由其它的原因引起的壞塊，可以嘗試使用RMAN的BMR（Block Media Recovery）修復(fù)。

如果數(shù)據(jù)段（表段、索引段）被定義為NOLOGGING屬性，那么當(dāng)NOLOGGING加APPEND、UNRECOVERABLE操作修改該數(shù)據(jù)段或者使用數(shù)據(jù)泵（DATAPUMP）impdp參數(shù)DISABLE_ARCHIVE_LOGGING:Y時(shí)，聯(lián)機(jī)重做日志只會(huì)記錄很少的日志信息。如果這些聯(lián)機(jī)重做日志或歸檔日志被用來(lái)恢復(fù)數(shù)據(jù)文件，那么Oracle會(huì)將對(duì)應(yīng)的數(shù)據(jù)塊標(biāo)志為無(wú)效（Soft Corrupt），而且下一次訪問(wèn)這些數(shù)據(jù)塊時(shí)，會(huì)報(bào)ORA-01578和ORA-26040錯(cuò)誤。

例如：

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)入時(shí)會(huì)禁止LOGGING定義，而產(chǎn)生NOLOGGING操作。如果相應(yīng)的datafile被restored和recovered，那么接下來(lái)的涉及到目標(biāo)表的查詢會(huì)報(bào)錯(cuò)ORA-1578和ORA-26040。如果數(shù)據(jù)庫(kù)是FORCE LOGGING模式，那么DISABLE_ARCHIVE_LOGGING選項(xiàng)不會(huì)關(guān)閉LOGGING。

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

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

NOLOGGING導(dǎo)致的壞塊不會(huì)導(dǎo)致RMAN備份失敗。一般來(lái)說(shuō)soft corrupt block不會(huì)導(dǎo)致RMAN備份失敗，不需要設(shè)置MAXCORRUPT。數(shù)據(jù)庫(kù)備份中就會(huì)含有soft corrupt block，如果使用這些備份恢復(fù)數(shù)據(jù)，那么恢復(fù)的數(shù)據(jù)也含有soft corrupt block。

除ORA-26040錯(cuò)誤之外，當(dāng)還有一些其他通用信息出現(xiàn)時(shí)，block dump可能會(huì)被產(chǎn)生。如果數(shù)據(jù)塊的block dump內(nèi)有byte 0xff信息或者屬于某個(gè)段，ORA-1578和ORA-26040會(huì)因?yàn)榻橘|(zhì)恢復(fù)了NOLOGGING的部分導(dǎo)致了corruption而出現(xiàn)。

（二）利用RMAN、DBV檢測(cè)NOLOGGING導(dǎo)致的壞塊

DBV在檢測(cè)壞塊時(shí)，如果RDBMS版本小于10.2.0.4，那么DBV打印錯(cuò)誤DBV-200，如果RDBMS版本大于或等于10.2.0.4，那么DBV打印錯(cuò)誤DBV-201：

DBV-00200: Block, dba 46137428, already marked corrupted

DBV-00201: Block, DBA 46137428, marked corrupt for invalid redo application

RMAN的VALIDATE命令可以用來(lái)檢測(cè)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在檢測(cè)壞塊時(shí)，如果RDBMS版本小于10.2.0.5和11.1.0.7，RMAN打印如下錯(cuò)誤：

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報(bào)告，查看視圖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”去驗(yàn)證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

在告警日志中會(huì)更新以下信息：

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操作，并且之后在沒(méi)有備份的情況下，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

當(dāng)初始化參數(shù)db_unrecoverable_scn_tracking設(shè)置為true（默認(rèn)值，該參數(shù)在10g中是不可用的），那么V$DATAFILE中以下列會(huì)被更新；

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的情況下，物理備庫(kù)的MRP進(jìn)程會(huì)檢查出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: ..."

（四）識(shí)別數(shù)據(jù)塊什么時(shí)候被標(biāo)志為NOLOGGING

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

①　使用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到十進(jìn)制511453045995。

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

如果運(yùn)行RMAN validate命令后，v$database_block_coruption視圖中corruption_type='NOLOGGING' (10.2.0.5 和 11.2.0.1+)，那么CORRUPTION_CHANGE#列的值就是十進(jìn)制的SCN值?？梢允褂孟旅娴姆椒ǐ@得SCN Timestamp時(shí)間：

select scn_to_timestamp(&&decimal_scn) from dual;

如果運(yùn)行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遇到錯(cuò)誤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#;

 

 

如果運(yùn)行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等出現(xiàn)NOARCHIVELOG和NOLOGGING問(wèn)題

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

RDBMS版本變化：

RDBMS版本

變化

10.2.0.4+

DBverify報(bào)告NOLOGGING block錯(cuò)誤信息 "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參數(shù)

11.1.0.6 or 11.1.0.7 or 11.2.0.1

NOARCHIVELOG模式數(shù)據(jù)庫(kù)，對(duì)NOLOGGING對(duì)象執(zhí)行了DIRECT PATH操作，并且以后手動(dòng)恢復(fù)數(shù)據(jù)庫(kù)，即使打開了FORCE LOGGING，也會(huì)報(bào)ORA-1578 和 ORA-26040。這個(gè)約束在11.2.0.2以上版本取消，這個(gè)問(wèn)題在10g不會(huì)發(fā)生。

12c

RMAN validate的結(jié)果不在視圖v$database_block_corruption中，而是在視圖v$nonlogged_block

12.2

以下RMAN命令被引入：

RMAN> validate [database / datafile] nonlogged block;

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

 

（六）解決方法

NOLOGGING操作引起的壞塊是不能修復(fù)的，比如“Media Recovery”或“RMAN blockrecover”都無(wú)法修復(fù)這種壞塊?？尚械姆椒ㄊ窃?/span>NOLOGGING操作之后立刻備份對(duì)應(yīng)的數(shù)據(jù)文件。

如果錯(cuò)誤是執(zhí)行RMAN DUPLICATE 或 RESTORE之后產(chǎn)生的，那么在源庫(kù)打開FORCE LOGGING，然后再重新運(yùn)行RMAN DUPLICATE 或 RESTORE。

alter database force logging;

如果錯(cuò)誤出現(xiàn)在物理STANDBY數(shù)據(jù)庫(kù)，那么可以從主庫(kù)恢復(fù)被影響的數(shù)據(jù)文件（只有當(dāng)主庫(kù)沒(méi)有這個(gè)問(wèn)題的情況下）。參考文檔Doc ID 958181.1。在Oracle 12c中可以使用RMAN選項(xiàng)RECOVER NONLOGGED BLOCK with DATAFILE、TABLESPACE、DATABASE。例如：

RMAN> RECOVER DATABASE NONLOGGED BLOCK;

為了避免這個(gè)問(wèn)題發(fā)生，在主庫(kù)強(qiáng)制生產(chǎn)日志：

alter database force logging;

如果同一個(gè)datafile的數(shù)據(jù)塊在主庫(kù)出現(xiàn)nologging壞塊，但是備庫(kù)沒(méi)有，可以通過(guò)手動(dòng)跳過(guò)(dbms_repair)壞塊或者設(shè)置event 10231。主庫(kù)出現(xiàn)nologging壞塊可能是由于主庫(kù)執(zhí)行過(guò)備份恢復(fù)或者之前是備庫(kù)，執(zhí)行了switchover。

如果NOLOGGING數(shù)據(jù)塊位于空閑數(shù)據(jù)塊（dba_free_space視圖可以查詢到），那么DBVerify檢查會(huì)發(fā)現(xiàn)這個(gè)問(wèn)題，報(bào)錯(cuò)DBV-00201或者在v$database_block_corruption視圖中顯示。對(duì)于這種情況，可以等待到這個(gè)數(shù)據(jù)塊被重用時(shí)會(huì)自動(dòng)格式化或者手動(dòng)強(qiáng)制格式化。

如果是索引，那么可以重新創(chuàng)建（drop/create）索引。如果是表，那么可以使用存儲(chǔ)過(guò)程DBMS_REPAIR.SKIP_CORRUPT_BLOCKS跳過(guò)壞塊，然后考慮是否重建表。

在刪除有壞塊的段之后，這個(gè)壞塊就處于空閑狀態(tài)，后續(xù)可以被分配給其他對(duì)象或段，當(dāng)這個(gè)壞塊被分配給其它對(duì)象或段時(shí)，這個(gè)數(shù)據(jù)塊被重新格式化。如果v$database_block_corruption視圖中還是顯示為壞塊，那么可以手動(dòng)運(yùn)行rman validate來(lái)清除視圖中的信息。

如果是LOB，那么請(qǐng)參考Note 293515.1。

實(shí)驗(yàn)一：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

 

實(shí)驗(yàn)二：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

 



向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手