watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

          subject         part

【DB Written interview 792】 stay Oracle in ,ORA-01578 and ORA-26040--NOLOGGING Bad block caused by operation - Misinterpretation and solutions .


     
          Answer section          



( One )NOLOGGING Bad block caused by operation (ORA-01578 and ORA-26040) brief introduction

If it's just a mistake ORA-01578, Without accompanying ORA-26040, So this bad block is caused by other reasons , You can try to use RMAN Of BMR(Block Media Recovery) Repair .

If the data segment ( Table segment 、 Index segment ) Is defined as NOLOGGING attribute , So when NOLOGGING Add APPEND、UNRECOVERABLE Operate to modify the data segment or use the data pump (DATAPUMP)impdp Parameters DISABLE_ARCHIVE_LOGGING:Y when , Online redo logs only record a small amount of log information . If these online redo logs or archive logs are used to recover data files , that Oracle The corresponding data block will be marked as invalid (Soft Corrupt), And the next time you access these blocks , Will be submitted to the ORA-01578 and ORA-26040 error .

for example :

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


Data dictionary view DBA_TABLES、DBA_INDEXES、DBA_LOBS、DBA_TAB_PARTITIONS、DBA_LOB_PARTITIONS、DBA_TAB_SUBPARTITIONS Medium LOGGING The column records NOLOGGING attribute . if LOGGING='NO' said NOLOGGING.

Data pump DATAPUMP Of impdp Parameters DISABLE_ARCHIVE_LOGGING:Y The import will disable LOGGING Definition , And produce NOLOGGING operation . If the corresponding datafile By restored and recovered, Then the next query involving the target table will report an error ORA-1578 and ORA-26040. If the database is FORCE LOGGING Pattern , that DISABLE_ARCHIVE_LOGGING The option will not turn off LOGGING.

impdp Using parameter “DISABLE_ARCHIVE_LOGGING:Y” An example of :

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

NOLOGGING The resulting bad block will not result in RMAN Backup failed . Generally speaking soft corrupt block Will not lead to RMAN Backup failed , You don't have to set it MAXCORRUPT. Database backup will contain soft corrupt block, If you use these backups to restore data , So the recovered data also contains soft corrupt block.

except ORA-26040 Beyond errors , When there is some other general information ,block dump May be produced . If the data block block dump There are byte 0xff Information may belong to a certain segment ,ORA-1578 and ORA-26040 Because the media is restored NOLOGGING The part that led to corruption And it appears .

( Two ) utilize RMAN、DBV testing NOLOGGING The resulting bad block

DBV When detecting broken blocks , If RDBMS Version less than 10.2.0.4, that DBV Printing error DBV-200, If RDBMS Version greater than or equal to 10.2.0.4, that DBV Printing error DBV-201:

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


RMAN Of VALIDATE Commands can be used to detect NOLOGGING Data blocks , The results are recorded in the view V$DATABASE_BLOCK_CORRUPTION( Less than 12c Version of ) and V$NONLOGGED_BLOCK(12c And above ).

The following example checks out DATAFILE 4 Yes 933 Bad block , Inquire about V$DATABASE_BLOCK_CORRUPTION perhaps V$NONLOGGED_BLOCK.

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


RMAN When detecting broken blocks , If RDBMS Version less than 10.2.0.5 and 11.1.0.7,RMAN Print the following error :

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


If RDBMS Version greater than or equal to 10.2.0.5 and 11.2.0.1,RMAN The report , View view V$DATABASE_BLOCK_CORRUPTION in CORRUPTION_TYPE=NOLOGGING The record of .

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


stay 12c And later ,RMAN validate The result is not in the view V$DATABASE_BLOCK_CORRUPTION in , It's in the view V$NONLOGGED_BLOCK. from 12.2 Version start , You can use the new command :“validate .. nonlogged block” To verify NOLOGGING Of Block.

In the following example , Data files 5 and 6 Yes nologged Of block:

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


The following information will be updated in the alarm log :

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


 

( 3、 ... and ) monitor NOLOGGING operation

If implemented NOLOGGING operation , And then without backup ,RMAN command “REPORT UNRECOVERABLE” You can find out the affected datafile.

1RMAN> report unrecoverable;
2
3using target database control file instead of recovery catalog
4Report of files that need backup due to unrecoverable operations
5File Type of Backup Required Name
6---- ----------------------- -----------------------------------
74    full or incremental     /oracle/dbs/users.dbf
     


When initializing parameters db_unrecoverable_scn_tracking Set to true( The default value is , The parameter is in 10g Is not available ), that V$DATAFILE The following will be updated ;

[email protected]> select UNRECOVERABLE_CHANGE# ,
2  2  UNRECOVERABLE_TIME, 
3  3  FIRST_NONLOGGED_SCN ,
4  4  FIRST_NONLOGGED_TIME from v$datafile where file#=6;
5
6UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME  FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM
7--------------------- ------------------- ------------------- -------------------
8              2878238 2018-04-10 10:53:47             2878238 2018-04-10 10:53:47
     


stay 11.2.0.4 or 12.1.0.2+ In the version , Set up event 16490 Under the circumstances , Physical backup MRP The process will check out NOLOGGING change , And recorded in alert log.

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


( Four ) Identify when the data block is marked as NOLOGGING

Identify when the data block is marked as NOLOGGING, Can be trace Data block in file SCN perhaps V$DATABASE_BLOCK_CORUPTION In the view CORRUPTION_CHANGE# Value to time :

① Use trace Data block in file SCN, for example :

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


extract SCN value 0x0771.4fa24eb5, Delete '.', And then switch 0x07714fa24eb To the decimal system 511453045995.

② Use V$DATABASE_BLOCK_CORUPTION In the view CORRUPTION_CHANGE# value

If you run RMAN validate After the command ,V$DATABASE_BLOCK_CORUPTION In the view corruption_type='NOLOGGING' (10.2.0.5 and 11.2.0.1+), that CORRUPTION_CHANGE# The value of the column is decimal SCN value . You can use the following method to get SCN Timestamp Time :

1SELECT SCN_TO_TIMESTAMP(&&DECIMAL_SCN) FROM DUAL;
     


If you run RMAN VALIDATE:

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


stay 12c in :

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


If you inquire GV$ARCHIVED_LOG or GV$LOG_HISTORY, Then there will be mistakes ORA-08181:

1ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
2SELECT FIRST_TIME, NEXT_TIME
3FROM GV$ARCHIVED_LOG
4WHERE &DECIMAL_SCN BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#;
5 or
6SELECT FIRST_TIME
7FROM GV$LOG_HISTORY
8WHERE &DECIMAL_SCN BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#;
     

 

If you run RMAN VALIDATE:

 1ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
 2
 3SELECT FILE#, BLOCK#, FIRST_TIME, NEXT_TIME
 4FROM V$ARCHIVED_LOG, V$DATABASE_BLOCK_CORRUPTION
 5WHERE CORRUPTION_CHANGE# BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#
 6AND CORRUPTION_TYPE='NOLOGGING';
 7
 8 or
 9
10SELECT FILE#,BLOCK#,FIRST_TIME
11FROM   V$LOG_HISTORY, V$DATABASE_BLOCK_CORRUPTION
12WHERE  CORRUPTION_CHANGE# BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#
13  AND CORRUPTION_TYPE='NOLOGGING';
14
1512C:
16
17ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
18
19SELECT FILE#, BLOCK#, FIRST_TIME, NEXT_TIME
20FROM V$NONLOGGED_BLOCK, V$ARCHIVED_LOG
21WHERE NONLOGGED_START_CHANGE# BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#;
22
23 or
24
25SELECT FILE#, BLOCK#, FIRST_TIME
26FROM V$NONLOGGED_BLOCK, V$LOG_HISTORY
27WHERE NONLOGGED_START_CHANGE# BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#;
     


 

( 5、 ... and )SYSAUX Table space 、AWR、EM Etc NOARCHIVELOG and NOLOGGING problem

If the database version is 11.1.0.6 or 11.1.0.7 or 11.2.0.1, Yes NOLOGGING The object has executed DIRECT PATH operation , And then I did RECOVER DATABASE command , Even if the database FORCE LOGGING When it's on , There will be ORA-1578 and ORA-26040 error . This kind of problem often occurs in SYSAUX In tablespace AWR or EM object . Please refer to Note 1071869.1. Note that the current version of the database may be greater than 11.1 perhaps 11.2.0.1 But the problem may have been before the upgrade . The constraint is 11.2.0.2 Cancel... In the above version , The question is 10g Not going to happen .

RDBMS Version change :

RDBMS edition

change

10.2.0.4+

DBverify The report NOLOGGING block error message "DBV-00201: Block, DBA <rdba>, marked corrupt for invalid redo application"

10.2.0.5, 10.2.0.1+

RMAN validate Command check NOLOGGING block, stay v$database_block_coruption Record... In view corruption_type='NOLOGGING'

11g+

introduce db_unrecoverable_scn_tracking Parameters

11.1.0.6 or 11.1.0.7 or 11.2.0.1

NOARCHIVELOG Schema database , Yes NOLOGGING Object executed DIRECT PATH operation , And later restore the database manually , Even if it turns on FORCE LOGGING, It will also be reported ORA-1578 and ORA-26040. The constraint is 11.2.0.2 The above version is cancelled , The question is 10g Not going to happen .

12c

RMAN validate The result is not in the view v$database_block_corruption in , It's in the view v$nonlogged_block

12.2

following RMAN Orders are introduced :

RMAN> validate [database / datafile] nonlogged block;

RMAN> recover [database / datafile] nonlogged block; -> about Standby database

 

( 6、 ... and ) resolvent

NOLOGGING The bad block caused by operation cannot be repaired , such as “Media Recovery” or “RMAN blockrecover” Can't fix this bad block . The feasible way is to NOLOGGING Back up the corresponding data file immediately after the operation .

If the error is execution RMAN DUPLICATE or RESTORE After that , Then open it in the source library FORCE LOGGING, And then rerun RMAN DUPLICATE or RESTORE.

1alter database force logging;
     

If the error is in Physics STANDBY database , Then the affected data files can be recovered from the main database ( Only when the main database does not have this problem ). Reference documents Doc ID 958181.1. stay Oracle 12c Can be used in RMAN Options RECOVER NONLOGGED BLOCK with DATAFILE、TABLESPACE、DATABASE. for example :

1RMAN> RECOVER DATABASE NONLOGGED BLOCK;
     

To avoid this problem , Force the production log in the main database :

1ALTER DATABASE FORCE LOGGING;
     

If the same datafile The data block of appears in the main database nologging Bad block , But there is no such thing as , It can be skipped by hand (dbms_repair) Bad block or setting event 10231. The main library appears nologging The bad block may be due to the backup recovery performed by the primary database or the backup database before , Yes switchover.

If NOLOGGING The data block is in the free data block (DBA_FREE_SPACE The view can find ), that DBVerify The inspection will find out the problem , Report errors DBV-00201 Or in V$DATABASE_BLOCK_CORRUPTION The view shows . In this case , You can wait until the data block is reused and it will be automatically formatted or manually forced to format .

If index , Then you can recreate (drop/create) Indexes . If it's a watch , Then you can use stored procedures DBMS_REPAIR.SKIP_CORRUPT_BLOCKS Skip the bad block , Then consider whether to rebuild the table .

After deleting a section with bad blocks , This bad block is idle , It can then be assigned to other objects or segments , When this bad block is assigned to another object or segment , This data block is reformatted . If V$DATABASE_BLOCK_CORRUPTION The view still shows bad blocks , Then you can run it manually RMAN VALIDATE To clear the information in the view .

If it is LOB, Please refer to Note 293515.1.