PURPOSE : This guide will help you on how to manually drop a Database.
NOTE : As with any manual, ensure to test properly in a development environment before attempting to perform it in production.
Hostname: hostname.localdomain
ORACLE_SID: DB1
1. Verify the cron job. Perform Full Backup. DO NOT PROCEED ON DROPPING DB1unless full backup is successful.
Reference:
crontab -l | grep DB1| grep hot_rman
crontab -l | grep DB1| grep run_export_db
>> The last full backup is /EXPORTS/oradata/DB1/export_db_DB1.dmp.gz
2. Check/spool for database files.
2A. View the names of the database's datafiles, redo log files, and control files from data dictionary views V$DATAFILE, V$LOGFILE, and V$CONTROLFILE, respectively. Check also the spfile and archive logs.
2B.Generate log for removing DB files.set lines 250
set pages 999
set heading off
set pages 9999
set feedback off
spool DB1_datafiles.log
select 'ls -l '|| name ||'' from v$datafile;
select 'ls -l '|| member ||'' from V$LOGFILE;
select 'ls -l '|| NAME ||'' from V$CONTROLFILE;
show parameter spfile;
show parameter log_archive
spool off
set lines 250
set pages 999
set heading off
set pages 9999
set feedback off
spool DB1_datafiles_remove.log
select 'rm -rf '|| name ||'' from v$datafile;
select 'rm -rf '|| member ||'' from V$LOGFILE;
select 'rm -rf '|| NAME ||'' from V$CONTROLFILE;
spool off
3. Permanently remove DB1from the DBSPI configuration
C/O Unix Team - Completed.
4. Shutdown DB1 database.
[oracle@oracle11g ~]$ sqlplus '/as sysdba'
SQL> select name from v$database;
SQL> shutdown immediate;
5. Dropping a DatabaseSQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Dropping a database involves removing its datafiles, redo log files, control files, and initialization parameter files.
5A1.For 10g and 11g database, startup mount the database in exclusive and restricted mode.
All of the following conditions must apply:
- The database must be mounted and closed.
- The database must be mounted exclusively--not in shared mode.
- The database must be mounted as RESTRICTED.
OPTION 1:
SQL> startup mount exclusive;
SQL> alter system enable restricted session;SQL> startup mount exclusive;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 322963840 bytes
Database Buffers 92274688 bytes
Redo Buffers 6094848 bytes
Database mounted.
5A2. From a new session, monitor the alert log;SQL> alter system enable restricted session;
System altered.
cd /oracle_home/home/dba/oracle/admin/DB1/bdump
tail -500f alert_DB1.log
5A3. Drop DB1 database:
The DROP DATABASE statement deletes all control files and all other database files listed in the control file.
SQL> drop database;
SQL> exitSQL> drop database;
Database dropped.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
OPTION 2:
Note: The DROP DATABASE statement has no effect on archived log files, nor does it have any effect on copies or backups of the database. It is best to use RMAN to delete such files. If the database is on raw disks, the actual raw disk special files are not deleted.
[oracle@oracle11g ~]$ sqlplus '/as sysdba'
SQL> shutdown immediate
SQL> startup mount exclusiveSQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> alter system enable restricted session;SQL> startup mount exclusive;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 322963840 bytes
Database Buffers 92274688 bytes
Redo Buffers 6094848 bytes
Database mounted.
SQL> exitSQL> alter system enable restricted session;
System altered.
[oracle@oracle11g ~]$ rman target /
RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;[oracle@oracle11g ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 3 08:18:03 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL11G (DBID=889097371, not open)
The option INCLUDING BACKUPS deletes backup sets, proxy copies, image copies, and archived logs associated with the target database from all configured device types.RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;
database name is "ORCL11G" and DBID is 889097371
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
specification does not match any backup in the repository
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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
specification does not match any archived log in the repository
database name is "ORCL11G" and DBID is 889097371
database dropped
RMAN>
5B1. To drop a 9i database, you must remove its datafiles, redo log files, and all other associated files (control files, initialization parameter files, archived log files).
>> Run the OS command for deleting database files generated in Step 2B.
>> Remove init/spfile from $ORACLE_HOME/dbs
cd $ORACLE_HOME/dbs
ls -ltrh *DB1.ora
rm -rf initDB1.ora
>> Remove all archive logs.
6. Verify that datafiles were deleted. Refer to DB1_datafiles.log generated in Step 2A.
7. Update the CI status in ESL from "in production" to "out of production"
8. Remove DB1backup jobs from cron.
9. Reload Listener. Note that DB1 is a 9i db and it uses the 9i Listener.
hostname.localdomain% ps -eaf |grep lsnr
oracle 20353 12726 0 16:49:09 pts/3 0:00 grep lsnr
oracle 5372 1 0 Mar 25 ? 753:17 /oracle_home/home/dba/oracle/product/9.2.0.8-64bit/bin/tnslsnr LISTENER -inheri
oracle 5411 1 0 Mar 25 ? 112:42 /oracle_home/home/dba/oracle/product/10.2.0.3-64bit/bin/tnslsnr LISTENER_10G -i
oracle 5446 1 0 Mar 25 ? 148:19 /oracle_home/home/dba/oracle/product/11.1.0.7/bin/tnslsnr LISTENER_11G -inherit
lsnrctl status LISTENER
Below lines should disappear after reloading.
Instance "DB1", status UNKNOWN, has 1 handler(s) for this service...
vi /var/opt/oracle/listener.ora
Note: The default location is $ORACLE_HOME/network/admin/listener.ora
Comment out the below lines:
# (SID_DESC =
# (GLOBAL_DBNAME= explsrv5.dreamland)
# (ORACLE_HOME= /oracle_home/home/dba/oracle/product/9.2.0.8-64bit)
# (SID_NAME = DB1)
# )
hostname.localdomain DB1 /var/opt/oracle > lsnrctl reload LISTENER
LSNRCTL for Solaris: Version 9.2.0.8.0 - Production on 01-MAY-2012 01:56:51
Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=PNPKEY))
The command completed successfully
lsnrctl status LISTENER
10. Comment out DB1 from tnsnames.ora entry.
cd /var/opt/oracle
vi tnsnames.ora
Note: The default location is $ORACLE_HOME/network/admin/tnsnames.ora
Comment out the following lines:
#DB1=
# (DESCRIPTION =
# (ADDRESS_LIST =
# (ADDRESS =
# (COMMUNITY = CENTRAL)
# (PROTOCOL = TCP)
# (HOST = hostname.localdomain)
# (PORT = 1526)
# )
# )
# (CONNECT_DATA =
# (SID = DB1)
# )
# )
11. Comment out DB1 from oratab.
cd /var/opt/oracle
vi oratab
#DB1:/oracle_home/home/dba/oracle/product/9.2.0.8-64bit:N
If dropping a database in a RAC environment,
SQL> alter system set cluster_database=false scope=spfile;
-- If the above don't work, you need to edit the text parameter file to include the parameter.
-- Shutdown the database in all nodes:
srvctl stop database -d [db_name]
-- SQL> startup mount restrict;
-- SQL> drop database;
-- DONE --