分类目录归档：ORA-xxxxx

查询v$session报ORA-04031错误

发表于 2014 年 11 月 3 日 由 惜分飞

客户的数据库在出账期间有工具登录Oracle数据库偶尔性报ORA-04031,经过分析是因为该工具需要查询v$session,经过分析确定是Bug 12808696 – Shared pool memory leak of “hng: All sessi” memory (Doc ID 12808696.8),重现错误如下
节点1进行查询报ORA-4031

SQL> select count(*) from v$session; 

COUNT(*) 
---------- 
1536 

SQL> select count(*) from gv$session; 

COUNT(*) 
---------- 
2089 

SQL> select /*+ full(t) */ count(*) from gv$session t; 

COUNT(*) 
---------- 
2053 

SQL> select * from gv$session; 
select * from gv$session 
* 
ERROR at line 1: 
ORA-12801: error signaled in parallel query server PZ93, instance 
ocs_db_2:zjocs2 (2) 
ORA-04031: unable to allocate 308448 bytes of shared memory ("shared 
pool","unknown object","sga heap(1,0)","hng: All sessions data for API.")

节点2进行查询报ORA-04031

SQL> select * from gv$session; 
select * from gv$session 
* 
ERROR at line 1: 
ORA-12801: error signaled in parallel query server PZ95, instance 
ocs_db_2:zjocs2 (2) 
ORA-04031: unable to allocate 308448 bytes of shared memory ("shared 
pool","unknown object","sga heap(6,0)","hng: All sessions data for API.") 

SQL> select * from v$session; 
select * from v$session 
* 
ERROR at line 2: 
ORA-04031: unable to allocate 308448 bytes of shared memory ("shared 
pool","unknown object","sga heap(7,0)","hng: All sessions data for API.")

通过上述分析:确认是节点2的v$session遭遇到Bug 12808696，导致在该节点中中查询v$session和Gv$session报ORA-04031，而在节点1中查询v$session正常，查询Gv$session报ORA-04031.
bug-12808696
该bug在11.1.0.6中修复,所有的10g版本中未修复,只能通过临时重启来暂时避免,注意该bug通过flash shared_pool无法解决
如果您有权限可以进步一查询SR 3-7670890781: 查询v$session的BLOCKING_SESSION字段时，出现ora-04031错误

发表在 ORA-xxxxx | 标签为 , | 评论关闭

数据库启动报ORA-00704 ORA-39714错误解决

发表于 2014 年 10 月 25 日 由 惜分飞

数据库启动失败,报ORA-00704、ORA-39714错误

[oracle@www.xifenfei.com ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 7 08:15:35 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> startup
ORACLE instance started.

Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39714: upgrade script utlmmig.sql failed
Process ID: 11592
Session ID: 1 Serial number: 5


SQL> startup upgrade
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

alert日志报错

Thu Aug 07 07:42:25 2014
SMON: enabling cache recovery
Thu Aug 07 07:42:25 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_11592.trc:
ORA-39714: upgrade script utlmmig.sql failed
Thu Aug 07 07:42:25 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_11592.trc:
ORA-00704: bootstrap process failure
ORA-39714: upgrade script utlmmig.sql failed
Thu Aug 07 07:42:25 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_11592.trc:
ORA-00704: bootstrap process failure
ORA-39714: upgrade script utlmmig.sql failed
Thu Aug 07 07:42:25 2014
Error 704 happened during db open, shutting down database
USER (ospid: 11592): terminating the instance due to error 704

通过分析utlmmig.sql脚本知道,数据库在升级bootstrap$之前会先在props$表中插入BOOTSTRAP_UPGRADE_ERROR相关记录,数据库在启动之时会检测该值,如果发现该值存在,数据库只能以upgrade模式启动,清理掉相关记录,数据库即可正常启动

[oracle@www.xifenfei.com ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 7 07:42:44 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
Database opened.
SQL>   delete from props$ where name = 'BOOTSTRAP_UPGRADE_ERROR';

1 row deleted.

SQL>   delete from props$ where name = 'LOGMNR_BOOTSTRAP_UPGRADE_ERROR';

0 rows deleted.

SQL>   commit;

Commit complete.

SQL> 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
Database opened.
SQL>

数据库虽然正常启动成功,但是由于bootstrap$对象升级失败,后续还是有很大风险,建议分析报错原因,解决原因然后继续升级bootstrap$基表

发表在 ORA-xxxxx | 标签为 , , , | 评论关闭

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

发表于 2014 年 9 月 29 日 由 惜分飞

在昨天11.2.0.2 for Linux 数据库恢复过程中,把数据文件从asm复制到单节点机器中恢复,在resetlogs过程中报如下ORA-38856错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

ORA-38856 is the expected error during open database resetlogs when the set of enabled instances (redo threads) in the controlfile does not match the set of enabled instances (redo threads) in datafile checkpoint. This is expected behavior in a normal RAC restore/recover/open resetlogs situation.
这句话的意思是:数据库在resetlogs的时候发现控制文件中的redo threads和数据文件汇总的redo threads不一致,从而出现该问题.
在本次恢复中禁用了所有和thread 2相关参数,数据库依然报告错误,是因为数据库在异常恢复过程中需要读取节点2的redo信息,现在无法读取从而出现该错误.但是使用了_allow_resetlogs_corruption 之后还是报该错误,实在诡异.通过查询mos发现有类似Unpublished Bug 4355382 ORA-38856: FAILED TO OPEN DATABASE WITH RESETLOGS WHEN USING RAC BACKUP,虽然说该bug在10.2.0.3中修复,但是在异常恢复过程中,本着在风险可控的情况下,大胆尝试,继续使用_no_recovery_through_resetlogs,数据库正常resetlogs成功.
可以参考：RMAN Duplicate from RAC backup fails ORA-38856 (Doc ID 334899.1)

发表在 ORA-xxxxx | 标签为 , | 2 条评论