Oracle 数据库 DBA 基本技能

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,Copyright Oracle Corporation, 200,2,. All rights reserved.,1-,70,主要内容,Oracle,数据库的组成结构,管理临时表空间,加快索引的创建,使用,SPFILE,作为初始化参数文件,使用,Profile,加强数据库安全,监控系统的锁资源争用,SQL,语句调优,Oracle,数据库的基本结构,Oracle,服务器,An Oracle server:,Is a database management system that provides an open, comprehensive, integrated approach to information management,Consists of an Oracle instance and an Oracle database,Oracle server,Password,file,Oracle,服务器的主要组件,Instance,SGA,Redo LogBuffer,Shared Pool,Data DictionaryCache,Library,Cache,DBWR,SMON,PMON,CKPT,LGWR,Others,Userprocess,Serverprocess,PGA,Control files,Data,files,Database,DatabaseBuffer Cache,Redo Log files,Java Pool,Large Pool,Parameter,file,Archived,Log files,Oracle,实例,An Oracle instance:,Is a means to access an Oracle database,Always opens one and only one database,Consists of memory and background process structures,Background process structures,Memory structures,Instance,SGA,Redo LogBuffer,Shared Pool,Data DictionaryCache,Library,Cache,DBWR,SMON,PMON,CKPT,LGWR,Others,DatabaseBuffer Cache,Java Pool,Large Pool,Oracle Database,An Oracle database:,Is a collection of data that is treated as a unit,Consists of three file types,Password,file,Parameter,file,Archived,Log files,Control files,Data,files,Redo Log files,Oracle Database,建立一个数据库连接,Connecting to an Oracle instance:,Establishing a user connection,Creating a session,Session created,Database user,Userprocess,Serverprocess,Connection established,Oracle server,用户进程,A program that requests interaction with the Oracle server,Must first establish a connection,Does not interact directly with the Oracle server,Database user,Serverprocess,Userprocess,Connectionestablished,服务器进程,A program that directly interacts with the Oracle server,Fulfills calls generated and returns results,Can be dedicated or shared server,Connection established,Session created,Database user,Userprocess,Serverprocess,Oracle server,服务器进程,sdjf_dom2_srv4:/oracle ps -ef | grep oracle,oracle 2904150 2379866 0 Jan 27 - 0:06 oraclejzjf2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq),oracle 3866722 1 0 08:50:21 - 0:00 oraclejzjf1 (LOCAL=NO),oracle 3870846 1 0 11:34:20 - 0:00 oraclejzjf1 (LOCAL=NO),oracle 3895388 1 0 02:37:35 - 0:00 oraclejzjf1 (LOCAL=NO),oracle 3903652 1 0 20:14:28 - 1:28 oraclejzjf1 (LOCAL=NO),进程的所有者通常为,oracle,用户,进程的名称为：,oracle,SID,可以被安全的,kill,服务器进程,sdjf_dom2_srv4:/oracle topas,Name PID CPU% PgSp Owner,oracle 561246 36.6 7.1 oracle,oracle 2711644 6.7 2.3 rate,oracle 3358774 2.0 7.4 oracle,oracle 454698 0.1 16.1 oracle,-执行查询,select,s.sid, s.status, s.terminal, s.machine, s.program,from,v$process p, v$session s,where,s.paddr = p.addr,and,p.spid =,561246,;,察看,Top CPU,进程,找到该进程的,SID,服务器进程,- 察看会话的属性,select,s.sid, s.status, s.program, sa.sql_text, sa.hash_value,from,v$session s, v$sqlarea sa,where,s.sql_address = sa.address,and,s.sql_hash_value = sa.hash_value,and,s.sid = 1,561,;,SIDSTATUSTERMINALMACHINEPROGRAM,- - - - -,ACTIVE,菏泽计费,WORKGROUP,菏泽计费,plsqldev.exe,-,察看会话正在执行的,SQL,select,sa.sql_text,from,v$session s, v$sqlarea sa,where,s.sql_address = sa.address,and,s.sql_hash_value = sa.hash_value,and,s.sid =,1488,;,根据,SID,察看会话的属性,察看会话正在执行的,SQL,服务器进程,- 杀死服务器进程,ps -ef | grep -v grep | grep LOCAL=NO | awk print $2| xargs kill -9,杀死服务器进程，以加快实例的关闭,后台进程,Maintains and enforces relationships between physical and memory structures:,Mandatory background processes:,DBWnPMONCKPT,LGWRSMON,Optional background processes:,ARCnLMDn QMNn,CJQ0LMON RECO,DnnnLMS Snnn,LCKnPnnn,后台进程,sdjf_dom2_srv4:/oracle ps -ef | grep ora_,oracle 466988 1 0 Jan 06 - 11:54 ora_smon_jzjf2,oracle 487480 1 0 Jan 06 - 15:08 ora_lmon_jzjf2,oracle 602182 1 0 Jan 06 - 14:54 ora_dbw0_jzjf2,oracle 704594 1 0 Jan 06 - 19:20 ora_pmon_jzjf2,oracle 737408 1 0 Jan 06 - 23:33 ora_ckpt_jzjf2,进程的所有者通常为,oracle,用户,进程的名称为：,ora_,后台进程类型_,SID,被,kill,后可能会导致实例终止,管理临时表空间,临时表空间中的空间管理,OPS/RAC,中的每个实例仅创建一个临时段，实例中的所有进程将共享该排序段,实例启动后第一个利用磁盘执行排序操作的进程创建临时段；实例关闭后，系统将清空临时表空间,临时空间的分配以,extent,为单位进行，并通过,SGA,中的,Sort Extent Pool,对这些,extents,进行管理。,每个实例会维护一个属于自己的,SEP，,并通过视图,v$sort_segment,向用户开放,临时表空间中的空间管理,临时表空间已经 100% 满了,遇到了,ORA-01652 “unable to extend temp segment by %s in tablespace %s”,错误,查询,v$sort_segment,查询,v$tempseg_usage,临时表空间中的空间管理,select,s.tablespace_name, s.total_blocks, s.used_blocks,from,v$sort_segment s;,TABLESPACE_NAMETOTAL_BLOCKSUSED_BLOCKS,- - -,TEMP,4420864,15104,select,s.sid, s.program, s.machine, t.,extents, t.blocks,from,v$tempseg_usage t, v$session s,where,t.session_addr = s.saddr; SIDPROGRAMMACHINEEXTENTSBLOCKS,- - - - -,1247plsqldev.exeJSJZXZXY3384,临时表空间中的空间管理,在,OPS/RAC,中，当一个实例需要临时空间并且,SEP,中没有可用的,extent,时：,或者增长临时段,或者系统自动从另一个实例,re-assign extent,增大参数,PGA_AGGREGAGE_TARGET,参数值以尽量避免磁盘排序,select,*,from,v$sysstat,where,name,like,%sort%,;,STATISTIC#NAMECLASSVALUESTAT_ID,- - - - -,312sorts (memory)64797009872091983730,313,sorts (disk),6469002533123502,314sorts (rows)64603954468743757672740,加快索引的创建,加快索引的创建,alter session set db_file_multiblock_read_count = 128;,alter index bill.ind_charge_id rebuild,parallel 5 nologging,;,create index ,parallel 5 nologging;,创建超大分区表的本地索引,create index idx_subs_score_detail_attr_sid,on subs_score_detail_attr( subsoid, region,),local,tablespace inx,unusable,;,首先创建,UNUSABLE,状态的索引,然后利用多个进程，,rebuild,失效状态的索引分区,能够利用,nologging,参数的操作,CREATE TABLE AS SELECT,INSERT TABLE SELECT,CREATE INDEX,REBULD INDEX or INDEX PARTITION,MOVE TABLE or TABLE PARTITION,SQL*Loader direct-path load,查找失效的索引,-,查找非分区索引,select,owner, index_name, index_type, table_owner, table_name,from,dba_indexes,where,partitioned =,NO,and,status =,UNUSABLE,; OWNERINDEX_NAMEINDEX_TYPETABLE_OWNER TABLE_NAME- - - - -,BILLIND_CHARGE_ID,NORMAL,BILLT_RC_INSTANCEBILLIND_CHARGE_ID0,NORMAL,BILLT_RC_INSTANCE,-,查找分区索引,select,i.index_owner, i.index_name, i.partition_name,from,dba_ind_partitions i,where,status =,UNUSABLE,;,使用,SPFILE,作为初始化参数文件,初始化参数文件,From Oracle9,i,onwards, there are two types of initialization parameter file used:,Static parameter file, PFILE,Persistent server parameter file, SPFILE,spfiledb01.ora,初始化参数文件,CONNECT / AS SYSDBA,STARTUP,Oracle Instance,SGA,Redo LogBuffer,Shared Pool,Data DictionaryCache,Library,Cache,DBW0,SMON,PMON,CKPT,LGWR,Others,DatabaseBuffer Cache,Java Pool,Large Pool,PFILE,initSID.ora,文本文件,通过文本编辑器进行修改,采用手工的方式进行修改,所作的修改在实例下次启动时生效,仅在实例启动时打开,确省的存取目录是,$ORACLE_HOME/dbs,SPFILE,spfileSID.ora,二进制文件,通过,Oracle,服务器进行修改,总是存放在服务器上,Ability to make changes persistent across shut down and start up,创建,SPFILE,通过,PFILE,创建,SPFILE,where,SPFILE-NAME,: SPFILE to be created,PFILE-NAME,: PFILE creating the SPFILE,可以在实例启动之前或者之后执行,CREATE SPFILE =,$ORACLE_HOME/dbs/spfileDBA01.ora,FROM PFILE =,$ORACLE_HOME/dbs/initDBA01.ora,;,创建,SPFILE,SQL sqlplus / as sysdba,Connected to an idle instance.,SQL create pfile from spfile;,create pfile from spfile,*,ERROR at line 1:,ORA-27037: unable to obtain file status,IBM AIX RISC System/6000 Error: 2: No such file or directory,Additional information: 3,SQL create pfile from spfile=/dev/rjf_spfile;,File created.,不指定文件名，系统将使用缺省值，或者,spfile,参数值,SPFILE,内容示例,*,.background_dump_dest= /home/dba01/ADMIN/BDUMP,*.control_files=/home/dba01/ORADATA/u01/ctrl01.ctl *.core_dump_dest= /home/dba01/ADMIN/CDUMP,*.db_block_size=8192,*.db_name=jzjf,jzjf1.thread=1,jzjf2.thread=2,jzjf1.undo_tablespace=UNDOTBS1,jzjf2.undo_tablespace=UNDOTBS2,检查当前是否使用了,SPFILE,察看初始化参数,spfile,sqlplus / as sysdba,SQL show parameter spfile,NAME TYPE VALUE,- - -,spfile string /dev/rjf_spfile,修改,SPFILE,中的参数,修改参数值,指明是临时修改还是永久保存,重设参数值,ALTER SYSTEM SET undo_tablespace =,UNDO2,;,ALTER SYSTEM SET undo_tablespace = UNDO2,SCOPE=BOTH;,ALTER SYSTEM SET undo_retention = 3600,SCOPE=memory,SID=,jzjf1;,ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID=*;,STARTUP,命令,处理顺序,:,spfileSID.ora,initSID.ora,Specified PFILE can override precedence.,PFILE,中可以指明使用,SPFILE,STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora,SPFILE = /database/startup/spfileDBA1.ora,使用,Profile,加强数据库安全,使用,Profile,加强数据库安全,A profile is a named set of password and resource limits.,通过,CREATE USER,或者,ALTER USER,command,将,profile,赋予用户,概要文件可以被启用或者禁用,Profiles can relate to the,DEFAULT,profile.,User,Password expiration and aging,Password verification,Password history,Account locking,Setting up profiles,Password,管理,Set up password management by using profiles and assigning them to users.,Lock, unlock, and expire accounts using the,CREATE USER,or,ALTER USER,command.,Password limits are always enforced.,To enable password management, run the,utlpwdmg.sql,script as the user,SYS,.,启用,Password,管理,账号锁定,参数,Number of failed login attempts,before lockout of the account,Number of days the account is,locked after the specified number,of failed login attempts,FAILED_LOGIN_ATTEMPTS,PASSWORD_LOCK_TIME,说明,参数值,Lifetime of the password in days,after which the password expires,Grace period in days for changing,the password,after the first,successful login after the password,has expired,PASSWORD_LIFE_TIME,PASSWORD_GRACE_TIME,说明,Password,过期,Password,历史,Parameter,Number of days before a,password can be reused,Maximum number of changes,required before a password,can be reused,PASSWORD_REUSE_TIME,PASSWORD_REUSE_MAX,Description,Password,校验,Parameter,PL/SQL function that performs a,password complexity check,before a password is assigned,PASSWORD_VERIFY_FUNCTION,Description,用户定义的,Password,函数,This function must be created in the,SYS,schema and must have the following specification:,function_name(,userid_parameter,IN VARCHAR2(30),password_parameter,IN VARCHAR2(30),old_password_parameter,IN,VARCHAR2(30),RETURN BOOLEAN,Password,校验函数,VERIFY_FUNCTION,Minimum length is four characters.,Password should not be equal to username.,Password should have at least one alphabetic, one numeric, and one special character.,Password should differ from the previous password by at least three letters.,CREATE PROFILE grace_5 LIMIT,FAILED_LOGIN_ATTEMPTS 3,PASSWORD_LOCK_TIME UNLIMITED,PASSWORD_LIFE_TIME 30,PASSWORD_REUSE_TIME 30,PASSWORD_VERIFY_FUNCTION verify_function,PASSWORD_GRACE_TIME 5;,设置,Password,选项：创建,Profile,ALTER PROFILE default LIMIT,FAILED_LOGIN_ATTEMPTS 3,PASSWORD_LIFE_TIME 60,PASSWORD_GRACE_TIME 10;,设置,Password,选项：修改,Profile,Use,ALTER PROFILE,to change password limits.,DROP PROFILE developer_prof;,DROP PROFILE developer_prof CASCADE;,删除,Profile,Drop the profile using,DROP PROFILE,command.,DEFAULT,profile cannot be dropped.,CASCADE,revokes the profile from the user to whom it was assigned.,监控锁资源的争用,锁机制,锁机制的根本目的是：使对共享资源的访问串行化，比如行记录、数据库对象的定义等。,Automatic management,Exclusive and Share lock modes,Locks held until commit or rollback operationsare performed,两种类型的锁,DML or data locks:,Table-level locks,Row-level locks,DDL or dictionary locks,(,TM),(,TX),TM,锁和,TX,锁,A DML transaction gets at least two locks:,A shared table lock,An exclusive row lock,select,*,from,my_test,for,update,;,select,*,from,v$lock,where,sid =,1216,;,SIDTYPELMODEREQUESTCTIMEBLOCK,- - - - - -,1216TX608082,1216TM308082,Enqueue,机制,The enqueue mechanism keeps track of:,Users waiting for locks,The requested lock mode,The order in which users requested the lock,DDL,锁,Exclusive DDL locks are required for:,DROP TABLE,statements,ALTER TABLE,statements,(The lock is released when the DDL statement completes.),Shared DDL locks are required for:,CREATE PROCEDURE,statements,AUDIT,statements,(The lock is released when the DDL parse completes.),锁争用的常见原因,Uncommitted changes,Bad application design,监控锁活动的工具,Transaction 1,UPDATE employeesSET salary =salary x 1.1;,Transaction 2,Transaction 3,v$lock,v$locked_object,dba_waiters,dba_blockers,UPDATE employeesSET salary =salary x 1.1WHERE empno = 1000;,UPDATE employeesSET salary = salary x 1.1WHERE empno = 2000;,select,*,from,v$session s,where,s.lockwait,is,not,null,;,select,*,from,v$session_wait w,where,w.event,like,enq%,;,select,*,from,dba_waiters;,WAITING_SESSIONHOLDING_SESSIONLOCK_TYPEMODE_HELDMODE_REQUESTED,- - - - -,13201216TransactionExclusiveExclusive,Waiting Lock,的会话,使用下面的语句察看是否有会话在等待锁资源：,select,*,from,v$lock l,where,l.,block,=,1,;,SIDTYPELMODEREQUESTCTIMEBLOCK,- - - - - -,1216TX604561,-,察看,blocking,会话是否在等待,select,*,from,v$session_wait where sid =,1216,;,SIDSEQ#EVENT,-,12161155,SQL*Net message from client,Blocking,会话,察看,Blocking,会话的信息：,select,/*+ rule */,l.session_id, l.os_user_name, l.locked_mode, o.owner,o.object_name,from,v$locked_object l, dba_objects o,where,l.object_id = o.object_id;,SESSION_IDOS_USER_NAMELOCKED_MODEOBJECT_NAME,- - - -,1251usr_zb3T_ST_MODULE_MONITOR,1373hzzcgx3T_USAGE_TOTAL_200701,1373hzzcgx3T_USAGE_TOTAL_200701,当前被锁定的对象,察看当前有哪些对象被锁定：,SQL,语句调优,优化,SQL,的思路,优化,SQL,要从以下三个方面入手：,访问路径：,Access paths are ways in which data is retrieved from the database.,联结方法,Joins are statements that retrieve data from more than one table.,联结次序,优化,SQL,要的手段：使用,hint,SQL,的执行计划,一个,SQL,语句执行计划的示例：,常用的访问路径,Full Table Scan,/*+ full( a ) */,Rowid Scan,Index Unique Scan,Index Range Scan,/*+ index( a idx_name ) */,Index Range Scans Descending,Index Full Scan,Index Fast Full Scan,/*+ index_ffs( a idx_name ) */,常用的联结方法,Nested Loop Join,Hash Join,Sort Merge Join,Nested Loop Join,Nested Loop Join,的执行步骤：,优化器确定驱动表，或者叫,outter table,另一个表作为,innter table,对于,outter table,中的每一行记录，,Oracle,用其作为条件访问,innter table。,在执行计划中：,NESTED LOOPS,outer_loop,inner_loop,效率最高的条件：两个表的数据量较小；或者驱动表的数据量较小并且联接条件可以高效的访问,inner table,Nested Loop Join,示例,SELECT e.employee_id, e.salary, j.job_title,FROM employees e, jobs j,WHERE e.employee_id 103,AND e.job_id = j.job_id;,| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|,|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|,| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|,|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |,用来指定,Nested Loop Join,的,Hint,/*+,use_nl( a b ) */,Hash Join,Hash joins are used for joining large data sets.,不涉及到关联次序,/*+,use_hash( a b ) */,SELECT o.customer_id, l.unit_price * l.quantity,FROM orders o ,order_items l,WHERE l.order_id = o.order_id;,-,| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|,-,| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|,|* 1 | HASH JOIN | | 665 | 13300 | 8 (25)|,| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|,| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|,-,Sort Merge Join,Hash joins generally perform better than sort merge joins.,On the other hand, sort merge joins can perform better than hash joins if both of the following conditions exist:,The row sources are sorted already.,A sort operation does not have to be done.,/*+ use_merge( a b ) */,