Oracle OCP 1Z0-050（1-10题）解析

Oracle OCP 1Z0-050（1-10题）解析

QUESTION 1:
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.

正确答案：B、C

【解析】

Data Recovery Advisor（以下简称DRA）是Oracle的一个内置（Build-In）工具，用于进行数据错误、损坏的报告和修复建议。比如，DRA能够自动发现当前存在坏块，并且查看备份资料库（RMAN），给出修复建议和语句。DRA甚至可以做到“一键式”的恢复，敲一个修复命令，就自动执行修复脚本，将错误解除。

DRA目前可以在两种方式下进行工作，一个是数据库启动障碍，比如启动过程报错。另一个是运行过程障碍，例如运行中数据库异常损坏（如数据文件被后台删除）。故正确答案为B、C。

注意，在11g版本中，Data Recovery Advisor不支持RAC环境。

QUESTION 2:
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

正确答案：C

【解析】

从$ SQLPLUS scott/tiger@DB01 AS SYSDBA，是使用密码验证的，明显看到scott账户的密码就是tiger

LDAP_DIRECTORY_SYSAUTH enables or disables directory-based authorization for SYSDBA and SYSOPER.

QUESTION 3:
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

正确答案：B

【解析】

在Oracle Database 11g之前，Oracle的各类跟踪文件、alert日志文件等诊断文件的存储位置并不统一，而从Oracle 11g开始统一规划这些文件的存储。推出了Automatic Diagnostic Repository（ADR）。它是基于文件的资料库，用来存储数据库诊断信息，如traces、dumps、alert log和健康监控报告。数据库、自动存储管理(ASM)、集群就绪服务(CRS) 和其它Oracle 产品或组件将所有诊断数据都存储在ADR 中，不同的产品和组件都具有相同的目录结构。 

数据库初始化参数DIAGNOSTIC_DEST决定了ADR base的存储位置，默认情况下，如果设置了ORACLE_BASE,那么DIAGNOSTIC_DEST 等于 ORACLE_BASE，否则等于ORACLE_HOME/log。指定了参数DIAGNOSTIC_DEST，由原来的参数CORE_DUMP_DEST、BACKGROUND_DUMP_DEST和USER_DUMP_DEST设置的位置将被忽略。

QUESTION 4:
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.

正确答案：D

【解析】

DBMS_SPM包允许用户使用SQL计划管理功能来管理SQL执行计划，SQL计划管理功能可以通过长时间的记录和分析SQL语句执行计划来有效的防止由于突然间更改一个SQL语句执行计划所导致数据库性能的衰退，而且还可以通过已知的一组高效的执行计划生成一些执行计划基线。这些SQL计划基线能够随后用于保证适当的性能，即使是在系统发生改变的时候，通常在如下的情况，使用SQL计划功能来管理SQL执行计划：

• 数据库升级安装改变优化器时，通常会有少部分的执行计划改变，大部分可能是有变化的或者是有提高的。无论怎样，还是存在一部分的计划改变所导致性能的衰退问题。此时利用SQL计划基线能显著地减少由于数据库更新带来的性能问题。
• 正在运行的系统和不断变化的数据会带来一些性能问题。利用SQL计划基线可以减少性能回退同时可以维持系统稳定。

    有时部署新的系统模块相当于引用新的SQL语句到系统中，应用程序需要有适当的SQL执行计划，而这些新的执行计划需要通过一些标准的测试获得，使用SQL计划基线能在随时间的变化产生更好的性能。
    DBMS_SPM包属于SYS用户，其他用户需要被分配到ADMINISTER SQL MANAGEMENT OBJECT权限才可以执行这个包。

CONFIGURE Procedure

This procedure sets configuration options for SQL management base, in parameter/value format. This function can be called numerous times, each time setting a different configuration option.

Syntax

DBMS_SPM.CONFIGURE (

   parameter_name    IN VARCHAR2,

   parameter_value   IN NUMBER);

Parameters

CONFIGURE Procedure Parameters

Parameter	Description
parameter_name	Name of parameter to set (see table below)
parameter_value	Value of parameter to use (see table below)

 

Names & Values for CONFIGURE Procedure Parameters

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

 

Usage Notes

• The default space budget for SQL management base is no more than ten percent of the size of SYSAUX tablespace. The space budget can be set to a maximum of 50%. The default unused plan retention period is one year and one week, which means a plan will be automatically purged if it has not been used for more than a year. The retention period can be set to a maximum of 523 weeks (i.e. a little over 10 years).

• When the space occupied by SQL management base exceeds the defined space budget limit, a weekly database alert is generated.

QUESTION 5:

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.

正确答案：D

【解析】

动态性能视图V$MEMORY_DYNAMIC_COMPONENTS显示所有动态优化的内存组件的当前大小，其中包括SGA 和实例PGA 的总大小。

三次启动的SGA的各个组件大小都一样，是动态变化的，PGA也是动态变化的，所以此数据库是处于自动内存管理即AMM，也即是说MEMORY_TARGET设定为非零值。

 

QUESTION 6:

 

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.

正确答案：B、D

【解析】

更改冗余级别的过程中因停电导致磁盘组的一个磁盘不可用

B asm跟踪的块在些期间被更改

D 磁盘自动脱机

 

QUESTION 7:

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

正确答案：B、D

【解析】

Oracle 官方文档中描述如下：

【原文如下】

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.

从Oracle数据库11g开始，SQL Tuning Advisor在系统维护窗口（时间段）期间作为维护任务自动运行。在每次自动运行期间，advisor选择系统中的高负载SQL查询，并生成关于如何优化这些查询的建议。             

自动SQL优化顾问可以配置为自动实现SQL配置文件推荐。SQL概要文件包含特定于SQL语句的附加SQL统计信息，并使查询优化器在运行时生成明显更好的执行计划。如果启用自动实现，那么顾问只为性能提高至少三倍的SQL语句创建SQL配置文件。其他类型的建议，例如创建新索引、刷新优化器统计数据或重构SQL，只能手动实现。DML语句不被用于自动SQL优化顾问的调整。             

QUESTION 8:

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.

正确答案：D

【解析】

参考资料

https://blog.csdn.net/rlhua/article/details/16860279

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65067

 

QUESTION 9:

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.

正确答案：A、C

【解析】

Oracle官方文档原文如下：https://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 10:

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.

正确答案：A

【解析】

在Oracle 10g中的flashback包括： flashback version query、flashback transaction query、flashback database、flashback table和flashback drop等特性。在这些闪回技术当中，除了Flashback Database（依赖于闪回日志）之外，其他的闪回技术都是依赖于Undo撤销数据，都与数据库初始化参数UNDO_RETENTION密切相关。

它们是从撤销数据中读取信息来构造旧数据的。这样就有一个限制，就是undo中的信息不能被覆盖。而undo段是循环使用的，只要事务提交，之前的undo信息就可能被覆盖，虽然可以通过undo_retention等参数来延长undo的存活期，但这个参数会影响所有的事务，设置过大，可能导致undo tablespace快速膨胀。

Oracle 11g中flashback增加了：Flashback Data Archive特性。该技术与之前的Flashback的实现机制不同，通过将变化数据另外存储到创建的闪回归档区（Flashback Archive）中，以和undo区别开来，这样就可以为闪回归档区单独设置存储策略，使之可以闪回到指定时间之前的旧数据而不影响undo策略。并且可以根据需要指定哪些数据库对象需要保存历史变化数据，而不是将数据库中所有对象的变化数据都保存下来，这样可以极大地减少空间需求。

Flashback Data Archive并不是记录数据库的所有变化，而只是记录了指定表的数据变化。所以，Flashback Data Archive是针对对象的保护，是Flashback Database的有力补充。

通过Flashback Data Archive，可以查询指定对象的任何时间点（只要满足保护策略）的数据，而且不需要用到undo，这在有审计需要的环境，或者是安全性特别重要的高可用数据库中，是一个非常好的特性。缺点就是如果该表变化很频繁，对空间的要求可能很高。

（1Z0-050 第1~10题）