Daily Archives: 16.11.2015

Today on my test system I did delete by mistake all the datafiles from one PDB on Oracle 12.1.0.2.

Then, when I wanted to drop it from the CDB, I was getting the error:

SQL> drop pluggable database PDB2_C2 including datafiles;
drop pluggable database PDB2_C2 including datafiles
*
ERROR at line 1:
ORA-01116: error in opening database file 19
ORA-01110: data file 19: '/shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

After I tried to see if RMAN would propose any solution, knowing that there were no backups or archivelogs.

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
62         CRITICAL OPEN      16-NOV-15     System datafile 19: '/shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf' is corrupt
22         CRITICAL OPEN      16-NOV-15     System datafile 19: '/shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf' is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. If file /shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf was unintentionally renamed or moved, restore it
2. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair
3. Please contact Oracle Support Services to resolve failure 62: System datafile 19: '/shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf' is corrupt

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
no automatic repair options available

With the help of my Trivadis colleague Daniele Massimi quickly we found the solution.
The CDB was now in mount mode.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 16 17:28:43 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             973082528 bytes
Database Buffers          520093696 bytes
Redo Buffers               13848576 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 19 - see DBWR trace file
ORA-01110: data file 19: '/shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf'

SQL> alter database datafile 19 offline drop;
alter database datafile 19 offline drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "19"

SQL> alter session set container=PDB2_C2;
Session altered.

SQL> alter database datafile 19 offline drop;
Database altered.

SQL> alter session set container=cdb$root;
Session altered.

SQL> alter database open;
Database altered.

SQL> drop pluggable database PDB2_C2 including datafiles;
Pluggable database dropped.

Moral of the story: be careful when deleting files. 🙂