[20160406] 恢复until scn NNN.txt
--昨天别人问的问题,如果使用rman恢复,restore database until scn NNN;是恢复到NNN,还是NNN-1.
--我个人的理解应该是NNN-1.包括像UNTIL SEQUENCE integer 以及UNTIL TIME xxx;也是少1个或者少1秒.
--实际上我以前如果做测试,我自己总是查询误操作的scn,然后仅仅恢复到减去1的scn号.(感觉这样比较保险^_^)
until
prep.到...为止, 在...以前
conj.到...为止, 在...以前, 直到...才
--即使查词霸也没有提示until是否包括这点.如果是"到...为止"我感觉应该包括这点.如果是"在...以前",感觉应该是不包括这点.
--还是通过测试说明问题.
1.环境:
SCOTT@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
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name BOOK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /mnt/ramdisk/book/system01.dbf
2 1580 SYSAUX *** /mnt/ramdisk/book/sysaux01.dbf
3 1435 UNDOTBS1 *** /mnt/ramdisk/book/undotbs01.dbf
4 500 USERS *** /mnt/ramdisk/book/users01.dbf
5 346 EXAMPLE *** /mnt/ramdisk/book/example01.dbf
6 100 SUGAR *** /mnt/ramdisk/book/sugar01.dbf
7 1 TEA *** /mnt/ramdisk/book/tea01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 400 TEMP 32767 /mnt/ramdisk/book/temp01.dbf
RMAN> backup database format '/home/oracle/backup/full0406_%u' ;
RMAN> backup archivelog all format '/home/oracle/backup/archive0406_%u' ;
SCOTT@book> select * from scott.empx where rownum<=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 ZZZZ CLERK 7902 1980-12-17 00:00:00 800 20
SCOTT@book> select * from dba_extents where owner=user and segment_name='EMPX';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT EMPX TABLE TEA 0 7 128 65536 8 7
--表EMPX在表空间TEA.
SCOTT@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 28 52428800 512 1 YES INACTIVE 13227540144 2016-04-05 22:00:10 13227562225 2016-04-06 08:05:56 1 ONLINE /mnt/ramdisk/book/redo01.log NO
2 1 29 52428800 512 1 YES INACTIVE 13227562225 2016-04-06 08:05:56 13227565684 2016-04-06 09:23:33 2 ONLINE /mnt/ramdisk/book/redo02.log NO
3 1 30 52428800 512 1 NO CURRENT 13227565684 2016-04-06 09:23:33 2.814750E+14 3 ONLINE /mnt/ramdisk/book/redo03.log NO
-当前日志是seq=30.
SCOTT@book> drop table empx purge ;
Table dropped.
SCOTT@book> alter system archive log current ;
System altered.
2.通过logminer确定drop的时间:
SELECT SCN
,timestamp "Time Stamp"
,seg_type_name "Segment Type Name"
,seg_owner "Segment Owner"
,seg_name "Segment Name"
,table_space "Tablespace"
,abs_file# "Abs File #"
,row_id "Row ID"
,session# "Session #"
,serial# "Serial #"
,session_info "Session Info"
,username "Username"
,operation "Operation"
,sql_redo "SQL Redo"
,sql_undo "SQL Undo"
,Info "Info"
,status "Status"
FROM V$LOGMNR_CONTENTS
WHERE seg_name = 'EMPX'
ORDER BY 1;
SCN: 13227565841
Time Stamp: 2016/04/06 09:26:36
Segment Type Name: TABLE
Segment Owner: SCOTT
Segment Name: EMPX
Tablespace:
Abs File #: 0
Row ID: AAAAAAAAAAAAAAAAAB
Session #: 0
Serial #: 0
Session Info: UNKNOWN
Username: UNKNOWN
Operation: DDL
SQL Redo: drop table empx purge ;
SQL Undo:
Info: USER DDL (PlSql=0 RecDep=0)
Status: 0
--记下drop时候的scn号以及时间.
3.测试.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ mv book book_0406
$ cd /mnt/ramdisk/
$ mkdir book
$ ll -l /home/oracle/backup
total 2181696
-rw-r----- 1 oracle oinstall 2564096 2016-04-06 09:23:33 archive0406_4ur2bfol
-rw-r----- 1 oracle oinstall 2219237376 2016-04-06 09:23:00 full0406_4sr2bfng
-rw-r----- 1 oracle oinstall 10059776 2016-04-06 09:23:12 full0406_4tr2bfnv
RMAN> startup nomount
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
RMAN> restore controlfile from '/home/oracle/backup/full0406_4tr2bfnv' ;
Starting restore at 2016-04-06 09:40:33
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=254 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/mnt/ramdisk/book/control01.ctl
output file name=/mnt/ramdisk/book/control02.ctl
Finished restore at 2016-04-06 09:40:34
RMAN> alter database mount ;
database mounted
released channel: ORA_DISK_1
4.恢复使用until seq方式:
--我跳过不需要恢复的文件,这样能节省磁盘空间以及恢复时间.仅仅需要恢复3个表空间system,tea,untotbs1.
run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
set until sequence 30;
restore database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
recover database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
release channel c1;
release channel c2;
}
--摘录恢复过程其中一段:
archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/archivelog/book/1_29_907434361.dbf
archived log file name=/u01/app/oracle/archivelog/book/1_29_907434361.dbf thread=1 sequence=29
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-04-06 09:46:30
--可以发现恢复不包括sequence 30.
SYS@book> alter database open read only ;
Database altered.
SYS@book> select * from scott.empx where rownum<=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 ZZZZ CLERK 7902 1980-12-17 00:00:00 800 20
--如果恢复到sequence 30,应该看不到上面信息.
5.恢复使用until scn方式:
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
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.
run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
set until scn 13227565841;
restore database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
recover database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
release channel c1;
release channel c2;
}
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,3,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ----------------
1 13227565841 2016-04-06 09:26:36 7 13227286650 ONLINE 1020 NO /mnt/ramdisk/book/system01.dbf SYSTEM
3 13227565841 2016-04-06 09:26:36 923328 13227286650 ONLINE 935 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
7 13227565841 2016-04-06 09:26:36 13227207527 13227286650 ONLINE 43 NO /mnt/ramdisk/book/tea01.dbf TEA
--这个视乎给人的感觉视乎是到了 CHECKPOINT_CHANGE#=13227565841.时间也恢复到了2016-04-06 09:26:36.(注:我个人不喜欢until time).
SYS@book> alter database open read only ;
Database altered.
SYS@book> select * from scott.empx where rownum<=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
7369 ZZZZ CLERK 7902 1980-12-17 00:00:00 800 20
--scn再加1看看:
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
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.
run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
set until scn 13227565842;
# restore database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
recover database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
release channel c1;
release channel c2;
}
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,3,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ----------------
1 13227565842 2016-04-06 09:26:36 7 13227286650 ONLINE 1020 NO /mnt/ramdisk/book/system01.dbf SYSTEM
3 13227565842 2016-04-06 09:26:36 923328 13227286650 ONLINE 935 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
7 13227565842 2016-04-06 09:26:36 13227207527 13227286650 ONLINE 43 NO /mnt/ramdisk/book/tea01.dbf TEA
SYS@book> alter database open read only ;
Database altered.
SYS@book> select * from scott.empx where rownum<=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
7369 ZZZZ CLERK 7902 1980-12-17 00:00:00 800 20
--奇怪吗?数据还在?
--scn再加1看看,测试结果数据还在.
--采用until time看看.注意这样写要定义环境变量
$ env | grep NLS
NLS_LANG=AMERICAN_AMERICA.zhs16gbk
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
set until time '2016-04-06 09:26:37';
# restore database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
recover database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
release channel c1;
release channel c2;
}
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,3,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ---------------
1 13227565859 2016-04-06 09:26:39 7 13227286650 ONLINE 1020 NO /mnt/ramdisk/book/system01.dbf SYSTEM
3 13227565859 2016-04-06 09:26:39 923328 13227286650 ONLINE 935 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
7 13227565859 2016-04-06 09:26:39 13227207527 13227286650 ONLINE 43 NO /mnt/ramdisk/book/tea01.dbf TEA
--注意看时间实际上恢复到"2016-04-06 09:26:39".与实际的不同,我个人建议不要使用时间方式.scn=13227565859.
SYS@book> alter database open read only ;
Database altered.
SYS@book> select * from scott.empx where rownum<=1;
select * from scott.empx where rownum<=1
*
ERROR at line 1:
ORA-00942: table or view does not exist
--我最终测试恢复到scn 13227565849还可以看到数据.scn 13227565850就不行了.
run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
set until scn 13227565849;
restore database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
recover database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
release channel c1;
release channel c2;
}
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,3,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ----------------
1 13227565849 2016-04-06 09:26:36 7 13227286650 ONLINE 1020 NO /mnt/ramdisk/book/system01.dbf SYSTEM
3 13227565849 2016-04-06 09:26:36 923328 13227286650 ONLINE 935 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
7 13227565849 2016-04-06 09:26:36 13227207527 13227286650 ONLINE 43 NO /mnt/ramdisk/book/tea01.dbf TEA
SYS@book> alter database open read only ;
Database altered.
SYS@book> select * from scott.empx where rownum<=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
7369 ZZZZ CLERK 7902 1980-12-17 00:00:00 800 20
run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
set until scn 13227565850;
restore database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
recover database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
release channel c1;
release channel c2;
}
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,3,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
1 13227565850 2016-04-06 09:26:36 7 13227286650 ONLINE 1020 NO /mnt/ramdisk/book/system01.dbf SYSTEM
3 13227565850 2016-04-06 09:26:36 923328 13227286650 ONLINE 935 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
7 13227565850 2016-04-06 09:26:36 13227207527 13227286650 ONLINE 43 NO /mnt/ramdisk/book/tea01.dbf TEA
SYS@book> alter database open read only ;
Database altered.
SYS@book> select * from scott.empx where rownum<=1;
select * from scott.empx where rownum<=1
*
ERROR at line 1:
ORA-00942: table or view does not exist
--感觉越做越糊涂.总之选择scn比较准确.觉得应该还是以查询到的v$datafile_header为准.
--补充做until time的情况:
run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
set until time '2016-04-06 09:26:36';
restore database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
recover database skip forever tablespace SYSAUX,USERS,EXAMPLE,SUGAR;
release channel c1;
release channel c2;
}
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,3,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
1 13227565836 2016-04-06 09:26:36 7 13227286650 ONLINE 1020 NO /mnt/ramdisk/book/system01.dbf SYSTEM
3 13227565836 2016-04-06 09:26:36 923328 13227286650 ONLINE 935 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
7 13227565836 2016-04-06 09:26:36 13227207527 13227286650 ONLINE 43 NO /mnt/ramdisk/book/tea01.dbf TEA
--可以发现以时间恢复,scn=13227565836,更加靠前.这个可以更加确定应该不包括这个时间点.
--总结:
--做不完全恢复,选择scn为准.这样比较准确.选择时间可能存在一定的偏差.
--until sequence NNN,仅仅恢复到NNN-1.
--until scn NNN,我自己的理解应该还是在NNN-1之前.不过还是以查看视图v$datafile_header为准吧.
--until time 'XXX',存在一定的不可控因素,最好不要选择.
