[20210225]控制檔案序列號滿的恢復.txt
[20210225]控制檔案序列號滿的恢復.txt
--//繼續昨天的測試,今天主要是測試恢復.
--//我想給自己增加一點點難度,就是使用noresetlogs開啟,因為這樣重建的控制檔案要讀取redo,資料檔案重新
--//回填一些資訊,實際上resetlogs也類似,但是noresetlogs回填的控制檔案seq很大,一樣打不開資料庫.
--//也就是必須提到我前面要修改的資料檔案以及redo檔案的幾個偏移位置.
1.環境:
SCOTT@book> @ 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
--//首先做一個冷備份便於重複測試:
$ cp /mnt/ramdisk/book/*.* /u01/tmp/
*/
2.首先注意要修改的位置:
$ seq 6 | xargs -IQ echo "p dba Q,1 kcvfh.kcvfhhdr.kccfhcsq" | rlbbed| grep kccfhcsq
BBED> ub4 kccfhcsq @40 0xffffffff
BBED> ub4 kccfhcsq @40 0xffffffff
BBED> ub4 kccfhcsq @40 0xffffffff
BBED> ub4 kccfhcsq @40 0xffffffff
BBED> ub4 kccfhcsq @40 0xffffffff
BBED> ub4 kccfhcsq @40 0xffffffff
--//資料檔案塊1的偏移40的位置.
$ seq 501 503 | xargs -IQ echo "dump /v dba Q,1 offset 36 count 8" | rlbbed | grep 00900100
f8ffffff 00900100 l ........
fcffffff 00900100 l ........
fcffffff 00900100 l ........
--//redo檔案的位置.
$ echo 15,17,281,319 | tr ',' '\n' | xargs -IQ echo -e 'set dba 101,Q\ndump /v offset 8 count 8' | rlbbed | grep -C2 "\-\-"
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 15 Offsets: 8 to 15 Dba:0x1940000f
-----------------------------------------------------------------------------------------------------------
ffffffff ffff0104 l ........
--
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 17 Offsets: 8 to 15 Dba:0x19400011
-----------------------------------------------------------------------------------------------------------
ffffffff ffff0104 l ........
--
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 281 Offsets: 8 to 15 Dba:0x19400119
-----------------------------------------------------------------------------------------------------------
85ffffff ffff0104 l ........
--
BBED> File: /mnt/ramdisk/book/control01.ctl (101)
Block: 319 Offsets: 8 to 15 Dba:0x1940013f
-----------------------------------------------------------------------------------------------------------
83ffffff ffff0104 l ........
--//控制檔案的位置,以上內容是昨天的分析.控制檔案的修改比較麻煩我重建使用noresetlogs建立,主要這樣修改我不確定這樣操作是否
--//會遺漏.
3.修改指令碼準備:
$ seq 6 | xargs -IQ echo -e "assign dba Q,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000\nsum apply dba Q,1"
assign dba 1,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 1,1
assign dba 2,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 2,1
assign dba 3,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 3,1
assign dba 4,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 4,1
assign dba 5,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 5,1
assign dba 6,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 6,1
--//手工執行如上命令.如果不想手工執行,必須在第2行加上Y,這樣可以透過管道執行.
$ cat aa.txt
assign dba 1,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
Y
sum apply dba 1,1
assign dba 2,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 2,1
assign dba 3,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 3,1
assign dba 4,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 4,1
assign dba 5,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 5,1
assign dba 6,1 kcvfh.kcvfhhdr.kccfhcsq=0x77770000
sum apply dba 6,1
--//修改redo檔案:
$ seq 501 503 | xargs -IQ echo -e "modify /x 77770000 dba Q,1 offset 36\nsum apply dba Q,1"
modify /x 77770000 dba 501,1 offset 36
sum apply dba 501,1
modify /x 77770000 dba 502,1 offset 36
sum apply dba 502,1
modify /x 77770000 dba 503,1 offset 36
sum apply dba 503,1
--//我按照輸出修改如下:
$ cat bb.txt
modify /x 17770000 dba 501,1 offset 36
Y
sum apply dba 501,1
modify /x 57770000 dba 502,1 offset 36
sum apply dba 502,1
modify /x 37777000 dba 503,1 offset 36
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sum apply dba 503,1
--//注:下劃線的地方我修改錯誤.
4.建立建立控制檔案指令碼:
--//這個過程略,在mount階段(不能使用當前的控制檔案,可以使用備份的控制檔案),執行alter database backup controlfile to trace.
--//也可以手工建立:
$ cat /tmp/aa.txt
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "BOOK" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/mnt/ramdisk/book/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/mnt/ramdisk/book/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/mnt/ramdisk/book/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 4 '/mnt/ramdisk/book/redostb01.log' SIZE 50M BLOCKSIZE 512,
-- GROUP 5 '/mnt/ramdisk/book/redostb02.log' SIZE 50M BLOCKSIZE 512,
-- GROUP 6 '/mnt/ramdisk/book/redostb03.log' SIZE 50M BLOCKSIZE 512,
-- GROUP 7 '/mnt/ramdisk/book/redostb04.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/mnt/ramdisk/book/system01.dbf',
'/mnt/ramdisk/book/sysaux01.dbf',
'/mnt/ramdisk/book/undotbs01.dbf',
'/mnt/ramdisk/book/users01.dbf',
'/mnt/ramdisk/book/example01.dbf',
'/mnt/ramdisk/book/tea01.dbf'
CHARACTER SET ZHS16GBK
;
5.開始恢復:
$ cat aa.txt | rlbbed
$ cat bb.txt | rlbbed
--//輸出略.
--//檢查:
$ seq 6 | xargs -IQ echo "p dba Q,1 kcvfh.kcvfhhdr.kccfhcsq" | rlbbed| grep kccfhcsq
BBED> ub4 kccfhcsq @40 0x77770000
BBED> ub4 kccfhcsq @40 0x77770000
BBED> ub4 kccfhcsq @40 0x77770000
BBED> ub4 kccfhcsq @40 0x77770000
BBED> ub4 kccfhcsq @40 0x77770000
BBED> ub4 kccfhcsq @40 0x77770000
$ seq 501 503 | xargs -IQ echo "dump /v dba Q,1 offset 36 count 8" | rlbbed | grep 00900100
17770000 00900100 l .w......
57770000 00900100 l Ww......
37777000 00900100 l 7wp.....
~~~~~~~~~~
--//這裡輸入錯誤.
--//建立控制檔案.
SYS@book> @ /tmp/aa.txt
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Control file created.
$ dbv file=/mnt/ramdisk/book/control01.ctl blocksize=16384
DBVERIFY: Release 11.2.0.4.0 - Production on Thu Feb 25 09:19:00 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/control01.ctl
DBVERIFY - Verification complete
Total Pages Examined : 614
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 29
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 585
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2004287491 (65535.2004287491)
--//2004287491 = 0x77770003.
--//昏後面的3從哪裡跑出來的,估計哪裡搞錯了.
BBED> dump /v dba 101,1 Offset 40 count 8
File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 47 Dba:0x19400001
-----------------------------------------------------------------------------------------------------------
04007777 66020000 l ..wwf...
<32 bytes per line>
--//昏前面的修改錯誤.大小頭問題搞暈了.bbed的修改應該是0x00007777,所以做這類恢復工作要小心小心在小心.前面的檢查也沒注意.
--//還有就是redo修改指令碼也存在錯誤.
$ cat aa.txt
assign dba 1,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
Y
sum apply dba 1,1
assign dba 2,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
sum apply dba 2,1
assign dba 3,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
sum apply dba 3,1
assign dba 4,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
sum apply dba 4,1
assign dba 5,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
sum apply dba 5,1
assign dba 6,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
sum apply dba 6,1
$ cat aa.txt | rlbbed
$ seq 6 | xargs -IQ echo "p dba Q,1 kcvfh.kcvfhhdr.kccfhcsq" | rlbbed| grep kccfhcsq
BBED> ub4 kccfhcsq @40 0x00007777
BBED> ub4 kccfhcsq @40 0x00007777
BBED> ub4 kccfhcsq @40 0x00007777
BBED> ub4 kccfhcsq @40 0x00007777
BBED> ub4 kccfhcsq @40 0x00007777
BBED> ub4 kccfhcsq @40 0x00007777
--//建立控制檔案的步驟從來.
SYS@book> @ /tmp/aa.txt
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Control file created.
$ dbv file=/mnt/ramdisk/book/control01.ctl blocksize=16384
DBVERIFY: Release 11.2.0.4.0 - Production on Thu Feb 25 09:28:18 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/control01.ctl
DBVERIFY - Verification complete
Total Pages Examined : 614
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 29
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 585
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 7370554 (65535.7370554)
--//7370554 = 0x70773a
BBED> dump /v dba 101,1 Offset 40 count 8
File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 40 to 47 Dba:0x19400001
-----------------------------------------------------------------------------------------------------------
3b777000 66020000 l ;wp.f...
<32 bytes per line>
--//先不管它,.看看是否可以open.
--//昏事後仔細檢查我前面的指令碼寫成如下:
modify /x 37777000 dba 503,1 offset 36
--//多寫了一個7.顛倒就是0x00707737 = 7370551,這樣比較接近了.
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
SYS@book> recover database ;
Media recovery complete.
SYS@book> alter database open ;
Database altered.
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
7370584
--//OK恢復成功.
5.重來:
--//從冷備份恢復.
SYS@book> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ /bin/cp /u01/tmp/* /mnt/ramdisk/book/
*/
$ cat aa.txt
assign dba 1,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
Y
sum apply dba 1,1
assign dba 2,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
sum apply dba 2,1
assign dba 3,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
sum apply dba 3,1
assign dba 4,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
sum apply dba 4,1
assign dba 5,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
sum apply dba 5,1
assign dba 6,1 kcvfh.kcvfhhdr.kccfhcsq=0x00007777
sum apply dba 6,1
$ cat bb.txt
modify /x 17770000 dba 501,1 offset 36
Y
sum apply dba 501,1
modify /x 57770000 dba 502,1 offset 36
sum apply dba 502,1
modify /x 37770000 dba 503,1 offset 36
sum apply dba 503,1
--//其它步驟忽略.
$ dbv file=/mnt/ramdisk/book/control01.ctl blocksize=16384 2>&1 | grep Highest
Highest block SCN : 30586 (65535.30586)
--//30586 = 0x777a,這樣與我的測試接近了.
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
SYS@book> recover database ;
Media recovery complete.
SYS@book> alter database open ;
Database altered.
SYS@book> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
30616
--//30616= 0x7798.
RMAN> list incarnation ;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 BOOK 1337401710 CURRENT 925702 2015-11-24 09:11:12
--//沒有生成新的incarnation
6.收尾:
--//執行如下,裡面的指令碼選擇性執行:
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/archivelog/book/1_1_824297850.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/archivelog/book/1_1_896605872.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ramdisk/book/temp01.dbf' REUSE;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb01.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb02.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb03.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb04.log'
SIZE 50M BLOCKSIZE 512 REUSE;
7.總結:
--//有點亂,但是記錄我整個操作過程.我之所以這樣寫,便於以後回憶總結,也避免一些低階錯誤.
--//建議遇到這類檔案選擇重建控制檔案選擇resetlogs,不建議像我這樣操作,我自不過當作練刀過程.
8.補充說明:
--//前面我提到的rlbbed我建立的bash shell函式,你可以使用別名代替,效果一樣的.
$ export RLWRAP=$(which rlwrap)
$ type rlbbed
rlbbed is a function
rlbbed ()
{
cd /home/oracle/bbed;
$RLWRAP -s 9999 -c -r -i -f /usr/local/share/rlwrap/bbed $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=cmd.par
}
--//關於bbed配置看相關文件.
$ cat cmd.par
set count 64
set width 160
$ cat bbed.par
blocksize=8192
listfile=$HOME/bbed/filelist.txt
mode=edit
PASSWORD=blockedit
SPOOL=Y
--//filelist.txt檔案透過select file#||' '||name c100 from v$dbfile order by file#;生成.我增加了控制檔案以及redo檔案.
--//還有臨時檔案.
$ cat filelist.txt | grep -v "#"
4 /mnt/ramdisk/book/users01.dbf
1 /mnt/ramdisk/book/system01.dbf
2 /mnt/ramdisk/book/sysaux01.dbf
3 /mnt/ramdisk/book/undotbs01.dbf
5 /mnt/ramdisk/book/example01.dbf
6 /mnt/ramdisk/book/tea01.dbf
101 /mnt/ramdisk/book/control01.ctl
102 /mnt/ramdisk/book/control02.ctl
201 /mnt/ramdisk/book/temp01.dbf
501 /mnt/ramdisk/book/redo01.log
502 /mnt/ramdisk/book/redo02.log
503 /mnt/ramdisk/book/redo03.log
504 /mnt/ramdisk/book/redostb01.log
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2759215/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210224]控制檔案序列號滿的分析.txt
- 控制檔案恢復—從trace檔案中恢復
- rman恢復--丟失控制檔案的恢復
- RMAN恢復控制檔案
- 手工恢復控制檔案
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 控制檔案恢復—從快照中恢復
- 恢復丟失的控制檔案
- 控制檔案的恢復方法（一）
- 控制檔案的恢復方法（二）
- 控制檔案的恢復方法（三）
- 控制檔案的恢復方法（四）
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- 使用舊的控制檔案備份來恢復控制檔案
- cp方式恢復控制檔案
- 控制檔案恢復測試
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 使用rman恢復控制檔案
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）
- RMAN備份恢復之控制檔案的恢復（一）
- 與控制檔案有關的恢復
- RMAN - "丟失控制檔案的恢復"
- 利用備份的控制檔案恢復
- 控制檔案全部丟失的恢復
- 控制檔案部分丟失的恢復
- 控制檔案的備份和恢復
- 重建控制檔案的恢復(noresetlogs)
- 損壞控制檔案的恢復方法
- 控制檔案丟失的RMAN恢復
- 備份與恢復--利用備份的控制檔案恢復
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- 控制檔案丟失恢復(二)
- 控制檔案全部丟失恢復
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？