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

小麦苗DBA宝典

发布于 2022-02-22 16:13:23

4610

发布于 2022-02-22 16:13:23

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

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

题目

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.

答案

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窗口：

ALTER DATABASE CLOSE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
DROP DATABASE;

或：

STARTUP FORCE MOUNT RESTRICT;
DROP DATABASE;

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

实验如下：

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

[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的主机上删除数据库时，报错信息不一样，如下所示：

告警日志：

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）