oracle培訓(xùn)_XXXX0829

1、3-1Copyright 2003, Oracle. All rights reserved.主要培訓(xùn)內(nèi)容主要培訓(xùn)內(nèi)容1、直觀概覽、直觀概覽2、Sql語(yǔ)句種類語(yǔ)句種類3、體系結(jié)構(gòu)、體系結(jié)構(gòu)4、數(shù)據(jù)庫(kù)啟動(dòng)與關(guān)閉、數(shù)據(jù)庫(kù)啟動(dòng)與關(guān)閉5、數(shù)據(jù)字典、動(dòng)態(tài)性能視圖、數(shù)據(jù)字典、動(dòng)態(tài)性能視圖6、UNDO7、連接、連接8、PGA9、CHECKPIONT10、鎖、鎖11、事務(wù)、事務(wù)12、SCN13、移動(dòng)數(shù)據(jù)；備份、恢復(fù)（見備份與恢復(fù)介紹、移動(dòng)數(shù)據(jù)；備份、恢復(fù)（見備份與恢復(fù)介紹.ppt)14、性能管理、性能管理3Copyright 2003, Oracle. All rights reserved.1、直觀概覽、直

2、觀概覽3-3Copyright 2003, Oracle. All rights reserved.oracle_base/product/admin /inst_name/pfile/release_number/bin/dbs/rdbms/sqlplusSoftwareoradata/db01/system01.dbfcontrol01.ctlredo0101.log.db02/system01.dbfcontrol01.ctlredo0101.log.FilesOracle Software and File Locations3-4Copyright 2003, Oracle. All

3、 rights reserved.-bash-3.00$ ps -ef | grep ora_ | grep -v grep oracle 13665 1 0 6月月 18 ? 36:56 ora_smon_ora10g oracle 14576 1 0 6月月 18 ? 5:08 ora_qmnc_ora10g oracle 27706 1 0 09:22:43 ? 0:38 ora_j000_ora10g oracle 15023 1 0 6月月 18 ? 5:41 ora_q000_ora10g oracle 13711 1 0 6月月 18 ? 59:52 ora_mmon_ora10

4、g oracle 13551 1 0 6月月 18 ? 311:29 ora_pmon_ora10g oracle 2450 1 0 10:07:55 ? 0:00 ora_j002_ora10g oracle 13697 1 0 6月月 18 ? 93:06 ora_cjq0_ora10g oracle 13569 1 0 6月月 18 ? 5:59 ora_psp0_ora10g oracle 13585 1 0 6月月 18 ? 7:52 ora_mman_ora10g oracle 15147 1 0 6月月 18 ? 3:55 ora_q001_ora10g oracle 13729

5、 1 0 6月月 18 ? 417:36 ora_mmnl_ora10g oracle 6818 1 0 06:00:02 ? 0:12 ora_j001_ora10g oracle 13656 1 0 6月月 18 ? 185:26 ora_ckpt_ora10g oracle 13617 1 0 6月月 18 ? 1835:19 ora_dbw1_ora10g oracle 13786 1 0 6月月 18 ? 0:17 ora_s000_ora10g oracle 13684 1 0 6月月 18 ? 0:46 ora_reco_ora10g oracle 13744 1 0 6月月 1

6、8 ? 0:09 ora_d000_ora10g oracle 13637 1 0 6月月 18 ? 3305:06 ora_lgwr_ora10g oracle 13604 1 0 6月月 18 ? 3037:33 ora_dbw0_ora10g3-5Copyright 2003, Oracle. All rights reserved.SQL show parameter dumpNAME TYPE VALUE- - -background_core_dump string partialbackground_dump_dest string /export/home/oracle/adm

7、in/ora1 0g/bdumpcore_dump_dest string /export/home/oracle/admin/ora1 0g/cdumpmax_dump_file_size string UNLIMITEDshadow_core_dump string PARTIALuser_dump_dest string /export/home/oracle/admin/ora1 0g/udumpSQL select name from v$datafile;NAME-/data2/oradata/system01.dbf/data2/oradata/undotbs01.dbf/dat

8、a2/oradata/sysaux01.dbf/data2/oradata/users01.dbf/data1/userdata/tbs_dninmsv30.dbf/data1/userdata/tbs_dninms_report.dbf/data1/userdata/tbs_dninmsv30_02.dbf/data1/userdata/tbs_nmsv3_Table128M_02.dbf/data1/userdata/tbs_nmsv3_Table128M_01.dbf/export/home2/userdata/tbs_nmsv3_IDX_128M_02.dbf/export/home2

9、/userdata/tbs_nmsv3_IDX_128M_01.dbf3-6Copyright 2003, Oracle. All rights reserved.SQL show parameter controlNAME TYPE VALUE- - -control_file_record_keep_time integer 7control_files string /data2/oradata/control01.ctl, /data2/oradata/control02.ctl, /data2/oradata/control03.ctlSQL col member format a3

10、0SQL select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_- - - - - 1 ONLINE /data2/oradata/redo01.log NO 2 ONLINE /data2/oradata/redo02.log NO 3 ONLINE /data2/oradata/redo03.log NO 4 ONLINE /data2/oradata/redo04.log NOSQL archive log listDatabase log mode No Archive ModeAutomatic archival Disabled

11、Archive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 50455Current log sequence 504583-7Copyright 2003, Oracle. All rights reserved.-bash-3.00$ strings spfileora10g.ora +l阓阓ora10g._db_cache_size=19360907264ora10g._java_pool_size=33554432ora10g._large_pool_size=16777216ora10g._share

12、d_pool_size=1040187392ora10g._streams_pool_size=0*._awr_flush_threshold_metrics=TRUE*._kgl_large_heap_warning_threshold=50000000*.audit_file_dest=/export/home/oracle/admin/ora10g/adump*.background_dump_dest=/export/home/oracle/admin/ora10g/bdump*.compatible=.0*.control_files=/data2/oradata/c

13、ontrol01.ctl,/data2/oradata/control02.ctl,/data2/oradata/control03.ctl*.core_dump_dest=/export/home/oracle/admin/ora10g/cdump*.db_32k_cache_size=117440512*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=ora10g*.db_recovery_file_dest=/export/home/oracle/flash_recovery_area*

14、.db_recovery_file_dest_size=2147483648*.dispatchers=(PROTOCOL=TCP) (SERVICE=ora10gXDB)*.job_queue_processes=30*.nls_language=SIMPLIFIED CHINESE*.nls_territory=CHINA*.open_cursors=300*.open_links=10*.pga_aggregate_target=6860832768*.processes=2500*.remote_login_passwordfile=EXCLUSIVE*.sessions=1105*.

15、sga_target=20583546880*.undo_management=AUTO*.undo_retention=10800*.undo_tablespace=UNDOTBS1*.user_dump_dest=/export/home/oracle/admin/ora10g/udump3-8Copyright 2003, Oracle. All rights reserved.-bash-3.00$ pwd/export/home/oracle/product/ora10g/dbs-bash-3.00$ cd ./network/admin/-bash-3.00$ lslistener

16、.ora samples sqlnet.log tnsnames.ora tnsnames.ora.bak0812lz shrept.lst sqlnet.ora tnsnames.ora.bak0721-bash-3.00$ more listener.ora # listener.ora Network Configuration File: /export/home/oracle/product/ora10g/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_ORA10G = (SID

17、_LIST = (SID_DESC = (GLOBAL_DBNAME = ora10g) (ORACLE_HOME = /export/home/oracle/product/ora10g) (SID_NAME = ora10g) ) )ORA10G = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) )#-ADDED BY TNSLSNR 09-JAN-2009 00:12:39-INBOUND_CONNECT_TIMEOUT_LISTENER = 0#-SUBSCRIBE_FOR_NODE

18、_DOWN_EVENT_ORA10G=OFF3-9Copyright 2003, Oracle. All rights reserved.-bash-3.00$ more sqlnet.ora # sqlnet.ora Network Configuration File: /export/home/oracle/product/ora10g/network/admin/sqlnet.ora# Generated by Oracle configuration tools.#NAMES.DIRECTORY_PATH= (TNSNAMES)SQLNET.INBOUND_CONNECT_TIMEO

19、UT=0-bash-3.00$ more tnsnames.ora# tnsnames.ora Network Configuration File: /export/home/oracle/product/ora10g/network/admin/tnsnames.ora# Generated by Oracle configuration tools.#INMS1 =# (DESCRIPTION =# (ADDRESS = (PROTOCOL = TCP)(HOST = INMS1)(PORT = 1521)# (CONNECT_DATA =# (SERVICE_NAME =ora10g)

20、# (INSTANCE_NAME =INMS1)# )# )#INMS2=# (DESCRIPTION =# (ADDRESS = (PROTOCOL = TCP)(HOST =INMS2)(PORT = 1521)# (CONNECT_DATA =# (SERVICE_NAME =ora10g)# (INSTANCE_NAME =INMS2)# )# )3-10Copyright 2003, Oracle. All rights reserved.2、Sql語(yǔ)句種類語(yǔ)句種類3-11Copyright 2003, Oracle. All rights reserved.SQL Statemen

21、ts SELECT INSERTUPDATEDELETEMERGECREATEALTERDROPRENAMETRUNCATECOMMENTGRANTREVOKECOMMITROLLBACKSAVEPOINTData manipulation language (DML)Data definition language (DDL)Transaction controlData control language (DCL)3-12Copyright 2003, Oracle. All rights reserved.3、體系結(jié)構(gòu)、體系結(jié)構(gòu)3-13Copyright 2003, Oracle. Al

22、l rights reserved.數(shù)據(jù)庫(kù)體系結(jié)構(gòu)：概述數(shù)據(jù)庫(kù)體系結(jié)構(gòu)：概述 存儲(chǔ)結(jié)構(gòu)存儲(chǔ)結(jié)構(gòu)內(nèi)存結(jié)構(gòu)內(nèi)存結(jié)構(gòu)進(jìn)程結(jié)構(gòu)進(jìn)程結(jié)構(gòu)實(shí)例實(shí)例 系統(tǒng)全局區(qū)系統(tǒng)全局區(qū) (SGA)后臺(tái)進(jìn)程后臺(tái)進(jìn)程數(shù)據(jù)庫(kù)文件數(shù)據(jù)庫(kù)文件3-14Copyright 2003, Oracle. All rights reserved.Oracle 內(nèi)存結(jié)構(gòu)內(nèi)存結(jié)構(gòu) Java 池池?cái)?shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 緩沖區(qū)高速緩存緩沖區(qū)高速緩存重做日志重做日志 緩沖區(qū)緩沖區(qū)共享池共享池大型池大型池SGA 流池流池服務(wù)器服務(wù)器進(jìn)程進(jìn)程 1 PGA服務(wù)器服務(wù)器 進(jìn)程進(jìn)程 2 PGA后臺(tái)進(jìn)程后臺(tái)進(jìn)程PGA3-15Copyright 2003, Oracle.

23、 All rights reserved.Oracle 內(nèi)存結(jié)構(gòu)內(nèi)存結(jié)構(gòu)完整注釋頁(yè)完整注釋頁(yè) 3-16Copyright 2003, Oracle. All rights reserved.Oracle 進(jìn)程進(jìn)程 系統(tǒng)系統(tǒng) 監(jiān)視器監(jiān)視器 (SMON) 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 寫進(jìn)程寫進(jìn)程 (DBWn) 檢查點(diǎn)檢查點(diǎn) (CKPT) 日志寫進(jìn)程日志寫進(jìn)程 (LGWR) 進(jìn)程進(jìn)程 監(jiān)視器監(jiān)視器 (PMON) 歸檔程序歸檔程序 (ARCn)服務(wù)器服務(wù)器 進(jìn)程進(jìn)程 服務(wù)器服務(wù)器 進(jìn)程進(jìn)程 服務(wù)器服務(wù)器 進(jìn)程進(jìn)程 服務(wù)器服務(wù)器 進(jìn)程進(jìn)程 后臺(tái)進(jìn)程后臺(tái)進(jìn)程系統(tǒng)全局區(qū)系統(tǒng)全局區(qū) (SGA) 3-17Copyright

24、2003, Oracle. All rights reserved.概述概述 Oracle 實(shí)例管理實(shí)例管理SGA 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 寫進(jìn)程寫進(jìn)程 (DBWn) 日志寫進(jìn)程日志寫進(jìn)程 (LGWR) 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 緩沖區(qū)高速緩存緩沖區(qū)高速緩存 重做日志重做日志 緩沖區(qū)緩沖區(qū) 1用戶進(jìn)程用戶進(jìn)程2示例：示例： 啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例 建立連接建立連接 系統(tǒng)系統(tǒng) 監(jiān)視器監(jiān)視器 (SMON) 進(jìn)程進(jìn)程 監(jiān)視器監(jiān)視器 (PMON) 數(shù)據(jù)文件數(shù)據(jù)文件重做日志文件重做日志文件實(shí)例實(shí)例服務(wù)器服務(wù)器 進(jìn)程進(jìn)程 33-18Copyright 2003, Oracle. All rights reserved.概

25、述概述 Oracle 實(shí)例管理實(shí)例管理 用戶更新了某一行數(shù)據(jù)用戶更新了某一行數(shù)據(jù)重做日志文件重做日志文件實(shí)例實(shí)例 SGA數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 寫進(jìn)程寫進(jìn)程 (DBWn) 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 緩沖區(qū)高速緩存緩沖區(qū)高速緩存 重做日志重做日志緩沖區(qū)緩沖區(qū)服務(wù)器服務(wù)器 進(jìn)程進(jìn)程 用戶進(jìn)程用戶進(jìn)程 4107658示例：處理示例：處理 SQL 語(yǔ)句語(yǔ)句 歸檔程序歸檔程序 (ARCn)9檢查點(diǎn)檢查點(diǎn) (CKPT) 數(shù)據(jù)文件數(shù)據(jù)文件存檔日志存檔日志控制文件控制文件日志寫進(jìn)程日志寫進(jìn)程 (LGWR)3-19Copyright 2003, Oracle. All rights reserved.SHARED POOL3-20C

26、opyright 2003, Oracle. All rights reserved.Shared Pool ContentsMajor components of the shared pool are:Library cacheData dictionary cacheUser global area (UGA) for shared server sessionsDatabase buffer cacheRedo log buffer Shared pool Library cache Data dictionary cache User global area Large pool U

27、GA3-21Copyright 2003, Oracle. All rights reserved.Shared PoolDefined by SHARED_POOL_SIZELibrary cache contains statement text, parsed code, and execution plan.Data dictionary cache contains definitions for tables, columns, and privileges from the data dictionary tables.UGA contains session informati

28、on for Oracle Shared Server users when a large pool is not configured.Shared poolLibrarycacheDatadictionarycacheUGAShared poolShared pool 3-22Copyright 2003, Oracle. All rights reserved.LARGE POOL3-23Copyright 2003, Oracle. All rights reserved.Large Pool Can be configured as a separate memory area i

29、n the SGA to be used for: Oracle backup and restore operations I/O server processes Session memory for the shared serversIs sized by the LARGE_POOL_SIZE parameter3-24Copyright 2003, Oracle. All rights reserved.Shared poolLibrary cache Data dictionary cache User global area Database buffer cacheRedo

30、log buffer Large pool Large PoolCan be configured as a separate memory area in the SGA, used for memory with:I/O server processes: DBWR_IO_SLAVESBackup and restore operationsSession memory for the shared serversParallel query messagingUsed to avoid performance overhead caused by shrinking the shared

31、 SQL cacheSized by the parameter LARGE_POOL_SIZE3-25Copyright 2003, Oracle. All rights reserved.DB BUFFER CACHE3-26Copyright 2003, Oracle. All rights reserved.Buffer Cache CharacteristicsData filesSGADB buffer cacheCheckpoint Queue.LRU listsDBWnServerDB_BLOCK_SIZEDB_CACHE_SIZEDB_KEEP_CACHE_SIZEDB_RE

32、CYCLE_CACHE_SIZEDB_BLOCK_CHECKSUM.3-27Copyright 2003, Oracle. All rights reserved.SGADB buffer cacheCheckpoint Queue.LRU lists.Managing the Database Buffer CacheDBWnLGWRData files1Server22343-28Copyright 2003, Oracle. All rights reserved.SGADB buffer cacheCheckpoint Queue.Managing the Database Buffe

33、r CacheDBWnLGWRServerData filesLRU lists3-29Copyright 2003, Oracle. All rights reserved.Free ListsA free list for an object maintains a list of blocks that are available for inserts.The number of free lists for an object can beset dynamically.Single-CPU systems do not benefit greatly from multiple f

34、ree lists.The tuning goal is to ensure that an object has sufficient free lists to minimize contention.Using Automatic Free Space Management eliminates the need for free lists, thus reducing contention on the database.3-30Copyright 2003, Oracle. All rights reserved.Diagnosing Free List Contentionv$w

35、aitstat columns:CLASS “segment header” COUNT TIMESGA Data buffer cachev$system_eventcolumns:EVENT “buffer busy waits” TOTAL_WAITSFREELISTS3-31Copyright 2003, Oracle. All rights reserved.SGA Data buffer cacheResolving Free List ContentionServerprocessv$session_wait columns:EVENT “buffer busy waits” P

36、1 “FILE” P2 “BLOCK” P3 “ID”dba_segments columns:SEGMENT_NAME SEGMENT_TYPE FREELISTS HEADER_FILE HEADER_BLOCKFREELISTSServerprocessObject ID3-32Copyright 2003, Oracle. All rights reserved.REDO LOG BUFFER3-33Copyright 2003, Oracle. All rights reserved.Database buffer cacheRedo log buffer Shared pool L

37、ibrary cache Data dictionary cache User global area The Redo Log BufferSQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=736;Serverprocess LGWRControl filesARCnArchivedlog filesRedo log filesData files3-34Copyright 2003, Oracle. All rights reserved.Sizing the Redo Log BufferAdjust the

38、 LOG_BUFFER parameter.Default value: Either 512K or 128K * the value of CPU_COUNT, whichever is greater.3-35Copyright 2003, Oracle. All rights reserved.Archivedlog filesRedo log filesDiagnosing Redo Log Buffer InefficiencySQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=736;Serverpro

39、cess LGRWARCHServerprocessSQL DELETE FROM employees 2 WHERE employee_id=7400;3-36Copyright 2003, Oracle. All rights reserved.Using Dynamic Views to Analyze Redo Log Buffer Efficiencyv$session_waitLog Buffer Space eventv$sysstatRedo Buffer Allocation RetriesRedo Entries Redo log buffer3-37Copyright 2

40、003, Oracle. All rights reserved.物理結(jié)構(gòu)物理結(jié)構(gòu)3-38Copyright 2003, Oracle. All rights reserved.物理數(shù)據(jù)庫(kù)結(jié)構(gòu)物理數(shù)據(jù)庫(kù)結(jié)構(gòu) 聯(lián)機(jī)重做日志文件聯(lián)機(jī)重做日志文件口令文件口令文件 參數(shù)文件參數(shù)文件 歸檔日志文件歸檔日志文件 控制文件控制文件 數(shù)據(jù)文件數(shù)據(jù)文件 預(yù)警和跟蹤日志文件預(yù)警和跟蹤日志文件 備份文件備份文件 3-39Copyright 2003, Oracle. All rights reserved.物理數(shù)據(jù)庫(kù)結(jié)構(gòu)物理數(shù)據(jù)庫(kù)結(jié)構(gòu)完整注釋頁(yè)完整注釋頁(yè) 3-40Copyright 2003, Oracle. Al

41、l rights reserved.邏輯和物理數(shù)據(jù)庫(kù)結(jié)構(gòu)邏輯和物理數(shù)據(jù)庫(kù)結(jié)構(gòu) 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)邏輯邏輯 物理物理表空間表空間數(shù)據(jù)文件數(shù)據(jù)文件操作系統(tǒng)塊操作系統(tǒng)塊段段區(qū)區(qū)Oracle 數(shù)據(jù)塊數(shù)據(jù)塊方案方案3-41Copyright 2003, Oracle. All rights reserved.數(shù)據(jù)庫(kù)結(jié)構(gòu)數(shù)據(jù)庫(kù)結(jié)構(gòu)完整注釋頁(yè)完整注釋頁(yè) 3-42Copyright 2003, Oracle. All rights reserved.3-43Copyright 2003, Oracle. All rights reserved.Database Storage HierarchyTablespace

42、SegmentsBlocksExtentsExtentsExtents3-44Copyright 2003, Oracle. All rights reserved.Storage and Relationship StructureDatabasePRODDISK1/SYS1.dbfDISK2/USER1.dbfDISK3/USER2.dbfDISK1/UNDO1.dbfDISK1/TEMP.dbfD.D.TableDataSegD.D.IndexIndexSegRBSegS_DEPTDataSegS_EMPDataSegTempTempSeg1 2 1 2 1 2 1 1 2 2 1 FR

43、EE 1 1 2 2 1EXTENTSS_DEPT(contd)DataSegS_EMPFIRST_NAMEIndexIndexSegOracle DATA BLOCKSRBS1(contd)RBSegRBS2(contd)RBSegRBS1RBSegRBS2RBSegSEGMENTS SYSTEMUSER_DATARBSTEMPTABLESPACESDATA FILES3-45Copyright 2003, Oracle. All rights reserved.數(shù)據(jù)庫(kù)體系結(jié)構(gòu)：結(jié)構(gòu)組件匯總數(shù)據(jù)庫(kù)體系結(jié)構(gòu)：結(jié)構(gòu)組件匯總 內(nèi)存結(jié)構(gòu)內(nèi)存結(jié)構(gòu) 系統(tǒng)全局區(qū)系統(tǒng)全局區(qū) (SGA)：數(shù)據(jù)庫(kù)緩沖區(qū)高速緩存

44、、重做緩沖：數(shù)據(jù)庫(kù)緩沖區(qū)高速緩存、重做緩沖區(qū)以及各種池區(qū)以及各種池 程序全局區(qū)程序全局區(qū) (PGA) 進(jìn)程結(jié)構(gòu)進(jìn)程結(jié)構(gòu) 用戶進(jìn)程和服務(wù)器進(jìn)程用戶進(jìn)程和服務(wù)器進(jìn)程 后臺(tái)進(jìn)程：后臺(tái)進(jìn)程：SMON、PMON、DBWn、CKPT、LGWR、ARCn 等等 存儲(chǔ)結(jié)構(gòu)存儲(chǔ)結(jié)構(gòu) 邏輯：數(shù)據(jù)庫(kù)、方案、表空間、段、區(qū)和邏輯：數(shù)據(jù)庫(kù)、方案、表空間、段、區(qū)和 Oracle 塊塊 物理：用于存儲(chǔ)數(shù)據(jù)、參數(shù)、重做和操作系統(tǒng)塊的文件物理：用于存儲(chǔ)數(shù)據(jù)、參數(shù)、重做和操作系統(tǒng)塊的文件 3-46Copyright 2003, Oracle. All rights reserved.方案（方案（schema）3-47Copyri

45、ght 2003, Oracle. All rights reserved.什么是方案什么是方案HR 方案方案 HR 用戶用戶 擁有擁有 方案方案約束條件約束條件 索引索引 視圖視圖 序列序列 臨時(shí)表臨時(shí)表 數(shù)據(jù)字典數(shù)據(jù)字典 3-48Copyright 2003, Oracle. All rights reserved.方案完整備注頁(yè)方案完整備注頁(yè) 3-49Copyright 2003, Oracle. All rights reserved.4、數(shù)據(jù)庫(kù)啟動(dòng)與關(guān)閉、數(shù)據(jù)庫(kù)啟動(dòng)與關(guān)閉3-50Copyright 2003, Oracle. All rights reserved.啟動(dòng)啟動(dòng) Orac

46、le 數(shù)據(jù)庫(kù)實(shí)例：數(shù)據(jù)庫(kù)實(shí)例：OPEN OPEN MOUNT NOMOUNT SHUTDOWN 已按此實(shí)例控制文件的已按此實(shí)例控制文件的描述打開了所有文件描述打開了所有文件 此實(shí)例的控制此實(shí)例的控制文件已打開文件已打開 實(shí)例已啟動(dòng)實(shí)例已啟動(dòng) STARTUP3-51Copyright 2003, Oracle. All rights reserved.關(guān)閉模式關(guān)閉模式 A 否否 否否 否否 否否 T 否否 否否 是是 是是 I 否否 否否 否否 是是 關(guān)閉模式關(guān)閉模式 允許新連接允許新連接 等待當(dāng)前會(huì)話結(jié)束等待當(dāng)前會(huì)話結(jié)束 等待當(dāng)前事務(wù)處理結(jié)束等待當(dāng)前事務(wù)處理結(jié)束 強(qiáng)制選擇檢查點(diǎn)并關(guān)閉文件強(qiáng)制選

47、擇檢查點(diǎn)并關(guān)閉文件 N 否否 是是 是是 是是 關(guān)閉模式：關(guān)閉模式： A = ABORT I = IMMEDIATE T = TRANSACTIONAL N = NORMAL 3-52Copyright 2003, Oracle. All rights reserved.SHUTDOWN 選項(xiàng)選項(xiàng) 在以下期間在以下期間 SHUTDOWN NORMAL： 或或 SHUTDOWN TRANSACTIONAL 或或 SHUTDOWN IMMEDIATE 一致的數(shù)據(jù)庫(kù)一致的數(shù)據(jù)庫(kù) （干凈的數(shù)據(jù)庫(kù)）（干凈的數(shù)據(jù)庫(kù)） 關(guān)閉時(shí)：關(guān)閉時(shí)： 執(zhí)行執(zhí)行 IMMEDIATE 時(shí)，會(huì)回退未提交時(shí)，會(huì)回退未提交的更改的

48、更改 數(shù)據(jù)庫(kù)緩沖區(qū)高速數(shù)據(jù)庫(kù)緩沖區(qū)高速緩存會(huì)寫入到數(shù)據(jù)緩存會(huì)寫入到數(shù)據(jù)文件文件 會(huì)釋放資源會(huì)釋放資源 啟動(dòng)時(shí)：?jiǎn)?dòng)時(shí)： 不用恢復(fù)實(shí)例不用恢復(fù)實(shí)例 3-53Copyright 2003, Oracle. All rights reserved.完整備注頁(yè)完整備注頁(yè) 3-54Copyright 2003, Oracle. All rights reserved.SHUTDOWN 選項(xiàng)選項(xiàng) 關(guān)閉時(shí)：關(guān)閉時(shí)： 修改過的緩沖修改過的緩沖區(qū)不寫入到數(shù)區(qū)不寫入到數(shù)據(jù)文件據(jù)文件 不回退未提交不回退未提交的更改的更改 在以下期間在以下期間 SHUTDOWN ABORT 或或 實(shí)例失敗實(shí)例失敗 或或 STARTU

49、P FORCE 不一致的數(shù)據(jù)庫(kù)不一致的數(shù)據(jù)庫(kù) （灰數(shù)據(jù)數(shù)據(jù)庫(kù)）（灰數(shù)據(jù)數(shù)據(jù)庫(kù)） 啟動(dòng)時(shí)：?jiǎn)?dòng)時(shí)： 使用聯(lián)機(jī)重做使用聯(lián)機(jī)重做日志文件重新日志文件重新應(yīng)用更改應(yīng)用更改 使用還原段回使用還原段回退未提交的更改退未提交的更改 會(huì)釋放資源會(huì)釋放資源 3-55Copyright 2003, Oracle. All rights reserved.使用使用 SQL*Plus 啟動(dòng)和關(guān)閉啟動(dòng)和關(guān)閉 oracleEDRSR9P1 oracle$ sqlplus dba1/oracle as sysdba SQL shutdown immediate Database closed. Database dismo

50、unted. ORACLE instance shut down. SQL startup ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218472 bytes Variable Size 250177624 bytes Database Buffers 33554432 bytes Redo Buffers 262144 bytes Database mounted. Database opened. SQL 3-56Copyright 2003, Oracle. All righ

51、ts reserved.5、數(shù)據(jù)字典、動(dòng)態(tài)性能視圖、數(shù)據(jù)字典、動(dòng)態(tài)性能視圖3-57Copyright 2003, Oracle. All rights reserved.數(shù)據(jù)字典：概覽數(shù)據(jù)字典：概覽 表表 索引索引 視圖視圖 用戶用戶 方案方案 過程過程 等等等等 方案方案約束條件約束條件 索引索引 視圖視圖 序列序列 臨時(shí)表臨時(shí)表 數(shù)據(jù)字典數(shù)據(jù)字典 SELECT * FROM dictionary;3-58Copyright 2003, Oracle. All rights reserved.數(shù)據(jù)字典視圖數(shù)據(jù)字典視圖 誰(shuí)可以查詢誰(shuí)可以查詢 內(nèi)容內(nèi)容 下列項(xiàng)的子集下列項(xiàng)的子集 注注 DBA_

52、DBA 一切對(duì)象一切對(duì)象 無(wú)無(wú) 可能有其它僅限可能有其它僅限 DBA 使用的列使用的列 ALL_ 每個(gè)用戶每個(gè)用戶 用戶有權(quán)查看用戶有權(quán)查看的一切對(duì)象的一切對(duì)象 DBA_ 視圖視圖 包括用戶擁有的對(duì)象包括用戶擁有的對(duì)象 USER_ 每個(gè)用戶每個(gè)用戶 用戶擁有的用戶擁有的一切對(duì)象一切對(duì)象 ALL_ 視圖視圖 除了缺少除了缺少 OWNER 列，列，通常與通常與 ALL_ 相同。某相同。某些視圖有象些視圖有象 PUBLIC 別別名一樣的簡(jiǎn)稱。名一樣的簡(jiǎn)稱。 3-59Copyright 2003, Oracle. All rights reserved.數(shù)據(jù)字典：用法示例數(shù)據(jù)字典：用法示例 SELEC

53、T USERNAME, ACCOUNT_STATUS FROMdba_users WHERE ACCOUNT_STATUS = OPEN;SELECT table_name, tablespace_name FROM user_tables;SELECT sequence_name, min_value, max_value, increment_by FROM all_sequences WHERE sequence_owner IN (MDSYS,XDB);abcDESCRIBE dba_indexes;d3-60Copyright 2003, Oracle. All rights res

54、erved.動(dòng)態(tài)性能視圖動(dòng)態(tài)性能視圖 通過動(dòng)態(tài)性能視圖可訪問有關(guān)通過動(dòng)態(tài)性能視圖可訪問有關(guān)在數(shù)據(jù)庫(kù)中更改狀態(tài)和條件的在數(shù)據(jù)庫(kù)中更改狀態(tài)和條件的信息。信息。 會(huì)話數(shù)據(jù)會(huì)話數(shù)據(jù) 等待事件等待事件 內(nèi)存分配內(nèi)存分配 運(yùn)行運(yùn)行 SQL UNDO 用法用法 打開游標(biāo)打開游標(biāo) 重做日志的用法重做日志的用法 等等等等 Oracle 實(shí)例實(shí)例 組件組件 SQL*Plus 初始化參數(shù)初始化參數(shù) 數(shù)據(jù)庫(kù)啟動(dòng)數(shù)據(jù)庫(kù)啟動(dòng) 數(shù)據(jù)庫(kù)關(guān)閉數(shù)據(jù)庫(kù)關(guān)閉 預(yù)警日志預(yù)警日志 性能視圖性能視圖 3-61Copyright 2003, Oracle. All rights reserved.動(dòng)態(tài)性能視圖：用法示例動(dòng)態(tài)性能視圖：用法示例

55、 SQL SELECT sql_text, executions FROM v$sql WHERE cpu_time 200000;SQL SELECT * FROM v$session WHERE machine = EDRSR9P1 and logon_time SYSDATE - 1;SQL SELECT sid, ctime FROM v$lock WHERE block 0;abc3-62Copyright 2003, Oracle. All rights reserved.動(dòng)態(tài)性能視圖：注意事項(xiàng)動(dòng)態(tài)性能視圖：注意事項(xiàng) 這些視圖由這些視圖由 SYS 用戶擁有。用戶擁有。 不同時(shí)間可以

56、使用不同視圖：不同時(shí)間可以使用不同視圖： 實(shí)例已啟動(dòng)。實(shí)例已啟動(dòng)。 數(shù)據(jù)庫(kù)已裝載。數(shù)據(jù)庫(kù)已裝載。 數(shù)據(jù)庫(kù)已打開。數(shù)據(jù)庫(kù)已打開。 通過查詢通過查詢 V$FIXED_TABLE 可查看所有視圖名稱。可查看所有視圖名稱。 這些視圖通常又稱為這些視圖通常又稱為“v$ 視圖視圖”。 由于數(shù)據(jù)是動(dòng)態(tài)的，因此這些視圖不保證讀取一致性。由于數(shù)據(jù)是動(dòng)態(tài)的，因此這些視圖不保證讀取一致性。 3-63Copyright 2003, Oracle. All rights reserved.6、UNDO3-64Copyright 2003, Oracle. All rights reserved.數(shù)據(jù)操縱數(shù)據(jù)操縱 數(shù)據(jù)操

57、縱語(yǔ)言數(shù)據(jù)操縱語(yǔ)言 (DML) 由以下由以下 SQL 語(yǔ)句組成：語(yǔ)句組成： INSERT UPDATE DELETE MERGE DML 始終作為事務(wù)處理的一部分執(zhí)行，它可以：始終作為事務(wù)處理的一部分執(zhí)行，它可以： 回退，使用回退，使用 ROLLBACK 命令命令 提交，使用提交，使用 COMMIT 命令命令 3-65Copyright 2003, Oracle. All rights reserved.還原數(shù)據(jù)還原數(shù)據(jù) 還原數(shù)據(jù)是：還原數(shù)據(jù)是： 原始的、修改之前的數(shù)據(jù)副本原始的、修改之前的數(shù)據(jù)副本為更改數(shù)據(jù)的每個(gè)事務(wù)處理而捕獲為更改數(shù)據(jù)的每個(gè)事務(wù)處理而捕獲 至少保留到事務(wù)處理結(jié)束至少保留到事

58、務(wù)處理結(jié)束 用于支持：用于支持： 回退操作回退操作 讀取一致性和閃回查詢讀取一致性和閃回查詢 從失敗事務(wù)處理中恢復(fù)從失敗事務(wù)處理中恢復(fù) 用戶用戶 3-66Copyright 2003, Oracle. All rights reserved.還原數(shù)據(jù)完整備注頁(yè)還原數(shù)據(jù)完整備注頁(yè) 3-67Copyright 2003, Oracle. All rights reserved.事務(wù)處理和還原數(shù)據(jù)事務(wù)處理和還原數(shù)據(jù) 每個(gè)事務(wù)處理只分配給一個(gè)還原段每個(gè)事務(wù)處理只分配給一個(gè)還原段一個(gè)還原段可以同時(shí)服務(wù)多個(gè)事務(wù)處理一個(gè)還原段可以同時(shí)服務(wù)多個(gè)事務(wù)處理還原段還原段 更新事務(wù)處理更新事務(wù)處理 舊值舊值 新值新值

59、 表表 3-68Copyright 2003, Oracle. All rights reserved.存儲(chǔ)還原信息存儲(chǔ)還原信息 還原信息存儲(chǔ)在還原段中，還原段又存儲(chǔ)在還原表空間中。還原信息存儲(chǔ)在還原段中，還原段又存儲(chǔ)在還原表空間中。還原表空間：還原表空間：僅用于還原段僅用于還原段 包含特殊的恢復(fù)注意事項(xiàng)包含特殊的恢復(fù)注意事項(xiàng) 只能與單個(gè)實(shí)例相關(guān)聯(lián)只能與單個(gè)實(shí)例相關(guān)聯(lián) 要求在任意指定時(shí)間，一個(gè)給定的實(shí)例只能有一個(gè)是要求在任意指定時(shí)間，一個(gè)給定的實(shí)例只能有一個(gè)是當(dāng)前可寫還原表空間當(dāng)前可寫還原表空間3-69Copyright 2003, Oracle. All rights reserved.還原

60、數(shù)據(jù)與重做數(shù)據(jù)還原數(shù)據(jù)與重做數(shù)據(jù) 還原還原 重做重做 記錄記錄 如何還原更改如何還原更改 如何重新生成更改如何重新生成更改 用于用于 回退、讀取一致性回退、讀取一致性 向前滾動(dòng)數(shù)據(jù)庫(kù)更改向前滾動(dòng)數(shù)據(jù)庫(kù)更改 存儲(chǔ)于存儲(chǔ)于 還原段還原段 重做日志文件重做日志文件 避免避免 在多用戶系統(tǒng)中讀取不一致在多用戶系統(tǒng)中讀取不一致 數(shù)據(jù)丟失數(shù)據(jù)丟失 3-70Copyright 2003, Oracle. All rights reserved.監(jiān)視還原監(jiān)視還原 還原通常不需要管理。要監(jiān)視的范圍包括：還原通常不需要管理。要監(jiān)視的范圍包括： 還原表空間中的空閑空間還原表空間中的空閑空間 “快照太舊快照太舊”錯(cuò)誤