1.controlfile 相关错误有哪些？
+++++++controlfile丢失
数据库无法mount，可以将一个控制文件复制过去
dd if=/dev/zero of=/home/oracle/app/oradata/oracleogg/aa bs=16384 count=10
dd if=aa of=/home/oracle/app/oradata/oracleogg/control01.ctl seek=440 bs=16384 count=10 conv=notrunc
数据库可以正常的mount
+++++++++++++如果是controlfile存在信息的个别block损坏呢
[oracle@oracle-ogg oracleogg]$ strings bb|more
siyang
[oracle@oracle-ogg oracleogg]$ dd if=bb of=control01.ctl seek=20 bs=16384 count=10 conv=notrunc
10+0 records in
10+0 records out
163840 bytes (164 kB) copied, 0.000211009 s, 776 MB/s
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 499125816 bytes
Database Buffers 281018368 bytes
Redo Buffers 2596864 bytes
SQL> alter database mount;
Database altered.
查看alert日志信息
alter database mount
Read from controlfile member '/home/oracle/app/oradata/oracleogg/control01.ctl' has found a corrupted block (blk# 24, cf seq# 5926)
Hex dump of (file 0, block 24) in trace file /home/oracle/app/diag/rdbms/oracleogg/oracleogg/trace/oracleogg_ora_7995.trc
Corrupt block relative dba: 0x00000018 (file 0, block 24)
Completely zero block found during control file block read
+++++++++++controlfile header损坏
1)损坏文件头文件
[oracle@oracle-ogg oracleogg]$ cp control02.ctl control01.ctl
[oracle@oracle-ogg oracleogg]$ echo "siyang">bb
[oracle@oracle-ogg oracleogg]$ strings bb|more
siyang
[oracle@oracle-ogg oracleogg]$ dd if=bb of=control01.ctl seek=1 bs=16384 count=1 conv=notrunc
0+1 records in
0+1 records out
7 bytes (7 B) copied, 4.656e-05 s, 150 kB/s
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 499125816 bytes
Database Buffers 281018368 bytes
Redo Buffers 2596864 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00227: corrupt block detected in control file: (block 0, # blocks )
针对这样的情况，恢复也很简单，如果其中一个controlfile header损坏，那么可以用dd其它文件copy覆盖整个损坏的controlfile,均可，如下:
[oracle@oracle-ogg oracleogg]$ echo "siyang">bb
[oracle@oracle-ogg oracleogg]$ strings bb|more
siyang
[oracle@oracle-ogg oracleogg]$ dd if=bb of=control01.ctl seek=1 bs=16384 count=1 conv=notrunc
0+1 records in
0+1 records out
7 bytes (7 B) copied, 4.656e-05 s, 150 kB/s
[oracle@oracle-ogg oracleogg]$ dd if=control02.ctl of=control01.ctl bs=16384 count=2 conv=notrunc
2+0 records in
2+0 records out
32768 bytes (33 kB) copied, 5.3724e-05 s, 610 MB/s
SQL> alter database mount;
Database altered.
uccessful mount of redo thread 1, with mount id 2298093392
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
2）如何恢复控制文件
restore controlfile from autobackup;
或者resotre controlfile from 'xx'
或者利用controlfile快照
restore controlfile to '' from '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_oracleogg.f'
在这个时间点，我们可以通过重建controlfile来避免使用resetlogs打开数据库，如下：
Database altered.
SQL> alter database backup controlfile to trace; ---通过之前的老的备份集恢复控制文件，启动到mount状态下，转储
Database altered.
SQL> oradebug tracefile_name
/home/oracle/app/diag/rdbms/oracleogg/oracleogg/trace/oracleogg_ora_11009.trc
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2252824 bytes
Variable Size 188747752 bytes
Database Buffers 117440512 bytes
Redo Buffers 4718592 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/orcl/system01.dbf',
14 '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
15 '/u01/app/oracle/oradata/orcl/users01.dbf',
16 '/u01/app/oracle/oradata/orcl/tbs_unvdata01.dbf',
17 '/u01/app/oracle/oradata/orcl/undotbs02.dbf'
18 CHARACTER SET ZHS16GBK
19 ;
Control file created.
SQL> alter database open;
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' reuse;
Tablespace altered.
select * from v$nls_parameters; ---查看字符集
我们知道数据库字符集存在一个字典表prop$中，我们可以从该表获得
SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file# from props$;
FILE#
----------
1
SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk# from props$;
BLK#
----------
801
[oracle@localhost trace]$ cd /u01/app/oracle/oradata/orcl/
[oracle@localhost orcl]$ dd if=system01.dbf of=siyang bs=8192 skip=801 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000147999 s, 55.4 MB/s
[oracle@localhost orcl]$ strings siyang
++++++++++++++++++controlfile header block++++++++++++++
BBED> d /v count 365
File: /home/oracle/app/oradata/oracleogg/control01.ctl (1)
Block: 1 Offsets: 0 to 364 Dba:0x00400001
-------------------------------------------------------
15c20000 01000000 00000000 00000104 l ..............
adb20000 00000000 0004200b cc64b188 l ?........ .蘢
4f524143 4c454f47 97190000 66020000 l ORACLEOG....f...
00400000 00000100 00000000 00000000 l .@..............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
33b5fb88 74931635 55a21c00 00000000 l 3蝶.t..5U.....
e5c61735 00000000 00000000 00000000 l 迤.5............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 08000000 08000000 l ................
08000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00 l .............
<16 bytes per line>
offset 从0开始，到361结束。这部分内容是controlfile header内容，如下进行详细描述:
offset 25~28 0004200b 表示Compatibility Vsn，即版本号
offset 29~32 cc64b188 表示DB id，转换后为2293327052
33~37 4f524143 4c 表示 db name. oracleogg
41~42 9719 表示control seq number
45~46 6602 表示file size即是controlfile大小，转换后为614
49~50 0040 表示blocksize
55 01 表示file type ,01表示controlfile，关于该值得属性如下描述:
KCCTYPCF 1 /*control file*/
KCCTYPRL 2 /*redo log file*/
KCCTYPDF 3 /* Vanilla Db file */
KCCTYPBC 4 /* Backup Contolfile*/
KCCTYPBP 5 /*Backup Piece*/
KCCTYPTF 6 /* Temporary db file*/
我们来先看下database entry 信息（对应controlfile dump的database entry信息):
BBED> set file 6 block 17
FILE# 6
BLOCK# 17
BBED> d /v count 200
File: /u01/app/oracle/oradata/orcl/control01.ctl (6)
Block: 17 Offsets: 0 to 199 Dba:0x01800011
-------------------------------------------------------
15c20000 11000000 f74b0000 ffff0104 l ........
6e3c0000 00000000 00000000 5a26053b l n<..........Z&.;
4f52434c 00000000 00000000 08024000 l ORCL..........@.
01404000 00000000 00000000 06200e00 l .@@.......... ..
00000000 689dd13a 01000000 00000000 l ....h.......
7ac92131 00000000 0004200b 05000000 l z..... .....
05000000 01000000 dac65700 00000000 l ............
01000100 01000100 02000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 l ........
<16 bytes per line>
针对上面关键的offset进行描述:
offset 29~32 5a26053b 表示controlfile create timestamp
offset 33~37 4f52434c 00 这部分表示db_name
offset 49~52 01404000 表示database flags
offset 61~64 06200e00 表示resetlogs scn值
offset 69~72 689dd13a 表示resetlogs timestamp
offset 73~76 01000000 表示prior resetlogs scn
offset 81~84 7ac92131 prior resetlogs timestamp
offset 89~92 0004200b redo version compatible --11.2.0.4
offset 93~96 05000000 datafile的数量
offset 97~100 05000000 datafile online的数量
offset 100~104 01000000 表示thread number 为1表示只有一个线程，即单实例。
offset 105~108 dac65700 database checkpoint scn
offset 113~116 01000100 处于enable 状态的thread编号。
这部分我们关注的地方也就是resetlogs scn和time 以及 database checkpoint scn.
下面继续dump,来看下controlfile 中另外一个重要的地方:checkpoint progess records
BBED> set file 6 block 3
FILE# 6
BLOCK# 3
BBED> d /v count 200
File: /u01/app/oracle/oradata/orcl/control01.ctl (6)
Block: 3 Offsets: 0 to 199 Dba:0x01800003
-------------------------------------------------------
15c20000 03000000 00000000 00000104 l ............
a4a70000 02000000 00000000 2a000000 l ........*...
d6000000 2af40000 00000000 d6000000 l *... ecf40000 00000000 d9fa5700 00000000 l ........
702f053b 689dd13a 06200e00 00000000 l p/.;h.......
64a2053b dade7a5a 00000000 00000000 l d.......
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 d6000000 l ............ 01000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 l ........
阅读(2119) | 评论(0) | 转发(0) |
