Maclean’s Oracle Database Tech Blog Archives

脚本正文: set linesize 120 pagesize 66 col c1 for a9 col c1 heading “OS User” col c2 for a9 col c2 heading “Oracle User” col b1 for a9 col b1 heading “Unix PID” col b2 for 9999 justify left col b2 heading “SID” col b3 for 99999 justify left col b3 heading “SERIAL#” col sql_text…

针对字典管理临时表空间: select (s.tot_used_blocks/f.total_blocks)*100 as “percent used” from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name=’TEMP’) s, (select sum(blocks) total_blocks from dba_data_files where tablespace_name=’TEMP’) f; 针对本地管理表空间: select (s.tot_used_blocks/f.total_blocks)*100 as “percent used” from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name=’TEMP’) s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name=’TEMP’) f; 示例输出: select (s.tot_used_blocks/f.total_blocks)*100 as “percent used” 2 from (select…

本文永久地址：https://www.askmac.cn/archives/oracle-8i-备份恢复（recovery-manager）.html 说明 Recovery Manager（RMAN）是从Oracle8 R8.0之后追加的管理oracle数据库备份/储存/恢复的工具。在此，我将解说oracle8i的RMAN中扩展的新功能。   RMAN基本功能的扩展 媒介管理软件相关功能的扩展 OPS相关功能的扩展 在此对于RMAN命令的详细语法方面的内容，请参考《Oracle8i备份、恢复指南》   RMAN基本功能的扩展 1-1.复制备份set 考虑到Oracle的备份时，类似于归档日志以及控制文件的案例，某个备份受损可能造成毁灭性的故障。Oracle8i的RMAN中，可以一次制成多个类似的的备份。因此，在存储时某个备份无法使用的话，用于其他备份就OK了。 为了制成多个备份，需要通过RMAN命令的SET DUPLEX命令来指定复制数，执行备份。SET DUPLEX命令如下所示。   SET DUPLEX = {ON|OFF|1|2|3|4}；   关键词ON意味着指定为2 。DUPLEX最多可以制成4个拷贝。以下是使用SET DUPLEX命令的范例。 RUN { SET DUPLEX =2; ALLOCATE CHANNEL ch1 TYPE ‘sbt_tape’; BACKUP FORMAT ‘arc_%s_%p_%c’ FILESPERSET 1 ARCHIVELOG ALL DELETE INPUT; RELEASE CHANNEL ch1; } SET DUPLEX命令需要比分配channel（ALLOCATE CHANNEL命令）更早执行。另外，执行SET DUPLEX命令后，在分配到的所有channel中执行备份复制。 在制成tape装置的备份是，需要指定初始化参数BACKUP_TAPE_IO_SLAVES =…

RBO基于规则的优化器access paths优先级: RBO Path 1: Single Row by Rowid RBO Path 2: Single Row by Cluster Join RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key RBO Path 4: Single Row by Unique or Primary Key RBO Path 5: Clustered Join RBO Path 6: Hash Cluster Key RBO Path 7:…

NOTE: This article was originally created for version 7.3. Conceptually the ideas presented are the same as in later versions but implementation details may have changed or have been enhanced. Histograms ========== For uniformly distributed data, the cost-based approach makes fairly accurate guesses at the cost of executing a particular statement. However, when the data…

Sometime we’d like to trigger ORA-00600/7445 internal errors for testing purpose, But it’s not easy if you don’t know a little trick like below: declare a exception; pragma exception_init(a,-600); begin raise a; end; declare * ERROR at line 1: ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [],…

某用户windows 2003上的数据库由于存储故障导致系统表空间system.dbf出现IO问题，当打开数据库OPEN database时报错：     ORA-01115: IO error reading block from file 15 ORA-01110: data file … ORA-27091: unable to queue I/O ORA-27070: async read/write failed OSD-04006: ReadFile() failure, unable to read from file O/S-Error: (OS 121) The semaphore timeout period has expired. 以上ORA-01115、ORA-01110、ORA-27091、ORA-27070、OSD-04006、O/S-Error 这堆报错本质与Oracle数据库层面没有关系，问题的根本原因是Windows上对应磁盘驱动器下的文件无法读取出来，这可能是OS bug 也可能就是对应磁盘出现了坏道或其他物理故障，所以对于该问题有限考虑在OS层面解决文件的读取问题， 如果确实发现无法从OS层面或从备份解决，那么可以考虑特殊的恢复手段。‘   如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复! 诗檀软件专业数据库修复团队 服务热线 ： 13764045638 QQ号:47079569…

该脚本可以用于显示活跃的分布式事务(Distributed Transactions from dblink)，可以协助诊断dblink远程事务：   REM distri.sql column origin format a13 column GTXID format a35 column LSESSION format a10 column s format a1 column waiting format a15 Select /*+ ORDERED */ substr(s.ksusemnm,1,10)||’-‘|| substr(s.ksusepid,1,10) “ORIGIN”, substr(g.K2GTITID_ORA,1,35) “GTXID”, substr(s.indx,1,4)||’.’|| substr(s.ksuseser,1,5) “LSESSION” , substr(decode(bitand(ksuseidl,11), 1,’ACTIVE’, 0, decode(bitand(ksuseflg,4096),0,’INACTIVE’,’CACHED’), 2,’SNIPED’, 3,’SNIPED’, ‘KILLED’),1,1) “S”, substr(event,1,10) “WAITING” from x$k2gte g, x$ktcxb t,…

ORA-07445:  [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x5A1113A] [ADDR:0xFFFFFFFFFFFFFFFF] [UNABLE_TO_READ]一般是Windows平台上常见的内存不足问题引起，在64 bit 或 32bit 平台均可能发生，一般建议通过增加SGA_MAX_SIZE和SGA_TARGET(ASMM)来解决该问题，同时增加SHARED_POOL_SIZE、 JAVA_POOL_SIZE、 STREAMS_POOL_SIZE到200M以上。 也可能是由于Windows平台感染了计算机病毒引起的，建议同时也要查杀病毒。   ORA-07445 [ACCESS_VIOLATION] [unable_to_trans_pc] on Windows Platforms Applies to: Oracle Server – Enterprise Edition – Version: 10.2.0.3 and later [Release: 10.2 and later ] Information in this document applies to any platform. ***Checked for relevance on 06-Oct-2011*** RDBMS 9.2 or greater on Windows platforms…

Solaris平台上的Oracle数据库，Alert日志偶尔会出现”Network Interface No Longer Operational”的相关记录： ospid 11223: network interface with IP address 192.4.1.22 no longer operational requested interface 192.4.1.22 ioctl get mtu. Check output from ifconfig command 该错误一般是由Solaris操作系统Bug 6546482引起的，该错误一般可以忽略。