ITPub博客

http://www.orafaq.com/faqdbain.htm[@more@]Oracle Database Internals FAQ$Date: 26-Apr-2002 $$Revision: 1.02 $$Author: Frank Naudé $ WARNING: This FAQ is for informational purposes only. Check with Oracle Support before using the information below. What is the difference between locks, latches, enqueues and semaphores? Where can one get a list of all hidden Oracle parameters? What is a database EVENT and how does one set it? What database events can be set? How can one dump internal database stru

shutdown之前的好习惯[@more@]今天看到的OTN文档375935.1,讲的时候为什么shutdown immediate有时候会hang住。其中一个原因是因为在shutdown immediate前有大的transaction需要rollback，只能等待事务回滚完毕，数据库达到一致的状态，才能顺利shutdown.因此，shutdown数据库之前最好察看一下是否有大的transaction在运行。select sum(used_ublk) from v$transaction;如果没有心思等待而执行了shutdown abort，则在再次启动数据库时候，startup时要做rollback回滚操作，仍然需要很多时间。长短取决于要回滚的事务的大小。10g> select *

根据官方文档做了一下disksuite,记录一下!参考文档：http://docs.sun.com/app/docs/doc/806-3205?l=en[@more@]1、在VMware的Summary View界面选择Edit virtual machine settings，add一个新hard disk，选择IDE方式，确定添加后在Devices中会看到多出一块Hard Disk 2(IDE 0:1)。进入系统后，执行devfsadm命令，系统检测新增加的设备，这种方法不需要reboot，很好.2.由于metadb不能安装在已使用过的分区上,所以就要在第一块磁盘上化出部分空间做为放metadb的分区,一般是从swap分区中分出部分空间来.步骤大致如下: 2.1. 修改/etc/vfstab文件

Oracle常用dump命令，记录一下备查。[@more@]Oracle常用dump命令，记录一下备查。一.Memory Dumps1).Global AreaALTER SESSION SET EVENTS ‘immediate trace name global_area level n’;1 包含PGA2 包含SGA4 包含UGA8 包含indrect memory2).Library CacheALTER SESSION SET EVENTS ‘immediate trace name library_cache level n’;1 library cache统计信息2 包含hash table histogram3 包含object handle4 包含object结构（Heap 0）3).Row CacheALTER SESSION SET EVENTS ‘immediate trace name row_cache level n’;1 row cache统计信

看到warehouse的精华，做了点实验，记录一下。http://www.itpub.net/thread-1065138-1-1.html[@more@]checkpoint_change#记录在不同的地方,当checkpoint完成后,这几个地方的checkpoint_change#就会更新为一致,如出现不一致的情况下,就需要进行恢复,先看看哪些地方存储着checkpoint_change#SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';Session altered.SQL> set linesize 1000SQL> alter system checkpoint;System altered.--controlfile记录的checkpoint_change#SQL> select checkpoint_change# from

看深入浅出时做的一些实验，算是了解一个大概了吧。[@more@]Session 1:SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250

看深入浅出的一些疑问，希望以后能弄清楚。[@more@]我的测试库的db_cache_size为16MB,此时的_db_block_hash_buckets为4096,正好为block buffer的2倍(16*1024/8)*2.在oracle8i之前,该参数是db_block_buffers/4,而之后该参数缺省为db_block_buffers*2.SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProdPL/SQL Release 10.2.0.4.0 - ProductionCORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4

http://tolywang.itpub.net/post/48/455900这里只谈物理data guard的原理[@more@]一般来说，我们在谈到data guard的时候，都会用很简单的语言来描述整个data guard的运做流程。通常是这样的: primary database通过arch 进程，或者lgwr进程把archive log 送到standby database的archive_dest，然后standby database 通过RFS来接收这个archive log，然后MRP进程运用此log，也就是redo apply，使物理特征和primary database一模一样。这样讲清楚了吗？ 表面看起来很清楚，其实非常不清楚，歧义很多。歧义在哪里？ 那么我来问几个问题：1.arch

How to release space from database这篇文章比较详细，包括回收到HWM，以及如何降低HWM后再继续回收。[@more@]How to release space from database(Step 3-5 only applies to development/testing DB or in an offline production DB, because move table would destroy index.)当然就是resize datafile 了，下面稍微写了一下步骤 How to release space from database(Step 3-5 only applies to development/testing DB or in an offline production DB, because move table would destroy index.)1. Find which tablespace have much fr

通过试验对freelist,freelist group 以及手动管理段的管理有了初步的了解。[@more@]建立手动管理的表空间SQL> CREATE TABLESPACE eric DATAFILE '/u01/app/oradata/orcl/eric01.dbf' SIZE 10M AUTOEXTEND OFFEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1MBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT MANUAL; Tablespace created.分别建立不同的freelist和freelist group的三个表SQL> CREATE TABLE t_manualTABLESPACE ERICSTORAGE ( FREELISTS 1 FREELIST GROUPS 1 )as select * from dba_objec

一直以来以ASSM的概念很模糊，今天做了个实验，总算有点收获。[@more@]SQL> create tablespace eric 2 datafile '/u01/app/oradata/orcl/eric01.dbf' size 10m 3 extent management local uniform size 128k 4 segment space management auto;Tablespace created.SQL> select tablespace_name,extent_management,allocation_type,segment_space_management 2 from dba_tablespaces 3 where tablespace_name='ERIC';TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN------------------------------ ----------

看循序渐进和数据库性能优化时进行的测试，记录一下。[@more@]UNIFORM SIZE的测试SQL> CREATE TABLESPACE eric DATAFILE 2 '/u01/app/oradata/orcl/eric01.dbf' SIZE 10M AUTOEXTEND OFF 3 LOGGING 4 PERMANENT 5 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M 6 BLOCKSIZE 8K 7 SEGMENT SPACE MANAGEMENT MANUAL 8 FLASHBACK ON;Tablespace created.SQL> select tablespace_name,extent_management,allocation_type 2 from dba_tablespaces where tablespace_name='ERIC';TABLESPACE_NAME EXTENT_MAN

从9.2.0.1升级到10.2.0.4的过程，同事写的，记录一下。[@more@]1. Environment Setting：Solaris 10 + Oracle 9.2.0.1/9.2.0.4(SPARC 64-bit)2. DB Version：SQL>select version from v$instance;>=9.2.0.4 direct upgrade to 10.2.0.4.0；<9.2.0.4 of 9iR2, upgrade to 9.2.0.8 first, then upgrade to 10.2.0.4.03. Upgrade to 9.2.0.8：A). Preparation:a).Check pool size of SHARED_POOL_SIZE & JAVA_POOL_SIZE to minimum size 150M each;If not, verify it pfile;b). Shutdown DB and Take a full cold backupc). Shu

目的：将linux的数据库迁移到solaris上同时升级数据库原系统情况：OS：RHEL4 U4oracle:10.2.0.1IP：172.17.61.131 目标系统情况：OS：solaris 10oracle:10.2.0.2IP：172.17.61.130linux平台的版本是10.2.0.1,sun...

安装完成之后，首先配置ftp方便文件传输：RED HAT AS4 U4下gssftp服务的配置 1、在操作系统里，使用ntsysv命令查看是否已经开启gssftp服务；2、在/etc/xinetd.d的目录中，修改gssftp配置文件如下：service ftp{dis...

[oracle@rhel131 ~]$ expdp l5m/l5m directory=dpdata dumpfile=full.dmp full=y job_name=expfullExport: Release 10.2.0.1.0 - Production on Wednesday, 15 October, 2008 2:23:47Copyright (c) 2003, 2005, Or...

top 的%CPU总数大于100是为什么呢。top - 11:34:07 up 10 days,  2:35,  2 users,  load average: 2.35, 2.11, 1.59Tasks: 171 total,   4 running, 167 sleeping,  &...

$ exp mtrx/mtrx tables=BRAND_SPENDING_MM_MP file=mtrx.dmp log=mtrx.log statistics=none buffer=4096000 direct=y recordlength=65535Export: Release 10.2.0.4.0 - Production on Mon Oct 13 22:40:23 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character setAbout t

目的：将linux的测试表空间传到到solaris[@more@]源系统情况：OS：RHEL4 U4oracle:10.2.0.1IP：172.17.61.131目标系统情况：OS：solaris 10oracle:10.2.0.2IP：172.17.61.130linux平台下[oracle@rhel131 ~]$ sqlplus '/ as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 6 08:50:31 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> col platform_name for

在10.2.0.1+rhel4上碰到一个bug,导致在shutdown数据库时时间较长。[@more@]To implement the solution, execute the following steps::1. In $ORACLE_HOME/network/admin/listener.ora, of the database where the Metadata Repository is to be loaded, add the line:INBOUND_CONNECT_TIMEOUT_LISTENER = 02. In the Oracle Server 10g sqlnet.ora, add the line:SQLNET.INBOUND_CONNECT_TIMEOUT = 03. Stop and restart the database and the listener for the changes to take effect这是metalink上的：Applies to:Oracle Server - Ent