[20160405]bbed修改檔案頭.txt
[20160405]bbed修改檔案頭.txt
--以前做過一次,重複測試:
http://blog.itpub.net/267265/viewspace-746222/
如果資料庫資料檔案損壞,並且archivelog損壞,這樣無法完全恢復,如果僅僅某個資料檔案的scn與其他檔案不同步,導致該資料檔案無法mount.
正常可以像odu之類的工具恢復.但是在實際上如果修改資料檔案的scn保持同步,這樣資料庫可以正常開啟,選擇常規的方法imp/exp以及expdp/impdp
方式恢復,這樣雖然丟失一部分資料,至少一定程度減少損失.
--以前測試有點亂.
1.環境:
--冷備份資料庫:
$ cp tea01.dbf tea01.dbf_ORG
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
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name BOOK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /mnt/ramdisk/book/system01.dbf
2 1580 SYSAUX *** /mnt/ramdisk/book/sysaux01.dbf
3 1435 UNDOTBS1 *** /mnt/ramdisk/book/undotbs01.dbf
4 500 USERS *** /mnt/ramdisk/book/users01.dbf
5 346 EXAMPLE *** /mnt/ramdisk/book/example01.dbf
6 100 SUGAR *** /mnt/ramdisk/book/sugar01.dbf
7 1 TEA *** /mnt/ramdisk/book/tea01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 400 TEMP 32767 /mnt/ramdisk/book/temp01.dbf
SYS@book> select * from scott.empx where rownum<=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 YYYY CLERK 7902 1980-12-17 00:00:00 800 20
SYS@book> update scott.empx set ename='ZZZZ' where EMPNO=7369;
1 row updated.
SYS@book> commit ;
Commit complete.
alter system archive log current ;
/
/
/
2.假設現在資料庫破壞,tea檔案僅僅存在舊的備份,看看是否online看看.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ cp tea01.dbf tea01.dbf_0405
$ mv tea01.dbf_ORG tea01.dbf
--模擬歸檔丟失.
$ cd /u01/app/oracle/archivelog/
$ mv book book.xxx
$ mkdir book
3.測試:
SYS@book> startup
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.
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'
--都是歸檔,無法恢復.
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 13227502431 2016-04-05 11:33:18 7 13227286650 ONLINE 1008 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13227502431 2016-04-05 11:33:18 1834 13227286650 ONLINE 1004 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13227502431 2016-04-05 11:33:18 923328 13227286650 ONLINE 924 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13227502431 2016-04-05 11:33:18 16143 13227286650 ONLINE 1008 NO /mnt/ramdisk/book/users01.dbf USERS
5 13227502431 2016-04-05 11:33:18 952916 13227286650 ONLINE 921 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13227502431 2016-04-05 11:33:18 1314508 13227286650 ONLINE 937 NO /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13227500866 2016-03-31 08:53:17 13227207527 13227286650 ONLINE 25 NO /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
SYS@book> @ &r/10to16 13227502431
10 to 16 HEX REVERSE16
-------------- -----------------------------------
00003146bab5f 0x5fab6b14-03000000
SYS@book> @ &r/10to16 13227500866
10 to 16 HEX REVERSE16
-------------- -----------------------------------
00003146ba542 0x42a56b14-03000000
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 7,1
ub4 kscnbas @484 0x146ba542
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 1,1
ub4 kscnbas @484 0x146bab5f
BBED> assign dba 7,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas = dba 1,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 kscnbas @484 0x146bab5f
BBED> sum apply dba 7.1
BBED-00205: illegal or out of range DBA (File 0, Block 7)
BBED> sum apply dba 7,1
Check value for File 7, Block 1:
current = 0x0b12, required = 0x0b12
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;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
1 13227502431 2016-04-05 11:33:18 7 13227286650 ONLINE 1008 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13227502431 2016-04-05 11:33:18 1834 13227286650 ONLINE 1004 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13227502431 2016-04-05 11:33:18 923328 13227286650 ONLINE 924 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13227502431 2016-04-05 11:33:18 16143 13227286650 ONLINE 1008 NO /mnt/ramdisk/book/users01.dbf USERS
5 13227502431 2016-04-05 11:33:18 952916 13227286650 ONLINE 921 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13227502431 2016-04-05 11:33:18 1314508 13227286650 ONLINE 937 NO /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13227502431 2016-03-31 08:53:17 13227207527 13227286650 ONLINE 25 NO /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'
SYS@book> recover datafile 7 ;
Media recovery complete.
SYS@book> alter database open ;
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;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ---------------
1 13227502434 2016-04-05 11:43:01 7 13227286650 ONLINE 1009 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13227502434 2016-04-05 11:43:01 1834 13227286650 ONLINE 1005 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13227502434 2016-04-05 11:43:01 923328 13227286650 ONLINE 925 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13227502434 2016-04-05 11:43:01 16143 13227286650 ONLINE 1009 YES /mnt/ramdisk/book/users01.dbf USERS
5 13227502434 2016-04-05 11:43:01 952916 13227286650 ONLINE 922 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13227502434 2016-04-05 11:43:01 1314508 13227286650 ONLINE 938 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13227502434 2016-04-05 11:43:01 13227207527 13227286650 ONLINE 32 YES /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
--自動修復CHECKPOINT_COUNT記數.視乎11.2.0.4不再需要修改CHECKPOINT_COUNT.
SYS@book> select * from scott.empx where rownum<=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
7369 YYYY CLERK 7902 1980-12-17 00:00:00 800 20
--不過修改都丟失了.還原:
$ mv tea01.dbf_0405 tea01.dbf
$ cd /u01/app/oracle/archivelog/
$ mv book book.test
$ mv book.xxx/ book
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> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'
SYS@book> recover datafile 7 ;
Media recovery complete.
SYS@book> alter database open ;
Database altered.
SYS@book> select * from scott.empx where rownum<=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 ZZZZ CLERK 7902 1980-12-17 00:00:00 800 20
--OK,已經還原了.
--CHECKPOINT_COUNT的位置:
BBED> p kcvfh.kcvfhcpc dba 7,1
ub4 kcvfhcpc @140 0x00000023
BBED> p kcvfh.kcvfhcpc dba 1,1
ub4 kcvfhcpc @140 0x000003f3
SYS@book> @ &r/16to10 3f3
16 to 10 DEC
------------
1011
SYS@book> @ &r/16to10 23
16 to 10 DEC
------------
35
--實際上這樣的測試存在許多問題,比如drop表,create表,這樣在系統表空間存在定義,而資料檔案已經丟失了.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2075424/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20160407]bbed修改檔案頭2(補充).txt
- [20160405]bbed的assign命令.txt
- BBED 修改檔案頭 解決 ORA-01190
- bbed 與檔案頭恢復
- 【kingsql分享】使用BBED修改Oracle資料檔案頭推進SCNSQLOracle
- 使用BBED修改檔案頭解決資料庫Open驗證問題（下）資料庫
- 使用BBED修改檔案頭解決資料庫Open驗證問題（上）資料庫
- 【BBED】 SYSTEM檔案頭損壞的恢復(4)
- 【BBED】使用BBED修改資料檔案SCN，使該檔案從offline轉變為online
- 使用BBED檢視SYSTEM檔案頭的root dba及bootstrap$boot
- oracle_bbed.Datafile.Header_System.資料檔案頭資訊OracleHeader
- [20140624]bbed修改資料記錄.txt
- [20150527]bbed解決資料檔案大小問題.txt
- [20160329]bbed修復offline的資料檔案.txt
- 使用BBED跳過歸檔檔案
- [20190104]bbed手動修改資料.txt
- [20160526]bbed修改資料記錄(不等長).txt
- 樹莓派ubuntu系統下修改config.txt檔案 樹莓派config.txt檔案修改記錄樹莓派Ubuntu
- [20170406]關於檔案頭轉儲.txt
- 【BBED】使用bbed修改數字型別資料型別
- 【BBED】使用bbed修改字元型別資料字元型別
- [20180604]在記憶體修改資料(bbed).txt記憶體
- [20140624]bbed修改資料記錄(不等長).txt
- 【BBED】使用bbed 修改日期型別的資料型別
- [20191009]檔案頭fuzzy.txt
- [20210429]檔案頭塊不會快取.txt快取
- wav檔案的檔案頭
- php如何上傳txt檔案，並且讀取txt檔案PHP
- PCL——txt檔案轉到PCD檔案
- [20150529]使用bbed解決丟失的歸檔.txt
- BBED 修改oracle 資料檔案的 SCN 號來做資料庫不完全恢復。Oracle資料庫
- sed 修改檔案
- 8.13 標頭檔案剖析：標頭檔案路徑（下）
- locate標頭檔案和庫檔案
- Mac電腦hosts檔案如何修改？macOS修改Hosts檔案教程Mac
- Oracle 匯出txt檔案Oracle
- SVN培訓筆記（下拉專案、同步修改、新增檔案、修改檔案、刪除檔案、改名檔案等）筆記
- PHP檔案頭BOM頭問題PHP