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.csdn.net/rlhua/article/details/16574625
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).
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_IOprocedure, 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 parametermax_iops, that the database can sustain. The valuemax_iopsis an important metric for OLTP databases. The output parameteractual_latencyprovides the average latency for this workload. When you need a specific target latency, you can specify the target latency with the input parametermax_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_IOprocedure issues random, 1 MB reads to all data files from all database instances. The second step yields the output parametermax_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 can be run at a time. If another calibration is initiated at the same time, it will fail.
For an Oracle Real Application Clusters (RAC) database, the workload is simultaneously generated from all instances.
270.Which tasks are run automatically as 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 Diagnostics Monitor
Answer: A, C, D
271.Examine the output of the query that you executed to list the objects in the recycle bin:
You verified that no table named SALES_TAB exists in the schema. Then you executed the following command to purge the objects in the recycle bin:
SQL> PURGE TABLE sales_tab;
What would be the outcome of this command?
A. All three tables in the recycle bin are purged
B. Only the table with the oldest DROPSCN is purged
C. The command returns an error because multiple entries with the same name exist in the recycle bin
D. Only the table with the latest DROPSCN is purged
Answer: B
273.Which statements are true regarding the system-defined moving window baseline in Oracle
Database 11g? (Choose all that apply.)
A. It does not allow you to change the moving window size.
B. Adaptive threshold functionalities use it by default to compute statistics.
C. It is created by default with the window size being equal to the AWR retention time.
D. It is created when the first snapshot 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:
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 been configured with default configurations in your database instance.
Which recommendation is automatically implemented without the DBA's intervention after the SQL Tuning
Advisor is run as part of the AUTOTASK framework?
A. statistics recommendations
B. SQL profile recommendations
C. index-related recommendations
D. restructuring of SQL recommendations
Answer: B
279.Identify the activities performed as part of the Automatic SQL Tuning process in the maintenance window? (Choose all that apply.)
A. generating the SQL profile
B. testing and accepting the SQL profile
C. generating a list of candidate SQLs for tuning
D. adding tuned SQL plans into the SQL plan baseline
E. tuning each SQL statement in the order of importance
F. generating baselines that include candidate SQLs for tuning
Answer: A, B, C, E
Has nothing to do with baseline...
282.You are managing an Oracle 11g database with ASM storage, for which the COMPATIBLE
initialization parameter is set to 11.1.0. In the ASM instance, the COMPATIBLE.RDBMS attribute for the disk group is set to 10.2 and the COMPATIBLE.ASM attribute is set to 11.1.
Which two statements are true in this scenario for the features enabled for ASM? (Choose two.)
A. The ASM-preferred mirror read feature is enabled.
B. The ASM supports variable sizes for extents of 1, 8, and 64 allocation units.
C. The ASM disk is dropped immediately from a disk group when it becomes unavailable.
D. The RDBMS always reads the 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 the APPPROD database as a DBA which is not using the Oracle-managed files.
You plan to duplicate this database in the same system with the name DUPDB.You want to create the
same directory structure for duplicate database files as of the target database.
You executed the following RMAN commands:
RMAN> CONNECT TARGET sys/sys@APPPROD
RMAN> CONNECT AUXILIARY sys/sys@DUPDB
RMAN> DUPLICATE TARGET DATABASE
TO dupdb
FROM ACTIVE DATABASE
PASSWORD FILE
SPILE
NOFILENAMECHECK;
What are the implications of this command?
A. It creates database files for the duplicate database under the Oracle base with a different directory for the duplicate database.
B. It overwrites data files of the target database because a different location for data files is not mentioned for the duplicate database.
C. It creates database files for the duplicate database under the same Oracle home as that of the target database with the same directory structure.
D. It creates database files for the duplicate database under the same Oracle home as that of the target but with a different directory for the duplicate database.
Answer: B
290.What are the recommendations for Oracle Database 11g installation to make it Optimal Flexible
Architecture (OFA)-compliant? (Choose all that apply.)
A. ORACLE_BASE should be set explicitly.
B. An Oracle base should have only one Oracle home created in it.
C. Flash recovery area and data file location should be on separate disks.
D. Flash recovery area and data file location should be created under Oracle base in a non-Automatic Storage Management (ASM) setup.
Answer: A, C, D
OFA can't be in a ASM setup...
291.ENCRYPT_TS is an encrypted tablespace that contains tables with data. Which statement is true regarding the effect of queries and data manipulation language (DML) statements on the encrypted data in the tables?
A. The data is decrypted during SORT and JOIN operations.
B. The data remains encrypted when it is read into memory.
C. The data remains encrypted when it is stored in the redo logs.
D. The data remains encrypted in the UNDO tablespace provided that the UNDO tablespace was created with the encryption option enabled.
Answer: C
292.You decided to use Direct NFS configuration in a non-RAC Oracle installation and created the
oranfstab file in /etc. Which two statements are true regarding this oranfstab file? (Choose two.)
A. Its entries are specific to a single database.
B. It contains file systems that have been mounted by Direct NFS.
C. It is globally available to all Oracle 11g databases on the machine.
D. It contains file systems that have been 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 RMAN stored scripts is true?
A. To create and execute an RMAN stored script, you must use a recovery catalog.
B. When executing a stored script and a command fails, the remainder of the script is executed, and a message is written to the alert log file.
C. RMAN stored scripts can always be executed 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 with the CONFIGURE command.
Answer: A
297.View the Exhibit for some of the parameter settings. You start a session and issue the following
command:
SQL>CREATE INDEX emp_ename ON emp(ename)
TABLESPACE users INVISIBLE;
What is the outcome of the above command?
A. The index is not used by the optimizer but is maintained during DML operations.
B. The index is not used by the optimizer and is not maintained during DML operations.
C. The index is used by the optimizer only if a hint is specified in the query statement and is maintained during DML operations.
D. The index is used by the optimizer only if a hint is specified in the query statement but is not maintained during DML operations.
Answer: A
The index is invisible and the parameter of optimizer_use_invisible_indexes is false.
298.While tuning a SQL statement, the SQL Tuning Advisor finds an existing SQL profile for the statement that has stale statistics available.
What would the optimizer do in this situation?
A. It updates the existing SQL profiles with current statistics.
B. It makes the statistics information available to GATHER_STATS_JOB.
C. It initiates the statistics collection process by running GATHER_STATS_JOB.
D. It logs a warning message in the alert log so that the DBA can perform statistics collection manually.
Answer: B
