OCP-1Z0-052-V8.02-62题

62. You are in the middle of a transaction updating a very important table. The machine on which a

database was running reboots because of power outage. This caused a database instance failure.

Which statement is true in this situation?

A.The online redo log files and archived redo log files are required to accomplish the recovery.会用到redo来前滚，undo来回滚

B.The uncommitted transaction will be committed at the next startup of the database instance. 没有提交的会回滚

C.The uncommitted transaction is rolled back automatically at the next opening of the database.

D.The DBA has to perform the recovery on the database to recover the uncommitted transaction. 由SMON执行实例恢复

Answer: C

答案解析：

1、首先查询scott.emp表

sys@TEST0924> select * from scott.dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

2.更新表，且不提交，然后模拟断电shutdown abort，再重启

sys@TEST0924> update scott.dept set dname='it' where deptno=10;

1 row updated.

sys@TEST0924> select * from scott.dept;

DEPTNO DNAME LOC

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

10 it NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

sys@TEST0924> shutdown abort;

ORACLE instance shut down.

sys@TEST0924> startup

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size 2230952 bytes

Variable Size 603981144 bytes

Database Buffers 1879048192 bytes

Redo Buffers 20078592 bytes

Database mounted.

Database opened.

3、 再次查询，值没有改变，回滚了。B错误。

sys@TEST0924> select * from scott.dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

alert_test0924.log内容：

Sat Sep 28 23:39:33 2013

Shutting down instance (abort)

License high water mark = 8

USER (ospid: 11026): terminating the instance

Instance terminated by USER, pid = 11026

Sat Sep 28 23:39:34 2013

Instance shutdown complete

Sat Sep 28 23:40:00 2013

Adjusting the default value of parameter parallel_max_servers

from 320 to 135 due to the value of parameter processes (150)

Starting ORACLE instance (normal)

****************** Large Pages Information *****************

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)

Large Pages unused system wide = 0 (0 KB) (alloc incr 16 MB)

Large Pages configured system wide = 0 (0 KB)

Large Page size = 2048 KB

RECOMMENDATION:

Total Shared Global Region size is 2402 MB. For optimal performance,

prior to the next instance restart increase the number

of unused Large Pages by atleast 1201 2048 KB Large Pages (2402 MB)

system wide to get 100% of the Shared

Global Region allocated with Large pages

***********************************************************

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

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: rtest.localdomain

Release: 2.6.18-308.el5

Version: #1 SMP Fri Jan 27 17:17:51 EST 2012

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/spfiletest0924.ora

System parameters with non-default values:

processes = 150

shared_pool_size = 496M

sga_target = 2400M

control_files = "/u01/app/oracle/oradata/test0924/control01.ctl"

control_files = "/u01/app/oracle/fast_recovery_area/test0924/control02.ctl"

db_block_size = 8192

db_keep_cache_size = 48M

compatible = "11.2.0.0.0"

db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"

db_recovery_file_dest_size= 4122M

undo_tablespace = "UNDOTBS1"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

dispatchers = "(PROTOCOL=TCP) (SERVICE=test0924XDB)"

audit_file_dest = "/u01/app/oracle/admin/test0924/adump"

audit_trail = "DB"

db_name = "test0924"

open_cursors = 300

pga_aggregate_target = 798M

diagnostic_dest = "/u01/app/oracle"

Sat Sep 28 23:40:01 2013

PMON started with pid=2, OS id=11126

Sat Sep 28 23:40:01 2013

PSP0 started with pid=3, OS id=11128

Sat Sep 28 23:40:02 2013

VKTM started with pid=4, OS id=11132 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Sat Sep 28 23:40:02 2013

GEN0 started with pid=5, OS id=11136

Sat Sep 28 23:40:02 2013

DIAG started with pid=6, OS id=11138

Sat Sep 28 23:40:02 2013

DBRM started with pid=7, OS id=11140

Sat Sep 28 23:40:02 2013

DIA0 started with pid=8, OS id=11142

Sat Sep 28 23:40:02 2013

MMAN started with pid=9, OS id=11144

Sat Sep 28 23:40:02 2013

DBW0 started with pid=10, OS id=11146

Sat Sep 28 23:40:02 2013

LGWR started with pid=11, OS id=11148

Sat Sep 28 23:40:02 2013

CKPT started with pid=12, OS id=11150

Sat Sep 28 23:40:02 2013

SMON started with pid=13, OS id=11152

Sat Sep 28 23:40:02 2013

RECO started with pid=14, OS id=11154

Sat Sep 28 23:40:02 2013

MMON started with pid=15, OS id=11156

Sat Sep 28 23:40:02 2013

MMNL started with pid=16, OS id=11158

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

Sat Sep 28 23:40:02 2013

ALTER DATABASE MOUNT

Successful mount of redo thread 1, with mount id 2721256018

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Sat Sep 28 23:40:07 2013

ALTER DATABASE OPEN

Beginning crash recovery of 1 threads

parallel recovery started with 7 processes

Started redo scan

Completed redo scan

read 38 KB redo, 28 data blocks need recovery

Started redo application at

Thread 1: logseq 27, block 60092

Recovery of Online Redo Log: Thread 1 Group 3 Seq 27 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/test0924/redo03.log

Completed redo application of 0.01MB

Completed crash recovery at

Thread 1: logseq 27, block 60168, scn 1487079

28 data blocks read, 28 data blocks written, 38 redo k-bytes read

LGWR: STARTING ARCH PROCESSES

Sat Sep 28 23:40:08 2013

ARC0 started with pid=27, OS id=11184

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Sat Sep 28 23:40:09 2013

ARC1 started with pid=28, OS id=11186

Sat Sep 28 23:40:09 2013

ARC2 started with pid=29, OS id=11188

ARC1: Archival started

Sat Sep 28 23:40:09 2013

ARC3 started with pid=30, OS id=11190

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Thread 1 advanced to log sequence 28 (thread open)

Thread 1 opened at log sequence 28

Current log# 1 seq# 28 mem# 0: /u01/app/oracle/oradata/test0924/redo01.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

SMON: enabling cache recovery

[11168] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:307151804 end:307151864 diff:60 (0 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Sat Sep 28 23:40:10 2013

QMNC started with pid=31, OS id=11192

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Archived Log entry 20 added for thread 1 sequence 27 ID 0xa22d4156 dest 1:

Sat Sep 28 23:40:12 2013

db_recovery_file_dest_size of 4122 MB is 30.36% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Completed: ALTER DATABASE OPEN

Sat Sep 28 23:40:12 2013

Starting background process CJQ0

Sat Sep 28 23:40:12 2013

CJQ0 started with pid=32, OS id=11206

Setting Resource Manager plan SCHEDULER[0x318E]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

Sat Sep 28 23:40:15 2013

Starting background process VKRM

Sat Sep 28 23:40:15 2013

VKRM started with pid=33, OS id=11208