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