RMAN可以用来对整个表空间进行指定SCN恢复的操作，如果误操作发生在个别的表空间，而且无法用select * from t as of scn来恢复，如果用flash backup database会影响到整个数据库，这时用RMAN的传输表空间进行对指定的表空间进行恢复也是一种办法，过程中只需要对当前表空间设成只读，不影响其它的表空间。实际上它是通过建立辅组实例来建立传输文件，再将传输文件导入到数据库中，这就要求服务器要有一定的磁盘空余容量。
测试过程如下：
1.先做一次全备，包括归档日志
备份之前检查一下测试的表空间是不是自包含的，如果不是的话需要作相应的调整。
SQL> exec dbms_tts.transport_set_check('L5MSPACE',true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
开始备份
SQL> alter system archive log current ;
System altered.
RMAN>
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/RMAN0/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
run {
crosscheck archivelog all;
delete expired archivelog all;
crosscheck backup;
delete expired backup;
allocate channel c1 type disk;
backup incremental level 0 database format '/u01/RMAN0/db0%u_%s_%p.bak' filesperset 3 include current controlfile;
backup spfile tag='spfile' format='/u01/RMAN0/ORCL_spfile_%U_%T';
sql 'alter system archive log current';
backup filesperset 1 format '/u01/RMAN0/arch%u_%s_%p.bak'
archivelog all delete input;
release channel c1;
}
exit;
2.建立测试环境并做一个人为的误操作：
SQL> create table t tablespace l5mspace as select * from dba_objects;
Table created.
SQL> alter system archive log current;
System altered.
SQL> select count(*) from t;
COUNT(*)
----------
54362
SQL> set numw 12
SQL> select current_scn,sysdate from v$database;
CURRENT_SCN SYSDATE
------------ ---------
17670295088 26-MAR-20
开始误操作：
SQL> delete from t where owner='SYSTEM';
454 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
将误操作的日志部分进行归档：
RMAN> backup archivelog all not backed up 1 times format '/u01/RMAN0/arch%u_% s_%p.bak';
Starting backup at 26-MAR-20
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=312 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=324 devtype=DISK
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_01_07/o1_mf_1_1_h18h4ypm_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_01_11/o1_mf_1_1_h1kdyhgv_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_01_18/o1_mf_1_2_h23t2h65_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_01_24/o1_mf_1_3_h2oq37w5_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_01_31/o1_mf_1_4_h372kcw5_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_02_06/o1_mf_1_5_h3r6zlr5_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_02_12/o1_mf_1_6_h48176ql_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_02_18/o1_mf_1_7_h4prb904_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_02_24/o1_mf_1_8_h55sr8w1_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_03_23/o1_mf_1_9_h7jvfx2h_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_03_26/o1_mf_1_10_h7ronrv9_.arc; already backed up 1 time(s)
skipping archive log file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/ar chivelog/2020_03_26/o1_mf_1_11_h7rpfh5v_.arc; already backed up 1 time(s)
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=12 recid=642 stamp=1036078709
channel ORA_DISK_1: starting piece 1 at 26-MAR-20
channel ORA_DISK_2: starting compressed archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=13 recid=643 stamp=1036078808
input archive log thread=1 sequence=14 recid=644 stamp=1036078884
channel ORA_DISK_2: starting piece 1 at 26-MAR-20
channel ORA_DISK_1: finished piece 1 at 26-MAR-20
piece handle=/u01/RMAN0/archqmus2jp6_854_1.bak tag=TAG20200326T154126 comment=NO NE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_2: finished piece 1 at 26-MAR-20
piece handle=/u01/RMAN0/archqnus2jp6_855_1.bak tag=TAG20200326T154126 comment=NO NE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
Finished backup at 26-MAR-20
Starting Control File and SPFILE Autobackup at 26-MAR-20
piece handle=/u01/RMAN0/c-1166343071-20200326-02 comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAR-20
3.现在用rman的transport命令来进行恢复：
RMAN> transport tablespace l5mspace tablespace destination '/u01/aux' auxiliary destination '/u01/aux' until scn 17670295088;
输出比较多就不复制了过程其实就是通过辅组实例+数据库备份来恢复到特定时间点或者scn
查看一下/u01/aux生成的文件
[oracle@qht115 aux]$ ll
total 1054848
-rw-r----- 1 oracle oinstall 5337088 Mar 26 17:21 dmpfile.dmp
-rw-r--r-- 1 oracle oinstall 1731 Mar 26 17:21 explog.log
-rw-r--r-- 1 oracle oinstall 2037 Mar 26 17:21 impscrpt.sql
-rw-r----- 1 oracle oinstall 1073750016 Mar 26 17:18 l5mspace01.dbf
drwxr-x--- 4 oracle oinstall 4096 Mar 26 17:12 TSPITR_ORCL_QNRP
--//l5mspace.dbf就是传输表空间的数据文件.dmpfile.dmp就是源数据,而impscrpt.sql就是如何导入的脚本.
4.导入整个表空间到数据库
直接导入肯定不行,表名以及表空间冲突,必须重新映射.
SQL> create user l5msys identified by l5msys;
User created.
SQL> grant connect,sysdba,dba,resource to l5msys;
Grant succeeded.
SQL> CREATE DIRECTORY D_AUX as '/u01/aux';
Directory created.
SQL> grant read,write on directory D_AUX to l5msys;
Grant succeeded.
执行导入操作：
[oracle@qht115 aux]$ impdp l5m/l5m dumpfile=dmpfile.dmp directory=D_aux transport_datafiles=/u01/aux/l5mspace01.dbf REMAP_TABLESPACE=l5mspace:l5mspace02 REMAP_SCHEMA=l5m:l5msys logfile=impdp.log
kipped due to table_exists_action of skip
ORA-39151: Table "YELLOWFLAG"."METADATA" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "YELLOWFLAG"."SPACES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "YELLOWFLAG"."TAGS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
ORA-39082: Object type TRIGGER:"L5MSYS"."PROPERTY_HEADER_DEL" created with compilation warnings
ORA-39082: Object type TRIGGER:"L5MSYS"."PROPERTY_HEADER_DEL" created with compilation warnings
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "L5M"."SYS_IMPORT_TRANSPORTABLE_01" completed with 493 error(s) at 20:26:15
由于这个表空间除了l5m用户外，还有其它的用户，这些用户会由于冲突无法导入，这里可以不管，我只需要查看导入的新用户l5msys的数据是否恢复过来。
验证一下：
SQL> select count(*) from l5msys.t;
COUNT(*)
----------
54362
这里有一个知识点，恢复出来数据的rowid与原数据的rowid是一致的。
两个表的rowid一样,除了这种情况rowid会一样，另一种就是cluster table
SQL> select rowid,object_name from l5m.t where rownum=1;
ROWID OBJECT_NAME
-------------------- --------------------
AABmZPAAGAAALTkAAA ICOL$
SQL> select rowid,object_name from l5msys.t where rownum=1;
ROWID OBJECT_NAME
-------------------- --------------------
AABmZPAAGAAALTkAAA ICOL$
参考：
https://www.cnblogs.com/lfree/p/7068974.html
扫一扫
1023
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
