存在datafile offline，如何进行异机恢复？
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 存在datafile offline，如何进行异机恢复？
在最近一个项目数据库的迁移中，检查发生有个datafile状态是offline的，而且该datafile
是2012年3月份offline的，如果要进行恢复，那么需要从2年多的archive，事实上，对于offline的
datafile，我们完全可以手工去修改该文件的检查点信息，然后正常open数据库。
当然，如果从datafile offline以后的所有archivelog都是全的且都可以用，那么你可以进行常规恢复，
我这里来演示下通过bbed来修复checkpoint 信息来达到欺骗oracle的目的！
—-原库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
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 |
—–进行备份
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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 RMAN> |
——进行异机recover（同主机，不同实例）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 |
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 command: 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> 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 |
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> 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信息
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 |
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 previous 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> 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 BBED> |
——再次recover，然后open database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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> 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. |
——–原库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
[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数据库。
6 Responses to “存在datafile offline，如何进行异机恢复？”
cool,good
Roger，还是那几招，我已经没有兴趣看了
Oracle与CUUG共同举办–Oracle高级技术沙龙
10月28日 北京 《王的盛宴－容灾技术大PK》将展示当前国际上流行的多种容灾软件的特点和应用范围，包括了oracle的GoldenGate、quest公司的shareplex、DSG的realsync以及九桥的DDS软件等
Oracle与CUUG共同举办–Oracle高级技术沙龙
10月28日 北京 《王的盛宴－容灾技术大PK》将展示当前国际上流行的多种容灾软件的特点和应用范围，包括了oracle的GoldenGate、quest公司的shareplex、DSG的realsync以及九桥的DDS软件等
发送 姓名 电话至 wangyc@cuug.com报名
朋友给我一首诗，说是从一个刊物上抄下来的，让我欣赏。作为一名文学爱好者，我对诗歌还是情有独钟，便拿来细细品读。读了一遍，未解其意，再读，仍不知所云。我揉揉眼睛，又读数遍，还是欣赏不了。我说，我的欣赏水平有限，能否给我点拨一
顶用就好
Leave a Reply
You must be logged in to post a comment.