ORA-xxxxx – 第5页 – Database SOS

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

有朋友找到我，说他对生产库做了误操作，导致数据库异常，请我帮忙处理,对/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.

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

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00600[kjhn_post_ha_alert0-862]原因分析

数据库版本和平台信息
数据库版本为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-00230: operation disallowed: snapshot control file enqueue unavailable

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00230: operation disallowed: snapshot control file enqueue unavailable

rman 备份控制文件报ORA-00230: operation disallowed: snapshot control file enqueue unavailable错误

db1:/home/oracle>$rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Wed Jun 10 16:00:08 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1150889877)
RMAN> backup current controlfile format '/tmp/xifenfei.ctl';
Starting backup at 10-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=628 instance=orcl1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
cannot make a snapshot control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/10/2015 16:03:10
ORA-00230: operation disallowed: snapshot control file enqueue unavailable

查看持有CF enqueue会话

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SELECT s.SID, USERNAME AS "User", PROGRAM, MODULE,
  2  ACTION, LOGON_TIME "Logon"
  3  FROM V$SESSION s, V$ENQUEUE_LOCK l
  4  WHERE l.SID = s.SID
  5  AND l.TYPE = 'CF'
  6  AND l.ID1 = 0
  7  AND l.ID2 = 2;
       SID User
---------- ------------------------------
PROGRAM
------------------------------------------------
MODULE
------------------------------------------------
ACTION                           Logon
-------------------------------- ------------
       648 SYS
rman@db1 (TNS V1-V3)
backup full datafile
0000152 STARTED111               03-JUN-15

kill相关session

SQL> select spid from v$process where addr in(select paddr from v$session where sid=648);
SPID
------------
40108238
SQL> !ps -ef|grep 40108238
  oracle 39125244 65011720   0 15:59:27  pts/0  0:00 grep 40108238
  oracle 40108238        1   0   Jun 03      -  1:18 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
db1:/home/oracle>$kill -9 40108238

再次测试备份控制文件–OK

db1:/home/oracle>$rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Wed Jun 10 16:05:06 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1150889877)
RMAN> backup current controlfile format '/tmp/xifenfei.ctl';
Starting backup at 10-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=632 instance=orcl1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 10-JUN-15
channel ORA_DISK_1: finished piece 1 at 10-JUN-15
piece handle=/tmp/xifenfei.ctl tag=TAG20150610T160516 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 10-JUN-15

ORA-21561: OID generation failed故障解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-21561: OID generation failed故障解决

数据库无法登陆报ORA-21561: OID generation failed错误

[oracle@essc ~]$ sqlplus XIFENFEI/"www.xifenfei.com"@172.16.50.200/orcl
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 1 16:52:29 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:
ORA-21561: OID generation failed
Enter user-name:
ERROR:
ORA-12545: Connect failed because target host or object does not exist
Enter user-name:
ERROR:
ORA-12545: Connect failed because target host or object does not exist
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

主机名无法ping通

[root@essc ~]# ping essc
ping: unknown host essc
[root@essc ~]# hostname
essc
[root@essc ~]# more /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.10.11 app1
[oracle@essc ~]$ ifconfig
eth3      Link encap:Ethernet  HWaddr 00:50:56:BB:00:6B
          inet addr:172.16.10.30  Bcast:172.16.10.255  Mask:255.255.255.0
          inet6 addr: fe80::250:56ff:febb:6b/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:9597 errors:0 dropped:0 overruns:0 frame:0
          TX packets:4018 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:1549999 (1.4 MiB)  TX bytes:470158 (459.1 KiB)
lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:164 errors:0 dropped:0 overruns:0 frame:0
          TX packets:164 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:11424 (11.1 KiB)  TX bytes:11424 (11.1 KiB)

修改hosts文件
让hosts中含主机名,也就是为了主机名能够ping通

[oracle@essc ~]$ more /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.10.11 app1
172.16.10.30 essc

数据库登录测试

[oracle@essc ~]$ sqlplus XIFENFEI/"www.xifenfei.com"@172.16.50.200/orcl
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 1 16:56:39 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

具体见官方说明
Ora-21561: OID Generation Failed (Doc ID 1335327.1)

APPLIES TO:
Oracle Net Services - Version 9.2.0.8 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 14-Jan-2013***
SYMPTOMS
When attempting to connect to the database using SQL*Plus or DBCA,
the following error occurs intermittently:
ERROR
-----------------------
ORA-21561: OID generation failed
CAUSE
This could be caused by not having the host name for the
target database fully qualified in the hosts file.
To verify if you are hitting this issue, the following symptoms should be met:
- ORA-21561: OID generation failed.
- Hosts file has un-fully qualified entry for the target database host:
127.0.0.1 loopback localhost # loopback (lo0) name/address
10.210.9.111 dbhost
In this sample, dbhost is the target db host.
This is reported in an unpublished Bug 12597261:
"ORA-21561 IF HOSTNAME ENVIRONMENT VARIABLE IS NOT FULLY QUALIFIED",
 which should be resolved as of 12G.
SOLUTION
Modify the hosts file to have the fully qualified host names,
by adding the fully qualified domain name to the entry.
127.0.0.1 loopback localhost # loopback (lo0) name/address
10.210.9.111 dbhost.sample.com
Verify that other environment and service handles are properly defined as well.
If this is a Windows environment, please check :
Windows: Connections Fail with ORA-12640 or ORA-21561 (Doc ID 744125.1)

再次建议:修改主机名请修改完全,具体参考：linux上安装oracle10g注意事项中修改主机名部分

ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified 恢复思路

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified 恢复思路

今天一网友找到我,说数据库恢复在推scn的过程中遇到了ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified错误无法解决,让我给予支持.这不禁让我想起,现在由于数据库bug和dblink原因导致了很多数据库scn很大,距离天花板非常近,从而使得数据库恢复过程中无法直接简单的推scn,这里正好结合该例子,简单说明下ORA-01052故障的处理.类似文档以前也写过:ORA-01052发生原因的类似文章

由于坏块导致数据库进行实例恢复无法进行

Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 1901 KB redo, 276 data blocks need recovery
Started redo application at
 Thread 1: logseq 1004, block 172771
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1004 Reading mem 0
  Mem# 0: F:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG
Fri May 29 10:59:56 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 439938 OF FILE 19
Fri May 29 11:00:00 2015
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2048] [PC:0x6215134, __intel_new_memcpy()+260]
Fri May 29 11:00:12 2015
Trace dumping is performing id=[cdmp_20150529110012]
Fri May 29 11:00:12 2015
Slave exiting with ORA-1172 exception
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_p007_1612.trc:
ORA-01172: 线程 1 的恢复停止在块 439938 (在文件 19 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Fri May 29 11:00:27 2015
ORA-01578: ORACLE 数据块损坏 (文件号 19, 块号 450245)
ORA-01110: 数据文件 19: 'F:\APP\ADMINISTRATOR\ORADATA\XFF\PSTORE_02.DBF'
ORA-10564: tablespace PSTORE
ORA-01110: 数据文件 19: 'F:\APP\ADMINISTRATOR\ORADATA\XFF\PSTORE_02.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 91642
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [_intel_new_memcpy()+260] [ACCESS_VIOLATION] [ADDR:0x2048]
[PC:0x6215134] [UNABLE_TO_READ] []
Fri May 29 11:00:27 2015
Aborting crash recovery due to slave death, attempting serial crash recovery
RECOVERY OF THREAD 1 STUCK AT BLOCK 439938 OF FILE 19
Fri May 29 11:00:45 2015
Trace dumping is performing id=[cdmp_20150529110045]
Aborting crash recovery due to error 1172
ORA-1172 signalled during: alter database open...

设置_allow_resetlogs_corruption并resetlogs尝试打开数据库

Assigning activation ID 4272042346 (0xfea2316a)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: F:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 29 11:30:47 2015
SMON: enabling cache recovery
Fri May 29 11:30:47 2015
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc  (incident=181236):
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Incident details in: f:\app\administrator\diag\rdbms\XFF\XFF\incident\incdir_181236\XFF_ora_3004_i181236.trc
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc:
ORA-00704: ????????
ORA-00704: ????????
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc:
ORA-00704: ????????
ORA-00704: ????????
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 3004): terminating the instance due to error 704
Instance terminated by USER, pid = 3004
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (3004) as a result of ORA-1092

这里可以看到数据库通过设置_allow_resetlogs_corruption参数，进行不完全恢复,跳过数据库启动的实例恢复,然后强制拉库,然后遭遇大家熟悉的ORA-600[2662]错误,使得恢复失败,根据经验,通过推scn来绕过该错误

使用_minimum_giga_scn尝试推SCN

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--------------------------------
*._minimum_giga_scn=13443
--------------------------------
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

通过运行Oracle Database Recovery Check检查发现数据库的scn已经非常大,距离天花板较近
_minimum_giga_scn
这里最大允许的推进的scn为13442.7,但是常规的最小的推scn的方法最小值为1024*1024*1024的倍数,因此这里遇到麻烦.
这里数据库遭遇了ORA-01052错误,导致推scn不成功,数据库无法正常启动.出现这类情况,由于scn可以增加的空间非常小,因此可以使用使用oradebug修改数据库scn或者直接修改控制文件scn的方式来精确控制推scn的值(可以实现任何值的scn增加,只要不超过天花板),也可以通过方法修改数据库scn距离天花板的距离,从而实现大幅度使用_minimum_giga_scn来推scn.另外还有一种解决方法:由于ORA-01052是由于scn过大导致(超过了数据库现在的天花板scn),因此出现了ORA-01052.所以另外一种变通的方法,就是通过调整数据库的天花板scn,从而使得_minimum_giga_scn可以继续推scn.在本次恢复中使用最为简单的增加天花板scn的方式来恢复(不过该方法恢复之后需要重建库,其实已经使用了隐含参数屏蔽redo恢复,本身就建议重建库保证数据字典一致性)

物理备库在read only时报ORA-01552错误处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：物理备库在read only时报ORA-01552错误处理

物理备库在read only时报ORA-01552错误

Tue Jan 06 11:53:38 中国标准时间 2015
alter database open read only
Tue Jan 06 11:53:38 中国标准时间 2015
SMON: enabling cache recovery
Tue Jan 06 11:53:39 中国标准时间 2015
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open read only
Tue Jan 06 11:54:04 中国标准时间 2015
Errors in file c:\oracle\product\10.2.0\admin\ntsy\udump\ntsy_ora_9080.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01552: 非系统表空间 'MY_SPACE' 不能使用系统回退段
ORA-06512: 在 line 2

分析trace文件

*** ACTION NAME:() 2015-01-06 11:54:04.828
*** MODULE NAME:(sqlplus.exe) 2015-01-06 11:54:04.828
*** SERVICE NAME:(SYS$USERS) 2015-01-06 11:54:04.828
*** SESSION ID:(1284.9) 2015-01-06 11:54:04.828
Error in executing triggers on connect internal
*** 2015-01-06 11:54:04.828
ksedmp: internal or fatal error
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01552: 非系统表空间 'MY_SPACE' 不能使用系统回退段
ORA-06512: 在 line 2
*** 2015-01-06 11:54:05.843
Process diagnostic dump for ORACLE.EXE (MMNL), OS id=10492,
pid: 13, proc_ser: 1, sid: <no session>

这里可以看出来，是由于执行触发器导致该问题,根据经验第一感觉很可能是logon之类的触发器导致。

查询触发器

SQL> select trigger_name,trigger_type,OWNER from dba_triggers where owner='OP';
TRIGGER_NAME                   TRIGGER_TYPE     OWNER
------------------------------ ---------------- ------------------------------
LOGAD                          AFTER EVENT      OP
TR_TRACE_DDL                   AFTER EVENT      OP

只有这两个触发器是基于事件的,另外从名字和dba_source中确定

SQL> select text from dba_source where name='LOGAD';
TEXT
--------------------------------------------------------------------------------
TRIGGER "OP".logad after logon on database
begin
insert into logad values (SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE,SYS_CO
NTEXT('USERENV','IP_ADDRESS')) ;
end;
已选择6行。
SQL> select text from dba_source where name='TR_TRACE_DDL';
TEXT
--------------------------------------------------------------------------------
TRIGGER "OP".tr_trace_ddl
AFTER ddl
ON database
DECLARE
sql_text ora_name_list_t;
state_sql ddl$trace.ddl_sql%TYPE;
BEGIN
FOR i IN 1..ora_sql_txt(sql_text) LOOP
state_sql := state_sql||sql_text(i);
END LOOP;
TEXT
--------------------------------------------------------------------------------
INSERT INTO ddl$trace(login_user,audsid,machine,ipaddress,
schema_user,schema_object,ddl_time,ddl_sql)
VALUES(ora_login_user,userenv('SESSIONID'),sys_context('userenv','host'),
sys_context('userenv','ip_address'),ora_dict_obj_owner,ora_dict_obj_name,SYSDATE
,state_sql);
EXCEPTION
WHEN OTHERS THEN
-- sp_write_log('捕获DDL语句异常错误：'||SQLERRM);
null;
END tr_trace_ddl;

基本上确定LOGAD是登录触发器,tr_trace_ddl是记录ddl触发器,那现在问题应该出在LOGAD的触发器上.因为该触发器在备库上当有用户登录之时,他也会工作插入记录到logad表中,由于数据库是只读,因此就出现了类似ORA-01552错误

解决方法
在触发器中加判断数据库角色条件,当数据库为物理备库之时才执行dml操作

SQL> CREATE OR REPLACE TRIGGER "OP".logad
  2  AFTER LOGON on database
  3  declare
  4  db_role varchar2(30);
  5  begin
  6  select database_role into db_role from v$database;
  7  If db_role <> 'PHYSICAL STANDBY' then
  8  insert into op.logad values (SYS_CONTEXT('USERENV', 'SESSION_USER'),
  9  SYSDATE,SYS_CONTEXT('USERENV','IP_ADDRESS')) ;
 10  end if;
 11  end;
 12  /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER "OP".logad:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/40     PL/SQL: ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> grant select on v_$database to op;
Grant succeeded.
SQL> CREATE OR REPLACE TRIGGER "OP".logad
  2  AFTER LOGON on database
  3  declare
  4  db_role varchar2(30);
  5  begin
  6  select database_role into db_role from v$database;
  7  If db_role <> 'PHYSICAL STANDBY' then
  8  insert into op.logad values (SYS_CONTEXT('USERENV', 'SESSION_USER'),
  9 SYSDATE,SYS_CONTEXT('USERENV','IP_ADDRESS')) ;
 10  end if;
 12  end;
 12  /
Trigger created.

数据库open正常

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel
Tue Jan 06 13:51:20 中国标准时间 2015
alter database open read only
Tue Jan 06 13:51:21 中国标准时间 2015
SMON: enabling cache recovery
Tue Jan 06 13:51:21 中国标准时间 2015
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Tue Jan 06 13:51:23 中国标准时间 2015
db_recovery_file_dest_size of 102400 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Jan 06 13:51:23 中国标准时间 2015
Completed: alter database open read only

升级数据库到10.2.0.5遭遇ORA-00918: column ambiguously defined

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：升级数据库到10.2.0.5遭遇ORA-00918: column ambiguously defined

一个数据库从10201升级到10205之后,出现ORA-00918错误,查询mos发现在以前版本中是bug,Oracle好像在10205中把它修复了,结果就是以前应用的sql无法正常执行.这次升级的结果就是客户晚上3点联系开发商紧急修改程序。再次提醒：再小的系统数据库升级都需要做,功能测试,SPA测试,确保升级后功能和性能都正常.

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

执行报错ORA-00918
多个表JOIN连接,由于在select中的列未指定表名，而且该列在多个表中有，因此在10205中报ORA-00918错误，Oracle认为在以前的版本中是 Bug 5368296: SQL NOT GENERATING ORA-918 WHEN USING JOIN. 升级到10.2.0.5, 11.1.0.7 and 11.2.0.2版本，需要注意此类问题。修复bug没事,但是修复了之后导致系统需要修改sql才能够运行,确实让人很无语

SQL> set autot trace
SQL> set lines 100
SQL> SELECT   yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx,
  2             item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh,
  3             TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb, zyh,ch,xm,
  4             bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
  5             sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy,
  6             lb_yp_yl, zsq_code
  7        FROM op.yz a LEFT OUTER JOIN op.pc b
  8             ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), ' ')
  9             LEFT JOIN op.zy p ON a.zyh = p.zyh
 10       WHERE p.cy='在院' AND p.new_patient='1'
 11         AND upper(nvl(p.bj,1))<> 'Y'
 12         AND (state = '已核对')
 13         AND is_in_bill IS NULL
 14    ORDER BY ksrq, yz_id ;
           bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
           *
ERROR at line 4:
ORA-00918: column ambiguously defined
SQL> select COLUMN_NAME,TABLE_NAME from DBA_tab_columns where column_name='BQ'
  2  AND TABLE_NAME IN('YZ','ZY','PC');
COLUMN_NAME                    TABLE_NAME
------------------------------ ------------------------------
BQ                             ZY
BQ                             YZ

10.2.0.1中执行正常

E:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 1月 3 14:09:51 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> set autot trace
SQL> set lines 100
SQL> SELECT   yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx,
  2             item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh,
  3             TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb, zyh,ch,xm
,
  4             bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
  5             sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy,
  6             lb_yp_yl, zsq_code
  7        FROM op.yz a LEFT OUTER JOIN op.pc b
  8             ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), '
')
  9             LEFT JOIN op.zy p ON a.zyh = p.zyh
 10       WHERE p.cy='在院' AND p.new_patient='1'
 11         AND upper(nvl(p.bj,1))<> 'Y'
 12         AND (state = '已核对')
 13         AND is_in_bill IS NULL
 14    ORDER BY ksrq, yz_id ;
已选择19804行。
执行计划
----------------------------------------------------------
ERROR:
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-16000: 打开数据库以进行只读访问
SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      41945  consistent gets
          0  physical reads
          0  redo size
    2075973  bytes sent via SQL*Net to client
      14989  bytes received via SQL*Net from client
       1322  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      19804  rows processed

10.2.0.5库中同名列增加表名前缀执行OK


SQL> set autot trace
SQL> set lines 100
SQL> SELECT   yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx,
  2             item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh,
  3             TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb,zyh,ch,xm,
  4             a.bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
  5             sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy,
  6             lb_yp_yl, zsq_code
  7        FROM op.yz a LEFT OUTER JOIN op.pc b
  8             ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), ' ')
  9             LEFT JOIN op.zy p ON a.zyh = p.zyh
 10       WHERE p.cy='在院' AND p.new_patient='1'
 11         AND upper(nvl(p.bj,1))<> 'Y'
 12         AND (state = '已核对')
 13         AND is_in_bill IS NULL
 14    ORDER BY ksrq, yz_id ;
20629 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3468887510
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |    10 |  2580 |  2968   (2)| 00:00:36 |
|   1 |  SORT ORDER BY                |            |    10 |  2580 |  2968   (2)| 00:00:36 |
|*  2 |   HASH JOIN OUTER             |            |    10 |  2580 |  2967   (2)| 00:00:36 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| YZ         |     3 |   672 |    42   (0)| 00:00:01 |
|   4 |     NESTED LOOPS              |            |    10 |  2390 |  2963   (2)| 00:00:36 |
|*  5 |      TABLE ACCESS FULL        | ZY         |     3 |    45 |  2917   (2)| 00:00:36 |
|*  6 |      INDEX RANGE SCAN         | DZBLYZ_ZYH |   118 |       |     2   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL          | PC         |    33 |   627 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(NVL(TRIM(UPPER("A"."PC")),' ')=NVL(TRIM(UPPER("B"."PC"(+))),' '))
   3 - filter("A"."STATE"='已核对' AND "A"."IS_IN_BILL" IS NULL)
   5 - filter("P"."CY"='在院' AND UPPER(NVL("P"."BJ",'1'))<>'Y' AND
              "P"."NEW_PATIENT"='1')
   6 - access("A"."ZYH"="P"."ZYH")
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42121  consistent gets
          0  physical reads
          0  redo size
    2181383  bytes sent via SQL*Net to client
      15617  bytes received via SQL*Net from client
       1377  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      20629  rows processed

Bug 5368296: SQL NOT GENERATING ORA-918 WHEN USING JOIN
Bug 12388159 : SQL REPORTING ORA00918 AFTER UPGRADE TO 10.2.0.5.0
再次提醒：再小的系统数据库升级都需要做,功能测试,SPA测试,确保升级后功能和性能都正常.

查询v$session报ORA-04031错误

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：查询v$session报ORA-04031错误

客户的数据库在出账期间有工具登录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-00704 ORA-39714错误解决

联系：手机/微信(+86 17813235971) QQ(107644445)

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

数据库启动失败,报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-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

联系：手机/微信(+86 17813235971) QQ(107644445)

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

在昨天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