Database SOS – 第76页 – Professional Database Recovery Support@+8617813235971

某医院存储掉线导致Oracle数据库故障恢复

xx医院存储突然掉线,导致数据库异常,现场工程师折腾了一天,问题依旧没有解决,无奈之下找到我们,希望我们能够帮忙恢复数据库.
启动报ORA-00600[2131]错误

Fri Nov 06 14:50:59 2015
ALTER DATABASE   MOUNT
This instance was first to mount
Fri Nov 06 14:50:59 2015
ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.4.4)(PORT=1521))' SCOPE=MEMORY SID='xifenfei1';
NOTE: Loaded library: System
SUCCESS: diskgroup DATA was mounted
NOTE: dependency between database xifenfei and diskgroup resource ora.DATA.dg is established
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13221.trc  (incident=191085):
ORA-00600: internal error code, arguments: [2131], [33], [32], [], [], [], [], [], [], [], [], []
Incident details in: /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_191085/xifenfei1_ora_13221_i191085.trc
Fri Nov 06 14:51:10 2015
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: ALTER DATABASE   MOUNT...

出现该错误的原因是由于：We are attempting to write a controlfile checkpoint progress record, but find we do not have the progress record generating this exception.由于控制文件异常导致,出现此类情况,我们一般使用单个控制文件一次尝试,如果都不可以考虑重建控制文件

由于坏块(逻辑/物理)导致数据库实例恢复无法进行

Beginning crash recovery of 2 threads
Started redo scan
kcrfr_rnenq: use log nab 393216
kcrfr_rnenq: use log nab 2
Completed redo scan
 read 4427 KB redo, 500 data blocks need recovery
Started redo application at
 Thread 1: logseq 5731, block 391398
 Thread 2: logseq 4252, block 520815
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5731 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_2.266.835331047
Recovery of Online Redo Log: Thread 2 Group 8 Seq 4252 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_8.331.835330421
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc  (incident=197486):
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Incident details in:/home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_197486/xifenfei1_ora_13770_i197486.trc
Fri Nov 06 15:03:09 2015
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc  (incident=197487):
ORA-01578: ORACLE data block corrupted (file # 2, block # 65207)
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 81045
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Incident details in:/home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_197487/xifenfei1_ora_13770_i197487.trc
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc:
ORA-01578: ORACLE data block corrupted (file # 2, block # 65207)
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 81045
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Recovery of Online Redo Log: Thread 2 Group 3 Seq 4253 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_3.332.835330505
Hex dump of (file 14, block 62536) in trace file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc
Reading datafile '+DATA/xifenfei/datafile/ht01.dbf' for corruption at rdba: 0x0380f448 (file 14, block 62536)
Reread (file 14, block 62536) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 62536 OF FILE 14
Fri Nov 06 15:03:13 2015
Abort recovery for domain 0
Aborting crash recovery due to error 1172
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc:
ORA-01172: recovery of thread 1 stuck at block 62536 of file 14
ORA-01151: use media recovery to recover block, restore backup if needed
Abort recovery for domain 0
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc:
ORA-01172: recovery of thread 1 stuck at block 62536 of file 14
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN...

查看资料发现和Bug 14301592 – Several errors by corrupt blocks shifted by 2 bytes in buffer cache during recovery caused by INDEX redo apply，可以通过ALLOW 1 CORRUPTION临时解决

使用ALLOW 1 CORRUPTION进行恢复,出现ORA-07445[kdxlin]错误

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/xifenfei/onlinelog/group_3.332.835330505
ORA-00279: change 700860458 generated at 11/05/2015 21:20:15 needed for thread
1
ORA-00289: suggestion : +ARCHIVE/xifenfei/xifenfei_1_5731_835324843.arc
ORA-00280: change 700860458 for thread 1 is in sequence #5731
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/xifenfei/onlinelog/group_2.266.835331047
ORA-00283: recovery session canceled due to errors
ORA-10562: Error occurred while applying redo to data block (file# 2, block#
70104)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 82289
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kdxlin()+4088] [SIGSEGV]
[ADDR:0xC] [PC:0x95FB572] [Address not mapped to object] []
ORA-01112: media recovery not started

ORA-07445[kdxlin()+4088]未找到类似说明,到了这一步,无法简单的恢复成功,只能通过设置隐含参数跳过实例恢复,尝试resetlog库

通过设置_allow_resetlogs_corruption参数继续恢复

SQL> startup pfile='/tmp/pfile.ora' mount;
ORACLE instance started.
Total System Global Area 7315603456 bytes
Fixed Size                  2267384 bytes
Variable Size            2566915848 bytes
Database Buffers         4731174912 bytes
Redo Buffers               15245312 bytes
Database mounted.
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: [kclchkblk_4], [0], [700869927],
[0], [700860464], [], [], [], [], [], [], []
Process ID: 13563
Session ID: 157 Serial number: 3

alert日志报错

Fri Nov 06 19:26:39 2015
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13563.trc  (incident=319140):
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927], [0], [700860464], [], [], [], [], [], [], []
Incident details in:/home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_319140/xifenfei1_ora_13563_i319140.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Redo thread 2 internally disabled at seq 1 (CKPT)
ARC1: Archiving disabled thread 2 sequence 1
Archived Log entry 9956 added for thread 2 sequence 1 ID 0x0 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13563.trc:
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927], [0], [700860464], [], [], [], [], [], [], []
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13563.trc:
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927], [0], [700860464], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 13563): terminating the instance due to error 600
Fri Nov 06 19:26:42 2015
Instance terminated by USER, pid = 13563
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (13563) as a result of ORA-1092
Fri Nov 06 19:26:42 2015
ORA-1092 : opitsk aborting process

这里是比较熟悉的ora-600[kclchkblk_4]错误,和ora-600[2662]错误类似,需要调整scn,由于数据库版本为11.2.0.4,无法使用常规方法调整scn,在修改控制文件,oradebug,bbed方法可供选择

使用oradebug方法处理
因为是asm环境,其他方法处理起来都相对麻烦

[oracle@wisetop1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 6 19:30:59 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/tmp/pfile.ora' mount;
ORACLE instance started.
Total System Global Area 7315603456 bytes
Fixed Size                  2267384 bytes
Variable Size            2566915848 bytes
Database Buffers         4731174912 bytes
Redo Buffers               15245312 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x06001AE70 4 0x2FAF0800
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER:  [06001AE70, 06001AE74) = 2FAF0800
SQL> alter database open;
Database altered.

至此数据库open成功,后续就是处理一些坏块的工作,并建议客户逻辑重建库.

Oracle 12C Active Data Guard Far Sync 配置

Active Data Guard Far Sync是Oracle 12c的新功能（也称为Far Sync Standby），Far Sync功能的实现是通过在距离主库(Primary Database)相对较近的地点配置Far Sync实例，主库(Primary Database) 同步(synchronous)传输redo到Far Sync实例，然后Far Sync实例再将redo异步(asynchronous)传输到终端备库(Standby Database)。这样既可以保证零数据丢失又可以降低主库压力。Far Sync实例只有密码文件，init参数文件和控制文件，而没有数据文件。如果redo 传输采用Maximum Availability模式，我们可以在距离生产中心(Primary Database)相对较近的地点配置Far Sync实例，主库(Primary Database)同步(synchronous)传输redo到Far Sync实例，保证零数据丢失（zero data loss），同时主库和Far Sync距离较近，网络延时很小，因此对主库性能影响很小。然后Far Sync实例再将redo异步(asynchronous)发送到终端备库(Standby Database)。如果redo 传输采用Maximum Performance模式，我们可以在距离生产中心(Primary Database)相对较近的地点配置Far Sync实例，主库(Primary Database) 异步传输redo到Far Sync实例，然后Far Sync实例再负责传输redo到其他多个终端备库(Standby Database)。这样可以减少主库向多个终端备库(Standby Database)传输redo的压力（offload）。 Far Sync配置对于Data Guard 角色转换(role transitions)是透明的，即switchover/failover命令方式与12c之前相同。
1.主库配置fra

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=200G;
System altered.
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
System altered.

2.启用归档模式和强制日志

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size                  4585912 bytes
Variable Size             671090248 bytes
Database Buffers         1828716544 bytes
Redo Buffers               12189696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.

3.主机规划

192.168.137.121  ora1221   --->主库(ORCL12C)
192.168.137.122  ora1222   --->Far Sync实例(ORCL12CFS)
192.168.137.123  ora1223   --->备库(ORCL12CDG)

4.tns配置

ORCL12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora1221)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )
ORCL12CDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora1223)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )
ORCL12CFS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora1222)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )

5.参数文件配置

--主库
db_unique_name='orcl12c'
service_names='orcl12c'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl12c'
log_archive_dest_2='service=orcl12cfs lgwr  sync  AFFIRM MAX_FAILURE=1 ALTERNATE=LOG_ARCHIVE_DEST_3 valid_for=(online_logfiles,primary_role) db_unique_name=orcl12cfs'
LOG_ARCHIVE_DEST_3='SERVICE=orcl12cdg  lgwr ASYNC ALTERNATE=LOG_ARCHIVE_DEST_2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl12cdg'
LOG_ARCHIVE_DEST_STATE_3=ALTERNATE
log_archive_config='dg_config=(orcl12c,orcl12cdg,orcl12cfs)'
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/orcl12c/','/u01/app/oracle/oradata/orcl12c/'
log_file_name_convert='/u01/app/oracle/oradata/orcl12c/','/u01/app/oracle/oradata/orcl12c/'
fal_server='orcl12cdg','orcl12cfs'
--Far Sync实例
db_unique_name='orcl12cfs'
service_names='orcl12c'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl12cfs'
log_archive_dest_2='service=orcl12cdg lgwr  async valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=orcl12cdg'
log_archive_config='dg_config=(orcl12c,orcl12cdg,orcl12cfs)'
standby_file_management=manual
fal_server='orcl12c'
--备库
db_unique_name='orcl12cdg'
service_names='orcl12c'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl12cdg'
log_archive_dest_2='service=orcl12c lgwr  async   valid_for=(online_logfiles,primary_role) db_unique_name=orcl12c'
log_archive_config='dg_config=(orcl12c,orcl12cdg,orcl12cfs)'
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/orcl12c/','/u01/app/oracle/oradata/orcl12c/'
log_file_name_convert='/u01/app/oracle/oradata/orcl12c/','/u01/app/oracle/oradata/orcl12c/'
fal_server='orcl12c','orcl12cfs'

6.密码文件
拷贝主库的密码文件到Far Sync实例和备库$ORACLE_HOME/dbs下(如果是win在%ORACLE_HOME%/database中)

7.创建Far Sync实例
创建和参数文件匹配的fra,adump目录

--主库
ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/tmp/controlfs01.ctl';
拷贝到Far Sync实例对应的控制文件位置
--Far Sync实例
startup pfile='/tmp/pfile' mount
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 10 '/u01/app/oracle/oradata/orcl12c/std_redo10.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 11 '/u01/app/oracle/oradata/orcl12c/std_redo11.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 12 '/u01/app/oracle/oradata/orcl12c/std_redo12.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 13 '/u01/app/oracle/oradata/orcl12c/std_redo13.log' size 50m reuse;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION;

8.创建备库
创建和参数文件匹配的fra,adump目录

--主库备份（传输至备库）
backup database format '/tmp/ora12c_%U';
--主库创建standby ctl（传输至备库和备库参数文件路径一致）
alter database create standby controlfile as '/tmp/controlst.ctl';
--启动备库至mount
startup pfile='/tmp/pfile' mount;
--备库注册备份
catalog start with '/tmp/xifenfei/';
--备库还原数据文件并恢复
restore database;
recover database;
--增加standby redo
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 10 '/u01/app/oracle/oradata/orcl12c/std_redo10.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 11 '/u01/app/oracle/oradata/orcl12c/std_redo11.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 12 '/u01/app/oracle/oradata/orcl12c/std_redo12.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 13 '/u01/app/oracle/oradata/orcl12c/std_redo13.log' size 50m reuse;
--备库开启mrp进程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION;
--主库增加standby redo
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 10 '/u01/app/oracle/oradata/orcl12c/std_redo10.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 11 '/u01/app/oracle/oradata/orcl12c/std_redo11.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 12 '/u01/app/oracle/oradata/orcl12c/std_redo12.log' size 50m reuse;
ALTER DATABASE ADD STANDBY LOGFILE thread 1  GROUP 13 '/u01/app/oracle/oradata/orcl12c/std_redo13.log' size 50m reuse;

9.配置结果

SQL> select * from V$DATAGUARD_CONFIG;
DB_UNIQUE_NAME                 PARENT_DBUN                    DEST_ROLE         CURRENT_SCN     CON_ID
------------------------------ ------------------------------ ----------------- ----------- ----------
orcl12c                        NONE                           PRIMARY DATABASE      1950551          0
orcl12cfs                      orcl12c                        FAR SYNC INSTANCE     1950390          0
orcl12cdg                      orcl12cfs                      PHYSICAL STANDBY      1950390          0
SQL> select group#, status, thread#, sequence#, first_change#, next_change# from v$standby_log;
    GROUP# STATUS        THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ---------- ------------- ------------
        10 ACTIVE              1         27       1863140
        11 UNASSIGNED          1          0
        12 UNASSIGNED          1          0
        13 UNASSIGNED          1          0
SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

10.调整保护级别
从MAXIMUM PERFORMANCE调整为MAXIMUM AVAILABILITY

--主库
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size                  4585912 bytes
Variable Size             671090248 bytes
Database Buffers         1828716544 bytes
Redo Buffers               12189696 bytes
Database mounted.
SQL> alter database set standby database to maximize availability;
Database altered.
SQL>  select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY UNPROTECTED
SQL> alter database open;
Database altered.
SQL>  select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
--Far Sync实例
SQL>  select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
--备库
SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY RESYNCHRONIZATION

dns解析导致opiodr aborting process unknown ospid (7266) as a result of ORA-609类似错误

数据库alert日志中出现大量类似opiodr aborting process unknown ospid (7266) as a result of ORA-609错误

Tue Oct 27 07:51:10 2015
opiodr aborting process unknown ospid (7266) as a result of ORA-609
Tue Oct 27 08:16:56 2015
opiodr aborting process unknown ospid (7660) as a result of ORA-609
Tue Oct 27 08:17:16 2015
opiodr aborting process unknown ospid (7666) as a result of ORA-609
Tue Oct 27 08:21:50 2015
opiodr aborting process unknown ospid (7725) as a result of ORA-609
Tue Oct 27 08:37:43 2015
opiodr aborting process unknown ospid (7940) as a result of ORA-609
Tue Oct 27 08:51:25 2015
opiodr aborting process unknown ospid (8126) as a result of ORA-609
Tue Oct 27 09:07:16 2015
opiodr aborting process unknown ospid (8359) as a result of ORA-609
Tue Oct 27 09:26:09 2015
opiodr aborting process unknown ospid (8600) as a result of ORA-609

测试tnsping

[oracle@Sql admin]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-OCT-2015 01:40:55
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.Sql.xifenfei.com)))
OK (20120 msec)
[oracle@Sql admin]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-OCT-2015 01:42:35
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.Sql.xifenfei.com)))
OK (41600 msec)

hosts文件配置

[oracle@Sql admin]$ vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.2.162 Sql.xifenfei.com
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

同时测试lsnrctl start/stop/status都很慢,但是ping localhost非常快,证明问题在监听之上.search 关键字lsnrctl slow/tnsping slow关键字发现有相关文档:
Bug 8307164 : TNSPING 11G USING DNS AND NOT HOSTS FILE
Listener Startup or Connections Hang in 11g (Doc ID 803838.1)
11g: Remote Connections Take Very Long to Establish (Doc ID 856820.1)
通过这些文档,可以知道在oracle 10g中解析主机名使用gethostbyname()函数,11g中使用了使用getaddrinfo()函数.这个函数对于/etc/nsswitch.conf的调用和ethostbyname()不一样,从而出现此类问题.根据官方建议:

Linux
hosts: files [NOTFOUND=continue] dns OR  hosts:files/dns
HPUX and Solaris
ipnodes: files [NOTFOUND=continue] dns

本次解决方法
因为对于没有使用到dns服务器的数据库环境而言,没有必要配置这玩意,给自己埋雷，估计是当时安装yum之时没有清理掉

vi /etc/nsswitch.conf
hosts:      files nds  -->hosts:      files

再次测试tnsping,lsnrctl等命令,速度ok

[oracle@Sql"]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-OCT-2015 01:58:30
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.Sql.xifenfei.com)))
OK (10 msec)

对于此类监听慢的问题，可以通过对client/server端trace进一步跟踪

TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client/server
TRACE_DIRECTORY_CLIENT = [any valid directory path]
TRACE_TIMESTAMP_CLIENT = ON
DIAG_ADR_ENABLED=off

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets—201507

11.2.0.1.0	11.2.0.1.0 Download Page
Older Versions	Raise a non-technical SR for Physical Shipment or Download URL. Refer Doc ID 1071023.1

Patchsets
^l12.1.0.2 (12.1.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)	21419221
11.2.0.4 (11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)	13390677
11.2.0.3 (11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER)	10404530
11.2.0.2 (11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)	10098816
11.1.0.7 (11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER)	6890831
10.2.0.5 (10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER)	8202632
^d10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)	6810189
^e10.2.0.3 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)	5337014
10.2.0.2 (10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER)	4547817
10.1.0.5 (10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER)	4505133
10.1.0.4 (10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER)	4163362
10.1.0.3 (10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER)	3761843
9.2.0.8 (9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER)	4547809
9.2.0.7 (9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER)	4163445
9.2.0.6 (9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER)	3948480
9.2.0.5 (ORACLE 9I DATABASE SERVER RELEASE 2 – PATCH SET 4 VERSION 9.2.0.5.0)	3501955
9.2.0.4 (9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER)	3095277
9.2.0.3 (9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)	2761332
9.2.0.2 (9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER)	2632931
9.0.1.5 (9.0.1.5 PATCHSET)	3301544
9.0.1.4 (9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER)	2517300
9.0.1.3 (9.0.1.3. PATCH SET FOR ORACLE DATA SERVER)	2271678
8.1.7.4 (8.1.7.4 PATCH SET FOR ORACLE DATA SERVER)	2376472
8.1.7.3 (8.1.7.3 PATCH SET FOR ORACLE DATA SERVER)	2189751
8.1.7.2 (8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER)	1909158

PSU, SPU(CPU), Bundle Patches


12.1.0.2
Description	PSU	GI PSU	Bundle Patch (Windows 32bit & 64bit)
JUL2015	20831110 (12.1.0.2.4)	20996835 (12.1.0.2.4)	21126814 (12.1.0.2.7)
APR2015	20299023 (12.1.0.2.3)	20485724 (12.1.0.2.3)	20684004 (12.1.0.2.4)
JAN2015	19769480 (12.1.0.2.2)	19954978 (12.1.0.2.2)	19720843 (12.1.0.2.1)
OCT2014	19303936 (12.1.0.2.1)	19392646 (12.1.0.2.1)	N/A

12.1.0.1
Description	PSU	GI PSU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
JUL2015	20831107 (12.1.0.1.8)	^j20996901 / ^k20996911 (12.1.0.1.8)	21076681 (12.1.0.1.20)
APR2015	20299016 (12.1.0.1.7)	^j20485762 / ^k19971331 (12.1.0.1.7)	20558101 (12.1.0.1.18)
JAN2015	19769486 (12.1.0.1.6)	^j19971324 / ^k19971331 (12.1.0.1.6)	20160748 (12.1.0.1.16)
OCT2014	19121550 (12.1.0.1.5)	^j19392372 / ^k19392451 (12.1.0.1.5)	19542943 (12.1.0.1.14)
JUL2014	18522516 (12.1.0.1.4)	^j18705901 / ^k18705972 (12.1.0.1.4)	19062327 (12.1.0.1.11)
APR2014	18031528 (12.1.0.1.3)	^j18139660 / ^k18413105 (12.1.0.1.3)	18448604 (12.1.0.1.7)
JAN2014	17552800 (12.1.0.1.2)	17735306 (12.1.0.1.2)	17977915 (12.1.0.1.3)
OCT2013	17027533 (12.1.0.1.1)	17272829 (12.1.0.1.1)	17363796 (12.1.0.1.1)	17363795 (12.1.0.1.1)

11.2.0.4
Description	PSU	SPU(CPU)	GI PSU	Bundle Patch (Windows 32bit & 64bit)
JUL2015	20760982 (11.2.0.4.7)	20803583	20996923 (11.2.0.4.7)	21469106 (11.2.0.4.18)
APR2015	20299013 (11.2.0.4.6)	20299015	20485808 (11.2.0.4.6)	20544696 (11.2.0.4.15)
JAN2015	19769489 (11.2.0.4.5)	19854503	19955028 (11.2.0.4.5)	20127071 (11.2.0.4.12)
OCT2014	19121551 (11.2.0.4.4)	19271443	19380115 (11.2.0.4.4)	19651773 (11.2.0.4.10)
JUL2014	18522509 (11.2.0.4.3)	18681862	18706472 (11.2.0.4.3)	18842982 (11.2.0.4.7)
APR2014	18031668 (11.2.0.4.2)	18139690	18139609 (11.2.0.4.2)	18296644 (11.2.0.4.4)
JAN2014	17478514 (11.2.0.4.1)	17551709	N/A	17987366 (11.2.0.4.1)

11.2.0.3
Description	PSU	SPU(CPU)	GI PSU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
JUL2015	20760997 (11.2.0.3.15)	20803576	20996944 (11.2.0.3.15)	21104036	21104035
APR2015	20299017 (11.2.0.3.14)	20299010	20485830 (11.2.0.3.14)	20420395	20420394
JAN2015	19769496 (11.2.0.3.13)	19854461	19971343 (11.2.0.3.13)	20233168	20233167
OCT2014	19121548 (11.2.0.3.12)	19271438	19440385 (11.2.0.3.12)	19618575	19618574
JUL2014	18522512 (11.2.0.3.11)	18681866	18706488 (11.2.0.3.11)	18940194	18940193
APR2014	18031683 (11.2.0.3.10)	18139695	18139678 (11.2.0.3.10)	18372244	18372243
JAN2014	17540582 (11.2.0.3.9)	17478415	17735354 (11.2.0.3.9)	18075406	17906981
OCT2013	16902043 (11.2.0.3.8)	17082364	17272731 (11.2.0.3.8)	17363850	17363844
JUL2013	16619892 (11.2.0.3.7)	16742095	16742216 (11.2.0.3.7)	16803775	16803774
APR2013	16056266 (11.2.0.3.6)	16294378	16083653 (11.2.0.3.6)	16345834	16345833
JAN2013	14727310 (11.2.0.3.5)	14841409	14727347 (11.2.0.3.5)	16042648	16042647
OCT2012	14275605 (11.2.0.3.4)	14390252	14275572 (11.2.0.3.4)	14613223	14613222
JUL2012	13923374 (11.2.0.3.3)	14038787	13919095 (11.2.0.3.3)	14223718	14223717
APR2012	13696216 (11.2.0.3.2)	13632717	13696251 (11.2.0.3.2)	13885389	13885388
JAN2012	13343438 (11.2.0.3.1)	13466801	13348650 (11.2.0.3.1)	13413168	13413167

11.2.0.2
Description	PSU	SPU(CPU)	GI PSU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^aOCT2013	17082367 (11.2.0.2.12)	17082375	17272753 (11.2.0.2.12)	17363838	17363837
JUL2013	16619893 (11.2.0.2.11)	16742100	16742320 (11.2.0.2.11)	16345852	16345851
APR2013	16056267 (11.2.0.2.10)	16294412	16166868 (11.2.0.2.10)	16345846	16345845
JAN2013	14727315 (11.2.0.2.9)	14841437	14841385 (11.2.0.2.9)	16100399	16100398
OCT2012	14275621 (11.2.0.2.8)	14390377	14390437 (11.2.0.2.8)	14672268	14672267
JUL2012	13923804 (11.2.0.2.7)	14038791	14192201 (11.2.0.2.7)	14134043	14134042
APR2012	13696224 (11.2.0.2.6)	13632725	13696242 (11.2.0.2.6)	13697074	13697073
JAN2012	13343424 (11.2.0.2.5)	13343244	13653086 (11.2.0.2.5)	13413155	13413154
OCT2011	12827726 (11.2.0.2.4)	12828071	12827731 (11.2.0.2.4)	13038788	13038787
JUL2011	12419331 (11.2.0.2.3)	12419321	12419353 (11.2.0.2.3)	12714463	12714462
APR2011	11724916 (11.2.0.2.2)	11724984	12311357 (11.2.0.2.2)	11896292	11896290
JAN2011	10248523 (11.2.0.2.1)	N/A	N/A	10432053	10432052


11.2.0.1
Description	PSU	CPU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^aJUL2011	12419378 (11.2.0.1.6)	12419278	12429529	12429528
APR2011	11724930 (11.2.0.1.5)	11724991	11731176	11883240
JAN2011	10248516 (11.2.0.1.4)	10249532	10432045	10432044
OCT2010	9952216 (11.2.0.1.3)	9952260	10100101	10100100
JUL2010	9654983 (11.2.0.1.2)	9655013	9736865	9736864
APR2010	9352237 (11.2.0.1.1)	9369797	N/A	N/A


11.1.0.7
Description	PSU	SPU(CPU)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^bJUL2015	20761024 (11.1.0.7.24)	20803573	21104030	21104029
^bAPR2015	20299012 (11.1.0.7.23)	20299020	20420391	20420390
^bJAN2015	19769499 (11.1.0.7.22)	19854433	20126915	20126914
^bOCT2014	19152553 (11.1.0.7.21)	19274522	19609034	19609032
^bJUL2014	18522513 (11.1.0.7.20)	18681875	18944208	18944207
^bAPR2014	18031726 (11.1.0.7.19)	18139703	18372258	18372257
^bJAN2014	17465583 (11.1.0.7.18)	17551415	17906936	17906935
^bOCT2013	17082366 (11.1.0.7.17)	17082374	17363760	17363759
^bJUL2013	16619896 (11.1.0.7.16)	16742110	16803788	16803787
^bAPR2013	16056268 (11.1.0.7.15)	16308394	16345862	16345861
^bJAN2013	14739378 (11.1.0.7.14)	14841452	15848067	15848066
^bOCT2012	14275623 (11.1.0.7.13)	14390384	14672313	14672312
JUL2012	13923474 (11.1.0.7.12)	14038803	14109868	14109867
APR2012	13621679 (11.1.0.7.11)	13632731	13715810	13715809
JAN2012	13343461 (11.1.0.7.10)	13343453	13460956	13460955
OCT2011	12827740 (11.1.0.7.9)	12828097	12914916	12914915
JUL2011	12419384 (11.1.0.7.8)	12419265	12695278	12695277
APR2011	11724936 (11.1.0.7.7)	11724999	11741170	11741169
JAN2011	10248531 (11.1.0.7.6)	10249534	10350788	10350787
OCT2010	9952228 (11.1.0.7.5)	9952269	9773825	9773817
JUL2010	9654987 (11.1.0.7.4)	9655014	9869912	9869911
APR2010	9352179 (11.1.0.7.3)	9369783	9392335	9392331
JAN2010	9209238 (11.1.0.7.2)	9114072	9166861	9166858
OCT2009	8833297 (11.1.0.7.1)	8836375	8928977	8928976
JUL2009	N/A	8534338	8553515	8553512
APR2009	N/A	8290478	8343070	8343061


11.1.0.6
Description	CPU	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^aJUL2009	8534378	8563155	8563154
APR2009	8290402	8333657	8333655
JAN2009	7592335	7631981	7631980
OCT2008	7375639	7378393	7378392
JUL2008	7150417	7210197	7210195
APR2008	6864063	6867180	6867178


10.2.0.5
Description	PSU	SPU(CPU)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
^abJUL2013	16619894 (10.2.0.5.12)	16742123	16803782	16803780	16803781
^bAPR2013	16056270 (10.2.0.5.11)	16270946	16345857	16345855	16345856
^bJAN2013	14727319 (10.2.0.5.10)	14841459	15848062	15848060	15848061
^bOCT2012	14275629 (10.2.0.5.9)	14390396	14553358	14553356	14553357
^bJUL2012	13923855 (10.2.0.5.8)	14038805	14134053	14134051	14134052
^bAPR2012	13632743 (10.2.0.5.7)	13632738	13654815	13654814	13870404
JAN2012	13343471 (10.2.0.5.6)	13343467	^b13460968	^b13460967	N/A
^bOCT2011	12827745 (10.2.0.5.5)	12828105	^c12914913	12914911	N/A
JUL2011	12419392 (10.2.0.5.4)	12419258	12429524	12429523	N/A
APR2011	11724962 (10.2.0.5.3)	11725006	12328269	12328268	N/A
JAN2011	10248542 (10.2.0.5.2)	10249537	10352673	10352672	N/A
OCT2010	9952230 (10.2.0.5.1)	9952270	10099855	10058290	N/A


10.2.0.4
Description	PSU	SPU(CPU)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^Itanium)
^bgJUL2013	16619897 (10.2.0.4.17)	16742253	N/A	N/A	N/A
^bgAPR2013	16056269 (10.2.0.4.16)	16270931	N/A	N/A	N/A
^bgJAN2013	14736542 (10.2.0.4.15)	14841471	N/A	N/A	N/A
^bgOCT2012	14275630 (10.2.0.4.14)	14390410	N/A	N/A	N/A
^bgJUL2012	13923851 (10.2.0.4.13)	14038814	N/A	N/A	N/A
^abAPR2012	12879933 (10.2.0.4.12)	12879926	13928775	13928776	N/A
JAN2012	12879929 (10.2.0.4.11)	12879912	^b13654060	N/A	N/A
^bOCT2011	12827778 (10.2.0.4.10)	12828112	12914908	12914910	12914909
JUL2011	12419397 (10.2.0.4.9)	12419249	12429519	12429521	12429520
APR2011	11724977 (10.2.0.4.8)	11725015	12328501	12328503	12328502
JAN2011	10248636 (10.2.0.4.7)	10249540	10349197	10349200	10349198
OCT2010	9952234 (10.2.0.4.6)	9952272	10084980	10084982	10084981
JUL2010	9654991 (10.2.0.4.5)	9655017	9777076	9777078	9777077
APR2010	9352164 (10.2.0.4.4)	9352191	9393548	9393550	9393549
JAN2010	9119284 (10.2.0.4.3)	9119226	9169457	9169460	9169458
OCT2009	8833280 (10.2.0.4.2)	8836308	8880857	8880861	8880858
JUL2009	8576156 (10.2.0.4.1)	8534387	8559466	8559467	8541782
APR2009	N/A	8290506	8307237	8307238	8333678
JAN2009	N/A	7592346	7584866	7584867	N/A
OCT2008	N/A	7375644	7386320	7386321	N/A
JUL2008	N/A	7150470	7218676	7218677	N/A


10.2.0.3
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)	Bundle Patch (Windows^64bit)
^aJAN2009	7592354	7631956	7631958	7631957
OCT2008	7369190	7353782	7353784	7353785
JUL2008	7150622	7252496	7252497	7252498
APR2008	6864068	6867054	6867055	6867056
JAN2008	6646853	6637237	6637238	6637239
OCT2007	6394981	6430171	6430173	6430174
JUL2007	6079591	6116131	6038242	6116139
APR2007	5901891	5948242	5916262	5948243
JAN2007	5881721	5846376	5846377	5846378


10.2.0.2
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^Itanium)
ⁱJAN2009	7592355	N/A	N/A	N/A
^hOCT2008	7375660	N/A	N/A	N/A
^hJUL2008	7154083	N/A	N/A	N/A
^hAPR2008	6864071	N/A	N/A	N/A
^aJAN2008	6646850	N/A	N/A	N/A
^fOCT2007	6394997	6397028	6397030	6397029
JUL2007	6079588	6013105	6013121	6013118
APR2007	5901881	5912173	5912179	5912176
JAN2007	5689957	5716143	5699839	5699824
OCT2006	5490848	5502226	5500921	5500894
JUL2006	5225799	5251025	5251028	5251026
APR2006	5079037	5140461	5140567	5140508


10.2.0.1
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^Itanium)
APR2007	5901880	N/A	N/A	N/A
JAN2007	5689937	5695784	5695786	5695785
OCT2006	5490846	5500927	5500954	5500951
JUL2006	5225798	5239698	5239701	5239699
APR2006	5049080	5059238	5059261	5059251
JAN2006	4751931	4751539	4770480	4751549


10.1.0.5
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JAN2012	13343482	13413002	13413003
OCT2011	12828135	12914905	12914906
JUL2011	12419228	12429517	12429518
APR2011	11725035	11731119	11731120
JAN2011	N/A	N/A	N/A
OCT2010	9952279	10089559	10089560
JUL2010	9655023	9683651	9683652
APR2010	9352208	9390288	9390289
JAN2010	9119261	9187104	9187105
OCT2009	8836540	8785211	8785212
JUL2009	8534394	8656224	8656226
APR2009	8290534	8300356	8300360
JAN2009	7592360	7486619	7586049
OCT2008	7375686	7367493	7367494
JUL2008	7154097	7047034	7047037
APR2008	6864078	6867107	6867108
JAN2008	6647005	6637274	6637275
OCT2007	6395024	6408393	6408394
JUL2007	6079585	6115804	6115818
APR2007	5901877	5907304	5907305
JAN2007	5689908	5716295	5634747
OCT2006	5490845	5500883	5500885
JUL2006	5225797	5251148	5251140
APR2006	5049074	5057606	5057609
JAN2006	4751932	4882231	4882236


10.1.0.4
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
APR2007	5901876	5909871	5909879
JAN2007	5689894	5695771	5695772
OCT2006	5490844	5500878	5500880
JUL2006	5225796	5239736	5239737
APR2006	5049067	5059200	5059227
JAN2006	4751928	4751259	4745040
OCT2005	4567866	4579182	4579188
JUL2005	4392423	4440706	4404600
APR2005	4210374	4287619	4287611


10.1.0.3
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JAN2007	5923277	N/A	N/A
OCT2006	5566825	N/A	N/A
JUL2006	5435164	N/A	N/A
APR2006	5158022	N/A	N/A
JAN2006	4751926	4741077	4741084
OCT2005	4567863	4567518	4567523
JUL2005	4392409	4389012	4389014
APR2005	4193286	4269715	4158888
JAN2005	4003062	4074232	3990812


10.1.0.2
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
APR2005	4193293	4181849	4213305
JUL2005	4400766	4388944	4388948
JAN2005	4003051	4104364	4083038


9.2.0.8
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JUL2010	9655027	9683644	9683645
APR2010	9352224	9390286	N/A
JAN2010	9119275	9187106	N/A
OCT2009	8836758	8785185	8785186
JUL2009	8534403	8427417	8427418
APR2009	8290549	8300340	8300346
JAN2009	7592365	7703210	7703212
OCT2008	7375695	7394394	7394402
JUL2008	7154111	7047026	7047029
APR2008	6864082	6867138	6867139
JAN2008	6646842	6637265	6637266
OCT2007	6395038	6417013	6417014
JUL2007	6079582	6130293	6130295
APR2007	5901875	5916268	5916275
JAN2007	N/A	N/A	N/A
OCT2006	5490859	5652380	5639519


9.2.0.7
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JUL2007	6079579	6146759	6146748
APR2007	5901872	5907274	5907275
JAN2007	5689875	5654905	5654909
OCT2006	5490841	5500873	5500874
JUL2006	5225794	5250980	5250981
APR2006	5049060	5064365	5064364
JAN2006	4751923	4751528	4741074
OCT2005	4567854	4579590	4579599
JUL2005	4547566	N/A	N/A


9.2.0.6
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
OCT2006	5490840	5500865	5500871
JUL2006	5225793	5239794	5239793
APR2006	5049051	5059614	5059615
JAN2006	4751921	4751261	4751262
OCT2005	4567846	4579093	4579097
JUL2005	4392392	4445852	4401917
APR2005	4193295	4269928	4213298


9.2.0.5
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
OCT2006	5689708	N/A	N/A
JUL2006	5435138	N/A	N/A
APR2006	5219762	N/A	N/A
OCT2005	4560421	N/A	N/A
JUL2005	4392256	4387563	4391819
APR2005	4193299	4195791	4214192
JAN2005	4003006	4104374	3990809


9.2.0.4
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JAN2005	4002994	4104369	4083202


8.1.7.4
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)
JAN2007	5689799	5686514
OCT2006	5490835	5496067
JUL2006	5225788	5236412
APR2006	5045247	5057601
JAN2006	4751906	4751570
OCT2005	4560405	4554818
JUL2005	4392446	4437058
APR2005	4193312	4180163
JAN2005	4002909	3921893

OJVM PSU Patches


12.1.0.2
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + GI PSU
JUL2015	21068507 (12.1.0.2.4)	21153530 (12.1.0.2.3)	21150768	21150782
APR2015	20415564 (12.1.0.2.3)	20391199 (12.1.0.2.2)	20834354	20834538
JAN2015	19877336 (12.1.0.2.2)	20225938 (12.1.0.2.1)	20132434	20132450
OCT2014 (12.1.0.2.1)	19282028		19791366	19791375


12.1.0.1
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + GI PSU	Generic JDBC
JUL2015 (12.1.0.1.4)	21068523	21153513	21150806	21150817	Included in OJVM PSU
APR2015 (12.1.0.1.3)	20406245	20225909	20834568	20834579
JAN2015 (12.1.0.1.2)	19877342	20225916	20132482	20132489
OCT2014 (12.1.0.1.1)	19282024	19801531	19791363	19791360	19852357


11.2.0.4
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + DB SPU	Combo OJVM + GI PSU	Generic JDBC
JUL2015 (11.2.0.4.4)	21068539	21153498	21150851	21150829	21150864	Included in OJVM PSU
APR2015 (11.2.0.4.3)	20406239	20225988	20834611	20834597	20834621
JAN2015 (11.2.0.4.2)	19877440	20225982	20132580	20132517	20132615
OCT2014 (11.2.0.4.1)	19282021	19799291	19791364	19791358	19791420	19852360


11.2.0.3
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + DB SPU	Combo OJVM + GI PSU	Generic JDBC
JUL2015 (11.2.0.3.4)	21068553	21153470	21150891	21150885	21150904	Included in OJVM PSU
APR2015 (11.2.0.3.3)	20406220	20391185	20834670	20834653	20834686
JAN2015 (11.2.0.3.2)	19877443	20227195	20132646	20132635	20132651
OCT2014 (11.2.0.3.1)	19282015	19806120	19791427	19791426	19791428	19852361


11.1.0.7
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + DB SPU	Combo OJVM + GI PSU	Generic JDBC
JUL2015 (11.1.0.7.4)	21068565	21153423	21150939	21150929	N/A	Included in OJVM PSU
APR2015 (11.1.0.7.3)	20406213	20391156	20834724	20834712	N/A
JAN2015 (11.1.0.7.2)	19877446	20227146	20132677	20132669	N/A
OCT2014 (11.1.0.7.1)	19282002	19806118	19791436	19791434	N/A	19852363

EXP-00104: datatype (BINARY_DOUBLE) 错误

进行了一次使用exp数据迁移,客户和我说丢失了表,我觉得比较奇怪,分析日志发现EXP-00104: datatype (BINARY_DOUBLE)错误,对其单表进行导出,发现问题重现

[oracle@app73 ~]$ exp system/oracle123 file=/Data/1.dmp tables=CHF.T_XIFENFEI
Export: Release 11.1.0.6.0 - Production on Wed Oct 14 23:25:31 2015
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to CHF
. . exporting table                  T_XIFENFEI
EXP-00104: datatype (BINARY_DOUBLE) of column NUMCOMMISSIONRATE in table
 CHF.T_XIFENFEI is not supported, table will not be exported

数据库版本

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

表结构

SQL> DESC CHF.T_XIFENFEI
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
…………
 NUMPRO                                             NUMBER
 NUMCOMMISSIONRATE                                  BINARY_DOUBLE
 NUMSEQ                                             BINARY_DOUBLE
 NUMMARK                                            NUMBER
…………

解决办法,使用expdp/impdp代替exp/imp

[oracle@app73 ~]$ expdp system/manager dumpfile=exp.dmp tables=CHF.T_XIFENFEI  directory=exp_dir
Export: Release 11.1.0.6.0 - 64bit Production on Wednesday, 14 October, 2015 23:30:38
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":system/******** dumpfile=exp.dmp tables=CHF.T_XIFENFEI directory=exp_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 959 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "CHF"."T_XIFENFEI"                    814.7 MB 1601202 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /tmp/exp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 23:31:31
[oracle@app101-2 Data]$ impdp system/oracle dumpfile=T_XIFENFEI.dmp tables=CHF.T_XIFENFEI directory=exp_dir
Import: Release 11.2.0.4.0 - Production on Wed Oct 14 23:44:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":system/******** dumpfile=T_XIFENFEI.dmp tables=CHF.T_XIFENFEI directory=exp_dir
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CHF"."T_XIFENFEI"                    814.7 MB 1601202 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Oct 14 23:45:37 2015 elapsed 0 00:01:02

出现此类错误,也和自己的粗心有关系,没有仔细检查导出日志,另外该问题从10.1.0.2就有了,直到现在才发现,证明知识点欠缺.同样对于binary_float也有一样问题.
具体可以参考：Export Failed Due To EXP-104 Error, Datatype (Binary_double) Is Not Supported [ID 421591.1]

ORA-65101 container database set up incorrectly

老朋友找我帮忙，说他们从笔记本中拷贝数据库到客户服务器上，无法启动，让我帮忙看下
启动报ORA-00211 ORA-00202错误

C:\Users\Administrator>sqlplus / as  sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期二 10月 13 21:35:45 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL> select open_MODE FROM V$DATABASE;
select open_MODE FROM V$DATABASE
                      *
第 1 行出现错误:
ORA-01507: ??????
SQL> alter database mount;
alter database mount
*
第 1 行出现错误:
ORA-00211: control file does not match previous control files
ORA-00202: control file:
''D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL12C\CONTROL02.CTL''

控制文件版本不匹配
这个错误很明显数据库中多个控制文件版本不一致，使用其中一个试试看

SQL> show parameter control;
NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time        integer
7
control_files                        string
D:\APP\ADMINISTRATOR\ORADATA\O
RCL12C\CONTROL01.CTL, D:\APP\A
DMINISTRATOR\FAST_RECOVERY_ARE
A\ORCL12C\CONTROL02.CTL
control_management_pack_access       string
DIAGNOSTIC+TUNING
SQL> alter system set control_files='d:\app\administrator\oradata\orcl12c\contro
l01.ctl' scope=spfile;
系统已更改。
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 1703624704 bytes
Fixed Size                  2403448 bytes
Variable Size            1090519944 bytes
Database Buffers          603979776 bytes
Redo Buffers                6721536 bytes
ORA-65101:container database set up incorrectly

数据库此处报ORA-65101错误，官方解释
解释很清楚，使用一个no cdb的控制文件启动一个cdb的库，因此出现ORA-65101错误。官方解决方案：重建控制文件,但是本库通过分析是数据文件和控制文件一起拷贝过来，应该不是改问题

Error code: ORA-65101
Description: container database set up incorrectly
Cause:
An attempt was made to use a non container database
control file to startup a container database (CDB).
Action:
Create a new control file for the CDB.

检查enable_pluggable_database参数
果然该参数设置为cdb模式,和控制文件的no cdb不匹配,因此出现该问题

SQL> show parameter enable_pluggable_database
NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
enable_pluggable_database            boolean
TRUE
SQL>
SQL> alter system set enable_pluggable_database=false scope=spfile;
系统已更改。
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 1703624704 bytes
Fixed Size                  2403448 bytes
Variable Size            1090519944 bytes
Database Buffers          603979776 bytes
Redo Buffers                6721536 bytes
数据库装载完毕。
数据库已经打开。

ORA-15042: ASM disk "N" is missing from group number "M" 故障恢复

接到一个朋友恢复请求,19个lun的asm 磁盘组，由于其中一个lun有问题，他们进行了增加一个新lun，删除老lun的方法操作,但是操作一半hang住了(因为坏的lun是底层损坏，无法完成rebalance),然后存储工程师继续修复异常lun，非常幸运异常lun修复好了，但是高兴过了头，直接从存储上删除了新加入的lun(已经rebalance一部分数据进去了)，这个时候asm dg彻底趴下了，不能mount成功，请求恢复支持。由于某种原因，无法从lun层面恢复，只能让我们提供数据库层面恢复

Mon Sep 21 19:52:35 2015
SQL> alter diskgroup  dg_XFF add disk '/dev/rhdisk116' size 716800M drop disk dg_XFF_0012
NOTE: Assigning number (1,20) to disk (/dev/rhdisk116)
NOTE: requesting all-instance membership refresh for group=1
NOTE: initializing header on grp 1 disk DG_XFF_0020
NOTE: requesting all-instance disk validation for group=1
Mon Sep 21 19:52:44 2015
NOTE: skipping rediscovery for group 1/0xb94738f1 (DG_XFF) on local instance.
NOTE: requesting all-instance disk validation for group=1
NOTE: skipping rediscovery for group 1/0xb94738f1 (DG_XFF) on local instance.
NOTE: initiating PST update: grp = 1
Mon Sep 21 19:52:44 2015
GMON updating group 1 at 25 for pid 27, osid 12124486
NOTE: PST update grp = 1 completed successfully
NOTE: membership refresh pending for group 1/0xb94738f1 (DG_XFF)
GMON querying group 1 at 26 for pid 18, osid 10092734
NOTE: cache opening disk 20 of grp 1: DG_XFF_0020 path:/dev/rhdisk116
GMON querying group 1 at 27 for pid 18, osid 10092734
SUCCESS: refreshed membership for 1/0xb94738f1 (DG_XFF)
Mon Sep 21 19:52:47 2015
SUCCESS: alter diskgroup  dg_XFF add disk '/dev/rhdisk116' size 716800M drop disk dg_XFF_0012
NOTE: starting rebalance of group 1/0xb94738f1 (DG_XFF) at power 1
Starting background process ARB0
Mon Sep 21 19:52:47 2015
ARB0 started with pid=28, OS id=10944804
NOTE: assigning ARB0 to group 1/0xb94738f1 (DG_XFF) with 1 parallel I/O
NOTE: Attempting voting file refresh on diskgroup DG_XFF
Mon Sep 21 20:35:06 2015

SQL> ALTER DISKGROUP DG_XFF MOUNT  /* asm agent *//* {1:51107:7083} */
NOTE: cache registered group DG_XFF number=1 incarn=0xdd6f975a
NOTE: cache began mount (first) of group DG_XFF number=1 incarn=0xdd6f975a
NOTE: Assigning number (1,0) to disk (/dev/rhdisk10)
NOTE: Assigning number (1,1) to disk (/dev/rhdisk11)
NOTE: Assigning number (1,2) to disk (/dev/rhdisk16)
NOTE: Assigning number (1,3) to disk (/dev/rhdisk17)
NOTE: Assigning number (1,4) to disk (/dev/rhdisk22)
NOTE: Assigning number (1,5) to disk (/dev/rhdisk23)
NOTE: Assigning number (1,6) to disk (/dev/rhdisk28)
NOTE: Assigning number (1,7) to disk (/dev/rhdisk29)
NOTE: Assigning number (1,8) to disk (/dev/rhdisk33)
NOTE: Assigning number (1,9) to disk (/dev/rhdisk34)
NOTE: Assigning number (1,10) to disk (/dev/rhdisk4)
NOTE: Assigning number (1,11) to disk (/dev/rhdisk40)
NOTE: Assigning number (1,12) to disk (/dev/rhdisk41)
NOTE: Assigning number (1,13) to disk (/dev/rhdisk45)
NOTE: Assigning number (1,14) to disk (/dev/rhdisk46)
NOTE: Assigning number (1,15) to disk (/dev/rhdisk5)
NOTE: Assigning number (1,16) to disk (/dev/rhdisk52)
NOTE: Assigning number (1,17) to disk (/dev/rhdisk53)
NOTE: Assigning number (1,18) to disk (/dev/rhdisk57)
NOTE: Assigning number (1,19) to disk (/dev/rhdisk58)
Wed Sep 30 11:08:07 2015
NOTE: start heartbeating (grp 1)
GMON querying group 1 at 33 for pid 35, osid 4194488
NOTE: Assigning number (1,20) to disk ()
GMON querying group 1 at 34 for pid 35, osid 4194488
NOTE: cache dismounting (clean) group 1/0xDD6F975A (DG_XFF)
NOTE: dbwr not being msg'd to dismount
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 1/0xDD6F975A (DG_XFF)
NOTE: cache ending mount (fail) of group DG_XFF number=1 incarn=0xdd6f975a
NOTE: cache deleting context for group DG_XFF 1/0xdd6f975a
GMON dismounting group 1 at 35 for pid 35, osid 4194488
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
NOTE: Disk  in mode 0x8 marked for de-assignment
ERROR: diskgroup DG_XFF was not mounted
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "20" is missing from group number "1"
ERROR: ALTER DISKGROUP DG_XFF MOUNT  /* asm agent *//* {1:51107:7083} */

这里比较明显，由于存储工程师直接删除了lun，这里导致磁盘组DG_XFF丢失asm disk 20，使得磁盘组无法直接mount，由于该磁盘组已经进行了较长时间的rebalance，丢失的盘中已经有大量数据（包括元数据），因此就算修改pst让磁盘组mount起来(不一定成功)，也会丢失大量数据，也不一定可以直接拿出来里面的数据，如果只是加入盘，但是由于某种原因没有做rebalance，那我们直接可以通过修改pst,使得磁盘组mount起来。因此对于这样的情况，我们能够做的，只能从底层扫描磁盘，生成数据文件(因为有部分文件的元数据在丢失lun之上，如果直接使用现存元数据信息，直接拷贝，或者unload数据都会丢失大量数据），然后再进一步unload数据，完成恢复。需要恢复磁盘信息

grp# dsk# bsize ausize disksize diskname        groupname       path
---- ---- ----- ------ -------- --------------- --------------- -------------
   1    0  4096  4096K   179200 DG_XFF_0000     DG_XFF          /dev/rhdisk10
   1    1  4096  4096K   179200 DG_XFF_0001     DG_XFF          /dev/rhdisk11
   1    2  4096  4096K   179200 DG_XFF_0002     DG_XFF          /dev/rhdisk16
   1    3  4096  4096K   179200 DG_XFF_0003     DG_XFF          /dev/rhdisk17
   1    4  4096  4096K   179200 DG_XFF_0004     DG_XFF          /dev/rhdisk22
   1    5  4096  4096K   179200 DG_XFF_0005     DG_XFF          /dev/rhdisk23
   1    6  4096  4096K   179200 DG_XFF_0006     DG_XFF          /dev/rhdisk28
   1    7  4096  4096K   179200 DG_XFF_0007     DG_XFF          /dev/rhdisk29
   1    8  4096  4096K   179200 DG_XFF_0008     DG_XFF          /dev/rhdisk33
   1    9  4096  4096K   179200 DG_XFF_0009     DG_XFF          /dev/rhdisk34
   1   10  4096  4096K   179200 DG_XFF_0010     DG_XFF          /dev/rhdisk4
   1   11  4096  4096K   179200 DG_XFF_0011     DG_XFF          /dev/rhdisk40
   1   12  4096  4096K   179200 DG_XFF_0012     DG_XFF          /dev/rhdisk41
   1   13  4096  4096K   179200 DG_XFF_0013     DG_XFF          /dev/rhdisk45
   1   14  4096  4096K   179200 DG_XFF_0014     DG_XFF          /dev/rhdisk46
   1   15  4096  4096K   179200 DG_XFF_0015     DG_XFF          /dev/rhdisk5
   1   16  4096  4096K   179200 DG_XFF_0016     DG_XFF          /dev/rhdisk52
   1   17  4096  4096K   179200 DG_XFF_0017     DG_XFF          /dev/rhdisk53
   1   18  4096  4096K   179200 DG_XFF_0018     DG_XFF          /dev/rhdisk57
   1   19  4096  4096K   179200 DG_XFF_0019     DG_XFF          /dev/rhdisk58

这次运气比较好，丢失的磁盘组只是一个业务磁盘组，而且里面只有19个表空间，10个分区表，因此在数据字典完成的情况下，恢复10个分区表(一共6443个分区)的数据，整体恢复效果如下:
RECOVER

从整体数据量看恢复比例为：6003.26953/6027.26935*100%=99.6018127%,对于丢失了一个已经rebalance的大部分的lun，依旧能够恢复如此的数据，整体看非常理想.
如果您遇到此类情况,无法解决请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

12C sysaux 异常恢复—ORA-01190错误恢复

有朋友请求支援，他们数据库由于file 3 大量坏块，然后直接使用rman 备份还原了file 3，但是在recover过程中发现归档丢失，而且整个库在丢失归档的scn之后，还做过resetlogs操作，导致现在整个库无法正常启动，报ORA-01190错误，希望帮忙把file 3 给online起来，整个库正常open【当然在丢失sysaux的情况下，数据库可以open起来，但是这种情况下，迁移数据比较麻烦】

SQL> startup;
ORACLE 例程已经启动。
Total System Global Area 3.1868E+10 bytes
Fixed Size                  3601144 bytes
Variable Size            2.8655E+10 bytes
Database Buffers         3154116608 bytes
Redo Buffers               54804480 bytes
数据库装载完毕。
ORA-01190: 控制文件或数据文件 3 来自最后一个 RESETLOGS 之前
ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF'

Oracle Database Recovery Check Result结果显示[脚本]
wrong+resetlogs

尝试不完全恢复并使用隐含参数打开库

Fri Oct 02 19:10:12 2015
ALTER DATABASE RECOVER  database until cancel
Fri Oct 02 19:10:12 2015
Media Recovery Start
 Started logmerger process
Fri Oct 02 19:10:12 2015
Media Recovery failed with error 16433
Fri Oct 02 19:10:14 2015
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database until cancel  ...
Fri Oct 02 19:10:37 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5176.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:10:37 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5176.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
ALTER DATABASE RECOVER  database until cancel
Fri Oct 02 19:11:18 2015
Media Recovery Start
 Started logmerger process
Fri Oct 02 19:11:18 2015
Media Recovery failed with error 16433
Fri Oct 02 19:11:19 2015
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database until cancel  ...
alter database open resetlogs
ORA-1139 signalled during: alter database open resetlogs...
alter database open
Fri Oct 02 19:11:49 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_4252.trc:
ORA-01190: 控制文件或数据文件 3 来自最后一个 RESETLOGS 之前
ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF'
ORA-1190 signalled during: alter database open...
Fri Oct 02 19:15:38 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5292.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:15:38 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5292.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:20:39 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_2276.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:20:39 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_2276.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:25:40 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_4804.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:25:40 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_4804.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:30:41 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_876.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:30:41 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_876.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:32:40 2015
Shutting down instance (abort)

数据库遭遇ORA-16433,此类方法无法打开数据库，根据经验值出现此类问题，可能需要重建控制文件，但是由于其中file 3的resetlogs scn不正确，无法包含该文件重建控制文件

Fri Oct 02 20:10:55 2015
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Fri Oct 02 20:10:55 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_5004.trc:
ORA-01189: ????????????? RESETLOGS
ORA-01110: ???? 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF'
ORA-1503 signalled during:  CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS FORCE LOGGING ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 8
     MAXLOGHISTORY 2921
 LOGFILE
 GROUP 3 'E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M,
 GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG'  size 50M,
 GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG'  size 50M
 DATAFILE
'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.DBF'
 CHARACTER SET AL32UTF8
 ...

除掉file 3 继续重建控制文件

Fri Oct 02 20:33:11 2015
Successful mount of redo thread 1, with mount id 1419796614
Completed:  CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS FORCE LOGGING ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 8
     MAXLOGHISTORY 2921
 LOGFILE
 GROUP 3 'E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M,
 GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG'  size 50M,
 GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG'  size 50M
 DATAFILE
'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF',
--'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.DBF'
 CHARACTER SET AL32UTF8

继续恢复数据库

ALTER DATABASE OPEN
Fri Oct 02 20:34:57 2015
…………
Archived Log entry 3 added for thread 1 sequence 8 ID 0x54a083a3 dest 1:
Fri Oct 02 20:35:16 2015
Tablespace 'SYSAUX' #1 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #3 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00003' in the controlfile.
Corrected file 15 plugged in read-only status in control file
Corrected file 16 plugged in read-only status in control file
Corrected file 17 plugged in read-only status in control file
Corrected file 18 plugged in read-only status in control file
Corrected file 19 plugged in read-only status in control file
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Fri Oct 02 20:35:19 2015
SMON: enabling tx recovery
Fri Oct 02 20:35:19 2015
*********************************************************************
WARNING: The following temporary tablespaces in container(CDB$ROOT)
         contain no files.
Starting background process SMCO
Fri Oct 02 20:35:19 2015
SMCO started with pid=45, OS id=1500
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
Fri Oct 02 20:35:21 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_2220.trc:
ORA-00376: 此时无法读取文件 3
ORA-01111: 数据文件 3 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 3: 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\MISSING00003'
Fri Oct 02 20:35:21 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_2220.trc:
ORA-00376: 此时无法读取文件 3
ORA-01111: 数据文件 3 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 3: 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\MISSING00003'
Error 376 happened during db open, shutting down database
USER (ospid: 2220): terminating the instance due to error 376
Fri Oct 02 20:35:26 2015
Instance terminated by USER, pid = 2220
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (2220) as a result of ORA-1092

此时由于file 3 未包含在控制文件中，但是存在数据字典中，因此在数据库open的时候出现了默认文件名MISSING0003，尝试重命名改文件指定为存在的file 3,并且尝试恢复

SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 3.1868E+10 bytes
Fixed Size                  3601144 bytes
Variable Size            2.8655E+10 bytes
Database Buffers         3154116608 bytes
Redo Buffers               54804480 bytes
数据库装载完毕。
SQL> alter database datafile 3 offline;
数据库已更改。
SQL> alter database rename file 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\
MISSING00003' to 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF';
数据库已更改。
SQL> recover database until cancel;
ORA-00279: 更改 617412726 (在 10/02/2015 20:35:06 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ORAADM\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_10_02\O1_MF_1_9_%U_.ARC
ORA-00280: 更改 617412726 (用于线程 1) 在序列 #9 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG
ORA-00310: archived log contains sequence 7; sequence 9 required
ORA-00334: archived log: 'E:\APP\ORAADM\ORADATA\ORCL\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: 'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF'
SQL> recover database until cancel;
ORA-00279: 更改 617412726 (在 10/02/2015 20:35:06 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ORAADM\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_10_02\O1_MF_1_9_%U_.ARC
ORA-00280: 更改 617412726 (用于线程 1) 在序列 #9 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG
已应用的日志。
完成介质恢复。
SQL> alter database datafile 3 online;
数据库已更改。
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01122: 数据库文件 3 验证失败
ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF'
ORA-01202: 此文件的原型错误 - 创建时间错误

这里比较明显ORA-01202，由于创建控制文件之时没有file 3信息，因此导致控制文件中关于file 3的信息和该文件头的创建时间不一致（此处之时显示了时间不一致，如果通过bbed修改时间，后续可能还有很多东西不一致，因此通过bbed 一个个修改一个个尝试，理论可行，但实际可操作性不好）,因此尝试直接使用bbed修改file 3文件头（由于是win环境，操作稍微麻烦点），把resetlogs信息修改和其他的一样

BBED> m /x 3c6b2b35
 File: SYSAUX01.dbf (3)
 Block: 2                Offsets:  112 to  143           Dba:0x00c00002
------------------------------------------------------------------------
 3c6b2b35 386b2200 00000000 00000000 00000000 00000000 00004000 bb460000
 <32 bytes per line>
BBED> set offset 116
        OFFSET          116
BBED> m /x 3137ca24
 File: SYSAUX01.dbf (3)
 Block: 2                Offsets:  116 to  147           Dba:0x00c00002
------------------------------------------------------------------------
 3137ca24 00000000 00000000 00000000 00000000 00004000 bb460000 7dc12b35
 <32 bytes per line>
BBED> m /x b9f8
 File: SYSAUX01.dbf (3)
 Block: 2                Offsets:  484 to  515           Dba:0x00c00002
------------------------------------------------------------------------
 b9f8a424 00000000 e65e2435 01000000 d3410000 b89b0000 10000900 02000000
 <32 bytes per line>
BBED> set offset +2
        OFFSET          486
BBED> m /x cc24
 File: SYSAUX01.dbf (3)
 Block: 2                Offsets:  486 to  517           Dba:0x00c00002
------------------------------------------------------------------------
 cc240000 0000e65e 24350100 0000d341 0000b89b 00001000 09000200 00000000
 <32 bytes per line>
BBED> m /x 87df offset 492
 File: SYSAUX01.dbf (3)
 Block: 2                Offsets:  492 to  523           Dba:0x00c00002
------------------------------------------------------------------------
 87df2435 01000000 d3410000 b89b0000 10000900 02000000 00000000 00000000
 <32 bytes per line>
BBED>
BBED> m /x 2b35 offset +2
 File: SYSAUX01.dbf (3)
 Block: 2                Offsets:  494 to  525           Dba:0x00c00002
------------------------------------------------------------------------
 2b350100 0000d341 0000b89b 00001000 09000200 00000000 00000000 00000000
 <32 bytes per line>
BBED> d offset 140
 File: SYSAUX01.dbf (3)
 Block: 2                Offsets:  140 to  171           Dba:0x00c00002
------------------------------------------------------------------------
 bb460000 7dc12b35 ba460000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x 4248
 File: SYSAUX01.dbf (3)
 Block: 2                Offsets:  140 to  171           Dba:0x00c00002
------------------------------------------------------------------------
 42480000 7dc12b35 ba460000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> d offset 148
 File: SYSAUX01.dbf (3)
 Block: 2                Offsets:  148 to  179           Dba:0x00c00002
------------------------------------------------------------------------
 ba460000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x 4148
 File: SYSAUX01.dbf (3)
 Block: 2                Offsets:  148 to  179           Dba:0x00c00002
------------------------------------------------------------------------
 41480000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> sum apply
Check value for File 3, Block 2:
current = 0xd0c8, required = 0xd0c8
BBED> verify
DBVERIFY - Verification starting
FILE = SYSAUX01.dbf
BLOCK = 1
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

修改完file 3的文件头之后，再次重建控制文件,此次包含file 3

Fri Oct 02 21:19:58 2015
Successful mount of redo thread 1, with mount id 1419797885
Completed:  CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS FORCE LOGGING ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 8
     MAXLOGHISTORY 2921
 LOGFILE
 GROUP 3 'E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M,
 GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG'  size 50M,
 GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG'  size 50M
 DATAFILE
'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.DBF'
 CHARACTER SET AL32UTF8

继续恢复数据库,数据库正常open，而且file 3 已经正常online，数据库可以直接导出来，至此恢复大体完成

ORA-01122 ORA-01210 故障恢复

有朋友数据文件头出现错误ORA-01122和ORA-01210等错误，数据库无法正常open。

因为平台是win，他们找我咨询win bbed,因为回老家电脑没有带,无法提供win的bbed.我通过dd部分文件头,然后在linux平台分析发现是该文件的文件头block大量坏块

bbed分析坏块情况

BBED> show all
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME        /tmp/30.dbf
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       8192
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No
BBED> set count 64
        COUNT           64
BBED> map
 File: /tmp/30.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (27)
BBED> d
 File: /tmp/30.dbf (0)
 Block: 1                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 03004400 bffd8a1d 0000000c acba0000 008f4500 00003455 fc020000
 02040000 00000000 00008001 04000000 00000000 00000000 949400b4 94514005
 <32 bytes per line>
BBED> set block +1
        BLOCK#          2
BBED> map
 File: /tmp/30.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (27)
BBED> d
 File: /tmp/30.dbf (0)
 Block: 2                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 04004400 bffd8a1d 0000000c a6e00000 008f4500 00003455 fc020000
 0204e81f 00000000 0000241e 05000000 00000000 00000000 11fc297f b426fe2b
 <32 bytes per line>
BBED> set block +1
        BLOCK#          3
BBED> d
 File: /tmp/30.dbf (0)
 Block: 3                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 05004400 bffd8a1d 0000000c 780a0000 008f4500 00003455 fc020000
 0204e81f 00000000 0000c001 06000000 00000000 00000000 2969a0d2 d30168a2
 <32 bytes per line>
BBED> set block +1
        BLOCK#          4
BBED> d
 File: /tmp/30.dbf (0)
 Block: 4                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 06004400 bffd8a1d 0000000c 6c5a0000 008f4500 00003455 fc020000
 0204e81f 00000000 0000f81d 07000000 00000000 00000000 7b51d409 6dc7ca4d
 <32 bytes per line>
BBED> set block +1
        BLOCK#          5
BBED> d
 File: /tmp/30.dbf (0)
 Block: 5                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 07004400 bffd8a1d 0000000c c5600000 008f4500 00003455 fc020000
 02040000 00000000 0000c001 08000000 00000000 00000000 14514005 25145200
 <32 bytes per line>
BBED> set block +1
        BLOCK#          6
BBED> d
 File: /tmp/30.dbf (0)
 Block: 6                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 08004400 bffd8a1d 0000000c 60480000 008f4500 00003455 fc020000
 0204e81f 00000000 0000c301 09000000 00000000 00000000 c2a1606a 7615130a
 <32 bytes per line>
BBED> set block +1
        BLOCK#          7
BBED> d
 File: /tmp/30.dbf (0)
 Block: 7                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 09004400 bffd8a1d 0000000c e3430000 008f4500 00003455 fc020000
 0204e81f 00000000 00000002 0a000000 00000000 00000000 00a28a28 00a28a28
 <32 bytes per line>
BBED> set block +1
        BLOCK#          8
BBED> d
 File: /tmp/30.dbf (0)
 Block: 8                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0a004400 07fe8a1d 0000000c fc000000 008f4500 00003455 fc020000
 0205e81f 00000000 0000f41d 00000000 00000000 00000000 ffd8ffe0 00104a46
 <32 bytes per line>
BBED> set block +1
        BLOCK#          9
BBED> d
 File: /tmp/30.dbf (0)
 Block: 9                Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0b004400 07fe8a1d 0000000c 48da0000 008f4500 00003455 fc020000
 0205e81f 00000000 0000c601 01000000 00000000 00000000 b47d69d3 7fa96a6f
 <32 bytes per line>
BBED> set block +1
        BLOCK#          10
BBED> d
 File: /tmp/30.dbf (0)
 Block: 10               Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0c004400 07fe8a1d 0000000c be0f0000 008f4500 00003455 fc020000
 0205e81f 00000000 0000181d 02000000 00000000 00000000 9de3e868 4782d83a
 <32 bytes per line>
BBED> set block +1
        BLOCK#          11
BBED> d
 File: /tmp/30.dbf (0)
 Block: 11               Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0d004400 07fe8a1d 0000000c 9cd00000 008f4500 00003455 fc020000
 0205e81f 00000000 0000241e 03000000 00000000 00000000 dead1259 5919e385
 <32 bytes per line>
BBED> set block +1
        BLOCK#          12
BBED> d
 File: /tmp/30.dbf (0)
 Block: 12               Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0e004400 07fe8a1d 0000000c df450000 008f4500 00003455 fc020000
 0205e81f 00000000 00004001 04000000 00000000 00000000 31d9a292 9698828a
 <32 bytes per line>
BBED> set block +1
        BLOCK#          13
BBED> d
 File: /tmp/30.dbf (0)
 Block: 13               Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 1ba20000 0f004400 07fe8a1d 0000000c 18790000 008f4500 00003455 fc020000
 02050000 00000000 00000002 05000000 00000000 00000000 b93f8235 5ea063b7
 <32 bytes per line>

拿block 1的rdba(04004400–倒序存储)分析[win文件拷贝到linux后使用bbed查看相差1 block]可以的出来block信息为file=1, block=262148,明显错误.

通过dul分析文件头损坏情况

Data UnLoader: 10.2.0.6.9 - Internal Only - on Tue Sep 29 22:15:22 2015
with 64-bit io functions
Copyright (c) 1994 2015 Bernard van Duijnen All rights reserved.
 Strictly Oracle Internal Use Only
DUL: Warning: Recreating file "dul.log"
Reading SCANNEDLOBPAGE.dat 1204 entries loaded and sorted 1204 entries
Reading SEG.dat 0 entries loaded
Reading EXT.dat 44 entries loaded and sorted 44 entries
Reading COMPATSEG.dat 0 entries loaded
DUL: Warning: Wrong DBA  0X00440004 (file=1, block=262148) (Ignored)
DUL: Error: While processing file# 30 block# 1
DUL: Warning: Found mismatch while checking file E:\TEMP\shebao\30.dbf
DUL: Warning: DUL osd_parameter or control.dul configuration error
DUL: Warning: Given file number(30) in control file does not match file# in dba(1)
DUL: Warning: Wrong DBA  0X00440004 (file=1, block=262148) (Ignored)
DUL: Error: While processing file# 30 block# 1
DUL>

通过bbed和dul证明文件头大量损坏,而且尚未有任何该文件的物理备份,因此恢复起来难道较大。

分析Oracle Database Recovery Check Result
通过对Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)的分析结果,我们意外的发现,人品不错，发现异常的文件创建时间为2015-09-26 19:39:33，进一步和客户沟通，这个文件存储为图片，少量丢失可以允许，优先恢复业务
Oracle-Database-Recovery-Check

有了这个结论,那处理起来就easy了,直接offline异常文件,然后分析丢失的表
从而确定时lob字典的少量extent数据分配到了file 30上
lob

为了避免查询对应lob之时出现错误,通过update 对应lob为空规避该问题

create table corrupt_lobs (corrupt_rowid rowid,table_name varchar2(100));
declare
  n number;
begin
  for cursor_lob in (select rowid r, xff_lob from xff.t_xifenfei) loop
  begin
    n:=dbms_lob.instr(cursor_lob.xff_lob,hextoraw('889911'));
  exception
    when others then
      insert into corrupt_lobs values (cursor_lob.r,'xff.t_xifenfei');
      commit;
    end;
  end loop;
end;
/
update xff.t_xifenfei
     set xff_lob = empty_blob()
     where rowid in (select corrupted_rowid from corrupt_lobs);

本次恢复是由于运气好,遇到异常文件刚好是最近加入,而且都是图片,客户允许少量丢失,如果是不允许丢失的数据文件,可能需要通过找历史的该文件的备份(Oracle 12C的第一次异常恢复—文件头坏块),在某些情况下,如果也没有此类备份,只能通过bbed重构block 1(如果有其他异常块一次处理,如果太多无法处理,最少也需要重构block 1）,然后尝试open数据库或者使用dul之类工具处理（因为文件头损坏,工具可能不能识别文件无法恢复）

ORA-600 k2vcbk_2 故障恢复

有朋友找到我说他们数据库无法启动，数据库启动报ORA-600[k2vcbk_2]错误，数据库版本为11.2.0.2 RAC，操作系统是AIX 6.1

SQL> recover database;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [k2vcbk_2], [], [], [], [], [], [],
[], [], [], [], []
Process ID: 7930020
Session ID: 49 Serial number: 14761

数据库节点1日志

Mon Sep 21 15:45:41 2015
Thread 1 advanced to log sequence 54076 (LGWR switch)
  Current log# 13 seq# 54076 mem# 0: +DG01/xifenfei/onlinelog/group_13.332.779459035
  Current log# 13 seq# 54076 mem# 1: +DG01/xifenfei/onlinelog/group_13.344.779582621
Mon Sep 21 15:45:44 2015
Archived Log entry 74655 added for thread 1 sequence 54075 ID 0x5a0bc0e1 dest 1:
Mon Sep 21 15:56:18 2015
Errors in file /oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_18088342.trc  (incident=184348):
ORA-00600: 内部错误代码, 参数: [kturPOTS_0], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_184348/xifenfei1_ora_18088342_i184348.trc
Mon Sep 21 15:56:34 2015
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Error 600 trapped in 2PC on transaction 7.16.120119. Cleaning up.
Error stack returned to user:
ORA-00600: 内部错误代码, 参数: [kturPOTS_0], [], [], [], [], [], [], [], [], [], [], []
Errors in file /oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_18088342.trc  (incident=184349):
ORA-00603: ORACLE 服务器会话因致命错误而终止
ORA-00600: 内部错误代码, 参数: [kturPOTS_0], [], [], [], [], [], [], [], [], [], [], []
Mon Sep 21 15:56:34 2015
Dumping diagnostic data in directory=[cdmp_20150921155634], requested by (instance=1, osid=18088342), summary=[incident=184348].
Incident details in: /oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_184349/xifenfei1_ora_18088342_i184349.trc
Mon Sep 21 15:56:35 2015
Sweep [inc][184349]: completed
Sweep [inc][184348]: completed
Sweep [inc2][184348]: completed
opiodr aborting process unknown ospid (18088342) as a result of ORA-603
Mon Sep 21 15:57:12 2015
Errors in file /oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_smon_7536810.trc  (incident=184274):
ORA-00600: internal error code, arguments: [k2vcbk_2], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_184274/xifenfei1_smon_7536810_i184274.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Sep 21 15:57:16 2015
Dumping diagnostic data in directory=[cdmp_20150921155716], requested by (instance=1, osid=7536810 (SMON)), summary=[incident=184274].
Fatal internal error happened while SMON was doing active transaction recovery.
Errors in file /oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_smon_7536810.trc:
ORA-00600: internal error code, arguments: [k2vcbk_2], [], [], [], [], [], [], [], [], [], [], []
SMON (ospid: 7536810): terminating the instance due to error 474
Mon Sep 21 15:57:18 2015
ORA-1092 : opitsk aborting process

数据库节点2日志

Mon Sep 21 15:21:50 2015
Archived Log entry 74653 added for thread 2 sequence 23559 ID 0x5a0bc0e1 dest 1:
Mon Sep 21 15:44:28 2015
Thread 2 advanced to log sequence 23561 (LGWR switch)
  Current log# 12 seq# 23561 mem# 0: +DG01/xifenfei/onlinelog/group_12.338.779457003
  Current log# 12 seq# 23561 mem# 1: +DG01/xifenfei/onlinelog/group_12.265.779582493
Mon Sep 21 15:44:31 2015
Archived Log entry 74654 added for thread 2 sequence 23560 ID 0x5a0bc0e1 dest 1:
Mon Sep 21 15:45:31 2015
DISTRIB TRAN xifenfei.1ebab0a5.20.3.1533822
  is local tran 20.3.1533822 (hex=14.03.17677e)
  insert pending committed tran, scn=14590688068086 (hex=d45.28c781f6)
Mon Sep 21 15:45:31 2015
DISTRIB TRAN xifenfei.1ebab0a5.20.3.1533822
  is local tran 20.3.1533822 (hex=14.03.17677e))
  delete pending committed tran, scn=14590688068086 (hex=d45.28c781f6)
Mon Sep 21 15:56:35 2015
Dumping diagnostic data in directory=[cdmp_20150921155634], requested by (instance=1, osid=18088342), summary=[incident=184348].
Mon Sep 21 15:57:10 2015
Error 3135 trapped in 2PC on transaction 20.11.1534704. Cleaning up.
Error stack returned to user:
ORA-03135: 连接失去联系
opidcl aborting process unknown ospid (9175532) as a result of ORA-604
Mon Sep 21 15:57:17 2015
Dumping diagnostic data in directory=[cdmp_20150921155716], requested by (instance=1, osid=7536810 (SMON)), summary=[incident=184274].
Mon Sep 21 15:57:23 2015
Reconfiguration started (old inc 18, new inc 20)
List of instances:
 2 (myinst: 2)
 Global Resource Directory frozen
 * dead instance detected - domain 0 invalid = TRUE
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
Mon Sep 21 15:57:23 2015
 LMS 2: 3 GCS shadows cancelled, 1 closed, 0 Xw survived
Mon Sep 21 15:57:23 2015
 LMS 0: 2 GCS shadows cancelled, 0 closed, 0 Xw survived
Mon Sep 21 15:57:23 2015
 LMS 1: 3 GCS shadows cancelled, 1 closed, 0 Xw survived
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Post SMON to start 1st pass IR
Mon Sep 21 15:57:23 2015
minact-scn: Inst 2 is now the master inc#:20 mmon proc-id:6816208 status:0x7
minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0d45.28c2bb5c gcalc-scn:0x0d45.28c3bd2e
minact-scn: master found reconf/inst-rec before recscn scan old-inc#:20 new-inc#:20
Mon Sep 21 15:57:23 2015
Instance recovery: looking for dead threads
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
 Fix write in gcs resources
Reconfiguration complete
Beginning instance recovery of 1 threads
 parallel recovery started with 31 processes
Started redo scan
Completed redo scan
 read 12626 KB redo, 1724 data blocks need recovery
Started redo application at
 Thread 1: logseq 54076, block 184416
Recovery of Online Redo Log: Thread 1 Group 13 Seq 54076 Reading mem 0
  Mem# 0: +DG01/xifenfei/onlinelog/group_13.332.779459035
  Mem# 1: +DG01/xifenfei/onlinelog/group_13.344.779582621
Completed redo application of 9.78MB
Completed instance recovery at
 Thread 1: logseq 54076, block 209669, scn 14590688357285
 1633 data blocks read, 1794 data blocks written, 12626 redo k-bytes read
Thread 1 advanced to log sequence 54077 (thread recovery)
Mon Sep 21 15:57:33 2015
Error 3113 trapped in 2PC on transaction 21.18.1965522. Cleaning up.
Redo thread 1 internally disabled at seq 54077 (SMON)
Error stack returned to user:
ORA-02050: 事务处理 21.18.1965522 已回退, 某些远程数据库可能有问题
ORA-03113: 通信通道的文件结尾
ORA-02063: 紧接着 line (起自 ZSK)
Mon Sep 21 15:57:34 2015
Archived Log entry 74656 added for thread 1 sequence 54076 ID 0x5a0bc0e1 dest 1:
Mon Sep 21 15:57:34 2015
ARC0: Archiving disabled thread 1 sequence 54077
Archived Log entry 74657 added for thread 1 sequence 54077 ID 0x5a0bc0e1 dest 1:
Mon Sep 21 15:57:35 2015
Thread 2 advanced to log sequence 23562 (LGWR switch)
  Current log# 8 seq# 23562 mem# 0: +DG01/xifenfei/onlinelog/group_8.334.779456945
  Current log# 8 seq# 23562 mem# 1: +DG01/xifenfei/onlinelog/group_8.267.779582453
Mon Sep 21 15:57:36 2015
Errors in file /oracle/diag/rdbms/xifenfei/xifenfei2/trace/xifenfei2_smon_6750672.trc  (incident=200218):
ORA-00600: internal error code, arguments: [k2vcbk_2], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/xifenfei/xifenfei2/incident/incdir_200218/xifenfei2_smon_6750672_i200218.trc
Archived Log entry 74658 added for thread 2 sequence 23561 ID 0x5a0bc0e1 dest 1:
Mon Sep 21 15:57:38 2015
minact-scn: master continuing after IR
Mon Sep 21 15:57:41 2015
Dumping diagnostic data in directory=[cdmp_20150921155741], requested by (instance=2, osid=6750672 (SMON)), summary=[incident=200218].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fatal internal error happened while SMON was doing instance transaction recovery.
Errors in file /oracle/diag/rdbms/xifenfei/xifenfei2/trace/xifenfei2_smon_6750672.trc:
ORA-00600: internal error code, arguments: [k2vcbk_2], [], [], [], [], [], [], [], [], [], [], []
SMON (ospid: 6750672): terminating the instance due to error 474
Mon Sep 21 15:57:41 2015
ORA-1092 : opitsk aborting process
Mon Sep 21 15:57:42 2015
ORA-1092 : opitsk aborting process
Mon Sep 21 15:57:42 2015
License high water mark = 291
Instance terminated by SMON, pid = 6750672
USER (ospid: 18874814): terminating the instance

通过数据库日志大概可以看出来，由于节点2的分布式事事务异常，而在11.2.0.2中，分布式事务跨节点，引起节点2的pmon清理异常事务，但是由于bug，使得异常事务无法被清理掉，从而引起节点1 crash,节点1 crash之后节点2进行恢复，也因为分布式事务bug，导致smon回滚失败，实例也crash。无法进行回滚导致数据库无法正常启动，通过查询mos发现定位到是Bug 10222544 ORA-600 [k2vpci_2] from multi-branch distributed transaction
ORA-600-k2vpci_2

对于这类问题，由于分布事务无法清理,处理方法就是找出来事务人工提交或者直接屏蔽掉这个事务解决该问题