数据库培训杨宝秋.ppt

ORACLE数据库基础,杨宝秋,议程,体系结构,3,议程,ORACLE简介,1,体系结构,3,关于Oracle,是一家世界500强的公司,2010财富杂志的排名为366位 有两个商标,一个是“Oracle”，另一个是“甲骨文” 老板是劳伦斯埃里森 Oracle是一个关系型的数据库 Bob Miner、Ed Oates、Scott 名字为中报局投资的项目代码,看两张图片,左起 Ed Oates、Bruce Scott、 Bob Miner、Larry Ellison,Oracle版本历史,1979年的夏季，RSI发布了Oracle第二版 1983年3月，RSI发布了Oracle第三版 1984年10月，Oracle发布了第4版产品 1985年，Oracle发布了5.0版 1988年，ORACLE发布了第6版,引入了行级锁 1992年6月，Oracle发布了第7版 1997年6月，Oracle第八版发布 1998年9月，Oracle公司正式发布Oracle 8i 2001年6月，Oracle发布了Oracle 9i，引入了RAC。 2003年9月，Oracle发布了Oracle 10g 2007年7月11日，Oracle发布了Oracle 11g,Tasks of an DBA,Evaluate the database server hardware. Install the Oracle software. Plan the database. Create and open the database Back up the database. Implement the database design. Recover from database failure. Monitor database performance.,议程,软件的安装与建库,体系结构,3,2,安装流程,结束,环境准备,配置,打补丁,安装Oracle 软件,建库,准备工作,准备一台机器 准备Oracle安装介质+补丁 /technetwork/database/enterprise-edition/downloads/index.html 补丁需要在metalink下，帐号及口令是收费的 准备installation guide文档 /pls/db102/homepage,System Requirements,Hardware: 512 MB of physical random access memory (RAM) 1 GB of swap space (or twice the size of RAM) 400 MB of disk space in the temporary directory (/tmp or Temp) 1.5 GB of disk space for the Oracle software 1.5 GB of disk space for the preconfigured database Operating system: See installation guide,Setting Environment Variables,ORACLE_BASE: The base of the Oracle directory structure for OFA ORACLE_HOME: The directory containing the Oracle software ORACLE_SID: The initial instance name (ORCL by default) NLS_LANG: The language, territory and client character set settings,Preinstallation Checks,Oracle Universal Installer,Inventory and UNIX Group Name,orainstRoot.sh,# sh /u01/app/oracle/oraInventory/orainstRoot.sh Creating the Oracle inventory pointer file (/etc/oraInst.loc) Changing groupname of /u01/app/oracle/oraInventory to oinstall.,File Locations,Install Type,Prerequisite Checks,Starter Database,Passwords and Summary,Installation,root.sh,Creating an Oracle Database,Database Configuration Assistant,Creating a Database,Database Identification,Management Options,Passwords and Storage,File Locations and Backup Recovery,File Location Variables,Content and Initialization Parameters,Database Storage,Creation Options and Create,议程,体系结构,3,Oracle体系架构,Instance,SGA,Redo Log Buffer,Shared Pool,Data Dictionary Cache,Library Cache,DBWR,SMON,PMON,CKPT,LGWR,Others,User process,Server process,PGA,Control files,Data files,Database,Database Buffer Cache,Redo Log files,Java Pool,Large Pool,Parameter file,Archived Log files,Control Files,Contain physical database structure information Multiplexed to protect against loss Required to start the instance,Control files,Redo Log Files,Record changes to the database Multiplex to protect against loss,Redo log buffer,Log writer LGWR,Group 1,Group 2,Group 3,Tablespaces and Datafiles,Tablespaces consist of one or more data files Data files belong to only one tablespace,Segments, Extents, and Blocks,Segments exist within a tablespace. Segments are made of a collection of extents. Extents are a collection of data blocks. Data blocks are mapped to OS blocks.,Segment,Extents,Data blocks,OS blocks,Oracle Memory Structures,Java Pool,Database Buffer Cache,Redo Log Buffer,Shared Pool,Large Pool,SGA,Streams Pool,Server Process 1,PGA,Server Process 2,PGA,Back- ground Process,PGA,Oracle Processes,System Monitor SMON,Database Writer DBWn,Check point CKPT,Log Writer LGWR,Process Monitor PMON,Archiver ARCn,Server Process,Server Process,Server Process,Server Process,System Global Area SGA,Background Processes,Connecting to an Instance,User,Server,Server,User,Client,User,Server,Oracle database,Server,Application server,Browser,议程,体系结构,3,数据库接口-SQL,4,What Is SQL*Plus?,Command-line tool Used interactively or in batch mode,$sqlplus / as sysdba SQL*Plus: Release .0 - Production on Thu Nov 25 15:13:36 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. SQL select * from dual; D - X,What Is SQL?,SQL provides statements for a variety of tasks, including: Querying data Inserting, updating, and deleting rows in a table Creating, replacing, altering, and dropping objects Controlling access to the database and its objects SQL unifies all of the preceding tasks in one consistent language.,使用 SQL,There are several tools for interfacing with the database using SQL: Oracle SQL*Plus Pl/sql developer,Toad等图形化软件 Pro*c和Oci程序设计,SQL Processing Phases,Close,Open,Fetch,Bind,Parse,Execute,SQL Processing: Parse,Parse phase: Searches for the statement in the shared pool Checks syntax Checks semantics and privileges Merges view definitions and subqueries Determines execution plan Minimize parsing as much as possible: Parse calls are expensive. Avoid reparsing Parse once, execute many times,LIBRARY CACHE LATCHES,Oracle Optimizer,The optimizer creates an execution plan for every SQL statement by: Evaluating expressions and conditions Using object and system statistics Deciding how to access the data Deciding how to join tables Deciding which path is most efficient Comparing the cost for execution of different plans Determining the least-cost plan,Top Database Performance Issues,Bad connection management Bad SQL Nonstandard initialization parameters I/O issues Long full-table scans In-disk sorts High amounts of recursive SQL Schema errors and optimizer problems,议程,体系结构,3,5,常用维护操作,启/停数据库,Starting Up a Database,OPEN,MOUNT,NOMOUNT,SHUTDOWN,All files opened as described by the control file for this instance,Control file opened for this instance,Instance started,STARTUP,SHUTDOWN,Shutting Down the Database,Shutdown mode: A = ABORT I = IMMEDIATE T = TRANSACTIONAL N = NORMAL,A No No No No,T No No Yes Yes,I No No No Yes,Shutdown Mode Allow new connections Wait until current sessions end Wait until current transactions end Force a checkpoint and close files,N No Yes Yes Yes,Initialization Parameter Files,Alert Log, Thread 1 advanced to log sequence 48897 (LGWR switch) Current log# 1 seq# 48897 mem# 0: /dev/zhkfvg01/rredo1_11.dbf Tue Sep 1 00:24:33 2009 Thread 1 advanced to log sequence 48898 (LGWR switch) Current log# 5 seq# 48898 mem# 0: /dev/zhkfvg01/rredo1_51.dbf Tue Sep 1 00:27:03 2009 Thread 1 advanced to log sequence 48899 (LGWR switch) Current log# 4 seq# 48899 mem# 0: /dev/zhkfvg01/rredo1_41.dbf Tue Sep 1 00:32:21 2009 Thread 1 advanced to log sequence 48900 (LGWR switch) Current log# 2 seq# 48900 mem# 0: /dev/zhkfvg01/rredo1_21.dbf Tue Sep 1 00:39:26 2009 Thread 1 advanced to log sequence 48901 (LGWR switch) Current log# 3 seq# 48901 mem# 0: /dev/zhkfvg01/rredo1_31.dbf Tue Sep 1 00:43:10 2009 ,Tablespace management,Space Management in Tablespaces,Locally managed tablespace: Free extents are managed in the tablespace. Bitmap is used to record free extents. Each bit corresponds to a block or group of blocks. Bit value indicates free or used.,Creating Tablespaces,CREATE TABLESPACE userdata DATAFILE /u01/oradata/userdata01.dbf SIZE 5M;,A tablespace is created using the command: CREATE TABLESPACE,CREATE TABLESPACE userdata DATAFILE /u01/oradata/userdata01.dbf SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;,CREATE TABLESPACE user_data DATAFILE /u01/oradata/userdata01.dbf SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;,Adding Data Files to a Tablespace,Increases the space allocated to a tablespace by adding additional data files ADD DATAFILE clause is used to add a data file Example:,ALTER TABLESPACE user_data ADD DATAFILE /u01/oradata/userdata03.dbf SIZE 200M;,Dropping Tablespaces,You cannot drop a tablespace if it: Is the SYSTEM tablespace Has active segments INCLUDING CONTENTS drops the segments. INCLUDING CONTENTS AND DATAFILES deletes data files. CASCADE CONSTRAINTS drops all referential integrity constraints.,DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;,表空间统计,SELECT UPPER(D.TABLESPACE_NAME) ts_name, D.TOT_GROOTTE_MB ts_size, D.TOT_GROOTTE_MB - nvl(F.TOTAL_BYTES,0) used_space, TO_CHAR(ROUND(D.TOT_GROOTTE_MB - nvl(F.TOTAL_BYTES,0) / D.TOT_GROOTTE_MB * 100, 2), 990.99) used_pct, nvl(F.TOTAL_BYTES,0) free_space, nvl(F.MAX_BYTES,0) max_block FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 4 DESC,结果：,User management,Creating a New User:,Set the initial password:,CREATE USER hr IDENTIFIED BY hr123 DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp QUOTA 15M ON data QUOTA 10M ON users PASSWORD EXPIRE;,Dropping a User,DROP USER aaron;,DROP USER aaron CASCADE;,Use the CASCADE clause to drop all objects in the schema if the schema contains objects.,Grant privilege,Grant connect to hr;,Some Predefined Roles:,Undo management,Undo Data,Undo data is: A copy of original, premodification, data Captured for every transaction that changes data Retained at least until the transaction is ended Used to support: Rollback operations Read-consistent and flashback queries Recovery from failed transactions,User,Storing Undo Information,Undo information is stored in undo segments, which are in turn stored in an undo tablespace. Undo tablespaces: Are only used for undo segments Have special recovery considerations May only be associated with a single instance, and an instance can only have one active undo tablespace at a time,Monitoring Undo,DBA,Undo usually requires little management. Areas to monitor include: Undo tablespace free space “Snapshot too old” errors,Administering Undo,Administration of undo should include preventing: Undo tablespace space errors Size the undo tablespace properly Ensure large transactions commit periodically “Snapshot too old” errors Configure an appropriate undo retention interval Size the undo tablespace properly Consider guaranteeing undo retention,DBA,UNDO_MANAGEMENT=AUTO UNDO_TABLESPACE=UNDOTBS1,Configuring Undo Retention,DBA,UNDO_RETENTION=0,Undo retention specifies (in seconds) the amount of already committed undo information to retain. Default value is 0 (automatic). Maximum value is 232 seconds (more than 187 years). A setting of 0 indicates automatic undo retention mode.,Ora-01555,1:40,A查到第4000记录,报ora-01555,回滚段中4000万记录extent被重用,1:35,用户B update了T1表中的第4000万行的这条记录 用户B接着执行了commit,1:30,A刚查到2500万记录,UPDATE hr.employees SET salary=salary+100 WHERE employee_id=101; 1 row updated.,1:00,select * from T1;,用户B,Time,用户A,假定表T1有5000万条记录,全表扫需要1小时,Monitoring and Resolving Lock Conflicts,Locks,Prevent multiple sessions from changing the same data at the same time Automatically obtained at the lowest possible level for a given statement,Transaction 1,SQL UPDATE hr.employees 2 SET salary=salary*1.1 3 WHERE employee_id=100;,SQL UPDATE hr.employees 2 SET salary=salary+100 3 WHERE employee_id=100;,Transaction 2,Locking Mechanism,High level of data concurrency Row-level locks for inserts, updates, and deletes No locks required for queries Automatic queue management Locks held until transaction ends (with commit or rollback operation),Transaction 1,SQL UPDATE hr.employees 2 SET salary=salary*1.1 3 WHERE employee_id=101;,SQL UPDATE hr.employees 2 SET salary=salary+100 3 WHERE employee_id=100;,Transaction 2,Enqueue Mechanism,The enqueue mechanism keeps track of: Sessions waiting for locks The requested lock mode The order in which sessions requested the lock,Lock Conflicts,Deadlocks,查看锁sql,SELECT C.SID, C.SERIAL#, A.OWNER, A.OBJECT_NAME, C.PROGRAM, B.ORACLE_USERNAME, B.OS_USER_NAME, B.LOCKED_MODE, C.MACHINE, C.STATUS, C.SERVER, C.PADDR FROM DBA_OBJECTS A, V$LOCKED_OBJECT B, V$SESSION C WHERE A.OBJECT_ID = B.OBJECT_ID AND B.SESSION_ID = C.SID - AND ORDER