itspace
- 浏览: 978395 次
- 性别:
- 来自: 杭州
-
文章分类
最新评论
-
孤星119：
好熟悉的数据库字段啊， 上家公司做的项目每天都跟这些字段打招呼 ...
Oracle exp compress参数引起的空间浪费 -
itspace：
quxiaoyong 写道遇到个问题，网上一搜，全他妈这篇文章 ...
数据库连接错误ORA-28547 -
quxiaoyong：
遇到个问题，网上一搜，全他妈这篇文章。你转来转去的有意思吗？
数据库连接错误ORA-28547 -
hctech：
关于version count过高的问题，不知博主是否看过ey ...
某客户数据库性能诊断报告 -
itspace：
invalid 写道写的不错，我根据这个来安装，有点理解错误了 ...
AIX 配置vncserver
数据库在异常宕机之后，数据库再次启动时，smon会进行crash recover之后再进行tx recover，这在alert日志可以清晰的看到smon的操作过程。
但事情发展往往不如人意，试想一下当回滚段出现异常时，数据库势必不能启动，这时候我们往往需要借助一些异常手段，将数据库open。
Oracle的一些内部事件允许设置debug_mode，以便允许在AUM模式下，手工进行回滚段的处理：
alter session set "_smu_debug_mode"=4;
alter rollback segment "_SYSSMU7$" ONLINE;
或者使用隐含参数offline_rollback_segments进行指定回滚段处理：
_offline_rollback_segments
drop rollback segment ‘xxx’
如果回滚段正常，某些恢复可能需要很长的时间，在此期间的一些异常可能导致SMON Crash，进而数据库Instance崩溃。这时候我们可以引进一些事件进行诊断。
如：
禁用smon恢复
event = '10513 trace name context forever,level 2'
跟踪smon进程
event = '10500 trace name context forever, level 1'
测试案例。
不设置event 系统为初始状态
SQL> show parameter event
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event
并产生大批量脏数据
SQL> insert into testsmon1 select * from testsmon1;
81920 rows created
数据库重启，注意这里使用的是force选项
SQL> startup force
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 1262164 bytes
Variable Size 494931372 bytes
Database Buffers 327155712 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
我们在alert日志里可以看到
ALTER DATABASE OPEN
Fri Dec 17 16:30:49 2010
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Fri Dec 17 16:30:49 2010
Started redo scan
Fri Dec 17 16:30:49 2010
Completed redo scan
28142 redo blocks read, 1885 data blocks need recovery
Fri Dec 17 16:30:49 2010
Started redo application at
Thread 1: logseq 66211, block 3
Fri Dec 17 16:30:49 2010
Recovery of Online Redo Log: Thread 1 Group 5 Seq 66211 Reading mem 0
Mem# 0: /oradata/mcstar/mcstar/redo05.log
Fri Dec 17 16:30:49 2010
Completed redo application
Fri Dec 17 16:30:49 2010
db_recovery_file_dest_size of 10240 MB is 79.77% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Dec 17 16:30:50 2010
Completed crash recovery at
Thread 1: logseq 66211, block 28145, scn 10995135133737
1885 data blocks read, 1885 data blocks written, 28142 redo blocks read
Fri Dec 17 16:30:50 2010
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=29, OS id=16094
Fri Dec 17 16:30:50 2010
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=30, OS id=16111
Fri Dec 17 16:30:50 2010
Thread 1 advanced to log sequence 66212
Thread 1 opened at log sequence 66212
Current log# 4 seq# 66212 mem# 0: /oradata/mcstar/mcstar/redo04.log
Successful open of redo thread 1
Fri Dec 17 16:30:50 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 17 16:30:50 2010
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Fri Dec 17 16:30:50 2010
SMON: enabling cache recovery
Fri Dec 17 16:30:50 2010
ARC0: Becoming the heartbeat ARCH
Fri Dec 17 16:30:50 2010
Successfully onlined Undo Tablespace 12.
Fri Dec 17 16:30:50 2010
SMON: enabling tx recovery
[ora10g@mcprod bdump]$ more mcstar_smon_15889.trc
/ora10g/app/admin/mcstar/bdump/mcstar_smon_15889.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /ora10g/oracle/product/10.2.0/db_1
System name: Linux
Node name: mcprod
Release: 2.6.18-53.el5PAE
Version: #1 SMP Mon Nov 12 02:55:09 EST 2007
Machine: i686
Instance name: mcstar
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 15889, image: oracle@mcprod (SMON)
*** SERVICE NAME:() 2010-12-17 16:30:51.329
*** SESSION ID:(549.1) 2010-12-17 16:30:51.329
Dead transaction 0x000a.02d.0006936b recovered by 16 server(s)
*** 2010-12-17 16:30:51.332
SMON: Parallel transaction recovery tried
现在我们创建一个测试表格
SQL> select OBJECT_ID from dba_objects where owner='ZHOU' and OBJECT_NAME='T';
OBJECT_ID
----------
2275996
SQL> declare
2 begin
3 for i in 1..100000 loop
4 insert into t values(i,i+1);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from v$bh where objd=2275996;
COUNT(*)
----------
434
现在将其重启
SQL> startup force
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 1262164 bytes
Variable Size 503319980 bytes
Database Buffers 318767104 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
后台我们看到了smon在进行并行恢复，对象2275996的block已经在内存中，并以current状态存在
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
STATUS COUNT(*)
------- ----------
xcur 212
我们将其从内存中刷出
SQL> alter system flush buffer_cache;
System altered.
可以看到都为内存free状态
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
STATUS COUNT(*)
------- ----------
free 392
现在我们查询表格T，数据会从哪里获取呢?从业务表空间还是回滚表空间呢？
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> select count(*) from t;
COUNT(*)
----------
0
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
STATUS COUNT(*)
------- ----------
xcur 425
free 275
观察后台trc进程可以看到，数据库都从业务表空间获取，可见smon进程在数据库启动时已经完成tx恢复。
现在测试的重点来了，引进一个事件
SQL> alter system set event='10513 trace name context forever,level 2' scope=spfile;
System altered.
继续在业务表空间创建大量脏数据
SQL> declare
2 begin
3 for i in 1..100000 loop
4 insert into t values(i,i+1);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
STATUS COUNT(*)
------- ----------
xcur 368
将系统重启，可以看到event已经生效
SQL> startup force
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 1262164 bytes
Variable Size 528485804 bytes
Database Buffers 293601280 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
SQL> show parameter event
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string 10513 trace name context forev
er,level 2
进一步观察alert日志，依然出现SMON: enabling tx recovery字段。
Fri Dec 17 17:02:23 2010
ALTER DATABASE OPEN
Fri Dec 17 17:02:23 2010
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Fri Dec 17 17:02:23 2010
Started redo scan
Fri Dec 17 17:02:23 2010
Completed redo scan
91999 redo blocks read, 2752 data blocks need recovery
Fri Dec 17 17:02:23 2010
Started redo application at
Thread 1: logseq 66217, block 3
Fri Dec 17 17:02:23 2010
Recovery of Online Redo Log: Thread 1 Group 5 Seq 66217 Reading mem 0
Mem# 0: /oradata/mcstar/mcstar/redo05.log
Fri Dec 17 17:02:24 2010
Completed redo application
Fri Dec 17 17:02:24 2010
Completed crash recovery at
Thread 1: logseq 66217, block 92002, scn 10995135225524
2752 data blocks read, 2752 data blocks written, 91999 redo blocks read
Fri Dec 17 17:02:24 2010
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=29, OS id=467
Fri Dec 17 17:02:24 2010
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=30, OS id=469
Fri Dec 17 17:02:24 2010
Thread 1 advanced to log sequence 66218
Thread 1 opened at log sequence 66218
Current log# 4 seq# 66218 mem# 0: /oradata/mcstar/mcstar/redo04.log
Successful open of redo thread 1
Fri Dec 17 17:02:24 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 17 17:02:24 2010
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Fri Dec 17 17:02:24 2010
ARC1: Becoming the heartbeat ARCH
Fri Dec 17 17:02:24 2010
SMON: enabling cache recovery
Fri Dec 17 17:02:24 2010
Successfully onlined Undo Tablespace 12.
Fri Dec 17 17:02:24 2010
SMON: enabling tx recovery
Fri Dec 17 17:02:24 2010
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Dec 17 17:02:24 2010
Streams APPLY A001 started with pid=31, OS id=471
Fri Dec 17 17:02:24 2010
Streams APPLY A002 started with pid=32, OS id=473
Fri Dec 17 17:02:24 2010
Starting background process QMNC
QMNC started with pid=33, OS id=475
Fri Dec 17 17:02:25 2010
Completed: ALTER DATABASE OPEN
但是没有相应trace文件产生，如果要产生trace文件，我们可以加另外一个event 10500，重复上述过程。
SQL> alter system set event='10513 trace name context forever,level 2','10500 trace name context forever, level 1' scope=spfile;
System altered.
数据库重启之后
SQL> startup force
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 1262164 bytes
Variable Size 536874412 bytes
Database Buffers 285212672 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
可以看到跟踪文件smon的状态
[ora10g@mcprod bdump]$ more mcstar_smon_11666.trc
/ora10g/app/admin/mcstar/bdump/mcstar_smon_11666.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /ora10g/oracle/product/10.2.0/db_1
System name: Linux
Node name: mcprod
Release: 2.6.18-53.el5PAE
Version: #1 SMP Mon Nov 12 02:55:09 EST 2007
Machine: i686
Instance name: mcstar
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 11666, image: oracle@mcprod (SMON)
*** SERVICE NAME:() 2010-12-17 17:08:12.897
*** SESSION ID:(549.1) 2010-12-17 17:08:12.897
*** 2010-12-17 17:08:12.897
SMON: Event 10513 is level 2, trans recovery disabled.
kglScanDependencyHandles4Unpin():
cumscan=1 cumupin=2 time=64424509440 upinned=0
*** 2010-12-17 17:08:12.976
SMON: Event 10513 is level 2, trans recovery disabled.
*** 2010-12-17 17:08:13.245
SMON: Event 10513 is level 2, trans recovery disabled.
*** 2010-12-17 17:08:13.299
SMON: Event 10513 is level 2, trans recovery disabled.
*** 2010-12-17 17:08:13.469
SMON: Event 10513 is level 2, trans recovery disabled.
*** 2010-12-17 17:08:13.477
SMON: Event 10513 is level 2, trans recovery disabled.
*** 2010-12-17 17:08:13.593
SMON: Event 10513 is level 2, trans recovery disabled.
但是alert日志里面显示，这跟trace矛盾。
Fri Dec 17 17:08:12 2010
SMON: enabling tx recovery
但是在数据库打开之后，我们看到内存中并没有对象2275996
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
no rows selected
观察业务数据从哪里获取呢？
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> select count(*) from t;
COUNT(*)
----------
0
可以看到有cr状态，推断出有部分数据从undo表空间中获取
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
STATUS COUNT(*)
------- ----------
xcur 501
cr 408
查看跟踪文件，可以看到和cr一样
[ora10g@mcprod udump]$ cat mcstar_ora_26883.trc|grep "uba: 0x008"|wc -l
408
最后将event恢复初始
SQL> alter system set event='' scope=spfile;
System altered.
再次重启系统
可以看到smon将0x000a.02d.00069371和0x0001.021.0006881d作为死事务处理
[ora10g@mcprod bdump]$ more mcstar_smon_14715.trc
/ora10g/app/admin/mcstar/bdump/mcstar_smon_14715.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /ora10g/oracle/product/10.2.0/db_1
System name: Linux
Node name: mcprod
Release: 2.6.18-53.el5PAE
Version: #1 SMP Mon Nov 12 02:55:09 EST 2007
Machine: i686
Instance name: mcstar
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 14715, image: oracle@mcprod (SMON)
*** SERVICE NAME:(SYS$BACKGROUND) 2010-12-17 17:32:48.011
*** SESSION ID:(549.1) 2010-12-17 17:32:48.011
Dead transaction 0x000a.02d.00069371 recovered by 15 server(s)
Dead transaction 0x0001.021.0006881d recovered by 15 server(s)
*** 2010-12-17 17:32:48.048
SMON: Parallel transaction recovery tried
回过头去看之前的跟踪日志，完全印证之前的事务。
[ora10g@mcprod udump]$ cat mcstar_ora_26883.trc|grep 00069371|wc -l
204
You have new mail in /var/spool/mail/ora10g
[ora10g@mcprod udump]$ cat mcstar_ora_26883.trc|grep 0006881d|wc -l
204
[ora10g@mcprod udump]$ cat mcstar_ora_26883.trc|grep "uba: 0x008"|wc -l
408
当然在设了event 10513之后，我们可以从x$ktuxe获取死事务的相关详细，详见
我的另外一篇文章http://itspace.iteye.com/blog/644509
answer1：死事务并不是指不能回滚的事务，但死事务一般恢复会比较慢。从x$ktuxe根据相关字段可以看出死事务的恢复情况。
answer2：在同一个会话中，如果之前曾做过DML语句，如果此时进行更新插入等操作，如果涉及到的数据块在内存中，Oracle就再次更改该内存中数据块，不会从undo中读取内容。但如果是不同会话，那就分很多情况了。
但事情发展往往不如人意，试想一下当回滚段出现异常时，数据库势必不能启动，这时候我们往往需要借助一些异常手段，将数据库open。
Oracle的一些内部事件允许设置debug_mode，以便允许在AUM模式下，手工进行回滚段的处理：
alter session set "_smu_debug_mode"=4;
alter rollback segment "_SYSSMU7$" ONLINE;
或者使用隐含参数offline_rollback_segments进行指定回滚段处理：
_offline_rollback_segments
drop rollback segment ‘xxx’
如果回滚段正常，某些恢复可能需要很长的时间，在此期间的一些异常可能导致SMON Crash，进而数据库Instance崩溃。这时候我们可以引进一些事件进行诊断。
如：
禁用smon恢复
event = '10513 trace name context forever,level 2'
跟踪smon进程
event = '10500 trace name context forever, level 1'
测试案例。
不设置event 系统为初始状态
SQL> show parameter event
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event
并产生大批量脏数据
SQL> insert into testsmon1 select * from testsmon1;
81920 rows created
数据库重启，注意这里使用的是force选项
SQL> startup force
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 1262164 bytes
Variable Size 494931372 bytes
Database Buffers 327155712 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
我们在alert日志里可以看到
ALTER DATABASE OPEN
Fri Dec 17 16:30:49 2010
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Fri Dec 17 16:30:49 2010
Started redo scan
Fri Dec 17 16:30:49 2010
Completed redo scan
28142 redo blocks read, 1885 data blocks need recovery
Fri Dec 17 16:30:49 2010
Started redo application at
Thread 1: logseq 66211, block 3
Fri Dec 17 16:30:49 2010
Recovery of Online Redo Log: Thread 1 Group 5 Seq 66211 Reading mem 0
Mem# 0: /oradata/mcstar/mcstar/redo05.log
Fri Dec 17 16:30:49 2010
Completed redo application
Fri Dec 17 16:30:49 2010
db_recovery_file_dest_size of 10240 MB is 79.77% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Dec 17 16:30:50 2010
Completed crash recovery at
Thread 1: logseq 66211, block 28145, scn 10995135133737
1885 data blocks read, 1885 data blocks written, 28142 redo blocks read
Fri Dec 17 16:30:50 2010
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=29, OS id=16094
Fri Dec 17 16:30:50 2010
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=30, OS id=16111
Fri Dec 17 16:30:50 2010
Thread 1 advanced to log sequence 66212
Thread 1 opened at log sequence 66212
Current log# 4 seq# 66212 mem# 0: /oradata/mcstar/mcstar/redo04.log
Successful open of redo thread 1
Fri Dec 17 16:30:50 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 17 16:30:50 2010
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Fri Dec 17 16:30:50 2010
SMON: enabling cache recovery
Fri Dec 17 16:30:50 2010
ARC0: Becoming the heartbeat ARCH
Fri Dec 17 16:30:50 2010
Successfully onlined Undo Tablespace 12.
Fri Dec 17 16:30:50 2010
SMON: enabling tx recovery
[ora10g@mcprod bdump]$ more mcstar_smon_15889.trc
/ora10g/app/admin/mcstar/bdump/mcstar_smon_15889.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /ora10g/oracle/product/10.2.0/db_1
System name: Linux
Node name: mcprod
Release: 2.6.18-53.el5PAE
Version: #1 SMP Mon Nov 12 02:55:09 EST 2007
Machine: i686
Instance name: mcstar
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 15889, image: oracle@mcprod (SMON)
*** SERVICE NAME:() 2010-12-17 16:30:51.329
*** SESSION ID:(549.1) 2010-12-17 16:30:51.329
Dead transaction 0x000a.02d.0006936b recovered by 16 server(s)
*** 2010-12-17 16:30:51.332
SMON: Parallel transaction recovery tried
现在我们创建一个测试表格
SQL> select OBJECT_ID from dba_objects where owner='ZHOU' and OBJECT_NAME='T';
OBJECT_ID
----------
2275996
SQL> declare
2 begin
3 for i in 1..100000 loop
4 insert into t values(i,i+1);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from v$bh where objd=2275996;
COUNT(*)
----------
434
现在将其重启
SQL> startup force
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 1262164 bytes
Variable Size 503319980 bytes
Database Buffers 318767104 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
后台我们看到了smon在进行并行恢复，对象2275996的block已经在内存中，并以current状态存在
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
STATUS COUNT(*)
------- ----------
xcur 212
我们将其从内存中刷出
SQL> alter system flush buffer_cache;
System altered.
可以看到都为内存free状态
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
STATUS COUNT(*)
------- ----------
free 392
现在我们查询表格T，数据会从哪里获取呢?从业务表空间还是回滚表空间呢？
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> select count(*) from t;
COUNT(*)
----------
0
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
STATUS COUNT(*)
------- ----------
xcur 425
free 275
观察后台trc进程可以看到，数据库都从业务表空间获取，可见smon进程在数据库启动时已经完成tx恢复。
现在测试的重点来了，引进一个事件
SQL> alter system set event='10513 trace name context forever,level 2' scope=spfile;
System altered.
继续在业务表空间创建大量脏数据
SQL> declare
2 begin
3 for i in 1..100000 loop
4 insert into t values(i,i+1);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
STATUS COUNT(*)
------- ----------
xcur 368
将系统重启，可以看到event已经生效
SQL> startup force
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 1262164 bytes
Variable Size 528485804 bytes
Database Buffers 293601280 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
SQL> show parameter event
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string 10513 trace name context forev
er,level 2
进一步观察alert日志，依然出现SMON: enabling tx recovery字段。
Fri Dec 17 17:02:23 2010
ALTER DATABASE OPEN
Fri Dec 17 17:02:23 2010
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Fri Dec 17 17:02:23 2010
Started redo scan
Fri Dec 17 17:02:23 2010
Completed redo scan
91999 redo blocks read, 2752 data blocks need recovery
Fri Dec 17 17:02:23 2010
Started redo application at
Thread 1: logseq 66217, block 3
Fri Dec 17 17:02:23 2010
Recovery of Online Redo Log: Thread 1 Group 5 Seq 66217 Reading mem 0
Mem# 0: /oradata/mcstar/mcstar/redo05.log
Fri Dec 17 17:02:24 2010
Completed redo application
Fri Dec 17 17:02:24 2010
Completed crash recovery at
Thread 1: logseq 66217, block 92002, scn 10995135225524
2752 data blocks read, 2752 data blocks written, 91999 redo blocks read
Fri Dec 17 17:02:24 2010
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=29, OS id=467
Fri Dec 17 17:02:24 2010
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=30, OS id=469
Fri Dec 17 17:02:24 2010
Thread 1 advanced to log sequence 66218
Thread 1 opened at log sequence 66218
Current log# 4 seq# 66218 mem# 0: /oradata/mcstar/mcstar/redo04.log
Successful open of redo thread 1
Fri Dec 17 17:02:24 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 17 17:02:24 2010
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Fri Dec 17 17:02:24 2010
ARC1: Becoming the heartbeat ARCH
Fri Dec 17 17:02:24 2010
SMON: enabling cache recovery
Fri Dec 17 17:02:24 2010
Successfully onlined Undo Tablespace 12.
Fri Dec 17 17:02:24 2010
SMON: enabling tx recovery
Fri Dec 17 17:02:24 2010
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Dec 17 17:02:24 2010
Streams APPLY A001 started with pid=31, OS id=471
Fri Dec 17 17:02:24 2010
Streams APPLY A002 started with pid=32, OS id=473
Fri Dec 17 17:02:24 2010
Starting background process QMNC
QMNC started with pid=33, OS id=475
Fri Dec 17 17:02:25 2010
Completed: ALTER DATABASE OPEN
但是没有相应trace文件产生，如果要产生trace文件，我们可以加另外一个event 10500，重复上述过程。
SQL> alter system set event='10513 trace name context forever,level 2','10500 trace name context forever, level 1' scope=spfile;
System altered.
数据库重启之后
SQL> startup force
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 1262164 bytes
Variable Size 536874412 bytes
Database Buffers 285212672 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
可以看到跟踪文件smon的状态
[ora10g@mcprod bdump]$ more mcstar_smon_11666.trc
/ora10g/app/admin/mcstar/bdump/mcstar_smon_11666.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /ora10g/oracle/product/10.2.0/db_1
System name: Linux
Node name: mcprod
Release: 2.6.18-53.el5PAE
Version: #1 SMP Mon Nov 12 02:55:09 EST 2007
Machine: i686
Instance name: mcstar
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 11666, image: oracle@mcprod (SMON)
*** SERVICE NAME:() 2010-12-17 17:08:12.897
*** SESSION ID:(549.1) 2010-12-17 17:08:12.897
*** 2010-12-17 17:08:12.897
SMON: Event 10513 is level 2, trans recovery disabled.
kglScanDependencyHandles4Unpin():
cumscan=1 cumupin=2 time=64424509440 upinned=0
*** 2010-12-17 17:08:12.976
SMON: Event 10513 is level 2, trans recovery disabled.
*** 2010-12-17 17:08:13.245
SMON: Event 10513 is level 2, trans recovery disabled.
*** 2010-12-17 17:08:13.299
SMON: Event 10513 is level 2, trans recovery disabled.
*** 2010-12-17 17:08:13.469
SMON: Event 10513 is level 2, trans recovery disabled.
*** 2010-12-17 17:08:13.477
SMON: Event 10513 is level 2, trans recovery disabled.
*** 2010-12-17 17:08:13.593
SMON: Event 10513 is level 2, trans recovery disabled.
但是alert日志里面显示，这跟trace矛盾。
Fri Dec 17 17:08:12 2010
SMON: enabling tx recovery
但是在数据库打开之后，我们看到内存中并没有对象2275996
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
no rows selected
观察业务数据从哪里获取呢？
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> select count(*) from t;
COUNT(*)
----------
0
可以看到有cr状态，推断出有部分数据从undo表空间中获取
SQL> select status,count(*) from v$bh where objd=2275996 group by status;
STATUS COUNT(*)
------- ----------
xcur 501
cr 408
查看跟踪文件，可以看到和cr一样
[ora10g@mcprod udump]$ cat mcstar_ora_26883.trc|grep "uba: 0x008"|wc -l
408
最后将event恢复初始
SQL> alter system set event='' scope=spfile;
System altered.
再次重启系统
可以看到smon将0x000a.02d.00069371和0x0001.021.0006881d作为死事务处理
[ora10g@mcprod bdump]$ more mcstar_smon_14715.trc
/ora10g/app/admin/mcstar/bdump/mcstar_smon_14715.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /ora10g/oracle/product/10.2.0/db_1
System name: Linux
Node name: mcprod
Release: 2.6.18-53.el5PAE
Version: #1 SMP Mon Nov 12 02:55:09 EST 2007
Machine: i686
Instance name: mcstar
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 14715, image: oracle@mcprod (SMON)
*** SERVICE NAME:(SYS$BACKGROUND) 2010-12-17 17:32:48.011
*** SESSION ID:(549.1) 2010-12-17 17:32:48.011
Dead transaction 0x000a.02d.00069371 recovered by 15 server(s)
Dead transaction 0x0001.021.0006881d recovered by 15 server(s)
*** 2010-12-17 17:32:48.048
SMON: Parallel transaction recovery tried
回过头去看之前的跟踪日志，完全印证之前的事务。
[ora10g@mcprod udump]$ cat mcstar_ora_26883.trc|grep 00069371|wc -l
204
You have new mail in /var/spool/mail/ora10g
[ora10g@mcprod udump]$ cat mcstar_ora_26883.trc|grep 0006881d|wc -l
204
[ora10g@mcprod udump]$ cat mcstar_ora_26883.trc|grep "uba: 0x008"|wc -l
408
当然在设了event 10513之后，我们可以从x$ktuxe获取死事务的相关详细，详见
我的另外一篇文章http://itspace.iteye.com/blog/644509
1
顶
顶
0
踩
踩
分享到：
评论
2 楼
itspace
2011-05-13
hctech 写道
想请问一下博主，关于死事务的问题，
死事务是不是指没办法进行回滚的未提交事务？
如果是的话，ORACLE如何处理这样的脏数据？就让它们留在硬盘上被当成正常数据使用吗？
实验中出现了CR读取，说明读取到得都是UNDO中的数据，那是不是意思是磁盘上的都是脏数据？然后读取count（*）得到的是0
如果此时进行更新插入等操作，会是怎样的结果？把脏数据覆盖？然后以UNDO中的块作为原数据进行修改并写入磁盘吗？
谢谢博主~
死事务是不是指没办法进行回滚的未提交事务？
如果是的话，ORACLE如何处理这样的脏数据？就让它们留在硬盘上被当成正常数据使用吗？
实验中出现了CR读取，说明读取到得都是UNDO中的数据，那是不是意思是磁盘上的都是脏数据？然后读取count（*）得到的是0
如果此时进行更新插入等操作，会是怎样的结果？把脏数据覆盖？然后以UNDO中的块作为原数据进行修改并写入磁盘吗？
谢谢博主~
answer1：死事务并不是指不能回滚的事务，但死事务一般恢复会比较慢。从x$ktuxe根据相关字段可以看出死事务的恢复情况。
answer2：在同一个会话中，如果之前曾做过DML语句，如果此时进行更新插入等操作，如果涉及到的数据块在内存中，Oracle就再次更改该内存中数据块，不会从undo中读取内容。但如果是不同会话，那就分很多情况了。
1 楼
hctech
2011-05-11
想请问一下博主，关于死事务的问题，
死事务是不是指没办法进行回滚的未提交事务？
如果是的话，ORACLE如何处理这样的脏数据？就让它们留在硬盘上被当成正常数据使用吗？
实验中出现了CR读取，说明读取到得都是UNDO中的数据，那是不是意思是磁盘上的都是脏数据？然后读取count（*）得到的是0
如果此时进行更新插入等操作，会是怎样的结果？把脏数据覆盖？然后以UNDO中的块作为原数据进行修改并写入磁盘吗？
谢谢博主~
死事务是不是指没办法进行回滚的未提交事务？
如果是的话，ORACLE如何处理这样的脏数据？就让它们留在硬盘上被当成正常数据使用吗？
实验中出现了CR读取，说明读取到得都是UNDO中的数据，那是不是意思是磁盘上的都是脏数据？然后读取count（*）得到的是0
如果此时进行更新插入等操作，会是怎样的结果？把脏数据覆盖？然后以UNDO中的块作为原数据进行修改并写入磁盘吗？
谢谢博主~
发表评论
-
buffer cache 的内部结构
2020-03-18 14:21 576BUFFER CACHE作为数据块的 ... -
Oracle OMC介绍
2020-03-18 13:19 484Oracle管理云服务（OMC）的大数据平台，自动收集的企业 ... -
参加Oracle勒索病毒防范专题培训会议
2019-09-27 17:15 5112019年7月22日，受邀参加Oracle勒索病毒防范专题培训 ... -
记一次内存换IO的Oracle优化
2019-09-27 16:50 826某客户数据库从P595物理 ... -
如何定位Oracle SQL执行计划变化的原因
2019-07-03 14:49 1458性能优化最难的是能够 ... -
如何定位Oracle SQL执行计划变化的原因
2018-10-30 09:24 1185性能优化最难的是能够 ... -
数据库性能优化目标
2018-10-08 10:59 518从数据库性能优化的场 ... -
数据库无法打开的原因及解决办法
2018-10-05 20:45 2117数据库的启动是一个相当复杂的过程。比如，Oracle在启动之前 ... -
怎么样彻底删除数据库？
2018-09-18 11:10 598Oracle提供了drop database命令用来删除数据库 ... -
Oracle减少日志量的方法
2018-09-10 10:17 865LGWR进程将LOG BUFFER中的 ... -
如何快速关闭数据库
2018-09-09 13:14 1231“一朝被蛇咬，十年怕井绳”。在没被“蛇”咬之前，很多DBA喜欢 ... -
关于《如何落地智能化运维》PPT
2018-05-17 10:19 1128在DTCC 2018发表《如何落地智能化运维》演讲，主要内容如 ... -
记录在redhat5.8平台安装oracle11.2容易忽视的几个问题
2018-05-11 19:58 577问题一：ping不通问题 在虚拟机上安装好linux系统后， ... -
《Oracle DBA实战攻略》第一章
2018-05-11 10:42 945即日起，不定期更新《OracleDBA实战攻略》一书电子版，请 ... -
Oracle 12c新特性
2018-05-11 10:33 898查询所有pdb [oracle@gj4 ~]$ sqlplu ... -
关于修改memory_target的值后数据库无法启动的问题
2017-02-28 12:24 3981操作系统：RHEL6.5 数据库版本：11.2.0.4 ... -
10g rac安装error while loading shared libraries libpthread.so.0 问题
2017-02-28 12:22 69311g rac安装在二节点跑脚本一般会报此错误： 解决这个问 ... -
记一次Oracle会话共享模式故障处理过程
2017-02-27 19:16 798故障简述 XXX第八人民医院HIS数据库7月13日11点左右从 ... -
RESMGR:cpu quantum等待事件处理过程
2017-02-27 18:23 2615由于数据库上线过程中出现大量的RESMGR:cpu quant ... -
谈谈log file sync
2014-03-19 14:18 1757数据库中的log file sync等待事件指的是，当user ...
相关推荐
在数据库启动时，Oracle会读取控制文件来确定如何打开和恢复数据库。 4. **参数文件**（Parameter File）：参数文件存储了数据库的配置参数，这些参数决定了数据库的行为和性能。通过调整参数，管理员可以优化...
- **后台进程**：Oracle包含多个后台进程，如PMON（进程监控器）负责清理异常退出的进程，SMON（系统监控器）执行系统恢复，LCKN（锁定进程）处理锁机制，RECO（恢复进程）用于分布式事务，DBWR（数据写进程）将...
- CKPT（Checkpoint）：标记检查点，确保数据库在下次启动时能够快速恢复到一致状态。 - ARCH（Archiver）：负责归档日志文件，用于备份和恢复。 7. **SQL语句执行流程** - 用户发送SQL请求，打开游标。 - SQL...
ORACLE数据库的后台进程包括PMON、LCKN、RECO、SMON、DBWR、LGWR、CKPT和ARCH等。这些进程负责处理数据库的各种操作，如事务恢复、系统清洁、锁管理等。 七、SQL语句执行过程 SQL语句的执行过程包括语法分析、...
Oracle数据库运行多个后台进程，如DBWR（数据库写入进程）、LGWR（日志写入进程）、SMON（系统监控进程）等，它们负责执行各种后台任务，如数据同步、日志写入和实例恢复。 2. 存储的划分 - 控制文件的备份与管理：...
Oracle数据库以其稳定性、可扩展性和安全性著称，支持大规模并发事务处理，适合复杂的企业级应用。此外，Oracle还提供了一整套数据库解决方案，包括数据库服务器、开发工具、中间件和云服务。 “Oracle体系结构”是...
- **IMP工具**：通过导入数据来恢复数据库，适用于简单的数据恢复场景。 - **基于时间点的不完全恢复**：当需要恢复到某个特定时间点时，可以使用此方法。这需要归档日志的支持。 - **使用Flashback恢复**：Oracle的...
Oracle实例由SGA和后台进程组成，是运行数据库时的内存和进程结构。 Oracle数据库的体系结构非常复杂，本总结只是对其中的一些关键概念和组件进行了概述。实际上，每个部分都可以展开深入讨论，并涵盖更多的细节。...
- 实例是Oracle数据库运行时的内存结构和后台进程的组合。启动Oracle数据库需要先启动实例，实例相当于汽车的发动机，提供运行数据库所需的服务。 4. **连接（Session）**： - Oracle支持多用户并发访问，每个...
4. **重做日志文件**：保存了对数据库的所有更改，用于在系统崩溃或介质故障时进行恢复。 5. **系统全局区（SGA）**：Oracle数据库实例的一部分，包含了数据缓冲区缓存、重做日志缓冲区、共享SQL区等，这些区域存储...
- **SMON（System Monitor）进程**：监控整个系统，负责数据库恢复操作。 - **PMON（Process Monitor）进程**：监控用户进程的状态，当用户进程异常终止时进行相应的清理工作。 - **ARCH（Archiver）进程**：当...
每当有用户连接到数据库时，都会创建一个新的会话(session)。每个会话都有自己的私有内存区和进程，这使得Oracle能够支持多用户同时操作。 ##### 5. 事务（Transaction） 事务是一组逻辑上相关的操作集合，它可以...
Oracle数据库是一种广泛应用于企业级应用的高性能关系型数据库管理系统，其设计复杂且高效。本文将深入探讨Oracle数据库的基础概念，包括Oracle体系结构、实例、内存结构以及进程。 1. Oracle体系结构 Oracle...
5. **SMON**（系统监控进程）：在数据库启动时进行恢复，并定期进行系统维护。 6. **PMON**（进程监控进程）：监视并清理已终止的用户进程。 此外，Oracle数据库还涉及到一些其他关键组件，如参数文件（Parameter ...
- **文件结构**：Oracle数据库主要包含五种类型的文件，分别是Oracle RDBMS代码文件、数据文件（存储表、视图、索引等信息）、日志文件（记录所有数据库变化以用于恢复）、控制文件（包含数据库启动所需的配置参数...
- **重做日志文件**：用于记录对数据库所做的更改，以便在发生故障时恢复数据库。 - **表空间**：逻辑存储单元，由一个或多个数据文件组成。 - **数据文件**：包含实际数据的物理文件，是表空间的一部分。 - **...
19. **控制文件作用**：在装载或打开数据库时需要使用控制文件，但启动实例本身不需要。 20. **占用存储空间**：索引会占用实际的存储空间，而视图、序列和存储过程不直接占用，它们是逻辑对象，只有在使用时才会...
- SGA是Oracle数据库运行时在操作系统内存中分配的一块区域，包含共享池（shared pool）、数据库缓冲区（database buffer cache）和重做日志缓冲区（redo log buffer）。 - **共享池**存储PL/SQL代码、SQL语句解析...