Identifying Corruption

IDENTIFYING CORRUPTION


Find Corrupt Segments
set linesize 140
col owner format a10
col segment_type format a10
col segment_name format a30
col partition_name format a15
col file# format 999
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#
/

Generate Tablespace DDL

GENERATE TABLESPACE DDL

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

or

select 'select dbms_metadata.get_ddl(''TABLESPACE'','''||tablespace_name||''') from dual;' from dba_tablespaces;

RMAN Backup File Order

RMAN BACKUP FILE ORDER

Question:
Assuming a single channel and filesperset=1, what order does RMAN choose to backup datafiles?

Answer:
The order of files is random. The File Names are read from V$DATAFILE view and due to performance bug, the ORDER BY FILE# clause is removed, hence the query can return file# in random order.

Reset Oracle Sequence in-place

HOW TO RESET A SEQUENCE IN-PLACE

  This avoids drop/create, and re-plumbing grants and synonyms.

  1. Set the "increment by" value to -nextval-1
  2. Increment the sequence
  3. Set the "increment by" value back to 1

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

1887203408

SQL> alter sequence myschema.mysequence increment by -1887203407;

Sequence altered.

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

         1

SQL> alter sequence myschema.mysequence increment by 1;

Sequence altered.

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

         2

SQL> alter system flush shared_pool;

System altered.