[20161012]数据文件offline马上执行recover.txt
--前几天看的1篇文章，提到数据文件offline，应该养成随手执行recover习惯.保证下一次online时，不需要恢复。
--如果offline很久，忘记online了，而归档日志已经不存在，该如何跳过应用日志，online数据文件呢？
--以前也做过一些测试，再重复测试看看。(注意:这样恢复存在数据丢失的风险)
1.环境：
SCOTT@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
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 100M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
RMAN> delete archivelog all ;
2.建立测试数据：
SCOTT@book> create table t tablespace sugar as select rownum id ,'AAAA' name from dual connect by level<=4;
Table created.
SCOTT@book> select rowid,t.* from t;
ROWID ID NAME
------------------ ------------ ------
AAAVp2AAGAAAACDAAA 1 AAAA
AAAVp2AAGAAAACDAAB 2 AAAA
AAAVp2AAGAAAACDAAC 3 AAAA
AAAVp2AAGAAAACDAAD 4 AAAA
SCOTT@book> @ &r/rowid AAAVp2AAGAAAACDAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
88694 6 131 0 0x1800083 6,131 alter system dump datafile 6 block 131 ;
SCOTT@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 36 52428800 512 1 YES INACTIVE 1730954 2016-10-12 08:57:01 1730962 2016-10-12 08:57:04 1 ONLINE /mnt/ramdisk/book/redo01.log NO
2 1 35 52428800 512 1 YES INACTIVE 1730948 2016-10-12 08:57:00 1730954 2016-10-12 08:57:01 2 ONLINE /mnt/ramdisk/book/redo02.log NO
3 1 37 52428800 512 1 NO CURRENT 1730962 2016-10-12 08:57:04 2.814750E+14 3 ONLINE /mnt/ramdisk/book/redo03.log NO
--当前seq#=37.
--打开session1:
SCOTT@book> update t set name='BBBB' where id=2;
1 row updated.
--打开session2,执行：
SYS@book> alter system checkpoint ;
System altered.
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
--session1:
SCOTT@book> update t set name='CCCC' where id=3;
1 row updated.
--session2:
SYS@book> alter database datafile 6 offline ;
Database altered.
SCOTT@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 39 52428800 512 1 YES INACTIVE 1731312 2016-10-12 09:04:03 1731316 2016-10-12 09:04:06 1 ONLINE /mnt/ramdisk/book/redo01.log NO
2 1 38 52428800 512 1 YES INACTIVE 1731308 2016-10-12 09:04:02 1731312 2016-10-12 09:04:03 2 ONLINE /mnt/ramdisk/book/redo02.log NO
3 1 40 52428800 512 1 NO CURRENT 1731316 2016-10-12 09:04:06 2.814750E+14 3 ONLINE /mnt/ramdisk/book/redo03.log NO
----当前seq#=40.
--打开session2:
SCOTT@book> commit ;
Commit complete.
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
3.现在假想seq#=40的归档丢弃了。看看如何恢复。
$ cd /u01/app/oracle/archivelog/book
$ mkdir backup
$ mv 1_40_896605872.dbf backup/
$ ls -l backup/
total 102528
-rw-r----- 1 oracle oinstall 13824 2016-10-12 09:07:34 1_40_896605872.dbf
4.开始测试恢复：
--先做1个备份拷贝：
$ cp /mnt/ramdisk/book/sugar01.dbf backup/
SCOTT@book> alter database datafile 6 online ;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
SCOTT@book> recover datafile 6;
ORA-00279: change 1731316 generated at 10/12/2016 09:04:06 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_40_896605872.dbf
ORA-00280: change 1731316 for thread 1 is in sequence #40
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_40_896605872.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_40_896605872.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--由于丢失seq#=40的归档日志，恢复无法进行。
SCOTT@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 1731413 2016-10-12 09:07:35 7 925702 ONLINE 140 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 1731413 2016-10-12 09:07:35 1834 925702 ONLINE 138 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 1731413 2016-10-12 09:07:35 923328 925702 ONLINE 59 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 1731413 2016-10-12 09:07:35 16143 925702 ONLINE 137 YES /mnt/ramdisk/book/users01.dbf USERS
5 1731413 2016-10-12 09:07:35 952916 925702 ONLINE 57 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 1731316 2016-10-12 09:04:06 1730665 925702 OFFLINE 7 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
6 rows selected.
SCOTT@book> select recid,name,sequence#,first_change#,next_change# from v$archived_log where sequence#>=37;
RECID NAME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------ -------------------------------------------------- --------- ------------- ------------
35 /u01/app/oracle/archivelog/book/1_37_896605872.dbf 37 1730962 1731308
36 /u01/app/oracle/archivelog/book/1_38_896605872.dbf 38 1731308 1731312
37 /u01/app/oracle/archivelog/book/1_39_896605872.dbf 39 1731312 1731316
38 /u01/app/oracle/archivelog/book/1_40_896605872.dbf 40 1731316 1731405
39 /u01/app/oracle/archivelog/book/1_41_896605872.dbf 41 1731405 1731409
40 /u01/app/oracle/archivelog/book/1_42_896605872.dbf 42 1731409 1731413
6 rows selected.
--seq#＝40已经不存在，也就是要跳过归档40.从scn=1731405开始恢复。实际上其他不需要修改，至少我测试这个版本不需要修改。
4.通过bbed修改文件头：
BBED> p /d kcvfh.kcvfhckp.kcvcpscn
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 1731316
ub2 kscnwrp @488 0
BBED> assign kcvfh.kcvfhckp.kcvcpscn.kscnbas=1731405
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 kscnbas @484 0x001a6b4d
BBED> p /d kcvfh.kcvfhckp.kcvcpscn
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 1731405
ub2 kscnwrp @488 0
BBED> sum apply
Check value for File 6, Block 1:
current = 0x6656, required = 0x6656
SCOTT@book> recover datafile 6;
Media recovery complete.
SCOTT@book> alter database datafile 6 online ;
Database altered.
SCOTT@book> select rowid,t.* from t;
ROWID ID NAME
------------------ --- -----
AAAVp2AAGAAAACDAAA 1 AAAA
AAAVp2AAGAAAACDAAB 2 BBBB
AAAVp2AAGAAAACDAAC 3 AAAA
AAAVp2AAGAAAACDAAD 4 AAAA
--你可以发现这样恢复id=3的修改丢失。
5.好了，现在重新再来恢复一次。这次能找到seq#=40.
SCOTT@book> alter database datafile 6 offline ;
Database altered.
$ cd /u01/app/oracle/archivelog/book
$ mv backup/1_40_896605872.dbf .
$ cp backup/sugar01.dbf /mnt/ramdisk/book
SCOTT@book> alter database datafile 6 online ;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
SCOTT@book> recover datafile 6;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [6], [131], [25165955], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 6, block# 131, file offset is 1073152 bytes)
ORA-10564: tablespace SUGAR
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 88694
--出现了不一致的情况。如果使用bbed检查数据块，发现：
BBED> set dba 6,131
DBA 0x01800083 (25165955 6,131)
BBED> x /rnc *kdbr[2]
rowdata[11] @8155
-----------
flag@8155: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8156: 0x02
cols@8157: 2
col 0[2] @8158: 3
col 1[4] @8161: CCCC
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 1732543 2016-10-12 09:35:57 7 925702 ONLINE 142 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 1732543 2016-10-12 09:35:57 1834 925702 ONLINE 140 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 1732543 2016-10-12 09:35:57 923328 925702 ONLINE 61 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 1732543 2016-10-12 09:35:57 16143 925702 ONLINE 139 YES /mnt/ramdisk/book/users01.dbf USERS
5 1732543 2016-10-12 09:35:57 952916 925702 ONLINE 59 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 1731413 2016-10-12 09:07:35 1730665 925702 OFFLINE 7 NO /mnt/ramdisk/book/sugar01.dbf SUGAR
6 rows selected.
SYS@book> select recid,name,sequence#,first_change#,next_change# from v$archived_log where sequence#>=37;
RECID NAME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------ -------------------------------------------------- ------------ ------------- ------------
35 /u01/app/oracle/archivelog/book/1_37_896605872.dbf 37 1730962 1731308
36 /u01/app/oracle/archivelog/book/1_38_896605872.dbf 38 1731308 1731312
37 /u01/app/oracle/archivelog/book/1_39_896605872.dbf 39 1731312 1731316
38 /u01/app/oracle/archivelog/book/1_40_896605872.dbf 40 1731316 1731405
39 /u01/app/oracle/archivelog/book/1_41_896605872.dbf 41 1731405 1731409
40 /u01/app/oracle/archivelog/book/1_42_896605872.dbf 42 1731409 1731413
6 rows selected.
--seq#=42已经应用完成。scn=1731413.
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 42 52428800 512 1 YES INACTIVE 1731409 2016-10-12 09:07:34 1731413 2016-10-12 09:07:35 1 ONLINE /mnt/ramdisk/book/redo01.log NO
2 1 41 52428800 512 1 YES INACTIVE 1731405 2016-10-12 09:07:34 1731409 2016-10-12 09:07:34 2 ONLINE /mnt/ramdisk/book/redo02.log NO
3 1 43 52428800 512 1 NO CURRENT 1731413 2016-10-12 09:07:35 2.814750E+14 3 ONLINE /mnt/ramdisk/book/redo03.log NO
--不知道如何修复，再增加scn看看。
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> p /d kcvfh.kcvfhckp.kcvcpscn
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 1731413
ub2 kscnwrp @488 0
BBED> assign kcvfh.kcvfhckp.kcvcpscn.kscnbas=1732543
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 kscnbas @484 0x001a6fbf
BBED> p /d kcvfh.kcvfhckp.kcvcpscn
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 1732543
ub2 kscnwrp @488 0
BBED> sum apply
Check value for File 6, Block 1:
current = 0x89a9, required = 0x89a9
SYS@book> recover datafile 6;
Media recovery complete.
SYS@book> alter database datafile 6 online ;
Database altered.
SYS@book> select rowid,t.* from scott.t;
ROWID ID NAME
------------------ --- ------
AAAVp2AAGAAAACDAAA 1 AAAA
AAAVp2AAGAAAACDAAB 2 BBBB
AAAVp2AAGAAAACDAAC 3 CCCC
AAAVp2AAGAAAACDAAD 4 AAAA
--总结：
数据文件offline，最好随手执行1次recover。避免时间久了，下次online时，归档已经不存在的情况。
先发一个alter system checkpoint，也许也是一个好习惯。
或者如果仅仅表空间仅仅1个数据文件，可以offline表空间，这样表空间online时不需要恢复。