Mindmajix

Regulating locally managed tablespace : Oracle DBA

Oracle database includes the dbms_space_admin package, which is a collection of aids for diagnosing and repairing problems in locally managed tablespaces.

Dbms_space_admin package procedures:

The following table lists the dbms_space_admin package procedures.

Procedure

Description

Assm_segment_verify

Verifies the integrity of segments created in tablespaces that have automatic segment space management enabled. Outputs a dump file named sid_ora_process_id.trc to the location specified in the user_dump_dest initialization parameter.

Use segment_verify for tablespaces with manual segment space management.

Assm_tablespace_verify

Verifies the integrity of tablespaces that have automatic segment space management enabled. Outputs a dump file named sid_ora_process_id.trc to the location specified in the user_dump_dest initialization parameter.

Use tablespace_verify for tablespaces with manual segment space management.

Segment_corrupt

Marks the segment corrupt or valid so that appropriate error recovery can be done

Segment_drop_corrupt

Drops a segment currently marked corrupt (without reclaiming space)

Segment_dump

Dumps the segment header and bitmap blocks of a specific segment to a dump file named sid_ora_process_id.trc in the location specified in the user_dump_dest initialization parameter. Provides an option to select a slightly abbreviated dump, which includes segment header and includes bitmap block summaries, without percent-free states of each block.

Segment_verify

Verifies the consistency of the extent map of the segment

Tablespace_fix_bitmaps

Marks the appropriate dba range (extent) as free or used in bitmap

Tablespace_fix_segment_states

Fixes the state of the segments in a tablespace in which migration was stopped

Tablespace_migrate_from_local

Migrates a locally managed tablespace to dictionary-managed tablespace

Tablespace_migrate_to_local

Migrates a dictionary-managed tablespace to a locally managed tablespace

Tablespace_rebuild_bitmaps

Rebuilds the appropriate bitmaps

Tablespace_rebuild_quotas

Rebuilds quotas for a specific tablespace

Tablespace_relocate_bitmaps

Relocates the bitmaps to the specified destination

Tablespace_verify

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

The following scenarios describe typical situations in which you can use the dbms_space_admin package to diagnose and resolve problems.

Note:

Some of these procedures can result in lost and unrecoverable data if not used properly. You should work with oracle support services if you have doubts about these 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 rebuild 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$
  5. Call tablespace_rebuild_quotas to rebuild 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 that 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:

  • Call the tablespace_rebuild_bitmaps procedure, either on all bitmap blocks, or on a single block if only one is corrupt.
  • Call the tablespace_rebuild_quotas procedure to rebuild quotas.
  • Call the tablespace_verify procedure to verify that the bitmaps are consistent.

Scenario 5: migrating from a dictionary-managed to a locally managed tablespace:

Use the tablespace_migrate_to_local procedure to migrate a dictionary-managed tablespace to a locally managed tablespace. This operation is done online, but space management operations are blocked until the migration has been completed. This means that you can read or modify data while the migration is in progress, but if you are loading a large amount of data that requires the allocation of additional extents, then the operation may be blocked.

Assume that the database block size is 2k and the existing extent sizes in tablespace tbs_1 are 10, 50, and 10,000 blocks (used, used, and free). The minimum extent value is 20k (10 blocks). Allow the system to choose the bitmap allocation unit. The value of 10 blocks is chosen, because it is the highest common denominator and does not exceed minimum extent.

The statement to convert tbs_1 to a locally managed tablespace is as follows:

Sql>exec dbms_space_admin.tablespace_migrate_to_local ('tbs_1');

If you choose to specify an allocation unit size, it must be a factor of the unit size calculated by the system.

Migrating the system tablespace to a locally managed tablespace:

Use the dbms_space_admin package to migrate the system tablespace from dictionary-managed to locally managed. The following statement performs the migration:

Sql>executedbms_space_admin.tablespace_migrate_to_local('system');

Before performing the migration, the following conditions must be met:

  • The database has a default temporary tablespace that is not system.
  • There are no rollback segments in the dictionary-managed tablespace.
  • There is at least one online rollback segment in a locally managed tablespace, or if using automatic undo management, an undo tablespace is online.
  • All tablespaces other than the tablespace containing the undo space (that is, the tablespace containing the rollback segment or the undo tablespace) are in read-only mode.
  • The system is in restricted mode.
  • There is a cold backup of the database.

All of these conditions, except for the coldbackup, are enforced by the blespace_migrate_to_local procedure.

Note:

After the system tablespace is migrated to locally managed, any dictionary-managed tablespaces in the database cannot be made read/write. If you want to be able to use the dictionary-managed tablespaces in read/write mode, then oracle recommends that you must first migrate these tablespaces to locally managed before migrating the system tablespace.

Transporting tablespaces between databases:

You must be using the enterprise edition of oracle8i or later to generate a transportable tablespace set. However, you can use any edition of oracle8i or later to import a transportable tablespace set into an oracle database on the same platform. To import a transportable tablespace set into an oracle database on a different platform, both databases must have compatibility set to at least 10.0.


 

0 Responses on Regulating locally managed tablespace : Oracle DBA"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.