当前位置： 首页 > BACKUP & RESTORE > 正文

ORACLE不完全恢复测试

huangtingzhong 发布于 2014-11-08 22:53BACKUP & RESTORE抢沙发

       在生产环境中，常常会遇到不完全恢复的情况。这里QQ群里朋友要求，测试使用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 测试环境

www.cdhtz.com > 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

 

www.cdhtz.com > !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 全备数据库

www.cdhtz.com > 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

 

www.cdhtz.com > 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的终点

www.cdhtz.com > select current_scn from v$database;

CURRENT_SCN

———–

   33871144

www.cdhtz.com > select count(*) from scott.htz;

  COUNT(*)

———-

     74724

www.cdhtz.com > delete scott.htz where rownum<1000;

999 rows deleted.

www.cdhtz.com > commit;

Commit complete.

www.cdhtz.com > select count(*) from scott.htz;

  COUNT(*)

———-

     73725

这里看到当前的日志序列号是1345，下面还原的时候，要求还原到1345结局。

www.cdhtz.com > 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

www.cdhtz.com > alter system switch logfile;

System altered.

www.cdhtz.com >  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，日志序列号对应日志中记录不会被应用。

www.cdhtz.com > !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-14

 

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’

 

www.cdhtz.com > 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
赏

您可能还会对这些文章感兴趣！

ORACLE不完全恢复测试：等您坐沙发呢！

发表评论

gravatar

? razz sad evil ! smile oops grin eek shock ??? cool lol mad twisted roll wink idea arrow neutral cry mrgreen

快捷键：Ctrl+Enter