3 深入解析controlfile

阅读原文时间：2023年07月12日阅读：3

3 深入解析controlfile

Control file: dump

SQL> alter session set events 'immediate trace name controlf level 2';

Session altered
SQL> select * from v$diag_info;

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16425.trc

分析控制文件的1号块

bbed

BBED> set filename '/u01/app/oracle/oradata/orcl/control01.ctl';
FILENAME /u01/app/oracle/oradata/orcl/control01.ctl

BBED> dump
File: /u01/app/oracle/oradata/orcl/control01.ctl (0)

Block: 1 Offsets: 0 to 511 Dba:0x00000000

15c20000 01000000 00000000 00000104 96b20000 00000000 0004200b cf766f5b
4f52434c 00000000 e50f0000 66020000 00400000 00000100 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
322f835b cfbd033c f8281400 00000000 05f1103c 00000000 00000000 00000000

dumpfile

[root@DSI ~]# more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16425.trc

DUMP OF CONTROL FILES, Seq # 4067 = 0xfe3
V10 STYLE FILE HEADER:
Compatibility Vsn = 186647552=0xb200400 Db ID=1534031567=0x5b6f76cf, Db Name='ORCL'
Activation ID=0=0x0 Control Seq=4067=0xfe3, File size=614=0x266
File Number=0, Blksiz=16384, File Type=1 CONTROL

Offset 0~0 15 表示mask
Offset 1~1 c2 表示blocksize(16384)
Offset 4~4 01 表示No. 1 block
Offset 24~27 0004200b 表示Version
Offset 28~31 cf766f5b 表示DBID
Offset 32~39 4f52434c 00000000 表示DBNAME
Offset 40~41 e50f 表示sequence
Offset 44~45 6602 表示file size (block Number)
Offset 49~50 0040 表示blocksize 16834
Offset 53~54 0000 表示fileNo
Offset 55~56 0100 表示fileType

控制文件3号块

BBED> set filename '/u01/app/oracle/oradata/orcl/control01.ctl' block 3;
FILENAME /u01/app/oracle/oradata/orcl/control01.ctl
BLOCK# 3

BBED> dump
File: /u01/app/oracle/oradata/orcl/control01.ctl (0)

Block: 3 Offsets: 0 to 511 Dba:0x00000000

15c20000 03000000 00000000 00000104 7d2c0000 02000000 00000000 03000000
6a000000 9d260000 00000000 6a000000 a8260000 00000000 03291400 00000000
20f1103c 4f2bfa3b 01000000 00000000 18430e3c 322f835b 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 6a000000
01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

Offset 0~0 15 表示mask
Offset 1~1 c2 表示blocksize(16384)
Offset 4~4 03 表示No. 3 block
Offset 20~20 02 表示thread status ，1表示线程关闭，2表标线程处于open状态
Offset 32~41 6a000000 9d260000 0000 表示low cache rba:(0x6a.2650.0)
Offset 42~51 0000 6a000000 a8260000 表示on disk rba:(0x6a.266c.0)
Offset 56~59 03291400 表示表示on disk SCN
Offset 72~75 01000000 表示resetlogs scn: 0x0000.00000001

控制文件包含的信息

*** 2019-05-07 11:43:04.818
DUMP OF CONTROL FILES, Seq # 3472 = 0xd90
V10 STYLE FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=1534031567=0x5b6f76cf, Db Name='ORCL'
Activation ID=0=0x0
Control Seq=3472=0xd90, File size=614=0x266
File Number=0, Blksiz=16384, File Type=1 CONTROL

DATABASE ENTRY
CHECKPOINT PROGRESS RECORDS
EXTENDED DATABASE ENTRY
REDO THREAD RECORDS
LOG FILE RECORDS
DATA FILE RECORDS
TEMP FILE RECORDS
TABLESPACE RECORDS
RMAN CONFIGURATION RECORDS
FLASHBACK LOGFILE RECORDS
THREAD INSTANCE MAPPING RECORDS
MTTR RECORDS
STANDBY DATABASE MAP RECORDS
RESTORE POINT RECORDS
ACM SERVICE RECORDS
LOG FILE HISTORY RECORDS
OFFLINE RANGE RECORDS
ARCHIVED LOG RECORDS
FOREIGN ARCHIVED LOG RECORDS
BACKUP SET RECORDS
BACKUP PIECE RECORDS
BACKUP DATAFILE RECORDS
BACKUP LOG RECORDS
DATAFILE COPY RECORDS
BACKUP DATAFILE CORRUPTION RECORDS
DATAFILE COPY CORRUPTION RECORDS
DELETION RECORDS
PROXY COPY RECORDS
INCARNATION RECORDS
RMAN STATUS RECORDS
DATAFILE HISTORY RECORDS
NORMAL RESTORE POINT RECORDS
DATABASE BLOCK CORRUPTION RECORDS

#控制文件中有信息些是能覆盖的，有些不能覆盖

Control File : Temp File Records
***************************************************************************
TEMP FILE RECORDS
***************************************************************************
(size = 56, compat size = 56, section max = 100, section in-use = 1,
last-recid= 1, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 90, numrecs = 100)
TEMP FILE #1: External File #201
name #9: /u01/app/oracle/oradata/orcl/temp01.dbf
creation size=7552 block size=8192 status=0x1e head=9 tail=9 dup=1
tablespace 3, index=6 krfil=1 prev_file=0

unrecoverable scn: 0x0000.0011299e 04/29/2019 16:33:33

The status field is different from that for permanent files :
0x01 – Created in primary database
0x02 – File is Online
0x04 – Reading is Enabled

0x08 – Changing is Enabled

Control File : Tablespace Records
***************************************************************************
TABLESPACE RECORDS
***************************************************************************
(size = 68, compat size = 68, section max = 100, section in-use = 6,
last-recid= 1, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 89, numrecs = 100)
TABLESPACE #0 SYSTEM: recno=1
First datafile link=1 Tablespace Flag=0
Tablespace PITR mode start scn: 0x0000.00000000 01/01/1988 00:00:00

Tablespace PITR last completion scn: 0x0000.00000000 01/01/1988 00:00:00

select * from V$TABLESPACE;
select * from v$dbfile;
The first data file link is a pointer to the number of the first data file defined for this tablespace. For
temporary tablespace, this points only to temp files.
The TSPITR records are used to record when tablespace point-in-time recovery operations were
performed on the tablespace.
Starting in 8i, there is a status flag for the tablespace records.

0x01 – Temporary tablespace contains only temp files

Control File : RMAN configuration Records
RMAN CONFIGURATION RECORDS
***************************************************************************
(size = 1108, compat size = 1108, section max = 50, section in-use = 3,
last-recid= 3, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 91, numrecs = 50)
RETENTION POLICY:TO RECOVERY WINDOW OF 7 DAYS recno=1
CONTROLFILE AUTOBACKUP:ON recno=2

BACKUP OPTIMIZATION:ON recno=3

Low scn= Low SCN of the flashback log
High SCN = Last SCN of the flashback log.

Redo in this log will have SCN > low SCN

Control File : Offline Range Records
***************************************************************************
OFFLINE RANGE RECORDS
***************************************************************************
(size = 200, compat size = 200, section max = 163, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 96, numrecs = 163)

Records are created only when a file is taken offline and then brought back online. This
information can be viewed through V$OFFLINE_RANGE
An offline range is created for a datafile when its tablespace is first altered to be OFFLINE
NORMAL or READ ONLY, and then subsequently altered to be ONLINE or read/write.
Note that no offline range is created if the datafile itself is altered to be OFFLINE or if the

tablespace is altered to be OFFLINE IMMEDIATE.

Control File : Database Block Corruption Records
***************************************************************************
DATABASE BLOCK CORRUPTION RECORDS
***************************************************************************
(size = 80, compat size = 80, section max = 8384, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 228, numrecs = 8384)

Corruption type
KCCBLKCOR_DEL 1 /* record marked as deleted */
KCCBLKCOR_ZER 2 /* zero'ed block range */
KCCBLKCOR_FRA 3 /* fractured block range */
KCCBLKCOR_CHK 4 /* invalid checksum range */
KCCBLKCOR_CRPT 5 /* other media corruption */
KCCBLKCOR_NOLOGGING 6 /* nologging corruption */

KCCBLKCOR_LOGICAL 7 /* logical corruption */

问题：

1 数据文件5号文件头offset=1的a2代表什么意思？如何把5号文件的文件头offset=1的值a2变为c2

2 Oracle实例恢复从low cache rba开始恢复，至少恢复到on disk rba请用实验来证明？

3 误操作rm -rf control0*.ctl删除全部控制文件，通过文件描述符对控制文件进行恢复。

1.数据文件5号文件头offset=1的a2代表什么意思？如何把5号文件的文件头offset=1的值a2变为c2(写出详细操作步骤，切不能用BBED修改)
SQL> select * from v$dbfile where file#=5;

 FILE# NAME

 5 /u01/app/oracle/oradata/dsidb/ts.dbf

BBED> set file 5 block 1
FILE# 5
BLOCK# 1

BBED> dump
File: /u01/app/oracle/oradata/dsidb/ts16k.dbf (5)

Block: 1 Offsets: 0 to 199 Dba:0x01400001

0ba20000 01004001 00000000 00000104 56740000 00000000 0004200b 4edbac96
##

Offset 1~1 a2 表示blocksize(8192)，数据文件块大小8k
备注： offset 1 该位置代表数据文件 blocksize，其中：
82=4096
A2=8192
C2=16384
E2=32768

BBED> set file 5 block 1
FILE# 5
BLOCK# 1

BBED> dump
File: /u01/app/oracle/oradata/orcl/test01.dbf (5)

Block: 1 Offsets: 0 to 511 Dba:0x01400001

0ba20000 01004001 00000000 00000104 2c170000 00000000 0004200b cf766f5b
4f52434c 00000000 9c0f0000 00190000 00200000 05000300 00000000 00000000

创建数据文件并设置blocksize为16k
默认在8k的blocksize下的表空间脚本
CREATE TABLESPACE TEST DATAFILE
'/u01/app/oracle/oradata/orcl/test01.dbf' SIZE 50M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
看是否能直接创建16k的数据文件
SQL> create tablespace test02 datafile 'test02.dbf' size 10m blocksize 16k;
create tablespace test02 datafile 'test02.dbf' size 10m blocksize 16k
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
不能直接创建，需要修改blocksize，由8k变为16k
SQL> show parameter db_16k

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0

2.Oracle实例恢复从low cache rba开始恢复，至少恢复到on disk rba请用实验来证明？
？？

RBA:它由 3 部分组成， 4byte+4byte+2byte 分别为 logfile sequence number ， logfileblock number， byte offsetinto the block ，
即 redo 序列号， redo block 号，以及偏移量。
并且全部使用 16 进制。
low cache RBA：即 checkpoint RBA ，决定线程从哪里开始恢复
on disk rba：是记录在磁盘上最高的 RBA 值（redo block address）。在恢复过程中，所有redo 指针指向该 RBA 的必须应用

--1 进行dml操作，commit，然后shutdown abort数据库
--2 startup mount;，alter session set events 'immediate trace name controlf level 2';
--3 alter database open;，查看trace log中的redo thread 的low cache rba和on disk rba

CREATE USER test
IDENTIFIED BY "test01"
DEFAULT TABLESPACE TEST
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
SQL> conn sys / as sysdba
SQL> grant RESOURCE,CONNECT,DBA to test;
SQL> conn test/test01
SQL> alter system switch logfile;
SQL> /
create table test_rba(id int);
insert into test_rba values(1);
insert into test_rba values(2);
SQL> commit;
SQL> conn sys / as sysdba
shutdown abort
SQL> startup mount;
ORACLE instance started.

Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 511708728 bytes
Database Buffers 264241152 bytes
Redo Buffers 6791168 bytes
Database mounted.
QL> oradebug SETMYPID
Statement processed.
SQL> oradebug dump controlf 4;
Statement processed.
SQL> select * from v$diag_info;

INST_ID NAME VALUE
---------- -------------------------- -------------------------------------------------------------
1 Default Trace File /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2760.trc
[oracle@DSI ~]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2760.trc
Processing Oradebug command 'SETMYPID'

*** 2019-05-13 17:22:53.203
Oradebug command 'SETMYPID' console output:

*** 2019-05-13 17:23:00.540
Processing Oradebug command 'dump controlf 4'
DUMP OF CONTROL FILES, Seq # 5130 = 0x140a
V10 STYLE FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=1534031567=0x5b6f76cf, Db Name='ORCL'
Activation ID=0=0x0
Control Seq=5130=0x140a, File size=614=0x266
File Number=0, Blksiz=16384, File Type=1 CONTROL

***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
(size = 8180, compat size = 8180, section max = 11, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x2 flags:0x0 dirty:59
low cache rba:(0x70.4.0) on disk rba:(0x71.41.0)
on disk scn: 0x0000.00164878 05/13/2019 17:21:26
resetlogs scn: 0x0000.00000001 04/22/2019 10:07:11
heartbeat: 1008187486 mount id: 1535928526
THREAD #2 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
###low cache rba:(0x70.4.0)
seq#=0x70=112
blk#=4
ofs#=0
###on disk rba:(0x71.41.0) on disk scn: 0x0000.00164878 05/13/2019 17:21:26
seq#=0x71=113
blk#=41
ofs#=0
(system@127.0.0.1:3306) [(none)]> select CAST(0x70 AS UNSIGNED) as low_cache_rba_seq,CAST(0x71 AS UNSIGNED) as on_disk_rba_seq;
+-------------------+-----------------+
| low_cache_rba_seq | on_disk_rba_seq |
+-------------------+-----------------+
| 112 | 113 |
+-------------------+-----------------+
打开数据库
SQL> alter database open;
查看
[oracle@DSI ~]$ tail -n 200 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Started redo scan
Completed redo scan
read 34 KB redo, 59 data blocks need recovery
Started redo application at
Thread 1: logseq 112, block 4
Recovery of Online Redo Log: Thread 1 Group 1 Seq 112 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 113 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Completed redo application of 0.02MB
Completed crash recovery at
Thread 1: logseq 113, block 65, scn 1480344
59 data blocks read, 59 data blocks written, 34 redo k-bytes read
Mon May 13 17:28:25 2019
LGWR: STARTING ARCH PROCESSES
Mon May 13 17:28:25 2019
备注：验证恢复起始点为 logseq 112 block 4 (low cache rba)
恢复结束点为：logseq 113, block 65, scn 1480344 (on disk rba)

3.误操作rm -rf control0*.ctl删除全部控制文件，通过文件描述符对控制文件进行恢复。
##数据库状态正常
SQL> select open_mode from v$database;

OPEN_MODE

READ WRITE
SQL> select name from v$controlfile; ##控制文件位置

NAME

/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/control02.ctl
##删除控制文件
[root@DSI orcl]# ll control0*
-rw-r----- 1 oracle oinstall 10076160 May 8 16:54 control01.ctl
-rw-r----- 1 oracle oinstall 10076160 May 8 16:54 control02.ctl
[root@DSI orcl]# rm -rf control0*
由于ckpt进程会打开控制文件，所有可以通过进程ckpt的文件句柄来恢复控制文件
[root@DSI orcl]# ps -ef|grep ckpt
oracle 1960 1 0 May06 ? 00:00:20 ora_ckpt_orcl
root 17739 2045 0 16:56 pts/1 00:00:00 grep ckpt
[root@DSI orcl]# ll -l /proc/1960/fd
total 0
lr-x------ 1 oracle oinstall 64 May 8 16:56 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 May 8 16:56 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 May 8 16:56 10 -> /u01/app/oracle/product/11.2.0/db_1/dbs/lkORCL
lr-x------ 1 oracle oinstall 64 May 8 16:56 11 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 May 8 16:56 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 May 8 16:56 256 -> /u01/app/oracle/oradata/orcl/control01.ctl (deleted)
lrwx------ 1 oracle oinstall 64 May 8 16:56 257 -> /u01/app/oracle/oradata/orcl/control02.ctl (deleted)
lrwx------ 1 oracle oinstall 64 May 8 16:56 258 -> /u01/app/oracle/oradata/orcl/system01.dbf
lrwx------ 1 oracle oinstall 64 May 8 16:56 259 -> /u01/app/oracle/oradata/orcl/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 May 8 16:56 260 -> /u01/app/oracle/oradata/orcl/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 May 8 16:56 261 -> /u01/app/oracle/oradata/orcl/users01.dbf
lrwx------ 1 oracle oinstall 64 May 8 16:56 262 -> /u01/app/oracle/oradata/orcl/test01.dbf
lr-x------ 1 oracle oinstall 64 May 8 16:56 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 May 8 16:56 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 May 8 16:56 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 May 8 16:56 6 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 May 8 16:56 7 -> /proc/1960/fd
lr-x------ 1 oracle oinstall 64 May 8 16:56 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 May 8 16:56 9 -> /u01/app/oracle/product/11.2.0/db_1/dbs/hc_orcl.dat
直接使用操作系统的cp命令，从操作系统级别恢复控制文件
[root@DSI orcl]# cp /proc/1960/fd/256 /u01/app/oracle/oradata/orcl/control01.ctl
[root@DSI orcl]# cp /proc/1960/fd/257 /u01/app/oracle/oradata/orcl/control02.ctl
修改权限，由于这里是root操作的
[root@DSI orcl]# chown oracle:oinstall control0*
[root@DSI orcl]# ll
total 2792184
-rw-r----- 1 oracle oinstall 10076160 May 8 16:57 control01.ctl
-rw-r----- 1 oracle oinstall 10076160 May 8 16:57 control02.ctl
SQL> shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3

SQL> shutdown immediate;
ORA-03113: end-of-file on communication channel
SQL> select open_mode from v$database;
ERROR:
ORA-03114: not connected to ORACLE

SQL> startup;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> shutdown abort;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@DSI ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 8 17:02:35 2019

Connected to an idle instance.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 511708728 bytes
Database Buffers 264241152 bytes
Redo Buffers 6791168 bytes
Database mounted.
Database opened.

手机扫一扫

移动阅读更方便

你可能感兴趣的文章