来到杭州快1年了，不知道干什么，在不同数据库和代码之间徘徊，好久没有写blog了。备战相关技术，准备对某些数据库技术个系列出来。
print("hellow db");
db.isMaster();use I_S;
MYSQL5.7 INNODB引擎参数解析(三)
9、半同步复制参数说明
master上：
rpl_semi_sync_master_enabled=on表示master上已经开启了半同步复制模式
rpl_semi_sync_master_timeout=1000：该参数默认为1000毫秒，即10秒，此参可动态修改。主库在某次事务中等待事件超过10秒，则降级为异步模式，
不再等待slave从库，如果主库再次探测到slave从库恢复，则自动再次回到半同步复制模式
rpl_semi_sync_master_wait_no_slave:表示是否允许master每个事务提交后都要等待slave的接收确认信息，默认为no，每个事务都要。off，则slave追赶上后，再不会开启semi sync。需要手工启动
rpl_semi_sync_master_trace_level=32：表示用于开启半同步复制模式的调试级别，默认为32
rpl_semi_sync_master_wait_point :默认为AFTER_SYNC
AFTER_COMMIT,master写事务到binlog落地磁盘，并且发送给slave端。主库等待从库写到自己的relay-log中确认信息。在接收确认信息后，master端把事务写到存储引擎里并把相应结果反馈给客户端，客户端将在那时进行处理。
AFTER_SYNC,主库把每一个事务写到binlog并落盘，并且发给从库，并把事务写到存储引擎中，master再等待从库写relay-log里确认信息。在接到确认信息后，主库把相应结果反馈给客户端，客户端将在那时处理
slave上：
rpl_semi_sync_slave_enabled=on:表示slave开启semi sync
rpl_semi_sync_slave_trace_level=32：semi sync模式的调试级别
mysql5.7需要安装plugin，一般情况下次插件已在plugin目录下
mysql> show variables like '%rpl_semi%'; Empty set (0.01 sec) mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.01 sec) mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%rpl_semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +-------------------------------------------+------------+ 8 rows in set (0.00 sec)
半同步的状态信息，这里没有开启
mysql> show status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_status | OFF | +--------------------------------------------+-------+ 15 rows in set (0.01 sec)
Rpl_semi_sync_master_clients:说明有多少个slave配置了semi sync
Rpl_semi_sync_master_yes_tx：说明slave确认的成功提交数量
Rpl_semi_sync_master_no_tx：说明slave确认的不成功的数量
11gR2 buffer busy waits
11gR2 buffer busy waits
The buffer we want is either being read into the buffer cache by another process, or it is in an incompatible mode and is being modified. Normally the wait time is one second,
except in the case if this is the second wait for an exclusive block, in which case the wait is three seconds. This event is the indication for data block contention, the same buffer is requested by more than one process at any given time. Reducing waits on this event depends on the block type (data, segment header, undo）
1.query object identifier
SELECT row_wait_obj#,p1,p2,p3 FROM V$SESSION WHERE EVENT = 'buffer busy waits'; select p1,p2,p3 from v$session_wait_history where event='buffer busy waits';
2.find the segment using file# and block# ,header of file and block
SELECT owner, segment_name, file_id, block_id starting_block_id, block_id + blocks ending_block_id, blocks FROM dba_extents WHERE file_id = &file_num AND ( block_id <= &block_id AND (&block_id < (block_id + blocks))); select owner,segment_name,header_block,header_file from dba_segments where segment_name=$seg_name;
3.The action required depends on the class of block contended for and the actual segment.
(1).segment header
If the contention is on the segment header, then this is most likely free list contention.
If possible, switch from manual space management to automatic segment-space management (ASSM).
SELECT SEGMENT_NAME, FREELISTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = segment name AND SEGMENT_TYPE = segment type;
(2).data block
Problem: Too many rows in each block
Solution 1: Reduce the number of rows by changing pctfree/pctused
Solution 2: Reduce DB_BLOCK_SIZE
Problem: Right-hand indexes causing inserts into the same block
Solution: Use reverse key indexes or rebuild the index
(3).undo header
Problem: Too many transactions per rollback segment
Solution 1: Add more rollback segments Solution 2: Reduce TRANSACTIONS_PER_ROLLBACK_SEGMENT
(4).undo block
If you are not using automatic undo management, then consider making rollback segment sizes larger.
P3 parameter detail informations before ORACLE 10gR1:
– A modification is happening on a SCUR or XCUR buffer but has not yet completed.
0 The block is being read into the buffer cache.
100 We want to NEW the block, but the block is currently being read by another session (most likely for undo).
110 We want the CURRENT block either shared or exclusive but the block is being read into cache by another session, so we have to wait until its read() is completed.
120 We want to get the block in current mode, but someone else is currently reading it into the cache. Wait for the user to complete the read. This occurs during buffer lookup.
130 Block is being read by another session, and no other suitable block image was found, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefore it will read the CR version of the block.
200 We want to NEW the block, but someone else is using the current copy, so we have to wait for that user to finish.
210 The session wants the block in SCUR or XCUR mode. If this is a buffer exchange or the session is in discrete TX mode, the session waits for the first time and the second time escalates the block as a deadlock, so does not show up as waiting very long. In this case, the statistic: "exchange deadlocks" is incremented, and we yield the CPU for the "buffer deadlock" wait event.
220 During buffer lookup for a CURRENT copy of a buffer, we have found the buffer but someone holds it in an incompatible mode, so we have to wait.
230 Trying to get a buffer in CR/CRX mode, but a modification has started on the buffer that has not yet been completed.
231 CR/CRX scan found the CURRENT block, but a modification has started on the buffer that has not yet been completed.
ORACLE 11gR2 DATAGURAD MRP0: Background Media Recovery terminated with error 1274
出问题的是dataguard备库，原因很直接明了就是一个参数设置的不对所致。因为primary添加数据文件。备库参数STANDBY_FILE_MANAGEMENT是manual方式，导致无法在备库自动创建文件。
因为主库和备库的文件路径是不一样的。是通过了convert参数做了转换动作，所以无法创建是正常的。这里我们要知道怎么处理.相关信息来自oracle11gR2官方文档，实际案例已做特殊化处理:
alert日志如下：
Media Recovery Log /u02/backup/1_2587_909679324.dbf
Mon Jul 11 04:16:41 2016
File #87 added to control file as ‘UNNAMED00087’ because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log /u02/backup/1_2587_909679324.dbf
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/oracle/diag/rdbms/trsendb/trsen3/trace/trsen3_pr00_5356.trc:
ORA-01274: cannot add datafile ‘+DATA/trsenpr/trsenidx06.dbf’ – file could not be created
Mon Jul 11 04:18:04 2016
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 15190297611 but controlfile could be ahead of datafiles.
Mon Jul 11 04:18:06 2016
MRP0: Background Media Recovery process shutdown (trsen3)
Mon Jul 11 10:28:02 2016
alter database recover managed standby database cancel
ORA-16136 signalled during: alter database recover managed standby database cancel…
alter database activate physical standby database
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (trsen3)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Beginning Standby Crash Recovery.
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Standby Crash Recovery aborted due to error 1111.
Errors in file /u01/app/oracle/diag/rdbms/trsendb/trsen3/trace/trsen3_ora_7475.trc:
ORA-01111: name for data file 87 is unknown – rename to correct file
ORA-01110: data file 87: ‘/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00087’
ORA-01157: cannot identify/lock data file 87 – see DBWR trace file
ORA-01111: name for data file 87 is unknown – rename to correct file
ORA-01110: data file 87: ‘/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00087’
Completed Standby Crash Recovery.
ORA-1157 signalled during: alter database activate physical standby database…
alter database open
Beginning Standby Crash Recovery.
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Standby Crash Recovery aborted due to error 1111.
Errors in file /u01/app/oracle/diag/rdbms/trsendb/trsen3/trace/trsen3_ora_7475.trc:
ORA-01111: name for data file 87 is unknown – rename to correct file
ORA-01110: data file 87: ‘/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00087’
ORA-01157: cannot identify/lock data file 87 – see DBWR trace file
ORA-01111: name for data file 87 is unknown – rename to correct file
ORA-01110: data file 87: ‘/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00087’
Completed Standby Crash Recovery.
Errors in file /u01/app/oracle/diag/rdbms/trsendb/trsen3/trace/trsen3_ora_7475.trc:
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 87 – see DBWR trace file
ORA-01111: name for data file 87 is unknown – rename to correct file
ORA-01110: data file 87: ‘/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00087’
ORA-10458 signalled during: alter database open…
处理方式如下：
SQL> alter system set standby_file_management=manual scope=both;
System altered.
SQL> alter database create datafile ‘/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00087’ as ‘/u02/oradata/hpaypre3/rctlidx06.dbf’;
Database altered.
SQL> alter system set standby_file_management=auto scope=both;
System altered.
–继续应用日志
alter database recover managed standby database disconnect from session;
–查看信息
select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
alter database create datafile语句的限制:
You cannot create a new file based on the first data file of the SYSTEM tablespace.
You cannot specify the autoextend_clause of datafile_tempfile_spec in this CREATE DATAFILE clause
注意:
If you omit the AS clause entirely, then Oracle Database creates the new file with the same name and size as the file specified by filename or filenumber.
During recovery, all archived redo logs written to since the original data file was created must be applied to the new, empty version of the lost data file.
Oracle Database creates the new file in the same state as the old file when it was created.
You must perform media recovery on the new file to return it to the state of the old file at the time it was lost.
扩展:
注意:看到报错这种报错，可能会立刻想到recover里一个语句就是alter database rename file…..to …。但是这个语句是有前提的就是
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 87 – see DBWR trace file
ORA-01111: name for data file 87 is unknown – rename to correct file
ORA-01110: data file 87: ‘/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00087’
ORA-10458 signalled during: alter database open…
限制条件:
1、Use the RENAME FILE clause to rename data files, temp files, or redo log file members.
You must create each filename using the conventions for filenames on your operating system before specifying this clause.
2、To use this clause for a data file or temp file, the database must be mounted.
The database can also be open, but the data file or temp file being renamed must be offline.
In addition, you must first rename the file on the file system to the new name.
2、To use this clause for logfiles, the database must be mounted but not open.
3、If you have enabled block change tracking, then you can use this clause to rename the block change tracking file.
The database must be mounted but not open when you rename the block change tracking file.
注意事项:
This clause renames only files in the control file. It does not actually rename them on your operating system.
The operating system files continue to exist, but Oracle Database no longer uses them.