小布老师讲座笔记（五）

 

小布老师讲座笔记（五）

Archived Redo Log Files

★    Filled online redo log files can be archived

★    There are two advantages in running the database in ARCHIVELOG mode and archiving online redo log files:

﹟Recovery: A database backup together with online and archived redo log files can guarantee recovery of all committed transactions.

﹟Backup:This can be performed which the database is open

★    By default, the database is created in NOARCHIVELOG mode.

Concept of Redo Thread

★    In the RAC system, each instance has to have its own redo log groups,

The redo log file groups of an instance are collectively called a “thread”, or more appropriately, a “redo log thread”.Each instance has its own redo thread. The redo log groups function in a true circular fashion; as one fills up, another redo log records the redo entries In a stand-alone instance, there is only one thread.In a RAC sytem. typically you have as many threads as instnce ,The thread number identifies each thread.The threads may have different numbers of redo groups, but each group must have at least two members, as shown in Fig

 

 

Archived Redo Log Files

★    Accomplished automatically by ARCn

★    Accomplished manaually through SQL statements

When successfully archived:

★    An entry in the control file is made

★    Records:archive log name, log sequence number, and high and low system change number(SCN)

Filled online redo log files cannot be reused until:

★    A checkpoint has taken place

★    File has been archived by ARCn

★    Can be multiplexed

★    Maintained by the DBA

Chapter 8 Managing Tablespace & Data Files

After completing this lesson, you should be able to do the following:

★    Define the purpose of tablespaces and data files

★    Create tablespaces

★    Manage tablespaces

★    Obtain tablespace information

 

Tablespaces & Data Files

Oracle stores data logically in tablespaces and physically in data files.

Tablespaces:

★    Can belong to only one database at a time

★    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.

Storage Hierarchy summary

★    A database is made up of one or more tablespaces.

★    A tablespace is made up of one or more data files.These files might be cooked files in a file system, raw partitions, ASM managed database files, or a file on a clustered file system. A tablespace contains segments.

★    A segment(Table, Index, and so on) is made up of one or more extents. A segment exists in a tablespace, but may have data in many data files within that tablespace.

★    A extent is a logically contiguous set of blocks on disk. An extent is in a single tablespace and, furthermore, is always in a single file within that tablespace.

★    A block is the smallest unit of allocation in the database, A block is the smallest unit of I/O used by a database.

Types of Tablespaces

★    SYSTEM tablespace

﹟Created with the database

﹟Contains the data dictionary

﹟Contains the SYSTEM undo segment

★    Non-SYSTEM tablespace

﹟Separate segments

﹟Eases space administration

﹟Controls amount of space allocated to a user.

Permanent , undo , temporary

Creating Tablespaces

A tablespace is created using the command:

create tablespace

create tablespace paul datafile

   '/u01/oradata/bbk/paul01.dbf' size 20M;

select * from v$tablespace;

 

       TS# NAME                           INC BIG FLA ENC

---------- ------------------------------ --- --- --- ---

         0 SYSTEM                         YES NO  YES

         1 UNDOTBS1                       YES NO  YES

         2 SYSAUX                         YES NO  YES

         3 TEMP                           NO  NO  YES

         4 MYTBS                          YES NO  YES

         5 PAUL                           YES NO  YES

select file_name, tablespace_name from dba_data_files;

 

FILE_NAME                                TABLESPACE_NAME

---------------------------------------- ------------------------------

/u01/oradata/bbk/system01.dbf            SYSTEM

/u01/oradata/bbk/undotbs01.dbf           UNDOTBS1

/u01/oradata/bbk/sysaux01.dbf            SYSAUX

/u01/oradata/bbkmytbs01.dbf              MYTBS

/u01/oradata/bbk/paul01.dbf              PAUL

Space Mgmt in Tablespaces

    Locally managed tablespace:

☆    Free extents are managed in the tablespace.

☆    Bitmap is used to record free extents.

☆    Each bit corresponds to a block or group of blocks

☆    Bit value indicates free or used.

Dictionary-managed tablespace:

☆    Free extents are managed by the data dictionary.

☆    Appropriate tables are updated when extents are allocated or deallocated

☆     

 

select tablespace_name, contents, extent_management from dba_tablespaces;

 

TABLESPACE_NAME                CONTENTS  EXTENT_MAN

------------------------------ --------- ----------

SYSTEM                         PERMANENT LOCAL

UNDOTBS1                       UNDO      LOCAL

SYSAUX                         PERMANENT LOCAL

TEMP                           TEMPORARY LOCAL

MYTBS                          PERMANENT LOCAL

PAUL                           PERMANENT LOCAL

 

Locally Managed Tablespace

☆    Reduced contention on data dictionary tables

☆    No undo generated when space allocation or deallocation occurs

☆    No coalescing required

Migrating a DM SYSTEM TS

Migrate a dictionary managed SYSTEM tablespace to locally managed:

dbms_space_admin.tablespace_migrate_to_local(“SYSTEM”);

 

Undo Tablespace

☆    Used to store undo segments

☆    Cannot contain any other objects

☆    Extents are locally managed

☆    Can only use the DATAFILE and EXTENT MANAGEMENT clauses

create undo tablespace undo1 datafile ‘/u01/oradata/undo01.dbf’ size 40M;

Temporary Tablespaces

☆    Used for sort operations

☆    Can be shared by multiple users

☆    Cannot contain any permanent objects

☆    Locally managed extents recommended

Create temporary tablespace temp tempfile ‘/u01/oradata/temp01.dbf’ size 20M extent management local uniform size 4M;

Temporary tablespaces

when the system tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.

If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, the SYSTEM is still used for default temporary storage, however, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.

Default Temporary TS

*Specifies a database-wide default temporary tablespace

*Eliminates using SYSTEM tablespace for storing temporary data

*Can be created by using:

    create database

    alter database

To find the default temporary tablespace for the database query database_properties:

select * from database_properties;

 

Read-Only Tablespaces

☆    Use the following command to place a tablespace in read-only mode;

alter tablespace userdata read only;

Causes a checkpoint

Data available only for read operations

Objects can be dropped from tablespace

Taking a Tablespace offline

☆    Not available for data access

☆    Tablespaces that cannot be taken offline;

☆    SYSTEM tablespace

☆    Tablespace with active undo segments

☆    Default temporary tablespace

To take a tablespace offline:

alter tablespace userdata offline;

To take a tablespace online:

alter tablespace userdata online;

 

Resizing a Tablespace

A tablespace can be resized by:

Changing the size of a data file:

☆    Automatically using AUTOEXTEND

☆    Manually using ALTER DATABASE

Adding a database file using ALTER TABLESPACE

Methods for Moving DF

☆    alter tablespace

﹟Tablespace must be offline.

﹟Target data files must exits

alter tablespace userdata rename

datafile ‘/u01/oradata/bbk/userdata01.dbf’

to ‘/u01/oradata/userdata01.dbf’;

☆    alter database

  ﹟Database must be mounted.

  ﹟Target data file must exist.

   alter database rename file ‘/u01/oradata/system01.dbf’

   to ‘/u03/oradata/system01.dbf’;

 Dropping Tablespaces

    ﹟You cannot drop a tablespace if it:

        ﹟Is the SYSTEM tablespace

       ﹟Has active segments

﹟INCLUDING  CONTENTS drops the segments

﹟INCLUDING CONTENTS AND DATAFILE deletes

  data files.

CASCADE CONSTRAINTS drops all referential integrity constraints.

drop tablespace userdata including contents and datafiles;

Get Tablespace Information

Obtaining tablespace and data file information can be obtained by querying the following;

★    Tablespace information:

﹟dba_tablespaces

﹟v$tablespace

★    Data file information:

﹟DBA_DATA_FILES

﹟v$datafile

★    Temp file information:

 

Chpter 9  Storage Structure &

After completing this lesson, you should be able to the following:

★    Describe the logical structure of the database

★    List the segment types and theirs uses

★    List the keywords that control bolck space usage

★    Obtain storage structure information

Types of Segments

    ﹟Table, Table partition, Cluster, Index,Index-organizded table,

       Index partiton, Undo segment Temporary segment,LOB segment,

       Nested table, Bootstrap segment

If storage parameters are altered, the new options apply only to the extents not yet allocated.

Some parameter cannot be specified at the tablespace level.These parameters must be specified at the segment level only.

If minimum extent size has been sepcified for the tablespace, this size applies to all extents that are allocated for segments in the tablespace in the future.

An extent is a chunk of space used by a segment within a tahlespace.

An extent is allocated when the segments is:

﹟Created

﹟Extended

﹟Altered

An extent is deallocated when the segment si :

﹟Dropped, Altered Truncated

Minumum unit of I/O

Consists of one or more operating system blocks

Set at tablespace creation

DB BLOCK SIZE is the default block size

A database can be created with a standard block size and up to four nonstandard block sizes.

Block sizes can have any power-of-two value between 2 KB and 32 KB.

Set at database creation using the DB_BLOCK_SIZE parameter;cannot be changed without recreating the database.

Used for system and temporary tablespaces

DB_CACHE_SIZE specifies the size of the default buffer for standard block size:

Minimum size = one granule(4MB or 16 MB)

Default value = 48MB

Configure additional caches with the following dynamic parameters:

﹟DB_2K_CACHE_SIZE for 2KB blocks

﹟DB_4K_CACHE_SIZE for 4KB blocks

﹟DB_8K_CACHE_SIZE for 8KB blocks

﹟DB_16K_CACHE_SIZE for 16KB blocks

﹟DB_32K_CACHE_SIZE for 32KB blocks

DB_nK_CACHE_SIZE is not allowed

if nK is the standard block size.

Minimum size for each cache is one granule.

create tablespace tbs_1 datafile ‘tbs_1.dbf’ size 10M blocksize 4K;

dba_tablespace

block structure Header, Free space,Data

parameter initrans ,maxtrans, pctfree, pctused.

initrans and maxtrans: Specify the initial and the maximum number of transaction slots that are created in an index or a data block, the trancaction slots are used to store information about transactions that are making changes to the block at a point in time.A trancation uses only one transaction slot, enven if it is changing more than one row or index entry.

pctfree, pctused :the parameter specifies for a data segment, the percentage of space in each data block that is reserved for growth resulting from updates to rows in the block, The default for pctfree is 10%.

pctused: For a data segment, this parameter represents the minimum percentage of used space that the Oracle sever tries to maintain for each data block of the table. A block is put back on the free list when its used space falls below pctused.

Two methods are available for managing data blocks:

Automatic segment-space management

Manual management

Two methods are available for managing data blocks:

﹟Automatic segment-space management

﹟Manual management

It is a method of managing free space inside database segments.

Tracking in-segment free and used space is done using bitmaps as opposed to using free lists.

This method provides:

﹟Ease of management

﹟Better space utilization

﹟Better performance for concurrent Insert operations

Bitmap segments contain a bitmap that describes the status of each block in the segment with respect to its available space.

The map is contained in a separate set of blocks referred to as bitmapped blocks(BMBs).

When inserting a new row, the server searches the map for a block with sufficient space.

As the amount of space availbale in a block changes, its new state is reflected in the bitmap.

Automatic segment-space management can be enabled at the tablespace level only, for locally managed tablespaces

create tablespace data02

datafile ‘/u01/oradata/data02.dbf’ size 5M extent management local uniform size 64K segment space management auto;

After a tablespace is created, the specifications apply to all segments created in the tablespace.

Allows you to configure data blocks manaully using parameter such as :

-pctfree  -pctused –freeflist

The only method available in previous Oracle versions

HWM(High-Water Mark)for segments

10 chapter Managing Undo Data

After completing this lesson, you should be able to do the following:

﹟Describe the purpose of undo data

﹟Implement Automatic Undo Management

﹟Create and configure undo segments

﹟Obtain undo segment information from the data dictionary

Types of Undo Segments

SYSTEM: Used for objects in the SYSTEM tablespace.

NON-SYSTEM: Used for objects in other tablespaces

﹟Auto mode: Requires an UNDO tablespace

﹟Manual mode:

Auto Undo Mgmt;Concepts

﹟Undo data is managed using an UNDO tablespace.

﹟You allocate one UNDO tablespace per instance with enough space for the workload of the instance.

﹟The Oracle server automatically maintains undo data within the UNDO tablespace.

Auto Undo Mgmt: Config

﹟Configure two parameters in the initialization file:

★    UNDO MANAGEMENT

★    UNDO_TABLESPACE

﹟Create at least one UNDO tablespace

AUM: init parameters

﹟UNDO_MANAGEMENT: Specifies whether the system should use AUTO mode.

﹟UNDO_TABLESPACE: Specifies a particular UNDO tablespace to be used

UNDO_MANAGEMENT=AUTO

UNDO_TABLESPACE=UNDOTBS

AUM: UNDO Tablespace

Create the UNDO tablespace with the database by adding a clause in the CREATE DATABASE command:

CREATE DATBABASE db01

…

DATAFILE ‘/u01/oradata/undo1db01.dbf’ size 20M

AUTOEXTEND ON

OR create it later by using the create undo tablespace command:

create undo tablespace undo1

datafile ‘/u01/ordada/undo1db01.dbf’ size 20M;

AUM: Switching UNDO TS

﹟You can switch from using one UNDO tablespace to another

﹟Only one UNDO tablespace can be in assigned to a database at a time.

﹟More than one UNDO tablespace may exits within an instance, but only one can be active.

﹟Use the alter system command for dynamic switching between UNDO tablespaces.

alter system set undo_tablespace=undotbs2;

AUM: Dropping an UNDO TS

﹟The DROP TABLESPACE command drops an UNDO tablespace.

drop tablespace undotbs2;

﹟An UNDO tablespace can only be dropped if it is currently not in use by any instance.

﹟to drop an active undo tablespace:

    ﹟Switch to a new UNDO tablespace.

    ﹟Drop the tablespace after all current transactions are complete.

 

AUM: Other Parameters

☆    UNDO_SUPPRESS_ERRORS parameter:

﹟Set true, this parameter suppresses errors while attempting to execute manual operations in AUTO mode.

☆    UNDO_RETENTION parameter:

﹟This parameter controls the amount of undo data to retain for consistent read.

 

select begin_time, end_time, undoblks from v$undostat;

 

BEGIN_TIM END_TIME    UNDOBLKS

--------- --------- ----------

20-NOV-09 20-NOV-09          0

20-NOV-09 20-NOV-09         32

20-NOV-09 20-NOV-09         87

20-NOV-09 20-NOV-09          1

20-NOV-09 20-NOV-09          0

20-NOV-09 20-NOV-09          0

 

SQL> begin

  2  for i in 1 .. 10000

  3  loop

  4     insert into test values(i);

  5  end loop;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

AUM:Undo Quota

☆    Long transactions and improperly written transaction can consume valuable resources..

☆    with undo quota, users can be grouped and a maximum undo space limit can be assigned to the group.

☆    UNDO_POOL, a Resource Managr directive, defines the amount of space allowed for a resource group.

☆    When a group exceeds its limit, no new transactions are possible for the group, until undo space is freed by current transactions which are either completing or aborting.

 

Information about undo segments