前言
在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或以上
👇好了,说了这么多,下面直接进去实操👇
实战全记录
环境准备
数据库版本
操作系统 | DB | CDB/NON-CDB |
---|---|---|
rhel7.6 | 19.12.0.0 | CDB |
📢本次实验操作全部位于pdb中,关于non-cdb中的命令将在后面列出
创建表空间
sys@ORCL 12:40:56> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
sys@ORCL 16:54:49> alter session set container = orclpdb;
Session altered.
sys@ORCL 16: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@ORCL 16:59:43> create tablespace eason datafile '/oradata/ORCL/D62DBC6AC5D57356E053647CA8C07405/datafile/eason.dbf' size 10m autoextend on ;
Tablespace created.
创建用户
sys@ORCL 16:59:52> create user hyj identified by hyj default tablespace eason;
User created.
sys@ORCL 17:12:10> grant dba to hyj;
Grant succeeded.
创建表
- 创建普通表
[oracle@dbserver ~]$ sqlplus hyj/hyj@orclpdb
hyj@ORCLPDB 20:29:48> show con_name
CON_NAME
------------------------------
ORCLPDB
hyj@ORCLPDB 16:48:30> create table students(
id int,
name varchar2 (20),
age int);
Table created.
- 创建分区表
hyj@ORCLPDB 16:48:33> create table students_p (
id int ,
name varchar2(20) ,
age int)
partition by 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
- 插入数据
insert into students(id,name,age) values(1,'李四',20);
insert into students(id,name,age) values(2,'张帆',16);
insert into students(id,name,age) values(3,'张三',35);
insert into students(id,name,age) values(4,'王八',65);
insert into students(id,name,age) values(5,'张飞',70);
insert into students(id,name,age) values(6,'林白',41);
insert into students_p(id,name,age) values(1,'李四',20);
insert into students_p(id,name,age) values(2,'张帆',16);
insert into students_p(id,name,age) values(3,'张三',35);
insert into students_p(id,name,age) values(4,'王八',65);
insert into students_p(id,name,age) values(5,'张飞',70);
insert into students_p(id,name,age) values(6,'林白',41);
备份整个数据库
[oracle@dbserver ~]$ rman target /
connected to target database: ORCL (DBID=1622462283)
RMAN> backup database plus archivelog;
删除表
- truncate students表
hyj@ORCLPDB 17:07:31> truncate table students;
Table truncated.
hyj@ORCLPDB 17:07:50> select * from students;
no rows selected
- 删除分区表数据
hyj@ORCLPDB 17:10:55> delete from students_p partition(p2) ;
2 rows deleted.
hyj@ORCLPDB 17:11:05> commit;
Commit complete.
hyj@ORCLPDB 17:11:23> select * from students_p partition(p2);
no rows selected
hyj@ORCLPDB 17:11:33> select * from students_p;
ID NAME AGE
---------- -------------------------------------------------------------------------------- ----------
2 张帆 16
6 林白 41
4 王八 65
5 张飞 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';
分区表恢复
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'
DUMP FILE 'students_p_p2.dmp' ;
需要恢复的分区被恢复成了一张单表。
总结
由于时间关系,其他的实验操作过程大同小异就略过。下面列出了单表恢复的语法和其他场景使用案例。
语法
RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…'
AUXILIARY DESTINATION '/u01/tablerecovery'
DATAPUMP DESTINATION '/u01/dumpfile'
DUMP FILE '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'; ---更改表空间
报错处理
1 如果你的误操作不是drop,只是增删改了表中的记录或者truncate,此时你在recover table时候会出现 RMAN-05112
: table “XXX”.“XXXX” already exists。此时你可以remap或dump操作,添加notableimport参数不执行导入操作,然后再手工impdp处理
2 在启动辅助实例时遇到如下报错,是由于SGA设置过大导致的,这可能也算是个小bug吧,因为你的正常实例sga+辅助实例sga
超过了服务器内存。其实辅助实例根本用不到这么多内存,但是由于它使用的是rman备份中的spfile,也无法手工修改参数文件,此时如果要继续使用该功能,你只能修改sga大小重启数据库完成后续操作
starting up automatic instance ORCL
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/10/2022 18:00:05
RMAN-04014: startup failed: ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 28: No space left on device
Additional information: 4648
Additional information: 5351931904
官方文档:
http://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV686