Oracle基础知识Word格式文档下载.docx

1、Fixed Size 2089336 bytesVariable Size 486542984 bytesDatabase Buffers 570425344 bytesRedo Buffers 6295552 bytesDatabase mounted. alter database open;Crash Recovery将从alter database open开始，我们来观察其日志=alert.log=alter database openTue Jun 14 18:19:53 2011Beginning crash recovery of 1 threadsparallel recov

2、ery started with 2 processesStarted redo scanCompleted redo scan0 redo blocks read, 0 data blocks need recoveryStarted redo application atThread 1: logseq 1004, block 1124, scn 17136185Recovery of Online Redo Log: Thread 1 Group 2 Seq 1004 Reading mem 0 Mem# 0: /flashcard/oradata/G10R2/onlinelog/o1_

3、mf_2_6v34jokt_.log Mem# 1: /s01/flash_recovery_area/G10R2/onlinelog/o1_mf_2_6v34jotq_.logCompleted redo applicationCompleted crash recovery at logseq 1004, block 1124, scn 171561860 data blocks read, 0 data blocks written, 0 redo blocks readLGWR: STARTING ARCH PROCESSESARC0: Archival started STARTIN

4、G ARCH PROCESSES COMPLETEARC0 started with pid=16, OS id=7829Thread 1 advanced to log sequence 1005 （thread open）Thread 1 opened at log sequence 1005 Current log# 3 seq# 1005 mem# 0: /flashcard/oradata/G10R2/onlinelog/o1_mf_3_6v34jpmp_.log Current log# 3 seq# 1005 mem# 1: /s01/flash_recovery_area/G1

5、0R2/onlinelog/o1_mf_3_6v34jpyn_.logSuccessful open of redo thread 1 Becoming the no FAL ARCHno SRL Becoming the heartbeat ARCHSMON: enabling cache recoverydb_recovery_file_dest_size of 204800 MB is 6.81% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for

6、 recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.54 2011Successfully onlined Undo Tablespace 1. enabling tx recoveryDatabase Characterset is UTF8Opening with internal Resource Manager planwhere NUMA PG = 1, CPUs = 2replication_d

7、ependency_tracking turned off （no async multimaster replication found）Starting background process QMNCQMNC started with pid=17, OS id=783155 2011Completed: alter database open注意上述单实例Crash Recovery到数据库打开的整个过程: Beginning crash recovery of 1 threads Started redo scan Completed redo scan Started redo ap

8、plication at Completed redo application Completed crash recovery at SMON: Successfully onlined Undo Tablespace 1 Completed:从上述步骤中我们可以看到三种恢复名词，即： crash recovery cache recovery tx recovery这和官方文档所描述的Crash Recovery概念是不一致的，我们现在来理清这几种recovery。crash recovery包含对redo的scan和application，显然其完成的是Rolling Forward前滚

9、的工作，告警日志中出现的crash recovery等同于官方文档中介绍的”cache recovery”，我们可以将” Completed crash recovery”看做前滚完成的标志。而tx recovery从字面就可以看出实际上是Transaction Recovery，tx recovery发生在Undo Tablespace online之后（回滚事务的前提是Undo可用），数据完成打开操作之前（“Completed: alter database open”）。注意tx recovery并不要求数据库打开前完成，仅仅是在数据库打开之前由smon启动（“SMON: enablin

10、g tx recovery”）。剩下的唯一的问题是，这里的cache recovery是什么？显然它不是官方文档中所描述的”cache recovery”，几乎没有任何文档介绍存在这样一个recovery操作，这也是本文重点要介绍的。我们来看另一个演示，这个演示用以说明cache recovery还存在于最普通的不包含Crash Recovery的数据库打开过程中: shutdown immediate;Database closed.Database dismounted.Database altered. select * from v$version;BANNER-Oracle Data

11、base 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - ProductionCORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4.0 - ProductionNLSRTL Version 10.2.0.4.0 - Production select * from global_name;GLOBAL_NAME-43:52 2011ARC0 started with pid=14, OS id=8133Current log# 3

12、 seq# 1005 mem# 0:Current log# 3 seq# 1005 mem# 1:Incremental checkpoint up to RBA 0x3ed.624.0, current log tail at RBA 0x3ed.944.0QMNC started with pid=15, OS id=8135因为是clean shutdown，所以这里不存在crash recovery。但这里同样出现了”SMON: enabling cache recovery”，可见cache recovery是每次实例启动instance startup必要执行的一种恢复操作。但问

13、题是，这个恢复操作到底针对何种对象？实际上cache recovery所要恢复的是rowcache，也就是我们常说的字典缓存（dictionary cache）。关于这个结论，肯定有很多人要问我这样说的依据是什么，对应于这个”cache recovery”的问题，我们很难从google中得到一些启示，因为它和官方文档所描述的”cache recovery-rolling forward”存在重名的关系。为了证明cache recovery所恢复的是rowcache，我们需要一个实证，从正式的系统中得到验证。要做到这一点是比较困难的，我们需要Oracle愿意把整个database open的过程

14、变成慢动作来供我们参考，验证要用到一些调试工具，例如gdb或者dbx。我们首先将实例启动到mount状态，并对执行startup的LOCAL进程做gdb的breakpoint断点调试:找出LOCAL进程的系统进程号SPID select spid from v$process2 where addr in （3 select paddr from v$session4 where sid=（select distinct sid from v$mystat）5 /SPID-8326在实例startup nomount/mount后共享池的library cache就是可用的 select na

15、mespace from v$librarycache where gets!=0;NAMESPACE-SQL AREATABLE/PROCEDURE而rowcache则尚未被填充，因为字典缓存来源于自举对象（bootstrap$）和字典基表 select parameter,count,gets from v$rowcache where count!no rows selected另开一个terminal窗口，并执行对LOCAL进程8326的gdb breakpoint调试oraclerh2 $ gdb $ORACLE_HOME/bin/oracle 8326GNU gdb （GDB）Re

16、d HatEnterprise Linux （7.0.1-23.el5）Copyright （C） 2009 Free Software Foundation, Inc.License GPLv3+: GNU GPL version 3 or laterThis is free software: you are free to change and redistribute it.There is NO WARRANTY, to the extent permitted by law. Type show copyingand show warranty for details.This G

17、DB was configured as x86_64-RedHat-linux-gnu.For bug reporting instructions, please see:.Reading symbols from /s01/db_1/bin/oracle.（no debugging symbols found）.done.Attaching to program: /s01/db_1/bin/oracle, process 8326Reading symbols from /s01/db_1/lib/libskgxp10.so.（no debugging symbols found）.d

18、one.Loaded symbols for /s01/db_1/lib/libskgxp10.soReading symbols from /s01/db_1/lib/libhasgen10.so.（no debugging symbols found）.done.Loaded symbols for /s01/db_1/lib/libhasgen10.soReading symbols from /s01/db_1/lib/libskgxn2.so.（no debugging symbols found）.done.Loaded symbols for /s01/db_1/lib/libs

19、kgxn2.soReading symbols from /s01/db_1/lib/libocr10.so.（no debugging symbols found）.done.Loaded symbols for /s01/db_1/lib/libocr10.soReading symbols from /s01/db_1/lib/libocrb10.so.（no debugging symbols found）.done.Loaded symbols for /s01/db_1/lib/libocrb10.soReading symbols from /s01/db_1/lib/liboc

20、rutl10.so.（no debugging symbols found）.done.Loaded symbols for /s01/db_1/lib/libocrutl10.soReading symbols from /s01/db_1/lib/libjox10.so.（no debugging symbols found）.done.Loaded symbols for /s01/db_1/lib/libjox10.soReading symbols from /s01/db_1/lib/libclsra10.so.（no debugging symbols found）.done.L

21、oaded symbols for /s01/db_1/lib/libclsra10.soReading symbols from /s01/db_1/lib/libdbcfg10.so.（no debugging symbols found）.done.Loaded symbols for /s01/db_1/lib/libdbcfg10.soReading symbols from /s01/db_1/lib/libnnz10.so.（no debugging symbols found）.done.Loaded symbols for /s01/db_1/lib/libnnz10.soReading symbols from /usr/lib6