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 --
