[转帖]using backup controlfile 两种使用情况区别_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1867 | 回复: 0   主题： [转帖]using backup controlfile 两种使用情况区别        下一篇 
huan.liu
注册用户
等级：新兵
经验：72
发帖：58
精华：0
注册：2011-11-23
状态：离线
发送短消息息给huan.liu 加好友    发送短消息息给huan.liu 发消息
发表于： IP：您无权察看 2015-3-20 9:26:32 | [全部帖] [楼主帖] 楼主

using backup controlfile 两种使用情况区别

试验准备条件

SQL> select* fromv$version;
BANNER
-------------------------------------------------------------------------
Oracle Database11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS forLinux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> selectto_char(sysdate,'yyyy-mm-dd')"www.xifenfei.com"fromdual;
www.xifenfei.com
--------------------
2012-07-13
SQL> alterdatabaseopenresetlogs;
Databasealtered.
SQL> selectnamefromv$controlfile;
NAME
----------------------------------------------------
/u01/oracle/oradata/ora11g/control01.ctl
SQL> !cp /u01/oracle/oradata/ora11g/control01.ctl /tmp/xff.ctl
SQL> altersystem checkpoint;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> altersystem switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> altersystem checkpoint;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> !rm /u01/oracle/oradata/ora11g/control01.ctl
SQL> ! cp /tmp/xff.ctl /u01/oracle/oradata/ora11g/control01.ctl
SQL> startup
ORACLE instance started.
Total System GlobalArea  523108352 bytes
Fixed Size1346052 bytes
Variable Size432014844 bytes
DatabaseBuffers           83886080 bytes
Redo Buffers                5861376 bytes
Databasemounted.
ORA-01122: databasefile 1 failed verification check
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
ORA-01207: file ismore recent than control file - old control file


相关说明：
1.通过resetlogs使得试验更加清晰
2.通过多次的checkpoint实现增加scn,switch logfile实现日志组切换
3.通过模拟备份控制文件恢复

查询相关SCN

SQL> setlinesize 150
SQL> selectfile#,to_char(checkpoint_change#,'9999999999999999') "SCN",
2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
3  fromv$datafile_header;
FILE# SCN                                RESETLOGS SCN                      FUZZY
---------- ---------------------------------- ---------------------------------- ------
1           2118981                            2118577                  YES
2           2118981                            2118577                  YES
3           2118981                            2118577                  YES
4           2118981                            2118577                  YES
6           2118981                            2118577                  YES
SQL> selectfile#,to_char(checkpoint_change#,'999999999999999') "SCN",
2  to_char(last_change#,'999999999999999')"STOP_SCN"fromv$datafile;
FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
1          2118580
2          2118580
3          2118580
4          2118580
6          2118580
SQL> selectCONTROLFILE_CHANGE#   fromv$database;
CONTROLFILE_CHANGE#
-------------------
2118713


做关于控制文件和数据文件dump

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump file_hdrs 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_593.trc
SQL> exit
Disconnected fromOracle Database11g Enterprise Edition Release 11.2.0.3.0 - Production
Withthe Partitioning, OLAP, Data Mining andRealApplication Testing options
[oracle@xifenfei ~]$ sqlplus / assysdba
SQL*Plus: Release 11.2.0.3.0 Production onFri Jul 13 03:05:48 2012
Copyright (c) 1982, 2011, Oracle.  Allrights reserved.
Connected to:
Oracle Database11g Enterprise Edition Release 11.2.0.3.0 - Production
Withthe Partitioning, OLAP, Data Mining andRealApplication Testing options
SQL> oradebug setmypid;
Statement processed.
SQL>  oradebug dump controlf 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_775.trc


分析file_hdrs 3 dump文件

--datafile 1的datafile header信息
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000007 09/18/201117:33:47
Backup taken at scn: 0x0000.00000000 01/01/198800:00:00 thread:0
reset logs count:0x2eff82e3 scn: 0x0000.002053b1
prev reset logs count:0x2e9e8451 scn: 0x0000.0016eaab
recovered at 07/13/201202:56:18
status:0x2004 root dba:0x00400208 chkpt cnt: 760 ctl cnt:759
begin-hot-backup filesize: 0
Checkpointed at scn:  0x0000.00205545 07/13/201203:01:42
--datafile 1的控制文件中信息
DATA FILE #1:
name #7: /u01/oracle/oradata/ora11g/system01.dbf
creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/198800:00:00
Checkpoint cnt:752 scn: 0x0000.002053b4 07/13/201202:59:18
Stop scn: 0xffff.ffffffff 07/13/201202:58:43
Creation Checkpointed at scn:  0x0000.00000007 09/18/201117:33:47


通过这里可以看出来:
datafile header的Checkpointed scn(00205545)>controfile datafile的Checkpoint scn(002053b4)
datafile header的checkpiont count(760)>controfile datafile的checkpiont count(752)
所以在数据库open的时候会报ORA-01207错误

尝试恢复数据库

SQL> recover databaseusing backup controlfile;
ORA-00279: change 2118713 generated at07/13/2012 02:58:43 needed forthread 1
ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_1_788497123.dbf
ORA-00280: change 2118713 forthread 1 isinsequence#1
Specify log: {<RET>=suggested filename AUTO CANCEL}
ORA-00308: cannot openarchived log
'/u01/oracle/oradata/archivelog/ora11g/1_1_788497123.dbf'
ORA-27037: unable toobtain file status
Linux Error: 2: Nosuch file ordirectory
Additional information: 3
SQL>
SQL> selectto_char(2118713,'xxxxxxx') fromdual;
TO_CHAR(2118713,
----------------
205439


分析controlf 3 dump文件

***************************************************************************
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/12/201222:17:06
DB Name "ORA11G"
Database flags = 0x00404000 0x00001000
Controlfile Creation Timestamp  03/12/201222:17:07
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.002053b1 Resetlogs Timestamp  07/13/201202:58:43
Prior resetlogs scn: 0x0000.0016eaab Prior resetlogs Timestamp  05/01/201213:14:57
Redo Version: compatible=0xb200000
#Data files = 5, #Online files = 5
Database checkpoint: Thread=1 scn: 0x0000.002053b4
Threads: #Enabled=1, #Open=1, Head=1, Tail=1
Max log members = 3, Max data members = 1
Arch list: Head=0, Tail=0, Force scn: 0x0000.00000000scn: 0x0000.00000000
Activation ID: 4184707968


Controlfile Checkpointed at scn:  0x0000.00205439 07/13/201202:59:25 <==控制文件checkpiont,控制文件每3秒的一次checkpiont

thread:0 rba:(0x0.0.0)


通过recover的提示和对于controlf 3 dump文件的分析,可以确定数据库使用备份控制文件恢复,需要改控制文件备份之时开始的所有归档日志

查询数据库当前redo情况

SQL> selectmember fromv$logfile;
MEMBER
-------------------------------------------------------------
/u01/oracle/oradata/ora11g/redo03.log
/u01/oracle/oradata/ora11g/redo02.log
/u01/oracle/oradata/ora11g/redo01.log
SQL> selectSEQUENCE#,STATUS,FIRST_CHANGE# ,NEXT_CHANGE# fromv$log;
SEQUENCE# STATUS                           FIRST_CHANGE# NEXT_CHANGE#
---------- -------------------------------- ------------- ------------
1 CURRENT2118577   2.8147E+14
0 UNUSED                                       0            0
0 UNUSED                                       0            0
SQL> archive log list;
Databaselog mode              NoArchive Mode
Automatic archival             Disabled
Archive destination            /u01/oracle/oradata/archivelog/ora11g
Oldest online log sequence1
Currentlog sequence1
SQL> altersystem dump logfile '/u01/oracle/oradata/ora11g/redo03.log';
System altered.
SQL> altersystem dump logfile '/u01/oracle/oradata/ora11g/redo02.log';
System altered.
SQL> altersystem dump logfile '/u01/oracle/oradata/ora11g/redo01.log';
System altered.


因为数据库处于非归档模式,而得到的redo信息主要都是来自控制文件,所以只能通过dump redo来分析当前redo的情况

分析redo log dump

DUMP OF REDO FROM FILE '/u01/oracle/oradata/ora11g/redo03.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 = 186646528=0xb200000
Db ID=4173966754=0xf8c9ada2, Db Name='ORA11G'
Activation ID=4184707968=0xf96d9380
Control Seq=7660=0x1dec, File size=30720=0x7800
File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000003, SCN 0x00000020553d-0x000000205540"
thread: 1 nab: 0x2 seq: 0x00000003 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x2eff82e3 scn: 0x0000.002053b1 (2118577)
prev resetlogs count: 0x2e9e8451 scn: 0x0000.0016eaab (1501867)
Low  scn: 0x0000.0020553d (2118973) 07/13/201203:01:34
Next scn: 0x0000.00205540 (2118976) 07/13/201203:01:35
Enabled scn: 0x0000.002053b1 (2118577) 07/13/201202:58:43
Thread closed scn: 0x0000.0020553d (2118973) 07/13/201203:01:34
Disk cksum: 0xa716 Calc cksum: 0xa716
DUMP OF REDO FROM FILE '/u01/oracle/oradata/ora11g/redo02.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 = 186646528=0xb200000
Db ID=4173966754=0xf8c9ada2, Db Name='ORA11G'
Activation ID=4184707968=0xf96d9380
Control Seq=7663=0x1def, File size=30720=0x7800
File Number=2, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000005, SCN 0x000000205543-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x00000005 hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x2eff82e3 scn: 0x0000.002053b1 (2118577)
prev resetlogs count: 0x2e9e8451 scn: 0x0000.0016eaab (1501867)
Low  scn: 0x0000.00205543 (2118979) 07/13/201203:01:36
Next scn: 0xffff.ffffffff 01/01/198800:00:00
Enabled scn: 0x0000.002053b1 (2118577) 07/13/201202:58:43
Thread closed scn: 0x0000.00205543 (2118979) 07/13/201203:01:36
Disk cksum: 0xc3f9 Calc cksum: 0xc3f9
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery  01/01/198800:00:00
Most recent redo scn: 0x0000.00000000
DUMP OF REDO FROM FILE '/u01/oracle/oradata/ora11g/redo01.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 = 186646528=0xb200000
Db ID=4173966754=0xf8c9ada2, Db Name='ORA11G'
Activation ID=4184707968=0xf96d9380
Control Seq=7663=0x1def, File size=30720=0x7800
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000004, SCN 0x000000205540-0x000000205543"
thread: 1 nab: 0x2 seq: 0x00000004 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x2eff82e3 scn: 0x0000.002053b1 (2118577)
prev resetlogs count: 0x2e9e8451 scn: 0x0000.0016eaab (1501867)
Low  scn: 0x0000.00205540 (2118976) 07/13/201203:01:35
Next scn: 0x0000.00205543 (2118979) 07/13/201203:01:36
Enabled scn: 0x0000.002053b1 (2118577) 07/13/201202:58:43
Thread closed scn: 0x0000.00205540 (2118976) 07/13/201203:01:35
Disk cksum: 0xaa26 Calc cksum: 0xaa26
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery  01/01/198800:00:00
Most recent redo scn: 0x0000.00000000


通过对redo dump的分析可以得到：
1.最小的sequence#=3是redo03.log
2.current redo为redo02.log

继续尝试恢复

SQL> recover databaseusing backup controlfile;
ORA-00279: change 2118713 generated at07/13/2012 02:58:43 needed forthread 1
ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_1_788497123.dbf
ORA-00280: change 2118713 forthread 1 isinsequence#1
Specify log: {<RET>=suggested filename AUTO CANCEL}
/u01/oracle/oradata/ora11g/redo02.log
ORA-00326: log begins atchange 2118979, need earlier change 2118713
ORA-00334: archived log: '/u01/oracle/oradata/ora11g/redo02.log'


关于重建控制文件后使用using backup controlfile总结:恢复的启动控制文件备份的scn,需要该控制文件备份后的所有归档日志.对于当前这个非归档,而且redo被覆盖的库,该方法无法正常恢复

重建控制文件并做controlf 3 dump

SQL> alterdatabasebackup controlfile totrace as'/tmp/ctl.trace';
Databasealtered.
SQL> shutdown immediate
ORA-01109: databasenotopen
Databasedismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATECONTROLFILE REUSE DATABASE"ORA11G"NORESETLOGS  NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP1 '/u01/oracle/oradata/ora11g/redo01.log'SIZE15M BLOCKSIZE 512,
GROUP2 '/u01/oracle/oradata/ora11g/redo02.log'SIZE15M BLOCKSIZE 512,
GROUP3 '/u01/oracle/oradata/ora11g/redo03.log'SIZE15M BLOCKSIZE 512
DATAFILE
'/u01/oracle/oradata/ora11g/system01.dbf',
'/u01/oracle/oradata/ora11g/sysaux01.dbf',
'/u01/oracle/oradata/ora11g/undotbs01.dbf',
'/u01/oracle/oradata/ora11g/users01.dbf',
'/u01/oracle/oradata/ora11g/xifenfei02.dbf'
CHARACTERSETAL32UTF8
;
ORACLE instance started.
Total System GlobalArea  523108352 bytes
Fixed Size1346052 bytes
Variable Size432014844 bytes
DatabaseBuffers           83886080 bytes
Redo Buffers                5861376 bytes
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18
Control file created.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump controlf 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_1867.trc


尝试数据库恢复

SQL> recover databaseusing backup controlfile;
ORA-00279: change 2118981 generated at07/13/2012 03:01:42 needed forthread 1
ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_5_788497123.dbf
ORA-00280: change 2118981 forthread 1 isinsequence#5
Specify log: {<RET>=suggested filename AUTO CANCEL}
ORA-00308: cannot openarchived log
'/u01/oracle/oradata/archivelog/ora11g/1_5_788497123.dbf'
ORA-27037: unable toobtain file status
Linux Error: 2: Nosuch file ordirectory
Additional information: 3
SQL>
SQL> selectto_char(2118981,'xxxxxxx') fromdual;
TO_CHAR(2118981,
----------------
205545
SQL> setlinesize 150
SQL> selectfile#,to_char(checkpoint_change#,'9999999999999999') "SCN",
2 to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
3 fromv$datafile_header;
FILE# SCN RESETLOGS SCN FUZZY
---------- ---------------------------------- ---------------------------------- ------
1 2118981 2118577 YES
2 2118981 2118577 YES
3 2118981 2118577 YES
4 2118981 2118577 YES
6 2118981 2118577 YES
SQL> selectfile#,to_char(checkpoint_change#,'999999999999999') "SCN",
2 to_char(last_change#,'999999999999999')"STOP_SCN"fromv$datafile;
FILE# SCN STOP_SCN
---------- -------------------------------- --------------------------------
1 2118981
2 2118981
3 2118981
4 2118981
6 2118981


分析 controlf 3 dump文件

***************************************************************************
DATABASEENTRY
***************************************************************************
(size= 316, compat size= 316, sectionmax= 1, sectionin-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
07/13/2012 03:24:51
DB Name"ORA11G"
Databaseflags = 0x00400102 0x00001000
Controlfile Creation Timestamp07/13/2012 03:24:51
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.002053b1 Resetlogs Timestamp07/13/2012 02:58:43
Priorresetlogs scn: 0x0000.0016eaab Priorresetlogs Timestamp05/01/2012 13:14:57
Redo Version: compatible=0xb200000
#Data files = 5, #Online files = 5
Databasecheckpoint: Thread=1 scn: 0x0000.00205543
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size= 520, compat size= 520, sectionmax= 100, sectionin-use = 6,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
name#8: /u01/oracle/oradata/ora11g/system01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpointcnt:760 scn: 0x0000.00205545 07/13/2012 03:01:42
Stop scn: 0xffff.ffffffff 07/13/2012 03:24:51
Creation Checkpointed atscn:  0x0000.00000007 09/18/2011 17:33:47


完成恢复

SQL> recover databaseusing backup controlfile;
ORA-00279: change 2118981 generated at07/13/2012 03:01:42 needed forthread 1
ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_5_788497123.dbf
ORA-00280: change 2118981 forthread 1 isinsequence#5
Specify log: {<RET>=suggested filename AUTO CANCEL}
/u01/oracle/oradata/ora11g/redo02.log
Log applied.
Media recovery complete.


关于重建控制文件后使用using backup总结:重建控制文件后,恢复的起点是datafile header scn 最小值,需要改scn之后的所有日志








--转自 北京联动北方科技有限公司


赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com