FORMAS DE DROPEAR UNA BASE DE DATOS – DROP DATABASE

A continuación se presentan 3 métodos mas usados para dropear una Base de datos Oracle, a los cuales se les puede sacar provecho en distintos escenarios:

 1.- DROP database  command:

El comando “drop database “, esta disponible desde oracle 10gR1 y puede ser usado desde SQLPLUS y RMAN.De hecho desde RMAN este comando es mas flexible por que tenemos otras opciones disponibles.

El comando DROP DATABASE, elimina los siguientes elementos de una BD:

  • Datafiles
  • Redo Logfiles
  • Controlfiles
  • Spfile

¿Que no elimina el comando DROP DATABASE?

  • init.ora file
  • password files
  • listener.ora
  • la entrada de la BD en el oratab.
sintaxis: 
shutdown abort;
startup mount exclusive restrict;
drop database;

Tener en cuenta que la Base de datos debe ser montada en modo  EXCLUSIVE y RESTRICTED, de lo contrario nos mostrará el siguiente error:

SQL> DROP DATABASE;
DROP DATABASE
*ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

Caso práctico desde SQL PLUS:
SQL> shut abort
ORACLE instance shut down.

SQL> startup mount exclusive restrict pfile=’/home/oracle/pfile_bdprod.ora’;
ORACLE instance started.

Total System Global Area 7.0551E+10 bytes
Fixed Size 2253536 bytes
Variable Size 6442454304 bytes
Database Buffers 6.3888E+10 bytes
Redo Buffers 218234880 bytes
Database mounted.
SQL> drop database;
Database dropped.

DROP DATABASE desde RMAN: al usar el comando Drop database desde RMAN, tenemos disponible las siguientes variaciones del comando:

1. DROP DATABASE;
2. DROP DATABASE NOPROMPT;
3. DROP DATABASE INCLUDING BACKUPS;
4. DROP DATABASE INCLUDING BACKUPS NOPROMPT;

basicamente la diferencia con respecto al metodo clasico usado desde SQLPlus es que adicional a la Base de datos, tambien podemos borrar: backup sets, proxy copies, image copies, y archivelogs asociados a la BD a borrar.

Caso práctico desde RMAN:

RMAN> CONNECT TARGET SYS@oratest

target database Password: 
connected to target database: ORACLEJP (DBID=1748401226)

RMAN> STARTUP FORCE MOUNT
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

RMAN> DROP DATABASE INCLUDING BACKUPS;
database name is "ORACLEJP" and DBID is 1748401226

Do you really want to drop all backups and the database (enter YES or NO)? yes (BY DEFAULT ITS PROMPT)

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=127 devtype=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
18      18      1   1   AVAILABLE   DISK        /RMAN/ORACLE_bkp1
19      19      1   1   AVAILABLE   DISK        /RMAN/ARCH_ORACLE_bkp1
deleted backup piece
backup piece handle=/RMAN/ORACLE_bkp1 recid=18 stamp=711242843
deleted backup piece
backup piece handle=/RMAN/ARCH_ORACLE_bkp1 recid=19 stamp=711243739
Deleted 2 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=127 devtype=DISK

List of Control File Copies
Key     S Completion Time Ckp SCN    Ckp Time        Name
------- - --------------- ---------- --------------- ----
2       A 27-DEC-09       2754635    27-DEC-09       D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
202     1    132     A 16-JAN-10 /ORACLE/PRODUCT/10.2.0/ARCHIVE/ORACLE/ORACLE_ARCH_132_701523840_1
203     1    133     A 16-JAN-10 /ORACLE/PRODUCT/10.2.0/ARCHIVE/ORACLE/ORACLE_ARCH_133_701523840_1
204     1    134     A 16-JAN-10 /ORACLE/PRODUCT/10.2.0/ARCHIVE/ORACLE/ORACLE_ARCH_134_701523840_1
....
..
.
247     1    177     A 04-FEB-10 /ORACLE/PRODUCT/10.2.0/ARCHIVE/ORACLE/ORACLE_ARCH_177_701523840_1
248     1    178     A 04-FEB-10 /ORACLE/PRODUCT/10.2.0/ARCHIVE/ORACLE/ORACLE_ARCH_178_701523840_1
249     1    179     A 04-FEB-10 /ORACLE/PRODUCT/10.2.0/ARCHIVE/ORACLE/ORACLE_ARCH_179_701523840_1

deleted control file copy
control file copy filename=/ORACLE/PRODUCT/10.2.0/STANDBY/STANDBYCTL01.CTL recid=2 stamp=706679151
deleted archive log
archive log filename=/ORACLE/PRODUCT/10.2.0/ARCHIVE/ORACLE/ORACLE_ARCH_132_701523840_1 recid=202 stamp=708474283
deleted archive log
...
..
.
archive log filename=/ORACLE/PRODUCT/10.2.0/ARCHIVE/ORACLE/ORACLE_ARCH_178_701523840_1 recid=248 stamp=710118565
deleted archive log
archive log filename=/ORACLE/PRODUCT/10.2.0/ARCHIVE/ORACLE/ORACLE_ARCH_179_701523840_1 recid=249 stamp=710248827
Deleted 49 objects

database name is "ORACLEJP" and DBID is 1748401226
database dropped

RMAN>

Usando el comando drop database including backups NOPROMPT, RMAN no pedirá confirmación para dropear la Base de datos y sus backups asociados.

2.-DBCA modo silent

sintaxis:
dbca -silent -deleteDatabase -sourceDB <Oracle_Sid>

Caso práctico:
Setear variables de ambiente previamente:

[oracle@limspbdprev02 dbs]$ env | grep ORA
ORACLE_SID=BDPROD
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/oracle11204/product/11.2.0.4

[oracle@limspbdprev02 dbs]$ dbca -silent -deleteDatabase -sourceDB BDPROD
Connecting to database
4% complete
9% complete
14% complete
19% complete
23% complete
28% complete
47% complete
Updating network configuration files
52% complete
Deleting instance and datafiles
76% complete
100% complete
Look at the log file “/oracle/cfgtoollogs/dbca/BDPROD.log” for further details

–ver log :
[oracle@limspbdprev02 dbs]$ tail -100 /oracle/cfgtoollogs/dbca/BDPROD.log | more
The Database Configuration Assistant will delete the Oracle instance and datafiles for your database. All information in the database will be destroyed. Do you want to proceed?
Connecting to database
DBCA_PROGRESS : 4%
DBCA_PROGRESS : 9%
DBCA_PROGRESS : 14%
DBCA_PROGRESS : 19%
DBCA_PROGRESS : 23%
DBCA_PROGRESS : 28%
DBCA_PROGRESS : 47%
Updating network configuration files
DBCA_PROGRESS : 52%
Deleting instance and datafiles
DBCA_PROGRESS : 76%
DBCA_PROGRESS : 100%
Database deletion completed.

 

3.-DBCA modo gráfico

DBCA ->seleccionar BD -> delete database->next->next->finish

drop database