Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › remove the current control file when the database is ...
发新帖
ora-hansey

36

积分

0

好友

0

主题

remove the current control file when the database is running on linux or unix

1#
发表于 2012-3-7 00:54:00 | 查看: 8215| 回复: 21
下面这个例子中，假如controlfile被删除掉了，instance还没有关闭，可以通过lsof找出controlfile的file descriptors,再通过dd把文件恢复过来...
我觉得这样dd出来的控制文件是没有办法直接启动这个数据库的，因为CKPT每3S一次，你dd恢复出来的控制文件SCN肯定和datafile不一样，比datafile小，... 要是recover会提示控制文件太久，不知道我理解正不正确啊？

http://www.oracledatabase12g.com/archives/unix-linux%e4%b8%8b%e5%88%a0%e9%99%a4oracle%e6%8e%a7%e5%88%b6%e6%96%87%e4%bb%b6controlfile%e4%b8%ba%e4%bb%80%e4%b9%88%e5%ae%9e%e4%be%8binstance%e6%b2%a1%e6%9c%89%e7%ab%8b%e5%8d%b3%e5%a5%94%e6%ba%83.html


dd恢复的控制文件 怎么会比打他file、
怎么会比datafile的scn 小呢

一旦关库，那么fd是不是就消失了，没有办法dd了， 那么造成scn不相同的原因是dd过程中scn的变化么？

还请大致谈一下造成scn更新的必要条件。


还请指点
2#
发表于 2012-3-7 14:37:56
[oracle@vrh8 ~]$ cp /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl /s01/new.ctl
[oracle@vrh8 ~]$ rm /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl

SQL>  alter system checkpoint;

System altered.

SQL> /

System altered.



[oracle@vrh8 fd]$ ps -ef|grep ckpt
oracle    4142     1  0 17:00 ?        00:00:00 ora_ckpt_G10R21
oracle    4184  3540  0 17:00 pts/0    00:00:00 grep ckpt

[oracle@vrh8 fd]$ cd /proc/4142/fd

[oracle@vrh8 fd]$ ls -l
total 0
lr-x------ 1 oracle oinstall 64 Mar  7 17:01 0 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar  7 17:01 1 -> /dev/null
l-wx------ 1 oracle oinstall 64 Mar  7 17:01 10 -> /s01/admin/G10R21/bdump/alert_G10R21.log
lr-x------ 1 oracle oinstall 64 Mar  7 17:01 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Mar  7 17:01 12 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Mar  7 17:01 13 -> /s01/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Mar  7 17:01 14 -> /s01/oracle/product/10.2.0/db_1/dbs/hc_G10R21.dat
lrwx------ 1 oracle oinstall 64 Mar  7 17:01 15 -> /s01/oracle/product/10.2.0/db_1/dbs/lkG10R21
lrwx------ 1 oracle oinstall 64 Mar  7 17:01 16 -> /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl (deleted)


SQL> oradebug setospid  4142
Oracle pid: 7, Unix process pid: 4142, image: oracle@vrh8.oracle.com (CKPT)
SQL> oradebug call close 16;
Function returned 0
SQL>
SQL>
SQL>
SQL> ERROR:
ORA-03135: connection lost contact


[oracle@vrh8 ~]$ cp /s01/new.ctl /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl

SQL> startup mount;
ORACLE instance started.

Total System Global Area  620756992 bytes
Fixed Size                  2022760 bytes
Variable Size             234881688 bytes
Database Buffers          377487360 bytes
Redo Buffers                6365184 bytes
Database mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1:
'/s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf'
ORA-01207: file is more recent than control file - old control file


Why?

Cause:  The control file change sequence number in the datafile is greater
        than the number in the control file.
        This implies that the wrong control file is being used.
Note:   If this message occurs repeatedly, by opening the database many times,
        the message may stop occurring without the problem being corrected.
        Every attempt to open the database will advance the control file
        change sequence number until it is great enough.
Action: Use the current control file or perform cancel-based recovery to make
        the control file current.
        Be sure to follow all restrictions on performing a cancel-based
        recovery.

SQL> oradebug setmypid;
Statement processed.
SQL>  oradebug dump file_hdrs 3;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_4254.trc


以下是控制文件(controlfile)中的datafile 记录 ：


DATA FILE #1:
  (name #10) /s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf
creation size=0 block size=8192 status=0xe head=10 tail=10 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 02/10/2012 00:50:09
Checkpoint cnt:2231 scn: 0x0000.00e237a4 03/07/2012 17:00:24                 ==> controlfile 中的记录  checkpoint scn 00e237a4
Stop scn: 0xffff.ffffffff 03/07/2012 16:59:46
Creation Checkpointed at scn:  0x0000.00000008 10/22/2005 21:44:31
thread:0 rba:(0x0.0.0)


以下是数据文件头( datafile header)中的记录 ：

  Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
V10 STYLE FILE HEADER:
        Compatibility Vsn = 169869568=0xa200100
        Db ID=2807279631=0xa753b00f, Db Name='G10R21'
        Activation ID=0=0x0
        Control Seq=33213=0x81bd, File size=444160=0x6c700
        File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000008 10/22/2005 21:44:31
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2e54e476 scn: 0x0000.00e14b4d reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2e516be7 scn: 0x0000.00debd3d prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 03/07/2012 17:00:24
status:0x2004 root dba:0x00400179 chkpt cnt: 2233 ctl cnt:2232                        ==》数据文件中的记录
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.00e239e7 03/07/2012 17:00:43                checkpoint scn 00e239e7


可以看到 数据文件头 (datafile header) 和 控制文件中的 :

checkpoint cnt : 不匹配 2231 vs 2233
checkpoint scn : 不匹配  e237a4 vs e239e7

都是 datafile header 中的要大一些， 这会造成ORA-01207错误


注意这里有一个前提 是做过 " alter system checkpoint "的FULL checkpoint ， 在FULL checkpoint 发生时  oracle才会更新 数据文件头 ， 而控制文件则是 每3s heartbeat 心跳同步一次的。

回复 只看该作者 道具 举报

3#
发表于 2012-3-7 14:47:32
如果在以上测试中没有 做过 " alter system checkpoint "的FULL checkpoint ，那么一般DB 可以顺利open 打开:

[oracle@vrh8 datafile]$ sqlplus / as sysdba                           

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 7 17:37:27 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> recover database using backup controlfile;
ORA-00279: change 14825943 generated at 03/07/2012 17:00:24 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R21/archivelog/2012_03_07/o1_mf_1_4_%u_.arc
ORA-00280: change 14825943 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/s01/oradata/G10R21/onlinelog/o1_mf_2_7ch8144q_.log
Log applied.
Media recovery complete.


SQL> alter database open resetlogs;

Database altered.


SQL> startup force;
ORACLE instance started.

Total System Global Area  620756992 bytes
Fixed Size                  2022760 bytes
Variable Size             234881688 bytes
Database Buffers          377487360 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.

[oracle@vrh8 datafile]$ cp /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl /s01/new.ctl

[oracle@vrh8 datafile]$ rm /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl

[oracle@vrh8 datafile]$ ps -ef|grep ckpt
oracle    4884     1  0 17:38 ?        00:00:00 ora_ckpt_G10R21
oracle    4926  4708  0 17:39 pts/0    00:00:00 grep ckpt


[oracle@vrh8 datafile]$ cd /proc/4142/fd
-bash: cd: /proc/4142/fd: No such file or directory
[oracle@vrh8 datafile]$ cd /proc/4884/fd
[oracle@vrh8 fd]$ ls -l
total 0
lr-x------ 1 oracle oinstall 64 Mar  7 17:39 0 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar  7 17:39 1 -> /dev/null
l-wx------ 1 oracle oinstall 64 Mar  7 17:39 10 -> /s01/admin/G10R21/bdump/alert_G10R21.log
lr-x------ 1 oracle oinstall 64 Mar  7 17:39 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Mar  7 17:39 12 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Mar  7 17:39 13 -> /s01/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Mar  7 17:39 14 -> /s01/oracle/product/10.2.0/db_1/dbs/hc_G10R21.dat
lrwx------ 1 oracle oinstall 64 Mar  7 17:39 15 -> /s01/oracle/product/10.2.0/db_1/dbs/lkG10R21
lrwx------ 1 oracle oinstall 64 Mar  7 17:39 16 -> /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl (deleted)

SQL> oradebug setospid 4884
Oracle pid: 7, Unix process pid: 4884, image: oracle@vrh8.oracle.com (CKPT)
SQL> oradebug call close 16
Function returned 0
SQL> ERROR:
ORA-03135: connection lost contac

[oracle@vrh8 fd]$ cp /s01/new.ctl /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl
[oracle@vrh8 fd]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 7 17:40:43 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup ;
ORACLE instance started.

Total System Global Area  620756992 bytes
Fixed Size                  2022760 bytes
Variable Size             234881688 bytes
Database Buffers          377487360 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.

以下为controlfile中的记录：

DATA FILE #1:
  (name #10) /s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf
creation size=0 block size=8192 status=0xe head=10 tail=10 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 02/10/2012 00:50:09
Checkpoint cnt:2242 scn: 0x0000.00e2d7ed 03/07/2012 17:40:50
Stop scn: 0xffff.ffffffff 03/07/2012 17:39:57
Creation Checkpointed at scn:  0x0000.00000008 10/22/2005 21:44:31
thread:0 rba:(0x0.0.0)


以下为controlfile 中的记录:

Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
V10 STYLE FILE HEADER:
        Compatibility Vsn = 169869568=0xa200100
        Db ID=2807279631=0xa753b00f, Db Name='G10R21'
        Activation ID=0=0x0
        Control Seq=33315=0x8223, File size=444160=0x6c700
        File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000008 10/22/2005 21:44:31
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2e54edf3 scn: 0x0000.00e239e8 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2e54e476 scn: 0x0000.00e14b4d prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 03/07/2012 17:40:51
status:0x2004 root dba:0x00400179 chkpt cnt: 2242 ctl cnt:2241
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.00e2d7ed 03/07/2012 17:40:50
thread:1 rba:(0x3.2.10)


checkpoint cnt : 均为2242              

==》因为没有发生过FULL checkpoint，所以datafile header中cnt和老的控制文件一致

checkpoint scn: 均为e2d7ed  

=》 datafile header的checkpint scn要FULL checkpoint时才更新 所以也和老的 控制文件(old controlfile)中的记录一致

回复 只看该作者 道具 举报

4#
发表于 2012-3-7 22:41:57
关于这个·，在做测试的时候，删除了controlfile以后，alter system switch logfile ;scn还会有一个记录在redo日志里面，那么当recover using controlfile的时候，会提示
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/export/home/oracle/product/oradata/bbk10g/redo03.log
ORA-00328: archived log ends at change 2709672, need later change 2709673
ORA-00334: archived log:
'/export/home/oracle/product/oradata/bbk10g/redo03.log'
change no ,小了一号

回复 只看该作者 道具 举报

5#
发表于 2012-3-7 22:45:25
尝试指定下一个 redolog

回复 只看该作者 道具 举报

6#
发表于 2012-3-8 00:36:38
scn：2709672  是不是redo里面的scn， 2709673 是不是controlfile的scn， 我的理解是应该controlfile里面的应该比较小

回复 只看该作者 道具 举报

7#
发表于 2012-3-8 00:39:57
alter database register or replace physical logfile '/export/home/oracle/product/oradata/bbk10g/redo03.log'
数据库正常启动

回复 只看该作者 道具 举报

8#
发表于 2012-3-8 01:24:11
又从新的做了一次：
SQL>  recover database using backup controlfile
ORA-00279: change 2719612 generated at 04/04/2011 22:06:27 needed for thread 1
ORA-00289: suggestion :
/export/home/oracle/product/10.0/dbs/arch/1_4_747611505.dbf
ORA-00280: change 2719612 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/export/home/oracle/product/oradata/bbk10g/redo01.log
Log applied.
Media recovery complete.

这一次要4#号文件，但是redo里面没有4号文件，且归档里面也没有对应的scn的归档。
所以使用alter database register or replace physical logfile '/export/home/oracle/product/oradata/bbk10g/redo0*.log 不能正常的打开库


使用你的方法。来回试了几个当前的日志文件。编号分别为0 ，1，2的，成功打开库。


不知道为什么？ 还请解释

回复 只看该作者 道具 举报

9#
发表于 2012-3-8 19:42:01
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump file_hdrs 3;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_3804.trc


数据文件中的记录

Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
V10 STYLE FILE HEADER:
        Compatibility Vsn = 169869568=0xa200100
        Db ID=2807279631=0xa753b00f, Db Name='G10R21'
        Activation ID=0=0x0
        Control Seq=33439=0x829f, File size=444160=0x6c700
        File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000008 10/22/2005 21:44:31
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2e54edf3 scn: 0x0000.00e239e8 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2e54e476 scn: 0x0000.00e14b4d prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 03/08/2012 21:37:03
status:0x2004 root dba:0x00400179 chkpt cnt: 2259 ctl cnt:2258
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.00e5ef5f 03/08/2012 22:19:32
thread:1 rba:(0x13.e351.10)

控制文件中的记录：

***************************************************************************
DATABASE ENTRY
***************************************************************************
(size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
11/07/2011 04:08:47
DB Name "G10R21"
Database flags = 0x00405007 0x00001000
Controlfile Creation Timestamp  11/07/2011 04:08:48
Incmplt recovery scn: 0x0000.00e5db0d
Resetlogs scn: 0x0000.00e239e8 Resetlogs Timestamp  03/07/2012 17:37:55
Prior resetlogs scn: 0x0000.00e14b4d Prior resetlogs Timestamp  03/07/2012 16:57:26
Redo Version: compatible=0xa200100
#Data files = 31, #Online files = 31
Database checkpoint: Thread=1 scn: 0x0000.00e5d8d6
Threads: #Enabled=1, #Open=1, Head=1, Tail=1

Max log members = 3, Max data members = 1
Arch list: Head=2, Tail=2, Force scn: 0x0000.00e57dd6 scn: 0x0000.00e5d8d5
Activation ID: 2818025403
Controlfile Checkpointed at scn:  0x0000.00e5db0d 03/08/2012 21:42:03
thread:0 rba:(0x0.0.0)


ckpt 每3s 同步控制文件一次， 更新控制文件的 controlfile checkpoint ， 旧的控制文件 (old controlfile)中的

Incmplt recovery scn: 0x0000.00e5db0d 是 e5db0d=> 15063821 这将是recover database using backup controlfile的起点

SQL> recover database using backup controlfile;
ORA-00279: change 15063821 generated at 03/08/2012 21:37:03 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R21/archivelog/2012_03_08/o1_mf_1_19_%u_.arc
ORA-00280: change 15063821 for thread 1 is in sequence #19


15063821 =》　e5db0d


旧的控制文件并不知道　redo已经写到哪里了？　它不知道　要开始恢复的起点 (change 15063821 ) 所对应的现在(之前可能过了很久，已经沧海桑田了) redo log是被归档了　还是没有？ 它都不知道， 这些信息在数据文件头(datafile header )中都找不到， 它只知道这个对象的redo log的sequence 是多少：

***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
(size = 8180, compat size = 8180, section max = 11, section in-use = 0,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x2 flags:0x0 dirty:50
low cache rba:(0x13.e0f7.0) on disk rba:(0x13.e351.0)
on disk scn: 0x0000.00e5ef30 03/08/2012 22:17:14
resetlogs scn: 0x0000.00e239e8 03/07/2012 17:37:55
heartbeat: 777437739 mount id: 2818123620


disk rba:(0x13.e351.0)  0x13 => sequence 19

所以它会问 DBA指出 哪一个文件是 sequence 19的日志文件， 即便这个文件是online redo log中的一个 没有被归档， 它也不知道！

如果恰好这个redo log没有被归档，那么就是 现在在线的日志之一 只要指定正确的名字就可以了。 如果被归档了， 那么DBA就需要去归档日志目录下找出这个sequence的log.




回复 只看该作者 道具 举报

10#
发表于 2012-3-8 20:25:24
实际上 从 Incmplt recovery scn: 0x0000.00e5db0d  这个起点到 最后的终点 中 实例instance 可能发生了多个日志切换 logfile switch ，这不要紧：

[oracle@vrh8 ~]$ cp /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl /s01/new.ctl
[oracle@vrh8 ~]$  rm /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.


SQL> oradebug setospid 3935
Oracle pid: 7, Unix process pid: 3935, image: oracle@vrh8.oracle.com (CKPT)

SQL> oradebug call close 16
Function returned 0


[oracle@vrh8 ~]$  cp /s01/new.ctl /s01/oradata/G10R21/controlfile/o1_mf_7ch80zvk_.ctl

SQL> startup mount;
ORACLE instance started.

Total System Global Area  620756992 bytes
Fixed Size                  2022760 bytes
Variable Size             234881688 bytes
Database Buffers          377487360 bytes
Redo Buffers                6365184 bytes
Database mounted.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump controlf 3;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_4019.trc


***************************************************************************
DATABASE ENTRY
***************************************************************************
(size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
11/07/2011 04:08:47
DB Name "G10R21"
Database flags = 0x00404001 0x00001000
Controlfile Creation Timestamp  11/07/2011 04:08:48
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.00e5ef60 Resetlogs Timestamp  03/08/2012 22:44:58
Prior resetlogs scn: 0x0000.00e239e8 Prior resetlogs Timestamp  03/07/2012 17:37:55
Redo Version: compatible=0xa200100
#Data files = 31, #Online files = 31
Database checkpoint: Thread=1 scn: 0x0000.00e63df5
Threads: #Enabled=1, #Open=1, Head=1, Tail=1


Max log members = 3, Max data members = 1
Arch list: Head=1, Tail=1, Force scn: 0x0000.00000000scn: 0x0000.00e63df4
Activation ID: 2818173706
Controlfile Checkpointed at scn:  0x0000.00e63e4d 03/08/2012 22:45:22
thread:0 rba:(0x0.0.0)


***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
(size = 8180, compat size = 8180, section max = 11, section in-use = 0,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x2 flags:0x0 dirty:7
low cache rba:(0x2.53.0) on disk rba:(0x2.68.0)
on disk scn: 0x0000.00e63e58 03/08/2012 22:45:29
resetlogs scn: 0x0000.00e5ef60 03/08/2012 22:44:58
heartbeat: 777401907 mount id: 2818154218



change 起点为 0x0000.00e63e4d =》15089229


disk rba:(0x2.68.0)  ，recover database using backup controlfile 会向你要 sequence 2 ， 注意之前我们resetlogs过了，所以是2



***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 16,
  last-recid= 57, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  (name #5) /s01/oradata/G10R21/onlinelog/o1_mf_1_7ch812dg_.log
  (name #6) /s01/flash_recovery_area/G10R21/onlinelog/o1_mf_1_7ch813h5_.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000002 hws: 0x2 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00e5ef60
Low scn: 0x0000.00e63df4 03/08/2012 22:45:21
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #2:
  (name #3) /s01/oradata/G10R21/onlinelog/o1_mf_2_7ch8144q_.log
  (name #4) /s01/flash_recovery_area/G10R21/onlinelog/o1_mf_2_7ch815cj_.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000001 hws: 0x7 bsz: 512 nab: 0x7d flg: 0x1 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00e5ef60 03/08/2012 22:44:58
Next scn: 0x0000.00e63df4 03/08/2012 22:45:21
LOG FILE #3:
  (name #1) /s01/oradata/G10R21/onlinelog/o1_mf_3_7ch816g2_.log
  (name #2) /s01/flash_recovery_area/G10R21/onlinelog/o1_mf_3_7ch817gp_.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x00000000 hws: 0x3 bsz: 512 nab: 0x2 flg: 0x1 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00


LOGFILE在旧的控制文件中(注意 这些信息理论上全是过时的，无价值的！！）的状态是 LOGFILE #1是current logfile

我们在recover database using backup controlfile 的时候直接输入LOGFILE #1的 路径：



SQL> recover database using backup controlfile;
ORA-00279: change 15089229 generated at 03/08/2012 22:45:21 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R21/archivelog/2012_03_08/o1_mf_1_2_%u_.arc
ORA-00280: change 15089229 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/s01/oradata/G10R21/onlinelog/o1_mf_1_7ch812dg_.log

ORA-00279: change 15089256 generated at 03/08/2012 22:46:08 needed for thread 1
ORA-00289: suggestion :


change 在变化

SQL> recover database using backup controlfile;
ORA-00279: change 15089256 generated at 03/08/2012 22:46:08 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R21/archivelog/2012_03_08/o1_mf_1_3_%u_.arc
ORA-00280: change 15089256 for thread 1 is in sequence #3
ORA-00278: log file '/s01/oradata/G10R21/onlinelog/o1_mf_1_7ch812dg_.log' no
longer needed for this recovery



Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/s01/oradata/G10R21/onlinelog/o1_mf_3_7ch816g2_.log
ORA-00279: change 15089260 generated at 03/08/2012 22:46:16 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R21/archivelog/2012_03_08/o1_mf_1_4_%u_.arc
ORA-00280: change 15089260 for thread 1 is in sequence #4
ORA-00278: log file '/s01/oradata/G10R21/onlinelog/o1_mf_3_7ch816g2_.log' no
longer needed for this recovery



SQL> recover database using backup controlfile;
ORA-00279: change 15089260 generated at 03/08/2012 22:46:16 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R21/archivelog/2012_03_08/o1_mf_1_4_%u_.arc
ORA-00280: change 15089260 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/s01/oradata/G10R21/onlinelog/o1_mf_2_7ch8144q_.log
Log applied.
Media recovery complete.



那么Oracle 知道  recover 的起点 change 是 15089229 ， 它怎么知道 终点是哪里呢？






很简单， logfile 里有必要的信息




SQL> alter system dump logfile  '/s01/oradata/G10R21/onlinelog/o1_mf_3_7ch816g2_.log';

System altered.







DUMP OF REDO FROM FILE '/s01/oradata/G10R21/onlinelog/o1_mf_3_7ch816g2_.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
        Compatibility Vsn = 169869568=0xa200100
        Db ID=2807279631=0xa753b00f, Db Name='G10R21'
        Activation ID=2818173706=0xa7f9eb0a
        Control Seq=33536=0x8300, File size=102400=0x19000
        File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000003, SCN 0x000000e63e68-0x000000e63e6c"
thread: 1 nab: 0x2 seq: 0x00000003 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x2e56876a scn: 0x0000.00e5ef60 (15069024)
resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
prev resetlogs count: 0x2e54edf3 scn: 0x0000.00e239e8 (14825960)
prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
Low  scn: 0x0000.00e63e68 (15089256) 03/08/2012 22:46:08
Next scn: 0x0000.00e63e6c (15089260) 03/08/2012 22:46:16
Enabled scn: 0x0000.00e5ef60 (15069024) 03/08/2012 22:44:58
Thread closed scn: 0x0000.00e63e68 (15089256) 03/08/2012 22:46:08
Disk cksum: 0xa837 Calc cksum: 0xa837
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery  01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x0
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
END OF REDO DUMP


o1_mf_3_7ch816g2_.log 的  Low  scn: 0x0000.00e63e68 (15089256)=》 Next scn: 0x0000.00e63e6c (15089260)




SQL> alter system dump logfile '/s01/oradata/G10R21/onlinelog/o1_mf_2_7ch8144q_.log';

System altered.


DUMP OF REDO FROM FILE '/s01/oradata/G10R21/onlinelog/o1_mf_2_7ch8144q_.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
        Compatibility Vsn = 169869568=0xa200100
        Db ID=2807279631=0xa753b00f, Db Name='G10R21'
        Activation ID=2818173706=0xa7f9eb0a
        Control Seq=33536=0x8300, File size=102400=0x19000
        File Number=2, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000004, SCN 0x000000e63e6c-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x00000004 hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x2e56876a scn: 0x0000.00e5ef60 (15069024)
resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
prev resetlogs count: 0x2e54edf3 scn: 0x0000.00e239e8 (14825960)
prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
Low  scn: 0x0000.00e63e6c (15089260) 03/08/2012 22:46:16
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.00e5ef60 (15069024) 03/08/2012 22:44:58
Thread closed scn: 0x0000.00e63e6c (15089260) 03/08/2012 22:46:16
Disk cksum: 0xccaa Calc cksum: 0xccaa
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery  01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x0
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
END OF REDO DUMP


o1_mf_2_7ch8144q_.log 就是最后一个 apply的redo log

Low  scn: 0x0000.00e63e6c (15089260)  可以看到紧接着前一个

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00          这就是终点， 0xffff.ffffffff 说明


o1_mf_2_7ch8144q_.log 是真正的 current logfile， 是DB crash之前瞬间的current logfile。



Oracle会recover到这个current logfile 的最近一个on disk RBA ， 至此recover database using backup controlfile完成。

但是因为丢失了controlfile 所以还是要 resetlogs ！　Why? 因为理论上你可以 复制一个不是最新的 current logfile 来 骗过oracle ，虽然这个 “current logfile"的 Next scn 也是 0xffff.fffffff  ，但是它可能不是最新的"latest"的， 虽然这个recover 还是可以完成， 但是 是不是 complete recovery 就存在疑问了， 所以oracle强制 丢失控制文件 要 resetlogs!



回复 只看该作者 道具 举报

11#
发表于 2012-3-8 21:10:09
多谢了  刘总  亲

回复 只看该作者 道具 举报

12#
发表于 2012-3-9 01:46:58
=》 datafile header的checkpint scn要FULL checkpoint时才更新 所以也和老的 控制文件(old controlfile)中的记录一致
上述，在没有发生fullcheckpoint的时候， 控制文件的scn和data header的控制文件 scn是一样。

记得 心跳的规则是：3秒发生一次checkpoint事件，更新控制文件和数据文件。 一担控制文件删除。 那么在3秒以后，datafile的scn肯定会发生变化（心跳checkpoint) ,理论上在哪老的控制文件和datafile的scn相比，也会发生变化。

求解。

回复 只看该作者 道具 举报

13#
发表于 2012-3-9 19:00:56
"记得 心跳的规则是：3秒发生一次checkpoint事件，更新控制文件和数据文件。 一担控制文件删除。 那么在3秒以后，datafile的scn肯定会发生变化（心跳checkpoint) ,理论上在哪老的控制文件和datafile的scn相比，也会发生变化。 ”

这个说法是错误的！

同时更新controlfile 控制文件 和  datafile headers 数据文件头中CHECKPOINT_CHANGE#的 有以下几种 可能：

1.Full Checkpoint             8i以后       alter system checkpoint or shutdown immediate

2.Thread Checkpoint         Alter system checkpoint local

3. File Checkpoint           Alter tablespace XXX offline  Alter tablespace XXX begin backup Alter tablespace XXX read only

4. Log Switch Checkpoint  -- 是在该种checkpoint 结束时

     
CKPT 每3s heartbeat controlfile 的原因是 8i以后引入的 Incremental Checkpoint (Controlfile is updated every 3 seconds by CKPT Checkpoint progress record)，  Incremental Checkpoint发生时 只更新 controlfile  而不更新 datafiles headers

这很容易 可以证明：


21:55:58 SQL> select distinct checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          15162703


21:56:07 SQL> exec dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

21:56:32 SQL> select distinct checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          15162703



21:56:48 SQL> show parameter checkpoint

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     1800
log_checkpoints_to_alert             boolean     TRUE




21:57:20 SQL> alter system set log_checkpoint_timeout=10;

System altered.


log_checkpoint_timeout =10   即 10s 强制发生一次Incremental checkpoint

观察alert.log :


Fri Mar  9 21:57:28 2012
Incremental checkpoint up to RBA [0x14.5a.0], current log tail at RBA [0x14.6d.0]
Fri Mar  9 21:57:37 2012
Incremental checkpoint up to RBA [0x14.6e.0], current log tail at RBA [0x14.6e.0]
Fri Mar  9 21:57:46 2012
Incremental checkpoint up to RBA [0x14.6e.0], current log tail at RBA [0x14.6e.0]
Fri Mar  9 21:57:55 2012
Incremental checkpoint up to RBA [0x14.6e.0], current log tail at RBA [0x14.6e.0]
Fri Mar  9 21:58:04 2012
Incremental checkpoint up to RBA [0x14.6e.0], current log tail at RBA [0x14.6e.0]
Fri Mar  9 21:58:13 2012
Incremental checkpoint up to RBA [0x14.6e.0], current log tail at RBA [0x14.6e.0]
Fri Mar  9 21:58:22 2012
Incremental checkpoint up to RBA [0x14.6e.0], current log tail at RBA [0x14.6e.0]
Fri Mar  9 21:58:31 2012
Incremental checkpoint up to RBA [0x14.6e.0], current log tail at RBA [0x14.6e.0]


发生了多次 Incremental checkpoint  增量检查点， 但是 datafile header的 checkpoint_change#是不变的


21:58:32 SQL> select distinct checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          15162703

回复 只看该作者 道具 举报

14#
发表于 2012-3-9 20:36:42
有网友提问，若控制文件是 create controlfile 重建得来的 又会如何？ 这里我们可以看一下：

我们来看一下：


SQL> alter system switch logfile;

System altered.

SQL> select distinct checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          15169797

         
SQL> alter system checkpoint;

System altered.


SQL> select distinct checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          15169802            =>E7783A



15169802            =>E7783A 最近的一次FULL CHECKPOINT的SCN


SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
EXAMPLE
TEMP         
         
SQL> alter tablespace SYSTEM begin backup;

Tablespace altered.

SQL> alter tablespace UNDOTBS1 begin backup;

Tablespace altered.

SQL> alter tablespace SYSAUX begin backup;

Tablespace altered.

SQL> alter tablespace USERS begin backup;

Tablespace altered.

SQL> alter tablespace EXAMPLE begin backup;

Tablespace altered.

SQL> alter tablespace SYSTEM end backup;

Tablespace altered.

SQL> alter tablespace UNDOTBS1 end backup;

Tablespace altered.

SQL> alter tablespace SYSAUX end backup;

Tablespace altered.

SQL>  alter tablespace USERS end backup;

Tablespace altered.

SQL> alter tablespace EXAMPLE end backup;

Tablespace altered.

         
begin /end backup 会引发FILE CHECKPOINT ，造成datafile_header的 checkpoint_change# 更新



SQL> select distinct checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          15169814      
          15169827
          15169842
          15169821
          15169835

          15169814      =>E77916 是最小的一个 datafile header checkpoint_change#


SQL> oradebug setmypid;                     
Statement processed.

SQL> oradebug dump controlf 3;
Statement processed.

SQL> oradebug tracefile_name;



但是此时去dump controlf 可以发现 控制文件中的database checkpint 仍是 E7783A 15169802  即上一次FULL CHECKPOINT的 SCN



***************************************************************************
DATABASE ENTRY
***************************************************************************
(size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
03/09/2012 23:04:33
DB Name "G10R21"
Database flags = 0x00404001 0x00001000
Controlfile Creation Timestamp  03/09/2012 23:04:33
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.00e7784f Resetlogs Timestamp  03/09/2012 23:11:46
Prior resetlogs scn: 0x0000.00e77780 Prior resetlogs Timestamp  03/09/2012 22:57:44
Redo Version: compatible=0xa200100
#Data files = 16, #Online files = 16
Database checkpoint: Thread=1 scn: 0x0000.00e7790a                                  ==>15169802
Threads: #Enabled=1, #Open=1, Head=1, Tail=1

***************************************************************************
REDO THREAD RECORDS
***************************************************************************
(size = 256, compat size = 256, section max = 8, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 9, numrecs = 8)
THREAD #1 - status:0xf thread links forward:0 back:0
#logs:3 first:1 last:3 current:2 last used seq#:0x2
enabled at scn: 0x0000.00e7784f 03/09/2012 23:11:46
disabled at scn: 0x0000.00000000 01/01/1988 00:00:00
opened at 03/09/2012 23:11:46 by instance G10R21
Checkpointed at scn:  0x0000.00e7790a 03/09/2012 23:12:12
thread:1 rba:(0x2.3.10)


SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;        

SQL> shutdown abort;
ORACLE instance shut down.

[oracle@vrh8 ~]$ rm /s01/recreate.ctl



我们以NORESETLOGS重建控制文件:



SQL> startup nomount;
ORACLE instance started.

Total System Global Area  620756992 bytes
Fixed Size                  2022760 bytes
Variable Size             234881688 bytes
Database Buffers          377487360 bytes
Redo Buffers                6365184 bytes
SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /s01/recreate.ctl



SQL> CREATE CONTROLFILE REUSE DATABASE "G10R21" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2921
  7  LOGFILE
  8    GROUP 1 (
  9      '/s01/oradata/G10R21/onlinelog/o1_mf_1_7ch812dg_.log',
10      '/s01/flash_recovery_area/G10R21/onlinelog/o1_mf_1_7ch813h5_.log'
11    ) SIZE 50M,
12    GROUP 2 (
13      '/s01/oradata/G10R21/onlinelog/o1_mf_2_7ch8144q_.log',
14      '/s01/flash_recovery_area/G10R21/onlinelog/o1_mf_2_7ch815cj_.log'
15    ) SIZE 50M,
16    GROUP 3 (
17      '/s01/oradata/G10R21/onlinelog/o1_mf_3_7ch816g2_.log',
18      '/s01/flash_recovery_area/G10R21/onlinelog/o1_mf_3_7ch817gp_.log'
19    ) SIZE 50M
20  -- STANDBY LOGFILE
DATAFILE
21   22    '/s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf',
23    '/s01/oradata/G10R21/datafile/o1_mf_undotbs1_7ch80081_.dbf',
24    '/s01/oradata/G10R21/datafile/o1_mf_sysaux_7ch8004x_.dbf',
25    '/s01/oradata/G10R21/datafile/o1_mf_users_7ch80085_.dbf',
26    '/s01/oradata/G10R21/datafile/o1_mf_example_7ch81ch3_.dbf',
27    '/s01/oradata/G10R21/datafile/o1_mf_users_7gvd5pwh_.dbf',
28    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6fdt_.dbf',
29    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6nxy_.dbf',
30    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6qc2_.dbf',
31    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6stt_.dbf',
32    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6w5w_.dbf',
33    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6yo0_.dbf',
34    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t717x_.dbf',
35    '/s01/oradata/G10R21/datafile/o1_mf_undotbs1_7h6ttkkx_.dbf',
36    '/s01/oradata/G10R21/datafile/o1_mf_undotbs1_7h6ttpn1_.dbf',
37    '/s01/oradata/G10R21/datafile/o1_mf_undotbs1_7h6ttsbd_.dbf'
38  CHARACTER SET AL32UTF8;

Control file created.


SQL> oradebug setmypid;
Statement processed.

SQL> oradebug dump controlf 3;
Statement processed.


***************************************************************************
DATABASE ENTRY
***************************************************************************
(size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
03/09/2012 23:16:57
DB Name "G10R21"
Database flags = 0x00400103 0x00001000
Controlfile Creation Timestamp  03/09/2012 23:16:58
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.00e7784f Resetlogs Timestamp  03/09/2012 23:11:46
Prior resetlogs scn: 0x0000.00e77780 Prior resetlogs Timestamp  03/09/2012 22:57:44
Redo Version: compatible=0xa200100
#Data files = 16, #Online files = 16
Database checkpoint: Thread=1 scn: 0x0000.00e77902                              15169794
Threads: #Enabled=1, #Open=1, Head=1, Tail=1


Database checkpoint: 变成了 00e77902  =》 15169794，比上次FULL CHECKPOINT的15169802   还要小

这个
00e77902  =》 15169794从哪里来？


***************************************************************************
REDO THREAD RECORDS
***************************************************************************
(size = 256, compat size = 256, section max = 8, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 9, numrecs = 8)
THREAD #1 - status:0x7 thread links forward:0 back:0
#logs:3 first:1 last:3 current:2 last used seq#:0x2
enabled at scn: 0x0000.00e7784f 03/09/2012 23:11:46
disabled at scn: 0x0000.00000000 01/01/1988 00:00:00
opened at 01/01/1988 00:00:00 by instance
Checkpointed at scn:  0x0000.00e77902 03/09/2012 23:11:54
thread:1 rba:(0x2.2.0)



***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  (name #3) /s01/oradata/G10R21/onlinelog/o1_mf_1_7ch812dg_.log
  (name #4) /s01/flash_recovery_area/G10R21/onlinelog/o1_mf_1_7ch813h5_.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000001 hws: 0x4 bsz: 512 nab: 0xb6 flg: 0x0 dup: 2
Archive links: fwrd: 2 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00e7784f 03/09/2012 23:11:46
Next scn: 0x0000.00e77902 03/09/2012 23:11:54
LOG FILE #2:
  (name #1) /s01/oradata/G10R21/onlinelog/o1_mf_2_7ch8144q_.log
  (name #2) /s01/flash_recovery_area/G10R21/onlinelog/o1_mf_2_7ch815cj_.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000002 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 2
Archive links: fwrd: 0 back: 1 Prev scn: 0x0000.00e7784f
Low scn: 0x0000.00e77902 03/09/2012 23:11:54
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
  (name #5) /s01/oradata/G10R21/onlinelog/o1_mf_3_7ch816g2_.log
  (name #6) /s01/flash_recovery_area/G10R21/onlinelog/o1_mf_3_7ch817gp_.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x00000000 hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00


原来这个
00e77902  =》 15169794 来源于  current online redo logfile中最小的LOW SCN






***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 428, compat size = 428, section max = 100, section in-use = 19,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  (name #22) /s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf
creation size=0 block size=8192 status=0x12 head=22 tail=22 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:2314 scn: 0x0000.00e77916 03/09/2012 23:12:46
Stop scn: 0xffff.ffffffff 03/09/2012 23:16:58
Creation Checkpointed at scn:  0x0000.00000008 10/22/2005 21:44:31
thread:0 rba:(0x0.0.0)

DATAFILE 1 的checkpoint scn最小为 00e77916 =》15169814


SQL> recover database using backup controlfile;
ORA-00279: change 15169814 generated at 03/09/2012 23:12:46 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R21/archivelog/2012_03_09/o1_mf_1_2_%u_.arc
ORA-00280: change 15169814 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}



ORA-00279: change 15169814 generated at 03/09/2012 23:12:46 needed for thread 1

可以看到 recover database using backup controlfile 的起点是 15169814  即 最小的一个 datafile header 的CHECKPOINT CHANGE#，

也就是说 丢失控制文件 并(create controlfile)重建后的recover 从 最小的datafile header CHECKPOINT_CHANGE#开始 ，直到NEXT SCN 无穷大的 current logfile的 end 为止!









我们在看一下 RESETLOGS的情况：


SQL>                                                         
SQL> CREATE CONTROLFILE REUSE DATABASE "G10R21" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
  2    3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2921
  7  LOGFILE
  8    GROUP 1 (
  9      '/s01/oradata/G10R21/onlinelog/o1_mf_1_7ch812dg_.log',
10      '/s01/flash_recovery_area/G10R21/onlinelog/o1_mf_1_7ch813h5_.log'
11    ) SIZE 50M,
12    GROUP 2 (
13      '/s01/oradata/G10R21/onlinelog/o1_mf_2_7ch8144q_.log',
14      '/s01/flash_recovery_area/G10R21/onlinelog/o1_mf_2_7ch815cj_.log'
15    ) SIZE 50M,
16    GROUP 3 (
17      '/s01/oradata/G10R21/onlinelog/o1_mf_3_7ch816g2_.log',
18      '/s01/flash_recovery_area/G10R21/onlinelog/o1_mf_3_7ch817gp_.log'
19    ) SIZE 50M
20  -- STANDBY LOGFILE
21  DATAFILE
22    '/s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf',
23    '/s01/oradata/G10R21/datafile/o1_mf_undotbs1_7ch80081_.dbf',
  '/s01/oradata/G10R21/datafile/o1_mf_sysaux_7ch8004x_.dbf',
24   25    '/s01/oradata/G10R21/datafile/o1_mf_users_7ch80085_.dbf',
26    '/s01/oradata/G10R21/datafile/o1_mf_example_7ch81ch3_.dbf',
27    '/s01/oradata/G10R21/datafile/o1_mf_users_7gvd5pwh_.dbf',
28    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6fdt_.dbf',
29    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6nxy_.dbf',
30    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6qc2_.dbf',
31    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6stt_.dbf',
32    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6w5w_.dbf',
33    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t6yo0_.dbf',
34    '/s01/oradata/G10R21/datafile/o1_mf_users_7h6t717x_.dbf',
35    '/s01/oradata/G10R21/datafile/o1_mf_undotbs1_7h6ttkkx_.dbf',
36    '/s01/oradata/G10R21/datafile/o1_mf_undotbs1_7h6ttpn1_.dbf',
37    '/s01/oradata/G10R21/datafile/o1_mf_undotbs1_7h6ttsbd_.dbf'
CHARACTER SET AL32UTF8; 38  

Control file created.


SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump controlf 3;
Statement processed.
SQL> oradebug tracefile_name;




***************************************************************************
DATABASE ENTRY
***************************************************************************
(size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
03/09/2012 23:20:03
DB Name "G10R21"
Database flags = 0x00400147 0x00001000
Controlfile Creation Timestamp  03/09/2012 23:20:03
Incmplt recovery scn: 0x0000.00e77932
Resetlogs scn: 0x0000.00e7784f Resetlogs Timestamp  03/09/2012 23:11:46
Prior resetlogs scn: 0x0000.00e77780 Prior resetlogs Timestamp  03/09/2012 22:57:44
Redo Version: compatible=0xa200100
#Data files = 16, #Online files = 16
Database checkpoint: Thread=0 scn: 0x0000.00000000
Threads: #Enabled=1, #Open=0, Head=0, Tail=0


***************************************************************************
REDO THREAD RECORDS
***************************************************************************
(size = 256, compat size = 256, section max = 8, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 9, numrecs = 8)
THREAD #1 - status:0x6 thread links forward:0 back:0
#logs:3 first:1 last:3 current:3 last used seq#:0x0
enabled at scn: 0x0000.00000000 01/01/1988 00:00:00
disabled at scn: 0x0000.00000000 01/01/1988 00:00:00
opened at 01/01/1988 00:00:00 by instance
Checkpointed at scn:  0x0000.00000000
thread:0 rba:(0x0.0.0)



***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  (name #5) /s01/oradata/G10R21/onlinelog/o1_mf_1_7ch812dg_.log
  (name #6) /s01/flash_recovery_area/G10R21/onlinelog/o1_mf_1_7ch813h5_.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #2:
  (name #3) /s01/oradata/G10R21/onlinelog/o1_mf_2_7ch8144q_.log
  (name #4) /s01/flash_recovery_area/G10R21/onlinelog/o1_mf_2_7ch815cj_.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #3:
  (name #1) /s01/oradata/G10R21/onlinelog/o1_mf_3_7ch816g2_.log
  (name #2) /s01/flash_recovery_area/G10R21/onlinelog/o1_mf_3_7ch817gp_.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x2 flg: 0xb dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00



虽然database checkpoint scn 等都是0 ，但是不影响 从 最小的datafile header checkpoint change 开始recover的 结论。



SQL> recover database using backup controlfile;
ORA-00279: change 15169814 generated at 03/09/2012 23:12:46 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R21/archivelog/2012_03_09/o1_mf_1_2_%u_.arc
ORA-00280: change 15169814 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

回复 只看该作者 道具 举报

15#
发表于 2012-3-9 21:27:07
强烈推荐 看过这个帖子的同学 再看一下 《What's the Point of Oracle Checkpoints?》 这个文档      巩固检查点的知识

回复 只看该作者 道具 举报

16#
发表于 2012-3-12 00:34:14
同时更新controlfile 控制文件 和  datafile headers 数据文件头中CHECKPOINT_CHANGE#的 有以下几种 可能：

1.Full Checkpoint             8i以后       alter system checkpoint or shutdown immediate

2.Thread Checkpoint         Alter system checkpoint local

3. File Checkpoint           Alter tablespace XXX offline  Alter tablespace XXX begin backup Alter tablespace XXX read only

4. Log Switch Checkpoint  -- 是在该种checkpoint 结束时


SQL> alter tablespace SYSTEM begin backup;

Tablespace altered.

SQL> alter tablespace UNDOTBS1 begin backup;

Tablespace altered.

SQL> alter tablespace SYSAUX begin backup;

Tablespace altered.

SQL> alter tablespace USERS begin backup;

Tablespace altered.

SQL> alter tablespace EXAMPLE begin backup;

Tablespace altered.

SQL> alter tablespace SYSTEM end backup;

Tablespace altered.

SQL> alter tablespace UNDOTBS1 end backup;

Tablespace altered.

SQL> alter tablespace SYSAUX end backup;

Tablespace altered.

SQL>  alter tablespace USERS end backup;

Tablespace altered.

SQL> alter tablespace EXAMPLE end backup;

Tablespace altered.

         
begin /end backup 会引发FILE CHECKPOINT ，造成datafile_header的 checkpoint_change# 更新



SQL> select distinct checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           15169814      
          15169827
           15169842
           15169821
           15169835

          15169814      =>E77916 是最小的一个 datafile header checkpoint_change#


SQL> oradebug setmypid;                     
Statement processed.

SQL> oradebug dump controlf 3;
Statement processed.

SQL> oradebug tracefile_name;



但是此时去dump controlf 可以发现 控制文件中的database checkpint 仍是 E7783A 15169802  即上一次FULL CHECKPOINT的 SCN

file checkpoint ，不是要同事更新control file and datafile head 的scn么？ 为什么只更新了datafile head而没有controfile的scn?

回复 只看该作者 道具 举报

17#
发表于 2012-3-12 00:38:49
还有上一页的，在不进行alter system checkpoint ;然后使用old controlfile去open database ,databse 不需要recover 。 但是你在操作的没有写出shutdown immediate，你既然想使用old controlfile，那么必须 shutdown immediate然后从新open。shutdown immediate就是完成full checkpoint，那么datafile head的scn就发生了变化，同样需要recover 。
同样，通过你的实验证明，数据库没有通过recover ，是不是shutdown immediate 不会更新 datafile head的scn呢？   和你的总结有出入。。  望解释。

               --------------不好意思，老总。老是打扰你。。新婚快乐

回复 只看该作者 道具 举报

18#
发表于 2012-3-12 00:40:29
scn: 0x0000.00e7790a                                  ==>15169802
这个有换算工具？ 或者人为的通过什么换算的，想知道。 谢谢您

回复 只看该作者 道具 举报

19#
发表于 2012-3-12 00:42:19
FILE CHECKPOINT

Controlfile and datafile headers are updated
CHECKPOINT_CHANGE#



SQL> select distinct checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          15334756

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> select distinct checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          15334990
          15334756
                  
                  
                  
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump controlf 3 ;
Statement processed.


***************************************************************************
DATABASE ENTRY
***************************************************************************
(size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
03/09/2012 23:20:03
DB Name "G10R21"
Database flags = 0x00404001 0x00001000
Controlfile Creation Timestamp  03/09/2012 23:20:03
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.00e7795c Resetlogs Timestamp  03/09/2012 23:22:15
Prior resetlogs scn: 0x0000.00e7784f Prior resetlogs Timestamp  03/09/2012 23:11:46
Redo Version: compatible=0xa200100
#Data files = 16, #Online files = 16
Database checkpoint: Thread=1 scn: 0x0000.00e9fd64                         => 00e9fd64= 15334756
Threads: #Enabled=1, #Open=1, Head=1, Tail=1
  Max log members = 3, Max data members = 1
Arch list: Head=3, Tail=3, Force scn: 0x0000.00e96b07scn: 0x0000.00e7795c
Activation ID: 2818178275
Controlfile Checkpointed at scn:  0x0000.00e9fe55 03/12/2012 04:36:31
thread:0 rba:(0x0.0.0)





***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 428, compat size = 428, section max = 100, section in-use = 19,
  last-recid= 16, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  (name #22) /s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf
creation size=0 block size=8192 status=0xe head=22 tail=22 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:2373 scn: 0x0000.00e9fe4e 03/12/2012 04:36:26                  ==> 00e9fe4e =15334990
Stop scn: 0xffff.ffffffff 03/12/2012 04:35:54
Creation Checkpointed at scn:  0x0000.00000008 10/22/2005 21:44:31
thread:0 rba:(0x0.0.0)


                  
Database checkpoint: Thread=1 scn: 0x0000.00e9fd64                         => 00e9fd64= 15334756
datafile 1  Checkpoint cnt:2373 scn: 0x0000.00e9fe4e 03/12/2012 04:36:26                  ==> 00e9fe4e =15334990


FILE checkpoint 是 数据文件级别的检查点， 它 更新的是 控制文件中的datafile record 和 datafile headers ， 说它 更新controlfile and datafile header 没有错。

回复 只看该作者 道具 举报

20#
发表于 2012-3-12 00:47:39
scn: 0x0000.00e7790a                                  ==>15169802

0x0000.00e7790a    是16进制 ， 转成十进制 就是15169802

回复 只看该作者 道具 举报

21#
发表于 2012-3-12 00:51:59
”还有上一页的，在不进行alter system checkpoint ;然后使用old controlfile去open database ,databse 不需要recover 。 “

如果 不执行"alter system checkpoint "  ，因为在短期内 控制文件中checkpoint scn 和 datafile 上的checkpoint scn 是一致的，所以 oracle 没有识别出这是一个 old controlfile 。

“。 但是你在操作的没有写出shutdown immediate，你既然想使用old controlfile，那么必须 shutdown immediate然后从新open。”

不需要， shutdown immediate 做checkpoint ， 但是"alter system checkpoint" 已经完成了一个检查点。  为了营造一个crash的环境 所以用了“ shutdown abort "

回复 只看该作者 道具 举报

22#
发表于 2012-3-17 22:54:38
请教：
控制文件中的Incmplt recovery scn是啥呀？为啥我的都是Incmplt recovery scn: 0x0000.00000000
还有，增量检查点更新控制文件的Controlfile Checkpointed at scn，为啥我两次（间隔>3s）dump控制文件的Controlfile Checkpointed at scn都是同一个值呀，而且都是几小时之前的值？我的是win系统

[ 本帖最后由 wzhihua 于 2012-3-18 08:14 编辑 ]

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-15 07:25 , Processed in 0.054363 second(s), 22 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部