逻辑备份和物理备份表级恢复

一、逻辑备份(schema级备份，表级，分区级恢复）

1. 建表

create table test.test_partition(id number)

partition by range(id)

(

partition P1 values less than (10000),

partition P2 values less than (20000),

partition P3 values less than (30000),

partition P4 values less than (MAXVALUE)

) tablespace TEST;

create index test.IDX_test_partition on test.test_partition(id) nologging  local tablespace idx_test;

 

2. 备份test schema

expdp "'/ as sysdba'" directory=export dumpfile=test_schema_20191103 schemas=test

 

3. 损坏test.TEST_PARTITION表

delete from test.test_partition where id<10000;

commit;

4. 表级恢复

impdp "' / as sysdba'" directory=export dumpfile=test_schema_20191103.dmp tables=test.TEST_PARTITION table_exists_action=replace

1. 分区级恢复

impdp "' / as sysdba'" directory=export dumpfile=test_schema_20191103.dmp tables=test.TEST_PARTITION:P1 table_exists_action=append

二、物理全备恢复表，分区（rman全备数据库，表级、分区级恢复)

1. 表级、分区级恢复条件：

（1）The target database must be in read-write mode.

（2）The target database must be in ARCHIVELOG mode.

（3）You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover these objects.

（4）To recover single table partitions, the COMPATIBLE initialization parameter for target database must be set to 11.1.0 or higher.

（5）If the indexes or partitions for a table in tablespace tbs1 are contained in tablespace tbs2, then you can recover the table only if tablepsace tbs2 is also included in the recovery set.

（6）Tables under sys and in tablespace system or sysaux  cannot be restored.

（7）恢复的目录要能够放得下恢复的表所在表空间的数据库文件（原因见附录）

（8）To recover tables or table partitions, you need a full backup of undo, SYSTEM, SYSAUX, and the tablespace that contains the tables or table partitions.

To recover a table, all partitions that contain the dependent objects of the table must be included in the recovery set. If the indexes or partitions for a table in tablespace tbs1 are contained in tablespace tbs2, then you can recover the table only if tablepsace tbs2 is also included in the recovery set.

经测试，若表和对应的索引不在同一个表空间，只要目标数据库里存在索引所在的表空间，在表级恢复的过程中索引也能恢复（重建）

但是分区表的索引若为local，导入到临时表时，索引无法重建

2. 恢复方法

（1）基于scn

（2）基于time

（3）基于redo log sequence no

3. 恢复步骤（基于scn）

（0）建表和分区表

create table TD_ROAD_COMP_INDEX_H  

(   ID  NUMBER(32)           not null,    

STAT_TIME   DATE,    

constraint PK_TD_ROAD_COMP_INDEX_H primary key (ID));

 

create table test.test_partition(id number)

partition by range(id)

(

partition P1 values less than (10000),

partition P2 values less than (20000),

partition P3 values less than (30000),

partition P4 values less than (MAXVALUE)

) tablespace TEST;

create index test.IDX_test_partition on test.test_partition(id) nologging  local tablespace idx_test;

 

begin

for i in 1..10000000 loop

insert into test.TD_ROAD_COMP_INDEX_H(id,STAT_TIME) values(i,sysdate);

end loop;

commit;

end;

/

 

begin

for i in 1..40000 loop

insert into test.test_partition(id) values(i);

end loop;

commit;

end;

/

（1）全备数据库（集成文档）

backup incremental level 0 tag 'dbL0' format '/oradata/export/%d_Incr0_%T_%s_%u' database INCLUDE CURRENT CONTROLFILE;

sql 'alter system archive log current';

backup format '/oradata/export/%d_log_%T_%U' archivelog until time 'sysdate'  delete all input ;

（2）损坏表或分区

drop table TD_ROAD_COMP_INDEX_H;

alter table TEST_PARTITION truncate partition p1;

 

（3）选择恢复的scn

rman target /

list backup;

 

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

1       Incr 0  4.28G      DISK        00:00:16     03-NOV-19

      BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: DBL0

      Piece Name: /oradata/export/CATDB_Incr0_20191103_1_01ufvl2s

List of Datafiles in backup set 1

File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name

---- -- ---- ---------- --------- ----------- ------ ----

1    0  Incr 94158873   03-NOV-19              NO    /oradata/CATDB/system01.dbf

2    0  Incr 94158873   03-NOV-19              NO    /oradata/CATDB/idx_test01.dbf

3    0  Incr 94158873   03-NOV-19              NO    /oradata/CATDB/sysaux01.dbf

4    0  Incr 94158873   03-NOV-19              NO    /oradata/CATDB/undotbs01.dbf

5    0  Incr 94158873   03-NOV-19              NO    /oradata/CATDB/test01.dbf

7    0  Incr 94158873   03-NOV-19              NO    /oradata/CATDB/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

2       Incr 0  10.17M     DISK        00:00:01     03-NOV-19

      BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: DBL0

      Piece Name: /oradata/export/CATDB_Incr0_20191103_2_02ufvl3l

Control File Included: Ckp SCN: 94158894     Ckp time: 03-NOV-19

BS Key  Type LV Size

------- ---- -- ----------

3       Full    10.20M

SPFILE Included: Modification time: 03-NOV-19

SPFILE db_unique_name: CATDB

Control File Included: Ckp SCN: 94158903     Ckp time: 03-NOV-19

Backup Set Copy #1 of backup set 3

Device Type Elapsed Time Completion Time Compressed Tag

----------- ------------ --------------- ---------- ---

DISK        00:00:00     03-NOV-19       NO         TAG20191103T213031

  List of Backup Pieces for backup set 3 Copy #1

  BP Key  Pc# Status      Piece Name

  ------- --- ----------- ----------

  3       1   AVAILABLE   /oracle/product/18/dbs/c-2566813493-20191103-00

（4）基于scn恢复

恢复表：

RECOVER TABLE 'TEST'.'TD_ROAD_COMP_INDEX_H'  UNTIL scn 94158903 AUXILIARY DESTINATION '/oradata/export';

恢复分区：

RECOVER TABLE 'TEST'.'TEST_PARTITION':'P1'  UNTIL scn 94158903 AUXILIARY DESTINATION '/oradata/export';

（5）检查恢复的效果

SQL> select count(*) from TD_ROAD_COMP_INDEX_H;

COUNT(*)

----------

10000000

SQL> select count(*) from test_partition partition(p1);

COUNT(*)

----------

       0

SQL> select count(*) from test_partition_p1;

COUNT(*)

----------

    9999

发现表级恢复成功，但分区级没有，看日志发现，分区无法恢复到原分区，而是恢复到了一个临时表(test_partition_p1)中，我们可以对这个临时表再操作，将其插入到之前的分区表中

If you omit the REMAP TABLE clause, RMAN uses default names for the imported tables. The name is a combination of the original table name and the partition name.

4. rman恢复表的原理和内部步骤

（1）Determines which backup contains the tables or table partitions that need to be recovered, based on the point in time specified for the recovery.

确定要恢复表或分区所需的备份集

（2）Determines if there is sufficient space on the target host to create the auxiliary instance that will be used during the table or partition recovery process.

If the required space is not available, then RMAN displays an error and exits the recovery operation.

在恢复表或分区的过程中，利用scn等会将备份集恢复到一个辅助数据库，会检查恢复的路径空间是否够用

Creates an auxiliary database on the target host and recovers the specified tables or table partitions, until the specified point in time, into this auxiliary database.

You can specify the location on the target host to which the recovered data files are stored in the auxiliary database.

（3）Creates a Data Pump export dump file that contains the recovered tables or table partitions.

You can specify the name and the location of the Data Pump export dump file used to store the metadata of the recovered tables or table partitions.

利用数据泵将所需表或分区导出到一个dumpfile

（4）(Optional) Imports the Data Pump export dump file into the target instance.

You can choose not to import the export dump file that contains the recovered tables or table partitions into the target database. If you do not import the export dump file as part of the recovery process, you must manually import it later using the Data Pump Import utility.

你可以从源数据库导入表或分区(可选)

（5）(Optional) Renames the recovered tables or table partitions in the target database.

You can also import recovered objects into a tablespace or schema that is different from the one in which they originally existed.

在恢复过程中进行重命名操作，remap_schema或remap_tablespace

5.附录

官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/rman-recovering-tables-partitions.html#GUID-87B7F772-335F-4179-81C9-91678D026D01

恢复表和分区的日志（从日志中可以看出rman基于备份恢复表和分区的原理）

📎恢复表的日志.txt

📎恢复分区的日志.txt