[20161012]資料檔案offline馬上執行recover
[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時不需要恢復。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2126152/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 資料檔案offlineOracle
- OFFLINE和DROP資料檔案的理解
- 表空間OFFLINE和資料檔案OFFLINE的區別
- 表空間offline,資料檔案offline 的區別(ZT)
- 利用offline datafile檔案方式遷移資料
- 資料檔案OFFLINE的3種情況
- wine-在mac上執行exe執行檔案Mac
- 資料檔案、表空間offline用法及區別
- 資料檔案offline後unusable索引造成的問題索引
- 資料泵引數檔案用於執行資料泵命令
- 上傳執行sql檔案到linuxSQLLinux
- react中在函式繫結時會馬上執行.沒有加括號不會馬上執行React函式
- 執行大資料量SQL檔案大資料SQL
- Data Guard 主端OFFLINE資料檔案和表空間
- [20160329]bbed修復offline的資料檔案.txt
- 表空間與資料檔案的offline和online操作
- 資料檔案實驗操作datafile的create/offline/drop/rename等操作
- 轉載-表空間和資料檔案offline的影響分析
- 20160331資料檔案offline與open resetlogs
- online/offline 表空間和資料檔案需謹慎！
- 【BBED】使用BBED修改資料檔案SCN，使該檔案從offline轉變為online
- 採用job定時執行recover datafile
- 非歸檔模式下恢復利用offline drop命令誤刪除的資料檔案模式
- 3行程式碼列出硬碟上所有檔案及資料夾行程硬碟
- [20161019]資料檔案offline與open resetlog
- 臨時資料檔案 offline 對於匯入匯出的影響
- [20151028]理解資料檔案offline+drop.txt
- 執行react build 檔案ReactUI
- redis執行lua檔案Redis
- Java執行exe,bat等可執行檔案JavaBAT
- AIX上打包排除某些檔案/資料夾AI
- [20180202]備庫資料檔案offline.txt
- 資料檔案offline後，再online時，提示需要介質恢復。
- 20160331資料檔案offline與open resetlogs3S3
- 20160331資料檔案offline與open resetlogs2
- Oracle單個資料檔案損壞，在Rman命令裡設定表空間、資料檔案offline方式來恢復最方便Oracle
- 資料填充檔案最大一次能執行多少條sqlSQL
- 從C檔案到可執行elf檔案