ITPub博客

什么是rba？[@more@]Redo Byte Address (RBA)Recent entries in the redo thread of an Oracle instance are addressed using a 3-part redo byte address, or RBA. An RBA is comprised of the log file sequence number (4 bytes) the log file block number (4 bytes) the byte offset into the block at which the redo record starts (2 bytes) RBAs are not necessarily unique within their thread, because the log file sequence number may be reset to 1 in all threads if a database is opened with the RESETLOGS option. R

oracle的x$表非常重要，记录一下，备查！[@more@][K]ernel Layer[2]-Phase Commit [G]lobal [T]ransaction [E]ntryX$K2GTE - Current 2PC tx X$K2GTE2 - Current 2PC tx [C]ache Layer [B]uffer Management Buffer [H]ash X$BH - Hash Table Buffer LRU Statistics X$KCBCBH - [C]urrent [B]uffers (buckets) - lru_statistics X$KCBRBH - [R]ecent [B]uffers (buckets) - lru_extendedBuffer [WAIT]s X$KCBWAIT - Waits by block class X$KCBFWAIT - Waits by File[W]orking Sets - 7.3 or higher X$KCBW

How Oracle Works[@more@]How Oracle WorksThe following example describes the most basic level of operations that Oracle performs. This illustrates an Oracle configuration where the user and associated server process are on separate computers (connected through a network). An instance has started on the computer running Oracle (often called the host or database server).A computer running an application (a local computer or client workstation) runs the application in a user process. The client appl

物理读的情况是比较复杂的：1、会受到参数db_file_multiblock_read_count的影响已经不用多少了，为了减少物理读，需要设置db_file_multiblock_read_count为最大临界值。doc上提到绝大多数系统是1m，那么db_file_multiblock_read_count=系统的(max I/O size)/db_block_size系统的(max I/O size)如何准确的获得，也是我的疑问2、db_cache_size如果很小的话，那么物理读通常都会显著的高，原因是一次读进来的block无法或者是很少能找到连续的memory来容纳，这样只能通过多次来读取，读进来的block只能离散的分布在memory中，这也正是scattere

网上很多地方写的编译bbed的命令是：make -f ins_rdbms.mk BBED=/opt/app/oracle/product/10.2.0/db_1/rdbms/lib/bbed /opt/app/oracle/product/10.2.0/db_1/bin/bbed可是总是编译不成功，在yxyup的帮助下总算编译成功了：make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed多谢yxyup!顺手记录下大致过程，进入bbed时需要输入default的password：blockedit[@more@][oracle@xys lib]$ ls ins*ins_rdbms.mk[oracle@xys lib]$ make -f ins_rdbms.mk BBED=/opt/app/oracle/product/10.2.0/db_1/rdbms/lib/bbed /opt/app/

insert操作放在undo中的rowid怎么来的！[@more@]SQL> create table t1(id int , name varchar2(10)) tablespace users pctfree 99;表已创建。SQL> insert into t1 values(1 , 'a');已创建 1 行。SQL> insert into t1 values(2 , 'b');已创建 1 行。SQL> insert into t1 values(3 , 'c');已创建 1 行。SQL> insert into t1 values(4 , 'd');已创建 1 行。SQL> insert into t1 values(5 , 'e');已创建 1 行。SQL> insert into t1 values(6 , 'f');已创建 1 行。SQL> insert into t1 values(7 , 'g');已创建 1 行。S

ora-01555模拟以及undo tbs的guarantee特性测试！[@more@]session 1:SQL> variable i refcursor;SQL> select *from t1; ID NAME---------- ---------- 3 c 2 test 4 d 1 undoSQL> exec open :i for select *from t1;PL/SQL 过程已成功完成。session 2:11:02:00 SQL> create undo tablespace undotbs3 datafile 'E:ORACLEPRODUCT10.2.0ORADATATESTundotbs3.dbf' size 1m;表空间已创建。11:14:48 SQL> alter system set undo_tablespace=undotbs3;系统已更改。11:15:04 SQL> alte

拷贝linux下冷备份db到win下测试过程[@more@]讲课的时候经常会有人问到是否可以把unix/linux下的数据库直接拷贝到win下用，或者相反，从未测试过，隐约的感觉到应该不可以，今天测试了一下，没有出现问题：测试过程是把linux下的db shutdown之后直接拷贝到win下发现没有问题：大致过程：--===================================1、首先确认linux下和win下v$transportable_platform中的ENDIAN_FORMAT都相同：这里都是LittleSQL> select *from v$transportable_platform;PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT--

通过$ORACLE_HOME/rdbm/admin/utlsyxsz.sql脚本可以估算当前sysaux中各主件对空间的使用情况.[@more@]SQL> @ E:oracleproduct10.2.0db_1RDBMSADMINutlsyxsz.sqlThis script estimates the space required for the SYSAUX tablespace.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Specify the Report File Name~~~~~~~~~~~~~~~~~~~~~~~~~~~~The default report file name is utlsyxsz.txt. To use this name,press <return> to continue, otherwise enter an alternative.Enter value for report_name:

使用emca可以手动配置em！配置em的过程中有一个环节要特别主要：不论使用dbca还是使用emca -deconfig dbcontrol db -repos drop在删除资料库时都有可能删除不干净，这样在再次创建资料库时都会出现错误（显示的log中有详细的记录），因此在创建资料库时最好使用下面命令手动删除一次：DROP public synonym SETEMVIEWUSERCONTEXT;DROP PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;drop role MGMT_USER;DROP USER MGMT_VIEW CASCADE;DROP USER SYSMAN CASCADE;[@more@]C:>emca -deconfig dbcontrol db -repos dropEMCA 开始于 2008-8-31 13:00

cache buffers chains和buffer busy waits模拟！引用kamus说的一段话：Cache Buffers Chains Latches用于保护内存中block的buffer header hash chain，任何对于处于某个hash chain中的block的操作，都要先获得Cache Buffers Chains Latches，所以多session频繁操作位于一个block中的数据，一定会造成Cache Buffers Chains Latches的等待，表现在v$sesssion_wait中就是一个latch free事件。当一个进程获得了cache buffers chains latch 之后，并且找到了自己需要操作的block的buffer header，这时候就需要获得这个buffer header 的锁以便于

1、利用dbstart和dbshut在开关机时自动启动和关闭db2、使用chkconfig增加服务，靠，叫服务准确嘛[@more@]1、编辑/etc/oratab文件TEST:/u01/app/oracle/product/9.2.0:Y (把原来的N改为Y)2、增加Linux服务(叫服务不一定准且，反正理解意思就行，名称为oradb10g)2.1 cd /etc/rc.d/init.d/touch oradb10g chmod a+x oracle9i2.2 vi oradb10g 并输入以下内容:[oracle@xys init.d]$ more oradb10g#more /etc/rc.d/init.d/oradb10g#!/bin/bash#whoami#root#chkconfig:345 58 42 这里注意如果db使用的raw，那么启动顺序一定要放在rawdevices被mou

1、编写启动db的脚本文件dbstart.shlsnrctl startsqlplus /nolog <<!connect sys/system@test as sysdbastartup!2、把该脚本文件加入/etc/rc.local中，加入以后rc.local内容如下：[oracle@xys oracle]$ more /etc/rc.localtouch /var/lock/subsys/localsu - oracle -c /home/oracle/dbstart.sh[@more@]办法应该比较多吧

特别需要主要的就是要清楚的知道各类文件（ctl，dbf，redo）文件头占用了几个block，当然还要知道各类文件他们的block的大小和单位！[@more@]下面文件raw_convert_fs.sql 中记录的是一个raw db到file系统db的大致过程！[oracle@xys orcl]$ more raw_convert_fs.sql SQL> !SQL> @ dbf.sqlFILE_NAME BYTES TABLESPACE BLOCKS BYTES+BYTES/BLOCKS ---------------- ---------- ---------- ---------- ------------------ /dev/raw/raw1 314572800 SYSTEM 38400 314580992 /dev/raw/raw3 157286400 UNDOTBS1 19200 157294592 /dev/raw/r

通过试验验证了一下。[@more@]SQL> insert into tt select * from dba_objects;已创建40231行。SQL> select ubafil,ubablk,start_ubablk,used_ublk from v$transaction; UBAFIL UBABLK START_UBABLK USED_UBLK---------- ---------- ------------ ---------- 2 183 99 19SQL> select segment_name,extent_id,file_id,block_id from dba_extents where segment_name='RBS1';SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID-------------------- ---------- ---------- --------

不容易理解的2个字段，Jonathan Lewis的解释http://www.dbforums.com/archive/index.php/t-1314288.html[@more@]Jonathan LewisThe slots in the transaction table in the undo segment headerare continuously being reused. Each time a slot it re-used,its sequence number (xidsqn) goes up.Similarly, undo blocks are re-used (though it takes moretime to get round to re-using them than transaction slots).Each time an undo block is "newed" and reused, itssequence number UBASQN goes up.

坦率的说这2个字段困扰了自己很久，每次看到的是doc的解释：CUREXTNUMBERCurrent extentCURBLKNUMBERCurrent block都不理解什么意思，每次想到的总是去dump这个block，而没有去dump undo segment header，所以一直也没搞清楚它的确切意思。[@more@]SQL> select count(*) from tt; COUNT(*)---------- 40190SQL> insert into tt select * from tt;已创建40190行。SQL> select usn,curext,curblk from v$rollstat where xacts=1; USN CUREXT CURBLK---------- ---------- ---------- 12 1

http://www.ixora.com.au/notes/cache_block_types.htm[@more@]http://www.ixora.com.au/notes/cache_block_types.htm

oracle的block其实是和os的扇区相对应！[@more@]之所以要说明oracle的block和os的扇区相对应，是因为oracle的doc上提到oracle的block是os的block的整数倍（DB_BLOCK_SIZE specifies (in bytes) the size of Oracle database blocks. Typical values are 4096 and 8192. The value of this parameter must be a multiple of the physical block size at the device level.），那么oracle中提到的os的block到底是指os的什么需要明确。oracle的block其实是和os的扇区相对应，和簇应该没有任何关系!os的扇区是os的最小io单位为什么这样说：1、

O7_DICTIONARY_ACCESSIBILITY是针对select any table权限控制是否可以访问data dictionary的![@more@]SQL> connect /as sysdba已连接。SQL> show parameter o7NAME TYPE VALUE------------------------------------ ----------- ------------------------------O7_DICTIONARY_ACCESSIBILITY boolean FALSESQL> select name from system_privilege_map where name like '%SELECT ANY TABLE%';NAME----------------------------------------SELECT ANY TABLESQL> grant se