一、需求，测试环境rm删除了数据文件，db重启后无法open
测试环境12.2，linux 单实例，存在一个pdb, 没有任何备份！ 允许数据丢失，如何恢复db open!
RMAN> report schema ; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name ORCL List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 810 SYSTEM *** /picclife/app/oracle/oradata/orcl/system01.dbf 3 510 SYSAUX *** /picclife/app/oracle/oradata/orcl/sysaux01.dbf 4 270 UNDOTBS1 *** /picclife/app/oracle/oradata/orcl/undotbs01.dbf 5 250 PDB$SEED:SYSTEM *** /picclife/app/oracle/oradata/orcl/pdbseed/system01.dbf 6 330 PDB$SEED:SYSAUX *** /picclife/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf 7 5 USERS *** /picclife/app/oracle/oradata/orcl/users01.dbf 8 100 PDB$SEED:UNDOTBS1 *** /picclife/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf 9 260 C11PDB:SYSTEM *** /picclife/app/oracle/oradata/orcl/c11pdb/system01.dbf 10 360 C11PDB:SYSAUX *** /picclife/app/oracle/oradata/orcl/c11pdb/sysaux01.dbf 11 100 C11PDB:UNDOTBS1 *** /picclife/app/oracle/oradata/orcl/c11pdb/undotbs01.dbf 12 5 C11PDB:USERS *** /picclife/app/oracle/oradata/orcl/c11pdb/users01.dbf 13 0 TEST_TBS *** /tmp/test_tbs_01.dbf 14 0 C11PDB:TEST_TBS *** /tmp/test_tbs_02.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 219 TEMP 32767 /picclife/app/oracle/oradata/orcl/temp01.dbf 2 64 PDB$SEED:TEMP 32767 /picclife/app/oracle/oradata/orcl/pdbseed/temp012021-03-04_22-18-29-699-PM.dbf 3 129 C11PDB:TEMP 32767 /picclife/app/oracle/oradata/orcl/c11pdb/temp01.dbf RMAN> list backup; RMAN> list backup of datafile 13,14; specification does not match any backup in the repository # blkid /dev/sda1: UUID="da98feda-e6f9-4093-a21f-3bcc08e75888" TYPE="xfs" /dev/sda2: UUID="HfPVhM-rMvw-kapY-za7u-sf9a-Q5MD-RVrnu8" TYPE="LVM2_member" /dev/sda3: UUID="SfLui4-Nzcb-tDig-YLga-D40z-NSYy-3dn0Dm" TYPE="LVM2_member" /dev/mapper/ol-root: UUID="a6f36f93-52fc-4f34-a055-54211ef601d8" TYPE="xfs" /dev/mapper/ol-swap: UUID="913d908f-1ca0-4cba-85fc-280ecc629ea8" TYPE="swap" /dev/mapper/ol-home: UUID="c3b07688-eada-4185-858d-7216eb0a2bdc" TYPE="xfs" xfs文件系统中用xfsdump备份和恢复文件,需要先有备份才能进行恢复 https://blog.51cto.com/u_14449524/2433036
二、强制删除表空间，将DB拉起来
2.1 处理CDB的一个表空间
由于没有提前进行备份，无法使用该工具进行恢复操作，本次采用强制删除！ 确保大部分可用！ 丢失13、14文件，13文件在CDB中，14在PDB中
此时CDB 处于Mount状态 select file#,ts#,status,bytes,name,con_id from v$datafile where file# in(13,14); FILE# TS# STATUS BYTES NAME CON_ID ---------- ---------- ---------- ---------- ------------------------------ ---------- 13 6 ONLINE 0 /tmp/test_tbs_01.dbf 1 14 6 ONLINE 0 /tmp/test_tbs_02.dbf 3 alter database datafile 13 offline drop; alter database datafile 14 offline drop; ORA-01516: nonexistent log file, data file, or temporary file "14" in the current container alter pluggable database datafile 14 offline drop; ORA-01109: database not open SQL> select file#,ts#,status,bytes,name,con_id from v$datafile where file# in(13,14); FILE# TS# STATUS BYTES NAME CON_ID ---------- ---------- ---------- ---------- ------------------------------ ---------- 13 6 RECOVER 0 /tmp/test_tbs_01.dbf 1 14 6 ONLINE 0 /tmp/test_tbs_02.dbf 3 SQL> alter database open; SQL> select file_name,file_id,tablespace_name,bytes,status ,ONLINE_STATUS from dba_data_files where file_id=13; FILE_NAME FILE_ID TABLESPACE_NAME BYTES STATUS ONLINE_STATUS ------------------------------ ---------- ------------------------------ ---------- ---------- -------------- /tmp/test_tbs_01.dbf 13 TEST_TBS AVAILABLE RECOVER SQL> select file_name,file_id,tablespace_name,bytes,status ,ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='TEST_TBS'; FILE_NAME FILE_ID TABLESPACE_NAME BYTES STATUS ONLINE_STATUS ------------------------------ ---------- ------------------------------ ---------- ---------- -------------- /tmp/test_tbs_01.dbf 13 TEST_TBS AVAILABLE RECOVER alter tablespace test_tbs drop datafile 13 * ERROR at line 1: ORA-03261: the tablespace TEST_TBS has only one file SQL> drop tablespace test_tbs INCLUDING CONTENTS ; SQL> select file_name,file_id,tablespace_name,bytes,status ,ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='TEST_TBS'; no rows selected
2.2 处理PDB的表空间文件
此时CDB误删除的13号文件，已经被彻底删除记录，只剩下PDB所在的14号文件未处理
SQL> select file#,ts#,status,bytes,name,con_id from v$datafile where file# in(13,14); FILE# TS# STATUS BYTES NAME CON_ID ---------- ---------- ---------- ---------- ------------------------------ ---------- 14 6 ONLINE 0 /tmp/test_tbs_02.dbf 3 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 C11PDB MOUNTED SQL> alter session set container=C11PDB; alter pluggable database datafile 14 offline drop; SQL> select file#,ts#,status,bytes,name,con_id from v$datafile where file# in(13,14); FILE# TS# STATUS BYTES NAME CON_ID ---------- ---------- ---------- ---------- ------------------------------ ---------- 14 6 OFFLINE 0 /tmp/test_tbs_02.dbf 3 SQL> select file_name,file_id,tablespace_name,bytes,status ,ONLINE_STATUS from dba_data_files where file_id=14; ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 12 needs media recovery ORA-01110: data file 12: '/picclife/app/oracle/oradata/orcl/c11pdb/users01.dbf' SQL> recover datafile 12; Media recovery complete. 依次recover多个pdb文件后,最后open pdb SQL> select file_name,file_id,tablespace_name,bytes,status ,ONLINE_STATUS from dba_data_files where file_id=14; FILE_NAME FILE_ID TABLESPACE_NAME BYTES STATUS ONLINE_STATUS ------------------------------ ---------- ------------------------------ ---------- ---------- -------------- /tmp/test_tbs_02.dbf 14 TEST_TBS AVAILABLE OFFLINE select file_name,file_id,tablespace_name,bytes,status ,ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='TEST_TBS'; FILE_NAME FILE_ID TABLESPACE_NAME BYTES STATUS ONLINE_STATUS ------------------------------ ---------- ------------------------------ ---------- ---------- -------------- /tmp/test_tbs_02.dbf 14 TEST_TBS AVAILABLE OFFLINE SQL> drop tablespace test_tbs INCLUDING CONTENTS ; 重启确认，观察DB Alert无异常！ SQL> shutdown immediate; SQL> startup