Flash-forward Oracle database to restore Point - Moving back and forth with time without resetlogs

We  had  requirement to flashback database to   take export   backup of table having issues 

We All know we can flashback database  to restore point but  less we knew  that we can also flash forward database to restore point .   Thanks to blog posted in reference link  we came to  know we can  also flash forward  database to  restore point . 

We  have performed this steps on  standby  though we can perform same on production too 

 

Below are steps performed  this 

1)   Enable Flashback database  and create restore point One 

2)   Application perform deployment and Notice  they need to flashback 

-->  flashback  

3) Before we flash back database  Create  new restore point Two
4) Flashback to  restore point One

5) Perform sanpshot standby  to   take expdp  of table we need that has incorrect records  .

6) Convert to physical standby once  export is done

--> flash forward 

7) Finally Flash  forward Standby to Restore point Two created in Step 1 
 



Sample Commands :

--> Create restore point 

SQL> create restore point one  guarantee flashback database;

--> Flashback database 

RMAN> flashback database to restore point one ;

Or

SQL> flashback database to restore point one;

--> Flash forward database 

RMAN> recover database to restore point two ;

Or 

RMAN> flashback database to restore point two;

Reference :
https://www.pythian.com/blog/technical-track/flashback-and-forth

Oracle Automatic Data Compression using Information Lifecycle Management

We  had requirement from customer to  share strategy for automatic data compression   that directed us to ILM

 

With Oracle 12c, the feature Automatic Data Optimization (ADO) can help us with auto Compression  of data under predefined conditions.  ADO is part of Information Lifecycle Management (ILM).

 

ADO requires Advanced Compression Option.

Enable Heat Map   : 

To use ADO for compression, the Heat Map must be enabled. Indeed once enabled, Heat Map will collect statistics required for ADO actions. All accesses are tracked by the in-memory activity tracking module.

SQL> alter system set heat_map=ON scope=both;

Before adding the ADO policy, we can verify that heat map statistics are already collected.

SELECT OBJECT_NAME, to_char(TRACK_TIME,'DD-MON-YYYY HH24:MI:SS'), SEGMENT_WRITE "Seg_write",

SEGMENT_READ "Seg_read", FULL_SCAN, LOOKUP_SCAN

FROM v$heat_map_segment WHERE object_name='ARTICLE';

Adding a segment level row compression policy

Adding a segment level row compression policy on  table that will compress the segment when no modification on the segment will have occurred 

in the last 30 days  

SQL> ALTER TABLE app.article ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;

SQL> SELECT policy_name, action_type, scope, compression_level, condition_type, condition_days FROM user_ilmdatamovementpolicies ORDER BY policy_name;

 

SQL> SELECT policy_name, object_name, enabled FROM user_ilmobjects;

 

Changing  policy time : 

By default the policy time is specified in days. If we query the DBA_ILMPARAMETERS, the value for POLICY TIME determines 

if ADO policies are specified in seconds or days. Values are 1 for seconds or 0 for days (default).

SQL> col name for a20

SQL> select * from DBA_ILMPARAMETERS;

Changing policy time from days to seconds . 

SQL> EXEC dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME,dbms_ilm_admin.ILM_POLICY_IN_SECONDS);

Flush the heat map statistics from memory to disk and let’s wait 30 days 

SQL> EXEC dbms_ilm.flush_all_segments;

Manually execute the policy without waiting the maintenance window.

DECLARE

v_executionid number;

BEGIN

dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,

execution_mode => dbms_ilm.ilm_execution_offline,

task_id => v_executionid);

END;

/

 

Information  about the job can be obtained in the following views

SQL> SELECT task_id, start_time as start_time FROM user_ilmtasks order by 1;

SQL> SELECT task_id, job_name, job_state, completion_time completion FROM user_ilmresults ORDER BY 1 ;

To check compressesion 

SELECT compression, compress_for FROM dba_tables WHERE table_name = 'ARTICLE1';

SELECT CASE compression_type

WHEN 1 THEN 'No Compression'

WHEN 2 THEN 'Advanced compression level'

WHEN 4 THEN 'Hybrid Columnar Compression for Query High'

WHEN 8 THEN 'Hybrid Columnar Compression for Query Low'

WHEN 16 THEN 'Hybrid Columnar Compression for Archive High'

WHEN 32 THEN 'Hybrid Columnar Compression for Archive Low'

WHEN 64 THEN 'Compressed row'

WHEN 128 THEN 'High compression level for LOB operations'

WHEN 256 THEN 'Medium compression level for LOB operations'

WHEN 512 THEN 'Low compression level for LOB operations'

WHEN 1000 THEN 'Minimum required number of LOBs in the object for which LOB compression ratio is to be estimated'

WHEN 4096 THEN 'Basic compression level'

WHEN 5000 THEN 'Maximum number of LOBs used to compute the LOB compression ratio'

WHEN 1000000 THEN 'Minimum required number of rows in the object for which HCC ratio is to be estimated'

WHEN -1 THEN 'To indicate the use of all the rows in the object to estimate HCC ratio'

WHEN 1 THEN 'Identifies the object whose compression ratio is estimated as of type table'

ELSE 'Unknown Compression Type'

END AS compression_type, n as num_rows

FROM (SELECT compression_type, Count(*) n

FROM (SELECT dbms_compression.Get_compression_type(USER, 'ARTICLE', ROWID) AS COMPRESSION_TYPE

FROM app.article)

GROUP BY compression_type

);

References :

https://www.oracle.com/a/ocom/docs/database/implementing-ilm-with-oracle-database.pdf

DBA_TEMP_FREE_SPACE Shows Wrong Information on 19c

 

Recently we have seen on 19c  that DBA_TEMP_FREE_SPACE Shows Wrong Information on 19c . Its calculating more space then actual space . 

Thanks to Oracle  (Doc ID 2633068.1)  that   explains this  Gap .  Issue is related with duplicate entries in gv$temp_space_header

We  need to use dba_temp_files instead 

++ Issue is happening on 18c and 19c multitenant environment.

++ dba_temp_free_space showing wrong information for temp tablespace in 18c and 19c multitenant environment.

++ Issue does not happen in 18c/19c non-multitenant environment. 

++ For a single tempfile in TEMP Tablespace, gv$temp_space_header is showing 3 rows in 18c and 19c. Whereas shows correctly in 12.2.0.1 as 1 row.

SQL> select TABLESPACE_NAME,(bytes_used+bytes_free),BYTES_USED,BYTES_FREE from gv$temp_space_header;

TABLESPACE_NAME (BYTES_USED+BYTES_FREE) BYTES_USED BYTES_FREE

------------------------------ ----------------------- ---------- ----------

TEMP 1.2884E+10 1.2884E+10 0

TEMP 1.2884E+10 1.2884E+10 0

TEMP 1.2884E+10 1.2884E+10 0

This issue is caused by below bug

Bug 30576120 - GV$TEMP_SPACE_HEADER SHOWING DUPLICATE ENTRIES FOR SINGLE TEMPFILE

The issue is fixed in future release 20.1

Oracle Database Table and Index Partitioning Handy Cookbook

We have many partitioning maintenance task so its good  to have handy commands and new features 

 

Types Of  Partitioning : 

Range Partition :-

Range Partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates.

SQL> create table details(order_id number,order_date date) partition by range (order_date) (partition p1 values less than ('01-jan-2018') tablespace t1,partition p2 values less than ('01-mar-2018') tablespace t2,partition p3 values less than ('01-aug-2018') tablespace t3,partition p4 values less than ('01-dec-2018') tablespace t4 );

LIST PARTITION 

List Partitioning is used to list together unrelated data into partitions. It is a technique where you specify a list of discrete values for the partitioning key in the description for each partition.

SQL> CREATE TABLE sales_list (salesman_id NUMBER(5),sales_state VARCHAR2(20)) PARTITION BY LIST(sales_state)(PARTITION south VALUES ('py','tn','ap','kl','ka') tablespace t1,PARTITION east VALUES ('cac','or','bi') tablespace t2,PARTITION west VALUES ('pu','go') tablespace t3,PARTITION north VALUES ('dl','ja') tablespace t4);

Automatic list partitioning was introduced in Oracle Database 12c Release 2 (12.2) to solve the problem of how to handle new distinct values of the list partitioning key.

CREATE TABLE orders

(

  id            NUMBER,

  country_code  VARCHAR2(5),

  customer_id   NUMBER,

  order_date    DATE,

  order_total   NUMBER(8,2),

  CONSTRAINT orders_pk PRIMARY KEY (id)

)

PARTITION BY LIST (country_code) AUTOMATIC

(

  PARTITION part_usa VALUES ('USA'),

  PARTITION part_uk_and_ireland VALUES ('GBR', 'IRL')

);

HASH PARTITION :

Hash partitioning based on a hash algorithm. Hash partitioning enables partitioning of data that does not lend itself to range or list partitioning. The records in a table, are partitions based on Hash value found in the value of the column, which is used for partitioning. Hash partitioning does not have any logical meaning to the partitions as do the range partitioning.

SQL> CREATE TABLE emp (empno NUMBER(4),sal NUMBER) PARTITION BY HASH(empno) PARTITIONS 3 STORE IN (t1,t2,t3);

INTERVAL PARTITION :

Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added.The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.

SQL> create table order_details(order_id number,order_date date) partition by range (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) (partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY'))) tablespace t1;

Restrictions in Interval partitioning :

Interval partitioning is restricted to a single partition key that must be a numerical or date range.

At least one partition must be defined when the table is created.

Interval partitioning is not supported for index-organized tables.

You cannot create a domain index on an interval partitioned table.

Interval partitioning can be used as the primary partitioning mechanism in composite partitioning, but it can’t be used at the subpartition level.

A MAXVALUE partition cannot be defined for an interval partitioned table.

NULL values are not allowed in the partition column.

 

SYSTEM PARTITIONING  :

There are scenarios where a database developer or database designer is not able to make a logical way to partition a huge table. Oracle 11g has provided us a way to define partitions in an intelligent manner by System Partitioning, where application needs to control destination partition for a specific record. The DBA just needs to define the partitions.

SQL> create table system_order_details(order_id number,order_date date) partition by SYSTEM (PARTITION p1,PARTITION p2) tablespace t1;

 

REFERENCE PARTITIONING :-

Reference partitioning is for a child table is inherited from the parent table through a primary key – foreign key relationship. The partitioning keys are not stored in actual columns in the child table.

The child table is partitioned using the same partitioning key as the parent table without having to duplicate the key columns.Partition maintenance operations performed on the parent table are reflected on the child table, but no partition maintenance operations are allowed on the child table.

Parent table creation

CREATE TABLE parent_tab (

  id           NUMBER NOT NULL,

  code         VARCHAR2(10) NOT NULL,

  description  VARCHAR2(50),

  created_date DATE,

  CONSTRAINT parent_tab_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (created_date)

(

   PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),

   PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))

);

Child table creation 

CREATE TABLE child_tab (

  id             NUMBER NOT NULL,

  parent_tab_id  NUMBER NOT NULL,

  code           VARCHAR2(10),

  description    VARCHAR2(50),

  created_date   DATE,

  CONSTRAINT child_tab_pk PRIMARY KEY (id),

  CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)

                               REFERENCES parent_tab (id))

PARTITION BY REFERENCE (child_parent_tab_fk);

COMPOSITE PARTITIONING :-

Also called as Subpartition 

Composite partitioning is a combination of the basic partitioning techniques of Range, List, Hash, and Interval Partitioning.

Composite partitioning has been extended to include hash-* partitioning. The available composite partitioning schemes are listed below.

Range-Range (8i)

Range-Hash (8i)

Range-List (9i)

List-Range (11gR1)

List-Hash (11gR1)

List-List (11gR1)

Hash-Hash (12cR1)

Hash-List (12cR1)

Hash-Range (12cR1)

RANGE-HASH PARTITION:

This is basically a combination of range and hash partitions. The data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.

SQL> CREATE TABLE rng_hash (cust_id NUMBER(10),time_id DATE) PARTITION BY RANGE(time_id) SUBPARTITION BY HASH(cust_id) SUBPARTITION TEMPLATE(SUBPARTITION sp1 TABLESPACE t1,SUBPARTITION sp2 TABLESPACE t2,SUBPARTITION sp3 TABLESPACE t3)(PARTITION R1 VALUES LESS THAN ('01-apr-2010'),PARTITION R2 VALUES LESS THAN ('01-aug-2010'),PARTITION R3 VALUES LESS THAN ('01-dec-2010'),PARTITION R4 VALUES LESS THAN(MAXVALUE));

RANGE-LIST PARTITION:

This is a combination of Range and List partitions, first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using list key values. Each subpartition individually represents logical subset of the data not like composite Range-Hash Partition.

SQL> CREATE TABLE rng_list (cust_state VARCHAR2(2),time_id DATE)PARTITION BY RANGE(time_id)SUBPARTITION BY LIST (cust_state)SUBPARTITION TEMPLATE(SUBPARTITION south VALUES ('tn','py') TABLESPACE t1,SUBPARTITION east VALUES ('wb','bi') TABLESPACE t2,SUBPARTITION north VALUES ('jm','dl') TABLESPACE t3)(PARTITION a1 VALUES LESS THAN ('01-apr-2010'),PARTITION a2 VALUES LESS THAN ('01-aug-2010'),PARTITION a3 VALUES LESS THAN ('01-dec-2010'),PARTITION a4 VALUES LESS THAN(MAXVALUE));

Partition  Pruning :

In partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list. 

This functionality enables Oracle Database to perform operations only on those partitions that are relevant to the SQL statement. This is called Partition Pruning.

Partition pruning allows you to specify filter conditions for partition key columns.   

This avoids the errors and waste of resources that are caused by full table scans. 

However, partition pruning may not take effect sometimes.

1) Improper use of UDFs

If you use user-defined functions (UDFs) or specific built-in functions to specify partitions, partition pruning may not take effect. 

In this case, we recommend that you execute the EXPLAIN statement to check whether partition pruning is effective.

2) Improper use of joins

When you join tables, pay attention to the following rules:

If partition pruning conditions are specified in the WHERE clause, partition pruning is effective.

If partition pruning conditions are specified in the ON clause, partition pruning is effective for the secondary table, but not the primary table.

Keys to force partition pruning : 

1)  use partition key to apply predicates in whren clause , so that optimizer can eliminate unneeded partitions. It is called Partition Pruning.

2)   Add an Join Operation Hint: /*+ NO_USE_NL(CA DUD) */

Partition Maintenance : 

-- ADD

ALTER TABLE t1

ADD

 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),

 PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),

 PARTITION part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),

 PARTITION part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY'));

-- MERGE

ALTER TABLE t1

MERGE

  PARTITIONS part_2015, part_2016, part_2017, part_2018

  INTO PARTITION part_2018;

-- SPLIT

ALTER TABLE t1

SPLIT

  PARTITION part_2018 INTO (

    PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),

    PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),

    PARTITION part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),

    PARTITION part_2018

);

-- TRUNCATE

ALTER TABLE t1

TRUNCATE

  PARTITION part_2014, part_2015, part_2016, part_2017, part_2018;

-- DROP

ALTER TABLE t1

DROP

  PARTITION part_2016, part_2017, part_2018;

--  Make a partition ready only (12CR2)

-- From oracle 12.2.0.1 Relase, we can make few partitions of a table read only.

SQL> alter table dbatest.ORDER_TAB modify partition CREATED_2105_P10 read only;

Table altered.

SQL> select partition_name,read_only from dba_tab_partitions where table_name='ORDER_TAB';

--  Rename a partition

ALTER TABLE employee RENAME PARTITION TAB3 TO TAB4;

--  Move partition to new tablespace

- Move a single partition to a new tablespace 

ALTER TABLE SCOTT.EMP MOVE PARTITION EMP_Q1 TABLESPACE

TS_USERS;

--- Move a single partition to a new tablespace WITH PARALLEL 

ALTER TABLE SCOTT.EMP MOVE PARTITION

EMP_Q1 TABLESPACE TS_USERS PARALLEL(DEGREE 4) NOLOGGING;

- Dynamic script to move all partitions of a table 

select 'ALTER TABLE 

'||TABLE_OWNER ||'.'||table_name||' MOVE

PARTITION '||partition_name||' TABLESPACE TS_USERS PARALLEL(DEGREE 4) NOLOGGING;'

from dba_tab_partitions where table_name='&TABLE_NAME' and table_owner='&SCHEMA_NAME';

Oracle Asynchronous  Global Index Maintenance  jobs  for DROP and TRUNCATE Partition in Oracle Database 12c Release 1

I have documented about SYS.PMO_DEFERRED_GIDX_MAINT_JOB  in my previous Blog below 

https://abdul-hafeez-kalsekar.blogspot.com/2021/07/oracle-asynchronous-global-index.html

New Features : 

==> Enhancement in 19c 

1) Oracle 19c New Feature Hybrid Partitioned Tables

In Oracle 12c Release 2, one of the new features was the ability to create partitions on External tables.

New in Oracle 19c is the feature where we can create a Hybrid Partitioned table – so some partitions exist in the database and some partitions are hosted external to the database – maybe on a normal file system or ACFS or even Hadoop File System.

==> Enhancements in 12.2 

1) Create Table for Exchange With a Partitioned Table in Oracle Database 12c Release 2 (12.2)

Oracle 12.2 makes it easy to create the new empty table with the correct structure, thanks to the FOR EXCHANGE WITH TABLE clause. This clause duplicates a number internal settings and attributes including unusable columns, invisible columns, virtual columns etc.

2) Automatic List Partitioning in Oracle Database 12c Release 2 (12.2)

Automatic list partitioning creates a partition for any new distinct value of the list partitioning key. We can enable automatic list partitioning on the existing table using the ALTER TABLE command.

3) Multi-Column List Partitioning in Oracle Database 12c Release 2 (12.2)

Oracle Database 12c Release 2 (12.2) introduced the ability to define a list partitioned table based on multiple columns. 

Creating a multi-column list partitioned table is similar to creating a regular list partitioned table, except the PARTITION BY LIST clause 

includes a comma separated list of columns. Each partition is associated with valid combinations of those columns, along with an optional single default partition to catch any unspecified combinations.

4)  Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle Database 12c Release 2 (12.2)

 

In previous releases you could partition a non-partitioned table using EXCHANGE PARTITION or DBMS_REDEFINITION in an "almost online" manner, 

but both methods required multiple steps. Oracle Database 12c Release 2 makes it easier than ever to convert a non-partitioned table to a partitioned table, requiring only a single command and no downtime.

5) Online SPLIT PARTITION and SPLIT SUBPARTITION in Oracle Database 12c Release 2 (12.2)

In Oracle Database 12c Release 2 (12.2) the SPLIT PARTITION and SPLIT SUBPARTITION operations on heap tables can be performed online so they don't block DML. 

This is done by the addition of the ONLINE keyword, which also causes local and global indexes to be updated without having to specify the UPDATE INDEXES clause.

6) Read-Only Partitions and Subpartitions in Oracle Database 12c Release 2 (12.2)

A partitioned table can be created with read-only partitions by specifying the READ ONLY clause at the partition level in the CREATE TABLE statement.

7) Partitioned External Tables in Oracle Database 12c Release 2 (12.2)

Partitioned external tables were introduced in Oracle Database 12c Release 2 (12.2), allowing external tables to benefit from partition pruning and partition-wise joins. With the exception of hash partitioning, many partitioning and subpartitioning strategies are supported with some restrictions.

8) Filtered Partition Maintenance Operations in Oracle Database 12c Release 2 (12.2)

Oracle Database 12c Release 2 (12.2) allows you to add a filter condition when you move, split or merge a partition, 

move a table or convert a non-partitioned table to a partitioned table. Only those rows matching the filter condition are included in the resulting object.

==> Enhancements in 12.1

1) Cascade Functionality for TRUNCATE PARTITION and EXCHANGE PARTITION in Oracle Database 12c Release 1

The TRUNCATE [SUB]PARTITION and EXCHANGE [SUB]PARTITION commands can now include a CASCADE clause, allowing the actions to cascade down the hierarchy of reference partitioned tables. For this to work, the referenced foreign keys must include the ON DELETE CASCADE clause.

2) Asynchronous (Delayed) Global Index Maintenance for DROP and TRUNCATE Partition in Oracle Database 12c Release 1

Oracle 12c can optimize the performance of some DROP PARTITION and TRUNCATE PARTITION commands by deferring the associated index maintenance, while leaving the global indexes in a valid state.

3) Interval-Reference Partitioning in Oracle Database 12c Release 1

In previous releases you were able to do reference partitioning and interval partitioning, but you couldn't use an interval partitioned table as the parent for a reference partitioned table. Oracle 12c lifts that restriction, so you can now use interval-reference partitioning.

4) Online Move of Partitions and Sub-Partitions in Oracle 12c Release 1

5) Partial Indexes for Partitioned Tables in Oracle Database 12c Release 1

6) Partition Maintenance Operations on Multiple Partitions in Oracle Database 12c Release 1

7) Statistics Collection Enhancements in Oracle Database 12c Release 1 : Enhancements to Incremental Statistics

8) Partitioning Enhancements in Oracle Database 12c Release 1 (12.1)

9) Extended Composite Partitioning

References :

https://oracle-base.com/articles/misc/articles-misc#partitioning

https://oracle-base.com/articles/12c/partitioning-enhancements-12cr2

https://oracle-base.com/articles/12c/partitioning-enhancements-12cr1

Views : 

Find the table partition keys

--- describes the partitioning key columns for all partitioned objects of a schema

set pagesize 200

set lines 200

set long 999

col owner for a12

col name for a20

col object_type for a20

col column_name for a32

SELECT owner, NAME, OBJECT_TYPE,column_name

FROM dba_part_key_columns where owner='&OWNER'

ORDER BY owner, NAME;

References :

https://docs.oracle.com/en/database/oracle/oracle-database/21/vldbg/partition-pruning.html#GUID-45D3CCAF-17BC-4E79-8B7F-E65C7F1866F3

https://oracle-base.com/articles/misc/articles-misc#partitioning

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/maintenance-partition-tables-indexes.html

Oracle Database Block Corruption CookBook

 

Since we had many  corruption issues reported  thought of keeping this handy . In this blog we will try to  Oracle database  corruption insights 

We will Try to cover below topics 

1) Type of Block Corruption 

2) How to detect Block Corruption and How to Fix  Block Corruption 

3) Options to prevent Block Corruption 

4) How to manually corrupt Database Block for testing 

5) What caused block corruption 

 

#############################################

Type of Block Corruption 

#############################################

1) Physical  Block Corruption 

2) Logical  Block Corruption 

PHYSICAL CORRUPTION

Also called media corruption

Inconsistency between header and footer is one of the symptom of physical corruption. There can be an invalid checksum or header, or when the

block contains all zeroes.

Generally the result of infrastructure problems like OS/Storage issues, faulty disks/disks controllers, Memory issues.

Oracle Recovery Manager’s BLOCKRECOVER command can help in recovering from Physical Corruption

 

LOGICAL CORRUPTION

Also called soft corruption

Internal inconsistency in the block while the block may have good header and footer. The block checksum will be correct but the block structures may be corrupt.

Lost write can also cause Logical corruption. A lost write is a write I/O to persistent storage that the database believes has occurred based on

information from the I/O subsystem

Can show up as a result of a failure in the Oracle software or some bug, cache corruption etc.

Inter-block corruption, the corruption that occurs between blocks can only be a logical corruption

Sample of Logical Corruption received in Front end :

Timestamp: 7/22/2014 4:21:34 PM

Message: OraDataServiceProvider.AddIssueDetails Message : ORA-01476: divisor is equal to zero

ORA-06512: at “SALES.F_UPDATEINVENOTARY”, line 517

ORA-00600: internal error code, arguments: [4511], [], [], [], [], [], [], [], [], [], [], []

ORA-06512: at “SALES.P_ADDISSUE”, line 334

ORA-06512: at line 1

—————————————-

Timestamp: 7/22/2014 4:21:34 PM

Message: OraDataServiceProvider.AddIssueDetails StackTrace :    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx,

OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)

at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure,

IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)

at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()

at eHIS.OracleDataAccessHelper.ORACLEHelper.ExecuteNonQuery(String connectionString, CommandType cmdType, String

sqlCommandText, OracleParameter[] parameterArray)

at eHIS.SALES.DataAccess.OraDataServiceProvider.AddIssueDetails(String Issue, String& IssueCode)

Sample of Logical Corruption :

ORA-01578: ORACLE data block corrupted (file # 8, block # 13)

ORA-01110: data file 8: '/oracle/oradata/trgt/users01.dbf'

ORA-01578: ORACLE data block corrupted (file # 2, block # 19)

ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'

Obtain the datafile numbers and block numbers for the corrupted blocks. Typically, you obtain this output from the standard output, the alert.log, trace files, or a media management interface. For example, you may see the following in a trace file:

ORA-01578: ORACLE data block corrupted (file # 8, block # 13)

ORA-01110: data file 8: '/oracle/oradata/trgt/users01.dbf'

ORA-01578: ORACLE data block corrupted (file # 2, block # 19)

ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'

If you know the file number and block number you can run the following query to see the exact data block that has corruption:

select

   relative_fno,

   owner,

   segment_name,

   segment_type

from

   dba_extents

where

   file_id = 6

and

   437 between block_id and block_id + blocks - 1;

We can Dump  block information  : 

alter session set tracefile_identifier='BLOCKDUMP';

alter session set max_dump_file_size = unlimited;

alter session set "_sga_clear_dump"=TRUE;

alter system dump datafile ' +DATAC1/LNRFK4PC/DEDCC0CD76F00641E0531738E80A8BF1/DATAFILE/system.1365.1104551497' block min 2760 block max 2765;

#############################################

What Causes Block Corruption 

#############################################

1) When database is in with No-logging , Dataguard  objects will face corruption 

If a NOLOGGING (or UNRECOVERABLE) operation is performed on an object and the datafile containing that object is subsequently recovered, then the data blocks affected by the NOLOGGING operation are marked as corrupt and will signal an ORA-1578 error when accessed. In Oracle 8i, an ORA-26040 is also signaled (ORA-26040: Data block was loaded using the NOLOGGING option) which makes the cause fairly obvious, but earlier releases have no additional error message. If a block is corrupt due to recovery through a NOLOGGING operation, you need to understand that:

Recovery cannot retrieve the NOLOGGING data

No data is salvageable from inside the block

2) Storage / Filesystem issue 

#############################################

 How to detect  and  Fix   Block Corruption    

#############################################

- DBVERIFY always checks the whole data file

- RMAN checks the datafile until the high water mark,

Most Common  Way   used to  remediate Corruption :

1) Point in time Restore database 

2) Re-Creating Index and Ctask of table 

3)  Recover from service  from prod   or  Dr 

1)   Rman Validate  and Block Recover  + Data Recovery Advisor .

 Oracle Recovery Manager (RMAN) can validate the database using the BACKUP VALIDATE command.

By default the command only checks for physical corruption. Add the CHECK LOGICAL clause to include checks for logical corruption.

The VALIDATE command initiates data integrity checks, logging physical, and optionally logical, block corruptions of database files and backups in the V$DATABASE_BLOCK_CORRUPTION view and the Automatic Diagnostic Repository as one or more failures. 

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

The process outputs the same information you would see during a backup, but no backup is created. Any block corruptions are visible in the V$DATABASE_BLOCK_CORRUPTION view, as well as in the RMAN output.

By default the command only checks for physical corruption. Add the CHECK LOGICAL clause to include checks for logical corruption.

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

RMAN can validate the contents of backup files using the RESTORE VALIDATE command.

RMAN> RESTORE DATABASE VALIDATE;

RMAN> RESTORE ARCHIVELOG ALL VALIDATE;

In a similar way to the BACKUP VALIDATE command, the RESTORE VALIDATE command mimics the process of a restore, without actually performing the restore.

Prior to 11g, the straight VALIDATE command could only be used to validate backup related files. In Oracle 11g onward, the VALIDATE command can also validate datafiles, tablespaces or the whole database, so you can use it in place of the BACKUP VALIDATE command.

RMAN> VALIDATE DATAFILE 1;

RMAN> VALIDATE DATAFILE '/u01/app/oracle/oradata/ORCL/system01.dbf';

RMAN> VALIDATE CHECK LOGICAL DATAFILE 1;

RMAN> VALIDATE CHECK LOGICAL DATAFILE '/u01/app/oracle/oradata/ORCL/system01.dbf';

RMAN> VALIDATE TABLESPACE users;

RMAN> VALIDATE CHECK LOGICAL TABLESPACE users;

RMAN> VALIDATE DATABASE;

RMAN> VALIDATE CHECK LOGICAL DATABASE;

Any block corruptions are visible in the V$DATABASE_BLOCK_CORRUPTION view. You can identify the objects containing a corrupt block using a query like this.

COLUMN owner FORMAT A20

COLUMN segment_name FORMAT A30

SELECT DISTINCT owner, segment_name

FROM   v$database_block_corruption dbc

       JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1

ORDER BY 1,2;

SET MARKUP HTML ON

Spool /tmp/newdata.html

set lines 200 pages 10000

col segment_name format a30

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#

, greatest(e.block_id, c.block#) corr_start_block#

, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#

, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)

- greatest(e.block_id, c.block#) + 1 blocks_corrupted

, null description

FROM dba_extents e, v$database_block_corruption c

WHERE e.file_id = c.file#

AND e.block_id <= c.block# + c.blocks - 1

AND e.block_id + e.blocks - 1 >= c.block#

UNION

SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#

, header_block corr_start_block#

, header_block corr_end_block#

, 1 blocks_corrupted

, 'Segment Header' description

FROM dba_segments s, v$database_block_corruption c

WHERE s.header_file = c.file#

AND s.header_block between c.block# and c.block# + c.blocks - 1

UNION

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#

, greatest(f.block_id, c.block#) corr_start_block#

, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#

, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)

- greatest(f.block_id, c.block#) + 1 blocks_corrupted

, 'Free Block' description

FROM dba_free_space f, v$database_block_corruption c

WHERE f.file_id = c.file#

AND f.block_id <= c.block# + c.blocks - 1

AND f.block_id + f.blocks - 1 >= c.block#

ORDER BY file#, corr_start_block#;

SET MARKUP HTML off

V$COPY_CORRUPTION

This view displays information about datafile copy corruptions from the control file.

Read here about the column definition of this table.

V$BACKUP_CORRUPTION

This view displays information about corrupt block ranges in datafile backups from the control file.

Read here about the column definition of this table.

After Validate  has been run    we can repair using either REPAIR FAILURE  or block recover command 

RMAN> LIST FAILURE;

RMAN> ADVISE FAILURE;  /  RMAN> advise failure all; 

RMAN> REPAIR FAILURE PREVIEW;

RMAN> REPAIR FAILURE NOPROMPT;  / RMAN> repair failure;

RMAN> CHANGE FAILURE 202 PRIORITY LOW;

To Fix  corruption we can   Run “blockrecover corruption list”

Need to  run after backup check logical validate database . 

Execute the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, you can specify what type of backup should be used to restore the blocks:

# restore from backupset

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;

# restore from datafile image copy

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM DATAFILECOPY;

You can indicate the backup by specifying a tag:

# restore from backupset with tag "mondayam"

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199  FROM TAG = mondayam;

You can limit the backup candidates to those made before a certain point:

# restore using backups made before one week ago

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 

  RESTORE UNTIL 'SYSDATE-7';

# restore using backups made before SCN 100

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 

  RESTORE UNTIL SCN 100;

# restore using backups made before log sequence 7024

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 

  RESTORE UNTIL SEQUENCE 7024;

Note that if you limit the restore of datablocks with the UNTIL clause, then RMAN must perform more recovery on the blocks, and the recovery phase must scan all logs for changes to the specified blocks.

Examples

Recovering a Group of Corrupt Blocks: Example 

This example recovers corrupt blocks in three datafiles:

BLOCKRECOVER DATAFILE 2 BLOCK 12, 13 DATAFILE 3 BLOCK 5, 98, 99 DATAFILE 4 BLOCK 19;

Limiting Block Media Recovery by Type of Restore: Example 

The following example recovers a series of blocks and restores only from datafile copies:

RUN

{

  BLOCKRECOVER DATAFILE 3 BLOCK 2,3,4,5 TABLESPACE sales DBA 4194405, 4194409, 4194412

  FROM DATAFILECOPY;

}

Limiting Block Media Recovery by Backup Tag: Example 

This example recovers blocks and restores only from the backup with the tag weekly_backup:

BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405 FROM TAG "weekly_backup";

Limiting Block Media Recovery by Time: Example 

The following example recovers two blocks in the SYSTEM tablespace. It restores only from backups that could be used to recover the database to a point two days ago:

BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405 RESTORE UNTIL TIME 'SYSDATE-2';

Repairing All Block Corruption in the Database: Example 

The following example runs a backup validation to populate V$DATABASE_BLOCK_CORRUPTION, then repairs any corrupt blocks recorded in the view:

BACKUP VALIDATE DATABASE;

BLOCKRECOVER CORRUPTION LIST;

  Commands that can be used:

RMAN> backup check logical validate datafile 1672;

RMAN> backup validate check logical database;

RMAN> blockrecover corruption list;

RMAN> blockrecover datafile 1281 block 8759;

RMAN> blockrecover datafile 1281 block 8759 from tag 'TAG20100101T200011';

RMAN> list backup of datafile 1281;

RMAN> BLOCKRECOVER DATAFILE 172 block 59903 DATAFILE 1607 block 368273 DATAFILE 1630  block 406686 DATAFILE 1574 block 197819 DATAFILE 1753 block 304906 DATAFILE 1607 block 443063 DATAFILE 1729 block 952410;

RMAN> BLOCKRECOVER DATAFILE 172 block 59903 DATAFILE 1607 block 368273 DATAFILE 1630  block 406686 DATAFILE 1574 block 197819 DATAFILE 1753 block 304906 DATAFILE 1607 block 443063 DATAFILE 1729 block 952410 from tag 'TAG20100101T200011';

2)  dbverify  utility 

https://docs.oracle.com/cd/E11882_01/server.112/e22490/dbverify.htm#SUTIL1536

DBVerify is an external utility that allows validation of offline and online datafiles. In addition to offline datafiles it can be used to check the validity of backup datafiles.

C:\>dbv file=C:\Oracle\oradata\TSH1\system01.dbf feedback=10000 blocksize=8192

This utility is not usually used for controlfiles or redo logs, but in MOS Doc ID 1949795.1 there is an example of using it with controlfiles.

DBVerify to validate the Segment

Note: User must have sysdba privilieges

col segment_name for a12

col tablespace_name for a15

select tablespace_name, segment_name, TABLESPACE_ID, HEADER_FILE, HEADER_BLOCK

from sys.sys_user_segs where SEGMENT_NAME like 'TEST%';

TABLESPACE_NAME SEGMENT_NAME TABLESPACE_ID HEADER_FILE HEADER_BLOCK

--------------- ------------ ------------- ----------- ------------

SYSTEM TEST10 0 1 32776

SYSTEM TEST20 0 1 32784

Advertisements

REPORT THIS AD

-- User has the sysdba priviliges

dbv  SEGMENT_ID=0.1.32784

Script to get list of DBV commands of all datafiles

set echo off

set feedback off

set verify off

set pages 0

set termout off

set linesize 150

spool dbv.cmd

select 'dbv file=' || name || ' blocksize=' || block_size || ' feedback=' || round(blocks*.10,0) from v$datafile;

spool off

3) Full database export  -- exp/expdp  or  Perform Count(*)  on  Table 

4)  Analyze validate structure  Command :

Run UTLVALID.SQL  to create  INVALID_ROWS table.

SQL> alter session set tracefile_identifier=’ANALYZE’;

SQL> analyze table SALES.PURCHASEITEMS validate structure online;

The following statement analyses the  EMPLOYEES table:

ANALYZE TABLE  EMPLOYEES VALIDATE STRUCTURE;

You can validate an object and all dependent objects (for example, indexes) by including the CASCADE option:

ANALYZE TABLE  EMPLOYEES VALIDATE STRUCTURE CASCADE;

By default the CASCADE option performs a complete validation. Because this operation can be resource intensive, you can perform a faster version of the validation by using the FAST clause. This version checks for the existence of corruptions using an optimized check algorithm, but does not report details about the corruption. If the FAST check finds a corruption, you can then use the CASCADE option without the FAST clause to locate it.

ANALYZE TABLE  EMPLOYEES VALIDATE STRUCTURE CASCADE FAST;

You can specify that you want to perform structure validation online while DML is occurring against the object being validated. There can be a slight performance impact when validating with ongoing DML affecting the object, but this is offset by the flexibility of being able to perform ANALYZE online. The following statement validates the emp table and all associated indexes online:

ANALYZE TABLE  EMPLOYEES VALIDATE STRUCTURE CASCADE ONLINE;

5) DBMS_REPAIR : (Doc ID 556733.1)

REM Create the repair table in a given tablespace:

BEGIN

  DBMS_REPAIR.ADMIN_TABLES (

  TABLE_NAME => 'REPAIR_TABLE',

  TABLE_TYPE => dbms_repair.repair_table,

  ACTION => dbms_repair.create_action,

  TABLESPACE => '&tablespace_name');

END;

/

Creating an Orphan Key Table

 

BEGIN

  DBMS_REPAIR.ADMIN_TABLES (

     TABLE_NAME => 'ORPHAN_KEY_TABLE',

     TABLE_TYPE => dbms_repair.orphan_table,

     ACTION     => dbms_repair.create_action,

     TABLESPACE => 'USERS');

END;

/

REM Identify corrupted blocks for schema.object (it also can be done at partition level with parameter PARTITION_NAME):

set serveroutput on

DECLARE num_corrupt INT;

BEGIN

  num_corrupt := 0;

  DBMS_REPAIR.CHECK_OBJECT (

  SCHEMA_NAME => '&schema_name',

  OBJECT_NAME => '&object_name',

  REPAIR_TABLE_NAME => 'REPAIR_TABLE',

  corrupt_count => num_corrupt);

  DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;

/

REM Optionally display any corrupted block identified by check_object:

select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION

from REPAIR_TABLE;

REM Mark the identified blocks as corrupted ( Soft Corrupt - reference Note 1496934.1 )

DECLARE num_fix INT;

BEGIN

  num_fix := 0;

  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

  SCHEMA_NAME => '&schema_name',

  OBJECT_NAME=> '&object_name',

  OBJECT_TYPE => dbms_repair.table_object,

  REPAIR_TABLE_NAME => 'REPAIR_TABLE',

  FIX_COUNT=> num_fix);

  DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));

END;

/

Finding Index Entries Pointing to Corrupt Data Blocks.  

This procedure is useful in identifying orphan keys in indexes that are pointing to corrupt rows of the table:

SET SERVEROUTPUT ON

DECLARE num_orphans INT;

BEGIN

 num_orphans := 0;

 DBMS_REPAIR.DUMP_ORPHAN_KEYS (

     SCHEMA_NAME => 'SCOTT',

     OBJECT_NAME => 'PK_DEPT',

     OBJECT_TYPE => dbms_repair.index_object,

     REPAIR_TABLE_NAME => 'REPAIR_TABLE',

     ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',

     KEY_COUNT => num_orphans);

 DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));

END;

/

REM Allow future DML statements to skip the corrupted blocks:

BEGIN

  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

  SCHEMA_NAME => '&schema_name',

  OBJECT_NAME => '&object_name',

  OBJECT_TYPE => dbms_repair.table_object,

  FLAGS => dbms_repair.SKIP_FLAG);

END;

/

6) DBMS_HM.RUN_CHECK 

Check DBMS HM performed which health Checkup

col name for a30

SELECT name FROM v$hm_check WHERE internal_check='N';

NAME

------------------------------------

DB Structure Integrity Check

Data Block Integrity Check

Redo Integrity Check

Transaction Integrity Check

Undo Segment Integrity Check

Dictionary Integrity Check

Check the parameter name present in the Health checkup and used while running the health check with DBMS_HM Package

set line 200 pages 200

col check_name for a30

col parameter_name for a20

SELECT c.name check_name, p.name parameter_name

--, p.type,p.default_value, p.description

FROM v$hm_check_param p, v$hm_check c

WHERE p.check_id = c.id and c.internal_check = 'N'

ORDER BY c.name;

CHECK_NAME                     PARAMETER_NAME

------------------------------ --------------------

ASM Allocation Check           ASM_DISK_GRP_NAME

CF Block Integrity Check       CF_BL_NUM

Data Block Integrity Check     BLC_DF_NUM

Data Block Integrity Check     BLC_BL_NUM

Dictionary Integrity Check     CHECK_MASK

Dictionary Integrity Check     TABLE_NAME

Redo Integrity Check           SCN_TEXT

Transaction Integrity Check    TXN_ID

Undo Segment Integrity Check   USN_NUMBER

Parameters for Data Block Integrity Check

Parameter Name Type Default Value Description

BLC_DF_NUM      Number   (none)         Block data file number

BLC_BL_NUM      Number   (none) Data block number

Example to run 

BEGIN

DBMS_HM.RUN_CHECK (

check_name   => ‘Data Block Integrity Check’,

run_name     => ‘datablockint’,

input_params => ‘BLC_DF_NUM=4;BLC_BL_NUM=191’);

END;

Execute DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'report1');

exec DBMS_HM.RUN_CHECK(check_name => ‘Transaction Integrity Check’, run_name => ‘report2’, input_params => ‘TXN_ID=7.33.2’);

Check the health checkup report

SET LONG 100000

SET LONGCHUNKSIZE 1000

SET PAGESIZE 1000

SET LINESIZE 512

SELECT DBMS_HM.GET_RUN_REPORT('REPORT2') FROM DUAL;

Views for DBMS HM for view status, errors and results

SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;

SELECT type, description FROM v$hm_finding ;

Clear HM report

exec dbms_hm.drop_schema(FALSE);

7) Oracle 12c  diskgroup  scrub repair

SQL> alter diskgroup DG1 scrub repair;

Diskgroup altered.

SQL> alter diskgroup DG1 scrub file '+DATA_DISK/DATAFILE/system.254.939393617' repair wait;

Diskgroup altered.

SQL> alter diskgroup DG1 scrub disk DATA_DISK1 repair power max force;

Diskgroup altered

REPAIR:  If the repair option is not specified, ASM only check and report logical corruption

POWER:  LOW, HIGH, or MAX.  If power is not specified, the scrubbing power is controlled based on the system I/O load

FORCE:  Command is processed immediately regarless of system load

Two ways of scrubbing:  On-demand  by administrator on specific area as like above, Occur as part of rebalance operation if disk attribute content.check=TRUE mentioned at disk level.

SQL> alter diskgroup DG1 attribute 'content.check' = 'TRUE';

Diskgroup altered.

8) Handling Redo log corruption 

Errors in file C:\APP\PC\diag\rdbms\admin\admin\trace\admin_lgwr_8104.trc:

ORA-00313: open failed for members of log group 5 of thread 1

ORA-00312: online log 5 thread 1: 'u01/redol/REDO07.LOG'

ORA-27046: file size is not a multiple of logical block size

OSD-04012: file size mismatch (OS 10484100)

  SELECT GROUP#, ARCHIVED,STATUS FROM V$LOG;

  SELECT GROUP#,L.STATUS,V.MEMBER,L.SEQUENCE# FROM V$LOG L JOIN V$LOGFILE V USING (GROUP#) ORDER BY GROUP#;

 STARTUP MOUNT

 ALTER DATABASE CLEAR UNARCHIVED LOGFILE;

 ALTER DATABASE CLEAR LOGFILE;

 Shu immediate 

 Startup mount 

 ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 6;

 Alter Database  open ; 

 ALTER SYSTEM SWITCH LOGFILE; 

9)  Checking Archivelog Corruption 

SQL> exec dbms_logmnr.add_logfile('<path>/<arch file name>.arc');

SQL> exec dbms_logmnr.start_logmnr;

SQL> select count(1) from v$logmnr_contents;

When the archive file is good, you will be able to execute logmnr and select from the view:

SQL> exec dbms_logmnr.add_logfile('/rdbms/oracle/ora1022i/64/admin/ora1022a/arch/1_mf_1_879_56dggmtf_.arc');

SQL> exec dbms_logmnr.start_logmnr;

SQL> select count(1) from v$logmnr_contents;

COUNT(1)

----------

18466

If the archive is bad, logmnr will not be able to access it:

SQL> exec dbms_logmnr.add_logfile('/rdbms/oracle/ora1022i/64/admin/ora1022a/arch/

wqbsqa01_652800616_1_4265.arc');

BEGIN dbms_logmnr.add_logfile('/rdbms/oracle/ora1022i/64/admin/ora1022a/arch/wqbsqa01_652800616_1_4265.arc'); END;

*

ERROR at line 1:

ORA-01284: file

/rdbms/oracle/ora1022i/64/admin/ora1022a/arch/wqbsqa01_652800616_1_4265.arc cannot be opened

ORA-00308: cannot open archived log

'/rdbms/oracle/ora1022i/64/admin/ora1022a/arch/wqbsqa01_652800616_1_4265.arc'

ORA-27047: unable to read the header block of file

OSD-04001: invalid logical block size (OS 3899694081)

ORA-06512: at "SYS.DBMS_LOGMNR", line 68

ORA-06512: at line 1

We can also use dump logfile  method prescribed below 

SQL> oradebug setmypid

SQL> alter system dump logfile 'Archive_log_path.arc' validate;

SQL> oradebug tracefile_name

 

10) Checking Dictionary Corruption as per hcheck.sql 

hcheck.sql can  be used  to perform Dictionary checks   for corruption  .   hcheck.sql can  be run  at Cdb and Pdb Both . 

For 19.22.0.0.240116 onward  we can also run dbms_dictionary_check :

SQL> set serveroutput on size unlimited

SQL> execute dbms_dictionary_check.full

To Check “Critical” Findings and the status:

SQL> execute dbms_dictionary_check.critical

To Run a FULL Repair for the reported findings:

SQL> set serveroutput on size unlimited

SQL> EXECUTE dbms_dictionary_check.full(repair=>TRUE

Copy of script   needs  to  be downloaded from Oracle Doc 136697.1 .    I have also  copied script  in  below  link . 

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/07/oracle-support-hchecksql.html

11)  Lob corruption :

Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management (ORA-1555) (Doc ID 253131.1)

Lob corruption can also be checked using SYSTEM.READLOBSFROMTABLE as  per Doc ID 253131.1

Example:

     SQL> set serveroutput on

     SQL> exec system.readlobsfromtable('SCOTT','MYLOBTABLE','LOBCOLUMN');

     Total Lobs Read         : 1374

     Total Errors Encountered: 0

 

Code for PROCEDURE SYSTEM.READLOBSFROMTABLE can be fetched from (Doc ID 253131.1)  which is also placed under below  blog

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2024/12/concurrent-writes-may-corrupt-lob.html

#############################################

Options to prevent Block Corruption :

#############################################

1)   Active data guard 

Active Standby Database Automatic Block Corruption Repair (ABMR)

Automatic Block Media Repair feature of Active standby database is an excellent feature in which data block corruptions on the Primary database side can be repaired by obtaining those blocks from the standby site. This recovery process is done transparently by a background process (ABMR). It can work vice-versa also to repair block corruptions on the Active Standby site by applying right blocks received from the Primary site.

 INCASE THE DATAFILE HEADER ITSELF IS CORRUPT, THEN AUTOMATIC BLOCK MEDIA RECOVERY IS NOT POSSIBLE.

2)   Set a Lag In dataguard log shipping -- ArchiveLagTarget

3)  Db_ultra_safe

You must avoid setting this on standby or we will end up facing "Bug 7426336 Standby may report a false lost write (ORA-752)"  on standby 

The db_ultra_safe init.ora parameter is a meta parameter got Oracle's file corruption checking facility.  If you have "safe" disks (at least two years without a file corruption issue, you may not want to turn-on the overhead of file checking.

However, if you are using replication where redo logs and updates are transferred between systems (RAC, Data Guard, Streams standby database), the possibility of data corruption may increase slightly.

The db_ultra_safe parameter is a meta parameter, such that a single setting sets the values for multiple other parameters.

Oracle knows that with some additional overhead, you can add extra checking to ensure that your data files are not corrupted:

• db_block_checking:  This db_block_checking checks data block integrity before writing the data block to disk.  In this involves checking the internal block headers and footer, plus internal row linkages.

• db_block_checksum: In 10g, this will allow the DBWR (database writer) and sqlldr.exe program to do a checksum when writing data to disk.  In 11g, the checks are moved to foreground processes. to add less overhead.
  

• db_lost_write_protect is introduced in 11g to turn-off file write checking overhead.  This is important on disk arrays with their own RAM that issue false "acks", acknowledging that data has been written, while it is actually still i  the disk array data buffer.

The db_ultra_safe parameter sets the default values for other parameters that control protection levels.

• db_ultra_safe= off:  When any of db_block_checking, db_block_checksum, or db_lost_write_protect are explicitly set, no changes are made.

•  db_ultra_safe=data_only:  This single setting for db_ultra_safe sets these three values:
  - db_block_checking = medium
  - db_lost_write_protect = typical
  - db_block_checksum = full.
  

• db_ultra_safe=data_and_index
  - db_block_checking will be set to full.
  - db_lost_write_protect will be set to typical.
  - db_block_checksum will be set to full.

 

#############################################

References : 

#############################################

1) How to identify all the Corrupted Objects in the Database with RMAN (Doc ID 472231.1)

2)  ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution (Doc ID 794505.1)

3) DBMS_REPAIR SCRIPT (Doc ID 556733.1) /  DBMS_REPAIR example (Doc ID 68013.1)

4) How to Format Corrupted Block Not Part of Any Segment ( Doc ID 336133.1 ) 

5) Checking for Oracle archive log corruption (Doc ID 1268590.1)

6) RMAN : Block-Level Media Recovery - Concept & Example (Doc ID 144911.1)

7) HOW TO PERFORM BLOCK MEDIA RECOVERY (BMR) WHEN BACKUPS ARE NOT TAKEN BY RMAN. (Doc ID 342972.1)