天萃荷净
Oracle研究中心学习笔记：分享一篇关于Oracle数据库不完全恢复的笔记，详细记录使用Oracle set until命令进行指定恢复点进行恢复测试。
在生产环境中，常常会遇到不完全恢复的情况。测试使用set until命令的不完全恢复，此功能在8I中就已经增加。
语法如下：
UNTIL SCN integer | UNTIL SEQUENCE integer [THREAD integer] | UNTIL TIME ‘ date_string ‘
Semantics
Syntax Element
Description
UNTIL SCN integer
Specifies an SCN as an upper, noninclusive limit.
RMAN selects only files that it can use to restore or recover up to but not including the specified SCN (see Example 4-38). For example, RESTORE DATABASE UNTIL SCN 1000 chooses only backups that could be used to recover to SCN 1000.
UNTIL SEQUENCE integer
Specifies a redo log sequence number and thread as an upper, noninclusive limit.
RMAN selects only files that it can use to restore or recover up to but not including the specified sequence number. For example, REPORT OBSOLETE UNTIL SEQUENCE 8000 reports only backups that could be used to recover through log sequence 7999.
THREAD integer
Specifies the number of the redo thread.
UNTIL TIME ‘date_string’
Specifies a time as an upper, noninclusive limit (see Example 4-39).
RMAN selects only files that it can use to restore and recover up to but not including the specified time. For example, LIST BACKUP UNTIL TIME ‘SYSDATE-7’ lists all backups that could be used to recover to a point one week ago.
When specifying dates in RMAN commands, the date string must be either:
A literal string whose format matches the NLS_DATE_FORMAT setting.
A SQL expression of type DATE, for example, ‘SYSDATE-10′ or “TO_DATE(’01/30/2007’, ‘MM/DD/YYYY’)”. The second example includes its own date format mask and so is independent of the current NLS_DATE_FORMAT setting.
Following are examples of typical date settings in RMAN commands:
BACKUP ARCHIVELOG FROM TIME ‘SYSDATE-31’ UNTIL TIME ‘SYSDATE-14′;
RESTORE DATABASE UNTIL TIME “TO_DATE(’09/20/06′,’MM/DD/YY’)”;
Note: The granularity of time-based recovery is dependent on time stamps in the redo log. For example, suppose that you specify the following command:
RECOVER DATABASE UNTIL TIME ‘2007-07-26 17:45:00’;
If no redo was written with a time stamp of 17:45:00, then recovery proceeds until it finds the next redo time stamp that is higher. For example, the next redo time stamp may be 17:45:04. You can check for the nearest time for a specific SCN by querying the FIRST_TIME and FIRST_CHANGE# columns in V$LOG_HISTORY TABLE.
1 测试环境
oracleplus.net> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production
oracleplus.net> !uname -a
Linux orcl9i 2.6.9-89.EL #1 Mon Apr 20 10:22:29 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
2 全备数据库
oracleplus.net> select thread#,sequence#,status,first_change#,next_change# from v$log;
THREAD# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
———- ———- —————- ————- ————
1 1342 INACTIVE 33869876 33869884
1 1343 CURRENT 33869884 2.8147E+14
1 1341 INACTIVE 33865263 33869876
下面是RMAN备份脚本，此脚本请不要用于生产环境，
RMAN> RUN {
2> ALLOCATE CHANNEL ch00 TYPE DISK;
3> BACKUP
4> SKIP INACCESSIBLE
5> TAG hot_db_bk_level0
6> FORMAT ‘/soft/rman/bk_%s_%p_%t’
7> DATABASE;
8> sql ‘alter system archive log current’;
9> BACKUP
10> FORMAT ‘/soft/rman/ar_%s_%p_%t’
11> ARCHIVELOG ALL DELETE INPUT;
12> BACKUP
13> FORMAT ‘/soft/rman/sp_%s_%p_%t’
14> SPFILE;
15> BACKUP
16> FORMAT ‘/soft/rman/con_%s_%p_%t’
17> CURRENT CONTROLFILE;
18> RELEASE CHANNEL ch00;
19> }
released channel: ORA_DISK_1
allocated channel: ch00
channel ch00: SID=1 device type=DISK
Starting backup at 07-NOV-14
channel ch00: starting full datafile backup set
channel ch00: specifying datafile(s) in backup set
input datafile file number=00006 name=/oracle/app/oracle/oradata/orcl1123/htz01.dbf
input datafile file number=00001 name=/oracle/app/oracle/oradata/orcl1123/system01.dbf
input datafile file number=00002 name=/oracle/app/oracle/oradata/orcl1123/sysaux01.dbf
input datafile file number=00004 name=/oracle/app/oracle/oradata/orcl1123/undo2.dbf
input datafile file number=00005 name=/oracle/app/oracle/oradata/orcl1123/undotbs2.dbf
channel ch00: starting piece 1 at 07-NOV-14
channel ch00: finished piece 1 at 07-NOV-14
piece handle=/soft/rman/bk_70_1_863008947 tag=HOT_DB_BK_LEVEL0 comment=NONE
channel ch00: backup set complete, elapsed time: 00:02:35
channel ch00: starting full datafile backup set
channel ch00: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch00: starting piece 1 at 07-NOV-14
channel ch00: finished piece 1 at 07-NOV-14
piece handle=/soft/rman/bk_71_1_863009102 tag=HOT_DB_BK_LEVEL0 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:01
Finished backup at 07-NOV-14
sql statement: alter system archive log current
Starting backup at 07-NOV-14
current log archived
channel ch00: starting archived log backup set
channel ch00: specifying archived log(s) in backup set
input archived log thread=1 sequence=1343 RECID=292 STAMP=863009104
input archived log thread=1 sequence=1344 RECID=293 STAMP=863009104
channel ch00: starting piece 1 at 07-NOV-14
channel ch00: finished piece 1 at 07-NOV-14
piece handle=/soft/rman/ar_72_1_863009105 tag=TAG20141107T124504 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:01
channel ch00: deleting archived log(s)
archived log file name=/oracle/nbu/1_1343_849959296.dbf RECID=292 STAMP=863009104
archived log file name=/oracle/nbu/1_1344_849959296.dbf RECID=293 STAMP=863009104
Finished backup at 07-NOV-14
Starting backup at 07-NOV-14
channel ch00: starting full datafile backup set
channel ch00: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch00: starting piece 1 at 07-NOV-14
channel ch00: finished piece 1 at 07-NOV-14
piece handle=/soft/rman/sp_73_1_863009106 tag=TAG20141107T124506 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:01
Finished backup at 07-NOV-14
Starting backup at 07-NOV-14
channel ch00: starting full datafile backup set
channel ch00: specifying datafile(s) in backup set
including current control file in backup set
channel ch00: starting piece 1 at 07-NOV-14
channel ch00: finished piece 1 at 07-NOV-14
piece handle=/soft/rman/con_74_1_863009107 tag=TAG20141107T124507 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:03
Finished backup at 07-NOV-14
released channel: ch00
oracleplus.net> select thread#,sequence#,status,first_change#,next_change# from v$log;
THREAD# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
———- ———- —————- ————- ————
1 1345 CURRENT 33870229 2.8147E+14
1 1343 INACTIVE 33869884 33870221
1 1344 INACTIVE 33870221 33870229
这里看到全备完成后，当前的日志序列号是1345。
3 对表进行操作，用于验证UNTIL的终点
oracleplus.net> select current_scn from v$database;
CURRENT_SCN
———–
33871144
oracleplus.net> select count(*) from scott.htz;
COUNT(*)
———-
74724
oracleplus.net> delete scott.htz where rownum<1000;
999 rows deleted.
oracleplus.net> commit;
Commit complete.
oracleplus.net> select count(*) from scott.htz;
COUNT(*)
———-
73725
这里看到当前的日志序列号是1345，下面还原的时候，要求还原到1345结局。
oracleplus.net> select thread#,sequence#,status,first_change#,next_change# from v$log;
THREAD# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
———- ———- —————- ————- ————
1 1345 CURRENT 33870229 2.8147E+14
1 1343 INACTIVE 33869884 33870221
1 1344 INACTIVE 33870221 33870229
oracleplus.net> alter system switch logfile;
System altered.
oracleplus.net> select thread#,sequence#,status,first_change#,next_change# from v$log;
THREAD# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
———- ———- —————- ————- ————
1 1345 ACTIVE 33870229 33871179
1 1346 CURRENT 33871179 2.8147E+14
1 1344 INACTIVE 33870221 33870229
开始备份
RMAN> RUN {
2> ALLOCATE CHANNEL ch00 TYPE DISK;
3> sql ‘alter system archive log current’;
4> BACKUP
5> FORMAT ‘/soft/rman/ar_%s_%p_%t’
6> ARCHIVELOG ALL DELETE INPUT;
7> BACKUP
8> FORMAT ‘/soft/rman/con_%s_%p_%t’
9> CURRENT CONTROLFILE;
10> RELEASE CHANNEL ch00;
11> }
4 基于归档序列号的不完全恢复
ORACLE在内部都是以SCN为时钟，如果在不完全恢复的时候，指定UNTIL SEQUENCE,UNTIL TIME都会转为SCN的。
这里UNTIL SEQUENCE的日志序列号需要注意如下内容：
1，日志序列号对应日志中最小SCN不能小于RESTORE还原的数据文件中最大的SCN。
2，日志序列号必须在还原的控制文件中存在。
3，日志序列号对应日志中记录不会被应用。
oracleplus.net> !rman target /
Recovery Manager: Release 11.2.0.3.0 – Production on Fri Nov 7 13:53:02 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL1123 (not mounted)
RMAN> restore controlfile from ‘/soft/rman/con_76_1_863009936’;
Starting restore at 07-NOV-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oracle/app/oracle/oradata/orcl1123/control01.ctl
output file name=/oracle/app/oracle/fast_recovery_area/orcl1123/control02.ctl
Finished restore at 07-NOV-14
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> run{
2> ALLOCATE CHANNEL ch00 TYPE DISK;
3> set until sequence 1345;
4> restore database;
5> recover database;
6> }
allocated channel: ch00
channel ch00: SID=20 device type=DISK
executing command: SET until clause
Starting restore at 07-NOV-14
Starting implicit crosscheck backup at 07-NOV-14
Crosschecked 27 objects
Finished implicit crosscheck backup at 07-NOV-14
Starting implicit crosscheck copy at 07-NOV-14
Finished implicit crosscheck copy at 07-NOV-14
searching for all files in the recovery area
cataloging files…
no files cataloged
channel ch00: starting datafile backup set restore
channel ch00: specifying datafile(s) to restore from backup set
channel ch00: restoring datafile 00001 to /oracle/app/oracle/oradata/orcl1123/system01.dbf
channel ch00: restoring datafile 00002 to /oracle/app/oracle/oradata/orcl1123/sysaux01.dbf
channel ch00: restoring datafile 00004 to /oracle/app/oracle/oradata/orcl1123/undo2.dbf
channel ch00: restoring datafile 00005 to /oracle/app/oracle/oradata/orcl1123/undotbs2.dbf
channel ch00: restoring datafile 00006 to /oracle/app/oracle/oradata/orcl1123/htz01.dbf
channel ch00: reading from backup piece /soft/rman/bk_70_1_863008947
channel ch00: piece handle=/soft/rman/bk_70_1_863008947 tag=HOT_DB_BK_LEVEL0
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:01:45
Finished restore at 07-NOV-14
Starting recover at 07-NOV-14
starting media recovery
channel ch00: starting archived log restore to default destination
channel ch00: restoring archived log
archived log thread=1 sequence=1343
channel ch00: restoring archived log
archived log thread=1 sequence=1344
channel ch00: reading from backup piece /soft/rman/ar_72_1_863009105
channel ch00: piece handle=/soft/rman/ar_72_1_863009105 tag=TAG20141107T124504
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:00:01
archived log file name=/oracle/nbu/1_1343_849959296.dbf thread=1 sequence=1343
archived log file name=/oracle/nbu/1_1344_849959296.dbf thread=1 sequence=1344
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-NOV-14
released channel: ch00
这里看到日志运用1344结束，说明1345是没有被运用的。
5 基于SCN的不完全恢复
这里官方文档介绍的是运行日志到SCN-1。官方例子如下：
STARTUP FORCE MOUNT
RUN
{
SET UNTIL SCN 1418901;# set to 1418901 to recover database through SCN 1418900
RESTORE DATABASE;
RECOVER DATABASE;
}
但是如下测试发生并不是SCN-1，而是SCN。
RMAN> run{
2> ALLOCATE CHANNEL ch00 TYPE DISK;
3> set UNTIL scn 33870222;
restore database;
recover database;
4> 5> 6> }
allocated channel: ch00
channel ch00: SID=20 device type=DISK
executing command: SET until clause
Starting restore at 07-NOV-1
channel ch00: starting datafile backup set restore
channel ch00: specifying datafile(s) to restore from backup set
channel ch00: restoring datafile 00001 to /oracle/app/oracle/oradata/orcl1123/system01.dbf
channel ch00: restoring datafile 00002 to /oracle/app/oracle/oradata/orcl1123/sysaux01.dbf
channel ch00: restoring datafile 00004 to /oracle/app/oracle/oradata/orcl1123/undo2.dbf
channel ch00: restoring datafile 00005 to /oracle/app/oracle/oradata/orcl1123/undotbs2.dbf
channel ch00: restoring datafile 00006 to /oracle/app/oracle/oradata/orcl1123/htz01.dbf
channel ch00: reading from backup piece /soft/rman/bk_70_1_863008947
channel ch00: piece handle=/soft/rman/bk_70_1_863008947 tag=HOT_DB_BK_LEVEL0
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:01:35
Finished restore at 07-NOV-14
Starting recover at 07-NOV-14
starting media recovery
archived log for thread 1 with sequence 1343 is already on disk as file /oracle/nbu/1_1343_849959296.dbf
archived log for thread 1 with sequence 1344 is already on disk as file /oracle/nbu/1_1344_849959296.dbf
archived log file name=/oracle/nbu/1_1343_849959296.dbf thread=1 sequence=1343
archived log file name=/oracle/nbu/1_1344_849959296.dbf thread=1 sequence=1344
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-NOV-14
released channel: ch00
在alert中看到下面的信息
alter database recover logfile ‘/oracle/nbu/1_1344_849959296.dbf’
Media Recovery Log /oracle/nbu/1_1344_849959296.dbf
Incomplete Recovery applied until change 33870222 time 11/07/2014 12:45:04
Media Recovery Complete (orcl1123)
Completed: alter database recover logfile ‘/oracle/nbu/1_1344_849959296.dbf’
oracleplus.net> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
——————
33870222
33870222
33870222
33870222
33870222
这里看到数据文件头中的SCN是SET UNTIL SCN中接的值，并不是SCN-1
6 打开数据库
这里需要使用open resetlogs方式来打开数据库，其实也可以不能OPEN RESETLOGS方式打开数据库，但是需要重建控制文件
RMAN> alter database open resetlogs;
database opened
本文固定链接: http://www.htz.pw/2014/11/08/oracle%e4%b8%8d%e5%ae%8c%e5%85%a8%e6%81%a2%e5%a4%8d%e6%b5%8b%e8%af%95.html | 认真就输
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle不完全恢复之使用set until命令指定恢复点
218
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
