datafile 也能跨resetlogs ？
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: datafile 也能跨resetlogs ？
我们都知道Oracle 10g引入了跨resetlogs特性，这个特性确实很棒。在10g之前，如果我们要进行跨resetlogs恢复，
那么只能手工去修改datafile header的checkpoint 信息. 然而这一切在10g发现了改变，引入了跨resetlogs恢复。
但是，Oracle 10g引入的跨resetlogs恢复，仅仅是database 级别，如是某个datafile的checkpoint信息跟其他文件
不一致，那么仍然是不能进行正常恢复的，除非通过restore+archivelog的方式去恢复。下面来看我的测试：
1)Test For 10gR2
|
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 |
[ora10g@killdb ~]$ sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 13 01:39:52 2013 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /home/ora10g/archivelog Oldest online log sequence 149 Next log sequence to archive 151 Current log sequence 151 SQL> oradebug setmypid Statement processed. SQL> alter database backup controlfile to trace; Database altered. SQL> oradebug tracefile_name /home/ora10g/admin/roger/udump/roger_ora_10179.trc SQL> SQL> alter system switch logfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 629145600 bytes Fixed Size 1275272 bytes Variable Size 255855224 bytes Database Buffers 369098752 bytes Redo Buffers 2916352 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "ROGER" RESETLOGS FORCE LOGGING ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/home/ora10g/oradata/roger/redo01.log' SIZE 50M, 9 GROUP 2 '/home/ora10g/oradata/roger/redo02.log' SIZE 50M, 10 GROUP 3 '/home/ora10g/oradata/roger/redo03.log' SIZE 50M 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/home/ora10g/oradata/roger/system01.dbf', 14 '/home/ora10g/oradata/roger/undotbs01.dbf', 15 '/home/ora10g/oradata/roger/sysaux01.dbf', 16 '/home/ora10g/oradata/roger/users01.dbf', 17 '/home/ora10g/oradata/roger/system02.dbf', 18 '/home/ora10g/oradata/roger/undotbs2.dbf', 19 '/home/ora10g/oradata/roger/streams01.dbf' 20 CHARACTER SET ZHS16GBK 21 ; Control file created. SQL> recover database using backup controlfile until cancel; ORA-00279: change 12360262 generated at 12/13/2013 01:47:16 needed for thread 1 ORA-00289: suggestion : /home/ora10g/archivelog/1_152_822878558.dbf ORA-00280: change 12360262 for thread 1 is in sequence #152 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/home/ora10g/archivelog/1_152_822878558.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/archivelog/1_152_822878558.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; 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/product/10.2/dbs/MISSING00005 RECOVER 6 /home/ora10g/oradata/roger/system02.dbf SYSTEM 7 /home/ora10g/oradata/roger/undotbs2.dbf ONLINE 8 /home/ora10g/oradata/roger/streams01.dbf ONLINE 8 rows selected. SQL> alter database rename file '/home/ora10g/product/10.2/dbs/MISSING00005' to '/home/ora10g/oradata/roger/roger01.dbf'; Database altered. SQL> recover datafile 5; ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [krhpfh_03-1202], [fno =], [5], [fhcrt =], [821580206], [cptim =], [0], [] ORA-01110: data file 5: '/home/ora10g/oradata/roger/roger01.dbf' |
|
1 |
大家可以看到，这是不能直接进行recover的，需要手工去修改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 |
BBED> info all File# Name Size(blks) ----- ---- ---------- 1 /home/ora10g/oradata/roger/system01.dbf 0 2 /home/ora10g/oradata/roger/undotbs01.dbf 0 3 /home/ora10g/oradata/roger/sysaux01.dbf 0 4 /home/ora10g/oradata/roger/users01.dbf 0 5 /home/ora10g/oradata/roger/roger01.dbf 0 6 /home/ora10g/oradata/roger/system02.dbf 0 7 /home/ora10g/oradata/roger/undotbs2.dbf 0 8 /home/ora10g/oradata/roger/streams01.dbf 0 BBED> set file 5 block 1 FILE# 5 BLOCK# 1 BBED> modify /x e238b6 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /home/ora10g/oradata/roger/roger01.dbf (5) Block: 1 Offsets: 112 to 115 Dba:0x01400001 ------------------------------------------------------------------------ e238b631 <32 bytes per line> BBED> modify /x 479abc File: /home/ora10g/oradata/roger/roger01.dbf (5) Block: 1 Offsets: 116 to 119 Dba:0x01400001 ------------------------------------------------------------------------ 479abc00 <32 bytes per line> BBED> modify /x 4a offset 484 File: /home/ora10g/oradata/roger/roger01.dbf (5) Block: 1 Offsets: 484 to 487 Dba:0x01400001 ------------------------------------------------------------------------ 4a9abc00 <32 bytes per line> BBED> modify /x e9 offset 492 File: /home/ora10g/oradata/roger/roger01.dbf (5) Block: 1 Offsets: 492 to 495 Dba:0x01400001 ------------------------------------------------------------------------ e938b631 <32 bytes per line> BBED> sum apply Check value for File 5, Block 1: current = 0x37d1, required = 0x37d1 |
|
1 |
修改完成之后，我们flush 一下buffer cache 即可进行恢复并将datafile online，如下： |
|
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 |
SQL> alter system flush buffer_cache; System altered. SQL> recover datafile 5; Media recovery complete. SQL> alter database datafile 5 online; Database altered. SQL> SQL> select file#,name,status from v$datafile; 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/system02.dbf SYSTEM 7 /home/ora10g/oradata/roger/undotbs2.dbf ONLINE 8 /home/ora10g/oradata/roger/streams01.dbf ONLINE 8 rows selected. SQL> |
然而这一切，在oracle 11gR2中发现改变了，经过测试,在11.2.0.3以及以后的版本中可以进行直接恢复的，下面是我的测试.
2)Test For 11.2.0.3
|
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 |
[oracle@11gR2texing ~]$ sqlplus "/as sysdba" SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 17 11:46:37 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Real Application Testing options SQL> oradebug setmypid Statement processed. SQL> alter database backup controlfile to trace; Database altered. SQL> oradebug tracefile_name /oracle/diag/rdbms/roger/roger/trace/roger_ora_13278.trc SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 730714112 bytes Fixed Size 2231952 bytes Variable Size 452985200 bytes Database Buffers 272629760 bytes Redo Buffers 2867200 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "ROGER" RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/oracle/oradata/roger/redo01.log' SIZE 50M BLOCKSIZE 512, 9 GROUP 2 '/oracle/oradata/roger/redo02.log' SIZE 50M BLOCKSIZE 512, 10 GROUP 3 '/oracle/oradata/roger/redo03.log' SIZE 50M BLOCKSIZE 512 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/oracle/oradata/roger/system01.dbf', 14 '/oracle/oradata/roger/sysaux01.dbf', 15 '/oracle/oradata/roger/undotbs01.dbf', 16 '/oracle/oradata/roger/users01.dbf', 17 '/oracle/oradata/roger/dbtk.dbf' 18 CHARACTER SET WE8MSWIN1252 19 ; Control file created. SQL> recover database until cancel; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done SQL> recover database using backup controlfile until cancel; ORA-00279: change 12093166 generated at 10/17/2013 11:47:11 needed for thread 1 ORA-00289: suggestion : /arch/1_163_793419533.dbf ORA-00280: change 12093166 for thread 1 is in sequence #163 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/arch/1_163_793419533.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/arch/1_163_793419533.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> alter database open resetlogs; Database altered. SQL> set lines 200 SQL> col name for a65 SQL> select file#,name,status from v$datafile; FILE# NAME STATUS ---------- ----------------------------------------------------------------- ------- 1 /oracle/oradata/roger/system01.dbf SYSTEM 2 /oracle/oradata/roger/sysaux01.dbf ONLINE 3 /oracle/oradata/roger/undotbs01.dbf ONLINE 4 /oracle/oradata/roger/users01.dbf ONLINE 5 /oracle/oradata/roger/dbtk.dbf ONLINE 6 /oracle/product/11.2.0/db_1/dbs/MISSING00006 RECOVER 6 rows selected. SQL> alter database rename file '/oracle/product/11.2.0/db_1/dbs/MISSING00006' to '/oracle/oradata/roger/roger01.dbf'; Database altered. SQL> alter database datafile 6 online; alter database datafile 6 online * ERROR at line 1: ORA-01190: control file or data file 6 is from before the last RESETLOGS ORA-01110: data file 6: '/oracle/oradata/roger/roger01.dbf' SQL> recover datafile 6; Media recovery complete. SQL> alter database datafile 6 online; Database altered. SQL> select file#,name,status from v$datafile; FILE# NAME STATUS ---------- ----------------------------------------------------------------- ------- 1 /oracle/oradata/roger/system01.dbf SYSTEM 2 /oracle/oradata/roger/sysaux01.dbf ONLINE 3 /oracle/oradata/roger/undotbs01.dbf ONLINE 4 /oracle/oradata/roger/users01.dbf ONLINE 5 /oracle/oradata/roger/dbtk.dbf ONLINE 6 /oracle/oradata/roger/roger01.dbf ONLINE 6 rows selected. SQL> |
其他版本并未测试，欢迎大家测试！
Leave a Reply
You must be logged in to post a comment.