数据库版本11.2.0.4
1.使用10046事件查看数据库OPEN时的TRACE文件:
SYS@ bys3>startup mount;ORACLE instance started.
Total System Global Area 150654976 bytes
Fixed Size 1363216 bytes
Variable Size 96469744 bytes
Database Buffers 50331648 bytes
Redo Buffers 2490368 bytes
Database mounted.
SYS@ bys3>alter session set events='10046 trace name context forever,level 12';
Session altered.
SYS@ bys3>alter database open;
Database altered.
SYS@ bys3>select value from v$diag_info where name like 'Default%'; 其它查看TRACE文件位置方法是:oradebug setmypid; oradebug tracefile_name 或者直接在user_dump_dest目录中使用 ls -alt 找最近生成的文件
VALUE
-------------------------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_13020.trc
####################################
引导过程: 关于root dba及 bootstrap$,详见:http://blog.csdn.net/q947817003/article/details/17025955
2.然后用db file sequential read方式读取了file 1 block#520的内容(这一步应该有从file 1 blcok 1找到root dba来定位到file#1 block#520,不知道怎么从TRACE文件中看???)
WAIT #3064435524: nam='db file sequential read' ela= 264 file#=1 block#=520 blocks=1 obj#=-1 tim=1385703907158975
3.ORACLE找到了file#=1 block#=520后,读取其内容,在内存中创建:
PARSING IN CURSOR #3064429824 len=188 dep=1 uid=0 oct=1 lid=0 tim=1385703907172599 hv=4006182593 ad='257ea26c' sqlid='32r4f1brckzq1'
create table bootstrap$ (
END OF STMT
4.此后ORACLE通过递归查询,从bootstrap$中获取创建对象的语句:
PARSING IN CURSOR #3064429824 len=55 dep=1 uid=0 oct=3 lid=0 tim=1385703907195679 hv=2111436465 ad='257e8e88' sqlid='6apq2rjyxmxpj'
select line#, sql_text from bootstrap$ where obj# != :1
5.然后,根据查出语句,创建这些对象:如下: 这些创建对象语句在数据库中也通过select * from bootstrap$ 可以查到
=====================
PARSING IN CURSOR #3064429824 len=129 dep=1 uid=0 oct=36 lid=0 tim=1385703907299714 hv=1119914026 ad='b6a51d5c' sqlid='864bmh11c121a'
CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
END OF STMT
PARSE #3064429824:c=999,e=31611,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1385703907299710
EXEC #3064429824:c=1000,e=18763,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1385703907318789
CLOSE #3064429824:c=0,e=7,dep=1,type=0,tim=1385703907318912
=====================
6.再然后,创建完这些对象后,就正常打开数据库。
########################################
2.关于转储文件中的几个等待事件简单说明:
Disk file operations I/OThis event is used to wait for disk file operations (for example, open, close, seek, and resize). It is also used for miscellaneous I/O operations such as block dumps and password file accesses.
control file sequential read
控制文件连续读
rdbms ipc reply
这个事件说明等待一个后台进程的响应。
db file sequential read
等待时间是由于执行对索引,回滚(undo)段,和表(当借助rowid来访问),控制文件和数据文件头的单块读操作SQL语句(用户和递归)引起的。
control file parallel write-控制文件并行写
当server 进程更新所有控制文件时,这个事件可能出现。如果等待很短,可以不用考虑。如果等待时间较长,检查存放控制文件的物理磁盘I/O 是否存在瓶颈
########################################
3.TRACE文件内容---截取部分
Trace file /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_13020.trcOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u0