OCP題庫解析-1Z0-053-QUESTION679

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/493318/viewspace-1075159/，如需轉載，請註明出處，否則將追究法律責任。

OCP題庫解析-1Z0-053-QUESTION679

相關文章