前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >专栏 >【OCP最新题库解析(052)--题16】Your database instance is in NOMOUNT state

【OCP最新题库解析(052)--题16】Your database instance is in NOMOUNT state

作者头像
AiDBA宝典
发布于 2022-02-22 08:13:23
发布于 2022-02-22 08:13:23
52800
代码可运行
举报
文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏
运行总次数：0
代码可运行

该系列专题为2018年4月OCP-052考题变革后的最新题库。题库为小麦苗解答，若解答有不对之处，可留言，也可联系小麦苗进行修改。

Q

题目

Your database instance is in NOMOUNT state.You select the delete option in the Database Configuration Assistant(DBCA) for your database.

What is the outcome?

A. DBCA is able to delete the services but not the data files.

B. DBCA prompts you to open the database, and then proceeds with the deletion.

C. DBCA is able to delete data files but not the services.

D. DBCA shuts down the instance and deletes the database.

A

答案

Answer：A

对于B选项，DBCA并不会提示你打开数据库，只会提示数据库没有挂载（ORA-01507: database not mounted）。

对于C选项，说反了。C和A是矛盾选项。这里的services可以理解为/etc/oratab中的有个数据库的记录。如：

[oracle@OCPLHR ~]$ cat /etc/oratab | tail -n 1

OCPLHR2:/u01/app/oracle/product/11.2.0/dbhome_1:N

对于D选项，在NOMOUNT和MOUNT状态下，DBCA图形界面会关闭数据库实例，删除/etc/oratab中的记录和参数文件（spfile和pfile），但是不会删除数据库文件。但是，DBCA静默方式会直接报错，而不做任何操作。

以上解析针对没有安装grid的环境。

You can also use DBCA to delete a database. When DBCA deletes a database, it shuts down the database instance and then deletes all database files. On the Windows platform, it also deletes associated Windows services.

有如下几种方式可以用来删除Oracle数据库：

（1）直接在OS级别调用dbca命令以静默的方式删除数据库。使用DBCA进行删除的数据库必须处于OPEN状态，否则不能删除。如下所示，其中，mydb为数据库名：

dbca -silent -deleteDatabase -sourceDB mydb

当然，使用dbca也可以进行图形界面删除，被删除的数据库也必须处于OPEN状态，否则不能删除。其实，从告警日志中可以看到，在OPEN状态下，DBCA删除数据库的过程是，首先将数据库关闭，然后启动数据库到MOUNT状态，接着执行“ALTER SYSTEM ENABLE RESTRICTED SESSION;”让数据库处于受限模式，最后执行“DROP DATABASE;”删除数据库。在删除数据库完成后，会清理文件/etc/oratab中有关被删除数据库的信息，也会删除与该数据库有关的所有的SPFILE和PFILE文件。

需要注意的是，在安装有grid的主机上，如果当前数据库处于非OPEN状态，那么DBCA图形界面和静默方式不会删除和修改任何文件（/etc/oratab和参数文件）；如果主机上没有安装grid，当前数据库处于非OPEN状态，那么DBCA图形界面会删除与该数据库有关的所有的SPFILE和PFILE文件，但是不会删除数据文件，而DBCA静默方式依然会报错，且不会删除和修改任何文件。

（2）SQL窗口：

代码语言：javascript
代码运行次数：0
运行
AI代码解释
复制
ALTER DATABASE CLOSE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
DROP DATABASE;

或：

代码语言：javascript
代码运行次数：0
运行
AI代码解释
复制
STARTUP FORCE MOUNT RESTRICT;
DROP DATABASE;

注意：强烈推荐第1种方式，对于第2种方式，若是在RAC环境中，数据库库需要设置参数CLUSTER_DATABASE为FALSE后才可以执行DROP DATABASE，设置的命令为：ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SID='*' SCOPE=SPFILE;。

实验如下：

首先在安装了grid的环境下测试：

代码语言：javascript
代码运行次数：0
运行
AI代码解释
复制
[oracle@OCPLHR ~]$ ORACLE_SID=OCPXXT1
[oracle@OCPLHR ~]$ sas
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 20 15:14:10 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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@OCPXXT1> startup force nomount
ORACLE instance started.
Total System Global Area  521936896 bytes
Fixed Size                  2229944 bytes
Variable Size             352323912 bytes
Database Buffers          159383552 bytes
Redo Buffers                7999488 bytes
SYS@OCPXXT1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OCPLHR ~]$ dbca -silent -deleteDatabase -sourceDB OCPXXT1
ORA-01507: database not mounted
[oracle@OCPLHR ~]$ 
启动到mount状态：
SYS@OCPXXT1> alter database mount;
Database altered.
SYS@OCPXXT1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OCPLHR ~]$ dbca -silent -deleteDatabase -sourceDB OCPXXT1
ORA-01219: database not open: queries allowed on fixed tables/views only
启动到OPEN状态：
[oracle@OCPLHR ~]$ sas
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 20 15:29:55 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, OLAP, Data Mining and Real Application Testing options
SYS@OCPXXT1> alter database open;
Database altered.
SYS@OCPXXT1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OCPLHR ~]$ dbca -silent -deleteDatabase -sourceDB OCPXXT1
Connecting to database
4% complete
9% complete
14% complete
19% complete
23% complete
28% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instance and datafiles
76% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/OCPXXT1.log" for further details.
[oracle@OCPLHR ~]$ 
告警日志：
Fri Apr 20 15:31:06 2018
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Fri Apr 20 15:31:07 2018
Stopping background process CJQ0
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
License high water mark = 4
Stopping Job queue slave processes, flags = 7
Job queue slave processes stopped
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Fri Apr 20 15:31:12 2018
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Apr 20 15:31:12 2018
Shutting down archive processes
Archiving is disabled
Fri Apr 20 15:31:12 2018
ARCH shutting down
ARC3: Archival stopped
Fri Apr 20 15:31:12 2018
ARCH shutting down
ARC2: Archival stopped
Fri Apr 20 15:31:12 2018
ARCH shutting down
ARC1: Archival stopped
Fri Apr 20 15:31:12 2018
ARCH shutting down
ARC0: Archival stopped
Thread 1 closed at log sequence 49
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Fri Apr 20 15:31:14 2018
Stopping background process VKTM
Fri Apr 20 15:31:14 2018
NOTE: Shutting down MARK background process
Fri Apr 20 15:31:18 2018
Instance shutdown complete
Fri Apr 20 15:31:18 2018
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
WARNING: db_recovery_file_dest is same as db_create_file_dest
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      OCPLHR
Release:        2.6.18-194.el5
Version:        #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine:        x86_64
VM name:        VMWare Version: 6
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileOCPXXT1.ora
System parameters with non-default values:
  processes                = 150
  memory_target            = 500M
  control_files            = "/u01/app/oracle/oradata/OCPXXT1/control01.ctl"
  control_files            = "/u01/app/oracle/oradata/OCPXXT1/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_create_file_dest      = "+DATA"
  db_recovery_file_dest    = "+DATA"
  db_recovery_file_dest_size= 4122M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=OCPASM1XDB)"
  local_listener           = "LISTENER_OCPASM1"
  job_queue_processes      = 1000
  audit_file_dest          = "/u01/app/oracle/admin/OCPASM1/adump"
  audit_trail              = "DB"
  db_name                  = "OCPXXT1"
  open_cursors             = 300
  diagnostic_dest          = "/u01/app/oracle"
Fri Apr 20 15:31:19 2018
PMON started with pid=2, OS id=32329 
Fri Apr 20 15:31:19 2018
PSP0 started with pid=3, OS id=32331 
Fri Apr 20 15:31:20 2018
VKTM started with pid=4, OS id=32333 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Fri Apr 20 15:31:20 2018
GEN0 started with pid=5, OS id=32337 
Fri Apr 20 15:31:20 2018
DIAG started with pid=6, OS id=32339 
Fri Apr 20 15:31:20 2018
DBRM started with pid=7, OS id=32341 
Fri Apr 20 15:31:20 2018
DIA0 started with pid=8, OS id=32343 
Fri Apr 20 15:31:20 2018
MMAN started with pid=9, OS id=32345 
Fri Apr 20 15:31:20 2018
DBW0 started with pid=10, OS id=32347 
Fri Apr 20 15:31:20 2018
LGWR started with pid=11, OS id=32349 
Fri Apr 20 15:31:20 2018
CKPT started with pid=12, OS id=32351 
Fri Apr 20 15:31:20 2018
SMON started with pid=13, OS id=32353 
Fri Apr 20 15:31:20 2018
RECO started with pid=14, OS id=32355 
Fri Apr 20 15:31:20 2018
MMON started with pid=15, OS id=32357 
Fri Apr 20 15:31:20 2018
MMNL started with pid=16, OS id=32359 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Fri Apr 20 15:31:20 2018
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 4088229128
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Apr 20 15:31:25 2018
Stopping background process MMNL
Stopping background process MMON
Starting background process MMON
Fri Apr 20 15:31:27 2018
MMON started with pid=15, OS id=32403 
Starting background process MMNL
Fri Apr 20 15:31:27 2018
MMNL started with pid=16, OS id=32405 
ALTER SYSTEM enable restricted session;
DROP DATABASE
Deleted file /u01/app/oracle/oradata/OCPXXT1/system01.dbf
Deleted file /u01/app/oracle/oradata/OCPXXT1/sysaux01.dbf
Deleted file /u01/app/oracle/oradata/OCPXXT1/undotbs101.dbf
Deleted file /u01/app/oracle/oradata/OCPXXT1/users01.dbf
Deleted file /u01/app/oracle/oradata/OCPXXT1/example01.dbf
Deleted file /u01/app/oracle/oradata/OCPXXT1/testasm01.dbf
Deleted file /u01/app/oracle/oradata/OCPXXT1/redo01_1.log
Deleted file /u01/app/oracle/oradata/OCPXXT1/redo01_2.log
Deleted file /u01/app/oracle/oradata/OCPXXT1/redo02_1.log
Deleted file /u01/app/oracle/oradata/OCPXXT1/redo02_2.log
Deleted file /u01/app/oracle/oradata/OCPXXT1/redo03_1.log
Deleted file /u01/app/oracle/oradata/OCPXXT1/redo03_2.log
Deleted file /u01/app/oracle/oradata/OCPXXT1/temp01.dbf
Deleted file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_OCPXXT1.f
Shutting down archive processes
Archiving is disabled

所以，经过测试，若安装了grid，则使用DBCA进行删除的数据库必须处于OPEN状态，否则不能删除。另外，需要注意的是，在没有安装grid的主机上删除数据库时，报错信息不一样，如下所示：

告警日志：

代码语言：javascript
代码运行次数：0
运行
AI代码解释
复制
Fri Apr 20 16:52:54 2018
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 2
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Fri Apr 20 16:52:59 2018
Stopping background process VKTM
Fri Apr 20 16:53:01 2018
Instance shutdown complete

查看数据文件，依然存在。只是删除了spfile和pfile，且清除了/etc/oratab中的信息。OPEN状态下删除数据库和安装了grid的主机是一样的。

OCP最新题库解析历史连接（052）

http://mp.weixin.qq.com/s/bUgn4-uciSndji_pUbLZfA

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2018-05-09，如有侵权请联系 cloudcommunity@tencent.com 删除
数据分析
数据库
sql
面向对象编程

本文分享自 DB宝 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

数据分析
数据库
sql
面向对象编程
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2025 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论
目录
本文部分代码块支持一键运行，欢迎体验
本文部分代码块支持一键运行，欢迎体验