20160331数据文件offline与open resetlogs2
[20160331]數(shù)據(jù)文件offline與open resetlogs3.txt
--接上面的測試.鏈接:
--關(guān)機做一個冷備份,便于重復測試.取出冷備份,重復測試:
--做一些必要的清理清除歸檔.
1.環(huán)境:
SYS@book> @ &r/ver1
PORT_STRING??????????????????? VERSION??????? BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx??????????? 11.2.0.4.0???? Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--session 1:
update t set name='aaaa' where id=1;
commit ;
--session 2:
update t set name='bbbb' where id=2;
--不提交。
--session 3:
SYS@book> alter database datafile 7 offline ;
Database altered.
--session 2:
commit ;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
--切換出全部redo到歸檔。
SYS@book> shutdown abort ;
ORACLE instance shut down.
2.重新打開數(shù)據(jù)庫看看：
SYS@book> startup mount
ORACLE instance started.
Total System Global Area? 634732544 bytes
Fixed Size????????????????? 2255792 bytes
Variable Size???????????? 197133392 bytes
Database Buffers????????? 427819008 bytes
Redo Buffers??????????????? 7524352 bytes
Database mounted.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#? , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name? FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME???? CREATION_CHANGE# RESETLOGS_CHANGE# STATUS? CHECKPOINT_COUNT FUZ NAME???????????????????????????? TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
??? 1??????? 13227501245 2016-03-31 11:56:45??????????????? 7?????? 13227286650 ONLINE????????????? 1006 YES /mnt/ramdisk/book/system01.dbf?? SYSTEM
??? 2??????? 13227501245 2016-03-31 11:56:45???????????? 1834?????? 13227286650 ONLINE????????????? 1002 YES /mnt/ramdisk/book/sysaux01.dbf?? SYSAUX
??? 3??????? 13227501245 2016-03-31 11:56:45?????????? 923328?????? 13227286650 ONLINE?????????????? 922 YES /mnt/ramdisk/book/undotbs01.dbf? UNDOTBS1
??? 4??????? 13227501245 2016-03-31 11:56:45??????????? 16143?????? 13227286650 ONLINE????????????? 1006 YES /mnt/ramdisk/book/users01.dbf??? USERS
??? 5??????? 13227501245 2016-03-31 11:56:45?????????? 952916?????? 13227286650 ONLINE?????????????? 919 YES /mnt/ramdisk/book/example01.dbf? EXAMPLE
??? 6??????? 13227501245 2016-03-31 11:56:45????????? 1314508?????? 13227286650 ONLINE?????????????? 935 YES /mnt/ramdisk/book/sugar01.dbf??? SUGAR
??? 7??????? 13227500869 2016-03-31 11:55:43????? 13227207527?????? 13227286650 OFFLINE?????????????? 26 YES /mnt/ramdisk/book/tea01.dbf????? TEA
7 rows selected.
--如果我使用 recover database using backup controlfile until cancel;恢復后，不能在使用open打開，必須加入open resetlogs參數(shù)。
SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227501245 generated at 03/31/2016 11:56:45 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_22_907434361.dbf
ORA-00280: change 13227501245 for thread 1 is in sequence #22
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 13227501251 generated at 03/31/2016 11:56:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_23_907434361.dbf
ORA-00280: change 13227501251 for thread 1 is in sequence #23
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_22_907434361.dbf' no longer needed for this recovery
ORA-00279: change 13227501257 generated at 03/31/2016 11:56:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf
ORA-00280: change 13227501257 for thread 1 is in sequence #24
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_23_907434361.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_24_907434361.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
SYS@book> @ &r/logfile
GROUP# THREAD# SEQUENCE#?????? BYTES BLOCKSIZE MEMBERS ARC STATUS???? FIRST_CHANGE# FIRST_TIME????????? NEXT_CHANGE# NEXT_TIME?????????? GROUP# STATUS???? TYPE?????? MEMBER???????????????????????????????????????????? IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- -------------------------------------------------- ---
???? 1?????? 1??????? 22??? 52428800?????? 512?????? 1 YES ACTIVE?????? 13227501245 2016-03-31 11:56:45? 13227501251 2016-03-31 11:56:47????? 1??????????? ONLINE???? /mnt/ramdisk/book/redo01.log?????????????????????? NO
???? 2?????? 1??????? 23??? 52428800?????? 512?????? 1 YES ACTIVE?????? 13227501251 2016-03-31 11:56:47? 13227501257 2016-03-31 11:56:47????? 2??????????? ONLINE???? /mnt/ramdisk/book/redo02.log?????????????????????? NO
???? 3?????? 1??????? 24??? 52428800?????? 512?????? 1 NO? CURRENT????? 13227501257 2016-03-31 11:56:47 2.814750E+14????????????????????????? 3??????????? ONLINE???? /mnt/ramdisk/book/redo03.log?????????????????????? NO
--還需要 /mnt/ramdisk/book/redo03.log.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#? , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name? FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME???? CREATION_CHANGE# RESETLOGS_CHANGE# STATUS???? CHECKPOINT_COUNT FUZ NAME??????????????????????????? TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------- ---------------
??? 1??????? 13227501257 2016-03-31 11:56:47??????????????? 7?????? 13227286650 ONLINE???????????????? 1006 YES /mnt/ramdisk/book/system01.dbf? SYSTEM
??? 7??????? 13227500869 2016-03-31 11:55:43????? 13227207527?????? 13227286650 ONLINE?????????????????? 26 YES /mnt/ramdisk/book/tea01.dbf???? TEA
--另外可以發(fā)現(xiàn)數(shù)據(jù)文件7在recover時已經(jīng)設(shè)置為online.人為再次設(shè)置為offline.
SYS@book> alter database datafile 7 offline ;
Database altered.
--繼續(xù)恢復.
SYS@book> alter database datafile 7 offline ;
Database altered.
SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227501257 generated at 03/31/2016 11:56:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf
ORA-00280: change 13227501257 for thread 1 is in sequence #24
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo03.log
Log applied.
Media recovery complete.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#? , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name? FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME???? CREATION_CHANGE# RESETLOGS_CHANGE# STATUS???? CHECKPOINT_COUNT FUZ NAME??????????????????????????? TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------- ---------------
??? 1??????? 13227501259 2016-03-31 11:56:47??????????????? 7?????? 13227286650 ONLINE???????????????? 1007 NO? /mnt/ramdisk/book/system01.dbf? SYSTEM
??? 7??????? 13227500869 2016-03-31 11:55:43????? 13227207527?????? 13227286650 OFFLINE????????????????? 26 YES /mnt/ramdisk/book/tea01.dbf???? TEA
--注意一個細節(jié),數(shù)據(jù)文件7 是offline狀態(tài).也就是在mount狀態(tài)設(shè)置offline,在恢復時不會在變成online.這樣恢復就不像前面那樣需要seq=20,21,22,23 歸檔.
--不知道這個細節(jié)oracle如何控制的???
--恢復僅僅需要/mnt/ramdisk/book/redo03.log,而數(shù)據(jù)文件7的CHECKPOINT_CHANGE#依舊等于13227500869.也就是沒有進行任何恢復.
SYS@book> alter database open resetlogs;
Database altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#? , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name? FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME???? CREATION_CHANGE# RESETLOGS_CHANGE# STATUS???? CHECKPOINT_COUNT FUZ NAME?????????????????????????? TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------ ---------------
??? 1??????? 13227501263 2016-03-31 12:03:48??????????????? 7?????? 13227501260 ONLINE???????????????? 1009 YES /mnt/ramdisk/book/system01.dbf SYSTEM
??? 7??????? 13227500869 2016-03-31 11:55:43????? 13227207527?????? 13227286650 OFFLINE????????????????? 26 YES /mnt/ramdisk/book/tea01.dbf??? TEA
--數(shù)據(jù)文件7的RESETLOGS_CHANGE#與數(shù)據(jù)文件1的RESETLOGS_CHANGE#不一致.
SYS@book> alter database datafile 7 online ;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01190: control file or data file 7 is from before the last RESETLOGS
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'
--不要被ORA-01190提示蒙騙,實際上10g以后可以跨resetlogs恢復,只要歸檔日志全部都在.
SYS@book> recover datafile 7;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 7 belongs to an orphan incarnation
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'
--這次不行,因為我前一次恢復數(shù)據(jù)文件7CHECKPOINT_CHANGE#,RESETLOGS_CHANGE#僅僅相差1,完成了前面的恢復.所以recover datafile 7 ok.
3.嘗試使用rman恢復:
--我嘗試使用 恢復到resetlog之前13227501259.
recover datafile 7 until scn 13227501259;
reset database to incarnation 4;
recover datafile 7 until scn 13227501259;
--都不行,視乎提示要使用舊的控制文件.恢復過程略....僅僅注意我使用的是冷備份,要使用如下方式:
SYS@book> alter database datafile 1,2,3,4,5,6 offline ;
Database altered.
SYS@book> recover database using backup controlfile until change 13227501259;
ORA-00279: change 13227500866 generated at 03/31/2016 08:53:17 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_20_907434361.dbf
ORA-00280: change 13227500866 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 13227501237 generated at 03/31/2016 11:56:45 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_21_907434361.dbf
ORA-00280: change 13227501237 for thread 1 is in sequence #21
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_20_907434361.dbf' no longer needed for this recovery
ORA-00279: change 13227501245 generated at 03/31/2016 11:56:45 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_22_907434361.dbf
ORA-00280: change 13227501245 for thread 1 is in sequence #22
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_21_907434361.dbf' no longer needed for this recovery
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
ORA-01112: media recovery not started
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#? , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name? FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME???? CREATION_CHANGE# RESETLOGS_CHANGE# STATUS? CHECKPOINT_COUNT FUZ NAME??????????????????????? TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- --------------------------- ----------------
??? 1????????????????? 0??????????????????????????????????? 0???????????????? 0 OFFLINE??????????????? 0
??? 7??????? 13227501259 2016-03-31 11:56:47????? 13227207527?????? 13227286650 ONLINE??????????????? 25 NO? /mnt/ramdisk/book/tea01.dbf TEA
--然后要拷貝回去.步驟忽略.
4.繼續(xù)恢復:
SYS@book> startup mount
ORACLE instance started.
Total System Global Area??? 634732544 bytes
Fixed Size??????????????????? 2255792 bytes
Variable Size?????????????? 197133392 bytes
Database Buffers??????????? 427819008 bytes
Redo Buffers????????????????? 7524352 bytes
Database mounted.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#? , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name? FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME???? CREATION_CHANGE# RESETLOGS_CHANGE# STATUS? CHECKPOINT_COUNT FUZ NAME??????????????????????????? TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ----------------
??? 1??????? 13227501915 2016-03-31 12:05:34??????????????? 7?????? 13227501260 ONLINE????????????? 1010 NO? /mnt/ramdisk/book/system01.dbf? SYSTEM
??? 7??????? 13227501259 2016-03-31 11:56:47????? 13227207527?????? 13227286650 OFFLINE?????????????? 25 NO? /mnt/ramdisk/book/tea01.dbf???? TEA
alter database open;
SYS@book> alter database datafile 7 online ;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01190: control file or data file 7 is from before the last RESETLOGS
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'
--不要再次蒙騙,僅僅差1.
SYS@book> recover datafile 7 ;
Media recovery complete.
SYS@book> alter database datafile 7 online ;
Database altered.
SYS@book> select rowid,t.* from scott.t ;
ROWID??????????????????????? ID NAME
------------------ ------------ ---------
AAAWgeAAHAAAACLAAA??????????? 1 aaaa
AAAWgeAAHAAAACLAAB??????????? 2 bbbb
AAAWgeAAHAAAACLAAC??????????? 3 text
AAAWgeAAHAAAACLAAD??????????? 4 text
--沒有任何丟失.
--從這些測試也說明,在恢復執(zhí)行alter database open resetlogs,一定要確定是否有數(shù)據(jù)文件還處在offline的狀態(tài).避免不必要的麻煩.
總結(jié)
以上是生活随笔為你收集整理的20160331数据文件offline与open resetlogs2的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： redis实战课题
- 下一篇： redis简单了解 二 （集群）