Oracle dirty block some of the study (V)

2011-04-11  来源:本站原创  分类:Database  人气:74 

Through the above series of tests, we know that Oracle's dirty block, running out of memory when you brush, buffer cache to datafile block will cover the block.
If the database is rac, then what happens?
Consider the following scenario:
Assuming rac 2 nodes, 1 node generates dirty block, 1 be node process by lmns this dirty block transmission to the 2nd node, 2 nodes again modify dirty block.
Through the above operation, you can see the dirty block after the second amended and modified in the 1st node is less than the 2nd node change scn scn.
Note that in Oracle 10g rac environment, the Node 1 and Node 2, scn synchronization completed by GCS.
By default, the use of Broadcast-on-Commit Scheme, the decision by the max_commit_propagation_delay = 0. When a node starts at RAC alert log can be seen in the following information:
ALTER DATABASE OPEN
Picked broadcast on commit scheme to generate SCNs
Now not only have a question:
If the 2nd node of the dirty block ahead on the 1st node to refresh the dirty block to a data file, according to Oracle will flush dirty block to the data file characteristics,
1 node that will be refreshed when the dirty block will be stored in data files 2 nodes dirty block coverage. In this case, will result in data loss.
So we come to the following conjecture:
(1) dirty block can only exist in a number of nodes, that is on the 1st node dirty block transmission to the 2nd node, the node will be the 1st dirty block is removed from the dirty list,
Through this mechanism, Oracle ensures that only the global a dirty block. However, this mechanism brings the efficiency of transmission is relatively low.
(2) dirty block exist in multiple nodes, but node 2, the latest dirty block to be refreshed when the first node 1 refresh dirty block.
Through this mechanism, to ensure the data is not lost, but Oracle has to write two copies of dirty block, in a highly concurrent, high-load conditions, efficiency is bound to be very low.
Through experiments to solve the above questions;
First node to create dirty block 1
SQL> alter system flush buffer_cache;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> update zhoul.zhoul set name = 'active1' where obj # = 100;

1 row updated.

SQL> commit;

Commit complete.

Then in the 2nd node to create a dirty block

SQL> alter system flush buffer_cache;

System altered.

SQL> update zhoul.zhoul set name = 'active2' where obj # = 100;

1 row updated.

SQL> commit;

Commit complete.

Note that a good node in the 2 local mode checkpoint, in rac environment, perform alte system checkpoint, default is globle.
SQL> alter system checkpoint local;

System altered.

be node dump 2 latest redolog, noted for the 0x0000.0008de69 scn and time stamp 10:33:15
SQL> select member from v $ log a, v $ logfile b where a.group # = b.group # and a.status = 'CURRENT' and a.thread # = 2;

MEMBER
-------------------------------------------------- ------------------------------
/ Oradata/zhoul/redo2_01.log

SQL> alter system dump logfile '/ oradata/zhoul/redo2_01.log' layer 23 opcode 1;

System altered.

REDO RECORD - Thread: 2 RBA: 0x000003.00000004.0090 LEN: 0x0044 VLD: 0x02
SCN: 0x0000.0008de70 SUBSCN: 1 04/10/2011 10:33:15
CHANGE # 1 MEDIA RECOVERY MARKER SCN: 0x0000.00000000 SEQ: 0 OP: 23.1
Block Written - afn: 6 rdba: 0x0180000c BFT: (1024,25165836) non-BFT: (6,12)
scn: 0x0000.0008de69 seq: 0x01 flg: 0x06

be node redolog dump 1
SQL> select member from v $ log a, v $ logfile b where a.group # = b.group # and a.status = 'CURRENT' and a.thread # = 1;

MEMBER
-------------------------------------------------- ------------------------------
/ Oradata/zhoul/redo03.log

SQL> alter system dump logfile '/ oradata/zhoul/redo03.log' layer 23 opcode 1;

System altered.

be node dump 2 latest redolog, noted for the 0x0000.0008de59 scn and time stamp 10:33:14
REDO RECORD - Thread: 1 RBA: 0x000005.00000004.0010 LEN: 0x0070 VLD: 0x06
SCN: 0x0000.0008de70 SUBSCN: 1 04/10/2011 10:33:14
CHANGE # 1 MEDIA RECOVERY MARKER SCN: 0x0000.00000000 SEQ: 0 OP: 23.1
Block Written - afn: 6 rdba: 0x0180000c BFT: (1024,25165836) non-BFT: (6,12)
scn: 0x0000.0008de59 seq: 0x01 flg: 0x02

Through the above test can clearly know that when 2 nodes to brush out the latest dirty block, in order to ensure that data is not lost, the dirty block 1, node 2 will be the first node in the brush.
This mechanism tells us: do update the same object as the same node, if a different node, a side effect of not only dirty block 2 times a brush out inefficiencies, such as a block and a series of low pass efficiency.

相关文章
  • Oracle dirty block some of the study (V) 2011-04-11

    Through the above series of tests, we know that Oracle's dirty block, running out of memory when you brush, buffer cache to datafile block will cover the block. If the database is rac, then what happens? Consider the following scenario: Assuming rac

  • Oracle dirty block some of the study (a) 2011-04-10

    Today, just after bone pot, home is 21:30. Since yesterday and today for Oracle dirty block to do some testing to get some results. So want to record about the testing process, for later forget. Oracle dirty block refers to the buffer cache has chang

  • Oracle dirty block some of the study (II) 2011-04-10

    Continuing with the topic: Now discuss the second case: If the block in the buffer cache has changed (which means dirty block), perform alter system flush buffer_cache whether this block will be flushed to the data files? Still with bbed positioning

  • Oracle dirty block some of the study (IV) 2011-04-11

    Continue to explore the topic of Oracle dirty, the front has been used in the alter system flush_cache command. That alter system checkpoint and alter system flush buffer_cache What is the difference? A simple test you can see some clues. Open SQLPUS

  • ORA-01578: ORACLE data block corrupted one solution (transfer) 2011-03-11

    os: winxp db: 10g r2 Error: ORA-01578: ORACLE data block corrupted (file No. 6, Block No. 20) ORA-01110: data file 6: 'F: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ MOVO \ BLOCK.DBF' Block damage and recovery Database recovery is sometimes a very compli

  • ORA-01578: ORACLE data block corrupted solution 2011-09-01

    Error Description : Errors in file d:\app\administrator\diag\rdbms\ticket\ticket\trace\ticket_ora_46572.trc (incident=170565): ORA-01578: ORACLE Data block corrupted ( File number 6, Block No. 4621) ORA-01110: Data files 6: 'D:\APP\ADMINISTRATOR\ORAD

  • Oracle Data block of the physical structure 2010-03-04

    1. One of the block of the dump process: PHP code: SQL> create table t9 (a varchar (10)); Table created. SQL> insert into t9 values ('a'); 1 row created. SQL> commit; Commit complete. SQL> set serveroutput on SQL> exec show_space ('T9'); Fr

  • Notes of an AIX platform to store dirty block deal 2010-08-06

    Customer database problems, archived logs readable, showing as Quote $ Du-sm / arch du: / arch/1_418_722029122.dbf: path name of the file or directory does not exist. du: / arch/1_419_722029122.dbf: path name of the file, or directory does not exist.

  • oracle data block (block) Detailed structure 2010-12-23

    1: 20 bytes type: 0x06 = trans data defined in kcb.h frmt: 8i ~ 9i are 0x02 10.1.0 2k: 0x62 4k: 0x82 8k: 0xa2 16k: 0xc2 (logfile 0x22 512 bytes) spare1/2_kcbh: ub1 spare1_kcbh this field is no longer used (old inc #, now always 0) ub1 spare2_kcbh thi

  • Oracle: Enabling Block Change Tracking-10g New Features 2011-04-29

    Block chage tracking is a new feature in Oracle10g, Block change tracking process (CTWR) record since since the last level 0 backup of the data block changes, and the information recorded in the trace file. RMAN incremental backup using this file to

  • Summary of the concept of Oracle data block (original) 2011-02-22

    Oracle data within the logical storage Oracle stored data, the smallest particle size (finest level of granularity) is called the data block (data block) (also called logical block (logical block), Oracle block (Oracle block) or page (page)). A data

  • Research Oracle delayed block cleanout (defered block cleanout) 2011-06-14

    Scene One: Session an update of a small table, update the block number is less than db_block_buffers * 10%, when the session is one to commit, the update block is also present in the buffer cache. As the number of blocks updated less, Oracle uses SO

  • oracle commonly used commands a large aggregate (V) 2010-10-29

    Chapter XII: backup and recovery 1. V $ sga, v $ instance, v $ process, v $ bgprocess, v $ database, v $ datafile, v $ sgastat 2. Rman need set dbwr_io_slaves or backup_tape_io_slaves and large_pool_size 3. Monitoring parallel rollback > V $ fast_sta

  • Oracle Learning - block structure 2011-03-10

    PL / SQL procedure body can be divided into blocks, each block contains a PL / SQL any SQL statement. Typical PL / SQL block contains the following structure: [DECLARE declaration_statements ] BEGIN executable_statements [EXCEPTION exception_handling

  • oracle view state data block usage of v $ bh 2010-12-27

    1 Create a test table, test, and insert 10,000 rows of data; begin for i in 1 .. 10000 loop execute immediate 'insert into test values(:x)' using i; end loop; end; (2) create a stored procedure SHOW_SPACE: create or replace procedure show_space(p_seg

  • Oracle performance tuning study notes (E) - buffer Cache tuning A 2011-09-16

    buffer Cache tuning buffer cache hit ratio is not required to share pool hit rate so high. BD_BLOCK_SIZE: SGA in two queues: LRU lists: monitoring the buffer cache to use based on access frequency and duration of the order. Head for the most recent d

  • Oracle 10g study notes of popular database files 2011-09-30

    First, the parameter file and server parameter file parameter file (Parameter File) are usually referred to as the initial file (init file), or the init.ora file. The default name is init <ORACLE_SID>. Ora text file. SPFILE generate PFILE: create pf

  • oracle to dba_.user_.v $ _.all_.session_.index_ beginning in the common tables and views 2011-08-07

    the oracle to dba_, user_, v $ _, all_, session_, index_ commonly used tables and views beginning August 10, 2009 Monday 17:06 the oracle to dba_, user_, v $ _, all_, session_, index_ commonly used tables and views beginning dba_ beginning dba_users

  • oracle tbs segment.extent.block relationship 2010-12-31

    Oracle table space (tableSpace), section (segment), panel (extent), block (block), which are used to store Oracle database object allocation unit Section is a database object, it consumes storage space, such as tables, indexes, rollback segments, etc

  • Relationship in Oracle block.extent.segment 2011-03-14

    1, block: an Oracle data block stored in the smallest units, so the data are stored in a block in the final. It is also known as logic blocks or pages (pages). Each operating system has its own block size. And here the block is Oracle's own, differen