ITPub博客

首页 > 数据库 > Oracle > OCP题库解析-1Z0-053-QUESTION679

OCP题库解析-1Z0-053-QUESTION679

原创 Oracle 作者:macxue 时间:2014-01-24 21:49:56 0 删除 编辑

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

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

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

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

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) 2009 Microsoft Corporation。保留所有权利。

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

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

连接到:

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

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

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) 2009 Microsoft Corporation。保留所有权利。


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

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

连接到:

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

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

连接到:

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


相关参考

http://docs.oracle.com/cd/E11882_01/server.112/e40540/startup.htm#CNCPT89042

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/493318/viewspace-1075159/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-10

  • 博文量
    9
  • 访问量
    71824