oops, sorry I dropped the wrong directory

oops, sorry I dropped the wrong directory

There are some days, that you play around in your lab and end up with a situation you hope will never happen in real life. Today was one of those days. While playing around with 12.2 pdb’s I found following scenario.

Imagine a container database in noarchivelog. I would not recommend to run in noarchivelog, but sometimes, it still happens. At a certain moment in time, you play around with the db_create_file_dest parameter and you decide to set it to /u03 because you decide that’s a better place to have your new pdb’s. Which is a plausible option in a real life situation actually. So clone an existing pdb and it will use that destination, which is perfectly normal. If you then for any reason whatsoever remove that directory … you get an interesting situation which I hope you never might need to recover from. It might not be the most optimal way, but this is what works. when you’re in archivelog mode, there are better ways, but this is how i get my lab database open again. I created a dummy situation for this blogpost, but you will get the picture. Here we go.

we start with 2 pdbs, the seed and one I created by default with the dbca


SQL> select con_id,name,open_mode from v$pdbs;
CON_ID     NAME                           OPEN_MODE
---------- ------------------------------ ---------- 
2          PDB$SEED                       READ ONLY 
3          PDBS04N01                      READ WRITE
SQL>

Then we will mimic the situation. So I will change the db_create_file_dest parameter to a, in this lab-case, temporary directory.

SQL> show parameter db_create_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
SQL> !mkdir -p /u01/app/oracle/oradata/removeme
SQL> !ls -ld /u01/app/oracle/oradata/removeme
drwxr-xr-x 2 oracle oinstall 4096 Jun 21 20:40 /u01/app/oracle/oradata/removeme
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/removeme' scope=both;
System altered.
SQL>

So far so good. We’re all setup. Very easy thing to do, clone a pluggable database. In this testcase I have only one, so not too many choices


SQL> create pluggable database screwme from PDBS04N01;

Pluggable database created.

SQL> alter pluggable database screwme open ;

Pluggable database altered.

SQL>

and the temporary directory is actually used

SQL> !tree /u01/app/oracle/oradata/removeme
/u01/app/oracle/oradata/removeme
└── ORCL
└── 527DBC23DD991FACE0534001000A7F6F
└── datafile
├── o1_mf_sysaux_dnohkgor_.dbf
├── o1_mf_system_dnohkglk_.dbf
├── o1_mf_temp_dnohkgot_.dbf
├── o1_mf_undotbs1_dnohkgos_.dbf
└── o1_mf_users_dnohkgot_.dbf

3 directories, 5 files

SQL>

There is the fun part, screwing it.

SQL> !rm -Rf /u01/app/oracle/oradata/removeme

SQL> !ls -ld /u01/app/oracle/oradata/removeme
ls: cannot access /u01/app/oracle/oradata/removeme: No such file or directory

SQL>

It’s gone and we’re officially screwed now. Ok, then we need to fix it. What you see is, that the immediate moment you delete the directory, basically nothings happens and also the alertlog remains empty. The database needs some time to realise it. I did not time it on how much the timing is, but then the complete CDB goes down. This is intended behaviour.
During that time I was creating some extra tablespaces, that succeeds by the way, and eventually the cdb went down. ok, Time to fix it.

First things first, we need an instance. It will fail to open, so startup mount would do it. I used the normal startup to show the error.

[oracle@labvms04n01 u01]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 21 21:06:00 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size		    8800712 bytes
Variable Size		  369100344 bytes
Database Buffers	  662700032 bytes
Redo Buffers		    7974912 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13:
'/u01/app/oracle/oradata/removeme/ORCL/527DBC23DD991FACE0534001000A7F6F/datafile
/o1_mf_system_dnohkglk_.dbf'


SQL>

Database says no. And she’s right because the file is really gone. So given the situation, you never can get that pdb back because of the noarchivelog mode and the info isn’t in the redologs, so this pdb is definitely lost. So the only option to get your CDB open again, as far as I know, so please let me know if I made a mistake here, then I’ll be happy to correct it, is get rid of this pdb and then the cdb will be happy to open. The first thing everybody would try first:

SQL> drop pluggable database screwme including datafiles;
drop pluggable database screwme including datafiles
*
ERROR at line 1:
ORA-01109: database not open


SQL>

It simply does not work. To drop a pluggable database you would need an open container database. So … let’s look further, because there must be a way to get your database open again. When you know how, it’s actually fairly easy.
Make sure the pdb _lockdown is set to null. If not, note the value and reset the parameter to null. You can find more information about this parameter here

So the database is in mount, but you can actually switch to the pluggable database in your session. We need that now.

SQL> alter session set container=screwme;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
SCREWME
SQL>

The obvious, it doesn’t work, but yes i did try it on first attempt, so I want to share it here as well. Dropping it does not work.

SQL> drop pluggable database screwme including datafiles;
drop pluggable database screwme including datafiles
*
ERROR at line 1:
ORA-01109: database not open


SQL>

but what DID work is getting rid of all the datafiles of this pdb, they are unrecoverable anyways and then the database should spwan some errors, but at least it should open. So let’s try that.
I looked up in alertlog, just a simple search in it, all the datafiles of the pdb and then we drop them offline.

SQL>  alter database datafile '/u01/app/oracle/oradata/removeme/ORCL/527DBC23DD991FACE0534001000A7F6F/datafile/o1_mf_users_dnohkgot_.dbf' offline for drop;
 alter database datafile '/u01/app/oracle/oradata/removeme/ORCL/527DBC23DD991FACE0534001000A7F6F/datafile/o1_mf_users_dnohkgot_.dbf' offline ffor drop
                                                                                                                                             *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> alter database datafile '/u01/app/oracle/oradata/removeme/ORCL/527DBC23DD991FACE0534001000A7F6F/datafile/o1_mf_users_dnohkgot_.dbf' offline for drop;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/removeme/ORCL/527DBC23DD991FACE0534001000A7F6F/datafile/o1_mf_undotbs1_dnohkgos_.dbf' offline for drop ;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/removeme/ORCL/527DBC23DD991FACE0534001000A7F6F/datafile/o1_mf_sysaux_dnohkgor_.dbf' offline for drop;

Database altered.

SQL> alter database datafile '/home/oracle/totally_screwed01.dbf' offline for drop;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/removeme/ORCL/527DBC23DD991FACE0534001000A7F6F/datafile/o1_mf_system_dnohkglk_.dbf' offline for drop;

Database altered.

SQL> 

So we got rid of these and then we can open the container.

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter database open;

Database altered.

SQL>

Hey look who’s back 🙂 So that means that we have an OPEN cdb, so we can get rid of the pluggable database now

SQL> drop pluggable database screwme including datafiles;

Pluggable database dropped.

SQL>

And as expected, this works too. No corruptions anything, just a database with one pdb less.

As always, questions, remarks? find me on twitter @vanpupi

Comments are closed.
%d bloggers like this: