intro to oracle

8/11/2019 Intro to Oracle

1/91

Introduction to Oracle

Prepared by: Shayne Capo

Senior Database AdministratorOpera Global Technical Services

Opera Global Technical Services


2/91


Oracle

Architectural

Components


3/91


Architectural Components

There are several files, processes and memorystructures in an Oracle Server. Some of them areused when processing a SQL statement (ormanipulating an application like Opera).

Others are used to improve the performance of the

database, ensure that the database can be recoveredin the event of a software or hardware error, orperform other tasks necessary to maintain thedatabase.

The Oracle server consists of:

Oracle InstanceOracle database


4/91


Oracle Server


5/91


Oracle instance

A combination of background processes and memorystructures that access an Oracle database

Instance must be started before accessing thedatabase

Every time started, a System Global Area (SGA) is

allocated and Oracle background processes arestarted

Background processes perform functions on behalf ofthe invoking process

They consolidate functions that would otherwise behandled by multiple Oracle programs running foreach user

Always opens one and only one database


6/91


Oracle Instance


7/91


Oracle database service


8/91


Oracle Database

The physical structure consists of 3 file types + otherfile structures


9/91



10/91


Overview of Primary Components

System Global Area (SGA) Shared Pool

Database Buffer Cache

Redo Log Buffer Cache

Large Pool Background Processes

Program Global Area (PGA)


11/91


System Global Area (SGA)

The SGA consists of several memory structuresShared pool

Database buffer cache

Redo log buffer

Other structures (e.g. locklatch management,statistical data)

There are two optional memory structures that can

be configured within the SGALarge Pool

Java pool


12/91


13/91


14/91


Large Pool

Optional area of memory in the SGA configured onlyin a shared server environment

LARGE_POOL_SIZE

Services the parsing requirements for Javacommands

Required when installing and using Java (the Opera

application does) JAVA_POOL_SIZE

Java Pool


15/91


Redo Log Buffer Cache

Records all changes made to the database data blocks Primary purpose is recovery Changes recorded within are called redo entries Redo entries contain information to reconstruct or redo changes

In Oracle Database 10g, Automatic Shared MemoryManagement (ASMM) feature is introduced to automaticallydetermine the size of Database buffer cache (default pool),

Shared pool, Large pool and Java pool by setting the parameterSGA_TARGET.

SGA_TARGET


16/91


Overview of Primary Components


17/91


Background Processes

The relationship between the physical andmemory structures is maintained andenforced by Oracles background processes

Mandatory processes:

DBWn PMON CKPTLGWR SMONOptional processes:

ARCn Dnnn SnnnPnnn LCKn QMNnLMON LMDn RECO


18/91


Database Writer (DBWn)

DBWn writes when: Checkpoint occurs Dirty buffers reach

threshold There are no more free

buffers Tablespace OFFLINE,

READ ONLY, BEGINBACKUP

Table DROP, TRUNCATE Timeout


19/91


Log Writer (LGWR)

LGWR writes:

At Commit

When 1/3 of the LogBuffer is full

Before DBWn writes tothe datafiles

When 1MB of redo isgenerated

Every 3 seconds


20/91


System Monitor (SMON)

Responsible for:

Instance recovery

Rolling forward

Opening the database

Rolling back

Coalescing free space

Deallocating temporary

segments


21/91


22/91


Checkpoint (CKPT)

Responsible for: Signaling DBWn at

checkpoints

Updating datafile

headers Updating control files

Reducing time torecover

Ensure committed datais written to disk


23/91


Archiver (ARCn)

Responsible for:

Optional backgroundprocess

Automatically archives

online redo logs whenrunning in ARCHIVELOGmode


24/91


25/91


Oracle

Storage

Structures


26/91


Physical vs. Logical


27/91


Datafiles

A datafile is a physical structure Can hold data for only one tablespace

Can resize dynamically

Space allocated upon creation

Opera database datafiles:System01.dbf, sysaux01.dbf, tempseg01.dbf,

undotbs01.dbf, findata01.dbf, finindx01.dbf,logdata01.dbf, logindx01.dbf, namedata01.dbf,nameindx01.dbf, opera_data01.dbf,opera_indx01.dbf, oxi_data01.dbf, oxi_indx01.dbf,quickdata01.dbf, quickindx01.dbf, ratedata01.dbf,rateindx01.dbf, resvdata01.dbf, resvindx01.dbf,tools01.dbf


28/91


Tablespaces

Largest logical unit

Can reside in one or more datafiles

May contain one or more segments

Can be taken offline

Can be made readonly (SYSREAD)

OPERA database tablespaces:

system, sysaux, tempseg, undotbs, findata,

finindx, logdata, logindx, namedata, nameindx,opera_data, opera_indx, oxi_data, oxi_indx,quickdata, quickindx, ratedata, rateindx, resvdata,resvindx, tools


29/91


Logical Overview

SEGMENTS Second largest logical unit Can belong to only one tablespace, but can reside in

multiple datafiles Is made up of one or more extents

EXTENTS Third largest logical unit Can belong to only one segment and cannot spawn datafiles Is made up of contiguous Oracle BlocksWhen segments grow, new extents are added

BLOCKS Smallest logical unit Can belong to only one extent Corresponds to one or more operating system blocks DB_BLOCK_SIZE=8m in OPERA


30/91


Create

Database


31/91


Create Database CommandCREATE DATABASE "opera"MAXINSTANCES 32MAXLOGHISTORY 1

MAXLOGFILES 192MAXLOGMEMBERS 3MAXDATAFILES 1024DATAFILE 'd:\oracle\oradata\OPERA\system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10240K MAXSIZE

2048M EXTENT MANAGEMENT LOCALSYSAUX DATAFILE 'd:\oracle\oradata\OPERA\sysaux01.dbf' SIZE 120M AUTOEXTEND ON NEXT 10240K

MAXSIZE 2048MDEFAULT TEMPORARY TABLESPACE TEMPSEG TEMPFILE 'd:\oracle\oradata\OPERA\tempseg01.dbf' SIZE 20M

reuse AUTOEXTEND ON NEXT 640K MAXSIZE 2048M

UNDO TABLESPACE "UNDOTBS1" DATAFILE 'd:\oracle\oradata\OPERA\undotbs01.dbf' SIZE 200M reuseAUTOEXTEND ON NEXT 5120K MAXSIZE 2048MCHARACTER SET UTF8NATIONAL CHARACTER SET UTF8LOGFILEGROUP 1 ('d:\oracle\oradata\OPERA\redo01a.log','d:\oracle\oradata\OPERA\redo01b.log') SIZE 102400K,GROUP 2 ('d:\oracle\oradata\OPERA\redo02a.log','d:\oracle\oradata\OPERA\redo02b.log') SIZE 102400K,GROUP 3 ('d:\oracle\oradata\OPERA\redo03a.log','d:\oracle\oradata\OPERA\redo03b.log') SIZE 102400K,GROUP 4 ('d:\oracle\oradata\OPERA\redo04a.log','d:\oracle\oradata\OPERA\redo04b.log') SIZE 102400K,

GROUP 5 ('d:\oracle\oradata\OPERA\redo05a.log','d:\oracle\oradata\OPERA\redo05b.log') SIZE 102400K,GROUP 6 ('d:\oracle\oradata\OPERA\redo06a.log','d:\oracle\oradata\OPERA\redo06b.log') SIZE 102400K,GROUP 7 ('d:\oracle\oradata\OPERA\redo07a.log','d:\oracle\oradata\OPERA\redo07b.log') SIZE 102400K,GROUP 8 ('d:\oracle\oradata\OPERA\redo08a.log','d:\oracle\oradata\OPERA\redo08b.log') SIZE 102400K,GROUP 9 ('d:\oracle\oradata\OPERA\redo09a.log','d:\oracle\oradata\OPERA\redo09b.log') SIZE 102400K,GROUP 10 ('d:\oracle\oradata\OPERA\redo10a.log','d:\oracle\oradata\OPERA\redo10b.log') SIZE 102400K;


32/91


Data

Dictionary


33/91


Data Dictionary

Central to the database Describes the database and all its objects

Set of READ ONLY tables and views

Maintained by the Oracle Server

Stored in the SYSTEM tablespace

Owned by SYS

Modified by DDL statements


34/91


Data Dictionary

Provides information about:Logical and physical structures

Definitions and space allocations of objects

Integrity constraints

Users, Roles and Privileges

Auditing information


35/91


36/91


Data Dictionary Views

Three sets of views, each with a different scope

DBA, ALL, USER views can be accessed only whenthe database is up and running

Examples are: xxx_tables

xxx_users

xxx_database


37/91


Dynamic Views

Virtual tables that gather current database activityfrom the last database startup database in mounted state Information accessed from memory and control files Synonyms begin with V$ Listed in V$FIXED_TABLE Examples are:V$SESSIONV$INSTANCE

V$SGAV$VERSION


38/91


Control

File


39/91


Control File

Small physical binary fileAutomatically created at

create database

Maintains integrity of

database Continuously updated

Contains databaseinformation

Belongs to only onedatabase


40/91


Control File

A control file contains:

Database name

Tablespace names

Name and location of datafiles and redo log files

Current redo log sequence number

Checkpoint information

Begin and end of undo segment

Redo log archive information

Backup information (RMAN only)


41/91


Control Files

initOPERA.ora

#################################File Configuration

################################control_files=("d:\oracle\oradata\opera\control01.ctl","g:\oracle\oradata\opera\control02.ctl")


42/91


Create Controlfile SyntaxCREATE CONTROLFILE REUSE DATABASE "OPERA" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 32MAXLOGMEMBERS 3MAXDATAFILES 200MAXINSTANCES 8MAXLOGHISTORY 292

LOGFILEGROUP 1 ('B:\ORACLE\ORADATA\OPERA\REDO01A.RDO', 'D:\ORACLE\ORADATA\OPERA\REDO01B.RDO' ) SIZE 100M,

.

.

GROUP 10 ('B:\ORACLE\ORADATA\OPERA\REDO10A.RDO', 'D:\ORACLE\ORADATA\OPERA\REDO10B.RDO' ) SIZE 100M-- STANDBY LOGFILEDATAFILE'B:\ORACLE\ORADATA\OPERA\SYSTEM01.DBF','B:\ORACLE\ORADATA\OPERA\UNDOTBS01.DBF','B:\ORACLE\ORADATA\OPERA\SYSAUX01.DBF','B:\ORACLE\ORADATA\OPERA\FINDATA01.DBF','B:\ORACLE\ORADATA\OPERA\FINDATA02.DBF',

'B:\ORACLE\ORADATA\OPERA\FININDX01.DBF','B:\ORACLE\ORADATA\OPERA\FININDX02.DBF',..

'B:\ORACLE\ORADATA\OPERA\OPERA_DATA08.DBF','B:\ORACLE\ORADATA\OPERA\LOGDATA04.DBF'CHARACTER SET UTF8;


43/91


Control File

Information about the control file can be obtained byquerying the following:

V$CONTROLFILE

V$CONTROLFILE_RECORD_SECTION


44/91


Redo Logs


45/91


Redo Logs

Record all changes made to data Provides a means to recover the database

Can be organized into groups (multiplexing)

Minimum of two groups required


46/91


How Redo Logs Work

Written in a cyclic fashionWhen one group is full LGWR moves to the next

group

Log switch and checkpoint occurs


47/91


Redo Log Information

Information about the redo logs can be obtained byquerying the following:

V$LOG

V$LOGFILE

V$LOGHIST

V$BACKUP_REDOLOG


48/91


Archived Redo Logs

Filled online redo logs can be archived The two advantages are:

A backup of the datafiles + redo logs + archivelogs can be used to restore the database up to the

last committed transaction.The backup can be made online.

When running in ARCHIVELOG mode a redo log filecannot be overwritten until

Checkpoint has completed

Redo Log has been archived

By default the database is created in NOARCHIVELOGmode


49/91


50/91


Enable Archiving

Set initOPERA.ora archiving parametersMount the database in exclusive mode

D:\>sqlplus sys/opera10g as sysdba

SQL>Startup mount exclusive

Set the database in ARCHIVELOG mode

SQL>ALTER DATABASE ARCHIVELOG;

Startup the databaseSQL>ALTER DATABASE OPEN;


51/91


InstanceManagement


52/91


53/91


Initialization Files

Instance specific Instance parameters set explicit or implicit(default)

Database name

Memory allocation

Control file names

Undo Segment settings

pfile and spfile

Comment out parameters #

IFILE points to location of pfile

initOPERA.ora


54/91


Database States

Startup / Open (Database open) Shutdown (Database closed)

Nomount (Instance started)

Mount (Control files opened)


55/91


56/91


Alter Database Startup

ALTER DATABASE command:

ALTER DATABASE {MOUNT | OPEN}

ALTER DATABASE OPEN [READ WRITE | READ ONLY]

READ WRITE: (default) enables normal access

READ ONLY: no redo log will be generated

Database required to be in nomount or mount mode.


57/91


Shutdown Command

Shutdown command:

SHUTDOWN [NORMAL | TRANSACTIONAL |

IMMEDIATE | ABORT]


58/91


Monitor the Instance

alertSID.log file:Records results of major events

Very useful for diagnosing database errors

alertOPERA.log

Each entry has a timestamp

Located in BACKGROUND_DUMP_DEST

Trace files in USER_DUMP_DEST


59/91


60/91


Tablespaces

AndDatafiles


61/91


Tablespaces and Datafiles

All objects in the database are stored logically inTABLESPACES and physically in DATAFILES

TABLESPACES:- Belong to only one Database- Consist of one or more Datafiles- Are divided in Segments, Extents and Blocks

DATAFILES- Belong to only one Tablespace

- Physical files on the operating system

f bl


62/91


Types of Tablespaces

SYSTEMCreated in create database

Contains the data dictionary

Contains the System Undo Segment

NON-SYSTEM

Eases space administration

Separate segments

Can aid in controlling user space quotas


63/91


Space Management

Locally managed tablespace:Free extents managed in the tablespace

A bitmap records free extents

Dictionary-managed tablespace:

Free extents managed in the data dictionary

Extent allocation or deallocation triggers a DMLstatement on the data dictionary

L ll M d T bl


64/91


Locally Managed Tablespaces

Reduced contention on the data dictionary No undo data generation when space allocation or

deallocation occurs No coalescing required UNIFORM sized extents are more reusable

CREATE TABLESPACE opera_data DATAFILEg:\oracle\oradata\opera\opera_data01.dbf SIZE512M AUTOEXTEND ON NEXT 100M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M

SEGMENT SPACE MANAGEMENT AUTO;


65/91


Dictionary Managed Tablespaces

Extents managed in the data dictionary Each segment may have a different storage clause

Coalescing required

CREATE TABLESPACE opera_data DATAFILEg:\oracle\oradata\opera\opera_data01.dbf SIZE512M EXTENT MANAGEMENT DICTIONARY DEFAULTSTORAGE (initial 1M NEXT 1M PCTINCREASE 0);

U d T bl


66/91


Undo Tablespace

Store undo segments Cannot store any other objects

Extents are locally managed

CREATE UNDO TABLESPACE undotbs DATAFILEg:\oracle\oradata\opera\undotbs01.dbf SIZE 512M;

T T bl


67/91


Temporary Tablespace

Used for sort operations

Cannot store any permanent objects Locally managed extents Tempfiles are always NOLOGGING

CREATE TEMPORARY TABLESPACE tempseg TEMPFILEg:\oracle\oradata\opera\temp01.dbf SIZE 512MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

Default temporary tablespace define at database creation. Can change the default temporary tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACEtempseg;

Cannot be taken offline or dropped until a new temporarytablespace is available

R d O l T bl


68/91


Read Only Tablespace

The following command makes a tablespace readonly:

ALTER TABLESPACE sys_read READ ONLY;

This causes a checkpointData within the tablespace is available only for

SelectsObjects can be dropped

SYS_READ tablespace

R i i T bl


69/91


Resizing Tablespaces

ALTER DATABASE ALTER TABLESPACE

Can change the size of a tablespace by:

Alter the size of the datafile

Alter the datafile to have AUTOEXTEND turned on

Add a datafile

ALTER DATABASE DATAFILEg:\oracle\oradata\opera\opera_data01.dbf RESIZE 4096m;

ALTER DATABASE DATAFILE

g:\oracle\oradata\opera\opera_data01.dbf AUTOEXTEND ONNEXT 100m MAXSIZE 4096m;

ALTER TABLESPACE opera_data ADD DATAFILEg:\oracle\oradata\opera\opera_data02.dbf size 2048m;

M i R i D t fil


70/91


Moving or Renaming Datafiles

Shutdown the database Physically on the OS move the datafile

Startup mount the database

Execute: ALTER DATABASE RENAME FILE

g:\oracle\oradata\opera\opera_data01.dbf TOh:\oracle\oradata\opera_data01.dbf;

Open the database

T bl I f ti


71/91


Tablespace Information

Information about tablespaces and datafiles can beobtained in the following views: TablespacesDBA_TABLESPACESV$TABLESPACE

DatafilesDBA_DATA_FILESV$DATAFILE

Temporary files

DBA_TEMP_FILESV$TEMPFILE


72/91


32bit and 64bitArchitectures

32bit A hit t


73/91


32bit Architecture

Windows Server 2003

32-bit x86 systems

Mainstream deployments where 64bit applications ordrivers are unavailable

Most common on servers with 1-4 processors

64bit A hit t


74/91


64bit Architecture

Windows Server 2003 x64 EditionsGradually replacing 32bit windows as mainstream

offering

Can combine 32bit and 64bit software

Windows Server 2003 for Itanium-based Systems

Intended for the most demanding database andlarge deployments on 8-way+

Designed for pure 64bit software stacks

32bit s 64bit Memo Limits


75/91


32bit vs. 64bit Memory Limits

Memory Model


76/91


Memory Model

Process Memory restrictions32bit 4GB (Total user & kernel)

64bit 8TB

32bit Windows

All instances memory requirements must fit in the4GB limit

64bit Windows

A process has 8TB to play with


77/91


4GB RAM Tuning


78/91


4GB RAM Tuning

Increase addressable memory available to the Oracleprocess by adding the /3GB switch to the boot.ini file:

Reboot server to enable

Whats wrong with 32bit?


79/91


Whats wrong with 32bit?

Nothing..but 32bits = 4GBper process

Use /3GB switch in boot.ini

Doing this restricts the OS

non-paged pool Certain OS pools get cut in

half

Why 64bit?


80/91


Why 64bit?

Each threaddedicated user connection

All Oracle memory has to come out of the addressspace

Options on Windows

Windows ItaniumFirst 64bit database onWindows

Windows x64EM64T/Opteroncan recompile32bit to 64bit


81/91



82/91


OPERAInformation

OPERA Commands


83/91


OPERA Commands

sqlplus opera/opera Select * from installed_app;

select license_code from installed_app_licenses;

ALTER USER user_name IDENTIFIED BY

new_password; DROP USER user_name [CASCADE];

OPERA File Locations


84/91


OPERA File Locations

alertOPERA.logG:\oracle\admin\opera\bdump

Arvhive logsD:\oracle\admin\opera\archive

Control FilesD:\oracle\oradata\opera\control01.ctlG:\oracle\oradata\opera\control02.ctl

OPERA datafiles:\oracle\oradata\opera\.dbf


85/91


86/91


Daylight

SavingsTime

DST Changes


87/91


DST Changes

The Energy Policy Act of 2005 was signed into law inAugust 2005 to extend daylight saving time.

Beginning in 2007, daylight saving time in the U.S. willbegin on the second Sunday in March and end the firstSunday in November rather than beginning on the first

Sunday in April and ending the last Sunday in October, asit did in the past.

Under the new rules for 2007, DST will start on March 11,2007 end on November 04, 2007. As a result the database

may report incorrect time zone data between 03/11/200704/01/2007 and between 10/28/200711/04/2007 (andon different dates in subsequent years), unless therequired patches are applied.

What is the database timezone?


88/91


What is the database timezone?

The database time zone is not as important as it sounds. First ofall it does not influence functions like sysdate, or systimestamp.These function take their contents (date and time, and in thecase of systimestamp also time zone) completely from the OSwithout any "Oracle" intervention.

The only function of the database time zone is that it functionsas a time zone in which the values of the "TIMESTAMP WITHLOCAL TIME ZONE" (TSLTZ) datatype are normalized when theyare stored in the database.

However, these values are always converted into the sessiontime zone on insert and retrieval, so the actual setting of thedatabase time zone is more or less immaterial.

DST Patch


89/91


DST Patch

Who needs the DST patch?ONLY locations where the database is not in the

same time zone as the hotel would beaffected. Mostly larger sites will be impacted, like

Candlewood datacenter. All sites just need tomake sure they have the OS patches/updatesapplied.

The patch is applied to the database. It replaces twofiles and requires the database to be restarted.

The patch is included in the Micros 403 database CD.


90/91


91/91

Thank You

intro to oracle

Documents