[20140404]startup force问题.txt
前一段时间别人问看见别人的讲座使用startup force启动oracle数据库有什么问题，实际上使用startup force重启数据库，相当于
shutdown abort+startup，这样要启动中进行crash recovery。
自己做一个简单的测试看看：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select rownum id,'test' name from dual;
Table created.
SCOTT@test> select rowid,t.* from t ;
ROWID ID NAME
------------------ ---------- --------------------
AABFpSAAEAAAACjAAA 1 test
SCOTT@test> @lookup_rowid AABFpSAAEAAAACjAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
285266 4 163 0 4,163 alter system dump datafile 4 block 163 ;
SCOTT@test> update t set name='TEST' where id=1;
1 row updated.
SCOTT@test> commit ;
Commit complete.
--然后使用startup force启动，查看alert*.log文件：
Fri Apr 04 09:41:10 2014
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Started redo scan
Completed redo scan
read 2604 KB redo, 581 data blocks need recovery
Started redo application at
Thread 1: logseq 939, block 19330
Recovery of Online Redo Log: Thread 1 Group 3 Seq 939 Reading mem 0
Mem# 0: /u01/app/oracle11g/oradata/test/redo03.log
Completed redo application of 2.24MB
Completed crash recovery at
Thread 1: logseq 939, block 24539, scn 3268805981
581 data blocks read, 581 data blocks written, 2604 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
--可以发现执行crash recovery操作。
重复做一个shutdown abort看看。
SCOTT@test> update t set name='AAAA' where id=1;
1 row updated.
SCOTT@test> commit ;
Commit complete.
SYS@test> shutdown abort
ORACLE instance shut down.
SYS@test>
通过bbed观察：
BBED> set dba 4,163
DBA 0x010000a3 (16777379 4,163)
BBED> print *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rxdc
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x01
cols@8179: 2
col 0[2] @8180: 0xc1 0x02
col 1[4] @8183: 84 69 83 84
BBED> x /rnc
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x01
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: TEST
-- 可以发现还是原来的旧值。
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 1006636496 bytes
Database Buffers 587202560 bytes
Redo Buffers 7344128 bytes
Database mounted.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE# FROM v$datafile;
FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE#
---------- ------------------ --------------------- ------------ --------------- --------------
1 3268867109 0 0 0
2 3268867109 0 0 0
3 3268867109 0 0 0
4 3268867109 3263502733 0 0
5 3268867109 0 0 0
6 3268867109 0 0 0
7 3268867109 0 3242489810 3242489957
8 3268867109 0 3268229241 3268229525
9 3268867109 0 3223747580 3223747948
10 3268867109 0 0 0
10 rows selected.
SYS@test>
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE# FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE#
---------- ------------------ ---------------- -----------------
1 3268867109 7 3011113647
2 3268867109 2140 3011113647
3 3268867109 3241444492 3011113647
4 3268867109 17993 3011113647
5 3268867109 973735 3011113647
6 3268867109 1412559 3011113647
7 3268867109 4383251 3011113647
8 3268867109 13169364 3011113647
9 3268867109 3223747107 3011113647
10 3268867109 3223804181 3011113647
10 rows selected.
SYS@test>
SYS@test> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode FROM v$database;
SCN locatio NAME CHECKPOINT_CHANGE# OPEN_MODE
----------- -------------------- ------------------ --------------------
controlfile SYSTEM checkpoint 3268867109 MOUNTED
--可以发现shutdown abort关闭数据库，视图v$datafile的LAST_CHANGE#是NULL。
SYS@test> alter database open ;
Database altered.
SYS@test> alter system checkpoint;
System altered.
--再使用bbed观察：
BBED> set dba 4,163
DBA 0x010000a3 (16777379 4,163)
BBED> print *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rnc
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: AAAA
--做一个正常关机的测试：
SYS@test> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 1006636496 bytes
Database Buffers 587202560 bytes
Redo Buffers 7344128 bytes
Database mounted.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE# FROM v$datafile;
FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE#
---------- ------------------ --------------------- ------------ --------------- --------------
1 3268887675 0 3268887675 0 0
2 3268887675 0 3268887675 0 0
3 3268887675 0 3268887675 0 0
4 3268887675 3263502733 3268887675 0 0
5 3268887675 0 3268887675 0 0
6 3268887675 0 3268887675 0 0
7 3268887675 0 3268887675 3242489810 3242489957
8 3268887675 0 3268887675 3268229241 3268229525
9 3268887675 0 3268887675 3223747580 3223747948
10 3268887675 0 3268887675 0 0
10 rows selected.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE# FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE#
---------- ------------------ ---------------- -----------------
1 3268887675 7 3011113647
2 3268887675 2140 3011113647
3 3268887675 3241444492 3011113647
4 3268887675 17993 3011113647
5 3268887675 973735 3011113647
6 3268887675 1412559 3011113647
7 3268887675 4383251 3011113647
8 3268887675 13169364 3011113647
9 3268887675 3223747107 3011113647
10 3268887675 3223804181 3011113647
10 rows selected.
SYS@test> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode FROM v$database;
SCN locatio NAME CHECKPOINT_CHANGE# OPEN_MODE
----------- -------------------- ------------------ --------------------
controlfile SYSTEM checkpoint 3268887675 MOUNTED
--正常关闭数据库，视图中 v$datafile的CHECKPOINT_CHANGE# =CHECKPOINT_CHANGE#
--参考链接：http://avdeo.com/2008/05/29/oracle-database-recovery-details/