admin – 第31页 – Database SOS

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

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

ogg同步部分列配置

自从2010年后,基本上没有玩ogg了,最近有客户需求,a库在内网,b库在外网,希望同步a库中几个基础业务表的每个表的几个字段同步到b库中,采用a–>c–>b的方式来实现同步(c同时接通内外网),ogg 本身同步不难,关键是自己好多年没有玩,而且这次是只要同步部分列的情况,因此做了一个同步表部分列的一个demo测试
源端数据库准备
启动归档模式，开启强制日志和辅助日志，创建测试用户/表，ogg用户

SQL> create user xifenfei identified by xifenfei;
User created.
SQL> grant dba to xifenfei;
Grant succeeded.
SQL> conn xifenfei/xifenfei
Connected.
SQL> create table t_xifenfei as select * from dba_objects;
Table created.
SQL> alter table t_xifenfei add constraint pk_t_xifenfei primary key(object_id);
Table altered.
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     9
Current log sequence           11
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  901775360 bytes
Fixed Size                  2024944 bytes
Variable Size             239077904 bytes
Database Buffers          658505728 bytes
Redo Buffers                2166784 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database force logging;
Database altered.
SQL>  alter database add supplemental log data;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> create user ogg identified by oracle;
User created.
SQL> grant dba to ogg;
Grant succeeded.

配置mgr进程

[oracle@xffdbrh5 ogg]$ export PATH=/u01/ogg:$PATH
[oracle@xffdbrh5 ogg]$ export LD_LIBRARY_PATH=/u01/ogg:$ORACLE_HOME/lib
[oracle@xffdbrh5 ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_02 16363018 OGGCORE_11.2.1.0.6_PLATFORMS_130301.1500_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Mar  1 2013 19:04:05
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (xffdbrh5) 1> create subdirs
Creating subdirectories under current directory /u01/ogg
Parameter files                /u01/ogg/dirprm: already exists
Report files                   /u01/ogg/dirrpt: created
Checkpoint files               /u01/ogg/dirchk: created
Process status files           /u01/ogg/dirpcs: created
SQL script files               /u01/ogg/dirsql: created
Database definitions files     /u01/ogg/dirdef: created
Extract data files             /u01/ogg/dirdat: created
Temporary files                /u01/ogg/dirtmp: created
Stdout files                   /u01/ogg/dirout: created
GGSCI (xffdbrh5) 2> edit param mgr
port 7839
DYNAMICPORTLIST 7840-7850
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI (xffdbrh5) 3> dblogin userid ogg, password oracle
Successfully logged into database.
GGSCI (xffdbrh5) 4> add checkpointtable ogg.ggs_checkpoint
Successfully created checkpoint table ogg.ggs_checkpoint.
GGSCI (xffdbrh5) 5> EDIT PARAMS ./GLOBALS
ogg.ggs_checkpoint
GGSCI (xffdbrh5) 6> start mgr
Manager started.
GGSCI (xffdbrh5) 7> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING

配置extract进程

GGSCI (xffdbrh5) 3> dblogin userid ogg, password oracle
Successfully logged into database.
GGSCI (xffdbrh5) 4> add trandata xifenfei.t_xifenfei
Logging of supplemental redo data enabled for table XIFENFEI.T_XIFENFEI.
GGSCI (xffdbrh5) 5>  add extract ext_1, tranlog, begin now, threads 1
EXTRACT added.
GGSCI (xffdbrh5) 6>  add EXTTRAIL ./dirdat/r1, extract ext_1,megabytes 100
EXTTRAIL added.
GGSCI (xffdbrh5) 7> edit param ext_1
EXTRACT ext_1
userid ogg,password oracle
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/ext_1.dsc,APPEND,MEGABYTES 1024
DISCARDROLLOVER AT 3:00
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
TRANLOGOPTIONS DISABLESUPPLOGCHECK   --bug 16857778
TABLE xifenfei.t_xifenfei, COLS (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID);
GGSCI (xffdbrh5) 8> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     EXT_1       00:00:00      00:00:22
GGSCI (xffdbrh5) 9> start ext_1
Sending START request to MANAGER ...
EXTRACT EXT_1 starting
GGSCI (xffdbrh5) 10> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT_1       00:01:18      00:00:00

配置pump data进程

GGSCI (xffdbrh5) 1> edit param dpe_1
extract dpe_1
dynamicresolution
passthru
rmthost 192.168.137.251, mgrport 7839, compress
rmttrail ./dirdat/t1
numfiles 5000
TABLE xifenfei.t_xifenfei;
GGSCI (xffdbrh5) 2> start dpe_1
Sending START request to MANAGER ...
EXTRACT DPE_1 starting
GGSCI (xffdbrh5) 3> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     DPE_1       00:00:00      00:16:47
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:07

目标端数据库准备

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 13 00:40:19 2014
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user ogg identified by oracle;
User created.
SQL> grant dba to ogg;
Grant succeeded.
SQL> create user xff identified by xifenfei;
User created.
SQL> grant dba to xff;
Grant succeeded.
SQL> conn xff/xifenfei
Connected.
SQL> create  database link syc_data
  2    connect to ogg identified by oracle
  3    using '(DESCRIPTION =
  4    (ADDRESS_LIST =
  5    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.252)(PORT = 1521))
  6    )
  7    (CONNECT_DATA =
  8    (SERVER = DEDICATED)
  9    (SERVICE_NAME = ora10g)
 10    )
 11    )';
Database link created.
SQL> select count(*) from xifenfei.t_xifenfei@syc_data;
  COUNT(*)
----------
      9917
SQL> SELECT CURRENT_SCN FROM V$DATABASE@syc_data;
CURRENT_SCN
-----------
     793069
SQL> create table xff.t_xff as select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 2 > OBJECT_ID from xifenfei.t_xifenfei@syc_data AS OF SCN  793069;
Table created.
SQL> alter table xff.t_xff add constraint pk_t_xff primary key(object_id);
Table altered.

目标端mgrp配置

[oracle@xifenfei ogg]$export LD_LIBRARY_PATH=/home/oracle/amdu:$ORACLE_HOME/lib:/u01/oracle/oradata/ogg
[oracle@xifenfei ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (xifenfei) 8> edit param mgr
port 7839
DYNAMICPORTLIST 7840-7850
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
autorestart extract *, waitminutes 1, retries 60
autorestart replicat *, waitminutes 1, retries 60
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI (xifenfei) 12> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED
GGSCI (xifenfei) 13> create subdirs
Creating subdirectories under current directory /u01/oracle/oradata/ogg
Parameter files                /u01/oracle/oradata/ogg/dirprm: already exists
Report files                   /u01/oracle/oradata/ogg/dirrpt: already exists
Checkpoint files               /u01/oracle/oradata/ogg/dirchk: already exists
Process status files           /u01/oracle/oradata/ogg/dirpcs: already exists
SQL script files               /u01/oracle/oradata/ogg/dirsql: already exists
Database definitions files     /u01/oracle/oradata/ogg/dirdef: already exists
Extract data files             /u01/oracle/oradata/ogg/dirdat: already exists
Temporary files                /u01/oracle/oradata/ogg/dirtmp: already exists
Stdout files                   /u01/oracle/oradata/ogg/dirout: already exists
GGSCI (xifenfei) 2> dblogin userid ogg, password oracle
Successfully logged into database.
GGSCI (xifenfei) 3> add checkpointtable ogg.ggs_checkpoint
Successfully created checkpoint table ogg.ggs_checkpoint.
GGSCI (xifenfei) 4> EDIT PARAMS ./GLOBALS
checkpointtable ogg.ggs_checkpoint
GGSCI (xifenfei) 5> start mgr
Manager started.
GGSCI (xifenfei) 6> add replicat rep_1,exttrail ./dirdat/t1,checkpointtable ogg.ggs_checkpoint
REPLICAT added.
GGSCI (xifenfei) 7> edit params rep_1
REPLICAT rep_1
USERID ogg,PASSWORD oracle
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
assumetargetdefs
DISCARDFILE ./dirrpt/rep_1.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 3:00
ALLOWNOOPUPDATES
MAP xifenfei.t_xifenfei, TARGET xff.t_xff;
GGSCI (xifenfei) 8> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     REP_1       00:00:00      00:01:45
GGSCI (xifenfei) 9> start rep_1,aftercsn 793069
Sending START request to MANAGER ...
REPLICAT REP_1 starting
GGSCI (xifenfei) 10> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     REP_1       00:00:00      00:00:01

测试数据库同步

--源端库
SQL> desc t_XIFENFEI
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
SQL> update t_XIFENFEI set owner='www.xifenfei.com' where rownum<100;
99 rows updated.
SQL> commit;
Commit complete.
--目标端库
SQL> desc xff.t_xff
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
SQL> select count(*) from xff.t_xff where owner='www.xifenfei.com';
  COUNT(*)
----------
        99
--源端库
SQL> delete from t_XIFENFEI where  owner='www.xifenfei.com';
99 rows deleted.
SQL> commit;
Commit complete.
--目标端
SQL> select count(*) from xff.t_xff where owner='www.xifenfei.com';
  COUNT(*)
----------
         0
--源端库
SQL> insert into xifenfei.t_xifenfei(owner,object_id) values('www.xifenfei.com',1);
1 row created.
SQL> commit;
Commit complete.
--目标端库
SQL>  select count(*) from xff.t_xff where owner='www.xifenfei.com';
  COUNT(*)
----------
         1
SQL> select * from xff.t_xff where owner='www.xifenfei.com';
OWNER                OBJECT_NAME         SUBOBJECT_NAME                  OBJECT_ID
-------------------- ------------------- ------------------------------ ----------
www.xifenfei.com                                                                 1

这里实现部分列同步,主要在extract端使用COLS捕获需要列,使用ctas结合dblink,flashback query实现表测试后.

ora.crf资源异常—临时停止和禁用

检查发现运行在win 2008平台的11.2.0.3 rac的crs的alert日志里面出现大量类似记录
CRS-2765错误

2015-09-04 00:12:10.431
[ohasd(3844)]CRS-2765:资源 'ora.crf' 已失败 (在服务器 'rac2' 上)。
2015-09-04 00:16:46.047
[ohasd(3844)]CRS-2765:资源 'ora.crf' 已失败 (在服务器 'rac2' 上)。
2015-09-04 00:21:21.479
[ohasd(3844)]CRS-2765:资源 'ora.crf' 已失败 (在服务器 'rac2' 上)。
2015-09-04 00:25:57.365
[ohasd(3844)]CRS-2765:资源 'ora.crf' 已失败 (在服务器 'rac2' 上)。

查看crfmond.log日志发现类似记录

2015-09-04 00:07:35.607: [    GPNP][19080] clsgpnp_getCachedProfileEx: [at clsgpnp.c:613] Result: (26)
 CLSGPNP_NO_PROFILE. Can't get offline GPnP service profile: local gpnpd is up and running. Use getProfile instead.
2015-09-04 00:07:35.607: [    GPNP][19080] clsgpnp_getCachedProfileEx: [at clsgpnp.c:623] Result:
(26) CLSGPNP_NO_PROFILE. Failed to get offline GPnP service profile.
2015-09-04 00:07:35.732: [ CRFMOND][19080]Sysmond coming up...
2015-09-04 00:07:35.732: [ CRFMOND][19080]Failed to load init file ret=1
2015-09-04 00:07:35.732: [ CRFMOND][19080]OSD error: op="scrfosm_loadInitFile" loc="read fail1"
other="crfhome="D:\app\11.2.0\grid" and gipath="D:\app\11.2.0\grid\crf\admin\crf.ora"" dep="2"
2015-09-04 00:07:37.095: [ COMMCRS][19696]clsc_send_msg: (00000000058C98E0) NS err (12571, 12560), transport (533, 57, 0)
[  clsdmc][19676]Fail to connect (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=61022)) with status 9
[  clsdmt][19712]Listening to (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=61022))
2015-09-04 00:07:37.201: [  clsdmt][19712]PID for the Process [19672], connkey 5
2015-09-04 00:07:37.201: [  clsdmt][19712]Creating PID [19672] file for home D:\app\11.2.0\grid
host rac2 bin osysmond to D:\app\11.2.0\grid\osysmond\init\
2015-09-04 00:07:37.202: [  clsdmt][19712]Writing PID [19672] to the file [D:\app\11.2.0\grid\osysmond\init\rac2.pid]
2015-09-04 00:07:37.734: [ CRFMOND][19676]mond_init: clsdms init successful
[   CLWAL][19676]clsw_Initialize: OLR initlevel [70000]
2015-09-04 00:12:10.050: [    GPNP][19676] clsgpnp_getCachedProfileEx: [at clsgpnp.c:613] Result: (26)
CLSGPNP_NO_PROFILE. Can't get offline GPnP service profile: local gpnpd is up and running. Use getProfile instead.
2015-09-04 00:12:10.051: [    GPNP][19676] clsgpnp_getCachedProfileEx: [at clsgpnp.c:623] Result:
(26) CLSGPNP_NO_PROFILE. Failed to get offline GPnP service profile.
2015-09-04 00:12:10.197: [ CRFMOND][19676]Sysmond coming up...
2015-09-04 00:12:10.197: [ CRFMOND][19676]Failed to load init file ret=1
2015-09-04 00:12:10.197: [ CRFMOND][19676]OSD error: op="scrfosm_loadInitFile" loc="read fail1"
other="crfhome="D:\app\11.2.0\grid" and gipath="D:\app\11.2.0\grid\crf\admin\crf.ora"" dep="2"
2015-09-04 00:12:11.557: [ COMMCRS][18376]clsc_send_msg: (00000000059498E0) NS err (12571, 12560), transport (533, 57, 0)

查询mos发现匹配文章Windows: CRS-2765:Resource ‘ora.crf’ has failed on server (文档 ID 1480263.1),从文中说明看是由于unpublished bug 14010695导致该问题,给出来建议是打psu到最新,但是升级psu需要停机窗口。临时想通过禁用ora.crf资源的方式来解决,在禁用该资源之前,我们先看下该资源的用途,确定是否可以禁用。

ora.crf用途
资源对应的功能是CHM.Cluster Health Monitor（以下简称CHM）是一个Oracle提供的工具，用来自动收集操作系统的资源（CPU、内存、SWAP、进程、I/O以及网络等）的使用情况。CHM会每秒收集一次数据。这些系统资源数据对于诊断集群系统的节点重启、Hang、实例驱逐(Eviction)、性能问题等是非常有帮助的。另外，用户可以使用CHM来及早发现一些系统负载高、内存异常等问题，从而避免产生更严重的问题。CHM会自动安装在下面的软件：
11.2.0.2 及更高版本的 Oracle Grid Infrastructure for Linux (不包括Linux Itanium) 、Solaris (Sparc 64 和 x86-64)
11.2.0.3 及更高版本 Oracle Grid Infrastructure for AIX 、 Windows (不包括Windows Itanium)。
根据上述描述可知ora.crf资源主要是用来收集信息的,而且在11.2.0.2之后才有,因此可以停止并禁用它

停止ora.crf资源

C:\Users\Administrator>crsctl status res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       rac2                     Started
ora.crf
      1        ONLINE  ONLINE       rac2
ora.crsd
      1        ONLINE  ONLINE       rac2
ora.cssd
      1        ONLINE  ONLINE       rac2
ora.cssdmonitor
      1        ONLINE  ONLINE       rac2
ora.ctssd
      1        ONLINE  ONLINE       rac2                     OBSERVER
ora.drivers.acfs
      1        ONLINE  ONLINE       rac2
ora.evmd
      1        ONLINE  ONLINE       rac2
ora.gipcd
      1        ONLINE  ONLINE       rac2
ora.gpnpd
      1        ONLINE  ONLINE       rac2
ora.mdnsd
      1        ONLINE  ONLINE       rac2
C:\Users\Administrator>crsctl stop res ora.crf -init
CRS-2673: 尝试停止 'ora.crf' (在 'rac2' 上)
CRS-2677: 成功停止 'ora.crf' (在 'rac2' 上)
C:\Users\Administrator>crsctl status res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       rac2                     Started
ora.crf
      1        OFFLINE OFFLINE
ora.crsd
      1        ONLINE  ONLINE       rac2
ora.cssd
      1        ONLINE  ONLINE       rac2
ora.cssdmonitor
      1        ONLINE  ONLINE       rac2
ora.ctssd
      1        ONLINE  ONLINE       rac2                     OBSERVER
ora.drivers.acfs
      1        ONLINE  ONLINE       rac2
ora.evmd
      1        ONLINE  ONLINE       rac2
ora.gipcd
      1        ONLINE  ONLINE       rac2
ora.gpnpd
      1        ONLINE  ONLINE       rac2
ora.mdnsd
      1        ONLINE  ONLINE       rac2

禁用ora.crf资源

C:\Users\Administrator>crsctl stat res ora.crf -init
NAME=ora.crf
TYPE=ora.crf.type
TARGET=OFFLINE
STATE=OFFLINE
C:\Users\Administrator>crsctl modify resource "ora.crf" -attr "AUTO_START=0" -init
C:\Users\Administrator>crsctl stat res ora.crf -init
NAME=ora.crf
TYPE=ora.crf.type
TARGET=OFFLINE
STATE=OFFLINE

Linux 7 新命令之—lscpu和systemctl

redhat 7 系列发布已经有一段时间了,最近抽时间看了下官方文档,对比了一些命令,对于其中比较关注的进行了记录,本篇主要是列出来了服务的管理改进,引进了systemctl管理和一个非常方便看cpu信息的命令lscpu
系统版本

[root@em12cdb ~]# uname -a
Linux em12cdb 3.8.13-55.1.6.el7uek.x86_64 #2 SMP Wed Feb 11 14:18:22 PST 2015 x86_64 x86_64 x86_64 GNU/Linux
[root@em12cdb ~]# more /etc/oracle-release
Oracle Linux Server release 7.1

查看cpu信息

[root@em12cdb ~]# lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                2
On-line CPU(s) list:   0,1
Thread(s) per core:    1
Core(s) per socket:    2
Socket(s):             1
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 60
Model name:            Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz
Stepping:              3
CPU MHz:               3997.739
BogoMIPS:              7995.47
Hypervisor vendor:     VMware
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              8192K
NUMA node0 CPU(s):     0,1

systemctl命令管理服务和开机启动
以前主要是通过/etc/init.d/或者service命令管理服务,通过chkconfig管理是否开机启动

--查看某个服务状态
[root@em12cdb ~]# systemctl status crond.service
crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled)
   Active: active (running) since Mon 2015-07-27 11:59:16 CST; 1 months 7 days ago
 Main PID: 1245 (crond)
   CGroup: /system.slice/crond.service
           â””â”€1245 /usr/sbin/crond -n
Jul 27 11:59:16 em12cdb systemd[1]: Started Command Scheduler.
Jul 27 11:59:16 em12cdb crond[1245]: (CRON) INFO (RANDOM_DELAY will be scaled with factor 33% if used.)
Jul 27 11:59:17 em12cdb crond[1245]: (CRON) INFO (running with inotify support)
--停止某个服务
[root@em12cdb ~]# systemctl stop crond.service
[root@em12cdb ~]# systemctl status crond.service
crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled)
   Active: inactive (dead) since Thu 2015-09-03 00:27:55 CST; 2s ago
 Main PID: 1245 (code=exited, status=0/SUCCESS)
Jul 27 11:59:16 em12cdb systemd[1]: Started Command Scheduler.
Jul 27 11:59:16 em12cdb crond[1245]: (CRON) INFO (RANDOM_DELAY will be scaled with factor 33% if used.)
Jul 27 11:59:17 em12cdb crond[1245]: (CRON) INFO (running with inotify support)
Sep 03 00:27:55 em12cdb systemd[1]: Stopping Command Scheduler...
Sep 03 00:27:55 em12cdb systemd[1]: Stopped Command Scheduler.
--启动某个服务
[root@em12cdb ~]# systemctl start crond.service
[root@em12cdb ~]# systemctl status crond.service
crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled)
   Active: active (running) since Thu 2015-09-03 00:28:08 CST; 1s ago
 Main PID: 7294 (crond)
   CGroup: /system.slice/crond.service
           â””â”€7294 /usr/sbin/crond -n
Sep 03 00:28:08 em12cdb systemd[1]: Started Command Scheduler.
Sep 03 00:28:08 em12cdb crond[7294]: (CRON) INFO (RANDOM_DELAY will be scaled with factor 56% if used.)
Sep 03 00:28:09 em12cdb crond[7294]: (CRON) INFO (running with inotify support)
Sep 03 00:28:09 em12cdb crond[7294]: (CRON) INFO (@reboot jobs will be run at computer's startup.)
--重启某个服务
[root@em12cdb ~]# systemctl restart crond.service
[root@em12cdb ~]# systemctl status crond.service
crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled)
   Active: active (running) since Thu 2015-09-03 00:28:24 CST; 2s ago
 Main PID: 7323 (crond)
   CGroup: /system.slice/crond.service
           â””â”€7323 /usr/sbin/crond -n
Sep 03 00:28:24 em12cdb systemd[1]: Starting Command Scheduler...
Sep 03 00:28:24 em12cdb systemd[1]: Started Command Scheduler.
Sep 03 00:28:24 em12cdb crond[7323]: (CRON) INFO (RANDOM_DELAY will be scaled with factor 61% if used.)
Sep 03 00:28:24 em12cdb crond[7323]: (CRON) INFO (running with inotify support)
Sep 03 00:28:24 em12cdb crond[7323]: (CRON) INFO (@reboot jobs will be run at computer's startup.)
--检查某个服务是否开机启动
[root@em12cdb ~]# systemctl is-enabled crond.service
enabled
--禁止某个服务开机启动
[root@em12cdb ~]# systemctl disable crond.service
rm '/etc/systemd/system/multi-user.target.wants/crond.service'
[root@em12cdb ~]# systemctl is-enabled crond.service
disabled
--查看所有服务开机启动情况(这里使用了grep便于说明)
[root@em12cdb ~]# systemctl list-unit-files --type service|grep cron
crond.service                               disabled
[root@em12cdb ~]# systemctl enable crond.service
ln -s '/usr/lib/systemd/system/crond.service' '/etc/systemd/system/multi-user.target.wants/crond.service'
[root@em12cdb ~]# systemctl list-unit-files --type service|grep cron
crond.service                               enabled

systemctl chkconfig 对比

systemctl命令修改启动模式
以前版本中,直接通过vi修改/etc/inittab文件

--多用户图形界面
[root@em12cdb ~]# systemctl get-default
graphical.target
--多用户字符界面
[root@em12cdb ~]# systemctl set-default multi-user.target
rm '/etc/systemd/system/default.target'
ln -s '/usr/lib/systemd/system/multi-user.target' '/etc/systemd/system/default.target'
[root@em12cdb ~]# systemctl get-default
multi-user.target

systemd runlevel

[MySQL异常恢复]mysql drop table 数据恢复

对于MySQL数据库的innodb引擎的数据库中,由于误操作删除表,或者由于sqldump自动生成语句含drop table create table语句导致数据丢失,在没有覆盖的情况下,可以实现完美恢复
创建测试表

mysql> CREATE TABLE recover.`t_drop` (
    ->   `messageId` varchar(30) NOT NULL,
    ->   `msgContent` varchar(1000) default NULL,
    ->   `scheduleDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   `deliverState` int(1) default NULL,
    ->   PRIMARY KEY  (`messageId`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into recover.t_drop select messageId,msgContent,scheduleDate,deliverState from sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0
mysql> select * from recover.`t_drop`;
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
| messageId          | msgContent                                                                       | scheduleDate        | deliverState |
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
| 10235259536125650  | 尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
| 10235353811295807  | 尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
| 102354211240398235 | 尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
| 102354554052884567 | 尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
| 132213454294519126 | 尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
| 82329022242584577  | 尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
| 82329022242584581  | 尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
| 8233400415607376   | 尊敬的用户您好：您的手机验证码为338470如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
| 82334502212106951  | 尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
| 82339012756833423  | 尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
| 8234322198577796   | 尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 |
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
11 rows in set (0.00 sec)
mysql> checksum table t_drop;
+-----------------+-----------+
| Table           | Checksum  |
+-----------------+-----------+
| recover.t_drop  | 920719058 |
+-----------------+-----------+
1 row in set (0.00 sec)

删除测试表,后续用来恢复

mysql> drop table recover.t_drop;
Query OK, 0 rows affected (0.00 sec)

解析ibdata文件

[root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440855928 Sat Aug 29 21:45:28 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440855928 Sat Aug 29 21:45:28 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Worker(1): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(0): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(4): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(3): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(2): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(7): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(5): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(6): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
All workers finished in 1 sec

恢复mysql字典

[root@web103 mysql_recovery]# ./recover_dictionary.sh
Generating dictionary tables dumps... OK
Creating test database ... OK
Creating dictionary tables in database test:
SYS_TABLES ... OK
SYS_COLUMNS ... OK
SYS_INDEXES ... OK
SYS_FIELDS ... OK
All OK
Loading dictionary tables data:
SYS_TABLES ... 162 recs OK
SYS_COLUMNS ... 1247 recs OK
SYS_INDEXES ... 216 recs OK
SYS_FIELDS ... 411 recs OK
All OK
[root@web103 mysql_recovery]# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10162
Server version: 5.0.95 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from SYS_TABLES WHERE name like 'recover%';
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
| NAME                    | ID  | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
| recover/#sql-64a9_1f6d  | 120 |      8 |    1 |      0 |       0 |              |     0 |
| recover/#sql2-64a9-1f6d | 115 |      8 |    1 |      0 |       0 |              |     0 |
| recover/t_delete        |  74 |      2 |    1 |      0 |       0 |              |     0 |
| recover/t_delete1       |  84 |      2 |    1 |      0 |       0 |              |     0 |
| recover/t_drop          | 125 |      4 |    1 |      0 |       0 |              |     0 |
| recover/t_truncate      | 120 |      8 |    1 |      0 |       0 |              |     0 |
| recover/t_xifenfei      |  75 |      2 |    1 |      0 |       0 |              |     0 |
| recover/zx_users        |  89 |     85 |    1 |      0 |       0 |              |     0 |
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
8 rows in set (0.00 sec)
mysql> select * from SYS_INDEXES WHERE TABLE_ID=125;
+----------+-----+---------+----------+------+-------+------------+
| TABLE_ID | ID  | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO    |
+----------+-----+---------+----------+------+-------+------------+
|      125 | 142 | PRIMARY |        1 |    3 |     0 | 4294967295 |
+----------+-----+---------+----------+------+-------+------------+
1 row in set (0.00 sec)

恢复被删除表记录

[root@web103 mysql_recovery]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000142.page  -t dictionary/t_drop.sql >dumps/default/t_drop 2>2.sql
[root@web103 mysql_recovery]# more dumps/default/t_drop
-- Page id: 9860, Format: COMPACT, Records list: Valid, Expected records: (11 11)
00000099F9F2    80000026800110  t_drop  "10235259536125650"     "尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800129  t_drop  "10235353811295807"     "尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800142  t_drop  "102354211240398235"    "尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    8000002680015C  t_drop  "102354554052884567"    "尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800176  t_drop  "132213454294519126"    "尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800190  t_drop  "82329022242584577"     "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001A9  t_drop  "82329022242584581"     "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001C2  t_drop  "8233400415607376"      "尊敬的用户您好：您的手机验证码为338470如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001DA  t_drop  "82334502212106951"     "尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001F3  t_drop  "82339012756833423"     "尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    8000002680020C  t_drop  "8234322198577796"      "尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
-- Page id: 9860, Found records: 11, Lost records: NO, Leaf page: YES

恢复数据入库

mysql> source dictionary/t_drop.sql
Query OK, 0 rows affected (0.00 sec)
mysql> source 2.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 11 rows affected (0.00 sec)
Records: 11  Deleted: 0  Skipped: 0  Warnings: 0
mysql> checksum table t_drop;
+-----------------+-----------+
| Table           | Checksum  |
+-----------------+-----------+
| recover.t_drop  | 920719058 |
+-----------------+-----------+
1 row in set (0.00 sec)

至此实现删除数据完美恢复

[MySQL异常恢复]无主键情况下innodb数据恢复

在mysql的innodb引擎的数据库异常恢复中,一般都要求有主键或者唯一index,其实这个不是必须的,当没有index信息之时,可以在整个表级别的index_id进行恢复
创建模拟表—无主键

mysql>  CREATE TABLE `t1` (
    ->   `messageId` varchar(30) character set utf8 NOT NULL,
    ->   `tokenId` varchar(20) character set utf8 NOT NULL,
    ->   `mobile` varchar(14) character set utf8 default NULL,
    ->   `msgFormat` int(1) NOT NULL,
    ->   `msgContent` varchar(1000) character set utf8 default NULL,
    ->   `scheduleDate` timestamp NOT NULL default '0000-00-00 00:00:00',
    ->   `deliverState` int(1) default NULL,
    ->   `deliverdTime` timestamp NOT NULL default '0000-00-00 00:00:00'
    -> ) ENGINE=INnodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 select * from sms_service.sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0
…………
mysql> insert into t1 select * from t1;
Query OK, 81664 rows affected (2.86 sec)
Records: 81664  Duplicates: 0  Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 163328 rows affected (2.74 sec)
Records: 163328  Duplicates: 0  Warnings: 0
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   326656 |
+----------+
1 row in set (0.15 sec)

解析innodb文件

[root@web103 mysql_recovery]# rm -rf pages-ibdata1/
[root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
Opening file: /var/lib/mysql/ibdata1
File information:
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
(regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819465 Sat Aug 29 11:37:45 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)
Size to process:                 236978176 (226.000 MiB)
All workers finished in 0 sec

恢复数据字典

[root@web103 mysql_recovery]# ./recover_dictionary.sh
Generating dictionary tables dumps... OK
Creating test database ... OK
Creating dictionary tables in database test:
SYS_TABLES ... OK
SYS_COLUMNS ... OK
SYS_INDEXES ... OK
SYS_FIELDS ... OK
All OK
Loading dictionary tables data:
SYS_TABLES ... 48 recs OK
SYS_COLUMNS ... 397 recs OK
SYS_INDEXES ... 67 recs OK
SYS_FIELDS ... 89 recs OK
All OK

分析数据字典,找出来index_id
这里需要注意对于没有主键的表恢复,我们对应的类型是GEN_CLUST_INDEX

mysql> select * from SYS_TABLES where name='test/t1';
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
| NAME                                   | ID  | N_COLS      | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
| test/t1                                | 100 |           8 |    1 |      0 |       0 |              |     0 |
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
40 rows in set (0.00 sec)
mysql> SELECT * FROM SYS_INDEXES where table_id=100;
+----------+-----+------------------------------+----------+------+-------+------------+
| TABLE_ID | ID  | NAME                         | N_FIELDS | TYPE | SPACE | PAGE_NO    |
+----------+-----+------------------------------+----------+------+-------+------------+
|      100 | 119 | GEN_CLUST_INDEX              |        0 |    1 |     0 |       2951 |
+----------+-----+------------------------------+----------+------+-------+------------+
67 rows in set (0.00 sec)

恢复数据

root@web103 mysql_recovery]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000119.page  -t dictionary/t1.sql >/tmp/2.txt 2>2.sql
[root@web103 mysql_recovery]# more /tmp/2.txt
-- Page id: 10848, Format: COMPACT, Records list: Valid, Expected records: (73 73)
00000002141B    0000009924F2    80000027133548  t1      "82334502212106951"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141C    0000009924F2    80000027133558  t1      "82339012756833423"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141D    0000009924F2    80000027133568  t1      "8234322198577796"      "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141E    0000009924F2    80000027133578  t1      "10235259536125650"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141F    0000009924F2    80000027133588  t1      "10235353811295807"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021420    0000009924F2    80000027133598  t1      "102354211240398235"    "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021421    0000009924F2    800000271335A8  t1      "102354554052884567"    "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021422    0000009924F2    800000271335B8  t1      "132213454294519126"    "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021423    0000009924F2    800000271335C8  t1      "82329022242584577"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "2015-08-26 22:02:17"
…………
[root@web103 mysql_recovery]# cat /tmp/2.txt|grep  -v "Page id:"|wc -l
380731

因为没有主键,使得恢复出来记录可能有一些重复,整体而言,可以较为完美的恢复数据

作者：admin