DROP DATABASE

最新推荐文章于 2025-10-31 15:08:50 发布

原创最新推荐文章于 2025-10-31 15:08:50 发布 · 7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#ORA-01586 #drop database #alter system enable restricted #EXCLUSIVE模式 #startup mount exclusive restri

Oracle 专栏收录该内容

16 篇文章

订阅专栏

本文详细解析了在Oracle RAC环境中删除数据库时遇到的问题及解决步骤。从初始尝试到最终成功，文章展示了如何正确配置参数、调整数据库模式，以及执行必要操作以确保数据库的顺利移除。

在做迁移时，由于某种原因导致回退，现在准备将备库的数据库删除，然后重新部署dg，但在删除备库的时候遇到一些小问题，在节点一执行

[oracle@sde1 ~]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 15 10:46:44 2018
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options

    SQL> 
    SQL> 
    SQL> drop database;
    drop database
    *
    ERROR at line 1:
    ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

提示执行该命令数据库必须处于mount状态(EXCLUSIVE模式mount)，关库以RESTRICTED模式开启

SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> 
    SQL>  startup mount exclusive restrict;
    ORACLE instance started.

    Total System Global Area 4.1557E+10 bytes
    Fixed Size                  2237328 bytes
    Variable Size            1.3959E+10 bytes
    Database Buffers         2.7515E+10 bytes
    Redo Buffers               81035264 bytes
    Database mounted.
    SQL> show parameter instance;

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    active_instance_count                integer
    cluster_database_instances           integer     2
    instance_groups                      string
    instance_name                        string      sde1
    instance_number                      integer     1
    instance_type                        string      RDBMS
    open_links_per_instance              integer     50
    parallel_instance_group              string
    parallel_server_instances            integer     2
    SQL> 
    SQL> alter system enable restricted session;
    System altered.
    SQL> 
    SQL> drop database;
    drop database
    *
    ERROR at line 1:
    ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

还是报错，明明是以exclusive模式mount的数据库实例，怎么还会报这个错误呢？看一下alert日志看会不会有发现：

ALTER DATABASE   MOUNT
    Successful mount of redo thread 2, with mount id 3008285745
    Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
    Lost write protection disabled
    Completed: ALTER DATABASE   MOUNT

通过alert日志我们可以清楚的看到，虽然我们在mount的时候指定了exclusive,但是因为CLUSTER_DATABASE=TRUE，所以数据库还是会以Shared模式被mount

mos中有一篇note提到如果是RAC数据库使用DROP DATABASE 要将设置CLUSTER_DATABASE=FALSE(alter system set cluster_database=FALSE scope=spfile;)，然后mount数据库，再删除。可以参考What Is The Best Way To Remove A Database If Using Oracle 10.x And Higher (Doc ID 362047.1)

SQL> show parameter luster
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    cluster_database                     boolean     TRUE
    cluster_database_instances           integer     2
    cluster_interconnects                string

    SQL> alter system set cluster_database=false scope=spfile;
    System altered.

    SQL> shutdown abort
    ORACLE instance shut down. 
    SQL> 
    SQL> startup mount exclusive restrict;
    ORACLE instance started.

    Total System Global Area 4.1557E+10 bytes
    Fixed Size                  2237328 bytes
    Variable Size            1.3959E+10 bytes
    Database Buffers         2.7515E+10 bytes
    Redo Buffers               81035264 bytes
    Database mounted.
    SQL> 
    SQL> alter system enable restricted session;
    System altered.
    SQL> 
    SQL> drop database;
    Database dropped.

    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
    SQL>