OCP读书笔记(23)

andyyuduo

　　200.Which operation requires that you create an auxiliary instance manually before executing the
　　operation? (Choose all that apply.)
　　A. Backup-based database duplication.
　　B. Active database duplication.
　　C. Tablespace point-in-time recovery.
　　D. No operation requires the creation of an auxiliary instance.
　　Answer: A, B
　　202.In which two aspects does hot patching differ from conventional patching? (Choose two.)
　　A. It consumes more memory compared with conventional patching.
　　B. It can be installed and uninstalled via OPatch unlike conventional patching.
　　C. It takes more time to install or uninstall compared with conventional patching.
　　D. It does not require down time to apply or remove unlike conventional patching.
　　E. It is not persistent across instance startup and shutdown unlike conventional patching.
　　Answer: A, D
　　参考：http://blog.iyunv.com/rlhua/article/details/16574625
　　


热补丁注意事项

· 可能不是所有平台上都有热补丁程序。当前在以下平台上有热补丁程序：
– Linux x86
– Linux x86-64
– Solaris SPARC64
· 要消耗一些额外的内存。
– 确切的内存数取决于：
— 补丁程序的大小
— 当前运行的Oracle 进程数
– 最小内存数：每个Oracle 进程大约占一个OS 页面　　206.Which statements are true regarding the Query Result Cache? (Choose all that apply.)
　　A. It can be set at the system, session, or table level.
　　B. It is used only across statements in the same session.
　　C. It can store the results from normal as well as flashback queries.
　　D. It can store the results of queries based on normal, temporary, and dictionary tables.
　　Answer: A, C
　　207.You want to analyze a SQL Tuning Set (STS) using SQL Performance Analyzer in a test database.
　　Which two statements are true regarding the activities performed during the test execution of SQLs in a SQL Tuning Set? (Choose two.)
　　A. Every SQL statement in the STS is considered only once for execution.
　　B. The SQL statements in the STS are executed concurrently to produce the execution plan and execution statistics.
　　C. The execution plan and execution statistics are computed for each SQL statement in the STS.
　　D. The effects of DDL and DML are considered to produce the execution plan and execution statistics.
　　Answer: A, C

　　211.You want to disable resumable space allocation for all sessions.
　　Which value should be assigned to the RESUMABLE_TIMEOUT parameter to disable resumable space
　　allocation for all sessions?
　　A. 0
　　B. 10
　　C. 100
　　D. NULL
　　Answer: A

　　214.You issued the following command on the temporary tablespace LMTEMP in your database:
　　SQL>ALTER TABLESPACE lmtemp SHRINK SPACE KEEP 20M;
　　Which requirement must be fulfilled for this command to succeed?
　　A. The tablespace must be locally managed.
　　B. The tablespace must have only one temp file.
　　C. The tablespace must be made nondefault and offline.
　　D. The tablespace can remain as the default but must have no active sort operations.
　　Answer: A

　　对于已经分配和还未分配的Extent的元数据可能存放在数据字典中（字典管理表空间），也可能存放在tablespace header中（本地管理表空间）。
　　可以通过以下语句来查看当前的表空间是本地管理还是字典管理：
　　select tablespace_name, extent_management, allocation_type from dba_tablespaces;
　　字典管理表空间（DMT）
　　Oracle使用SYS.FET$表来记录空闲空间，使用SYS.UET$表来记录已经使用过的空间。当从表空间分配新的Extent时，必须先获得space transaction(ST) enqueue latch后才能插入或删除SYS.FET$和SYS.UET$表。在特定的时间内，只有一个进程能获得ST enqueue，这通常会引起竞争和等待。
　　本地管理表空间（LMT）
　　使用LMT，每个表空间使用表空间数据文件中的一个bitmap结构来管理空闲和已使用的空间的元数据信息。每一bit对应一个数据库block或一组blcok。
　　216.Which three elements can a job chain process involve? (Choose three)
　　A. an event
　　B. a schedule
　　C. a program
　　D. another chain
　　E. a lightweight job
　　Answer: B, D, E

　　program只是Job的组成部分。
　　218.Your database is running in ARCHIVELOG mode. You are performing a user-managed backup of the
　　DATA1 tablespace. You place the DATA1 tablespace in backup mode by issuing the following statement:
　　ALTER TABLESPACE data1 BEGIN BACKUP;
　　While you are performing the backup, an error occurs that causes the instance to terminate abnormally.
　　Which statement about the DATA1 tablespace is true?
　　A. The DATA1 tablespace is automatically taken out of backup mode when the instance aborts.
　　B. If you restart the database, the DATA1 tablespace will be automatically taken out of backup mode when the database is opened.
　　C. If you restart the database, the DATA1 tablespace will be automatically taken out of backup mode when the database is mounted.
　　D. If you restart the database, the database will not be opened.
　　Answer: D

　　220.Which statements describe the capabilities of the DBMS_NETWORK_ACL_ADMIN package?
　　(Choose all that apply.)
　　A. It can be used to allow the access privilege settings for users but not roles.
　　B. It can be used to allow the access privilege settings for users as well as roles.
　　C. It can be used to control the time interval for which the access privilege is available to a user.
　　D. It can be used to selectively restrict the access for each user in a database to different host computers.
　　E. It can be used to selectively restrict a user's access to different applications in a specific host computer.
　　Answer: B, C, D

　　221.To generate recommendations to improve the performance of a set of SQL queries in an application,
　　you execute the following blocks of code:
　　BEGIN dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor,'TASK1'); END;/
　　BEGIN dbms_advisor.set_task_parameter('TASK1','ANALYSIS_SCOPE','ALL');
　　dbms_advisor.set_task_parameter('TASK1','MODE','COMPREHENSIVE');
　　END;
　　/
　　BEGIN
　　dbms_advisor.execute_task('TASK1');
　　dbms_output.put_line(dbms_advisor.get_task_script('TASK1'));
　　END;
　　/
　　The blocks of code execute successfully; however, you do not get the required outcome.
　　What could be the reason?
　　A. A template needs to be associated with the task.
　　B. A workload needs to be associated with the task.
　　C. The partial or complete workload scope needs to be associated with the task.
　　D. The type of structures (indexes, materialized views, or partitions) to be recommended need to be specified for the task.
　　Answer: B

　　223.You plan to set up the Automatic Workload Repository (AWR) baseline metric thresholds for a moving window baseline. Which action would you take before performing this task?
　　A. Compute the baseline statistics.
　　B. Take an immediate AWR snapshot.
　　C. Decrease the window size for the baseline.
　　D. Decrease the expiration time for the baseline.
　　Answer: A

　　225.View the Exhibit to examine the Automatic Database Diagnostic Monitor (ADDM) tasks. You executed the following commands:
　　SQL> VAR tname VARCHAR2(60);
　　SQL> BEGIN :tname := 'my_instance_analysis_mode_task';
　　DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(:tname,'Sg_directive','SCOTT');
　　END;
　　Which statement describes the consequence?
　　
　　A. The ADDM task is filtered to suppress the Segment Advisor suggestions for the SCOTT schema.
　　B. The ADDM task is filtered to produce the Segment Advisor suggestions for the SCOTT schema only.
　　C. The PL/SQL block produces an error because the my_instance_analysis_mode_task task has not been reset to its initial state.
　　D. All subsequent ADDM tasks including my_instance_analysis_mode_task are filtered to suppress the Segment Advisor suggestions for the SCOTT schema.
　　Answer: C

　　Is in completed status but not set to its initial state.
　　227.You plan to control idle sessions that are blocking other sessions from performing transactions. Your
　　requirement is to automatically terminate these blocking sessions when they remain idle for a specified
　　amount of time.
　　How would you accomplish this task?
　　A. Set metric threshold
　　B. Implement Database Resource Manager
　　C. Enable resumable timeout for user sessions
　　D. Add directives to Automatic Database Diagnostic Monitor (ADDM)
　　Answer: B
　　228.Evaluate the following statements:
　　CREATE TABLE purchase_orders (po_id NUMBER(4),
　　po_date TIMESTAMP, supplier_id NUMBER(6), po_total NUMBER(8,2),
　　CONSTRAINT order_pk PRIMARY KEY(po_id)) PARTITION BY RANGE(po_date)
　　(PARTITION Q1 VALUES LESS THAN (TO_DATE(?1-apr-2007?d-mon-yyyy?), PARTITION Q2 VALUES
　　LESS THAN
　　(TO_DATE(?1-jul-2007?d-mon-yyyy?), PARTITION Q3 VALUES LESS THAN
　　(TO_DATE(?1-oct - 2007?d-mon-yyyy?), PARTITION Q4 VALUES LESS THAN
　　(TO_DATE(?1-jan-2008?d-mon-yyyy?));
　　CREATE TABLE purchase_order_items (po_id NUMBER(4) NOT NULL, product_id NUMBER(6) NOT
　　NULL, unit_price NUMBER(8,2), quantity NUMBER(8), CONSTRAINT po_items_fk FOREIGN KEY
　　(po_id) REFERENCES purchase_orders(po_id)) PARTITION BY
　　REFERENCE(po_items_fk);
　　What are the two consequences of the above statements? (Choose two.)
　　A. Partitions of PURCHASE_ORDER_ITEMS have system-generated names.
　　B. Both PURCHASE_ORDERS and PURCHASE_ORDER_ITEMS tables are created with four partitions each.
　　C. Partitions of the PURCHASE_ORDER_ITEMS table exist in the same tablespaces as the partitions of the PURCHASE_ORDERS table.
　　D. The PURCHASE_ORDER_ITEMS table inherits the partitioning key from the parent table by automatically duplicating the key columns.
　　E. Partition maintenance operations performed on the PURCHASE_ORDER_ITEMS table are automatically reflected in the PURCHASE_ORDERS table.
　　Answer: B, C
Creating Reference-Partitioned Tables
　　To create a reference-partitioned table, you specify a PARTITION BY REFERENCE clause in the CREATE TABLE statement. This clause specifies the name of a referential constraint and this constraint becomes the partitioning referential constraint that is used as the basis for reference partitioning in the table. The referential constraint must be enabled and enforced.
　　As with other partitioned tables, you can specify object-level default attributes, and you can optionally specify partition descriptors that override the object-level defaults on a per-partition basis.
　　If partition descriptors are provided, then the number of partitions described must exactly equal the number of partitions or subpartitions in the referenced table. If the parent table is a composite partitioned table, then the table has one partition for each subpartition of its parent; otherwise the table has one partition for each partition of its parent.
　　Partition bounds cannot be specified for the partitions of a reference-partitioned table.
　　The partitions of a reference-partitioned table can be named. If a partition is not explicitly named, then it inherits its name from the corresponding partition in the parent table, unless this inherited name conflicts with an existing explicit name. In this case, the partition has a system-generated name.
　　Partitions of a reference-partitioned table collocate with the corresponding partition of the parent table, if no explicit tablespace is specified for the reference-partitioned table's partition.
　　229.Which statements are true regarding SecureFile LOBs? (Choose all that apply.)
　　A. The amount of undo retained is user controlled.
　　B. SecureFile LOBs can be used only for nonpartitioned tables.
　　C. Fragmentation is minimized by using variable-sized chunks dynamically.
　　D. SecureFile encryption allows for random reads and writes of the encrypted data.
　　E. It automatically detects duplicate LOB data and conserves space by storing only one copy.
　　Answer: C, D
　　Oracle Database 11 g 将LOB 数据类型作为Oracle SecureFiles  进行了完全重新设计，显著改进了应用程序开发的性能、可管理性和易用性。新实施也提供了下一代高级功能，如智能压缩和透明加密。
使用SecureFiles  时，块大小介于Oracle  数据块大小到64 MB 之间。Oracle DB 尝试使数据集中在磁盘的相邻物理位置，从而将内部碎片降到最低。通过使用可变的块大小，SecureFiles  避免对不必要的大型LOB 数据块进行版本化。
SecureFiles  还提供了新的客户机/ 服务器网络层，从而允许在支持更高读写性能的服务器和客户机之间进行高速数据传输。SecureFiles  自动确定生成重做和还原的最有效方法，因而不需要用户定义参数。SecureFiles  自动确定是仅为更改生成重做和还原，还是通过生成完整的重做记录创建新版本。
由于SecureFiles  需要维护各种内存中统计信息以帮助有效地分配内存和空间，所以将其设计为可自适应的智能工具。这样，由于减少了很难使用不可预测的负荷进行优化的可优化参数数量，可管理性有所提高。
SecureFiles：高级功能
Oracle SecureFiles  实施也提供了下一代高级功能，如智能压缩和透明加密。压缩功能支持显式压缩SecureFiles 。SecureFiles  仅为随机读取或写入访问透明解压缩所需的数据块集，从而自动维护未压缩和已压缩的偏移量之间的映射。如果将压缩级别从MEDIUM 更改为HIGH，则映射会自动进行更新以反映新的压缩算法。取消重复可自动检测重复的SecureFile LOB  数据，并通过仅存储一个副本来节省空间-减少磁盘存储空间、I/O  和重做事件记录。可在表级别或分区级别指定取消重复，但不能跨越分区的LOB 。取消重复需要使用高级压缩选项。
现在可以在适当位置存储加密的LOB 数据，并对其进行随机读取和写入，因而提高了数据的安全性。只能按列加密SecureFile LOB  （与透明数据加密相同）。将使用相同的加密算法对LOB 列中的所有分区进行加密。无法对BasicFiles 数据进行加密。SecureFiles  支持行业标准加密算法：3DES168 、AES128、AES192（默认）和AES256。加密是高级安全选项的一部分。
注：必须将COMPATIBLE初始化参数设置为11.0.0.0.0 或更高，才能使用SecureFiles 。在11.1.0.0.0 兼容性下BasicFiles（以前的LOB）格式仍然受支持。设置11.0.0.0.0 后不会出现功能降低。
8.2.1 SecureFile LOBs and BasicFile LOBs
　　SecureFile LOBs (SecureFiles) were introduced in Oracle Database 11g Release 1 (11.1) to supplement the original BasicFile LOBs implementation that is identified by the SQL parameter BASICFILE. The performance of SecureFile LOBs is significantly better than that of BasicFile LOBs, especially for large media data. Oracle recommends using SecureFile LOBs for storing media data whenever possible. SecureFile LOBs are identified by specifying the SQL parameter SECUREFILE

8.2.2 TABLESPACE
　　You can achieve the best performance for LOBs by specifying storage for LOBs in a different tablespace than the one used for the table that contains the LOB. If many different LOBs are to be accessed frequently, you can also specify a separate tablespace for each LOB column or attribute to reduce device contention.

8.2.3 CACHE, NOCACHE, and CACHE READS
　　The cache option is a part of the STORE AS clause, and determines whether LOB pages are stored in the buffer cache.

• 　　When the option has the value CACHE, Oracle places LOB pages in the buffer cache where they can be shared among multiple users. Over time and if the LOB pages are no longer accessed, the pages are eventually removed from the buffer cache.
  
• 　　For the value NOCACHE, LOB pages are not placed in the buffer cache.
  
• 　　For the value CACHE READS, LOB pages are placed in the cache for read operations only.
  

　　If your application performs multiple read operations on a media object (for example: invoking the setProperties( ) method and then generating a thumbnail image), enable read caching for the source media object.

8.2.4 LOGGING and NOLOGGING
　　The logging option is a part of the STORE AS clause and determines if REDO data is logged when a LOB is updated. If the [NO]LOGGING clause is omitted, neither NOLOGGING nor LOGGING is specified and the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.
　　There is another alternative depending on how the cache option is specified.

• 　　If CACHE is specified and [NO]LOGGING is omitted, LOGGING is automatically implemented (because you cannot have CACHE NOLOGGING).
  
• 　　If CACHE is not specified and [NO]LOGGING is omitted, the [NO]LOGGING value is obtained from the tablespace in which the LOB segment resides.
  

　　Specify NOLOGGING only when you do not care about media recovery. However, if the disk, tape, or storage media fails, you will not be able to recover your changes from the log because those changes were not logged.
　　NOLOGGING can be useful for bulk loading of media data. For instance, when loading data into the LOB, if you do not care about the redo operation and you can start the load over if it fails, set the LOB data segment storage characteristics to NOCACHE NOLOGGING. This option provides good performance for the initial loading of data.
　　After you finish loading data, if necessary, you can use the ALTER TABLE statement to modify the LOB storage characteristics for the LOB data segment for normal LOB operations (for example: to CACHE or NOCACHE LOGGING).

Note:
Oracle Data Guard Redo Apply technology uses logging to populate the standby database. Thus, do not specify NOLOGGING with this Data Guard technology.
8.2.5 CHUNK
　　The CHUNK option applies only to BasicFile LOBs. It is part of the STORE AS clause, and indicates the size of the minimum unit of storage for the LOB data. CHUNK must be an integer multiple of the block size, and it must have a maximum value of 32K bytes.
　　Accessing LOBs in bigger chunks is more efficient. For the most efficient storage of media objects, which are almost always much larger than 32K, choose the maximum value of 32K.
　　231.You upgraded Oracle Database 10g to Oracle Database 11g. How would this affect the existing
　　users' passwords?
　　A. All passwords automatically become case-sensitive.
　　B. All passwords remain non-case-sensitive till they are changed.
　　C. All passwords remain non-case-sensitive and cannot be changed.
　　D. All passwords remain non-case-sensitive until their password attribute in the profile is altered.
　　Answer: B

　　238.View the Exhibit to examine the output for the V$DIAG_INFO view.
　　Which statements are true regarding the location of diagnostic traces? (Choose all that apply.)
　　
　　A. The path to the location of the background as well as the foreground process trace files is /u01/oracle/diag/rdbms/orclbi/orclbi/trace.
　　B. The location of the text alert log file is /u01/oracle/diag/rdbms/orclbi/orclbi/alert.
　　C. The location of the trace file for the current session is /u01/oracle/diag/rdbms/orclbi/orclbi/trace.
　　D. The location of the XML-formatted version of the alert log is /u01/oracle/diag/rdbms/orclbi/orclbi/alert.
　　Answer: A, C, D

　　241.You issued the following RMAN command to back up the database:
　　RMAN> RUN{
　　ALLOCATE CHANNEL c1 DEVICE TYPE sbt
　　BACKUP DATABASE
　　TAG quarterly
　　KEEP FOREVER
　　RESTORE POINT FY06Q4;}
　　Which two statements are true regarding the backup performed? (Choose two.)
　　A. Archived redo log files are backed up along with data files. --bacause of keep forever

　　B. Only data files are backed up and a restore point named FY06Q4 is created.
　　C. Archived log files are backed up along with data files, and the archived log files are deleted.
　　D. The command creates a restore point named FY06Q4 to match the SCN at which this backup is consistent.
　　Answer: A, D

　　246.The OPTIMIZER_USE_PLAN_BASELINES parameter is set to TRUE. The optimizer generates a plan for a SQL statement but does not find a matching plan in the SQL plan baseline.
　　Which two operations are performed by the optimizer in this scenario? (Choose two.)
　　A. The optimizer adds the new plan to the plan history.
　　B. The optimizer selects the new plan for the execution of the SQL statement.
　　C. The optimizer adds the new plan to the SQL plan baseline as an accepted plan.
　　D. The optimizer adds the new plan to the SQL plan baseline but not in the ENABLED state.
　　E. The optimizer costs each of the accepted plans in the SQL plan baseline and picks the one with the lowest cost.
　　Answer: A, E

　　248.You want the ability to recovery any time within the last seven days and therefore you configured the recovery window retention policy using the command:
　　RMAN> CONFIGURE RETENTIOH POLICY TO RECOVFRY WINDOW OF 7 DAYS;
　　After configuring the recovery window, you performed the database backup as follows:
　　A. Backup RB1 at log sequence number 12871 on 5th Jan
　　B. Backup RB2 at log sequence number 15622 on 12th Jan
　　C. Backup RB3 at log sequence 16721 on 15th Jan
　　D. On 20th Jan when the log sequence number was 18112 you realize that there Is a need to a point in time at the beginning of the recovery window. You have all an hived redo log files to date.
　　这道题的答案没理解到什么，不过知道CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;是什么意思就可以了。
　　Configuring a Recovery Window-Based Retention Policy
　　The RECOVERY WINDOW parameter of the CONFIGURE command specifies the number of days between the current time and the earliest point of recoverability. RMAN does not consider any full or level 0 incremental backup as obsolete if it falls within the recovery window. Additionally, RMAN retains all archived logs and level 1 incremental backups that are needed to recover to a random point within the window.
　　Run the CONFIGURE RETENTION POLICY command at the RMAN prompt. This example ensures that you can recover the database to any point within the last week:
　　CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
　　RMAN does not automatically delete backups rendered obsolete by the recovery window. Instead, RMAN shows them as OBSOLETE in the REPORT OBSOLETE output and in the OBSOLETE column of V$BACKUP_FILES. RMAN deletes obsolete files if you run the DELETE OBSOLETE command.
　　Answer: D

　　252.Which three are the valid statements in relation to SQL plan baselines? (Choose three.)
　　A. The plans can be manually loaded to the SQL plan baseline.
　　B. The plans in the SQL plan baseline are verified and accepted plans.
　　C. The plans generated for every SQL statement are stored in the SQL plan baseline by default.
　　D. The plan baselines are stored temporarily in the memory as long as the database instance is running.
　　E. For the SQL plan baselines to be accessible to the optimizer, the SYSAUX tablespace must be online.
　　Answer: A, B, E

　　253.You run the SQL Tuning Advisor (STA) to tune a SQL statement that is part of a fixed SQL plan baseline. The STA generates a SQL profile for the SQL statement, which recommends that you accept the profile.
　　Which statement is true when you accept the suggested SQL profile?
　　A. The tuned plan is not added to the SQL plan baseline.
　　B. The tuned plan is added to the fixed SQL plan baseline as a fixed plan.
　　C. The tuned plan is added to the fixed SQL plan baseline as a nonfixed plan.
　　D. The tuned plan is added to a new nonfixed SQL plan baseline as a nonfixed plan.
　　Answer: C
　　254.Which three statements correctly describe the features of the I/O calibration process? (Choose three.)
　　A. Only one I/O calibration process can run at a time.
　　B. It automates the resource allocation for the Automated Maintenance Tasks.
　　C. It improves the performance of the performance-critical sessions while running.
　　D. It can be used to estimate the maximum number of I/Os and maximum latency time for the system.
　　E. The latency time is computed only when the TIMED_STATISTICS initialization parameter is set to TRUE.
　　Answer: A, D, E
Running I/O Calibration
　　The I/O calibration feature of Oracle Database is accessed using the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. This procedure issues an I/O intensive read-only workload, made up of one megabyte of random of I/Os, to the database files to determine the maximum IOPS (I/O requests per second) and MBPS (megabytes of I/O per second) that can be sustained by the storage subsystem.
　　The I/O calibration occurs in two steps:

• 　　In the first step of I/O calibration with the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure, the procedure issues random database-block-sized reads, by default, 8 KB, to all data files from all database instances. This step provides the maximum IOPS, in the output parameter max_iops, that the database can sustain. The value max_iops is an important metric for OLTP databases. The output parameter actual_latency provides the average latency for this workload. When you need a specific target latency, you can specify the target latency with the input parameter max_latency (specifies the maximum tolerable latency in milliseconds for database-block-sized IO requests).
  
• 　　The second step of calibration using the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure issues random, 1 MB reads to all data files from all database instances. The second step yields the output parameter max_mbps, which specifies the maximum MBPS of I/O that the database can sustain. This step provides an important metric for data warehouses.
  

　　The calibration runs more efficiently if the user provides the num_physical_disks input parameter, which specifies the approximate number of physical disks in the database storage system.
　　Due to the overhead from running the I/O workload, I/O calibration should only be performed when the database is idle, or during off-peak hours, to minimize the impact of the I/O workload on the normal database workload.
　　255.Which two statements about the SQL Management Base (SMB) are true? (Choose two.)
　　A. It contains only SQL profiles generated by SQL Tuning Advisor.
　　B. It stores plans generated by the optimizer using a stored outline.
　　C. It is part of the data dictionary and stored in the SYSAUX tablespace.
　　D. It is part of the data dictionary and stored in the SYSTEM tablespace.
　　E. It contains the statement log, the plan history, plan baselines, and SQL profiles.
　　Answer: C, E
　　SQL Management Base
The SQL management base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles. To allow weekly purging of unused plans and logs, the SMB uses automatic space management.
You can also add plans manually to the SMB for a set of SQL statements. This feature is especially useful when upgrading the database from a version before Oracle Database 11g because it helps to minimize plan regressions resulting from the use of a new optimizer version.
Because the SMB is located entirely within SYSAUX, the database does not use SQL plan management and SQL tuning features when this tablespace is unavailable.
15.6.1 Disk Space Usage
Disk space used by the SMB is regularly checked against a limit based on the size of the SYSAUX tablespace. By default, the limit for the SMB is no more than 10% of the size of SYSAUX. The allowable range for this limit is between 1% and 50%.
A weekly background process measures the total space occupied by the SMB. When the defined limit is exceeded, the process writes a warning to the alert log. The database generates alerts weekly until one of the following conditions is met:
    The SMB space limit is increased
    The size of the SYSAUX tablespace is increased
    The disk space used by the SMB is decreased by purging SQL management objects (SQL plan baselines or SQL profiles)
To change the percentage limit, use the CONFIGURE procedure of the DBMS_SPM package. The following example changes the space limit to 30%:
BEGIN
  DBMS_SPM.CONFIGURE('space_budget_percent',30);
END;
/
15.6.2 Purging Policy
A weekly scheduled purging task manages the disk space used by SQL plan management. The task runs as an automated task in the maintenance window.
The database purges plans not used for more than 53 weeks, as identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. The 53-week period ensures plan information is available during any yearly SQL processing. The unused plan retention period can range between 5 and 523 weeks (a little more than 10 years).
To configure the retention period, use the CONFIGURE procedure of the DBMS_SPM PL/SQL package. The following example changes the retention period to 105 weeks:
BEGIN
  DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);
END;
/
15.6.3 SQL Management Base Configuration Parameters
You can access the current configuration settings for the SQL management base using the DBA_SQL_MANAGEMENT_CONFIG view.  
　　257.Which client requests to the database can be captured as a part of the workload capture? (Choose all that apply.)
　　A. flashback query
　　B. distributed transactions
　　C. logging in and logging out of sessions
　　D. all DDL statements having bind variables
　　E. direct path load of data from external files
　　Answer: C, D

　　Workload Capture Restrictions
Certain types of user sessions and client requests may sometimes be captured in a workload, but they are not supported by Database Replay. Capturing these session and request types in a workload may result in errors during workload replay.
The following types of user sessions and client requests are not supported by Database Replay:

•     Direct path load of data from external files using utilities such as SQL*Loader
  
•     Non-PL/SQL based Advanced Queuing (AQ)
  
•     Flashback queries
  
•     Oracle Call Interface (OCI) based object navigations
  
•     Non SQL-based object access
  
•     Distributed transactions
  
•     Any distributed transactions that are captured will be replayed as local transactions.
  
•     XA transactions
  
•     XA transactions are not captured or replayed. All local transactions are captured.
  
•     JAVA_XA transactions
  

　　    If the workload uses the JAVA_XA package, JAVA_XA function and procedure calls are captured as normal PL/SQL workload. To avoid problems during workload replay, consider dropping the JAVA_XA package on the replay system to enable the replay to complete successfully.

•     Database Resident Connection Pooling (DRCP)
  
•     Workloads using OUT binds
  
•     Multi-threaded Server (MTS) and shared server sessions with synchronization mode set to OBJECT_ID
  
•     Migrated sessions
  

　　    The workload is captured for migrated sessions. However, user logins or session migration operations are not captured. Without a valid user login or session migration, the replay may cause errors because the workload may be replayed by a wrong user.
Typically, Database Replay refrains from capturing these types of non-supported user sessions and client requests. Even when they are captured, Database Replay will not replay them. Therefore, it is usually not necessary to manually filter out non-supported user sessions and client requests. In cases where they are captured and found to cause errors during replay, consider using workload capture filters to exclude them from the workload.
　　258.Which two statements are true regarding the SQL Repair Advisor? (Choose two.)
　　A. The SQL Repair Advisor can be invoked to tune the performance of the regressed SQL statements.
　　B. The SQL Repair Advisor can be invoked even when the incident is not active for a SQL statement
　　crash.
　　C. The SQL Repair Advisor is invoked by the Health Monitor when it encounters the problematic SQL statement.
　　D. The DBA can invoke the SQL Repair Advisor when he or she receives an alert generated when a SQL statement crashes and an incident is created in the ADR.
　　Answer: B, D

　　264.Using the LIST command in Recovery Manager (RMAN), which two pieces of information in the
　　RMAN repository can be listed? (Choose two.)
　　A. stored scripts in the recovery catalog
　　B. backups that can be deleted from disk
　　C. backup sets and image copies are obsolete
　　D. backups that do not have the AVAILABLE status in the RMAN repository
　　官方参考：http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta027.htm#RCMRF132
　　排除法：BC所表达的意思都是list命令能列出在磁盘上能被删除的备份集等。即obsolete状态。而obsolete状态是用report obsolete列出的。
　　 Answer: A, D

　　266.View the Exhibit to examine the parameters set for your database instance. You execute the following command to perform I/O calibration after the declaration of bind variables in the session that are used in the command:
　　SQL> EXECUTE dbms_resource_manager.calibrate_io( num_physical_disks=>1, - max_latency=>50,
　　-max_iops=>:max_iops, -max_mbps=>:max_mbps, -
　　actual_latency=>:actual_latency);
　　Which statement describes the consequence?
　　
　　A. The command produces an error.
　　B. The calibration process runs successfully and populates all the bind variables.
　　C. The calibration process runs successfully but the latency time is not computed.
　　D. The calibration process runs successfully but only the latency time is computed.
　　Answer: A

　　在Oracle Database 11g Release 1中引入的过程CALIBRATE_IO从Oracle内部提供了获取存储系统性能的方法。该过程的使用存在一些限制。
　　该过程的调用需要SYSDBA权限。
　　TIMED_STATISTICS参数必须设置为TRUE，这是STATISTICS_LEVEL参数设置为TYPICAL时的默认值。
　　数据文件必须使用异步I/O进行访问，这是使用ASM时的默认设置。
　　Only users with the SYSDBA privilege can run this procedure. Qualified users must also turn on timed_statistics, and ensure asynch_io is enabled for datafiles. This can be achieved by setting filesystemio_options to either ASYNCH or SETALL. One can also query the asynch_io status by means of the following SQL statement:
　　col name format a50;
　　SELECT name, asynch_io FROM v$datafile f,v$iostat_file i
   WHERE f.file#        = i.file_no
   AND  filetype_name  = 'Data File'
  /
　　Only one calibration canbe run at a time. If another calibration is initiated at the same time, it willfail.
　　For an Oracle RealApplication Clusters (RAC) database, the workload is simultaneously generatedfrom all instances.
　　270.Which tasks are run automaticallyas part of the Automated Maintenance Task by default? (Choose
　　all that apply.)
　　A. Segment Advisor
　　B. SQL Access Advisor
　　C. Optimizer statistics gathering
　　D. Automatic SQL Tuning Advisor
　　E. Automatic Database DiagnosticsMonitor
　　Answer: A, C, D
　　271.Examine the output of the querythat you executed to list the objects in the recycle bin:
　　
　　You verified that no table namedSALES_TAB exists in the schema. Then you executed the following command topurge the objects in the recycle bin:
　　SQL> PURGE TABLE sales_tab;
　　What would be the outcome of thiscommand?
　　A. All three tables in the recycle binare purged
　　B. Only the table with the oldestDROPSCN is purged
　　C. The command returns an errorbecause multiple entries with the same name exist in the recycle bin
　　D. Only the table with the latestDROPSCN is purged
　　Answer: B

　　273.Which statements are trueregarding the system-defined moving window baseline in Oracle
　　Database 11g? (Choose all that apply.)
　　A. It does not allow you to change themoving window size.
　　B. Adaptive threshold functionalitiesuse it by default to compute statistics.
　　C. It is created by default with thewindow size being equal to the AWR retention time.
　　D. It is created when the firstsnapshot is collected by the Automatic Workload Repository (AWR).
　　Answer: B, C
Baselines
　　A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.
　　There are several types of available baselines in Oracle Database:

• 　　Fixed Baselines
  
• 　　Moving Window Baseline
  
• 　　Baseline Templates
  

5.2.2.1 Fixed Baselines
　　A fixed baseline corresponds to a fixed, contiguous time period in the past that you specify. Before creating a fixed baseline, carefully consider the time period you choose as a baseline, because the baseline should represent the system operating at an optimal level. In the future, you can compare the baseline with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time.

5.2.2.2 Moving Window Baseline
　　A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the database can use AWR data in the entire AWR retention period to compute metric threshold values.
　　Oracle Database automatically maintains a system-defined moving window baseline. The default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8 days. If you are planning to use adaptive thresholds, consider using a larger moving window—such as 30 days—to accurately compute threshold values. You can resize the moving window baseline by changing the number of days in the moving window to a value that is equal to or less than the number of days in the AWR retention period. Therefore, to increase the size of a moving window, you must first increase the AWR retention period accordingly.

5.2.2.3 Baseline Templates
　　You can also create baselines for a contiguous time period in the future using baseline templates. There are two types of baseline templates: single and repeating.
　　You can use a single baseline template to create a baseline for a single contiguous time period in the future. This technique is useful if you know beforehand of a time period that you intend to capture in the future. For example, you may want to capture the AWR data during a system test that is scheduled for the upcoming weekend. In this case, you can create a single baseline template to automatically capture the time period when the test occurs.
　　You can use a repeating baseline template to create and drop baselines based on a repeating time schedule. This is useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis. For example, you may want to capture the AWR data during every Monday morning for a month. In this case, you can create a repeating baseline template to automatically create baselines on a repeating schedule for every Monday, and automatically remove older baselines after a specified expiration interval, such as one month.
　　274.The SQL Tuning Advisor has beenconfigured with default configurations in your database instance.
　　Which recommendation is automaticallyimplemented without the DBA's intervention after the SQL Tuning
　　Advisor is run as part of the AUTOTASKframework?
　　A. statistics recommendations
　　B. SQL profile recommendations
　　C. index-related recommendations
　　D. restructuring of SQLrecommendations
　　Answer: B

　　279.Identify theactivities performed as part of the Automatic SQL Tuning process in themaintenance window? (Choose all that apply.)
　　A. generating the SQLprofile
　　B. testing and acceptingthe SQL profile
　　C. generating a list ofcandidate SQLs for tuning
　　D. adding tuned SQL plansinto the SQL plan baseline
　　E. tuning each SQLstatement in the order of importance
　　F. generating baselinesthat include candidate SQLs for tuning
　　Answer: A, B, C, E
　　Has nothing to do with baseline...
　　
　　282.You are managing anOracle 11g database with ASM storage, for which the COMPATIBLE
　　initialization parameteris set to 11.1.0. In the ASM instance, the COMPATIBLE.RDBMS attribute for thedisk group is set to 10.2 and the COMPATIBLE.ASM attribute is set to 11.1.
　　Which two statements aretrue in this scenario for the features enabled for ASM? (Choose two.)
　　A. The ASM-preferredmirror read feature is enabled.
　　B. The ASM supportsvariable sizes for extents of 1, 8, and 64 allocation units.
　　C. The ASM disk is droppedimmediately from a disk group when it becomes unavailable.
　　D. The RDBMS always readsthe primary copy of a mirrored extent of the ASM disk group.
　　Answer: A, B

　　COMPATIBLE.RDBMS attribute is only 10.2, so D option is not correct.

　　289.You are managing theAPPPROD database as a DBA which is not using the Oracle-managed files.
　　You plan to duplicate thisdatabase in the same system with the name DUPDB.You want to create the
　　same directory structurefor duplicate database files as of the target database.
　　You executed the followingRMAN commands:
　　RMAN> CONNECT TARGET sys/sys@APPPROD
　　RMAN> CONNECT AUXILIARYsys/sys@DUPDB
　　RMAN> DUPLICATE TARGETDATABASE
　　TO dupdb
　　FROM ACTIVE DATABASE
　　PASSWORD FILE
　　SPILE
　　NOFILENAMECHECK;
　　What are the implicationsof this command?
　　A. It creates databasefiles for the duplicate database under the Oracle base with a differentdirectory for the duplicate database.
　　B. It overwrites datafiles of the target database because a different location for data files is notmentioned for the duplicate database.
　　C. It creates databasefiles for the duplicate database under the same Oracle home as that of thetarget database with the same directory structure.
　　D. It creates databasefiles for the duplicate database under the same Oracle home as that of thetarget but with a different directory for the duplicate database.
　　Answer: B
　　290.What are therecommendations for Oracle Database 11g installation to make it OptimalFlexible
　　Architecture(OFA)-compliant? (Choose all that apply.)
　　A. ORACLE_BASE should beset explicitly.
　　B. An Oracle base shouldhave only one Oracle home created in it.
　　C. Flash recovery area anddata file location should be on separate disks.
　　D. Flash recovery area anddata file location should be created under Oracle base in a non-Automatic StorageManagement (ASM) setup.
　　Answer: A, C, D

　　OFA can't be in a ASM setup...
　　291.ENCRYPT_TS is an encryptedtablespace that contains tables with data. Which statement is true regardingthe effect of queries and data manipulation language (DML) statements on theencrypted data in the tables?
　　A. The data is decrypted during SORTand JOIN operations.
　　B. The data remains encrypted when itis read into memory.
　　C. The data remains encrypted when itis stored in the redo logs.
　　D. The data remains encrypted in theUNDO tablespace provided that the UNDO tablespace was created with theencryption option enabled.
　　Answer: C
　　292.You decided to use Direct NFSconfiguration in a non-RAC Oracle installation and created the
　　oranfstab file in /etc. Which twostatements are true regarding this oranfstab file? (Choose two.)
　　A. Its entries are specific to asingle database.
　　B. It contains file systems that havebeen mounted by Direct NFS.
　　C. It is globally available to allOracle 11g databases on the machine.
　　D. It contains file systems that havebeen mounted by the kernel NFS system.
　　Answer: C, D

　　Using the oranfstab File with Direct NFS Client
If you use Direct NFS Client, then you can choose to use a new file specific for Oracle data file management, oranfstab, to specify additional options specific for Oracle Database to Direct NFS Client. For example, you can use oranfstab to specify additional paths for a mount point. You can add the oranfstab file either to /etc or to $ORACLE_HOME/dbs.
With shared Oracle homes, when the oranfstab file is placed in $ORACLE_HOME/dbs, the entries in the file are specific to a single database. In this case, all nodes running an Oracle RAC database use the same $ORACLE_HOME/dbs/oranfstab file. In non-shared Oracle RAC installs, oranfstab must be replicated on all nodes.
When the oranfstab file is placed in /etc, then it is globally available to all Oracle databases, and can contain mount points used by all Oracle databases running on nodes in the cluster, including standalone databases. However, on Oracle RAC systems, if the oranfstab file is placed in /etc, then you must replicate the file /etc/oranfstab file on all nodes, and keep each /etc/oranfstab file synchronized on all nodes, just as you must with the /etc/fstab file.
In all cases, mount points must be mounted by the kernel NFS system, even when they are being served using Direct NFS Client.
　　295.Which statement about using RMANstored scripts is true?
　　A. To create and execute an RMANstored script, you must use a recovery catalog.
　　B. When executing a stored script anda command fails, the remainder of the script is executed, and a message iswritten to the alert log file.
　　C. RMAN stored scripts can always beexecuted against any target database that is registered in the
　　recovery catalog.
　　D. When you execute a stored script,it always executes using the persistent channel settings previously set withthe CONFIGURE command.
　　Answer: A

　　297.View the Exhibit forsome of the parameter settings. You start a session and issue the following
　　command:
　　SQL>CREATE INDEXemp_ename ON emp(ename)
　　TABLESPACE usersINVISIBLE;
　　What is the outcome of theabove command?
　　
　　A. The index is not usedby the optimizer but is maintained during DML operations.
　　B. The index is not usedby the optimizer and is not maintained during DML operations.
　　C. The index is used bythe optimizer only if a hint is specified in the query statement and ismaintained during DML operations.
　　D. The index is used bythe optimizer only if a hint is specified in the query statement but is notmaintained during DML operations.
　　Answer: A
　　The index is invisible and the parameter of optimizer_use_invisible_indexes is false.
　　298.While tuning a SQLstatement, the SQL Tuning Advisor finds an existing SQL profile for thestatement that has stale statistics available.
　　What would the optimizerdo in this situation?
　　A. It updates the existingSQL profiles with current statistics.
　　B. It makes the statisticsinformation available to GATHER_STATS_JOB.
　　C. It initiates thestatistics collection process by running GATHER_STATS_JOB.
　　D. It logs a warningmessage in the alert log so that the DBA can perform statistics collectionmanually.
　　Answer: B