Online Training on Oracle Database and Storage Structure

Information about Online Training on Oracle Database and Storage Structure

Published on July 26, 2014

Author: intellipaat

Source: authorstream.com

Content

What is Oracle Database: What is Oracle Database Oracle Storage Structure Intellipaat Software Solutions Pvt. Ltd. 1 DATABASE: DATABASE Why we Need a Database To Collect, Process and Distribute the Data. I can do that using Excel, Notepad a simple C,VB code etc. Why Database ? Consider the Challenges of the Above approach. DBA’s Perspective (Developers have many more reasons) Manage 100’s of files. Manage 100’s of users. Just need to see One Line/record from one of the file. Changes to the Data and propagation of the Changes. Files getting Lost, Corruption due to Software, Hardware Issues. Backup & Restore. 2 Intellipaat Software Solutions Pvt. Ltd. How Oracle DB Does IT: How Oracle DB Does IT Data File 1 Data File 2 Data file 3 Roll Back Segment Redo Log 1_1,1_2 Redo Log 2_1,2_2 Redo Log 3_1,3_2 Archive Logs 1,2,3,4,5………. Control File 1 2 3 Shared Pool DataBase Buffer Cache Redo Buffer Oracle Process Oracle Process Oracle Process Locks Init _{SID}. ora spfile Users Background Processes 3 Intellipaat Software Solutions Pvt. Ltd. A Typical Unix Kernel: A Typical Unix Kernel Hardware Applications System Libraries (libc) System Call Interface Architecture-Dependent Code I/O Related Process Related Scheduler Memory Management IPC File Systems Networking Device Drivers Modules 4 Intellipaat Software Solutions Pvt. Ltd. Database: Database Physical Parameter file Control files Data files Temp files Redo Log files Archive Log files Alert & Trace Files Backup files Logical Tablespace Schema Segments Extents Oracle Data blocks 5 Intellipaat Software Solutions Pvt. Ltd. Oracle Storage Structure: Oracle Storage Structure Schema_1 TableSpace_1 Ts1_df_1 Ts1_df_2 Ts1_df_3 TableSpace_2 Ts2_df_4 Ts2_df_5 Segment_1 Segment_2 Segment_3 Table_1 Extent_1 Block Block Block Block Block Block Block Block Extent_2 Block Block Block Extent_3 Block Block Block Block Free Block Free Block Free Block Free Block Free Block Free Block Free Block Free Block Table_2 Table_3 Table_4 Table_5 6 Intellipaat Software Solutions Pvt. Ltd. A Simple Create Table Statement: A Simple Create Table Statement CREATE TABLESPACE TableSpace_1 DATAFILE '\ oraserv \ORADATA\ Ts1_df_1.dbf ' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M; Alter tablespace T ableSpace_1 add datafile '\ oraserv \ORADATA\ Ts1_df_2.dbf ‘ Size 10GB; Alter Database Datafile '\ oraserv \ORADATA\ Ts1_df_2.dbf ‘ resize 20GB; CREATE TABLE Schema_1.Table_1 ( Col_1 VARCHAR2(100), Col_2 NUMBER , col_3 DATE ) TABLESPACE TableSpace_1 STORAGE ( INITIAL 16K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ) 7 Intellipaat Software Solutions Pvt. Ltd. Schemas and Schema Objects: Schemas and Schema Objects Collection of database objects Tables Views Sequences Synonyms Indexes Procedures Functions Packages Triggers 8 Intellipaat Software Solutions Pvt. Ltd. Data Blocks: Data Blocks The smallest unit of Input/Output used by Oracle database. The size of data block for any database is fixed at the time of creation of the database; Some values of the data block size are 2KB, 8KB, 16KB, and 32KB. Oracle recommends a size of 8KB 9 Intellipaat Software Solutions Pvt. Ltd. Extents: Extents The next level of data storage. One extent consists of a specific number of data blocks One or more extents in turn make up a segment. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment. 10 Intellipaat Software Solutions Pvt. Ltd. Segment: Segment A segment consists of a set of extents Each table’s data is stored in its own single segment. Each index’s data is stored in a single segment. More extents are automatically allocated by Oracle to a segment if its existing extents become full. The different types of segments are the data segments, index segments,rollback segments, and temporary segments. 11 Intellipaat Software Solutions Pvt. Ltd. Control Files: Control Files Contain a list of all other files in the database Key information such as Name of the database Date created Current state Backups performed Time period covered by redo files 12 Intellipaat Software Solutions Pvt. Ltd. Redo Log Files: Store a recording of changes made to the database as a result of transactions and internal Oracle Activities When Oracle fills one redo log, it automatically fills a second. Used for database recovery Redo Log Files 13 Intellipaat Software Solutions Pvt. Ltd.

Related presentations