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(2222);

SCOTT@pdb1>select * from it;

        ID

----------

       9999

       8888

备份整个数据库（原来有备份的情况，注意备份优化参数）

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

backup as compressed backupset incremental level 0  database format '/home/oracle/backup4/full_db_level0_%U_%T.bak' ;

sql 'alter system archive log current';

backup as compressed backupset archivelog all tag='arc_bak' format='/home/oracle/backup4/archivelog_%U_%T' skip inaccessible filesperset 6 not  backed up 1 times  delete input;

backup current controlfile format '/home/oracle/backup4/ctl_level0_%U_%T.bak';

backup spfile format '/home/oracle/backup4/spfile_level0_%U_%T.bak';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

3、 删除表空间pdb1:data

SYS@pdb1>drop tablespace data including contents and datafiles;

Tablespace dropped. 

SYS@pdb1>select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

SYSTEM

SYSAUX

UNDOTBS1

TEMP01

4、 恢复表空间（通过alert.log删除日志，确定具体的删除时间，以此来恢复）

2022-08-01T10:11:20.290861+08:00

2022-08-02T21:09:57.979142+08:00

TT00 (PID:57527): Error 12543 received logging on to the standby

2022-08-02T21:13:13.834835+08:00

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

2022-08-02T21:13:17.048894+08:00

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

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

21:09:572022-08-02T21:15:00.988777+08:00

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

备注：data表空间于2022-08-02T21:13:13.834835+08:00 删除

恢复方法步骤：

①使PDB1处于mount状态；

②rman基于时间点的恢复表空间；

具体操作：

SYS@pdb1>show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         3 PDB1                           READ WRITE NO

创建恢复临时目录：

[oracle@db1 ~]$ mkdir -p /home/oracle/aux

[oracle@db1 ~]$ ll /home/oracle/aux

使用命令：

recover tablespace pdb1:data until time "to_date('2022-08-02 21:13:13','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/home/oracle/aux';

备注：中间是恢复过程，文字太多，略，恢复过程中注意报错信息。

检查恢复后data表空间状态：

SYS@pdb1>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP01                         ONLINE

DATA                           OFFLINE

SYS@pdb1>alter tablespace data online;

SYS@pdb1>select owner,segment_name,bytes/1024 kb from dba_segments where segment_name='IT';

OWNER               SEGMENT_NAME          KB

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

SCOTT                     IT                   64

SYS@pdb1>select * from scott.it;

        ID

----------

      9999

      8888

总结：表空间的恢复，有2种方法，第一种是pitr基于时间点的恢复，这种恢复对现有的数据库影响不大，但是有个限制条件是需要检查表空间的关联特性，涉及到哪些表空间，都需要恢复。

第二种：使用利用现有数据库的备份，整个数据库的回滚，如果这样操作数据库丢失的数据就很多了，不建议这么做。

Yicheng16
22.08.02 

-- The End --