1 / 38

Introduction to Oracle

Anton Topurov IT-DB. Introduction to Oracle. What is a database?. O rganized Collection of Data. IT manager. App Developer. Database Administrator. System Administrator. IT manager. What is. Database?. Ready to find out more?. Oracle Database Internals. Oracle Database.

adriel
Download Presentation

Introduction to 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. Anton Topurov IT-DB Introduction to Oracle Introduction to Oracle

  2. What is a database? Introduction to Oracle

  3. Organized Collection of Data Introduction to Oracle

  4. IT manager Introduction to Oracle

  5. App Developer Introduction to Oracle

  6. Database Administrator Introduction to Oracle

  7. System Administrator Introduction to Oracle

  8. IT manager Introduction to Oracle

  9. What is Database? Introduction to Oracle

  10. Introduction to Oracle

  11. Introduction to Oracle

  12. Ready to find out more? Introduction to Oracle

  13. Oracle Database Internals Introduction to Oracle

  14. OracleDatabase Oracle Instance Real Application Clusters (RAC) Working with Oracle Database SQL statements processing Introduction to Oracle

  15. Introduction to Oracle Introduction to Oracle 16 23 April 2013

  16. Oracle Database Server Introduction to Oracle

  17. Simplified view System Global Area (SGA) PGA Background Processes Server Processes Data Files Control Files Online Redo Logs Introduction to Oracle

  18. Database Physical Layout Data Files Control Files Online Redo Logs Introduction to Oracle

  19. Physical to Logical Data Files Introduction to Oracle

  20. Logical Layout • Block • The smallest logical unitof data storage • Tablespace • Maps to one or more datafiles • Segment • Type (table, index,..) • Within a tablespace • Extent • Continios set of blocks Introduction to Oracle

  21. OracleDatabase Oracle Instance Real Application Clusters (RAC) Working with Oracle Database SQL statements processing Introduction to Oracle

  22. Oracle Instance System Global Area (SGA) Background Processes Data Files Control Files Online Redo Logs Introduction to Oracle

  23. SGA contents • Shared pool • shared memory constructs like shared SQL areas • most recently executed SQL statements • most recently used data definitions • Buffer cache • holds copies of data blocks • server processes manipulate data from buffer cache (not directly from datafiles) • managed using LRU algorithm (Least Recently Used) Introduction to Oracle

  24. Background Processes System Monitor Redo Log Buffer System Global Area (SGA) Buffer Cache Process Monitor Diagnosability Process Database Writer Checkpointer Redo Log Writer Redo Log Archiver Backup Data Files Control Files Online Redo Logs Archived Redo Logs Introduction to Oracle

  25. OracleDatabase Oracle Instance Real Application Clusters (RAC) Working with Oracle Database SQL statements processing Introduction to Oracle

  26. Real Application Clusters SGA SGA SGA PGA PGA PGA Clusterware Clusterware Data Files Control Files Online Redo Logs Introduction to Oracle

  27. OracleDatabase Oracle Instance Real Application Clusters (RAC) Working with Oracle Database SQL statements processing Introduction to Oracle

  28. Database Account Introduction to Oracle • Database user • username/password to connect to database • Privileges • System and object privileges / roles • CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE JOB,… • EXECUTE ON DBMS_LOCK, SELECT ON HR.EMPLOYEES, … • Profiles • CERN_DEV_PROFILE: 10 concurrent sessions • CERN_APP_PROFILE: 400 concurrent sessions • Space quota on tablespaces

  29. Database vsSchema • select name from hr.employees where id=7; Introduction to Oracle A Schema? Collection of objects Owned by a database user and has the same name as that user Each user owns a single schema. Good programming practice is to prefix objects by schema name

  30. Connecting to Oracle Database Instance SID: DEVDB11 • sqlplususername@ • ’( DESCRIPTION= • (ADDRESS= (PROTOCOL=TCP) • (HOST=node1.cern.ch) (PORT=10121)) • (CONNECT_DATA= (SID=DEVDB11)))’ SGA PGA Listener port 10121 node1 Control Files Data Files Online Redo Logs Introduction to Oracle

  31. Connecting to RAC Database • sqlplususername@ ( DESCRIPTION= • (ADDRESS= (PROTOCOL=TCP) (HOST=node2-v.cern.ch) (PORT=10121)) • (ADDRESS= (PROTOCOL=TCP) (HOST=node1-v.cern.ch) (PORT=10121)) (LOAD_BALANCE=off) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=CERN_SRV1.cern.ch) ))’ SGA node1.cern.ch node2.cern.ch PGA SGA PGA Listener Listener Clusterware node2-v Clusterware node1-v CERN_SRV1 CERN_SRV3 CERN_SRV2 Online Redo Logs Data Files Control Files Introduction to Oracle

  32. Local naming method • sqlplususername@ cerndb1 • ( DESCRIPTION= • (ADDRESS= (PROTOCOL=TCP) (HOST=node2-v.cern.ch) (PORT=10121)) • (ADDRESS= (PROTOCOL=TCP) (HOST=node1-v.cern.ch) (PORT=10121)) (LOAD_BALANCE=off) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=CERN_SRV1.cern.ch) ))’ = Introduction to Oracle Based on tnsnames.ora /afs/cern.ch/project/oracle/admin \\cern.ch\dfs\Applications\Oracle\ADMIN

  33. OracleDatabase Oracle Instance Real Application Clusters (RAC) Working with Oracle Database SQL statements processing Introduction to Oracle

  34. SQL Statements Processing Row Source Generation select * from hr.employees; Optimization Shared Pool Check Semantic Check SQL Statement Syntax Check Execution Parsing select * from hr.employees; sql_id = 8k3hpjw6yjup5 Hard Parse Soft Parse select name from hr.employees where id=:id_val; Introduction to Oracle

  35. Summary Introduction to Oracle

  36. Documentation Introduction to Oracle • Oracle Database Concepts http://docs.oracle.com/cd/E11882_01/server.112/e25789/toc.htm • Oracle 11g documentation • http://www.oracle.com/pls/db112/portal.all_books • Architecture poster

  37. Thank you! Introduction to Oracle

More Related