1、 数据库版本

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

2、创建表空间 pdb1:data，录入数据 scott.it表 8888

SYS@pdb1>create tablespace data datafile ‘/oradata/ORCL/pdb1/data01.dbf’ size 100m autoextend on maxsize 2g;

SYS@pdb1>alter user scott default tablespace data;

SYS@pdb1>conn scott/oracle@pdb1

Connected.

SCOTT@pdb1>insert into it values(3333);

16:33:39 SYS@pdb1>select * from scott.it;

        ID

----------

       999

       888

      1111

      2222

      3333

备份库使用上一节备份数据；

3、删除 PDB1 data表空间

16:34:09 SYS@pdb1>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP01                         ONLINE

DATA                           ONLINE

16:36:22 SYS@pdb1>drop tablespace data including contents and datafiles;

Tablespace dropped.

16:36:41 SYS@pdb1>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP01                         ONLINE

查看后台 alert.log日志

2022-08-06T16:36:38.296678+08:00

PDB1(3):drop tablespace data including contents and datafiles

2022-08-06T16:36:41.586729+08:00

PDB1(3):Deleted file /oradata/ORCL/pdb1/data.dbf

PDB1(3):Completed: drop tablespace data including contents and datafiles

4、恢复表空间及数据

关闭整个数据库，恢复备份控制文件，并启动到 mount状态；

[oracle@db1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 6 19:36:23 2022

Version 19.3.0.0.0

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

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/home/oracle/backup/ctl_level0_ov14gmkv_1_1_20220806.bak';

Starting restore at 06-AUG-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=379 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/oradata/ORCL/control01.ctl

output file name=/flash_recovery_area/ORCL/control02.ctl

Finished restore at 06-AUG-22

RMAN> alter database mount;

run{

set until time "to_date('2022-08-06 19:32:43','yyyy-mm-dd hh24:mi:ss')";

restore database ;

recover database;

}

过程略：

Starting recover at 06-AUG-22

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /flash_recovery_area/ORCL/archivelog/2022_08_06/o1_mf_1_2_kgwn0rtj_.arc

archived log for thread 1 with sequence 3 is already on disk as file /oradata/ORCL/redo03.log

archived log file name=/flash_recovery_area/ORCL/archivelog/2022_08_06/o1_mf_1_2_kgwn0rtj_.arc thread=1 sequence=2

archived log file name=/oradata/ORCL/redo03.log thread=1 sequence=3

media recovery complete, elapsed time: 00:00:01

Finished recover at 06-AUG-22

SYS@orcl>alter database open resetlogs;

Database altered.

SYS@orcl>show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

检查表空间及数据：

SYS@pdb1>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP01                         ONLINE

DATA                           ONLINE

SYS@pdb1>select file_name from dba_data_files;

FILE_NAME

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

/oradata/ORCL/pdb1/undotbs01.dbf

/oradata/ORCL/pdb1/sysaux01.dbf

/oradata/ORCL/pdb1/system01.dbf

/oradata/ORCL/pdb1/data.dbf

SYS@pdb1>select * from scott.it;

        ID

----------

       999

       888

      1111

      2222

      3333

备注：到此为止，表空间及数据都得以恢复！产生了性的 incarnation，做一个全备份吧。

总结：表空间认为的删除或者丢失，都会造成数据库无法启动或者出现其他的报错，此种操作方法如果在数据及归档日志完成情况下，不会造成数据丢失，操作时需要小心谨慎，否则可能造成数据丢失。

参考 weixin_39582569 第二种方法没有做成功。

Yicheng16
22.08.06

-- The End --