|
|
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
|
|
楼主: guoyJoe
IP卡
狗仔卡
发表于 2014-4-11 13:40
显身卡
发表于 2014-4-11 15:01
楼主