Drop database avec RMAN

Il y a quelques jours on m'avait demandé de supprimer une bd Oracle et j'ai bien profité pour créer un petit document que pourrait être utile à tous.

echo $ORACLE_SID
MYDB

srvctl remove database -d MYDB


rman target / catalog  user/apssword@BDCATALOG   ou rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 9 14:46:36 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYDB (DBID=2774697611)
connected to recovery catalog database


RMAN> REPORT SCHEMA ;
Report of database schema for database with db_unique_name MMYDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1530     SYSTEM               YES     +DATINXRWMYDB/MMYDB/datafile/system.265.870780837
2    1490     SYSAUX               NO      +DATINXRWMYDB/MMYDB/datafile/sysaux.266.870780837
3    695      UNDOTBS1             YES     +DATINXRWMYDB/MMYDB/datafile/undotbs1.261.870780837
4    5        USERS                NO      +DATINXRWMYDB/MMYDB/datafile/users.259.870780839
5    5        TBS_RO_TEST01        NO      +DATINXROMYDB/MMYDB/datafile/tbs_ro_test01.265.870780839
6    10       TBS_PDF_01           NO      +DATINXROMYDB/MMYDB/datafile/tbs_pdf_01.dbf
7    200      GGS_DATA             NO      +DATINXRWMYDB/MMYDB/datafile/ggs_data.267.870780837

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    100      TEMP                 100         +TEMPMYDB/MMYDB/tempfile/temp.256.870780879
2    500      TEMP_NEW             32767       +TEMPMYDB/MMYDB/TEMPFILE/temp_new.257.881479571


RMAN> SHUTDOWN IMMEDIATE ;
database closed
database dismounted
Oracle instance shut down

RMAN> STARTUP MOUNT ;
connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    5368709120 bytes

Fixed Size                     5284640 bytes
Variable Size               1895832800 bytes
Database Buffers            3456106496 bytes
Redo Buffers                  11485184 bytes


RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION

Si on veut tout supprimer et ne pas laisser de traces des sauvegardes prises pour cette base de données on doit ajouter le INCLUDING BACKUPS.

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "MYDB" and DBID is 2774697611

Do you really want to drop all backups and the database (enter YES or NO)? YES

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1371427 1371426 1   1   AVAILABLE   DISK        /bkpdskrman/MYDB/01pue54s_1_1
1371445 1371439 1   1   AVAILABLE   DISK        /bkpdskrman/MYDB/02pue54v_1_1
1371446 1371440 1   1   AVAILABLE   DISK        /bkpdskrman/MYDB/03pue55o_1_1
1371464 1371461 1   1   AVAILABLE   DISK        /bkpdskrman/MYDB/04pue55s_1_1
1371474 1371472 1   1   AVAILABLE   DISK        /bkpdskrman/MYDB/05pue55v_1_1
deleted backup piece
backup piece handle=/bkpdskrman/MYDB/01pue54s_1_1 RECID=1 STAMP=870782108
deleted backup piece
backup piece handle=/bkpdskrman/MYDB/02pue54v_1_1 RECID=2 STAMP=870782111
deleted backup piece
backup piece handle=/bkpdskrman/MYDB/03pue55o_1_1 RECID=3 STAMP=870782137
deleted backup piece
backup piece handle=/bkpdskrman/MYDB/04pue55s_1_1 RECID=4 STAMP=870782140
deleted backup piece
backup piece handle=/bkpdskrman/MYDB/05pue55v_1_1 RECID=5 STAMP=870782144
Deleted 5 objects


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name MMYDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
1371361 1    1       A 04-FEB-15
Name: +ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_1.312.870781535

1371362 1    2       A 04-FEB-15
Name: +ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_2.311.870781733

1371416 1    3       A 04-FEB-15
Name: +ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_3.310.870782095

1371421 1    4       A 04-FEB-15
Name: +ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_4.309.870782107

1371559 1    5       A 04-FEB-15
Name: +ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_5.321.870793853

1371438 1    5       A 04-FEB-15
Name: +ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_5.308.870782139

1371602 1    67      A 04-FEB-15
Name: +ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_67.320.870793939

1371603 1    68      A 04-FEB-15
Name: +ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_68.319.870793941

1371604 1    69      A 04-FEB-15
Name: +ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_69.318.870793941

deleted archived log
archived log file name=+ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_1.312.870781535 RECID=1 STAMP=870781535
deleted archived log
archived log file name=+ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_2.311.870781733 RECID=2 STAMP=870781733
deleted archived log
archived log file name=+ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_3.310.870782095 RECID=3 STAMP=870782094
deleted archived log
archived log file name=+ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_4.309.870782107 RECID=4 STAMP=870782106
deleted archived log
archived log file name=+ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_5.321.870793853 RECID=9 STAMP=870793852
deleted archived log
archived log file name=+ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_5.308.870782139 RECID=5 STAMP=870782139
deleted archived log
archived log file name=+ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_67.320.870793939 RECID=9 STAMP=870793940
deleted archived log
archived log file name=+ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_68.319.870793941 RECID=10 STAMP=870793940
deleted archived log
archived log file name=+ARCHMYDB/MMYDB/archivelog/2015_02_04/thread_1_seq_69.318.870793941 RECID=11 STAMP=870793941
Deleted 9 objects


database name is "MYDB" and DBID is 2774697611
database dropped

database name is "MYDB" and DBID is 2774697611
database unregistered from the recovery catalog

Bien sûr, il reste à faire le ménage dans les fichiers *.ora et selon le cas les policies de backup qu'auraient pu être reliés à cette base de données.