分类目录归档：数据库

在数据库open过程中常遇到ORA-01555汇总

发表于 2015 年 7 月 3 日 由 惜分飞

在数据库open的过程中，select ctime, mtime, stime from obj$ where obj# = :1语句报ORA-01555错误,数据库无法正常open
一般情况下会报某个回滚段,但是这里ORA-01555: snapshot too old: rollback segment number 0 with name “SYSTEM” too small这里直接报了system(系统回滚段),属于少见情况

Fri Jun 26 11:47:31 2015
SMON: enabling cache recovery
Fri Jun 26 11:47:31 2015
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0b41.37629378):
Fri Jun 26 11:47:31 2015
select ctime, mtime, stime from obj$ where obj# = :1
Fri Jun 26 11:47:31 2015
Errors in file /orabin/admin/doocrm/udump/doocrm_ora_5046722.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 5046722
ORA-1092 signalled during: alter database open resetlogs...

在数据库open的过程中,select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1语句上报ORA-01555,导致数据库open失败
ORA-01555


在数据库open过程中,select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1语句导致数据库open失败.

ARC0: Becoming the 'no SRL' ARCH
Sun Jun 28 16:08:22 2015
ARC1: Becoming the heartbeat ARCH
Sun Jun 28 16:08:22 2015
SMON: enabling cache recovery
Sun Jun 28 16:08:22 2015
ORA-01555 caused by SQL statement below (SQL ID: 7bd391hat42zk, Query Duration=0 sec, SCN: 0x0d27.0a1ce29d):
Sun Jun 28 16:08:22 2015
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,
   DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
Sun Jun 28 16:08:22 2015
Errors in file /oracle/app/oracle/admin/ibsscrm/udump/xxxx_ora_30212428.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 30212428
ORA-1092 signalled during: alter database open...

出现这类问题,一般是由于obj$,seq$,undo$等基表上对象scn大于数据库当前scn或者是由于这些表上有事务未提交,出现上述两种情况,数据库需要找对应的undo的回滚段中记录,而此时对应的回滚段异常(或者是由于redo未进行正常前滚,导致上述对象或者回滚段记录不正常),从而出现类似情况,一般出现此类情况,可以通过10046定位到block，然后故障原因采用bbed修改scn或者bbed提交事务来解决此类问题.
最近两年的恢复中又遇到一些ora-00704 ora-00604 ora-01555的错误,导致数据库无法正常open,对其进行补充,具体参见:数据库open过程遭遇ORA-1555对应sql语句补充

发表在 Oracle | 标签为 , , , , , | 评论关闭

误修改/u01权限/所有者的故障恢复

发表于 2015 年 7 月 1 日 由 惜分飞

有朋友找到我，说他对生产库做了误操作，导致数据库异常，请我帮忙处理,对/u01目录修改了用户和权限,导致数据库无法登录,但是业务还在继续
误操作命令

mkdir -p /u01/app/grid
mkdir -p /u01/app/11.2.0/grid
chown -R grid:oinstall /u01
mkdir -p /u01/app/oracle
chown oracle:oinstall /u01/app/oracle
chmod -R 775 /u01

尝试sqlplus登录数据库报ORA-12547

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

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 1 17:40:42 2015

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

ERROR:
ORA-12547: TNS:lost contact


Enter user-name: 


ERROR:
ORA-12547: TNS:lost contact


Enter user-name: 

ERROR:
ORA-12547: TNS:lost contact


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

数据库alert日志报错

Wed Jul 01 18:03:22 2015
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_28977.trc  (incident=129553):
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1100], [1101], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_129553/xifenfei_ora_28977_i129553.trc
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_129553/xifenfei_ora_28977_i129553.trc:
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1100], [1101], [], [], [], [], [], [], [], [], []
Wed Jul 01 18:03:22 2015
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_28979.trc  (incident=129561):
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1100], [1101], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_129561/xifenfei_ora_28979_i129561.trc
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_129561/xifenfei_ora_28979_i129561.trc:
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1100], [1101], [], [], [], [], [], [], [], [], []
Wed Jul 01 18:03:22 2015
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_28985.trc  (incident=129569):
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1100], [1101], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_129569/xifenfei_ora_28985_i129569.trc
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_129569/xifenfei_ora_28985_i129569.trc:
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1100], [1101], [], [], [], [], [], [], [], [], []
Wed Jul 01 18:03:22 2015
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_28983.trc  (incident=129577):
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1100], [1101], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_129577/xifenfei_ora_28983_i129577.trc
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_129577/xifenfei_ora_28983_i129577.trc:
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1100], [1101], [], [], [], [], [], [], [], [], []

trace文件信息

Dump file /u01/app/oracle/diag/rdbms/hybris01/hybris01/incident/incdir_129577/hybris01_ora_28983_i129577.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:	Linux
Node name:	dpppdridbo00
Release:	2.6.32-431.17.1.el6.x86_64
Version:	#1 SMP Wed May 7 23:32:49 UTC 2014
Machine:	x86_64
Instance name: hybris01
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
Unix process pid: 28983, image: oracle@dpppdridbo00


*** 2015-07-01 18:03:22.296
Dump continued from file: /u01/app/oracle/diag/rdbms/hybris01/hybris01/trace/hybris01_ora_28983.trc
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1100], [1101], [], [], [], [], [], [], [], [], []

========= Dump for incident 129577 (ORA 600 [spstp: ORACLE_HOME uid does not match euid]) ========

*** 2015-07-01 18:03:22.297
dbkedDefDump(): Starting incident default dumps (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [1100], [1101], [], [], [], [], [], [], [], [], []
----- SQL Statement (None) -----
Current SQL information unavailable - no SGA.

相关用户名相关信息

[oracle@dpppdridbo00 incdir_129577]$ id grid
uid=1100(grid) gid=1000(oinstall) groups=1000(oinstall),1100(asmadmin),1300(asmdba),1301(asmoper)
[oracle@dpppdridbo00 incdir_129577]$ id oracle
uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1200(dba),1300(asmdba)

这里比较明显，数据库的oracle_home,的所有者id为1101，但是被修改为了1100

查看相关目录文件权限

[oracle@www.xifenfei.com ~]$ env|grep ORA
ORACLE_SID=xifenfei
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@www.xifenfei.com ~]$ cd /u01/app/oracle/product/11.2.0/db_1
[oracle@www.xifenfei.com db_1]$ ls -ltr
total 308
-rwxrwxr-x.  1 grid oinstall    63 Mar  1 16:39 oraInst.loc
drwxrwxr-x.  8 grid oinstall  4096 Mar  1 16:40 assistants
drwxrwxr-x.  6 grid oinstall  4096 Mar  1 16:40 crs
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:40 csmig
drwxrwxr-x.  7 grid oinstall  4096 Mar  1 16:40 cv
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:40 demo
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:40 diagnostics
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:40 has
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:40 emcli
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:40 dv
drwxrwxr-x.  8 grid oinstall  4096 Mar  1 16:40 ide
drwxrwxr-x.  8 grid oinstall  4096 Mar  1 16:40 javavm
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:40 jdev
drwxrwxr-x.  6 grid oinstall  4096 Mar  1 16:40 md
drwxrwxr-x.  6 grid oinstall  4096 Mar  1 16:40 nls
drwxrwxr-x.  6 grid oinstall  4096 Mar  1 16:40 odbc
drwxrwxr-x.  5 grid oinstall  4096 Mar  1 16:40 olap
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:40 oracore
drwxrwxr-x.  8 grid oinstall  4096 Mar  1 16:40 ord
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:40 owm
drwxrwxr-x.  7 grid oinstall  4096 Mar  1 16:40 precomp
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:40 scheduler
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:40 relnotes
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:40 slax
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:40 sqlj
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:40 wwg
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:40 usm
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:40 uix
drwxrwxr-x.  6 grid oinstall  4096 Mar  1 16:40 plsql
drwxrwxr-x.  2 grid oinstall  4096 Mar  1 16:40 utl
drwxrwxr-x.  7 grid oinstall  4096 Mar  1 16:40 xdk
drwxrwxr-x.  2 grid oinstall  4096 Mar  1 16:40 instantclient
drwxrwxr-x. 20 grid oinstall  4096 Mar  1 16:40 oc4j
drwxrwxr-x.  2 grid oinstall  4096 Mar  1 16:40 timingframework
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:41 clone
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:41 j2ee
drwxrwxr-x.  8 grid oinstall  4096 Mar  1 16:41 apex
drwxrwxr-x. 13 grid oinstall  4096 Mar  1 16:41 sqldeveloper
drwxrwxr-x.  2 grid oinstall  4096 Mar  1 16:41 jlib
drwxrwxr-x.  2 grid oinstall  4096 Mar  1 16:41 dc_ocm
drwxrwxr-x.  6 grid oinstall  4096 Mar  1 16:41 jdk
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:41 jdbc
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:41 ucp
drwxrwxr-x.  7 grid oinstall  4096 Mar  1 16:41 OPatch
drwxrwxr-x.  7 grid oinstall  4096 Mar  1 16:41 ccr
drwxrwxr-x. 26 grid oinstall  4096 Mar  1 16:41 owb
drwxrwxr-x.  6 grid oinstall  4096 Mar  1 16:41 mgw
drwxrwxr-x.  7 grid oinstall  4096 Mar  1 16:41 opmn
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:41 cdata
drwxrwxr-x.  6 grid oinstall  4096 Mar  1 16:41 css
drwxrwxr-x.  2 grid oinstall  4096 Mar  1 16:41 mesg
drwxrwxr-x.  2 grid oinstall  4096 Mar  1 16:41 config
drwxrwxr-x.  3 grid oinstall  4096 Mar  1 16:41 EMStage
drwxrwxr-x. 12 grid oinstall  4096 Mar  1 16:41 ldap
drwxrwxr-x. 15 grid oinstall  4096 Mar  1 16:41 sysman
drwxrwxr-x.  9 grid oinstall  4096 Mar  1 16:41 srvm
drwxrwxr-x.  7 grid oinstall  4096 Mar  1 16:41 racg
drwxrwxr-x. 10 grid oinstall  4096 Mar  1 16:41 ctx
drwxrwxr-x.  7 grid oinstall  4096 Mar  1 16:42 sqlplus
drwxrwxr-x.  8 grid oinstall  4096 Mar  1 16:42 oui
drwxrwxr-x.  4 grid oinstall 12288 Mar  1 16:42 lib
drwxrwxr-x.  5 grid oinstall  4096 Mar  1 16:42 perl
drwxrwxr-x. 11 grid oinstall  4096 Mar  1 16:42 network
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:42 deinstall
drwxrwxr-x.  5 grid oinstall  4096 Mar  1 16:42 hs
-rwxrwxr-x.  1 grid oinstall   494 Mar  1 16:42 root.sh
drwxrwxr-x. 13 grid oinstall  4096 Mar  1 16:42 rdbms
drwxrwxr-x. 13 grid oinstall  4096 Mar  1 16:42 inventory
drwxrwxr-x.  4 grid oinstall  4096 Mar  1 16:42 cfgtoollogs
drwxrwxr-x.  7 grid oinstall  4096 Mar  1 16:44 install
drwxrwxr-x.  2 grid oinstall 12288 Mar  1 16:44 bin
drwxrwxr-x.  4 grid oinstall  4096 Mar  4 18:49 log
drwxrwxr-x.  2 grid oinstall  4096 Jun 30 22:31 dbs

处理方法

root用户
chown -R oracle:oinstall /u01
chown oracle:oinstall /u01/app/oracle

oracle用户
chmod 6751 $ORACLE_HOME/bin/oracle

这里的修改权限,为了保证业务运行正常,是尽量往大的方向修改的,如果条件允许,在后期有条件的情况下,建议重新安装oracle软件

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

ORA-00600[kjhn_post_ha_alert0-862]原因分析

发表于 2015 年 6 月 23 日 由 惜分飞

数据库版本和平台信息
数据库版本为10.2.0.1版本,而且是32位的win 2003 sp2之上

ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 2
CPU                 : 2 - type 586, 1 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:2608M/3990M, Ph+PgF:4511M/5871M, VA:1242M/2047M
Instance name: orcl

数据库报大量ORA-600[kjhn_post_ha_alert0-862]错误
数据库的mmon进程报大量ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862], [], [], [], [], [], [], []错误

Wed Jun 03 21:50:40 2015
Restarting dead background process MMON
MMON started with pid=11, OS id=3804
Wed Jun 03 21:50:43 2015
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mmon_3804.trc:
ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862], [], [], [], [], [], [], []

Wed Jun 03 21:50:49 2015
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mmon_3804.trc:
ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862], [], [], [], [], [], [], []

Wed Jun 03 21:55:44 2015
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mmon_3804.trc:
ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862], [], [], [], [], [], [], []

Wed Jun 03 21:55:49 2015
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mmon_3804.trc:
ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862], [], [], [], [], [], [], []

Wed Jun 03 22:00:40 2015
Thread 1 advanced to log sequence 476
  Current log# 1 seq# 476 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Wed Jun 03 22:00:44 2015
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mmon_3804.trc:
ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862], [], [], [], [], [], [], []

查询对应trace文件发现

ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862], [], [], [] , [], [], [], [] 
Current SQL statement for this session: 
BEGIN :success := dbms_ha_alerts_prvt.check_ha_resources; END;

人工执行该过程

SQL> var success varchar2
SQL> begin
  2  :success := sys.dbms_ha_alerts_prvt.check_ha_resources;
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL> print success
 
SUCCESS                                                                       
  
--------------------------------                                              
  
N

通过查询相关资料得到如下说明

@ This check is triggered with FAN enabled at this instance and it seems to be 
@ associated with a startup action. From the procedure itself which is called 
@ this is a run-once MMON (startup) action which supports instance down 
@ notification reliability. It does the folowing a) registers the current 
@ instance incarnation in recent_resource_incarnations$ if it's not already 
@ there b) deletes recent_resource_incarnations$ records that don't apply to 
@ this database. They may, e.g., have been copied from seed db or from a former 
@ DataGuard primary c) scans recent_resource_incarnations$ for instance 
@ incarnations that are no longer alive, and submits instance down alerts for 
@ them . If all is good then return 'Y' else 'N' (or error) if there is a 
@ failure. That failure is to get back to MMON, so that it may retry this 
@ action later. In the local instance I get a 'Y' but in the customer's system 
@ it fails with a 'N' which seems related to the ORA-600 assert.

@ This function is kjhn_post_ha_alert0() which is internal and does the real work of 
@ posting HA alerts. It is used by both kjhn_post_ha_alert and 
@ kjn_post_ha_alert_plsql. Its parameters are basically the same as those of 
@ kjhn_post_ha_alert,other than the fact that it uses individual parameters 
@ rather than the more easily extensible structure. Also the parameters passed 
@ to it are the instance_name and the host_name which is the kernelized 
@ implementation for posting HA alerts. Without actually having the arguments 
@ the guess is that either the host_name or the instance_name raised in the 
@ assert is null which triggered it.

mmon进程尝试调用相关程序,然后无法得出正确值,返回N,然后会一直尝试,如果不能得到返回Y,就会一直报ORA-600,错误.通过上述的三种情况来说,都和recent_resource_incarnations$表有关系.
该故障原因是由于:mmon在调用kjhn_post_ha_alert0函数在执行的时候,如果发现参数host_name或者instance_name为null,就会报该错误出来.

处理方法
This problem has been documented as Bug 5173066 REPEATED ORA-600 [KJHN_POST_HA_ALERT0-862] FROM MMON PROCESS.
The bug is fixed in 11.1.0.6. A workaround is available for the problem.
该bug在11.1.0.6中得以修复

To implement the workaround, please execute the following steps as the SYS user: 

1. Collect the following information and spool it to a file for your records. 

a. output of select * from v$instance 
b. show parameter instance_name 
c. set pages 1000 
d. select * from recent_resource_incarnations$ 

2. Create a backup table of recent_resource_incarnations$. 

SQL> create table recent_resource_inc$bk as select * from recent_resource_incarnations$; 


3. Truncate recent_resource_incarnations$. Be sure to do this while the instance is up and running.
    Do not issue this statement if a shutdown is pending. 

SQL> truncate table recent_resource_incarnations$; 


4. Perform a clean shutdown, followed by a startup.

具体参考:
ORA-600 [kjhn_post_ha_alert0-862] Continuously Repeated in the Alert Log (Doc ID 401640.1)
Bug 5173066 : REPEATED ORA-600 [KJHN_POST_HA_ALERT0-862] FROM MMON PROCESS

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