欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

[20210225]控制文件序列号满的恢复.txt

程序员文章站 2022-03-05 17:00:54
[20210225]控制文件序列号满的恢复.txt--//继续昨天的测试,今天主要是测试恢复.--//我想给自己增加一点点难度,就是使用noresetlogs打开,因为这样重建的控制文件要读取redo,数据文件重新--//回填一些信息,实际上resetlogs也类似,但是noresetlogs回填的 ......

[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

[20210225]控制文件序列号满的恢复.txt