20160331資料檔案offline與open resetlogs3
[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檢視包含了許多這方面的資訊,太複雜放棄!
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2073156/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 20160331資料檔案offline與open resetlogs
- 20160331資料檔案offline與open resetlogs2
- [20161019]資料檔案offline與open resetlog
- oracle 資料檔案offlineOracle
- OFFLINE和DROP資料檔案的理解
- 表空間OFFLINE和資料檔案OFFLINE的區別
- 表空間offline,資料檔案offline 的區別(ZT)
- 利用offline datafile檔案方式遷移資料
- 資料檔案OFFLINE的3種情況
- 表空間與資料檔案的offline和online操作
- 資料檔案、表空間offline用法及區別
- 資料檔案offline後unusable索引造成的問題索引
- 檔案與資料
- Data Guard 主端OFFLINE資料檔案和表空間
- [20160329]bbed修復offline的資料檔案.txt
- 重建控制檔案與 datafile offline,tablespace read only
- 資料檔案實驗操作datafile的create/offline/drop/rename等操作
- 轉載-表空間和資料檔案offline的影響分析
- online/offline 表空間和資料檔案需謹慎！
- 在open狀態下恢復丟失的資料檔案
- 【BBED】使用BBED修改資料檔案SCN，使該檔案從offline轉變為online
- 非歸檔模式下恢復利用offline drop命令誤刪除的資料檔案模式
- 臨時資料檔案 offline 對於匯入匯出的影響
- [20151028]理解資料檔案offline+drop.txt
- 在open狀態下恢復未備份的資料檔案
- 資料檔案合併與拆分
- JAVA檔案與資料流（1）Java
- [20180202]備庫資料檔案offline.txt
- [20161012]資料檔案offline馬上執行recover
- 資料檔案offline後，再online時，提示需要介質恢復。
- Oracle單個資料檔案損壞，在Rman命令裡設定表空間、資料檔案offline方式來恢復最方便Oracle
- 案例：在open狀態下恢復未備份的資料檔案
- ORA-00279異常處理_offline資料檔案缺失日誌檔案問題一鍵修復
- Oracle 表空間與資料檔案Oracle
- 新增資料檔案與恢復cf
- 【TABLESPACE】資料庫Open狀態下調整表空間資料檔案位置及名稱資料庫
- Office檔案的Open Xml 格式XML
- 將PDF檔案Open In MyAppAPP