sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 产品DBA > Oracle BBED >

【案例】Oracle bbed修复checkpoint恢复启动数据库

时间:2016-12-14 16:06   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库迁移后发现存在offline的datafile,需要online数据文件则需要修复检查点,通过bbed修复checkpoint恢复启动数据库。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 存在datafile offline,如何进行异机恢复?

在最近一个项目数据库的迁移中,检查发生有个datafile状态是offline的,而且该datafile是2012年3月份offline的,如果要进行恢复,那么需要从2年多的archive,事实上,对于offline的datafile,我们完全可以手工去修改该文件的检查点信息,然后正常open数据库。

当然,如果从datafile offline以后的所有archivelog都是全的且都可以用,那么你可以进行常规恢复,我这里来演示下通过bbed来修复checkpoint 信息来达到欺骗oracle的目的!

—-原库

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE             146801896 bytes
DATABASE Buffers           16777216 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.
DATABASE opened.

SQL> SELECT file#,name,STATUS FROM V$datafile ORDER BY 1;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/ora10g/oradata/roger/system01.dbf                      SYSTEM
         2 /home/ora10g/oradata/roger/undotbs01.dbf                     ONLINE
         3 /home/ora10g/oradata/roger/sysaux01.dbf                      ONLINE
         4 /home/ora10g/oradata/roger/users01.dbf                       ONLINE
         5 /home/ora10g/oradata/roger/roger01.dbf                       ONLINE
         6 /home/ora10g/oradata/roger/undotb2_01.dbf                    ONLINE
         7 /home/ora10g/oradata/roger/test1.dbf                         ONLINE
         8 /home/ora10g/oradata/roger/sqlt_01.dbf                       ONLINE
         9 /home/ora10g/oradata/roger/undotbs03.dbf                     ONLINE

9 ROWS selected.

SQL> ALTER DATABASE datafile 5 offline;
DATABASE altered.

SQL> SELECT file#,name,STATUS FROM V$datafile ORDER BY 1;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/ora10g/oradata/roger/system01.dbf                      SYSTEM
         2 /home/ora10g/oradata/roger/undotbs01.dbf                     ONLINE
         3 /home/ora10g/oradata/roger/sysaux01.dbf                      ONLINE
         4 /home/ora10g/oradata/roger/users01.dbf                       ONLINE
         5 /home/ora10g/oradata/roger/roger01.dbf                       RECOVER
         6 /home/ora10g/oradata/roger/undotb2_01.dbf                    ONLINE
         7 /home/ora10g/oradata/roger/test1.dbf                         ONLINE
         8 /home/ora10g/oradata/roger/sqlt_01.dbf                       ONLINE
         9 /home/ora10g/oradata/roger/undotbs03.dbf                     ONLINE

9 ROWS selected.

SQL> archive log list;
DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/ora10g/archivelog
Oldest online log SEQUENCE     16
NEXT log SEQUENCE TO archive   18
CURRENT log SEQUENCE           18

SQL> ALTER system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> SELECT file#,CHECKPOINT_CHANGE# FROM v$datafile ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            5403435
         2            5403435
         3            5403435
         4            5403435
         5            5402495
         6            5403435
         7            5403435
         8            5403435
         9            5403435

9 ROWS selected.

SQL> archive log list;
DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/ora10g/archivelog
Oldest online log SEQUENCE     19
NEXT log SEQUENCE TO archive   21
CURRENT log SEQUENCE           21

—–进行备份

RMAN> backup database include current controlfile format '/home/ora10g/db_full%u_%s_%p';

Starting backup at 19-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/ora10g/oradata/roger/system01.dbf
input datafile fno=00002 name=/home/ora10g/oradata/roger/undotbs01.dbf
input datafile fno=00009 name=/home/ora10g/oradata/roger/undotbs03.dbf
input datafile fno=00003 name=/home/ora10g/oradata/roger/sysaux01.dbf
input datafile fno=00004 name=/home/ora10g/oradata/roger/users01.dbf
input datafile fno=00005 name=/home/ora10g/oradata/roger/roger01.dbf
input datafile fno=00008 name=/home/ora10g/oradata/roger/sqlt_01.dbf
input datafile fno=00007 name=/home/ora10g/oradata/roger/test1.dbf
input datafile fno=00006 name=/home/ora10g/oradata/roger/undotb2_01.dbf
channel ORA_DISK_1: starting piece 1 at 19-OCT-12
channel ORA_DISK_1: finished piece 1 at 19-OCT-12
piece handle=/home/ora10g/db_full29no4kll_73_1 tag=TAG20121019T082004 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 19-OCT-12
channel ORA_DISK_1: finished piece 1 at 19-OCT-12
piece handle=/home/ora10g/db_full2ano4koa_74_1 tag=TAG20121019T082004 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 19-OCT-12

——进行异机recover(同主机,不同实例)

RMAN> startup nomount pfile='/tmp/a.ora';

connected to target database (not started)
Oracle instance started

Total System Global Area     167772160 bytes

Fixed Size                     1272600 bytes
Variable Size                113247464 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2920448 bytes

RMAN> restore controlfile from '/home/ora10g/db_full2ano4koa_74_1';

Starting restore at 19-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/home/ora10g/oradata/recover/control01.ctl
Finished restore at 19-OCT-12

RMAN> startup mount

database is already started
database mounted

RMAN> RUN {
2> ALLOCATE CHANNEL ch00 TYPE  disk;
3> set newname for datafile 1 to '/home/ora10g/oradata/recover/system01.dbf';
4> set newname for datafile 2 to '/home/ora10g/oradata/recover/undotbs01.dbf';
5> set newname for datafile 3 to '/home/ora10g/oradata/recover/sysaux01.dbf';
6> set newname for datafile 4 to '/home/ora10g/oradata/recover/users01.dbf';
7> set newname for datafile 5 to '/home/ora10g/oradata/recover/roger01.dbf';
8> set newname for datafile 6 to '/home/ora10g/oradata/recover/undotb2_01.dbf';
9> set newname for datafile 7 to '/home/ora10g/oradata/recover/test1.dbf';
10> set newname for datafile 8 to '/home/ora10g/oradata/recover/sqlt_01.dbf';
11> set newname for datafile 9 to '/home/ora10g/oradata/recover/undotbs03.dbf';
12> restore database;
13> switch datafile all;
14> RELEASE CHANNEL ch00;
15> }

allocated channel: ch00
channel ch00: sid=156 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing commnd: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 19-OCT-12

channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/ora10g/oradata/recover/system01.dbf
restoring datafile 00002 to /home/ora10g/oradata/recover/undotbs01.dbf
restoring datafile 00003 to /home/ora10g/oradata/recover/sysaux01.dbf
restoring datafile 00004 to /home/ora10g/oradata/recover/users01.dbf
restoring datafile 00005 to /home/ora10g/oradata/recover/roger01.dbf
restoring datafile 00006 to /home/ora10g/oradata/recover/undotb2_01.dbf
restoring datafile 00007 to /home/ora10g/oradata/recover/test1.dbf
restoring datafile 00008 to /home/ora10g/oradata/recover/sqlt_01.dbf
restoring datafile 00009 to /home/ora10g/oradata/recover/undotbs03.dbf
channel ch00: reading from backup piece /home/ora10g/db_full29no4kll_73_1
channel ch00: restored backup piece 1
piece handle=/home/ora10g/db_full29no4kll_73_1 tag=TAG20121019T082004
channel ch00: restore complete, elapsed time: 00:01:26
Finished restore at 19-OCT-12

datafile 1 switched to datafile copy
input datafile copy recid=56 stamp=797071282 filename=/home/ora10g/oradata/recover/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=57 stamp=797071282 filename=/home/ora10g/oradata/recover/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=58 stamp=797071282 filename=/home/ora10g/oradata/recover/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=59 stamp=797071282 filename=/home/ora10g/oradata/recover/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=60 stamp=797071282 filename=/home/ora10g/oradata/recover/roger01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=61 stamp=797071282 filename=/home/ora10g/oradata/recover/undotb2_01.dbf
datafile 7 switched to datafile copy
input datafile copy recid=62 stamp=797071282 filename=/home/ora10g/oradata/recover/test1.dbf
datafile 8 switched to datafile copy
input datafile copy recid=63 stamp=797071282 filename=/home/ora10g/oradata/recover/sqlt_01.dbf
datafile 9 switched to datafile copy
input datafile copy recid=64 stamp=797071282 filename=/home/ora10g/oradata/recover/undotbs03.dbf
released channel: ch00

RMAN> catalog start with '/home/ora10g/arch';

searching for all files that match the pattern /home/ora10g/arch

List of Files Unknown to the Database
=====================================
File Name: /home/ora10g/arch/0001_1_20_792658815.dbf
File Name: /home/ora10g/arch/0001_1_19_792658815.dbf
File Name: /home/ora10g/arch/0001_1_18_792658815.dbf

Do you really want to catalog the above files (enter YES or NO)  yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/ora10g/arch/0001_1_20_792658815.dbf
File Name: /home/ora10g/arch/0001_1_19_792658815.dbf
File Name: /home/ora10g/arch/0001_1_18_792658815.dbf

RMAN> recover database;

Starting recover at 19-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 18 is already on disk as file /home/ora10g/arch/0001_1_18_792658815.dbf
archive log thread 1 sequence 19 is already on disk as file /home/ora10g/arch/0001_1_19_792658815.dbf
archive log thread 1 sequence 20 is already on disk as file /home/ora10g/arch/0001_1_20_792658815.dbf
archive log filename=/home/ora10g/arch/0001_1_18_792658815.dbf thread=1 sequence=18
archive log filename=/home/ora10g/arch/0001_1_19_792658815.dbf thread=1 sequence=19
archive log filename=/home/ora10g/arch/0001_1_20_792658815.dbf thread=1 sequence=20
unable to find archive log
archive log thread=1 sequence=21
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/19/2012 08:42:22
RMAN-06054: media recovery requesting unknown log: thread 1 seq 21 lowscn 5403435

RMAN>  recover database until scn 5404334;

Starting recover at 19-OCT-12
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=21
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/home/ora10g/oradata/recover/system01.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/19/2012 08:45:07
RMAN-06054: media recovery requesting unknown log: thread 1 seq 21 lowscn 5403435
——rename logfiles

SQL> ALTER DATABASE RENAME file '/home/ora10g/oradata/roger/redo01.log' TO '/home/ora10g/oradata/recover/redo01.log';

ALTER DATABASE RENAME file '/home/ora10g/oradata/roger/redo02.log' TO '/home/ora10g/oradata/recover/redo02.log';
ALTER DATABASE RENAME file '/home/ora10g/oradata/roger/redo03.log' TO '/home/ora10g/oradata/recover/redo03.log';
DATABASE altered.
SQL>
DATABASE altered.
SQL>
DATABASE altered.

SQL> SELECT name FROM V$datafile;

NAME
--------------------------------------------------------
/home/ora10g/oradata/recover/system01.dbf
/home/ora10g/oradata/recover/undotbs01.dbf
/home/ora10g/oradata/recover/sysaux01.dbf
/home/ora10g/oradata/recover/users01.dbf
/home/ora10g/oradata/recover/roger01.dbf
/home/ora10g/oradata/recover/undotb2_01.dbf
/home/ora10g/oradata/recover/test1.dbf
/home/ora10g/oradata/recover/sqlt_01.dbf
/home/ora10g/oradata/recover/undotbs03.dbf

9 ROWS selected.

SQL> SELECT member FROM v$logfile;

MEMBER
--------------------------------------------------------
/home/ora10g/oradata/recover/redo03.log
/home/ora10g/oradata/recover/redo02.log
/home/ora10g/oradata/recover/redo01.log

RMAN> ALTER DATABASE OPEN resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure OF ALTER db command at 10/19/2012 08:42:59
ORA-01152: file 1 was NOT restored FROM a sufficiently OLD backup
ORA-01110: DATA file 1: '/home/ora10g/oradata/recover/system01.dbf'

检查datafile scn

SQL> ALTER DATABASE datafile 5 online;
DATABASE altered.

SQL> SELECT file#,name,STATUS FROM V$datafile ORDER BY 1;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/ora10g/oradata/recover/system01.dbf                    SYSTEM
         2 /home/ora10g/oradata/recover/undotbs01.dbf                   ONLINE
         3 /home/ora10g/oradata/recover/sysaux01.dbf                    ONLINE
         4 /home/ora10g/oradata/recover/users01.dbf                     ONLINE
         5 /home/ora10g/oradata/recover/roger01.dbf                     RECOVER
         6 /home/ora10g/oradata/recover/undotb2_01.dbf                  ONLINE
         7 /home/ora10g/oradata/recover/test1.dbf                       ONLINE
         8 /home/ora10g/oradata/recover/sqlt_01.dbf                     ONLINE
         9 /home/ora10g/oradata/recover/undotbs03.dbf                   ONLINE

9 ROWS selected.

SQL> SELECT file#,CHECKPOINT_CHANGE# FROM v$datafile ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            5404334
         2            5404334
         3            5404334
         4            5404334
         5            5403435
         6            5404334
         7            5404334
         8            5404334
         9            5404334

9 ROWS selected.

---尝试在sqlplus中进行恢复

SQL>  recover DATABASE USING backup controlfile ;
ORA-00279: CHANGE 5403435 generated at 10/19/2012 08:04:18 needed FOR thread 1
ORA-00289: suggestion : /home/ora10g/arch/0001_1_21_792658815.dbf
ORA-00280: CHANGE 5403435 FOR thread 1 IS IN SEQUENCE #21

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot OPEN archived log '/home/ora10g/arch/0001_1_21_792658815.dbf'
ORA-27037: unable TO obtain file STATUS
Linux Error: 2: No such file OR directory
Additional information: 3

ORA-00308: cannot OPEN archived log '/home/ora10g/arch/0001_1_21_792658815.dbf'
ORA-27037: unable TO obtain file STATUS
Linux Error: 2: No such file OR directory
Additional information: 3

SQL> ALTER DATABASE datafile 5 online;

DATABASE altered.

SQL> SELECT file#,name,STATUS FROM V$datafile ORDER BY 1;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/ora10g/oradata/recover/system01.dbf                    SYSTEM
         2 /home/ora10g/oradata/recover/undotbs01.dbf                   ONLINE
         3 /home/ora10g/oradata/recover/sysaux01.dbf                    ONLINE
         4 /home/ora10g/oradata/recover/users01.dbf                     ONLINE
         5 /home/ora10g/oradata/recover/roger01.dbf                     RECOVER
         6 /home/ora10g/oradata/recover/undotb2_01.dbf                  ONLINE
         7 /home/ora10g/oradata/recover/test1.dbf                       ONLINE
         8 /home/ora10g/oradata/recover/sqlt_01.dbf                     ONLINE
         9 /home/ora10g/oradata/recover/undotbs03.dbf                   ONLINE

9 ROWS selected.

SQL> SELECT file#,CHECKPOINT_CHANGE# FROM v$datafile ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            5404334
         2            5404334
         3            5404334
         4            5404334
         5            5403435
         6            5404334
         7            5404334
         8            5404334
         9            5404334

9 ROWS selected.

SQL> ALTER DATABASE OPEN resetlogs;
ALTER DATABASE OPEN resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery IF it was restored FROM backup, OR END BACKUP IF it was NOT
ORA-01110: DATA file 1: '/home/ora10g/oradata/recover/system01.dbf'

——用bbed修改checkpoint信息

BBED> set file 1 block 1
        FILE#           1
        BLOCK#          1

BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x0000041b

BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x0000041a

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484    
   struct kcvcpscn, 8 bytes                 @484    
      ub4 kscnbas                           @484      0x005276ae
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x2f8252b5
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500    
      struct kcvcprba, 12 bytes             @500    
         ub4 kcrbaseq                       @500      0x00000015
         ub4 kcrbabno                       @504      0x00000afc
         ub2 kcrbabof                       @508      0x0010
   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 file 5 block 1
        FILE#           5
        BLOCK#          1

BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x000000af

BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x000000ae

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484    
   struct kcvcpscn, 8 bytes                 @484    
      ub4 kscnbas                           @484      0x005276ae
      ub2 kscnwrp                           @488      0x0000
   ub4 kcvcptim                             @492      0x2f824f02
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500    
      struct kcvcprba, 12 bytes             @500    
         ub4 kcrbaseq                       @500      0x00000015
         ub4 kcrbabno                       @504      0x00000afc
         ub2 kcrbabof                       @508      0x0010
   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 file 5 block 1
        FILE#           5
        BLOCK#          1

BBED> modify /x  1b04 offset 140
Warning: contents of pOracleoracleplus.netrevious BIFILE will be lost. Proceed  (Y/N) y
File: /home/ora10g/oradata/recover/roger01.dbf (5)
Block: 1                Offsets:  140 to  239           Dba:0x01400001
------------------------------------------------------------------------
1b040000 515c822f ae000000 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 1a04 offset 148
File: /home/ora10g/oradata/recover/roger01.dbf (5)
Block: 1                Offsets:  148 to  247           Dba:0x01400001
------------------------------------------------------------------------
1a040000 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 b552 offset 492
File: /home/ora10g/oradata/recover/roger01.dbf (5)
Block: 1                Offsets:  492 to  591           Dba:0x01400001
------------------------------------------------------------------------
b552822f 01003f2f 15000000 fc0a0000 10003e2f 02000000 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 5, Block 1:
current = 0x7c9f, required = 0x7c9f

BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/recover/roger01.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

——再次recover,然后open database

SQL> recover DATABASE USING backup controlfile until cancel;
ORA-00279: CHANGE 5404334 generated at 10/19/2012 08:20:05 needed FOR thread 1
ORA-00289: suggestion : /home/ora10g/arch/0001_1_21_792658815.dbf
ORA-00280: CHANGE 5404334 FOR thread 1 IS IN SEQUENCE #21

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot OPEN archived log '/home/ora10g/arch/0001_1_21_792658815.dbf'
ORA-27037: unable TO obtain file STATUS
Linux Error: 2: No such file OR directory
Additional information: 3

ORA-00308: cannot OPEN archived log '/home/ora10g/arch/0001_1_21_792658815.dbf'
ORA-27037: unable TO obtain file STATUS
Linux Error: 2: No such file OR directory
Additional information: 3

SQL> ALTER DATABASE OPEN resetlogs;
DATABASE altered.

SQL> SELECT file#,name,STATUS FROM V$datafile ORDER BY 1;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/ora10g/oradata/recover/system01.dbf                    SYSTEM
         2 /home/ora10g/oradata/recover/undotbs01.dbf                   ONLINE
         3 /home/ora10g/oradata/recover/sysaux01.dbf                    ONLINE
         4 /home/ora10g/oradata/recover/users01.dbf                     ONLINE
         5 /home/ora10g/oradata/recover/roger01.dbf                     ONLINE
         6 /home/ora10g/oradata/recover/undotb2_01.dbf                  ONLINE
         7 /home/ora10g/oradata/recover/test1.dbf                       ONLINE
         8 /home/ora10g/oradata/recover/sqlt_01.dbf                     ONLINE
         9 /home/ora10g/oradata/recover/undotbs03.dbf                   ONLINE

9 ROWS selected.

SQL> SELECT file#,CHECKPOINT_CHANGE# FROM v$datafile ORDER BY 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            5404338
         2            5404338
         3            5404338
         4            5404338
         5            5404338
         6            5404338
         7            5404338
         8            5404338
         9            5404338

9 ROWS selected.

——–原库

[ora10g@killdb oradata]$ mv roger_bak  roger
[ora10g@killdb oradata]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production ON Fri Oct 19 10:25:59 2012
Copyright (c) 1982, 2010, Oracle.  ALL Rights Reserved.
Connected TO an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE             146801896 bytes
DATABASE Buffers           16777216 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.
DATABASE opened.

SQL> ALTER DATABASE datafile 5 online;
ALTER DATABASE datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery IF it was restored FROM backup, OR END BACKUP IF it was NOT
ORA-01110: DATA file 5: '/home/ora10g/oradata/roger/roger01.dbf'


SQL> shutdown immediate
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE             134218984 bytes
DATABASE Buffers           29360128 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.
SQL> recover datafile 5;
ORA-00279: CHANGE 5402495 generated at 10/19/2012 08:03:32 needed FOR thread 1
ORA-00289: suggestion : /home/ora10g/archivelog/0001_1_18_792658815.dbf
ORA-00280: CHANGE 5402495 FOR thread 1 IS IN SEQUENCE #18


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL>
SQL> ALTER DATABASE datafile 5 online;

DATABASE altered.

SQL> SELECT file#,STATUS FROM v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE
         8 ONLINE
         9 ONLINE

9 ROWS selected.

SQL> ALTER DATABASE OPEN;
DATABASE altered.
我们可以看到,在异机恢复的时候,recover以后,我们再去修改checkpoint信息,然后可以顺利的open数据库。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle bbed修复checkpoint恢复启动数据库

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1398.html

Oracle研究中心

关键词:

Oracle bbed使用案例

Oracle存在datafile offline如何进行异机恢复

使用bbed工具修复data file的checkpoint检查点