oracle培訓 (2)ppt課件

1、主要培訓內容1、直觀概覽2、Sql語句種類3、體系構造4、數(shù)據(jù)庫啟動與封鎖5、數(shù)據(jù)字典、動態(tài)性能視圖6、UNDO7、銜接8、PGA9、CHECKPIONT10、鎖11、事務12、SCN13、挪動數(shù)據(jù)；備份、恢復見備份與恢復引見.ppt)14、性能管理1、直觀概覽oracle_base/product/admin /inst_name/pfile/release_number/bin/dbs/rdbms/sqlplusSoftwareoradata/db01/system01.dbfcontrol01.ctlredo0101.log.db02/system01.dbfcontrol01.ctlr

2、edo0101.log.FilesOracle Software and File Locations-bash-3.00$ ps -ef | grep ora_ | grep -v grep oracle 65 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 11 1 0 6月 1

3、8 ? 59:52 ora_mmon_ora10g oracle 51 1 0 6月 18 ? 311:29 ora_pmon_ora10g oracle 2450 1 0 10:07:55 ? 0:00 ora_j002_ora10g oracle 97 1 0 6月 18 ? 93:06 ora_cjq0_ora10g oracle 69 1 0 6月 18 ? 5:59 ora_psp0_ora10g oracle 85 1 0 6月 18 ? 7:52 ora_mman_ora10g oracle 15147 1 0 6月 18 ? 3:55 ora_q001_ora10g oracl

4、e 29 1 0 6月 18 ? 417:36 ora_mmnl_ora10g oracle 6818 1 0 06:00:02 ? 0:12 ora_j001_ora10g oracle 56 1 0 6月 18 ? 185:26 ora_ckpt_ora10g oracle 17 1 0 6月 18 ? 1835:19 ora_dbw1_ora10g oracle 86 1 0 6月 18 ? 0:17 ora_s000_ora10g oracle 84 1 0 6月 18 ? 0:46 ora_reco_ora10g oracle 44 1 0 6月 18 ? 0:09 ora_d000

5、_ora10g oracle 37 1 0 6月 18 ? 3305:06 ora_lgwr_ora10g oracle 04 1 0 6月 18 ? 3037:33 ora_dbw0_ora10gSQL show parameter dumpNAME TYPE VALUE- - -background_core_dump string partialbackground_dump_dest string /export/home/oracle/admin/ora1 0g/bdumpcore_dump_dest string /export/home/oracle/admin/ora1 0g/

6、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/data2/oradata/sysaux01.dbf/data2/oradata/users01.dbf/data1/userdata/tbs_dni

7、nmsv30.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/userdata/tbs_nmsv3_IDX_128M_01.dbfSQL show parameter controlNAME TYPE V

8、ALUE- - -control_file_record_keep_time integer 7control_files string /data2/oradata/control01.ctl, /data2/oradata/control02.ctl, /data2/oradata/control03.ctlSQL col member format a30SQL select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_- - - - - 1 ONLINE /data2/oradata/redo01.log NO 2 ONLINE /da

9、ta2/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 DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 50455Current log sequence 50458-bash-3.00$ stri

10、ngs spfileora10g.ora +l阓ora10g._db_cache_size=19360907264ora10g._java_pool_size=33554432ora10g._large_pool_size=16777216ora10g._shared_pool_size=1040187392ora10g._streams_pool_size=0*._awr_flush_threshold_metrics=TRUE*._kgl_large_heap_warning_threshold=50000000*.audit_file_dest=/export/home/oracle/a

11、dmin/ora10g/adump*.background_dump_dest=/export/home/oracle/admin/ora10g/bdump*patible=.0*.control_files=/data2/oradata/control01.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=8

12、192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=ora10g*.db_recovery_file_dest=/export/home/oracle/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.dispatchers=(PROTOCOL=TCP) (SERVICE=ora10gXDB)*.job_queue_processes=30*.nls_language=SIMPLIFIED CHINESE*.nls_territory=CHINA*.open_

13、cursors=300*.open_links=10*.pga_aggregate_target=6860832768*.processes=2500*.remote_login_passwordfile=EXCLUSIVE*.sessions=1105*.sga_target=20583546880*.undo_management=AUTO*.undo_retention=10800*.undo_tablespace=UNDOTBS1*.user_dump_dest=/export/home/oracle/admin/ora10g/udump-bash-3.00$ pwd/export/h

14、ome/oracle/product/ora10g/dbs-bash-3.00$ cd ./network/admin/-bash-3.00$ lslistener.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/

15、admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_ORA10G = (SID_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 TNS

16、LSNR 09-JAN-2021 00:12:39-INBOUND_CONNECT_TIMEOUT_LISTENER = 0#-SUBSCRIBE_FOR_NODE_DOWN_EVENT_ORA10G=OFF-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= (TN

17、SNAMES)SQLNET.INBOUND_CONNECT_TIMEOUT=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)# (CON

18、NECT_DATA =# (SERVICE_NAME =ora10g)# (INSTANCE_NAME =INMS1)# )# )#INMS2=# (DESCRIPTION =# (ADDRESS = (PROTOCOL = TCP)(HOST =INMS2)(PORT = 1521)# (CONNECT_DATA =# (SERVICE_NAME =ora10g)# (INSTANCE_NAME =INMS2)# )# )2、Sql語句種類SQL Statements SELECT INSERTUPDATEDELETEMERGECREATEALTERDROPRENAMETRUNCATECOM

19、MENTGRANTREVOKECOMMITROLLBACKSAVEPOINTData manipulation language (DML)Data definition language (DDL)Transaction controlData control language (DCL)3、體系構造數(shù)據(jù)庫體系構造：概述 存儲構造內存構造進程構造實例 系統(tǒng)全局區(qū) (SGA)后臺進程數(shù)據(jù)庫文件Oracle 內存構造 Java 池數(shù)據(jù)庫 緩沖區(qū)高速緩存重做日志 緩沖區(qū)共享池大型池SGA 流池效力器進程 1 PGA效力器 進程 2 PGA后臺進程PGAOracle 內存構造完好注釋頁 Oracle

20、 進程 系統(tǒng) 監(jiān)視器 (SMON) 數(shù)據(jù)庫 寫進程 (DBWn) 檢查點 (CKPT) 日志寫進程 (LGWR) 進程 監(jiān)視器 (PMON) 歸檔程序 (ARCn)效力器 進程 效力器 進程 效力器 進程 效力器 進程 后臺進程系統(tǒng)全局區(qū) (SGA) 概述 Oracle 實例管理SGA 數(shù)據(jù)庫 寫進程 (DBWn) 日志寫進程 (LGWR) 數(shù)據(jù)庫 緩沖區(qū)高速緩存 重做日志 緩沖區(qū) 1用戶進程2例如： 啟動數(shù)據(jù)庫實例 建立銜接 系統(tǒng) 監(jiān)視器 (SMON) 進程 監(jiān)視器 (PMON) 數(shù)據(jù)文件重做日志文件實例效力器 進程 3概述 Oracle 實例管理 用戶更新了某一行數(shù)據(jù)重做日志文件實例 SG

21、A數(shù)據(jù)庫 寫進程 (DBWn) 數(shù)據(jù)庫 緩沖區(qū)高速緩存 重做日志緩沖區(qū)效力器 進程 用戶進程 4107658例如：處置 SQL 語句 歸檔程序 (ARCn)9檢查點 (CKPT) 數(shù)據(jù)文件存檔日志控制文件日志寫進程 (LGWR)SHARED POOLShared Pool ContentsMajor components of the shared pool are:Library cacheData dictionary cacheUser global area (UGA) for shared server sessionsDatabase buffer cacheRedo log bu

22、ffer Shared pool Library cache Data dictionary cache User global area Large pool UGAShared 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 tab

23、les.UGA contains session information for Oracle Shared Server users when a large pool is not configured.Shared poolLibrarycacheDatadictionarycacheUGAShared poolShared pool LARGE POOLLarge Pool Can be configured as a separate memory area in the SGA to be used for:Oracle backup and restore operationsI

24、/O server processesSession memory for the shared serversIs sized by the LARGE_POOL_SIZE parameterShared poolLibrary cache Data dictionary cache User global area Database buffer cacheRedo log buffer Large pool Large PoolCan be configured as a separate memory area in the SGA, used for memory with:I/O

25、server processes: DBWR_IO_SLAVESBackup and restore operationsSession memory for the shared serversParallel query messagingUsed to avoid performance overhead caused by shrinking the shared SQL cacheSized by the parameter LARGE_POOL_SIZEDB BUFFER CACHEBuffer Cache CharacteristicsData filesSGADB buffer

26、 cacheCheckpoint Queue.LRU listsDBWnServerDB_BLOCK_SIZEDB_CACHE_SIZEDB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEDB_BLOCK_CHECKSUM.SGADB buffer cacheCheckpoint Queue.LRU lists.Managing the Database Buffer CacheDBWnLGWRData files1Server2234SGADB buffer cacheCheckpoint Queue.Managing the Database Buffer Cac

27、heDBWnLGWRServerData filesLRU listsFree 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 free lists.The tuning goal is to ensure that an object

28、 has sufficient free lists to minimize contention.Using Automatic Free Space Management eliminates the need for free lists, thus reducing contention on the database.Diagnosing Free List Contentionv$waitstat columns:CLASS “segment header COUNT TIMESGA Data buffer cachev$system_eventcolumns:EVENT “buf

29、fer busy waits TOTAL_WAITSFREELISTSSGA Data buffer cacheResolving Free List ContentionServerprocessv$session_wait columns:EVENT “buffer busy waits P1 “FILE P2 “BLOCK P3 “IDdba_segments columns:SEGMENT_NAME SEGMENT_TYPE FREELISTS HEADER_FILE HEADER_BLOCKFREELISTSServerprocessObject IDREDO LOG BUFFERD

30、atabase buffer cacheRedo log buffer Shared pool Library 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 filesSizing the Redo Log BufferAdjust th

31、e LOG_BUFFER parameter.Default value: Either 512K or 128K * the value of CPU_COUNT, whichever is greater.Archivedlog filesRedo log filesDiagnosing Redo Log Buffer InefficiencySQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=736;Serverprocess LGRWARCHServerprocessSQL DELETE FROM emplo

32、yees 2 WHERE employee_id=7400;Using Dynamic Views to Analyze Redo Log Buffer Efficiencyv$session_waitLog Buffer Space eventv$sysstatRedo Buffer Allocation RetriesRedo Entries Redo log buffer物理構造物理數(shù)據(jù)庫構造 聯(lián)機重做日志文件口令文件 參數(shù)文件 歸檔日志文件 控制文件 數(shù)據(jù)文件 預警和跟蹤日志文件 備份文件 物理數(shù)據(jù)庫構造完好注釋頁 邏輯和物理數(shù)據(jù)庫構造 數(shù)據(jù)庫邏輯 物理表空間數(shù)據(jù)文件操作系統(tǒng)塊段區(qū)Or

33、acle 數(shù)據(jù)塊方案數(shù)據(jù)庫構造完好注釋頁 Database Storage HierarchyTablespaceSegmentsBlocksExtentsExtentsExtentsStorage 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

34、 2 2 1 FREE 1 1 2 2 1EXTENTSS_DEPT(contd)DataSegS_EMPFIRST_NAMEIndexIndexSegOracle DATA BLOCKSRBS1(contd)RBSegRBS2(contd)RBSegRBS1RBSegRBS2RBSegSEGMENTS SYSTEMUSER_DATARBSTEMPTABLESPACESDATA FILES數(shù)據(jù)庫體系構造：構造組件匯總 內存構造 系統(tǒng)全局區(qū) (SGA)：數(shù)據(jù)庫緩沖區(qū)高速緩存、重做緩沖區(qū)以及各種池 程序全局區(qū) (PGA) 進程構造 用戶進程和效力器進程 后臺進程：SMON、PMON、DBWn、CK

35、PT、LGWR、ARCn 等 存儲構造 邏輯：數(shù)據(jù)庫、方案、表空間、段、區(qū)和 Oracle 塊 物理：用于存儲數(shù)據(jù)、參數(shù)、重做和操作系統(tǒng)塊的文件 方案schema什么是方案HR 方案 HR 用戶 擁有 方案約束條件 索引 視圖 序列 暫時表 數(shù)據(jù)字典 方案完好備注頁 4、數(shù)據(jù)庫啟動與封鎖啟動 Oracle 數(shù)據(jù)庫實例：OPEN OPEN MOUNT NOMOUNT SHUTDOWN 已按此實例控制文件的描畫翻開了一切文件 此實例的控制文件已翻開 實例已啟動 STARTUP封鎖方式 A 否 否 否 否 T 否 否 是 是 I 否 否 否 是 封鎖方式 允許新銜接 等待當前會話終了 等待當前事務處

36、置終了 強迫選擇檢查點并封鎖文件 N 否 是 是 是 封鎖方式： A = ABORT I = IMMEDIATE T = TRANSACTIONAL N = NORMAL SHUTDOWN 選項 在以下期間 SHUTDOWN NORMAL： 或 SHUTDOWN TRANSACTIONAL 或 SHUTDOWN IMMEDIATE 一致的數(shù)據(jù)庫 干凈的數(shù)據(jù)庫 封鎖時： 執(zhí)行 IMMEDIATE 時，會回退未提交的更改 數(shù)據(jù)庫緩沖區(qū)高速緩存會寫入到數(shù)據(jù)文件 會釋放資源 啟動時： 不用恢復實例 完好備注頁 SHUTDOWN 選項 封鎖時： 修正正的緩沖區(qū)不寫入到數(shù)據(jù)文件 不回退未提交的更改 在以下

37、期間 SHUTDOWN ABORT 或 實例失敗 或 STARTUP FORCE 不一致的數(shù)據(jù)庫 灰數(shù)據(jù)數(shù)據(jù)庫 啟動時： 運用聯(lián)機重做日志文件重新運用更改 運用復原段回退未提交的更改 會釋放資源 運用 SQL*Plus 啟動和封鎖 oracleEDRSR9P1 oracle$ sqlplus dba1/oracle as sysdba SQL shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL startup ORACLE instance started. Total

38、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 5、數(shù)據(jù)字典、動態(tài)性能視圖數(shù)據(jù)字典：概覽 表 索引 視圖 用戶 方案 過程 等等 方案約束條件 索引 視圖 序列 暫時表 數(shù)據(jù)字典 SELECT * FROM dictionary;數(shù)據(jù)字典視圖 誰可以查詢 內容 下列項的子集

39、 注 DBA_ DBA 一切對象 無 可能有其它僅限 DBA 使用的列 ALL_ 每個用戶 用戶有權查看的一切對象 DBA_ 視圖 包括用戶擁有的對象 USER_ 每個用戶 用戶擁有的一切對象 ALL_ 視圖 除了缺少 OWNER 列，通常與 ALL_ 相同。某些視圖有象 PUBLIC 別名一樣的簡稱。 數(shù)據(jù)字典：用法例如 SELECT USERNAME, ACCOUNT_STATUS FROMdba_users WHERE ACCOUNT_STATUS = OPEN;SELECT table_name, tablespace_name FROM user_tables;SELECT sequ

40、ence_name, min_value, max_value, increment_by FROM all_sequences WHERE sequence_owner IN (MDSYS,XDB);abcDESCRIBE dba_indexes;d動態(tài)性能視圖 經過動態(tài)性能視圖可訪問有關在數(shù)據(jù)庫中更改形狀和條件的信息。 會話數(shù)據(jù) 等待事件 內存分配 運轉 SQL UNDO 用法 翻開游標 重做日志的用法 等等 Oracle 實例 組件 SQL*Plus 初始化參數(shù) 數(shù)據(jù)庫啟動 數(shù)據(jù)庫封鎖 預警日志 性能視圖 動態(tài)性能視圖：用法例如 SQL SELECT sql_text, executio

41、ns 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;abc動態(tài)性能視圖：本卷須知 這些視圖由 SYS 用戶擁有。 不同時間可以運用不同視圖： 實例已啟動。 數(shù)據(jù)庫已裝載。 數(shù)據(jù)庫已翻開。 經過查詢 V$FIXED_TABLE 可查看一切視圖稱號。 這些視圖通常又稱為“v$ 視圖。 由于數(shù)據(jù)是動態(tài)的，因此這些視圖不保證讀取一

42、致性。 6、UNDO數(shù)據(jù)支配 數(shù)據(jù)支配言語 (DML) 由以下 SQL 語句組成： INSERT UPDATE DELETE MERGE DML 一直作為事務處置的一部分執(zhí)行，它可以： 回退，運用 ROLLBACK 命令 提交，運用 COMMIT 命令 復原數(shù)據(jù) 復原數(shù)據(jù)是： 原始的、修正之前的數(shù)據(jù)副本為更改數(shù)據(jù)的每個事務處置而捕獲 至少保管到事務處置終了 用于支持： 回退操作 讀取一致性和閃回查詢 從失敗事務處置中恢復 用戶 復原數(shù)據(jù)完好備注頁 事務處置和復原數(shù)據(jù) 每個事務處置只分配給一個復原段一個復原段可以同時效力多個事務處置復原段 更新事務處置 舊值 新值 表 存儲復原信息 復原信息存儲

43、在復原段中，復原段又存儲在復原表空間中。復原表空間：僅用于復原段 包含特殊的恢復本卷須知 只能與單個實例相關聯(lián) 要求在恣意指定時間，一個給定的實例只能有一個是當前可寫復原表空間復原數(shù)據(jù)與重做數(shù)據(jù) 還原 重做 記錄 如何還原更改 如何重新生成更改 用于 回退、讀取一致性 向前滾動數(shù)據(jù)庫更改 存儲于 還原段 重做日志文件 避免 在多用戶系統(tǒng)中讀取不一致 數(shù)據(jù)丟失 監(jiān)視復原 復原通常不需求管理。要監(jiān)視的范圍包括： 復原表空間中的空閑空間 “快照太舊錯誤 DBA 監(jiān)視復原完好備注頁 管理復原 管理復原應包括防止： 在復原表空間中出現(xiàn)空間錯誤： 正確調整復原表空間的大小。 確保定期提交大型事務處置。 “

44、快照太舊錯誤： 配置適當?shù)膹驮９芷陂g隔。 正確調整復原表空間的大小。 思索確保復原保管期。 運用自動復原管理： DBA UNDO_MANAGEMENT=AUTO UNDO_TABLESPACE=UNDOTBS1 配置復原保管期 UNDO_RETENTION 指定要保管的已提交復原信息量秒數(shù)。必需設置這個參數(shù)的最適宜情況是： 復原表空間啟用了 AUTOEXTEND 選項 要設置 LOB 的復原保管期 要確保保管期 DBA 確保復原保管期 確保：15 分鐘 生成的復原數(shù)據(jù)比可用空間多時事務處置睬失敗。 復原數(shù)據(jù)一直滿足 SELECT語句運轉 15 分鐘或更少時間。 調整復原表空間的大小 當前表空

45、間大小復原占用率運用復原指點 7、銜接Dedicated Server ProcessesClientServerUserprocessServerprocess PMON DBWnSMON LGWRCKPTARCnInstanceSGAOracle Shared ServerShared serverprocessesDatabase serverClientSnnnSnnnUserprocessUserprocessSnnnInstanceSGADispatcherD003DispatcherD001DispatcherD00221Database serverClientConnecti

46、ngUserprocessUserprocessListenerDispatcherprocessD001DispatcherprocessD0023 PMON DBWnSMON LGWRCKPTARCnRequestQueueResponse queue for (D002)Response queue for (D003)InstanceSGAProcessing a RequestShared serverprocessesDatabase serverclientSnnnSnnnUserprocessListenerDispatcherD002DispatcherD003SnnnRes

47、ponse queue for (D001)453216DispatcherD001UserprocessOverviewDatabase serverListenerClientSystem Global AreaOracle background processesDispatcher processesSharedserverprocessesOracle servercode programinterfaceRequest queueResponse queuesThe Listener Processlistener.oraListenerClientServertnsnames.o

48、rasqlnet.oraSpawn and Bequeath ConnectionsListenerServerClientDedicated Server Process 123Direct Hand-Off ConnectionsListenerDispatcher123SharedServerProcessSharedServerProcessRedirected SessionportListenerClientServerportServer ordispatcherprocess123456Static Service Registration:The listener.ora F

49、ileDefault settings for the listener.ora file with Oracle software installation:Listener name LISTENERPort1521Protocols TCP/IP and IPCSID nameDefault instanceHost name Default host nameStatic Service Registration: The listener.ora File1.LISTENER =2.(ADDRESS_LIST =3. (ADDRESS= (PROTOCOL= TCP)(Host= s

50、tc-sun02)(Port= 1521)4.SID_LIST_LISTENER =5. (SID_LIST =6. (SID_DESC =7. (ORACLE_HOME= /home/oracle) (GLOBAL_DBNAME = ORCL.us.oracle)9. (SID_NAME = ORCL)Dynamic Service Registration:Configure RegistrationTo ensure that service registration is functional, the following initialization parameters must

51、be configured:SERVICE_NAMESINSTANCE_NAME Dynamic Service Registration:Registering Information with the ListenerBy default, PMON registers with a local listener on the server on the default local address of TCP/IP, port 1521PMON can register with a non default listener if:LOCAL_LISTENER initializatio

52、n parameter is defined LISTENERS attribute of the DISPATCHERS initialization parameter is defined for Oracle Shared ServerLSNRCTL CommandsUse the following commands to control the listener:Starting the listener:Stopping the listener:$lsnrctl START listener_name$lsnrctl STOP listener_name8、PGAOracle

53、Shared Server: User session data is held in the SGASGAUsersession dataCursor stateShared pool and other memory structuresStackspacePGADedicated Server: User session data is kept in the PGAThe SGA and PGASGAShared pool and other memory structuresStackspaceUser session dataCursor statePGAThe Sorting P

54、rocessIf sort space requirement is greater than SORT_AREA_SIZE:Segments hold data while the server works on another sort runServerprocessSort run 1Sort run 2TEMPORARY tablespaceSort run 2Temporary segmentSort Area and ParametersThe sort space is in:The PGA for a dedicated server connectionThe shared

55、 pool for Oracle Shared Server connectionShared poolPGAStackspaceUser session dataCursor stateUGASort areaStackspacePGAUser session dataCursor stateUGASort areaThe Sorting Process and Temporary SpaceCreate a temporary tablespace by using:Temporary tablespace2MOne single sort segmentPermanentObjects2

56、M2Mtemp01.dbftemp02.dbftemp04.dbfCREATE TEMPORARY TABLESPACE TEMP TEMPFILE$HOME/ORADATA/u06/temp01.dbf size 200M;Diagnostic Toolsv$sysstatSort area (UGA)SORT_AREA_SIZESORT_AREA_RETAINED_SIZEServerprocessTEMPORARYtablespaceSort on diskSort in memoryPCTINCREASEINITIALNEXTStatspackv$sort_usagev$sort_se

57、gment9、CHECKPIONTDatabase CheckpointsCheckpoints are used to determine where recovery should startCheckpoint position - where recovery startsCheckpoint queue - link list of dirty blocksTypes of CheckpointsFull checkpointAll dirty buffers are writtenSHUTDOWN NORMAL, IMMEDIATE, or TRANSACTIONALALTER S

58、YSTEM CHECKPOINTIncremental checkpoint (Fast-Start checkpoint)Periodic writesOnly write the oldest blocksPartial checkpointDirty buffers belonging to the tablespaceALTER TABLESPACE BEGIN BACKUPALTER TABLESPACE tablespace OFFLINE NORMALCKPT ProcessInstanceSGADatabase buffer cacheLarge poolJava poolSh

59、ared poolData dict.cacheShared SQLand PL/SQLARCnUserprocessServerprocessPGADatabaseDatafile 1Datafile 2Datafile 3ControlfileRedo log file 1Redo log file 2ParameterfilePasswordfileArchived log files PMONSMON LGWRCKPTRedo log buffer DBWnDatabase SynchronizationAll datafiles (except offline and read-on

60、ly) must be synchronized for the database to open.Synchronization is based on the current checkpoint number.Applying changes recorded in the redo log files synchronizes datafiles.Redo log files are automatically requested by the Oracle server.DatabaseDatafile 1UndoDatafileDatafile 3ControlfileRedo l