Monday, October 28, 2024

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
 




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

Saturday, September 14, 2024

Oracle 19c New Feature High-Frequency Statistics : No more stale statistics in 19c


Since there was  lot of hype of  19c  new feature   High frequency statistics thought of documenting few note .

Since main motto of Blog is High-Frequency Statistics have  mentioned about it first   But  later also documented  notes on our  routine stats  maintenance window 



#########################
19c High-frequency automatic optimizer statistics collection. :
#########################

The new feature introduced in Oracle 19c called High-Frequency Automatic Optimizer Statistics Collection complements the standard automatic statistics collection job.

AutoTask schedules tasks to run automatically between  maintenance windows. By default, one window is scheduled for each day of the week. Automatic optimizer statistics collection (DBMS_STATS) runs in all predefined maintenance windows.

Statistics can go stale between two consecutive statistics collection tasks. If data changes frequently, the stale statistics could cause performance problems. For example, a brokerage company might receive tremendous data during trading hours, leading the optimizer to use stale statistics for queries executed during this period.


By default, the high-frequency statistics collection occurs every 15 minutes and as such there is less possibility of having stale statistics even for those tables where data is changing continuously.

The DBMS_STATS.SET_GLOBAL_PREFS procedure is used to enable and disable the high-frequency statistics gather task as well as change the execution interval (default 15 minutes) and the maximum run time (60 minutes).



select
s.START_TIME, s.END_TIME
,s.STALENESS
,s.OSIZE
,s.OBJ#, name, NVL(subname,'<NULL>') subname
,s.TYPE#
,decode(s.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
'UNDEFINED') object_type
,s.FLAGS, s.STATUS
,s.SID, s.SERIAL#
,s.PART#, s.BO#
from sys.stats_target$ s
left outer join sys.obj$ o on o.obj# = s.obj#
Where start_time >= sysdate-10/1440
order by start_time
/



==>  To Change Setting 

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','600');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240');

 


==> to check current setting 

SET LINESIZE 100 pages 99
COLUMN auto_task_status HEADING 'Auto Task|Status' FORMAT a10
COLUMN auto_task_max_run_time HEADING 'Auto Task|Max Run Time' FORMAT a15
COLUMN auto_task_interval HEADING 'Auto Task|Interval' FORMAT a10
SELECT DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS') AS auto_task_status
, DBMS_STATS.GET_PREFS('AUTO_TASK_MAX_RUN_TIME') AS auto_task_max_run_time
, DBMS_STATS.GET_PREFS('AUTO_TASK_INTERVAL') AS auto_task_interval
FROM dual;



==>  To Check execution history 


COL OPID FORMAT 99999999
COL STATUS FORMAT a11
COL ORIGIN FORMAT a20
COL COMPLETED FORMAT 99999
COL FAILED FORMAT 99999
COL TIMEOUT FORMAT 99999
COL INPROG FORMAT 99999

SELECT OPID, ORIGIN, STATUS, TO_CHAR(START_TIME, 'DD/MM HH24:MI:SS' ) AS BEGIN_TIME,
       TO_CHAR(END_TIME, 'DD/MM HH24:MI:SS') AS END_TIME, COMPLETED, FAILED,
       TIMED_OUT AS TIMEOUT, IN_PROGRESS AS INPROG
FROM  DBA_AUTO_STAT_EXECUTIONS
ORDER BY OPID;



with x as (
SELECT x.*
, end_time-start_time diff
, start_time-(LAG(end_time,1) over (order by start_time)) start_lag
FROM DBA_AUTO_STAT_EXECUTIONS x
ORDER BY x.start_time
)
select opid, origin, status
, ((extract( day from start_lag )*24+
extract( hour from start_lag ))*60+
extract( minute from start_lag ))*60+
extract( second from start_lag ) start_lag
, start_time, end_time
, ((extract( day from diff )*24+
extract( hour from diff ))*60+
extract( minute from diff ))*60+
extract( second from diff ) secs
, completed, failed, timed_out, in_progress
from x
Where start_time >= sysdate-1/24
/





#########################
To Enable Or Disable old  Auto task during daily maintenance window 
#########################


BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE (  
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL 
);
END;
/


BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE (  
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL 
);
END;
/


-- disable sql tuning advisor on monday
BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => 'MONDAY_WINDOW');
END;
/




#########################
Alter old auto stats setting for  daily maintenance window 
#########################

EXEC DBMS_STATS.alter_stats_history_retention(90);
EXEC DBMS_STATS.set_global_prefs('estimate_percent', '5');


BEGIN
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 1200);
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'ACCEPT_SQL_PROFILES', 'FALSE');
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_SQL_PROFILES_PER_EXEC', 20);
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_AUTO_SQL_PROFILES', 10000);
END;
/



#########################
Old Auto Task Details View for  daily maintenance window :
#########################


select log_id,owner,job_name,status,actual_start_date,instance_id from dba_scheduler_job_run_details
where job_name = 'GATHER_STATS_JOB' and ACTUAL_START_DATE>SYSDATE-3
or job_name like 'ORA$AT_OS_OPT_SY%' and ACTUAL_START_DATE>SYSDATE-3
order by log_date desc;



COL CLIENT_NAME FORMAT a31
select client_name, status, window_group from dba_autotask_client;

SELECT client_name, status FROM dba_autotask_operation;

SELECT task_name, status, TO_CHAR(execution_end,'DD-MON-YY HH24:MI')
FROM dba_advisor_executions WHERE task_name='SYS_AUTO_SQL_TUNING_TASK'
ORDER BY execution_end;

SELECT WINDOW_NAME, to_char(START_TIME,'DD-Mon-RR hh24:mi') START_TIME, DURATION
FROM DBA_AUTOTASK_SCHEDULE
ORDER BY WINDOW_NAME, START_TIME DESC;

select client_name,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION from DBA_AUTOTASK_JOB_HISTORY 
where JOB_START_TIME >systimestamp -7 and client_name='auto optimizer stats collection'  order by 4 desc ; 

select parameter_name, parameter_value
from dba_advisor_parameters
where task_name = 'SYS_AUTO_SQL_TUNING_TASK'
and parameter_name IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES');




select window_name, repeat_interval, duration from dba_scheduler_windows where window_name like 'WEEK%';

select window_name,  next_start_date , enabled , resource_plan from dba_scheduler_windows ; 

SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS')
,sql_tune_advisor, optimizer_stats, segment_advisor
FROM dba_autotask_window_clients;

select * from dba_scheduler_wingroup_members order by 1,2;





col target  for a20
col start_time for a20
col end_time  for a20
col notes for a20
select target,start_time,end_time,notes 
from DBA_OPTSTAT_OPERATION_TASKS 
-- where target like '%DEMO%' 
order by OPID desc;


 
 set linesize 250
set pagesize 100
column preference_name format a30
column preference_value format a50
 
-- global preferences
with preflist (preference_name,global_only)
as (select 'APPROXIMATE_NDV_ALGORITHM',0 from dual union all
    select 'AUTO_STAT_EXTENSIONS'     ,0 from dual union all
    select 'AUTO_TASK_STATUS'         ,0 from dual union all
    select 'AUTO_TASK_MAX_RUN_TIME'   ,0 from dual union all
    select 'AUTO_TASK_INTERVAL'       ,0 from dual union all
    select 'AUTOSTATS_TARGET'         ,1 from dual union all
    select 'CASCADE'                  ,0 from dual union all
    select 'CONCURRENT'               ,0 from dual union all
    select 'DEGREE'                   ,0 from dual union all
    select 'ESTIMATE_PERCENT'         ,0 from dual union all
    select 'GLOBAL_TEMP_TABLE_STATS'  ,0 from dual union all
    select 'GRANULARITY'              ,0 from dual union all
    select 'INCREMENTAL'              ,0 from dual union all
    select 'INCREMENTAL_STALENESS'    ,0 from dual union all
    select 'INCREMENTAL_LEVEL'        ,0 from dual union all
    select 'METHOD_OPT'               ,0 from dual union all
    select 'NO_INVALIDATE'            ,0 from dual union all
    select 'OPTIONS'                  ,0 from dual union all
    select 'PREFERENCE_OVERRIDES_PARAMETER',0 from dual union all
    select 'PUBLISH'                  ,0 from dual union all
    select 'STALE_PERCENT'            ,0 from dual union all
    select 'STAT_CATEGORY'            ,0 from dual union all
    select 'TABLE_CACHED_BLOCKS'      ,0 from dual union all
    select 'WAIT_TIME_TO_UPDATE_STATS',0 from dual )
select preference_name, 
       sys.dbms_stats.get_prefs(preference_name) as preference_value,
       global_only
from preflist;




#########################
Pdb Parameters related to AutoTask 
#########################

AUTOTASK_MAX_ACTIVE_PDBS
ENABLE_AUTOMATIC_MAINTENANCE_PDB



#########################
Old Auto Task Window for  daily maintenance window  : 
#########################

select window_name, repeat_interval, duration from dba_scheduler_windows order by window_name;
select * from dba_scheduler_window_groups;
select * from dba_scheduler_wingroup_members order by 1,2;
select client_name, status, con_id from cdb_autotask_client;


EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval','freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW','repeat_interval',' freq=daily;byday=SAT;byhour=07;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',   'repeat_interval','freq=daily;byday=MON;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW',  'repeat_interval','freq=daily;byday=TUE;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW', 'repeat_interval','freq=daily;byday=THU;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW',   'repeat_interval','freq=daily;byday=FRI;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'repeat_interval','freq=daily;byday=SAT;byhour=13;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW',   'repeat_interval','freq=daily;byday=SUN;byhour=13;byminute=0; bysecond=0');


BEGIN
  dbms_scheduler.disable(
    name  => 'WINDOW_NAME');
  dbms_scheduler.set_attribute(
    name      => 'WINDOW_NAME',
    attribute => 'DURATION',
    value     => numtodsinterval(6, 'hour'));
  dbms_scheduler.enable(
    name => 'WINDOW_NAME');
END;
/



BEGIN
  DBMS_SCHEDULER.disable(
    name  => 'SYS.MAINTENANCE_WINDOW_GROUP',
    force => TRUE);

  DBMS_SCHEDULER.enable(
    name  => 'SYS.MAINTENANCE_WINDOW_GROUP');
END;
/


 
BEGIN
dbms_scheduler.create_window(
window_name=>'MORNING_WINDOW',
resource_plan=>'DEFAULT_MAINTENANCE_PLAN',
repeat_interval=>'freq=daily;byhour=10;byminute=0;bysecond=0',
duration=>interval '2' hour,
comments=>'Maintenance window');

dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP',
'MORNING_WINDOW');
END;
/



BEGIN
dbms_scheduler.remove_window_group_member('MAINTENANCE_WINDOW_GROUP',
'MONDAY_WINDOW');
END;
/



#########################
Data Dictionary Views:
#########################

DBA_AUTOTASK_CLIENT_JOB 
DBA_AUTOTASK_CLIENT 
DBA_AUTOTASK_JOB_HISTORY 
DBA_AUTOTASK_WINDOW_CLIENTS 
DBA_AUTOTASK_CLIENT_HISTORY 
DBA_AUTOTASK_OPERATION
dba_autotask_schedule




#########################
Reference : 
#########################

https://tinky2jed.wordpress.com/technical-stuff/oracle-stuff/changing-the-oracle-database-default-maintenance-window-time/
https://docs.oracle.com/database/121/ADMIN/tasks.htm


Tuesday, August 27, 2024

Oracle Sql Loader handy commands

 

Data can be modified as it loads into the Oracle Database. One can also populate columns with static or derived values. However, this only applies for the conventional load path (and not for direct path loads



SQL*Loader provides the following methods to load data:

Conventional path loads – construct INSERT statements from the contents of the input datafile based on the predefined specification and execute the inserts.

Direct path loads – creates data blocks in Oracle database block format from the datafile and directly writes the data block to the database. This way is much faster than the conventional path but subject to some restrictions.

External table loads – create an external table for the data stored in the datafile and execute INSERT statements to insert the data from the datafile into the target table. The external table loads support parallel loading if datafile is big enough.



To execute the SQL*Load tool, you need at least three files:

The input data file stores delimited or raw data
The parameter file stores the location of the input/output files
The control file contains the specification on how data is loaded.




Sample Sql lOADER Commadns :

sqlldr username/password contorl=controlfile.ctl bad=bad.log data=data.dat logfile=log1.log direct=true parallel=true multithreading=true  discard=discard.log silent=feedback 
errors=1000000 bindsize=1000000 readsize=1000000  rows=1000000    multithreading=true



SQL*Loader Tuning options :

1) Direct mode 
2) bindsize=1000000 readsize=1000000  rows=1000000
3) Multiple controlfile using load and skip 
4) use append hint 
5) multithread and parallel 




Sample Control file  contents :

options (errors=10000) 
load data 
APPEND INTO TABLE AAA  ( COLUMN ) 
INSERT INTO TABLE SSS ( DDDD  ) 


  cat example1.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )



If you like to insert more data to the tables without having to delete the existing rows, use the “append’ command as shown in the following control file.

$ vi sqlldr-append-more.ctl
load data
 infile '/home/ramesh/newemployee.txt'
 append
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )


LOAD DATA
 INFILE *
 INTO TABLE tab1 WHEN tab = 'tab1'
   ( tab  FILLER CHAR(4),
     col1 INTEGER
   )
 INTO TABLE tab2 WHEN tab = 'tab2'
   ( tab  FILLER POSITION(1:4),
     col1 INTEGER
   )
BEGINDATA
tab1|1
tab1|2
tab2|2
tab3|3





LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE data.ebc "fix 86 buffers 1024"
BADFILE data.bad'
DISCARDFILE data.dsc'
REPLACE
INTO TABLE temp_data
(
 field1    POSITION (1:4)     INTEGER EXTERNAL,
 field2    POSITION (5:6)     INTEGER EXTERNAL,
 field3    POSITION (7:12)    INTEGER EXTERNAL,
 field4    POSITION (13:42)   CHAR,
 field5    POSITION (43:72)   CHAR,
 field6    POSITION (73:73)   INTEGER EXTERNAL,
 field7    POSITION (74:74)   INTEGER EXTERNAL,
 field8    POSITION (75:75)   INTEGER EXTERNAL,
 field9    POSITION (76:86)   INTEGER EXTERNAL
)




Unload data from database Table To Flat File : 


set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
  from tab1
where col2 = 'XYZ';
spool off



set colsep ','
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1, col2, col3
  from tab1
 where col2 = 'XYZ';
spool off



declare fp utl_file.file_type;
begin
  fp := utl_file.fopen('c:\oradata','tab1.txt','w');
  utl_file.putf(fp, '%s, %sn', 'TextField', 55);
  utl_file.fclose(fp);
end;
/




Reference :

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader-commands.html#GUID-CD662CD8-DAA7-4A30-BC84-546E4C40DB31