手动删除Oracle单实例数据库

最新推荐文章于 2024-06-18 16:39:18 发布

shayuwei

最新推荐文章于 2024-06-18 16:39:18 发布

阅读量3.7k

点赞数

分类专栏： Oracle 笔记文章标签： oracle

本文链接：https://blog.csdn.net/shayuwei/article/details/106350731

版权

笔记同时被 2 个专栏收录

164 篇文章 5 订阅

订阅专栏

Oracle

129 篇文章 7 订阅

订阅专栏

删除数据库主要有两种方法：
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

根据情况修改。

Oracle rac手动删库

shayuwei

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
手动删除Oracle单实例数据库

删除数据库主要有两种方法：1.使用DBCA工具在图形界面删库2.使用DROP DATABASE手动删除数据库手动删库1.查看当前信息这台服务器上有两个实例，分别是ora11g和orcl，现删除实例orcl[oracle@db oradata]$ lsnrctl statLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-5月 -2020 14:30:22Copyright (c) 1991, 2013, Oracle. Al
复制链接

扫一扫

专栏目录