分类 Troubleshooting 中的文章

EXPDP Streams AQ: enqueue blocked on low memory

有一台11.2.0.4的库最近expdp导出非常慢,总是停留在99%以后还要持续2个多小时,还有另外一台同版本的数据库也碰到了类似的情况。 Job: SYS_EXPORT_SCHEMA_01 Owner: XXX Operation: EXPORT Creator Privs: TRUE GUID: 8A27A5FA09F142F9E055000000000001 Start Time: Mode: SCHEMA Instance: Max Parallelism: 4 EXPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND backup_user/******** dumpfile= COMPRESSION ALL State: EXECUTING Bytes Processed: 45,234,549,312 Percent Done: 99 Current Parallelism: 4 Job Error Count: 0 当前导出的对象都是一些空的分区,查询导出任务是否在等待 SQL> select dw.waiting_session, dw.holding_session, b.serial#, w.event, w.program wprogram, b.program bprogram, w.module wmod, b.module bmod, LOCK_ID1 from sys.dba_waiters dw, v$session w, v$session b where dw.waiting_session = w.sid and dw.holding_session = b.sid and (w.module like 'Data Pump%' or w.program like……

阅读全文

ORA-15001: diskgroup "FRA" does not exist or is not mounted

一台数据库在启动时候报错,提示一个asm group不存在 ORA-00210: cannot open the specified control file ORA-00202: control file: '+FRA/STBRAC/CONTROLFILE/current.294.1005908473' ORA-17503: ksfdopn:2 Failed to open file +FRA/STBRAC/CONTROLFILE/current.294.1005908473 ORA-15001: diskgroup "FRA" does not exist or is not mounted ORA-15040: diskgroup is incomplete ORA-205 signalled during: alter database mount... WARNING: failed to open a disk[/dev/asmdiskd] ORA-15025: could not open disk "/dev/asmdiskd" ORA-27041: unable to open file Linux-x86_64 Error: 13: Permission denied 检查asm的磁盘等信息均正常 [grid@stbracnode1 ~]$ asmcmd ASMCMD> lsdg State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 512 4096 4194304 51200 16784 0 16784 0 Y CRS/ MOUNTED EXTERN N 512 512 4096 4194304 61440 55336 0 55336 0 N DATA/ MOUNTED EXTERN N 512 512 4096 4194304 20480 17404 0 17404 0 N FRA/ [grid@stbracnode1 ~]$ ll /dev/asm* brw-rw---- 1 grid asmadmin 8, 16 Apr 18 13:22 /dev/asmdiskb brw-rw---- 1 grid……

阅读全文

ORA-20200: Database/Instance does not exist in AWR_PDB_DATABASE_INSTANCE

当我在12.2中通过pdb登录生成awr时报错 [oracle@xb admin]$ sqlplus sys/oracle@pdb12c as sysdba sys@PDB12C> @?/rdbms/admin/awrsqrpt.sql Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: Type Specified: html Specify the location of AWR Data ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ AWR_ROOT - Use AWR data from root (default) AWR_PDB - Use AWR data from PDB Enter value for awr_location: AWR_PDB Location of AWR Data Specified: AWR_PDB >>>>====这里选择的AWR_PDB declare * ERROR at line 1: ORA-20200: Database/Instance 3393322654/1 does not exist in AWR_PDB_DATABASE_INSTANCE ORA-06512: at line 27 但是如果选择AWR_ROOT时则正常 Location of AWR Data Specified: AWR_ROOT >>>>==== ... Current Instance ~~~~~~~~~~~~~~~~……

阅读全文

oracle无法启动ORA-27154,ORA-27300,ORA-27301,ORA-27302

发现一台实例突然无法启动,版本11.2.0.4 SQL> startup nomount ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semget failed with status: 28 ORA-27301: OS failure message: No space left on device ORA-27302: failure occurred at: sskgpcreates 看报错信息大概是sskgpcreates创建失败,资源不足。这个主要是在数据库启动时根据process参数设置的值去分配信号资源,如果内核参数设置的不合理,则会导致报错。 在Linux里,信号量主要是用来控制特定进程的利用率。它是一个可共享的资源,分别由P……

阅读全文

分析一则enq: HW – contention问题

同事在做压力测试的时候,说数据库很慢,于是登上数据库看了下,发现大量的enq: TX - row lock contention和enq: HW - contention事件。 %This SECONDS AAS WAIT_CHAIN ------ ---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------ 18% 960 1.6 -> ON CPU 9% 470 .8 -> enq: TX - row lock contention -> ON CPU 8% 460 .8 -> enq: HW - contention -> ON CPU 8% 410 .7 -> enq: TX - row lock contention -> enq: HW - contention -> ON CPU 5% 260 .4 -> enq: TX - row lock contention -> enq: HW - contention -> enq: HW - contention 4% 220 .4 -> enq: HW - contention -> enq: HW - contention 4% 210 .4 -> enq: HW - contention -> enq: HW - contention -> ON CPU 3% 190……

阅读全文

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

在做一个rman异机恢复完成后,数据库无法打开,报错提示 Oracle Error: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '+DG/xxx/datafile/system01.dbf' 用的源库一个rman 0级备份以及后来的部分归档日志,拷贝到这个测试环境,恢复完controlfile之后,进行了基于scn的recover,scn则是restore之后控制文件里显示的scn号 recover database until scn 75238614094; 尝试open时报错 16:54:07 sys. >alter database open resetlogs; alter database open resetlogs * ERROR at line 1:……

阅读全文

Oracle 11g 清理SYSAUX表空间

收到告警一个11g的库sysaux表空间不足,上去检查看到使用率达到了95%,所以接下来就说下如何处理这种情况。 SELECT Upper(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99') || '%' "使用比", F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大……

阅读全文

ORA-16086: standby database does not contain available standby log files

早上来检查告警邮件,一套10.2.0.5的dg主库一直在报ora-16086错误,根据错误提示大概是备库没有可用的standby log files。在11g以后,这个错误名称换成了ORA-16086 Redo data cannot be written to the standby redo log 16086, 0000, "standby database does not contain available standby log files" // *Cause: The primary database is in "no data loss" mode, but the standby // database does not contain any "standby log files". // *Action: Add one or more standby log files to the standby database. // This can be done while the standby database is mounted. 当前主备同步依旧正常,切换日志也……

阅读全文

ORA-04031一例 ("shared pool","…","SQLA","tmp")

今天突然收到告警,一台11.2.0.4的数据库报错ORA-04031。 SYS@> show sga ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","SELECT DECODE(null,'','Total...","SQLA","tmp") 根据错误代码可以判断是共享池使用出了问题 [oracle@xxx trace]$ oerr ora 4031 04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")" // *Cause: More shared memory is needed than was allocated in the shared // pool or Streams pool. // *Action: If the shared pool is out of memory, either use the // DBMS_SHARED_POOL package to pin large packages, // reduce your use of shared memory, or increase the amount of // available shared memory by increasing the value of the // initialization parameters SHARED_POOL_RESERVED_SIZE and // SHARED_POOL_SIZE. // If the large pool is out of memory, increase the initialization // parameter LARGE_POOL_SIZE. // If the error is issued from an……

阅读全文

MMON进程未自动重启导致awr未生成

问题现象 今天在生成一套rac的awr报告时,发现只有一号节点的数据,这是一套11.2.0.4的rac。 sys@TFDW1> select * from Dba_Hist_Snapshot where instance_number=2; 未选定行 查看mmon进程信息,发现二号节点并没有这个进程 [root@tfdw2 ~]# ps -ef|grep mmon grid 38361 1 0 Feb24 ? 01:30:51 asm_mmon_+ASM2 root 126466 126429 0 16:04 pts/1 00:00:00 grep mmon 而一号节点是正常的 [root@tfdw1 ~]# ps -ef|grep mmon grid 37050 1 0 Dec10 ? 00:03:04 asm_mmon_+ASM1 oracle 53834 1 0 Dec10 ? 00:12:44 ora_mmon_tfdw1 root 79038 42730 0 16:04 pts/0 00:00:00 grep mmon 要知道mmon进程属于可以被pmon respawned的进程之一,……

阅读全文