Oracle rac手动删库

最新推荐文章于 2024-03-17 09:45:14 发布

shayuwei

最新推荐文章于 2024-03-17 09:45:14 发布

阅读量997

点赞数

CC 4.0 BY-SA版权

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

本文链接：https://blog.youkuaiyun.com/shayuwei/article/details/106497450

笔记同时被 2 个专栏收录

164 篇文章

订阅专栏

Oracle

129 篇文章

订阅专栏

本文详细介绍了Oracle RAC环境下数据库删除的步骤，包括停止监听、查看磁盘组空间、关闭数据库、启动到mount状态、修改参数、使用drop database命令、手动清除相关文件等关键操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

与单实例删库步骤基本一致。

1.停止监听

防止有新的连接产生，同时，在数据库配置了em的，也需要停止

$ lsnrctl stop listener_name
$ emctl stop dbconsole

2.查看ASM磁盘组空间

获得数据文件，日志文件及控制文件的相关信息，包含归档文件

SQL> select
name "名称",
state "状态",
round(total_mb/1024,2) "总空间GB",
round(free_mb/1024,2) "剩余空间GB",
round(total_mb/1024,2)-round(free_mb/1024,2) "使用空间GB",
round(free_mb/total_mb*100,2) "空闲比"
from v$asm_diskgroup;  
名称               状态            总空间GB    剩余空间GB 使用空间GB     空闲比
------------------------ ----------- ---------- ---------- ---------- ----------
FRA               CONNECTED      200         .01        199.99         0
OCR               MOUNTED        2           1.61       .39 80        .65
RACDB_DATA    CONNECTED      2099.99    239.49     1860.5         11.4

如果这台实例已经关掉数据库服务了，可以用asmcmd查看磁盘组空间

[grid@testrac1 ~]$ asmcmd
ASMCMD> ls -l
State    Type    Rebal  Name
MOUNTED  EXTERN  N      FRA/
MOUNTED  EXTERN  N      OCR/
MOUNTED  EXTERN  N      RACDB_DATA/
ASMCMD> ls -s
Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
   512   4096  1048576    204805   198132                0          198132              0             N  FRA/
   512   4096  1048576      2047     1651                0            1651              0             Y  OCR/
   512   4096  1048576   2150394   244562                0          244562              0             N  RACDB_DATA

3.查看实例相关文件信息

如果数据库开着

获取控制文件的路径

SQL> show parameter control

获取数据文件的位置

SQL> select file_name from dba_data_files;

获取日志文件的位置

SQL> select * from v$logfile;

获取归档日志的路径

SQL> archive log list;

如果实例已经关闭，可以通过grid用户:

获取文件的位置

[grid@testrac1 ~]$ sqlplus / as sysdba

# 查看文件，注意group_number表示位于不同的磁盘组
SQL> select name,file_number,group_number,file_incarnation from v$asm_alias;

NAME	FILE_NUMBER GROUP_NUMBER FILE_INCARNATION
--------------------------------- ----------- ------------ ----------------
.
.
.
# 共有274个文件
274 rows selected.

查看控制文件位置：

$ asmcmd

ASMCMD> cd racdb_data/racdb/controlfile
ASMCMD> ls
Current.256.1023463073
current.268.1023469245		# 当前实例的控制文件
current.444.1041513741
current.445.1041517695

4.关闭数据库

SQL>shutdown immediate(abort);

反正都要删库了，可以考虑用abort

5.启动到mount状态(使用exclusive restart)

SQL> startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area 5.3447E+10 bytes
Fixed Size            2265864 bytes
Variable Size         3.0870E+10 bytes
Database Buffers     2.2549E+10 bytes
Redo Buffers           26480640 bytes
Database mounted.

再次确认是否是要删除的实例

SQL> show parameter instance;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
active_instance_count             integer
cluster_database_instances         integer     1
instance_groups              string
instance_name                 string     racdb2
instance_number              integer     2
instance_type                 string     RDBMS
open_links_per_instance          integer     4
parallel_instance_group          string
parallel_server_instances         integer     1

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> exit
[oracle@testrac2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 5月 28 11:28:01 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected

8.手动清除相关文件

删库后控制文件已自动删除：

ASMCMD> cd controlfile
ASMCMD> ls
Current.256.1023463073
current.444.1041513741
current.445.1041517695

数据文件除了sys表空间、users表空间、undo表空间、临时表空间、redo、参数文件外，都被删除

ASMCMD> ls
EGRANTCLOB.275.1023478277
SYSAUX.260.1023463079
SYSTEM.259.1023463075
UNDOTBS1.261.1023463081
UNDOTBS2.263.1023463091
USERS.264.1023463091
egrantclob31.dbf   #遗留了一个数据文件
ASMCMD> rm -rf EGRANTCLOB.275.1023478277
ASMCMD> rm -rf egrantclob31.dbf
ASMCMD-8002: entry 'egrantclob31.dbf' does not exist in directory '+racdb_data/racdb/datafile/'
ASMCMD> ls
SYSAUX.260.1023463079
SYSTEM.259.1023463075
UNDOTBS1.261.1023463081
UNDOTBS2.263.1023463091
USERS.264.1023463091

空间释放：

ASMCMD> ls -s
Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
   512   4096  1048576    204805   203905                0          203905              0             N  FRA/
   512   4096  1048576      2047     1651                0            1651              0             Y  OCR/
   512   4096  1048576   2150394  2125326                0         2125326              0             N  RACDB_DATA/

查看文件

select name,file_number,group_number,file_incarnation from v$asm_alias;

NAME                                       FILE_NUMBER GROUP_NUMBER FILE_INCARNATION
---------------------------------------------------------------------- ----------- ------------ ----------------
RACDB                                    4294967295          2       4294967295
CONTROLFILE                                4294967295          2       4294967295
Current.256.1023381111                                   256          2       1023381111
Current.261.1023463073                                   261          2       1023463073
current.1393.1041517699                               1393          2       1041517699
ONLINELOG                                4294967295          2       4294967295
group_1.257.1023381111                                   257          2       1023381111
group_2.258.1023381111                                   258          2       1023381111
group_3.259.1023381881                                   259          2       1023381881
group_4.260.1023381881                                   260          2       1023381881
group_1.262.1023463073                                   262          2       1023463073
group_2.263.1023463075                                   263          2       1023463075
group_3.264.1023463843                                   264          2       1023463843
group_4.265.1023463843                                   265          2       1023463843
testrac-cluster                             4294967295          3       4294967295
ASMPARAMETERFILE                            4294967295          3       4294967295
REGISTRY.253.1023288043                                253          3       1023288043
OCRFILE                                 4294967295          3       4294967295
REGISTRY.255.1023288045                                255          3       1023288045
RACDB                                    4294967295          4       4294967295
CONTROLFILE                                4294967295          4       4294967295
Current.256.1023463073                                   256          4       1023463073
current.444.1041513741                                   444          4       1041513741
current.445.1041517695                                   445          4       1041517695
ONLINELOG                                4294967295          4       4294967295
group_1.257.1023463073                                   257          4       1023463073
group_2.258.1023463075                                   258          4       1023463075
group_3.265.1023463843                                   265          4       1023463843
group_4.266.1023463843                                   266          4       1023463843
DATAFILE                                4294967295          4       4294967295
SYSTEM.259.1023463075                                   259          4       1023463075
SYSAUX.260.1023463079                                   260          4       1023463079
UNDOTBS1.261.1023463081                                261          4       1023463081
UNDOTBS2.263.1023463091                                263          4       1023463091
USERS.264.1023463091                                   264          4       1023463091
TEMPFILE                                4294967295          4       4294967295
TEMP.262.1023463083                                   262          4       1023463083
spfileracdb.ora                                    267          4       1041591383
DB_UNKNOWN                                4294967295          4       4294967295
PARAMETERFILE                                4294967295          4       4294967295
SPFILE.267.1041591383                                   267          4       1041591383

41 rows selected.

9.跑路

删库删全套，删完跑路！