Administration Oracle Master Finish
Administration Oracle Master Finish
Administration Oracle Master Finish
Didier LESTRAT
Independent Information System Consultant
The principles
u Architecture stand alone
u Architecture 2 tiers
u Architecture 3 tiers
u Architecture n tiers
The application server must provide flexibility for data development and
production.
Oracle Administration 11G R2 - Didier LESTRAT 4
Architecture of an Enterprise Information System
Architecture 2-tiers
The two-tier architecture consists of several clients (100 user)
and one server.
Logic Architecture
Client LAYER
Presentation layer
Application Layer
Data Layer
u Physical independence
Be able to modify storage structures or indexes without affecting
the application
Disks, access methods, placement modes, data encoding are not
apparent
Logical independence
Allow different applications to have different views of the same
data
Allow the DBA to modify the logic scheme without affecting the
application level
Relational model
Market DBMS
PostgreSql
u Différent motors
u MyIsam :
u InnoDB :
Works with olaris, Linux, SunOS, Windows
Administration tool
u MySql administrator
u MySQ query browser
u Mysql Migration toollkit
u Mysql cluster
Sql server
u Architecture similar Oracle
u A lot of demand since the implementation of collaborative tools
(SHAREPOINT)
Outils d’administration
u SQL Server Management Studio
u SQL Server Database Tuning Wizard.
Very stable
Edition 15.5
Administration tools
u Adaptive server Entreprise
u Sql Anywhere
Oracle
Administration tools
u Oracle entreprise manager
u Oracle net Manager
u Oracle installer
Trades
u DBA
u Administrator
u Security manager
u Network and System Administrator
u Application Developer (MCO MOE TMA)
u Business users: edit data, create reports
u Installation of products
u A server, client applications
u Oracle Network Components
u Creating / starting / stopping databases
u Management of storage structures
u Management of non-business users (and their rights)
u Backup / Restore.
u Export Import
u Creating operating script
u Optimization and tuning of the bases
Database administrator
u Operating Documentation
u Current administration: Monitoring of operating processes,
performance, flow fluidity, availability
u AMOE on the architecture of the bases
u In general, the DBA is in the operating group
Production with the systems and networks team.
It is very rarely in MOE except in large Structures.
Concept
Two possible architectures:
u Client / Server: Client Applications Send SQL or Pl / SQL Queries to a
Server
DBMS Structure
After starting an instance, the Oracle Software associates the instance with a specific database
Database mounted, opened, accessible to autthorized uers.
A multiple instance can execute on the same computer, each accessing its own physical Database
u Shared by all users
u The SGA is allocated at the start of the instance and released when the instance is stopped.
u It is sized by a set of parameters defined in the parameter file.
u The maximum size of the SGA is limited by the SGA_MAX_SIZE parameter
Quand vous lancez SQL plus ou OEM, la SGA alloue une espace dédié et unique. Paramètres SGA : SGA_MAX_SIZE
Structure da la mémoire
SGA (System Global Area)
La SGA is the memory area that contains data control information for the
instance
1. Database Buffer Cache : Caches block of Data retrieved from the database
2. Redo Log Buffer : Cache redo informations until it can be written to the
physical redo lod file files stored on the disk
3. Shared Pool : Caches various cosntructs that can be shared among users
4. Large Pool : Optional area tht provides large memory allocations for
certain large processes, such as Oracle backup and recovery operations,
and I/O server processes
5. Java Pool : Memory area used for all session specific Java code and Data
in the Jvava Virtual Machine (JVM)
The database buffer is the portion of SGA that holds copies of data blocks that
are read from data files.
All uers who arre concurrently connected to the instance share access to the
dtabase buffer cache.
The firts time an Oracle Dtabase user process requires a particular piece of data,
its searches for the data in the dtabase buffer cache. If the process finds the
data already in the cache. It can reda the data directly from memory (more
speed)
If the process cannot find the data in the buffre cache; it muts copy the dta
block from a dta file into the buffer
Used Parameters :
u DB_CACHE_SIZE: cache size for block size
u DB_nK_CACHE_SIZE: size of blocks of nKo (values of n 2, 4, 8, 16, 32)
!T the redo log buffer is really important, it's the heart of the database, it handles
transactions.
In case of a server failure, it is in this memory space that we will recover the
transcations
Large Pool
Memory regions that contains data and control information for a server or
background process.
A PGA is nonshared memeory created by Oracle Dtabase when a server or
background process is started.
Each server process and background process has its own PGA.
Process Architecture
Database processes :
u Server process : connects to the Oracle instance and is started when a
user establishes a session
Process structures
Writes :
u When a user porcess commits a transaction
u When the redo log is one-third full
u Before a DBWn process writes modified buffers to disk
u Every 3 seconds : automatic
u One instance.
u Name of the data base
u The paths to the database control files
u Trace files : Lists all processing errors, important for the DBA.
Tablespaces
u A database is divided into logical storage units called Tablespaces.
u You may have a tablespace for the data et a another for indexes.
Logical Structure
Le Segment : the level of logical database storage above
an extent is called a segment
This is the space used by a logical structure: tables,
indexes
The different types of segments are as follows :
u Table
u Cluster
u Index
u Partition d'index
u Index lob
u Rollback segment
u Tempory Segment
u Starting segment
u Owner of a user
Set of user objects manipulable in SQL
Tables, indexes, clusters, triggers, views, dimensions
An object corresponds to
u Several extents
u A tablespace
u One or more physical disk files.
GOALS
u Describe your role as a database administrator (DBA)
u Plan an Oracle Database installation
u Use Optimal Flexible Architecture (OFA)
u Install the Oracle software by using Oracle Universal Installer (OUI)
DBA account
The SYSOPER privilege gives about the same rights as SYSDBA, with the
notable exception of creating the database.
u White room
u Network connection tool: VNC, Pc anywhere ...
u Control tools: cmd
u Secure or unsecured channel: SSL ...
u Connection rights and role on servers
Connection Principle
Connexion à l’instance Oracle
u Direct connection via a control tool
u CONNECT account/password AS { SYSDBA | SYSOPER }
u Better organization
u Facilitating administration
u Facilitate the relationship between several ORACLE
databases
u Administer and manage database scaling
u Separate administrative files and data files
u To have multiple versions of Oracle installed on the server
On Windows
On Unix or Linux
Installation agreement
Tree and Names
Under the user Oracle
/ORA01
u Directories
u /ora01/app/oracle
u /ora01/app/oracle/product/11.2.0/db_1
u Files
u Control files : /ora01/app/oracle/oradata-nom_instance/controln.ctl
u Redo log files : /ora01/app/oracle/oradata/nom_instance/redo.log
u Data Files : /ora01/app/oracle/oradata/nom_instance/tn.dbf
(N is an order number)
The serveur
u Mémory : 1GB /instance
u Minimum server requirements
u 1,5GB de mémoire
u 400 MB espace disque
u 1,5GB et 3,5 Gb for Oracle tools
u 1, 2 Gb / for the database
u 2,4 GB for the restore
u Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL
packages, and to support proper functioning of SQL*Plus
u @?/rdbms/admin/catalog.sql
u @?/rdbms/admin/catproc.sql
u @?/sqlplus/admin/pupbld.sql
u EXIT
SGA
u Set a maximum size
u Fix these components
u Set the number of user processes
u Choose a cancellation management mode, before the COMMIT
u Automatic: Managed in a tablespace
u Handbook; Managed in the cancellation segments
Complements
To change settings
ALTER SYSTEM for instance
ALTER DATABASE to return to the CREATE DATABASE choices
To view database information
Sql>Show parameters
Sql>select * from V$DATABASE;
Rôle du Listener :
Listen and transmit data sent between the client and the server
Par défaut le fichier listener.ora file est dans le dossier
u $ORACLE_HOME/network/admin sous Unix
u %ORACLE_HOME%\network\admin sous Windows
Oracle Listener
LSNRCTL>
Configuration côté client
u sélectionner les méthodes de résolution, elles sont stockées dans le fichier sql.ora
u Lancer Oracle net configuration et suivre les instructions
u Visualiser le fichier tnsname.ora
u Lancer la commande tnsping pour visualiser la connexion réseau
Nota : le fichier tnsname.ora n’est pas fixé à un client. Vous pouvez le dupliquer sur
d’autres machines clients en le maintenant dans un dossier de distribution
1. Démarrage de l’instance
2. Montage de la base de données
3. Ouverture de la base
Oracle Administration 11G R2 - Didier LESTRAT 104
Managing the instance and a database
$ Export ORACLE_SID=orcl
$ sqlplus /nolog
SQL>STARTUP
u NORMAL
Oracle waits for all users to be disconnected (no new connections allowed) and
then properly closes the database.
IMMEDIATE
Oracle disconnects all users (by performing a ROLLBACK of any transactions in
progress) and then closes the database cleanly.
TRANSACTIONAL
Oracle waits for all current transactions to end before disconnecting users (no new
transactions allowed) and then properly closes the database.
u ABORT
Oracle disconnects all users (without performing any ROLLBACK of any
transactions in progress) and closes the database "brutally" without
performing a checkpoint. A recovery of the instance will be necessary
the next time you start
TRANSACTIONA
Database Behavior ABORT IMMEDIATE L NORMAL
NEW connections No No No No
Waiting for Sessions No No No Yes
Waiting for No No Yes Yes
Transactions
Performs a No Yes Yes Yes
checkpoint
and closes open
files
SHUTDOWN
NORMAL
IMMEDIATE
TRANSACTIONAL
ABORT
CREATE TABLESPACE
u Exemple :
u CREATE TABLESPACE lmtbsb DATAFILE
'/ora01/oracle/data/lmtbsb01.dbf' SIZE 50MEXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Create 2 tablespaces
u 1 for the index
u 1 for the user
&5($7(86(5PHWLHU
,'(17,),('%<PHWLHUBHVDLS
'()$8/77$%/(63$&(WEVBXVHUV
4827$021WEVBXVHUV
7(0325$5<7$%/(63$&(WHPS
4827$021V\VWHP
352),/(DSSBXVHU
3$66:25'(;3,5(
Modify user
$/7(586(5PHWLHU
,'(17,),('%<QHZBSDVVZG
'()$8/77$%/(63$&(WEVBXVHUV
Privileges
A privilege is a right to execute a particular type of SQL statement or to access another
user’s object
System privileges
u Create role : affecte un rôle à un utilisateur
u Create user : droit de créer un utilisateur
Object privileges
u Insert
u Delete
u Update
u Select
u With admin option : Assigns the user the right to assign, remove,
modify, and in turn delete the received role privileges
u with the ALL PRIVILEGES option. It grants almost unlimited rights to the
user who inherits it, with the security risks that this entails
Roles
A role is a grouping of privileges that is assigned to one or more users
u Easier privilege management
u Dynamic privilege management
u Selective availability of privileges
&5($7(52/(GEDBH[SORLW
u Check in tables and system views and send results to a text file
Profile Management
A set that limits the resources assigned to a user
u Create 3 role
u Role 1 named admin_job :
u Create table, create user, Lauching procdures, create index
u Role named user_job1 :
u Update On columns first_name et last_name Of HR employee table
u Select Of HR employee table
Check in tables and system views and send results to a text file
A schema
TYPE VALEURS
BINARY-INTEGER entiers allant de –2**31 à 2**31)
POSITIVE / entiers positifs allant jusqu’à 2**31 -1
NATURAL
NUMBER Numérique (entre –2**418 à 2**418)
INTEGER Entier stocké en binaire (entre –2**126 à 2**126)
CHAR (n) Chaîne fixe de 1 à 32767 caractères (différent pour une colonne de table)
VARCHAR2 (n) Chaîne variable (1 à 32767 caractères)
Chaîne variable (1 à 32767 caractères) (maximum 2 gigaoctets)
DATE Date (ex. 01/01/1996 ou 01-01-1996 ou 01-JAN-96 ...)
CLOB Grand objet caractère. Objets de type long stockés en binaire (maximum 4 giga
octets)
BLOB Grand objet binaire. Objets de type long (maximum 4 giga octets)
NCLOB Support en langage nationale (NLS) des grands
objets caractères. Déclare une variable gérant un
pointeur sur un grand bloc de caractères utilisant
un jeu de caractères mono-octets, multi-octets de
longueur fixe ou encore multi-octets de longueur
variable et stocké en base de données
the principles
u Solutions:
u RMAN tool
u System Command with SQL Queries
Principe
u To : Backup of data files
u Archiving enabled
u T1 : Incident occurs and the data file is lost
Data recovery :
u Taking the Last Backup
u Apply archived log files on this backup
Scenarios
u Full backup closed base (consistent)
u Full backup open database (inconsistent)
u Partially open backup
u Incremental backup
Loss of data
Yes No
yes Archivelog Archivelog
Noarchivelog
Backup closed base?
No Archivelog Archivelog
RMAN Tool:
u rman target /
u Rman>
Configurer RMAN
Configuration actuelle
u RMAN>show all
Configurer la destination de la sauvegarde
CONFIGURE CHANNEL DEVICE TYPE DISK options
Ram >CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT \home\oracle\sauve\%u
MAXPIECESSIZE 4 G;
(taille des fichiers de sauvegarde est limité à 4 GO, le jeu comprendra plusieurs jeu)
Pour revenir à la configuration par défaut
Rman>CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR
Fenêtre de restauration
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF n DAYS;
Redondance
CONFIGURE RETENTION POLICY TO REDUNDANCY n;
Oracle Administration 11G R2 - Didier LESTRAT 144
Les index
Les types d’index :
u B-tree Index
u Bitmap Index
But d’indexer une table
u Performance des requêtes
u Accès plus rapide dans les colonnes
u Eviter le FULL Scan des tables
Les index peut également améliorer dans l'application des clés primaires et uniques.
Création d’index
CREATE INDEX employees_idx1 ON employees (last_name, job_id);
CREATE INDEX employees_idx2 ON employees (job_id, last_name);
u Inconvénient :
u Dégrade les performances pour les mises à jour
u Stockage des index et tablespace : même attribut que pour les tables
u Attribut pour le STORAGE : INITIAL, NEXT, MINEXTENTS, MAXEXTENTS,
PCTINCREASE
CREATE INDEX emp_ename ON emp(ename)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTFREE 20);
u Index d’une contrainte de clé primaire
Sur tous les objets d’une base, ce sont les tables et les index qui occupent de
la place
Les tables et les index sont des segments et le stockage est géré par
extensions et par les caractéristiques des tablespaces
Header
Il contient l’information générale : bloc de données, bloc d’index, rollback…
Table Directory :Informations sur la table
Row Directory : Cette partie du bloc de données contient des informations sur les lignes réelles dans
le bloc
Free Space
Espace pour l’insertion de nouvelles données
Row Data
Contenu des données des tables ou index
Oracle Administration 11G R2 - Didier LESTRAT 150
Oracle Administration 11G R2 - Didier LESTRAT 151
Header
Il contient l’information générale : bloc de données, bloc d’index, rollback…
Table Directory :Informations sur la table
Row Directory : Cette partie du bloc de données contient des informations sur les lignes réelles dans le
bloc
Free Space
Espace pour l’insertion de nouvelles données
Row Data
Contenu des données des tables ou index
Le ROWID est une colonne virtuelle qui est présente dans chaque table
u Donne la localisation de l’adresse physique de stockage de la ligne
On peut requêter sur cet valeur
u SQL> select ROWID …..
u Ou sql> UPDATE …. Where ROWID = ‘abc’
u Augmente la performance dans une application dans les clauses
WHERE
Chaînage et migration
Une ligne complète est stockée en totalité dans un bloc
Si la ligne est trop grande, Oracle l’insère dans d’autres blocs
u Chaînage d’une ligne
u Lit plusieurs blocs
u Pointeur entre bloc
u Perte en performance
Commande VALIDATE
VALIDATE clause
DATABASE, DATAFILE_LIST, CURRENT, SPFILE…..
Détecter les problèmes de corruption ou de fichiers manquants
Sauvegarde
Base montée : fichiers de contrôle ouverts
Sauvegarde base ouverte si la base fonctionne en ARCHIVELOG
Fichiers de données, contrôle, fichiers de journalisation archivé, paramètres,…
1. Shutdown de la base
2. Startup mount
3. Backup..(conditions)
Syntaxe
BACKUP mode quoi [option]
Mode :
u INCREMENTATE LEVEL n [CUMUTATIVE]
u VALIDATE
u AS COPY AS [COMPRESSED] BACKUPSET
u Option : DATABASE, DATAFILE cible,….
Practice 10
&UHDWHXVHU
/DXFK 50$1WRRO
64/!VKXWGRZQLPPHGLDWH
64/!VWDUWXSPRXQW
6TO!H[LW
UPDQ WDUJHW
Other tools
u DATA PUMP
Move data or metadata between Oracle databases
u EXPORT
Export to a binary file: complete, or partial
u IMPORT : complet, ou partiel
u SQL*LOADER
Lets you load into a table of stored ACII files, or flat file
u Impdp
Practice
u /* Privileges */
u /* */