1 / 23

Laboratoire Supinfo des technologies Oracle

Laboratoire Supinfo des technologies Oracle. Rappels. Shared pool. Library cache. Data dict. cache. Aperçu de l’architecture Oracle. Instance. User process. SGA. Data buffer cache. Redo log buffer. Server process. PGA. SMON. PMON. CKPT. LGWR. Others. DBW0. Database.

wren
Download Presentation

Laboratoire Supinfo des technologies Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Laboratoire Supinfo des technologies Oracle Rappels

  2. Shared pool Library cache Data dict.cache Aperçu de l’architecture Oracle Instance Userprocess SGA Data buffercache Redo logbuffer Serverprocess PGA SMON PMON CKPT LGWR Others DBW0 Database Data files Control files Redo logfiles Parameter file Archived log files Password file

  3. SMON DBWR PMON CKPT LGWR ARCH Le fichier de paramètre Instance SGA Shared pool Library cache Data buffercache Redo logbuffer Data dict.cache initU15.ora SQL> CONNECT / AS SYSDBA SQL> STARTUP PFILE=/DISK1/initU15.ora

  4. Startup et shutdown STARTUP OPEN All files opened as described by the control file for this instance. MOUNT Control file opened for this instance. NOMOUNT Instance started. SHUTDOWN SHUTDOWN

  5. Options de Shutdown A x x x x Shutdown Mode Allow new connections Wait until current sessions end Wait until current transactions end Force a checkpoint and close files I x x x o T x x o o N x o o o Shutdown mode: A Abort I Immediate T Transactional N Normal x o NO YES

  6. Creating the database SPOOL creU16.log STARTUP NOMOUNT PFILE=initU16.ora CREATE DATABASE U16 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXLOGHISTORY 100 LOGFILE GROUP 1 ('/DISK3/log1a.rdo',/DISK4/log1b.rdo’) SIZE 1 M, GROUP 2 ('/DISK3/log2a.rdo',/DISK4/log2b.rdo’) SIZE 1 M DATAFILE '/DISK1/system01.dbf' size 50M autoextend on CHARACTER SET WE8ISO8859P1;

  7. Catégories de vues DBA_xxx All of the objects in the database ALL_xxx Objects accessible by the current user USER_xxx Objects owned by the current user

  8. Examples • DICT_COLUMNS • DBA_TAB_COLUMNS • DBA_CONSTRAINTS • DBA_EXTENTS • General overview • DICTIONARY • Schema objects • DBA_TABLES • DBA_OBJECTS • Space allocation • DBA_ SEGMENTS • DBA_FREE_SPACE • Database structure • DBA_DATA_FILES • DBA_ROLLBACK_SEGS • DBA_TABLESPACES

  9. Script de création de vues Script Purpose catalog.sql Creates commonly used data dictionary views and synonyms catproc.sql Runs scripts required for server-side PL/SQL

  10. L’utilité du fichier de controle • Small, binary file • Required: • At mount • To operate • Linked to a single database • Should be multiplexed • Loss may require recovery Data files Control files Redo logfiles Database

  11. control_files=(/DISK1/control01.con,/DISK2/control02.con) Disk 1 Disk 2 control01.con control02.con Multiplexage

  12. Groupes et membres de redo Group 1 Group 2 Group 3 Disk 1 Member Member Member Disk 2 Member Member Member

  13. Sans archivage Backup Disk failure Control files Data files 50 51 100 101 t2 t1

  14. Avec Archivage Backup Archived redo logs Disk failure 50 Control files Data files 99 50 51 100 101 t2 t1

  15. Hiérarchie de stockage Database Tablespace Data file Logical Physical Segment Extent Oracle block OS block

  16. Création de tablespace CREATE TABLESPACE app_data DATAFILE '/DISK4/app_data_01.dbf' SIZE 100M, '/DISK5/app data_ 02.dbf' SIZE 100M MINIMUM EXTENT 500K DEFAULT STORAGE ( INITIAL 500K NEXT 500K MAXEXTENTS 500 PCTINCREASE 0 );

  17. Rollback: Fonctionnalités Transaction rollback Transaction recovery Read consistency Rollback segment

  18. Héritage des clause de stockage Oracle default Tablespace Segment

  19. Vue du dictionnaire de données Used extents DBA_EXTENTS Free extents DBA_FREE_SPACE Segments DBA_SEGMENTS Data files DBA_DATA_FILES Tablespaces DBA_TABLESPACES

  20. Capacité de fragmentation Tablespace SYSTEM TOOLS DATAn INDEXn RBSn TEMPn Usage Data dictionary Applications Data segments Index segments Rollback segments Temporary segments Fragmentation Zero Very low Low Low High Very high* * Relevant only if tablespace PERMANENT

  21. Structure d’une ligne Row header Column length Database block Column value

  22. Classification des indexes • Logical • Single column or concatenated • Unique or nonunique • Function-based • Physical • Partitioned or nonpartitioned • B-tree • Normal or reverse key • Bitmap

  23. Fin du rappel VOUS VOUS RAPPELEZ DE TOUT, ON FAIT UN CONTRÔLE

More Related