an over view of oracle

Slide 1: 

10/14/2010 1 OVERVIEW ORACLE OF

Slide 2: 

10/14/2010 2 OBJECTIVES Getting familiarity with Oracle Going through installation of Oracle 8 Working with Database creation in Oracle 8 Getting familiarity with Oracle database structure Managing Oracle database structure

Slide 3: 

10/14/2010 3 INTRODUCTION ORACLE 8 INSTALLATION DATABASE CREATION DATABASE STRUCTURES MANAGE DATABASE STRUCTURES CONTENTS

Slide 4: 

10/14/2010 4 Oracle Corporation is the world's leading supplier of software products for information management Oracle Corporation has more than 43,000 employees worldwide and does business in over 150 countries. Based in Redwood Shores, California, USA INTRODUCTION Founded in August, 1977 by Larry Ellison, Bob Miner, Ed Oates and Bruce Scott

Slide 5: 

10/14/2010 5 Oracle Corporation is the world's first software company to develop and deploy 100 percent Internet -enabled enterprise software Oracle RDBMS is the world's first to support the SQL INTRODUCTION First commercial RDBMS was built in 1979 Oracle remains the gold standard for database technology and applications in enterprises throughout the world

Slide 6: 

10/14/2010 6 INTRODUCTION Oracle 8th release – Oracle 8.0.5 Oracle 8 Database commonly referred to as Oracle 8 RDBMS or simply Oracle 8. Oracle 8 is a relational database management system. It provides the ability to store and access data. It defines data model known as the relational data model. Its predecessor is Oracle 7th release, Oracle 7.3

Slide 7: 

10/14/2010 7 INTRODUCTION Oracle 8 was launched in 1999 Oracle 8 is able to store and retrieve tens of terabytes and capture up to 1,000 columns of data It supports Java, HTML and OLTP Oracle 8 has been designed to support access over the Web by thousands of users Oracle 8 includes the technology to speed up data warehouse queries Oracle 8 has implemented automated server backup and recovery

Slide 8: 

10/14/2010 8 ORACLE 8 INSTALLATION System Requirements - Hardware requirements - Software requirements Hardware requirements Server: PC based on a Pentium processor, Multi-processor computers are also supported, min 48 MB RAM Hard disk space: Min 283 MB for db s/w and default database

Slide 9: 

10/14/2010 9 Software requirements Windows NT WS 4.0/Windows NT Server 4.0/ Windows NT Server EEd.4.0 / Windows 95/98 ORACLE 8 INSTALLATION System Requirements Hardware requirements Clients: PC based on an Intel 80486 processor or higher Windows NT: 16 MB RAM for Application User 32 MB RAM for Database Administrator Windows 95/98: 8 MB RAM for Application User 24 MB RAM for Database Administrator Hard disk space: App user – min 120 MB, DBA – min 155MB

Slide 10: 

10/14/2010 10 DATABASE CREATION Using Oracle Database Assistant Select the type of database creation Select the size of database Assign database name, sid, parameter file, internal password Create control files Assign no. of max data files, max log files, max log members Create system, user, rollback, index and temporary tablespaces Create redo log files and assign its sizes & other parameters Assign SGA parameters Assign trace file directory for user and background processes

Slide 11: 

10/14/2010 11 Memory Structures of Oracle Instance The combination of SGA and processes is called database instance System Global Area (SGA) An area in the memory, The combination of shared pool, the database buffer, and the redo-log buffer Program Global Area (PGA) The area in the memory that is used by a single Oracle user process

Slide 12: 

10/14/2010 12 SGA Database Buffer A portion of SGA that stores the most recently used blocks of database data. Shared Pool A portion of SGA that contains shared memory constructs such as shared SQL areas. Redo Log Buffer A portion of SGA that stores redo entries.

Slide 13: 

10/14/2010 13 Processes of Oracle For each database instance, there is a set of processes. These processes maintain and enforce the relationships between the database’s physical structures and memory structures. DBWR Writes modified data blocks to the data files LGWR Writes the contents of the redo-log-buffer to the redo-log files. SMON Performs instance recovery as needed. It cleans up the database from aborted transactions involved.

Slide 14: 

10/14/2010 14 Processes of Oracle PMON Cleans up failed user processes and it also cleans up the resources used by these processes. USER Communicate with other processes started by application programs. It is responsible for sending respective operations and requests to the SGA/PGA. ARCH Copies the online redo log files to archival storage when they are full.

Slide 15: 

10/14/2010 15 Oracle System Architecture

Slide 16: 

10/14/2010 16 DATABASE STRUCTURES Physical Database Structure Logical Database structure The physical and logical database structure are separate, the physical storage of data can be managed without affecting the access to logical storage structures.

Slide 17: 

10/14/2010 17 Physical Database Structure An Oracle database's physical structure is determined by the operating system files that constitute the database. Each Oracle database is made of three types of files: one or more datafiles, two or more redo log files, and one or more control files. The files of an Oracle database provide the actual physical storage for database information.

Slide 18: 

10/14/2010 18 Physical Database Structure Datafiles Redo Log Files Control Files

Slide 19: 

10/14/2010 19 Logical Database Structure The logical structure of an Oracle database is determined by - One or more tablespaces,segments and extents The database's schema objects A tablespace is a logical area of storage A schema is a collection of database objects. Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, stored procedures, synonyms, indexes and database links.

Slide 20: 

10/14/2010 20 Physical Database Structure Datafiles Every Oracle database has one or more physical datafiles. A database's datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.

Slide 21: 

10/14/2010 21 Physical Database Structure The characteristics of datafiles are: A datafile can be associated with only one tablespace. Database files can be automatically extend. One or more datafiles form a logical unit of database storage called a tablespace. The Use of Datafiles: The data is stored/written in the datafiles. The data is read from the datafiles.

Slide 22: 

10/14/2010 22 Physical Database Structure Redo Log Files Every Oracle database has a set of two or more redo log files. The set of redo log files for a database is collectively known as the database's redo log. The primary function of the redo log is to record all changes made to data. Oracle allows a multiplexed redo log so that two or more copies of the redo log can be maintained on different disks. The Use of Redo Log Files: The information in a redo log file is used only to recover the database.

Slide 23: 

10/14/2010 23 Physical Database Structure Control Files Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database. Oracle allows the control file to be multiplexed for protection of the control file. It contains the following types of information: Database name Names and locations of a database's datafiles and redo log files Time stamp of database creation

Slide 24: 

10/14/2010 24 The Use of Control Files: Physical Database Structure Control file is used to identify the datafiles and redo log files that must be opened for database operation to proceed. Control file is automatically modified by Oracle to reflect the change if the physical makeup of database is altered. Control file is also used if database recovery is necessary.

Slide 25: 

10/14/2010 25 Logical Database Structure Logical database structure includes: Tablespaces Schema objects Data blocks Extents Segments.

Slide 26: 

10/14/2010 26 Logical Database Structure Tablespaces A tablespace is a logical storage unit within an Oracle database. A tablespace consists of at least one datafile. Schema objects such as tables, indexes, sequences, stored procedures, synonyms etc. are stored in database belong to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem.

Slide 27: 

10/14/2010 27 Logical Database Structure Tablespaces of Oracle 8 SYSTEM USERS INDEX ROLLBACK TEMPORARY

Slide 28: 

10/14/2010 28 Logical Database Structure Tablespaces (The relationship among database, tablespaces, and datafiles)

Slide 29: 

10/14/2010 29 Logical Database Structure Tablespaces The relationship among database, tablespaces, and datafiles is illustrated as :- Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace. Datafiles physically store the data of all logical structures in a tablespace.

Slide 30: 

10/14/2010 30 Logical Database Structure Tablespaces The relationship among database, tablespaces, and datafiles is illustrated as :- The combined size of a tablespace's datafiles is the total storage capacity of the tablespace. The combined storage capacity of a database's tablespaces is the total storage capacity of the database.

Slide 31: 

10/14/2010 31 Logical Database Structure Tablespaces Online and Offline Tablespaces A tablespace can be online. An online tablespace is accessible. An offline tablespace is not accessible. A tablespace is normally online so that users can access the information within the tablespace. A tablespace can be offline.

Slide 32: 

10/14/2010 32 Logical Database Structure Schema A schema is a collection of objects such as tables, views, sequences, stored procedures, synonyms, indexes and database links. Schema is the structure for a Database.

Slide 33: 

10/14/2010 33 Logical Database Structure Schema objects Tables Views Sequences Procedures Synonyms Indexes Database links

Slide 34: 

10/14/2010 34 Logical Database Structure Data blocks A smallest unit of storage Made of physical bytes Size is a multiple of the OS block size If OS data block size is 2K then Oracle data block size can be 2K, 4K, 8K, 16k etc. Store data and storage information

Slide 35: 

10/14/2010 35 Data Block Format :

Slide 36: 

10/14/2010 36 Data Block Format : Header Data block information, such as the block address and the type of segment Table Directory Information about the tables Row Directory Information about the actual rows in the block Overhead Data block header, table directory, and row directory are referred to collectively as overhead.

Slide 37: 

10/14/2010 37 Free Space Data Block Format : Space allocated for insertion of new rows For updates to rows that require additional space Row Data Contains table or index data

Slide 38: 

10/14/2010 38 Extents Logical Database Structure A logical unit of storage space allocation Consists of one/more contiguous data blocks A part of a segment Belongs to a tablespace.

Slide 39: 

10/14/2010 39 Logical Database Structure Segments A logical storage A set of extents Allocated for a logical structure A database object A segment is a container for database objects

Slide 40: 

10/14/2010 40 Logical Database Structure Types of Segments Data Segment Index Segment Rollback Segment Temporary Segment

Slide 41: 

10/14/2010 41 Logical Database Structure Data Segment Data of a table that is not partitioned or clustered Data of a partition of a partitioned table Data of a cluster of tables Associated with a tablespace Created by CREATE statement

Slide 42: 

10/14/2010 42 Logical Database Structure Index Segment Data of a non-partitioned index Data of a partitioned index Created by CREATE INDEX statement Associated with a tablespace

Slide 43: 

10/14/2010 43 Logical Database Structure Rollback Segment Created by the database administrator Temporarily store "undo" information Rollback uncommitted transactions for users Used during transactions Associated with a tablespace

Slide 44: 

10/14/2010 44 Logical Database Structure Temporary Segment Automatically created A database area for sorting Used when processing queries Associated with a tablespace

Slide 45: 

10/14/2010 45 Relationships Among Segments, Extents, and Data Blocks

Slide 46: 

10/14/2010 46 MANAGE DATABASE STRUCTURE Managing Tablespaces Managing Datafiles Managing the Online Redo Log Managing Control Files Managing Rollback Segments Managing Tables

Slide 47: 

10/14/2010 47 Managing Tablespaces Guidelines for Managing Tablespaces Creating Tablespaces Managing Tablespace Allocation Altering Tablespace Availability Making a Tablespace Read-Only Dropping Tablespaces

Slide 48: 

10/14/2010 48 Managing Tablespaces Guidelines for Managing Tablespaces Separate user data from data dictionary data Separate one application data from another's Store different tablespaces datafiles on separate HDD Separate rollback segment data from user data Back up individual tablespaces Specify default storage parameters for a new tablespace Assign tablespace quotas as necessary to database users

Slide 49: 

10/14/2010 49 Managing Tablespaces Creating Tablespaces Create Tablespaces by OEM Create Tablespaces by SQL command The following statement creates the tablespace RB_SEGS: CREATE TABLESPACE rb_segs DATAFILE 'datafile_1' SIZE 50M DEFAULT STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50);

Slide 50: 

10/14/2010 50 Managing Tablespaces Managing Tablespace Allocation Altering Storage Settings for Tablespaces The following statement alter the tablespace RB_SEGS: ALTER TABLESPACE rb_segs DEFAULT STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 5 MAXEXTENTS 50); By using OEM By using SQL command

Slide 51: 

10/14/2010 51 Managing Tablespaces Managing Tablespace Allocation Coalescing Free Space The following statement coalesce all available free space extents in a tablespace ALTER TABLESPACE tablespace COALESCE; Smaller contiguous free extents can be coalesced into one larger free extent

Slide 52: 

10/14/2010 52 Managing Tablespaces Altering Tablespace Availability Bringing Tablespaces Online Taking Tablespaces Offline Statements for online/offline a tablespace ALTER TABLESPACE users ONLINE; ALTER TABLESPACE users OFFLINE NORMAL ; By using OEM By using SQL command

Slide 53: 

10/14/2010 53 Managing Tablespaces Making a Tablespace Read-Only Statements for making a tablespace read-only ALTER TABLESPACE users READ ONLY; By using OEM By using SQL command Dropping Tablespaces By using OEM By using SQL command DROP TABLESPACE users INCLUDING CONTENTS;

Slide 54: 

10/14/2010 54 Managing Datafiles Guidelines for Managing Datafiles Creating and Adding Datafiles to a tablespace Changing a Datafile's Size Altering Datafile Availability Renaming and Relocating Datafiles

Slide 55: 

10/14/2010 55 Managing Datafiles Guidelines for Managing Datafiles Number of Datafiles Set the Size of Datafiles Place Datafiles Appropriately Store Datafiles Separately From Redo Log Files

Slide 56: 

10/14/2010 56 Managing Datafiles Creating and Adding Datafiles to a tablespace By using OEM By using SQL command ALTER TABLESPACE rb_segs ADD DATAFILE 'filename1' SIZE 10M; ;

Slide 57: 

10/14/2010 57 Managing Datafiles Changing a Datafile's Size By using OEM By using SQL command ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;

Slide 58: 

10/14/2010 58 Managing Datafiles Altering Datafile Availability By using OEM By using SQL command ALTER DATABASE DATAFILE 'filename' ONLINE; ALTER DATABASE DATAFILE 'filename' OFFLINE DROP;

Slide 59: 

10/14/2010 59 Managing Datafiles Renaming and Relocating Datafiles Renaming and Relocating Datafiles for a Single Tablespace Renaming and Relocating Datafiles for Multiple Tablespaces ALTER TABLESPACE users RENAME DATAFILE 'filename1', 'filename2' TO 'filename3', 'filename4';

Slide 60: 

10/14/2010 60 Managing the Online Redo Log Planning the Online Redo Log Creating Online Redo Log Groups and Members Renaming and Relocating Online Redo Log Members Dropping Online Redo Log Groups Dropping Online Redo Log Members Clearing an Online Redo Log File

Slide 61: 

10/14/2010 61 Managing the Online Redo Log Planning the Online Redo Log Multiplex the Online Redo Log Place Online Redo Log Members on Different Disks Set the Size of Online Redo Log Members Choose an Appropriate Number of Online Redo Log Files

Slide 62: 

10/14/2010 62 Managing the Online Redo Log Creating Online Redo Log Groups and Members Creating Online Redo Log Groups ALTER DATABASE ADD LOGFILE ('log1c', 'log2c') SIZE 500K; Creating Online Redo Log Members ALTER DATABASE ADD LOGFILE MEMBER 'log2b' TO GROUP 2;

Slide 63: 

10/14/2010 63 Managing the Online Redo Log Renaming and Relocating Online Redo Log Members The files LOG1A and LOG2A on Disk A copied to the new files LOG1C and LOG2C on Disk C - ALTER DATABASE RENAME FILE 'log1a', 'log2a' TO 'log1c', 'log2c'; Dropping Online Redo Log Groups The following statement drops redo log group number 3 - ALTER DATABASE DROP LOGFILE GROUP 3;

Slide 64: 

10/14/2010 64 Managing the Online Redo Log Dropping Online Redo Log Members The following statement drops the redo log LOG3C: ALTER DATABASE DROP LOGFILE MEMBER 'log3c'; Clearing an Online Redo Log File The following statement clears the log files in redo log group number 3: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3; ;

Slide 65: 

10/14/2010 65 Managing Control Files Guidelines for Control Files Creating Control Files Troubleshooting After Creating Control Files Dropping Control Files

Slide 66: 

10/14/2010 66 Managing Control Files Guidelines for Control Files Name Control Files Multiplex Control Files on Different Disks Place Control Files Appropriately Manage the Size of Control Files

Slide 67: 

10/14/2010 67 Managing Control Files Creating Control Files Creating Initial Control Files Creating Additional Copies of the Control File, and Renaming and Relocating Control Files Creating New Control Files The control file of an Oracle database is created at the time of database creation

Slide 68: 

10/14/2010 68 Managing Control Files Creating Control Files (contd.) CREATE CONTROLFILE SET DATABASE prod LOGFILE GROUP 1 ('logfile1A', 'logfile1B') SIZE 50K, GROUP 2 ('logfile2A', 'logfile2B') SIZE 50K NORESETLOGS DATAFILE 'datafile1' SIZE 3M, 'datafile2' SIZE 5M MAXLOGFILES 50 MAXLOGMEMBERS 3 MAXDATAFILES 200 MAXINSTANCES 6 ARCHIVELOG; The following statement creates a new control file for the PROD database

Slide 69: 

10/14/2010 69 Managing Control Files Troubleshooting After Creating Control Files Checking for Missing or Extra Files (see alert log of oracle in C:\orant\RDBMS80\TRACE) Handling Errors During CREATE CONTROLFILE Oracle sends an error (ORA-01173, ORA- 01176, ORA-01177, ORA-01215, or ORA-01216) when attempting to mount and open database after creating a new control file - omitting a file – including a wrong file After issuing the CREATE CONTROLFILE statement, it may encounter some common errors

Slide 70: 

10/14/2010 70 Managing Control Files Dropping Control Files Shut down the database Edit the CONTROL_FILES parameter in the parameter file to delete the old control file's name Restart the database It can be done if location of a control file is inappropriate. The database must have at least two control files at all times.

Slide 71: 

10/14/2010 71 Managing Rollback Segments Guidelines for Managing Rollback Segments Creating Rollback Segments Specifying Storage Parameters for Rollback Segments Taking Rollback Segments Online and Offline Explicitly Assigning a Transaction to a Rollback Segment Dropping Rollback Segments Monitoring Rollback Segment Information

Slide 72: 

10/14/2010 72 Managing Rollback Segments Guidelines for Managing Rollback Segments Use Multiple Rollback Segments Specify Rollback Segments to Acquire Automatically Set Rollback Segment Sizes Appropriately Create Rollback Segments with Many Equally Sized Extents Set the Storage Location for Rollback segments

Slide 73: 

10/14/2010 73 Managing Rollback Segments Creating Rollback Segments To create rollback segments, you must have the CREATE ROLLBACK SEGMENT system privilege To create additional rollback segments for a database - - Oracle Enterprise Manager - SQL Commands CREATE PUBLIC ROLLBACK SEGMENT users_rs TABLESPACE users;

Slide 74: 

10/14/2010 74 Managing Rollback Segments Specifying Storage Parameters for Rollback Segments - Oracle Enterprise Manager - SQL Commands CREATE PUBLIC ROLLBACK SEGMENT data1_rs TABLESPACE users STORAGE ( INITIAL 50K NEXT 50K OPTIMAL 750K MINEXTENTS 15 MAXEXTENTS 100);

Slide 75: 

10/14/2010 75 Managing Rollback Segments Taking Rollback Segments Online and Offline A rollback segment is either online or offline Online is available to transactions Offline is unavailable to transactions - Oracle Enterprise Manager - SQL Commands ALTER ROLLBACK SEGMENT user_rs_2 ONLINE; ALTER ROLLBACK SEGMENT user_rs_2 OFFLINE;

Slide 76: 

10/14/2010 76 Managing Rollback Segments Explicitly Assigning a Transaction to a Rollback Segment A transaction can be explicitly assigned to a specific rollback segment using SET TRANSACTION command with the USE ROLLBACK SEGMENT parameter SET TRANSACTION USE ROLLBACK SEGMENT large_rs1;

Slide 77: 

10/14/2010 77 Managing Rollback Segments Dropping Rollback Segments You can drop rollback segments when the extents of a segment become too fragmented on disk, or the segment needs to be relocated in a different tablespace. DROP PUBLIC ROLLBACK SEGMENT data1_rs;

Slide 78: 

10/14/2010 78 Managing Rollback Segments Monitoring Rollback Segment Information - Monitoring through Oracle Enterprise Manager - Monitoring through SQL commands SELECT * FROM USER_SEGMENTS; SELECT * FROM DBA_SEGMENTS;

Slide 79: 

10/14/2010 79 Managing Tables Guidelines for Managing Tables Creating Tables Altering Tables Dropping Tables

Slide 80: 

10/14/2010 80 Managing Tables Design Tables Before Creation Specify the Location of Each Table Estimate Table Size and Set Storage Parameters Guidelines for Managing Tables

Slide 81: 

10/14/2010 81 Creating Tables Managing Tables The SQL statement CREATE TABLE is used to create a table in a schema. The following statement creates a table emp: CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, ename VARCHAR2(15) NOT NULL, job VARCHAR2(10), mgr NUMBER(5), hiredate DATE DEFAULT (sysdate), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(3) NOT NULL CONSTRAINT dept_fkey REFERENCES dept) TABLESPACE users STORAGE ( INITIAL 50K NEXT 50K MAXEXTENTS 10);

Slide 82: 

10/14/2010 82 Altering Tables Managing Tables A table can be altered using SQL statement ALTER TABLE. A table in an Oracle database can be altered for the following reasons: a. To add one or more new columns to the table b. To add one or more integrity constraints to a table c. To modify an existing column's definition d. To drop integrity constraints associated with the table The following statement alters the EMP table: ALTER TABLE emp modify (ename varchar2(25));

Slide 83: 

10/14/2010 83 Dropping Tables Managing Tables A table can be dropped using SQL statement DROP TABLE. The following statement drops the EMP table: DROP TABLE emp;