删除数据库主要有两种方法:
1.使用DBCA工具在图形界面删库
2.使用DROP DATABASE手动删除数据库
手动删库
1.查看当前信息
这台服务器上有两个实例,分别是ora11g和orcl,现删除实例orcl
[oracle@db oradata]$ lsnrctl stat
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-5月 -2020 14:30:22
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-12月-2019 16:21:01
Uptime 156 days 22 hr. 9 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ora11g" has 1 instance(s).
Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "ora11gXDB" has 1 instance(s).
Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
2.停止监听
防止有新的连接产生,同时,在数据库配置了em的,也需要停止
$ lsnrctl stop listener_name
$ emctl stop dbconsole
3.查看实例相关文件信息
获得数据文件,日志文件及控制文件的相关信息,包含归档
- 需要删除的是实例ora11g,需要声明
[oracle@db ~]$ echo $ORACLE_SID
orcl
[oracle@db ~]$ export ORACLE_SID=ora11g
[oracle@db ~]$ echo $ORACLE_SID
ora11g
- 查看实例名字
SQL> show parameter instance;
# 或
SQL> select instance_name from v$instance;
INSTANCE_NAME
------------------------------------------------
ora11g
- 获取控制文件的路径
SQL> show parameter control
NAME TYPE VALUE
-------------------------------- ------------------- --------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/ora11g/control01.ctl, /u01/app/oracle/oradata/ora11g/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
- 获取数据文件的位置
SQL> select file_name from dba_data_files;
- 获取日志文件的位置
SQL> select * from v$logfile;
- 获取归档日志的路径
SQL> archive log list;
- 查看文件:
[oracle@db ~]$ cd /u01/app/oracle/oradata/ora11g
[oracle@db ora11g]$ ll
total 14638592
-rw-r----- 1 oracle oinstall 9748480 May 25 15:15 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 May 25 15:15 control02.ctl
-rw-r----- 1 oracle oinstall 3250593792 May 24 21:15 clob01.dbf
-rw-r----- 1 oracle oinstall 2936020992 May 24 21:15 clob03.dbf
-rw-r----- 1 oracle oinstall 734011392 May 24 21:15 datas01.dbf
-rw-r----- 1 oracle oinstall 629153792 May 24 21:15 datas03.dbf
-rw-r----- 1 oracle oinstall 629153792 May 24 21:15 datas04.dbf
-rw-r----- 1 oracle oinstall 209723392 May 24 21:15 index03.dbf
-rw-r----- 1 oracle oinstall 524296192 May 24 21:15 indexs01.dbf
-rw-r----- 1 oracle oinstall 314573312 May 25 15:15 redo01.log
-rw-r----- 1 oracle oinstall 314573312 May 23 22:03 redo02.log
-rw-r----- 1 oracle oinstall 314573312 May 24 21:10 redo03.log
-rw-r----- 1 oracle oinstall 314573312 May 16 22:03 redo04.log
-rw-r----- 1 oracle oinstall 314573312 May 17 18:04 redo05.log
-rw-r----- 1 oracle oinstall 314573312 May 18 22:00 redo06.log
-rw-r----- 1 oracle oinstall 314573312 May 19 22:00 redo07.log
-rw-r----- 1 oracle oinstall 314573312 May 20 22:00 redo08.log
-rw-r----- 1 oracle oinstall 314573312 May 21 22:54 redo09.log
-rw-r----- 1 oracle oinstall 314573312 May 23 00:00 redo10.log
-rw-r----- 1 oracle oinstall 943726592 May 25 15:14 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 May 25 15:14 system01.dbf
-rw-r----- 1 oracle oinstall 209723392 May 25 00:40 system02.dbf
-rw-r----- 1 oracle oinstall 61874176 May 25 13:28 temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 24 21:15 test02.dbf
-rw-r----- 1 oracle oinstall 104865792 May 24 21:15 test.dbf
-rw-r----- 1 oracle oinstall 728768512 May 25 15:14 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 May 24 21:15 users01.dbf
- 查看参数文件等:
[oracle@db dbs]$ ll
total 44
-rw-rw---- 1 oracle oinstall 1544 Dec 20 16:20 hc_ora11g.dat
-rw-rw---- 1 oracle oinstall 1544 May 25 14:28 hc_orcl.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 903 Nov 6 2019 initora11g.ora
-rw-r--r-- 1 oracle oinstall 831 May 25 14:42 initorcl.ora
-rw-r----- 1 oracle oinstall 24 Nov 1 2019 lkORA11G
-rw-r----- 1 oracle oinstall 24 May 25 13:58 lkORCL
-rw-r----- 1 oracle oinstall 2048 Nov 5 2019 orapwora11g
-rw-r----- 1 oracle oinstall 1536 May 25 13:58 orapworcl
-rw-r----- 1 oracle oinstall 2560 Apr 13 00:03 spfileora11g.ora
-rw-r----- 1 oracle oinstall 2560 May 25 14:29 spfileorcl.ora
4.关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
反正都要删除了,可以考虑用abort
5.启动到mount状态
(使用exclusive restart) mount数据库到独占模式,保证只有sysdba可登录
SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 3006406656 bytes
Fixed Size 2257032 bytes
Variable Size 2868907896 bytes
Database Buffers 117440512 bytes
Redo Buffers 17801216 bytes
Database mounted.
- 再次查看是否是要删除的实例,这一步非常重要。
SQL> select instance_name from v$instance;
INSTANCE_NAME
------------------------------------------------
ora11g
6.修改参数为允许受限的会话模式
SQL> alter system enable restricted session;
System altered.
7.使用drop database命令来清除数据库
SQL> drop database;
Database dropped.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
- 现在的状态:
SQL> select * from v$logfile;
SP2-0640: Not connected
SQL> exit
[oracle@db ~]$ echo $ORACLE_SID
ora11g
[oracle@db ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on 星期一 5月 25 15:18:37 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
已经是空实例了。
- 在drop的过程中查看数据文件等是否也一并删除:
drop未结束的时候查看了一下,在删库的过程中,数据文件等也一并被删掉了,可以确定的是,先被删掉的是数据文件及日志文件,控制文件最后才被删掉。 - 查看参数文件等
[oracle@db dbs]$ ll
total 40
-rw-rw---- 1 oracle oinstall 1544 May 25 15:17 hc_ora11g.dat
-rw-rw---- 1 oracle oinstall 1544 May 25 14:28 hc_orcl.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 903 Nov 6 2019 initora11g.ora
-rw-r--r-- 1 oracle oinstall 831 May 25 14:42 initorcl.ora
-rw-r----- 1 oracle oinstall 24 Nov 1 2019 lkORA11G
-rw-r----- 1 oracle oinstall 24 May 25 13:58 lkORCL
-rw-r----- 1 oracle oinstall 2048 Nov 5 2019 orapwora11g
-rw-r----- 1 oracle oinstall 1536 May 25 13:58 orapworcl
-rw-r----- 1 oracle oinstall 2560 May 25 14:29 spfileorcl.ora
只有spfile被删除掉了。pfile和口令文件依旧存在。
8.手动清除相关文件
注:在博客上看到有些网友在drop掉数据库后,物理文件依然存在。需要手动删除:
根据查询的结果,删除相应的文件。
windows可以打开窗口,进入目录删除。linux可以使用rm删除
9.清除监听相关文件
11G以后,很多是动态注册的,所以不用考虑这一步。如果配置了静态监听的,需要删除listener.ora文件里的配置
- 监听也自动删除了:
[oracle@db ~]$ lsnrctl stat
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-5月 -2020 15:18:50
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-12月-2019 16:21:01
Uptime 156 days 22 hr. 57 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
10.修改oratab文件以及.bash_profile
根据情况修改。