使用RMAN恢复表

使用场景

官方文档

Recovering Tables and Table Partitions from RMAN Backups

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

2、需要恢复逻辑上已损坏或已删除并清除的表。

3、由于所需的时间点早于可用的撤消时间，因此无法使用闪回表。

4、希望恢复DDL操作修改表结构后丢失的数据。使用Flashback Table是不可能的，因为DDL是在所需时间点和当前时间之间的表上运行的。Flashback表无法通过结构更改（如截断表操作）倒带表。

RMAN全备

[oracle@host01 ~]$ rman target sys/oracle@prodcdb

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 25 08:02:31 2023

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRODCDB (DBID=2891862819)

RMAN> backup as compressed backupset database INCLUDE CURRENT CONTROLFILE plus archivelog;

 删除表

1、删除表
HR@pdbprod1> select table_name from user_tables;

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COUNTRIES
DEPARTMENTS
REGIONS
LOCATIONS
JOBS
EMPLOYEES
JOB_HISTORY
TEST
EMP_DEPT

9 rows selected.

HR@pdbprod1> select * from test;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400
           70 Public Relations                      204        2700
           80 Sales                                 145        2500
           90 Executive                             100        1700
          100 Finance                               108        1700
          110 Accounting                            205        1700
          120 Treasury                                         1700
          130 Corporate Tax                                    1700
          140 Control And Credit                               1700
          150 Shareholder Services                             1700
          160 Benefits                                         1700
          170 Manufacturing                                    1700
          180 Construction                                     1700
          190 Contracting                                      1700
          200 Operations                                       1700
          210 IT Support                                       1700
          220 NOC                                              1700
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700
          250 Retail Sales                                     1700
          260 Recruiting                                       1700
          270 Payroll                                          1700

27 rows selected.

HR@pdbprod1> purge recyclebin;

Recyclebin purged.
HR@pdbprod1> show recyclebin

2、删除表并且不进入回收站，使用不了闪回drop恢复
HR@pdbprod1> drop table test purge;

Table dropped.
HR@pdbprod1> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist
HR@pdbprod1> show recyclebin

使用RMAN备份恢复表

1、查看数据库备份，恢复到比备份pbdprod1大一点的控制文件的scn号，或者查找删除表的时间，恢复到删除表时间之前
RMAN> list backup of pluggable database pdbprod1;
List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
33      Full    176.13M    DISK        00:00:40     25-AUG-23      
        BP Key: 33   Status: AVAILABLE  Compressed: YES  Tag: TAG20230825T061409
        Piece Name: /u01/app/oracle/fast_recovery_area/PRODCDB/22B36BED61E5436CE053650200C0A2E9/backupset/2023_08_25/o1_mf_nnndf_TAG20230825T061409_lghopwh3_.bkp
  List of Datafiles in backup set 33
  Container ID: 3, PDB Name: PDBPROD1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 2906673    25-AUG-23 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf
  8       Full 2906673    25-AUG-23 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
  9       Full 2906673    25-AUG-23 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/PDBPROD1_users01.dbf
  13      Full 2906673    25-AUG-23 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/example01.dbf
  26      Full 2906673    25-AUG-23 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/old_data_tbs01.dbf
  27      Full 2906673    25-AUG-23 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/cust_data01.dbf

2、查看控制文件备份
RMAN> list backup of controlfile;
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
28      Full    17.48M     DISK        00:00:00     24-AUG-23      
        BP Key: 28   Status: AVAILABLE  Compressed: NO  Tag: TAG20230824T235415
        Piece Name: /u01/app/oracle/fast_recovery_area/PRODCDB/autobackup/2023_08_24/o1_mf_s_1145750055_lggz975g_.bkp
  Control File Included: Ckp SCN: 2865986      Ckp time: 24-AUG-23

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29      Full    17.48M     DISK        00:00:02     25-AUG-23      
        BP Key: 29   Status: AVAILABLE  Compressed: N