oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Repairing Tablespaces in Oracle

Diagnosing and Repairing Locally Managed Tablespace Problems

To diagnose and repair corruptions in Locally Managed Tablespaces Oracle has supplied a package called DBMS_SPACE_ADMIN. This package has many procedures described below:

Procedure

Description

SEGMENT_VERIFY

Verifies the consistency of the extent map of the segment.

SEGMENT_CORRUPT

Marks the segment corrupt or valid so that appropriate error recovery can be done. Cannot be used for a locally managed SYSTEM tablespace.

SEGMENT_DROP_CORRUPT

Drops a segment currently marked corrupt (without reclaiming space). Cannot be used for a locally managed SYSTEM tablespace.

SEGMENT_DUMP

Dumps the segment header and extent map of a given segment.

TABLESPACE_VERIFY

Verifies that the bitmaps and extent maps for the segments in the tablespace are in sync.

TABLESPACE_REBUILD_BITMAPS

Rebuilds the appropriate bitmap. Cannot be used for a locally managed SYSTEM tablespace.

TABLESPACE_FIX_BITMAPS

Marks the appropriate data block address range (extent) as free or used in bitmap. Cannot be used for a locally managed SYSTEM tablespace.

TABLESPACE_REBUILD_QUOTAS

Rebuilds quotas for given tablespace.

TABLESPACE_MIGRATE_FROM_LOCAL

Migrates a locally managed tablespace to dictionary-managed tablespace. Cannot be used to migrate a locally managed SYSTEM tablespace to a dictionary-managed SYSTEM tablespace.

TABLESPACE_MIGRATE_TO_LOCAL

Migrates a tablespace from dictionary-managed format to locally managed format.

TABLESPACE_RELOCATE_BITMAPS

Relocates the bitmaps to the destination specified. Cannot be used for a locally managed system tablespace.

TABLESPACE_FIX_SEGMENT_STATES

Fixes the state of the segments in a tablespace in which migration was aborted.

 

Be careful using the above procedures if not used properly you will corrupt your database. Contact Oracle Support before using these procedures.

Following are some of the Scenarios where you can use the above procedures

Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)

The TABLESPACE_VERIFY procedure discovers that a segment has allocated blocks that are marked free in the bitmap, but no overlap between segments is reported.

In this scenario, perform the following tasks:

  1. Call the SEGMENT_DUMP procedure to dump the ranges that the administrator allocated to the segment.
  2. For each range, call the TABLESPACE_FIX_BITMAPS procedure with the TABLESPACE_EXTENT_MAKE_USED option to mark the space as used.
  3. Call TABLESPACE_REBUILD_QUOTAS to fix up quotas.

Scenario 2: Dropping a Corrupted Segment

You cannot drop a segment because the bitmap has segment blocks marked "free". The system has automatically marked the segment corrupted.

In this scenario, perform the following tasks:

  1. Call the SEGMENT_VERIFY procedure with the SEGMENT_VERIFY_EXTENTS_GLOBAL option. If no overlaps are reported, then proceed with steps 2 through 5.
  2. Call the SEGMENT_DUMP procedure to dump the DBA ranges allocated to the segment.
  3. For each range, call TABLESPACE_FIX_BITMAPS with the TABLESPACE_EXTENT_MAKE_FREE option to mark the space as free.
  4. Call SEGMENT_DROP_CORRUPT to drop the SEG$ entry.
  5. Call TABLESPACE_REBUILD_QUOTAS to fix up quotas.

Scenario 3: Fixing Bitmap Where Overlap is Reported

The TABLESPACE_VERIFY procedure reports some overlapping. Some of the real data must be sacrificed based on previous internal errors.

After choosing the object to be sacrificed, in this case say, table t1, perform the following tasks:

  1. Make a list of all objects that t1 overlaps.
  2. Drop table t1. If necessary, follow up by calling the SEGMENT_DROP_CORRUPT procedure.
  3. Call the SEGMENT_VERIFY procedure on all objects that t1 overlapped. If necessary, call the TABLESPACE_FIX_BITMAPS procedure to mark appropriate bitmap blocks as used.
  4. Rerun the TABLESPACE_VERIFY procedure to verify the problem is resolved.

Scenario 4: Correcting Media Corruption of Bitmap Blocks

A set of bitmap blocks has media corruption.

In this scenario, perform the following tasks:

  1. Call the TABLESPACE_REBUILD_BITMAPS procedure, either on all bitmap blocks, or on a single block if only one is corrupt.
  2. Call the TABLESPACE_REBUILD_QUOTAS procedure to rebuild quotas.
  3. Call the TABLESPACE_VERIFY procedure to verify that the bitmaps are consistent.

Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace

To migrate a dictionary-managed tablespace to a locally managed tablespace. You use the TABLESPACE_MIGRATE_TO_LOCAL procedure.

For example if you want to migrate a dictionary managed tablespace ICA2 to Locally managed then give the following command.

EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('ica2');

 

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved