Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to fix the bad block problem of ORA-1578 caused by NOLOGGING operation". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Applicable to:

Oracle Database-Enterprise Edition-versions 7.1.6.0 to 12.2.0.1 [releases 7.1.6 to 12.2] the information contained in this document applies to all platforms

Use

Important:

If it's just an error ORA-1578 that doesn't accompany ORA-26040, then this bad block is something else and can be fixed by RMAN Block Media Recovery. Ref. 1578.1

Scope of application

This article applies to users and Oracle Support.

detailed information

If the data segment is defined as a NOLOGGING attribute, when the NOLOGGING/UNRECOVERABLE operation modifies the segment or uses the datapump import parameter disable_archive_logging:y, the online redo log records very little log information, which will invalidate these blocks if the RECOVERY operation is performed later.

If these online redo / archive logs are used to recover data files, Oracle marks the corresponding blocks as invalid and reports ORA-1578 and ORA-26040 errors the next time they are accessed.

For example:

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

The LOGGING column in the following data dictionary view records the NOLOGGING property:

DBA_TABLES, DBA_INDEXES, DBA_LOBS, DBA_TAB_PARTITIONS, DBA_LOB_PARTITIONS, DBA_TAB_SUBPARTITIONS, etc.

LOGGING='NO' stands for NOLOGGING.

Next, these blocks are marked as Soft Corrupt, and the next time the block is accessed, ORA-1578 and ORA-26040 errors are reported.

DATAPUMP parameter DISABLE_ARCHIVE_LOGGING

The DATAPUMP impdp parameter DISABLE_ARCHIVE_LOGGING:Y disables the definition of LOGGING when import, resulting in a NOLOGGING operation; if the corresponding datafile is restored and recovered, then the following statements will error ORA-1578 and ORA-26040.

"if database is in FORCE LOGGING mode, then the DISABLE_ARCHIVE_LOGGING option does not turn off logging.

An example of using this parameter when import:

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

RDBMS version change

10.2.0.4+DBverify reports NOLOGGING block error message "DBV-00201: Block, DBA, marked corrupt for invalid redo application" 10.2.0.5, 10.2.0.1+RMAN validate command checks NOLOGGING block, records corruption_type='NOLOGGING'11g+ introducing db_unrecoverable_scn_tracking parameter 11.1.0.6 or 11.1.0.7 or 11.2.0.1 in v$database_block_coruption view

NOARCHIVELOG schema database, performing DIRECT PATH operations on NOLOGGING objects, and later manually restoring the database, even if FORCE LOGGING is open

ORA-1578 and ORA-26040 will also be reported. This constraint is removed in version 11.2.0.2 and this problem does not occur at 10g.

The result of 12cRMAN validate is not in the view v$database_block_corruption, but in the view v$nonlogged_block12.2

The following RMAN commands are introduced:

RMAN > validate [database / datafile] nonlogged block

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

Solution method

Bad blocks caused by NOLOGGING operations cannot be repaired, such as "Media Recovery" or "RMAN blockrecover". The feasible way is to back up the corresponding data files immediately after the NOLOGGING operation.

The problem is that it occurs after the execution of RMAN DUPLICATE or RESTORE?

If the problem is after executing RMAN DUPLICATE or RESTORE, open FORCE LOGGING in the source library, and then rerun RMAN DUPLICATE or RESTORE.

Alter database force logging

The problem is that it happens in the physical standby library?

If the error occurs in the physical STANDBY database, recover the affected data files from the main database (only if the primary database does not have this problem). Reference document Doc ID 958181.1. The RMAN option RECOVER NONLOGGED BLOCK with DATAFILE,TABLESPACE,DATABASE can be used in 12c. For example:

RMAN > RECOVER DATABASE NONLOGGED BLOCK

To avoid this problem, force the production log in the main library:

Alter database force logging

If the data block of the same datafile appears nologging bad block in the main database, but the standby database does not have it, you can skip the bad block manually or set event 10231.

The occurrence of nologging bad blocks in the main database may be due to the backup and recovery of the main database or the execution of switchover in the previous standby database.

Identify the affected Segment

Refer to Note 819533.1 and Note 472231.1 to find the object where the bad block is located:

If the NOLOGGING block is in an idle block (which can be queried by the dba_free_space view), the DBVerify check will find this problem and report an error DBV-00201

Or display in the v$database_block_corruption view. In this case, we can wait for the data block to be automatically formatted when it is reused, or

Manual forced formatting, refer to Doc ID 336133.1

If it is an index, drop/create the index.

If it is a table, use procedure DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to skip bad blocks, please refer to the example of using Note 556733.1 to get the package DBMS_REPAIR. Then consider whether to rebuild the table:

Mobile table: alter table & table_name move

Or

Save the data (export, Create Table as Select, etc) and then truncate or drop/create

Example:

The blocks in the tag table that require skip:

BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME = >'& schema_name'

OBJECT_NAME = >'& table_name'

OBJECT_TYPE = > dbms_repair.table_object

FLAGS = > dbms_repair.SKIP_FLAG)

END

/

Confirm that the skipping corrupt blocks of the table is ENABLED:

Select SKIP_CORRUPT

From dba_tables

Where owner ='& schema_name'

And table_name ='& table_name'

Move this table:

Alter table & table_name move

OR if decided to save the data:

Export (datapump or conventional export)

Or

Create Table & newtable as Select * From & nologging_corrupted_table

If it is LOB, refer to Note 293515.1.

If a segment with a bad block is deleted, the bad block is idle and can later be assigned to another object / segment when the bad block is assigned to another object / segment

This data block is reformatted. If the v$database_block_corruption or v$nonlogged_block (12c +) view still appears as a bad block, run rman validate manually to clear the information in the view.

This is the end of the content of "how to fix the bad block problem of ORA-1578 caused by NOLOGGING operation". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.