[20161019]数据文件offline后恢复到那个scn

简介: [20161019]数据文件offline后恢复到那个scn号.txt --前一天别人问的问题,如果数据文件offline时,online要恢复,一般恢复到scn是多少,是offline时的scn吗? --总不见得如果长时间offline,要应用许多归档日志吧,通过测试说明问题: 1.

[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值。

目录
相关文章
|
Oracle 关系型数据库 SQL
[20180423]表空间闪回与snapshot standby
[20180423]flashback tablespace与snapshot standby.txt --//缺省建立表空间是打开flashback on,如果某个表空间flashback off,在dg启动snapshot standby时注意,可能"回不来", --//通过测试说明问题.
1270 0
|
测试技术 数据库
[20180202]备库数据文件offline.txt
[20180202]备库数据文件offline.txt --//今天测试,不小心导致日志无法应用.我想把主库文件拷贝过去,做了备库数据文件offline. --//恢复遇到问题,做一个记录.
1264 0
|
Oracle 关系型数据库 数据库
[20161012]数据文件offline马上执行recover
[20161012]数据文件offline马上执行recover.txt --前几天看的1篇文章,提到数据文件offline,应该养成随手执行recover习惯.保证下一次online时,不需要恢复。
826 0
|
Oracle 关系型数据库 数据库
[20160721]rman与undo表空间备份.txt
[20160721]rman与undo表空间备份.txt --//UNDO表空间主要用于存储前镜像数据,这些数据在回滚以及恢复过程中可能被用到。 --//一般生产数据库的UNDO表空间可能会变得非常巨大,甚至包括多个数据文件,而备份完整的UNDO数据文件在恢复时一般可能用到的比 --//例很小。
851 0
|
SQL Oracle 关系型数据库
重建控制文件时resetlogs与noresetlogs的使用情况
重建控制文件时resetlogs与noresetlogs的使用情况 控制文件中记录着数据库的数据文件,日志文件,备份数据等信息,更为重要的,控制文件中还记录了数据库的检查点 和scn信息,这些信息在数据恢复的过程中将起到关键性作用.
734 0