ORA-01586: 对于此操作, 数据库必须以 EXCLUSIVE 模式装载且不打开

最新推荐文章于 2024-05-30 22:35:24 发布

原创最新推荐文章于 2024-05-30 22:35:24 发布 · 2k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

笔记同时被 2 个专栏收录

164 篇文章

订阅专栏

Oracle

129 篇文章

订阅专栏

本文详细介绍了在Oracle RAC环境下，如何正确地关闭和删除数据库实例的步骤。面对在exclusive模式下删除数据库时遇到的ORA-01586错误，以及在尝试设置SPFILE参数时出现的ORA-32000错误，文章提供了具体的解决方案，包括调整集群参数、处理资源争抢问题等关键操作。

问题描述

删除rac实例的时候，关库后，在一个节点mount exclusive restrict后drop数据库时报错：

SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: 对于此操作, 数据库必须以 EXCLUSIVE 模式装载且不打开

以为是需要两个节点都需要mount exclusive restrict。所以讲两个节点都mount到了exclusive模式，但是drop db依旧报这个错。

问题解决

1.先停止所有节点实例
2.在其中一节点操作,先关闭rac模式

SQL> startup nomount;
SQL> alter system set CLUSTER_DATABASE=FALSE scope=spfile;
SQL> shutdown immediate;

3.启动mount restric模式

SQL> startup mount restrict;

4.删除数据库

drop database;

节点1：

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
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
[oracle@testrac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 5月 28 10:42:52 2020

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 6.4137E+10 bytes
Fixed Size            2269072 bytes
Variable Size         3.6776E+10 bytes
Database Buffers     2.7246E+10 bytes
Redo Buffers          112762880 bytes
SQL> alter system set CLUSTER_DATABASE=FALSE scope=spfile;
alter system set CLUSTER_DATABASE=FALSE scope=spfile
*
ERROR at line 1:
ORA-32000: ????? SPFILE, ?? SPFILE ????


SQL> exit
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
[oracle@testrac1 dbs]$ export NLS_LANG=american_america.zhs16gbk
[oracle@testrac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 28 10:45:18 2020

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


Connected to:
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> alter system set CLUSTER_DATABASE=FALSE scope=spfile;
alter system set CLUSTER_DATABASE=FALSE scope=spfile
*
ERROR at line 1:
ORA-32000: write to SPFILE requested but SPFILE is not modifiable

报了ORA-32000: write to SPFILE requested but SPFILE is not modifiable肯定是参数文件有问题

因为当时要恢复一套rac在这套rac上，所以节点1的pfile已经不是需要删除的实例的pfile了，所以在节点2手动修改pfile：

#*.cluster_database=TRUE
*.cluster_database=FALSE

修改后通过pfile创建spfile

[oracle@testrac2 dbs]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 5月 28 10:55:51 2020

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

Connected to an idle instance.

SQL> create spfile ='+racdb_data/racdb/spfileracdb.ora' from pfile;

File created.

直接在节点2操作：

SQL> startup mount restrict;
ORA-01102: cannot mount database in EXCLUSIVE mode

看到一篇博客也报这个错但是是通过删除 lk{$ORACLE_SID}解决（未核验）：ORA-01102: cannot mount database in EXCLUSIVE mode解决
这个问题的原因是资源争抢。想到节点1并没有关闭，因此肯定无法将库以restrict模式打开。

节点1释放：

[oracle@testrac1 dbs]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 28 11:04:33 2020

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


Connected to:
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> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.

节点2重新开库：

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

删库

SQL> alter system enable restricted session;

System altered.

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