本文共 12009 字,大约阅读时间需要 40 分钟。
[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 ProductionCREATE 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 AAAASCOTT@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.dbf4.开始测试恢复:
--先做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: 3ORA-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 0BBED> assign kcvfh.kcvfhckp.kcvcpscn.kscnbas=1731405
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y ub4 kscnbas @484 0x001a6b4dBBED> p /d kcvfh.kcvfhckp.kcvcpscn
struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 1731405 ub2 kscnwrp @488 0BBED> sum apply
Check value for File 6, Block 1: current = 0x6656, required = 0x6656SCOTT@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/bookSCOTT@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: 2col 0[2] @8158: 3
col 1[4] @8161: CCCCSYS@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 0BBED> assign kcvfh.kcvfhckp.kcvcpscn.kscnbas=1732543
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y ub4 kscnbas @484 0x001a6fbfBBED> p /d kcvfh.kcvfhckp.kcvcpscn
struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 1732543 ub2 kscnwrp @488 0BBED> sum apply
Check value for File 6, Block 1: current = 0x89a9, required = 0x89a9SYS@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时不需要恢复。
转载地址:http://jsbcl.baihongyu.com/