[20160331]数据文件offline与open resetlogs3.txt
--接上面的测试.链接:
--关机做一个冷备份,便于重复测试.取出冷备份,重复测试:
--做一些必要的清理清除归档.
1.环境:
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.重新打开数据库看看：
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参数。
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
--另外可以发现数据文件7在recover时已经设置为online.人为再次设置为offline.
SYS@book> alter database datafile 7 offline ;
Database altered.
--继续恢复.
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
--注意一个细节,数据文件7 是offline状态.也就是在mount状态设置offline,在恢复时不会在变成online.这样恢复就不像前面那样需要seq=20,21,22,23 归档.
--不知道这个细节oracle如何控制的???
--恢复仅仅需要/mnt/ramdisk/book/redo03.log,而数据文件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
--数据文件7的RESETLOGS_CHANGE#与数据文件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'
--这次不行,因为我前一次恢复数据文件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.继续恢复:
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
--没有任何丢失.
--从这些测试也说明,在恢复执行alter database open resetlogs,一定要确定是否有数据文件还处在offline的状态.避免不必要的麻烦.
