公司一台数据库测试服务器,某天开始sqlplus登陆缓慢,要卡四五秒才能登陆成功,而且是sqlplus / as sysdba

感觉问题排查无法下手,又不是什么大问题,库也不重要,一直没有去管

今天闲来无事,决定着手解决这个问题,排查此类问题的终极大杀器,祭出strace

[oracle@test8 ~]$ strace -T -t -o /tmp/nohost sqlplus / as sysdba

查看/tmp/nohost内容,此处省略部分内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
10:05:39 open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 9 <0.000086>
10:05:39 fstat(9, {st_mode=S_IFREG|0644, st_size=208, ...}) = 0 <0.000061>
10:05:39 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fb66c9a2000 <0.000063>
10:05:39 read(9, "127.0.0.1 localhost localhost."..., 4096) = 208 <0.000069>
10:05:39 read(9, "", 4096) = 0 <0.000059>
10:05:39 close(9) = 0 <0.000125>
10:05:39 munmap(0x7fb66c9a2000, 4096) = 0 <0.000078>
10:05:39 open("/home/app/oracle/product/11.2.0/dbhome_1/lib/libnss_dns.so.2", O_RDONLY) = -1 ENOENT (No such file or directory) <0.000099>
10:05:39 open("/etc/ld.so.cache", O_RDONLY) = 9 <0.000085>
10:05:39 fstat(9, {st_mode=S_IFREG|0644, st_size=404774, ...}) = 0 <0.000059>
10:05:39 mmap(NULL, 404774, PROT_READ, MAP_PRIVATE, 9, 0) = 0x7fb66c71c000 <0.000069>
10:05:39 close(9) = 0 <0.000058>
10:05:39 open("/lib64/libnss_dns.so.2", O_RDONLY) = 9 <0.000090>
10:05:39 read(9, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0\0\20\0\0\0\0\0\0"..., 832) = 832 <0.000062>
10:05:39 fstat(9, {st_mode=S_IFREG|0755, st_size=27424, ...}) = 0 <0.000060>
10:05:39 mmap(NULL, 2117880, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 9, 0) = 0x7fb66c13b000 <0.000067>
10:05:39 mprotect(0x7fb66c140000, 2093056, PROT_NONE) = 0 <0.000074>
10:05:39 mmap(0x7fb66c33f000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 9, 0x4000) = 0x7fb66c33f000 <0.000071>
10:05:39 close(9) = 0 <0.000058>
10:05:39 open("/home/app/oracle/product/11.2.0/dbhome_1/lib/libresolv.so.2", O_RDONLY) = -1 ENOENT (No such file or directory) <0.000100>
10:05:39 open("/lib64/libresolv.so.2", O_RDONLY) = 9 <0.000089>
10:05:39 read(9, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\00009\300E>\0\0\0"..., 832) = 832 <0.000060>
10:05:39 fstat(9, {st_mode=S_IFREG|0755, st_size=113952, ...}) = 0 <0.000060>
10:05:39 mmap(0x3e45c00000, 2202248, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 9, 0) = 0x3e45c00000 <0.000066>
10:05:39 mprotect(0x3e45c16000, 2097152, PROT_NONE) = 0 <0.000067>
10:05:39 mmap(0x3e45e16000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 9, 0x16000) = 0x3e45e16000 <0.000077>
10:05:39 mmap(0x3e45e18000, 6792, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x3e45e18000 <0.000072>
10:05:39 close(9) = 0 <0.000058>
10:05:39 mprotect(0x3e45e16000, 4096, PROT_READ) = 0 <0.000123>
10:05:39 mprotect(0x7fb66c33f000, 4096, PROT_READ) = 0 <0.000069>
10:05:39 munmap(0x7fb66c71c000, 404774) = 0 <0.000082>
10:05:39 socket(PF_INET, SOCK_DGRAM|SOCK_NONBLOCK, IPPROTO_IP) = 9 <0.000094>
10:05:39 connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("221.228.255.1")}, 16) = 0 <0.000076>
10:05:39 poll([{fd=9, events=POLLOUT}], 1, 0) = 1 ([{fd=9, revents=POLLOUT}]) <0.000061>
10:05:39 sendto(9, "\23\232\1\0\0\1\0\0\0\0\0\0\5test8\0\0\1\0\1", 23, MSG_NOSIGNAL, NULL, 0) = 23 <0.000201>
10:05:39 poll([{fd=9, events=POLLIN|POLLOUT}], 1, 5000) = 1 ([{fd=9, revents=POLLOUT}]) <0.000062>
10:05:39 sendto(9, "\3d\1\0\0\1\0\0\0\0\0\0\5test8\0\0\34\0\1", 23, MSG_NOSIGNAL, NULL, 0) = 23 <0.000098>
10:05:39 poll([{fd=9, events=POLLIN}], 1, 4998) = 1 ([{fd=9, revents=POLLIN}]) <0.006345>
10:05:39 ioctl(9, FIONREAD, [39]) = 0 <0.000063>
10:05:39 recvfrom(9, "\23\232\201\200\0\1\0\1\0\0\0\0\5test8\0\0\1\0\1\300\f\0\1\0\1\0\0\0"..., 2048, 0, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("221.228.255.1")}, [16]) = 39 <0.000096>
10:05:39 poll([{fd=9, events=POLLIN}], 1, 4991) = 0 (Timeout) <4.996143>
10:05:44 poll([{fd=9, events=POLLOUT}], 1, 0) = 1 ([{fd=9, revents=POLLOUT}]) <0.000100>
10:05:44 sendto(9, "\23\232\1\0\0\1\0\0\0\0\0\0\5test8\0\0\1\0\1", 23, MSG_NOSIGNAL, NULL, 0) = 23 <0.000198>
10:05:44 poll([{fd=9, events=POLLIN}], 1, 5000) = 1 ([{fd=9, revents=POLLIN}]) <0.006851>
10:05:44 ioctl(9, FIONREAD, [39]) = 0 <0.000151>
10:05:44 recvfrom(9, "\23\232\201\200\0\1\0\1\0\0\0\0\5test8\0\0\1\0\1\300\f\0\1\0\1\0\0\0"..., 2048, 0, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("221.228.255.1")}, [16]) = 39 <0.000107>
10:05:44 poll([{fd=9, events=POLLOUT}], 1, 4990) = 1 ([{fd=9, revents=POLLOUT}]) <0.000088>
10:05:44 sendto(9, "\3d\1\0\0\1\0\0\0\0\0\0\5test8\0\0\34\0\1", 23, MSG_NOSIGNAL, NULL, 0) = 23 <0.000120>
10:05:44 poll([{fd=9, events=POLLIN}], 1, 4990) = 1 ([{fd=9, revents=POLLIN}]) <0.008139>
10:05:44 ioctl(9, FIONREAD, [98]) = 0 <0.000074>
10:05:44 recvfrom(9, "\3d\201\203\0\1\0\0\0\1\0\0\5test8\0\0\34\0\1\0\0\6\0\1\0\0\35\17"..., 2009, 0, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("221.228.255.1")}, [16]) = 98 <0.000084>
10:05:44 close(9) = 0 <0.000143>
10:05:44 open("/etc/hostid", O_RDONLY) = -1 ENOENT (No such file or directory) <0.000095>
10:05:44 uname({sys="Linux", node="test8", ...}) = 0 <0.000061>
10:05:44 open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 9 <0.000114>
10:05:44 fstat(9, {st_mode=S_IFREG|0644, st_size=208, ...}) = 0 <0.000062>
10:05:44 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fb66c9a2000 <0.000071>
10:05:44 read(9, "127.0.0.1 localhost localhost."..., 4096) = 208 <0.000089>
10:05:44 read(9, "", 4096) = 0 <0.000059>
10:05:44 close(9) = 0 <0.000098>
10:05:44 munmap(0x7fb66c9a2000, 4096) = 0 <0.000090>
10:05:44 socket(PF_INET, SOCK_DGRAM|SOCK_NONBLOCK, IPPROTO_IP) = 9 <0.000092>
10:05:44 connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("221.228.255.1")}, 16) = 0 <0.000072>
10:05:44 poll([{fd=9, events=POLLOUT}], 1, 0) = 1 ([{fd=9, revents=POLLOUT}]) <0.000059>
10:05:44 sendto(9, "\333\360\1\0\0\1\0\0\0\0\0\0\5test8\0\0\1\0\1", 23, MSG_NOSIGNAL, NULL, 0) = 23 <0.000159>
10:05:44 poll([{fd=9, events=POLLIN}], 1, 5000) = 1 ([{fd=9, revents=POLLIN}]) <0.008444>
10:05:44 ioctl(9, FIONREAD, [39]) = 0 <0.000067>
10:05:44 recvfrom(9, "\333\360\201\200\0\1\0\1\0\0\0\0\5test8\0\0\1\0\1\300\f\0\1\0\1\0\0\0"..., 1024, 0, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("221.228.255.1")}, [16]) = 39 <0.000077>
10:05:44 close(9) = 0 <0.000086>
10:05:44 write(10, "\2x\0\0\6\0\0\0\0\0\3s\3\0\0\0\0\0\0\0\0\0\0\0\0!\0\0\0\376\377\377"..., 632) = 632 <0.000124>
10:05:44 read(11, "\6\331\0\0\6\0\0\0\0\0\10&\0\23\0\0\0\23AUTH_VERSION_S"..., 8208) = 1753 <0.013111>
10:05:44 open("/home/app/oracle/product/11.2.0/dbhome_1//rdbms/mesg/oraus.msb", O_RDONLY) = 9 <0.000142>
10:05:44 fcntl(9, F_SETFD, FD_CLOEXEC) = 0 <0.000060>
10:05:44 lseek(9, 0, SEEK_SET) = 0 <0.000062>
10:05:44 read(9, "\25\23\"\1\23\3\t\t\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 256) = 256 <0.000091>
10:05:44 lseek(9, 512, SEEK_SET) = 512 <0.000060>

其中10:05:39 poll([{fd=9, events=POLLIN}], 1, 4991) = 0 (Timeout) <4.996143>是耗时最长的一步
而且卡在此处足足有5s,而且最终超时,根据上下文判断,此处是一个socket连接,而且还涉及到dns服务器的地址,由此推断应该和解析有关

查看/etc/resolv.conf

1
2
3
4
5
[root@test8 etc]# cat /etc/resolv.conf
# Generated by NetworkManager
#nameserver 221.228.255.1
nameserver 221.228.255.1
nameserver 218.2.135.1

可见配置了dns,其实我将dns注释掉之后sqlplus就恢复了正常,但dns不应该成为导致此问题的根本原因,此处明显是去dns上解析本机了,再来看/etc/hosts

1
2
3
4
5
[root@test8 etc]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.8 test8.com
192.168.1.29 oem.oracle.com

在重新查阅了hosts的正确配置之后发现我的hosts配置方法是错误的,以下为正确格式

1
2
3
4
5
格式:
  一般情况下hosts的内容关于主机名(hostname)的定义,每行为一个主机,每行由三部份组成,每个部份由空格隔开。其中#号开头的行做说明,不被系统解释。
  第一部份:网络IP地址;
  第二部份:主机名.域名,注意主机名和域名之间有个半角的点,比如 localhost.localdomain
  第二部份:主机名(主机名别名) ,其实就是主机名;

我的主机名是test8,在我测试邮件的时候,为了骗过邮件服务器,我把hosts里解析随意加了个域名,却从未正式了解过hosts的配置方法,现在正确配置一下

1
2
3
4
5
[root@test8 etc]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.8 test8.com test8
192.168.1.29 oem.oracle.com

再测试sqlplus,已经恢复正常

然而有些问题仍然比较困惑,正解析与反解析何时会进行?为什么会进行?为什么hosts和dns都不存在时候反而没问题,而dns存在hosts配置不正确的时候为什么会出问题?这些疑惑以我现在的能力还无法参破,就连strace的内容也只看个皮毛,期待来日解决吧!

某天凌晨5点左右,被客户方运维人员电话吵醒,客户的交换机因故重启,导致做了RHCS的两台Linux服务器(非oracle服务器)因心跳问题也导致重启

第一时间登上去查看oracle运行情况,发现主库已是mount状态,备库已变为主库,可以肯定是因为心跳问题导致了自动切换(生产上的DG环境配置了Fast-Start Failover)

BROKER连上去查看状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
DGMGRL> SHOW CONFIGURATION VERBOSE;
Configuration - jsconf
Protection Mode: MaxAvailability
Databases:
jsread - Primary database
Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

jsrw - (*) Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated


(*) Fast-Start Failover target


Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'


Fast-Start Failover: ENABLED


Threshold: 30 seconds
Target: jsrw
Observer: CAHW50
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE


Configuration Status:
ERROR

查看主备库的状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
DGMGRL> show database jsrw


Database - jsrw


Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
jsrw


Database Status:
ORA-16661: the standby database needs to be reinstated


DGMGRL> show database jsread


Database - jsread


Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
jsread


Database Error(s):
ORA-16820: fast-start failover observer is no longer observing this database


Database Warning(s):
ORA-16817: unsynchronized fast-start failover configuration


Database Status:
ERROR

看样子原主库已被隔离,查看alert日志

原主库日志:

1
2
3
4
5
6
7
8
9
10
11
12
Tue Dec 15 04:14:45 2015
ORA-16198: LGWR received timedout error from KSR
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16198)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 16198 for archive log file 6 to 'jsread'
ORA-16198: LGWR received timedout error from KSR
LGWR: Error 16198 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'jsread'
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
Primary has heard from neither observer nor target standby within FastStartFailoverThreshold seconds.
It is likely an automatic failover has already occurred. Primary is shutting down.
Errors in file /orasystem_readwrite/oracle/oraWR/diag/rdbms/jsrw/jsrw/trace/jsrw_lgwr_73912.trc:
ORA-16830: primary isolated from fast-start failover partners longer than FastStartFailoverThreshold seconds: shutting down

日志中可以看到,主库失去了备库和observer的链接,已超时30秒,因此主库猜测此时极有可能已经发生了自动故障转移,然后将自己关掉

原备库日志:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
Tue Dec 15 04:14:47 2015
RFS[44]: Assigned to RFS process 41247
RFS[44]: Possible network disconnect with primary database
Tue Dec 15 04:14:47 2015
RFS[40]: Possible network disconnect with primary database
Tue Dec 15 04:14:47 2015
RFS[42]: Possible network disconnect with primary database
Tue Dec 15 04:14:50 2015
Attempting Fast-Start Failover because the threshold of 30 seconds has elapsed.
Tue Dec 15 04:14:50 2015
Data Guard Broker: Beginning failover
Tue Dec 15 04:14:50 2015
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Tue Dec 15 04:14:50 2015
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /orasystem_readonly/oracle/oraread/diag/rdbms/jsread/jsread/trace/jsread_pr00_59244.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Tue Dec 15 04:14:50 2015
ALTER SYSTEM SET service_names='jsread' SCOPE=MEMORY SID='jsread';
Recovery interrupted!
Recovered data files to a consistent state at change 229593026
Tue Dec 15 04:14:51 2015
MRP0: Background Media Recovery process shutdown (jsread)
Managed Standby Recovery Canceled (jsread)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
Attempt to do a Terminal Recovery (jsread)
Media Recovery Start: Managed Standby Recovery (jsread)
started logmerger process
Tue Dec 15 04:14:51 2015
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 64 slaves
Media Recovery Waiting for thread 1 sequence 3176 (in transit)
Killing 1 processes with pids 41252 (all RFS, wait for I/O) in order to disallow current and future RFS connections. Requested by OS process 130750
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Terminal Recovery timestamp is '12/15/2015 04:14:57'
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 3176 redo required
Terminal Recovery:
Recovery of Online Redo Log: Thread 1 Group 9 Seq 3176 Reading mem 0
Mem# 0: /oradata_readonly/jsread/stb_redo02.log
Identified End-Of-Redo (failover) for thread 1 sequence 3176 at SCN 0xffff.ffffffff
Incomplete Recovery applied until change 229593027 time 12/15/2015 04:14:14
Media Recovery Complete (jsread)
Terminal Recovery: successful completion
Forcing ARSCN to IRSCN for TR 0:229593027
Tue Dec 15 04:14:57 2015
ARCH: Archival stopped, error occurred. Will continue retrying
Attempt to set limbo arscn 0:229593027 irscn 0:229593027
ORACLE Instance jsread - Archival ErrorResetting standby activation ID 486853183 (0x1d04ca3f)
ORA-16014: log 9 sequence# 3176 not archived, no available destinations
ORA-00312: online log 9 thread 1: '/oradata_readonly/jsread/stb_redo02.log'
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (jsread)
Maximum wait for role transition is 15 minutes.
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 117884 user 'grid' program 'oracle@jsc_db_r (TNS V1-V3)'
Tue Dec 15 04:15:00 2015
Active process 117884 user 'grid' program 'oracle@jsc_db_r (TNS V1-V3)'
CLOSE: all sessions shutdown successfully.
Tue Dec 15 04:15:01 2015
SMON: disabling cache recovery
Backup controlfile written to trace file /orasystem_readonly/oracle/oraread/diag/rdbms/jsread/jsread/trace/jsread_rsm0_79936.trc
Standby terminal recovery start SCN: 229593026
RESETLOGS after incomplete recovery UNTIL CHANGE 229593027
Online log /oradata_readonly/jsread/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata_readonly/jsread/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata_readonly/jsread/redo03.log: Thread 1 Group 3 was previously cleared
Online log /oradata_readonly/jsread/redo04.log: Thread 1 Group 4 was previously cleared
Online log /oradata_readonly/jsread/redo05.log: Thread 1 Group 5 was previously cleared
Online log /oradata_readonly/jsread/redo06.log: Thread 1 Group 6 was previously cleared
Online log /oradata_readonly/jsread/redo07.log: Thread 1 Group 7 was previously cleared
Online log /oradata_readonly/jsread/redo08.log: Thread 1 Group 16 was previously cleared
Online log /oradata_readonly/jsread/redo09.log: Thread 1 Group 17 was previously cleared
Online log /oradata_readonly/jsread/redo10.log: Thread 1 Group 18 was previously cleared
Standby became primary SCN: 229593025
Tue Dec 15 04:15:09 2015
Setting recovery target incarnation to 5
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
ALTER DATABASE OPEN
Data Guard Broker initializing...
Tue Dec 15 04:15:09 2015
Assigning activation ID 488981393 (0x1d254391)
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Thread 1 advanced to log sequence 2 (thread open)
Tue Dec 15 04:15:09 2015
ARC2: Becoming the 'no SRL' ARCH
Tue Dec 15 04:15:09 2015
ARC3: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /oradata_readonly/jsread/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
ARC2: Becoming the 'no SRL' ARCH
SMON: enabling cache recovery
Archived Log entry 3278 added for thread 1 sequence 1 ID 0x1d254391 dest 1:
Archiver process freed from errors. No longer stopped
Tue Dec 15 04:15:10 2015
PING[ARC0]: Heartbeat failed to connect to standby 'jsrw'. Error is 16058.
[79936] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1846831906 end:1846832126 diff:220 (2 seconds)
Dictionary check beginning
PING[ARC0]: Heartbeat failed to connect to standby 'jsrw'. Error is 16058.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process SMCO
Database Characterset is ZHS16GBK
Tue Dec 15 04:15:10 2015
idle dispatcher 'D000' terminated, pid = (24, 1)
Tue Dec 15 04:15:10 2015
SMCO started with pid=24, OS id=131236
No Resource Manager plan active
Tue Dec 15 04:15:11 2015
Starting background process QMNC
Tue Dec 15 04:15:11 2015
QMNC started with pid=35, OS id=131244
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN

备库确实在失去主库之后开始自动故障转移,并最终转移成功。

这里要说一下,按理交换机恢复之后DG检测到原主库会自动将其转换为备库,但这次显然没有,还记得有两个配置了RHCS的服务器因网络问题重启了,而客户的observer刚好部署在上面,登上去查看甚至没有找到observer的日志,为什么observer没有打日志出来的?

尝试开启observer,重启原主库,寄希望于observer自动将失败的主库转为备库,但是在start observer的时候就遇到了问题

1
2
3
4
5
6
7
8
DGMGRL> start observer
Error: ORA-16647: could not start more than one observer
Failed.
DGMGRL> stop observer
Error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

查看observer日志:

1
2
3
4
5
6
7
8
9
10
Observer started
[W000 12/15 05:30:13.80] Observer started.
Observer stopped
Error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
[W000 12/15 05:32:05.86] Failed to start the Observer.
Error: ORA-16647: could not start more than one observer
[W000 12/15 05:33:40.06] Failed to start the Observer.

既无法停止,也无法启动,猜测应该是服务器重启导致的状态错乱,此时情况变 的复杂,因交换机重启导致网络不通,导致快速故障转移,推测转移工作未完全结束,observer的服务器又被重启,使得DG处在一个错误的状态下,
现在已无法通过observer自动恢复失败的主库,只能通过手动解决,而日志里确认新主库已转移成功,那么恢复失败的主库即可,那么如何恢复呢?有很多选择重建?利用flashback databse?或者利用broker的reinstate?
其实reinstate也是使用的flashback,那么干脆使用简单的reinstate吧

1
2
3
4
5
6
7
8
9
10
11
12
13
DGMGRL> reinstate database jsrw;
Reinstating database "jsrw", please wait...
Operation requires shutdown of instance "jsrw" on database "jsrw"
Shutting down instance "jsrw"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "jsrw" on database "jsrw"
Starting instance "jsrw"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "jsrw" ...
Reinstatement of database "jsrw" succeeded

查看状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
DGMGRL> show configuration verbose;


Configuration - jsconf


Protection Mode: MaxAvailability
Databases:
jsread - Primary database
Error: ORA-16820: fast-start failover observer is no longer observing this database


jsrw - (*) Physical standby database
Error: ORA-16820: fast-start failover observer is no longer observing this database


(*) Fast-Start Failover target


Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'


Fast-Start Failover: ENABLED


Threshold: 30 seconds
Target: jsrw
Observer: CAHW50
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE


Configuration Status:
ERROR

可见已经恢复成功,此时stop observer,start observer就没有问题了,但为了保险起见,停止了observer

1
2
3
4
DGMGRL> stop observer;
Done.
DGMGRL> start observer;
Observer started

这次显然是交换机导致的乌龙failover,根本不需要failover的,本来的高可用措施反而带来了些许麻烦,后来找机会又切换回原来的主库了,自动故障转移也一直没有再启动,届时手动faiover更为灵活,因为客户的应用并不够灵活.

上一篇文章介绍了ASSM三级位图管理下的数据插入和高水位推进的关系,我们得出的结论是随着数据不断的插入,段大小的不断增长,L1中挂载的数据块的数量也会增长,而高水位是以L1中数据块大小的总和与区大小之间最小的一方为单位进行推进,而且我们知道要想应对大并发插入,就要使高水位之下的空闲块的数量尽可能多,但是如此一来就有可能造成空间的浪费,而oracle的初心也是本着节省空间的目的来设计的。
之前我们用的是常规路径插入,能以L1和区的单位推动高水位,前提是要填满L1或区,而我们熟知的直接路径插入是在高水位之上插入,那是不是可以用直接路径插入快速增加高水位呢?我们来一探究竟。

首先构造测试的表空间和表,跟上一篇一样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> drop tablespace lp including contents and datafiles;

Tablespace dropped.

SQL> create tablespace lp datafile '/home/oracle/app/oracle/oradata/DG43/lp.dbf' size 2048M uniform size 1m;
create table lp (id number,des1 char(2000),des2 char(2000),des3 char(2000),des4 char(500)) tablespace lp;

Tablespace created.

SQL>
Table created.

SQL> alter table lp pctfree 24;

Table altered.


SQL> select object_id,object_name from dba_objects where object_name='LP';

OBJECT_ID OBJECT_NAME
---------- ------------------------------
78760 LP

首先看一下,78760这个对象在buffer里的数据块有哪些

1
2
3
4
5
6
7
8
SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 131 N 78760
5 129 N 78760
5 130 N 78760
5 128 N 78760

有没有眼熟呢,128、129、130、131四个块刚好是两个L1、L2、L3
我们定位段头块,看一下此时的高水位

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> select segment_name ,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='LP';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
LP 5 131

Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01400084 ext#: 0 blk#: 4 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0

SQL> select dbms_utility.data_block_address_file(to_number('01400084', 'xxxxxxxx')) file#,
2 dbms_utility.data_block_address_block(to_number('01400084', 'xxxxxxxx')) block#
3 from dual;

FILE# BLOCK#
---------- ----------
5 132

可以看出在没有任何数据的情况下,高水位是在第一个数据块上的,下面常规插入一行数据

1
insert into lp values(1,'a','a','a','a');

看一下高水位和buffer中的块

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 165 Y 78760
5 131 Y 78760
5 173 Y 78760
5 160 Y 78760
5 168 Y 78760
5 163 Y 78760
5 129 N 78760
5 171 Y 78760
5 166 Y 78760
5 174 Y 78760
5 161 Y 78760

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 169 Y 78760
5 164 Y 78760
5 130 N 78760
5 172 Y 78760
5 167 Y 78760
5 175 Y 78760
5 162 Y 78760
5 128 Y 78760
5 170 Y 78760

20 rows selected.

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
5 160
1
2
3
4
5
6
7
8
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x014000c0 ext#: 0 blk#: 64 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 60
mapblk 0x00000000 offset: 0

发现数据插入到160号块中,buffer中却多了16个块,高水位已移动到第二个L1中的第一个块上,看一下第一个L1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
HWM Flag: HWM Set
Highwater:: 0x014000c0 ext#: 0 blk#: 64 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 60
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01400080 Length: 64 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:Metadata
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:FULL 33:75-100% free 34:75-100% free 35:75-100% free
36:75-100% free 37:75-100% free 38:75-100% free 39:75-100% free
40:75-100% free 41:75-100% free 42:75-100% free 43:75-100% free
44:75-100% free 45:75-100% free 46:75-100% free 47:75-100% free
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
1
2
3
4
5
6
7
   SQL> select dbms_utility.data_block_address_file(to_number('014000c0', 'xxxxxxxx')) file#,
2 dbms_utility.data_block_address_block(to_number('014000c0', 'xxxxxxxx')) block#
3 from dual;

FILE# BLOCK#
---------- ----------
5 192

可以发现,buffer中新增的16个块是一次性格式化的这16个块,而高水位指在192号块上,直接路径插入一行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 165 N 78760
5 131 N 78760
5 173 N 78760
5 160 N 78760
5 168 N 78760
5 163 N 78760
5 129 N 78760
5 171 N 78760
5 166 N 78760
5 174 N 78760
5 161 N 78760

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 169 N 78760
5 164 N 78760
5 130 N 78760
5 172 N 78760
5 167 N 78760
5 175 N 78760
5 162 N 78760
5 128 N 78760
5 170 N 78760

20 rows selected.


SQL> insert /*+ append_values(lp)*/ into lp values(2,'b','b','b','b');

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
5 160
5 192

SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 165 N 78760
5 131 Y 78760
5 173 N 78760
5 160 N 78760
5 168 N 78760
5 163 N 78760
5 129 Y 78760
5 171 N 78760
5 192 Y 78760
5 166 N 78760
5 174 N 78760

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 161 N 78760
5 169 N 78760
5 164 N 78760
5 130 N 78760
5 172 N 78760
5 167 N 78760
5 175 N 78760
5 162 N 78760
5 128 Y 78760
5 170 N 78760

21 rows selected.

SQL> alter system checkpoint;

System altered.

可以发现确实插入到了192号块,并且192号块已在buffer中,看一下此时的高水位

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x014000c1 ext#: 0 blk#: 65 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 65
mapblk 0x00000000 offset: 0

HWM Flag: HWM Set
Highwater:: 0x014000c1 ext#: 0 blk#: 65 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 65
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x014000c0 Length: 64 Offset: 0

0:FULL 1:unformatted 2:unformatted 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
--------------------------------------------------------
End dump data blocks tsn: 5 file#: 5 minblk 129 maxblk 129

发现高水位仅仅移动了1个block,再试一遍

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SQL> insert /*+ append_values(lp)*/ into lp values(3,'c','c','c','c');

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
5 160
5 192
5 193

SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 165 N 78760
5 131 Y 78760
5 173 N 78760
5 160 N 78760
5 168 N 78760
5 163 N 78760
5 129 Y 78760
5 171 N 78760
5 192 N 78760
5 166 N 78760
5 174 N 78760

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 161 N 78760
5 169 N 78760
5 164 N 78760
5 130 N 78760
5 172 N 78760
5 193 Y 78760
5 167 N 78760
5 175 N 78760
5 162 N 78760
5 128 N 78760
5 170 N 78760

22 rows selected.
1
2
3
4
5
6
Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 128   
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x014000c2 ext#: 0 blk#: 66 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 66
mapblk 0x00000000 offset: 0

仍然是只推动了一个block,但是发现为何每次直接路径插入的block都会出现在buffer里呢?难道是11g这个append_values新的hint的原因,再来试一下传统的append

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
  SQL> insert /*+ append*/ into lp select *  from lp where trim(des1)='a';

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
5 160
5 192
5 193
5 194

SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 165 N 78760
5 131 Y 78760
5 173 N 78760
5 194 Y 78760
5 160 N 78760
5 168 N 78760
5 163 N 78760
5 129 Y 78760
5 171 N 78760
5 192 N 78760
5 166 N 78760

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 174 N 78760
5 161 N 78760
5 169 N 78760
5 164 N 78760
5 130 N 78760
5 172 N 78760
5 193 N 78760
5 167 N 78760
5 175 N 78760
5 162 N 78760
5 128 N 78760

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 170 N 78760

23 rows selected.

SQL> alter system checkpoint;

System altered.
1
2
3
4
5
6
7
8
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x014000c3 ext#: 0 blk#: 67 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 67
mapblk 0x00000000 offset: 0

依然如故,不知道大家有没有发现,每次我执行完插入,都会执行一条select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp;
这其实是全表扫描,本来块没在buffer里,一个FTS把块全给整进来了(小表buffer读,大表在11g中通常情况是直接路径读),知道原因了,再试一遍

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
SQL> insert /*+ append*/ into lp select *  from lp where trim(des1)='b';

1 row created.

SQL> commit;

Commit complete.

SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 165 N 78760
5 131 Y 78760
5 173 N 78760
5 194 N 78760
5 160 N 78760
5 168 N 78760
5 163 N 78760
5 129 Y 78760
5 171 N 78760
5 192 N 78760
5 166 N 78760

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 174 N 78760
5 161 N 78760
5 169 N 78760
5 164 N 78760
5 130 N 78760
5 172 N 78760
5 193 N 78760
5 167 N 78760
5 175 N 78760
5 162 N 78760
5 128 N 78760

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 170 N 78760

23 rows selected.

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
5 160
5 192
5 193
5 194
5 195

SQL> select file#,BLOCK#,DIRTY,OBJD from v$bh where objd='78760';

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 165 N 78760
5 131 Y 78760
5 173 N 78760
5 194 N 78760
5 160 N 78760
5 168 N 78760
5 163 N 78760
5 129 Y 78760
5 171 N 78760
5 192 N 78760
5 166 N 78760

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 174 N 78760
5 195 Y 78760
5 161 N 78760
5 169 N 78760
5 164 N 78760
5 130 N 78760
5 172 N 78760
5 193 N 78760
5 167 N 78760
5 175 N 78760
5 162 N 78760

FILE# BLOCK# D OBJD
---------- ---------- - ----------
5 128 N 78760
5 170 N 78760

24 rows selected.

终于看到了想要的结果,总结一下吧,直接路径下高水位的推进大概如下图的样子:

不知道大家有没有注意到,直接路径插入完后再读入buffer中居然是个脏块,这是不是跟延迟提交清除有关呢?我们以后再慢慢分析!

#ASSM三级位图结构与高水位的探究

三级位图

Oracle9i在本地表空管理(LMT)的基础上,对段空间管理也引入了位图管理(Segment Space Management Auto)来取代原来的freelist管理方式(Segment Space Management Manual)。
但是默认system和undo表空间仍然是MSSM的管理方式,本文主要探究ASSM管理方式下,段的三级位图结构和高水位推进的关系 先来看
ASSM管理方式下的三级位图结构图:


注:此图来源于网络
一个段被创建之后,段头其实是一个L3块,在我的试验中,一个1M固定区大小,block为8K的表空间,创建的第一个段第0个区,前两个块是L1(128,129号块),第三个是L2(130号块),第四个是L3(131号块),前128个块是数据文件头,本文不讨论。
当数据被插入的时候,oracle根据连接进来的session,做hash运算,随机选择一个L3(如果有多个的话),再随机选择一个L2,接下来在该L2下随机选择一个L1,再从L1中管理的block里面随机选择一个块将数据插入,不同的session经过hash之后,最后落到block时已经是很分散的了,不会产生很多和会话同时往一个block中插数据申请独占buffer pin,而造成大量buffer busy waits的情况,这就是ASSM号称支持大并发插入的原理所在,但是事实上真的如此么?我们来一探究竟!

实验环境

1
2
OS:REDHAT6.5 X64
DB VERSION:11.2.0.4

首先,创建一个1M区大小的表空间,并在上面创建一张表,此表创建了4个空间很大的字段,目的是要一行占满一个block,便于观察结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> select file#,NAME,BYTES/1024/1204 from v$datafile;

FILE# NAME BYTES/1024/1204
---------- -------------------------------------------------------------------------------- ---------------
1 +DATA/min/datafile/system.256.854775095 637.873754
2 +DATA/min/datafile/sysaux.257.854775097 484.784053
3 +DATA/min/datafile/undotbs1.258.854775097 187.109635
4 +DATA/min/datafile/users.259.854775097 103.122924

SQL> create tablespace lp datafile '+DATA/min/datafile/lp.dbf' size 2048M uniform size 1m;

Tablespace created.

SQL> select file#,NAME,BYTES/1024/1204 from v$datafile;

FILE# NAME BYTES/1024/1204
---------- -------------------------------------------------------------------------------- ---------------
1 +DATA/min/datafile/system.256.854775095 637.873754
2 +DATA/min/datafile/sysaux.257.854775097 484.784053
3 +DATA/min/datafile/undotbs1.258.854775097 187.109635
4 +DATA/min/datafile/users.259.854775097 103.122924
5 +DATA/min/datafile/lp.dbf 1741.8206

SQL> create table lp (id number,des1 char(2000),des2 char(2000),des3 char(2000),des4 char(500)) tablespace lp;

Table created.

观察新建的表所占区

1
2
3
4
5
6
7
8
9
10
11
SQL> col SEGMENT_NAME for a30
SQL> col des1 for a1
SQL> col des2 for a1
SQL> col des3 for a1
SQL> col des4 for a1
SQL> set line 200
SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID, BLOCKS from dba_extents where segment_name='LP';

SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
LP 0 5 128 128

此表是5号文件,拥有1个区,block从128号开始,接下来我们插入一条数据,并dump出段头进行观察

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> insert into lp values(1,'a','a','a','a');

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name ,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='LP';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
LP 5 131


SQL> alter system checkpoint;

System altered.

LP的段头是5号文件,131号块,进行dump

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> alter system dump datafile 5 block 131;

SQL> col value for a65
SQL> select * from v$diag_info;

INST_ID NAME VALUE
---------- -------------------------------------------------------------------------------- -----------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/min/min
1 Diag Trace /u01/app/oracle/diag/rdbms/min/min/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/min/min/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/min/min/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/min/min/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/min/min/hm
1 Default Trace File /u01/app/oracle/diag/rdbms/min/min/trace/min_ora_2835.trc
1 Active Problem Count 0
1 Active Incident Count 0

11 rows selected.

观察trc文件min_ora_2835.trc

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
Block dump from disk:
buffer tsn: 8 rdba: 0x01400083 (5/131)
scn: 0x0000.00140d51 seq: 0x01 flg: 0x04 tail: 0x0d512301
frmt: 0x02 chkval: 0xfd8a type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1



Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x014000c0 ext#: 0 blk#: 64 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 60
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01400084 ext#: 0 blk#: 4 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01400080
Level 1 BMB for Low HWM block: 0x01400080
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01400082
Last Level 1 BMB: 0x01400081
Last Level II BMB: 0x01400082
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 87596 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01400080 length: 128

Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01400080 Data dba: 0x01400084
--------------------------------------------------------

Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01400082

End dump data blocks tsn: 8 file#: 5 minblk 131 maxblk 131

很容易发现这是一个PAGETABLE SEGMENT HEADER块,Extent Map中只有一个区,另外在尾部有一个指向二级位图块的地址0x01400082,这是数据块地址的二进制用十六进制来表示,我们来将其转化成直观一点的信息

1
2
3
4
5
6
7
SQL> select dbms_utility.data_block_address_file(to_number('01400082', 'xxxxxxxx')) file#,
2 dbms_utility.data_block_address_block(to_number('01400082', 'xxxxxxxx')) block#
3 from dual;

FILE# BLOCK#
---------- ----------
5 130

依样将5号文件130号块的内容dump出来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Block dump from disk:
buffer tsn: 8 rdba: 0x01400082 (5/130)
scn: 0x0000.00140a45 seq: 0x02 flg: 0x04 tail: 0x0a452102
frmt: 0x02 chkval: 0xd119 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1


Dump of Second Level Bitmap Block
number: 2 nfree: 2 ffree: 0 pdba: 0x01400083
Inc #: 0 Objd: 87596
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x01400080 Free: 5 Inst: 1
0x01400081 Free: 5 Inst: 1

--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 130 maxblk 130

可以发现这是一个二级位图块,并从其中找到了2个L1块的地址,我们来看第一个L1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Block dump from disk:
buffer tsn: 8 rdba: 0x01400080 (5/128)
scn: 0x0000.00140d51 seq: 0x03 flg: 0x04 tail: 0x0d512003
frmt: 0x02 chkval: 0xe697 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1

DBA Ranges :
--------------------------------------------------------
0x01400080 Length: 64 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:Metadata
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
28:75-100% free 29:75-100% free 30:75-100% free 31:0-25% free
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 128 maxblk 128

无疑这是一个L1块,里面挂了64个数据块,因为我之前插入了一行数据,oracle已经格式化了一部分,并向其中一个块插入了数据,但是没达到我们的效果,被插入的块还显示0-25%的空闲,我们要的是full的状态,说明我们构造的数据不够大,没关系,我们修改一下pctfree就可以了,另外我们可以发现这个区是1M,有2个L1,每个L1里有64个数据块,接下来修改pctfree

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> alter table lp pctfree 24;

Table altered.

SQL> select TABLE_NAME,PCT_FREE from dba_tables where table_name='LP';

TABLE_NAME PCT_FREE
------------------------------ ----------
LP 24

SQL> insert into lp values(2,'a','a','a','a');

1 row created.

SQL> commit;

Commit complete.
--手动触发检查点将内存中的块刷新到磁盘,本文每次dump之前都会做这个操作,以保证磁盘和内存的内容一致
SQL> alter system checkpoint;

System altered.

我们再看128号块

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DBA Ranges :
--------------------------------------------------------
0x01400080 Length: 64 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:Metadata
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
28:75-100% free 29:75-100% free 30:75-100% free 31:0-25% free
32:75-100% free 33:75-100% free 34:75-100% free <font color='red'>35:FULL</font>
36:75-100% free 37:75-100% free 38:75-100% free 39:75-100% free
40:75-100% free 41:75-100% free 42:75-100% free 43:75-100% free
44:75-100% free 45:75-100% free 46:75-100% free 47:75-100% free
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 128 maxblk 128

已经是我们想的结果了,到这里我们再来看一下另外一个L1吧,dump129号块

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DBA Ranges :
--------------------------------------------------------
0x014000c0 Length: 64 Offset: 0

0:unformatted 1:unformatted 2:unformatted 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 129 maxblk 129

全是未格式化的块,试一试并发插入,这里不模拟真正的并发了,只是开不同的session来进行插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SQL> insert into lp values(3,'a','a','a','a');

1 row created.

SQL> insert into lp values(4,'a','a','a','a');

1 row created.

SQL> insert into lp values(5,'a','a','a','a');

1 row created.

SQL> insert into lp values(6,'a','a','a','a');

1 row created.

SQL> insert into lp values(7,'a','a','a','a');

1 row created.

--单独session5条

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
5 158
5 159
5 163
5 167
5 171
5 172
5 174
5 175
5 179
5 183
5 187
5 191

12 rows selected.

一个单独的session里插入了5条,另外5个单独的session里各插一条,发现规律了么,插入的块只在132和192之间,我们分别看看两个L1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
SQL> alter system checkpoint;

System altered.


DBA Ranges :
--------------------------------------------------------
0x01400080 Length: 64 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:Metadata
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
28:75-100% free 29:75-100% free 30:FULL 31:0-25% free
32:75-100% free 33:75-100% free 34:75-100% free 35:FULL
36:75-100% free 37:75-100% free 38:75-100% free 39:FULL
40:75-100% free 41:75-100% free 42:75-100% free 43:FULL
44:FULL 45:75-100% free 46:FULL 47:FULL
48:75-100% free 49:75-100% free 50:75-100% free 51:FULL
52:75-100% free 53:75-100% free 54:75-100% free 55:FULL
56:75-100% free 57:75-100% free 58:75-100% free 59:FULL
60:75-100% free 61:75-100% free 62:75-100% free 63:FULL
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 128 maxblk 128

********************************************************************
看一下第二个L1

DBA Ranges :
--------------------------------------------------------
0x014000c0 Length: 64 Offset: 0

0:unformatted 1:unformatted 2:unformatted 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 129 maxblk 129

这说明只在第一个L1里面随机,那为什么L2下挂了2个L1,我插入了10条却没有一条随机到第二个L1块呢?答案是高水位,这是vage大师已经论证过的了,偶在这里仅为见证一下~~,好了,还记得L3里的高水位信息么?

1
2
3
4
5
6
7
8
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 101 #blocks: 12928
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x014000c0 ext#: 0 blk#: 64 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 60
mapblk 0x00000000 offset: 0

Highwater:: 0x014000c0是哪个块呢?

1
2
3
4
5
6
7
  SQL> select dbms_utility.data_block_address_file(to_number('014000c0', 'xxxxxxxx')) file#,
2 dbms_utility.data_block_address_block(to_number('014000c0', 'xxxxxxxx')) block#
3 from dual;

FILE# BLOCK#
---------- ----------
5 192

这是第二个L1管理的第一个块,可见数据的插入只在高水位之下进行(直接路径插入除外),此时的状态如下:

那么L1里面只会有64个块么?高水位永远都会指向L1的最后一个块么?我们继续剖析!
首先给LP表多分配一些区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
SQL> alter table lp allocate extent(size 100m);

Table altered.

SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID, BLOCKS from dba_extents where segment_name='LP';

SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
LP 0 5 128 128
LP 1 5 256 128
LP 2 5 384 128
LP 3 5 512 128
LP 4 5 640 128
LP 5 5 768 128
LP 6 5 896 128
LP 7 5 1024 128
LP 8 5 1152 128
LP 9 5 1280 128
LP 10 5 1408 128
LP 11 5 1536 128
LP 12 5 1664 128
LP 13 5 1792 128
LP 14 5 1920 128
LP 15 5 2048 128
LP 16 5 2176 128
LP 17 5 2304 128
LP 18 5 2432 128
LP 19 5 2560 128
LP 20 5 2688 128
LP 21 5 2816 128
LP 22 5 2944 128
LP 23 5 3072 128
LP 24 5 3200 128
LP 25 5 3328 128
LP 26 5 3456 128
LP 27 5 3584 128
LP 28 5 3712 128
LP 29 5 3840 128
LP 30 5 3968 128
LP 31 5 4096 128
LP 32 5 4224 128
LP 33 5 4352 128
LP 34 5 4480 128
LP 35 5 4608 128
LP 36 5 4736 128
LP 37 5 4864 128
LP 38 5 4992 128
LP 39 5 5120 128
LP 40 5 5248 128
LP 41 5 5376 128
LP 42 5 5504 128
LP 43 5 5632 128
LP 44 5 5760 128
LP 45 5 5888 128
LP 46 5 6016 128
LP 47 5 6144 128
LP 48 5 6272 128
LP 49 5 6400 128
LP 50 5 6528 128
LP 51 5 6656 128
LP 52 5 6784 128
LP 53 5 6912 128
LP 54 5 7040 128
LP 55 5 7168 128
LP 56 5 7296 128
LP 57 5 7424 128
LP 58 5 7552 128
LP 59 5 7680 128
LP 60 5 7808 128
LP 61 5 7936 128
LP 62 5 8064 128
LP 63 5 8192 128
LP 64 5 8320 128
LP 65 5 8448 128
LP 66 5 8576 128
LP 67 5 8704 128
LP 68 5 8832 128
LP 69 5 8960 128
LP 70 5 9088 128
LP 71 5 9216 128
LP 72 5 9344 128
LP 73 5 9472 128
LP 74 5 9600 128
LP 75 5 9728 128
LP 76 5 9856 128
LP 77 5 9984 128
LP 78 5 10112 128
LP 79 5 10240 128
LP 80 5 10368 128
LP 81 5 10496 128
LP 82 5 10624 128
LP 83 5 10752 128
LP 84 5 10880 128
LP 85 5 11008 128
LP 86 5 11136 128
LP 87 5 11264 128
LP 88 5 11392 128
LP 89 5 11520 128
LP 90 5 11648 128
LP 91 5 11776 128
LP 92 5 11904 128
LP 93 5 12032 128
LP 94 5 12160 128
LP 95 5 12288 128
LP 96 5 12416 128
LP 97 5 12544 128
LP 98 5 12672 128
LP 99 5 12800 128
LP 100 5 12928 128

101 rows selected.

我分配了100个区,加上原来的一共101个,现在我们看看L3段头里面的内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 101 #blocks: 12928
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x014000c0 ext#: 0 blk#: 64 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 60
mapblk 0x00000000 offset: 0


Extent Map
-----------------------------------------------------------------
0x01400080 length: 128
0x01400100 length: 128
0x01400180 length: 128
0x01400200 length: 128
0x01400280 length: 128
0x01400300 length: 128
0x01400380 length: 128
0x01400400 length: 128
0x01400480 length: 128
0x01400500 length: 128
0x01400580 length: 128
0x01400600 length: 128
0x01400680 length: 128
0x01400700 length: 128
0x01400780 length: 128
0x01400800 length: 128
0x01400880 length: 128
0x01400900 length: 128
0x01400980 length: 128
0x01400a00 length: 128
0x01400a80 length: 128
0x01400b00 length: 128
0x01400b80 length: 128
0x01400c00 length: 128
0x01400c80 length: 128
0x01400d00 length: 128
0x01400d80 length: 128
0x01400e00 length: 128
0x01400e80 length: 128
0x01400f00 length: 128
0x01400f80 length: 128
0x01401000 length: 128
0x01401080 length: 128
0x01401100 length: 128
0x01401180 length: 128
0x01401200 length: 128
0x01401280 length: 128
0x01401300 length: 128
0x01401380 length: 128
0x01401400 length: 128
0x01401480 length: 128
0x01401500 length: 128
0x01401580 length: 128
0x01401600 length: 128
0x01401680 length: 128
0x01401700 length: 128
0x01401780 length: 128
0x01401800 length: 128
0x01401880 length: 128
0x01401900 length: 128
0x01401980 length: 128
0x01401a00 length: 128
0x01401a80 length: 128
0x01401b00 length: 128
0x01401b80 length: 128
0x01401c00 length: 128
0x01401c80 length: 128
0x01401d00 length: 128
0x01401d80 length: 128
0x01401e00 length: 128
0x01401e80 length: 128
0x01401f00 length: 128
0x01401f80 length: 128
0x01402000 length: 128
0x01402080 length: 128
0x01402100 length: 128
0x01402180 length: 128
0x01402200 length: 128
0x01402280 length: 128
0x01402300 length: 128
0x01402380 length: 128
0x01402400 length: 128
0x01402480 length: 128
0x01402500 length: 128
0x01402580 length: 128
0x01402600 length: 128
0x01402680 length: 128
0x01402700 length: 128
0x01402780 length: 128
0x01402800 length: 128
0x01402880 length: 128
0x01402900 length: 128
0x01402980 length: 128
0x01402a00 length: 128
0x01402a80 length: 128
0x01402b00 length: 128
0x01402b80 length: 128
0x01402c00 length: 128
0x01402c80 length: 128
0x01402d00 length: 128
0x01402d80 length: 128
0x01402e00 length: 128
0x01402e80 length: 128
0x01402f00 length: 128
0x01402f80 length: 128
0x01403000 length: 128
0x01403080 length: 128
0x01403100 length: 128
0x01403180 length: 128
0x01403200 length: 128
0x01403280 length: 128

Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01400080 Data dba: 0x01400084
Extent 1 : L1 dba: 0x01400100 Data dba: 0x01400102
Extent 2 : L1 dba: 0x01400180 Data dba: 0x01400182
Extent 3 : L1 dba: 0x01400200 Data dba: 0x01400202
Extent 4 : L1 dba: 0x01400280 Data dba: 0x01400282
Extent 5 : L1 dba: 0x01400300 Data dba: 0x01400302
Extent 6 : L1 dba: 0x01400380 Data dba: 0x01400382
Extent 7 : L1 dba: 0x01400400 Data dba: 0x01400402
Extent 8 : L1 dba: 0x01400480 Data dba: 0x01400482
Extent 9 : L1 dba: 0x01400500 Data dba: 0x01400502
Extent 10 : L1 dba: 0x01400580 Data dba: 0x01400582
Extent 11 : L1 dba: 0x01400600 Data dba: 0x01400602
Extent 12 : L1 dba: 0x01400680 Data dba: 0x01400682
Extent 13 : L1 dba: 0x01400700 Data dba: 0x01400702
Extent 14 : L1 dba: 0x01400780 Data dba: 0x01400782
Extent 15 : L1 dba: 0x01400800 Data dba: 0x01400802
Extent 16 : L1 dba: 0x01400880 Data dba: 0x01400882
Extent 17 : L1 dba: 0x01400900 Data dba: 0x01400902
Extent 18 : L1 dba: 0x01400980 Data dba: 0x01400982
Extent 19 : L1 dba: 0x01400a00 Data dba: 0x01400a02
Extent 20 : L1 dba: 0x01400a80 Data dba: 0x01400a82
Extent 21 : L1 dba: 0x01400b00 Data dba: 0x01400b02
Extent 22 : L1 dba: 0x01400b80 Data dba: 0x01400b82
Extent 23 : L1 dba: 0x01400c00 Data dba: 0x01400c02
Extent 24 : L1 dba: 0x01400c80 Data dba: 0x01400c82
Extent 25 : L1 dba: 0x01400d00 Data dba: 0x01400d02
Extent 26 : L1 dba: 0x01400d80 Data dba: 0x01400d82
Extent 27 : L1 dba: 0x01400e00 Data dba: 0x01400e02
Extent 28 : L1 dba: 0x01400e80 Data dba: 0x01400e82
Extent 29 : L1 dba: 0x01400f00 Data dba: 0x01400f02
Extent 30 : L1 dba: 0x01400f80 Data dba: 0x01400f82
Extent 31 : L1 dba: 0x01401000 Data dba: 0x01401002
Extent 32 : L1 dba: 0x01401080 Data dba: 0x01401082
Extent 33 : L1 dba: 0x01401100 Data dba: 0x01401102
Extent 34 : L1 dba: 0x01401180 Data dba: 0x01401182
Extent 35 : L1 dba: 0x01401200 Data dba: 0x01401202
Extent 36 : L1 dba: 0x01401280 Data dba: 0x01401282
Extent 37 : L1 dba: 0x01401300 Data dba: 0x01401302
Extent 38 : L1 dba: 0x01401380 Data dba: 0x01401382
Extent 39 : L1 dba: 0x01401400 Data dba: 0x01401402
Extent 40 : L1 dba: 0x01401480 Data dba: 0x01401482
Extent 41 : L1 dba: 0x01401500 Data dba: 0x01401502
Extent 42 : L1 dba: 0x01401580 Data dba: 0x01401582
Extent 43 : L1 dba: 0x01401600 Data dba: 0x01401602
Extent 44 : L1 dba: 0x01401680 Data dba: 0x01401682
Extent 45 : L1 dba: 0x01401700 Data dba: 0x01401702
Extent 46 : L1 dba: 0x01401780 Data dba: 0x01401782
Extent 47 : L1 dba: 0x01401800 Data dba: 0x01401802
Extent 48 : L1 dba: 0x01401880 Data dba: 0x01401882
Extent 49 : L1 dba: 0x01401900 Data dba: 0x01401902
Extent 50 : L1 dba: 0x01401980 Data dba: 0x01401982
Extent 51 : L1 dba: 0x01401a00 Data dba: 0x01401a02
Extent 52 : L1 dba: 0x01401a80 Data dba: 0x01401a82
Extent 53 : L1 dba: 0x01401b00 Data dba: 0x01401b02
Extent 54 : L1 dba: 0x01401b80 Data dba: 0x01401b82
Extent 55 : L1 dba: 0x01401c00 Data dba: 0x01401c02
Extent 56 : L1 dba: 0x01401c80 Data dba: 0x01401c82
Extent 57 : L1 dba: 0x01401d00 Data dba: 0x01401d02
Extent 58 : L1 dba: 0x01401d80 Data dba: 0x01401d82
Extent 59 : L1 dba: 0x01401e00 Data dba: 0x01401e02
Extent 60 : L1 dba: 0x01401e80 Data dba: 0x01401e82
Extent 61 : L1 dba: 0x01401f00 Data dba: 0x01401f02
Extent 62 : L1 dba: 0x01401f80 Data dba: 0x01401f82
Extent 63 : L1 dba: 0x01402000 Data dba: 0x01402001
Extent 64 : L1 dba: 0x01402000 Data dba: 0x01402080
Extent 65 : L1 dba: 0x01402100 Data dba: 0x01402101
Extent 66 : L1 dba: 0x01402100 Data dba: 0x01402180
Extent 67 : L1 dba: 0x01402200 Data dba: 0x01402201
Extent 68 : L1 dba: 0x01402200 Data dba: 0x01402280
Extent 69 : L1 dba: 0x01402300 Data dba: 0x01402301
Extent 70 : L1 dba: 0x01402300 Data dba: 0x01402380
Extent 71 : L1 dba: 0x01402400 Data dba: 0x01402401
Extent 72 : L1 dba: 0x01402400 Data dba: 0x01402480
Extent 73 : L1 dba: 0x01402500 Data dba: 0x01402501
Extent 74 : L1 dba: 0x01402500 Data dba: 0x01402580
Extent 75 : L1 dba: 0x01402600 Data dba: 0x01402601
Extent 76 : L1 dba: 0x01402600 Data dba: 0x01402680
Extent 77 : L1 dba: 0x01402700 Data dba: 0x01402701
Extent 78 : L1 dba: 0x01402700 Data dba: 0x01402780
Extent 79 : L1 dba: 0x01402800 Data dba: 0x01402801
Extent 80 : L1 dba: 0x01402800 Data dba: 0x01402880
Extent 81 : L1 dba: 0x01402900 Data dba: 0x01402901
Extent 82 : L1 dba: 0x01402900 Data dba: 0x01402980
Extent 83 : L1 dba: 0x01402a00 Data dba: 0x01402a01
Extent 84 : L1 dba: 0x01402a00 Data dba: 0x01402a80
Extent 85 : L1 dba: 0x01402b00 Data dba: 0x01402b01
Extent 86 : L1 dba: 0x01402b00 Data dba: 0x01402b80
Extent 87 : L1 dba: 0x01402c00 Data dba: 0x01402c01
Extent 88 : L1 dba: 0x01402c00 Data dba: 0x01402c80
Extent 89 : L1 dba: 0x01402d00 Data dba: 0x01402d01
Extent 90 : L1 dba: 0x01402d00 Data dba: 0x01402d80
Extent 91 : L1 dba: 0x01402e00 Data dba: 0x01402e01
Extent 92 : L1 dba: 0x01402e00 Data dba: 0x01402e80
Extent 93 : L1 dba: 0x01402f00 Data dba: 0x01402f01
Extent 94 : L1 dba: 0x01402f00 Data dba: 0x01402f80
Extent 95 : L1 dba: 0x01403000 Data dba: 0x01403001
Extent 96 : L1 dba: 0x01403000 Data dba: 0x01403080
Extent 97 : L1 dba: 0x01403100 Data dba: 0x01403101
Extent 98 : L1 dba: 0x01403100 Data dba: 0x01403180
Extent 99 : L1 dba: 0x01403200 Data dba: 0x01403201
Extent 100 : L1 dba: 0x01403200 Data dba: 0x01403280
--------------------------------------------------------

Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01400082

End dump data blocks tsn: 8 file#: 5 minblk 131 maxblk 131

这里我们主要看Auxillary Map,里面记录了每个区所属的L1,不难发现从第64个区开始,64、65两个区的L1相同,这说明什么,说明L1下面挂了整整两个区的数据块,验证一下,将0x01402000块dump出来之后的内容:

1
2
3
4
5
6
  01402000  十进制 》20979712
SQL> select dbms_utility.data_block_address_file(20979712) Rfile#,dbms_utility.data_block_address_block(20979712) "Block#" from dual;

RFILE# Block#
---------- ----------
5 8192
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
          DBA Ranges :
--------------------------------------------------------
0x01402000 Length: 128 Offset: 0
0x01402080 Length: 128 Offset: 128

0:Metadata 1:unformatted 2:unformatted 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
64:unformatted 65:unformatted 66:unformatted 67:unformatted
68:unformatted 69:unformatted 70:unformatted 71:unformatted
72:unformatted 73:unformatted 74:unformatted 75:unformatted
76:unformatted 77:unformatted 78:unformatted 79:unformatted
80:unformatted 81:unformatted 82:unformatted 83:unformatted
84:unformatted 85:unformatted 86:unformatted 87:unformatted
88:unformatted 89:unformatted 90:unformatted 91:unformatted
92:unformatted 93:unformatted 94:unformatted 95:unformatted
96:unformatted 97:unformatted 98:unformatted 99:unformatted
100:unformatted 101:unformatted 102:unformatted 103:unformatted
104:unformatted 105:unformatted 106:unformatted 107:unformatted
108:unformatted 109:unformatted 110:unformatted 111:unformatted
112:unformatted 113:unformatted 114:unformatted 115:unformatted
116:unformatted 117:unformatted 118:unformatted 119:unformatted
120:unformatted 121:unformatted 122:unformatted 123:unformatted
124:unformatted 125:unformatted 126:unformatted 127:unformatted
128:unformatted 129:unformatted 130:unformatted 131:unformatted
132:unformatted 133:unformatted 134:unformatted 135:unformatted
136:unformatted 137:unformatted 138:unformatted 139:unformatted
140:unformatted 141:unformatted 142:unformatted 143:unformatted
144:unformatted 145:unformatted 146:unformatted 147:unformatted
148:unformatted 149:unformatted 150:unformatted 151:unformatted
152:unformatted 153:unformatted 154:unformatted 155:unformatted
156:unformatted 157:unformatted 158:unformatted 159:unformatted
160:unformatted 161:unformatted 162:unformatted 163:unformatted
164:unformatted 165:unformatted 166:unformatted 167:unformatted
168:unformatted 169:unformatted 170:unformatted 171:unformatted
172:unformatted 173:unformatted 174:unformatted 175:unformatted
176:unformatted 177:unformatted 178:unformatted 179:unformatted
180:unformatted 181:unformatted 182:unformatted 183:unformatted
184:unformatted 185:unformatted 186:unformatted 187:unformatted
188:unformatted 189:unformatted 190:unformatted 191:unformatted
192:unformatted 193:unformatted 194:unformatted 195:unformatted
196:unformatted 197:unformatted 198:unformatted 199:unformatted
200:unformatted 201:unformatted 202:unformatted 203:unformatted
204:unformatted 205:unformatted 206:unformatted 207:unformatted
208:unformatted 209:unformatted 210:unformatted 211:unformatted
212:unformatted 213:unformatted 214:unformatted 215:unformatted
216:unformatted 217:unformatted 218:unformatted 219:unformatted
220:unformatted 221:unformatted 222:unformatted 223:unformatted
224:unformatted 225:unformatted 226:unformatted 227:unformatted
228:unformatted 229:unformatted 230:unformatted 231:unformatted
232:unformatted 233:unformatted 234:unformatted 235:unformatted
236:unformatted 237:unformatted 238:unformatted 239:unformatted
240:unformatted 241:unformatted 242:unformatted 243:unformatted
244:unformatted 245:unformatted 246:unformatted 247:unformatted
248:unformatted 249:unformatted 250:unformatted 251:unformatted
252:unformatted 253:unformatted 254:unformatted 255:unformatted
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 8192 maxblk 8192

256个块,2M 2个区,可见L1中块的数量会随着段大小的改变而做调整的,可以增大到1024个甚至更多,我在这里不去再做验证,将重点放在高水位上,根据我们之前观察到的现象,高水位在第二个L1的地一个块,也可说第一个L1的末端,那么现在的L1里有256个块,高水位是不是也会推进到这个L1的末端呢?很容易进行验证,将数据插满到这个L1的前几个块再去观察段头高水位的变化就行了,那么插多少条呢?再来计算一下,当前的L1是8192号块,前面一共63个区,每个区里面两个L1,加上第一个区里面的一个L2和L3,我们需要插满8192-63*2-2=8064

1
2
3
4
5
6
7
8
declare
i number;
begin
for i in 1..8064 loop
insert into lp values(i,'a','a','a','a');
end loop;
end;
/

观察62号区的第二个L1,第一个L1的地址是0x01401f80,那么第二个就是01401f81,5号文件8065号块,dump出来的结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DBA Ranges :
--------------------------------------------------------
0x01401fc0 Length: 64 Offset: 0

0:FULL 1:FULL 2:FULL 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
16:FULL 17:FULL 18:FULL 19:FULL
20:FULL 21:FULL 22:FULL 23:FULL
24:FULL 25:FULL 26:FULL 27:FULL
28:FULL 29:FULL 30:FULL 31:FULL
32:FULL 33:FULL 34:FULL 35:FULL
36:FULL 37:FULL 38:FULL 39:FULL
40:FULL 41:FULL 42:FULL 43:FULL
44:FULL 45:FULL 46:FULL 47:FULL
48:FULL 49:FULL 50:FULL 51:FULL
52:FULL 53:FULL 54:FULL 55:FULL
56:FULL 57:FULL 58:FULL 59:FULL
60:FULL 61:FULL 62:FULL 63:FULL
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 8065 maxblk 8065

居然全满了?再看8192号L1呢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
DBA Ranges :
--------------------------------------------------------
0x01402000 Length: 128 Offset: 0
0x01402080 Length: 128 Offset: 128

0:Metadata 1:FULL 2:FULL 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
16:FULL 17:FULL 18:FULL 19:FULL
20:FULL 21:FULL 22:FULL 23:FULL
24:FULL 25:FULL 26:FULL 27:FULL
28:FULL 29:FULL 30:FULL 31:FULL
32:FULL 33:FULL 34:FULL 35:FULL
36:FULL 37:FULL 38:FULL 39:FULL
40:FULL 41:FULL 42:FULL 43:FULL
44:FULL 45:FULL 46:FULL 47:FULL
48:FULL 49:FULL 50:FULL 51:FULL
52:FULL 53:FULL 54:FULL 55:FULL
56:FULL 57:FULL 58:FULL 59:FULL
60:FULL 61:FULL 62:FULL 63:FULL
64:FULL 65:FULL 66:FULL 67:FULL
68:FULL 69:FULL 70:FULL 71:FULL
72:FULL 73:FULL 74:FULL 75:FULL
76:FULL 77:FULL 78:FULL 79:FULL
80:FULL 81:FULL 82:FULL 83:FULL
84:FULL 85:FULL 86:FULL 87:FULL
88:FULL 89:FULL 90:FULL 91:FULL
92:FULL 93:FULL 94:FULL 95:FULL
96:FULL 97:FULL 98:FULL 99:FULL
100:FULL 101:FULL 102:FULL 103:FULL
104:FULL 105:FULL 106:FULL 107:FULL
108:FULL 109:FULL 110:FULL 111:FULL
112:FULL 113:FULL 114:FULL 115:FULL
116:FULL 117:FULL 118:FULL 119:FULL
120:FULL 121:FULL 122:FULL 123:FULL
124:FULL 125:FULL 126:FULL 127:FULL
128:unformatted 129:unformatted 130:unformatted 131:unformatted
132:unformatted 133:unformatted 134:unformatted 135:unformatted
136:unformatted 137:unformatted 138:unformatted 139:unformatted
140:unformatted 141:unformatted 142:unformatted 143:unformatted
144:75-100% free 145:75-100% free 146:75-100% free 147:75-100% free
148:75-100% free 149:75-100% free 150:75-100% free 151:75-100% free
152:75-100% free 153:75-100% free 154:75-100% free 155:FULL
156:75-100% free 157:75-100% free 158:75-100% free 159:75-100% free
160:75-100% free 161:75-100% free 162:75-100% free 163:FULL
164:75-100% free 165:75-100% free 166:75-100% free 167:75-100% free
168:75-100% free 169:75-100% free 170:75-100% free 171:75-100% free
172:75-100% free 173:75-100% free 174:75-100% free 175:75-100% free
176:unformatted 177:unformatted 178:unformatted 179:unformatted
180:unformatted 181:unformatted 182:unformatted 183:unformatted
184:unformatted 185:unformatted 186:unformatted 187:unformatted
188:unformatted 189:unformatted 190:unformatted 191:unformatted
192:unformatted 193:unformatted 194:unformatted 195:unformatted
196:unformatted 197:unformatted 198:unformatted 199:unformatted
200:unformatted 201:unformatted 202:unformatted 203:unformatted
204:unformatted 205:unformatted 206:unformatted 207:unformatted
208:unformatted 209:unformatted 210:unformatted 211:unformatted
212:unformatted 213:unformatted 214:unformatted 215:unformatted
216:unformatted 217:unformatted 218:unformatted 219:unformatted
220:unformatted 221:unformatted 222:unformatted 223:unformatted
224:unformatted 225:unformatted 226:unformatted 227:unformatted
228:unformatted 229:unformatted 230:unformatted 231:unformatted
232:unformatted 233:unformatted 234:unformatted 235:unformatted
236:unformatted 237:unformatted 238:unformatted 239:unformatted
240:unformatted 241:unformatted 242:unformatted 243:unformatted
244:unformatted 245:unformatted 246:unformatted 247:unformatted
248:unformatted 249:unformatted 250:unformatted 251:unformatted
252:unformatted 253:unformatted 254:unformatted 255:unformatted
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 8192 maxblk 8192

完蛋了,计算错误,L1下面的第二个区也已经被格式化了,看段头信息

1
2
3
4
5
6
7
8
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 101 #blocks: 12928
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01402100 ext#: 64 blk#: 128 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 8191
mapblk 0x00000000 offset: 64

果然高水位指到了第66个区的L1块地址,这会影响我们的判断,到底哪里出问题了呢?观察8192号块内容是后128个块里被插入了2条,前128个块全满,也就是说我多插了130个块,后来猛然醒悟,我使用DBA来计算的,忘记减去128个块的文件头了,这么一算结果相查差还是不大的,太马虎了,要不是因为马虎哥也能上清华北大了;然后该怎么办呢?重新来过?不用!既然这个L1推过头了,索性把它堆满,然后我们去观察下一个L1,这下就好计算了;

1
2
3
4
5
6
7
8
declare
i number;
begin
for i in 1..126 loop
insert into lp values(i,'a','a','a','a');
end loop;
end;
/

观察8192号块

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 DBA Ranges :
--------------------------------------------------------
0x01402000 Length: 128 Offset: 0
0x01402080 Length: 128 Offset: 128

0:Metadata 1:FULL 2:FULL 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
16:FULL 17:FULL 18:FULL 19:FULL
20:FULL 21:FULL 22:FULL 23:FULL
24:FULL 25:FULL 26:FULL 27:FULL
28:FULL 29:FULL 30:FULL 31:FULL
32:FULL 33:FULL 34:FULL 35:FULL
36:FULL 37:FULL 38:FULL 39:FULL
40:FULL 41:FULL 42:FULL 43:FULL
44:FULL 45:FULL 46:FULL 47:FULL
48:FULL 49:FULL 50:FULL 51:FULL
52:FULL 53:FULL 54:FULL 55:FULL
56:FULL 57:FULL 58:FULL 59:FULL
60:FULL 61:FULL 62:FULL 63:FULL
64:FULL 65:FULL 66:FULL 67:FULL
68:FULL 69:FULL 70:FULL 71:FULL
72:FULL 73:FULL 74:FULL 75:FULL
76:FULL 77:FULL 78:FULL 79:FULL
80:FULL 81:FULL 82:FULL 83:FULL
84:FULL 85:FULL 86:FULL 87:FULL
88:FULL 89:FULL 90:FULL 91:FULL
92:FULL 93:FULL 94:FULL 95:FULL
96:FULL 97:FULL 98:FULL 99:FULL
100:FULL 101:FULL 102:FULL 103:FULL
104:FULL 105:FULL 106:FULL 107:FULL
108:FULL 109:FULL 110:FULL 111:FULL
112:FULL 113:FULL 114:FULL 115:FULL
116:FULL 117:FULL 118:FULL 119:FULL
120:FULL 121:FULL 122:FULL 123:FULL
124:FULL 125:FULL 126:FULL 127:FULL
128:FULL 129:FULL 130:FULL 131:FULL
132:FULL 133:FULL 134:FULL 135:FULL
136:FULL 137:FULL 138:FULL 139:FULL
140:FULL 141:FULL 142:FULL 143:FULL
144:FULL 145:FULL 146:FULL 147:FULL
148:FULL 149:FULL 150:FULL 151:FULL
152:FULL 153:FULL 154:FULL 155:FULL
156:FULL 157:FULL 158:FULL 159:FULL
160:FULL 161:FULL 162:FULL 163:FULL
164:FULL 165:FULL 166:FULL 167:FULL
168:FULL 169:FULL 170:FULL 171:FULL
172:FULL 173:FULL 174:FULL 175:FULL
176:FULL 177:FULL 178:FULL 179:FULL
180:FULL 181:FULL 182:FULL 183:FULL
184:FULL 185:FULL 186:FULL 187:FULL
188:FULL 189:FULL 190:FULL 191:FULL
192:FULL 193:FULL 194:FULL 195:FULL
196:FULL 197:FULL 198:FULL 199:FULL
200:FULL 201:FULL 202:FULL 203:FULL
204:FULL 205:FULL 206:FULL 207:FULL
208:FULL 209:FULL 210:FULL 211:FULL
212:FULL 213:FULL 214:FULL 215:FULL
216:FULL 217:FULL 218:FULL 219:FULL
220:FULL 221:FULL 222:FULL 223:FULL
224:FULL 225:FULL 226:FULL 227:FULL
228:FULL 229:FULL 230:FULL 231:FULL
232:FULL 233:FULL 234:FULL 235:FULL
236:FULL 237:FULL 238:FULL 239:FULL
240:FULL 241:FULL 242:FULL 243:FULL
244:FULL 245:FULL 246:FULL 247:FULL
248:FULL 249:FULL 250:FULL 251:FULL
252:FULL 253:FULL 254:FULL 255:FULL
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 8192 maxblk 8192

已经全满了,这个时候我们再去看一下段头的高水位

1
2
3
4
5
6
7
8
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 101 #blocks: 12928
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01402100 ext#: 64 blk#: 128 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 8191
mapblk 0x00000000 offset: 64

没有动,看来oracle也是事不到眼前不知道着急啊,来看一看下一个L1

1
2
3
4
5
6
7
SQL> select dbms_utility.data_block_address_file(to_number('01402100', 'xxxxxxxx')) file#,
2 dbms_utility.data_block_address_block(to_number('01402100', 'xxxxxxxx')) block#
3 from dual;

FILE# BLOCK#
---------- ----------
5 8448

这个L1是5号文件8448号块,dump出来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 DBA Ranges :
--------------------------------------------------------
0x01402100 Length: 128 Offset: 0
0x01402180 Length: 128 Offset: 128

0:Metadata 1:unformatted 2:unformatted 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
64:unformatted 65:unformatted 66:unformatted 67:unformatted
68:unformatted 69:unformatted 70:unformatted 71:unformatted
72:unformatted 73:unformatted 74:unformatted 75:unformatted
76:unformatted 77:unformatted 78:unformatted 79:unformatted
80:unformatted 81:unformatted 82:unformatted 83:unformatted
84:unformatted 85:unformatted 86:unformatted 87:unformatted
88:unformatted 89:unformatted 90:unformatted 91:unformatted
92:unformatted 93:unformatted 94:unformatted 95:unformatted
96:unformatted 97:unformatted 98:unformatted 99:unformatted
100:unformatted 101:unformatted 102:unformatted 103:unformatted
104:unformatted 105:unformatted 106:unformatted 107:unformatted
108:unformatted 109:unformatted 110:unformatted 111:unformatted
112:unformatted 113:unformatted 114:unformatted 115:unformatted
116:unformatted 117:unformatted 118:unformatted 119:unformatted
120:unformatted 121:unformatted 122:unformatted 123:unformatted
124:unformatted 125:unformatted 126:unformatted 127:unformatted
128:unformatted 129:unformatted 130:unformatted 131:unformatted
132:unformatted 133:unformatted 134:unformatted 135:unformatted
136:unformatted 137:unformatted 138:unformatted 139:unformatted
140:unformatted 141:unformatted 142:unformatted 143:unformatted
144:unformatted 145:unformatted 146:unformatted 147:unformatted
148:unformatted 149:unformatted 150:unformatted 151:unformatted
152:unformatted 153:unformatted 154:unformatted 155:unformatted
156:unformatted 157:unformatted 158:unformatted 159:unformatted
160:unformatted 161:unformatted 162:unformatted 163:unformatted
164:unformatted 165:unformatted 166:unformatted 167:unformatted
168:unformatted 169:unformatted 170:unformatted 171:unformatted
172:unformatted 173:unformatted 174:unformatted 175:unformatted
176:unformatted 177:unformatted 178:unformatted 179:unformatted
180:unformatted 181:unformatted 182:unformatted 183:unformatted
184:unformatted 185:unformatted 186:unformatted 187:unformatted
188:unformatted 189:unformatted 190:unformatted 191:unformatted
192:unformatted 193:unformatted 194:unformatted 195:unformatted
196:unformatted 197:unformatted 198:unformatted 199:unformatted
200:unformatted 201:unformatted 202:unformatted 203:unformatted
204:unformatted 205:unformatted 206:unformatted 207:unformatted
208:unformatted 209:unformatted 210:unformatted 211:unformatted
212:unformatted 213:unformatted 214:unformatted 215:unformatted
216:unformatted 217:unformatted 218:unformatted 219:unformatted
220:unformatted 221:unformatted 222:unformatted 223:unformatted
224:unformatted 225:unformatted 226:unformatted 227:unformatted
228:unformatted 229:unformatted 230:unformatted 231:unformatted
232:unformatted 233:unformatted 234:unformatted 235:unformatted
236:unformatted 237:unformatted 238:unformatted 239:unformatted
240:unformatted 241:unformatted 242:unformatted 243:unformatted
244:unformatted 245:unformatted 246:unformatted 247:unformatted
248:unformatted 249:unformatted 250:unformatted 251:unformatted
252:unformatted 253:unformatted 254:unformatted 255:unformatted
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 8448 maxblk 8448

256个块,除了第一个是L1元数据之外,没有一个格式化的,这就达到了我们的目的,我现在要插入一条数据,迫使高水位推动,猜一下高水位会在哪?在L1管理的最后一个块后面么?
看插入一行数据之后的8448号块

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
DBA Ranges :
--------------------------------------------------------
0x01402100 Length: 128 Offset: 0
0x01402180 Length: 128 Offset: 128

0:Metadata 1:unformatted 2:unformatted 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
28:FULL 29:75-100% free 30:75-100% free 31:75-100% free
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
64:unformatted 65:unformatted 66:unformatted 67:unformatted
68:unformatted 69:unformatted 70:unformatted 71:unformatted
72:unformatted 73:unformatted 74:unformatted 75:unformatted
76:unformatted 77:unformatted 78:unformatted 79:unformatted
80:unformatted 81:unformatted 82:unformatted 83:unformatted
84:unformatted 85:unformatted 86:unformatted 87:unformatted
88:unformatted 89:unformatted 90:unformatted 91:unformatted
92:unformatted 93:unformatted 94:unformatted 95:unformatted
96:unformatted 97:unformatted 98:unformatted 99:unformatted
100:unformatted 101:unformatted 102:unformatted 103:unformatted
104:unformatted 105:unformatted 106:unformatted 107:unformatted
108:unformatted 109:unformatted 110:unformatted 111:unformatted
112:unformatted 113:unformatted 114:unformatted 115:unformatted
116:unformatted 117:unformatted 118:unformatted 119:unformatted
120:unformatted 121:unformatted 122:unformatted 123:unformatted
124:unformatted 125:unformatted 126:unformatted 127:unformatted
128:unformatted 129:unformatted 130:unformatted 131:unformatted
132:unformatted 133:unformatted 134:unformatted 135:unformatted
136:unformatted 137:unformatted 138:unformatted 139:unformatted
140:unformatted 141:unformatted 142:unformatted 143:unformatted
144:unformatted 145:unformatted 146:unformatted 147:unformatted
148:unformatted 149:unformatted 150:unformatted 151:unformatted
152:unformatted 153:unformatted 154:unformatted 155:unformatted
156:unformatted 157:unformatted 158:unformatted 159:unformatted
160:unformatted 161:unformatted 162:unformatted 163:unformatted
164:unformatted 165:unformatted 166:unformatted 167:unformatted
168:unformatted 169:unformatted 170:unformatted 171:unformatted
172:unformatted 173:unformatted 174:unformatted 175:unformatted
176:unformatted 177:unformatted 178:unformatted 179:unformatted
180:unformatted 181:unformatted 182:unformatted 183:unformatted
184:unformatted 185:unformatted 186:unformatted 187:unformatted
188:unformatted 189:unformatted 190:unformatted 191:unformatted
192:unformatted 193:unformatted 194:unformatted 195:unformatted
196:unformatted 197:unformatted 198:unformatted 199:unformatted
200:unformatted 201:unformatted 202:unformatted 203:unformatted
204:unformatted 205:unformatted 206:unformatted 207:unformatted
208:unformatted 209:unformatted 210:unformatted 211:unformatted
212:unformatted 213:unformatted 214:unformatted 215:unformatted
216:unformatted 217:unformatted 218:unformatted 219:unformatted
220:unformatted 221:unformatted 222:unformatted 223:unformatted
224:unformatted 225:unformatted 226:unformatted 227:unformatted
228:unformatted 229:unformatted 230:unformatted 231:unformatted
232:unformatted 233:unformatted 234:unformatted 235:unformatted
236:unformatted 237:unformatted 238:unformatted 239:unformatted
240:unformatted 241:unformatted 242:unformatted 243:unformatted
244:unformatted 245:unformatted 246:unformatted 247:unformatted
248:unformatted 249:unformatted 250:unformatted 251:unformatted
252:unformatted 253:unformatted 254:unformatted 255:unformatted
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 8448 maxblk 8448

有一个块已经满了,揭晓答案的时候来了,dump出段头

1
2
3
4
5
6
7
8
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 101 #blocks: 12928
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01402180 ext#: 65 blk#: 128 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 8318
mapblk 0x00000000 offset: 65

高水位是0x01402180,这个地址在哪呢?0x01402180比0x01402100多了十六进制80个块,就是十进制的128个块,仅仅是推动了一个区的大小,并不是一个L1的大小,这说明常规路径下插入高水位的推动是以L1和区中小的那个单位来推动的,就是L1小就推动L1里面所有块的大小,区小就推动一个区的大小,可见所谓的高并发并非像宣传的那样,还是受高水位的限制的,这个时候的区块大概像下面这个样子:

开几个session做下插入试试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from lp where trim(des1)='b';

5 8474
5 8482
5 8484
5 8488
5 8489
5 8490
5 8491
5 8492
5 8493
5 8494
5 8495
5 8496
5 8498
5 8500
5 8504
5 8506
5 8512
5 8514
5 8520
5 8522

20 rows selected.

全部是在高水位之下
这个实验有一点值得思考,就是PCTFREE如何设置,在插入删除频繁的段,PCTFREE的值应该要远离三个百分比线,就是25%/50%/75%,避免频繁插入删除的时候块状态频繁改变,由full变为非full,如此就会修改L1块的内容,有可能造成buffer busy waits。
而大家熟知的直接路径下的插入是直接在高水位之上插入并且绕过cache buffer的,这种情况下的高水位是如何推动的呢?我们再慢慢分析.

开始

先说一下为什么要写这个异机完全恢复的blog,作为一个年轻的DBA,相信大多数都是备份长做而恢复不常做,我会经常担心我的生产库的服务器挂了怎么办,我的备份完好,但我们又没有容灾,我能否保证我的数据不丢失呢,所以就要rman利用备份重建,而如果此时我能找到完好无损的在线日志,我是不是可以完全恢复了呢,理论上大家都说可以,确从未在网上见到过完全恢复的事例(完全其实很简单,只是网上阐述了太多的不完全恢复,会让我觉得set until time等等是必备的其中一步),甚至oracle的官方网文档上也使用了“SET UNTIL SCN 123456;” 的案例,有鉴于此,我做了这个实验,并记录下来,跟大家分享,不见得这篇原创有多高深的技术和见解O(∩_∩)O

1
2
3
4
5
6
7
8
9
环境:
源库:
os:redhat 6.3 x64
db: 11gr2 11.2.0.4
存储方式:ASM
目标库:
os:redhat 6.5 x64
db: 11gr2 11.2.0.4
存储方式:文件系统

1. 首先,源库进行rman备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--手动0级全库备份
run {
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+DATA/backup/ctl_atuo_%F';
ALLOCATE CHANNEL c1 TYPE DISK;
ALLOCATE CHANNEL c2 TYPE DISK;
CROSSCHECK ARCHIVELOG ALL;
BACKUP INCREMENTAL LEVEL 0
DATABASE FORMAT '+DATA/backup/db_%U' TAG 'db_rman';
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL FORMAT '+DATA/backup/arc_%U' TAG 'ARC_rman'
DELETE INPUT;
DELETE NOPROMPT OBSOLETE;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
}
阅读全文 »
0%