Dog eat Dog… Oracle deletes itself by mistake!

While implementing the backup on a new DB inherited from a customer, I scheduled our standard backup “type disk” procedure through rman, on Windows.
The morning after I saw that the “delete obsolete” tried to delete ALL CURRENT DATAFILES!!

i criteri di conservazione RMAN verranno applicati al comando
i criteri di conservazione RMAN sono impostati su una ridondanza 1
canale allocato: ORA_DISK_1
canale ORA_DISK_1: sid=29 devtype=DISK
Eliminazione dei seguenti backup e copie obsoleti:
Tipo Chiave Ora fine Nome file/Handle
-------------------- ------ ------------------ --------------------
Set di backup 917 28-GIU-11
...
Set di backup 927 29-GIU-11
Backup piece 1005 29-GIU-11 H:\ORACLE\BACKUP\ORAPERSP\RMAN\SPFILEBCK_20110629
Copia file di dati 14 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF
Copia file di dati 16 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TOOLS01.DBF
Copia file di dati 17 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\USERS01.DBF
Copia file di dati 18 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\DRSYS01.DBF
Copia file di dati 19 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\EXAMPLE01.DBF
Copia file di dati 20 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\ODM01.DBF
Copia file di dati 21 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\XDB01.DBF
Copia file di dati 22 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\CWMLITE01.DBF
Copia file di dati 23 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLDATI01.ORA
Copia file di dati 24 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLINDEX01.ORA
Copia file di dati 25 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\OEM_REPOSITORY1.ORA
Copia file di dati 26 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\SYSTEM01.DBF
Copia file di dati 27 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\UNDOTBS01.DBF
backup piece eliminata
...
backup piece eliminata
handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\C-2220366420-20110628-02 recid=990 stamp=755031582
backup piece eliminata
handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\C-2220366420-20110629-00 recid=1002 stamp=755130872
backup piece eliminata
handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\CTL_20110629 recid=1004 stamp=755130883
backup piece eliminata
handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\SPFILEBCK_20110629 recid=1005 stamp=755130885
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_1 channel at 06/29/2011 22:34:55
ORA-19584: file E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF giĆ  in usoRecovery Manager ha terminato.

That’s because all current datafiles were registered into recovery catalog as backup copy. With a recovery redundancy of 1, all datafiles were set as obsolete! But since it’s windows, a delete command doesn’t delete datafiles if they are already in use. What it was on unix? We had just luck!

Then we had to uncatalog all copies.


RMAN> list copy;

la specifica non corrisponde a nessuno dei log di archivio del Recovery Catalog

Lista di copie del file di dati
Chiave SCN Ckp file S Ora di completamento Nome Ora ckp
------- ---- - -------------------- ---------- -------------------- ----
26 1 X 29-NOV-10 18535127593 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\SYSTEM01.DBF
27 2 X 29-NOV-10 18535127762 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\UNDOTBS01.DBF
14 3 X 29-NOV-10 18535122625 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF
16 4 X 29-NOV-10 18535123721 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TOOLS01.DBF
17 5 X 29-NOV-10 18535124423 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\USERS01.DBF
18 6 X 29-NOV-10 18535124439 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\DRSYS01.DBF
19 7 X 29-NOV-10 18535124453 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\EXAMPLE01.DBF
20 8 X 29-NOV-10 18535124554 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\ODM01.DBF
21 9 X 29-NOV-10 18535125790 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\XDB01.DBF
22 10 X 29-NOV-10 18535125874 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\CWMLITE01.DBF
23 11 X 29-NOV-10 18535125887 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLDATI01.ORA
24 12 X 29-NOV-10 18535126750 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLINDEX01.ORA
25 13 X 29-NOV-10 18535127211 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\OEM_REPOSITORY1.ORA


RMAN> change copy of datafile 1..N uncatalog;

copia non catalogata del file di dati
filename di copia del file di dati=E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF recid=14 stamp=736336991
Oggetti 1 non catalogati
...

until no “obsolete” current datafile were reported!


RMAN> report obsolete;

i criteri di conservazione RMAN verranno applicati al comando
i criteri di conservazione RMAN sono impostati su una ridondanza 1
non sono stati trovati backup obsoleti

Lesson learned: never schedule delete obsolete without actually checking what could be deleted!

The following two tabs change content below.

Ludovico

Oracle ACE Director and Principal Consultant at Trivadis
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Principal Consultant for Trivadis, the leading Oracle consulting firm in Switzerland and German-speaking Europe.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.