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

发表于 2015 年 8 月 29 日 由 惜分飞

在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

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

发表在 MySQL恢复 | 标签为 , , | 评论关闭

ORACLE异常恢复后awr异常处理

发表于 2015 年 8 月 29 日 由 惜分飞

有一个通过非常规方法恢复过的客户数据库出现问题awr无法收集统计信息(几个月前非常规方法恢复的库,因为未重建库),不太方便跟踪数据库性能,让其帮忙分析跟踪问题.
人工收集统计信息报错RA-00001: 违反唯一约束条件 (SYS.WRM$_SNAPSHOT_PK)

SQL> execute dbms_workload_repository.create_snapshot();
BEGIN dbms_workload_repository.create_snapshot(); END;

*
第 1 行出现错误:
ORA-13509: 更新 AWR 表时出错
ORA-00001: 违反唯一约束条件 (ORA-00001: 违反唯一约束条件 (SYS.WRM$_SNAPSHOT_PK)
.)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: 在 line 1

通过分析trace文件问题如下

Trace file D:\APP\ADMINISTRATOR\diag\rdbms\rac\rac2\trace\rac2_m000_1628.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option
Windows NT Version V6.1 Service Pack 1 
CPU                 : 32 - type 8664, 32 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:83326M/131035M, Ph+PgF:214386M/262068M 
Instance name: rac2
Redo thread mounted by this instance: 2
Oracle process number: 61
Windows thread id: 1628, image: ORACLE.EXE (M000)


*** 2015-08-28 11:39:51.967
*** SESSION ID:(2062.93) 2015-08-28 11:39:51.968
*** CLIENT ID:() 2015-08-28 11:39:51.968
*** SERVICE NAME:(SYS$BACKGROUND) 2015-08-28 11:39:51.968
*** MODULE NAME:(MMON_SLAVE) 2015-08-28 11:39:51.968
*** ACTION NAME:(Auto-Flush Slave Action) 2015-08-28 11:39:51.968
 
*** KEWROCISTMTEXEC - encountered error: (ORA-00001: 违反唯一约束条件 (SYS.WRM$_SNAPSHOT_PK)
)  
  *** SQLSTR: total-len=342, dump-len=240, 
      STR={insert into   WRM$_SNAPSHOT  (snap_id, dbid, instance_number, startup_time,begin_interval_time, 
end_interval_time, snap_level,    status, error_count, bl_moved, snap_flag, snap_timezone)  
values   (:snap_id, :dbid, :instance_number, :sta}
*** KEWRAFS: Error=13509 encountered by Auto Flush Slave.

这里可以明确的定位到,由于insert WRM$_SNAPSHOT表之时出现主键冲突导致无法收集统计信息.因为awr的数据都是历史数据,可以全部清理,因此尝试删除掉awr相关数据看是否能够解决问题

对收集快照做10046 跟踪发现

SQL> oradebug setmypid
已处理的语句
SQL> alter session set events '10046 trace name context forever, level 12';

会话已更改。

SQL> oradebug tracefile_name
D:\APP\ADMINISTRATOR\diag\rdbms\rac\rac2\trace\rac2_ora_5944.trc
SQL> execute dbms_workload_repository.create_snapshot();
BEGIN dbms_workload_repository.create_snapshot(); END;

*
第 1 行出现错误:
ORA-13509: 更新 AWR 表时出错
ORA-00001: 违反唯一约束条件 (ORA-00001: 违反唯一约束条件 (SYS.WRM$_SNAPSHOT_PK)
.)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: 在 line 1


--trace文件分析
PARSING IN CURSOR #1362260992  lid=0 tim=22781405124 hv=438921370 ad='148fd90590' sqlid='15rbgh4d2ku4u'
insert into WRM$_SNAPSHOT(snap_id, dbid, instance_number, startup_time,begin_interval_time, end_interval_time,
snap_level,status, error_count, bl_moved,snap_flag, snap_timezone)values(:snap_id, :dbid, :instance_number, 
:startup_time, :begin_interval_time, :end_interval_time, :snap_level,    :status, 0, 0, :bind1, :bind2)
END OF STMT
PARSE #1362260992:c=0,e=474,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=22781405122
BINDS #1362260992:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=208 off=0
  kxsbbbfp=513a6bf8  bln=22  avl=03  flg=05
  value=9277
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=513a6c10  bln=22  avl=06  flg=01
  value=2429481020
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=513a6c28  bln=22  avl=02  flg=01
  value=2
 Bind#3
  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=09 pre=00
  oacflg=00 fl2=8000000 frm=00 csi=00 siz=0 off=72
  kxsbbbfp=513a6c40  bln=11  avl=07  flg=01
  value=28-8月 -15 10.06.53 上午
 Bind#4
  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=09 pre=00
  oacflg=00 fl2=8000000 frm=00 csi=00 siz=0 off=88
  kxsbbbfp=513a6c50  bln=11  avl=07  flg=01
  value=28-8月 -15 10.06.53 上午
 Bind#5
  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=09 pre=00
  oacflg=00 fl2=8000000 frm=00 csi=00 siz=0 off=104
  kxsbbbfp=513a6c60  bln=11  avl=11  flg=01
  value=28-8月 -15 04.11.40.017000000 下午
 Bind#6
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=120
  kxsbbbfp=513a6c70  bln=22  avl=02  flg=01
  value=1
 Bind#7
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=144
  kxsbbbfp=513a6c88  bln=22  avl=02  flg=01
  value=1
 Bind#8
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=168
  kxsbbbfp=513a6ca0  bln=22  avl=02  flg=01
  value=1
 Bind#9
  oacdty=183 mxl=11(11) mxlc=00 mal=00 scl=09 pre=09
  oacflg=01 fl2=8000000 frm=00 csi=00 siz=0 off=192
  kxsbbbfp=513a6cb8  bln=11  avl=11  flg=01
  value=Unhandled datatype (183) found in kxsbndinf

这里可以明确定位到,awr在收集信息的时候就是插入的值和库中本身存在的记录冲突,从而出现此类问题

清理awr数据

SQL> select max(snap_id),min(snap_id) from WRM$_SNAPSHOT;

MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
        9277         9081

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(9081,9277);

PL/SQL 过程已成功完成。

SQL>
SQL> select max(snap_id),min(snap_id) from WRM$_SNAPSHOT;

MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
        9277         9277

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(9080,9278);

PL/SQL 过程已成功完成。

SQL> select max(snap_id),min(snap_id) from WRM$_SNAPSHOT;

MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
        9277         9277

SQL> delete from  WRM$_SNAPSHOT where snap_id=9277;
delete from  WRM$_SNAPSHOT where snap_id=9277
             *
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [13011], [6653], [8456911], [2], [8456911], [3],
[], [], [], [], [], []


SQL> delete /*+ RULE */ from  WRM$_SNAPSHOT where snap_id=9277;

已删除0行。

这里有几分诡异,snap_id=9277的记录无法清理,而且正常删除报ORA-00600[13011].根据经验,出现该问题,很可能是由于表和index的记录问题

尝试rebuild index

SQL> analyze table WRM$_SNAPSHOT validate structure cascade;
analyze table WRM$_SNAPSHOT validate structure cascade
*
第 1 行出现错误:
ORA-01499: 表/索引交叉引用失败 - 请参阅跟踪文件


SQL> select index_name from dba_indexes where table_name='WRM$_SNAPSHOT';

INDEX_NAME
------------------------------
WRM$_SNAPSHOT_PK

SQL> alter index WRM$_SNAPSHOT_PK rebuild;

索引已更改。

SQL> select /*+ full(t) */ max(snap_id),min(snap_id) from WRM$_SNAPSHOT t;

MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------


SQL> select max(snap_id),min(snap_id) from WRM$_SNAPSHOT;

MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
        9277         9277

这里很明确的定位了,由于表和index的记录不一致,而且通过rebuild,发现index依旧有问题

重建index

SQL> set linesize 180
SQL> set pages 999
SQL> set long 90000
SQL> select dbms_metadata.get_ddl('INDEX','WRM$_SNAPSHOT_PK','SYS') from dual;

DBMS_METADATA.GET_DDL('INDEX','WRM$_SNAPSHOT_PK','SYS')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SYS"."WRM$_SNAPSHOT_PK" ON "SYS"."WRM$_SNAPSHOT" ("DBID", "SNAP_ID", "INSTANCE_NUMBER")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"


SQL> DROP INDEX "SYS"."WRM$_SNAPSHOT_PK" ;
DROP INDEX "SYS"."WRM$_SNAPSHOT_PK"
                 *
第 1 行出现错误:
ORA-02429: 无法删除用于强制唯一/主键的索引


SQL> alter table "SYS"."WRM$_SNAPSHOT" drop  constraint "SYS"."WRM$_SNAPSHOT_PK";
alter table "SYS"."WRM$_SNAPSHOT" drop  constraint "SYS"."WRM$_SNAPSHOT_PK"
                                                        *
第 1 行出现错误:
ORA-01735: 无效的 ALTER TABLE 选项


SQL> alter table "WRM$_SNAPSHOT" drop  constraint "WRM$_SNAPSHOT_PK";

表已更改。

SQL>alter table "WRM$_SNAPSHOT" add constraint "WRM$_SNAPSHOT_PK" primary key("DBID", "SNAP_ID", "INSTANCE_NUMBER");

表已更改。

再次尝试做快照

SQL> execute dbms_workload_repository.create_snapshot();
BEGIN dbms_workload_repository.create_snapshot(); END;

*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kewrose_1], [600], [ORA-00600: 内部错误代码, 参数: [6002], [6], [104], 
[4], [0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: 在 line 1

悲剧再次发生,收集快照之时遭遇悲催的ORA-00600[kewrose_1]/ORA-600[6002]的错误.范围awr的以前数据都不要了,也就采用最极端的处理方法,定位到表,然后处理之

继续10046跟踪

PARSING IN CURSOR #1495840456 tim=24328721585 hv=4050667988 ad='146f9948f8'sqlid='84qubbrsr0kfn'
insert into wrh$_latch(snap_id, dbid, instance_number, latch_hash, level#, gets, misses, sleeps,
 immediate_gets,immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time)  
select :snap_id, :dbid, :instance_number, hash, level#, gets,    misses, sleeps, 
immediate_gets, immediate_misses, spin_gets,    sleep1, sleep2, sleep3, 
sleep4, wait_time  from    v$latch  order by    hash
END OF STMT
PARSE #1495840456:c=0,e=376,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=24328721584
BINDS #1495840456:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=72 off=0
  kxsbbbfp=60471350  bln=22  avl=03  flg=05
  value=9280
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=60471368  bln=22  avl=06  flg=01
  value=2429481020
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=60471380  bln=22  avl=02  flg=01
  value=2
ORA-00600: 内部错误代码, 参数: [6002], [6], [104], [4], [0], [], [], [], [], [], [], []

通过这里可以定位到问题是发生在wrh$_latch表的insert操作之上

分析并truncate table

SQL> SELECT COUNT(*) FROM wrh$_latch;

COUNT(*)
--------
     0

SQL> truncate table wrh$_latch;

表被截断

再次收集快照信息

SQL> execute dbms_workload_repository.create_snapshot();

PL/SQL 过程已成功完成。

SQL> @?/rdbms/admin/awrrpt.sql
--工作正常

经过一些列处理,终于让awr能够正常工作了,特别是在做过异常恢复之后,awr数据可能有各种问题导致工作不正常,可以考虑重建awr,也可以考虑类似我这样彻底清理awr数据,然后放手处理.当然对于使用非常规方法恢复的Oracle数据库,在条件允许的情况下,建议逻辑方式重建库.因为有数据字典不一致,有逻辑坏块,有表和index不一致等问题,在后续的使用中逐渐被显露出来,从而导致很多麻烦,重建库彻彻底底解决问题.

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

system01.dbf文件被offline,导致数据库报ORA-01245 ORA-01110故障恢复

发表于 2015 年 8 月 28 日 由 惜分飞

对于这样的情况,可以使用自研的Oracle Recovery Tools工具快速修改scn,然后online数据文件即可
有朋友找到我,说数据库做recover报ORA-01245和ORA-01110错误,无法继续恢复,请求支持

SQL> recover database using backup controlfile until cancel;
…………

第 1 行出现错误:
ORA-01245: RESETLOGS 完成时脱机文件 1 将丢失
ORA-01110: 数据文件 1: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\SYSTEM01.DBF'

通过Oracle Database Recovery Check检查数据库情况,发现datafile 1处于offline状态
oracle_recovery_check

Wed Aug 26 23:11:00 2015
alter database datafile 1 offline drop
Completed: alter database datafile 1 offline drop

从这里基本上可以知道为什么出现ORA-01245错误了,由于system表空间中文件被offline导致.

redo信息
oracle_recovery_check_redo

Mon Aug 24 22:38:35 2015
alter database clear unarchived logfile group 2
Clearing online log 2 of thread 1 sequence number 5705
Completed: alter database clear unarchived logfile group 2
Wed Aug 26 23:13:23 2015
alter database clear logfile group 3
Clearing online log 3 of thread 1 sequence number 5706
Completed: alter database clear logfile group 3

除当前redo之外,其他redo被clear

尝试恢复

SQL> alter database datafile 1 online;

数据库已更改。

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-01610: 使用 BACKUP CONTROLFILE 选项的恢复必须已完成


SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570

5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO03.LOG
ORA-00310: 归档日志包含序列 5706; 要求序列 5705
ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO03.LOG'


SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570

5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO02.LOG
ORA-00339: 归档日志未包含任何重做
ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO02.LOG'


SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570

5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO01.LOG
ORA-00310: 归档日志包含序列 5707; 要求序列 5705
ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO01.LOG'

数据库做恢复需要seq 5705的redo,但是redo已经被clear,导致现在数据库常规手段无法恢复,只用使用隐含参数屏蔽数据库前滚(一致性检查)

再次尝试打开数据库

ORACLE 例程已经启动。

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             486540712 bytes
Database Buffers          285212672 bytes
Redo Buffers                5259264 bytes
数据库装载完毕。
SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570

5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
介质恢复已取消。
SQL> alter database open resetlogs;

数据库已更改。

在数据库恢复中,请不要对system表空间数据文件进行offline操作,如果对此类文件进行offline操作,讲在数据库恢复过程中出现ORA-01245和ORA-01110错误,而且文件还会出现SYSOFF状态

发表在 Oracle备份恢复 | 标签为 , , , , | 评论关闭