19c新特性-rman单表恢复

前言

在12C之前，某张表被delete、update、insert并且提交后，我们可以在时间和undo允许情况下，通过flashback找回数据；如果表被drop，并且未带purge参数时，可以通过recyclebin找回drop的表；如果以上情况都不满足，那我们就只能备份来还原数据，费时费力不说，如果没有rman只有EXP或EXPDP备份，而且数据也可能丢失😹
别慌，Oracle 12c的Recover Table新特性进一步加强了自动化运维，让DBA们能更快找回数据

Recover Table

RMAN的表级和表分区级恢复应用场景

1、You need to recover a very small number of tables to a particular point in time. In this situation, TSPITR is not the most effective solution because it moves all the objects in the tablespace to a specified point in time.

2、You need to recover tables that have been logically corrupted or have been dropped and purged.

3、Flashback Table is not possible because the desired point-in-time is older than available undo.

4、You want to recover data that is lost after a DDL operation modified the structure of tables. Using Flashback Table is not possible because a DDL was run on the tables between the desired point in time and the current time. Flashback Table cannot rewind tables through structural changes such as a truncate table operation.

译

您需要将非常少量的表恢复到特定的时间点。在这种情况下，TSPITR不是最有效的解决方案，因为它将表空间中的所有对象都移动到指定的时间点。

您需要恢复已被逻辑损坏或已被删除和清除的表。

Flashback Table 不可用，如undo 数据已经被覆盖。

恢复在DDL操作修改表结构之后丢失的数据。使用Flashback表是不可能的，因为在需要的时间点和当前时间之间的表上运行一个DDL。闪回表不能通过诸如截断表操作之类的结构更改来倒表。

RMAN的表级和表分区级恢复限制

1、Tables and table partitions belonging to SYS schema cannot be recovered.

2、Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered.

3、Tables and table partitions on standby databases cannot be recovered.

4、Tables with named NOT NULL constraints cannot be recovered with the REMAP option.

译

SYS用户表或分区无法恢复

存储于SYSAUX和SYSTEM表空间下的表和分区无法恢复

在备库上的表和分区表不能恢复

当REMAP选项用来恢复的表包含NOT NULL约束时，恢复此表是不可行的

RMAN的表级和表分区级恢复前提

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.

译

数据库必须处于读写模式

数据库必须处于归档模式

如果要恢复表或者分区，你必须拥有这些表或者分区存在后的时间的备份

想要恢复单个表分区，COMPATIBLE初始化参数所在的目标库必须设置为11.1.0或以上

👇好了，说了这么多，下面直接进去实操👇

实战全记录

环境准备

数据库版本

操作系统DBCDB/NON-CDBrhel7.619.12.0.0CDB
📢本次实验操作全部位于pdb中，关于non-cdb中的命令将在后面列出

创建表空间

sys@ORCL12:40:56>show pdbs
CON_ID CON_NAME              OPENMODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED              READ ONLY  NO3 ORCLPDB              READWRITENO
     
sys@ORCL16:54:49>altersessionset container = orclpdb;Session altered.

sys@ORCL16:58:34>select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------/oradata/ORCL/D62DBC6AC5D57356E053647CA8C07405/datafile/o1_mf_system_jyr5xj32_.dbf
/oradata/ORCL/D62DBC6AC5D57356E053647CA8C07405/datafile/o1_mf_sysaux_jyr5xj3g_.dbf
/oradata/ORCL/D62DBC6AC5D57356E053647CA8C07405/datafile/o1_mf_undotbs1_jyr5xj3h_.dbf

sys@ORCL16:59:43>createtablespace eason datafile '/oradata/ORCL/D62DBC6AC5D57356E053647CA8C07405/datafile/eason.dbf' size 10m autoextend on;Tablespace created.

创建用户

sys@ORCL16:59:52>createuser hyj identified by hyj defaulttablespace eason;User created.

sys@ORCL17:12:10>grant dba to hyj;Grant succeeded.

创建表

创建普通表

[oracle@dbserver~]$ sqlplus hyj/hyj@orclpdb
hyj@ORCLPDB20:29:48>show con_name

CON_NAME
------------------------------
ORCLPDB
hyj@ORCLPDB16:48:30>createtable students(
id int,
name varchar2 (20),
age int);Table created.

创建分区表

hyj@ORCLPDB16:48:33>createtable students_p (
id int,
name varchar2(20),
age int)partitionby range(age)(partition p1 values less than(18),partition p2 values less than(40),partition p3 values less than(60),partition p4 values less than(maxvalue));Table created

插入数据

insertinto students(id,name,age)values(1,'李四',20);insertinto students(id,name,age)values(2,'张帆',16);insertinto students(id,name,age)values(3,'张三',35);insertinto students(id,name,age)values(4,'王八',65);insertinto students(id,name,age)values(5,'张飞',70);insertinto students(id,name,age)values(6,'林白',41);insertinto students_p(id,name,age)values(1,'李四',20);insertinto students_p(id,name,age)values(2,'张帆',16);insertinto students_p(id,name,age)values(3,'张三',35);insertinto students_p(id,name,age)values(4,'王八',65);insertinto students_p(id,name,age)values(5,'张飞',70);insertinto students_p(id,name,age)values(6,'林白',41);

备份整个数据库

[oracle@dbserver~]$ rman target /
connected to target database: ORCL (DBID=1622462283)
RMAN>backupdatabase plus archivelog;

删除表

truncate students表

hyj@ORCLPDB17:07:31>truncatetable students;Table truncated.

hyj@ORCLPDB17:07:50>select*from students;norows selected

删除分区表数据

hyj@ORCLPDB17:10:55>deletefrom students_p partition(p2);2rows deleted.

hyj@ORCLPDB17:11:05>commit;Commit complete.

hyj@ORCLPDB17:11:23>select*from students_p partition(p2);norows selected

hyj@ORCLPDB17:11:33>select*from students_p;

    ID NAME                                            AGE
---------- -------------------------------------------------------------------------------- ----------2 张帆                                             166 林白                                             414 王八                                             655 张飞                                             70

恢复数据

创建辅助目录

[oracle@dbserver/backup]$ mkdir tablereovery
[oracle@dbserver/backup]$ mkdir dumpfile

🐸注意：辅助目录的权限

恢复单表

RMAN> RECOVER TABLE hyj.students OF PLUGGABLE DATABASE orclpdb
UNTIL TIME"to_date('2022-01-25 17:07:00','yyyy-mm-dd hh24:mi:ss')"
AUXILIARY DESTINATION '/backup/tablereovery/'
REMAP TABLE'HYJ'.'STUDENTS':'STUDENTS_RESTORE';

恢复后重命名的studetns_restore的数据与之前一致，students原表中数据保留truncate后的状态

分区表恢复

RMAN> RECOVER TABLE  hyj.students_p:p2 OF PLUGGABLE DATABASE orclpdb
    UNTIL TIME"to_date('2022-01-25 17:07:00','yyyy-mm-dd hh24:mi:ss')"
    AUXILIARY DESTINATION '/backup/tablereovery/'
    DATAPUMP DESTINATION  '/backup/dumpfile/'
    REMAP TABLE'HYJ'.'STUDENTS_P':'P2':'P2_RESTORE'DUMPFILE'students_p_p2.dmp';

需要恢复的分区被恢复成了一张单表。

总结

由于时间关系，其他的实验操作过程大同小异就略过。下面列出了单表恢复的语法和其他场景使用案例。

语法

RECOVER TABLE username.tablename UNTIL TIME'TIMESTAMP…' 
AUXILIARY DESTINATION '/u01/tablerecovery' 
DATAPUMP DESTINATION '/u01/dumpfile'DUMPFILE'tablename.dmp' 
NOTABLEIMPORT        -- this option avoids importing the table automatically.(此选项避免自动导入表) 
REMAP TABLE'username.tablename': 'username.new_table_name';-- can rename table with this option.(此选项可以对表重命名)

当然recover table除了until time以后，还支持以下3种方式：

SCN号

Sequence number（日志序列号）

Time：根据NLS_LANG和NLS_DATE_FORMAT环境变量中的格式来指定时间，也可以用SYSDATE，比如"SYSDATE-30"、“to_date(‘2018-04-09:13:51:48’,‘yyyy-mm-dd hh24:mi:ss’)”

恢复多张表到多个用户多个表空间

RECOVER TABLE owner1.table1,owner1.table2 OF PLUGGABLE DATABASE HR_PDB
UNTIL TIME'SYSDATE-4'
AUXILIARY DESTINATION '/tmp/backups/'
REMAP TABLE owner1.table1:owner2.table1_1, owner1.table2:owner2.table2_1    ---重命名多张表，使用','分开
REMAP TABLESPACE'tablespace1':'tablespace2';---更改表空间

报错处理

如果你的误操作不是drop，只是增删改了表中的记录或者truncate，此时你在recover table时候会出现

RMAN-05112

: table “XXX”.“XXXX” already exists。此时你可以remap或dump操作，添加notableimport参数不执行导入操作，然后再手工impdp处理

官方文档：

http://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV686

标签： dba

本文转载自: https://blog.csdn.net/easonhyj/article/details/122668847
版权归原作者 easonhyj 所有，如有侵权，请联系我们删除。