ocp 1Z0-042 121-178题解析

121. You want to create a new optimized database for your transactional production environment to be used by a financial application. While creating the database, you want the Oracle software to take care of all basic settings to optimize the database performance. Which method would you use to achieve this objective?
A) Use the CREATE DATABASE.. command to create the database with Oracle-managed files.
B) Use the Database Configuration Assistant (DBCA) to create the database with Oracle-managed files.
C) Use Enterprise Manager to create a new database with the Online Transaction Processing (OLTP) option.
D) Use Database Configuration Assistant (DBCA) to create the database with Transaction Processing template.
E) Use the CREATE DATABASE.. command to create the database with Automatic Storage Management (ASM) file system.
答案:D
Editor’s note:the key of this question is transactional production environment（交易生产环境）It seems to me that OLTP is needed. So ,you must create the OLTP db.Answer A/B/E does not meet the requirement of the question.
Answer C is wrong.OEM cann’t create db.
122. You received complaints about the degradation of SQL query performance. You identified top SQL queries that consume time. What would be your next step to find out recommendations about statistics collection and restructuring of the SQL statement to improve query performance?
A) run Segment Advisor
B) run SQL Tuning Advisor on top SQL statements
C) run the Automatic Workload Repository (AWR) report
D) run the Automatic Database Diagnostic Monitor (ADDM) on top SQL statements
答案：B
当你发现sql执行慢等sql问题时，你使用top sql发现耗费时间的sql语句。这是你需要使用SQL Tuning Advisor推荐数据收集和重建sql语句。
可以使用 SQL 优化指导分析 SQL 语句，并获得性能建议案。通常，会将此指导作为ADDM 性能判断工具来运行。
Editor’s note: ADDM is like awr,it is using for monitor sql for system.not for top sql.
awr:auto workload repository ,体系的核心,statspack 的进一步改进(不收集操作系统信息，没有db time等),awr属于sys用户,存在sysaux表空间上,每1小时收集一次,在磁盘上保留7天select * from dba_hist_wr_control
addm:auto database diagonstic(诊断) monitor 提供建议
123. View the Exhibit.
Which statement regarding the dept and emp tables is true?
A) When you delete a row from the emp table, you would receive a constraint violation error.
B) When you delete a row from the dept table, you would receive a constraint violation error.
C) When you delete a row from the emp table, automatically the corresponding rows are deleted from the dept table.
D) When you delete a row from the dept table, automatically the corresponding rows are deleted from the emp table.
E) When you delete a row from the dept table, automatically the corresponding rows are updated with null values in the emp table.
F) When you delete a row from the emp table, automatically the corresponding rows are updated with null values in the dept table.
答案：D
124. User Scott has updated the salary of one of the employees in the EMPLOYEES table and has not committed the transaction. What are the two types of locks that this scenario would lead to? (Choose two.)
A) null lock on the row being updated
B) null lock on the table containing the row
C) ROW SHARE lock for the row being updated
D) ROW EXCLUSIVE lock for the row being updated
E) shared row-exclusive lock for the row being updated
F) a shareable table lock for the table containing the row
G) exclusive table-level lock for the table containing the row
答案：DF
D意思是被更新记录是Exclusive lock（排它锁），F是shared table lock。
125. View the Exhibit and examine the output.
Which statement describes the conclusion?
A) The users should use bind variables instead of literals in the query.
B) The dictionary cache is consuming more space than the library cache.
C) The shared pool size should be increased to accommodate the SQL statements.
D) Preparing indexes on the tables used in the SQL statements would improve the library cache performance.
答案：A
要用绑定变量
126. Your database is configured in NOARCHIVELOG mode. All the control files have been lost due to a hard disk failure but the data files are not lost. You have the closed whole database backup available to you. Which two statements are true in this scenario? (Choose two.)
A) The instance aborts.
B) The database cannot be recovered.
C) The database can be recovered by restoring the control files from the backup.
D) The database remains opened and you have to shut it down with the ABORT option.
E) The database can be restored till the point of the last closed whole database backup.
答案：AE
运行于非归档模式。所有控制文件丢失。数据库关闭。只有最近的一次数据库全库备份。那么数据库只能恢复到最后一次备份，尽管数据文件并未丢失
E的选项意思是库现在crash了，控制文件又丢失了（不能重建）。日志又因为noarchivelog，给覆盖了！依靠备份可以恢复到哪个时间点。
它忽略了可以重建控制文件这种手段，也没有交代从备份完成库crash之间日志是否被覆盖。
127. While running the Oracle Universal Installer on a Unix platform to install Oracle Database 10g software, you are prompted to run orainstRoot.sh script. What does this script accomplish?
A) It creates the pointer file.
B) It creates the base directory.
C) It creates the Inventory pointer file.
D) It creates the Oracle user for installation.
E) It modifies the Unix kernel parameters to match Oracle's requirement.
答案：C
Inventory:详细目录
可以查看$ORACLE_BASE/oraInventory/orainstRoot.sh 脚本的内容。该脚本实际上完成了以下工作：
（1）创建software inventory location pointer file: /etc/oraInst.loc，内容为
inventory_loc=$ORACLE_BASE/oraInventory
inst_group=oinstall
修改该文件属性:chmod 644 /etc/oraInst.loc
（2）创建inventory directory: $ORACLE_BASE/oraInventory
修改文件属性： chmod -R 770 $ORACLE_BASE/oraInventory
chgrp oinstall $ORACLE_BASE/oraInventory
oraInventory目录是用来存储oracle安装的所有软件组件的信息的，每个组件可能占用150k的空间.
128. Your database is started with SPFILE. You want the database instance to be dynamically registered with a
listener L2 with the following details:
Protocol: TCP
Host: indl151e
Port: 1525
Which is the correct order of the steps that you would follow to achieve this?
1. Set the LOCAL_LISTENER parameter to L2 dynamically.
2. Make an entry for L2 in tnsnames.ora on the database server.
3. Restart L2.
4. Modify the listener.ora file to add the instance name in SID_LIST of L2.
A) 1, 2, 4, 3
B) 1, 2, 3; 4 is not required.
C) 2, 1; 3 and 4 are not required.
D) 1, 2; 3 and 4 are not required.
答案：C
Dynamically register就是不需要修改listener.ora。DB Instance会自动通知listener。
如果在listerer.ora中配置sid_llist，那就是静态注册了，而本题考的是动态注册。
一、静态注册
由于静态注册，参数是手动静态添加，与数据库无关。数据库无法确认监听是否正确配置。因此，lsnrctl中的status显示状态为unkown。即不保证能连通数据库。注意：静态注册监听，客户端在配置tnsnames.ora服务命名时，“（Oracle 8i或更高版本）服务名”里填写内容要与服务端静态注册监听器时的全局数据库名一致。否则，无法连通
二、动态注册
1、缺省的动态注册
pmon在数据库启动到mount或open时，动态从参数文件中读取service_names值。service_names可以为多个值。service_names缺省为dbca建立数据库时的全局数据库名。
注意：不管参数service_names为何值，pmon都会自动以全局数据库名(这里为mydata.ccddt.cn)为服务名，动态注册一个监听。缺省情况下，若启用动态注册监听，端口号必须为1521。若启用其他端口的动态监听注册，必须要做相关配置。通过查看v$session，状态为SYS$USERS的连接为通过静态注册监听连接到服务器。
2、自定义端口的动态监听注册
若要启用非默认端口1521的动态监听注册，缺省状态，Oracle不会进行动态注册。要启用动态注册，必须设置local_listener参数。并在服务端配置tnsnames.ora指定监听参数，或者直接通过修改local_listener指定监听参数。步骤如下：
1）服务端: netmgr，配置监听程序，监听端口为1525（非默认端口）,保存配置
2）指定监听参数
法1：直接通过修改local_listener参数指定
SQL>alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.23)(PORT=1525))';
SQL>alter system register
法2：在Oracle服务器端建立$ORACLE_HOME/network/admin/tnsnames.ora。解析文件，位置并填入如下内容mytest =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCP)(HOST = 192.168.1.23)(PORT = 1525)) 这里的mytest也可以根据自己需要，修改为其他字符串。如a或b等。
设置参数，指定通过tnsnames.ora内的信息指定监听参数SQL>alter system set local_listener=mytest;
SQL>alter system register;
3、查看监听器状态 LSNRCTL> status
(warehouse)show parameter local_listener
一定要配置服务端tns 让pmon自动注册其他端口
a123=
(
(address_list=(address=(prottocol=tcp)(host=xys)(port=1522))
)
tnsping a123; tnsping+服务命名
alter system set local_listener=a123;
129. The junior DBA in your organization has accidentally deleted the alert log file. What will you do to create new alert log file?
A) Create the new text file file as ALERT.LOG.
B) You have to recover the alert log file from the valid backup.
C) Change the value for the BACKGROUND_DUMP_DEST parameter.
D) No action required.The file would be created automatically by the instance.
答案： D
不小心删除alert文件没有关系，数据库会自己自动重建
130.View the Exhibit
Your Oracle 10g database has 6 tablespaces in which)
-TEMP is the default temporary tablespace
-UNDOTBS1 is the default undo tablespace
-USERS is the default permanent tablespace
In this database, which two tablespaces can be made read only? (Choose two.)
A) TEMP
B) PROD
C) USERS
D) SYSAUX
E) SYSTEM
F) UNDOTBS1
答案:BC
system、sysaux、undo、temp表空间是不能被设置为只读的
SQL> alter tablespace sysaux read only;
第 1 行出现错误:
ORA-13505: SYSAUX 表空间无法设置为只读
SYSAUX表空间作为SYSTEM的辅助表空间
1) 不能删除 2)不能重命名 3)不能设为只读4)可以offline(why?)
如果SYSAUX表空间失效，比如发生介质故障后有些数据库的功能会随之失效。
将表空间设置为只读状态后，与其相关的数据文件中的SCN值和内容都不会发生任何变化。
131. You are using the backup scheduler in Enterprise Manager (EM) to schedule a backup of your database. Which type of script does the backup scheduler generate?
A) SQL script
B) PL/SQL script
C) Operating System (OS) script
D) Enterprise Manager (EM) script
E) Recovery Manager (RMAN) script
答案： E
132. You are in the middle of a transaction and very crucial data has been modified. Because of a hardware failure, the instance has shut down before synchronizing all the database files. Which two statements are true? (Choose two.)
A) On startup, SMON coordinates instance recovery.
B) On startup, CKPT coordinates instance recovery.
C) On startup, use RMAN to perform instance recovery.
D) Uncommitted changes will be rolled back after the database is opened.
E) On startup, perform media recovery and then instance recovery.
F) On startup, all the files will be synchronized and you get both committed and uncommitted data.
答案：AD
由于硬件损坏，类似意外掉电，这就意味着shutdown abort,当重新启动后SMON进程会自动恢复实例，uncommited的数据会自动rollback,同时,commited的数据而又未写到datafile中的会被写进datafile中。并没有数据文件丢失，不需要介质恢复,E错,容易迷惑
System Monitor Process (SMON)
The system monitor process (SMON) performs recovery, if necessary, at instance startup. SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary managed tablespaces. If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or file is brought back online. SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it. With Real Application Clusters, the SMON process of one instance can perform instance recovery for a failed CPU or instance.
133.After being hired as a database administrator, you find that there is only one database that is functional and that is being accessed by the applications. You want to create a replica of the database, to be used for testing purposes. What is the best method to create the replica?
A) create a database by using CREATE DATABASE .. command and manually copy the data
B) use Database Configuration Assistant (DBCA) to create a template from the existing database to contain the database structure
C) use DBCA to create a template from the existing database to contain the database structure and then manually copy the data using Oracle Data Pump
D) use DBCA to create a template from the existing database to contain the database structure with data files and then use the same template to create the database in the new location
答案：D
134. You are working on an online transaction processing (OLTP) system. You notice that a PL/SQL procedure got executed twice at 2:00 p.m. This has incorrectly updated the EMP_SAL table. How would you revert the table to its state at 2:00 p.m.?
A) Perform point-in-time recovery to 2: 00 p.m.
B) Use Flashback Table feature to revert the changes.
C) Restore the entire database from the recent backup and open it.
D) Issue the rollback statement with system change number (SCN).
答案：B
Flashback Table单独恢复某个表,不影响别个表数据,即别的业务可以正常进行
135. View the Exhibit.
What would happen if you change the value of Desired Mean Time To Recover to 30?
A) The MTTR Advisor would be enabled.
B) The Redo Log Advisor would be disabled.
C) Automatic checkpoint tuning would be disabled(不可用).
D) The redo log from log buffers would be written to redo log files at a slower rate.
答案： A
Automatic Checkpoint Tuning in 10g [ID 265831.1] How to enable: ============== Automatic checkpoint tuning is enabled by default. If it is disabled, by setting the parameter to zero explicitly, you can enable it by unsetting FAST_START_MTTR_TARGET or set it to a nonzero value. If you set this parameter to zero this feature will be disabled. Note: this is different from defaulting (i.e. not setting) fast_start_mttr_target to 0.
每当我设置fast_start_mttr_target=0的时候MTTR advisory 会自动关闭
你可以看看告警日志，里面有这样一段： MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
136. View the Exhibit to observe the privileges granted to the MGR_ROLE role.
The SCOTT user has been granted the CONNECT and RESOURCE roles only. The database administrator (DBA) grants MGR_ROLE to the SCOTT user by using this command:
SQL> GRANT MGR_ROLE TO SCOTT WITH ADMIN OPTION;
Which statement is true about the SCOTT user after he is granted this role?
A) The SCOTT user can grant the MGR_ROLE role and the privileges in it to other users.
B) The SCOTT user can grant the privileges in the MGR_ROLE role to other users but not with ADMIN_OPTION.
C) The SCOTT user can grant only the MGR_ROLE role to other users but not the privileges in it.
D) The SCOTT user can grant the privileges in the MGR_ROLE role to other users but cannot revoke privileges from them.
答案：C
ADMIN OPTION当有这个字段时，表明可以把该权限赋予另外一个人
获得该角色的用户只对该角色有管理的权限,不能对该角色所拥有的对象权限或系统权限进行操作.(需实验)
另:授予他人的对象权限在被授予者向第三人授予该权限时，最初授权者无法取消第三人的对象权限。但是如果最初授予的是系统权限，则最初授权人可以取消第三人的系统权限。
137. Which two statements are true about simple views? (Choose two.)
A) Views can be created as read only.
B) Views are data segments like tables.
C) Views can be created on the basis of more than one table.
D) Data manipulation language (DML) operations cannot be performed on views.
答案：AC
视图可以被创建为只读，视图本身是没有数据的，视图可以基于多表建立，在视图上可以执行DML语句
A只是说视图可以被创建为只读,而不是只能创建为只读,小心迷惑
138. Which naming method uses the tnsnames.ora file to store the connect descriptor used by the client while connecting to the database instance from a remote machine?
A) host naming method
B) local naming method
C) external naming method
D) directory naming method
答案：B
tnsnames.ora在Oracle中,Oracle\Network\ADMIN\tnsnames.ora 文件很重要,它作用是： 本地命名的配置。本地名用简单的名称就可以访问到所需的数据库或服务器所需的信息。
local naming method言外之意就是需要把连接db的信息配置到本地（client端）的tnsnames.ora文件里
hostname方法就是把client端的hostname注册到db server端的listener.ora文件里，不再需要在client端本地配置tnsnames文件了，很少有人用了
给你一个DEMO,自己玩玩,假设我的主机名是THOMAS_SERVER 第一步:修改slqnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME) 第二步:修改listener.ora
LISTENER =
(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))) (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = THOMAS_SERVER)(PORT = 1521))) ) ) SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =(GLOBAL_DBNAME = THOMAS_SERVER)
(ORACLE_HOME = d:\oracle10g)
(SID_NAME = ora10g)
)
)
第三步:测试连接
sqlplus[email=username/password@THOMAS_SERVER]username/password@THOMAS_SERVER[/email]
139. Your database is running in the ARCHIVELOG mode. You placed a tablespace, tbs_1, offline with the immediate option. Which statement is correct in this scenario?
A) The operation would fail if tbs_1 were a read-only tablespace.
B) The operation would fail if tbs_1 were a read/write tablespace.
C) The operation would fail if tbs_1 were the default tablespace for the database.
D) If the above operation were successful, media recovery would be required to bring the tablespace online.
E) If the above operation were successful, instance recovery would be required to bring the tablespace online.
答案：D
(一)做把试验就知道了原因应该是：当你上线表空间的时候，你表空间数据文件的头部信息可能已经和当前的数据库的不同步了，因为有可能在你的表空间下线的时候，数据库发生过检查点事件，而检查点会更新控制文件和数据文件头部，显然你下线的那部分表空间数据文件没有得到更新，因此上线的时候要做介质恢复，其实就是更新头部到一致
（二）写的很清楚，是TableSpace Offline Immediate。不是Instance Shutdown Immediate。因此做的是Media Recovery。而不是Instance Recovery。
想要offline tablespace有以下三种方式： OFFLINE {NORMAL | TEMPORARY | IMMEDIATE} 其中，normal是默认的。
----offline normal: idle>select checkpoint_change#,last_change#,offline_change# from v$datafile where ts#=14;
offline normal，tablespace内所有的数据文件上触发checkpoint。 checkpoint_change#增加。
online时，不需要media recovery，同时tablespace内所有的数据文件上再次触发checkpoint。 checkpoint_change#增加。
----offline temporary:证明了文档中的如下说法： If no files are offline, but you use the temporary option, media recovery is not required to bring the tablespace back online.
对照之前的checkpoint，可以发现：offline temporary只对那些online的数据文件进行checkpoint，而且
在将tablespace online 的时候，那些进行过checkpoint的数据文件将不需要media recovery
----offline immediate
tablespace内所有的数据文件上不触发checkpoint。 checkpoint_change#不变。
将tablespace online 时需要media recovery。online时，tablespace内所有的数据文件上再次触发checkpoint。 checkpoint_change#增加。
参考http://www.itpub.net/threadqui.php?id=4&tid=807491
140. You work in a data warehouse environment that involves the execution of complex queries. The current content of the SQL cache holds the ideal workload for analysis. You want to analyze only few most resource-intensive statements. What would be your suggestion to receive recommendations on the efficient use of indexes and materialized views to improve query performance?
A) Run the SQL Access Advisor.
B) Run the SQL Tuning Advisor (STA).
C) Run the Automatic Workload Repository (AWR) report.
D) Run the Automatic Database Diagnostic Monitor (ADDM).
答案：A
141. You have a large amount of historical data in an operating system file. Some analysts in your organization need to query this data. The file is too large to load into your current database. Which is the most effective method to accomplish the task? A) Upgrade the hardware/memory to accommodate the data.
B) Load the data into your database by using the PARALLEL clause.
C) Give analysts DBA privilege, so that they can query DBA_EXTERNAL_TABLES.
D) Use an external table so you can have the metadata available in your database, but leave the data in the operating system files.
答案：D
142. Which two statements are true about a bitmap index? (Choose two.)
A) It is recommended for the columns that have unique values.
B) It can be converted to a B-tree index by using the ALTER INDEX command.
C) It has a bitmap segment for each distinct value in the key column, containing a string of bits in which each bit represents the presence or absence of a key column value.
D) Updating the key column locks the whole bitmap segment that contains the bit for the key value to be updated.
答案：CD
位图索引---在关键列上，每一个不同值都有位图段，更新关键列，会锁住位图段，包含主要要被更新的段
1：bitmap 索引是分段存储的，也就是说很多条记录可能是分做了N段来存储，也就是有N个begin/end ,当新的记录 insert 而使用以前未曾使用过的物理地址的时候，会产生一个bitmap 段来存储，就算只有一条记录 2： 当删除一条记录的时候,在bitmap 索引上做了一个delete 的标记并用一新的记录来标记了,下面请看具体的演示 3: 当 dml发生的时候，会lock住某个值的存储bit的那一rowid所在的记录，参考下面的 row 中 lock ,这样显然会影响并发
参考biti_rainy早年实验 http://www.itpub.net/114023.html
143. User SCOTT wants to export his objects using Oracle Data Pump and executes the following command:
$ expdp scott/tiger
directory = EXPORT_DIR
dumpfile = scott.dmp
include = table
include = view："like '%DEPARTMENTS%'"
content = DATA_ONLY
Which task would the command accomplish?
A) Oracle Data Pump would export only the data of all of the tables and views.
B) Oracle Data Pump would export all of the table structures along with data and all the views.
C) Oracle Data Pump would export the table data and the view definitions where the view name contains a string named DEPARTMENTS.
D) Oracle Data Pump would export the table data and the view definitions with data where view name contains a string named DEPARTMENTS.
E) Oracle Data Pump would export all of the table structures and the view definitions with data where view name contains a string named DEPARTMENTS.
答案：C
题目答案是C，导出表中的数据以及包含DEPARTMENTS的视图定义。
http://space.itpub.net/24131851/viewspace-678073
我的实验结果也是data_only似乎不应该与include=view同时使用，否则会有ORA-39168: Object path VIEW was not found 错误。
144. Which two statements regarding the LOGGING clause of the CREATE TABLESPACE... statement are correct? (Choose two.)
A) This clause is not valid for a temporary or undo tablespace.
B) If the tablespace is in the NOLOGGING mode, no operation on the tablespace will generate redo.
C) The tablespace will be in the NOLOGGING mode by default, if not specified while creating a tablespace.
D) The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
答案：AD
都是很基础的概念性问题，临时表空间都是nologging的
logging clause
这个子句声明这个表空间上所有的用户对象的日志属性（缺省是logging），包括表，索引，分区，物化视图，物化视图上的索引，分区。
FORCE LOGGING
使用这个子句指出表空间进入强制日志模式。此时，系统将记录表空间上对象的所有改变，除了临时段的改变。这个参数高于对象的nologging选项。
注意：设置这个参数在临时表空间和回滚表空间中不能使用这个选项。
With the given CREATE TABLESPACE statement, the tablespace created can be changed to the LOGGING mode, and using the NOLOGGING clause in the statement will save space in the redo log files.
The tablespace created using the NOLOGGING clause can be changed to the NOLOGGING mode by using the ALTER TABLESPACE statement with the NOLOGGING clause. When a tablespace is created by using the NOLOGGING clause,the operations in the tablespace will not generate any redo data. As a result, space will be saved in the redo log files.
The objects created using the NOLOGGING clause cannot be recovered because no redo is generated for these objects.
Therefore, the NOLOGGING clause should be used with objects that can be easily re-created without the redo data.
The NOLOGGING clause is not valid for a temporary or undo tablespace.
D选项是说在表空间级别的logging选项没有在表、视图等的级别高
145. You want to monitor and control the resource usage by sessions. You want to be warned automatically when more than 100 sessions are opened with your database. What action would you take to achieve this?
A) Use the Database Resource Manager.
B) Set the limits in the profiles used by users.
C) Modify the SESSIONS initialization parameter.
D) Set the warning threshold for the Current Logons Count metric.
答案：D
select * from dba_users;
create user test7 identified by test7 default profile;
口令10次锁定 password验证函数 idle time cpu空闲
建立新的profile sys: @..\rdbms\admin\utlpwdmg.sql;可以修改这段脚本
password验证函数 verify_function
Editor’s notes:Current Logons Count is system metric values of oracle. You can see this value by querying v$sysmetric.
SELECT * from v$sysmetric t WHERE t.METRIC_NAME='Current Logons Count';
从全局或系统级出发，数据库管理员通常想解决以下问题： 总的来讲，我的数据库运行状况如何？哪些因素影响效率？ 我的用户获得的平均响应时间是多少？ 哪些活动对总响应时间的影响最大？ 在 Oracle 数据库 10g 推出之前，数据库管理员很难准确回答这些问题，但现在如果您碰巧正在使用最新、最可靠的 Oracle 数据库，则可以轻松获得这样的度量。
通过在 Oracle 数据库 10g 中发出以下查询可以大体上获知数据库的运行状况： select METRIC_NAME,VALUE from SYS.V_$SYSMETRIC where METRIC_NAME IN ('Database CPU Time Ratio','Database Wait Time Ratio') AND INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);
详细资料:http://testingtop.com/bbs/viewthread.php?tid=3838
146. In your database, the STATISTICS_LEVEL initialization parameter is set to BASIC. What is the impact of this setting?
A) The optimizer statistics are collected automatically.
B) Only the timed operating system (OS) statistics and plan execution statistics are
collected.
C) The Oracle server dynamically generates the necessary statistics on tables as part of query optimization.
D) The snapshots for the Automatic Workload Repository (AWR) are not generated automatically.
E) Snapshots cannot be collected manually by using DBMS_WORKLOAD_REPOSITORY PACKAGE.
答案：D
statistics_level参数是oracle9.2开始引入的一个控制系统统计参数收集的一个开关.一共有三个值:basic,typical,all.支持alter session,alter system 动态修改.如果要用statspack或者AWR收集系统性能统计数据.那么这个参数的值必须为typical或all.通常all是一个全面收集,包括OS以及sql执行路径方面的一些统计信息,除非遇见非常严重的性能问题或在一些特殊的性能挣断方面才会用到statistics_level=all,平常statistics_level=typeical 已经足够挣断99%的性能问题了.statistics_level=basic的情况下,oracle关闭了所有性能数据的收集,也就是如果要关闭AWR或statspack收集,只要设置alter system set statistics_level=basic;就行了;
147. You have a large amount of historical data in an operating system file. Some analysts in your organization need to query this data. The file is too large to load into your current database. Which is the most effective method to accomplish the task?
A) Upgrade the hardware/memory to accommodate the data.
B) Load the data into your database by using the PARALLEL clause.
C) Give analysts DBA privilege, so that they can query DBA_EXTERNAL_TABLES.
D) Use an external table so you can have the metadata available in your database, but leave the data in the operating system files.
答案：D
148. The HR user creates a view with this command:
SQL> CREATE VIEW emp_v AS SELECT * FROM scott.emp;
Now HR wants to grant the SELECT privilege on the EMP_V view to the JIM user. Which statement is true in this scenario?
A) HR can grant the privilege to JIM but without GRANT OPTION.
B) HR can grant the privilege to JIM because HR is the owner of the view.
C) SCOTT has to grant the SELECT privilege on the EMP table to JIM before this operation.
D) HR needs the SELECT privilege on the EMP table with GRANT OPTION from SCOTT for this operation.
答案：D
HR用户根据scott用户的emp表建立了一个视图，他想把该视图的select权限赋予JIM用户，他需要有emp表的with GRANT OPTION才能这么做
缺省不能传递权限,要想能 Sys用户: grant create table to test1 with admin option;
而后test1授于test2了,然后收回test1,则test2的系统权限还在，对象权限则收回了
test1:grant all on t to test2 with grant option; 对象权限是 grant option
只有角色的赋予才像C选项这样做
149. You execute the following set of commands to create a database user and to grant the system privileges in your production environment.
SQL> CREATE USER user01
IDENTIFIED BY oracle
DEFAULT TABLESPACE tbs1
TEMPORARY TABLESPACE temp
PROFILE default
SQL> GRANT create session, create table TO user01;
While executing the command to create a table, the user gets the following error message and the CREATE
TABLE.. command fails.
ERROR at line 1:
ORA-01950: no privileges on tablespace
What could be the possible reason for this error message?
A) The tablespace TBS1 is full.
B) The user is not the owner of the SYSTEM tablespace.
C) The user does not have quota on the TBS1 tablespace.
D) The user does not have sufficient system privileges to create table in the TBS1 tablespace.
E) The user does not have sufficient privileges to create table on the default permanent tablespace.
答案：C
default tablespace 是定义了用户在不写明表空间时,使用的表空间,而因表空间管理的要求,oracle必须要能管理他的可用大小,就有了quota子句. GRANT create session, create table TO user01 是指user01拥有了建表的权限 ,oracle 也知道了他也有了默认的表空间,但默认的表空间没有给他分配空间,所以出错,你可以在建用户时加上quota 200M on tbs1 (给他200M 空间) 或直接 alter user user01 unlimited on tbs1 (让他随意使用tbs1表空间); 这个CREATE TABLE的权限究竟是针对什么而言? 是针对用户的空间里的建表.可以是该用户在默认表空间里建表,也可以是在其他表空间里的空间中建表.
所以在新建用户后，不光要为用户分配一定的Create等权限，还要为用户在相应的表空间中设置磁盘配额。
150. Which two statements about Flashback Query are true? (Choose two.)
A) It is generated by using the redo log files.
B) It helps in row-level recovery from user errors.
C) It can be performed to recover ALTER TABLE statements
D) It fails when undo data pertaining to the transaction is overwritten.
E) The database has to be opened with the resetlogs option after performing Flashback Query.
答案：BD
flashback table 和flashback query是基于回滚空间的,另flashback database 基于启用的日志(需要resetlog打开), flashback drop基于回收站
151. Your alert log file has the following information:
Tue May 25 17:43: 38 2004
ORA-00060: Deadlock detected. More info in file
/u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc.
What would you do to ensure that database is still running correctly?
A)examine the trace file and kill the session that caused the deadlock
B)examine the trace file and kill both the sessions responsible for the deadlock
C)examine the trace file for details and ask the user who caused the deadlock to rollback the transaction
D)examine the trace file for details and remember that deadlocks are resolved automatically by Oracle database
E)examine the trace file for details and use the Undo Advisor to rollback the transaction that caused the deadlock
答案：D
在alter中看到了死锁，没关系，oracle会自动处理
152. The ST_INFO master table has millions of rows that are updated very rarely. It has a STATE_CODE column that contains the value for 25 states. The table is frequently queried on the STATE_CODE column. Which type of index would you suggest to improve the query performance?
A) B-tree
B) bitmap
C) reverse key
D) function based
答案：B
153. Your database is in NOARCHIVELOG mode and a logswitch happens every 20 minutes. On Thursday morning, media failure resulted in corruption of a data file belonging to the TOOLS tablespace. Backups are taken on every Sunday. What would you do in this situation?
A) recover using Oracle Flashback Database technology
B) restore the data files from backup and perform a complete recovery
C) restore only the corrupted data file and perform tablespace recovery
D) restore the data files from backup and perform cancel-based recovery
E) restore the entire database from the most recent backup and start the instance and open the database
答案：E
在非归档模式下,只能恢复到最近备份的那一刻
155. While setting up an Oracle database for one of your critical applications, you want to ensure that the database is backed up at regular intervals without your intervention. What should you do to achieve the objective?
A) configure the database to run in ARCHIVELOG mode
B) configure the Flash Recovery Area to enable automatic database backup
C) schedule the database backup using DBMS_JOB package after creating the database
D) schedule the database backup using Recovery Manager (RMAN) commands after creating the database
E) schedule the database backup using Database Configuration Assistant (DBCA) while creating the database
答案：E
在创建数据库的时候有个选项是来创建数据库备份的
156. You created a response file and want to check it before starting installations in silent mode. You started installation of Oracle software in interactive mode by providing the response file. In the middle of the installation, you realize that the behavior of the Oracle Universal Installer (OUI) is not consistent with the response file. What action would you take to detect the cause of this behavior?
A) Compare the contents of the install.log file with the response file.
B) Refer to the contents of the oraInst.loc file to verify the steps performed by OUI.
C) Compare the contents of the installActions.log file with that of the response file.
D) Refer to the alert log file for information regarding the actions performed by OUI during installation.
答案：C
你安装数据库时OUI不给响应，这时应该比较installActions.log和response file查看原因
157. Which step do you need to perform to enable a user with the SYSDBA privilege to log in as SYSDBA in iSQL*Plus?
A) The user must be granted the database administrator (DBA) privilege.
B) The user must be listed in the password file for the authentication.
C) No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
D) Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and grant the web Dba role to the user.
答案：D
158. You want to create a tablespace with the following specifications:
1. The tablespace extends automatically.
2. Used and free extents should be managed by bitmaps.
3. Default PCTUSED attribute is set to 60.
4. All the extents would be of size 1 MB.
Which three options would you choose to create the tablespace? (Choose three.)
A) tablespace with AUTOEXTEND enabled
B) tablespace with dictionary-managed extents
C) tablespace with a uniform extent allocation of 1 MB
D) tablespace with segment space management as manual
E) tablespace with segment space management as automatic
答案：ACD
这里的要求是创建个本地管理的表空间,而且表空间是自动扩展的,区的大小统一为1m,pctused为60,说明不能是assm的,
在ASSM管理下,insert通过扫描位图来查找可用的block即使block的可用空间低于pctfree,也不会从位图中摘除,因此pctused参数不再需要,而pctfree参数,仍需要它来指示需要保留多少空间给后续的update导致的行数据增长使用.
159. Which two database operations can be performed at the MOUNT stage of database startup? (Choose two.)
A) renaming of data files
B) dropping the database user
C) renaming of control files
D) multiplexing of control files
E) configuring the database in ARCHIVELOG mode
答案：AE
160. You are using flat files as the data source for one of your data warehousing applications. You plan to move the data from the flat file structures to an Oracle database to optimize the application performance. In your database you have clustered tables. While migrating the data, you want to have minimal impact on the database performance and optimize the data load operation. Which method would you use to load data into Oracle database?
A) use the external table population
B) use the Oracle Data Pump export and import utility
C) use the conventional path data load of SQL*Loader utility
D) use the direct path data load of Oracle export and import utility
答案：C
SQL*LOADER的基本特点：
1）能装入不同数据类型文件及多个数据文件的数据2）可装入固定格式，自由定界以及可度长格式的数据3）可以装入二进制，压缩十进制数据4）一次可对多个表装入数据5）连接多个物理记录装到一个记录中6）对一单记录分解再装入到表中7）可以用数对制定列生成唯一的KEY 8）可对磁盘或磁带数据文件装入制表中9）提供装入错误报告10）可以将文件中的整型字符串，自动转成压缩十进制并装入列表中。
161. You want to protect your database from single point of failure by multiplexing the control file. Your database is started using SPFILE. You have to perform the following tasks:
1. Shut down the database instance.
2. Copy the control file to new location.
3. Configure the CONTROL_FILES parameter.
4. Restart the instance.
Select the correct order in which these tasks need to be performed.
A) 1,2,3,4
B) 3,2,1,4
C) 3,1,2,4
D) 2,3,1,4
答案：C
用排除法:必须先关闭数据库,才能拷贝控制文件
修改CONTROL_FILES 实验
1.OCP教材上修改控制文件的方法 a.configure the control_files parameter b.shutdown the database instance c.copy the control file to new location d.restart the instance 2.官方文档的方法 a.shutdown the database b.copy an existing control file to a new location c.edit the control_files parameter. d.restart the database
光看这两种修改CONTROL_FILE的方法的区别其实就是是先SHUTDOWN数据库还是先修改PARAMETER。但是这两种方法哪种更好呢？ 我觉得OCP教材上的方法才是真正好的方法,现在数据库都是利用SPFILE，如果我们先关闭了数据库,再去修改SPFILE是修改不成的,因为SPFILE是二进制格式的.我真不知道按照官方文档的方法是不是我做到第三步的时候我还要去用CREATE PFILE FROM SPFILE这个语句
修改步骤如下：
SQL> SHOW PARAMETER control
SQL>alter system set control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL', ……..,'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL04.CTL' scope=spfile;
SQL> shutdown immediate;
SQL> host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL C:\ORACLE\PRO DUCT\10.2.0\ORADATA\ORCL\CONTROL04.CTL
SQL> startup;
SQL> SHOW PARAMETER control
162. Examine the following commands executed in your database:
SQL> ALTER SESSION RECYCLEBIN=ON;
Session altered
SQL > CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM hr.employees;
Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp;
Table dropped.
What happens in this scenario?
A) The table is moved to the SYSAUX tablespace.
B) The table is moved to the SYSTEM tablespace.
C) The table is removed from the database permanently.
D) The table is renamed and remains in the TBSFD tablespace.
答案：D
打开回收站功能，当你drop一个表后，该表会被改名同时还放在原来的表空间里
163. When you try to start the Database Control by using the emctl start dbconsole command the following error is displayed:
TZ set to America/New_york
OC4J Configuration issue.
/u01/app/oracle/product/10.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_orcl.us.oracle.com not found.
Which two environment variables do you need to set appropriately to avoid such errors and start the Database Control successfully? (Choose two.)
A) NLS_LANG
B) ORACLE_SID
C) ORACLE_HOME
D) ORACLE_BASE
E) LD_LIBRARY_PATH
答案：BC
ORACLE_HOME＝$ORACLE_BASE/product/version
ORACLE_BASE是oracle的根目录，ORACLE_HOME是oracle产品的目录
164. View the Exhibit.
The DBA creates a new user as follows:
SQL> CREATE USER manfus IDENTIFIED BY manfus TEMPORARY TABLESPACE temp;
User manfus creates a table as follows:
SQL> CREATE TABLE material (id NUMBER (3));
In which tablespace will the material table be created?
A) TEMP
B) PROD
C) USERS
D) SYSAUX
E) SYSTEM
答案：C
165. You want to move all the objects of the APPS user in the test database to the DB_USER schema of the production database. Which option of IMPDP would you use to accomplish this task?
A) FULL
B) SCHEMAS
C) REMAP_SCHEMA
D) REMAP_DATAFILES
E) REMAP_TABLESPACE
答案:C
相关资料：
1.该参数的使用方法REMAP_SCHEMA=source_schema:target_schema 2.即使你指定的对应SCHEMA不存在，只要导入时连接的用户有足够的权限，就会使用DUMP文件中的CREATE USER的metadata来创建一个对应的用户的。
IMP工具的FROMUSER和TOUSER参数可以实现将一个用户的的数据迁移到另外一个用户。同样的功能在IMPPDP工具中如何得以体现呢？ 答案就是：使用IMPPDP的REMAP_SCHEMA参数实现。
1. REMAP_DATAFILE 该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬移表空间时可能需要该选项. REMAP_DATAFIEL=source_datafie:target_datafile
2. REMAP_SCHEMA 该选项用于将源方案的所有对象装载到目标方案中.
166. A user complains about getting this error after issuing a certain SQL statement:
ORA-02393: exceeded call limit on CPU usage
Because of the error, the SQL statement gets aborted. What action would you take to increase the CPU usage limit in the subsequent sessions of the user?
A) Modify the resource limit in the profile used by the user.
B) Set the RESOURCE_LIMIT initialization parameter to FALSE.
C) Increase the value of the SESSION_CACHED_CURSORS initialization parameter.
D) Increase the value of the SESSION_MAX_OPEN_FILES initialization parameter.
答案：A
你会采取什么行动来增加了限制在随后的CPU的使用 设置资源计划的初始化参数是resource_limit，这个参数设置为TRUE的时候起用资源限制。这
个初始化参数是可以动态修改的。 这个参数书上说只有设为TRUE时，Profile才可以起作用的。 profile分两部分，resource_limit为true限定 资源参数(resource parameters) 设置有效， 密码参数(password parameters)设置始终有效，不管resouce_limit的值为true或false
ROFILE的管理（资源文件） 当需要设置资源限制时，必须设置数据库系统启动参数RESOURCE_LIMIT，此参数默认值为FALSE可以使用如下命令来启动当前资源限制： alter system set RESOURCE_LIMIT=true;
(warehouse)
select * from dba_users;
create user test7 identified by test7 default profile;
口令10次锁定 password验证函数 idle time cpu空闲
建立新的profile
sys: @..\rdbms\admin\utlpwdmg.sql;可以修改这段脚本
password验证函数 verify_function
alter user test3 profile profile_test;
show parameter limit;
改resource_limit
alter system resource_limit=true; 让新的profile启用
167: Which three statements correctly describe the functions and use of constraints? (Choose three.) A. Constraints provide data independence. B. Constraints make complex queries easy. C. Constraints enforce rules at the view level. D. Constraints enforce rules at the table level. E. Constraints prevent the deletion of a table if there are dependencies. F. Constraints prevent the deletion of an index if there are dependencies.
答案:C D E
168: You are using three database, Certkiller DB01, Certkiller DB02, and Certkiller DB03, on different host machines in your development environment. The database server configuration, such as IP address and listener port number, change frequently due to development requirements, and you have the task of notifying the developers of the changes. Which connection method would you use to overcome this overhead? A.Host naming B.Local naming C.Easy Connect D.External naming E.directory naming
答案:E
这个也挺容易的，不过你先要理解每个命名的不同点和用途。目录命名把所有的连接标示符都放在一个目录服务器里，这有利于管理员集中管理。题目好像说的是三个数据库服务器的ip地址经常变化吧，那采用目录命名的话就只需要配置一下目录服务器就可以了，而其他的命名方式则需
要逐一修改tnsname和listener.ora
169: You are connecting to an Oracle database server from a client by using the following connect string:SQL> CONNECT hr/hr@pdserver.us.oracle.com:1521/proddb Which naming method is being used in this case?
A.Local Naming B.Easy Connect C.External Naming D.Directory Naming
Answer: B easy connect，应该是在客户端不用做其他的配置只要在 hosts文件里解析了服务器主机名 对应的ip在服务端 运行netca-〉命名方法配置—〉选择 easy connect 到右侧—〉在右侧将easy connect 移道最上-〉完成就可以使用了
170: You want to perform a backup of your database to tape. Which backup format can you use?
A. backup set only
B.image copyonly
C. only user-managed backup
D. both image copy and backup set
E. only incremental image copy backup
答案:A
Editor’s nots:image copy is just like os copy command. It copy thesingle datafile, archived redo log file, or control file.The difference of image copy and backup set shows fllowed:
1，在存储到磁盘上之前,backup set可以利用oracle自带的二进制压缩算法对数据文件和归档日志进行压缩，而image copy不行。
2，backup set不会包括空闲块，如果一个块从未被写入数据，则rman在进行的时候，会忽略这些空块，而image copy则不会，所以采用backup set通常比image copy要快而且占用空间更小。
3，增量备份可以通过backup set实现，不能通过image copy实现。
4，如果你为磁带安装了rman驱动的话，可以直接备份backup set到磁带，而image copy不能直接备份到磁带。
5，两者都可以通过rman转储，但backup set不能通过操作系统级别来生成和转储。
6，rman可以检测2者的是否损坏（corruption），通过操作系统工具进行image copy备份的时候，不能检查corruption.
171: One of the redo log members in your database is lost. You queried V$logfile for further details. What would be the member's status?
A. LOST
B. INVALID
C. EXPIRED
D. DELETED
E. CORRUPT
F. UNKNOWN
G. OBSOLETE
答案:B
Editor’s notes:v$Logfile.status has four values.:
INVALID - File is inaccessible
STALE - File's contents are incomplete
DELETED - File is no longer used
null - File is in use
172: You want to be notified when the space usage of an existing critical tablespace has reached 75% of the allocated space. Which option would you use to achieve this?
A. run a procedure to check the tablespace space usage
B. define the warning threshold for the tablespace at 75% in Database Control
C. create a trigger to be executed when tablespace space usage reaches 75%
D. submit a job by using DBMS_JOB package to check the free space in the tablespace at regular intervals
E. define the warning threshold to be 75% for the tablespace by ALTER TABLESPACE .. ADD THRESHOLD command
答案：B
Editor’s notes:system trigger contains:
System events such as startup, shutdown, and error messages
User events such as logon and logoff
173: Exhibit:View the Exhibit to see the information on existing tablespaces of the PROD database. You observe that a large volume of INSERTs and DELETEs are happening on the TRANS table residing in the USERS tablespace, and you suspect that the TRANS table is fragmented. Which advisory component would you refer to, in order to find the information about table fragmentation?
A. Memory Advisor
B. Segment Advisor
C. SQL Tuning and Access Advisors
D. Automatic Workload Repository (AWR)
E. Automatic Database Diagnostic Monitor (ADDM)
答案:B
Editor’s notes:witch advisor that oracle can use is display by DBA_ADVISOR_DEFINITIONS. Segment advisor is belonged to it. You can use dbms_advisor package to create a task for segment advisor.the method is showed followed:
174: You are working on a test database where instance recovery takes a considerable amount of time. How can reduce the recovery time? (Choose two)
A. By multiplexing the control files
B. By multiplexing the redo log files
C.By decreasing the size of redo log files
D.By configuring mean time to recover (MTTR) to a lower value
E.By setting the UNDO_RETENTION parameter to a higher value
答案:CD
Editor’s notes::decrease the size of redo log files means that the time of roll forward may decrease.So ,Answer C is write.
175: You work as a database administrator for Examsheets.net. Your database is configured for automatic undo management. UNDO_RETENTION is set to 3 hours. You want to flash back a table that was created last year. How far back can the flashback query go? A. 3 hours B. 6 months C. until last year D. until last commit E. until the point when the undo tablespace was refreshed F. until the database is shut down and the memory erased 答案: A
要求是flushback query,包括between timestamp,SCN等方式，都只能显示 UNDO_RETENTION 时间的数据，但可以flashback table to undo存在的任一时间点，可以做实验证明
176. You execute the following set of commands to create a database user and to grant the system privileges in your production database: SQL> CREATE USER user01 IDENTIFIED BY oracle DEFAULT TABLESPACE tbs1 TEMPORARY TABLESPACE temp PROFILE default; SQL> GRANT create session, create table TO user01; Identify the tasks that the USER01 user can perform.
A.Connecting to the database instance and creating tables only
B.Connecting to the database instance, creating tables and dropping tables
C.Only connecting to the database instance
D.Connecting to the database instance, creating tables and granting access for tables in other schemas
答案：C
这个题比149要难, GRANT create session, create table TO user01 是指user01拥有了建表的权限 ,oracle 也知道了他也有了默认的表空间,但默认的表空间没有给他分配空间,所以出错,你可以在建用户时加上quota 200M on tbs1 (给他200M 空间) 或直接 alter user user01 unlimited on tbs1 (让他随意使用tbs1表空间); 这个CREATE TABLE的权限究竟是针对什么而言? 是针对用户的空间里的建表.可以是该用户在默认表空间里建表,也可以是在其他表空间里的空间中建表.所以在新建用户后，不光要为用户分配一定的Create等权限，还要为用户在相应的表空间中设置磁盘配额。
177. The abnormal termination of the database background process causes the database instance to shut down without synchronizing the database files. The steps that will be performed at the next instance startup in random order are as follows: 1) Perform recovery manually 2) SMON applies redo to the database
3) The database opens 4) Uncommitted transactions are rolled back by SMON 5) SMON reads the archived redo log files and online redo log files. Arrange the steps required for instance recovery in the correct order
A.2, 3, and 4 (1 and 5 not required)
B.2, 4, and 3 (1 and 5 not required)
C.5, 3, and 4 (1 and 2 not required)
D.2, 3, and 1 (4 and 5 not required)
答案:A
实例恢复: 启动的时候,从某个点把日志文件里内容完全写到数据文件,打开,把没有提交的rollback
提交仅仅写日志文件,不写数据文件,整个log buffer都写出去(里面有提交和没提交的,包含别人session的,尽管已经写到磁盘了，但别个session自己看不到，必须自己提交) 用以提高效率
写日志优先写数据文件 ,先到log buffer 再到data buffer,内存是重复使用的
对于介质恢复和实例恢复来说，第一个步骤都是通过REDO LOG的信息进行前滚，在做前滚的时候，通过REDO LOG文件里记录的数据库变化矢量，根据SCN的比对，提交到相关的数据文件上，从而使数据文件的状态向前滚动。大家要注意的是，UNDO表空间的变化也被记录到REDO LOG里了，因此UNDO表空间相关的数据文件也会被前滚。当前滚到最后一个可用的REDO LOG或者归档日志的时候，所有的数据库恢复层面的工作就全部完成了。这个时候，数据库包含了所有的被记录的变化，这些变化中有些是已经提交，有些是尚未提交的。在最新状态的UNDO表空间中，我们也可以看到一些尚未提交的事务。
因此数据库下一步需要做的事情是事务层面的处理，回滚那些尚未提交的事务，以确保数据库的一致性。
首先是在实例故障时，可能某些事物对数据文件的修改并没有完全写入磁盘，可能磁盘文件中丢失了某些已经提交事务对数据文件的修改信息。其次是可能某些还没有提交的事务对数据文件的修改已经被写入磁盘文件了。也有可能某个原子变更的部分数据已经被写入文件，而部分数据还没有被写入磁盘文件。实例恢复就是要通过ONLINE REDO LOG文件中记录的信息，自动的完成上述数据的修复工作。这个过程是完全自动的，不需要人工干预。
在这个机制里，有两个问题需要解决，第一个是如何确保已经提交的事务不会丢失，第二个是如何在数据库性能和实例恢复所需要的时间上做出平衡，既确保数据库性能不会下降，又保证实例恢复的快速。
解决第一个问题比较简单，Oracle有一个机制，叫做Log-Force-at-Commit，就是说，在事务提交的时候，和这个事务相关的REDO LOG数据，包括COMMIT记录，都必须从LOG BUFFER中写入REDO LOG文件，此时事务提交成功的信号才能发送给用户进程。通过这个机制，可以确保哪怕这个已经提交的事务中的部分BUFFER CACHE还没有被写入数据文件，就发生了实例故障，在做实例恢复的时候，也可以通过REDO LOG的信息，将不一致的数据前滚。
解决第二个问题，oracle是通过checkpoint机制来实现的。Oracle数据库中，对BUFFER CAHCE的修改操作是前台进程完成的，但是前台进程只负责将数据块从数据文件中读到BUFFER CACHE中，不负责BUFFER CACHE写入数据文件。BUFFER CACHE写入数据文件的操作是由后台进程DBWR来完成的。DBWR可以根据系统的负载情况以及数据块是否被其他进程使用来将一部分数据块回写到数据文件中。这种机制下，某个数据块被写回文件的时间可能具有一定的随机性的，有些先修改的数据块可能比较晚才被写入数据文件。而CHECKPOINT机制就是对这个机制的一个有效的补充，CHECKPOINT发生的时候，CKPT进程会要求DBWR进程将某个SCN以前的所有被修改的块都被写回数据文件。这样一旦这次CHECKPOINT完成后，这个SCN前的所有数据变更都已经存盘，如果之后发生了实例故障，那么做实例恢复的时候，只需要冲这次CHECKPOINT已经完成后的变化量开始就行了，CHECKPOINT之前的变化就不需要再去考虑了。
178. Which two statements are true about the Flashback technology? (Choose two)
A.The accidental loss of schema can be recovered.
B.The accidental loss of tablespace can be recovered.
C.The restoration of files is not required for accomplishing the recovery.
D.The unsolicited or wrongly committed transactions can be recovered.
答案:C,D
Flashback Database 不能解决Media Failure,这种错误RMAN恢复仍是唯一选择,即数据文件的丢失是不能用闪回恢复的
schema:一个用户下所有对象的集合(逻辑概念,有对象自然就存在了)
schema下的过程,视图等的丢失就不能闪回恢复,A错
drop user 可以恢复吗？(需试验)

转载于:https://www.cnblogs.com/longjshz/p/4303373.html