【DB笔试面试792】在Oracle中，ORA-01578和ORA-26040--NOLOGGING操作引起的坏块解决方案

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

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

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

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

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

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

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

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

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

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

"INVD_BLKS: Invalidating (file <file number>, bno <block number>)"
"fname: 'Datafile name'. rdba: ..."

  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

SELECT SCN_TO_TIMESTAMP(&&DECIMAL_SCN) FROM DUAL;

SELECT FILE#, BLOCK#, SCN_TO_TIMESTAMP(CORRUPTION_CHANGE#)
FROM V$DATABASE_BLOCK_CORRUPTION
WHERE CORRUPTION_TYPE='NOLOGGING';

SELECT FILE#, BLOCK#, SCN_TO_TIMESTAMP(NONLOGGED_START_CHANGE#) FROM V$NONLOGGED_BLOCK;

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#;

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#;

alter database force logging;

RMAN> RECOVER DATABASE NONLOGGED BLOCK;

ALTER DATABASE FORCE LOGGING;