Oracle跳过归档的恢复，坏运气是跳过归档时遇到600-2608，好运气是有个完整的备份
周末朋友的线上数据库出问题，给我电话，由于他们的数据库管理很乱再加上DBA离职，生产已经停止6小时了。我上去看了下，基本上弄清了问题，下面就开始恢复，首先朋友告诉我没有完整的‘备份’(他所指的备份是冷备份......因为冷备份文件件内是空的)。虽然我知道在生产的复杂环境想跳过归档基本上是不可能的，但是还是尝试了一下，结果果不其然遇到ORA-00600: internal error code, arguments: [2608]。在一筹莫展的时候，我看了下Crontab，居然发现了RMAN定时备份，进入了RMAN后发现了完整备份.........
随后就是漫长的恢复等待，没有技术含量。
得到这个结果我算是运气坏的，在恢复方面我总是运气很坏，所以我需要很完善的RMAN备份，或者其他HA手段来保持我的数据安全性。
回来后又模拟了一变居然还是那个错误，看来我不是一般的倒霉啊.....................
下边记录
周末朋友的线上数据库出问题，给我电话，由于他们的数据库管理很乱再加上DBA离职，生产已经停止6小时了。我上去看了下，基本上弄清了问题，下面就开始恢复，首先朋友告诉我没有完整的‘备份’(他所指的备份是冷备份......因为冷备份文件件内是空的)。虽然我知道在生产的复杂环境想跳过归档基本上是不可能的，但是还是尝试了一下，结果果不其然遇到ORA-00600: internal error code, arguments: [2608]。在一筹莫展的时候，我看了下Crontab，居然发现了RMAN定时备份，进入了RMAN后发现了完整备份.........
随后就是漫长的恢复等待，没有技术含量。
得到这个结果我算是运气坏的，在恢复方面我总是运气很坏，所以我需要很完善的RMAN备份，或者其他HA手段来保持我的数据安全性。
回来后又模拟了一变居然还是那个错误，看来我不是一般的倒霉啊.....................
下边记录了我的测试过程。
SQL> create tablespace testrecover datafile '/u02/oradata/testrecover_01.dbf' size 10M extent management local uniform size 1M segment space management auto;
Tablespace created.
SQL> create table t1 tablespace testrecover as select * from dba_objects;
Table created.
SQL> create table t2 tablespace testrecover as select * from dba_users;
Table created.
SQL> create table t3 tablespace testrecover as select * from dba_users;
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
50681
SQL> select count(*) from t2;
COUNT(*)
----------
28
SQL> select count(*) from t3;
COUNT(*)
----------
28
SQL> select file#||' '||name||' '||bytes from v$datafile;
FILE#||''||NAME||''||BYTES
--------------------------------------------------------------------------------
..................
9 /u02/oradata/testrecover_01.dbf 10485760
9 rows selected.
SQL> delete from t2 where rownum<2;
1 row deleted.
SQL> delete from t3 where rownum<2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from t2;
COUNT(*)
----------
27
SQL> select count(*) from t3;
COUNT(*)
----------
27
SQL> alter system switch logfile;
System altered.
SQL> delete from t1 where rownum<10001;
10000 rows deleted.
SQL> delete from t2 where rownum<2;
1 row deleted.
SQL> delete from t3 where rownum<2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
COUNT(*)
----------
40681
SQL> select count(*) from t2;
COUNT(*)
----------
26
SQL> select count(*) from t3;
COUNT(*)
----------
26
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/oradata/archivelog
Oldest online log sequence 241
Next log sequence to archive 243
Current log sequence 243
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/oradata/archivelog
Oldest online log sequence 242
Next log sequence to archive 244
Current log sequence 244
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
node1*orcl-/u02/oradata/archivelog >l
..........................
-rw-r----- 1 oracle dba 51228672 Mar 1 03:00 1_239_722505196.dbf
-rw-r----- 1 oracle dba 36395520 Mar 1 20:11 1_240_722505196.dbf
-rw-r----- 1 oracle dba 6424064 Mar 1 20:23 1_241_722505196.dbf
-rw-r----- 1 oracle dba 269312 Mar 1 20:33 1_242_722505196.dbf
-rw-r----- 1 oracle dba 3728896 Mar 1 20:34 1_243_722505196.dbf
-rw-r----- 1 oracle dba 7680 Mar 1 20:34 1_244_722505196.dbf
-rw-r----- 1 oracle dba 3072 Mar 1 20:34 1_245_722505196.dbf
-rw-r----- 1 oracle dba 1024 Mar 1 20:34 1_246_722505196.dbf
-rw-r----- 1 oracle dba 1024 Mar 1 20:35 1_247_722505196.dbf
node1*orcl-/u02/oradata/archivelog >mv 1_245_722505196.dbf 1_245_722505196_backup.dbf
node1*orcl-/home/oracle >cd /u02/oradata/
node1*orcl-/u02/oradata >l
total 699168
drwxr-xr-x 3 oracle dba 4096 Mar 1 20:22 .
drwxr-xr-x 4 oracle dba 4096 Jul 29 2010 ..
drwxr-xr-x 2 oracle dba 4096 Mar 1 20:35 archivelog
-rw-r----- 1 oracle dba 52436992 Mar 1 20:35 demo01.dbf
-rw-r--r-- 1 oracle dba 8192 Nov 22 14:56 ETMCDB02_7_21.dd
-rw-r--r-- 1 oracle dba 8192 Nov 17 18:46 ETMCDB02_7_21.dd.bak
-rw-r----- 1 oracle dba 5251072 Nov 17 19:51 ETMCDB02.dbf
-rw-r----- 1 oracle dba 5251072 Nov 17 19:39 ETMCDB02.dbf.bak
-rw-r--r-- 1 oracle dba 8192 Nov 17 18:41 ETMCDB_7_21.dd
-rw-r----- 1 oracle dba 536879104 Mar 1 20:35 ETMCDB.dbf
-rw-r----- 1 oracle dba 104865792 Mar 1 20:35 example01.dbf
-rw-r----- 1 oracle dba 10493952 Mar 1 20:35 testrecover_01.dbf
node1*orcl-/u02/oradata >rm testrecover_01.dbf
node1*orcl-/home/oracle >sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 1 20:36:18 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 566231040 bytes
Fixed Size 1220604 bytes
Variable Size 134217732 bytes
Database Buffers 427819008 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/u02/oradata/testrecover_01.dbf'
SQL> alter database create datafile '/u02/oradata/testrecover_01.dbf' as '/u02/oradata/testrecover_01.dbf';
Database altered.
SQL> recover datafile 9;
ORA-00279: change 9105893 generated at 03/01/2011 20:22:28 needed for thread 1
ORA-00289: suggestion : /u02/oradata/archivelog/1_241_722505196.dbf
ORA-00280: change 9105893 for thread 1 is in sequence #241
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 9106048 generated at 03/01/2011 20:23:20 needed for thread 1
ORA-00289: suggestion : /u02/oradata/archivelog/1_242_722505196.dbf
ORA-00280: change 9106048 for thread 1 is in sequence #242
ORA-00278: log file '/u02/oradata/archivelog/1_241_722505196.dbf' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 9106453 generated at 03/01/2011 20:33:24 needed for thread 1
ORA-00289: suggestion : /u02/oradata/archivelog/1_243_722505196.dbf
ORA-00280: change 9106453 for thread 1 is in sequence #243
ORA-00278: log file '/u02/oradata/archivelog/1_242_722505196.dbf' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 9106606 generated at 03/01/2011 20:34:19 needed for thread 1
ORA-00289: suggestion : /u02/oradata/archivelog/1_244_722505196.dbf
ORA-00280: change 9106606 for thread 1 is in sequence #244
ORA-00278: log file '/u02/oradata/archivelog/1_243_722505196.dbf' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 9106612 generated at 03/01/2011 20:34:34 needed for thread 1
ORA-00289: suggestion : /u02/oradata/archivelog/1_245_722505196.dbf
ORA-00280: change 9106612 for thread 1 is in sequence #245
ORA-00278: log file '/u02/oradata/archivelog/1_244_722505196.dbf' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/u02/oradata/archivelog/1_245_722505196.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
node1*orcl-/u01/app/oracle/product/10.2.0/db_1/bin >bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Tue Mar 1 20:32:59 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 9
FILE# 9
BBED> set block 1
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x008af4b4
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2c62c05a
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x000000f5
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0000
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> set offset 500
OFFSET 500
BBED> dump
File: /u02/oradata/testrecover_01.dbf (9)
Block: 1 Offsets: 500 to 1011 Dba:0x02400001
------------------------------------------------------------------------
f5000000 02000000 00000000 02000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 0a000a00 0a000100 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> modify /x f6000000
BBED-00209: invalid number (f6000000)
BBED> modify /x f6
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /u02/oradata/testrecover_01.dbf (9)
Block: 1 Offsets: 500 to 1011 Dba:0x02400001
------------------------------------------------------------------------
f6000000 02000000 00000000 02000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 0a000a00 0a000100 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set offset 504
OFFSET 504
BBED> dump
File: /u02/oradata/testrecover_01.dbf (9)
Block: 1 Offsets: 504 to 1015 Dba:0x02400001
------------------------------------------------------------------------
02000000 00000000 02000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 0a000a00 0a000100 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> modify /x 01
File: /u02/oradata/testrecover_01.dbf (9)
Block: 1 Offsets: 504 to 1015 Dba:0x02400001
------------------------------------------------------------------------
01000000 00000000 02000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 0a000a00 0a000100 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 9, Block 1:
current = 0x4378, required = 0x4378
BBED> exit
node1*orcl-/u01/app/oracle/product/10.2.0/db_1/bin >bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Tue Mar 1 20:45:22 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 9
FILE# 9
BBED> set block 1
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x008af4b4
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2c62c05a
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x000000f6
ub4 kcrbabno @504 0x00000001
ub2 kcrbabof @508 0x0000
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED>
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 566231040 bytes
Fixed Size 1220604 bytes
Variable Size 134217732 bytes
Database Buffers 427819008 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/u02/oradata/testrecover_01.dbf'
SQL> recover datafile 9;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [2608], [1], [0], [9106612], [0],
[9106614], [], []
--EOF--
