managing storage

32
11 Copyright © 2006, Oracle. All rights reserved. Managing Storage

Upload: lakia

Post on 20-Jan-2016

37 views

Category:

Documents


0 download

DESCRIPTION

Managing Storage. Space Management Proactive Monitoring Seg. Advisor & Seg.Shrink Resumable Allocation Transportable TBS and DB. Objectives. After completing this lesson, you should be able to: Describe how the Oracle database automatically manages space - PowerPoint PPT Presentation

TRANSCRIPT

Page 1: Managing Storage

11Copyright © 2006, Oracle. All rights reserved.

Managing Storage

Page 2: Managing Storage

11-2 Copyright © 2006, Oracle. All rights reserved.

Objectives

After completing this lesson, you should be able to:

• Describe how the Oracle database automatically manages space

• Proactively monitor and manage tablespace space usage

• Use the Segment Advisor

• Reclaim wasted space from tables and indexes by using the segment shrink functionality

• Manage resumable space allocation

• Describe the concepts of transportable tablespaces and databases

• SpaceManagement

• Proactive Monitoring• Seg. Advisor

& Seg.Shrink• Resumable Allocation• Transportable

TBS and DB

Page 3: Managing Storage

11-3 Copyright © 2006, Oracle. All rights reserved.

Space Management: Overview

Space is automatically managed by the Oracle database. It generates alerts about potential problems and recommends possible solutions. Features include:

• Oracle Managed Files (OMF)

• Free-space management with bitmaps (“locally managed”) and automatic data file extension

• Proactive space management (default thresholds and server-generated alerts)

• Space reclamation (shrinking segments, online table redefinition)

• Capacity planning (growth reports)

Page 4: Managing Storage

11-4 Copyright © 2006, Oracle. All rights reserved.

Free Space Management

• Automatic

• Enabled by the use of locally managed tablespaces

• Tracked by bitmaps in segments

Benefits:

• More flexible space utilization

• Run-time adjustment

• Multiple process search of BMBs{Extent

BMB BMB

Datablock

… … … …

BMB

BMB

BMB

BMB

BMB

Segment

Page 5: Managing Storage

11-5 Copyright © 2006, Oracle. All rights reserved.

Types of Segments

A segment is a set of extents allocated for a certain logical structure. The different types of segments are:

• Data segment

• Index segment

• Temporary segment

Segments are dynamically allocated by the database.

Page 6: Managing Storage

11-6 Copyright © 2006, Oracle. All rights reserved.

Allocating Extents

• Searching the data file’s bitmap for the required number of adjacent free blocks

• Sizing extents with storage clauses:– UNIFORM – AUTOALLOCATE

• Viewing extent map

• Obtaining deallocation advice

Page 7: Managing Storage

11-7 Copyright © 2006, Oracle. All rights reserved.

Block Space Management

PCTFREE = 10

Deletes

Full block

Inserts,updates

Deletes

FS2

FS3

FS1

FS2

FS3

FS4

FS1

Full block

Page 8: Managing Storage

11-8 Copyright © 2006, Oracle. All rights reserved.

Row Chaining and Migration

Example:

• On update: Row length increases, exceeding the available free space in the block.

• Data needs to be stored in a new block.

• Original physical identifier of row (ROWID) is preserved.

• The Oracle database needs to read two blocks to retrieve data.

• The Segment Advisor finds segments containing the migrated rows.

Old

Original block with pointer to migrated

row

New data

Page 9: Managing Storage

11-9 Copyright © 2006, Oracle. All rights reserved.

Proactive Tablespace Monitoring . .

SpaceManagement

> Proactive Monitoring

Seg. Advisor& Seg.ShrinkResumable

Allocation Transportable

TBS and DB

Page 10: Managing Storage

11-10 Copyright © 2006, Oracle. All rights reserved.

Thresholds andResolving Space Problems

Resolve space problem by:

• Adding or resizing data file

• Setting AUTOEXTEND ON• Shrinking objects

• Reducing UNDO_RETENTION • Checking for long-running queries in temporary

tablespaces

85% full or 100 MB left warning

97% full or 5 MB left critical

Resolve space problem

Alert

Locally managed tablespace

DBA

Page 11: Managing Storage

11-11 Copyright © 2006, Oracle. All rights reserved.

Monitoring Tablespace Space Usage

• Read-only and offline tablespaces: Do not set up alerts.• Temporary tablespace: Threshold corresponds to

space currently used by sessions.• Undo tablespace: Threshold corresponds to space

used by active and unexpired extents.• Autoextensible files: Threshold is based on the

maximum file size.

MMON

85% Warning

97% Critical

Check every 10 min.

Alert

AlertCleared

Cleared

Page 12: Managing Storage

11-12 Copyright © 2006, Oracle. All rights reserved.

Shrinking Segments

HWM

HWM

HWM

ALTER TABLE employees SHRINK SPACE COMPACT;1

ALTER TABLE employees SHRINK SPACE;2

DML operations and queries can be issued during compaction.

DML operations are blocked when the HWM is adjusted.

SpaceManagementProactive

Monitoring> Seg. Advisor

& Seg.ShrinkResumable

Allocation Transportable

TBS and DB

Page 13: Managing Storage

11-13 Copyright © 2006, Oracle. All rights reserved.

Results of Shrink Operation

• Improved performance and space utilization

• Indexes maintained

• Triggers not executed

• Number of migrated rows may be reduced

• Rebuilding secondary indexes on IOTs recommended

DBAShrink

Triggers not executed

Table

Index

Page 14: Managing Storage

11-14 Copyright © 2006, Oracle. All rights reserved.

Space Reclamation with ASSM

• Online and in-place operation

• Applicable only to segments residing in ASSM tablespaces

• Candidate segment types:– Heap-organized tables and index-organized tables– Indexes– Partitions and subpartitions– Materialized views and materialized view logs

Page 15: Managing Storage

11-15 Copyright © 2006, Oracle. All rights reserved.

Segment Advisor: Overview

Page 16: Managing Storage

11-16 Copyright © 2006, Oracle. All rights reserved.

Segment Advisor

Page 17: Managing Storage

11-18 Copyright © 2006, Oracle. All rights reserved.

Implementing Recommendations

Page 18: Managing Storage

11-19 Copyright © 2006, Oracle. All rights reserved.

Database Control and Segment Shrink

Page 19: Managing Storage

11-20 Copyright © 2006, Oracle. All rights reserved.

Shrinking Segments by Using SQL

ALTER … SHRINK SPACE [CASCADE]

TABLE [OVERFLOW] INDEX MATERIALIZED VIEW MATERIALIZED VIEW LOG

MODIFY PARTITION MODIFY SUBPARTITION MODIFY LOB

ALTER TABLE employees SHRINK SPACE CASCADE;

ALTER TABLE employees ENABLE ROW MOVEMENT; 1

2

ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE);3

ALTER TABLE employees OVERFLOW SHRINK SPACE; 4

Page 20: Managing Storage

11-21 Copyright © 2006, Oracle. All rights reserved.

Managing Resumable Space Allocation

A resumable statement:

• Enables you to suspend large operations instead of receiving an error

• Gives you a chance to fix the problem while the operation is suspended, rather than starting over

• Is suspended for the following conditions:– Out of space– Maximum extents reached– Space quota exceeded

SpaceManagementProactive

MonitoringSeg. Advisor& Seg.Shrink

> Resumable Allocation Transportable

TBS and DB

Page 21: Managing Storage

11-22 Copyright © 2006, Oracle. All rights reserved.

Using Resumable Space Allocation

• Queries, DML operations, and certain DDL operations can be resumed if they encounter an out-of-space error.

• A resumable statement can be issued through SQL, PL/SQL, SQL*Loader, or the Oracle Call Interface (OCI).

• To run a resumable statement, you must first enable resumable statements for your session.

ALTER SESSION ENABLE RESUMABLE;

INSERT INTO sales_new SELECT * FROM sh.sales;

ALTER SESSION DISABLE RESUMABLE;

Page 22: Managing Storage

11-24 Copyright © 2006, Oracle. All rights reserved.

Resuming Suspended Statements

SQL statement

Full table

Suspended

Continue SQL operation

AFTERSUSPENDtrigger

Abort

Optionally

Table with free space

1

2

3 4

5

Page 23: Managing Storage

11-26 Copyright © 2006, Oracle. All rights reserved.

Transporting Tablespaces .

Concept: Cross-platform transportable tablespaces:

• Simplify data distribution between data warehouse and data marts

• Allow database migration from one platform to another

• Supported platforms:

Microsoft Windows 64-bit for AMDHP Open VMSAIX-Based Systems (64-bit)

Solaris Operating System (x86)

Apple Mac OSLinux IA (64-bit)Linux IA (32-bit)

Linux 64-bit for AMDHP-UX IA (64-bit)Microsoft Windows IA (32-bit)

IBM zSeries Based LinuxHP Tru64 UNIXSolaris[tm] OE (64-bit)

Microsoft Windows IA (64-bit)HP-UX (64-bit)Solaris[tm] OE (32-bit)

…Resumable

Allocation > Transportable TBS Transportable DB

Page 24: Managing Storage

11-27 Copyright © 2006, Oracle. All rights reserved.

Concept: Minimum Compatibility Level

• Both source and target databases must have COMPATIBLE set to 10.0.0 or higher.

• Data file headers are platform-aware.

• Before transporting, make sure that all read-only and offline files are platform-aware.

Read-only

Read-only

Read/write

Read-only

COMPATIBLE=10.0.0COMPATIBLE=9.2.0

Read/write

Read/write

Page 25: Managing Storage

11-28 Copyright © 2006, Oracle. All rights reserved.

Transportable Tablespace Procedure

Make tablespaces read-only.

Yes

Target uses the same endian format?

Use Data Pump to extract metadata.

Convert data filesby using RMAN.

Ship data files and dump file to target.

Use Data Pump to import metadata.

Make tablespaces read/write.

No

Source

Target

Page 26: Managing Storage

11-29 Copyright © 2006, Oracle. All rights reserved.

Determining the Endian Formatof a Platform

SELECT tp.endian_formatFROM v$transportable_platform tp, v$database dWHERE tp.platform_name = d.platform_name;

Source Target

Page 27: Managing Storage

11-30 Copyright © 2006, Oracle. All rights reserved.

Transporting Databases

• Generalize the transportable tablespace feature.

• Data can easily be distributed from a data warehousing environment to data marts, which are usually on smaller platforms.

• A database can be migrated from one platform to another very quickly.

Platform X Platform Y

Database files transfer

Same “endianness”

Existing database New database

…Resumable

AllocationTransportable TBS

> Transportable DB

Page 28: Managing Storage

11-31 Copyright © 2006, Oracle. All rights reserved.

Database Transportation Procedure:Source System Conversion

Sourcesystem

Read-only

Open database in READ ONLY modeand COMPATIBLE=10.0.0

Targetsystem

Read-onlyCONVERT

DATABASETO PLATFORM

Read-only

Ship

filesRead-only

Page 29: Managing Storage

11-32 Copyright © 2006, Oracle. All rights reserved.

Database Transportation Procedure:Target System Conversion

Sourcesystem

Open database in READ ONLY modeand COMPATIBLE=10.0.0

Targetsystem

Read-only

CONVERTDATABASEON TARGET

Read-only

Read-only

Read-only

Ship

files

Page 30: Managing Storage

11-33 Copyright © 2006, Oracle. All rights reserved.

Database Transportation: Considerations

• Create the password file on the target platform.

• Transport the BFILEs used in the source database.

• The generated pfile and transport script use OMF.

• Use DBNEWID to change the DBID.

Page 31: Managing Storage

11-34 Copyright © 2006, Oracle. All rights reserved.

Summary

In this lesson, you should have learned how to:

• Use the Oracle database to automatically manage space

• Proactively monitor and manage tablespace space usage

• Use the Segment Advisor

• Reclaim wasted space from tables and indexes by using the segment shrink functionality

• Manage resumable space allocation

• Describe the concepts of transportable tablespaces and databases

Page 32: Managing Storage

11-35 Copyright © 2006, Oracle. All rights reserved.

Practice Overview:Managing Storage

This practice covers the following topics:

• Using threshold alerts to proactively manage tablespaces

• Using the Segment Advisor to shrink space

• Viewing alerts and alert history in SQL*Plus and Enterprise Manager