ITPub博客

首页 > 数据库 > Oracle > 1Z0-050题库整理206道

1Z0-050题库整理206道

Oracle 作者:startforever 时间:2014-10-21 18:01:43 0 删除 编辑
通过了050考试,将此前整理的题库整理如下,考试中有两三道题不在题库中;
如需考试,将题库看熟,应该没问题;
本题库中的各题解释大部分查询自网络上,感谢各位网友分享。
这里尤为感谢  http://blog.csdn.net/rlhua   ,该网友资料整理的很全。

QUESTION 1

Evaluate the following command:

SQL>ALTER SYSTEM SET db_securefile = 'IGNORE';

What is the impact of this setting on the usage of SecureFiles?

A. It forces SecureFiles to be created even if the BASICFILE option is specified to create the LOB.

B. It forces BasicFiles to be created even if the SECUREFILE option is specified to create the LOB.

C. It does not allow the creation of SecureFiles and generates an error if the SECUREFILE koption is specified to create the LOB.

D. It ignores the SECUREFILE option only if a Manual Segment Space Management tablespace is used and creates a BasicFile.

 

Answer: B

Explanation/Reference:

  SecureFile功能是oracle 11g中对大对象(LOB)存储格式的完全重新设计实现,原来的LOB存储格式现在通称为BASICFILE,它仍然是默认的存储方法,但是SECURFILE关键字开启了新的存储方法,它允许加密、利用压缩节约空间和数据重复消除。

初始化参数

SecureFile功能在初始化参数COMPATIBLE设置11.0.0.0.0或更高时可用。

  DB_SECUREFILE初始化参数控制数据库对LOB存储格式的默认行为,允许的值有:

  ◆ALWAYS - ASSM表空间中的所有LOB对象以SecureFile LOB的格式创建,在非ASSM表空间中的所有LOB对象以BasicFile LOB的格式创建(除非明确地指出要以SecureFile格式创建),在没有指定选项的情况下,BasicFile存储格式选项被忽略,SecureFile默认存储格式选项被使用。

  ◆ FORCE - 所有LOB对象都以SecureFile LOB格式创建,如果是在一个非ASSM表空间中创建LOB,会出现错误,在没有指定选项的情况下,BasicFile存储格式选项被忽略,SecureFile默认存储格式选项被使用。

  ◆PERMITTED - 默认设置,当使用了SECUREFILE关键字时它允许SecureFile LOB存储格式,默认存储方法是BASICFILE

  ◆NEVER - 不允许创建SecureFile LOB对象。

  ◆IGNORE - 防止创建SecureFile LOB,使用SecureFile存储选项时忽略所有错误。

  这个参数是动态的,因此它可以使用ALTER SYSTEM命令设置。

 

例子:

SQL> ALTER SYSTEM SET db_securefile = 'FORCE';

SQL> ALTER SYSTEM SET db_securefile = 'PERMITTED';

  

  下面的例子假设DB_SECUREFILE初始化参数设置为默认值PERMITTED

  创建SecureFile LOB

  基础

  SecureFile LOB通过在LOB存储子句后添加SECUREFILE关键字来创建,下面的代码显示创建了两个表,第一个使用的是原来的存储格式,第二个使用的是SecureFile存储格式。

  CREATE TABLE bf_tab (

  id NUMBER,

  clob_data CLOB

  )

  LOB(clob_data) STORE AS BASICFILE;

  INSERT INTO bf_tab VALUES (1, 'My CLOB data');

  COMMIT;

  CREATE TABLE sf_tab (

  id NUMBER,

  clob_data CLOB

  )

  LOB(clob_data) STORE AS SECUREFILE;

  INSERT INTO sf_tab VALUES (1, 'My CLOB data');

  COMMIT;

 

 

LOB重复消除

  SecureFileDEDUPLICATE选项允许在表或分区一级上的一个LOB内消除重复数据,正如你预料的那样,这个技术与预防重写导致系统开销增大,KEEP_DUPLICATE选项明确地阻止重复消除,下面的例子对比了普通的SecureFile和重复消除SecureFile的空间使用情况。

  CREATE TABLE keep_duplicates_tab (

  id NUMBER,

  clob_data CLOB

  )

  LOB(clob_data) STORE AS SECUREFILE keepdup_lob(

  KEEP_DUPLICATES

  );

  CREATE TABLE deduplicate_tab (

  id NUMBER,

  clob_data CLOB

  )

  LOB(clob_data) STORE AS SECUREFILE dedup_lob (

  DEDUPLICATE

  );

  DECLARE

  l_clob CLOB := RPAD('X', 10000, 'X');

  BEGIN

  FOR i IN 1 .. 1000 LOOP

  INSERT INTO keep_duplicates_tab VALUES (i, l_clob);

  END LOOP;

  COMMIT;

  FOR i IN 1 .. 1000 LOOP

  INSERT INTO deduplicate_tab VALUES (i, l_clob);

  END LOOP;

  COMMIT;

  END;

  /

  EXEC DBMS_STATS.gather_table_stats(USER, 'keep_duplicates_tab');

  EXEC DBMS_STATS.gather_table_stats(USER, 'deduplicate_tab');

  COLUMN segment_name FORMAT A30

  SELECT segment_name, bytes

  FROM user_segments

  WHERE segment_name IN ('KEEPDUP_LOB', 'DEDUP_LOB');

  SEGMENT_NAME BYTES

  ------------------------------ ----------

  DEDUP_LOB 262144

  KEEPDUP_LOB 19267584

  2 rows selected.

  SQL>

 

 

  注意重复消除段要小很多,空间节约依赖于LOB段内的重复程度,重复模式可以使用ALTER TABLE命令进行重新设置。

keep_duplicates  不消除重复,deduplicate 消除重复

LOB_deduplicate_clause This clause is valid only for SecureFiles LOBs. KEEP_DUPLICATES disables LOB deduplication. DEDUPLICATE enables LOB deduplication. All lobs in the segment are read, and any matching LOBs are deduplicated before returning.

 

QUESTION 2

Which two statements are true regarding the functionality of the remap command in ASMCMD? (Choose two.)

A. It reads the blocks from a good copy of an ASM mirror and rewrites them to an alternate location on disk if the blocks on the original location cannot be read properly.

B. It repairs blocks by always reading them from the mirror copy and writing them to the original location.

C. It repairs blocks that have read disk I/O errors.

D. It checks whether the alias metadata directory and the file directory are linked correctly.

Answer: AC

Explanation/Reference:

 

remap

Purpose

Marks a range of blocks as unusable on the disk and relocates any data allocated in that range.

Syntax and Description

remap diskgroup disk block_range

Table 12-45 lists the syntax options for the remap command.

Table 12-45 Options for the remap command

Option

Description

diskgroup

Disk group name in which a disk must have data relocated.

disk

Name of the disk that must have data relocated. The name must match the NAME column in the V$ASM_DISK view.

block_range

Range of physical blocks to relocate in the format start_range_number-end_range_number.

 

The remap command only relocates blocks. It does not correct or repair blocks that contain corrupted contents. The command uses a physical block size based on the SECTOR_SIZE disk group attribute.

Examples

The first example remaps blocks 5000 through 5999 for disk DATA_0001 in disk group DATA. The second example remaps blocks 6230 through 6339 for disk FRA_0002 in disk group FRA

Example 12-49 Using the ASMCMD remap command

ASMCMD [+] > remap DATA DATA_0001 5000-5999
 
ASMCMD [+] > remap FRA FRA_0002 6230-6339

 

 

 

 

 

 

 

 

QUESTION 3

Which tasks can be accomplished using the DBMS_LOB.SETOPTIONS procedure?

A. only encryption and deduplication settings for only SecureFile CLOBs

B. only encryption and compression settings for all SecureFile LOBs

C. deduplication, encryption, and compression settings for all SecureFile LOBs

D. deduplication, encryption, and compression settings only for SecureFile CLOBs

Answer: C

Explanation/Reference:

 

http://blog.csdn.net/jgmydsai/article/details/36008669

deduplication, encryption, and compression 仅能用于SecureFile 

即能用于blob 又可用于clob

SETOPTIONS Procedures

This procedure enables/disables CSCE features on a per-LOB basis, overriding the default LOB column settings.

Syntax

DBMS_LOB.SETOPTIONS (
   lob_loc             IN     BLOB,
   option_types        IN     PLS_INTEGER,
   options             IN     PLS_INTEGER);
 
DBMS_LOB.SETOPTIONS (
  lob_loc             IN     CLOB CHARACTER SET ANY_CS,
  option_types        IN     PLS_INTEGER, 
  options             IN     PLS_INTEGER);

Parameters

Table 69-69 SETOPTIONS Procedure Parameter

Parameter

Description

lob_loc

Locator for the LOB to be examined. For more information, see Operational Notes.

option_type

See DBMS_LOB Option Types

options

See DBMS_LOB Option Values



Table 69-2 DBMS_LOB Option Types

Constant

Definition

OPT_COMPRESS

CONSTANT BINARY_INTEGER := 1;

OPT_ENCRYPT

CONSTANT BINARY_INTEGER := 2;

OPT_DEDUPLICATE

CONSTANT BINARY_INTEGER := 4;

 

 

ALTER TABLE Usage Notes for Deduplication

ALTER TABLE syntax enables or disables LOB-level deduplication.

·         This syntax alters the deduplication mode of the LOB column.

·         Deduplication on existing LOBs can add significant latency.

·         DBMS_LOB.SETOPTIONS can be used to enable or disable deduplication on individual LOBs.

·         Deduplication can be specified at a table level or partition level. Deduplication does not span across partitioned LOBs.

·         Deduplication is applicable only to SecureFiles.

 

ALTER TABLE Usage Notes for Compression

·         This syntax alters the compression mode of the LOB column.

·         Compression on existing LOBs can add significant latency.

·         DBMS_LOB.SETOPTIONS can be used to enable or disable compression on individual LOBs.

·         Compression can be specified at a table level or partition level.

·         MEDIUM, and HIGH options provide varying degrees of compression. The higher the compression, the higher the latency incurred. The HIGH setting incurs more work, but will compress the data better. The default is MEDIUM.

·         LOB compression is applicable only to SecureFiles.

ALTER TABLE Usage Notes for Encryption

ALTER TABLE is used to enable and disable LOB encryption for SECUREFILE LOBs. This syntax also enables LOB columns to be re-keyed with a new key or algorithm.

·         ENCRYPT/DECRYPT options enable or disable encryption on all LOBs in the SecureFile column.

·         SALT is the default for LOB encryption. NO SALT is not supported.

·         The DECRYPT option converts encrypted columns to its cleartext form.

·         Key management controls the ability to encrypt or decrypt.

·         LOBs can be encrypted only on a per-column basis. A partitioned LOB has either all partitions encrypted or unencrypted.

·         LOB encryption is applicable only to SecureFiles.

 

 

QUESTION 4

Which statement is true regarding virtual private catalogs?

A. A virtual private catalog owner can create a local stored script, and have read/write access to a global stored script.

B. The virtual private catalog owner cannot create and modify the stored scripts.

C. The set of views and synonyms that make up the virtual private catalog is stored in the schema of the RMAN

recovery catalog owner.

D. To perform most of the RMAN operations, the virtual catalog owner must have the SYSDBA or SYSOPER privilege on the target database.

Answer: D

Explanation/Reference:

 

http://blog.csdn.net/rlhua/article/details/13169297

参考:http://blog.csdn.net/rlhua/article/details/13169205

作为虚拟目录所有者,你只能查看授予了你访问权限的数据库。

注:如果目录所有者未被授予对目标数据库的SYSDBASYSOPER权限,则无法执行大多数RMAN 操作。

D选项:要执行大多数RMAN操作,虚拟目录的所有者必须对目标数据库具有SYSDBASYSOPER权限。正确。

 

 

 

Every virtual private catalog has access to all global stored scripts and those non-global stored scripts that belong to those databases for which this virtual private catalog has privileges. Virtual private catalogs cannot access non-global stored scripts that belong to databases that they do not have privileges for, and they cannot create global stored scripts.

 

 

QUESTION 5

Evaluate the following command:

SQL> CREATE TABLE design_data (id NUMBER, doc CLOB)

LOB(doc) STORE AS SECUREFILE(DEDUPLICATE);

Which statement is true regarding the above command?

A. All LOB data that is identical in two or more rows in a LOB column share the same data blocks.

B. The LOB values are cached by default in the buffer cache.

C. The LOB values are automatically stored in encrypted mode.

D. The LOB values are automatically compressed.

Answer: A

Explanation/Reference:

 

参考定义,securefile buffer cache,加密,压缩等都需要参数指定;去重复,就是将几行重复内容放在一行里;

 

 

 

 

QUESTION 6

Which statements are true regarding the Query Result Cache? (Choose all that apply.)

A. It can store the results from normal as well as flashback queries.

B. It can be set at the system, session, or table level.

C. It is used only across statements in the same session.

D. It can store the results of queries based on normal, temporary, and dictionary tables.

Answer: AB

Explanation/Reference:

 

 

Result cache is disabled for queries containing:

  Temporary or Dictionary tables

  Nondeterministic PL/SQL functions 

  Sequence

CURRVAL and NEXTVALSQL

functions

CURRENT_DATE,SYSDATE,SYS_GUID,

and

so

on

DDL/DML on remote database does not expire cached results

Flashback queries can be cached

 

Result Cache does not automatically release memory

  It grows until maximum size is reached

  DBMS_RESULT_CACHE.FLUSH purges memory

 

Bind variables

   Cached result is parameterized with variable values 

 

   Cached results can only be found for the same variable values

Cached result will not be build if:

   Query is build on a noncurrent version of data(read consistency enforcement)

   Current session has outstanding transaction on tables in query 

 

 

 

QUESTION 7

You are working on a CATDB database that contains an Oracle Database version 11.1 catalog schema owned by the

user RCO11. The INST1 database contains an Oracle Database version 10.1 catalog schema owned by the user

RCAT10. You want the RMAN to import metadata for database IDs 1423241 and 1423242, registered in RCAT10, into

the recovery catalog owned by RCO11.

You executed the following RMAN commands:

RMAN> CONNECT CATALOG rco11/password@catdb

RMAN> IMPORT CATALOG rcat10/oracle@inst1;

What happens when you execute the above commands? (Choose all that apply.)

A. They import metadata for all registered databases in the RCAT10 database.

B. They register all the RCAT10-catalog registered databases in the RCO11 catalog.

C. They overwrite all stored scripts in the RCO11 catalog with the same name as that in the RCAT10 catalog.

D. They deregister all databases registered in the RCAT10 catalog.

Answer: ABD

Explanation/Reference:

 

 

使用IMPORT CATALOG命令可将元数据从一个恢复目录方案导入至其它目录方案中。如果创建了不同版本的目录方案来存储多个目标数据库的元数据,则使用此命令可以为所有数据库维护单个目录方案。

IMPORT CATALOG

[DBID = [, ,…]]

[DB_NAME=[,

[ NO UNREGISTER ];

是源恢复目录连接字符串。源恢复目录方案的版本必须等于RMAN 可执行文件的当前版本。如果需要,将源目录升级到当前RMAN 本版。

DBID:你可以指定数据库ID 的列表,数据库ID 的元数据应从源目录方案导入。未指定列表时,RMAN 将所有数据库ID 的元数据从源目录方案合并到目标目录方案中。如果已在恢复目录方案中注册了合并元数据的数据库,RMAN 就会发出错误消息。

DB_NAME:可以指定应导入其元数据的数据库的名称列表。如果数据库名称不明确,RMAN 就会发出错误消息。

NO UNREGISTER默认情况下,导入的数据库ID 在成功导入后从源恢复目录方案中注销。使用NO UNREGISTER选项,可以强制RMAN 将导入的数据库ID 保留在源目录方案中。

RMAN> CONNECT CATALOG rco11/password@catdb

RMAN> IMPORT CATALOG rcat10/oracle@inst1 NO UNREGISTER;

RMAN 将数据库inst1的元数据导入到catdb 数据库中的rco11方案。而NO UNREGISTER说明,在rcat10方案中注册的数据库将不会注销,继续注册在rcat10用户目录中。

 

 

 

 

QUESTION 8

You are working on a CATDB database that contains an Oracle Database version 11.1 catalog schema owned by the

user RCO11. The INST1 database contains an Oracle Database version 10.1 catalog schema owned by the user

RCAT10. You want the RMAN to import metadata for database IDs 1423241 and 1423242, registered in RCAT10, into

the recovery catalog owned by RCO11. You executed the following commands:

RMAN> CONNECT CATALOG rco11/password@catdb

RMAN> IMPORT CATALOG rcat10/oracle@inst1 NO UNREGISTER; Which two statements are true regarding the

tasks accomplished with these commands? (Choose two.)

A. They register all databases registered in the RCAT10 catalog.

B. They import all metadata from the RCAT10 catalog.

C. They do not register the databases registered in the RCAT10 catalog.

D. They unregister the database from the RCAT10 catalog.

Answer: AB

Explanation/Reference:

参考上题

 

 

QUESTION 9

You are managing an Oracle Database 11g instance and an Oracle Database 10g instance on the same machine. Both instances use the ASM instance as storage. Which statements regarding the ASM disk group compatibility attributes are true in this scenario? (Choose all that apply.)

A. The database-compatibility version settings for each instance must be greater than or equal to the RDBMS

compatibility of all ASM disk groups used by that database instances.

B. RDBMS compatibility and the database version determines whether a database instance can mount the ASM disk

group.

C. The RDBMS compatibility settings for a disk group control the format of data structures for ASM metadata on the

disk.

D. ASM compatibility controls which features for the ASM will be enabled.

Answer: ABD

Explanation/Reference:

http://blog.csdn.net/rlhua/article/details/14123991

参考:http://blog.csdn.net/rlhua/article/details/12972983

适用于ASM 磁盘组的兼容性有三种:涉及描述磁盘组的持久性数据结构,客户机(磁盘组的使用者)的功能,以及能否在磁盘组中包含卷。这些属性分别称为“ASM 兼容性“RDBMS 兼容性“ADVM 兼容性。每个磁盘组的兼容性都可以独立控制。这是支持包含Oracle Database 10gOracle Database 11g磁盘组的异构环境所必需的。

这三种兼容性设置是每个ASM 磁盘组的属性:

?RDBMS 兼容性是指RDBMS 实例的最低兼容版本,此设置将允许该实例装载磁盘组。

该兼容性确定了ASM 实例与数据库(RDBMS) 实例间交换消息的格式。ASM 实例可以支持以不同兼容性设置运行的不同RDBMS 客户机。每个实例的数据库兼容版本设置必须高于或等于该数据库使用的所有磁盘组的RDBMS 兼容性。数据库实例与ASM 实例通常在不同的Oracle 主目录中运行。这意味着数据库实例可以运行与ASM 实例不同的软件版本。数据库实例第一次连接到ASM 实例时,系统会协定这两个实例都支持的最高版本。

数据库的兼容性参数设置、数据库的软件版本以及磁盘组的RDBMS 兼容性设置确定了数据库实例能否装载给定的磁盘组。

 

?ASM 兼容性是指控制磁盘上ASM 元数据的数据结构格式的持久性兼容性设置。

磁盘组的ASM 兼容性级别必须始终高于或等于同一磁盘组的RDBMS 兼容性级别。

ASM 兼容性只与ASM 元数据的格式相关。文件内容的格式取决于数据库实例。例如,可以将某个磁盘组的ASM 兼容性设置为11.0,而将该磁盘组的RDBMS 兼容性设置为10.1。这意味着该磁盘组只能由软件版本为11.0 或更高的ASM 软件管理,而软件版本高于或等于10.1 的任何数据库客户机都可以使用该磁盘组。

 

? ADVM 兼容性属性确定磁盘组能否包含Oracle ASM 卷。该值必须设置为11.2 或更高。设置该属性前,必须确保COMPATIBLE.ASM的值为11.2 或更高。此外,还必须加载ADVM 卷驱动程序。

仅当持久性磁盘结构或消息传送协议发生更改时,才需要提高磁盘组的兼容性。但是,提高磁盘组兼容性是一个不可逆的操作。可以使用CREATE DISKGROUP命令或ALTER DISKGROUP命令来设置磁盘组兼容性。

注:除了磁盘组兼容性,兼容参数(数据库兼容版本)确定了启用的功能;该参数适用于数据库或ASM 实例,具体取决于instance_type参数。例如,将该参数设置为10.1 将禁止使用Oracle Database 11g中引入的任何新功能(磁盘联机/脱机、可变区等)。

 

 

 

QUESTION 10

Which two statements about Oracle Direct Network File System (NFS) are true? (Choose two.)

A. It uses the operating system kernel NFS layer for user tasks and network communication modules.

B. File systems need not be mounted by the kernel NFS system when being served through Direct NFS.

C. Oracle Disk Manager can manage NFS on its own, without using the operating system kernel NFS driver.

D. A separate NFS interface is required for use across Linux, UNIX, and Windows platforms.

E. It bypasses the OS file system cache.

Answer: CE

Explanation/Reference:

 

In Oracle Database 11g, the Oracle NFS implements the NFS Version 3 protocol in the Oracle RDBMS kernel. Implementing the Oracle Direct NFS offers the following benefits:
Avoids the bottlenecks and resource constraints by avoiding the kernel NFS layer.
Provides a common NFS interface for Oracle for use on all operating system platforms and supported NFS servers.
Provides load balancing across multiple connections to the NFS servers, thus improving performance.
Performance is predictable because the Oracle NFS implementation enables you to completely control the input/output path to the Network File Servers.
Easier management including simpler configuration and superior diagnosability.

 

 

QUESTION 11

Which three statements are true regarding the functioning of the Autotask Background Process (ABP)? (Choose three.)

A. It creates jobs without considering the priorities associated with them.

B. It translates tasks into jobs for execution by the scheduler.

C. It determines the list of jobs that must be created for each maintenance window.

D. It is spawned by the MMON background process at the start of the maintenance window.

E. It maintains a repository in the SYSTEM tablespace to store the history of the execution of all tasks.

Answer: BCD

Explanation/Reference:

 

Oracle ABP(Autotask Background Process)

     ABP相当于自动任务与调度程序之间的中介,其主要作用是将自动任务转换成Autotask作业,供调度程序执行。同样重要的是,ABP还维护所有任务执行的历史记录。ABP将其专用资料档案库存储在sysaux表空间中,您可以通过DBA_AUTOTASK_TASK 查案该资料档案库。

        ABP是在启动维护窗口时,有MMON 启动的,所有实例只需要一个ABPMMON进程将监视ABP,并在必要时重启ABP.

        ABP可以确定为每项维护任务创建的作业列表,此列表按以下优先级排序:紧急、高级、中级。在每个优先级组中,作业是按执行的首选顺序排列的

        ABP按照以下方式创建作业:先创建所有紧急优先级的作业,然后创建高优先级的作业,最后创建所有中优先级的作业

        ABP将作业分配到多个调度程序作业类。这些作业类将作业映射到基于优先级的使用者组。

        注意:使用Oracle DB 11g时,不存在与特定任务永久关联的作业。因此,不能使用DBMS_SCHEDULER过程来控制自动任务的行为,请改而使用DBMS_AUTO_TASK_ADMIN过程

 

 

QUESTION 12

Which two statements are true with respect to the maintenance window? (Choose two.)

A. A DBA can enable or disable an individual task in all maintenance windows.

B. In case of a long maintenance window, all Automated Maintenance Tasks are restarted every four hours.

C. A DBA cannot change the duration of the maintenance window after it is created.

D. A DBA can control the percentage of the resource allocated to the Automated Maintenance Tasks in each window.

Answer: AD

Explanation/Reference:

 

 

In the case of a very long maintenance window, all automated maintenance tasks except Automatic SQL Tuning Advisor are restarted every four hours. This feature ensures that maintenance tasks are run regularly, regardless of window size.

对于一个非常长的维护窗口,所有自动维护任务除了自动SQL调试顾问都是在每4个钟重启的。这个功能确保了维护任务的运行规范,不需要理会窗口大小。

 

 

QUESTION 13

Exhibit:

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 calibration process runs successfully and populates all the bind variables.

B. The calibration process runs successfully but the latency time is not computed.

C. The calibration process runs successfully but only the latency time is computed.

D. The command produces an error.

Answer: D

Explanation/Reference:

 

http://blog.csdn.net/jgmydsai/article/details/38684347
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_resmgr.htm#ARPLS050
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:

 

 

 

QUESTION 14

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 until their password attribute in the profile is altered.

D. All passwords remain non-case-sensitive and cannot be changed.

Answer: B

Explanation/Reference:

 

 

 

QUESTION 15

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 logs a warning message in the alert log so that the DBA can perform statistics collection manually.

C. It initiates the statistics collection process by running GATHER_STATS_JOB.

D. It makes the statistics information available to GATHER_STATS_JOB.

Answer: D

Explanation/Reference:

http://blog.csdn.net/rlhua/article/details/14090013

Actions represent the jobs that are performed by a module. For example, the DBMS_SCHEDULER module can run the GATHER_STATS_JOB action to gather statistics on all database objects. If a single action is using the majority of the wait time, then you should investigate it.

 

 

QUESTION 16

You executed the following PL/SQL block successfully:

VARIABLE tname VARCHAR2(20)

BEGIN

dbms_addm.insert_finding_directive (NULL, DIR_NAME=>'Detail CPU Usage', FINDING_NAME=>'CPU Usage',

MIN_ACTIVE_SESSIONS=>0, MIN_PERC_IMPACT=>90);

:tname := 'database ADDM task4';

dbms_addm.analyze_db(:tname, 150, 162);

END;

/

Then you executed the following command:

SQL> SELECT dbms_addm.get_report(:tname) FROM DUAL;

The above command produces Automatic Database Diagnostic Monitor (ADDM) analysis ____.

A. with the CPU Usage finding if it is less than 90

B. without the CPU Usage finding if it is less than 90

C. with the CPU Usage finding for snapshots not between 150 and 162

D. with the CPU Usage finding for snapshots below 90

Answer: B

Explanation/Reference:

 

http://blog.csdn.net/rlhua/article/details/16856177

参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65063

INSERT_FINDING_DIRECTIVE Procedure

Creates a directive to limit reporting of a specific finding type.

INSERT_FINDING_DIRECTIVE Procedure

This procedure creates a directive to limit reporting of a specific finding type. The directive can be created for a specific task (only when the task is in INITIALstatus), or for all subsequently created ADDM tasks (such as a system directive).

Syntax

DBMS_ADDM.INSERT_FINDING_DIRECTIVE (

   task_name             IN VARCHAR2,

   dir_name              IN VARCHAR2,

   finding_name          IN VARCHAR2,

   min_active_sessions   IN NUMBER := 0,

   min_perc_impact       IN NUMBER := 0);

Parameters

Table 16-12 INSERT_FINDING_DIRECTIVE Procedure Parameters

Parameter

Description

task_name

Name of the task this directive applies to. If the value is NULL, it applies to all subsequently created ADDM Tasks.

dir_name

Name of the directive. All directives must be given unique names.

finding_name

Name of an ADDM finding to which this directive applies. All valid findings names appear in the NAME column of view DBA_ADVISOR_FINDING_NAMES.

min_active_sessions

Minimal number of active sessions for the finding. If a finding has less than this number, it is filtered from the ADDM result.

min_perc_impact

Minimal number for the "percent impact" of the finding relative to total database time in the analysis period. If the finding's impact is less than this number, it is filtered from the ADDM result.

 

Examples

A new ADDM task is created to analyze a local instance. However, it has special treatment for 'Undersized SGA' findings. The result of GET_REPORT shows only an 'Undersized SGA' finding if the finding is responsible for at least 2 average active sessions during the analysis period, and this constitutes at least 10% of the total database time during that period.

var tname VARCHAR2(60);

BEGIN

  DBMS_ADDM.INSERT_FINDING_DIRECTIVE(

   NULL,

   'Undersized SGA directive',

   'Undersized SGA',

   2,

   10);

  :tname := 'my_instance_analysis_mode_task';

  DBMS_ADDM.ANALYZE_INST(:tname, 1, 2);

END;

To see a report containing 'Undersized SGA' findings regardless of the directive:

SELECT DBMS_ADVISOR.GET_TASK_REPORT(:tname, 'TEXT', 'ALL') FROM DUAL;

 

 

QUESTION 17

You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter.The partition related to the current quarter is modified frequently and other partitions undergo fewer data manipulations.The preferences for the table are set to their default values. You collect statistics for the table using the following command in regular intervals:

SQL> EXECUTE BMS_STATS.GATHER_TABLE_STATS('SH','SALES',GRANULARITY=>'GLOBAL'); You need statistics to be collected more quickly. What can you do to achieve this?

A. Set the STATISTICS_LEVEL parameter to BASIC.

B. Set the INCREMENTAL value to TRUE for the partition table.

C. Set DYNAMIC_SAMPLING to level 4.

D. Increase the value of STALE_PERCENT for the partition table.

Answer: B

Explanation/Reference:

 

 

参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68595

 

Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:

·         INCREMENTAL value for the partitioned table is set to TRUE

·         PUBLISH value for the partitioned table is set to TRUE;

·         User specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table

If the INCREMENTAL value for the partitioned table was set to FALSE (default value), a full table scan is used to maintain the global statistics which is a much more resource intensive and time-consuming operation for large tables.

 

默认情况下INCREMENTAL false,如果将其设置为true,那么在搜集信息时,oracle只扫描改变的分区,这样搜集信息就会变得更快。故选C,正确

 

SQL> select dbms_stats.get_prefs('PUBLISH','SYS','T_PART') from dual;

DBMS_STATS.GET_PREFS('PUBLISH'
--------------------------------------------------------------------------------
TRUE

SQL> select dbms_stats.get_prefs('INCREMENTAL','SYS','T_PART') from dual;

DBMS_STATS.GET_PREFS('INCREMEN
--------------------------------------------------------------------------------
FALSE


SQL> select dbms_stats.get_prefs('GRANULARITY','SYS','T_PART') from dual;

DBMS_STATS.GET_PREFS('GRANULAR
--------------------------------------------------------------------------------
AUTO

 

 

 

 

QUESTION 18

You create a new Automatic Database Diagnostic Monitor (ADDM) task:

instance_analysis_mode_task. To view the ADDM report, you use the following command:

SQL> SELECT dbms_addm.get_report('my_instance_analysis_mode_task') FROM dual; You want to suppress ADDM output relating to Segment Advisor actions on user SCOTT's segments. What would you do to achieve this?

A. Add a segment directive for the ADDM task.

B. Disable the Segment Advisor from the Automatic Maintenance Task.

C. Add a finding directive for the ADDM task.

D. Add a parameter directive for the ADDM task.

Answer: A

Explanation/Reference:

 


http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_addm.htm#CACECFCJ

INSERT_SEGMENT_DIRECTIVE Procedure

This procedure creates a directive to prevent ADDM from creating actions to "run Segment Advisor" for specific segments. The directive can be created for a specific task (only when the task is in INITIAL status), or for all subsequently created ADDM tasks (such as a system directive).

Syntax

DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE (
   task_name             IN VARCHAR2,
   dir_name              IN VARCHAR2,
   owner_name            IN VARCHAR2,
   object_name           IN VARCHAR2 := NULL,
   sub_object_name       IN VARCHAR2 := NULL);
   
DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE (
   task_name             IN VARCHAR2,
   dir_name              IN VARCHAR2,
   object_number         IN NUMBER);

Parameters

Table 15-13 INSERT_SEGMENT_DIRECTIVE Procedure Parameters

Parameter

Description

task_name

Name of the task this directive applies to. If the value is NULL, it applies to all subsequently created ADDM Tasks.

dir_name

Name of the directive. All directives must be given unique names.

owner_name

Specifies the owner of the segment/s to be filtered. A wildcard is allowed in the same syntax used for "like" constraints.

object_name

Name of the main object to be filtered. Again, wildcards are allowed. The default value of NULL is equivalent to a value of '%'.

sub_object_name

Name of the part of the main object to be filtered. This could be a partition name, or even sub partitions (separated by a '.').Again, wildcards are allowed. The default value of NULL is equivalent to a value of '%'.

object_number

Object number of the SEGMENT that this directive is to filter, found in views DBA_OBJECTS or DBA_SEGMENTS

 

Examples

A new ADDM task is created to analyze a local instance. However, it has special treatment for all segments that belong to user SCOTT. The result of GET_REPORTwill not show actions for running Segment advisor for segments that belong to SCOTT.

var tname VARCHAR2(60);
BEGIN
  DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(NULL,
                                     'my Segment directive',
                                     'SCOTT'); 
  :tname := 'my_instance_analysis_mode_task';
  DBMS_ADDM.ANALYZE_INST(:tname, 1, 2);
END;

To see a report containing all actions regardless of the directive:

SELECT DBMS_ADVISOR.GET_TASK_REPORT(:tname, 'TEXT', 'ALL') FROM DUAL;

 

 

 

QUESTION 19

Evaluate the following SQL statement used to create the PRODUCTS table:

CREATE TABLE products

(product_id NUMBER(3) PRIMARY KEY,

product_desc VARCHAR2(25),

qty NUMBER(8,2),

rate NUMBER(10,2),

total_value AS ( qty * rate))

PARTITION BY RANGE (total_value)

(PARTITION p1 VALUES LESS THAN (100000),

PARTITION p2 VALUES LESS THAN (150000),

PARTITION p3 VALUES LESS THAN (MAXVALUE))

COMPRESS FOR ALL OPERATIONS;

Which statement is true regarding this command?

A. It produces an error because compression cannot be used for the TOTAL_VALUE partition key.

B. It executes successfully but partition pruning cannot happen for this partition key.

C. It produces an error because the TOTAL_VALUE column cannot be used as a partition key.

D. It executes successfully but the values in the TOTAL_VALUE column would not be physically stored in the

partitions.

Answer: D

Explanation/Reference:

 

 

 

QUESTION 20

Exhibit:

View the Exhibit to examine the details for an incident. Which statement is true regarding the status of the incident?

A. The incident has been newly created and is in the process of collecting diagnostic information.

B. The incident is now in the Done state and the ADR can select the incident to be purged.

C. The DBA is working on the incident and prefers that the incident be kept in the ADR.

D. The data collection for the incident is complete and the incident can be packaged and sent to Oracle Support.

Answer: D

Explanation/Reference:

 

 

 

QUESTION 21

Which statement describes the significance of the CHANGE FAILURE command in RMAN? (Choose all that apply.)

A. It is used to change failure priority only for HIGH or LOW priorities.

B. It is used to execute the advised repair script.

C. It is used to change failure priority only for the CRITICAL priority.

D. It is used to explicitly close the open failures.

E. It is used to inform the database about the repair after the repair script executes.

Answer: AD

Explanation/Reference:

 

参考:http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmrepai.htm#BRADV89736

If appropriate, you can use CHANGE FAILURE command at any time in the Data Recovery Advisor workflow to change the priority of a failure from LOW to HIGH orHIGH to LOW, or close a failure that has been fixed manually. 

 

 

QUESTION 22

Which statements describe the capabilities of the DBMS_NETWORK_ACL_ADMIN package? (Choose all that apply.)

A. It can be used to control the time interval for which the access privilege is available to a user.

B. It can be used to allow the access privilege settings for users but not roles.

C. It can be used to selectively restrict a user's access to different applications in a specific host computer.

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 allow the access privilege settings for users as well as roles.

Answer: ADE

Explanation/Reference:

 

http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG99984

Use the DBMS_NETWORK_ACL_ADMIN.CREATE_ACL procedure to create the content of the access control list. It contains a name of the access control list, a brief description, and privilege settings for one user or role that you want to associate with the access control list. In an access control list, privileges for each user or role are grouped together as an access control entry (ACE). An access control list must have the privilege settings for at least one user or role.

 

QUESTION 23

Identify the two direct sources from where SQL plans can be loaded into the SQL plan baselines. (Choose two.)

A. SQL Tuning Set

B. Cursor cache

C. Stored outline

D. Automatic Workload Repository (AWR) snapshots

Answer: AB

Explanation/Reference:

 

AWR snapshot 需要先导入到sql tuning set;

To load plans from a SQL tuning set, use the LOAD_PLANS_FROM_SQLSET function of the DBMS_SPM package.

To load plans from Automatic Workload Repository (AWR), load the plans stored in AWR snapshots into a SQL tuning set before using the LOAD_PLANS_FROM_SQLSET function as described in this section.

To load plans from the shared SQL area, use the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package.

 

QUESTION 24

Which statements are true regarding the creation of an incident package file by using the EM Workbench Support?

(Choose all that apply.)

A. You can add or remove the trace files to the package.

B. You can create the incremental incident package ZIP file for new or modified diagnostic information for the incident

package already created.

C. You can add SQL test cases to the incident package.

D. You cannot create an incremental incident package when the physical files are purged from the ADR.

Answer: ABC

Explanation/Reference:

 

 

参考:http://docs.oracle.com/cd/E11882_01/server.112/e25494/diag.htm#ADMIN13161

 

About Quick Packaging and Custom Packaging

The Enterprise Manager Support Workbench provides two methods for creating and uploading an incident package: the quick packaging method and the custom packaging method.

Quick Packaging—This is the more automated method with a minimum of steps, organized in a guided workflow (a wizard). You select a single problem, provide a package name and description, and then schedule upload of the package contents, either immediately or at a specified date and time. The Support Workbench automatically places diagnostic data related to the problem into the package, finalizes the package, creates the zip file, and then uploads the file. With this method, you do not have the opportunity to add, edit, or remove package files or add other diagnostic data such as SQL test cases. However, it is the simplest and quickest way to get first-failure diagnostic data to Oracle Support. 

Note that when quick packaging is complete, the package that was created by the wizard remains. You can then modify the package with custom packaging operations at a later time and manually reupload.

Custom Packaging—This is the more manual method, with more steps. It is intended for expert Support Workbench users who want more control over the packaging process. With custom packaging, you can create a new package with one or more problems, or you can add one or more problems to an existing package. You can then perform a variety of operations on the new or updated package, including:

·         Adding or removing problems or incidents

·         Adding, editing, or removing trace files in the package

·         Adding or removing external files of any type

·         Adding other diagnostic data such as SQL test cases

·         Manually finalizing the package and then viewing package contents to determine if you must edit or remove sensitive data or remove files to reduce package size.

You might conduct these operations over several days, before deciding that you have enough diagnostic information to send to Oracle Support.

With custom packaging, you create the zip file and request upload to Oracle Support as two separate steps. Each of these steps can be performed immediately or scheduled for a future date and time.

在手工打包Generate Upload File之后Select the Full or Incremental option to generate a full package zip file or an incremental package zip file.

For a full package zip file, all the contents of the package (original contents and all correlated data) are always added to the zip file.

For an incremental package zip file, only the diagnostic information that is new or modified since the last time that you created a zip file for the same package is added to the zip file. For example, if trace information was appended to a trace file since that file was last included in the generated physical file for a package, the trace file is added to the incremental package zip file. Conversely, if no changes were made to a trace file since it was last uploaded for a package, that trace file is not included in the incremental package zip file.

 

 

 

QUESTION 25

Exhibit:

View the Exhibit to examine the error during the database startup. You open an RMAN session for the database

instance. To repair the failure, you executed the following as the first command in the RMAN session:

RMAN> REPAIR FAILURE;

Which statement describes the consequence of the command?

A. The command performs the recovery and closes the failures.

B. The command executes the RMAN script to repair the failure and removes the entry from the Automatic Diagnostic

Repository (ADR).

C. The command only displays the advice and the RMAN script required for repair.

D. The command produces an error because the ADVISE FAILURE command has not been executed before the

REPAIR FAILURE command.

Answer: D

Explanation/Reference:

 

http://blog.csdn.net/rlhua/article/details/10906981

 

答案解析:

1、首先来看下各个文件的位置。

 

sys@TEST0910> select FILE_ID,FILE_NAME,TABLESPACE_NAME from dba_data_files;

 

   FILE_ID FILE_NAME                                          TABLESPACE_NAME

---------- -------------------------------------------------- ------------------------------

         4 /u01/app/oracle/oradata/test0910/users01.dbf       USERS

         3 /u01/app/oracle/oradata/test0910/undotbs01.dbf     UNDOTBS1

         2 /u01/app/oracle/oradata/test0910/sysaux01.dbf      SYSAUX

         1 /u01/app/oracle/oradata/test0910/system01.dbf      SYSTEM

         5 /u01/app/oracle/oradata/test0910/example01.dbf     EXAMPLE

 

2、先用rman备份users表空间,用作恢复。

RMAN> backup tablespace users;

 

Starting backup at 13-SEP-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=67 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle/oradata/test0910/users01.dbf

channel ORA_DISK_1: starting piece 1 at 13-SEP-13

channel ORA_DISK_1: finished piece 1 at 13-SEP-13

piece handle=/u01/app/oracle/fast_recovery_area/TEST0910/backupset/2013_09_13/o1_mf_nnndf_TAG20130913T111426_936byprm_.bkp tag=TAG20130913T111426 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 13-SEP-13

 

3、操作系统删除users.dbf文件

[oracle@rtest ~]$ rm -rf /u01/app/oracle/oradata/test0910/users01.dbf

[oracle@rtest ~]$ ls /u01/app/oracle/oradata/test0910/users01.dbf

ls: /u01/app/oracle/oradata/test0910/users01.dbf: No such file or directory

 

 

4shutdown abort模拟数据库宕机并startup,发现错误与题目一样

sys@TEST0910> shutdown abort;

ORACLE instance shut down.

sys@TEST0910> startup

ORACLE instance started.

 

Total System Global Area 2505338880 bytes

Fixed Size                  2230952 bytes

Variable Size             553649496 bytes

Database Buffers         1929379840 bytes

Redo Buffers               20078592 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/u01/app/oracle/oradata/test0910/users01.dbf'

 

 

5、进入rman,修复失败。

RMAN> repair failure ;

 

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of repair command at 09/13/2013 11:20:04

RMAN-06954: REPAIR command must be preceded by ADVISE command in same session

 

在试图没有进行advise failure命令时使用repair failure,则报错。

主要原因为:在repair failure之前,要先运行advise failure,让rman给出修复的建议,并给出修复的脚本,之后再运行repair failure

 

6、按照提示,进行advise命令。

RMAN> advise failure;

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of advise command at 09/13/2013 11:20:59

RMAN-07211: failure option not specified

 

为什么也报错呢?原因为在advise failure之前,要先运行list failure,列出所要修复的错误,所以先list failure

 

7、运行LIST-->ADVISE-->REPAIR 命令

 

RMAN> list failure;

 

List of Database Failures

=========================

 

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

122        HIGH     OPEN      13-SEP-13     One or more non-system datafiles are missing

 

 

 

RMAN> advise failure;

 

List of Database Failures

=========================

 

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

122        HIGH     OPEN      13-SEP-13     One or more non-system datafiles are missing

 

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=189 device type=DISK

analyzing automatic repair options complete

 

Mandatory Manual Actions

========================

no manual actions available

 

Optional Manual Actions

=======================

1. If file /u01/app/oracle/oradata/test0910/users01.dbf was unintentionally renamed or moved, restore it

 

Automated Repair Options

========================

Option Repair Description

------ ------------------

1      Restore and recover datafile 4 

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: /u01/app/oracle/diag/rdbms/test0910/test0910/hm/reco_3910127882.hm

 

advise failure命令对记录在自动诊断信息库中的所有故障给出建议。默认时,此命令只列出具有criticalhigh优先级的那些故障。除了产生所有输入故障的摘要外,此命令还对每个故障提供一个建议修复选项。通常,advise  failure命令同时给出自动和手动修复选项。在advisefailure命令输出结束时,RMAN生成一个脚本,列出建议的修复选项的细节。如果你想自己进行修复,可以直接使用这个脚本,或者对它进行修改。

 

RMAN> repair failure;

 

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/test0910/test0910/hm/reco_3910127882.hm

 

contents of repair script:

   # restore and recover datafile

   restore datafile 4;

   recover datafile 4;

   sql 'alter database datafile 4 online';

 

Do you really want to execute the above repair (enter YES or NO)? Y

executing repair script

 

Starting restore at 13-SEP-13

using channel ORA_DISK_1

 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test0910/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST0910/backupset/2013_09_13/o1_mf_nnndf_TAG20130913T111426_936byprm_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST0910/backupset/2013_09_13/o1_mf_nnndf_TAG20130913T111426_936byprm_.bkp tag=TAG20130913T111426

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 13-SEP-13

 

Starting recover at 13-SEP-13

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:09

 

Finished recover at 13-SEP-13

 

sql statement: alter database datafile 4 online

repair failure complete

 

Do you want to open the database (enter YES or NO)? YES

database opened

 

 

利用advise failure 命令提供的建议,repaire failure根据建议恢复错误。

 

 

 

 

QUESTION 26

Which of the following information will be gathered by the SQL Test Case Builder for the problems pertaining to SQL related problems? (Choose all that apply.)

A. ADR diagnostic files

B. PL/SQL functions, procedures, and packages

C. the table and index definitions and actual data

D. all the optimizer statistics

E. initialization parameter settings

Answer: CDE

Explanation/Reference:

 

26题的正确答案是BDE

 

Building SQL Test Cases

The information gathered by SQL Test Case Builder includes the query being executed, table and index definitions (but not the actual data), PL/SQL functions, procedures, and packages, optimizer statistics, and initialization parameter settings.

 

 

QUESTION 27

Which two activities are NOT supported by the Data Recovery Advisor? (Choose two.)

A. Diagnose and repair failures on a standby database.

B. Recover from failures in the Real Application Cluster (RAC) environment.

C. Diagnose and repair a data file corruption online.

D. Diagnose and repair a data file corruption offline.

Answer: AB

Explanation/Reference:

 

参考:http://blog.csdn.net/rlhua/article/details/12622415

Data Recovery Advisor支持的数据库配置:

单实例

RAC

支持故障转移到备用数据库,但不支持分析和修复备用数据库

 

 

QUESTION 28

You want to track and store all transactional changes to a table over its lifetime. To accomplish this task, you enabled

Flashback Data Archive with the retention of 5 years. After some time, the business requirement changed and you were asked to change the retention from 5 years to 3 years. To accomplish this, you issued the following command:

ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 3 YEAR; What is the implication of this command?

A. The command produces an error because the retention time cannot be reduced.

B. All historical data is retained but the subsequent flashback data archives are maintained for only three years.

C. All historical data is purged and the new flashback data archives are maintained for three years.

D. All historical data older than three years is purged from the flashback archive FLA1.

Answer: D

Explanation/Reference:

 

 

QUESTION 29

Exhibit:

View the Exhibit to examine the replay settings for replay parameters. What is the implication for setting the values for

replay parameters? (Choose all that apply.)

A. The COMMIT order in the captured workload is preserved during replay.

B. The value 100 in the THINK_TIME_SCALE parameter attempts to match the captured user think time while

replaying.

C. The value 100 in the CONNECT_TIME_SCALE parameter attempts to connect all sessions as captured.

D. The value 100 in the THINK_TIME_SCALE parameter attempts to make the replay client shorten the think time

between calls.

E. The value 100 in the CONNECT_TIME_SCALE parameter attempts to connect all sessions immediately as soon as

the replay begins.

Answer: ABC

Explanation/Reference:

http://blog.csdn.net/rlhua/article/details/14054127

参考:http://blog.csdn.net/rlhua/article/details/14053709

官方参考:http://docs.oracle.com/cd/E11882_01/server.112/e41481/dbr_replay.htm#RATUG141


 

 

 

QUESTION 30

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 can remain as the default but must have no active sort operations.

B. The tablespace must be locally managed.

C. The tablespace must have only one temp file.

D. The tablespace must be made nondefault and offline.

Answer: B

Explanation/Reference:

 

http://blog.csdn.net/jgmydsai/article/details/38169105

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN12353

Shrinking a Locally Managed Temporary Tablespace

Large sort operations performed by the database may result in a temporary tablespace growing and occupying a considerable amount of disk space. After the sort operation completes, the extra space is not released; it is just marked as free and available for reuse. Therefore, a single large sort operation might result in a large amount of allocated temporary space that remains unused after the sort operation is complete. For this reason, the database enables you to shrink locally managed temporary tablespaces and release unused space.

You use the SHRINK SPACE clause of the ALTER TABLESPACE statement to shrink a temporary tablespace, or the SHRINK TEMPFILE clause of the ALTER TABLESPACE statement to shrink a specific temp file of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or temp file. The optional KEEP clause defines a minimum size for the tablespace or temp file.

Shrinking is an online operation, which means that user sessions can continue to allocate sort extents if needed, and already-running queries are not affected.

The following example shrinks the locally managed temporary tablespace lmtmp1 while ensuring a minimum size of 20M.

ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;

The following example shrinks the temp file lmtemp02.dbf of the locally managed temporary tablespace lmtmp2. Because the KEEPclause is omitted, the database attempts to shrink the temp file to the minimum possible size.

ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';

 

 

 

QUESTION 31

Which statement is true regarding the VALIDATE DATABASE command?

A. It checks the database for intrablock corruptions only.

B. It checks for block corruption in the valid backups of the database.

C. It checks the database for both intrablock and interblock corruptions.

D. It checks for only those corrupted blocks that are associated with data files.

Answer: A

Explanation/Reference:

 

 

 

QUESTION 32

Exhibit:

View the Exhibit that sets the threshold for the Current Open Cursors Count metric. Why is the Significance Level

threshold type not available in the threshold setting?

A. because the STATISTICS_LEVEL parameter is set to BASIC

B. because the AWR baseline is a system-defined moving window baseline

C. because AWR baseline is not enabled

D. because Current Open Cursors Count is not a basic metric

Answer: D

Explanation/Reference:

这道题是问,为什么阈值类型没有显著性水平?

 

点击编辑阈值后


 

 

 

QUESTION 33

You enabled Flashback Data Archive on the INVENTORY table. Which DDL operation is supported on the table after

enabling Flashback Data Archive?

A. Partition the table

B. Truncate the table.

C. Drop the table.

D. Rename a column in the table.

E. Add a column to the table.

Answer: E

Explanation/Reference:

 

根据D50081CN11 Oracle Database 11g:面向管理员的新增功能文档

 

 

DDL 限制

出于安全性和合法兼容性方面的考虑,上述限制可确保闪回数据归档中的数据不会失效。对启用了闪回数据归档功能的表使用下述任一DDL 语句都会导致错误ORA-55610

? 执行以下任一操作的ALTER TABLE语句:

删除、重命名或修改列

执行分区或子分区操作

LONG列转换为LOB

包括UPGRADE TABLE子句(不管有无INCLUDING DATA子句)

? DROP TABLE语句

 

参考:http://blog.csdn.net/rlhua/article/details/12221269

 

官方参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS640

 

DDL Statements on Tables Enabled for Flashback Data Archive

Flashback Data Archive supports only these DDL statements:

·         ALTER TABLE statement that does any of the following:

o    Adds, drops, renames, or modifies a column

o    Adds, drops, or renames a constraint

o    Drops or truncates a partition or subpartition operation

·         TRUNCATE TABLE statement

·         RENAME statement that renames a table

Flashback Data Archive does not support DDL statements that move, split, merge, or coalesce partitions or subpartitions, move tables, or convert LONG columns to LOB columns.

For example, the following DDL statements cause error ORA-55610 when used on a table enabled for Flashback Data Archive:

·         ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause

·         ALTER TABLE statement that moves or exchanges a partition or subpartition operation

·         DROP TABLE statement

If you must use unsupported DDL statements on a table enabled for Flashback Data Archive, use theDBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA procedure to disassociate the base table from its Flashback Data Archive. To reassociate the Flashback Data Archive with the base table afterward, use the DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA procedure.

根据官方文档,此题可选CDE,可根据D50081CN11文档,只能选D,且没有提到C是否支持。

综合一下,如果考试时单选则选D,如果多选则选CDE

 

 

QUESTION 34

You are managing an Oracle Database 11g database with ASM storage. The ASM disk group has the COMPATIBLE. ASM attribute set to 11.1. Which statements are true regarding extent management and allocation units in the ASM disk group? (Choose all that apply.)

A. The au_size disk group attribute determines the size of allocation units in the disk group.

B. The allocation unit size may vary but the extent size is fixed.

C. The allocation unit size and extent size are fixed for all the disks in a disk group and cannot be changed.

D. Extent management is completely automated.

Answer: AD

Explanation/Reference:

 

 

QUESTION 35

Which are the prerequisites for performing flashback transactions on your database? (Choose all that apply.)

A. Undo retention guarantee for the database must be configured.

B. Supplemental log must be enabled for the primary key.

C. Supplemental log must be enabled.

D. Execute permission on the DBMS_FLASHBACK package must be granted to the user.

Answer: BCD

Explanation/Reference:

 

参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS1010

Configuring Your Database for Flashback Transaction

To configure your database for the Flashback Transaction feature, you or your database administrator must:

·         With the database mounted but not open, enable ARCHIVELOG:

ALTER DATABASE ARCHIVELOG;

·         Open at least one archive log:

ALTER SYSTEM ARCHIVE LOG CURRENT;

·         If not done, enable minimal and primary key supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

·         If you want to track foreign key dependencies, enable foreign key supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Note:

If you have very many foreign key constraints, enabling foreign key supplemental logging might not be worth the performance penalty.


 Flashback Transaction

Use Flashback Transaction to roll back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and run the corresponding compensating transactions that return the affected data to its original state. (Flashback Transaction is part ofDBMS_FLASHBACK package.) 

For DBMS_FLASHBACK Package

To allow access to the features in the DBMS_FLASHBACK package, grant the EXECUTE privilege on DBMS_FLASHBACK.

 

Using Flashback Transaction

The DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure rolls back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and run the compensating transactions that return the affected data to its original state.

The transactions being rolled back are subject to these restrictions:

·         They cannot have performed DDL operations that changed the logical structure of database tables.

·         They cannot use Large Object (LOB) Data Types:

o    BFILE

o    BLOB

o    CLOB

o    NCLOB

·         They cannot use features that LogMiner does not support.

The features that LogMiner supports depends on the value of the COMPATIBLE initialization parameter for the database that is rolling back the transaction. The default value is the release number of the most recent major release.

Flashback Transaction inherits SQL data type support from LogMiner. Therefore, if LogMiner fails due to an unsupported SQL data type in a the transaction, Flashback Transaction fails too.

Some data types, though supported by LogMiner, do not generate undo information as part of operations that modify columns of such types. Therefore, Flashback Transaction does not support tables containing these data types. These include tables with BLOB, CLOB and XML type.

 

 

QUESTION 36

Which statement is true regarding online redefinition for the migration of BasicFile LOBs to SecureFile LOBs?

A. It can be done at the table level or partition level.

B. It does not require additional storage because the operation is done online.

C. Local and global indexes are maintained automatically during the operation.

D. It cannot be done in parallel.

Answer: A

Explanation/Reference:

 

Online Redefinition for BasicFiles LOBs

Online redefinition is the only recommended method for migration of BasicFiles LOBs to SecureFiles LOBs. It can be done at the table or partition level.

迁移到SecureFiles 

使用LOB 接口超集,可轻松从BasicFile LOB 进行迁移。迁移到SecureFiles  有两种建议方法:分区交换和联机重新定义。

分区交换

? 需要与表中最大分区相等的额外空间

? 可在交换期间维护索引

? 可将工作量分散到多个较小的维护窗口

? 要求表或分区脱机以执行交换

联机重新定义(建议做法)

? 不要求表或分区脱机

? 可并行进行

? 要求额外存储空间等于整个表,并且所有LOB 段均可用

? 要求重建所有全局索引

这些解决方案通常意味着使用输入LOB 列中的数据所用磁盘空间两倍的空间。但是,使用分区和按分区执行这些操作有助于降低所需的磁盘空间。

 

 

QUESTION 37

Which three statements about performance analysis by SQL Performance Analyzer are true? (Choose three.)

A. It produces results that can be used to create the SQL plan baseline.

B. It detects changes in SQL execution plans.

C. It generates recommendations to run SQL Tuning Advisor to tune regressed SQLs.

D. The importance of SQL statements is based on the size of the objects accessed.

E. It shows only the overall impact on workload and not the net SQL impact on workload.

Answer: ABC

Explanation/Reference:

 

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41481/spa_intro.htm#RATUG174

SQL Performance Analyzer compares the performance of SQL statements before and after the change and produces a report identifying any changes in execution plans or performance of the SQL statements.

Fixing Regressed SQL Statements

If the performance analysis performed by SQL Performance Analyzer reveals regressed SQL statements, then you can make changes to remedy the problem. For example, you can fix regressed SQL by running SQL Tuning Advisor or using SQL plan baselines. You can then repeat the process of executing the SQL statements and comparing its performance to the first execution. Repeat these steps until you are satisfied with the outcome of the analysis

 

 

QUESTION 38

The INV_HISTORY table is created using the command:

SQL>CREATE TABLE INV_HISTORY

(inv_no NUMBER(3),

inv_date DATE,

inv_amt NUMBER(10,2))

partition by range (inv_date)

interval (numtoyminterval(1,'month'))

(partition p0 values less than (to_date('01-01-2005','dd-mm-yyyy')), partition p1 values less than (to_date('01-01-

2006','dd-mm-yyyy'))); The following data has been inserted into the INV_HISTORY table :

INV_NO INV_DATE INV_AMT

1 30-dec-2004 1000

2 30-dec-2005 2000

3 1-feb-2006 3000

4 1-mar-2006 4000

5 1-apr-2006 5000

You would like to store the data belonging to the year 2006 in a single partition and issue the command:

SQL> ALTER TABLE inv_history MERGE PARTITIONS FOR(TO_DATE('15-feb-2006','dd-mon-yyyy')), FOR(TO_DATE('15-apr-2006')) INTO PARTITION sys_py;

What would be the outcome of this command?

A. It executes successfully, and the transition point is set to '15-apr-2006'.

B. It produces an error because the partitions specified for merging are not adjacent.

C. It produces an error because the date values specified in the merge do not match the date values stored in the

table.

D. It executes successfully, and the transition point is set to '1-apr-2006'.

Answer: B

Explanation/Reference:

 

该表创建创建时默认是按一个月没间隔的分区:interval (numtoyminterval(1,'month'))

 

11g分布表新特性——Interval分区(下)

http://blog.itpub.net/17203031/viewspace-706173

Oracle 11g中推出的Interval-Partition特性,是针对Range类型分区的一种功能拓展。对连续数据类型的Range分区,如果插入的新数据值与当前分区均不匹配,Interval-Partition特性可以实现自动的分区创建。

目前的Interval-Partition支持的Range分区键类型只有numberdate两种类型。在上面的示例中,我们已经演示了数字number类型的分区拓展,下面我们进行date类型演示。

http://blog.csdn.net/rlhua/article/details/15694639

INTERVAL Clause

Use this clause to establish interval partitioning for the table. Interval partitions are partitions based on a numeric range or datetime interval. They extend range partitioning by instructing the database to create partitions of the specified range or interval automatically when data inserted into the table exceeds all of the range partitions.

 

 

QUESTION 39

You plan to have a larger moving window size for the default system-defined moving window baseline because you

want to use the adaptive threshold. Which statement factors in this consideration while increasing the size of the moving window?

A. The moving window size must be less than Undo Retention.

B. The moving window size should be greater than the Automatic Workload Repository (AWR) retention period.

C. The collection level for the AWR should be set to BASIC.

D. The moving window size should be equal to or less than the Automatic Workload Repository (AWR) retention period.

Answer: D

Explanation/Reference:

 

移动窗口基线

Oracle Database 会自动维护一个系统定义的移动窗口基线。系统定义的移动窗口基线的默认窗口大小为当前的AWR 保留期(默认为八天)。如果计划使用自适应阈值,则可考虑使用较大的移动窗口(如30 天),以便精确地计算阈值。通过将移动窗口中的天数更改为等于或小于AWR 保留期中的天数的值,可以调整移动窗口基线的大小。因此,要增加移动窗口的大小,需要先相应地增加AWR 保留期。

这种系统定义的基线提供了一个现成的默认基线,EM 的性能屏幕可对照当前的数据库性能对性能进行比较。

注:在Oracle Database 11g中,快照数据的默认保留期已经从七天更改为八天,以确保捕获整周的性能数据。

 

 

QUESTION 40

You issued the following command:

CREATE GLOBAL TEMPORARY TABLE admin_work_area

(startdate DATE,

enddate DATE,

class CHAR(20))

ON COMMIT DELETE ROWS

TABLESPACE tbs_t1;

An index is then created on the ADMIN_WORK_AREA temporary table. Which two statements are true regarding the

TBS_T1 tablespace in the above command? (Choose two.)

A. It stores only the temporary table but not its indexes.

B. It must be a nondefault temporary tablespace for the database.

C. It stores both the temporary table as well as its indexes.

D. It can be a default or nondefault temporary tablespace for the database.

E. It must be the default temporary tablespace of the user who issues the command.

Answer: CD

Explanation/Reference:

http://blog.csdn.net/rlhua/article/details/14494053

Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

By default, rows in a temporary table are stored in the default temporary tablespace of the user who creates it. However, you can assign a temporary table to another tablespace upon creation of the temporary table by using the TABLESPACE clause of CREATE GLOBAL TEMPORARY TABLE.

 

 

 

QUESTION 41

Which statements are true regarding the system-defined moving window baseline in Oracle Database 11g? (Choose all

that apply.)

A. It is created when the first snapshot is collected by the Automatic Workload Repository (AWR).

B. It is created by default with the window size being equal to the AWR retention time.

C. Adaptive threshold functionalities use it by default to compute statistics.

D. It does not allow you to change the moving window size.

Answer: BC

Explanation/Reference:

 

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/autostat.htm#PFGRF94179

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.

 

 

QUESTION 42

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. The latency time is computed only when the TIMED_STATISTICS initialization parameter is set to TRUE.

D. It improves the performance of the performance-critical sessions while running.

E. It can be used to estimate the maximum number of I/Os and maximum latency time for the system.

Answer: ACE

Explanation/Reference:

 

IO校准(IO Calibration)特性可以帮助我们了解存储系统的真实性能,以进一步判断I/O性能问题是由数据库还是存储系统自身引起的。I/O校准特性(IO Calibration)通过对Oracle数据文件的随机I/O访问存储介质,其结论值更符合数据库IO性能的真实情况。
在使用该特性前,我们要确保满足以下条件:

§  调用该存储过程需要用到SYSDBA权限

§  TIME_STATISTICS 参数为true:?

SQL> show parameter timed_statistics;

NAME TYPE VALUE
———————————— ———– ——————————
timed_statistics boolean TRUE

§  必须打开异步IO;注意在使用文件系统时,FILESYSTEMIO_OPTIONS需设为SETALLASYNC,否则Oracle不会启用异步IO

§  可以通过以下查询检验是否启用了异步IO:

SQL> SELECT NAME, ASYNCH_IO

2    FROM V$DATAFILE F, V$IOSTAT_FILE I

3   WHERE F.FILE# = I.FILE_NO

4     AND FILETYPE_NAME = 'Data File';

 

NAME                                                                             ASYNCH_IO

-------------------------------------------------------------------------------- ---------

D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_SYSTEM_65DN8HXT_.DBF       ASYNC_ON

D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_SYSAUX_65DN8J18_.DBF       ASYNC_ON

D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_UNDOTBS1_65DN8J1S_.DBF     ASYNC_ON

D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_USERS_65DN8J2X_.DBF        ASYNC_ON

IO Calibration特性可以通过DBMS_RESOURCE_MANAGER.CALIBRATE_IO存储过程调用;该过程会对Oracle数据文件引发一系列IO敏感的只读工作负载(1MB大小的随机IO组成),从而判断存储系统所能持续的最大IOPS(每秒最大IO请求数)MBPS(每秒IO传输速率)。为了使结果更具代表性,应当保持IO Calibration测试过程中整个数据库是空闲的,没有其他IO负载损耗。

 

 

 

QUESTION 43

Which statements are true regarding system-partitioned tables? (Choose all that apply.)

A. Only a single partitioning key column can be specified.

B. The same physical attributes must be specified for each partition.

C. Unique local indexes cannot be created on a system-partitioned table.

D. Traditional partition pruning and partitionwise joins are not supported on these tables.

E. All DML statements must use partition-extended syntax.

Answer: CD

Explanation/Reference:

 

从以下官方文档得知,ABC是错误的,D是正确的,排除法,选DE

题问:哪一个是关于系统分区表的真实陈述?

A.只有一个分区键列可以被指定。System partitioning does not use partitioning keys

B.所有DML语句必须使用分区扩展语法。错误。

Both INSERT and MERGE statements (not shown here) must use the partition extended syntax to identify the partition to which the row should be added.

While delete and update operations do not require the partition extended syntax

C.必须为每个分区指定同样的物理属性。错误。Each partition can have different physical attributes

 

D.唯一本地索引不能在系统分区表上被创建。

E.传统的分区修剪和智能化分区连接不支持这些表。

 

 

QUESTION 44

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 remains encrypted when it is stored in the redo logs.

B. The data remains encrypted when it is read into memory.

C. The data remains encrypted in the UNDO tablespace provided that the UNDO tablespace was created with the

encryption option enabled.

D. The data is decrypted during SORT and JOIN operations.

Answer: A

Explanation/Reference:

 

参考:http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN12327

To maximize security, data from an encrypted tablespace is automatically encrypted when written to the undo tablespace, to the redo logs, and to any temporary tablespace. There is no need to explicitly create encrypted undo or temporary tablespaces, and in fact, you cannot specify encryption for those tablespace types.

 

 

 

QUESTION 45

You are managing an Oracle Database 11g ASM instance with a disk group dg01 having three disks. One of the disks

in the disk group becomes unavailable because of power failure. You issued the following command to change the

DISK_REPAIR_TIME attribute from 3.6 hours to 5 hours:

ALTER DISKGROUP dg01 SET ATTRIBUTE 'disk_repair_time' = '5h'; To which disks in the disk group will the new

value be applicable?

A. all disks in the disk group

B. all disks that are not currently in OFFLINE mode

C. all disks that are currently in OFFLINE mode

D. all disks in the disk group only if all of them are ONLINE

Answer: B

Explanation/Reference:

 

参考:http://docs.oracle.com/cd/E11882_01/server.112/e10803/config_storage.htm#HABPT4818

Set the DISK_REPAIR_TIME Disk Group Attribute Appropriately

The DISK_REPAIR_TIME disk group attribute specifies how long a disk remains offline before Oracle ASM drops the disk. If a disk is made available before theDISK_REPAIR_TIME parameter has expired, the storage administrator can issue the ONLINE DISK command and Oracle ASM resynchronizes the stale data from the mirror side. In Oracle Database 11g, the online disk operation does not restart if there is a failure of the instance on which the disk is running. You must reissue the command manually to bring the disk online.

You can set a disk repair time attribute on your disk group to specify how long disks remain offline before being dropped. The appropriate setting for your environment depends on how long you expect a typical transient type of failure to persist.

Set the DISK_REPAIR_TIME disk group attribute to the maximum amount of time before a disk is definitely considered to be out of service.

 

 

QUESTION 46

You have applications that have frequently executed queries, and produce small and static result sets.

You configure the sqlnet.ora file in the client machine to set a nonzero value for the OCI_RESULT_CACHE_MAX_SIZE parameter.

What is the purpose of this configuration?

A. to avoid round trips to the server by enabling caching of query results in client memory

B. to improve performance by storing a copy of the data from the private SQL area of the PGA

C. to enhance the query performance by creating a cache in the client memory for sorting operations

D. to avoid the storing of query plans and results in the server by creating a cache in the client memory

Answer: A

Explanation/Reference:

 

The server initialization parameters:

CLIENT_RESULT_CACHE_SIZE

The default value is zero, implying that the client cache feature is disabled. To enable the client result cache feature, set the size to 32768 bytes (32 Kilobytes (KB)) or greater. This is the minimum size of the client per-process result set cache. All OCI client processes get this minimum size. This can be overridden by the sqlnet.ora configuration parameter OCI_RESULT_CACHE_MAX_SIZE only if this feature is enabled on the server by theCLIENT_RESULT_CACHE_SIZE initialization parameter.

You can view the current default maximum size by displaying the value of the CLIENT_RESULT_CACHE_SIZE parameter. To increase this maximum size, you can set CLIENT_RESULT_CACHE_SIZE. However, because CLIENT_RESULT_CACHE_SIZE is a static parameter, you must include the SCOPE = SPFILE clause if you use an ALTER SYSTEM statement, and you must restart the database before any changes to this parameter take effect.

Note that if the client result cache feature is disabled at the server, the client configuration parameter OCI_RESULT_CACHE_MAX_SIZE is ignored and the client result cache cannot be enabled at the client.

Client Configuration File

A client configuration file is optional and overrides the cache parameters set in the server init.ora initialization file. These parameters are part of a sqlnet.orafile. The following optional parameters are available for client configuration:

·         OCI_RESULT_CACHE_MAX_SIZE (optional) - Maximum size in bytes for the per-process query cache. Specifying a size less than 32768 in the client sqlnet.orafile disables the client result cache feature for client processes reading this sqlnet.ora file.

 

 

QUESTION 47

To control the execution of a server process when it is receiving bad packets from a potentially malicious client, you set the SEC_PROTOCOL_ERROR_FURTHER_ACTION initialization parameter as follows:

SQL> ALTER SYSTEM SET SEC_PROTOCOL_ERROR_FURTHER_ACTION = Drop,10;

What is the significance of this setting?

A. It terminates the client connection after receiving a bad packet and the client can reconnect to the same instance

after 10 minutes.

B. It terminates the client connection after 10 bad packets but the client can still reconnect, and attempt the same

operation again.

C. It terminates the client connection after 10 bad packets and the client cannot reconnect to the same instance.

D. It terminates the client connection 10 seconds after receiving a bad packet and the client cannot reconnect to the

same instance.

Answer: B

Explanation/Reference:

 

参考:http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams227.htm#REFRN10282

B,在发现有10个坏包后终止客户端连接,但客户端仍然可以重新连接,并再次尝试同样的操作。

SEC_PROTOCOL_ERROR_FURTHER_ACTION specifies the further execution of a server process when receiving bad packets from a possibly malicious client.

Values:

·         CONTINUE

The server process continues execution. The database server may be subject to a Denial of Service (DoS) if bad packets continue to be sent by a malicious client.

·         (DELAY,integer)

The client experiences a delay of integer seconds before the server process accepts the next request from the same client connection. Malicious clients are prevented from excessive consumption of server resources while legitimate clients experience a degradation in performance but can continue to function.

·         (DROP,integer)

The server forcefully terminates the client connection after integer cumulative bad packets. The server protects itself at the expense of the client (for example, a client transaction may be lost). The client may reconnect and attempt the same operation.

 

 

 

QUESTION 48

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. The execution plan and execution statistics are computed for each SQL statement in the STS.

B. The effects of DDL and DML are considered to produce the execution plan and execution statistics.

C. Every SQL statement in the STS is considered only once for execution.

D. The SQL statements in the STS are executed concurrently to produce the execution plan and execution statistics.

Answer: AC

Explanation/Reference:

 

 

 

 

QUESTION 49

Sales details are being stored on a daily basis in the SALES_2007 table. A large amount of data is added to the table daily. To save disk space, you issued the following command:

ALTER TABLE sales_2007 COMPRESS FOR ALL OPERATIONS;

What would be the outcome of this command?

A. It immediately compresses all existing data as well as new data, resulting from either fresh additions or modifications to existing data.

B. It produces an error because data already exists in the table.

C. It compresses all data added or modified henceforth but the existing data in the table is not compressed

immediately.

D. It produces an error because compression can be enabled at table creation only.

Answer: C

Explanation/Reference:

 

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#SQLRF54485

When you enable table compression by specifying COMPRESS FOR OLTP, you enable OLTP table compressionOracle Database compresses data during all DML operations on the table. This form of compression is recommended for OLTP environments.

Tables with COMPRESS FOR OLTP or NOCOMPRESS use the PCTFREE default value of 10, to maximize compress while still allowing for some future DML changes to the data, unless you override this default explicitly.

In earlier releases, OLTP table compression was enabled using COMPRESS FOR ALL OPERATIONS. This syntax has been deprecated.

If you specify COMPRESS FOR OLTPthen chained rows are not compressed unless the header for the row remains in the original block and all row columns are moved to another block. If the row chaining results in leaving just the row header in the block and moving all of the row's columns to the next block, and they all fit in the next block, then the columns can be compressed.

 

 

QUESTION 50

When executing a SQL workload, you choose to generate execution plans only, without collecting execution statistics.

Which two statements describe the implications of this? (Choose two.)

A. Only the changes in the execution plan, and not performance regression, are detected.

B. It shortens the time of execution and reduces the impact on system resources.

C. It produces less accurate results of the comparison analysis.

D. It automatically calls the SQL Tuning Advisor for recommendations.

Answer: BC

Explanation/Reference:

 

 

 

QUESTION 51

Which dependent object will get invalidated even if it is not affected by the table redefinition?

A. packages

B. triggers

C. synonyms

D. views

Answer: B

Explanation/Reference:

 

 

 

QUESTION 52

A PL/SQL procedure queries only those columns of a redefined table that were unchanged by the online table redefinition. What happens to the PL/SQL procedure after the online table redefinition?

A. It remains valid.

B. It becomes invalid for all options of online table redefinition but automatically gets revalidated the next time it is used.

C. It becomes invalid only if the storage parameters have been modified and it automatically gets revalidated the next

time it is used.

D. It becomes invalid for all options of online table redefinition and is automatically recompiled during online redefinition

of the table.

Answer: A

Explanation/Reference:

 

 

 

QUESTION 53

The ADMIN_EMP table has columns EMPNO, ENAME, DEPTNO, and SAL. It has a materialized view EMP_MV with a materialized log and an ENAME_IDX index on the ENAME column.

You need to perform an online table redefinition on the ADMIN_EMP table to move it from the TBS1 tablespace to the

TBS2 tablespace in the same schema. What action is required for the dependent objects when you perform online redefinition on the table?

A. The materialized view should have a complete refresh performed after the online table redefinition is completed.

B. The materialized view should have a fast refresh performed after the online table redefinition is completed.

C. The materialized view, materialized log, and the index should be dropped and re-created after the online table redefinition is complete.

D. The materialized view and materialized log should be dropped and all constraints disabled and re-created after the

online table redefinition is complete.

Answer: A

Explanation/Reference:

 

QUESTION 54

Which setting enables the baselines by default in Oracle Database 11g?

A. enabling Automated Maintenance Task using Oracle Enterprise Manager

B. setting the STATISTICS_LEVEL parameter to TYPICAL

C. adding filters to the Automatic Database Diagnostic Monitor (ADDM)

D. setting the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE

Answer: B

Explanation/Reference:

 

Gathering database statistics using the AWR is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVELparameter should be set to the TYPICAL or ALL to enable statistics gathering by the AWR. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASICdisables many Oracle Database features, including the AWR, and is not recommended. If STATISTICS_LEVEL is set to BASIC, you can still manually capture AWR statistics using the DBMS_WORKLOAD_REPOSITORY package. However, because in-memory collection of many system statistics—such as segments statistics and memory advisor information—will be disabled, the statistics captured in these snapshots may not be complete. For information about the STATISTICS_LEVELinitialization parameter, see Oracle Database Reference.

 

QUESTION 55

Identify the activities performed as part of the Automatic SQL Tuning process in the maintenance window? (Choose all

that apply.)

A. tuning each SQL statement in the order of importance

B. generating baselines that include candidate SQLs for tuning

C. generating the SQL profile

D. adding tuned SQL plans into the SQL plan baseline       该选项需数据库要使用SQL plan management才成立;

E. testing and accepting the SQL profile

F. generating a list of candidate SQLs for tuning

Answer: ACEF

Explanation/Reference:

http://docs.oracle.com/cd/E28271_01/server.1111/e16638/sql_tune.htm

The database can automatically tune SQL statements by identifying problematic statements and implementing recommendations using SQL Tuning Advisor during system maintenance windows. When run automatically, SQL Tuning Advisor is known as the Automatic SQL Tuning Advisor.

 

Oracle Database automatically runs SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. This task, called Automatic SQL Tuning, runs in the default maintenance windows on a nightly basis. By default, automatic SQL tuning runs for at most one hour. You can customize attributes of the maintenance windows, including start and end time, frequency, and days of the week.

After automatic SQL tuning begins, the database performs the following steps:

1.    Identifies SQL candidates in the AWR for tuning

2.    Oracle Database analyzes statistics in AWR and generates a list of potential SQL statements that are eligible for tuning. These statements include repeating high-load statements that have a significant impact on the database.

The database tunes only SQL statements that have an execution plan with a high potential for improvement. The database ignores recursive SQL and statements that have been tuned recently (in the last month), parallel queries, DML, DDL, and SQL statements with performance problems caused by concurrency issues.

The database orders the SQL statements that are selected as candidates based on their performance impact. The database calculates the impact by summing the CPU time and the I/O times in AWR for the selected statement in the past week.

3.    Tunes each SQL statement individually by calling SQL Tuning Advisor

During the tuning process, the database considers and reports all recommendation types, but it can implement only SQL profiles automatically.

4.    Tests SQL profiles by executing the SQL statement

If a SQL profile is recommended, the database tests the new profile by executing the SQL statement both with and without the profile. If the performance improvement improves at least threefold, then the database accepts the SQL profile, but only if the ACCEPT_SQL_PROFILES task parameter is set to TRUE. Otherwise, the automatic SQL tuning reports merely report the recommendation to create a SQL profile.

5.    Optionally, implements the SQL profiles provided they meet the criteria of threefold performance improvement

The database considers other factors when deciding whether to implement the SQL profile. For example, the database does not implement a profile when the objects referenced in the statement have stale optimizer statistics. SQL profiles that have been implemented automatically show type is AUTO in the DBA_SQL_PROFILES view.

If the database uses SQL plan management, and if a SQL plan baseline exists for the SQL statement, then the database adds a new plan baseline when creating the SQL profile. As a result, the optimizer uses the new plan immediately after profile creation. See Chapter 15, "Using SQL Plan Management".

At any time during or after the automatic SQL tuning process, you can view the results using the automatic SQL tuning report. This report describes in detail all the SQL statements that were analyzed, the recommendations generated, and the SQL profiles that were automatically implemented.

Figure 17-1 shows the steps performed by the database during automatic SQL tuning.

Figure 17-1 Automatic SQL Tuning


Description of "Figure 17-1 Automatic SQL Tuning"

 

Chapter 15, "Using SQL Plan Management".

Using SQL Plan Baselines with SQL Tuning Advisor

When tuning SQL statements with SQL Tuning Advisor, if the advisor finds a tuned plan and verifies its performance to be better than a plan chosen from the corresponding SQL plan baseline, then it makes a recommendation to accept a SQL profile. When the SQL profile is accepted, the database adds the tuned plan to the corresponding SQL plan baseline. However, SQL Tuning Advisor does not verify existing unaccepted plans in the plan history.

In Oracle Database 11g, an automatically configured task runs SQL Tuning Advisor during a maintenance window. This task targets high-load SQL statements as identified by the execution performance data collected in the Automatic Workload Repository (AWR) snapshots. The automatic SQL tuning task implements the SQL profile recommendations made by SQL Tuning Advisor. Thus, the database automatically adds tuned plans to the SQL plan baselines of the identified high-load SQL statements.

 

 

 

 

QUESTION 56

You installed Oracle Database 11g and are performing a manual upgrade of the Oracle9i database. As a part of the

upgrade process, you execute the following script:

SQL>@utlu111i.sql

Which statement about the execution of this script is true?

A. It must be executed from the Oracle Database 11g environment.

B. It must be executed only after the SYSAUX tablespace has been created.

C. It must be executed from both the Oracle Database 11g and Oracle Database 9i environments.

D. It must be executed from the environment of the database that is being upgraded.

E. It must be executed only after AUTOEXTEND is set to ON for all existing tablespaces.

Answer: D

Explanation/Reference:

 

 

 

QUESTION 57

Exhibit #1:

Exhibit #2:

View the Exhibit exhibit1.In the CUSTOMERS_OBE table, when the value of CUST_STATE_PROVINCE is "CA", the value of COUNTRY_ID is "US."

View the Exhibit exhibit2 to examine the commands and query plans. The optimizer can sense 8 rows instead of 29 rows, which is the actual number of rows in the table. What can you do to make the optimizer detect the actual selectivity?

A. Change the STALE_PERCENT value for the CUSTOMERS_OBE table.

B. Set the STATISTICS_LEVEL parameter to TYPICAL.

C. Create extended statistics for the CUST_STATE_PROVINCE and CUSTOMERS_OBE columns.

D. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE.

Answer: C

Explanation/Reference:

 

 参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF94728

Managing Extended Statistics

DBMS_STATS enables you to collect extended statistics, which are statistics that can improve cardinality estimates when multiple predicates exist on different columns of a table, or when predicates use expressions. An extension is either a column group or an expression.

Oracle Database supports the following types of extended statistics:

·         Column group statistics

This type of extended statistics can improve cardinality estimates when multiple columns from the same table occur together in a SQL statement. See"Managing Column Group Statistics".

·         Expression statistics

This type of extended statistics improves optimizer estimates when predicates use expressions, for example, built-in or user-defined functions. 

Managing Column Group Statistics

When the WHERE clause of a query specifies multiple columns from a single table (multiple single column predicates), the relationship between the columns can strongly affect the combined selectivity for the column group.

For example, consider the customers table in the sh schema. The columns cust_state_province and country_id are related, with cust_state_provincedetermining the country_id for each customer. Suppose you query the customers table where the cust_state_province is California:

SELECT COUNT(*)

FROM   sh.customers

WHERE  cust_state_province = 'CA';

The preceding query returns the following value:

COUNT(*)

----------

    3341

Adding an extra predicate on the country_id column does not change the result when the country_id is 52790 (United States of America). Assume that you run the following query:

SELECT COUNT(*)

FROM   customers

WHERE  cust_state_province = 'CA'

AND    country_id=52790;

The preceding query returns the same value as the previous query:

COUNT(*)

----------

    3341

Assume that the country_id has a different value, such as 52775 (Brazil), as in the following query:

SELECT COUNT(*)

FROM   customers

WHERE  cust_state_province = 'CA'

AND    country_id=52775;

In this case the returned value is as follows:

COUNT(*)

----------

       0

With individual column statistics, the optimizer has no way of knowing that the cust_state_province and the country_id columns are related. By gathering statistics on these columns as a group (column group), the optimizer has a more accurate selectivity value for the group, instead of having to generate the value based on the individual column statistics.

You can create column groups manually by using the DBMS_STATS package. You can use this package to create a column group, get the name of a column group, or delete a column group from a table.

 

 

 

QUESTION 58

Exhibit:

View the Exhibit for the object interdependency diagram. The PRODUCTS table is used to create the PRODCAT_VW

view.PRODCAT_VW is used in the GET_DATA procedure.GET_DATA is called in the CHECK_DATA function.

A new column PROD_QTY is added to the PRODUCTS table. How does this impact the status of the dependent objects?

A. All dependent objects remain valid.

B. Only the view becomes invalid and gets automatically revalidated the next time it is used.

C. Only the procedure and function become invalid and get automatically revalidated the next time they are called.

D. Only the procedure and function become invalid and must be recompiled.

Answer: A

Explanation/Reference:

 

 

QUESTION 59

Exhibit:

View the Exhibit to examine the Automatic SQL Tuning result details. Which action would you suggest for the selected SQL statement in the Exhibit?

A. Run the Access Advisor for the SQL statement.

B. Run the Segment Advisor for recommendations.

C. Collect statistics for the related objects.

D. Accept the recommended SQL profile.

Answer: A

Explanation/Reference:

 

SQL 优化指导会分别考察指导任务中包括的每个SQL 语句。创建一个新索引可能会对查询有帮助,但也可能增加DML 的响应时间。因此,应使用SQL 访问指导检查建议的索引或其它对象对工作量(一组SQL 语句)的影响,以确定性能是否有净提高。

 

QUESTION 60

Exhibit:

View the Exhibit and examine the output.

You executed the following command to enable Flashback Data Archive on the EXCHANGE_RATE table:

ALTER TABLE exchange_rate FLASHBACK ARCHIVE;

What is the outcome of this command?

A. The Flashback Archive is created on the same tablespace where the tables are stored.

B. The Flashback Archive is created on the SYSAUX tablespace.

C. The command generates an error because no Flashback Archive name is specified and there is no default

Flashback Archive.

D. The table uses the default Flashback Archive.

Answer: C

Explanation/Reference:

 

实验验证:

1、首先创建一个表空间用于存储闪回数据归档

sys@TEST1107> create tablespace fla_tbs1

  2  datafile '/u01/app/oracle/oradata/test1107/fla_tbs01.dbf' size 10M;

 

Tablespace created.

2、创建闪回数据归档

sys@TEST1107>  create flashback archive fla1 tablespace fla_tbs1 quota 10M retention 1 year;

 

Flashback archive created.

3、查询有哪些闪回数据归档以及其状态。

sys@TEST1107> select FLASHBACK_ARCHIVE_NAME,STATUS from DBA_FLASHBACK_ARCHIVE;

 

FLASHBACK_ARCHIVE_NAME         STATUS

------------------------------ -------

FLA1

4、对scott.dept表启用闪回数据归档,报错,因为没有指定默认闪回归档。

sys@TEST1107> alter table scott.dept flashback archive;

alter table scott.dept flashback archive

*

ERROR at line 1:

ORA-55608: Default Flashback Archive does not exist

 

5、将FLA1指定为默认闪回数据归档。

sys@TEST1107> alter flashback archive FLA1 set default;

 

Flashback archive altered.

 

6、查询,此时须注意,status栏位下面的有DEFAULT的状态。

sys@TEST1107> select FLASHBACK_ARCHIVE_NAME,STATUS from DBA_FLASHBACK_ARCHIVE;

 

FLASHBACK_ARCHIVE_NAME         STATUS

------------------------------ -------

FLA1                           DEFAULT

 

7、对表启用闪回数据归档,成功。

sys@TEST1107> alter table scott.dept flashback archive;

 

Table altered.

 

对于题中的显示,FLA1的状态栏位为空值,即没有指定FLA1为默认的闪回数据归档.执行命令会出错; 

 

B答案:这里没有说闪回数据归档被创建在哪个表空间,可以根据DBA_FLASHBACK_ARCHIVE_TS;来查询,此处没有提供。

sys@TEST0924> select * from DBA_FLASHBACK_ARCHIVE_TS;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB

------------------------- ------------------ ------------------------------ ---------------

FLA1 1 FLA_TBS1 10

 

 

 

QUESTION 61

You issued the following command to mount the DATA disk group in restricted mode:

ALTER DISKGROUP data MOUNT RESTRICT;

What is the implication of this command?

A. The client RDBMS instance can access the file as a read-only file.

B. A new disk cannot be added to a disk group.

C. A disk in a disk group can be taken offline.

D. The client RDBMS instance cannot access the files in the disk group.

Answer: D

Explanation/Reference:

 

MOUNT Specify MOUNT to mount the disk groups in the local Automatic Storage Management instance. Specify ALL MOUNT to mount all disk groups specified in theASM_DISKGROUPS initialization parameter. File operations can only be performed when a disk group is mounted.

RESTRICTED | NORMAL Use these clauses to determine the manner in which the disk groups are mounted.

·         In the RESTRICTED mode, the disk group is mounted in single-instance exclusive mode. No other ASM instance in the same cluster can mount that disk group. In this mode the disk group is not usable by any ASM client.

·         In the NORMAL mode, the disk group is mounted in shared mode, so that other ASM instances and clients can access the disk group. This is the default.

 

 

QUESTION 62

Your company wants to upgrade the current production database to the RAC environment. To perform testing before migrating to the RAC environment, you performed the workload capture on the production database to record the peak workload. You set up the test RAC database and want to replay the recorded workload on the test machine. Note the following steps that you may require to replay the database workload:

1) Preprocess the captured workload.

2) Restart the database in RESTRICTED mode.

3) Set up the Replay Clients.

4) Restore the test database to the point when the capture started.

5) Remap connections.

Arrange the steps required in the correct sequence to accomplish this task on the test machine.

A. 1, 4, 5, 3 (2 is not required.)

B. 1, 4, 3, 5 (2 is not required.)

C. 1, 2, 4, 5 (3 is not required.)

D. 2, 1, 5, 3, 4

E. 1, 2, 4, 5, 3

Answer: A

Explanation/Reference:

 

数据库重放步骤:

捕获工作量--预处理工作量--重放工作量--还原数据库----连接映射--设置客户机--分析

 

 

QUESTION 63

You are working on the APPDB database as a DBA. The database has a default Flashback Data Archive DFLA1 created on it.You issued the following command to drop the Flashback Archive FLA1:

DROP FLASHBACK ARCHIVE fla1;

What is the effect of this command?

B. The Flashback Archive FLA1 is dropped only if it is empty.

C. The tablespace having Flashback Archive is dropped.

D. The Flashback Archive FLA1 is dropped after moving the existing data in it to the default Flashback Archive.

E. The Flashback Data Archive and historical data are dropped.

Answer: D

Explanation/Reference:

 

管理员用户身份,删除 FLA1 闪回数据归档。

:删除闪回数据归档也会删除内部防篡改历史记录表。由于审计和安全的要求,无法直接删除此表。由于表空间可能包含其它数据,因此删除闪回数据归档不会删除它所在的表空间。

 

参考:http://blog.csdn.net/rlhua/article/details/12222819

 

DROP FLASHBACK ARCHIVE

Purpose

Use the DROP FLASHBACK ARCHIVE clause to remove a flashback data archive from the system.This statement removes the flashback data archive and all the historical data in it, but does not drop the tablespaces that were used by the flashback data archive.

Prerequisites

You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to drop a flashback data archive.

flashback_archive

Specify the name of the flashback data archive you want to drop.

官方参考:http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_8016.htm#SQLRF20010

 

 

QUESTION 64

You need to configure fine-grained access control to external network resources from within your database. You create an access control list (ACL) using the DBMS_NETWORK_ACL_ADMIN package.

Which statement is true regarding the ACL created?

A. It is a list of users and network privileges stored in the XML file according to which a group of users can connect to

one or more hosts.

B. It is a list of users and network privileges stored in the data dictionary according to which a group of users can

connect to one or more hosts.

C. It is a list of remote database links stored in the XML file that are available to the users of the database.

D. It is the list of the host names or the IP addresses stored in the data dictionary that can connect to your database

through PL/SQL network utility packages such as UTL_TCP.

Answer: A

Explanation/Reference:

 

 

The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network Access Control List (ACL).

Create the Access Control List and Its Privilege Definitions

Use the DBMS_NETWORK_ACL_ADMIN.CREATE_ACL procedure to create the content of the access control list. It contains a name of the access control list, a brief description, and privilege settings for one user or role that you want to associate with the access control list. In an access control list, privileges for each user or role are grouped together as an access control entry (ACE). An access control list must have the privilege settings for at least one user or role.

Note:

You cannot import or export the access control list settings by using the Oracle Database import or export utilities such as Oracle Data Pump.

 

 

QUESTION 65

Following is the list of locations in random order where oranfstab can be placed.

1./etc/mtab

2.$ORACLE_HOME/dbs/oranfstab

3./etc/oranfstab

What is the sequence in which Direct NFS will search the locations?

A. 1, 2, 3

B. 3, 2, 1

C. 1, 3, 2

D. 2, 3, 1

Answer: D

Explanation/Reference:

 

参考:http://docs.oracle.com/cd/E11882_01/install.112/e41961/storage.htm#CDEDBEDA

Mounting NFS Storage Devices with Direct NFS Client

Direct NFS Client determines mount point settings to NFS storage devices based on the configurations in /etc/mtab, which are changed with configuring the /etc/fstab file.

Direct NFS Client searches for mount entries in the following order:

1.    $ORACLE_HOME/dbs/oranfstab

2.    /etc/oranfstab

3.    /etc/mtab

Direct NFS Client uses the first matching entry found.

Oracle Database is not shipped with Direct NFS Client enabled by default. To enable Direct NFS Client, complete the following steps:

1.    Change the directory to $ORACLE_HOME/rdbms/lib.

2.    Enter the following command:

make -f ins_rdbms.mk dnfs_on

 

 

QUESTION 66

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 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.

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 overwrites data files of the target database because a different location for data files is not mentioned for the duplicate database.

Answer: D

Explanation/Reference:

 

官方参考:http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta020.htm#RCMRF90163

NOFILENAMECHECK

Prevents RMAN from checking whether the data files and online redo logs files of the source database are in use when the source database files share the same names as the duplicate database files. You are responsible for determining that the duplicate operation does not overwrite useful data.

This option is necessary when you are creating a duplicate database in a different host that has the same disk configuration, directory structure, and file names as the host of the source database. For example, assume that you have a small database located in the /dbs directory of srchost:

/oracle/dbs/system_prod1.dbf

/oracle/dbs/users_prod1.dbf

/oracle/dbs/rbs_prod1.dbf

Assume that you want to duplicate this database to desthost, which has the same file system/oracle/dbs/*, and you want to use the same file names in the duplicate database as in the source database. In this case, specify the NOFILENAMECHECK option to avoid an error message. Because RMAN is not aware of the different hosts, RMAN cannot determine automatically that it should not check the file names.

If duplicating a database on the same host as the source database, then ensure that NOFILENAMECHECK is not set. Otherwise, RMAN can potentially overwrite and corrupt the target database data files, temp files, or online logs. It may also signal the following error:

RMAN-10035: exception raised in RPC:

ORA-19504: failed to create

            file "/oracle/dbs/tbs_01.f"

ORA-27086: skgfglk: unable to lock file - already in use

SVR4 Error: 11: Resource temporarily unavailable

Additional information: 8

RMAN-10031: ORA-19624 occurred during call to

DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE


 

QUESTION 67

What are the recommendations for Oracle Database 11g installation to make it Optimal Flexible Architecture (OFA)-compliant? (Choose all that apply.)

A. Flash recovery area and data file location should be on separate disks.

B. ORACLE_BASE should be set explicitly.

C. An Oracle base should have only one Oracle home created in it.  (可以创建多个oralce_home,但不同的OS用户创建的oracle_home必须不同位置)

D. Flash recovery area and data file location should be created under Oracle base in a non-Automatic Storage Management (ASM) setup. (在非asm状态下,闪回区和数据文件需要创建在oracle_base)

Answer: ABD

Explanation/Reference:

 

 

 

QUESTION 68

You are managing an Oracle Database 11g instance. You want to create a duplicate database for testing purpose.

What are the prerequisites for performing the active database duplication? (Choose all that apply.)

A. The source database must be run in ARCHIVELOG mode if the database is open.

B. A net service name should be set up and a listener configured with the target as well as the source database.

C. The source database backup must be copied over the net for test database.

D. The source database must be shut down cleanly if the database is in mounted state.

Answer: ABD

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/13509067

官方参考:http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta020.htm#RCMRF90152

通过使用Oracle Enterprise Manager RMAN DUPLICATE命令的FROM ACTIVE DATABASE子句,可以指示源数据库直接将映像副本和归档日志副本复制到辅助实例。

此操作不需要使用备份。RMAN 作为TARGET连接到源数据库实例并作为AUXILIARY连接到辅助实例。

通过实例间的网络连接将数据库文件从源数据库复制到目标数据库或辅助实例。RMAN 随后使用内存脚本(仅包含在内存中)完成恢复并打开数据库。

Active duplicationRMAN duplicates the files directly from either an open or mounted database.

A,从活动数据库复制,不需要拷贝备份。错误。

B,源数据库必须是归档模式,正确。

C,如果数据库是mount状态,那么源数据库一定是shut down cleanly。正确。

D,需要建立网络服务名和配置监听。正确。

Prerequisites Specific to Active Database Duplication

When you execute DUPLICATE with FROM ACTIVE DATABASE, at least one normal target channel and at least one AUXILIARY channel are required.

When you connect RMAN to the source database as TARGET, you must specify a password, even if RMAN uses operating system authentication. The source database must be mounted or open. If the source database is open, then archiving must be enabledIf the source database is not open, then it must have been shut down consistently.

When you connect RMAN to the auxiliary instance, you must provide a net service name. This requirement applies even if the auxiliary instance is on the local host.

The source database and auxiliary instances must use the same SYSDBA password, which means that both instances must have password files. You can create the password file with a single password so you can start the auxiliary instance and enable the source database to connect to it.

The DUPLICATE behavior for password files varies depending on whether your duplicate database acts as a standby database. If you create a duplicate database that is not a standby database, then RMAN does not copy the password file by default. You can specify the PASSWORD FILE option to indicate that RMAN should overwrite the existing password file on the auxiliary instance. If you create a standby database, then RMAN copies the password file to the standby host by default, overwriting the existing password file. In this case, the PASSWORD FILE clause is not necessary.

You cannot use the UNTIL clause when performing active database duplication. RMAN chooses a time based on when the online data files have been completely copied, so that the data files can be recovered to a consistent point in time.

 

 

 

QUESTION 69

Which two kinds of failures make the Data Recovery Advisor (DRA) generate a manual checklist? (Choose two.)

A. failures because a data file is renamed by error

B. failures due to loss of connectivity-for example, an unplugged disk cable

C. failures that require no archive logs to be applied for recovery

D. failures when no standby database is configured

Answer: AB

Explanation/Reference:

 

“View and Manage Failures(查看和管理故障)页上单击“Advise(建议)按钮后,数据恢复指导会生成一个手动核对清单。可显示两种类型的故障。

? 需要人工干预的故障:例如,未插入磁盘电缆的连接故障

? 可通过撤消以前的错误操作快速修复的故障:例如,如果错误地重命名了数据文件,则与从备份启动RMAN 还原相比,将该文件重命名回以前的名称可以更快地进行修复。

 

 

QUESTION 70

The following databases are registered in the base recovery catalog: PROD1, PROD2, and PROD3. The database user CATOWNER owns the base recovery catalog. You want a new user VPC1 to have access to only the PROD1  and create a virtual private catalog.

Given below are some of the commands required to achieve this:

1.SQL> GRANT recovery_catalog_owner TO vpc1;

2.RMAN> CONNECT CATALOG vpc1/password@catdb;

3.RMAN> GRANT CATALOG FOR DATABASE prod1 TO vpc1;

4.RMAN> CONNECT CATALOG catowner/password@catdb;

5.RMAN> CREATE VIRTUAL CATALOG;

What is the correct sequence in which the commands have to be executed?

A. 1, 4, 3, 2, 5

B. 2, 3, 4, 5, 1

C. 4, 5, 2, 3, 1

D. 1, 4, 2, 3, 5

E. 1, 4, 5, 2, 3

Answer: A

Explanation/Reference:

 

参考:http://blog.csdn.net/rlhua/article/details/13169205

这道题考创建虚拟专用目录以及授权限制VPC1用户只能访问PROD1

步骤如下:

以下是使用rman虚拟专用目录步骤:

1. 创建RMAN 基本目录:

RMAN> CONNECT CATALOG catowner/oracle@catdb

RMAN> CREATE CATALOG;

2. RECOVERY_CATALOG_OWNER授予给VPC 所有者:

SQL> CONNECT SYS/oracle@catdb AS SYSDBA

SQL> GRANT RECOVERY_CATALOG_OWNER to vpcowner;

3a.REGISTER授予给VP所有者:

RMAN> CONNECT CATALOG catowner/oracle@catdb

RMAN> GRANT REGISTER DATABASE TO vpcowner;

3b.或将CATALOG FOR DATABASE授予给VPC 所有者:

RMAN>GRANT CATALOG FOR DATABASE db10g TO vpcowner;

 

 

4a.11g客户机创建虚拟目录:

RMAN> CONNECT CATALOGvpcowner/oracle@catdb

RMAN> CREATE VIRTUAL CATALOG;

4b.或者为11g之前的客户机创建虚拟目录:

SQL> CONNECT vpcowner/oracle@catdb

SQL> exec catowner.dbms_rcvcat.create_virtual_catalog;

5. 在目录中注册新数据库:

RMAN> CONNECT TARGET / CATALOG vpcowner/oracle@catdb

RMAN> REGISTER DATABASE;

6. 使用虚拟目录:

RMAN> CONNECT TARGET / CATALOG vpcowner/oracle@catdb;

RMAN> BACKUP DATABASE;

 

可以为数据库组和用户组创建虚拟专用RMAN 目录。

1. 目录所有者将创建基本目录。

2. 目录数据库的DBA 可以创建拥有虚拟专用目录(VPC) 的用户,并授予其RECOVERY_CATALOG_OWNER权限。

3. 基本目录的所有者可为VPC 所有者授予访问先前注册的数据库的权限或授予REGISTER权限。GRANT CATALOG命令如下:

GRANT CATALOG FOR DATABASE prod1, prod2 TO vpcowner;

GRANT REGISTER命令如下:

GRANT REGISTER DATABASE TO vpcowner;

然后,虚拟目录所有者可连接到特定目标的目录,或者注册一个目标数据库。配置完VPC 之后,VPC 所有者可像使用标准基本目录一样使用该目录。

 

4. 创建虚拟专用目录。

a. 如果目标数据库是Oracle Database 11g数据库并且RMAN 客户机是11g客户机,则可使用RMAN 命令:

CREATE VIRTUAL CATALOG;

b. 如果目标数据库是Oracle Database 10g发行版2 或更早版本(使用兼容客户机),则必须从SQL*Plus 执行提供的过程:

BASE_CATALOG_OWNER.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG;

5. 使用VPC 所有者登录名连接到该目录,将其作为常规目录使用。

6. 虚拟目录所有者只能查看其有访问权限的那些数据库。对于大多数RMAN 操作,你还需要针对目标数据库的SYSDBASYSOPER权限。

 

QUESTION 71

Exhibit #1:

Exhibit #2:

View the Exhibits exhibit1 and exhibit2.

Both the processes use PROG_1 as the job template that is already available. The time taken by the jobs are recorded in the TEST_LOG table. While comparing the time taken to create the jobs, the process in exhibit1 takes less time than the process in exhibit2.

What is the reason for this?

A. It updates several tables in the SYSTEM tablespace instead of creating new tables.

B. It creates jobs temporarily in memory only.

C. It creates less metadata for the jobs.

D. It writes the job metadata to disk in compressed format.

Answer: C

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e25494/schedover.htm#ADMIN13368

Exhibit #1job_style=>'lightweight'可知,此作业是轻量型作业。

Lightweight Jobs

Use lightweight jobs when you have many short-duration jobs that run frequently. Under certain circumstances, using lightweight jobs can deliver a small performance gain.

Lightweight jobs have the following characteristics:

·         Unlike regular jobs, they are not schema objects.

·         They have significantly better create and drop times over regular jobs because they do not have the overhead of creating a schema object.

·         They have lower average session create time than regular jobs.

·         They have a small footprint on disk for job metadata and run-time data.

You designate a lightweight job by setting the job_style job attribute to 'LIGHTWEIGHT'. The other job style is 'REGULAR', which is the default.

Like programs and schedules, regular jobs are schema objects. In releases before Oracle Database 11g Release 1, the only job style supported by the Scheduler was regular.

A regular job offers the maximum flexibility but does entail some overhead when it is created or dropped. The user has fine-grained control of the privileges on the job, and the job can have as its action a program or a stored procedure owned by another user.

If a relatively small number of jobs that run infrequently need to be created, then regular jobs are preferred over lightweight jobs.

A lightweight job must reference a program object (program) to specify a job action. The program must be already enabled when the lightweight job is created, and the program type must be either 'PLSQL_BLOCK' or 'STORED_PROCEDURE'. Because lightweight jobs are not schema objects, you cannot grant privileges on them. A lightweight job inherits privileges from its specified program. Thus, any user who has a certain set of privileges on the program has corresponding privileges on the lightweight job.

 

QUESTION 72

Which two prerequisites are needed for performing workload capture and replay? (Choose two.)

A. Close all sessions performing queries using database links.

B. running the database in shared server mode

C. The database on which the workload is replayed has to be a restore of the original database to a specific SCN.

D. setting up the directory to capture the workload

Answer: CD

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/14053709

捕获注意事项

在工作量记录的计划阶段要执行以下任务:

? 检查数据库备份策略,确保在记录开始时数据库可被还原为StartSCN

? 计划捕获期间:根据应用情况和峰值期间选择捕获期间。可以使用现有的可管理性功能,如自动工作量资料档案库(AWR) 和活动会话历史记录(ASH) ,根据工作量历史记录选择一个恰当的期间。应谨慎计划捕获的开始时间,因为建议的操作是在捕获开始前关闭并重新启动数据库。

? 指定工作量捕获数据的位置。必须设置用于存储工作量捕获数据的目录。应提供充足的磁盘空间,因为磁盘空间不足时记录会停止。但是,在停止之前捕获的所有内容仍可用于重放。

? 定义捕获过滤器,过滤掉不捕获的用户会话。可以指定记录过滤器以跳过不应捕获的会话。

? 数据库重放功能没有引入任何新的权限或用户角色。记录用户和重放用户必须具有SYSDBA 权限或SYSOPER权限。这是因为仅具备SYSOPER权限或SYSDBA 权限的用户才可以启动或关闭开始记录的数据库。还应分配正确的操作系统(OS) 权限,以便用户能够访问记录、重放目录以及操作这些目录下的文件。

重放注意事项

预处理阶段是必需的针对指定数据库版本的一次性操作。创建了必需的元数据以后,可以按需要多次重放工作量。

必须还原重放数据库,以便与工作量捕获开始时的捕获数据库匹配。成功的重放取决于应用程序事务处理,该事务处理要访问与捕获系统上的数据相同的应用程序数据。可以选择使用时间点恢复、闪回和导入/ 导出来还原应用程序数据。

 

 

QUESTION 73

Which two statements are true regarding Health Monitor checks in Oracle Database 11g? (Choose two.)

A. Health Monitor checks can be used to scan the contents of the redo log and archive logs for accessibility and corruption.

B. Health Monitor checks can be used to verify the integrity of database files and report failures if these files are inaccessible, corrupt or inconsistent.

C. Health Monitor checks can be used to verify the contents of dictionary entries for each dictionary object and fix it automatically.

D. Health Monitor checks are always initiated manually when there is some critical error.

Answer: AB

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e25494/diag.htm#ADMIN11273

Types of Health Checks

Health monitor runs the following checks:

·         DB Structure Integrity Check—This check verifies the integrity of database files and reports failures if these files are inaccessible, corrupt or inconsistent. If the database is in mount or open mode, this check examines the log files and data files listed in the control file. If the database is inNOMOUNT mode, only the control file is checked.

·         Data Block Integrity Check—This check detects disk image block corruptions such as checksum failures, head/tail mismatch, and logical inconsistencies within the block. Most corruptions can be repaired using Block Media Recovery. Corrupted block information is also captured in theV$DATABASE_BLOCK_CORRUPTION view. This check does not detect inter-block or inter-segment corruption.

·         Redo Integrity Check—This check scans the contents of the redo log for accessibility and corruption, as well as the archive logs, if available. The Redo Integrity Check reports failures such as archive log or redo corruption.

·         Undo Segment Integrity Check—This check finds logical undo corruptions. After locating an undo corruption, this check uses PMON and SMON to try to recover the corrupted transaction. If this recovery fails, then Health Monitor stores information about the corruption in V$CORRUPT_XID_LIST. Most undo corruptions can be resolved by forcing a commit.

·         Transaction Integrity Check—This check is identical to the Undo Segment Integrity Check except that it checks only one specific transaction.

·         Dictionary Integrity Check—This check examines the integrity of core dictionary objects, such as tab$ and col$. It performs the following operations:

o    Verifies the contents of dictionary entries for each dictionary object.

o    Performs a cross-row level check, which verifies that logical constraints on rows in the dictionary are enforced.

o    Performs an object relationship check, which verifies that parent-child relationships between dictionary objects are enforced.

The Dictionary Integrity Check operates on the following dictionary objects:

tab$clu$fet$uet$seg$undo$ts$file$obj$ind$icol$col$user$con$cdef$ccol$bootstrap$objauth$ugroup$tsq$syn$view$,typed_view$superobj$seq$lob$coltype$subcoltype$ntab$refcon$opqtype$dependency$access$viewcon$icoldep$dual$sysauth$objpriv$,defrole$, and ecol$.

 

 

QUESTION 74

Examine the section of the Health Check report given below:

DBMS_HM.GET_RUN_REPORT('HM_RUN_1061')

------------------------

Run Name : HM_RUN_1061

Run Id : 1061

Check Name : Data Block Integrity Check

Mode : REACTIVE

Status : COMPLETED

Start Time : 2007-05-12 22:11:02.032292 -07:00

End Time : 2007-05-12 22:11:20.835135 -07:00

Error Encountered : 0

Source Incident Id : 7418

Number of Incidents Created :0

Which two statements are true regarding the Health Check report? (Choose two.)

A. Health Check was performed to verify the integrity of database files and report failures.

B. Health Check was performed to check the disk image block corruptions.

C. Health Check was performed to check interblock and intersegment corruption.

D. Health Check was performed manually.

E. Health Check was performed by the Health Monitor automatically in response to a critical error.

Answer: BD

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e25494/diag.htm#ADMIN11274

Viewing Reports Using DBMS_HM

You can view Health Monitor checker reports with the DBMS_HM package function GET_RUN_REPORT. This function enables you to request HTML, XML, or text formatting. The default format is text, as shown in the following SQL*Plus example:

SET LONG 100000

SET LONGCHUNKSIZE 1000

SET PAGESIZE 1000

SET LINESIZE 512

SELECT DBMS_HM.GET_RUN_REPORT('HM_RUN_1061') FROM DUAL;

 DBMS_HM.GET_RUN_REPORT('HM_RUN_1061')

-----------------------------------------------------------------------

 

 Run Name                     : HM_RUN_1061

 Run Id                       : 1061

 Check Name                   : Data Block Integrity Check

 Mode                         : REACTIVE

 Status                       : COMPLETED

 Start Time                   : 2007-05-12 22:11:02.032292 -07:00

 End Time                     : 2007-05-12 22:11:20.835135 -07:00

 Error Encountered            : 0

 Source Incident Id           : 7418

 Number of Incidents Created  : 0

 

To obtain a list of health check names, run the following query:

sys@TEST1107> SELECT name FROM v$hm_check WHERE internal_check='N' ;

 

NAME

----------------------------------------------------------------

DB Structure Integrity Check

CF Block Integrity Check

Data Block Integrity Check

Redo Integrity Check

Transaction Integrity Check

Undo Segment Integrity Check

Dictionary Integrity Check

ASM Allocation Check

 

8 rows selected.

 

 

 

QUESTION 75

Which two statements are true regarding hot patching? (Choose two.)

A. It can detect conflicts between two online patches.

B. It requires relinking of the Oracle binary.

C. It is available for installing all patches on all platforms.

D. It works only in a single database instance environment.

E. It does not require database instance shutdown.

Answer: AE

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/16574625

安装热补丁程序

? 应用热补丁程序不需要关闭实例、重新链接Oracle二进制文件或重新启动实例。

? OPatch 可用于安装或卸载热补丁程序。

? OPatch 可以检测两个热补丁程序之间以及热补丁程序与常规补丁程序之间的冲突。

 

 

QUESTION 76

Exhibit:

View the Exhibit for some of the current parameter settings. A user logs in to the HR schema and issues the following

commands:

SQL> CREATE TABLE emp

(empno NUMBER(3),

ename VARCHAR2(20),

sal NUMBER(8,2));

SQL> INSERT INTO emp(empno,ename) VALUES(1,'JAMES'); At this moment, a second user also logs in to the HR schema and issues the following command:

SQL> ALTER TABLE emp MODIFY sal NUMBER(10,2);

What happens in the above scenario?

A. The second user's session immediately produces the resource busy error.

B. The second user's command executes successfully.

C. The second user's session waits for a time period before producing the resource busy error.

D. A deadlock is created.

Answer: C

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams074.htm#REFRN10267

DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

If a lock is not acquired before the timeout period expires, then an error is returned.

 

 

QUESTION 77

You want the Automatic SQL Tuning process to stop accepting and implementing the recommended SQL profiles automatically. Which action would you perform to achieve this?

A. Edit the automatic maintenance window group configuration.

B. Use the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETERS procedure to set ACCEPT_SQL_PROFILES to

FALSE.

C. Set the CURSOR_SHARING parameter to EXACT for the database instance.

D. Set the SQLTUNE_CATEGORY parameter to DEFAULT for the database instance.

Answer: B

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm#PFGRF94849

 

if want to stop accepting and implementing the recommended SQL profiles automatically

set ACCEPT_SQL_PROFILES to FALSE.

ACCEPT_SQL_PROFILE

Specifies whether to accept SQL profiles automatically.

To configure automatic SQL tuning: 

1.    Start SQL*Plus, and connect to the database with DBA privileges (or connect as SYS if you plan to run EXECUTE_AUTO_TUNING_TASK).

2.    Run the DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER procedure.

The following example configures the automatic SQL tuning task to automatically accept SQL profiles recommended by SQL Tuning Advisor:

BEGIN

  DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(

    parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');

END;

/

 

 

QUESTION 78

In which situations will the ASM metadata backup help you recover the ASM disk in a disk group? (Choose all that

apply.)

A. when one or more file directory paths are accidentally deleted from an ASM disk group

B. when one of the disks in a disk group is accidentaly unplugged

C. when the data file on an ASM disk group gets corrupted

D. when one or more disks in an ASM disk group are lost

Answer: AD

Explanation/Reference:

 

 

 

QUESTION 79

The Database Resource Manager is automatically enabled in the maintenance window that runs the Automated

Maintenance Task. What is the reason for this?

A. to prevent the Automated Maintenance Tasks from consuming excessive amounts of system resources

B. to prevent the creation of an excessive number of scheduler job classes

C. to allow resource sharing only among the Automated Maintenance Tasks in the maintenance window

D. to allow the Automated Maintenance Tasks to use system resources without any restriction

Answer: A

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/13092965

自动维护任务依赖于在维护窗口期间启用的资源管理器。维护窗口打开时,会自动设置DEFAULT_MAINTENANCE_PLAN资源管理器计划,以控制自动维护任务使用的CPU 数量。

a答案:防止自动维护任务消耗过多的系统资源,正确。

 

 

QUESTION 80

You plan to control the sessions performing a huge number of I/O operations. Your requirement is to kill the session when it exceeds a specified number of I/Os. Which statement describes a solution to the above?

A. Add directives to the Automatic Database Diagnostic Monitor (ADDM).

B. Modify the profile for the targeted users for which control needs to be imposed.

C. Implement the database resource manager to add the SWITCH_IO_REQS and SWITCH_GROUP directives.

D. Set a threshold for the default system-defined moving window baseline.

Answer: C

Explanation/Reference:

http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN11868

SWITCH_IO_REQS

Specifies the number of I/O requests that a session can execute before an action is taken. Default is UNLIMITED. The action is specified by SWITCH_GROUP.

 

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                => 'DAYTIME',
   GROUP_OR_SUBPLAN    => 'OLTP',
   COMMENT             => 'OLTP group',
   MGMT_P1             => 75,
   SWITCH_GROUP        => 'LOW_GROUP',
   SWITCH_IO_REQS      => 10000,
   SWITCH_IO_MEGABYTES => 2500,
   SWITCH_FOR_CALL     => TRUE);
END;
/

 

SWITCH_GROUP

Specifies the consumer group to which a session is switched if switch criteria are met. If the group name is 'CANCEL_SQL', then the current call is canceled when switch criteria are met. If the group name is 'KILL_SESSION', then the session is killed when switch criteria are met. Default isNULL.

If the group name is 'CANCEL_SQL', the SWITCH_FOR_CALL parameter is always set to TRUE, overriding the user-specified setting.



BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN             => 'DAYTIME',
   GROUP_OR_SUBPLAN => 'OLTP',
   COMMENT          => 'OLTP group',
   MGMT_P1          => 75,
   SWITCH_GROUP     => 'KILL_SESSION',
   SWITCH_TIME      => 60);
END;
/

 

 

QUESTION 81

Examine the following PL/SQL block:

DECLARE

my_plans pls_integer;

BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id => ?9twu5t2dn5xd?;END;

Which statement is true about the plan being loaded into the SQL plan baseline by the above command?

A. It is loaded with the ACCEPTED status.

B. It is loaded with the FIXED status.

C. It is not loaded with the ACCEPTED status.

D. It is not loaded with the ENABLED status.

Answer: A

Explanation/Reference:

http://blog.csdn.net/rlhua/article/details/16860565

 

 

QUESTION 82

You performed the RMAN database backup having a backupset key number 231 with the KEEP FOREVER option.

After some days, you want to change the status of the database backup and you issued the following command:

RMAN>CHANGE BACKUPSET 231 NOKEEP;

What is the implication of this command?

A. The backup overrides the backup retention policy.

B. the backup becomes eligible for deletion according to the existing retention policy

C. The backup is marked unavailable.

D. The backup is deleted.

Answer: B

Explanation/Reference:

 

 

 

QUESTION 83

The Oracle Database 11g database is running in the ARCHIVELOG mode. The archived redo log files are stored on

three locations. The Flash Recovery Area is one of the locations. The details are given below:

LOG_ARCHIVE_DEST_1 = 'LOCATION = /disk1/archive'

LOG_ARCHIVE_DEST_2 = 'SERVICE = stdb1'

DB_RECOVERY_FILE_DEST = '/u01/oradata'

Examine the following RMAN command issued to set the deletion policy for archived log files:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO sbt;

Which statement is true regarding what this command accomplishes?

A. All archived redo log files backed up at least twice to tape are deleted from the flash recovery area.

B. All archived redo log files in local archiving destinations and the flash recovery area backed up at least twice to tape are eligible for deletion.

C. All archived redo log files backed up at least once to tape are eligible for deletion.

D. All archived redo log files backed up at least twice to tape are deleted.

Answer: B

Explanation/Reference:

参考:归档重做日志删除策略:http://blog.csdn.net/rlhua/article/details/12306189

可以使用CONFIGURE ARCHIVELOG DELETION POLICY命令指定归档重做日志何时才能删除。该删除策略适用于所有归档目标,包括FRA

归档重做日志可由数据库自动删除,或因用户启动的RMAN 命令而删除:

? 只有FRA 中的日志可由数据库自动删除。对于FRA 中的归档重做日志文件,数据库将尽可能长地保留这些文件,并在需要额外磁盘空间时自动删除符合条件的日志。

? 发出BACKUP ... DELETE INPUTDELETE ARCHIVELOG时,可以从任何位置(无论在FRA 内部还是外部)手动删除符合条件的日志。

? 默认值为:CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

简化了多组件环境中的归档日志管理

当归档日志由多个组件(例如Streams)使用时,此功能简化了归档日志管理。它还提高了当快速恢复区中的归档日志丢失或无法访问时,备份归档日志时的可用性。

增强了删除策略的配置

仅当任何必需的组件(如Data GuardStreamsFlashback Database,等等)都不需要归档重做日志时,才能将归档重做日志删除。配置了归档日志删除策略后,该配置会应用到所有归档目标,包括FRABACKUP... DELETE INPUTDELETE... ARCHIVELOG都使用此配置,FRA 也是。

备份恢复区时,如果FRA 中的归档重做日志无法访问或已损坏,则RMAN 可故障转移到其它归档重做日志目标位置。

 

 

QUESTION 84

Examine the following PL/SQL block:

SET SERVEROUTPUT ON

SET LONG 10000

ECLARE report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE();

DBMS_OUTPUT.PUT_LINE(report);

END;

Which statement describes the effect of the execution of the above PL/SQL block?

A. The nonaccepted plans in the SQL Management Base are verified with the existing plan baselines.

B. The plan baselines are verified with the SQL profiles.

C. All fixed plan baselines are converted into nonfixed plan baselines.

D. All the nonaccepted SQL profiles are accepted into the plan baseline.

Answer: A

Explanation/Reference:

http://blog.csdn.net/rlhua/article/details/16846383

参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_spm.htm#ARPLS68168

参考:http://blog.csdn.net/rlhua/article/details/16369811

EVOLVE_SQL_PLAN_BASELINE Function

This function evolves SQL plan baselines associated with one or more SQL statements. A SQL plan baseline is evolved when one or more of its non-accepted plans is changed to an accepted plan or plans. If interrogated by the user (parameter verify = 'YES'), the execution performance of each non-accepted plan is compared against the performance of a plan chosen from the associated SQL plan baseline. If the non-accepted plan performance is found to be better than SQL plan baseline performance, the non-accepted plan is changed to an accepted plan provided such action is permitted by the user (parameter commit = 'YES').

 

QUESTION 85

Which two are the prerequisites to enable Flashback Data Archive? (Choose two.)

A. Database must be running in archivelog mode.

B. Automatic undo management must be enabled.

C. Undo retention guarantee must be enabled.

D. The tablespace on which the Flashback Data Archive is created must be managed with Automatic Segment Space

Management (ASSM).

Answer: BD

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/12221269

官方参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS01001

Oracle Flashback features use the Automatic Undo Management (AUM) system to obtain metadata and historical data for transactions. They rely on undo data, which are records of the effects of individual transactions. For example, if a user runs an UPDATE statement to change a salary from 1000 to 1100, then Oracle Database stores the value 1000 in the undo data.

Undo data is persistent and survives a database shutdown. By using flashback features, you can use undo data to query past data or recover from logical damage.

 

QUESTION 86

The following command is executed to create the baseline template:

SQL> EXECUTE

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(START_TIME=> TO_TIMESTAMP(SYSDATE +2),

END_TIME=>TO_TIMESTAMP(SYSDATE+10),

BASELINE_NAME=>'Mybase4',

TEMPLATE_NAME=>'Mytemp4',

EXPIRATION=>NULL);

Which statement is true about the effect of the above command?

A. It produces an error because no snapshot information is provided to create the baseline.

B. It creates a baseline template that expires after the Automatic Workload Repository (AWR) retention period.

C. It creates a baseline template that never expires.

D. It creates a repeating baseline template that repeats after the Automatic Workload Repository (AWR) retention

period.

Answer: C

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_workload_repos.htm#ARPLS69131

Parameters

Table 162-24 CREATE_BASELINE_TEMPLATE Procedure Parameters

Parameter

Description

start_time

Start Time for the baseline to be created'

end_time

End Time for the baseline to be created

baseline_name

Name of baseline to be created

template_name

Name for the template

expiration

Expiration in number of days for the baseline. If NULL, then expiration is infinite, meaning do not drop baseline ever. Defaults to NULL

dbid

Database Identifier for baseline. If NULL, this takes the database identifier for the local database. Defaults toNULL.

day_of_week

Day of week that the baseline should repeat on. Specify one of the following values: SUNDAYMONDAYTUESDAY,WEDNESDAYTHURSDAYFRIDAYSATURDAY.

hour_in_day

Value of 0-23 to specify the Hour in the Day the baseline should start

duration

Duration (in number of hours) after hour in the day that the baseline should last

baseline_name_prefix

Name for baseline prefix. When creating the baseline, the name of the baseline will be the prefix appended with the date information.

 

 

QUESTION 87

An index called ORD_CUSTNAME_IX has been created on the CUSTNAME column in the ORDERS table using the

following command:

SQL>CREATE INDEX ord_custname_ix ON orders(custname); The ORDERS table is frequently queried using the

CUSTNAME column in the WHERE clause.

You want to check the impact on the performance of the queries if the index is not available. You do not want the index to be dropped or rebuilt to perform this test. Which is the most efficient method of performing this task?

A. making the index invisible

B. making the index unusable

C. disabling the index

D. using the MONITORING USAGE clause for the index

Answer: A

Explanation/Reference:

Creating an Invisible Index

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

To create an invisible index: 

·         Use the CREATE INDEX statement with the INVISIBLE keyword.

The following statement creates an invisible index named emp_ename for the ename column of the emp table:

CREATE INDEX emp_ename ON emp(ename)

      TABLESPACE users

      STORAGE (INITIAL 20K

      NEXT 20k) INVISIBLE;

 

隐藏索引

scott@TESTDB> create index emp_ename_i on emp(ename) invisible;

切换到系统用户,修改参数

sys@TESTDB> alter session set optimizer_use_invisible_indexes=true;

隐藏索引变正常索引或反之

sys@TESTDB> alter index scott.emp_ename_i visible;

多个索引,把慢的索引隐藏点,让他走快的索引

 scott@TESTDB> alter index emp_ename_i visible;

 Index altered.

 scott@TESTDB> alter index emp_ename_i invisible;

 Index altered.

 

 

QUESTION 88

In your database, the RESULT_CACHE_MODE parameter has been set to MANUAL in the initialization parameter file.

You issued the following command:

SQL>SELECT /*+ RESULT_CACHE */ sale_category, sum(sale_amt) FROM sales

GROUP BY sale_category;

Where would the result of this query be stored?

A. database buffer cache

B. shared pool

C. PGA

D. large pool

Answer: B

Explanation/Reference:

RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan.

Values:

·         MANUAL

The ResultCache operator is added only when the query is annotated (that is, hints).

·         FORCE

The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).

Shared Pool Concepts

The main components of the shared pool are the library cache, the dictionary cache, and, depending on your configuration, the server result cache. The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. The server result cache stores the results of queries and PL/SQL function results.

Many of the caches in the shared pool automatically increase or decrease in size, as needed, including the library cache and the dictionary cache. Old entries are aged out to accommodate new entries when the shared pool does not have free space.

A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached.

Several features make large memory allocations in the shared pool: for example, the shared server, parallel query, or Recovery Manager. Oracle recommends segregating the SGA memory used by these features by configuring a distinct memory area, called the large pool.

Allocation of memory from the shared pool is performed in chunks. This chunking enables large objects (over 5 KB) to be loaded into the cache without requiring a single contiguous area. In this way, the database reduces the possibility of running out of enough contiguous memory due to fragmentation.

Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5 KB. To allow these allocations to occur most efficiently, Oracle Database segregates a small amount of the shared pool. This memory is used if the shared pool does not have enough space. The segregated area of the shared pool is called the reserved pool.

 

 

QUESTION 89

Exhibit:

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 XML-formatted version of the alert log is /u01/oracle/diag/rdbms/orclbi/orclbi/alert.

D. The location of the trace file for the current session is /u01/oracle/diag/rdbms/orclbi/orclbi/trace.

Answer: ACD

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e25494/diag.htm#ADMIN13483

B错,应该为 the text alert log file is /u01/oracle/diag/rdbms/orclbi/orclbi/trace

Subdirectory Name

Contents

alert

The XML-formatted alert log

cdump

Core files

incident

Multiple subdirectories, where each subdirectory is named for a particular incident, and where each contains dumps pertaining only to that incident

trace

Background and server process trace files, SQL trace files, and the text-formatted alert log

(others)

Other subdirectories of ADR home, which store incident packages, health monitor reports, and other information

 

Viewing ADR Locations with the V$DIAG_INFO View

The V$DIAG_INFO view lists all important ADR locations for the current Oracle Database instance.

SELECT * FROM V$DIAG_INFO;

 

INST_ID NAME                  VALUE

------- --------------------- -------------------------------------------------------------

      1 Diag Enabled          TRUE

      1 ADR Base              /u01/oracle

      1 ADR Home              /u01/oracle/diag/rdbms/orclbi/orclbi

      1 Diag Trace            /u01/oracle/diag/rdbms/orclbi/orclbi/trace

      1 Diag Alert            /u01/oracle/diag/rdbms/orclbi/orclbi/alert

      1 Diag Incident         /u01/oracle/diag/rdbms/orclbi/orclbi/incident

      1 Diag Cdump            /u01/oracle/diag/rdbms/orclbi/orclbi/cdump

      1 Health Monitor        /u01/oracle/diag/rdbms/orclbi/orclbi/hm

      1 Default Trace File    /u01/oracle/diag/rdbms/orclbi/orclbi/trace/orcl_ora_22769.trc

      1 Active Problem Count  8

      1 Active Incident Count 20

The following table describes some of the information displayed by this view.

Table 9-3 Data in the V$DIAG_INFO View

Name

Description

ADR Base

Path of ADR base

ADR Home

Path of ADR home for the current database instance

Diag Trace

Location of background process trace files, server process trace files, SQL trace files, and the text-formatted version of the alert log

Diag Alert

Location of the XML-formatted version of the alert log

Default Trace File

Path to the trace file for the current session

 

 

 

QUESTION 90

Evaluate the following command and its output:

SQL>SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

------------------------------------------------------ ---------------------------- ------------------

 LMTEMP            250609664         101048576            149561088

Which two statements correctly interpret the output? (Choose two.)

A. FREE_SPACE indicates both the space that is currently allocated and available for reuse, and the space that is currently unallocated.

B. ALLOCATED_SPACE indicates only the space currently allocated and in use.

C. ALLOCATED_SPACE indicates both the space currently allocated and used, and the space that is available for reuse.

D. FREE_SPACE indicates only the space that is currently unallocated.

E. FREE_SPACE indicates only the space that is currently allocated and available for reuse.

Answer: AC

Explanation/Reference:

DBA_TEMP_FREE_SPACE displays temporary space usage information at tablespace level.

Column

Datatype

NULL

Description

TABLESPACE_NAME

VARCHAR2(30)

NOT NULL

Name of the tablespace

TABLESPACE_SIZE

NUMBER

 

Total size of the tablespace, in bytes

ALLOCATED_SPACE

NUMBER

 

Total allocated space, in bytes, including space that is currently allocated and used and space that is currently allocated and available for reuse

FREE_SPACE

NUMBER

 

Total free space available, in bytes, including space that is currently allocated and available for reuse and space that is currently unallocated

 

QUESTION 91

Evaluate the following block of code:

BEGIN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (

acl => 'mycompany-com-permissions.xml',

principal => 'ACCT_MGR',

is_grant => TRUE,

privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

acl => 'mycompany-com-permissions.xml',

host => '*.mycompany.com');

END;

What is the outcome of the above code?

A. It creates an access control list (ACL) with the user ACCT_MGR who gets the CONNECT privilege but not the

RESOLVE privilege.

B. It creates an access control list (ACL) with the user ACCT_MGR who gets the CONNECT and RESOLVE privileges.

C. It produces an error because a fully qualified host name needs to be specified.

D. It produces an error because the range of ports associated with the hosts has not been specified.

Answer: B

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/15029727

创建访问控制列表(ACL)ACL 是在XML 文件中保存的用户和权限的列表。以acl参数命名的XML 文档与XML DB中的/sys/acl/文件夹相关。在题中例中ACCT_MGR被授予connect权限ACL 中的用户名区分大小写,而且必须与会话的用户名相匹配。只有resolveconnect权限,connect权限包含resolve权限。可选参数可以指定这些权限的开始和结束时间戳。要将更多用户和权限添加到此ACL 中,可使用ADD_PRIVILEGE过程。

 

 

QUESTION 92

Which statement is true regarding the retention policy for the incidents accumulated in the Automatic Diagnostic Repository (ADR)?

A. The incident files are retained but the incident metadata is purged when the problem is resolved and the DBA closes the SR

B. The incident files and dumps are not retained in the ADR for the manually created incidents.

C. The incident metadata is purged when the problem is resolved and the DBA closes the SR.

D. The default setting is for one year after which the incident metadata is purged from the ADR and the files are retained for one month.

Answer: D

Explanation/Reference:

ADR(Automatic Diagnostic Repository)是一个基于文件的档案库,用于存放数据库的诊断信息,例如跟踪文件,意外dump文件,IPS包,警告日志文件,健康监控报告,核心dump文件以及其它诊断信息。ADR的根目录叫做ADR base, 位置通过参数DIAGNOSTIC_DEST设置。ADR拥有统一的目录结构,在数据库之外存储多个产品和实例的诊断信息,因此即使在数据库关闭时仍然可以进行问题诊断。
   
Oracle 11gR1开始,ADR用于存储数据库,ASM,CRS和其它产品或组件(如listener)的诊断信息。每一个实例或者产品拥有各自的ADR home路径。例如在一个RAC环境下,ASM, 数据库实例拥有单独的ADR home

 

http://blog.chinaunix.net/uid-25528717-id-3160758.html

事件元数据保留在ADR中默认一年。
 
事件文件和dump保留在ADR中默认是一个月。
可以使用事件package configuration更改这些保留策略。后台进程MMON自动清洗过期的ADR数据。

 http://blog.csdn.net/tianlesoftware/article/details/8222724

官网对这2个参数的解释:

Attribute Name

Description

SHORTP_POLICY

Number of hours after which to purge ADR contents that have a short life. Default is 720 (30 days).

A setting of 0 (zero) means that all contents that have a short life can be purged. The maximum setting is 35791394. If a value greater than 35791394 is specified, then this attribute is set to 0 (zero).

The ADR contents that have a short life include the following:

Trace files

Core dump files

Packaging information

LONGP_POLICY

Number of hours after which to purge ADR contents that have a long life. Default is 8760 (365 days).

A setting of 0 (zero) means that all contents that have a long life can be purged. The maximum setting is 35791394. If a value greater than 35791394 is specified, then this attribute is set to 0 (zero).

The ADR contents that have a long life include the following:

Incident information

Incident dumps

Alert logs

 SHORTP_POLICY 默认是720小时,30天。其控制如下三种文件的保留时间:

(1)  Trace files

(2)  Core dump files

(3)  Packaging information

 LONGP_POLICY默认值是8760小时,即365天,1年,其控制如下三种文件的保留时间:

(1)  Incident information

(2)  Incident dumps

(3)  Alert logs

 修改ADR的保留策略:

adrci> set control (SHORTP_POLICY = 360) 
adrci> set control (LONGP_POLICY = 2160) 

 adrci> show control

 ADR Home =/u01/app/oracle/diag/rdbms/dave/dave:

*************************************************************************

ADRID                SHORTP_POLICY        LONGP_POLICY        

-------------------- ----------------------------------------

3642307927           360                  2160                 

1 rows fetched

 关于ADRCI 的更多内容,参考官网手册:

http://docs.oracle.com/cd/E11882_01/server.112/e10701/adrci.htm

 

 

QUESTION 93

You need to create a partitioned table to store historical data and you issued the following command:

CREATE TABLE purchase_interval

PARTITION BY RANGE (time_id)

INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3) (

PARTITION p1 VALUES LESS THAN(TO_DATE('1-1-2005', 'dd-mm-yyyy')), PARTITION p2 VALUES LESS THAN

(TO_DATE('1-1-2007', 'dd-mm-yyyy'))) AS

SELECT *

FROM purchases

WHERE time_id < TO_DATE('1-1-2007','dd-mm-yyyy');

What is the outcome of the above command?

A. It creates two range partitions of varying range. For data beyond '1-1-2007,' it creates partitions with a width of one

month each.

B. It returns an error because the number of tablespaces (TBS1,TBS2,TBS3)specified does not match the number of

range partitions (P1,P2) specified.

C. It returns an error because the range partitions P1 and P2 should be of the same range.

D. It creates two range partitions (P1, P2). Within each range partition, it creates monthwise subpartitions.

Answer: A

Explanation/Reference:

http://blog.csdn.net/jgmydsai/article/details/38231135
INTERVAL
11G新增的自动分区特性
该语句建立p1p2两个分区,其后的数据按月自动进行分区

 

QUESTION 94

Which two statements are true regarding the SQL Repair Advisor? (Choose two.)

A. The SQL Repair Advisor is invoked by the Health Monitor when it encounters the problematic SQL statement.

B. The SQL Repair Advisor can be invoked even when the incident is not active for a SQL statement crash.

C. 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.

D. The SQL Repair Advisor can be invoked to tune the performance of the regressed SQL statements.

Answer: BC

Explanation/Reference:

SQL Repair Advisor 只能用来修改错误,不能用于优化,可修改not activesql
SQL Repair Advisor
不能用于修改语法错误的语句,只能用于修复不良“sql引出的 ORA 0600 ORA 7445等问题
health check
不包括sql  critical error,见: http://docs.oracle.com/cd/E11882_01/server.112/e25494/diag.htm#ADMIN11270

Types of Health Checks

Health monitor runs the following checks:

·         DB Structure Integrity Check—This check verifies the integrity of database files and reports failures if these files are inaccessible, corrupt or inconsistent. If the database is in mount or open mode, this check examines the log files and data files listed in the control file. If the database is inNOMOUNT mode, only the control file is checked.

·         Data Block Integrity Check—This check detects disk image block corruptions such as checksum failures, head/tail mismatch, and logical inconsistencies within the block. Most corruptions can be repaired using Block Media Recovery. Corrupted block information is also captured in theV$DATABASE_BLOCK_CORRUPTION view. This check does not detect inter-block or inter-segment corruption.

·         Redo Integrity Check—This check scans the contents of the redo log for accessibility and corruption, as well as the archive logs, if available. The Redo Integrity Check reports failures such as archive log or redo corruption.

·         Undo Segment Integrity Check—This check finds logical undo corruptions. After locating an undo corruption, this check uses PMON and SMON to try to recover the corrupted transaction. If this recovery fails, then Health Monitor stores information about the corruption in V$CORRUPT_XID_LIST. Most undo corruptions can be resolved by forcing a commit.

·         Transaction Integrity Check—This check is identical to the Undo Segment Integrity Check except that it checks only one specific transaction.

·         Dictionary Integrity Check—This check examines the integrity of core dictionary objects, such as tab$ and col$. It performs the following operations:

o    Verifies the contents of dictionary entries for each dictionary object.

o    Performs a cross-row level check, which verifies that logical constraints on rows in the dictionary are enforced.

o    Performs an object relationship check, which verifies that parent-child relationships between dictionary objects are enforced.

The Dictionary Integrity Check operates on the following dictionary objects:

tab$clu$fet$uet$seg$undo$ts$file$obj$ind$icol$col$user$con$cdef$ccol$bootstrap$objauth$ugroup$tsq$syn$view$,typed_view$superobj$seq$lob$coltype$subcoltype$ntab$refcon$opqtype$dependency$access$viewcon$icoldep$dual$sysauth$objpriv$,defrole$, and ecol$.

 

 

QUESTION 95

You need to perform an online table redefinition of an existing SALES table to partition it into two tablespaces TBS1 and TBS2.

The SALES table has a materialized view, materialized log, indexes, referential integrity constraint, and triggers with the PRECEDES clause existing on it. What action is required for dependent objects when you perform online table redefinition?

A. The dependent materialized view should have a complete refresh performed after the online table redefinition

process.

B. Triggers with the PRECEDES clause should be disabled before the online table redefinition process.

C. Referential integrity constraints must be manually enabled after the online table redefinition process.

D. The materialized log should be dropped before the online table redefinition process.

Answer: A

Explanation/Reference:

 

 

QUESTION 96

You are managing an Oracle Database 11g database with the ASM storage. The database is having big file tablespaces. You want files to open faster and less memory to be used in the shared pool to manage the extent maps.

What configuration would you effect to achieve your objective? (Choose all that apply.)

A. Set the COMPATIBLE initialization parameter for the ASM instance to 11.1.0.

B. Set the COMPATIBLE initialization parameter for the database instance to 11.1.0.

C. Set the ASM compatibility attribute for the ASM disk group to 11.1.0.

D. Set the RDBMS compatibility attribute for the ASM disk group to 11.1.0.

Answer: BC

Explanation/Reference:

 

 

 

 

QUESTION 97

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.

B. Archived log files are backed up along with data files, and the archived log files are deleted.

C. The command creates a restore point named FY06Q4 to match the SCN at which this backup is consistent.

D. Only data files are backed up and a restore point named FY06Q4 is created.

Answer: AC

Explanation/Reference:

Creating a Consistent Database Backup for Archival Purposes

This example uses a keepOption to create an archival backup set that cannot be considered obsolete for one year. The example backs up the database, archives the redo in the current online logs to ensure that this new backup is consistent, and backs up only those archived redo log files needed to restore the data file backup to a consistent state.

The BACKUP command also creates a restore point to match the SCN at which this backup is consistent. The FORMAT parameter must be capable of creating multiple backup pieces in multiple backup sets.

BACKUP DATABASE

  FORMAT '/disk1/archival_backups/db_%U.bck'

  TAG quarterly

  KEEP UNTIL TIME 'SYSDATE + 365'

  RESTORE POINT Q1FY06;

 

 

QUESTION 98

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 costs each of the accepted plans in the SQL plan baseline and picks the one with the lowest cost.

E. The optimizer adds the new plan to the SQL plan baseline but not in the ENABLED state.

Answer: AD

Explanation/Reference:

优化器捕获的plan不会直接进入baseline,而是进入plan history,只有当比较后cost最低的才会进入baseline

 

QUESTION 99

Which tasks are run automatically as part of the Automated Maintenance Task by default? (Choose all that apply.)

A. Automatic Database Diagnostics Monitor

B. Automatic SQL Tuning Advisor

C. SQL Access Advisor

D. Segment Advisor

E. Optimizer statistics gathering

Answer: BDE

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/13021985

http://blog.csdn.net/rlhua/article/details/13092965

题问:以下哪种任务是自动维护任务的一部分,默认情况下自动运行的?

自动维护任务:是在创建数据库时自动创建的,并由调度程序进行管理。默认情况下,它会收集数据库中优化程序统计信息缺失或已过时的所有对象的统计信息。可以通过“Automatic Maintenance Tasks(自动维护任务)页更改默认配置。

点击配置后,可见这三个任务默认都是启用的,当然也可以自动配置。

 

 

QUESTION 100

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. It contains file systems that have been mounted by the kernel NFS system.

B. It is globally available to all Oracle 11g databases on the machine.

C. It contains file systems that have been mounted by Direct NFS.

D. Its entries are specific to a single database.

Answer: AB

Explanation/Reference:

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.

 

 

 

QUESTION 101

Examine the following command:

ALTER DISKGROUP data MOUNT FORCE;

In which scenario can you use the above command to mount the disk group?

A. when ASM disk goes offline

B. when some disks in a failure group for a disk group are rebalancing

C. when some disks in a disk group are offline

D. when one or more ASM files are dropped

Answer: C

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_1007.htm#SQLRF52684

FORCE | NOFORCE Use these clauses to determine the circumstances under which the disk groups are mounted.

·         In the FORCE mode, Oracle ASM attempts to mount the disk group even if it cannot discover all of the devices that belong to the disk group. This setting is useful if some of the disks in a normal or high redundancy disk group became unavailable while the disk group was dismounted. When MOUNT FORCEsucceeds, Oracle ASM takes the missing disks offline.--感觉这里应该为online

If Oracle ASM discovers all of the disks in the disk group, then MOUNT FORCE fails. Therefore, use the MOUNT FORCE setting only if some disks are unavailable. Otherwise, use NOFORCE.

In normal- and high-redundancy disk groups, disks from one failure group can be unavailable and MOUNT FORCE will succeed. Also in high-redundancy disk groups, two disks in two different failure groups can be unavailable and MOUNT FORCE will succeed. Any other combination of unavailable disks causes the operation to fail, because Oracle ASM cannot guarantee that a valid copy of all user data or metadata exists on the available disks.

·         In the NOFORCE mode, Oracle ASM does not attempt to mount the disk group unless it can discover all the member disks. This is the default.

 

QUESTION 102

Which client requests to the database can be captured as a part of the workload capture? (Choose all that apply.)

A. distributed transactions

B. direct path load of data from external files

C. flashback query

D. logging in and logging out of sessions

E. all DDL statements having bind variables

Answer: DE

Explanation/Reference:

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.

 

 

QUESTION 103

You are managing an Oracle Database 11g instance with ASM storage. The ASM instance is down. To know the details of the disks in the DATA disk group , you issued the following ASMCMD command:

ASMCMD> lsdsk -I -d DATA

Which statement is true regarding the outcome of this command?

A. The command succeeds and retrieves information by scanning the disk headers based on an ASM_DISKSTRING value.

B. The command succeeds but it retrieves only the disk names.

C. The command produces an error because the ASM instance is down.

D. The command succeeds but it shows only the status of the ASM instance.

Answer: A

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_util004.htm#OSTMG94559

-I

Scans disk headers for information rather than extracting the information from an Oracle ASM instance. This option forces non-connected mode.

-G

Restricts results to only those disks that belong to the group specified by diskgroup.

ASMCMD> lsdsk

Path

/dev/asm-diskb

/dev/asm-diskc

/dev/asm-diskd

/dev/asm-diske

 

ASMCMD> lsdsk -I -d DATA

WARNING:option 'd' is deprecated for 'lsdsk'

please use 'G'

 

ASMCMD> lsdsk -I -G DATA

 

 

QUESTION 104

You want to perform the following operations for the DATA ASM disk group:

-Verify the consistency of the disk.

-Cross-check all the file extent maps and allocation tables for consistency.

-Check whether the alias metadata directory and file directory are linked correctly. \

-Check that ASM metadata directories do not have unreachable allocated blocks.

Which command accomplishes these tasks?

A. ALTER DISKGROUP data CHECK DISK IN FAILURE GROUP 1;

B. ALTER DISKGROUP data CHECK FILE;

C. ALTER DISKGROUP data CHECK;

D. ALTER DISKGROUP data CHECK DISK;

Answer: C

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_1007.htm#SQLRF52644

ALTER DISKGROUP  data CHECK [ REPAIR | NOREPAIR ]

check_diskgroup_clause

The check_diskgroup_clause lets you verify the internal consistency of Oracle ASM disk group metadata. The disk group must be mounted. Oracle ASM displays summary errors and writes the details of the detected errors in the alert log.

The CHECK keyword performs the following operations:

·         Checks the consistency of the disk.

·         Cross checks all the file extent maps and allocation tables for consistently.

·         Checks that the alias metadata directory and file directory are linked correctly.

·         Checks that the alias directory tree is linked correctly.

·         Checks that Oracle ASM metadata directories do not have unreachable allocated blocks.

 

 

QUESTION 105

Observe the following warning in an RMAN session of your database instance:

WARNING: new failures were found since last LIST FAILURE command

Which statement describes the scenario that must have produced this warning?

A. The CHANGE FAILURE command has detected new failures recorded in the Automatic Diagnostic Repository

(ADR).

B. The RECOVER command has detected new failures recorded in the Automatic Diagnostic Repository (ADR) since

the last LIST FAILURE command was executed.

C. The ADVISE FAILURE command has detected new failures recorded in the Automatic Diagnostic Repository (ADR) since the last LIST FAILURE.

D. The VALIDATE DATABASE command has detected new failures recorded in the Automatic Diagnostic Repository

(ADR).

Answer: C

Explanation/Reference:

http://blog.csdn.net/rlhua/article/details/14497831

实验一把:验证repair failure脚本是存在ADR

 

QUESTION 106

Exhibit:

View the Exhibit to examine a portion of the output from the VALIDATE DATABASE command.

Which statement is true about the block corruption detected by the command?

A. No action is taken except the output in the Exhibit.

B. The ADVISE FAILURE command is automatically called to display the repair script.

C. The failure is logged into the Automatic Diagnostic Repository (ADR).

D. The corruption is repaired by the command implicitly.

Answer: C

Explanation/Reference:

 

 

 

QUESTION 107

Which two statements about workload capture and replay operations are true? (Choose two.)

A. The clients must be created manually on the test machines to perform more realistic testing.

B. Restart the database in RESTRICTED mode before beginning workload capture to enable a clean state for capturing the workload.

C. The application state of the capture system must be identical to the application state of the replay system when the workload replay begins.

D. Restart the database in RESTRICTED mode before beginning workload replay to enable a clean state for workload

replay.

Answer: BC

Explanation/Reference:

 

QUESTION 108

You are working on a CATDB database that contains an Oracle Database version 11.1 catalog schema owned by the

user RCO11. The INST1 database contains an Oracle Database version 10.1 catalog schema owned by the user

RCAT10. You want the RMAN to import metadata for database IDs 1423241 and 1423242, registered in RCAT10, into

the recovery catalog owned by RCO11. You also want to deregister them from the catalog after import.

You executed the following commands to achieve this:

RMAN> CONNECT CATALOG rco11/password@catdb

RMAN> IMPORT CATALOG rcat10/oracle@inst1 DBID=1423241,1423242;

What happens if the RCO11 catalog has scripts with the same name as that of the scripts in RCAT10 catalog?

A. RMAN renames the local stored scripts in the RCO11 catalog.

B. The scripts in the RCO11 catalog are overwritten.

C. The RMAN session in which the command is executed aborts.

D. RMAN renames the global scripts that are imported from the RCAT10 catalog.

Answer: D

Explanation/Reference:

 global scripts:可以针对在恢复目录中注册的任何数据库执行.

import时,如果 global scripts与目标方案的 global scripts名称相同,则RMAN会重新命名为COPY OF script_name

A stored script is either global or local. It is possible for global scripts, but not local scripts, to have name conflicts during import because the destination schema contains the script name. In this case, RMAN renames the global script name to COPY OF script_name. For example, RMAN renames bp_cmd to COPY OF bp_cmd.

If the renamed global script is still not unique, then RMAN renames it to COPY(2) OF script_name. If this script name also exists, then RMAN renames the script to COPY(3) OF script_name. RMAN continues the COPY(n) OF pattern until the script is uniquely named.

 

QUESTION 109

You are in the process of creating a virtual private catalog in your Oracle Database 11g database. The PROD1,

PROD2, and PROD3 Oracle Database 10g databases are registered in the base recovery catalog. The database user

who owns the base recovery catalog is CATOWNER. CATOWNER executes the following command to grant privileges

to a new user VPC1 using Oracle Database 11g RMAN executables:

RMAN> GRANT CATALOG FOR DATABASE prod1, prod2 TO vpc1; Then you issue the following commands:

RMAN> CONNECT CATALOG vpc1/oracle@catdb;

RMAN> SQL "EXEC catowner.dbms_rcvcat.create_virtual_catalog;" What is the outcome of the above commands?

A. They produce an error because you need to connect as CATOWNER to execute this packaged procedure.

B. They execute and create a virtual catalog for pre-Oracle 11g clients.

C. They produce an error because PROD1 and PROD2 databases belong to the older version.

D. They produce an error because you need to connect to the target database to execute this packaged procedure.

Answer: B

Explanation/Reference:

Explanation/Reference:

Section: Backup, Recovery & Recovery Manager (RMAN)

官方参考:http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta013.htm#RCMRF117

Because this operator eventually intends to use the virtual catalog with Oracle Database 10g target databases, the operator must execute theCREATE_VIRTUAL_CATALOG PL/SQL procedure before using the virtual catalog (as explained in "Usage Notes"). In the following example, the backup operator connects to the recovery catalog database as vpc1 and executes the PL/SQL procedure as follows:

SQL> CONNECT vpc1@catdb

Enter password:

password

Connected.

SQL> BEGIN

  2  catowner.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG;

  3  END;

  4  /

 

Usage Notes

Typically, you create the recovery catalog in a database created especially for this purpose. It is not recommended to create the recovery catalog in the SYSschema.

The best practice is to create one recovery catalog that serves as the central RMAN repository for many databases. For this reason it is called the base recovery catalog.

The owner of the base recovery catalog can GRANT or REVOKE restricted access to the catalog to other database users. Each restricted user has full read/write access to his own metadata, which is called a virtual private catalog. The RMAN metadata is stored in the schema of the virtual private catalog owner. The owner of the base recovery catalog controls what each virtual catalog user can access.

You must take an extra step when intending to use a 10.2 or earlier release of RMAN with a virtual catalog. Before using the virtual private catalog, this user must connect to the recovery catalog database as the virtual catalog owner and execute the following PL/SQL procedure (where base_catalog_owner is the database user who owns the base recovery catalog):

base_catalog_owner.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG

 

QUESTION 110

You executed the following commands in an RMAN session for your database instance that has failures:

RMAN> LIST FAILURE;

After some time, you executed the following command in the same session:

RMAN> ADVISE FAILURE;

But there are new failures recorded in the Automatic Diagnostic Repository (ADR) after the execution of the last LIST

FAILURE command. Which statement is true for the above ADVISE FAILURE command in this scenario?

A. It produces advice only for new failures and the failures listed in the last LIST FAILURE command are ignored.

B. It produces a warning for new failures before advising for CRITICAL and HIGH failures.

C. It produces an error with recommendation to run the LIST FAILURE command before the ADVISE FAILURE

command.

D. It ignores new failures and considers the failures listed in the last LIST FAILURE command only.

Answer: B

Explanation/Reference:

 

 

QUESTION 111

Your organization decided to upgrade the existing Oracle 10g database to Oracle 11g database in a multiprocessor

environment. At the end of the upgrade, you observe that the DBA executes the following script:

SQL> @utlrp.sql

What is the significance of executing this script?

A. It performs sequential recompilation of any stored PL/SQL as well as Java code.

B. It performs parallel recompilation of any stored PL/SQL as well as Java code.

C. It performs sequential recompilation of only the stored PL/SQL code.

D. It performs parallel recompilation of only the stored PL/SQL code.

Answer: B

Explanation/Reference:

 

 

 

QUESTION 112

View the Exhibit.

Examine the following command that is executed for the TRANSPORT table in the SH schema:

SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('sh', 'customers_obe', '(country_id,

cust_state_province)') FROM dual;

Which statement describes the significance of this command?

A. It collects statistics with AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT.

B. It collects statistics into the pending area in the data dictionary.

C. It creates a histogram to hold skewed information about the data in the columns.

D. It creates a virtual hidden column in the CUSTOMERS_OBE table.

Answer: D

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF94739

CREATE_EXTENDED_STATS Function

Creates a virtual column for a user specified column group or an expression in a table

Creating a Column Group

Use the CREATE_EXTENDED_STATISTICS function to create a column group. The CREATE_EXTENDED_STATISTICS function returns the system-generated name of the newly created column group. Table 13-5 lists the input parameters for this function.

Table 13-5 Parameters for the create_extended_statistics Function

Parameter

Description

ownname

Schema owner. NULL indicates current schema.

tabname

Name of the table to which the column group is added.

extension

Columns in the column group.

 

For example, to add a column group consisting of the cust_state_province and country_id columns to the customers table in SH schema, run the following PL/SQL block:

DECLARE

  cg_name VARCHAR2(30);

BEGIN

  cg_name := DBMS_STATS.CREATE_EXTENDED_STATS(null,'customers', 

             '(cust_state_province,country_id)');

END;

/

 

 

QUESTION 113

You installed Oracle Database 11g afresh. Which statements are true regarding the default audit settings in this

database? (Choose all that apply.)

A. The audit trail is stored in an operating system file.

B. Auditing is disabled for all privileges.

C. The audit trail is stored in the database.

D. Auditing is enabled for all privileges.

E. Auditing is enabled for certain privileges related to database security.

Answer: CE

Explanation/Reference:

 

 

QUESTION 114

Exhibit:

View the Exhibit to examine the output produced by the following query at three different times since the database

instance started and has undergone workloads of different capacities:

SQL> SELECT substr(component, 0, 10) COMP, current_size CS, user_specified_size US

FROM v$memory_dynamic_components

WHERE current_size!=0;

What do you infer from this?

A. All sessions are connected to the database instance in dedicated mode, and no RMAN or parallel query operations

have been performed.

B. The database instance is running with manual shared memory management.

C. The database instance is running with manual PGA management.

D. The database instance has the MEMORY_TARGET value set to a nonzero value.

Answer: D

Explanation/Reference:

BX. 因为PGA Targetuser_specified_size = 0,所以为自动PGA管理

CX. 因为SGA Targetuser_specified_size = 0,所以为自动共享内存管理

AX. 无法判断出是运行在Dedicated Server还是Shared Server

 

 

 

QUESTION 115

Exhibit:

View the Exhibit to examine the error during the database startup. You open an RMAN session for the database

instance. To repair the failure, you executed the following as the first command in the RMAN session:

RMAN> REPAIR FAILURE;

Which statement describes the consequence of the command?

A. The command performs the recovery and closes the failures.

B. The command executes the RMAN script to repair the failure and removes the entry from the Automatic Diagnostic

Repository (ADR).

C. The command only displays the advice and the RMAN script required for repair.

D. The command produces an error because the ADVISE FAILURE command has not been executed before the

REPAIR FAILURE command.

Answer: D

Explanation/Reference:

 

 

QUESTION 116

Exhibit:

View the Exhibit for the object interdependency diagram. The PRODUCTS table is used to create the PRODCAT_VW

view.

PRODCAT_VW is used in the GET_DATA procedure.

GET_DATA is called in the CHECK_DATA function.

A new column PROD_QTY is added to the PRODUCTS table. How does this impact the status of the dependent

objects?

A. All dependent objects remain valid.

B. Only the view becomes invalid and gets automatically revalidated the next time it is used.

C. Only the procedure and function become invalid and get automatically revalidated the next time they are called.

D. Only the procedure and function become invalid and must be recompiled.

Answer: A

Explanation/Reference:

 

 

QUESTION 117

Evaluate the following block of code:

BEGIN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (

acl => 'mycompany-com-permissions.xml',

principal => 'ACCT_MGR',

is_grant => TRUE,

privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

acl => 'mycompany-com-permissions.xml',

host => '*.mycompany.com');

END;

What is the outcome of the above code?

A. It creates an access control list (ACL) with the user ACCT_MGR who gets the CONNECT privilege but not the

RESOLVE privilege.

B. It creates an access control list (ACL) with the user ACCT_MGR who gets the CONNECT and RESOLVE privileges.

C. It produces an error because a fully qualified host name needs to be specified.

D. It produces an error because the range of ports associated with the hosts has not been specified.

Answer: B

Explanation/Reference:

CONNECT 权限包含resource权限

 

QUESTION 118

Examine the following command:

ALTER DISKGROUP data MOUNT FORCE;

In which scenario can you use the above command to mount the disk group?

A. when ASM disk goes offline

B. when some disks in a failure group for a disk group are rebalancing

C. when some disks in a disk group are offline

D. when one or more ASM files are dropped

Answer: C

Explanation/Reference:

 

 

QUESTION 119

Identify two situations in which you can use Data Recovery Advisor for recovery.

(Choose two.)

A. The user has dropped an important table that needs to be recovered.

B. The database files are corrupted when the database is open.

C. You are not able to start up the database instance because the required database files are missing.

D. The archived log files are missing for which backup is not available.

Answer: BC

Explanation/Reference:

 

 

QUESTION 120

In your database, the LDAP_DIRECTORY_SYSAUTH initialization parameter has been set to YES and the users who

need to access the database as DBAs have been granted SYSDBA enterprise role in Oracle Internet Directory (OID).

SSL and the password file have been configured. A user SCOTT with the SYSDBA privilege tries to connect to the

database instance from a remote machine using the command:

$ SQLPLUS scott/tiger@DB01 AS SYSDBA

where DB01 is the net service name.

Which authentication method would be used first?

A. authentication by using the local OS of the database server

B. authentication by using the Oracle Internet Directory

C. authentication by password file

D. authentication by using certificates over SSL

Answer: C

Explanation/Reference:

$ SQLPLUS scott/tiger@DB01 AS SYSDBA,是使用密码验证的,明显看到scott账户的密码就是tiger

LDAP_DIRECTORY_SYSAUTH enables or disables directory-based authorization for SYSDBA and SYSOPER.

 

 

QUESTION 121

Examine the following values of the initialization parameters in the database having the SID ORCL:

BACKGROUND_DUMP_DEST=/u01/app/oracle/product/11.1.0/db_1/bdump USER_DUMP_DEST=/u01/app/oracle/

product/11.1.0/db_1/udump CORE_DUMP_DEST=/u01/app/oracle/product/11.1.0/db_1/cdump DIAGNOSTIC_DEST=

The environment variables have the following value:

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

What is the location of the Automatic Diagnostic Repository (ADR) home?

A. /u01/app/oracle/product/11.1.0/db_1

B. /u01/app/oracle

C. $ORACLE_HOME/bdump

D. $ORACLE_HOME/log

Answer: B

Explanation/Reference:

既然题中已经说明The environmental variable ORACLE-BASE is set,那么

根据If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.

Structure, Contents, and Location of the Automatic Diagnostic Repository

The Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database. It is therefore available for problem diagnosis when the database is down.

The ADR root directory is known as ADR base. Its location is set by the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon startup as follows:

·         If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.

·         If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log.

 

 

QUESTION 122

Observe the following PL/SQL block:

BEGIN

dbms_spm.configure('SPACE_BUDGET_PERCENT', 30);

END;

Which statement is correct regarding the above PL/SQL block?

A. It automatically purges the SQL management objects when SMB occupies more than 30% of the SYSAUX

tablespace.

B. It reserves 30% of the space in the SYSAUX tablespace for SQL Management Base (SMB).

C. It reserves 30% of the space in the SYSTEM tablespace for SMB.

D. It generates a weekly warning in the alert log file when SMB occupies more than 30% of the SYSAUX tablespace.

Answer: D

Explanation/Reference:

http://blog.itpub.net/26474945/viewspace-744786/

 DBMS_SPM包允许用户使用SQL计划管理功能来管理SQL执行计划,SQL计划管理功能可以通过长时间的记录和分析SQL语句执行计划来有效的防止由于突然间更改一个SQL语句执行计划所导致数据库性能的衰退,而且还可以通过已知的一组高效的执行计划生成一些执行计划基线。这些SQL计划基线能够随后用于保证适当的性能,即使是在系统发生改变的时候,通常在如下的情况使用SQL计划功能来管理SQL执行计划:

1.      数据库升级安装改变优化器时,通常会有少部分的执行计划改变,大部分可能是有变化的或者是有提高的。无论怎样,还是存在一部分的计划改变所导致性能的衰退问题。此时利用SQL计划基线能显著地减少由于数据库更新带来的性能问题。

2.      正在运行的系统和不断变化的数据会带来一些性能问题。利用SQL计划基线能可以减少性能回退同时可以维持系统稳定

3.      有时部署新的系统模块相当于引用新的SQL语句到系统中,应用程序需要有适当的SQL执行计划,而这些新的执行计划需要通过一些标准的测试获得,使用SQL计划基线能在随时间的变化产生更好的性能

 DBMS_SPM包属于SYS用户,其他用户需要被分配到ADMINISTER SQL MANAGEMENT OBJECT权限才可以执行这个包 

CONFIGURE 程序

这个程序用于设置SQL管理程序的配置选项,使用parameter/value 的格式。这个函数可能被调用多次,每次可以设置不同的值。

语法

DBMS_SPM.CONFIGURE (

   parameter_name    IN VARCHAR2,

   parameter_value   IN NUMBER);

参数设置

Parameter

Description

parameter_name

Name of parameter to set (see table below)

parameter_value

Value of parameter to use (see table below)

 NameValues的设置

Name

Description

Possible Values

Default Value

space_budget_percent

Maximum percent of SYSAUX space that can be used for SQL management base

1,2, …, 50

10

plan_retention_weeks

Number of weeks to retain unused plans before they are purged

5,6, …, 523

53

 

 

QUESTION 123

Exhibit:

View the Exhibit to examine the output produced by the following query at three different times since the database

instance started and has undergone workloads of different capacities:

SQL> SELECT substr(component, 0, 10) COMP, current_size CS, user_specified_size US

FROM v$memory_dynamic_components

WHERE current_size!=0;

What do you infer from this?

A. All sessions are connected to the database instance in dedicated mode, and no RMAN or parallel query operations

have been performed.

B. The database instance is running with manual shared memory management.

C. The database instance is running with manual PGA management.

D. The database instance has the MEMORY_TARGET value set to a nonzero value.

Answer: D

Explanation/Reference:

 

 

QUESTION 124

You are managing an Oracle Database 11g ASM instance having three disks in a disk group with ASM compatibility

attribute set to 11.1.0 and redundancy set to high. One of the disks in the disk group becomes unavailable because of

power failure. Which statements will be true in this scenario? (Choose all that apply.)

A. The disk is immediately dropped from the disk group.

B. The ASM tracks the extents that are modified during the outage.

C. The ASM migrates the extents from the unavailable disk to the remaining disks.

D. The disk automatically goes offline.

Answer: BD

Explanation/Reference:

更改冗余级别的过程中因停电导致磁盘组的一个磁盘不可用
B asm
跟踪的块在些期间被更改
D
磁盘自动脱机

 

QUESTION 125

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. restructuring of SQL recommendations

D. index-related recommendations

Answer: B

Explanation/Reference:

http://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm#PFGRF94846

About the Automatic SQL Tuning Advisor

Beginning with Oracle Database 11g, the SQL Tuning Advisor runs automatically during system maintenance windows (time periods) as a maintenance task. During each automatic run, the advisor selects high-load SQL queries in the system and generates recommendations on how to tune these queries.

The Automatic SQL Tuning Advisor can be configured to automatically implement SQL profile recommendations. A SQL profile contains additional SQL statistics that are specific to the SQL statement and enable the query optimizer to generate a significantly better execution plan at run time. If you enable automatic implementation, then the advisor creates SQL profiles for only those SQL statements where the performance increase would be at least threefold. Other types of recommendations, such as the creation of new indexes, refreshing optimizer statistics, or restructuring SQL, can be implemented only manually. DML statements are not considered for tuning by the Automatic SQL Tuning Advisor.

You can view a summary of the results of automatic SQL tuning over a specified period (such as the previous 7 days), and a detailed report about recommendations made for all SQL statements that the SQL Tuning Advisor has processed. You can then implement selected recommendations. You can also view the recommendations that were automatically implemented.

 

QUESTION 126

Exhibit:

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. All subsequent ADDM tasks including my_instance_analysis_mode_task are 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 ADDM task is filtered to suppress the Segment Advisor suggestions for the SCOTT schema.

D. The PL/SQL block produces an error because the my_instance_analysis_mode_task task has not been reset to its

initial state.

Answer: D

Explanation/Reference:

http://blog.csdn.net/rlhua/article/details/16860279

 

QUESTION 127

Which statements are true regarding table compression? (Choose all that apply.)

A. It saves disk space and reduces memory usage.

B. It saves disk space but has no effect on memory usage.

C. It incurs extra CPU overhead during DML as well as direct loading operations.

D. It incurs extra CPU overhead during DML but not direct loading operations.

E. It requires uncompress operation during I/O.

Answer: AC

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11630

Consider Using Table Compression

As your database grows in size, consider using table compression. Compression saves disk space, reduces memory use in the database buffer cache, and can significantly speed query execution during reads. Compression has a cost in CPU overhead for data loading and DML. However, this cost might be offset by reduced I/O requirements.

 

 

QUESTION 128

You are working as a DBA on the decision support system. There is a business requirement to track and store all

transactions for at least three years for a few tables in the database. Automatic undo management is enabled in the

database. Which configuration should you use to accomplish this task?

A. Enable Flashback Data Archive for the tables.

B. Specify undo retention guarantee for the database.

C. Enable supplemental logging for the database.

D. Query V$UNDOSTAT to determine the amount of undo that will be generated and create an undo tablespace for

that size.

E. Create Flashback Data Archive on the tablespace on which the tables are stored.

Answer: A

Explanation/Reference:

 

 

QUESTION 129

USER_DATA is a nonencrypted tablespace that contains a set of tables with data. You want to convert all existing data in the USER_DATA tablespace and the new data into the encrypted format. Which methods would you use to achieve

this? (Choose all that apply.)

A. Encrypt the USER_DATA tablespace using the ALTER TABLESPACE statement so that all the data in the

tablespace is automatically encrypted.

B. Use Data Pump to transfer the existing data to a new encrypted tablespace.

C. Use ALTER TABLE..MOVE to transfer the existing data to a new encrypted tablespace.

D. Enable row movement for each table to be encrypted and then use ALTER TABLESPACE to encrypt the

tablespace.

E. Use CREATE TABLE AS SELECT to transfer the existing data to a new encrypted tablespace.

Answer: BCE

Explanation/Reference:

 

 

QUESTION 130

Exhibit:

View the Exhibit to observe the error.

You receive this error regularly and have to shut down the database instance to overcome the error. What can the

solution be to reduce the chance of this error in future, when implemented?

A. setting the PRE_PAGE_SGA parameter to TRUE

B. locking the SGA in memory

C. increasing the value of SGA_MAX_SIZE

D. automatic memory management

Answer: D

Explanation/Reference:

 

 

QUESTION 131

Which three are the valid statements in relation to SQL plan baselines? (Choose three.)

A. The plans generated for every SQL statement are stored in the SQL plan baseline by default.

B. The plans can be manually loaded to the SQL plan baseline.

C. The plan baselines are stored temporarily in the memory as long as the database instance is running.

D. The plans in the SQL plan baseline are verified and accepted plans.

E. For the SQL plan baselines to be accessible to the optimizer, the SYSAUX tablespace must be online.

Answer: BDE

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95122

 

15.2.1 Capturing SQL Plan Baselines

During the SQL plan baseline capture phase, the database detects plan changes and records the new plan so that it can be evolved (verified) by the database administrator. To this end, the database maintains a plan history for individual SQL statements. Because ad hoc SQL statements do not repeat and thus do not suffer performance degradation, the database maintains plan history only for repeatable SQL statements.

To recognize repeatable SQL statements, the database maintains a statement log that contains the SQL ID of various SQL statements that the optimizer has evaluated. The database recognizes a SQL statement as repeatable when it is parsed or executed again after it has been logged.

For each repeatable SQL statement, the database maintains a plan history that contains all plans generated by the optimizer. The set of all accepted plans in the plan history is the SQL plan baseline.

You can configure the SQL Plan Baseline Capture phase for automatic capture of plan history and SQL plan baselines for repeatable SQL statements. Alternatively, you can manually load plans as SQL plan baselines.

 

15.6 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.

 

 

QUESTION 132

You plan to use SQL Performance Analyzer to analyze the SQL workload. You created a SQL Tuning Set as a part of

the workload capturing. What information is captured as part of this process? (Choose all that apply.)

A. the system change number (SCN)

B. the execution plan

C. the execution context

D. the SQL text

E. the execution frequency

Answer: CDE

Explanation/Reference:

 

 

QUESTION 133

Which two statements are true regarding the Oracle Data Pump export and import operations?

(Choose two.)

A. You cannot export data from a remote database.

B. You can rename tables during an import operation.

C. You can overwrite existing dump files during an export operation.

D. You can compress the data during export but not the metadata because it is not supported.

Answer: BC

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/12749863

A.可以从远程数据库导出数据。

B.在执行导入操作期间重命名表。

C.在导出操作期间覆盖现有转储文件。

D.可选择在导出期间同时加密数据和元数据,仅加密数据,仅加密元数据,不加密数据或仅加密列。

 

 

QUESTION 134

You are installing Oracle Database 11g on a machine. When you run the installer, the Oracle Universal

Installer (OUI) shows the message that says one of the product-specific prerequisite checks has failed:

Checking available swap space requirements ...

Expected result: 1512MB

Actual Result: 1018MB

Check complete. The overall result of this check is: Failed <<<<

Problem: The system does not have the required swap space.

What happens to the installation in this situation?

A. It can be continued.

B. It resizes the swap space automatically when you proceed further.

C. It can be continued, but the instance cannot be started without increasing the swap space.

D. It shows a message saying one or more prerequisite checks have failed and the installation cannot proceed.

Answer: A

Explanation/Reference:

QUESTION 135

View the Exhibit.

You are creating an additional database by using the Database Configuration Assistant (DBCA). You opted

to create a General Purpose database and during the database creation realize that the Block Size field is

not enabled. What could be the reason for this?

A. You have chosen the File System option as the storage option.

B. You have chosen the Automatic Storage Management (ASM) as the storage option.

C. The data block size can be increased only when DBCA is invoked from Oracle Universal Installer (OUI).

D. You are using General Purpose template and not the Custom Database template to create the database.

Answer: D

Explanation/Reference:

 

 

QUESTION 136

Your database is functional with a peak load for the last one hour. You want to preserve the

performance statistics collected during this period to be used for comparison when you analyze the

performance of the database in the future. What action would you take to achieve this task?

A. Insert finding directives for ADDM tasks in the future.

B. Create a baseline on a pair of snapshots spanning the peak-load period.

C. Decrease the snapshot interval in the AWR to collect more snapshots during the peak-load period.

D. Set the snapshot retention period in the Automatic Workload Repository (AWR) to zero to avoid automatic purging of

snapshots.

Answer: B

Explanation/Reference:

QUESTION 137

Which is the correct description of a pinned buffer in the database buffer cache?

A. The buffer is currently being accessed.

B. The buffer is empty and has not been used.

C. The contents of the buffer have changed and must be flushed to the disk by the DBWn process.

D. The buffer is a candidate for immediate aging out and its contents are synchronized with the block contents on the

disk.

Answer: A

Explanation/Reference:

QUESTION 138

You have statistics collected for some selected tables. Your requirement is that the statistics for the

tables and all dependent indexes must not be overwritten by further statistics collection until a certain point

of time. How would you achieve this?

A. Lock statistics for the tables.

B. Change STALE_PERCENT to zero for the tables.

C. Set the TIMED_STATISTICS parameter to TRUE.

D. Set the STATISTICS_LEVEL parameter to BASIC.

E. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.

Answer: A

Explanation/Reference:

QUESTION 139

Which two kinds of failures make the Data Recovery Advisor (DRA) generate a manual checklist?

(Choose two.)

A. failure when no standby database is configured

B. failure because a data file is renamed accidentally

C. failure that requires no archive logs to be applied for recovery

D. failure due to loss of connectivity-for example, an unplugged disk cable

Answer: BD

Explanation/Reference:

 

 

QUESTION 140

View the Exhibit to examine the error that occurred during the database startup.

You opened an RMAN session for the database. To repair the failure, you executed the following command

as the first RMAN command:

RMAN>REPAIR FAILURE;

Which statement describes the consequence of this command?

A. The command performs the recovery and closes the failure.

B. The command only displays the advice and the RMAN script required for recovery.

C. The command executes the RMAN script to repair the failure and remove the entry from the Automatic Diagnostic

Repository (ADR).

D. The command produces an error because the ADVISE FAILURE command was not executed before the REPAIR

FAILURE command.

Answer: D

Explanation/Reference:

 

 

QUESTION 141

Which two files in the database can be configured for automatic backups by using the autobackup

feature in Recovery Manager (RMAN)? (Choose two.)

A. data files

B. control files

C. parameter file

D. online redo log files

E. server parameter file

Answer: BE

Explanation/Reference:

 

 

QUESTION 142

Automatic Shared Memory Management is disabled for your database instance. You realize that there

are cases of SQL statements performing poorly because of repeated parsing activity, resulting in

degradation of performance.

What would be your next step to improve performance?

A. Run the SQL Access Advisor

B. Run the Memory Advisor for the SGA.

C. Run the Memory Advisor for the PGA.

D. Run the Memory Advisor for the shared pool.

E. Run the Memory Advisor for the buffer cache.

Answer: E

Explanation/Reference:

 

 

QUESTION 143

Which two statements are true regarding the Automatic Diagnostic Repository (ADR) in Oracle Database 11g? (Choose

two.)

A. The BACKGROUND_DUMP_DEST initialization parameter overrides the DIAGNOSTIC_DEST initialization

parameter for the location of the alert log file.

B. A single ADR can support multiple ADR homes for different database instances.

C. The alert files are stored in XML file format in the TRACE directory of each ADR home.

D. If the environmental variable ORACLE_BASE is set, then DIAGNOSTIC_DEST is set to $ORACLE_BASE.

Answer: BD

Explanation/Reference:

 

 

QUESTION 144

While deploying a new application module, the software vendor ships the application software along with appropriate

SQL plan baselines for the new SQLs being introduced. Which two statements describe the consequences? (Choose

two.)

A. The optimizer does not generate new plans for the SQL statements for which the SQL plan baseline has been

imported.

B. The new SQL statements initially run with the plans that are known to produce good performance under standard

test configuration.

C. The plan baselines can be evolved over time to produce better performance.

D. The newly generated plans are directly placed into the SQL plan baseline without being verified.

Answer: BC

Explanation/Reference:

 

 

 

QUESTION 145

Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets

(STS) from the previous version. After changing the OPTIMIZER_FEATURE_ENABLE parameter to 10.2.0.4 and

running the SQL Performance Analyzer, you observed performance regression for a few SQL statements.

What would you do with these SQL statements?

A. Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE to prevent the plans from being loaded to the SQL plan baseline.

B. Capture the plans from the previous version using STS and then load them into the stored outline.

C. Set OPTIMIZER_USE_PLAN_BASELINES to FALSE to prevent the use of regressed plans.

D. Capture the plans from the previous version using STS and then load them into SQL Management Base (SMB).

Answer: D

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/16369811

SQL 性能分析器和SQL 计划基准方案

上一个图中所述的第一种方法的一个变体是通过使用SQL 性能分析器。可以捕获STS  Oracle Database 11 g 之前的计划,并将这些计划导入到Oracle Database 11 g。然后,将初始化参数optimizer_features_enable设置为10g,使优化程序将此数据库当成10g Oracle DB 进行操作。接下来,为STS  运行SQL 性能分析器。运行完成后,将初始化参数optimizer_features_enable设置回11g,并为STS  重新运行SQL 性能分析器。

SQL 性能分析器将生成一个报表,列出了从10g 11g 其计划已发生回归的SQL 语句。

对于那些SQL 性能分析器显示的由于新优化程序版本而发生性能回归的SQL 语句,可以使用STS  捕获其计划,然后将这些计划加载到SMB 中。

此方法提供了计划植入进程的最佳形式,因为它有助于在保留数据库升级所带来的性能改进的同时,防止性能回归。

 

 

 

QUESTION 146

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. The PURCHASE_ORDER_ITEMS table inherits the partitioning key from the parent table by automatically

duplicating the key columns.

C. Both PURCHASE_ORDERS and PURCHASE_ORDER_ITEMS tables are created with four partitions each.

D. Partition maintenance operations performed on the PURCHASE_ORDER_ITEMS table are automatically reflected

in the PURCHASE_ORDERS table.

E. Partitions of the PURCHASE_ORDER_ITEMS table exist in the same tablespaces as the partitions of the

PURCHASE_ORDERS table.

Answer: CE

Explanation/Reference:

答案解析:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm#i1006455

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.

Example 4-9 creates a parent table orders which is range-partitioned on order_date. The reference-partitioned child table order_items is created with four partitions, Q1_2005Q2_2005Q3_2005, and Q4_2005, where each partition contains the order_items rows corresponding to orders in the respective parent partition.

Example 4-9 Creating reference-partitioned tables

CREATE TABLE orders

    ( order_id           NUMBER(12),

      order_date         TIMESTAMP WITH LOCAL TIME ZONE,

      order_mode         VARCHAR2(8),

      customer_id        NUMBER(6),

      order_status       NUMBER(2),

      order_total        NUMBER(8,2),

      sales_rep_id       NUMBER(6),

      promotion_id       NUMBER(6),

      CONSTRAINT orders_pk PRIMARY KEY(order_id)

    )

  PARTITION BY RANGE(order_date)

    ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),

      PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),

      PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),

      PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))

    );

 

CREATE TABLE order_items

    ( order_id           NUMBER(12) NOT NULL,

      line_item_id       NUMBER(3)  NOT NULL,

      product_id         NUMBER(6)  NOT NULL,

      unit_price         NUMBER(8,2),

      quantity           NUMBER(8),

      CONSTRAINT order_items_fk

      FOREIGN KEY(order_id) REFERENCES orders(order_id)

    )

    PARTITION BY REFERENCE(order_items_fk);

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.

 

 

QUESTION 147  不理解

You are managing an Oracle Database 11g instance with ASM storage. You lost an ASM disk group DATA. You have RMAN backup of data as well as ASM metadata backup. You want to re-create the missing disk group by using the ASMCMD md_restore command. Which of these methods would you use to achieve this? (Choose all that apply.)

A. Restore the disk group with the exact configuration as the backed-up disk group, using the same disk group name, same set of disks, failure group configurations, and data on the disk group.

B. Restore the disk group with the exact configuration as the backed-up disk group, using the same disk group name, same set of disks, and failure group configurations.

C. Restore the disk group with changed disk group specification, failure group specification, disk group name, and other disk attributes.

D. Restore metadata in an existing disk group by passing the existing disk group name as an input parameter.

Answer: B, C, D

 

 

 

 

 

 

QUESTION 148

Examine the following RMAN command:

BACKUP DATABASE

TAG TESTDB

KEEP UNTIL 'SYSDATE+1'

RESTORE POINT TESTDB06;

Which two statements are true regarding the backup taken by using the above RMAN command? (Choose two.)

A. Archived redo logs are backed up only if the database is open during the backup.

B. The backup is deleted after one day, regardless of the default retention policy settings.

C. The backup becomes obsolete after one day, regardless of the default retention policy settings.

D. Only data files and the control file are backed up.

Answer: AC

Explanation/Reference:

 

QUESTION 149

Which statement describes the information returned by the DBMS_SPACE.SPACE_USAGE procedure for LOB space

usage?

A. It returns space usage of only SecureFile LOB chunks.

B. It returns space usage of only BasicFile LOB chunks.

C. It returns both BasicFile and SecureFile LOB space usage for both partitioned and nonpartitioned tables.

D. It returns both BasicFile and SecureFile LOB space usage for only nonpartitioned tables.

Answer: A

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#ARPLS68116

感觉这道题有歧义,如果题目指定为SECUREFILE LOB space usage,则A是正确的。

SPACE_USAGE Procedures

The first form of the procedure shows the space usage of data blocks under the segment High Water Mark. You can calculate usage for LOBs, LOB PARTITIONSand LOB SUBPARTITIONS. This procedure can only be used on tablespaces that are created with auto segment space management. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. Note that this overload cannot be used on SECUREFILE LOBs.

The second form of the procedure returns information about SECUREFILE LOB space usage. It will return the amount of space in blocks being used by all theSECUREFILE LOBs in the LOB segment. The procedure displays the space actively used by the LOB column, freed space that has retention expired, and freed space that has retention unexpired. Note that this overload can be used only on SECUREFILE LOBs.

 

 

 

QUESTION 150

Exhibit:

View the Exhibit to examine the parameter values.

You are planning to set the value for the MEMORY_TARGET parameter of your database instance. What value would

you assign?

A. 1440 MB

B. 90 MB

C. 362 MB

D. 272 MB

Answer: C

Explanation/Reference:

 

 

 

QUESTION 151

You have the following requirements in relation to the detection of block corruption for your database instance:

1. Check for logical self-consistency of data blocks when modified in memory.

2. Checksums are calculated before and after the block change.

3. Checks are performed for the lost writes to the physical standby database.

Which method would help you perform the above checks automatically?

A. Set the DB_LOCK_CHECKSUM parameter to TYPICAL.

B. Set the DB_SECUREFILE parameter to PERMITTED.

C. Set the DB_LOST_WRITE_PROTECT parameter to TYPICAL.

D. Set the DB_ULTRA_SAFE parameter to DATA_ONLY.

Answer: D

Explanation/Reference:

该参数设置其它参数的默认值来控制保护的级别

DB_ULTRA_SAFE = {OFF | DATA_ONLY | DATA_AND_INDEX}

1. OFF,DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, DB_LOST_WRITE_PROTECT任何一个被显示设置时,不会有任何改变.

2. DATA_ONLY

    DB_BLOCK_CHECKING = MEDIUM

    DB_BLOCK_CHECKSUM = FULL

    DB_LOST_WRITE_PROTECT = TYPICAL

3. DATA_AND_INDEX

    DB_BLOCK_CHECING = FULL

    DB_BLOCK_CHECKSUM = FULL

    DB_LOST_WRITE_PROTECT = TYPICAL

 

DB_BLOCK_CHECKING specifies whether or not Oracle performs block checking for database blocks.

Values:

  • OFF or FALSE

No block checking is performed for blocks in user tablespaces. However, semantic block checking for SYSTEM tablespace blocks is always turned on.

  • LOW

Basic block header checks are performed after block contents change in memory (for example, after UPDATE or INSERT statements, on-disk reads, or inter-instance block transfers in Oracle RAC).

  • MEDIUM

All LOW checks and full semantic checks are performed for all objects except indexes (whose contents can be reconstructed by a drop+rebuild on encountering a corruption).

  • FULL or TRUE

All LOW and MEDIUM checks and full semantic checks are performed for all objects.

Oracle checks a block by going through the data in the block, making sure it is logically self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload and the parameter value. Specific DML overhead may be higher. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable.

For backward compatibility, the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.

 

 

 

 

QUESTION 152

During the installation of Oracle Database 11g, you do not set ORACLE_BASE explicitly. You selected the option to

create a database as part of the installation. How would this environment variable setting affect the installation?

A. The installation terminates with an error.

B. The installation proceeds with the default value but a message would be generated in the alert log file.

C. The installation proceeds with the default value but it would not be an OFA-compliant database.

D. The installation proceeds with the default value without warnings and errors.

Answer: B

Explanation/Reference:

 

 

 

QUESTION 153

You are managing an Oracle Database 11g database. You want to take the backup of MULT_DATA, a big file

tablespace of size 100 TB on tape drive, but you have tape drives of only 10 GB each. Which method would accomplish the task quickly and efficiently?

A. parallel image copy backup

B. backup with MAXPIECESIZE configured for the channel

C. parallel backup with MAXPIECESIZE configured for the channel

D. intrafile parallel backup

Answer: D

Explanation/Reference:

RMAN 的新增功能

对超大型文件应用Intrafile  并行备份和还原

在备份单一大型数据文件时,现在可以使用多个并行服务器进程和通道来有效地分配工作量。这种使用多个部分的方法改善了备份的性能。

 

 

QUESTION 154

You perform the following activities during the database upgrade from Oracle Database 10g to Oracle Database 11g:

1. Capture plans for a SQL workload into a SQL Tuning Set (STS) before upgrade.

2. Load these plans from the STS into the SQL plan baseline immediately after the upgrade.

What is the reason for performing these activities?

A. to prevent plan capturing when the SQL statement is executed after the database upgrade

B. to keep the plan in the plan history so that it can be used when the older version of the optimizer is used

C. to minimize plan regression due to the use of a new optimizer version

D. to completely avoid the use of new plans generated by a new optimizer version

Answer: C

Explanation/Reference:

The SQL management base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespaceIt 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.

 

 

QUESTION 155

Which steps are mandatory to enable Direct NFS?

1. Mount all required file systems using the kernel NFS driver.

2. Create an oranfstab file containing the attributes for each NFS server to be accessed using Direct NFS.

3. Replace the ODM library libodm11.so_stub with libodm11.so.

A. 2 and 3

B. 1 and 2

C. 1, 2 and 3

D. 1 and 3

Answer: D

Explanation/Reference:

http://czmmiao.iteye.com/blog/1965839

Enabling Direct NFS
You must follow these steps to enable Direct NFS:
1.You must mount all NFS mount points with your kernel NFS client. You must make sure you mount any file systems you plan on using through ODM NFS and make the file systems available to Oracle over regular NFS mounts.
2.If you want to specify Oracle-specific options to Direct NFS, you’ll need an oranfstab file. This is an optional step. The oranfstab file must have the following attributes so the database can access all NFS servers through Direct NFS:

3. You must replace the standard ODM library, libnfsodm10.so, with the ODM NFS library, as shown here:
$ cd $ORACLE_HOME/lib
$ cp libodmll.so libodmll.so_stub
$ ln -s libnfsodm11.so libodm11.so
You can disable the Direct NFS client by using any of the following three methods.
Delete the oranfstab file.
Replace the ODM NFS library with the stub libodm11.so file.
Modify the oranfstab file by deleting the specific NFS server or the network paths to the NFS server.
If the database can’t open the NFS server using Direct NFS, it will use the operating system kernel client instead.

 

QUESTION 156

Exhibit:

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 used by the optimizer only if a hint is specified in the query statement and is maintained during DML

operations.

B. The index is not used by the optimizer but is maintained during DML operations.

C. The index is not used by the optimizer and is not 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: B

Explanation/Reference:

 

 

QUESTION 157

You executed the following commands:

SQL> ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = false;

SQL> EXECUTE DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH','false');

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SH', 'CUSTOMERS');

Which statement is correct regarding the above statistics collection on the SH.CUSTOMERS table in the above session?

A. The statistics are stored in the pending statistics table in the data dictionary.

B. The statistics are treated as the current statistics by the optimizer for the current sessions only.

C. The statistics are treated as the current statistics by the optimizer for all sessions.

D. The statistics are temporary and used by the optimizer for all sessions until this session terminates.

Answer: A

Explanation/Reference:

http://blog.csdn.net/renfengjun/article/details/8204228

该知识的中文介绍

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF94725

By default, the optimizer uses the published statistics stored in the data dictionary views. If you want the optimizer to use the newly collected pending statistics, then set the initialization parameter OPTIMIZER_USE_PENDING_STATISTICS to TRUE (the default value is FALSE), and run a workload against the table or schema:

ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

 

 

Pending statistics

11gr2开始,可以使用下面类型的操作来收集优化器统计信息:
1.            
自动发布收集的统计信息在收集操作结束以后(默认选项publish
2.            
保存新的统计信息,并且待定(暂不发布pending
这个特性可以将新收集的统计信息置为待定状态,所以可以先验证新统计信息的有效性然后再发布。
可以使用下面的命令来查看是否默认发布新的统计信息。
sys@DAVID> SELECTDBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;
PUBLISH
---------------------------------------------------------------------------------------
TRUE
返回为true或者falseTrue表示新的统计信息收集后即发布,也就是说优化器会使用新的统计信息来生查询计划,False表示收集的统计信息会 被放入USER_TAB_PENDING_STATS USER_IND_PENDING_STATS,并且不会立刻被优化器使用,为待定状态。

 

 

QUESTION 158

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. Take an immediate AWR snapshot.

B. Decrease the window size for the baseline.

C. Decrease the expiration time for the baseline.

D. Compute the baseline statistics.

Answer: D

Explanation/Reference:

http://docs.oracle.com/cd/E11882_01/server.112/e41573/autostat.htm#PFGRF94177

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.

 

 

QUESTION 159

You are managing the APPPROD database as a DBA. You plan to duplicate this database in the same system with the name DUPDB.You issued the following RMAN commands to create a duplicate database:

RMAN> CONNECT target sys/sys@APPPROD

RMAN> DUPLICATE TARGET DATABASE TO dupdb FROM ACTIVE DATABASE

DB_FILE_NAME_CONVERT '/oracle/oradata/prod/', '/scratch/oracle/oradata/dupdb/'

SPILE PARAMETER_VALUE_CONVERT '/oracle/oradata/prod/','/scratch/oracle/oradata/dupdb/'

SET SGA_MAX_SIZE = '300M'

SET SGA_TARGET = '250M'

SET LOG_FILE_NAME_CONVERT '/oracle/oradata/prod/redo/', '/scratch/oracle/oradata/dupdb/redo/';

Which three are the prerequisites for the successful execution of the above command? (Choose three.)

A. RMAN should be connected to both the instances as SYSDBA.

B. The target database backups should be copied to the source database backup directories.

C. The password file must exist for the source database and have the same SYS user password as the target.

D. The target database should be in ARCHIVELOG mode if it is open.

E. The source database should be open.

Answer: ACD

Explanation/Reference:

 

E,源数据库应该为打开,错误,也可以是mount状态。

B,目标数据库的备份必须拷贝到源数据库目录。错误,上面的复制数据库语句是从活动的数据库来复制的,不需要备份。

Prerequisites Specific to Active Database Duplication

When you execute DUPLICATE with FROM ACTIVE DATABASE, at least one normal target channel and at least one AUXILIARY channel are required.

When you connect RMAN to the source database as TARGET, you must specify a password, even if RMAN uses operating system authentication. The source database must be mounted or open. If the source database is open, then archiving must be enabled. If the source database is not open, then it must have been shut down consistently.

When you connect RMAN to the auxiliary instance, you must provide a net service name. This requirement applies even if the auxiliary instance is on the local host.

The source database and auxiliary instances must use the same SYSDBA password, which means that both instances must have password files. You can create the password file with a single password so you can start the auxiliary instance and enable the source database to connect to it.

The DUPLICATE behavior for password files varies depending on whether your duplicate database acts as a standby database. If you create a duplicate database that is not a standby database, then RMAN does not copy the password file by default. You can specify the PASSWORD FILE option to indicate that RMAN should overwrite the existing password file on the auxiliary instance. If you create a standby database, then RMAN copies the password file to the standby host by default, overwriting the existing password file. In this case, the PASSWORD FILE clause is not necessary.

You cannot use the UNTIL clause when performing active database duplication. RMAN chooses a time based on when the online data files have been completely copied, so that the data files can be recovered to a consistent point in time.

 

QUESTION 160

You performed the RMAN database backup with the KEEP option. Which two statements are true regarding this

backup? (Choose two.)

A. The backup contains data files, the server parameter file, and the control file even if the control file autobackup is

disabled.

B. The KEEP option overrides the configured retention policy.

C. The backup contains only data files and archived redo log files.

D. The KEEP option is an attribute of an individual backup piece.

Answer: AB

Explanation/Reference:

 

 

QUESTION 161

You set the following parameters in the parameter file and restarted the database:

MEMORY_MAX_TARGET=0

MEMORY_TARGET=500M

PGA_AGGREGATE_TARGET=90M

SGA_TARGET=270M

Which two statements are true regarding these parameters after the database instance is restarted? (Choose two.)

A. The lower bounds of PGA_AGGREGATE_TARGET and SGA_TARGET parameters are set to 90 MB and 270 MB,

respectively.

B. The value of the MEMORY_MAX_TARGET parameter remains zero till it is changed manually.

C. The MEMORY_MAX_TARGET parameter is automatically set to 500 MB.

D. The PGA_AGGREGATE_TARGET and SGA_TARGET parameters are automatically set to zero.

Answer: AC

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/12493791

只需要设置一个目标内存大小初始化参数( MEMORY_TARGET )  和一个最大内存大小初始化参数( MEMORY_MAX_TARGET),数据库就会根据处理需求在SGA 与实例 PGA 之间动态交换内存。

自动内存管理是用两个初始化参数进行配置的:

MEMORY_TARGET:动态控制SGAPGA时,Oracle总共可以使用的共享内存大小,这个参数是动态的,因此提供给Oracle的内存总量是可以动态增大,也可以动态减小的。它不能超过MEMORY_MAX_TARGET参数设置的大小。默认值是0

 MEMORY_MAX_TARGET:这个参数定义了MEMORY_TARGET最大可以达到而不用重启实例的值,如果没有设置MEMORY_MAX_TARGET值,默认等于MEMORY_TARGET的值。

使用动态内存管理时,SGA_TARGETPGA_AGGREGATE_TARGET代表它们各自内存区域的最小设置,要让Oracle完全控制内存管理,这两个参数应该设置为0。但并不会因为设置了MEMORY_TARGET,这两个参数就会自动变回0

 

 

QUESTION 162

You are managing an Oracle Database 11g database. You want to take a backup on tape drives of the USERS

tablespace that has a single data file of 900 MB. You have tape drives of 300 MB each. To accomplish the backup, you issued the following RMAN command:

RMAN>BACKUP

SECTION SIZE 300M

TABLESPACE users;

What configuration should be effected to accomplish faster and optimized backups by using the above command?

A. The SBT channel must be configured, with the default parallelism setting for the SBT device set to 1.

B. The COMPATIBLE initialization parameter for the database instance must be set to at least 10.0.

C. The SBT channel must be configured, with the parallelism setting for the SBT device set to 3.

D. The SBT channel must be configured, with the MAXPIECESIZE set to 300 MB.

Answer: C

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta007.htm#RCMRF90025

根据Note: You cannot use SECTION SIZE with MAXPIECESIZE or with INCREMENTAL LEVEL 1.这句话,说明D错误

根据RMAN channels can process each step independently and in parallel,应该选择C,可以并行备份。

 

SECTION SIZE sizeSpec

Specifies the size of each backup section produced during a data file backup.

By setting this parameter, RMAN can create a multisection backup. In a multisection backup, RMAN creates a backup piece that contains one file section, which is a contiguous range of blocks in a file. All sections of a multisection backup are the same size. You can create a multisection backup for a data file, but not a data file copy.

File sections enable RMAN to create multiple steps for the backup of a single large data file. RMAN channels can process each step independently and in parallel, with each channel producing one section of a multisection backup set.

If you specify a section size that is larger than the size of the file, then RMAN does not use multisection backup for the file. If you specify a small section size that would produce more than 256 sections, then RMAN increases the section size to a value that results in exactly 256 sections.

Depending on where you specify this parameter in the RMAN syntax, you can specify different section sizes for different files in the same backup job.

Note: You cannot use SECTION SIZE with MAXPIECESIZE or with INCREMENTAL LEVEL 1.

 

 

QUESTION 163

Exhibit:

View the Exhibit to examine the error while executing the REPAIR FAILURE command in an RMAN session.

What is the reason for this error?

A. Another repair session is running concurrently.

B. The failure ID has not been mentioned in the command for data file 5.

C. There are new failures recorded in the Automatic Diagnostic Repository (ADR).

D. The ADVISE FAILURE command has not been issued before the REPAIR FAILURE command.

Answer: A

Explanation/Reference:

 

 

QUESTION 164

Which statement describes the effect of table redefinition on the triggers attached to the table?

A. All triggers on the table are invalidated and are automatically revalidated with the next DML execution on the table.

B. All triggers on the table are invalidated and must be manually recompiled before the next DML execution on the

table.

C. All triggers on the table remain valid.

D. Only triggers that are affected by the changes to the structure of the table are invalidated and automatically

revalidated with the next DML execution on the table.

Answer: A

Explanation/Reference:

http://blog.csdn.net/jgmydsai/article/details/37520209

感觉描述有歧义,在线重定义后,trriger关联的是重定义前的表,而不是重定义后的表。

 

 

QUESTION 165

Evaluate the following code:

SQL>VARIABLE task_name VARCHAR2(255);

SQL>VARIABLE sql_stmt VARCHAR2(4000);

SQL>BEGIN

:sql_stmt := 'SELECT COUNT(*) FROM customers WHERE cust_state_province =''CA''';

:task_name := 'MY_QUICKTUNE_TASK';

DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_name, :sql_stmt);

END;

What is the outcome of this block of code?

A. It creates a task and workload but does not execute the task.

B. It produces an error because the SQL Tuning Set has not been created.

C. It produces an error because a template has not been created.

D. It creates a task and workload, and executes the task.

Answer: D

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/advisor.htm#PFGRF94911

Performing a Quick Tune

To tune a single SQL statement, the QUICK_TUNE procedure accepts as its input a task_name and a SQL statement. The procedure creates a task and workload and executes this task. There is no difference in the results from using QUICK_TUNE. They are exactly the same as those from using EXECUTE_TASK, but this approach is easier to use when there is only a single SQL statement to be tuned. The syntax is as follows:

DBMS_ADVISOR.QUICK_TUNE (

   advisor_name           IN VARCHAR2,

   task_name              IN VARCHAR2,

   attr1                  IN CLOB,

   attr2                  IN VARCHAR2 := NULL,

   attr3                  IN NUMBER := NULL,

   task_or_template       IN VARCHAR2 := NULL);

The following example shows how to quick tune a single SQL statement:

VARIABLE task_name VARCHAR2(255);

VARIABLE sql_stmt VARCHAR2(4000);

EXECUTE :sql_stmt := 'SELECT COUNT(*) FROM customers

                      WHERE cust_state_province =''CA''';

EXECUTE :task_name  := 'MY_QUICKTUNE_TASK';

EXECUTE DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,

              :task_name, :sql_stmt);

 

QUESTION 166

What is the advantage of setting the ASM-preferred mirror read for the Stretch cluster configuration?

A. It improves performance by reading from a copy of an extent closest to the node.

B. This feature enables much faster file opens.

C. It improves performance as fewer extent pointers are needed in the shared pool.

D. It improves resync operations.

Answer: A

Explanation/Reference:

 

 

QUESTION 167

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 a new nonfixed SQL plan baseline as a nonfixed plan.

D. The tuned plan is added to the fixed SQL plan baseline as a nonfixed plan.

Answer: D

Explanation/Reference:

官方参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95120

15.4 Using Fixed SQL Plan Baselines

A SQL plan baseline is fixed when it contains at least one enabled plan whose FIXED attribute is set to YES. You can use fixed SQL plan baselines to fix the set of possible plans (usually one plan) for a SQL statement, or migrate an existing stored outline by loading the "outlined" plan as a fixed plan.

If a fixed SQL plan baseline also contains non-fixed plans, then the optimizer gives preference to fixed plans over non-fixed ones. Thus, the optimizer picks the fixed plan with the least cost even though a non-fixed plan may have an even lower cost. If none of the fixed plans is reproducible, then the optimizer picks the best non-fixed plan.

The optimizer does not add new plans to a fixed SQL plan baseline. Because the optimizer does not automatically add new plans, the database does not evolve a fixed SQL plan baseline when you execute DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. However, you can evolve a fixed SQL plan baseline by manually loading new plans into it from the shared SQL area or a SQL tuning set.

When you tune a SQL statement with a fixed SQL plan baseline using SQL Tuning Advisor, a SQL profile recommendation has special meaning. When the SQL profile is accepted, the database adds the tuned plan to the fixed SQL plan baseline as a non-fixed plan. However, as described above, the optimizer does not use the tuned plan when a reproducible fixed plan is present. Therefore, the benefit of SQL tuning may not be realized. To enable the use of the tuned plan, manually alter the tuned plan to a fixed plan by setting its FIXED attribute to YES.

 

 

QUESTION 168

You plan to collect the Automatic Workload Repository (AWR) data every Monday morning for a month. You want

Oracle Database to automatically create a baseline every Monday and remove the old baseline. What is the correct

action to achieve this?

A. Create and populate a SQL Tuning Set from the AWR on every Monday.

B. Change the RETENTION setting for the AWR snapshots to 7 days on Monday.

C. Create a repeating baseline template.

D. Insert a finding directive for future ADDM tasks.

Answer: C

Explanation/Reference:


http://docs.oracle.com/cd/E11882_01/server.112/e41573/autostat.htm#PFGRF94180

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.

 

 

QUESTION 169

Which three statements are true regarding persistent lightweight jobs? (Choose three.)

A. Persistent lightweight jobs modify several tables in the data dictionary to generate a lot of redo.

B. The user cannot set privileges on persistent lightweight jobs.

C. Persistent lightweight jobs are useful when users need to create a large number of jobs in a short time.

D. Persistent lightweight jobs are useful when users need to create a small number of jobs that run infrequently.

E. The use of a template is mandatory to create persistent lightweight jobs.

Answer: BCE

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/16841885

? 持久轻量作业在创建/删除时间方面有很大改进,因为它没有创建数据库对象的开销。每个轻量作业都是使用作业模板创建的,并作为程序进行存储。因为持久轻量作业在运行时要向磁盘写入状态信息,所以在执行时应该只有少量改进。持久轻量作业有多种限制:

- 用户无法设置对这些作业的权限,它们将从父作业模板中继承其权限--B

- 必须使用模板,无法创建完全自包含的持久轻量作业。---E

- 只有一些特定的作业属性是可设置的,如JOB_ARGUMENTS

如果用户需要在非常短的时间内创建大量作业(每秒10 -100 个作业),并且有一组程序(作业模板)可供使用,则最适合使用轻量作业。--C

 

 

QUESTION 170

In which two aspects does hot patching differ from conventional patching? (Choose two.)

A. It takes more time to install or uninstall compared with conventional patching.

B. It can be installed and uninstalled via OPatch unlike conventional patching.

C. It does not require down time to apply or remove unlike conventional patching.

D. It is not persistent across instance startup and shutdown unlike conventional patching.

E. It consumes more memory compared with conventional patching.

Answer: CE

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/16574625

热补丁注意事项

? 可能不是所有平台上都有热补丁程序。当前在以下平台上有热补丁程序:

– Linux x86 

– Linux x86-64 

– Solaris SPARC64 

? 要消耗一些额外的内存

确切的内存数取决于:

补丁程序的大小

当前运行的Oracle 进程数

最小内存数:每个Oracle 进程大约占一个OS 页面

 

 

QUESTION 171

View the following SQL statements:

Transaction T1

INSERT INTO hr.regions VALUES (5,'Pole');

COMMIT;

Transaction T2

UPDATE hr.regions SET region_name='Poles' WHERE region_id = 5; COMMIT;

Transaction T3

UPDATE hr.regions SET region_name='North and South Poles' WHERE region_id = 5;

You want to back out transaction T2. Which option would you use?

A. It is possible, but transaction T3 also backs out.

B. It is possible with the NOCASCADE_FORCE option.

C. It is not possible because it has conflicts with transaction T3.

D. It is possible with the NONCONFLICT_ONLY option.

Answer: B

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS627

NOCASCADE_FORCE - The user forcibly backs out the given transactions without considering the dependent transactions. The RDBMS executes the UNDO SQL for the given transactions in reverse order of their commit times. If no constraints break, and the result is satisfactory, the user can eitherCOMMIT the changes or else ROLL BACK.

 

NOCASCADE_FORCE

Backs out specified transactions, ignoring dependent transactions. Server runs undo SQL statements for specified transactions in reverse order of commit times.

If no constraints break and you are satisfied with the result, you can commit the changes; otherwise, you can roll them back. 

 

 

QUESTION 172

Which statement is true when Automatic Workload Repository (AWR) baselines are created using baseline templates?

A. AWR baselines are always created with infinite expiration duration.

B. AWR baselines are always created as repeating baselines.

C. AWR baselines are always created using the Automatic Workload Repository (AWR) retention period as expiration

duration.

D. AWR baselines can be created on the basis of two time values.

Answer: D

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e40540/cncptdba.htm#CNCPT89120

An AWR baseline is a collection of statistic rates usually taken over a period when the system is performing well at peak load. You can specify a pair or range of AWR snapshots as a baseline. By using an AWR report to compare statistics captured during a period of bad performance to a baseline, you can diagnose problems.

 

 

QUESTION 173

Your database initialization parameter file has the following entry:

SEC_MAX_FAILED_LOGIN_ATTEMPTS=3

Which statement is true regarding this setting?

A. It drops the connection after the specified number of login attempts fail for any user.

B. It is enforced only if the password profile is enabled for the user.

C. It locks the user account after the specified number of attempts.

D. It drops the connection after the specified number of login attempts fail only for users who have the SYSDBA

privilege.

Answer: A

Explanation/Reference:

 

 

QUESTION 174

The user HR receives the following error while inserting data into the TTK table:

ERROR at line 1:

ORA-01653: unable to extend table HR.TTK by 128 in tablespace SMD Upon investigation, you find that

SMD is a small file tablespace.

Which three action would allow the user to insert data? (Choose three.)

A. Add a data file to the SMD tablespace.

B. Add a data file to the temporary tablespace associated with the user HR.

C. Resize the data file associated with the SMD tablespace to make it larger.

D. Alter the data file associated with the SMD tablespace to grow automatically.

E. Change the segment space management for the SMD tablespace to automatic.

Answer: ACD

Explanation/Reference:

 

 

QUESTION 175

In which situation may the UNDO_RETENTION parameter be ignored, even if it is set to a value?

A. when the data file of the undo tablespace is autoextensible

B. when there are more than one undo tablespaces available in the database

C. when the undo tablespace is of a fixed size and retention guarantee is not enabled

D. when the undo tablespace is autoextensible and retention guarantee is not enabled

Answer: C

Explanation/Reference:

 

 

QUESTION 176

Which two operations can be performed on an external table? (Choose two.)

A. Create a view on the table.

B. Create an index on the table.

C. Create a synonym on the table.

D. Add a virtual column to the table.

E. Update the table using the UPDATE statement.

F. Delete rows in the table using the DELETE command.

Answer: AC

Explanation/Reference:

 

 

QUESTION 177

After performing a clean shut down of the database instance for maintenance, you mount the database

and then execute a command to open the database:

SQL> ALTER DATABASE OPEN;

Which two statements are true? (Choose two.)

A. The online redo log files and online data files are opened.

B. All the online data file headers are checked for consistency.

C. Instance recovery is performed before opening the database.

D. The path and existence of all the log file members are checked.

Answer: AB

Explanation/Reference:

 

 

QUESTION 178

For which database operation would you need the database to be in the MOUNT state?

A. renaming the control files

B. re-creating the control files

C. dropping a user in your database

D. dropping a tablespace in your database

E. configuring the database instance to operate in the ARCHIVELOG or NOARCHIVELOG modes

Answer: E

Explanation/Reference:

 

 

QUESTION 179

Which two statements are true about checkpointing? (Choose two.)

A. The checkpoint frequency decreases with the smaller redo log file size.

B. It ensures that all committed data is written to the data files during normal shutdown.

C. The frequent full checkpoint in a database can cause the overall degradation of the database performance.

D. It prompts the Checkpoint (CKPT) process to write data to the data files and redo information to the online redo log

files.

Answer: BC

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/12616383

A.当重做日志文件太小的情况下,会频繁的切换日志,检查点信息写入数据文件的频率会增加而不是减少。A错。

B.在正常关库,会生成完全检查点,CKPT会让DBWn进程将所有已经提交的都写进数据文件,B对。

C.频繁的发生检查点会增加I/O的读写次数,造成数据库性能的下降,故C对。

D.CKPT是将重做信息写进重做日志里,DBWn是将数据写进数据文件,而不是CKPT.D错。

每隔三秒(或更加频繁),CKPT 进程就会在控制文件中存储一次数据,以记录DBWn已将哪些修改的数据块从SGA 写到磁盘。这称为增量检查点。检查点的用途是标识联机重做日志文件开始进行实例恢复的位置(这个位置称为检查点位置)。如果发生日志切换,则CKPT 进程还会将此检查点信息写入数据文件头。

存在检查点是由于下列原因:

? 确保内存中已修改的数据块能够定期写入到磁盘,这样在系统或数据库出现故障时就不会丢失数据

? 减少实例恢复所需的时间(只需要处理上一个检查点之后的联机重做日志文件条目,即可进行恢复。)

? 确保所有已提交的数据在关闭期间会被写入数据文件

CKPT 进程写入的检查点信息包括检查点位置、系统更改编号(SCN)、联机重做日志文件中恢复开始的位置、有关日志的信息等等。

注:CKPT 进程不会将数据块写入磁盘或将重做数据块写入联机重做日志文件。

 

 

QUESTION 180

In which of the scenarios will the DBA perform recovery? (Choose all that apply.)

A. The alert log file is corrupted.

B. A tablespace is accidentally dropped.

C. One of the redo log members is corrupted.

D. A database user terminates the session abnormally.

E. The hard disk on which the data file is stored is corrupted.

Answer: BE

Explanation/Reference:

 

 

QUESTION 181

A user, who is authenticated externally, logs in to a remote machine and connects to the database

instance. What action would you take to ensure that a user cannot connect to the database instance by

merely logging in to a remote machine?

A. Set REMOTE_OS_ROLES to FALSE.

B. Set the OS_ROLES parameter to FALSE.

C. Set the REMOTE_OS_AUTHENT parameter to FALSE.

D. Set the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE.

Answer: C

Explanation/Reference:

参考:http://blog.csdn.net/rlhua/article/details/12277557

限制远程数据库验证:默认情况下,REMOTE_OS_AUTHENT参数设置为FALSE一定不要更改这个设置,除非可确保所有客户机都能适当地验证用户。在引入了安全外部口令存储(在Oracle Database 10g发行版2 中提供)后,允许远程操作系统验证就有了令人信服的理由。

在远程验证过程中:

? 数据库用户通过外部方式来进行验证

? 远程系统验证用户

? 用户登录数据库,不必接受另外的验证

注:如果撤销了一些权限,务必全面测试应用程序。

Initialization Parameter

Default Setting

Description

OS_AUTHENT_PREFIX

OPS$

Specifies a prefix that Oracle Database uses to identify users attempting to connect to the database. Oracle Database concatenates the value of this parameter to the beginning of the user operating system account name and password. When a user attempts a connection request, Oracle Database compares the prefixed username with user names in the database.

REMOTE_LISTENER

No default setting

Specifies a network name that resolves to an address or address list of Oracle Net remote listeners (that is, listeners that are not running on the same computer as this instance). The address or address list is specified in the tnsnames.ora file or other address repository as configured for your system.

REMOTE_OS_AUTHENT

FALSE

Specifies whether remote clients will be authenticated with the value of the OS_AUTHENT_PREFIX parameter.

REMOTE_OS_ROLES

FALSE

Specifies whether operating system roles are allowed for remote clients. The default value, FALSE, causes Oracle Database to identify and manage roles for remote clients.

官方参考:http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_network_secure.htm#sthref346

 

 

QUESTION 182

In which cases is reference partitioning effective in enhancing performance?

A. It is effective only in partition pruning.

B. It is effective in both partition pruning as well as partitionwise joins irrespective of whether the query predicates are

different from or identical to the partitioning key.

C. It is effective in both partition pruning as well as partitionwise joins provided that the query predicates are identical to the partitioning key.

D. It is effective only in partitionwise joins provided that the query predicates are different from the partitioning key.

Answer: B

Explanation/Reference:

分区修剪,根据条件只访问对应的分区
http://docs.oracle.com/cd/E11882_01/server.112/e25555/tdpdw_perform.htm#TDPDW00821

Improving Performance: Partition Pruning

Partition pruning is an essential performance feature for data warehouses. In partition pruning, the optimizer analyzes FROM and WHEREclauses in SQL statements to eliminate unneeded partitions when building the partition access list. This enables Oracle Database to perform operations only on those partitions that are relevant to the SQL statement. Oracle Database prunes partitions when you use range, LIKE, equality, and IN-list predicates on the range or list partitioning columns, and when you use equality and IN-list predicates on the hash partitioning columns.

Partition pruning dramatically reduces the amount of data retrieved from disk and shortens the use of processing time, which improves query performance and resource use. If you partition the index and table on different columns (with a global partitioned index), partition pruning eliminates index partitions even when the partitions of the underlying table cannot be eliminated.

Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning. Static pruning occurs at compile time; the information about the partitions is accessed beforehand, dynamic pruning occurs at run time; the partitions are accessed by a statement and are not known beforehand. A sample scenario for static pruning is a SQL statement that contains a WHERE clause with a constant literal on the partition key column. An example of dynamic pruning is the use of operators or functions in the WHERE clause.

Partition pruning affects the statistics of the objects where pruning will occur and will affect the execution plan of a statement.


智能分区join,可以让对应的分区进行join

Improving Performance: Partitionwise Joins

Partitionwise joins reduce query response time by minimizing the amount of data exchanged among parallel execution servers when joins execute in parallel. This significantly reduces response time and improves the use of both CPU and memory resources. In Oracle Real Application Clusters environments, partitionwise joins also avoid or at least limit the data traffic over the interconnection, which is the key to achieving good scalability for massive join operations.

Partitionwise joins can be full or partial. Oracle Database decides which type of join to use.

 

 

 

QUESTION 183

What recommendations does the SQL Access Advisor provide for optimizing SQL queries? (Choose all that apply.)

A. creation of bitmap, function-based, and B-tree indexes

B. creation of index-organized tables

C. optimization of materialized views for maximum query usage and fast refresh

D. selection of SQL plan baselines

E. partitioning of tables and indexes

Answer: ACE

Explanation/Reference:

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/advisor.htm#PFGRF95276

SQL Access Advisor index recommendations include bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. Bitmap indexes are most commonly used in a data warehouse to index unique or near-unique keys. SQL Access Advisor materialized view recommendations include fast refreshable and full refreshable MVs, for either general rewrite or exact text match rewrite.

SQL Access Advisor, using the TUNE_MVIEW procedure, also recommends how to optimize materialized views so that they can be fast refreshable and take advantage of general query rewrite.

Using SQL Access Advisor in Enterprise Manager or API, you can do the following:

·         Recommend materialized views and indexes based on collected, user-supplied, or hypothetical workload information.

·         Recommend partitioning of tables, indexes, and materialized views.

·         Mark, update, and remove recommendations.

 

Oracle数据库出现性能问题时,使用Oracle本身的工具包,给出合理的调优建议是比较省力的做法。

 下面两种包的介绍及具体做法。

SQL Tuning Advisor 粒度最小的调整工具.

将一条或多条SQL语句做为输入内容,分析所有访问路径,然后生成改进SQL语句的建议,并提出索引,物化视图和分区方案来提高应用程序的总体性能。
另外,在维护窗口中,Oracle也会针对Automatic Workload Repository(AWR) 来确定和记录的高负载语句自动化运行SQL Tuning Advisor
SQL Access Advisor 

它涉及工作量中所有SQL语句,并提出索引,特化视图和分区方案来提高应用程序的总体性能。

小区别:

  SQL Tuning Advisor 分别调整每条SQL语句
  SQL Access Advisor
同时调整所有SQL语句

 

 

QUESTION 184   再看

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 supports variable sizes for extents of 1, 8, and 64 allocation units.

B. The ASM-preferred mirror read feature is enabled.

C. The RDBMS always reads the primary copy of a mirrored extent of the ASM disk group.

D. The ASM disk is dropped immediately from a disk group when it becomes unavailable.

Answer: AB

Explanation/Reference:

 

 

 

 

QUESTION 185

You want to take the backup of the USERS tablespace. It has a single data file of 900 MB.. You have tape drives of 300 MB each. The SBT channel is configured for the RMAN. To accomplish the backup, you issued the following RMAN command:

RMAN> BACKUP SECTION SIZE 300M TABLESPACE users;

Which two statements are true regarding the execution of the above command? (Choose two.)

A. The RMAN parallelizes the backup although the parallelism is not set for a channel.

B. The backup piece size will be limited to 300 MB.

C. The operation is accomplished using the default channel available.

D. Three channels for the tape drive must be configured by setting the parallelism to three.

Answer: BC

Explanation/Reference:

 

 

QUESTION 186

Exhibit #1:

Exhibit #2:

View the Exhibit exhibit1 to observe the maintenance window property. View the Exhibit exhibit2 to examine the output

of the query. Which two statements describe the conclusions? (Choose two.)

A. RESOURCE_PERCENTAGE should be increased.

B. The repeat time for the window should be decreased.

C. RESOURCE_PERCENTAGE should be decreased.

D. The window duration should be increased.

Answer: AD

Explanation/Reference:

 

 

QUESTION 187

Exhibit:

View the Exhibit to examine the error obtained during the I/O calibration process. There are no data files on raw

devices. What is the reason for this error?

A. Another session runs the I/O calibration process concurrently.

B. The FILESYSTEMIO_OPTIONS parameter is set to NONE.

C. The pending area has not been created before running the I/O calibration process.

D. The DISK_ASYNCH_IO parameter is set to TRUE.

Answer: B

Explanation/Reference:


http://docs.oracle.com/cd/E11882_01/server.112/e41573/iodesign.htm#PFGRF94383

8.3.1 Prerequisites for I/O Calibration

Before running I/O calibration, ensure that the following requirements are met:

·         The user must be granted the SYSDBA privilege

·         timed_statistics must be set to TRUE

·         Asynchronous I/O must be enabled

When using file systems, asynchronous I/O can be enabled by setting the FILESYSTEMIO_OPTIONS initialization parameter to SETALL.

·         Ensure that asynchronous I/O is enabled for data files by running the following query:

·         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';

Additionally, only one calibration can be performed on a database instance at a time.

http://docs.oracle.com/cd/E11882_01/server.112/e41573/os.htm#PFGRF94412

9.1.1.2 FILESYSTEMIO_OPTIONS Initialization Parameter

You can use the FILESYSTEMIO_OPTIONS initialization parameter to enable or disable asynchronous I/O or direct I/O on file system files. This parameter is platform-specific and has a default value that is best for a particular platform.

FILESYTEMIO_OPTIONS can be set to one of the following values:

·         ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.

·         DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.

·         SETALL: enable both asynchronous and direct I/O on file system files.

·         NONE: disable both asynchronous and direct I/O on file system files.

 

 

QUESTION 188

Which statement is true for enabling Enterprise Manager Support Workbench in Oracle Database 11g to upload the

physical files generated by Incident Packaging Service (IPS事件包装服务) to MetaLink?

A. The path for the Automatic Diagnostic Repository (ADR) must be configured with the DIAGNOSTIC_DEST

initialization parameter.

B. Select the Enable option in the Oracle Configuration Manager Registration window during the installation of the

Oracle Database 11g software, provide valid MetaLink credentials and select license agreement.

C. The Enterprise Manager Support Workbench can be enabled only if the background process manageability monitor

(MMON) is configured.

D. No special setup is required, and the feature is enabled by default.

E. The database must be running in ARCHIVELOG mode.

Answer: B

Explanation/Reference:

 

 

QUESTION 189

Which statements are true regarding SecureFile LOBs? (Choose all that apply.)

A. The amount of undo retained is user controlled.

B. It automatically detects duplicate LOB data and conserves space by storing only one copy.

C. SecureFile encryption allows for random reads and writes of the encrypted data.

D. Fragmentation is minimized by using variable-sized chunks dynamically.

E. SecureFile LOBs can be used only for nonpartitioned tables.

Answer: CD

Explanation/Reference:


http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#ADLOB45945

FREEPOOLS

Specifies the number of FREELIST groups for BasicFiles LOBs, if the database is in automatic undo mode. Not used for SecureFiles LOBs.


http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#ADLOB45950

LOGGING, NOLOGGING, or FILESYSTEM_LIKE_LOGGING

Specify LOGGING if you want the creation of the LOB, and subsequent inserts into the LOB, to be logged in the redo log file. LOGGINGis the default.

Specify NOLOGGING if you do not want these operations to be logged.

For a non-partitioned object, the value specified for this clause is the actual physical attribute of the segment associated with the object. For partitioned objects, the value specified for this clause is the default physical attribute of the segments associated with all partitions specified in the CREATE statement (and in subsequent ALTER ... ADD PARTITION statements), unless you specify the logging attribute in the PARTITION description.

FILESYSTEM_LIKE_LOGGING means that the system only logs the metadata. This option is invalid for BasicFiles LOBs. This setting is similar to metadata journaling of file systems, which reduces mean time to recovery from failures. The LOGGING setting for SecureFiles LOBs is similar to the data journaling of file systems. Both the LOGGING and FILESYSTEM_LIKE_LOGGING settings provide a complete transactional file system with SecureFiles LOBs.

For SecureFiles LOBs, the NOLOGGING setting is converted internally to FILESYSTEM_LIKE_LOGGING.

FILESYSTEM_LIKE_LOGGING ensures that data is completely recoverable after a server failure.


http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#ADLOB45947

CHUNK

CHUNK is one or more Oracle blocks. For SecureFiles LOBs, CHUNK is an advisory size and is provided for backward compatibility purposes. For BasicFiles LOBs, you may specify the chunk size when creating a table that stores LOBs; it corresponds to the data size used by Oracle Database when accessing or modifying the LOB value.

http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#ADLOB46110

About Encryption

SecureFiles Intelligent Encryption, available with the Oracle Advanced Security Option, introduces a new encryption facility for LOBs. The data is encrypted using Transparent Data Encryption (TDE), which allows the data to be stored securely, and still allows for random read and write access.



B
为什么不对,难道是因为license ?
http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#ADLOB46110

About Deduplication

SecureFiles Intelligent Deduplication, available with the Oracle Advanced Compression Option, enables Oracle Database to automatically detect duplicate LOB data within a LOB column or partition, and conserve space by storing only one copy of the data.

Note that you must have a license for the Oracle Advanced Compression Option before implementing SecureFiles Intelligent Deduplication. See Oracle Database Licensing Information for more information.

 

 

 

 

QUESTION 190

Which statements are true regarding the concept of problems and incidents in the fault diagnosability infrastructure for

Oracle Database 11g? (Choose all that apply.)

A. Only the incident metadata and dumps are stored in the Automatic Diagnostic Repository (ADR).

B. The problem key is the same as the incident number.

C. The database sends an incident alert to the Oracle Enterprise Manager Database Home page.

D. Every problem has a problem key, which is a text string that describes the problem.

E. The database makes an entry into the alert log file when problems and incidents occur.

Answer: CDE

Explanation/Reference:

 

 

QUESTION 191

What two statements are true regarding the recommendations received from the SQL Access Advisor? (Choose two.)

A. It cannot generate recommendations that support multiple workload queries.

B. It can recommend partitioning on tables provided that the workloads have some predicates and joins on the columns of the NUMBER or DATE type.

C. It can recommend partitioning only on tables that have at least 10,000 rows.

D. It can recommend only B-tree indexes and not bitmap or function-based indexes.

Answer: BC

Explanation/Reference:

 

QUESTION 192

Which two statements about the SQL Management Base (SMB) are true? (Choose two.)

A. It stores plans generated by the optimizer using a stored outline.

B. It is part of the data dictionary and stored in the SYSTEM tablespace.

C. It is part of the data dictionary and stored in the SYSAUX tablespace.

D. It contains only SQL profiles generated by SQL Tuning Advisor.

E. It contains the statement log, the plan history, plan baselines, and SQL profiles.

Answer: CE

Explanation/Reference:

 

 

QUESTION 193 不懂

Exhibit:

View the Exhibit to examine the metrics with a threshold. Which statement is true regarding the Number of Transactions (per second) metric?

A. Oracle determines when an adaptive threshold has been breached based on the maximum value captured by the

baseline.

B. The total concurrent number of threshold violations, which must occur before an alert is raised for the metric, has

been set to zero.

C. Oracle uses statistical relevance to determine when an adaptive threshold has been breached for the metric.

D. The statistics for the metric values observed over the baseline time period are not examined to determine threshold

values.

Answer: C

Explanation/Reference:

 

QUESTION 194

Which two statements are true regarding the starting of the database instance using the following command? (Choose

two.)

SQL>STARTUP UPGRADE

A. It allows only SYSDBA connections.

B. It enables all system triggers.

C. It ensures that all job queues remain active during the upgrade process.

D. It sets system initialization parameters to specific values that are required to enable database upgrade scripts to be

run.

Answer: AD

Explanation/Reference:

 

 

QUESTION 195

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. The partial or complete workload scope needs to be associated with the task.

B. The type of structures (indexes, materialized views, or partitions) to be recommended need to be specified for the

task.

C. A template needs to be associated with the task.

D. A workload needs to be associated with the task.

Answer: D

Explanation/Reference:

http://blog.csdn.net/rlhua/article/details/16856693

Usage Notes

Though the script is ready to execute, Oracle recommends that the user review the script for acceptable locations for new materialized views and indexes.

 

QUESTION 196  不懂

You opened the encryption wallet and then issued the following command:

SQL>CREATE TABLESPACE securespace

DATAFILE '/home/user/oradata/secure01.dbf'

SIZE 150M

ENCRYPTION USING '3DES168'

DEFAULT STORAGE(ENCRYPT);

Then you closed the wallet. Later, you issued the following command to create the EMPLOYEES table in the

SECURESPACE tablespace and you use the NO SALT option for the EMPID column.

What is the outcome?

A. It generates an error because the NO SALT option cannot be used with the ENCRYPT option.

B. It generates an error because the wallet is closed.

C. It creates the table but does not encrypt the data in it.

D. It creates the table and encrypts the data in it.

Answer: B

Explanation/Reference:

 

 

QUESTION 197

Which statement about the enabling of table compression in Oracle Database 11g is true?

A. Compression can be enabled only at the table level for both direct loads and conventional DML.

B. Compression can be enabled at the table, tablespace, or partition level for direct loads only.

C. Compression can be enabled at the table, tablespace, or partition level for both direct loads and conventional DML.

D. Compression can be enabled at the table, tablespace, or partition level for conventional DML only.

Answer: C

Explanation/Reference:

 

 

QUESTION 198

You are managing Oracle Database 11g with an ASM storage with high redundancy. The following command was

issued to drop the disks from the dga disk group after five hours:

ALTER DISKGROUP dga OFFLINE DISKS IN FAILGROUP f2 DROP AFTER 5H;

Which statement is true in this scenario?

A. It starts the ASM fast mirror resync.

B. All the disks in the dga disk group would be OFFLINE and the DISK_REPAIR_TIME disk attribute would be set to 5

hours.

C. It drops all disk paths from the dga disk group.

D. All the disks in the dga disk group in failure group f2 would be OFFLINE and the DISK_REPAIR_TIME disk attribute would be set to 5 hours.

Answer: D

Explanation/Reference:

 

 

QUESTION 199

Evaluate the following function code:

CREATE FUNCTION get_dept_avg(dept_id NUMBER)

RETURN NUMBER

RESULT_CACHE RELIES_ON (EMPLOYEES)

IS

avgsal NUMBER(6);

BEGIN

SELECT AVG(SALARY)INTO avgsal

FROM EMPLOYEES

WHERE DEPARTMENT_ID = dept_id;

RETURN avgsal;

END get_dept_avg;

Which statement is true regarding the above function?

A. The cached result becomes invalid when any structural change is done to the EMPLOYEES table.

B. If the function is invoked with a different parameter value, the existing result in the result cache gets overwritten by

the latest value.

C. Each time the function is invoked in a different session, the current result in the result cache gets overwritten.

D. If the function execution results in an unhandled exception, the exception result is also stored in the cache.

Answer: A

Explanation/Reference:

 

 

QUESTION 200

Which tasks can be accomplished using the Enterprise Manager Support Workbench in Oracle Database 11g? (Choose all that apply.)

A. Generate reports on data failure such as data file failures.

B. You can track the Service Request (SR) and implement repairs.

C. You can package and upload diagnostic data to Oracle Support.

D. You can manually run health checks to gather diagnostic data for a problem.

Answer: BCD

Explanation/Reference:

 

 

QUESTION 201

Exhibit #1:

Exhibit #2:

View the Exhibit exhibit1 to examine the series of SQL commands. View the Exhibit exhibit2 to examine the plans

available in the SQL plan baseline. The baseline in the first row of the Exhibit is created when OPTIMIZER_MODE was set to FIRST_ROWS.

Which statement is true if the SQL query in exhibit1 is executed again when the value of OPTIMIZER_MODE is set to

FIRST_ROWS?

A. The optimizer uses the plan in the first row of the exhibit2 because OPTIMIZER_MODE was set to FIRST_ROW

during its creation.

B. The optimizer uses the plan in the second row of the exhibit2 because it is an accepted plan.

C. The optimizer uses the plan in the first row of the exhibit2 because it is the latest generated plan.

D. The optimizer uses a new plan because none of the plans in the exhibit2 are fixed plans.

Answer: B

Explanation/Reference:

 

QUESTION 202

What are the advantages of variable extent size support for large ASM files? (Choose two.)

A. It improves resync operations when the disk comes online after being taken offline for maintenance purposes.

B. It improves performance in the extended cluster configuration by reading from a local copy of an extent.

C. Fewer extent pointers are needed to describe the file and less memory is required to manage the extent maps in the shared pool.

D. This feature enables faster file opens because of the reduction in the amount of memory that is required to store file

extents.

Answer: CD

Explanation/Reference:

 

QUESTION 203

Which two are the uses of the ASM metadata backup and restore (AMBR) feature? (Choose two.)

A. It can be used to back up all data on ASM disks.

B. It can be used to recover the damaged ASM disk group along with the data.

C. It can be used to gather information about a preexisting ASM disk group with disk paths, disk name, failure groups,

attributes, templates, and alias directory structure.

D. It can be used to re-create the ASM disk group with its attributes.

Answer: CD

Explanation/Reference:

 

QUESTION 204

Which two changes and their effect on the system can be tested by using the Database Replay feature? (Choose two.)

A. multiplexing of the control file

B. adding the redo log member to the database

C. database and operating system upgrades

D. changing the database storage to ASM-managed storage

Answer: CD

Explanation/Reference:、、

 

 

QUESTION 205

Which is the source used by Automatic SQL Tuning that runs as part of the AUTOTASK framework?

A. SQL statements that are part of the AWR baseline only

B. SQL statements based on the AWR top SQL identification

C. SQL statements that are part of the available SQL Tuning Set (STS) only

D. SQL statements that are available in the cursor cache and executed by a user other than SYS

Answer: B

Explanation/Reference:

 

 

QUESTION 206

While performing the backup of the flash recovery area, you notice that one of the archived redo log files is missing. You have multiple destinations for archived redo log files. What implications does it have on the backup of the flash recovery area?

A. The backup fails because one of the archived redo log files is missing.

B. The backup succeeds but it would be without the missing archived log.

C. During backup, you are prompted for the alternative destination for the missing archived redo log file.

D. The backup succeeds because it fails over to one of the alternative archived redo log destinations.

Answer: D

Explanation/Reference:

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28743704/viewspace-1306272/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-03-22

  • 博文量
    106
  • 访问量
    65519