ITPub博客

首页 > 数据库 > Oracle > [20140404]startup force问题.txt

[20140404]startup force问题.txt

原创 Oracle 作者:lfree 时间:2014-04-04 10:25:21 0 删除 编辑

[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/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1135905/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2630
  • 访问量
    6393785