利用RMAN传输表空间恢复部分数据

      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

 

已标记关键词 清除标记
【为什么还需要学习C++?】 你是否接触很多语言,但从来没有了解过编程语言的本质? 你是否想成为一名资深开发人员,想开发别人做不了的高性能程序? 你是否经常想要窥探大型企业级开发工程的思路,但苦于没有基础只能望洋兴叹?   那么C++就是你个人能力提升,职业之路进阶的不二之选。 【课程特色】 1.课程共19大章节,239课时内容,涵盖数据结构、函数、类、指针、标准库全部知识体系。 2.带你从知识与思想的层面从0构建C++知识框架,分析大型项目实践思路,为你打下坚实的基础。 3.李宁老师结合4大国外顶级C++著作的精华为大家推出的《征服C++11》课程。 【学完后我将达到什么水平?】 1.对C++的各个知识能够熟练配置、开发、部署; 2.吊打一切关于C++的笔试面试题; 3.面向物联网的“嵌入式”和面向大型化的“分布式”开发,掌握职业钥匙,把握行业先机。 【面向人群】 1.希望一站式快速入门的C++初学者; 2.希望快速学习 C++、掌握编程要义、修炼内功的开发者; 3.有志于挑战更高级的开发项目,成为资深开发的工程师。 【课程设计】 本课程包含3大模块 基础篇 本篇主要讲解c++的基础概念,包含数据类型、运算符等基本语法,数组、指针、字符串等基本词法,循环、函数、类等基本句法等。 进阶篇 本篇主要讲解编程中常用的一些技能,包含类的高级技术、类的继承、编译链接和命名空间等。 提升篇: 本篇可以帮助学员更加高效的进行c++开发,其中包含类型转换、文件操作、异常处理、代码重用等内容。
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页