Pluggable DB is down due to DB files removed
Pluggable DB is down due to DB files removed by mistake.
Overview of the issue
- In One container database in dev env, there are two pluggable databases.
- In 1st PDB two datafiles have been removed by mistake in ASM diskgroup level.
- Due to unavailability of DB files ,PDB creashed. Actually wanted to bringup PDB without those two files.
- While Opening the DB getting below message. Have backup and dont want those two DB files.
- When we recover the file# 64,it’s asking to recover 63 and so on .
- All files have been recovered and dropped those unwanted files and opened the database.
Error:
SQL> alter pluggable database ORDWT open;
alter pluggable database ORDWT open
*
ERROR at line 1:
ORA-65368: unable to open the pluggable database due to errors during recovery
ORA-01110: data file 66: ‘+ORA_DATA01/ORADWT/STG_OWNER01.dbf’
ORA-01157: cannot identify/lock data file 66 – see DBWR trace file
ORA-01110: data file 66: ‘+ORA_DATA01/ORADWT/STG_OWNER01.dbf’
ORA-01113: file 64 needs media recovery
ORA-01110: data file 64: ‘+ORA_DATA/UORADBG/033F319B0C7A42B5E0538030940AC762/DATAFILE/TS_CLM.457.1131683267’
Verify the Databases mode
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
4 ORDWT MOUNTED
5 ORDWD READ WRITE NO
SQL>
Verify Backups and restore archives.
$ rman target /
RMAN> report schema;
report schema reported all the details, extracted all the datafile numbers to recover in one step.
and found all the required archives had been backedup .
Restored the arcihves to archive log dest.
RMAN> run
{
allocate channel t1 type ‘SBT_TAPE’ parms=’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_ORADBG.tdpo)’;
allocate channel t2 type ‘SBT_TAPE’ parms=’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_ORADBG.tdpo)’;
restore archivelog from logseq=32276 until logseq=32293;
}
Drop unwanted datafiles:
alter database datafile 65 offline drop;
alter database datafile 66 offline drop;
Recovered all the files of Pluggable database.
RMAN> recover datafile 64;
Starting recover at 14-JUN-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2794 device type=DISK
starting media recovery
archived log for thread 1 with sequence 32278 is already on disk as file +FRA/UORADBG/ARCHIVELOG/2023_06_14/thread_1_seq_32278.6183.1139481021
archived log for thread 1 with sequence 32279 is already on disk as file +FRA/UORADBG/ARCHIVELOG/2023_06_14/thread_1_seq_32279.9858.1139481021
archived log file name=+FRA/UORADBG/ARCHIVELOG/2023_06_14/thread_1_seq_32278.6183.1139481021 thread=1 sequence=32278
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-JUN-23
RMAN>
SQL> select TS# from v$datafile where file# in (’65’,’66’);
TS#
———-
24
25
SQL> select name from v$tablespace where TS# in (’24’,’25’);
NAME
——————————
TS_IDX_OWNER1
TS_STG_OWNER1
SQL> select TS# from v$datafile where file#=64;
TS#
———-
55
SQL> select name from v$tablespace where TS#=55;
NAME
——————————
TS_CLM
SQL>
SQL> select file# from v$recover_file;
FILE#
———-
65
66
SQL>
SQL> alter database open;
Database altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.12.0.0.0
You have mail in /var/spool/mail/oracle
[ORADBG]$
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
4 ORDWT READ WRITE NO
5 ORDWD READ WRITE NO
SQL>
SQL>
SQL> alter session set container=ORDWT;
Session altered.
SQL>
SQL>
SQL> drop tablespace TS_IDX_OWNER1 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace TS_STG_OWNER1 including contents and datafiles;
Tablespace dropped.
SQL>
SQL> drop user STG_OWNER_AR cascade;
User dropped.
SQL> drop user PRF_OWNER_AR cascade;
User dropped.
SQL>