ITPub博客

首页 > 数据库 > Oracle > RMAN表空间按时间点恢复(一)

RMAN表空间按时间点恢复(一)

Oracle 作者:Otakuzel 时间:2016-03-13 00:27:25 0 删除 编辑

一.表空间按时间点恢复
RMAN的自动表空间按时间点恢复(TSPITR)能让你将数据库中的一个或多个表空间快速恢复到之前的某个时间点,而不会影响数据库中的其它表空间和对象。

为了有效地使用表空间按时间点恢复(TSPITR),需要理解你需要解决的问题是什么,使用TSPITR的主要原因是什么,在执行TSPITR时RMAN将会执行那些操作,何时以及如何使用TSPITR,使用TSPITR有那些限制。

二.执行TSPITR的操作步骤
为了使用RMAN和自动辅助实例来对恢复集执行TSPITR,需要对TSPITR进行一些准备工作,然后执行recover tablespace命令来指定要恢复的表空间和按时间点恢复的目标时间和辅助目标(如果需要的话)。
执行TSPITR,RMAN将会执行以下步骤:
1.如果没有连接到辅助实例,RMAN将创建一个辅助实例,启动并连接到辅助实例。
2.在目标数据库中将要被恢复的表空间置于脱机状态。
3.对辅助实例使用恢复目标时间之前的控制文件备份来还原控制文件。
4.从恢复集和辅助集中将数据文件还原到辅助实例。还原的数据文件可以存储在你指定的目录中或者是文件的原始目录中(对于恢复集文件)或者在辅助目录中(对于辅助集文件,如果你使用了有auxliary destination参数的recover tablespace命令)。
5.在辅助实例中将数据文件还原和恢复到指定的目标时间点。
6.使用resetlogs选项将辅助实例打开
7.导出目标数据库中要恢复表空间的数据字典元数据
8.关闭辅助实例
9.在目标数据库中执行switch命令,因为目标数据库的控制文件现在指示的是在辅助实例中被恢复的恢复集中的数据文件
10.将辅助数据库实的数据字典元数据导入到目标数据库中让恢复的对象可以被访问。
11.删除所有辅助集数据文件

执行完以上步骤,TSPITR处理就已经完成了。对于目标数据库恢复集中的数据文件已经被返回到指定的恢复目标时间点。

三.TSPITR的执行方式
选择好要恢复的表空间和恢复目标时间后,就可以执行RMAN的TSPITR。在执行TSPITR时可以有以下不同的选择:
1.完全自动执行TSPITR--在指定辅助目录后就可以让RMAN来管理TSPITR的所有方面。这是执行TSPITR最简单的方式,除非你需要在执行TSPITR后更好的控制恢复集文件的目录或者在执行TSPITR时控制辅助集文件或者控制通道配置或者控制辅助实例的一些其它方面,否则建议使用这种方式来执行TSPITR。
2.使用自定义自动辅助实例执行TSPITR--在完全自动TSPITR的基础上,自定义一个或多个方面的RMAN行为,比如辅助集目录或者恢复集文件,或者指定初始化参数或者辅助实例的通道配置等。
3.使用自己的辅助实例执行TSPITR--在这种情况下要手动设置,启动,停止和清除辅助实例,也可以使用自动辅助实例自定义TSPITR中的一些方法来管理TSPITR。

完全自动执行TSPITR
当完全自动执行TSPITR时,让RMAN来管理整个过程,并且有两个条件:
1.必须给RMAN指定辅助目录让辅助实例的辅助集数据文件和其它文件使用。
2.必须在目标实例上配置任何需要的通道(当目标实例执行TSPITR时,辅助实例使用相同的通道配置)。
对于TSPITR的基础配置在目标数据库上进行。当执行TSPITR时,恢复集数据文件会被写到目标数据库的当前目录。当从备份中还原文件时在目标数据库上有效的相同配置通道将被辅助实例所用。辅助集数据文件和其它辅助实例文件,会被存储在辅助目录中。
自动TSPITR的辅助目录
oracle建议在使用辅助实例时使用辅助目录。即使你使用其它的方法来重命名一些或者所有的辅助集数据文件,通过设置auxliary destination参数来为没有指定名称的辅助集数据文件提供一个默认的目录。为了指定辅助目录,在磁盘上找到有足够空间的目录来处理辅助集数据文件。在recover tablespace命令中使用auxliary destination参数来指定辅助目录。
这里我使用oracle 10g进行完全自动执行TSPITR的实验,下面来准备实验环境:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

创建实验表空间tspitr,用户tspitr,实验表tspitr:

SQL> create tablespace   tspitr datafile '/u01/app/oracle/oradata/test/tspitr01.dbf' size 100M autoextend off extent management local segment space management auto;

Tablespace created.

SQL> create user tspitr identified by "tspitr" default tablespace tspitr temporary tablespace temp;

User created.

SQL> grant connect,resource to tspitr;

Grant succeeded.

这里为了实例的方便使用给tspitr用户授予了DBA权限

SQL> grant dba to tspitr;

Grant succeeded.

SQL> conn tspitr/tspitr
Connected.
SQL> create table tspitr as select * from dba_objects;

Table created.
SQL> select count(*) from tspitr;

  COUNT(*)
----------
     50678

SQL> select sequence#, status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        28 INACTIVE
        29 CURRENT
        27 INACTIVE

在创建完实验表tspitr后,记录一下当前的logseq号为29。并对数据库进行一次全备份。

[root@oracle11g ~]# su - oracle
[oracle@oracle11g ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[oracle@oracle11g ~]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 16 10:34:12 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database

RMAN> backup as backupset database plus archivelog delete all input;


Starting backup at 16-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=13 recid=20 stamp=872692763
input archive log thread=1 sequence=14 recid=21 stamp=873390068
input archive log thread=1 sequence=15 recid=22 stamp=873477705
input archive log thread=1 sequence=16 recid=23 stamp=873557208
input archive log thread=1 sequence=17 recid=24 stamp=873628193
input archive log thread=1 sequence=18 recid=25 stamp=873881475
input archive log thread=1 sequence=19 recid=26 stamp=873882506
input archive log thread=1 sequence=20 recid=27 stamp=873886639
input archive log thread=1 sequence=21 recid=28 stamp=873886719
input archive log thread=1 sequence=22 recid=29 stamp=873969562
input archive log thread=1 sequence=23 recid=30 stamp=873969604
input archive log thread=1 sequence=24 recid=31 stamp=873974555
input archive log thread=1 sequence=25 recid=32 stamp=873974650
input archive log thread=1 sequence=26 recid=33 stamp=874234950
input archive log thread=1 sequence=27 recid=34 stamp=874315417
input archive log thread=1 sequence=28 recid=35 stamp=874486326
input archive log thread=1 sequence=29 recid=36 stamp=874492547
channel ORA_DISK_1: starting piece 1 at 16-MAR-15
channel ORA_DISK_1: finished piece 1 at 16-MAR-15
piece handle=/u02/ora_test874492549_351 tag=TAG20150316T103548 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_13_870806981.dbf recid=20 stamp=872692763
archive log filename=/u02/1_14_870806981.dbf recid=21 stamp=873390068
archive log filename=/u02/1_15_870806981.dbf recid=22 stamp=873477705
archive log filename=/u02/1_16_870806981.dbf recid=23 stamp=873557208
archive log filename=/u02/1_17_870806981.dbf recid=24 stamp=873628193
archive log filename=/u02/1_18_870806981.dbf recid=25 stamp=873881475
archive log filename=/u02/1_19_870806981.dbf recid=26 stamp=873882506
archive log filename=/u02/1_20_870806981.dbf recid=27 stamp=873886639
archive log filename=/u02/1_21_870806981.dbf recid=28 stamp=873886719
archive log filename=/u02/1_22_870806981.dbf recid=29 stamp=873969562
archive log filename=/u02/1_23_870806981.dbf recid=30 stamp=873969604
archive log filename=/u02/1_24_870806981.dbf recid=31 stamp=873974555
archive log filename=/u02/1_25_870806981.dbf recid=32 stamp=873974650
archive log filename=/u02/1_26_870806981.dbf recid=33 stamp=874234950
archive log filename=/u02/1_27_870806981.dbf recid=34 stamp=874315417
archive log filename=/u02/1_28_870806981.dbf recid=35 stamp=874486326
archive log filename=/u02/1_29_870806981.dbf recid=36 stamp=874492547
Finished backup at 16-MAR-15

Starting backup at 16-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-MAR-15
channel ORA_DISK_1: finished piece 1 at 16-MAR-15
piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 16-MAR-15

Starting backup at 16-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=30 recid=37 stamp=874492654
channel ORA_DISK_1: starting piece 1 at 16-MAR-15
channel ORA_DISK_1: finished piece 1 at 16-MAR-15
piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_30_870806981.dbf recid=37 stamp=874492654
Finished backup at 16-MAR-15

Starting Control File and SPFILE Autobackup at 16-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-MAR-15

RMAN> list backup ;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1197    106.98M    DISK        00:00:23     2015-03-16 10:36:12
        BP Key: 1198   Status: AVAILABLE  Compressed: NO  Tag: TAG20150316T103548
        Piece Name: /u02/ora_test874492549_351

  List of Archived Logs in backup set 1197
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    13      479270     2015-02-13 11:15:05 507890     2015-02-26 14:39:21
  1    14      507890     2015-02-26 14:39:21 537468     2015-03-03 16:21:06
  1    15      537468     2015-03-03 16:21:06 565660     2015-03-04 16:41:41
  1    16      565660     2015-03-04 16:41:41 597853     2015-03-05 14:46:22
  1    17      597853     2015-03-05 14:46:22 627695     2015-03-06 10:29:51
  1    18      627695     2015-03-06 10:29:51 662451     2015-03-09 08:51:08
  1    19      662451     2015-03-09 08:51:08 663522     2015-03-09 09:08:25
  1    20      663522     2015-03-09 09:08:25 666107     2015-03-09 10:17:18
  1    21      666107     2015-03-09 10:17:18 666153     2015-03-09 10:18:39
  1    22      666153     2015-03-09 10:18:39 692585     2015-03-10 09:19:20
  1    23      692585     2015-03-10 09:19:20 692853     2015-03-10 09:20:04
  1    24      692853     2015-03-10 09:20:04 695060     2015-03-10 10:42:34
  1    25      695060     2015-03-10 10:42:34 695112     2015-03-10 10:44:09
  1    26      695112     2015-03-10 10:44:09 726710     2015-03-13 11:02:26
  1    27      726710     2015-03-13 11:02:26 759353     2015-03-14 09:23:34
  1    28      759353     2015-03-14 09:23:34 786212     2015-03-16 08:52:00
  1    29      786212     2015-03-16 08:52:00 789814     2015-03-16 10:35:43

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1226    Full    612.91M    DISK        00:01:14     2015-03-16 10:37:32
        BP Key: 1244   Status: AVAILABLE  Compressed: NO  Tag: TAG20150316T103617
        Piece Name: /u02/ora_test874492578_361
  List of Datafiles in backup set 1226
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/system01.dbf
  2       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/undotbs01.dbf
  3       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/sysaux01.dbf
  4       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/users01.dbf
  5       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/example01.dbf
  6       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/tspitr01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1268    2.50K      DISK        00:00:01     2015-03-16 10:37:37
        BP Key: 1275   Status: AVAILABLE  Compressed: NO  Tag: TAG20150316T103735
        Piece Name: /u02/ora_test874492656_371

  List of Archived Logs in backup set 1268
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    30      789814     2015-03-16 10:35:43 789871     2015-03-16 10:37:33

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1286    Full    6.80M      DISK        00:00:01     2015-03-16 10:37:40
        BP Key: 1288   Status: AVAILABLE  Compressed: NO  Tag: TAG20150316T103739
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp
  Control File Included: Ckp SCN: 789893       Ckp time: 2015-03-16 10:37:39
  SPFILE Included: Modification time: 2015-03-16 08:52:02


模拟误操作:
记录当前时间点的logseq号为31

SQL> select sequence#, status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        31 CURRENT
        29 INACTIVE
        30 INACTIVE

进行日志切换,记录truncate操作之前的logseq号为32,并执行truncate操作

SQL> alter system switch logfile;

System altered.

SQL> select sequence#, status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        31 ACTIVE
        32 CURRENT
        30 INACTIVE

SQL> truncate table tspitr;

Table truncated.

SQL> alter system switch logfile;

System altered.

SQL> select sequence#, status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        31 ACTIVE
        32 ACTIVE
        33 CURRENT

在执行truncate table tspitr操作之后,进行日志切换,并记录truncate操作之后的当前logseq号为33。我们这里的恢复目标是将表空间tspitr恢复到logseq=32的时间点。为了执行自动RMAN TSPITR,启动RMAN客户端,连接到目标数据库和恢复目录(如果有的话)。注意,为了使用自动TSPITR在启动RMAN客户端时不要连接到辅助实例。如果在执行recover tablespace命令时,RMAN连接到一个辅助实例,RMAN会假设你将试管理自己单独的辅助实例。

创建辅助目录:

[oracle@oracle11g oradata]$ mkdir auxiliary
[oracle@oracle11g oradata]$ ls -lrt
total 8
drwxr-x--- 2 oracle oinstall 4096 Mar 16 10:22 test
drwxr-xr-x 2 oracle oinstall 4096 Mar 16 10:55 auxiliary

如果已经配置了通道,RMAN可以在主实例上从备份中执行还原,那么通过执行recover tablespace ...until ...命令就能执行TSPITR了。这里使用目标数据库中的缺省磁盘通道。

执行下面的命令将表空间tspitr恢复到logseq=32的时间点:

RMAN> recover tablespace tspitr until logseq 32 auxiliary destination '/u01/app/oracle/oradata/auxiliary';

Starting recover at 2015-03-16 11:27:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK

Creating automatic instance, with SID='hswB'

initialization parameters used for automatic instance:
db_name=TEST
compatible=10.2.0.5.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_TEST_hswB
sga_target=180M
processes=50
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle/oradata/auxiliary
control_files=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f


starting up automatic instance TEST

Oracle instance started

Total System Global Area     188743680 bytes

Fixed Size                     1272720 bytes
Variable Size                 62915696 bytes
Database Buffers             121634816 bytes
Redo Buffers                   2920448 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until  logseq 32 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2015-03-16 11:27:38
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=47 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp tag=TAG20150316T103739
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f
Finished restore at 2015-03-16 11:27:41

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  logseq 32 thread 1;
plsql < <>>;
# set an omf destination filename for restore
set newname for clone datafile  1 to new;
# set an omf destination filename for restore
set newname for clone datafile  2 to new;
# set an omf destination tempfile
set newname for clone tempfile  1 to new;
# set a destination filename for restore
set newname for datafile  6 to
 "/u01/app/oracle/oradata/test/tspitr01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile  1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  "TSPITR", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TSPITR offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2015-03-16 11:27:54
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492578_361
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 2015-03-16 11:29:00

datafile 1 switched to datafile copy
input datafile copy recid=19 stamp=874495741 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_bjdmfw0k_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=20 stamp=874495741 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_bjdmfw23_.dbf

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  6 online

Starting recover at 2015-03-16 11:29:01
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 31 is already on disk as file /u02/1_31_870806981.dbf
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=30
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492656_371
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf recid=38 stamp=874495743
archive log filename=/u02/1_31_870806981.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-16 11:29:05

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle\)\(ARGV0=oraclehswB\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=hswB^'\)\)\(CONNECT_DATA=\(SID=hswB\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
 TSPITR file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/zzh_2046@test as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TSPITR online";
sql "alter tablespace  TSPITR offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing Memory Script


Export: Release 10.2.0.5.0 - Production on Mon Mar 16 11:29:30 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                         TSPITR
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.5.0 - Production on Mon Mar 16 11:29:46 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table                       "TSPITR"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace TSPITR online

sql statement: alter tablespace  TSPITR offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_bjdmfw0k_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_bjdmfw23_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_temp_bjdmjfov_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_1_bjdmj1o6_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_2_bjdmj44r_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_3_bjdmj6f1_.log deleted
Finished recover at 2015-03-16 11:29:59

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TSPITR                         OFFLINE

7 rows selected.

TSPITR成功执行完成,RMAN会将表空间脱机,从备份中还原并在辅助实例中将表空间恢复到指定的目标时间点。在整个过程执行完成后,表空间仍然是脱机状态。所有的辅助集数据文件和其它的辅助实例文件会从辅助目录中删除。

TSPITR执行成功后需要执行的操作:
1.必须要备份恢复的表空间然后将表空间置于联机状态。
2.在TSPITR执行成功后立即备份恢复成功的表空间是非常重要的。在对表空间执行TSPITR后,在TSPITR恢复目标时间之前的表空间备份将不能再使用。如果不对恢复的表空间进行备份就开始使用,你将在没有这些表空间备份的情况下运行数据库。对于这个例子,执行以下命令来备份tspitr表空间或者对整个数据库进行备份:

RMAN> backup tablespace tspitr;

Starting backup at 2015-03-16 11:40:53
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-03-16 11:40:54
channel ORA_DISK_1: finished piece 1 at 2015-03-16 11:40:55
piece handle=/u02/ora_test874496454_391 tag=TAG20150316T114053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-03-16 11:40:55

Starting Control File and SPFILE Autobackup at 2015-03-16 11:40:55
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874496456_bjdn69mh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2015-03-16 11:40:59

然后可以安全的将表空间置于联机状态:

RMAN> sql 'alter tablespace tspitr online';

sql statement: alter tablespace tspitr online
starting full resync of recovery catalog
full resync complete

现在恢复的表空间就可以供用户使用了。

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TSPITR                         ONLINE

7 rows selected.

SQL> show user
USER is "TSPITR"
SQL> select count(*) from tspitr;

  COUNT(*)
----------
     50678

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30604784/viewspace-2056317/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle传输表空间
请登录后发表评论 登录
全部评论

注册时间:2015-12-01

  • 博文量
    58
  • 访问量
    205263