Oracle数据库DBA基本技能.ppt

主要内容,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 服务器的主要组件,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,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 Log Buffer,Shared Pool,Data Dictionary Cache,Library Cache,DBWR,SMON,PMON,CKPT,LGWR,Others,Database Buffer 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,建立一个数据库连接,Connecting to an Oracle instance: Establishing a user connection Creating a session,Session created,Database user,User process,Server process,Connection established,用户进程,A program that requests interaction with the Oracle server Must first establish a connection Does not interact directly with the Oracle server,Database user,Server process,User process,Connection established,服务器进程,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,User process,Server process,服务器进程,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 用户 进程的名称为：oracleSID 可以被安全的 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, gram from v$process p, v$session s where s.paddr = p.addr and p.spid = 561246;,察看 Top CPU 进程 找到该进程的 SID,服务器进程,- 察看会话的属性 select s.sid, s.status, gram, 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 = 1561; SID STATUS TERMINAL MACHINE PROGRAM - - - - - 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: DBWn PMON CKPT LGWR SMON Optional background processes: ARCn LMDn QMNn CJQ0 LMON RECO Dnnn LMS Snnn LCKn Pnnn,后台进程,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_NAME TOTAL_BLOCKS USED_BLOCKS - - - TEMP 4420864 15104 select s.sid, gram, s.machine, t.extents, t.blocks from v$tempseg_usage t, v$session s where t.session_addr = s.saddr; SID PROGRAM MACHINE EXTENTS BLOCKS - - - - - 1247 plsqldev.exe JSJZXZXY 3 384,临时表空间中的空间管理,在 OPS/RAC 中，当一个实例需要临时空间并且 SEP 中没有可用的 extent 时： 或者增长临时段 或者系统自动从另一个实例 re-assign extent 增大参数 PGA_AGGREGAGE_TARGET 参数值以尽量避免磁盘排序,select * from v$sysstat where name like %sort%; STATISTIC# NAME CLASS VALUE STAT_ID - - - - - 312 sorts (memory) 64 79700987 2091983730 313 sorts (disk) 64 6900 2533123502 314 sorts (rows) 64 60395446874 3757672740,加快索引的创建,加快索引的创建,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; OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME - - - - - BILL IND_CHARGE_ID NORMAL BILL T_RC_INSTANCE BILL IND_CHARGE_ID0 NORMAL BILL T_RC_INSTANCE,- 查找分区索引 select i.index_owner, i.index_name, i.partition_name from dba_ind_partitions i where status = UNUSABLE;,使用 SPFILE 作为初始化参数文件,初始化参数文件,From Oracle9i 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 Log Buffer,Shared Pool,Data Dictionary Cache,Library Cache,DBW0,SMON,PMON,CKPT,LGWR,Others,Database Buffer 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,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 管理,账号锁定,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 校验,用户定义的 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 operations are 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; SID TYPE LMODE REQUEST CTIME BLOCK - - - - - - 1216 TX 6 0 808 2 1216 TM 3 0 808 2,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 employees SET salary = salary x 1.1;,Transaction 2,Transaction 3,v$lock v$locked_object dba_waiters dba_blockers,UPDATE employees SET salary = salary x 1.1 WHERE empno = 1000;,UPDATE employees SET salary = salary x 1.1 WHERE 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_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED - - - - - 1320 1216 Transaction Exclusive Exclusive,Waiting Lock 的会话,使用下面的语句察看是否有会话在等待锁资源：,select * from v$lock l where l.block = 1; SID TYPE LMODE REQUEST CTIME BLOCK - - - - - - 1216 TX 6 0 456 1 - 察看 blocking 会话是否在等待 select * from v$session_wait where sid = 1216; SID SEQ# EVENT - - - 1216 1155 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_ID OS_USER_NAME LOCKED_MODE OBJECT_NAME - - - - 1251 usr_zb 3 T_ST_MODULE_MONITOR 1373 hzzcgx 3 T_USAGE_TOTAL_200701 1373 hzzcgx 3 T_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