Oracle數(shù)據(jù)庫DBA基本技能.ppt

主要內(nèi)容,Oracle 數(shù)據(jù)庫的組成結(jié)構(gòu) 管理臨時表空間 加快索引的創(chuàng)建 使用 SPFILE 作為初始化參數(shù)文件 使用 Profile 加強(qiáng)數(shù)據(jù)庫安全 監(jiān)控系統(tǒng)的鎖資源爭用 SQL語句調(diào)優(yōu),Oracle 數(shù)據(jù)庫的基本結(jié)構(gòu),Oracle 服務(wù)器,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 服務(wù)器的主要組件,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 實(shí)例,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,建立一個數(shù)據(jù)庫連接,Connecting to an Oracle instance: Establishing a user connection Creating a session,Session created,Database user,User process,Server process,Connection established,用戶進(jìn)程,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,服務(wù)器進(jìn)程,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,服務(wù)器進(jìn)程,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),進(jìn)程的所有者通常為 oracle 用戶 進(jìn)程的名稱為：oracleSID 可以被安全的 kill,服務(wù)器進(jìn)程,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 -執(zhí)行查詢 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 進(jìn)程 找到該進(jìn)程的 SID,服務(wù)器進(jìn)程,- 察看會話的屬性 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 菏澤計(jì)費(fèi) WORKGROUP菏澤計(jì)費(fèi) plsqldev.exe - 察看會話正在執(zhí)行的 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;,根據(jù) SID 察看會話的屬性 察看會話正在執(zhí)行的 SQL,服務(wù)器進(jìn)程,- 殺死服務(wù)器進(jìn)程 ps -ef | grep -v grep | grep LOCAL=NO | awk print $2| xargs kill -9,殺死服務(wù)器進(jìn)程，以加快實(shí)例的關(guān)閉,后臺進(jìn)程,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,后臺進(jìn)程,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,進(jìn)程的所有者通常為 oracle 用戶 進(jìn)程的名稱為：ora_后臺進(jìn)程類型_SID 被 kill 后可能會導(dǎo)致實(shí)例終止,管理臨時表空間,臨時表空間中的空間管理,OPS/RAC 中的每個實(shí)例僅創(chuàng)建一個臨時段，實(shí)例中的所有進(jìn)程將共享該排序段 實(shí)例啟動后第一個利用磁盤執(zhí)行排序操作的進(jìn)程創(chuàng)建臨時段；實(shí)例關(guān)閉后，系統(tǒng)將清空臨時表空間 臨時空間的分配以 extent 為單位進(jìn)行，并通過 SGA 中的Sort Extent Pool 對這些 extents 進(jìn)行管理。 每個實(shí)例會維護(hù)一個屬于自己的 SEP，并通過視圖v$sort_segment 向用戶開放,臨時表空間中的空間管理,臨時表空間已經(jīng) 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 中，當(dāng)一個實(shí)例需要臨時空間并且 SEP 中沒有可用的 extent 時： 或者增長臨時段 或者系統(tǒng)自動從另一個實(shí)例 re-assign extent 增大參數(shù) PGA_AGGREGAGE_TARGET 參數(shù)值以盡量避免磁盤排序,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,加快索引的創(chuàng)建,加快索引的創(chuàng)建,alter session set db_file_multiblock_read_count = 128; alter index bill.ind_charge_id rebuild parallel 5 nologging; create index parallel 5 nologging;,創(chuàng)建超大分區(qū)表的本地索引,create index idx_subs_score_detail_attr_sid on subs_score_detail_attr( subsoid, region,) local tablespace inx unusable;,首先創(chuàng)建 UNUSABLE 狀態(tài)的索引 然后利用多個進(jìn)程，rebuild 失效狀態(tài)的索引分區(qū),能夠利用 nologging 參數(shù)的操作,CREATE TABLE AS SELECT INSERT TABLE SELECT CREATE INDEX REBULD INDEX or INDEX PARTITION MOVE TABLE or TABLE PARTITION SQL*Loader direct-path load,查找失效的索引,- 查找非分區(qū)索引 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,- 查找分區(qū)索引 select i.index_owner, i.index_name, i.partition_name from dba_ind_partitions i where status = UNUSABLE;,使用 SPFILE 作為初始化參數(shù)文件,初始化參數(shù)文件,From Oracle9i onwards, there are two types of initialization parameter file used: Static parameter file, PFILE Persistent server parameter file, SPFILE,spfiledb01.ora,初始化參數(shù)文件,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,文本文件 通過文本編輯器進(jìn)行修改 采用手工的方式進(jìn)行修改 所作的修改在實(shí)例下次啟動時生效 僅在實(shí)例啟動時打開 確省的存取目錄是 $ORACLE_HOME/dbs,SPFILE spfileSID.ora,二進(jìn)制文件 通過 Oracle 服務(wù)器進(jìn)行修改 總是存放在服務(wù)器上 Ability to make changes persistent across shut down and start up,創(chuàng)建 SPFILE,通過 PFILE 創(chuàng)建 SPFILE where SPFILE-NAME: SPFILE to be created PFILE-NAME: PFILE creating the SPFILE 可以在實(shí)例啟動之前或者之后執(zhí)行,CREATE SPFILE = $ORACLE_HOME/dbs/spfileDBA01.ora FROM PFILE = $ORACLE_HOME/dbs/initDBA01.ora;,創(chuàng)建 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.,不指定文件名，系統(tǒng)將使用缺省值，或者 spfile 參數(shù)值,SPFILE 內(nèi)容示例,*.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,檢查當(dāng)前是否使用了 SPFILE,察看初始化參數(shù) spfile,sqlplus / as sysdba SQL show parameter spfile NAME TYPE VALUE - - - spfile string /dev/rjf_spfile,修改 SPFILE 中的參數(shù),修改參數(shù)值 指明是臨時修改還是永久保存 重設(shè)參數(shù)值,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 加強(qiáng)數(shù)據(jù)庫安全,使用 Profile 加強(qiáng)數(shù)據(jù)庫安全,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 校驗(yàn),用戶定義的 Password 函數(shù),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 校驗(yàn)函數(shù) 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;,設(shè)置 Password 選項(xiàng)：創(chuàng)建 Profile,ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LIFE_TIME 60 PASSWORD_GRACE_TIME 10;,設(shè)置 Password 選項(xiàng)：修改 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.,監(jiān)控鎖資源的爭用,鎖機(jī)制,鎖機(jī)制的根本目的是：使對共享資源的訪問串行化，比如行記錄、數(shù)據(jù)庫對象的定義等。 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 機(jī)制,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,監(jiān)控鎖活動的工具,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,當(dāng)前被鎖定的對象,察看當(dāng)前有哪些對象被鎖定：,SQL 語句調(diào)優(yōu),優(yōu)化 SQL 的思路,優(yōu)化 SQL 要從以下三個方面入手： 訪問路徑： Access paths are ways in which data is retrieved from the database. 聯(lián)結(jié)方法 Joins are statements that retrieve data from more than one table. 聯(lián)結(jié)次序 優(yōu)化 SQL 要的手段：使用 hint,SQL 的執(zhí)行計(jì)劃,一個 SQL 語句執(zhí)行計(jì)劃的示例：,常用的訪問路徑,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 ) */,常用的聯(lián)結(jié)方法,Nested Loop Join Hash Join Sort Merge Join,Nested Loop Join,Nested Loop Join