OCP题库解析-1Z0-053-QUESTION679-CSDN博客

QUESTION NO: 679

Which mode of database shutdown requires an instance recovery at the time of

the next database startup?

A.ABORT

B.NORMAL

C.IMMEDIATE

D.TRANSACTIONAL

【题目示意】

此题考查有关于shutdown mode的相关知识。

【解析】

shutdown mode是使用SQL语句对数据库进行shutdown操作的动作类型。

有如下表4种类型：

Database Behavior	ABORT	IMMEDIATE	TRANSACTIONAL	NORMAL
Permits new user connections	No	No	No	No
Waits until current sessions end	No	No	No	Yes
Waits until current transactions end	No	No	Yes	Yes
Performs a checkpoint and closes open files	No	Yes	Yes	Yes

? SHUTDOWN ABORT
这种模式是其他shutdown不能成功操作时的应急操作。这种模式执行shutdown是最快速的。但是shutdown abort不检查数据一致性，所以实例重新打开前需要recovery。

? SHUTDOWN IMMEDIATE
这种模式是速度仅次于 SHUTDOWN ABORT. Oracle Database 终止所有SQL会话及用户连接. 未提交事物会执行roll back.

? SHUTDOWN TRANSACTIONAL
这种模式会阻止新的会话提交, 但是会等待现有会话完成之后，执行shutdown。这种模式会对现有会话产生等待时间。

? SHUTDOWN NORMAL
这是默认的shutdown模式. 数据库在shutdown之前会等待所有的连接客户结束。

【实验】

通过启动数据库和实例，并创建数据库表，进行表操作。分别使用上面4种shutdown mode。

观察4种模式产生的不同结果。

1. 数据库环境：

1.1 启动监听程序;

[oracle@ENMOEDU ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-JAN-2014 18:03:25

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/ENMOEDU/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ENMOEDU)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ENMOEDU)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 23-JAN-2014 18:03:27

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/ENMOEDU/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ENMOEDU)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

The listener supports no services

The command completed successfully

1.2 启动数据库实例;

[oracle@ENMOEDU ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 23 18:04:11 2014

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1345380 bytes

Variable Size 348129436 bytes

Database Buffers 67108864 bytes

Redo Buffers 6086656 bytes

Database mounted.

Database opened.

SQL>

1.3 Client端登录;

Microsoft Windows [版本 6.1.7601]

C:\Users\Enmoedu>sqlplus sys/oracle@ENMOEDU as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期四 1月 23 18:16:40 2014

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. shutdown normal

2.1 服务器终端shutdown normal;

[oracle@ENMOEDU ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 23 18:04:11 2014

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1345380 bytes

Variable Size 348129436 bytes

Database Buffers 67108864 bytes

Redo Buffers 6086656 bytes

Database mounted.

Database opened.

SQL> shutdown normal

此时Client仍有连接接入服务器，shutdown normal处于等待中。

2.2 Client端结束sqlplus连接;

Microsoft Windows [版本 6.1.7601]

C:\Users\Enmoedu>sqlplus sys/oracle@ENMOEDU as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期四 1月 23 18:16:40 2014

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options 断

开

2.3 shutdown normal执行完成。

SQL> shutdown normal

Database closed.

Database dismounted.

ORACLE instance shut down.

从这可以了解shutdown normal在所有会话连接结束后执行。

3. shutdown transactional

3.1 创建数据表;

C:\Users\Enmoedu>sqlplus sys/oracle@ENMOEDU as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期四 1月 23 18:18:12 2014

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table tb (a number);

表已创建。

3.2 建立事务，插入表数据;

SQL> insert into tb values (1);

已创建 1 行。

3.3 shutdown transactional;

SQL> shutdown transactional

3.4 对事务进行提交;

SQL> commit;

提交完成。

3.5 shutdown transactional;

SQL> shutdown transactional

Database closed.

Database dismounted.

ORACLE instance shut down.

从这可以了解shutdown transactional在所有事务提交结束后执行。

4. shutdown immediate

4.1 再次执行3.2&3.4；

4.2 shutdown immediate;

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

4.3 startup mount;

SQL> startup mount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1345380 bytes

Variable Size 348129436 bytes

Database Buffers 67108864 bytes

Redo Buffers 6086656 bytes

Database mounted.

4.4 查看v$datafile中LAST_CHANGE#和CHECKPOINT_CHANGE#的值是否一致;

SQL> select FILE#,LAST_CHANGE#,CHECKPOINT_CHANGE#,NAME from v$datafile;

FILE# LAST_CHANGE# CHECKPOINT_CHANGE# NAME

----- ------------ ---------- --------------------------------------------------

1 1286191 1286191 /u01/app/oracle/oradata/ENMOEDU/system01.dbf

2 1286191 1286191 /u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf

3 1286191 1286191 /u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf

4 1286191 1286191 /u01/app/oracle/oradata/ENMOEDU/users01.dbf

5 1286191 1286191 /u01/app/oracle/oradata/ENMOEDU/example01.dbf

从这可以看到LAST_CHANGE#和CHECKPOINT_CHANGE#的值是一致，是因为shutdown

immediate进行数据一致性检查的原因。

5. shutdown abort

5.1 再次执行3.2&3.4;

5.2 shutdown abort;

SQL> shutdown abort

ORACLE instance shut down.

5.3 startup mount;

SQL> startup mount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1345380 bytes

Variable Size 348129436 bytes

Database Buffers 67108864 bytes

Redo Buffers 6086656 bytes

Database mounted.

5.4 查看v$datafile中LAST_CHANGE#和CHECKPOINT_CHANGE#的值是否一致;

SQL> select FILE#,LAST_CHANGE#,CHECKPOINT_CHANGE#,NAME from v$datafile;

FILE# LAST_CHANGE# CHECKPOINT_CHANGE# NAME

----- ------------ ---------- --------------------------------------------------

1 1286194 /u01/app/oracle/oradata/ENMOEDU/system01.dbf

2 1286194 /u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf

3 1286194 /u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf

4 1286194 /u01/app/oracle/oradata/ENMOEDU/users01.dbf

5 1286194 /u01/app/oracle/oradata/ENMOEDU/example01.dbf

从这可以看到LAST_CHANGE#和CHECKPOINT_CHANGE#的值不一致，即是因为shutdown

abort不进行数据一致性检查的原因。所有此方式在instance重新启动时需要recovery。

【小结】

1. shutdown normal,此为正常模式，需要等待用户关闭连接；

2. shutdown transactional,此模式，需要等待事务提交完成；

3. shutdown immediate,此模式，进行数据一致性检查；

4. shutdown abort,不检查数据一致性，实例重新启动前需要instance recovery，因此选择A。

【答案】 A