兔子先生

探寻计算机的历史与哲学密码

发表于 更新于 分类于

Once you start using the Broker you must always use the Broker to make any changes to your Data Guard configuration. This means that you must use Grid Control or the Broker CLI DGMGRL to change any Data Guard settings. If you use SQL*Plus to make configuration changes, the Broker will put things back the way it sees the world or this will lead to inconsistencies between the Broker configuration parameters and the database.

一旦开始使用Broker，就必须一直使用它修改DG的配置，这意味着必须使用grid control或者dgmgrl修改DG的配置，如果使用sqlplus修改配置，broker会将配置改回原样，或者导致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
DGMGRL> show configuration;
Configuration - minconf
Protection Mode: MaxAvailability
Databases:
minstd - Primary database
min - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> EDIT DATABASE 'minstd' SET PROPERTY 'LogXptMode'='ASYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE 'min' SET PROPERTY 'LogXptMode'='ASYNC';
Error: ORA-16805: change of LogXptMode property violates overall protection mode
Failed.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxPerformance;
Succeeded.
DGMGRL> show configuration;
Configuration - minconf
Protection Mode: MaxPerformance
Databases:
minstd - Primary database
min - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
````

使用broker修改会使LOG_ARCHIVE_DEST_2参数变得较为复杂，例如

service="minstd", LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="minstd" net_timeout=30, valid_for=(all_logfiles,primary_role)


而且修改后在sqlplus中show parameter LOG_ARCHIVE_DEST_2仅有主库显示更改之后的状态，而备库还保留原样，但是在show database verbose中是修改好的，仅当在发生切换时，备库变为主库时才会更正参数，可见最好使用broker来管理配置

发表于 更新于 分类于

给某一个客户安装ADG完毕，配置Broker方便日后的管理与切换，碰到些许问题，以作记录

创建完configuration之后，enable的过程很慢，而且状态出现error

主库查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DGMGRL> SHOW CONFIGURATION VERBOSE;
Configuration - anxinconf
Protection Mode: MaxAvailability
Databases:
anxin - Primary database
anxinstd - Physical standby database
Error: ORA-16664: unable to receive the result from a database
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: DISABLED
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
DGMGRL> SHOW CONFIGURATION VERBOSE;
Configuration - anxinconf
Protection Mode: MaxAvailability
Databases:
anxin - Primary database
anxinstd - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with database setting
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: DISABLED
Configuration Status:
WARNING

查看备数据库的状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DGMGRL> show database verbose anxinstd;
Database - anxinstd
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 54.00 KByte/s
Real Time Query: ON
Instance(s):
anxinstd
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting

......

此时主备alert日志均有报错 Fatal NI connect error 12514, connecting to:

主库：

1
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=anxinstd_DGB)(CID=(PROGRAM=oracle)(HOST=db01)(USER=oracle))))

备库：

1
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=anxin_DGB)(CID=(PROGRAM=oracle)(HOST=db02)(USER=oracle))))

备库状态报告4个属性值与数据库设置不一致，重新设置

1
2
3
4
5
6
7
8
9
DGMGRL> edit database anxinstd set property LogArchiveFormat='%t_%s_%r.arc';
Property "logarchiveformat" updated
DGMGRL> edit database anxinstd set property ArchiveLagTarget=0;
Property "archivelagtarget" updated
DGMGRL> edit database anxinstd set property LogArchiveTrace=0;
Property "logarchivetrace" updated
DGMGRL> edit database anxinstd set property LogArchiveMinSucceedDest=1;
Property "logarchiveminsucceeddest" updated
DGMGRL> show database verbose anxinstd;

再次查看broker状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DGMGRL> SHOW CONFIGURATION VERBOSE;
Configuration - anxinconf
Protection Mode: MaxAvailability
Databases:
anxin - Primary database
anxinstd - Physical standby database
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: DISABLED
Configuration Status:
SUCCESS

主备状态都正常了

接下来测试切换的时候又出现了问题

1
2
3
4
5
6
7
8
9
DGMGRL> SWITCHOVER TO anxinstd;
Performing switchover NOW, please wait...
Operation requires a connection to instance "anxinstd" on database "anxinstd"
Connecting to instance "anxinstd"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
connect to instance "anxinstd" of database "anxinstd"

官方文档中有如下描述：

1
2
3
4
5
After performing a switchover using DGMGRL, Data Guard requires a shutdown and startup of both the primary and standby databases. This issue can occur if any necessary entry is missing in the listener.ora file.
DGMGRL is unable to connect to the database after it has been stopped while performing the switchover

To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the listener of each instance. 
The value for the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain in the LISTENER.ORA file.

看一下StaticConnectIdentifier的值

1
2
3
4
DGMGRL> show database anxin StaticConnectIdentifier;
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxin_DGMGRL)(INSTANCE_NAME=anxin)(SERVER=DEDICATED)))'
DGMGRL> show database anxinstd StaticConnectIdentifier;
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=anxinstd_DGMGRL)(INSTANCE_NAME=anxinstd)(SERVER=DEDICATED)))'

均去连接一个db_unique_name_DGMGRL.db_domain格式的服务，那么需要在监听里静态注册一个db_unique_name_DGMGRL.db_domain的服务

主：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
xin =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
)
SID_LIST_xin=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=anxin)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=anxin)
)
(SID_DESC=
(GLOBAL_DBNAME=anxin_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=anxin)
)
)
)
ADR_BASE_LISTENER = /u01/app/oracle

备：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
xin =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
)
)
SID_LIST_xin=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=anxinstd)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=anxinstd)
)
(SID_DESC=
(GLOBAL_DBNAME=anxinstd_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=anxinstd)
)
)
)
ADR_BASE_LISTENER = /u01/app/oracle

再次测试，问题依旧，莫非和之前alert里的 Fatal NI connect error 12514报错有关？错误信息里的链接描述符均去请求一个db_unique_name_DGB.db_domain的服务，那么再依样添加到静态注册里试试

1
2
3
4
5
6
7
8
9
10
11
12
DGMGRL> switchover to anxinstd;
Performing switchover NOW, please wait...
Operation requires a connection to instance "anxinstd" on database "anxinstd"
Connecting to instance "anxinstd"...
Connected.
New primary database "anxinstd" is opening...
Operation requires startup of instance "anxin" on database "anxin"
Starting instance "anxin"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "anxinstd"

成功了，那么这个db_unique_name_DGB.db_domain的服务究竟是做什么用的呢？

{db_unique_name}_DGB.{db_domain}: This Service is used by the DMON-Processes to communicate between each other
DMON是一个用来管理broker的后台进程，这个进程负责与本地数据库以及远程数据库的DMON进程进行通讯（与远端数据库的DMON进程进行通讯的时候使用的是一个 动态注册 的service name “db_unique_name_DGB.db_domain”）

既然是动态注册，那缘何注册失败呢？
文档 ID 365314.1给出了答案：Database Will Not Register With Listener configured on IP instead of Hostname

将主备的{db_unique_name}_DGB.{db_domain}静态entry删掉，host采用hostname，重启监听测试，switchover成功。

由此可见监听配置里还是采用hostname为好，通过本次事件也解惑了萦绕我心头很久的问题，很多时候建库完毕，使用工具创建动态注册的监听，监听状态里会有很多XDB之类的服务，而我改成静态监听之后（每次都用IP）却没有了之前的自动注册的服务，可见这就是根本原因：Database Will Not Register With Listener configured on IP instead of Hostname

发表于 更新于 分类于

Question: I am getting the ORA-31693 error during a data pump export in RAC:

ORA-31693: Table data object "xxx" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/u01/expdp/dump_test.dmp" for write
ORA-19505: failed to identify file "/u01/expdp/dump_test.dmp"
ORA-27037: unable to obtain file status

What is the cause of the ORA-31693 error?

Answer: You can use the oerr utility to look-up the ORA-31693 error:

ORA-31617: unable to open dump file "xxx" for write

Cause: Export was unable to open the export file for writing. This message is usually followed by device messages from the operating system.

Action: Take appropriate action to restore the device.

The root cause of the ORA-31617 is likely in RAC is that this error occurs when parallel parameter is specified in using expdp in RAC databases and same physical directories does not exists in all nodes.

This is a bug in 11g and it is fixed in 11by setting the "cluster" parameter cluster=N :

发表于 更新于 分类于

做DG的切换测试，发现了一些有趣的小问题，寥作记录
v$archived_log中的fal字段意思如下：
Indicates whether the archive log was generated as the result of a FAL request (YES) or not (NO)

  1. 主库当时归档日志状态
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> select DEST_ID,NAME ,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,FAL,REGISTRAR,CREATOR from v$archived_log  order by 3;

   DEST_ID NAME                                                                              SEQUENCE# STA ARC APPLIED   DEL S FAL REGISTR CREATOR
---------- -------------------------------------------------------------------------------- ---------- --- --- --------- --- - --- ------- -------
         2 /u01/app/archive/1_74_903912515.arc                                                      74 NO  YES YES       NO  A YES RFS     ARCH
         2 /u01/app/archive/1_75_903912515.arc                                                      75 NO  YES YES       NO  A YES RFS     ARCH
         1 /u01/app/archive/1_76_903912515.arc                                                      76 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_77_903912515.arc                                                      77 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_78_903912515.arc                                                      78 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_79_903912515.arc                                                      79 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_80_903912515.arc                                                      80 NO  YES NO        NO  A NO  ARCH    ARCH
         2 min                                                                                      80 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_81_903912515.arc                                                      81 NO  YES NO        NO  A NO  ARCH    ARCH
         2 min                                                                                      81 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_82_903912515.arc                                                      82 NO  YES NO        NO  A NO  ARCH    ARCH
         2 min                                                                                      82 YES YES YES       NO  A NO  LGWR    LGWR
         1 /u01/app/archive/1_83_903912515.arc                                                      83 NO  YES NO        NO  A NO  ARCH    ARCH
         2 min                                                                                      83 YES YES YES       NO  A NO  LGWR    LGWR
         1 /u01/app/archive/1_84_903912515.arc                                                      84 NO  YES NO        NO  A NO  ARCH    ARCH
         2 min                                                                                      84 YES YES NO        NO  A NO  LGWR    LGWR
  1. 备库当时归档日志状态
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> select DEST_ID,NAME ,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,FAL,REGISTRAR,CREATOR from v$archived_log  order by 3;

   DEST_ID NAME                                                                                                  SEQUENCE# STA ARC APPLIED   DEL S FAL REGISTR CREATOR
---------- ---------------------------------------------------------------------------------------------------- ---------- --- --- --------- --- - --- ------- -------
         1 /u01/app/archive/1_72_903912515.arc                                                                          72 NO  YES YES       NO  A NO  ARCH    ARCH
         1 /u01/app/archive/1_73_903912515.arc                                                                          73 NO  YES YES       NO  A NO  ARCH    ARCH
         1 /u01/app/archive/1_74_903912515.arc                                                                          74 NO  YES YES       NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       74 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_75_903912515.arc                                                                          75 NO  YES YES       NO  A NO  FGRD    FGRD
         2 minstd                                                                                                       75 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_76_903912515.arc                                                                          76 NO  YES YES       NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       76 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_77_903912515.arc                                                                          77 NO  YES YES       NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       77 YES YES YES       NO  A NO  LGWR    LGWR
         2 minstd                                                                                                       78 YES YES NO        NO  A NO  LGWR    LGWR
         1 /u01/app/archive/1_78_903912515.arc                                                                          78 NO  YES YES       NO  A NO  ARCH    ARCH
         1 /u01/app/archive/1_79_903912515.arc                                                                          79 NO  YES YES       NO  A NO  RFS     FGRD
         2 minstd                                                                                                       79 YES YES NO        NO  A NO  FGRD    FGRD
         1 /u01/app/archive/1_80_903912515.arc                                                                          80 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_81_903912515.arc                                                                          81 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_82_903912515.arc                                                                          82 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_83_903912515.arc                                                                          83 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_84_903912515.arc                                                                          84 NO  YES IN-MEMORY NO  A NO  RFS     ARCH
  1. 此时进行切换
1
2
3
4
5
6
7
8
9
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Database altered.

主库的alert日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Tue Feb 16 14:55:15 2016
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 5441] (minstd)
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for potential Physical Standby switchover target to become synchronized...
Active, synchronized Physical Standby switchover target has been identified
Switchover End-Of-Redo Log thread 1 sequence 85 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x101743
ARCH: Noswitch archival of thread 1, sequence 85
ARCH: End-Of-Redo Branch archival of thread 1 sequence 85
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 85 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 17 added for thread 1 sequence 85 ID 0x97bd9e7c dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received alls redo
Final check for a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also received all redo

这时主库做了一次日志切换，加入EOR标记，并传输日志到所有备库，然后检查确认所有备库全部接受到所有的redo
这时查询备库

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 DEST_ID,NAME ,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,FAL,REGISTRAR,CREATOR from v$archived_log  order by 3;

   DEST_ID NAME                                                                                                  SEQUENCE# STA ARC APPLIED   DEL S FAL REGISTR CREATOR
---------- ---------------------------------------------------------------------------------------------------- ---------- --- --- --------- --- - --- ------- -------
         1 /u01/app/archive/1_72_903912515.arc                                                                          72 NO  YES YES       NO  A NO  ARCH    ARCH
         1 /u01/app/archive/1_73_903912515.arc                                                                          73 NO  YES YES       NO  A NO  ARCH    ARCH
         1 /u01/app/archive/1_74_903912515.arc                                                                          74 NO  YES YES       NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       74 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_75_903912515.arc                                                                          75 NO  YES YES       NO  A NO  FGRD    FGRD
         2 minstd                                                                                                       75 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_76_903912515.arc                                                                          76 NO  YES YES       NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       76 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_77_903912515.arc                                                                          77 NO  YES YES       NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       77 YES YES YES       NO  A NO  LGWR    LGWR
         2 minstd                                                                                                       78 YES YES NO        NO  A NO  LGWR    LGWR
         1 /u01/app/archive/1_78_903912515.arc                                                                          78 NO  YES YES       NO  A NO  ARCH    ARCH
         1 /u01/app/archive/1_79_903912515.arc                                                                          79 NO  YES YES       NO  A NO  RFS     FGRD
         2 minstd                                                                                                       79 YES YES NO        NO  A NO  FGRD    FGRD
         1 /u01/app/archive/1_80_903912515.arc                                                                          80 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_81_903912515.arc                                                                          81 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_82_903912515.arc                                                                          82 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_83_903912515.arc                                                                          83 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_84_903912515.arc                                                                          84 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_85_903912515.arc                                                                          85 NO  YES YES       NO  A NO  RFS     ARCH

85号日志确实已经收到并且应用，备库alert如下

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
Tue Feb 16 14:52:22 2016
Archived Log entry 19 added for thread 1 sequence 84 ID 0x97bd9e7c dest 1:
Media Recovery Waiting for thread 1 sequence 85 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 85 Reading mem 0
Mem# 0: /u01/app/oradata/min/stdb_redo01.log
Tue Feb 16 14:55:17 2016
RFS[5]: Assigned to RFS process 7837
RFS[5]: Selected log 4 for thread 1 sequence 85 dbid -1749202365 branch 903912515
Tue Feb 16 14:55:17 2016
Archived Log entry 20 added for thread 1 sequence 85 ID 0x97bd9e7c dest 1:
Tue Feb 16 14:55:17 2016
RFS[2]: Possible network disconnect with primary database
Tue Feb 16 14:55:17 2016
RFS[6]: Assigned to RFS process 7782
RFS[6]: Possible network disconnect with primary database
Tue Feb 16 14:55:17 2016
RFS[1]: Possible network disconnect with primary database
Tue Feb 16 14:55:17 2016
RFS[7]: Assigned to RFS process 7835
RFS[7]: Possible network disconnect with primary database
Tue Feb 16 14:55:17 2016
Resetting standby activation ID 2545786492 (0x97bd9e7c)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Media Recovery Waiting for thread 1 sequence 86

收到85号日志之后，与主库失去联系，在应用85号日志的时候遇到EOR标记
备库切换为主库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SQL> alter database open;

Database altered.

新主库alert

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
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (min)
Maximum wait for role transition is 15 minutes.
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Tue Feb 16 15:00:29 2016
SMON: disabling cache recovery
Backup controlfile written to trace file /home/oracle/app/oracle/diag/rdbms/min/min/trace/min_ora_7726.trc
SwitchOver after complete recovery through change 1054531
Online log /u01/app/oradata/min/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oradata/min/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oradata/min/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1054529
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 WITH SESSION SHUTDOWN
Tue Feb 16 15:00:42 2016
alter database open
Tue Feb 16 15:00:42 2016
Assigning activation ID 2545831887 (0x97be4fcf)
Thread 1 advanced to log sequence 87 (thread open)
Tue Feb 16 15:00:42 2016
ARC8: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 87
Current log# 2 seq# 87 mem# 0: /u01/app/oradata/min/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Feb 16 15:00:42 2016
ARC9: Becoming the 'no SRL' ARCH
Tue Feb 16 15:00:42 2016
ARCa: Becoming the 'no SRL' ARCH
Tue Feb 16 15:00:42 2016
SMON: enabling cache recovery
Archived Log entry 21 added for thread 1 sequence 86 ID 0x97be4fcf dest 1:
Tue Feb 16 15:00:42 2016
ARCt: Becoming the 'no SRL' ARCH
Tue Feb 16 15:00:42 2016
NSA2 started with pid=18, OS id=7850
[7726] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:86759444 end:86759474 diff:30 (0 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Starting background process SMCO
Tue Feb 16 15:00:42 2016
idle dispatcher 'D000' terminated, pid = (17, 1)
ARCt: Standby redo logfile selected for thread 1 sequence 86 for destination LOG_ARCHIVE_DEST_2
Tue Feb 16 15:00:42 2016
SMCO started with pid=50, OS id=7852
No Resource Manager plan active
Starting background process QMNC
Tue Feb 16 15:00:42 2016
QMNC started with pid=52, OS id=7856
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open
Tue Feb 16 15:00:43 2016
ARC0: Becoming the 'no SRL' ARCH
Tue Feb 16 15:00:43 2016
ARC1: Becoming the 'no SRL' ARCH
ARC0: Archive log rejected (thread 1 sequence 86) at host 'minstd'
FAL[server, ARC0]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance min - Archival Error. Archiver continuing.
Thread 1 advanced to log sequence 88 (LGWR switch)
Current log# 3 seq# 88 mem# 0: /u01/app/oradata/min/redo03.log
Tue Feb 16 15:00:45 2016
ARC2: Becoming the 'no SRL' ARCH
Archived Log entry 23 added for thread 1 sequence 87 ID 0x97be4fcf dest 1:
Tue Feb 16 15:00:45 2016
ARC3: Becoming the 'no SRL' ARCH
ARC3: Standby redo logfile selected for thread 1 sequence 87 for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 88 for destination LOG_ARCHIVE_DEST_2
Tue Feb 16 15:01:05 2016
ARCr: Becoming the 'no SRL' ARCH

新主库打开的时候向备库传输86号日志被拒绝，这里我猜测是一个gap检测的问题，至于为何报错，暂时没搞清楚，我觉得出现了FAL应该是备库检测到gap来请求日志的，但为何拒绝不甚清楚，而在新备库的日志却发现是接受成功的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Tue Feb 16 15:00:42 2016
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/archive
RFS[1]: Assigned to RFS process 5901
RFS[1]: Selected log 4 for thread 1 sequence 86 dbid -1749202365 branch 903912515
Tue Feb 16 15:00:42 2016
Archived Log entry 19 added for thread 1 sequence 86 ID 0x97be4fcf dest 1:
Tue Feb 16 15:00:45 2016
RFS[2]: Assigned to RFS process 5905
RFS[2]: Selected log 4 for thread 1 sequence 87 dbid -1749202365 branch 903912515
Tue Feb 16 15:00:45 2016
Archived Log entry 20 added for thread 1 sequence 87 ID 0x97be4fcf dest 1:
Tue Feb 16 15:00:45 2016
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Assigned to RFS process 5907
RFS[3]: Selected log 4 for thread 1 sequence 88 dbid -1749202365 branch 903912515
Tue Feb 16 15:01:36 2016
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect
Attempt to start background Managed Standby Recovery process (minstd)
Tue Feb 16 15:01:36 2016
MRP0 started with pid=55, OS id=5923
MRP0: Background Managed Standby Recovery process started (minstd)
started logmerger process

可见86，87已经接收到了
下一刻便开始正常传输了，主库日志信息如下：

1
2
3
4
5
6
7
8
9
Tue Feb 16 15:00:45 2016
ARC3: Becoming the 'no SRL' ARCH
ARC3: Standby redo logfile selected for thread 1 sequence 87 for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 88 for destination LOG_ARCHIVE_DEST_2
Tue Feb 16 15:01:05 2016
ARCr: Becoming the 'no SRL' ARCH

有一个归档目的地2激活的提示，难道是一开始未激活？
此时再来看一下归档日志信息

新主库：

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
SQL> select DEST_ID,NAME ,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,FAL,REGISTRAR,CREATOR from v$archived_log  order by 3;

   DEST_ID NAME                                                                                                  SEQUENCE# STA ARC APPLIED   DEL S FAL REGISTR CREATOR
---------- ---------------------------------------------------------------------------------------------------- ---------- --- --- --------- --- - --- ------- -------
         1 /u01/app/archive/1_72_903912515.arc                                                                          72 NO  YES YES       NO  A NO  ARCH    ARCH
         1 /u01/app/archive/1_73_903912515.arc                                                                          73 NO  YES YES       NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       74 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_74_903912515.arc                                                                          74 NO  YES YES       NO  A NO  ARCH    ARCH
         1 /u01/app/archive/1_75_903912515.arc                                                                          75 NO  YES YES       NO  A NO  FGRD    FGRD
         2 minstd                                                                                                       75 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_76_903912515.arc                                                                          76 NO  YES YES       NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       76 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_77_903912515.arc                                                                          77 NO  YES YES       NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       77 YES YES YES       NO  A NO  LGWR    LGWR
         2 minstd                                                                                                       78 YES YES YES       NO  A NO  LGWR    LGWR
         1 /u01/app/archive/1_78_903912515.arc                                                                          78 NO  YES YES       NO  A NO  ARCH    ARCH
         1 /u01/app/archive/1_79_903912515.arc                                                                          79 NO  YES YES       NO  A NO  RFS     FGRD
         2 minstd                                                                                                       79 YES YES YES       NO  A NO  FGRD    FGRD
         1 /u01/app/archive/1_80_903912515.arc                                                                          80 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_81_903912515.arc                                                                          81 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_82_903912515.arc                                                                          82 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_83_903912515.arc                                                                          83 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_84_903912515.arc                                                                          84 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_85_903912515.arc                                                                          85 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_86_903912515.arc                                                                          86 NO  YES NO        NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       86 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_87_903912515.arc                                                                          87 NO  YES NO        NO  A NO  ARCH    ARCH
         2 minstd                                                                                                       87 YES YES YES       NO  A YES ARCH    ARCH

24 rows selected.

备库归档目的地86，97两个日志的FAL都是yes，而且在新主库查看切换期间产生的84，85两个日志的applied状态为yes，而在新备库（原主库）查询84，85在新主库（原备库）的applied状态却为NO，很有意思的一个现象

新备库:

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
SQL> select DEST_ID,NAME ,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,FAL,REGISTRAR,CREATOR from v$archived_log  order by 3;

   DEST_ID NAME                                                          SEQUENCE# STA ARC APPLIED   DEL S FAL REGISTR CREATOR
---------- ------------------------------------------------------------ ---------- --- --- --------- --- - --- ------- -------
         2 /u01/app/archive/1_74_903912515.arc                                  74 NO  YES YES       NO  A YES RFS     ARCH
         2 /u01/app/archive/1_75_903912515.arc                                  75 NO  YES YES       NO  A YES RFS     ARCH
         1 /u01/app/archive/1_76_903912515.arc                                  76 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_77_903912515.arc                                  77 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_78_903912515.arc                                  78 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_79_903912515.arc                                  79 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_80_903912515.arc                                  80 NO  YES YES       NO  A NO  ARCH    ARCH
         2 min                                                                  80 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_81_903912515.arc                                  81 NO  YES YES       NO  A NO  ARCH    ARCH
         2 min                                                                  81 YES YES YES       NO  A YES ARCH    ARCH
         1 /u01/app/archive/1_82_903912515.arc                                  82 NO  YES YES       NO  A NO  ARCH    ARCH
         2 min                                                                  82 YES YES YES       NO  A NO  LGWR    LGWR
         1 /u01/app/archive/1_83_903912515.arc                                  83 NO  YES YES       NO  A NO  ARCH    ARCH
         2 min                                                                  83 YES YES YES       NO  A NO  LGWR    LGWR
         1 /u01/app/archive/1_84_903912515.arc                                  84 NO  YES YES       NO  A NO  ARCH    ARCH
         2 min                                                                  84 YES YES NO        NO  A NO  LGWR    LGWR
         1 /u01/app/archive/1_85_903912515.arc                                  85 NO  YES YES       NO  A NO  RFS     FGRD
         2 min                                                                  85 YES YES NO        NO  A NO  FGRD    FGRD
         1 /u01/app/archive/1_86_903912515.arc                                  86 NO  YES YES       NO  A NO  RFS     ARCH
         1 /u01/app/archive/1_87_903912515.arc                                  87 NO  YES YES       NO  A NO  RFS     ARCH

20 rows selected.

总结：

  1. switchover前后每个主库角色都会切换1次日志（本次实验为准，并不绝对）
  2. 新主库产生的前2个日志是以FAL方式传输到备库
  3. 在原主库查询switcover之前产生的两个日志的applied状态时为NO，而在新的主库（原备库）查询日志的应用状态是为YES的

关于本文中提到的FAL报错的问题，希望广大DBA朋友帮助解惑，如文中还有其他错误之处，还望批评指正

后记：在一套新的DG环境下测试切换，并未出现FAL报错的情况，怀疑是和当时备库的状态有关

发表于 更新于 分类于

公司一台数据库测试服务器，某天开始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%