提供综合数据库运维服务与优化方案(不限Oracle MySQL PG GaussDB GoldenDB OceanBase等), 微信/Tel:(+86)134-365-60330
数据库oracle
A few days ago a friend ask me to see a awr issue. In the TOP 5 Times Events, as you see, Obviously,database has relatively high sbtbackup wait event, Backup:sbtbackup falls under the administrative category also realted to the RMAN. These wait events will occur when there is RMAN backup running on the server. Backup: … Read more
因一次硬件原因导致了服务器异常停机，防止蝴蝶效应及时进行了数据迁移，一周发现了这个决定正确的。 申请了2小时的停机时间。因数据不大且表空间开始使用不规范决定用expdp数据泵方式迁移。 在新设备 1, raid10+安装OS 2，安装oracle 10.2.0.1(single instance)+ 3，升级oracle 10.2.0.5 4，建库, 5, 调整INSTANCE参数,调整temp tbs、redo file size、undo tbs大小 6，创建app schema、创建data\index tablespace 用时1小时左右 stop web app+挂通知 stop listener sys@TOPBOX>select object_type,count(*) cnt from dba_objects where owner=’TOPBOX’ GROUP BY OBJECT_TYPE; OBJECT_TYPE CNT ——————- ——————– SEQUENCE 802 PROCEDURE 25 DATABASE LINK 2 LOB 3 TRIGGER 3 MATERIALIZED VIEW 2 TABLE … Read more
It all started in January 2005 with Critical Patch Updates (CPU). Then Patch Set Updates (PSU) were added as cumulative patches that included priority fixes as well as security fixes.
Creates a backup directory if required, works out the ASM SID, renames the previous entry, then loops round backing up the metadata data for each diskgroup. It also lists each file backed up which is quite helpful. Finally it creates error checking and a log and error file outputs [grid@rac1 ~]$ cat asm_metadata_bk.sh #!/bin/sh # … Read more
系统监控对于严重紧急的问题发短信通知，利用中国移动的飞信就可以做到免费给飞信好友发送sms短信,飞信机器人就是调用飞信的接口，可以命令行下调用。我们恰好可以利用这点。
This wait event occurs when we are trying to access a buffer in the buffer cache but we find that the buffer is currently being read from disk by another user so we need to wait for that to complete before we can access it.
客户要求导出大量的数据(约800万记录)，开始为了格式上的好看生成的是ACCESS数据库文件，用Toad 11导出成生ACCESS，本地域名网的DB，生成700W生成的文件有1G多点，都用了约三个小时，更悲惨的是三个小时后，弹出的一个错误窗口，提示无空间，莫名其妙，硬盘空间很充足，当时的心情可真是”唯有长江水无语东流…” 变通一下800万的记录在ACCESS也没有可读性，无非是数据导入，所以考虑生成flat file,文本文件的形式。 想到了老楼的小工具sqluldr，free命令行操作方便。 下面是真实的操作，早知如此何必加那三小时班。 [oracle@dev-db ~]$ ./sqluldr2 user=icme6/icme6 query=”select /*+parallel(t 8)*/* from sdscore t” file=sd_score.txt field=0x09 record=0x0d0x0a 0 rows exported at 2013-04-12 19:59:18, size 0 MB. 1000000 rows exported at 2013-04-12 19:59:21, size 200 MB. 2000000 rows exported at 2013-04-12 19:59:24, size 320 MB. 3000000 rows exported at 2013-04-12 19:59:27, size 420 MB. … Read more
这两天在NFS上配置一套RAC玩，实在没有好的机器就是三台PC，内存2G，每个PC还单块物理网卡，不过最终安装成功。下面记录安装过程中的一些小问题。
The log file sync wait occurs at the end of a transaction (COMMIT or End Of Job)and the database writer (DBWR) process must wait for the log file to synchronize with the database.
This latch comes into play when user processes are attempting to access or update the cached data dictionary values.
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [qkssao2s1], [], [], [], [], [], [], []
sql中如果使用了parallel hint 或是force parallel query 想并行查询的特性，如果那样做了就一定能保证 query optimizer选择并行的执行计划么？知道那样做实际上产生什么样的影响么？
ITL(Interested Transaction List ),The Oracle database engine locks the data modified by a transaction at the row level. To implement this feature every data block contains a list of all transactions that are modifying it. This list is commonly called interested transaction list (ITL).
Technically there is no schema level flashback feature available. and flashback database is a binary operation, the bits and bytes of the flashed back data are identical to what they used to be, it is not a ‘logical’ flashback, it is a physical flashback. However, provided your recovery period does not include any data dictionary … Read more
Every once in awhile it is useful to find out which sessions are using a database link in an Oracle database. It’s one of those things that you may not need very often, but when you do need it, it is usually rather important. Yong Huang includes this script on his website, and notes that … Read more
创建索引可以方便的给DB带来查询上的性能提升，但是DML时index的维护代价也是昂贵的。合理的创建索引，删除不必要的索引。
You can get the correct size of PGA using V$PGA_TARGET_ADVICE, dynamic performance view.
In some cases the query optimizer might can not accurately estimate cardinality (e.g. group by having or like with bind variables cause etc..),therefore the Cardinality estimates are based on default seletivity of build-in oracle coredefault seletivity(5%).
Oracle BULK COLLECT and Oracle FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance. The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is … Read more
exp 时注意保证三处的字符集一致，对于本例的US7ASCII(10g) DMP 导入 ZHS16GBK DB (11g),采用了修改dmp文件中的两处字符集编号，最终解决了字符集乱码问题。