Less05 Storage Tb3

5

Managing Database Storage Structures

Copyright © 2005, Oracle. All rights reserved.

Objectives After completing this lesson, you should be able to do the following: • Describe how table row data is stored in blocks • Define the purpose of tablespaces and data files • Create and manage tablespaces • Obtain tablespace information • Describe the main concepts and functionality of Automatic Storage Management (ASM)

5-2

Copyright © 2005, Oracle. All rights reserved.

Storage Structures Logical

Physical

Database

Tablespace

OS file

Segment

Extent

Oracle data block

5-3

OS block

Copyright © 2005, Oracle. All rights reserved.

How Table Data Is Stored Columns

Table A

Blocks

Table B Rows

Segment

Segment

Table Tablespace

Row piece

5-4

Copyright © 2005, Oracle. All rights reserved.

Extent

Anatomy of a Database Block

Block header Growth Free space

Row data

5-5

Copyright © 2005, Oracle. All rights reserved.

Tablespaces and Data Files The Oracle database stores data logically in tablespaces and physically in data files. • Tablespaces: – Can belong to only one database – Consist of one or more data files – Are further divided into logical units of storage

•

Data files: – Can belong to only one tablespace and one database – Are a repository for schema object data

5-6

Database Tablespace

Copyright © 2005, Oracle. All rights reserved.

Data files

Oracle Managed Files (OMF) Specify file operations in terms of database objects rather than file names. Parameter

Description

DB_CREATE_FILE_DEST

Defines the location of the default file system directory for data files and temporary files

DB_CREATE_ONLINE_LOG_DEST_n

Defines the location for redo log files and control file creation

DB_RECOVERY_FILE_DEST

Defines the location for RMAN backups

Example: SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata'; SQL> CREATE TABLESPACE tbs_1; 5-7

Copyright © 2005, Oracle. All rights reserved.

Space Management in Tablespaces •

Locally managed tablespace: – – – – –

•

Free extents are managed in the tablespace. A bitmap is used to record free extents. Each bit corresponds to a block or group of blocks. The bit value indicates free or used extents. The use of locally managed tablespaces is recommended.

Dictionary-managed tablespace: – Free extents are managed by the data dictionary. – Appropriate tables are updated when extents are allocated or unallocated. – These tablespaces are supported only for backward compatibility.

5-8

Copyright © 2005, Oracle. All rights reserved.

Exploring the Storage Structure

Click the links to view detailed information. 5-9

Copyright © 2005, Oracle. All rights reserved.

Creating a New Tablespace

5-10

Copyright © 2005, Oracle. All rights reserved.

Creating a New Tablespace (Notes Only Slide)

5-11

Copyright © 2005, Oracle. All rights reserved.

Storage for Locally Managed Tablespaces

5-12

Copyright © 2005, Oracle. All rights reserved.

Storage for Locally Managed Tablespaces Full Notes Page

5-13

Copyright © 2005, Oracle. All rights reserved.

Tablespaces in the Preconfigured Database • • •

5-14

SYSTEM SYSAUX TEMP

• • •

UNDOTBS1 USERS EXAMPLE

Copyright © 2005, Oracle. All rights reserved.

Tablespaces in the Preconfigured Database Full Notes Page

5-15

Copyright © 2005, Oracle. All rights reserved.

Altering a Tablespace

5-16

Copyright © 2005, Oracle. All rights reserved.

Altering a Tablespace Full Notes Page

5-17

Copyright © 2005, Oracle. All rights reserved.

Altering a Tablespace Full Notes Page

5-18

Copyright © 2005, Oracle. All rights reserved.

Actions with Tablespaces

5-19

Copyright © 2005, Oracle. All rights reserved.

Actions with Tablespaces Full Notes Page

5-20

Copyright © 2005, Oracle. All rights reserved.

Dropping Tablespaces

5-21

Copyright © 2005, Oracle. All rights reserved.

Viewing Tablespace Information

5-22

Copyright © 2005, Oracle. All rights reserved.

Gathering Storage Information

5-23

Copyright © 2005, Oracle. All rights reserved.

Viewing Tablespace Contents

12061_1_sel_ts_3

5-24

Copyright © 2005, Oracle. All rights reserved.

Enlarging the Database

You can enlarge the database in the following ways: • Creating a new tablespace • Adding a data file to an existing tablespace • Increasing the size of a data file • Providing for the dynamic growth of a data file Database

SYSTEM tablespace 5-25

INVENTORY tablespace

Copyright © 2005, Oracle. All rights reserved.

What Is Automatic Storage Management? Automatic Storage Management • Is a portable and high-performance cluster file system Application • Manages Oracle database files Database • Spreads data across disks to balance load File system • Mirrors data ASM Volume • Solves many storage manager management challenges Operating system

5-26

Copyright © 2005, Oracle. All rights reserved.

ASM: Key Features and Benefits ASM • Stripes files, but not logical volumes • Provides online disk reconfiguration and dynamic rebalancing • Allows for adjustable rebalancing speed • Provides redundancy on a per-file basis • Supports only Oracle database files • Is cluster aware • Is automatically installed

5-27

Copyright © 2005, Oracle. All rights reserved.

ASM: Concepts ASM disk group

Database

Tablespace

ASM file

Data file

Segment

Extent

Oracle data block

5-28

ASM disk File system file or raw device

Allocation unit

Physical block

Copyright © 2005, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to: • Describe how table row data is stored in blocks • Define the purpose of tablespaces and data files • Create and manage tablespaces • Obtain tablespace information • Describe the main concepts and functionality of Automatic Storage Management (ASM)

5-29

Copyright © 2005, Oracle. All rights reserved.

Practice Overview: Managing Database Storage Structures This practice covers the following topics: • Creating tablespaces • Gathering information about tablespaces

5-30

Copyright © 2005, Oracle. All rights reserved.