[20161019]数据文件offline后恢复到那个scn号.txt
--前一天别人问的问题，如果数据文件offline时，online要恢复，一般恢复到scn是多少，是offline时的scn吗？
--总不见得如果长时间offline，要应用许多归档日志吧，通过测试说明问题：
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
$ cat x1.sql
select dbms_flashback.get_system_change_number scn from dual;
alter database datafile 6 offline;
select dbms_flashback.get_system_change_number scn from dual;
2.测试：
SCOTT@book> @ x1
SCN
----------
1987849
Database altered.
SCN
----------
1987866
--我的机器没有什么事务，恢复的scn是1987849+1=1987850吗？
BEGIN
DBMS_LOGMNR.START_LOGMNR
(
STARTSCN => 1987849
,ENDSCN => 1987866
,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+ DBMS_LOGMNR.CONTINUOUS_MINE
+ DBMS_LOGMNR.COMMITTED_DATA_ONLY
);
END;
/
SCOTT@book> create table xx as select * from V$LOGMNR_CONTENTS ;
Table created.
select * from xx where sql_redo like 'alter database datafile%';
Record View
As of: 2016/10/19 8:48:20
SCN: 1987853
START_SCN:
COMMIT_SCN:
TIMESTAMP: 2016/10/19 8:42:56
START_TIMESTAMP:
COMMIT_TIMESTAMP:
XIDUSN: 2
XIDSLT: 13
XIDSQN: 969
XID: 02000D00C9030000
PXIDUSN: 2
PXIDSLT: 13
PXIDSQN: 969
PXID: 02000D00C9030000
TX_NAME:
OPERATION: DDL
OPERATION_CODE: 5
ROLLBACK: 0
SEG_OWNER:
SEG_NAME:
TABLE_NAME:
SEG_TYPE: 64
SEG_TYPE_NAME:
TABLE_SPACE:
ROW_ID: AAAAAAAAAAAAAAAAAB
USERNAME: UNKNOWN
OS_USERNAME: UNKNOWN
MACHINE_NAME: UNKNOWN
AUDIT_SESSIONID: 0
SESSION#: 0
SERIAL#: 0
SESSION_INFO: UNKNOWN
THREAD#: 1
SEQUENCE#: 2
RBASQN: 53
RBABLK: 4258
RBABYTE: 416
UBAFIL: 3
UBABLK: 0
UBAREC: 0
UBASQN: 0
ABS_FILE#: 0
REL_FILE#: 0
DATA_BLK#: 0
DATA_OBJ#: 0
DATA_OBJV#: 0
DATA_OBJD#: 0
SQL_REDO: alter database datafile 6 offline;
SQL_UNDO:
RS_ID: 0x000035.000010a2.01a0
SSN: 0
CSF: 0
INFO: USER DDL (PlSql=0 RecDep=0)
STATUS: 0
REDO_VALUE: 2
UNDO_VALUE: 3
SAFE_RESUME_SCN:
CSCN:
OBJECT_ID:
EDITION_NAME:
CLIENT_ID:
--scn=1987853
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------- --------------------------------------------------
6 1961394 2016-10-18 12:00:07 1731053 2016-10-12 08:59:30 1987850 2016-10-19 08:42:56 0 0 RECOVER /mnt/ramdisk/book/sugar01.dbf
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
6 1961394 2016-10-18 12:00:07 1730665 925702 OFFLINE 32 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
--而控制文件里面记录的LAST_CHANGE#=1987850.存在一点点差异，与前面的logminer记录相差3.不知道为什么?
RMAN> recover datafile 6 ;
Starting recover at 2016-10-19 08:54:57
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-10-19 08:54:57
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------- --------------------------------------------------
6 1987850 2016-10-19 08:42:56 1731053 2016-10-12 08:59:30 1987850 2016-10-19 08:42:56 0 0 OFFLINE /mnt/ramdisk/book/sugar01.dbf
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
6 1987850 2016-10-19 08:42:56 1730665 925702 OFFLINE 33 NO /mnt/ramdisk/book/sugar01.dbf SUGAR
--recover后，CHECKPOINT_CHANGE#=1987850,也就是recover 仅仅需要恢复到LAST_CHANGE#=1731053.
SCOTT@book> alter database datafile 6 online ;
Database altered.
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------- --------------------------------------------------
6 1988406 2016-10-19 08:58:46 1731053 2016-10-12 08:59:30 0 0 ONLINE /mnt/ramdisk/book/sugar01.dbf
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
6 1988406 2016-10-19 08:58:46 1730665 925702 ONLINE 34 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
--online后，LAST_CHANGE#信息清除。
3.做一个测试看看,这个也是别人问的问题，就是offline后，一些事务rollback会怎样?
--session 1:
SCOTT@book(90,157)> create table DEMO (id number, name varchar2(20)) tablespace sugar;
Table created.
insert into DEMO values (1,'a');
insert into DEMO values (2,'b');
commit ;
SCOTT@book(90,157)> select rowid,demo.* from demo;
ROWID ID NAME
------------------ ------------ ----
AAAVqfAAGAAAACFAAA 1 a
AAAVqfAAGAAAACFAAB 2 b
SCOTT@book(90,157)> @ &r/rowid AAAVqfAAGAAAACFAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
88735 6 133 0 0x1800085 6,133 alter system dump datafile 6 block 133 ;
SCOTT@book(90,157)> update demo set name='AAA' where id=1;
1 row updated.
--不提交，打开另外的会话offline。session 2:
SCOTT@book(46,69)> @ x1
SCN
----------
1988698
Database altered.
SCN
----------
1988708
--session 1:
SCOTT@book(90,157)> rollback ;
rollback
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
Process ID: 57177
Session ID: 90 Serial number: 157
--可以发现这个时候执行rollback，要访问数据文件，由于offline数据文件，报错，事务rollback失败。也就是这个事务没有成功。
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------- --------------------------------------------------
6 1988406 2016-10-19 08:58:46 1731053 2016-10-12 08:59:30 1988699 2016-10-19 09:06:01 0 0 RECOVER /mnt/ramdisk/book/sugar01.dbf
--再次验证看看是否recover到scn=LAST_CHANGE#=1988699.
RMAN> recover datafile 6 ;
Starting recover at 2016-10-19 09:09:55
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-10-19 09:09:55
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------- --------------------------------------------------
6 1988699 2016-10-19 09:06:01 1731053 2016-10-12 08:59:30 1988699 2016-10-19 09:06:01 0 0 OFFLINE /mnt/ramdisk/book/sugar01.dbf
--确实recover仅仅恢复到LAST_CHANGE#。
SCOTT@book> alter database datafile 6 online ;
Database altered.
SCOTT@book> select rowid,demo.* from demo;
ROWID ID NAME
------------------ ---------- --------------------
AAAVqfAAGAAAACFAAA 1 a
AAAVqfAAGAAAACFAAB 2 b
总结：
1.数据文件offline，最好随手执行一次recover，或者之前就做一个检查点。如果仅仅仅仅属于一个表空间对应一个数据文件，可以offline表空间，这样不需要recover。
2.要online，仅仅恢复到控制文件记录的LAST_CHANGE#的scn值。