[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 #23

Specify 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     TEA

SYS@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-03000000

SYS@book> @ &r/10to16 13227501297
10 to 16 HEX   REVERSE16
-------------- -----------------------------------
00003146ba6f1 0xf1a66b14-03000000

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 7,1
ub4 kscnbas                                 @484      0x146ba545

BBED> 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 =  0x146ba6f5

BBED> assign dba 7,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas =  0x146ba6f5
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 kscnbas                                 @484      0x146ba6f5

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 7,1
ub4 kscnbas                                 @484      0x146ba6f5

BBED> 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      0x146ba6f1

BBED> sum apply dba 7,1
Check value for File 7, Block 1:
current = 0x2279, required = 0x2279

BBED> verify   dba 7,1
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/tea01.dbf
BLOCK = 1

DBVERIFY - 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=BBED

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        13227501297 2016-03-31 15:19:34      13227207527       13227286650 OFFLINE                  26 YES /mnt/ramdisk/book/tea01.dbf    TEA

SYS@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     TEA

SYS@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 NO

BBED> p kcvfh.kcvfhrlc dba 1,1
ub4 kcvfhrlc                                @112      0x361e2b4f

SYS@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      0x0003

SYS@book> @ &r/16to10 3146ba6f2
16 to 10 DEC
------------
13227501298

--offset=116,对应RESETLOGS_CHANGE#的base部分.
--offset=120,对应RESETLOGS_CHANGE#的wrap部分.
--主要是这几个地方.

--另外发现x$kcvfh视图包含了许多这方面的信息,太复杂放弃!