欲事之无繁，则必劳于始而逸于终
No results found
oracle 11g默认的自动收集统计信息的时间是22:00–2:00。
但这个时段在公司大型活动期间是业务的高峰期，给本已紧张的系统带来更大的负担。所以，需要把自动执行的时间改到空闲的时段。
col WINDOW_NAME for a20 col REPEAT_INTERVAL for a60 col DURATION for a30 set linesize 120 SELECT t1.window_name, t1.repeat_interval, t1.duration,enabled FROM dba_scheduler_windows t1, dba_scheduler_wingroup_members t2 WHERE t1.window_name = t2.window_name AND t2.window_group_name IN ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED'); WINDOW_NAME REPEAT_INTERVAL DURATION ENABL -------------------- ------------------------------------------------------------ ------------------------------ ----- MONDAY_WINDOW freq=daily;byday=MON;byhour=12;byminute=0; bysecond=0 +000 04:00:00 TRUE TUESDAY_WINDOW freq=daily;byday=TUE;byhour=12;byminute=0; bysecond=0 +000 04:00:00 TRUE WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=12;byminute=0; bysecond=0 +000 04:00:00 TRUE THURSDAY_WINDOW freq=daily;byday=THU;byhour=12;byminute=0; bysecond=0 +000 04:00:00 TRUE FRIDAY_WINDOW freq=daily;byday=FRI;byhour=12;byminute=0; bysecond=0 +000 04:00:00 TRUE SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE # WINDOW_NAME：任务名 # REPEAT_INTERVAL：任务重复间隔时间 # DURATION：持续时间 |
开启归档SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 44
Next log sequence to archive 46
Current log sequence 46
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 10G
recovery_parallelism integer 0
shutdown immediate startup mount alter session set events '10046 trace name context forever,level 8'; alter database open; col FILE_NAME for a50 select FILE_ID,FILE_NAME from dba_data_files; FILE_ID FILE_NAME ---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/orcl/system01.dbf 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 4 /u01/app/oracle/oradata/orcl/users01.dbf select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10932.trc 从trace中能看到和分析数据库OPEN读取数据文件头的过程 #-------------------------------------------------------------------------------------------------------------------------- WAIT #139880206935832: nam='control file sequential read' ela= 11 file#=0 block#=23 blocks=1 obj#=-1 tim=1523176482644848 WAIT #139880206935832: nam='db file sequential read' ela= 20 file#=1 block#=1 blocks=1 obj#=-1 tim=1523176482644960 WAIT #139880206935832: nam='control file sequential read' ela= 12 file#=0 block#=179 blocks=1 obj#=-1 tim=1523176482645079 WAIT #139880206935832: nam='db file sequential read' ela= 11 file#=2 block#=1 blocks=1 obj#=-1 tim=1523176482645169 WAIT #139880206935832: nam='db file sequential read' ela= 10 file#=3 block#=1 blocks=1 obj#=-1 tim=1523176482645258 WAIT #139880206935832: nam='db file sequential read' ela= 26 file#=4 block#=1 blocks=1 obj#=-1 tim=1523176482645328 #-------------------------------------------------------------------------------------------------------------------------- |
alter session set events 'immediate trace name controlf level <n>'; 如： alter session set events 'immediate trace name controlf level 1'; # 通过以下命令查看dump trace文件的位置 select * from v$diag_info where NAME='Default Trace File'; |
level
:
level 1: Generic File Header
Level 2: Level 1 + database information + database entry + check point
progress records + Extended database entry
level 3 or Higher< 9: level 2 + reuse record section
level 10: Memory dump of all the control file logical blocks
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 110 Next log sequence to archive 112 Current log sequence 112 SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE set line 150 set pagesize 9999 col name for a60 select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS ------- ------------------------------------------------------------ --- ---------- -------------- /u01/app/oracle/oradata/orcl/control01.ctl NO 16384 594 /u01/app/oracle/fast_recovery_area/orcl/control02.ctl NO 16384 594 # 为方便后续测试，先生成一个创建控制文件的脚本(也可以不生成，创建脚本可以oracle官方文档中找到) alter database backup controlfile to trace as '/tmp/control_bak.sql'; |
本课程就是基于Oracle DSI403e学习Oracle特殊恢复
DSI是Data Server Internals的缩写，是Oracle公司内部用来培训Oracle售后工程师使用的教材
DSI课程系统包括：
