Oracle Database Overview

Oracle Database Overview Hong Lee

Relational Database Management System (RDBMS) RDBMS is based on the relational model as introduced by E. F. Codd . D ata and the relationships are stored in the form of tables. Referential integrity between tables is often enforced by the combination of a primary key and a foreign key. RDBMS offers strong consistency guarantees, it uses t ransactions that provide "all-or-nothing", meaning each unit of work performed in a database must either complete entirely or have no effect at all. RDBMS provides strong concurrency control. Its concurrency mechanisms manage and support multiple users accessing the same group of resources (such as tables, rows). RDBMS interface is flexible and offers data independence. Uses interface with RDBMS in SQL – a standard interactive and programming language for querying and modifying data and managing databases. Such RDBMS include Microsoft SQL Server, DB2, Sybase and Oracle .

What is an Instance? A database instance , or an ‘instance’ is made up of the background processes/threads needed by the database software. These processes usually include a process monitor, session monitor, lock monitor, database writer, etc. They will vary from database vendor to database vendor. An instance controls 0 or more databases A database can have 1 or more instances

What is a Schema? A SCHEMA IS NOT A DATABASE, AND A DATABASE IS NOT A SCHEMA. A database application schema is the set of database objects (tables, indexes, triggers, etc.) that owned by a user that apply to a specific application. These objects are relational in nature, and are related to each other, within a database to serve a specific functionality. For example payroll, purchasing, order, etc. Usually several schemas coexist in a database.

Tables, Indexes, Constraints Table - a set of columns that contain data. In the old days, a table was called a file. Row - a set of columns from a table reflecting a record. Index - an object that allows for fast retrieval of table rows. Every primary key and foreign key should have an index for retrieval speed. Primary key (PK) - 1 or more columns in a table that makes a record unique. Foreign key (FK) - a common column between 2 tables that define the relationship between those 2 tables.

The Three Normal Forms First Normal Form: All column values are atomic - Indivisible

The Three Normal Forms Second Normal Form: All column values depends on the value of the primary key

The Three Normal Forms Third Normal Form: No column value depends on the value of any other column except the primary key.

DBA Tasks System DBA Installing and upgrading the database software and application tools Allocating system storage and planning future storage requirements for the database system Creating database and its parameters Creating database storage structures (tablespaces) Creating database objects (tables, views, indexes) Create users and grant database privileges Setup database security and auditing Setup database backup Recover database when necessary Setup database monitoring Database optimization and performance tuning Perform database maintenance (reorgs, collect stats) Design and Implement database high availability strategy Contact database vendor for technical support Ensuring compliance with the database license agreement

DBA Tasks Application DBA Reviewing database designs and application functionalities with the business, developers, and data modelers Reviewing database structure changes that provided by application developers and data modelers Implement the database structure such as tables, indexes Working with application developers to tune the performance of the database Reviewing and tuning the SQL scripts Designing and implementing the database migration Helping load/unload data Database refreshing and cloning Data replications

Oracle Instance and Database Oracle Instance Oracle background processes: SMON – System Monitoring PMON – Process Monitoring CKPT – Checkpoint process ARC0 – Archive log process LGWR – Log writer … Memory SGA – System Global Area PGA – Program(Private) Global Area Oracle Database Data files Control files Redo log files Archive log files Parameter files

A Simple View of Oracle Instance and Database Database Files Control Files Redo Log Files Archive Log Files Oracle Instance Shared_pool Database Buffer Cache Redo Log Buffer System Global Area (SGA) Oracle Processes Oracle Database

Database Data Cache Redo Log Buffer

How do I connect to the Oracle database tnsnames.ora sqlnet.ora listener.ora tnsnames.ora sqlnet.ora 1 2 3 4

What is a Tablespace? A tablespace is a logical storage unit in an Oracle database. users01.dbf users02.dbf

Database Space Management Add a new tablespace SYS1.dbf SYS2.dbf USER01.dbf CREATE TABLESPACE USERS DATAFILE ‘USERS01.dbf’

Database Space Management Add datafiles to a tablespace SYS1.dbf SYS2.dbf SYS3.dbf ALTER TABLESPACE SYSTEM ADD DATAFILE ‘SYS2.dbf’ ALTER TABLESPACE SYSTEM ADD DATAFILE ‘SYS3.dbf’

Database Space Management Dynamically sizing a datafile ALTER DATABASE DATAFILE ‘USER01.dbf’ AUTOEXTEND ON NEXT 20M MAXSIZE 1000M SYS1.dbf SYS2.dbf USER01.dbf

Automatic Storage Management (ASM) ASM is a database instance that provides: • Load balancing in parallel across disk drives • Prevention of disk space fragmentation • Online disk space reorganization • Data redundancy to provide fault tolerance ASM instance - Instance that manages the diskgroup metadata Disk Groups Logical grouping of raw disks Determines file mirroring options ASM Disks - LUN presented to ASM ASM Files - Includes database files

Volume Management vs ASM Direct I/O Asynchronous I/O Striping and Mirroring No File System Buffers

Install Software and Create Database Kernel settings: # Shmmax set to half of the physical mem. kernel.shmmax = 4294967296 kernel.shmall = 2097152 kernel.shmmni = 4096 fs.file-max = 327679 kernel.sem=250 32000 100 128 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default=262144 net.core.wmem_default=262144 net.core.rmem_max=262144 net.core.wmem_max=262144 OS Patches Run “root.sh” Oracle id: (ora1adm, ora5adm, ora9adm) Directories: /u01 – Oracle software /u02 ~ /uxx – Oracle database X-window is required

Oracle Backup and Recovery SMON – System Monitor Background process that automatic recover the database after a crash Export/Import Fast unload/load data and structure to a dump file Can only recover to when the export was taken Import will be slow for large database Best suited for Dev/Test, refreshing Data Pump Newer version of export and import Parallelism Can stop and restart RMAN (Recover Manager) Oracle backup and recover tool (language) Able to recover database to point-in-time Integrated with many media management tools Flashback recovery New technology that use Flashback Area and Undo space to undo the changes Best suited for logical data corruption (user error)

Database Monitoring What: Database up and down Database alert log error Database directory usage (archive log, software) Database backup status Tablespace space usage Database listener up and down How: K-shell scripting integrated with NSM Oracle Enterprise Manager (OEM)/Grid Control

Oracle Enterprise Manager (OEM) / Grid Control Grid control console Management server (OMS) Management repository RAC Database Managed targets Agent Agent Agent Database Database Agent

OEM/Grid Control – Server Performance

OEM/Grid Control – Database Performance

OEM/Grid Control – Top Activity

OEM/Grid Control – SQL Tuning

OEM/Grid Control – Memory Advice

High Availability – Standby Database

High Availability – Standby Database Failover

High Availability – Real Application Cluster

High Availability – Real Application Cluster Node n Node 1

High Availability – Real Application Cluster RAC

A Q & Q U E S T I O N S A N S W E R S

Oracle Database Overview

Recommended

More Related Content

What's hot (20)

Viewers also liked (7)

Similar to Oracle Database Overview (20)

Recently uploaded (20)

Oracle Database Overview