RMAN基于表空间的不完全恢复（TSPITR)

郑全 · 发表于 2016-7-2 20:02:55

本帖最后由郑全于 2016-7-2 20:05 编辑

RMAN基于表空间的不完全恢复（TSPITR)

•表空间时间点恢复(TSPITR)：概念

通过执行TSPITR 可将一个或多个表空间快速恢复到以前的某个时间。

执行TSPITR 不会影响数据库中其它表空间或对象的状态。

使用RMAN 自动表空间时间点恢复(TSPITR) 可将Oracle DB 中的一个或多个表空间快速恢复到以前的某个时间，而不会影响数据库中其它表空间和对象的状态。

•表空间时间点恢复(TSPITR)：术语

目标时间：表空间恢复到的时间点或SCN

恢复集：组成要恢复的表空间的数据文件

辅助集：对恢复集执行TSPITR 时所必需的，但不属于恢复集的数据文件。辅助集通常包括：

– SYSTEM表空间

–还原段表空间

–临时表空间

辅助目标：存储文件的磁盘位置

讨论TSPITR 时使用了以下术语：

目标时间：执行TSPITR 期间表空间将恢复到的时间点或系统更改号(SCN)。

恢复集：组成要恢复的表空间的数据文件。

辅助集：对恢复集执行TSPITR 时所必需的，但本身不属于恢复集的数据文件。辅助集通常包括：

- SYSTEM表空间的副本

- 包含来自目标实例的还原段的数据文件

- 某些情况下从辅助实例导出数据库对象期间使用的临时表空间

辅助目标：执行TSPITR 期间在磁盘上存储辅助实例的任何辅助集数据文件、控制文件和联机日志的位置。执行完TSPITR 之后可删除辅助目标中存储的文件。

•表空间时间点恢复：体系结构

RMAN基于表空间的不完全恢复（TSPITR)

参见:xppB_TSPITR.ppt 第五页

图表中显示了下列TSPITR 实体：

目标数据库：包含要恢复的表空间

控制文件：向RMAN 提供备份信息

备份集：来自目标数据库，用作重建表空间的来源

归档重做日志：来自目标数据库，用作重建表空间的来源

辅助实例：在恢复过程中用来执行恢复的Oracle DB 实例

RMAN 在执行表空间时间点恢复期间会执行下列步骤：

1.将目标时间之前某个时间点的备份控制文件还原到辅助实例。将恢复集的数据文件还原到目标数据库，将辅助集的数据文件还原到辅助实例。

2.将还原的数据文件恢复到指定的时间点。

参见:xppB_TSPITR.ppt 第六页

RMAN基于表空间的不完全恢复（TSPITR)

3.将已恢复表空间中对象的字典元数据导出到目标数据库。

4.在目标数据库上发出SWITCH命令，以使目标数据库控制文件指向辅助实例上已恢复的恢复集中的数据文件。

5.将辅助实例中的字典元数据导入目标实例，以便能访问已恢复对象。

•执行基本RMAN TSPITR

注意：要恢复的表空间必须是自包含的表空间

完全自动执行的TSPITR

–指定辅助目标。

– RMAN 会管理TSPITR 的所有方面。

–这是推荐的方法。

使用自动辅助实例的定制TSPITR

–这是在完全自动执行TSPITR 的基础上进行的。

–定制文件位置。

–指定初始化参数。

–指定通道配置。

使用您自己辅助实例的TSPITR

–配置并管理辅助实例。

执行TSPITR 时有下列选项：

完全自动执行的TSPITR：指定辅助目标，RMAN 会管理TSPITR 操作的所有方面。这是最简单的执行TSPITR 的方法，建议采用这种方法，除非明确指出需要在执行

TSPITR 之后控制恢复集的位置，或者在执行TSPITR期间控制辅助集文件的位置，或者需要控制辅助实例的通道配置或其它某个方面。

使用自动辅助实例的定制TSPITR：TSPITR 基于完全自动执行的TSPITR 的行为，可能仍使用辅助目标。可以自定义其行为的一个或多个方面，如辅助集文件或恢复集

文件的位置。可以为RMAN 创建并管理的辅助实例指定初始化参数或通道配置。

使用你自己辅助实例的TSPITR：设置、启动、停止和清除在TSPITR 中使用的辅助实例。另外，可以借助使用自动辅助实例的定制TSPITR 中的一些方法来管理TSPITR 过程。

郑全 · 发表于 2016-7-2 20:31:50

实例演示:
1.开归档并作备份
   数据库必须要有归档备份
   具体操作此处略.
2.确定要恢复的表空间为users
   其他表空间不受影响
   默认情况下,users表空间为整个系统的默认表空间,因此需要修改系统默认表空间为其他表空间
   Alter Database Default Tablespace example;
3.创建测试数据
   sql>conn hr/hr;
   19:06:41 SQL> create table emp tablespace users as select * from employees;
   Table created.
   19:07:11 SQL> select count(*) from emp;
   COUNT(*)
   ----------
         107
   19:07:17 SQL> --这个是后面要恢复的时间点
   19:07:43 SQL>
   19:07:44 SQL>

4.删除数据
   19:07:44 SQL> truncate table emp;
   Table truncated.
   19:07:51 SQL>
   19:08:10 SQL>
   19:08:10 SQL> select count(*) from emp;

   COUNT(*)
   ----------
      0
   19:11:49 SQL> insert into emp select * from employees where rownum<10;
               9 rows created.
   19:11:53 SQL> commit;
   Commit complete.
   19:11:55 SQL> select count(*) from emp;
   COUNT(*)
   ----------
      9
   数据恢复后,这个数据将为107行.
5.增加一张其他表到其他表空间
   19:09:54 SQL> create table dept tablespace example as select * from departments;
   Table created.
   19:10:02 SQL> select count(*) from dept;
   COUNT(*)
   ----------
      27
   由于数据在其他表空间,tspitr后,这个表的数据不会丢失.
   6.在users表空间增加一个其他表
   19:13:32 SQL> create table emp2  tablespace users as select * from emp;
   Table created.
   恢复后,这张表将丢失.
   7.进行表空间基于时间点恢复
   检查表空间是否自包含:
   SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('USERS',true);
   PL/SQL procedure successfully completed.
   SQL> select * from TRANSPORT_SET_VIOLATIONS t;
   no rows selected
   检查哪些对象在恢复后,将丢失:
      19:18:27 SQL>
      1  SELECT OWNER, NAME, TABLESPACE_NAME,TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS') as createtime
      2  FROM TS_PITR_OBJECTS_TO_BE_DROPPED
      3  WHERE TABLESPACE_NAME IN ('USERS')
      4  AND CREATION_TIME > TO_DATE('2016-07-02 19:07:17','yyyy-mm-dd hh24:mi:ss')
      5* ORDER BY TABLESPACE_NAME, CREATION_TIME
      OWNER                         NAME                         TABLESPACE_NAME             CREATETIME
   ------------------------------ ------------------------------ ------------------------------ ------------------------------
         HR                            EMP2                         USERS                         2016-07-02:19:13:51
      这与预期相符.
      进行tspitr
[oracle@dbserver ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 2 19:43:02 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SZTECH1 (DBID=3253410194)
recover tablespace users until time "to_date('2016-07-02 19:07:17','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/home/oracle/aux';
注意,事先要在/home/oracle下创建aux目录,

郑全 · 发表于 2016-7-2 20:36:14

RMAN> recover tablespace users until time "to_date('2016-07-02 19:07:17','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/home/oracle/aux';
Starting recover at 02-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=151 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='sDsx'
initialization parameters used for automatic instance:
db_name=SZTECH1
db_unique_name=sDsx_tspitr_SZTECH1
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/home/oracle/aux
log_archive_dest_1='location=/home/oracle/aux'
#No auxiliary parameter file used
starting up automatic instance SZTECH1
Oracle instance started
Total System Global Area 1071333376 bytes
Fixed Size                   1369420 bytes
Variable Size             281021108 bytes
Database Buffers          784334848 bytes
Redo Buffers                4608000 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

辅助实例创建完毕.

郑全 · 发表于 2016-7-2 20:38:55

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2016-07-02 19:07:17','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 02-JUL-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=81 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SZTECH1/backupset/2016_07_02/o1_mf_ncsnf_TAG20160702T185506_cqh7fsd5_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SZTECH1/backupset/2016_07_02/o1_mf_ncsnf_TAG20160702T185506_cqh7fsd5_.bkp tag=TAG20160702T185506
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/aux/SZTECH1/controlfile/o1_mf_cqhb6n2o_.ctl
Finished restore at 02-JUL-16
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2016-07-02 19:07:17','yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  4 to
"/u01/app/oracle/oradata/sztech1/users01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 4;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/aux/SZTECH1/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 02-JUL-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/aux/SZTECH1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/aux/SZTECH1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /home/oracle/aux/SZTECH1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/sztech1/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SZTECH1/backupset/2016_07_02/o1_mf_nnndf_TAG20160702T185506_cqh7dc7t_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SZTECH1/backupset/2016_07_02/o1_mf_nnndf_TAG20160702T185506_cqh7dc7t_.bkp tag=TAG20160702T185506
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:58
Finished restore at 02-JUL-16
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=916170260 file name=/home/oracle/aux/SZTECH1/datafile/o1_mf_system_cqhb6sv4_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=916170260 file name=/home/oracle/aux/SZTECH1/datafile/o1_mf_undotbs1_cqhb6svj_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=916170260 file name=/home/oracle/aux/SZTECH1/datafile/o1_mf_sysaux_cqhb6sv6_.dbf
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2016-07-02 19:07:17','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  4 online";
# recover and open resetlogs
recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile  1 online
sql statement: alter database datafile  3 online
sql statement: alter database datafile  2 online
sql statement: alter database datafile  4 online
Starting recover at 02-JUL-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/fast_recovery_area/SZTECH1/archivelog/2016_07_02/o1_mf_1_35_cqh7ftfs_.arc
archived log for thread 1 with sequence 36 is already on disk as file /u01/app/oracle/fast_recovery_area/SZTECH1/archivelog/2016_07_02/o1_mf_1_36_cqh8w8t1_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/SZTECH1/archivelog/2016_07_02/o1_mf_1_35_cqh7ftfs_.arc thread=1 sequence=35
archived log file name=/u01/app/oracle/fast_recovery_area/SZTECH1/archivelog/2016_07_02/o1_mf_1_36_cqh8w8t1_.arc thread=1 sequence=36
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-JUL-16
database opened

完成辅助实例不完整恢复到指定的时间.

郑全 · 发表于 2016-7-2 20:41:36

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  USERS read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/aux''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/aux''";
}
executing Memory Script
sql statement: alter tablespace  USERS read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/aux''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/aux''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_sDsx":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
EXPDP> Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_sDsx" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_sDsx is:
EXPDP> /home/oracle/aux/tspitr_sDsx_52313.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace USERS:
EXPDP> /u01/app/oracle/oradata/sztech1/users01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_sDsx" successfully completed at Sat Jul 2 19:45:21 2016 elapsed 0 00:00:49
Export completed

在辅助实例上导出表空间,注意使用的 "传输表空间" 完成.

郑全 · 发表于 2016-7-2 20:48:29

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace  USERS including contents keep datafiles cascade constraints';
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace  USERS including contents keep datafiles cascade constraints
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_sDsx" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_sDsx":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"OE"."PURCHASEORDER" creation failed
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_sDsx" completed with 26 error(s) at Sat Jul 2 19:45:52 2016 elapsed 0 00:00:07
Import completed

目标库导入辅助库上的表空间.

郑全 · 发表于 2016-7-2 20:50:01

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  USERS read write';
sql 'alter tablespace  USERS offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace  USERS read write
sql statement: alter tablespace  USERS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/aux/SZTECH1/datafile/o1_mf_temp_cqhb8rbj_.tmp deleted
auxiliary instance file /home/oracle/aux/SZTECH1/onlinelog/o1_mf_3_cqhb8q51_.log deleted
auxiliary instance file /home/oracle/aux/SZTECH1/onlinelog/o1_mf_2_cqhb8p2j_.log deleted
auxiliary instance file /home/oracle/aux/SZTECH1/onlinelog/o1_mf_1_cqhb8o39_.log deleted
auxiliary instance file /home/oracle/aux/SZTECH1/datafile/o1_mf_sysaux_cqhb6sv6_.dbf deleted
auxiliary instance file /home/oracle/aux/SZTECH1/datafile/o1_mf_undotbs1_cqhb6svj_.dbf deleted
auxiliary instance file /home/oracle/aux/SZTECH1/datafile/o1_mf_system_cqhb6sv4_.dbf deleted
auxiliary instance file /home/oracle/aux/SZTECH1/controlfile/o1_mf_cqhb6n2o_.ctl deleted
Finished recover at 02-JUL-16

RMAN>

目标库离线表空间,并删除辅助库对应的文件.

郑全 · 发表于 2016-7-2 20:51:00

8.验证恢复后数据
恢复后,表空间为offline状态
19:49:43 SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME             STATUS
------------------------------ ---------
SYSTEM                      ONLINE
SYSAUX                      ONLINE
UNDOTBS1                      ONLINE
TEMP                         ONLINE
USERS                         OFFLINE
EXAMPLE                      ONLINE
TBSBC                         ONLINE
TS_FDA1                      ONLINE
TS_FDA2                      ONLINE
9 rows selected.
表空间在线
19:49:52 SQL> alter tablespace users online;
Tablespace altered.
19:50:04 SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME             STATUS
------------------------------ ---------
SYSTEM                      ONLINE
SYSAUX                      ONLINE
UNDOTBS1                      ONLINE
TEMP                         ONLINE
USERS                         ONLINE
EXAMPLE                      ONLINE
TBSBC                         ONLINE
TS_FDA1                      ONLINE
TS_FDA2                      ONLINE
9 rows selected.
测试数据:
19:48:23 SQL> conn hr/hr
Connected.

19:50:42 SQL> select count(*) from emp;
  COUNT(*)
----------
   107
看到数据已经回来了
19:50:47 SQL> select count(*) from emp2;
select count(*) from emp2
                  *
ERROR at line 1:
ORA-00942: table or view does not exist
emp2倒是不在了,这个符合预期.
19:50:54 SQL> select count(*) from dept;
  COUNT(*)
----------
      27
19:51:02 SQL>
dept在其他表空间,不受影响.

至此,基于时间点的表空间不完整恢复全部完成.

帐号		自动登录	找回密码
密码			注册

[备份恢复] RMAN基于表空间的不完全恢复（TSPITR)