ORA-xxxxx – 第7页 – Database SOS

2014年第一个故障排查和解决：同事反馈给我说solaris 11.2 两节点rac无法启动，让我帮忙看下。通过分析是因为sga_target参数设置不合理导致asm无法正常启动
GI无法正常启动

grid@zwq-rpt1:~$crsctl status resource -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.
grid@zwq-rpt1:~$crsctl status resource -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  OFFLINE                               Instance Shutdown
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       zwq-rpt1
ora.crf
      1        ONLINE  ONLINE       zwq-rpt1
ora.crsd
      1        ONLINE  OFFLINE
ora.cssd
      1        ONLINE  ONLINE       zwq-rpt1
ora.cssdmonitor
      1        ONLINE  ONLINE       zwq-rpt1
ora.ctssd
      1        ONLINE  ONLINE       zwq-rpt1                 ACTIVE:0
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  INTERMEDIATE zwq-rpt1
ora.gipcd
      1        ONLINE  ONLINE       zwq-rpt1
ora.gpnpd
      1        ONLINE  ONLINE       zwq-rpt1
ora.mdnsd
      1        ONLINE  ONLINE       zwq-rpt1

asm未正常启动

GI日志报错

2014-01-01 00:40:47.708
[cssd(1418)]CRS-1605:CSSD voting file is online: /dev/rdsk/emcpower0a; details in /export/home/app/grid/log/zwq-rpt1/cssd/ocssd.log.
2014-01-01 00:40:53.234
[cssd(1418)]CRS-1601:CSSD Reconfiguration complete. Active nodes are zwq-rpt1 zwq-rpt2 .
2014-01-01 00:40:56.659
[ctssd(1483)]CRS-2407:The new Cluster Time Synchronization Service reference node is host zwq-rpt2.
2014-01-01 00:40:56.661
[ctssd(1483)]CRS-2401:The Cluster Time Synchronization Service started on host zwq-rpt1.
2014-01-01 00:41:02.016
[ctssd(1483)]CRS-2408:The clock on host zwq-rpt1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
2014-01-01 00:43:23.874
[/export/home/app/grid/bin/oraagent.bin(1348)]CRS-5019:All OCR locations are on ASM disk groups [], and none of these disk groups are mounted. Details are at "(:CLSN00100:)" in "/export/home/app/grid/log/zwq-rpt1/agent/ohasd/oraagent_grid/oraagent_grid.log".
2014-01-01 00:45:42.837
[/export/home/app/grid/bin/oraagent.bin(1348)]CRS-5019:All OCR locations are on ASM disk groups [], and none of these disk groups are mounted. Details are at "(:CLSN00100:)" in "/export/home/app/grid/log/zwq-rpt1/agent/ohasd/oraagent_grid/oraagent_grid.log".
2014-01-01 00:48:02.087
[/export/home/app/grid/bin/oraagent.bin(1348)]CRS-5019:All OCR locations are on ASM disk groups [], and none of these disk groups are mounted. Details are at "(:CLSN00100:)" in "/export/home/app/grid/log/zwq-rpt1/agent/ohasd/oraagent_grid/oraagent_grid.log".
2014-01-01 00:48:18.836
[ohasd(1083)]CRS-2807:Resource 'ora.asm' failed to start automatically.
2014-01-01 00:48:18.837
[ohasd(1083)]CRS-2807:Resource 'ora.crsd' failed to start automatically.
2014-01-01 01:05:15.396
[/export/home/app/grid/bin/oraagent.bin(1348)]CRS-5019:All OCR locations are on ASM disk groups [CRSDG], and none of these disk groups are mounted. Details are at "(:CLSN00100:)" in "/export/home/app/grid/log/zwq-rpt1/agent/ohasd/oraagent_grid/oraagent_grid.log".
2014-01-01 01:05:45.101
[/export/home/app/grid/bin/oraagent.bin(1348)]CRS-5019:All OCR locations are on ASM disk groups [CRSDG], and none of these disk groups are mounted. Details are at "(:CLSN00100:)" in "/export/home/app/grid/log/zwq-rpt1/agent/ohasd/oraagent_grid/oraagent_grid.log".
2014-01-01 01:06:15.104
[/export/home/app/grid/bin/oraagent.bin(1348)]CRS-5019:All OCR locations are on ASM disk groups [CRSDG], and none of these disk groups are mounted. Details are at "(:CLSN00100:)" in "/export/home/app/grid/log/zwq-rpt1/agent/ohasd/oraagent_grid/oraagent_grid.log".

这里较为明显的看到，因为asm磁盘组异常导致ocr无法被访问导致crs无法正常启动

ORAAGENT日志

2014-01-01 00:43:23.870: [ora.asm][9] {0:0:2} [start] InstConnection::connectInt (2) Exception OCIException
2014-01-01 00:43:23.870: [ora.asm][9] {0:0:2} [start] InstConnection:connect:excp OCIException OCI error 604
2014-01-01 00:43:23.870: [ora.asm][9] {0:0:2} [start] DgpAgent::queryDgStatus excp ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^34f764db","kglHeapInitialize:temp")

报了较为清晰的ORA-4031错误,检查asm日志

ASM日志报错

Wed Jan 01 00:47:33 2014
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /export/home/app/oracle
Wed Jan 01 00:47:39 2014
Errors in file /export/home/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_1728.trc  (incident=291447):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^34f764db","kglHeapInitialize:temp")
Incident details in: /export/home/app/oracle/diag/asm/+asm/+ASM1/incident/incdir_291447/+ASM1_ora_1728_i291447.trc
Wed Jan 01 00:47:48 2014
Dumping diagnostic data in directory=[cdmp_20140101004748], requested by (instance=1, osid=1728), summary=[incident=291447].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Jan 01 00:47:53 2014
Errors in file /export/home/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_1730.trc  (incident=291448):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^34f764db","kglHeapInitialize:temp")
Incident details in: /export/home/app/oracle/diag/asm/+asm/+ASM1/incident/incdir_291448/+ASM1_ora_1730_i291448.trc
Wed Jan 01 00:48:01 2014
Dumping diagnostic data in directory=[cdmp_20140101004801], requested by (instance=1, osid=1730), summary=[incident=291448].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Jan 01 00:48:07 2014
Errors in file /export/home/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_1732.trc  (incident=291449):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^34f764db","kglHeapInitialize:temp")
Incident details in: /export/home/app/oracle/diag/asm/+asm/+ASM1/incident/incdir_291449/+ASM1_ora_1732_i291449.trc
Wed Jan 01 00:48:16 2014
Dumping diagnostic data in directory=[cdmp_20140101004816], requested by (instance=1, osid=1732), summary=[incident=291449].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Jan 01 00:48:16 2014
License high water mark = 1
USER (ospid: 1736): terminating the instance
Instance terminated by USER, pid = 1736

这里可以清晰的看到，因为shared pool不足，导致asm报ora-4031错误，从而使得asm无法正常启动

分析原因

Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options.
ORACLE_HOME = /export/home/app/grid
System name:	SunOS
Node name:	zwq-rpt1
Release:	5.11
Version:	11.1
Machine:	sun4v
Using parameter settings in server-side spfile +CRSDG/zwq-rpt-cluster/asmparameterfile/registry.253.823992831
System parameters with non-default values:
  sga_max_size             = 2G
  large_pool_size          = 16M
  instance_type            = "asm"
  sga_target               = 0
  remote_login_passwordfile= "EXCLUSIVE"
  asm_diskstring           = "/dev/rdsk/*"
  asm_diskgroups           = "FRADG"
  asm_diskgroups           = "DATADG"
  asm_power_limit          = 1
  diagnostic_dest          = "/export/home/app/oracle"

这里可以看到sga_target被设置为了0,而shared pool又未被配置，这里因为shared pool不足从而出现了ORA-4031,从而导致crs在启动asm的过程失败,从而使得ocr不能被访问,进而使得crs不能正常启动.

处理方法
1.编辑pfile

grid@zwq-rpt1:/export/home/app/oracle/diag/asm/+asm/+ASM1/trace$vi /tmp/asm.pfile
  memory_target = 2G
  large_pool_size          = 16M
  instance_type            = "asm"
  sga_target               = 0
  remote_login_passwordfile= "EXCLUSIVE"
  asm_diskstring           = "/dev/rdsk/*"
  asm_diskgroups           = "FRADG"
  asm_diskgroups           = "DATADG"
  asm_power_limit          = 1
  diagnostic_dest          = "/export/home/app/oracle"

2.启动asm

grid@zwq-rpt1:/export/home/app/oracle/diag/asm/+asm/+ASM1/trace$sqlplus / as sysasm
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 1 01:04:10 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/tmp/asm.pfile'
ASM instance started
Total System Global Area 2138521600 bytes
Fixed Size                  2161024 bytes
Variable Size            2102806144 bytes
ASM Cache                  33554432 bytes
ASM diskgroups mounted

3. 创建spfile

SQL> create spfile='+CRSDG' FROM PFILE='/tmp/asm.pfile';
File created.
--asm alert日志
Wed Jan 01 01:08:59 2014
NOTE: updated gpnp profile ASM SPFILE to
NOTE: updated gpnp profile ASM diskstring: /dev/rdsk/*
NOTE: updated gpnp profile ASM diskstring: /dev/rdsk/*
NOTE: updated gpnp profile ASM SPFILE to +CRSDG/zwq-rpt-cluster/asmparameterfile/registry.253.835664939

4. 关闭asm

SQL> shutdown immediate
ORA-15097: cannot SHUTDOWN ASM instance with connected client (process 1971)
SQL> shutdown abort
ASM instance shutdown

5. 重启crs

root@zwq-rpt1:~# crsctl stop crs -f
root@zwq-rpt1:~# crsctl start crs

6. 重启其他节点crs

root@zwq-rpt2:~# crsctl stop crs -f
root@zwq-rpt2:~# crsctl start crs

7. 检查结果

root@zwq-rpt1:~# crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRSDG.dg
               ONLINE  ONLINE       zwq-rpt1
               ONLINE  ONLINE       zwq-rpt2
ora.DATADG.dg
               ONLINE  ONLINE       zwq-rpt1
               ONLINE  ONLINE       zwq-rpt2
ora.FRADG.dg
               ONLINE  ONLINE       zwq-rpt1
               ONLINE  ONLINE       zwq-rpt2
ora.LISTENER.lsnr
               ONLINE  ONLINE       zwq-rpt1
               ONLINE  ONLINE       zwq-rpt2
ora.asm
               ONLINE  ONLINE       zwq-rpt1                 Started
               ONLINE  ONLINE       zwq-rpt2                 Started
ora.gsd
               OFFLINE OFFLINE      zwq-rpt1
               OFFLINE OFFLINE      zwq-rpt2
ora.net1.network
               ONLINE  ONLINE       zwq-rpt1
               ONLINE  ONLINE       zwq-rpt2
ora.ons
               ONLINE  ONLINE       zwq-rpt1
               ONLINE  ONLINE       zwq-rpt2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       zwq-rpt1
ora.cvu
      1        ONLINE  ONLINE       zwq-rpt1
ora.oc4j
      1        ONLINE  ONLINE       zwq-rpt1
ora.rptdb.db
      1        ONLINE  ONLINE       zwq-rpt1                 Open
      2        ONLINE  ONLINE       zwq-rpt2                 Open
ora.scan1.vip
      1        ONLINE  ONLINE       zwq-rpt1
ora.zwq-rpt1.vip
      1        ONLINE  ONLINE       zwq-rpt1
ora.zwq-rpt2.vip
      1        ONLINE  ONLINE       zwq-rpt2

至此恢复正常,2014年第一个故障顺利解决

因RAC的undo_management参数不一致导致数据库mount报ORA-01105 ORA-01606

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

标题：因RAC的undo_management参数不一致导致数据库mount报ORA-01105 ORA-01606

环境Linux 5.8 10.2.0.5 RAC,两个节点只能一个节点mount,如果尝试mount另外节点就报ORA-01105和ORA-01606错误
数据库版本

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 Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

crs资源情况

[oracle@node1 dbs]$ $ORA_CRS_HOME/bin/crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....D1.inst application    OFFLINE   OFFLINE
ora....D2.inst application    ONLINE    ONLINE    node2
ora.PROD.db    application    ONLINE    ONLINE    node2
ora....SM1.asm application    ONLINE    ONLINE    node1
ora....E1.lsnr application    ONLINE    ONLINE    node1
ora.node1.gsd  application    ONLINE    ONLINE    node1
ora.node1.ons  application    ONLINE    ONLINE    node1
ora.node1.vip  application    ONLINE    ONLINE    node1
ora....SM2.asm application    ONLINE    ONLINE    node2
ora....E2.lsnr application    ONLINE    ONLINE    node2
ora.node2.gsd  application    ONLINE    ONLINE    node2
ora.node2.ons  application    ONLINE    ONLINE    node2
ora.node2.vip  application    ONLINE    ONLINE    node2

节点1 mount报错

SQL> startup
ORACLE instance started.
Total System Global Area  171966464 bytes
Fixed Size                  2094832 bytes
Variable Size             113248528 bytes
Database Buffers           50331648 bytes
Redo Buffers                6291456 bytes
ORA-01105: mount is incompatible with mounts by other instances
ORA-01606: gc_files_to_locks not identical to that of another mounted instance

Error:    ORA 1105
Text:     mount is incompatible with mounts by other instances
-------------------------------------------------------------------------------
Cause:  An attempt was made to mount the database, but another instance has already mounted
        a database by the same name, and the mounts are not compatible.
        dditional messages will accompany this message to report why the mounts are incompatible.
Action:  See the accompanying messages for the appropriate action to take.
Error:  ORA 1606
Text:   GC_FILES_TO_LOCKS not identical to that of another mounted instance
-------------------------------------------------------------------------------
Cause:  The initialization parameter GC_FILES_TO_LOCKS is not the same as
        another instance mounted in parallel mode.
        This parameter must be the same as that for all shared instances.
Action: Modify the parameter to be compatible with the other instances, then
        shut down and restart the instance.

根据这个错误提示，查询两个节点的gc_files_to_locks参数，均为空值(默认值),也就是值相同

SQL> show parameter gc_files_to_locks;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
gc_files_to_locks                    string

检查两个节点的gc相关隐含参数,发现所有值也均一致

NAME                           DESCRIPTION                                                           VALUE
------------------------------ --------------------------------------------------------------------- -------
_gc_affinity_limit             dynamic affinity limit                                                50
_gc_affinity_minimum           dynamic affinity minimum activity per minute                          6000
_gc_affinity_time              if non zero, enable dynamic object affinity                           10
_gc_async_memcpy               if TRUE, use async memcpy                                             FALSE
_gc_check_bscn                 if TRUE, check for stale blocks                                       TRUE
_gc_coalesce_recovery_reads    if TRUE, coalesce recovery reads                                      TRUE
_gc_defer_time                 how long to defer down converts for hot buffers                       3
_gc_dissolve_undo_affinity     if TRUE, dissolve undo affinity after an offline                      FALSE
_gc_dynamic_affinity_locks     if TRUE, get dynamic affinity locks                                   TRUE
_gc_element_percent            global cache element percent                                          103
_gc_global_lru                 turn global lru off, make it automatic, or turn it on                 AUTO
_gc_initiate_undo_affinity     if TRUE, initiate undo affinity after an online                       TRUE
_gc_integrity_checks           set the integrity check level                                         1
_gc_keep_recovery_buffers      if TRUE, make recovery buffers current                                TRUE
_gc_latches                    number of latches per LMS process                                     8
_gc_maximum_bids               maximum number of bids which can be prepared                          0
_gcs_fast_reconfig             if TRUE, enable fast reconfiguration for gcs locks                    TRUE
_gcs_latches                   number of gcs resource hash latches to be allocated per LMS process   64
_gcs_pkey_history              number of pkey remastering history                                    4000
_gcs_process_in_recovery       if TRUE, process gcs requests during instance recovery                TRUE
_gcs_resources                 number of gcs resources to be allocated
_gcs_shadow_locks              number of pcm shadow locks to be allocated
_gc_statistics                 if TRUE, kcl statistics are maintained                                TRUE
_gcs_testing                   GCS testing parameter                                                 0
_gc_tsn_undo_affinity          if TRUE, use TSN undo affinity                                        TRUE
_gc_undo_affinity              if TRUE, enable dynamic undo affinity                                 TRUE
_gc_undo_affinity_locks        if TRUE, get affinity locks for undo                                  TRUE
_gc_use_cr                     if TRUE, allow CR pins on PI and WRITING buffers                      TRUE
_gc_vector_read                if TRUE, vector read current buffers                                  TRUE

仔细对比数据库参数，发现undo异常

--节点1
SQL>  show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      SYSTEM
--节点2
SQL>  show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

这里已经明确，因为两个节点的undo_*相关参数配置不正确，导致数据库只能一个节点mount。进一步定位问题发现，原来是因为dba粗心在编辑节点1的参数文件的时候把undo_*相关的参数给弄丢了,从而数据库使用了默认值undo_management=manual,undo_tablespace=system

重建控制文件丢失undo异常恢复—ORA-01173模拟与恢复

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

标题：重建控制文件丢失undo异常恢复—ORA-01173模拟与恢复

数据库异常关闭，使用resetlogs方式重建控制文件，不包含undo表空间相关数据库，然后尝试resetlogs打开数据库，非常容易出现ORA-01173: data dictionary indicates missing data file from system tablespace之类的错误，本文通过模拟该错误，并简单提供解决方法
模拟ORA-01173错误

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> shutdown abort;
ORACLE instance shut down.
--除掉undo表空间相关数据库然后使用resetlogs模式重建控制文件
SQL> startup nomount;
ORACLE instance started.
Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes
SQL> @/tmp/ctl.sql
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 12696930512547 generated at 08/26/2013 14:43:13 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/ora11g/archivelog/1_8_824475918.dbf
ORA-00280: change 12696930512547 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel;
ORA-00308: cannot open archived log 'cancel;'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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: '/u01/oracle/oradata/ora11g/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 12696930512547 generated at 08/26/2013 14:43:13 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/ora11g/archivelog/1_8_824475918.dbf
ORA-00280: change 12696930512547 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01173: data dictionary indicates missing data file from system tablespace
Process ID: 15053
Session ID: 125 Serial number: 3

alert日志

Mon Aug 26 15:22:20 2013
alter database open resetlogs
RESETLOGS after complete recovery through change 12696930514503
Archived Log entry 1 added for thread 1 sequence 7 ID 0xfb91eb36 dest 1:
Archived Log entry 2 added for thread 1 sequence 8 ID 0xfb91eb36 dest 1:
Archived Log entry 3 added for thread 1 sequence 6 ID 0xfb91eb36 dest 1:
Clearing online redo logfile 1 /u01/oracle/oradata/ora11g/redo01.log
Clearing online log 1 of thread 1 sequence number 7
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/oracle/oradata/ora11g/redo02.log
Clearing online log 2 of thread 1 sequence number 8
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/oracle/oradata/ora11g/redo03.log
Clearing online log 3 of thread 1 sequence number 6
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 4220644150 (0xfb91eb36)
Online log /u01/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared
Mon Aug 26 15:22:26 2013
Setting recovery target incarnation to 2
Using SCN growth rate of 16384 per second
Mon Aug 26 15:22:26 2013
Assigning activation ID 4220652407 (0xfb920b77)
LGWR: STARTING ARCH PROCESSES
Mon Aug 26 15:22:26 2013
ARC0 started with pid=20, OS id=15131
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Aug 26 15:22:27 2013
ARC1 started with pid=21, OS id=15135
Mon Aug 26 15:22:27 2013
ARC2 started with pid=22, OS id=15139
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/oracle/oradata/ora11g/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Aug 26 15:22:27 2013
SMON: enabling cache recovery
Mon Aug 26 15:22:27 2013
ARC3 started with pid=23, OS id=15143
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15053.trc:
ORA-01173: data dictionary indicates missing data file from system tablespace
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15053.trc:
ORA-01173: data dictionary indicates missing data file from system tablespace
Error 1173 happened during db open, shutting down database
USER (ospid: 15053): terminating the instance due to error 1173
Instance terminated by USER, pid = 15053
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (15053) as a result of ORA-1092
Mon Aug 26 15:22:29 2013
ORA-1092 : opitsk aborting process

解决方案
解决方案设置相关异常回滚段,然后屏蔽掉，数据库即可正常起来

--参数文件修改(使用event或者工具，系统命令等)
undo_management='manual'
_corrupted_rollback_segments
--重启数据库使得_corrupted_rollback_segments生效
shutdown immediate;
startup
--尝试open数据库
recover database;
alter database open;
--新建undo
create undo tablespace undo_new datafile '' size 100m autoextend on next 10m maxsize 30G;
--删除老undo
drop tablespace old_undo
--修改参数
shutdonw immediate
undo_management='auto'
undo_tablespace='unod_new'
--启动数据库
startup
--逻辑导出数据，导入新库

姊妹篇:重建控制文件丢失undo异常恢复—ORA-600[25025]模拟与恢复

重建控制文件丢失undo异常恢复—ORA-600 25025模拟与恢复

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

标题：重建控制文件丢失undo异常恢复—ORA-600 25025模拟与恢复

在现实的情况中，有些人因为某种原因重建控制文件(丢失undo[有意或者无意])，然后又resetlogs库尝试恢复,这样的操作可能导致各种比较麻烦的恢复,这里模拟ORA-600[25025]异常恢复
模拟ORA-600[25025]错误

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> alter database backup controlfile to trace as '/tmp/ctl';
Database altered.
SQL> create table chf.t_xifenfei_www as select * from dba_objects where 1=0;
Table created.
SQL> insert into chf.t_xifenfei_www select * from dba_objects;
74749 rows created.
--另外一个会话abort
SQL> shutdown abort;
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes
SQL> !vi /tmp/ctl.sql
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oracle/oradata/ora11g/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/oracle/oradata/ora11g/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oracle/oradata/ora11g/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/oracle/oradata/ora11g/system01.dbf',
  '/u01/oracle/oradata/ora11g/sysaux01.dbf',
 -- '/u01/oracle/oradata/ora11g/undo02.dbf',
  '/u01/oracle/oradata/ora11g/users01.dbf',
  '/u01/oracle/oradata/ora11g/dbfs01.dbf',
  '/u01/oracle/oradata/ora11g/tts_xifenfei02.dbf',
  '/u01/oracle/oradata/ora11g/tts_xifenfei01.dbf',
  '/u01/oracle/oradata/ora11g/system02.dbf',
  '/u01/oracle/oradata/ora11g/czum01.dbf',
  '/u01/oracle/oradata/ora11g/undotbs02.dbf',
  '/u01/oracle/oradata/sp2008',
  '/u01/oracle/oradata/sp_2009',
  '/u01/oracle/oradata/sp_2010',
  '/u01/oracle/oradata/sp_2011',
  '/u01/oracle/oradata/sp_2012',
  '/u01/oracle/oradata/sp_2013',
  '/u01/oracle/oradata/sp_2014',
  '/u01/oracle/oradata/sp_2015',
  '/u01/oracle/oradata/sp_2016',
  '/u01/oracle/oradata/sp_2017',
  '/u01/oracle/oradata/sp_2018',
  '/u01/oracle/oradata/sp_2019',
  '/u01/oracle/oradata/sp_2020',
  '/u01/oracle/oradata/sp_2021',
  '/u01/oracle/oradata/sp_2022',
  '/u01/oracle/oradata/sp_2023',
  '/u01/oracle/oradata/sp_2024',
  '/u01/oracle/oradata/sp_2025',
  '/u01/oracle/oradata/sp_20max'
CHARACTER SET ZHS16GBK
;
"/tmp/ctl.sql" [New] 43L, 1519C written
SQL> @/tmp/ctl.sql
Control file created.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12696930343864 generated at 05/18/2013 01:17:54 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_38_805394597.dbf
ORA-00280: change 12696930343864 for thread 1 is in sequence #38
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo01.log
ORA-00310: archived log contains sequence 37; sequence 38 required
ORA-00334: archived log: '/u01/oracle/oradata/ora11g/redo01.log'
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: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL>  recover database using backup controlfile until cancel;
ORA-00279: change 12696930343864 generated at 05/18/2013 01:17:54 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_38_805394597.dbf
ORA-00280: change 12696930343864 for thread 1 is in sequence #38
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo03.log
ORA-00310: archived log contains sequence 39; sequence 38 required
ORA-00334: archived log: '/u01/oracle/oradata/ora11g/redo03.log'
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: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12696930343864 generated at 05/18/2013 01:17:54 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_38_805394597.dbf
ORA-00280: change 12696930343864 for thread 1 is in sequence #38
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo02.log
ORA-00279: change 12696930370956 generated at 08/26/2013 13:00:25 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_39_805394597.dbf
ORA-00280: change 12696930370956 for thread 1 is in sequence #39
ORA-00278: log file '/u01/oracle/oradata/ora11g/redo02.log' no longer needed
for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [],
[], [], [], [], []
Process ID: 12603
Session ID: 125 Serial number: 3

alert日志信息

ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup controlfile until cancel  ...
ALTER DATABASE RECOVER    LOGFILE '/u01/oracle/oradata/ora11g/redo02.log'
Media Recovery Log /u01/oracle/oradata/ora11g/redo02.log
Mon Aug 26 13:05:00 2013
ORA-279 signalled during: ALTER DATABASE RECOVER    LOGFILE '/u01/oracle/oradata/ora11g/redo02.log'  ...
Mon Aug 26 13:05:10 2013
ALTER DATABASE RECOVER    LOGFILE '/u01/oracle/oradata/ora11g/redo03.log'
Media Recovery Log /u01/oracle/oradata/ora11g/redo03.log
Mon Aug 26 13:05:10 2013
Incomplete recovery applied all redo ever generated.
Recovery completed through change 12696930370973 time 08/26/2013 13:00:56
Media Recovery Complete (ora11g)
Completed: ALTER DATABASE RECOVER    LOGFILE '/u01/oracle/oradata/ora11g/redo03.log'
alter database open resetlogs
RESETLOGS after complete recovery through change 12696930370973
Archived Log entry 1 added for thread 1 sequence 37 ID 0xfa6fa6cb dest 1:
Archived Log entry 2 added for thread 1 sequence 38 ID 0xfa6fa6cb dest 1:
Archived Log entry 3 added for thread 1 sequence 39 ID 0xfa6fa6cb dest 1:
Clearing online redo logfile 1 /u01/oracle/oradata/ora11g/redo01.log
Clearing online log 1 of thread 1 sequence number 37
Mon Aug 26 13:05:22 2013
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/oracle/oradata/ora11g/redo02.log
Clearing online log 2 of thread 1 sequence number 38
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/oracle/oradata/ora11g/redo03.log
Clearing online log 3 of thread 1 sequence number 39
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 4201621195 (0xfa6fa6cb)
Online log /u01/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared
Mon Aug 26 13:05:33 2013
Setting recovery target incarnation to 2
Mon Aug 26 13:05:33 2013
Using SCN growth rate of 16384 per second
Mon Aug 26 13:05:33 2013
Assigning activation ID 4220644150 (0xfb91eb36)
LGWR: STARTING ARCH PROCESSES
Mon Aug 26 13:05:33 2013
ARC0 started with pid=20, OS id=12679
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Aug 26 13:05:35 2013
ARC1 started with pid=21, OS id=12683
Mon Aug 26 13:05:35 2013
ARC2 started with pid=22, OS id=12687
Mon Aug 26 13:05:36 2013
ARC3 started with pid=24, OS id=12691
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/oracle/oradata/ora11g/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Aug 26 13:05:36 2013
SMON: enabling cache recovery
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_12603.trc  (incident=146705):
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_146705/ora11g_ora_12603_i146705.trc
Mon Aug 26 13:05:45 2013
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Undo initialization errored: err:600 serial:0 start:57601994 end:57610584 diff:8590 (85 seconds)
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_12603.trc:
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_12603.trc:
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 12603): terminating the instance due to error 600
Instance terminated by USER, pid = 12603
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (12603) as a result of ORA-1092
Mon Aug 26 13:05:47 2013
ORA-1092 : opitsk aborting process

trace文件

*** 2013-08-26 13:05:38.945
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=7j16t46cacjt9) -----
alter database open resetlogs
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+41        call     kgdsdst()            BF8A34E4 ? 2 ?
ksedst1()+77         call     skdstdst()           BF8A34E4 ? 0 ? 1 ? 8592C38 ?
                                                   8592AB6 ? 8592C38 ?
ksedst()+33          call     ksedst1()            0 ? 1 ?
dbkedDefDump()+2704  call     ksedst()             0 ? BF8A40E7 ? 47EF1FF4 ?
                                                   BF8A3D66 ? 0 ? BF8A3BF4 ?
ksedmp()+47          call     dbkedDefDump()       3 ? 2 ?
ksfdmp()+59          call     ksedmp()             3EB ? BF8A5458 ? F1DADED ?
                                                   116056E0 ? 3EB ? 116056E0 ?
dbgexPhaseII()+1725  call     00000000             116056E0 ? 3EB ?
dbgexProcessError()  call     dbgexPhaseII()       B6C515AC ? B6A0C890 ?
+2550                                              BF8A8D30 ?
dbgeExecuteForError  call     dbgexProcessError()  B6C515AC ? B6A0C890 ? 1 ? 0 ?
()+65                                              B6C515AC ? B6A0C890 ?
dbgePostErrorKGE()+  call     dbgeExecuteForError  B6C515AC ? B6A0C890 ? 0 ? 1 ?
1794                          ()                   0 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   116056E0 ? B6C330D4 ? 258 ?
50
kgeade()+324         call     00000000             116056E0 ? B6C330D4 ? 258 ?
kgeriv_int()+107     call     kgeade()             116056E0 ? 11605808 ?
                                                   B6C330D4 ? 258 ? 0 ? 61C1 ?
kgeriv()+25          call     kgeriv_int()         116056E0 ? B6C330D4 ? 61C1 ?
                                                   0 ? 1 ? BF8A96B8 ?
kgesiv()+98          call     kgeriv()             116056E0 ? B6C330D4 ? 61C1 ?
                                                   1 ? BF8A96B8 ?
ksesic1()+48         call     kgesiv()             116056E0 ? B6C330D4 ? 61C1 ?
                                                   1 ? BF8A96B8 ? 61C1 ? 1 ?krta2rfn()+78
                                                   call     ksesic1()            61C1 ? 0 ? 3 ? 0 ?
kttsinfo()+496       call     krta2rfn()           3 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ktusmout_online_ut(  call     kttsinfo()           9 ? 0 ? 0 ? BF8A9948 ?
)+810
ktusmiut_init_ut()+  call     ktusmout_online_ut(  B000 ? 89E57F8 ? 9 ?
                                                   BF8A96B8 ?
348                           )
ktuini()+518         call     ktusmiut_init_ut()   0 ? 0 ? 0 ? 0 ? 1 ? 0 ?
adbdrv()+46345       call     ktuini()             0 ? BF8A9DE0 ? 1004BF42 ?
                                                   BF8AA258 ? B6A0BFFC ?
                                                   10EA1F20 ?
opiexe()+16835       call     adbdrv()             25C8F1F8 ? 0 ? 0 ? 2A34F528 ?
                                                   2A0400C8 ? BF8AF07C ?
opiosq0()+3437       call     opiexe()             4 ? 0 ? BF8AFE8C ?
kpooprx()+239        call     opiosq0()            3 ? E ? BF8B0184 ? A4 ? 0 ?
kpoal8()+607         call     kpooprx()            BF8B2D6C ? BF8B10AC ? 1D ?
                                                   1 ? 0 ? A4 ?
opiodr()+962         call     00000000             5E ? 1C ? BF8B2D68 ?
ttcpip()+1930        call     00000000             5E ? 1C ? BF8B2D68 ? 0 ?
opitsk()+1355        call     ttcpip()             11616580 ? 5E ? BF8B2D68 ?
                                                   0 ? BF8B29F8 ? BF8B2E90 ?
                                                   FDEBA80 ? 0 ? BF8B2EBC ?
opiino()+827         call     opitsk()             0 ? 0 ?
opiodr()+962         call     00000000             3C ? 4 ? BF8B3E2C ?
opidrv()+479         call     opiodr()             3C ? 4 ? BF8B3E2C ? 0 ?
sou2o()+80           call     opidrv()             3C ? 4 ? BF8B3E2C ?
opimai_real()+109    call     sou2o()              BF8B3E10 ? 3C ? 4 ?
                                                   BF8B3E2C ?
ssthrdmain()+212     call     00000000             2 ? BF8B3F58 ? 0 ? 47DA6F14 ?
                                                   BF8B3F14 ? 47D9A670 ?
main()+147           call     ssthrdmain()         2 ? BF8B3F58 ?
__libc_start_main()  call     00000000             2 ? BF8B4054 ? BF8B4060 ?
+220                                               47D9A828 ? 0 ? 1 ?
_start()+33          call     __libc_start_main()  856F3B4 ? 2 ? BF8B4054 ?
                                                   BCC1EA0 ? BCC1E90 ?
                                                   47D8C790 ?
--------------------- Binary Stack Dump ---------------------

MOS中有类似描述ORA-600 [25025] [25] While Opening the Clone Database in Resetlog Mode (Doc ID 603100.1),该解决方案是重建控制文件增加所有数据文件，在本次测试中，我就是人为除掉了undo，模拟undo丢失[其实数据库已经resetlogs过了，就算加入undo重建控制文件也不会成功(人工修改undo文件头除外)]，又做了不正确的重建控制文件操作的故障，我提供解决方案如下

解决办法

--参数文件修改
undo_management='manual'
--尝试open数据库
recover database;
alter database open;
--新建undo
create undo tablespace undo_new datafile '' size 100m autoextend on next 10m maxsize 30G;
--屏蔽需要恢复回滚段
select tablespace_name,segment_name,status from dba_rollback_segs;
_corrupted_rollback_segments
--重启数据库使得_corrupted_rollback_segments生效
shutdown immediate;
startup
--删除老undo
drop tablespace old_undo
--修改参数
shutdonw immediate
undo_management='auto'
undo_tablespace='unod_new'
--启动数据库
startup
--导出数据，导入新库

姊妹篇:重建控制文件丢失undo异常恢复—ORA-01173模拟与恢复

ksuapc : ORA-1033 foreground process starts before PMON

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

标题：ksuapc : ORA-1033 foreground process starts before PMON

在11.2.0.1数据库中启动出现ksuapc : ORA-1033 foreground process starts before PMON错误

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /opt/oracle/product/11.2.0/dbhome_1/dbs/spfileora11bak.ora
System parameters with non-default values:
  processes                = 150
  nls_language             = "SIMPLIFIED CHINESE"
  nls_territory            = "CHINA"
  sga_target               = 1536M
  control_files            = "/opt/oracle/oradata/ora11bak/control01.ctl"
  control_files            = "/opt/oracle/flash_recovery_area/ora11bak/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "/opt/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 3882M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=ora11bakXDB)"
  audit_file_dest          = "/opt/oracle/admin/ora11bak/adump"
  audit_trail              = "DB"
  db_name                  = "ora11bak"
  open_cursors             = 300
  pga_aggregate_target     = 1595M
  diagnostic_dest          = "/opt/oracle"
Fri May 17 05:03:15 2013
ksuapc : ORA-1033 foreground process starts before PMON
Fri May 17 05:03:15 2013
ksuapc : ORA-1033 foreground process starts before PMON
Fri May 17 05:03:16 2013
ksuapc : ORA-1033 foreground process starts before PMON
Fri May 17 05:03:16 2013
ksuapc : ORA-1033 foreground process starts before PMON
Fri May 17 05:03:16 2013
ksuapc : ORA-1033 foreground process starts before PMON
Fri May 17 05:03:16 2013
ksuapc : ORA-1033 foreground process starts before PMON
…………

该错误的原因是数据库在启动过程中有前台进程连接数据库导致,该现象是数据库bug 8991997,该bug影响版本为:11.2.0.1/11.1.0.7,在11.2.0.1.1开始修复

ORA-600[2037]与ORA-07445[kcbs_dump_adv_state]错误

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

标题：ORA-600[2037]与ORA-07445[kcbs_dump_adv_state]错误

一台win oracle 数据库，重启后发现数据库无法访问，检查发现是Bug 4899479,但是oracle未提供完整的解决方法,这里根据自己对于数据库启动过程的理解，通过屏蔽前滚和回滚，拉起来数据库
数据库版本平台信息

ORACLE:11.1.0.7
OS:WIN 2008 R2 X64

数据库启动报错

Tue Apr 16 12:36:31 2013
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 28878 redo blocks read, 7353 data blocks need recovery
Started redo application at
 Thread 1: logseq 7960, block 14132
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7960 Reading mem 0
  Mem# 0: D:\APP\SDWLJG-DB101\ORADATA\WLJG\REDO01.LOG
Tue Apr 16 12:36:32 2013
RECOVERY OF THREAD 1 STUCK AT BLOCK 915068 OF FILE 9
Hex dump of (file 9, block 1698691) in trace file c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\trace\wljg_p001_1500.trc
Corrupt block relative dba: 0x0259eb83 (file 9, block 1698691)
Bad header found during crash/instance recovery
Data in bad block:
 type: 0 format: 0 rdba: 0x0000a206
 last change scn: 0x2359.0259eb83 seq: 0xf7 flg: 0x0b
 spare1: 0x0 spare2: 0x0 spare3: 0x601
 consistency value in tail: 0x02c10243
 check value in block header: 0x0
 block checksum disabled
Reread of rdba: 0x0259eb83 (file 9, block 1698691) found valid data
Slave exiting with ORA-1172 exception
Errors in file c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\trace\wljg_p001_1500.trc:
ORA-01172: recovery of thread 1 stuck at block 915068 of file 9
ORA-01151: use media recovery to recover block, restore backup if needed
Tue Apr 16 12:36:32 2013
Errors in file c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\trace\wljg_p003_4088.trc  (incident=187558):
ORA-00600: internal error code, arguments: [2037], [12619645], [41474], [6], [1], [247], [12619645], [0], [], [], [], []
Incident details in: c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\incident\incdir_187558\wljg_p003_4088_i187558.trc
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+1352] [ACCESS_VIOLATION]
 [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x16BFD20] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [2037], [12619645], [41474], [6], [1], [247], [12619645], [0], [], [], [], []
Incident details in: c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\incident\incdir_187559\wljg_p003_4088_i187559.trc
Errors in file c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\trace\wljg_p006_1216.trc  (incident=187567):

这里提示file 9 block 915068异常，但是通过dbv检查发现file 9无任何坏块.

trace文件内容

Dump continued from file: c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\trace\wljg_p003_4088.trc
ORA-00600: internal error code, arguments: [2037], [12620930], [41474], [2], [1], [247], [12619645], [0], [], [], [], []
** DBGRL Error: ARB Alert Log
** DBGRL Error: <msg time='2013-04-16T11:05:58.522+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='dbgexProcessError:1097:3370026720' type='TRACE' level='16'
 host_id='SDWLSCJG-DB' host_addr='172.18.1.15'>
 <txt>Incident details in: c:\app\sdwljg-db101\diag\rdbms\wljg\wlj
========= Dump for incident 129879 (ORA 600 [2037]) ========
*** 2013-04-16 11:05:58.522
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+111        CALL???  skdstdst()+0         000000000 000000000 01CFC9B80
                                                   000000200
ksedst()+63          CALL???  ksedst1()+0          000000005 021B00600 005D30C80
                                                   000002004
dbkedDefDump()+1012  CALL???  ksedst()+0           000000000 000000000 000000000
                                                   000000000
ksedmp()+51          CALL???  dbkedDefDump()+0     000000003 000000002 021AF92C0
                                                   000405038
__PGOSF184_ksfdmp()  CALL???  ksedmp()+0           000000000 000000000 000000000
+27                                                27F00000000
dbgexPhaseII()+266   CALL???  __PGOSF184_ksfdmp()  00000000D 0082FAE50 000000000
                              +0                   000000004
dbgexProcessError()  CALL???  dbgexPhaseII()+0     021B00600 021AFCA50 000000201
+1313                                              000000000
dbgeExecuteForError  CALL???  dbgexProcessError()  021B00600 021B07590 000000001
()+55                         +0                   000000000
dbgePostErrorKGE()+  CALL???  dbgeExecuteForError  021AFCA30 021AFCA80 00000002E
1608                          ()+0                 000000005
dbkePostKGE_kgsf()+  CALL???  dbgePostErrorKGE()+  01CFC99D0 021B0E080 000000258
65                            0                    021B0E080
kgeade()+556         CALL???  dbkePostKGE_kgsf()+  000002000 000000000 000000009
                              0                    000000004
kgeriv_int()+105     CALL???  kgeade()+0           3A4F00000003 000C09482
                                                   0FFFFFFFF 000000000
kgeriv()+27          CALL???  kgeriv_int()+0       3A9A024E0 000000000 01CFC9410
                                                   000000000
kgesiv()+102         CALL???  kgeriv()+0           0000008D5 0000008C3 021AFD9A0
                                                   000AFDC73
ksesic7()+125        CALL???  kgesiv()+0           006371F20 000000007 27F912000
                                                   200000004
kcoexam()+248        CALL???  ksesic7()+0          2000007F5 000000000 000C09482
                                                   000000000
kcbtema()+2154       CALL???  kcoexam()+0          27FFC22C8 39E113470 3A940BBB8
                                                   000000000
kcrpap()+355         CALL???  kcbtema()+0          27FFC22C8 28BFC2628 000000000
                                                   021B10200
kcrpdv()+1655        CALL???  kcrpap()+0           021B101A0 000000002 000000004
                                                   000000512
kxfprdp()+1384       CALL???  kcrpdv()+0           3A7AD3098 000000000 00000000C
                                                   00757CF00
opirip()+1396        CALL???  kxfprdp()+0          00000001E 005CDB518 021AFF9E0
                                                   000000000
opidrv()+855         CALL???  opirip()+0           000000032 000000004 021AFFD30
                                                   000000000
sou2o()+52           CALL???  opidrv()+213         000000032 000000004 021AFFD30
                                                   021AFFDB0
opimai_real()+295    CALL???  sou2o()+0            000000000 7FEFD9819B5
                                                   000000000 000000000
opimai()+96          CALL???  opimai_real()+0      000000000 000000000 000000000
                                                   000000000
BackgroundThreadSta  CALL???  opimai()+0           021AFFE98 000000001 000000000
rt()+695                                           000000000
00000000775AF56D     CALL???  BackgroundThreadSta  00A26B7A0 000000000 000000000
                              rt()+0               000000000
0000000077923281     CALL???  00000000775AF560     000000000 000000000 000000000
                                                   000000000
--------------------- Binary Stack Dump ---------------------

查询mos发现During Startup (Open Database) Alert Log Shows ORA-600[2037] and ORA-7445[kcbs_dump_adv_state] [ID 551993.1]和我们这里展示的错误相符,引起该问题的原因主要是因为:The database may crash and fail to open due to undo/redo corruption if you are using distributed transactions.因为使用分布式事务的时候，数据库crash导致undo/redo corruption,从而使得数据库无法正常启动.

故障处理思路
因为通过数据库alert日志可以知道,数据库是在做前滚的时候并发进程失败,设置fast_start_parallel_rollback=false,禁止数据库实例恢复并发,可以恢复依然失败.因为前滚过不去,那就通过设置隐含参数禁止数据库前滚,在open数据库的过程中发现ora-600[2662]错误,推进scn，继续open数据库发现ora-600[4194],通过设置undo管理模式,屏蔽事务,屏蔽回滚段等方法,终于重新open库并重建undo,然后重建库算是完成恢复任务

创建控制文件遭遇ORA-600 kccscf_1

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

标题：创建控制文件遭遇ORA-600 kccscf_1

数据库版本10.2.0.1创建控制文件出现ORA-00600[kccscf_1]错误

SQL> CREATE CONTROLFILE REUSE DATABASE "ITPLUSDW" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292000
  7  LOGFILE
  8    GROUP 1 '/backup/oracle/oradata/redo01.log'  SIZE 50M,
  9    GROUP 2 '/backup/oracle/oradata/redo02.log'  SIZE 50M,
 10    GROUP 3 '/backup/oracle/oradata/redo03.log'  SIZE 50M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/backup/oracle/oradata/system01.dbf',
 14    '/backup/oracle/oradata/undotbs01.dbf',
 15    '/backup/oracle/oradata/sysaux01.dbf',
 16    '/backup/oracle/oradata/users01.dbf',
 17    '/backup/oracle/oradata/itplus_data',
 18    '/backup/oracle/oradata/invertories_data',
 19    '/backup/oracle/oradata/inventories_data01',
 20    '/backup/oracle/oradata/itplus_discover',
 21    '/backup/oracle/oradata/inventories_data02',
 22    '/backup/oracle/oradata/inventories_data03',
 23    '/backup/oracle/oradata/inventories_data05',
 24    '/backup/oracle/oradata/inventories_data04'
 25  CHARACTER SET ZHS16CGB231280
 26  ;
CREATE CONTROLFILE REUSE DATABASE "ITPLUSDW" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00600: internal error code, arguments: [kccscf_1], [9], [292000], [65535],[], [], [], []

alert日志错误

Default Temporary Tablespace will be necessary for a locally managed database in future release
Tue Feb 26 15:49:36 2013
Errors in file /backup/oracle/admin/udump/itplustest_ora_25001.trc:
ORA-00600: internal error code, arguments: [kccscf_1], [9], [292000], [65535], [], [], [], []
Tue Feb 26 15:49:36 2013
Errors in file /backup/oracle/admin/udump/itplustest_ora_25001.trc:
ORA-00600: internal error code, arguments: [kccscf_1], [9], [292000], [65535], [], [], [], []
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "ITPLUSDW" NORESETLOGS  ARCHIVELOG

查询mos发现Create Control File fails with ORA-600 [kccscf_1] [ID 387210.1],中有描述，该bug在10.2.0.1到11.1.0.6版本中,如果MAXLOGHISTORY超过了65535均有可能出现，在10.2.0.4和11.1.0.6中修复，部分版本有Patch 4877360修复该问题。在不能升级或者没有patch的情况下，解决该问题的思路是设置MAXLOGHISTORY为65535或者更小值,重建控制文件

SQL TUNING导致ORA-07445[qsmmixComputeClusteringFactor()+386]

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

标题：SQL TUNING导致ORA-07445[qsmmixComputeClusteringFactor()+386]

在11.2.0.2版本中sql tuning可能导致ORA-07445[qsmmixComputeClusteringFactor()+386]错误
系统版本和平台信息

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0/dbhome_1
System name:	Linux
Node name:	FPMS01DB
Release:	2.6.18-238.el5
Version:	#1 SMP Sun Dec 19 14:22:44 EST 2010
Machine:	x86_64
VM name:	VMWare Version: 6
Instance name: tis
Redo thread mounted by this instance: 1
Oracle process number: 41
Unix process pid: 16822, image: oracle@FPMS01DB (J003)

alert日志信息

Thu Jan 10 22:00:02 2013
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Thu Jan 10 22:02:39 2013
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4]
[PC:0x597FEB4, qsmmixComputeClusteringFactor()+386] [flags: 0x0, count: 1]
Errors in file /oracle/diag/rdbms/tis/tis/trace/tis_j001_11073.trc  (incident=80033):
ORA-07445: exception encountered: core dump [qsmmixComputeClusteringFactor()+386]
[SIGSEGV] [ADDR:0x4] [PC:0x597FEB4] [Address not mapped to object] []
Incident details in: /oracle/diag/rdbms/tis/tis/incident/incdir_80033/tis_j001_11073_i80033.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Jan 10 22:02:51 2013
Dumping diagnostic data in directory=[cdmp_20130110220251],
requested by (instance=1, osid=11073 (J001)), summary=[incident=80033].

通过这里可以看出来，出现ORA-07445错误的原因很可能和SQL TUNING相关

trace文件信息

Dump continued from file: /oracle/diag/rdbms/tis/tis/trace/tis_j003_16822.trc
ORA-07445: exception encountered: core dump [qsmmixComputeClusteringFactor()+386]
[SIGSEGV] [ADDR:0x4] [PC:0x597FEB4] [Address not mapped to object] []
========= Dump for incident 80008 (ORA 7445 [qsmmixComputeClusteringFactor()+386]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4]
[PC:0x597FEB4, qsmmixComputeClusteringFactor()+386] [flags: 0x0, count: 1]
Registers:
%rax: 0x00002b0b684593d0 %rbx: 0x0000000000000003 %rcx: 0x0000000000000000
%rdx: 0x00002b0b684593d0 %rdi: 0x00007fff758ceff8 %rsi: 0x0000000000000000
%rsp: 0x00007fff758ced00 %rbp: 0x00007fff758cf5d0  %r8: 0x00002b0b684593a8
 %r9: 0x00002b0b683c8e60 %r10: 0x0000000000000017 %r11: 0x0000000000000000
%r12: 0x00002b0b684197a0 %r13: 0x00002b0b68419928 %r14: 0x00002b0b66ad2430
%r15: 0x00002b0b66d95590 %rip: 0x000000000597feb4 %efl: 0x0000000000010202
  qsmmixComputeClusteringFactor()+366 (0x597fea0) call 0x597fc62
  qsmmixComputeClusteringFactor()+371 (0x597fea5) lea -0x5d8(%rbp),%rdi
  qsmmixComputeClusteringFactor()+378 (0x597feac) mov 0x60(%rax),%rsi
  qsmmixComputeClusteringFactor()+382 (0x597feb0) mov 0x60(%rax),%rcx
> qsmmixComputeClusteringFactor()+386 (0x597feb4) movzwl 0x4(%rcx),%edx
  qsmmixComputeClusteringFactor()+390 (0x597feb8) add $6,%rsi
  qsmmixComputeClusteringFactor()+394 (0x597febc) mov %rax,-0x20(%rbp)
  qsmmixComputeClusteringFactor()+398 (0x597fec0) call 0xa056f0
  qsmmixComputeClusteringFactor()+403 (0x597fec5) mov -0x8(%rbp),%edx
*** 2013-01-09 22:50:24.205
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=durgh9p25f6bb) -----
/* SQL Analyze(788,1) */ SELECT  distinct
			                   null  as isdistribution,
			                   null as seqno,
			                   to_char(T1.SEQNO) as TRADEID
…………
from T_XIFENFEI
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xc5b644e8     11816  package body SYS.DBMS_SQLTUNE_INTERNAL
0xc54ca918         7  SYS.WRI$_ADV_SQLTUNE
0xd55aec98       587  package body SYS.PRVT_ADVISOR
0xd55aec98      2655  package body SYS.PRVT_ADVISOR
0xc5f66c70       241  package body SYS.DBMS_ADVISOR
0xc5418dc8       821  package body SYS.DBMS_SQLTUNE
0xd50d38c0         4  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+36        call     kgdsdst()            000000000 ? 000000000 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000001 ? 000000003 ?
ksedst1()+98         call     skdstdst()           000000000 ? 000000000 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000000 ? 000000003 ?
ksedst()+34          call     ksedst1()            000000001 ? 000000001 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000000 ? 000000003 ?
dbkedDefDump()+2741  call     ksedst()             000000001 ? 000000001 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000000 ? 000000003 ?
ksedmp()+36          call     dbkedDefDump()       000000003 ? 000000003 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000000 ? 000000003 ?
ssexhd()+2366        call     ksedmp()             000000003 ? 000000003 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000000 ? 000000003 ?
__sighandler()       call     ssexhd()             00000000B ? 2B0B65B74D70 ?
                                                   2B0B65B74C68 ? 000000001 ?
                                                   000000000 ? 000000003 ?
qsmmixComputeCluste  signal   __sighandler()       7FFF758CEFF8 ? 000000000 ?
ringFactor()+386                                   2B0B684593D0 ? 000000000 ?
                                                   2B0B684593A8 ? 2B0B683C8E60 ?
qsmmixGenFakeIdxSta  call     qsmmixComputeCluste  2B0B68419928 ? 7FFF758CEFF8 ?
ts()+1025                     ringFactor()         2B0B66D95590 ?
                                                   BFF0000000000000 ?
                                                   000000000 ? 2B0B683C8E60 ?
qsmmixPopulateIdxSt  call     qsmmixGenFakeIdxSta  2B0B66AD2430 ? 2B0B68419928 ?
ats()+71                      ts()                 2B0B684197A0 ? 2B0B66B92630 ?
                                                   40BAD30000000000 ?
                                                   2B0B683C8E60 ?
qsmmixSetKkotbixt()  call     qsmmixPopulateIdxSt  2B0B66B92630 ? 2B0B66AD2430 ?
+479                          ats()                2B0B68419928 ? 2B0B66B92630 ?
                                                   40BAD30000000000 ?
                                                   2B0B683C8E60 ?
qsmmixReturnCandToO  call     qsmmixSetKkotbixt()  2B0B66B92630 ? 2B0B66AD2430 ?
pt()+656                                           2B0B683F0090 ? 2B0B6845DCC8 ?
                                                   2B0B6845C2B8 ? 2B0B68462F08 ?
qsmmixOptimizerGenI  call     qsmmixReturnCandToO  2B0B66B92630 ? 2B0B66AD2430 ?
dxCand()+601                  pt()                 2B0B68462F60 ? 2B0B6845DCC8 ?
                                                   2B0B6845C2B8 ? 2B0B68462F08 ?
qsmmixOptimizerSetu  call     qsmmixOptimizerGenI  0913F73A0 ? 2B0B6845C2B8 ?
pIdxCand()+351                dxCand()             000000009 ? 000000001 ?
                                                   2B0B6845C2B8 ? 2B0B68462F08 ?
kkoiqb()+13730       call     qsmmixOptimizerSetu  0913F73A0 ? 2B0B683EEFD8 ?
                              pIdxCand()           000000009 ? 000000001 ?
                                                   2B0B6845C2B8 ? 2B0B68462F08 ?
kkooqb()+632         call     kkoiqb()             2B0B66B6FF88 ? 000000000 ?
                                                   000000000 ? 000000001 ?
                                                   2B0B6845C2B8 ? 2B0B68462F08 ?
kkoqbc()+2359        call     kkooqb()             2B0B66B6FF88 ? 000000000 ?
                                                   000000000 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apakkoqb()+166       call     kkoqbc()             7FFF758D2DF0 ? 2B0B66B6FF88 ?
                                                   000000000 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apaqbdDescendents()  call     apakkoqb()           7FFF758D2DF0 ? 2B0B66B6FF88 ?
+457                                               0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apaqbdList()+71      call     apaqbdDescendents()  7FFF758D2DF0 ? 2B0B66B6FF88 ?
                                                   0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apaqbdDescendents()  call     apaqbdList()         7FFF758D2DF0 ? 2B0B66B6FF88 ?
+710                                               0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apaqbdList()+71      call     apaqbdDescendents()  0913F73A0 ? 2B0B66C975C8 ?
                                                   0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apaqbd()+9           call     apaqbdList()         0913F73A0 ? 2B0B66C975C8 ?
                                                   0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apadrv()+861         call     apaqbd()             0913F73A0 ? 2B0B66C975C8 ?
                                                   0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
opitca()+1971        call     apadrv()             0913F73A0 ? 2B0B66C975C8 ?
                                                   0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
kksSetBindType()+76  call     opitca()             2B0B671A77F8 ? 0913F73A0 ?
05                                                 7FFF758D4D10 ? 000000004 ?
                                                   100000000 ? 2B0B00000000 ?
kksfbc()+10664       call     kksSetBindType()     7FFF758D4D10 ? 2B0B671A77F8 ?
                                                   7FFF758D4DA0 ? 000000102 ?
                                                   7FFF0000001F ? 000000000 ?
opiexe()+2268        call     kksfbc()             2B0B671A77F8 ? 000000003 ?
                                                   000000102 ? 000000000 ?
                                                   000000000 ? 7FFF758D5D70 ?
kpoal8()+2226        call     opiexe()             000000049 ? 000000003 ?
                                                   7FFF758D6310 ? 000000000 ?
                                                   000000000 ? 7FFF758D5D70 ?
opiodr()+910         call     kpoal8()             00000005E ? 000000000 ?
                                                   7FFF758DA288 ? 000000000 ?
                                                   000000000 ? 000000001 ?
kpoodrc()+31         call     opiodr()             00000005E ? 000000000 ?
                                                   7FFF758DA288 ? 000000000 ?
                                                   00989C970 ? 000000001 ?
rpiswu2()+1618       call     kpoodrc()            7FFF758D75C0 ? 000000000 ?
                                                   7FFF758DA288 ? 000000000 ?
                                                   00989C970 ? 000000001 ?
kpoodr()+617         call     rpiswu2()            0D3C52C80 ? 000000023 ?
                                                   2B0B65EDFAAC ? 000000004 ?
                                                   2B0B6C6B7340 ? 000000023 ?
upirtrc()+2417       call     kpoodr()             2B0B65EBF170 ? 00000005E ?
                                                   7FFF758DA288 ? 000000000 ?
                                                   2B0B6C6B7340 ? 000000023 ?
kpurcsc()+93         call     upirtrc()            2B0B65EBF170 ? 00000005E ?
                                                   7FFF758DA288 ? 7FFF758DA400 ?
                                                   7FFF758DB420 ? 2B0B65C7D308 ?
kpuexec()+10804      call     kpurcsc()            2B0B65EBF170 ? 00000005E ?
                                                   7FFF758DA288 ? 7FFF758DA400 ?
                                                   7FFF758DB420 ? 2B0B65C7D308 ?
OCIStmtExecute()+34  call     kpuexec()            2B0B65E3E5B8 ? 2B0B65E3E538 ?
                                                   7FFF758DA280 ? 000000000 ?
                                                   E0C28C4F00000000 ?
                                                   000000000 ?
qksanExecSql()+743   call     OCIStmtExecute()     2B0B65E3E5B8 ? 2B0B65E3E538 ?
                                                   7FFF758DA280 ? 000000000 ?
                                                   E0C28C4F00000000 ?
                                                   000000000 ?
qksanAnalyzeSql()+2  call     qksanExecSql()       7FFF758DDCA8 ? 2B0B65C7D308 ?
363                                                2B0B65C7D3E8 ? 2B0B65E3E538 ?
                                                   7FFF758DDC28 ? 7FFF758DE3A9 ?
qsmmixProcessQuery(  call     qksanAnalyzeSql()    7FFF758DDCA8 ? 2B0B67E90DA8 ?
)+1089                                             00000B3AB ? 2B0B65FBD218 ?
                                                   100000023 ? 7FFF758DE3A9 ?
qsmmixSqlTuneAnalyz  call     qsmmixProcessQuery(  2B0B65FBD218 ? 000000005 ?
eIdx()+449                    )                    2B0B67E90DA8 ? 2B0B65F37BF4 ?
                                                   2B0B65FBD218 ? 7FFF00000023 ?
kestsiIndexAnalyzeD  call     qsmmixSqlTuneAnalyz  7FFF758DE370 ? 000000005 ?
rv()+794                      eIdx()               2B0B67E90DA8 ? 2B0B65F37BF4 ?
                                                   2B0B65FBD218 ? 7FFF00000023 ?
kestsTuneSqlDrv()+3  call     kestsiIndexAnalyzeD  7FFF758E0548 ? 7FFF758E08C0 ?
83                            rv()                 7FFF758DFC78 ? 7FFF758E09B0 ?
                                                   7FFF758DED50 ? 7FFF758DED58 ?
kesaiExecAction()+9  call     kestsTuneSqlDrv()    7FFF758E0540 ? 7FFF758E08C0 ?
81                                                 7FFF758DFC78 ? 7FFF758E09B0 ?
                                                   7FFF758DED50 ? 7FFF758DED58 ?
kesaiTuneSqlDrv()+6  call     kesaiExecAction()    7FFF758DF420 ? 7FFF758E08C0 ?
258                                                7FFF758E09B0 ? 7FFF758E05E0 ?
                                                   7FFF758E0A68 ? 7FFF758E0A60 ?
spefcifa()+225       call     kesaiTuneSqlDrv()    7FFF758E21B8 ? 2B0B65CA3180 ?
                                                   000000000 ? 7FFF758E05E0 ?
                                                   0C5FB0033 ? 2B0B65C362B8 ?
spefmccallstd()+421  call     spefcifa()           7FFF758E1CE0 ? 000000004 ?
                                                   2B0B65CA3108 ? 7FFF758E0F30 ?
                                                   0C5FB0033 ? 2B0B65C362B8 ?
pextproc()+36        call     spefmccallstd()      7FFF758E20E0 ? 7FFF758E1D88 ?
                                                   7FFF758E1AA0 ? 7FFF758E1CE0 ?
                                                   000000000 ? 2B0B65C362B8 ?
__PGOSF589_peftrust  call     pextproc()           7FFF758E20E0 ? 7FFF758E1D88 ?
ed()+145                                           7FFF758E1AA0 ? 7FFF758E1CE0 ?
                                                   000000000 ? 2B0B65C362B8 ?
__PGOSF633_psdexsp(  call     __PGOSF589_peftrust  7FFF758E20E0 ? 7FFF758E1D88 ?
)+255                         ed()                 7FFF758E1AA0 ? 7FFF758E1CE0 ?
                                                   000000000 ? 2B0B65C362B8 ?
rpiswu2()+1618       call     __PGOSF633_psdexsp(  7FFF758E18D0 ? 7FFF758E20E0 ?
                              )                    7FFF758E1AA0 ? 000020003 ?
                                                   0037500E0 ? 7FFF758E3100 ?
psdextp()+695        call     rpiswu2()            0D3C52C80 ? 000000000 ?
                                                   7FFF758E1910 ? 000000002 ?
                                                   7FFF758E1950 ? 000000000 ?
pefccal()+726        call     psdextp()            7FFF758E3100 ? 7FFF758E1D88 ?
                                                   7FFF758E1AA0 ? 000000000 ?
                                                   000020003 ? 7FFF758E20E0 ?
pefcal()+219         call     pefccal()            7FFF758E20E0 ? 000A324C2 ?
                                                   00B7C8EA0 ? 000000000 ?
                                                   000020003 ? 7FFF758E20E0 ?
pevm_FCAL()+164      call     pefcal()             7FFF758E20E0 ? 00B7C9050 ?
                                                   2B0B65C835B8 ? 000000000 ?
                                                   000020003 ? 7FFF758E20E0 ?
pfrinstr_FCAL()+70   call     pevm_FCAL()          2B0B65C835B8 ? 0A6FB80D8 ?
                                                   2B0B65C835B8 ? 0A6FB8030 ?
                                                   000020003 ? 7FFF758E20E0 ?
pfrrun_no_tool()+63  call     pfrinstr_FCAL()      2B0B65C835B8 ? 0A5405930 ?
                                                   2B0B65C83628 ? 0A6FB8030 ?
                                                   000020003 ? 7FFF758E20E0 ?
pfrrun()+622         call     pfrrun_no_tool()     2B0B65C835B8 ? 0A5405930 ?
                                                   2B0B65C83628 ? 0A6FB8030 ?
                                                   000020003 ? 7FFF758E20E0 ?
plsql_run()+644      call     pfrrun()             2B0B65C835B8 ? 000000000 ?
                                                   2B0B65C83628 ? 7FFF758E3100 ?
                                                   000020003 ? 0928C7098 ?
peicnt()+296         call     plsql_run()          2B0B65C835B8 ? 000000001 ?
                                                   000000000 ? 7FFF758E3100 ?
                                                   000020003 ? 000000000 ?
kkxexe()+521         call     peicnt()             7FFF758E3100 ? 2B0B65C835B8 ?
                                                   2B0B65CA4FD8 ? 7FFF758E3100 ?
                                                   2B0B65CA2F30 ? 000000000 ?
opiexe()+17478       call     kkxexe()             2B0B65C8BD58 ? 2B0B65C835B8 ?
                                                   000000000 ? 7FFF758E3100 ?
                                                   2B0B65CA2F30 ? 000000000 ?
kpoal8()+2226        call     opiexe()             000000049 ? 000000003 ?
                                                   7FFF758E4730 ? 7FFF758E3100 ?
                                                   2B0B65CA2F30 ? 000000000 ?
opiodr()+910         call     kpoal8()             00000005E ? 000000000 ?
                                                   7FFF758E81A8 ? 7FFF758E3100 ?
                                                   2B0B65CA2F30 ? 7FFF00000001 ?
kpoodr()+648         call     opiodr()             00000005E ? 000000000 ?
                                                   7FFF758E81A8 ? 000000000 ?
                                                   00989C970 ? 7FFF00000001 ?
upirtrc()+2417       call     kpoodr()             2B0B65C796A8 ? 00000005E ?
                                                   7FFF758E81A8 ? 000000000 ?
                                                   00989C970 ? 7FFF00000001 ?
kpurcsc()+93         call     upirtrc()            2B0B65C796A8 ? 00000005E ?
                                                   7FFF758E81A8 ? 7FFF758E8320 ?
                                                   7FFF758E9340 ? 2B0B65C7D308 ?
kpuexec()+11692      call     kpurcsc()            2B0B65C796A8 ? 00000005E ?
                                                   7FFF758E81A8 ? 7FFF758E8320 ?
                                                   7FFF758E9340 ? 2B0B65C7D308 ?
OCIStmtExecute()+34  call     kpuexec()            2B0B65C88290 ? 2B0B65C88210 ?
                                                   7FFF758E81A0 ? 000000001 ?
                                                   7FFF00000000 ? 000000000 ?
__PGOSF529_jslvec_e  call     OCIStmtExecute()     2B0B65C88290 ? 2B0B65C88210 ?
xeccb()+2207                                       7FFF758E81A0 ? 000000001 ?
                                                   7FFF00000000 ? 000000000 ?
jslvswu()+54         call     __PGOSF529_jslvec_e  7FFF758EC39C ? 2B0B65C88210 ?
                              xeccb()              2B0B65C88210 ? 000000001 ?
                                                   7FFF00000000 ? 000000000 ?
jslve_execute0()+22  call     jslvswu()            000000000 ? 7FFF00000000 ?
17                                                 000000000 ? 000000001 ?
                                                   7FFF00000000 ? 000000000 ?
jslve_execute()+327  call     jslve_execute0()     7FFF758EE2B4 ? 000005946 ?
                                                   000000002 ? 7FFF758EE2A0 ?
                                                   000000000 ? 0FFFFFFFF ?
rpiswu2()+1618       call     jslve_execute()      7FFF758EE150 ? 000000002 ?
                                                   7FFF758EE2B4 ? 000005946 ?
                                                   7FFF758EE2A0 ? 0FFFFFFFF ?
kkjex1e()+374        call     rpiswu2()            0D3C52C80 ? 000000000 ?
                                                   7FFF758EE170 ? 000000002 ?
                                                   7FFF758EE190 ? 000000000 ?
kkjsexe()+705        call     kkjex1e()            7FFF758EE2B4 ? 000005946 ?
                                                   000000002 ? 7FFF758EE2A0 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
kkjrdp()+689         call     kkjsexe()            7FFF758EE2B4 ? 000005946 ?
                                                   000000001 ? 7FFF758EE2A0 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
opirip()+953         call     kkjrdp()             7FFF758EE2B4 ? 000005946 ?
                                                   000000001 ? 7FFF758EE2A0 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
opidrv()+598         call     opirip()             000000032 ? 000000004 ?
                                                   7FFF758EFA28 ? 7FFF758EE2A0 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
sou2o()+98           call     opidrv()             000000032 ? 000000004 ?
                                                   7FFF758EFA28 ? 7FFF758EE2A0 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
opimai_real()+261    call     sou2o()              7FFF758EFA00 ? 000000032 ?
                                                   000000004 ? 7FFF758EFA28 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
ssthrdmain()+252     call     opimai_real()        000000000 ? 7FFF758EFBF0 ?
                                                   000000004 ? 7FFF758EFA28 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
main()+196           call     ssthrdmain()         000000003 ? 7FFF758EFBF0 ?
                                                   000000001 ? 000000000 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
__libc_start_main()  call     main()               000000003 ? 7FFF758EFD90 ?
+244                                               000000001 ? 000000000 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
_start()+36          call     __libc_start_main()  000A077C8 ? 000000001 ?
                                                   7FFF758EFD88 ? 000000000 ?
                                                   0D57A5F08 ? 000000003 ?
--------------------- Binary Stack Dump ---------------------

分析Stack可以发现，他们和MOS[]中的非常类此,可以断定是该Bug 9746210
skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp<- ssexhd <- sighandler <- qsmmixComputeClusteringFactor <- qsmmixGenFakeIdxStats <- qsmmixPopulateIdxStats <- qsmmixSetKkotbixtqsmmixReturnCandToOpt <- qsmmixOptimizerGenIdxCand <- qsmmixOptimizerSetupIdxCandResponse
处理建议

The is fixed in following versions:
 12.1 (Future Release)
 11.2.0.3 (Server Patch Set)
 11.2.0.2.4 Patch Set Update
 11.2.0.2 Bundle Patch 12 for Exadata Database
 11.2.0.2 Patch 11 on Windows Platforms
To resolve the issue:
Either
Upgrade to the over versions
Or
Apply Patch 9746210

补充说明
对于SQL TUNING功能,在觉得多少人的环境中都不需要这个,可以考虑禁用该功能来屏蔽该错误

BEGIN
dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;

参考文档
SQL Tuning query fails with ORA-7445 [qsmmixComputeClusteringFactor] [ID 1483654.1]
Error ORA-07445 Qsmmixcomputeclusteringfactor From SQL Tuning [ID 1359148.1]
Bug 9746210 – ORA-7445 [qsmmixComputeClusteringFactor] from SQL tuning [ID 9746210.8]

重建控制文件引发ORA-00218故障

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

标题：重建控制文件引发ORA-00218故障

遇到一个案例在数据库启动的时候报ORA-00218错误,而这个故障的引起原因是因为重建控制文件的时候,有一个控制文件无法创建,而导致了原有的控制文件被破坏,提醒:创建控制文件之前,最好对原有控制文件进行备份
数据库启动报ORA-00218错误

SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size                  2020224 bytes
Variable Size              92277888 bytes
Database Buffers          188743680 bytes
Redo Buffers                2170880 bytes
ORA-00218: block size 0 of control file
'/u01/app/oracle/oradata/zxy/control01.ctl' does not match DB_BLOCK_SIZE (0)

分析ORA-00218错误

Oracle10g Release 1 Message
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Error:	  ORA-00218  (ORA-218)
Text:	  block size %s of controlfile '%s' does not match DB_BLOCK_SIZE
	  (%s)
---------------------------------------------------------------------------
Cause:	The block size as stored in the controlfile header is different
	from the value of the initialization parameter DB_BLOCK_SIZE. This
	might be due to an incorrect setting of DB_BLOCK_SIZE, or else
	might indicate that the controlfile has either been corrupted or
	belongs to a different database.
Action:	Restore a good copy of the controlfile. If the controlfile is
	known to be clean set the DB_BLOCK_SIZE to match controlfile
	headers block size value.
Oracle 9.2 or Earlier Error Message
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Error:  ORA 218
Text:   control file <name> was created with block size <num> now is <num>
-------------------------------------------------------------------------------
Cause:  The physical block size, stored in the control file header, was
        different in physical block size returned by the O/S.
        This usually indicates that the control file was corrupted.
Action: Restore a good copy of the control file.
        For more information about control files and recovery, see the index
        entries on "control files," "control files, backing up," "control
        files, recovery and" in <Oracle7 Server Concepts>.

通过这里可以知道,很可能是控制文件header的db_block_size和参数文件中的db_block_size的大小不一致,从而导致了该问题,而从启动数据库的错误提示上看,是控制文件的block size 为0.

分析控制文件

--dbv检查控制文件
[oracle@zxy bdump]$ dbv file='/u01/app/oracle/oradata/zxy/control01.ctl' blocksize=16384
DBVERIFY: Release 10.2.0.1.0 - Production on Sun Jan 6 23:39:32 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/zxy/control01.ctl
DBVERIFY - Verification complete
Total Pages Examined         : 450
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 450
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 0 (0.0)
--strings读控制文件
[oracle@zxy bdump]$ strings /u01/app/oracle/oradata/zxy/control01.ctl
}|{z
--正常库dbv检查控制文件
E:\oracle\oradata\xifenfei>dbv file=CONTROL01.CTL blocksize=16384
DBVERIFY: Release 11.2.0.3.0 - Production on 星期一 1月 7 10:26:46 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - 开始验证: FILE = E:\ORACLE\ORADATA\XIFENFEI\CONTROL01.CTL
DBVERIFY - 验证完成
检查的页总数: 600
处理的页总数 (数据): 0
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其他): 65
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 535
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 39198 (65535.39198)

检查参数文件db_block_size

SQL> show parameter db_block_size
TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

通过分析我们知道spfile中的db_block_size是正确的,而控制文件通过dbv和strings检测均为空值,证明是控制文件异常导致该问题,对于该问题可以通过重建控制文件或者还原备份控制文件来解决问题.

分析问题原因

--参数文件配置
control_files            = /u01/app/oracle/oradata/zxy/control01.ctl, /tmp/oradata/control04.ctl
--alert日志
Sun Jan  6 21:42:50 2013
CREATE CONTROLFILE REUSE DATABASE "ZXY" RESETLOGS  NOARCHIVELOG
 …………
CHARACTER SET AL32UTF8
Sun Jan  6 21:42:50 2013
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Sun Jan  6 21:42:53 2013
Errors in file /u01/app/oracle/admin/zxy/udump/zxy_ora_3898.trc:
ORA-00200: control file could not be created
ORA-00202: control file: '/tmp/oradata/control04.ctl'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
--再次启动
control_files            = /u01/app/oracle/oradata/zxy/control01.ctl
--日志
ALTER DATABASE   MOUNT
Sun Jan  6 21:56:31 2013
ORA-00218: block size 0 of control file
'/u01/app/oracle/oradata/zxy/control01.ctl' does not match DB_BLOCK_SIZE (0)
Sun Jan  6 21:56:31 2013
ORA-218 signalled during: ALTER DATABASE   MOUNT...

创建控制文件,因为/tmp/oradata/目录不存在或者没有权限导致创建控制文件失败,而导致原来有的控制文件也失败

故障重现

--正常启动
control_files='/u01/oracle/oradata/XFF/control01.ctl'
SQL> startup pfile=/tmp/pfile
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1267164 bytes
Variable Size             109054500 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> alter database backup controlfile to trace as '/tmp/ctl';
Database altered.
--尝试重建控制文件
control_files='/u01/oracle/oradata/XFF/control01.ctl','/tmp/xifenfei/con.ctl'
SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/oracle/oradata/XFF/redo01.log'  SIZE 50M,
  9    GROUP 2 '/u01/oracle/oradata/XFF/redo02.log'  SIZE 50M,
 10    GROUP 3 '/u01/oracle/oradata/XFF/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/u01/oracle/oradata/XFF/system01.dbf',
 13    '/u01/oracle/oradata/XFF/undotbs01.dbf',
 14    '/u01/oracle/oradata/XFF/sysaux01.dbf',
 15    '/u01/oracle/oradata/XFF/users01.dbf',
 16    '/u01/oracle/oradata/XFF/xifenfei01.dbf',
 17    '/u01/oracle/oradata/XFF/users03.dbf'
 18  CHARACTER SET ZHS16GBK
 19  ;
CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/tmp/xifenfei/con.ctl'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
--使用原控制文件启动库
control_files='/u01/oracle/oradata/XFF/control01.ctl'
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1267164 bytes
Variable Size             109054500 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
ORA-00218: block size 0 of control file '/u01/oracle/oradata/XFF/control01.ctl'
does not match DB_BLOCK_SIZE (0)

补充参数文件中DB_BLOCK_SIZE不正确导致后果

DB_BLOCK_SIZE从8192修改为16384
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1267164 bytes
Variable Size             109054500 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 16384)

delete expired backup报ORA-19606错误处理

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

标题：delete expired backup报ORA-19606错误处理

数据库版本(exadata x2 1/4)

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

使用delete expired backup报ORA-19606错

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_4 channel at 12/05/2012 13:35:07
ORA-19606: Cannot copy or restore to snapshot control file

报错原因

The snapshot controlfile is cataloged as a controlfile coy and is now obsolete.
RMAN cannot delete this file as it is used by rman.

处理方法[ID 1215493.1]

Since RMAN will continue to use that file as it's snapshot copy,
you must change the location/name that RMAN is using before it will allow you to delete the file.
1. Set new name (or location) for RMAN to use for snapshot controlfile:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCF_temp.ORA';
2. Remove the snapshot controlfile from the RMAN information as a controlfile copy.
delete 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA' on operating system.
3. Crosscheck and delete the file from RMAN:
crosscheck controlfilecopy 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
delete expired controlfilecopy 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
4. Set the snapshot controlfile name (or location) to original:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
5. Or to set the snapshot controlfile name back to default value:
CONFIGURE SNAPSHOT CONTROLFILE NAME clear;

处理方法就是把SNAPSHOT CONTROLFILE的配置路径改变,然后删除控制文件快照

分类目录归档：ORA-xxxxx