本文共 13042 字，大约阅读时间需要 43 分钟。
[20160331]数据文件offline与open resetlogs4.txt
--接上面的测试.链接:
--关机做一个冷备份,便于重复测试.取出冷备份,重复测试:
--做一些必要的清理清除归档.1.环境:
SYS@book> @ &r/ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production--session 1:
update t set name='aaaa' where id=1; commit ;--session 2:
update t set name='bbbb' where id=2; --不提交。--session 3:
SYS@book> alter database datafile 7 offline ; Database altered.--session 2:
commit ;alter system archive log current ;
alter system archive log current ; alter system archive log current ; alter system archive log current ;--切换出全部redo到归档。
SYS@book> shutdown abort ;
ORACLE instance shut down.--前面我提到我的恢复是归档,在线日志全在的情况下,如果归档日志丢失问题就大了,如果把offline的数据文件online呢?下面提供bbed修复.
2.重新打开数据库看看：
SYS@book> startup mount
ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes Database mounted.SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME ----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- --------------- 1 13227501289 2016-03-31 15:20:52 7 13227286650 ONLINE 1006 YES /mnt/ramdisk/book/system01.dbf SYSTEM 2 13227501289 2016-03-31 15:20:52 1834 13227286650 ONLINE 1002 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX 3 13227501289 2016-03-31 15:20:52 923328 13227286650 ONLINE 922 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1 4 13227501289 2016-03-31 15:20:52 16143 13227286650 ONLINE 1006 YES /mnt/ramdisk/book/users01.dbf USERS 5 13227501289 2016-03-31 15:20:52 952916 13227286650 ONLINE 919 YES /mnt/ramdisk/book/example01.dbf EXAMPLE 6 13227501289 2016-03-31 15:20:52 1314508 13227286650 ONLINE 935 YES /mnt/ramdisk/book/sugar01.dbf SUGAR 7 13227500869 2016-03-31 15:19:34 13227207527 13227286650 OFFLINE 26 YES /mnt/ramdisk/book/tea01.dbf TEA 7 rows selected.--如果我使用 recover database using backup controlfile until cancel;恢复后，不能在使用open打开，必须加入open resetlogs参数。
SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227501289 generated at 03/31/2016 15:20:52 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_23_907434361.dbf ORA-00280: change 13227501289 for thread 1 is in sequence #23Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 13227501295 generated at 03/31/2016 15:20:55 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf ORA-00280: change 13227501295 for thread 1 is in sequence #24 ORA-00278: log file '/u01/app/oracle/archivelog/book/1_23_907434361.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_24_907434361.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
SYS@book> @ &r/logfile
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME GROUP# STATUS TYPE MEMBER IS_ ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- -------------------------------------------------- --- 1 1 22 52428800 512 1 YES INACTIVE 13227501283 2016-03-31 15:20:51 13227501289 2016-03-31 15:20:52 1 ONLINE /mnt/ramdisk/book/redo01.log NO 2 1 23 52428800 512 1 YES ACTIVE 13227501289 2016-03-31 15:20:52 13227501295 2016-03-31 15:20:55 2 ONLINE /mnt/ramdisk/book/redo02.log NO 3 1 24 52428800 512 1 NO CURRENT 13227501295 2016-03-31 15:20:55 2.814750E+14 3 ONLINE /mnt/ramdisk/book/redo03.log NO--还需要 /mnt/ramdisk/book/redo03.log.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,7); FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME ----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------------------------- ------------------------------ 1 13227501295 2016-03-31 15:20:55 7 13227286650 ONLINE 1006 YES /mnt/ramdisk/book/system01.dbf SYSTEM 7 13227500869 2016-03-31 15:19:34 13227207527 13227286650 ONLINE 26 YES /mnt/ramdisk/book/tea01.dbf TEA--另外可以发现数据文件7在recover时已经设置为online.人为再次设置为offline.
SYS@book> alter database datafile 7 offline ; Database altered.SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227501295 generated at 03/31/2016 15:20:55 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf ORA-00280: change 13227501295 for thread 1 is in sequence #24 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_24_907434361.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_24_907434361.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227501295 generated at 03/31/2016 15:20:55 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf ORA-00280: change 13227501295 for thread 1 is in sequence #24 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /mnt/ramdisk/book/redo03.log Log applied. Media recovery complete.SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME ----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------ --------------- 1 13227501297 2016-03-31 15:20:55 7 13227286650 ONLINE 1007 NO /mnt/ramdisk/book/system01.dbf SYSTEM 7 13227500869 2016-03-31 15:19:34 13227207527 13227286650 OFFLINE 26 YES /mnt/ramdisk/book/tea01.dbf TEA--注意一个细节,数据文件7 是offline状态.也就是在mount状态设置offline,在恢复时不会在变成online.这样恢复就不像前面那样需要seq=20,21,22,23 归档.
--不知道这个细节oracle如何控制的??? --恢复仅仅需要/mnt/ramdisk/book/redo03.log,而数据文件7的CHECKPOINT_CHANGE#依旧等于13227500869.也就是没有进行任何恢复.SYS@book> alter database open resetlogs;
Database altered.SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME ----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------- ---------------- 1 13227501301 2016-03-31 15:27:11 7 13227501298 ONLINE 1009 YES /mnt/ramdisk/book/system01.dbf SYSTEM 7 13227500869 2016-03-31 15:19:34 13227207527 13227286650 OFFLINE 26 YES /mnt/ramdisk/book/tea01.dbf TEASYS@book> alter database datafile 7 online ; alter database datafile 7 online * ERROR at line 1: ORA-01190: control file or data file 7 is from before the last RESETLOGS ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'
--如果这个时候以前的归档都都丢失,如何online呢?使用bbed.
--有了前面的恢复经验,要恢复就很简单仅仅需要将file#=7的 CHECKPOINT_CHANGE# 从13227500869 修改为 13227501298-1=13227501297. --再执行recover就ok了.SYS@book> @ &r/10to16 13227500869
10 to 16 HEX REVERSE16 -------------- ----------------------------------- 00003146ba545 0x45a56b14-03000000SYS@book> @ &r/10to16 13227501297
10 to 16 HEX REVERSE16 -------------- ----------------------------------- 00003146ba6f1 0xf1a66b14-03000000BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 7,1
ub4 kscnbas @484 0x146ba545BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 1,1
ub4 kscnbas @484 0x146ba6f5--assign dba 7,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas = dba 1,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas
assign dba 7,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas = 0x146ba6f5BBED> assign dba 7,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas = 0x146ba6f5
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y ub4 kscnbas @484 0x146ba6f5BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 7,1
ub4 kscnbas @484 0x146ba6f5BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 1,1
ub4 kscnbas @484 0x146ba6f5 -- 噢,改错了BBED> assign dba 7,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas = 0x146ba6f1
ub4 kscnbas @484 0x146ba6f1BBED> sum apply dba 7,1
Check value for File 7, Block 1: current = 0x2279, required = 0x2279BBED> verify dba 7,1
DBVERIFY - Verification starting FILE = /mnt/ramdisk/book/tea01.dbf BLOCK = 1DBVERIFY - Verification complete
Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBEDSYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME ----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------ --------------- 1 13227501301 2016-03-31 15:27:11 7 13227501298 ONLINE 1009 YES /mnt/ramdisk/book/system01.dbf SYSTEM 7 13227501297 2016-03-31 15:19:34 13227207527 13227286650 OFFLINE 26 YES /mnt/ramdisk/book/tea01.dbf TEASYS@book> recover datafile 7 ;
Media recovery complete. SYS@book> alter database datafile 7 online ; Database altered.SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME ----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------- --------------- 1 13227501301 2016-03-31 15:27:11 7 13227501298 ONLINE 1009 YES /mnt/ramdisk/book/system01.dbf SYSTEM 7 13227502020 2016-03-31 15:41:59 13227207527 13227501298 ONLINE 28 YES /mnt/ramdisk/book/tea01.dbf TEASYS@book> select rowid,t.* from scott.t ;
ROWID ID NAME ------------------ ------------ ----- AAAWgeAAHAAAACLAAA 1 text AAAWgeAAHAAAACLAAB 2 text AAAWgeAAHAAAACLAAC 3 text AAAWgeAAHAAAACLAAD 4 text--但是我的一些dml操作丢失了.
--这样是改动最少的修改,仅仅需要修改offset=484.--我看了网上的一些帖子,除了要修改offset=484.还要修改:
SYS@book> select * from v$database_incarnation ;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED ------------ ----------------- ------------------- ----------------------- ------------------- ---------- ------------ ------------------ -------------------------- 1 1 2013-08-24 11:37:30 0 PARENT 824297850 0 NO 2 925702 2015-11-24 09:11:12 1 2013-08-24 11:37:30 PARENT 896605872 1 NO 3 13227285139 2016-03-25 16:45:42 925702 2015-11-24 09:11:12 PARENT 907433142 2 NO 4 13227286650 2016-03-25 17:06:01 13227285139 2016-03-25 16:45:42 PARENT 907434361 3 NO 5 13227501298 2016-03-31 15:27:11 13227286650 2016-03-25 17:06:01 CURRENT 907946831 4 NOBBED> p kcvfh.kcvfhrlc dba 1,1
ub4 kcvfhrlc @112 0x361e2b4fSYS@book> @ &r/16to10 361e2b4f
16 to 10 DEC ------------ 907946831--还要修改offset=112 ,对应RESETLOGS_ID.
BBED> p kcvfh.kcvfhrls dba 1,1
struct kcvfhrls, 8 bytes @116 ub4 kscnbas @116 0x146ba6f2 ub2 kscnwrp @120 0x0003SYS@book> @ &r/16to10 3146ba6f2
16 to 10 DEC ------------ 13227501298--offset=116,对应RESETLOGS_CHANGE#的base部分.
--offset=120,对应RESETLOGS_CHANGE#的wrap部分. --主要是这几个地方.--另外发现x$kcvfh视图包含了许多这方面的信息,太复杂放弃!
转载地址：http://gjcum.baihongyu.com/