oracle培训 (2)ppt课件

1、主要培训内容1、直观概览2、Sql语句种类3、体系构造4、数据库启动与封锁5、数据字典、动态性能视图6、UNDO7、衔接8、PGA9、CHECKPIONT10、锁11、事务12、SCN13、挪动数据；备份、恢复见备份与恢复引见.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、体系构造数据库体系构造：概述 存储构造内存构造进程构造实例 系统全局区 (SGA)后台进程数据库文件Oracle 内存构造 Java 池数据库 缓冲区高速缓存重做日志 缓冲区共享池大型池SGA 流池效力器进程 1 PGA效力器 进程 2 PGA后台进程PGAOracle 内存构造完好注释页 Oracle

20、 进程 系统 监视器 (SMON) 数据库 写进程 (DBWn) 检查点 (CKPT) 日志写进程 (LGWR) 进程 监视器 (PMON) 归档程序 (ARCn)效力器 进程 效力器 进程 效力器 进程 效力器 进程 后台进程系统全局区 (SGA) 概述 Oracle 实例管理SGA 数据库 写进程 (DBWn) 日志写进程 (LGWR) 数据库 缓冲区高速缓存 重做日志 缓冲区 1用户进程2例如： 启动数据库实例 建立衔接 系统 监视器 (SMON) 进程 监视器 (PMON) 数据文件重做日志文件实例效力器 进程 3概述 Oracle 实例管理 用户更新了某一行数据重做日志文件实例 SG

21、A数据库 写进程 (DBWn) 数据库 缓冲区高速缓存 重做日志缓冲区效力器 进程 用户进程 4107658例如：处置 SQL 语句 归档程序 (ARCn)9检查点 (CKPT) 数据文件存档日志控制文件日志写进程 (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物理构造物理数据库构造 联机重做日志文件口令文件 参数文件 归档日志文件 控制文件 数据文件 预警和跟踪日志文件 备份文件 物理数据库构造完好注释页 逻辑和物理数据库构造 数据库逻辑 物理表空间数据文件操作系统块段区Or

33、acle 数据块方案数据库构造完好注释页 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数据库体系构造：构造组件汇总 内存构造 系统全局区 (SGA)：数据库缓冲区高速缓存、重做缓冲区以及各种池 程序全局区 (PGA) 进程构造 用户进程和效力器进程 后台进程：SMON、PMON、DBWn、CK

35、PT、LGWR、ARCn 等 存储构造 逻辑：数据库、方案、表空间、段、区和 Oracle 块 物理：用于存储数据、参数、重做和操作系统块的文件 方案schema什么是方案HR 方案 HR 用户 拥有 方案约束条件 索引 视图 序列 暂时表 数据字典 方案完好备注页 4、数据库启动与封锁启动 Oracle 数据库实例：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 一致的数据库 干净的数据库 封锁时： 执行 IMMEDIATE 时，会回退未提交的更改 数据库缓冲区高速缓存会写入到数据文件 会释放资源 启动时： 不用恢复实例 完好备注页 SHUTDOWN 选项 封锁时： 修正正的缓冲区不写入到数据文件 不回退未提交的更改 在以下

37、期间 SHUTDOWN ABORT 或 实例失败 或 STARTUP FORCE 不一致的数据库 灰数据数据库 启动时： 运用联机重做日志文件重新运用更改 运用复原段回退未提交的更改 会释放资源 运用 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、数据字典、动态性能视图数据字典：概览 表 索引 视图 用户 方案 过程 等等 方案约束条件 索引 视图 序列 暂时表 数据字典 SELECT * FROM dictionary;数据字典视图 谁可以查询 内容 下列项的子集

39、 注 DBA_ DBA 一切对象 无 可能有其它仅限 DBA 使用的列 ALL_ 每个用户 用户有权查看的一切对象 DBA_ 视图 包括用户拥有的对象 USER_ 每个用户 用户拥有的一切对象 ALL_ 视图 除了缺少 OWNER 列，通常与 ALL_ 相同。某些视图有象 PUBLIC 别名一样的简称。 数据字典：用法例如 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动态性能视图 经过动态性能视图可访问有关在数据库中更改形状和条件的信息。 会话数据 等待事件 内存分配 运转 SQL UNDO 用法 翻开游标 重做日志的用法 等等 Oracle 实例 组件 SQL*Plus 初始化参数 数据库启动 数据库封锁 预警日志 性能视图 动态性能视图：用法例如 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动态性能视图：本卷须知 这些视图由 SYS 用户拥有。 不同时间可以运用不同视图： 实例已启动。 数据库已装载。 数据库已翻开。 经过查询 V$FIXED_TABLE 可查看一切视图称号。 这些视图通常又称为“v$ 视图。 由于数据是动态的，因此这些视图不保证读取一

42、致性。 6、UNDO数据支配 数据支配言语 (DML) 由以下 SQL 语句组成： INSERT UPDATE DELETE MERGE DML 一直作为事务处置的一部分执行，它可以： 回退，运用 ROLLBACK 命令 提交，运用 COMMIT 命令 复原数据 复原数据是： 原始的、修正之前的数据副本为更改数据的每个事务处置而捕获 至少保管到事务处置终了 用于支持： 回退操作 读取一致性和闪回查询 从失败事务处置中恢复 用户 复原数据完好备注页 事务处置和复原数据 每个事务处置只分配给一个复原段一个复原段可以同时效力多个事务处置复原段 更新事务处置 旧值 新值 表 存储复原信息 复原信息存储

43、在复原段中，复原段又存储在复原表空间中。复原表空间：仅用于复原段 包含特殊的恢复本卷须知 只能与单个实例相关联 要求在恣意指定时间，一个给定的实例只能有一个是当前可写复原表空间复原数据与重做数据 还原 重做 记录 如何还原更改 如何重新生成更改 用于 回退、读取一致性 向前滚动数据库更改 存储于 还原段 重做日志文件 避免 在多用户系统中读取不一致 数据丢失 监视复原 复原通常不需求管理。要监视的范围包括： 复原表空间中的空闲空间 “快照太旧错误 DBA 监视复原完好备注页 管理复原 管理复原应包括防止： 在复原表空间中出现空间错误： 正确调整复原表空间的大小。 确保定期提交大型事务处置。 “

44、快照太旧错误： 配置适当的复原保管期间隔。 正确调整复原表空间的大小。 思索确保复原保管期。 运用自动复原管理： DBA UNDO_MANAGEMENT=AUTO UNDO_TABLESPACE=UNDOTBS1 配置复原保管期 UNDO_RETENTION 指定要保管的已提交复原信息量秒数。必需设置这个参数的最适宜情况是： 复原表空间启用了 AUTOEXTEND 选项 要设置 LOB 的复原保管期 要确保保管期 DBA 确保复原保管期 确保：15 分钟 生成的复原数据比可用空间多时事务处置睬失败。 复原数据一直满足 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