Oracle Database Management Basic 1

Oracle Database Management Basic 1

• 1.
  ORACLE 申建忠 Frank Oracle OCM 9i/10g/11g
• 2.
  Oracle&Hardware CPUs MEMORY DISKs INSTANCE DATABASE
• 3.
  Oracle&OS Background Processes Oracle Database Server Processes OracleSoftware OS CPUs MEMORY DISKs Listener SYSTEM Global Area (SGA) (PGA) PROGRAM Global Area (PGA) Oracle Server
• 4.
  Oracle&OS Background Processes Server Processes PROGRAM Global Area (PGA) /u02/oradata/orcl/control01.ctl /u02/oradata/orcl/system01.dbf /u02/oradata/orcl/sysaux01.dbf /u02/oradata/orcl/undotbs1.dbf /u02/oradata/orcl/temp01.dbf /u02/oradata/orcl/users01.dbf /u02/oradata/orcl/log01a.log /u02/oradata/orcl/log02a.log OracleDatabase OracleSoftware OS CPUs MEMORY DISKs Listener SYSTEM Global Area (SGA) (PGA) /u01/app/oracle/product/11.2.0/dbhome_1
• 5.
  INSTANCE Shared PoolBuffer Cache Large Pool Java Pool Streams Pool SMON PMON DBWR LGWR CKPT RECO ARCH Log Buffer Group1 Header Header Header Header Datafiles Group2 Controlfile LogFiles Archived logfile ParameterFile Password File Alert Logfile Trace File DATABASE User Process Server Process System Global Area Background Processes Program Global Area
• 6.
  Memory Type Sharefor Server and Background processes System Global Area (SGA) Private by Server and Background processes Program Global Area (PGA)
• 7.
  Automatic Memory Management(AMM) 11g MEMORY_MAX_TARGET MEMORY_TARGET SGA_TARGET + PGA_AGGREGATE_TARGET 10g SGA_TARGET 9i SGA_MAX_SIZE PGA_AGGREGATE_TARGET
• 8.
  SGA 變動 ⾃自動管理 STREAMS_POOL_SIZE JAVA_POOL_SIZE LARGE_POOL_SIZE SHARED_POOL_SIZE DB_CACHE_SIZE ⼈人為管理 DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE DB_2K_CACHE_SIZE DB_4K_CACHE_SIZE DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE 固定 LOG_BUFFER FIXED SGA PGA ⾃自動管理PGA_AGGREGATE_TARGET MEMORY_TARGET SGA_TARGET
• 9.
  PGA Large BufferCache Pool SMO N Shared Pool Statistics Java Pool Streams Pool Fixed SGA Log Buffer Server process PGA PGA PMO N PGA DBW R PGA LGW R PGA CKPT PGA ARCH PGA MMO N PGA MMA N Server process PGA Server process PGA Server process PGA Server process PGA MEMORY_TARGET PGA_AGGREGATE_TARGET SGA_TARGET Memory Advisors 取得 呼叫 建議 調整 Server 記錄 Parameter file User process User process User process User process User process
• 10.
  QUIZ MEMORY_TARGET=500M andSGA_TARGET=0 MEMORY_TARGET=500M and SGA_TARGET=200M MEMORY_TARGET=0 and SGA_TARGET=200M MEMORY_TARGET=500M and SGA_TARGET=200M and SHARED_POOL_SIZE=100M MEMORY_TARGET=0 and SGA_TARGET=200M and SHARED_POOL_SIZE=100M MEMORY_TARGET=0 and SGA_TARGET=0 and SHARED_POOL_SIZE=100M
• 11.
  STARUP Stage SQL>ALTER DATABASE OPEN ; SQL> ALTER DATABASE MOUNT ; SQL> STARTUP NOMOUNT ; SHUTDOWN NOMOUNT MOUNT OPEN SQL> STARTUP OPEN ;
• 12.
  Header Header HeaderHeader Datafiles Group1 Group2 Controlfile LogFiles ParameterFile Password File Archived Alert Logfile logfile Trace File INSTANCE NOT EXISTS
• 13.
  NOMOUNT Shared PoolBuffer Cache Large Pool Java Pool Streams Pool Log Buffer SMON PMON DBWR LGWR CKPT RECO ARCH VKTM Header Header Header Header Datafiles Group1 Group2 Controlfile LogFiles 3.使⽤用Parameter File開啟INSTANCE ParameterFile 2.找到 ParameterFile Password File 1.取得SYSDBA
• 14.
  Ways to getSYSDBA OS Group Authentication 安裝Oracle軟體時指定 $ORACLE_HOME/rdbms/lib/config.c Password File Authentication $ORACLE_HOME/dbs/orapw$ORACLE_SID AS SYSDBA 屬於DBA Group Passwo rd File驗證密碼成 功 Insufficient Privilege %ORACLE_HOME%/database/pwd%ORACLE_SID%.ora Get Yes SYSDBA Yes No No
• 15.
  [oracle@oracleDB ~]$ id uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba), 54323(oper),54324(asmdba),54325(asmoper),54326(asmadmin) ! [oracle@oracleDB ~]$ cat $ORACLE_HOME/rdbms/lib/config.c ! /* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */ /* Refer to the Installation and User's Guide for further information. */ ! /* IMPORTANT: this file needs to be in sync with rdbms/src/server/osds/config.c, specifically regarding the number of elements in the ss_dba_grp array. */ ! #define SS_DBA_GRP "dba" #define SS_OPER_GRP "oper" #define SS_ASM_GRP "" ! char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};
• 17.
  PARAMETER FILE $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfile.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora
• 18.
  orcl.__db_cache_size=100663296 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/u01/app/oracle' orcl.__pga_aggregate_target=180355072 orcl.__sga_target=343932928 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=163577856 orcl.__streams_pool_size=4194304 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u02/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' *.db_2k_cache_size=54525952 *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (DISPATCHERS=2)' *.log_archive_dest_1='location=use_db_recovery_file_dest' *.log_archive_dest_2='location=/u02/oradata/orcl/archive1' *.memory_target=524288000 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'
• 19.
  NOMOUNT Shared PoolBuffer Cache Large Pool Java Pool Streams Pool Log Buffer SMON PMON DBWR LGWR CKPT RECO ARCH VKTM Header Header Header Header Datafiles Group1 Group2 Controlfile LogFiles MOUNT 4.使⽤用Parameter File裡的control_files 內容得知controlfile 的位置
• 20.
  NOMOUNT Shared PoolBuffer Cache Large Pool Java Pool Streams Pool Log Buffer SMON PMON DBWR LGWR CKPT RECO ARCH VKTM Header Header Header Header Datafiles Group1 Group2 Controlfile LogFiles MOUNT OPEN 5.由control file內容得知 datafile/logfile的位置
• 21.
  No/Mount Operation NOMOUNT Create New Database Re-Create Controlfile MOUNT Change Log Mode (Noarchive<->Archive) Recover Database Rename filename(logfile/datafile)
• 22.
  SHUTDOWN Options SQL>SHUTDOWN {NORMAL|TRANSACTIONAL|IMMEDIATE|ABORT} ; NORMAL TRANSACTIONAL IMMEDIATE ABORT
• 23.
  SHUTDOWN NORMAL 1.不允許新的session建⽴立。︒ 2.已經連線的session不受到任何影響。︒進⾏行中的交易可以持續。︒ ⽽而且當現有交易結束後，還是可以開啟新交易。︒ 3.等待所有現存的session結束。︒ 4.DBWR將所有Dirty buffers寫回Datafile,LGWR將Redo Entry寫 到Current Logfile Group。︒接著CKPT將checkpoint資訊(timestamp 與scn)記錄到所有的Datafile Header與Controlfile裡。︒ 5.關閉Datafile與Logfile --close database 6.關閉Controlfile --dismount database 7.結束Background processes，釋放SGA所佔⽤用的記憶體。︒
• 24.
  SHUTDOWN TRANSACTIONAL 1.不允許新的session建⽴立。︒ 2.已經連線的session不受到任何影響。︒進⾏行中的交易可以持續。︒ 但當現有交易結束後，便⾃自動結束此session。︒ 3.等待所有現存的session結束。︒ 4.DBWR將所有Dirty buffers寫回Datafile,LGWR將Redo Entry寫 到Current Logfile Group。︒接著CKPT將checkpoint資訊(timestamp 與scn)記錄到所有的Datafile Header與Controlfile裡。︒ 5.關閉Datafile與Logfile --close database 6.關閉Controlfile --dismount database 7.結束Background processes，釋放SGA所佔⽤用的記憶體。︒
• 25.
  SHUTDOWN IMMEDIATE 1.不允許新的session建⽴立。︒ 2.已經連線的session不受到任何影響。︒但進⾏行中的交易必須 Rollback。︒當現有交易完成Rollback後，此session⾃自動結束。︒ 3.等待所有現存的session結束。︒ 4.DBWR將所有Dirty buffers寫回Datafile,LGWR將Redo Entry寫 到Current Logfile Group。︒接著CKPT將checkpoint資訊(timestamp 與scn)記錄到所有的Datafile Header與Controlfile裡。︒ 5.關閉Datafile與Logfile --close database 6.關閉Controlfile --dismount database 7.結束Background processes，釋放SGA所佔⽤用的記憶體。︒
• 26.
  SHUTDOWN ABORT 1.直接將INSTANCE關閉。︒ 2.Dirty Buffers並沒有被寫回Datafile。︒ 3.Redo Entry也沒有被寫到Logfile。︒ 4.有些交易尚在進⾏行中。︒
• 27.
  INSTANCE Recovery ROLLFORWARD ReCreate Dirty Buffers Data Blocks & Undo Blocks ROLLBACK SHUTDOWN Rollback Uncommitted Transactions NOMOUNT MOUNT OPEN SMON
• 28.
  --Alert Log內容 Completed:ALTER DATABASE MOUNT --資料庫掛載完成 Thu Apr 29 07:56:52 2010 ALTER DATABASE OPEN --準備開啟資料庫,但尚未開啟 Beginning crash recovery of 1 threads --SMON檢查是否要進⾏行Instance Recovery, --藉由檢查儲存在Datafile Header與Controlfile的checkpoint資訊是否⼀一致.若不⼀一致則開始進⾏行Instance復原. Started redo scan --two pass scan的first pass redo scan(由controlfile所記錄的checkpoint position開始) Completed redo scan --checkpoint position其實是checkpoint queue裡最⽼老的Dirty buffer的Redo entry位於Logfile的位置 read 19191 KB redo, 2730 data blocks need recovery --找出需要復原的Dirty buffers個數 Started redo application at --two pass scan的second path read scan Thread 1: logseq 17, block 44708 --redo logfile的block size=512 bytes. --redo byte address(checkpoint position)記錄在controlfile裡(由ckpt定期更新) Recovery of Online Redo Log: Thread 1 Group 2 Seq 17 Reading mem 0 --RollForward開始 Mem# 0: +DATA/orcl/onlinelog/group_2.262.717353923 Mem# 1: +FRA/orcl/onlinelog/group_2.258.717353927 Completed redo application of 16.43MB Completed crash recovery at --RollForward完成 Thread 1: logseq 17, block 83091, scn 1081446 2730 data blocks read, 2729 data blocks written, 19191 redo k-bytes read Thread 1 advanced to log sequence 18 (thread open) Thread 1 opened at log sequence 18 Current log# 3 seq# 18 mem# 0: +DATA/orcl/onlinelog/group_3.263.717353927 Current log# 3 seq# 18 mem# 1: +FRA/orcl/onlinelog/group_3.259.717353933 Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Thu Apr 29 07:57:10 2010 SMON: enabling cache recovery --修復bootstrap segment Successfully onlined Undo Tablespace 9. Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery --Rollback開始 Database Characterset is ZHT16MSWIN950 No Resource Manager plan active SMON: Parallel transaction recovery tried replication_dependency_tracking turned off (no async multimaster replication found) Thu Apr 29 07:57:18 2010 Starting background process QMNC Thu Apr 29 07:57:18 2010 QMNC started with pid=30, OS id=12191 Completed: ALTER DATABASE OPEN --資料庫開啓完成,但是Rollback操作可能還有部分尚未完成
• 29.
  Connection&Session Connection userprocess與server process所建⽴立的連線 IPC(Inter Processes Communication) C/S(Client Server) Session server process透過user process所提供的user name與password，建⽴立與Instance的階段作業
• 30.
  $ sqlplus hr/hr@192.168.56.102:1521/orcl SQL> ! SQL> select sid,serial#,saddr,paddr from v$session where username='HR'; ! SID SERIAL# SADDR PADDR ---------- ---------- ---------------- ---------------- 29 9 000000007EA5A868 000000007E8A3DF0 ! SQL> set autotrace on !! SQL> select sid,serial#,saddr,paddr from v$session where username='HR'; ! SID SERIAL# SADDR PADDR ---------- ---------- ---------------- ---------------- 19 37 000000007EA784F8 000000007E8A3DF0 29 9 000000007EA5A868 000000007E8A3DF0 ! SQL> disconnect SQL> connect sh/sh@192.168.56.102:1521/orcl ! SQL> select sid,serial#,saddr,paddr from v$session where username='SH'; ! SID SERIAL# SADDR PADDR ---------- ---------- ---------------- ---------------- 30 63 000000007EA578C0 000000007E8A3DF0
• 31.
  Process Type UserProcess 產⽣生SQL statement Server Process 代表User Process執⾏行SQL statement,並回傳SELECT結果給 User Process Background Process 必要Processes SMON,PMON,DBWR,LGWR,CKPT,RECO Listener Process
• 32.
  PMO N INSTANCE orcl orcl DATABASE Inter Processes Communication User Process Server Process oracleDB orcl.uuu.com.tw Service Name Instance Name Database Name 3.Session 1 2. Connection $ export ORACLE_SID=orcl $ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 $ sqlplus hr/hr SQL>
• 33.
  6. Connection 7.Session orcl.uuu.com.tw orcl 3 4 LISTENER PMO N INSTANCE orcl orcl DATABASE $ lsnrctl start LISTENER $ORACLE_HOME/network/admin/listener.ora User Process $sqlplus hr/hr@orcl 5 $ORACLE_HOME/network/admin/sqlnet.ora NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT) $ORACLE_HOME/network/admin/tnsnames.ora Server Process LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = oracleDB)(PORT = 1521)) ) ) oracleDB ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracleDB)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.uuu.com.tw) ) ) 1521 Naming Method Local Naming orcl.uuu.com.tw Service Name Instance Name Database Name 1 2 Client/Server frankMBP Dynamic Registration
• 34.
  Shared Servers dispatchers=’(protocol=tcp)(dispatchers=2)’shared_servers=3 PMO N Server Process Server Process Server Process Dispatcher2 Dispatcher1 user process user process user process user process server process Large Pool SQL3 SQL2 SQL1 SQL1 SQL2 SQL1 SQL2 user process user process SQL3 SQL3 Result1 Result3 Result1 Result3 Result1 Result3 SQL4 Result4 Request Queue Response Queue 1 Response Queue 2 Shared Processes Dedicated Processes LISTENER
• 35.
  SQL> select sid,serial#,username,service_namefrom v$session; --每個Session有⼀一筆記錄 ! SID SERIAL# USERNAME SERVICE_NAME ---------- ---------- -------------- ------------------------------ 1 36 SYSMAN orcl --透過listener建⽴立連線，其所要求的service name為orcl 2 1 SYS$BACKGROUND --Background process所建⽴立的連線 3 1 SYS$BACKGROUND 4 1 SYS$BACKGROUND 5 1 SYS$BACKGROUND 6 1 SYS$BACKGROUND 7 1 SYS$BACKGROUND 8 1 SYS$BACKGROUND 9 1 SYS$BACKGROUND 10 1 SYS$BACKGROUND 22 127 SYSMAN orcl 24 467 SYSMAN orcl 33 31 DBSNMP SYS$USERS 35 8 SYSMAN orcl 39 533 DBSNMP SYS$USERS --使⽤用IPC所建⽴立的連線 42 683 HR orcl 43 658 SYSMAN orcl 44 274 DBSNMP SYS$USERS 45 98 SYS$BACKGROUND
• 36.
  Background Process [oracle@oraDB~]$ ps aux| grep ora_ | grep _orcl oracle 22569 0.0 0.8 750600 18080 ? Ss 09:56 0:00 ora_w000_orcl oracle 23400 0.0 0.9 751112 18888 ? Ss 10:06 0:00 ora_w001_orcl oracle 23405 0.0 0.9 751112 18648 ? Ss 10:06 0:00 ora_w002_orcl oracle 23513 0.4 1.5 751112 31000 ? Ds 10:07 0:00 ora_m000_orcl oracle 29276 0.0 0.9 752896 20084 ? Ss Jan08 0:24 ora_pmon_orcl oracle 29280 0.0 0.8 750604 16464 ? Ss Jan08 1:49 ora_psp0_orcl oracle 29284 9.7 0.7 750604 16068 ? Ss Jan08 5:02 ora_vktm_orcl oracle 29290 0.0 0.7 750604 16336 ? Ss Jan08 0:10 ora_gen0_orcl oracle 29294 0.0 0.7 750604 16060 ? Ss Jan08 0:13 ora_diag_orcl oracle 29298 0.0 2.0 751116 41756 ? Ss Jan08 0:10 ora_dbrm_orcl oracle 29302 0.0 1.0 752140 20768 ? Ss Jan08 1:01 ora_dia0_orcl oracle 29306 0.0 2.0 750604 42072 ? Ss Jan08 0:09 ora_mman_orcl oracle 29310 0.0 4.8 758152 100212 ? Ss Jan08 0:27 ora_dbw0_orcl oracle 29314 0.0 1.3 766156 26808 ? Ss Jan08 0:33 ora_lgwr_orcl oracle 29318 0.0 1.1 750604 24388 ? Ss Jan08 1:25 ora_ckpt_orcl oracle 29322 0.0 8.4 754196 173024 ? Ss Jan08 0:18 ora_smon_orcl oracle 29326 0.0 1.5 751116 31544 ? Ss Jan08 0:03 ora_reco_orcl oracle 29330 0.0 6.1 756580 125636 ? Ss Jan08 0:48 ora_mmon_orcl oracle 29334 0.0 1.7 752356 36488 ? Ss Jan08 0:59 ora_mmnl_orcl oracle 29338 0.0 0.7 752636 16028 ? Ss Jan08 0:07 ora_d000_orcl oracle 29342 0.0 0.7 751800 14868 ? Ss Jan08 0:06 ora_s000_orcl oracle 29785 0.0 0.8 750604 17724 ? Ss Jan08 0:03 ora_qmnc_orcl oracle 29889 0.0 7.0 756312 144344 ? Ss Jan08 0:32 ora_cjq0_orcl oracle 29893 0.0 1.9 752268 40884 ? Ss Jan08 0:03 ora_q000_orcl oracle 29897 0.0 3.2 756140 65900 ? Ss Jan08 0:03 ora_q001_orcl oracle 30496 0.0 1.2 751116 25208 ? Ss Jan08 0:09 ora_smco_orcl
• 37.
  Server Process [oracle@oraDB~]$ ps aux| grep oracleorcl oracle 24862 0.0 1.4 754428 28856 ? Ss 10:22 0:01 oracleorcl (LOCAL=NO) oracle 24867 0.0 1.0 754408 20828 ? Ss 10:22 0:00 oracleorcl (LOCAL=NO) oracle 24920 0.0 1.5 760532 31064 ? Ss 10:23 0:01 oracleorcl (LOCAL=NO) oracle 24937 0.0 1.0 754368 20608 ? Ss 10:23 0:00 oracleorcl (LOCAL=NO) oracle 24941 0.0 0.7 753336 16176 ? Ss 10:23 0:00 oracleorcl (LOCAL=NO) oracle 24946 0.0 3.9 757476 80228 ? Ss 10:23 0:01 oracleorcl (LOCAL=NO) oracle 25022 3.7 1.0 758580 22540 ? Ss 10:24 1:36 oracleorcl (DESCRIPTION=(LOCAL=oracle 25243 0.1 6.1 768008 126452 ? Ss 10:27 0:04 oracleorcl (LOCAL=NO) oracle 25291 0.0 1.5 758500 32720 ? Ss 10:27 0:00 oracleorcl (LOCAL=NO) oracle 25884 0.2 4.2 756544 88324 ? Ss 10:29 0:05 oracleorcl (LOCAL=NO) oracle 27580 74.9 2.2 774840 47180 ? Rs 10:51 12:50 oracleorcl (DESCRIPTION=(LOCAL=oracle 27681 0.0 1.0 766648 22544 ? Ss 10:51 0:00 oracleorcl (DESCRIPTION=(LOCAL=oracle 27714 0.0 1.0 766648 22052 ? Ss 10:51 0:00 oracleorcl (DESCRIPTION=(LOCAL=oracle 27715 0.0 1.0 766648 22044 ? Ss 10:51 0:00 oracleorcl (DESCRIPTION=(LOCAL=oracle 27725 0.0 1.0 766648 22060 ? Ss 10:51 0:00 oracleorcl (DESCRIPTION=(LOCAL=oracle 27735 0.0 1.0 766648 22232 ? Ss 10:51 0:00 oracleorcl (DESCRIPTION=(LOCAL=oracle 27745 0.0 1.0 766648 22256 ? Ss 10:51 0:00 oracleorcl (DESCRIPTION=(LOCAL=
• 38.
  [oracle@oracleDB ~]$ ipcs-m ! ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 65536 oracle 600 393216 2 dest 0x00000000 98305 oracle 600 393216 2 dest 0x00000000 131074 oracle 600 393216 2 dest 0x00000000 163843 oracle 600 393216 2 dest 0x00000000 196612 oracle 600 393216 2 dest 0x00000000 229381 oracle 600 393216 2 dest 0x00000000 262150 oracle 600 393216 2 dest 0x00000000 294919 oracle 600 393216 2 dest 0x00000000 327688 oracle 600 393216 2 dest 0x00000000 360457 oracle 600 393216 2 dest 0x00000000 58851338 oracle 660 4096 0 0x00000000 58884107 oracle 660 4096 0 0xfa55c7d8 720908 oracle 660 4096 0 0x00000000 1179661 oracle 600 393216 2 dest 0x00000000 109543438 oracle 777 138880 2 dest 0x42e38fd0 58916879 oracle 660 4096 0 0x00000000 109576208 oracle 777 138880 2 dest 0x00000000 57049105 oracle 777 138880 2 dest
• 39.
  SQL Execution Steps Server Process User Process Oracle Server Parse Execute Fetch (Select only) $ sqlplus hr/hr@orcl SQL> select last_name,salary SQL from employees where employee_id=100; Shared Pool Buffer Cache Log Buffer DML only Datafile LAST_NAME SALARY ------------------------- ---------- King 24000 Result Control file Log File
• 40.
  Shared Pool LibraryCache SQL Text/Parsed Code/Execution Plan Data Dictionary Cache Result Cache SHARED_POOL_SIZE
• 41.
  Hard/Soft Parse ExecutionPlan Query Transformer Estimator Plan Generator Row Source Generator Execution Plan Data Dictionary Parser Optimizer optimizer statistics Hash(SQL) Execution Plan Execution Plan Execution Plan Execution Plan Execution Plan Soft Parse Hard Parse Execution Plan Shared Pool (Library Cache) Syntax analysis Semantic analysis Server Process
• 42.
  Buffer Cache DataBlock Copy From DataFiles Least Recently Used(LRU) DBWR write Dirty Buffer to Datafile DB_CACHE_SIZE Free/Unused Clean Dirty DBWR write back to datafile Server process read from datafile Server process Modify block content
• 43.
  Datafile PGA Select Log Buffer DBWR LGWR Control file Logfile group 1 Logfile group 2 Server Process Execution Plan SMON PMON CKPT RECO 1 A A1 10 J J 10 17 Q Q17 2 B B2 18 R R18 19 S S1 9 3 C C3 7 G G7 11 K K 11 4 D D4 12 L L 12 21 U U 21 6 F F6 13 M M13 22 V V 22 8 H H8 15 O O15 24 X X 24 5 E E5 14 N N 14 23 W W23 9 I I9 16 P P 16 20 T T 20 A B C 1 A A1 2 B B2 3 C C3 4 D D4 5 E F5 6 F E6 7 G G7 8 H H8 9 I I9 10 J J10 11 K K11 12 L L12 13 M M13 14 N N14 15 O O15 16 P P16 17 Q Q17 18 R R18 19 S S19 20 T T20 21 U U21 22 V V22 23 W W23 24 X X24 User Process SELECT c FROM t1 WHERE a=19; 1.Parse S19 3.Fetch Shared Pool Buffer Cache 2 B B2 18 R R18 19 S S1 9 2.Execute
• 44.
  Log Buffer RedoEntry from Server Process’s PGA DML/DDL Circular buffer LOG_BUFFER_SIZE
• 45.
  2.4 Copy RedoEntries to Log Buffer Datafile PGA DML Log Buffer DBWR LGWR Control file Logfile group 1 Logfile group 2 Server Process Execution Plan SMON PMON CKPT RECO 1 A A1 10 J J 10 17 Q Q17 2 B B2 18 R R18 19 S S1 9 3 C C3 7 G G7 11 K K 11 4 D D4 12 L L 12 21 U U 21 6 F F6 13 M M13 22 V V 22 8 H H8 15 O O15 24 X X 24 5 E E5 14 N N 14 23 W W23 9 I I9 16 P P 16 20 T T 20 A B C 1 A A1 2 B B2 3 C C3 4 D D4 5 E F5 6 F E6 7 G G7 8 H H8 9 I I9 10 J J10 11 K K11 12 L L12 13 M M13 14 N N14 15 O O15 16 P P16 17 Q Q17 18 R R18 19 S S19 20 T T20 21 U U21 22 V V22 23 W W23 24 X X24 User Process 1.Parse Shared Pool Buffer Cache 2 B B2 18 R R18 19 S S9 1 UPDATE t1 SET c=‘S91’ WHERE a=19; 2.3 Generated Redo Entries undo S19 data S91 2.Execute 2.1 Get Data Blocks 1 row updated 2.2 Lock Row undo S19 data S91 2.6 Change Date Block 2.5 S19 Copy before image to Undo Block
• 46.
  Datafile PGA COMMIT Log Buffer DBWR LGWR 2.3 Write Redo Entries to Log File Control file Logfile group 1 Logfile group 2 Server Process Execution Plan SMON PMON CKPT RECO 1 A A1 10 J J 10 17 Q Q17 2 B B2 18 R R18 19 S S1 9 3 C C3 7 G G7 11 K K 11 4 D D4 12 L L 12 21 U U 21 6 F F6 13 M M13 22 V V 22 8 H H8 15 O O15 24 X X 24 5 E E5 14 N N 14 23 W W23 9 I I9 16 P P 16 20 T T 20 A B C 1 A A1 2 B B2 3 C C3 4 D D4 5 E F5 6 F E6 7 G G7 8 H H8 9 I I9 10 J J10 11 K K11 12 L L12 13 M M13 14 N N14 15 O O15 16 P P16 17 Q Q17 18 R R18 19 S S91 20 T T20 21 U U21 22 V V22 23 W W23 24 X X24 User Process 1.Parse Shared Pool Buffer Cache 2 B B2 18 R R18 19 S S9 1 S19 COMMIT; commit 2.Execute COMMITTED undo S19 data S91 commit 2.1 Generated Redo Entries 2.2 Copy Redo Entries to Log Buffer undo S19 data S91 commit 2.4 Release Lock
• 47.
  DBWR&COMMIT Datafile PGA Log Buffer DBWR LGWR Control file Logfile group 1 Logfile group 2 Server Process Execution Plan User Process Shared SMON PMON CKPT RECO 1 A A1 10 J J 10 17 Q Q17 2 B B2 18 R R18 19 S S9 1 3 C C3 7 G G7 11 K K 11 4 D D4 12 L L 12 21 U U 21 6 F F6 13 M M13 22 V V 22 8 H H8 15 O O15 24 X X 24 5 E E5 14 N N 14 23 W W23 9 I I9 16 P P 16 20 T T 20 A B C 1 A A1 2 B B2 3 C C3 4 D D4 5 E F5 6 F E6 7 G G7 8 H H8 9 I I9 10 J J10 11 K K11 12 L L12 13 M M13 14 N N14 15 O O15 16 P P16 17 Q Q17 18 R R18 19 S S91 20 T T20 21 U U21 22 V V22 23 W W23 24 X X24 Pool Buffer Cache S19 undo S19 data S91 undo S19 data S91 2 B B2 18 R R18 19 S S9 1 S19
• 48.
  INSTANCE Shared PoolBuffer Cache Large Pool Java Pool Streams Pool SMON PMON DBWR LGWR CKPT RECO ARCH Log Buffer Group1 Header Header Header Header Datafiles Group2 Controlfile LogFiles Archived logfile Program Global Area ParameterFile Password File Alert Logfile Trace File DATABASE User Process Server Process
• 49.
  SCN是⼀一個數字,最快速度在⼀一秒內可以增加16384次(每次加1),Oracle Server使⽤用6 bytes儲存此值,並保證在522年不會⽤用盡。 SCN與時間有3秒差距 SQL> select timestamp_to_scn(to_date('2013-04-20:15:37:00','YYYY-MM-DD:HH24:MI:SS')) from dual; ! TIMESTAMP_TO_SCN(TO_DATE('2013-04-20:15:37:00','YYYY-MM-DD:HH24:MI:SS')) ------------------------------------------------------------------------ 1122536 ! SQL> select timestamp_to_scn(to_date('2013-04-20:15:37:01','YYYY-MM-DD:HH24:MI:SS')) from dual; ! TIMESTAMP_TO_SCN(TO_DATE('2013-04-20:15:37:01','YYYY-MM-DD:HH24:MI:SS')) ------------------------------------------------------------------------ 1122536 ! SQL> select timestamp_to_scn(to_date('2013-04-20:15:37:02','YYYY-MM-DD:HH24:MI:SS')) from dual; ! TIMESTAMP_TO_SCN(TO_DATE('2013-04-20:15:37:02','YYYY-MM-DD:HH24:MI:SS')) ------------------------------------------------------------------------ 1122536 ! SQL> select timestamp_to_scn(to_date('2013-04-20:15:37:03','YYYY-MM-DD:HH24:MI:SS')) from dual; ! TIMESTAMP_TO_SCN(TO_DATE('2013-04-20:15:37:03','YYYY-MM-DD:HH24:MI:SS')) ------------------------------------------------------------------------ 1122537 scn換算為timestamp時,有3秒鐘的誤差 ! SQL> select scn_to_timestamp(1122536) from dual; ! SCN_TO_TIMESTAMP(1122536) --------------------------------------------------------------------------- 20-4⽉月 -13 03.37.00.000000000 下午 ! SQL> select scn_to_timestamp(1122537) from dual; ! SCN_TO_TIMESTAMP(1122537) --------------------------------------------------------------------------- 20-4⽉月 -13 03.37.03.000000000 下午
• 50.
  Log Buffer Con trol file Logfil e group 1 Logfil e group 2 Shared Pool Buffer Cache MMON Data Dictionary Automatic Workload Repository X$ V$XXX DBA_XXX DBA_HIST_XXX
• 51.
  Data Dictionary View DBA_TABLES 資料庫裡所有的表格。︒ ALL_TABLES 查詢此View的Session User所有可以使⽤用的表格。︒ 包含⾃自⼰己擁有與他⼈人擁有但授權此Session User可 以使⽤用的表格。︒ USER_TABLES 查詢此View的Session User所擁有的表格。︒
• 52.
  ! SQL> descdictionary Name Null? Type ---------------------------- -------- ---------------------------- TABLE_NAME VARCHAR2(30) COMMEN VARCHAR2(4000) ! SQL> select substr(table_name,1,4),count(*) 2> from dictionary 3> where substr(table_name,1,4) in ('DBA_','ALL_','USER') 4> group by substr(table_name,1,4); ! SUBSTR(T COUNT(*) ----------------- -------- ALL_ 355 USER 375 DBA 705 ! !
• 53.
  SQL> show user USER is "SYSMAN" SQL> select count(*) from user_tables; ! COUNT(*) ---------- 728 ! SQL> select count(*) from all_tables; ! COUNT(*) ---------- 832 ! SQL> select count(*) from dba_tables; ! COUNT(*) ---------- 2783 SQL> show user USER is "HR" SQL> select count(*) from user_tables; ! COUNT(*) ---------- 7 ! SQL> select count(*) from all_tables; ! COUNT(*) ---------- 106 ! SQL> select count(*) from dba_tables; select count(*) from dba_tables * ERROR at line 1: ORA-00942: table or view does not exist
• 54.
  Dynamic Performance View 資料來源 INSTANCE(⼤大多數)/Control File(少數)/Data Dictionary(極少數) Dynamic 內容持續變動，無法保證⼀一致性讀取。︒ Performance 其中資訊主要⽤用來進⾏行INSTANCE的效能調校。︒ Base Table X$ V_$ Dynamic Performance View V$
• 55.
  SQL> select count(*)from v$sql; ! COUNT(*) ----------------- 847 SQL> desc v$fixed_table Name Null? Type ------------------------------- -------- ------------ NAME VARCHAR2(30) OBJECT_ID NUMBER TYPE VARCHAR2(5) TABLE_NUM NUMBER ! SQL> startup force ORACLE instance started. ! Total System Global Area 418484224 bytes Fixed Size 1336932 bytes Variable Size 318769564 bytes Database Buffers 92274688 bytes Redo Buffers 6103040 bytes Database mounted. Database opened. ! SQL> select count(*) from v$sql; ! COUNT(*) ----------------- 285 --INSTANCE重新啟動,V$的內容將歸零重新累計
• 56.
  DATABASE TABLESPACE SEGMENT EXTENT LOGFILE CONTROLFILE DATAFILE BLOCK OS BLOCK
• 57.
  SQL> select sum(bytes)from dba_data_files 2> where tablespace_name='EXAMPLE'; ! SUM(BYTES) ------------------- 362414080 ! SQL> select sum(bytes) from dba_segments 2> where tablespace_name='EXAMPLE'; ! SUM(BYTES) ------------------- 324141056 ! SQL> select sum(bytes) from dba_extents SUM(DBA_DATA_FILES) != SUM(DBA_EXTENTS) 2> where tablespace_name='EXAMPLE'; ! SUM(BYTES) ------------------- 324141056 WHY?
• 58.
  DBA_TABLESPACES DBA_SEGMENTS DBA_EXTENTS DBA_DATA_FILES DBA_FREE_SPACE SUM(DBA_DATA_FILES) = SUM(DBA_EXTENTS)+SUM(DBA_FREE_SPACE)
• 59.
  SQL> SELECT sum(bytes)FROM dba_data_files WHERE tablespace_name='EXAMPLE'; ! SUM(BYTES) ------------------- ! SQL> SELECT sum(bytes) FROM dba_segments WHERE tablespace_name='EXAMPLE'; ! SUM(BYTES) ------------------- ! SQL> SELECT sum(bytes) FROM dba_extents WHERE tablespace_name='EXAMPLE'; ! SUM(BYTES) ------------------- SUM(DBA_DATA_FILES) != 362414080 324141056 324141056 WHY? SUM(DBA_EXTENTS) + SUM(DBA_FREE_SPACE) ! SQL> SELECT sum(bytes) FROM dba_free_space WHERE tablespace_name='EXAMPLE'; ! SUM(BYTES) ------------------- 37224448 --(362414080-324141056-37224448)=1048576(File Header Bitmap)
• 60.
  Auto Extent Size SQL> CREATE TABLESPACE demots1 2 DATAFILE '/u02/oradata/orcl/demots01.dbf' SIZE 3M 3 EXTENT MANAGEMENT LOCAL 4 AUTOALLOCATE 5 SEGMET SPACE MANAGEMENT AUTO; Bitmap 1 1 0 1M 2M 8 k 8 k 8 k 8 k 8 k 8 k 8 k 8 k extent Extent size: 64K : 1-16 1M : 17-79 8M : 80-200 64M : 201- ... 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K 64 K
• 61.
  Uniform extent size SQL> CREATE TABLESPACE demots2 2 DATAFILE '/u02/oradata/orcl/demots02.dbf' SIZE 3M 3 EXTENT MANAGEMENT LOCAL 4 UNIFORM SIZE 128K 5 SEGMENT SPACE MANAGEMENT AUTO; 1 1 0 Bitmap 1M 2M 8 k 8 k 8 k 8 k 8 k 8 k 8 k 8 k 8 k 8 k 8 k 8 k 8 k 8 k 8 k 8 k extent Extent size:128K 128 K 128 K 128 K 128 K 128 K 128 K 128 K 128 K 128 K 128 K 128 K 128 K 128 K 128 K 128 K 128 K
• 62.
  SQL> CREATE TABLESPACEdemots1 2 DATAFILE '/u02/oradata/orcl/demots01.dbf' SIZE 10M 3 EXTENT MANAGEMENT LOCAL 4 AUTOALLOCATE; ! SQL> SELECT file_id,block_id,bytes,blocks 2 FROM dba_free_space 3 WHERE tablespace_name='DEMOTS'; ! FILE_ID BLOCK_ID BYTES BLOCKS --------------- ----------------- -------------- --------------- 7 128 9437184 1152 ! SQL> SELECT 10*1024*1024-9437184 FROM dual; ! 10*1024*1024-9437184 ----------------------------- 1048576
• 63.
  SQL> CREATE TABLESPACEdemots2 2 DATAFILE '/u02/oradata/orcl/demots02.dbf' SIZE 10M 3 EXTENT MANAGEMENT LOCAL 4 UNIFORM SIZE 128K; ! SQL> SELECT file_id,block_id,bytes,blocks 2 FROM dba_free_space 3 WHERE tablespace_name='DEMOTS2'; ! FILE_ID BLOCK_ID BYTES BLOCKS --------------- ----------------- -------------- --------------- 7 128 9437184 1152 ! SQL> SELECT 10*1024*1024-9437184 FROM dual; ! 10*1024*1024-9437184 ----------------------------- 1048576
• 64.
  SQL> CREATE TABLEhr.t1 2> ( a NUMBER, 3> b VARCHAR2(100)) 4> TABLESPACE demots1; DDL=DML on Data Dictionary 1 11 1 1 1 1 1111111101110010100 x$ktfbue 11 1 1 0 1101111000000000000 obj$ 123 T1 seg$ 123 123 T1 tab$ col$ 123 T1 123 T1 A N 123 T1 B V SQL> INSERT INTO hr.t1 HWM 2> VALUES(1,’A’); 1 A SQL> INSERT INTO hr.t1 2> VALUES(2,’B’); 2 B 3 C SYSTEM DEMOTS1
• 65.
  Delete HR.T1’s DefinitionHR.T1’s Data 1 11 1 1 1 1 1111111101110010100 x$ktfbue 11 1 1 1 1101111000000000000 obj$ 123 T1 seg$ 123 123 T1 tab$ col$ 123 T1 123 T1 A Number 123 T1 B Varchar2 HWM 1 A 2 B 3 C SQL> DELETE hr.t1 2> WHERE a=1; SQL> DELETE hr.t1 2> WHERE b=’D’; 4 D 5 E 6 F SYSTEM DEMOTS1
• 66.
  Truncate HR.T1’s DefinitionHR.T1’s Data 1 11 1 1 1 1 1111111101110010100 x$ktfbue 11 1 1 0 1101111000000000000 obj$ 123 T1 seg$ 123 123 T1 tab$ col$ 123 T1 123 T1 A Number 123 T1 B Varchar2 HWM 1 A 2 B 3 C SQL> truncate table 2> hr.t1; 4 D 5 E 6 F SYSTEM DEMOTS1
• 67.
  Lock Type DDLLock 避免同時間多個Session異動相同表格的表 格定義。︒ DML Lock 避免同時間多個Session異動相同的資料列 避免在異動過程中，其他Session對同⼀一個
• 68.
  DDL Locks TableLevel Lock(TM) eXclusive Lock CREATE/ALTER/DROP TABLE/INDEX/CLUSTER/TABLESPACE Share Lock AUDIT/NOAUDIT/COMMENT CREATE VIEW/PROCEDURE/FUNCTION/PACKAGE/PACKAGE BODY/ TRIGGER CREATE SYNONYM Breakable Parse Lock SQL Statement與PL/SQL物件載入Shared Pool
• 69.
  DML Locks TableLevel Lock(TM) Row eXclusive(RX) Row Level Lock(TX) eXclusive(X)
• 70.
  Get Lock Automatic DDL -> Table Level(TM):X/S DML -> 1:TM:RX 2:TX:X Manual SQL> LOCK TABLE t1 IN {ROW SHARE| ROW EXCLUSIVE|SHARE|SHARE ROW EXCLUSIVE|EXCLUSIVE} MODE;
• 71.
  Lock Table NULLRow Share Row eXclusive Share Share Row eXclusive eXclusive NULL(1) ✔ ✔ ✔ ✔ ✔ ✔ RS(2) ✔ ✔ ✔ ✔ ✔ RX(3) ✔ ✔ ✔ S(4) ✔ ✔ ✔ SRX(5) ✔ ✔ X(6) ✔
• 72.
  Block Structure BlockAddress/Segment Type Transaction Slots Table Directory Row Directory Block Header Command Header Block Overhead Variable Header Row Row Row Row Row Row Row Row Free Space Row Data Row Header Column Data Row Overhead Lock Byte Column Value Column Length Chaining info Number of Columns 5 10001 6 ORACLE 0 20 Administration Workshop 1 5 Bytes 6 Bytes 20 Bytes 4 Column Value Column Value Column Value
• 73.
  Transaction TRANSACTION的組成份⼦子: 多個DMLs指令 ⼀一個DDL ⼀一個DCL
• 74.
  Begin/END of TX Begin of TRANSACTION 上⼀一個TRANSACTION結束後，遇到第⼀一個的DML指令。︒ End of TRANSACTION COMMIT AUTO COMMIT 遇到DDL/DCL指令 正常結束SESSION ROLLBACK AUTO ROLLBACK INSTANCE CRASH SESSION不正常結束
• 75.
  Transaction ID SQL>COMMIT; --結束⽬目前的交易 ! SQL> UPDATE TABLE t1 --開始另⼀一個交易.Transaction ID將先被指定. 2> SET salary=3000 3> WHERE empid=100; ! SQL> SELECT xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,status,ses_addr,xid 2> FROM v$transaction; ! XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN STATUS SES_ADDR XID ---------------- --------------- -------------- ------------- --------------- --------------- ------------ ------------------------- -------------------------- 3 31 1253 3 1198 354 ACTIVE 000000007E454C58 03001F00E5040000 ! SQL> SELECT sid,type,id1,id2,lmode,request 2> FROM v$lock 3> WHERE type IN ('TM','TX'); ! SID TY ID1 ID2 LMODE REQUEST ----------- ----- ---------- ---------- ------------- --------------- 125 TM 77329 0 3 0 125 TX 196639 1253 6 0 XIDUSN=trunc(196639/power(2,16)) XIDSLOT=bitand(196639,to_number('ffff','xxxx')) XIDSQN=1253
• 76.
  Transaction&Block Transaction slots 100 Frank 10-May-12 2000 Row Header Column Data Undo Segment #1 Undo Segment #2 Undo Segment #3 Transaction Table Trans TBL Index State Wrap DBA 1 9 1100 2 10 989 ..... ...... ...... 31 9 1252 SQL> COMMIT; 1 2 3 4 5 Row Directory
• 77.
  Transaction&Block Transaction slots 3.31.1253 100 Frank 10-May-12 3000 Row Header Column Data Undo Segment #1 Undo Segment #2 Undo Segment #3 Transaction Table Trans TBL Index State Wrap DBA 1 9 1100 2 10 989 ...... ...... ...... 31 10 1253 3.1198 SQL> COMMIT; SQL> UPDATE TABLE t1 2> SET salary=3000 3> WHERE empid=100; 1 2 3 4 5 2 2000 Transaction ID Row Directory
• 78.
  CR Block --另⼀一個SESSION SQL> SELECT ＊FROM t1 WHERE empid=100; ! EMPID EMP_NAM HIRE_DATE SALARY ---------- ------------- --------------- ---------- 100 Frank 10-May-12 2000 Undo Segment #1 Undo Segment #2 Undo Segment #3 Transaction Table Trans TBL Index State Wrap DBA 1 9 1100 2 10 989 ..... ...... ...... 31 10 1253 3.1198 2000 Transaction slots 3.31.1253 1 2 3 4 5 step 1 step 2 step 7 100 Frank 10-May-12 3000 2 Row Header Column Data step 3 Transaction slots 3.31.1253 1 2 3 4 5 100 Frank 10-May-12 2000 2 Row Header Column Data XCUR Block Consistent Read Block step 5 step 6 step 4
• 79.
  Undo Management AutomaticUndo Management(AUM) UNDO_MANAGEMENT=AUTO UNDO_TABLESPACE=undotbs1 UNDO_RETENTION=900
• 80.
  SQL> SELECT tablespace_name,contentsFROM dba_tablespaces; ! TABLESPACE_NAME CONTENTS ------------------------------- ------------------- SYSTEM PERMANENT SYSAUX PERMANENT UNDOTBS1 UNDO TEMP TEMPORARY USERS PERMANENT EXAMPLE PERMANENT ! SQL> CREATE UNDO TABLESPACE undotbs2 2> DATAFILE ‘/u02/oradata/orcl/undotbs2.dbf’ SIZE 1000M 3> AUTOEXTEND ON NEXT 100M MAXSIZE 5000M; ! SQL> SELECT tablespace_name,contents FROM dba_tablespaces; ! TABLESPACE_NAME CONTENTS ------------------------------- ------------------- SYSTEM PERMANENT SYSAUX PERMANENT UNDOTBS1 UNDO TEMP TEMPORARY USERS PERMANENT EXAMPLE PERMANENT UNDOTBS2 UNDO ! SQL> ALTER SYSTEM SET undo_tablespace=UNDOTBS2;
• 81.
  Q&A