oracle-ocp-043试题分类汇总.docx

性能调优1.You observe that a database performance has degraded over a period of time. While investigating the reason, you find the size of the database buffer cache is not large enough to cache all the needed data blocks. Which advisory component wold you refer to, in order to determine that required size of the database buffer cache? A. Memory Advisor B. Segment Advisor C. SQL Tuning Advisor D. SQL Access Advisor E. Automatic Database Diagnostic Monitor(ADDM)A发现数据库运行一段时间后性能下降了，调查发现原因是因为buffer cache不能装下所有需要的数据块，用个工具确定数据库所需要的buffer cache的大小。2.Exhibit -显示所有日志在同一个目录下In your production database, the total waits and the time waited for log file parallel write are significantly high. While investigating the reason, you find that there are three redo log groups with two members in each group, and all redo log members are places on a single physical disk. What action would you take to minimize the waits? A. Start the log writer slave processes B. Increase the number of redo log files C. Increase the size of the redo log buffer D. Place the redo log files on the different disks. E. Increase the number of log writer processes. Answer:D等待并行写日志的时间明显很高。调查发现三个日志文件组，每组由两个成员，都在同一个物理磁盘上。通过什么方法解决问题？-主要是不懂题目意思会出错。24. You are designing an application for Certkiller .com and you have been asked to design a database table to facilitate monthly bill generation. The bill would include details of customer calls, listed in chronological order. Which method would you follow to achieve this objective without increasing the overhead of sorting the rows? A. create a hash cluster to store the data B. create an index cluster to store the data C. create a partitioned table to store the data D. create a sorted hash cluster to store the data E. create a heap table with rowid to store the data Answer: D 27. In which scenarios would you rebuild an index? (Choose all that a pply.) A. when you need to disable the index usage B. when you need to change storage options C. when you need to enable index monitoring D. when you need to move the index to another tablespace Answer: B, D 35. While designing your database, you have created the EMPLOYEES table as an index-organized (IOT). You want to create a bitmap index on the JOD_ID column to make queries faster. Which task must have been completed so that you are able to create the bitmap index? A. A primary key must have been created. B. A mapping table must have been created. C. An overflow tablespace must have been specified. D. The PCTTHRESHOLD option must have been specified. Answer: B 51. You want to use the SQL Tuning Advisor to generate recommendations for badly written SQL statements in your development environment. Which three sources can you select for the advisor to analyze? (Choose three.) A. Top SQL B. snapshots C. SQL Tuning sets D. index access path E. optimizer statistics F. materialized view logs Answer: A,B,C 54. You find that the execution time of reports in your datawarehouseapplication is significantly high. You suspect the lack of indexes tobe the reason for the degradation in performance. Which advisory component would you refer to, in order to determine the appropriate indexes? A. Memory Advisor B. Segment Advisor C. SQL Access Advisor D. Automatic Workload Repository (AWR) E. Automatic Database Diagnostic Monitor (ADDM) Answer: C 你发现你的数据仓库中的执行报告时间非常慢，你怀疑是缺少相应的索引，可以使用 SQL Access Advisor（SQL 访问指导） SQL 访问指导：处理方案问题并确定最佳数据访问路径（如索引和实体化视图） 。实体化视图=物化试图 物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。68. You noticed that the index tablespace in your database requires arecovery. However, instead of performing a media recovery, you decided to re-create the indexes in a new tablespace. Which two options would you use to reduce the time it takes to re-create the indexes? (Choose two.) A. ONLINE B. REVERSE C. PARALLEL D. COMPRESS E. NOLOGGING F. COMPUTE STATISTICS Answer: C, E 关于控制文件恢复3. One of the tablespace is read-only in your database. The los s of all control file forced you to recreate the control file. Which operation do you need to perform after re-creating the control file and opening the database? A. Drop and re-create the read-only tablespaces B. Rename the read-only data file to their correct file names. C. Change the tablespace status from read/write to read-only. D. Re-create the read-only tablespace because it is automatical ly removed. Answer: B本来就是read only，控制文件丢失，重建了控制文件，重新打开数据库的时候需要做什么？重命名那个表空间对应的文件就可以了。这里是将read only,如果是普通情况？道理在什么地方？日志7. In your database, online redo log files are multiplexed and one of the members in a group is lost due to media failure? How would you recover the lost redo log member? A.import the database from the last export B.restore all the members in the group from the last backup C.drop the lost member from the database and then add a new mem ber to the group D.restore all the database files from the backup and then perform a complete recovery E.restore all the database files from the backup and then perform an incomplete recovery Answer: C Redo group 里如果有多个成员，那么他们是冗余的。 每个成员里的内容都一样，所以删除再添加一个即可。19. You are using an Automatic Storage Management (ASM) instance to manage the files of your production database. You have two disk groups, DG1and DG2 with one device each. In the parameter file of the production database, the following parameters have been specified: DB_CREATE_ONLINE_LOG_DEST_1 = +dg1 DB_CREATE_ONLINE_LOG_DEST_2 = +dg2 What would be the impact of this setting? A. When a new log group is added, it would have one member in each disk group. B. When a new log group is added, it would have two members in each disk group. C. When a new tablespace is added, it would have one data file in each disk group. D. When a new log file is added, it would have one member spread across the disk groups. Answer: A DB_CREATE_ONLINE_LOG_DEST_1 = +dg1 DB_CREATE_ONLINE_LOG_DEST_2 = +dg2 当一个新的日志组加入了以后，会在每个磁盘组中有个成员。Editors notes: DB_CREATE_ONLINE_LOG_DEST_n (where n = 1, 2, 3, . 5) specifies the default location for Oracle-managed control files and online redo logs. If more than one DB_CREATE_ONLINE_LOG_DEST_n parameter is specified, then the control file or online redo log is multiplexed across the locations of the other DB_CREATE_ONLINE_LOG_DEST_n parameters. One member of each online redo log is created in each location, and one control file is created in each location. Specifying at least two parameters provides greater fault tolerance for the control files and online redo logs if one of the locations should fail. If a file system directory is specified as the default location, then the directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it . Oracle generates unique names for the files, and a file thus created is an Oracle-managed file. db_recovery_file _dest 参数启用 redo、control、rman backups、archived logs、flashback logs 文件的 omf 管理,类似的 db_create_online_log_dest_n 参数启用 redo、control 的 omf管理,从中我们也可以看出 redo、control 文件的重要性！这三个参数对 redo、control文件位置的影响： 1.位置优先级：【control_files 初始化参数】 db_create_online_l og_dest_n db_recovery_file_dest = db_create_file_dest 此处的等于号比较有意思：如果没有 db_create_online_log_dest_n,而存在db_create_file_dest 时，db_recovery_file_dest 就相当db_create_online_log_dest_2，db_create_file_dest 则相当于db_create_online_log_dest_1 2.redo 文件的多路复用 通常应该通过指定 db_create_online_log_dest_n 来达到该目的！ 3.如果三个参数指定相同的值会怎样？自己猜吧 ：） 4.omf 文件的命名规则 /o1_mf_%t_%u_.dbf (做题目的时候容易想的跟选的不一致)46. A redo log file is corrupted while the database is open; as a consequence, database operations are stopped because archiving cannot continue. What would you do to solve the problem? A. clear the redo log group B. perform redo log file import C. perform an incomplete recovery D. perform a redo log recovery using Recovery Manager (RMAN) E. shut down the database and open the database in the NOARCHIVELOG mode Answer: A 数据库在打开的状态下日志坏掉，然后数据库自动关了，那个坏掉的日志没有归档。只需要使用命令 clear redo log group 就行了，同时由于缺少了一部分 archive log 需要整个数据库重新备份一下52. The loss of which two types of files may require a recovery with the RESETLOGS option?(Choose two.) A. control files B. password file C. archived log files D. system-critical data files for which all the redo entries are present E. non-system-critical data files for which all the redo entries are present Answer: A, C 几点总结：(eygle:) 1:只要拥有当前日志文件,就可以执行完全恢复,是否需要 resetlogs 方式打开,取决是否使用备份的控制文件。 2:如果日志文件没有损失,可以通过重建控制文件的方法来完成完全恢复,这种情况下,不再需要通过 resetlogs 方式打开数据库。 3:如果恢复执行到某一个日志文件停止,数据库要以读写方式打开,则必须执行resetlgos 打开。 (warehous:) select file#,checkpoint_change# from v$datafile;(来自控制文件) select file#,checkpoint_change# from v$datafile_header;(来自数据文件) 当两个结果不相等,需 open resetlogs,即截断多余的 scn 103. You are using Oracle Database 10g. The log LOG_ARCHIVE_FORMAT parameter is set to LOG%t_%_s_%r.dbf. Why is %r used in the file name format? A. To uniquely identify the archived log files with the restore operation. B. To uniquely identify the archived log files with the redo log group number. C. To uniquely identify the archived log files for each incarnation of the database. D. To uniquely identify the archived log files with the number of recovery operations performed. Answer: C 112. Because of hardware failure, you decided to drop a redo log member from the database. Which condition should be met to drop a redo log file? A. The redo log file should belong to an active group. B. The redo log file should belong to an inactive group. C. The redo log file should belong to the current group. D. The redo log file can be dropped only if all the transactions are stopped. E. Before a redo log file is dropped, it should be deleted from the operating system (OS). Answer: B 128. A media failure has occurred. This has resulted in all the members of the next hop log group being rendered inaccessible to the log writer process (LGWR) at a log switch. How does this failure affect the operational database? A. The database re-created the missing redo log files automatically. B.The database continues to function normally with the existing filesC. The database allows only queries, no other statements are allowed.D.The database returns and error and the database instance shuts downE. The user sessions that generate redo logs are terminated automatically. Answer: D 内存管理4. You have set some of the initialization parameters as: DB_BLOCK_SIZE=8KB SGA_MAX_SIZE=2GB SGA_TARGET =0 SHARED_POOL_SIZE=120MB DB_CHCHE_SIZE=896MB STREAM_POOL_SIZE=0 LARGE_POOL_SIZE=110MB Which two statements are correct?(Choose two). A. You can not set a value for the DB_8K_CHCHE_SIZE parameter. B. If you increase the size of the large pool to 120MB, then th e memory allocated to the shared poll will be reduced to 110MB. C. If the value for SGA_TARGET is changed to 1GB and SHARED_POOL_SIZE is 120MB, then memory cannot be taken from the shared pool, even i f the shared pool has free space available. D. If an application attempts to allocate more than 120MB from the shared pool and free space is available in the buffer pool, then the free space from the buffer pool is allocated to the shared pool. Answer: AC几个参数的意思？关系？已经设置了 DB_BLOCK_SIZE = 8k 就不能设置 DB_ 8K_CACHE_SIZE 了 ？ 如果指定了 shared pool size 那么就不能再缩小了，只能增大 不能使用 DB_nK_CACHE_SIZE 参数来调整标准块的大小.例如,假如 DB_BLOCK_SIZE 设置为 2K,设置 DB_2K_CACHE_SIZE. 等于 2K 是无效的.标准缓存的块大小总是由 DB_CACHE_SIZE决定的. Oracle 10g 中，与内存相关的参数可以归为两类： q 自动调优的 SGA 参数：目前这些参数包括 DB_CACHE_SIZE、SHARED_POOL_SIZE、LARGE_POOL_SIZE 和 JA VA_POOL_SIZE。(注意 LOG_BUFFER 不在自动之列) q 手动 SGA 参数：这些参数包括 LOG_BUFFER、STREAMS_POOL、DB_NK_CACHE_SIZE、DB_KEEP_CACHE_SIZE 和 DB_RECYCLE_CACHE_SIZE。 在 Oracle 10g 中，任何时候你都能查询 V$SGAINFO，来查看 SGA 的哪些组件的大小可以调整。注意 要使用自动 SGA 内存管理，参数 STATISTICS_LEVEL 必须设置为 TYPICAL 或 ALL。如果不支持统计集合，数据库就没有必要的历史信息来确定大小。 采用自动 SGA 内存管理时，确定自动调整组件大小的主要参数是 SGA_TARGET，这个参数可以在数据库启动并运行时动态调整，最大可以达到 SGA_MAX_SIZE 参数设置的值（默认等于SGA_TARGET，所以如果想增加 SGA_TARGET，就必须在启动数据库实例之前先把 SGA_MAX_SIZE 设置得大一些）。数据库会使用 SGA_TARGET 值，再减去其他手动设置组件的大小（如 DB_KEEP_CACHE_SIZE、DB_RECYCLE_CACHE_SIZE 等），并使用计算得到的内存量来设置默认缓冲区池、共享池、大池和 Java 池的大小。在运行时，实例会根据需要动态地对这 4 个内存区分配和撤销内存。如果共享池内存用光了，实例不会向用户返回一个 ORA- 04031“Unable to allocate N bytes of shared memory”（无法分配 N 字节的共享内存）错误，而是会把缓冲区缓存缩小几 MB（一个颗粒的大小），再相应地增加共享池的大小。28. You enabled Automatic Shared Memory Management. The initialization parameters are set as shown below: SGA_TARGET= 10GB SGA_MAX_SIZE = 14GB STREAMS_POOL_SIZE = 1GB SHARED_POOL_SIZE = 3GB Which two statements are correct in this scenario? (Choose two.) A. A maximum of 3 GB can be allocated to shared pool. B. The value for SGA_TARGET can be increased up to a maximum of 14 GBC. A total of 14 GB memory will be allocated to the automatically tuned memory components. D. Increasing the value for SGA_TARGET will automatically increase the memory allocated for STREAMS_POOL_SIZE. E. Increasing the value for SGA_TARGET to 12 GB will automatically increase the memory allocated to autotuned parameters. F. Reducing the value for SGA_TARGET to 9 GB will automatically decrease the memory allocated to shared pool from 3 GB to 2 GB. Answer: B, E SHARED_POOL_SIZE 设置值后只会增加,不会减少Rman5. You are performing a block media recovery on the tools01.dbf data file in the SALES database using RMAN. Which two statements are correct in this scenario? (Choose two.) A. You must ensure that the SALES database is mounted or open. B. You must restore a backup control file to perform a block media recovery. C. You must take the tools01.dbf data file offline before you start a block media recovery. D. You must put the database in NOARCHIVELOG mode to perform a block media recovery. E. You can perform only a complete media recovery of individual blocks, point-in-time recovery of individual data blocks is not supported. Answer: A, E 6.Exhibit You executed the following command to perform a backup of the USERStablespace: RMAN BACKUP TABLESPACE USERS; Which type of backup would this command perform? A. backup set B. image copy C. incremental backup D. None; the user receives an error indicating that the backup type must be specified. Answer: A 缺省为 CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; 如果要镜像备份: CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;11. You work as a database administrator at Certkiller .com. Your database is open and running in ARCHIVELOG mode. You take RMAN full backups every Sunday night. On Monday morning, while querying the user1.employees table, you receive the following error message: 01578: ORACLE data block corrupted (file # 5, block # 51) ORA-01110: data file 5: /u01/app/oracle/oradata/orcl/example01.dbf You need to rectify the corruption while ensuring the following: The data file should remain online. The mean time to recover (MTTR) should be minimal. You are not using a backup control file and all thearchived logs are accessible. Which option would you choose? A. flash back the corrupted blocks B. use the DBMS_REPAIR package C. use the RMAN TSPITR command D. use the RMAN BLOCKRECOVER command E. use the RESTORE DATABASE and RECOVER DATABASE commands F. investigate the time at which the corruption occurred and perform a point-in-time recovery Answer: D 数据库在归档模式下，每个星期天进行一次全备份，星期一早晨发现数据块损坏，要求 MTTR 时间最少，要求 data file 在线，所以最好的方法就是 RMAN BLOCKRECOVER17. The current time is 12:00 noon. You want to recover the USERS tablespace from a failure that occurred at 11:50 a.m. You discover that the only member of an unarchived redo log group containing information from 11:40 a.m. onwards is corrupt. With reference to this scenario, if you are not using Recovery Manager (RMAN), which recovery method would you use? A. time-based recovery B. log sequence recovery C. cancel-based recovery D. change-based recovery Answer: C 使用 recover database until cancel 进行恢复时，可以一直输入回车，直到没有归档日志文件可用为止（在这种情况下，若已经将所有的在线日志文件归档，那么利用这种情况可以恢复所有的数据）；也可以在恢复的过程中，输入 CANCEL，利用一部分归档日志文件进行恢复（这种情况可能只能恢复一部分数据）。In cancel-based recovery, recovery proceeds by prompting you with the suggested filenames of archived redo log files. Recovery stops when you specify CANCEL instead of a filename or when all redo has been applied to the datafiles. Cancel-based recovery is better than change-based or time-based recovery if you want to control which archived log terminates recovery. For example, you may know that you have lost all logs past sequence 1234, so you want to cancel recovery after log 1233 is applied. RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE The database indicates whether recovery is successful. If you cancel before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get an ORA-1113 error if more recovery is necessary. As explained in Determining Which Datafiles Require Recovery, you can query V$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery. (warehous 实验)把 25,26 号删除,恢复到 24，再手工输入联机日志文件,因为该语句recover database using backup controlfile 是一个完全恢复，oracle 需要用到当前联机日志的!(即使有相同序号的归档日志也不会用)现在已经恢复到 26 号了,27 号没有了，