|
|||||
|
我们在大的事务失败时往往面临长时间的回滚，在回滚期间表会被加以TM-3 SX sub-exclusive锁，此时一般我们是无法针对表实施DDL操作的。长时间的大事务回滚可能耗尽我们的耐心，不过我们还是有办法预估何时回滚能够完成的，参考中的脚本<Script:when transaction will finish rollback>中的脚本，注意该脚本需要访问x$ktuxe内部视图，所以需要以sysdba身份方能执行。 SQL> select * from v$lock where type in (‘TM’,'TX’); ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ——– ——– ———- — ———- ———- ———- ———- ———- ———- 0ED0F30C 0ED0F33C 9 TM 13865 0 3 0 3757 0 2C3975FC 2C39763C 9 TX 65557 677 6 0 3757 . . . → Read More: 监控一个大事务的回滚 sqlplus应当是DBA 1.0时代使用最为频繁的管理工具，经常有经验丰富的老DBA会提到自己敲过几万次的sqlplus:)，但有的时候这个吃饭家伙也会不好用，偶尔还会出现Segmentation fault错误，亦或者彻底hang住。在这里我介绍几种应对sqlplus无法正常使用的应对方法: 1.出现Segmentation fault，这种情况下一般是sqlplus 2进制文件被损坏了，可以通过重新build一个sqlplus来解决问题 [oracle@rh2 bin]$ sqlplus Segmentation fault /* 使用$ORACLE_HOME/sqlplus/lib目录下的make文件，编译一个新的sqlplus */ [oracle@rh2 ~]$ make -f $ORACLE_HOME/sqlplus/lib/ins_sqlplus.mk newsqlplus Linking /s01/oracle/product/11.2.0/dbhome_1/sqlplus/bin/sqlplus rm -f /s01/oracle/product/11.2.0/dbhome_1/sqlplus/bin/sqlplus gcc -o /s01/oracle/product/11.2.0/dbhome_1/sqlplus/bin/sqlplus -m64 -L/s01/oracle/product/11.2.0/dbhome_1/sqlplus/lib/ -L/s01/oracle/product/11.2.0/dbhome_1/lib/ -L/s01/oracle/product/11.2.0/dbhome_1/lib/stubs/ /s01/oracle/product/11.2.0/dbhome_1/sqlplus/lib/s0afimai.o -lsqlplus -lclntsh `cat /s01/oracle/product/11.2.0/dbhome_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /s01/oracle/product/11.2.0/dbhome_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lztkg11 -lclient11 . . . → Read More: 解决sqlplus的segmentation fault或hang问题 一套Linux x86-64上的11.2.0.2数据库在使用data pump数据泵expdp工具导出时遭遇了ORA-31623错误，详细日志如下: [oracle@rh2 dbs]$ expdp system/D2nb51tz dumpfile=s01:sh.dmp schemas=sh Export: Release 11.2.0.2.0 – Production on Mon May 2 22:46:57 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining . . . → Read More: 解决UDE-31623错误一例 一套Linux x86-64上的11.2.0.2数据库在startup启动阶段遭遇了ORA-27103:internal error内部错误，其出错日志如下: SQL> startup nomount; ORA-27103: internal error Linux-x86_64 Error: 2: No such file or directory Additional information: 9404423 Additional information: 2 oerr 27103 Usage: oerr facility error Facility is identified by the prefix string in the error message. For example, if you get ORA-7300, “ora” is the facility and “7300″ is the error. . . . → Read More: 解决ORA-27103:internal error错误一例 一套11.1.0.7上的Oracle Application Object Library应用程序，在收集schema统计信息时出现了ORA-20001错误，具体错误日志如下: SQL> exec fnd_stats.gather_schema_statistics(‘AP’); PL/SQL procedure successfully completed. SQL> show error No errors. ============================================ Concurrent request error Log ———————————— **Starts**14-APR-2011 02:20:53 **Ends**14-APR-2011 04:40:43 ORA-0000: normal, successful completion +—————————————————————————+ Start of log messages from FND_FILE +—————————————————————————+ In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP stats on table AQ$_WF_CONTROL_P is . . . → Read More: ORA-20001错误一例 前2天有客户报一套10.2.0.3的数据库个别服务进程PGA使用量暴涨，疑似内存泄露(memory leak)；遂提供on-site service，赶到用户现场时问题进程已经消失，系统内存使用量恢复正常，客户之前除了保留了v$process动态性能视图的信息外未抓取其他有用的诊断信息。 查看保存的v$process视图信息可以看到进程991714的PGA内存使用量达到13个G： select spid,program,PGA_USED_MEM,PGA_ALLOC_MEM from v$process; SPID PROGRAM PGA_USED_MEM PGA_ALLOC_MEM ———————— ———————————————— ———— ————- 991714 oracleBTS@oam_app_a 14427510986 14432001786 oracle@oam_app_a@/oracle/product/10.2.0/dbs $ ulimit -a time(seconds) unlimited file(blocks) unlimited data(kbytes) unlimited stack(kbytes) 4194304 memory(kbytes) unlimited coredump(blocks) unlimited nofiles(descriptors) unlimited SQL> select x.ksppinm name,y.ksppstvl value 2 from sys.x$ksppi x, sys.x$ksppcv y 3 where x.inst_id=USERENV(‘Instance’) 4 and y.inst_id . . . → Read More: Probe how does your PGA consume 下午在客户office遭遇了ORA-07274错误，那是一套AIX 5.3上10.2.0.4的2节点RAC系统，乍看到7274还以为是ORA-7445的孪生兄弟，心中暗想10.2.0.4的RAC问题还真是不少: Errors in file /s01/oracle/product/10.2.0/db_1/rdbms/log/prod21_psp0_4272.trc: ORA-07274: spdcr: access error, access to oracle denied. No such file or directory Mon Apr 18 17:05:44 2011 PSP0: terminating instance due to error 7274 Mon Apr 18 17:05:44 2011 Errors in file /s01/oracle/product/10.2.0/db_1/rdbms/log/prod21_lms1_4285.trc: ORA-07274: spdcr: access error, access to oracle denied. Mon Apr 18 17:05:44 2011 Errors in . . . → Read More: ORA-07274 spdcr:access error错误一例 一套Power AIX上的9.2.0.1系统在数据库打开过程中遇到ORA-00600:[2667]内部错误，详细日志如下: Wed Mar 9 19:03:38 2011 RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 117699122479 Resetting resetlogs activation ID 2197911857 (0×83017931) Wed Mar 9 19:03:47 2011 LGWR: Primary database is in CLUSTER CONSISTENT mode Assigning activation ID . . . → Read More: Oracle内部错误ORA-00600:[2667]一例 |
|
||||
|
Copyright © 2012 Oracle Clinic – Maclean Liu的个人技术博客 - All Rights Reserved |
|||||
最新评论