ocp-043

52、You have a read-only tablespace on read-only media. You want to perform. a media recovery on the existing data files, but using a backup control file. The backup control file indicates that the status of the read-only tablespace was read/write when the control file was backed up. What should you consider?

A. take data files from the read-only tablespace offline before performing a recovery

B. recovery using backup control file is not possible, so restore all the files from the last full database backup, and then open the database

C. drop the read-only tablespace and re-create the tablespace after recovery

D. perform. a recovery; status of the tablespace in control file will be changed automatically

文章来自：http://xiangqinghu1988.blog.163.com/blog/static/58822991201121353349211/

Editor’s notes:

If you have a read-only tablespace on read-only media, then you may encounter errors or poor performance when recovering with the USING BACKUP CONTROLFILE option.

This situation occurs when the backup control file indicates that a tablespace was read/write when the control file was backed up. In this case, media recovery may attempt to write to the files. For read-only media, the database issues an error saying that it cannot write to the files.

Following are alternatives you can use to recover read-only media when using a backup control file:

·Take data files from read-only tablespaces offline before doing recovery with a backup control file, and then bring the files online at the end of media recovery.

·Use the correct version of the control file for the recovery. If the tablespace will be readonly when recovery completes, then the control file backup must be from a time when the tablespace was read-only. Similarly, if the tablespace will be read/write at the end of recovery, then the control file must be from a time when the tablespace was read/write.

其实容易忽略“media is read-only” 这个条件，如果不将文件offline，那么在recover的时候，那么要往数据文件中应用read log，因为介质是只读的的，这时候就会报错。

69. In the parameter file of your production database, the FAST_START
_MTTR_TARGET parameter is set to 300 to optimize instance recovery. 
While observing the performance of the database during instancerecov
ery, you find that the redo log files are not sized properly tosuppo
rt this activity. Which two sources could you use to determine the op
timal size of the redo log files? 
(Choose two.) 
A. the V$LOG view 
B. the V$DBFILE view 
C. the V$LOGFILE view 
D. the V$INSTANCE_RECOVERY view 
E. Oracle Enterprise Manager Database Control 
Answer: D, E 
FAST_START_MTTR_TARGET：实例恢复的时间限制，oracle将这个时间换算成
redo blocks数量，当log buffer中未写入log file的redo block数量超过这个
值，就会触发增量检查点。 
v$instance_recovery 视图的 optimal_logfile_size 列通过显示 MTTR 尺寸的
最佳重做日志尺寸，帮助你确定恰当的重做日志尺寸。Oracle 建议所有联机重
做日志至少与 optimal_logfile_size 列值所指定的相同。 
checkpoint 主要 2 个作用： 
1、保证数据库的一致性，这是指将脏数据写出到硬盘，保证内存和硬盘上的数
据是一样的； 
2、缩短实例恢复的时间，实例恢复要把实例异常关闭前没有写出到硬盘的脏数
据通过日志进行恢复。如果脏块过多，实例恢复的时间也会很长，检查点的发生
可以减少脏块的数量，从而提高实例恢复的时间。 
checkpoint 就像 word 的自动保存一样。

189: You have many users complaining about slow inserts into a large table. 
While investigating the reason,you find that the number of indexes on the 
table is high. You want to find out which indexes are not beingused. 
Which method would you follow to achieve this? 
A.enable index monitoring and query the DBA_OBJECTS view 
B.enable index monitoring and query the DBA_INDEXES view 
C.enable index monitoring and query the V$OBJECT_USAGE view 
D.enable index monitoring and view the DBA_INDEXTYPE_COMMENTS view 
Answer: C 
1: $object_usage可以用来监控系统中索引的使用情况 
2: 监控索引的使用：SQL> alter index index_test_pk monitoring usage; 
3: SQL> select * from v$object_usage; 
4: $object_usage只包括当前用户的索引使用记录，如果需要查出所有用户的索
引使用记录，使用下面的sql，这个sql来自DBA日记： 
SQL> select u.name owner, io.name index_name, t.name table_name, 
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring, 
decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,ou.start_monitoring 
start_monitoring,ou.end_monitoring end_monitoring 
from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage 
ou where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo# 
and u.user# = io.owner#; 
5: 取消对索引的监控 
SQL> alter index index_test_pk nomonitoring usage;