Oracle OCP 1Z0-050（121-130题）解析

Oracle OCP 1Z0-050（121-130题）解析

QUESTION 121:

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

解析：参考Oracle官方文档：

https://docs.oracle.com/cd/E11882_01/server.112/e10822/tdppt_sqltune.htm#TDPPT160

The optimizer determines whether it is more efficient to read all data in the table, called a full table scan, or use an index. It compares the cost of all possible approaches and chooses the approach with the least cost. The access method for physically executing a SQL statement is called an execution plan, which the optimizer is responsible for generating. The determination of an execution plan is an important step in the processing of any SQL statement, and can greatly affect execution time.

The query optimizer can also help you tune SQL statements. By using SQL Tuning Advisor and SQL Access Advisor, you can run the query optimizer in advisory mode to examine a SQL statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor and SQL Access Advisor can make various recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes or materialized views, and refreshing optimizer statistics. Additionally, Oracle Enterprise Manager (Enterprise Manager) enables you to accept and implement many of these recommendations easily.

SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. SQL Tuning Advisor makes other types of recommendations, such as creating SQL profiles and restructuring SQL statements. If significant performance improvements can be gained by creating a new index, then SQL Tuning Advisor may recommend it. However, such recommendations should be verified by running SQL Access Advisor using a SQL workload that contains a set of representative SQL statements.

当获得recommendations后，需要运行SQL Access Advisor来验证那些recommendation是否适用。

The page lists SQL statements that have been automatically selected by the database as candidates for SQL tuning.

 

QUESTION 122:

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

解析：

参考Oracle官方文档：

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#SQLRF01001

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5010.htm#SQLRF20008

You must be logged in as SYSDBA to specify DEFAULT. Use this clause to designate this flashback data archive as the default flashback data archive for the database. When a CREATE TABLE or ALTER TABLE statement specifies the flashback_archive_clause without specifying a flashback data archive name, the database uses the default flashback data archive to store data from that table.

You cannot specify this clause if a default flashback data archive already exists. However, you can replace an existing default flashback data archive using the ALTER FLASHBACK ARCHIVE ... SET DEFAULT clause.

 

flashback_archive_clause

You must have the FLASHBACK ARCHIVE object privilege on the specified flashback data archive to specify this clause. Use this clause to enable or disable historical tracking for the table.

• Specify FLASHBACK ARCHIVE to enable tracking for the table. You can specify flashback_archive to designate a particular flashback data archive for this table. The flashback data archive you specify much already exist.

If you omit the archive name, then the database uses the default flashback data archive designated for the system. If no default flashback data archive has been designated for the system, then you must specify flashback_archive.

You cannot specify FLASHBACK ARCHIVE to change the flashback data archive for this table. Instead you must first issue an ALTER TABLE statement with the NO FLASHBACK ARCHIVE clause and then issue an ALTER TABLE statement with the FLASHBACK ARCHIVE clause.

• Specify NO FLASHBACK ARCHIVE to disable tracking for the table.

 

The CREATE TABLE flashback_archive_clause for information on creating a table with tracking enabled and CREATE FLASHBACK ARCHIVE for information on creating default flashback data archives

 

QUESTION 123:

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

解析：参考Oracle官方文档：

https://docs.oracle.com/cd/E11882_01/server.112/e18951/asmdiskgrps.htm#OSTMG94155

Mounting and Dismounting Disk Groups

Disk groups that are specified in the ASM_DISKGROUPS initialization parameter are mounted automatically at Oracle ASM instance startup. This makes them available to all database instances running on the same node as Oracle ASM. The disk groups are dismounted at Oracle ASM instance shutdown. Oracle ASM also automatically mounts a disk group when you initially create it, and dismounts a disk group if you drop it.

When a disk group is mounted, a disk group number is chosen. This number may change across disk group mounts. A disk group number is not recorded in any persistent structure, but the current value can be viewed in the GROUP_NUMBER column of the V$ASM views.

When you want to mount or dismount disk groups manually, use the ALTER DISKGROUP...MOUNT or ALTER DISKGROUP...DISMOUNT statement. You can mount or dismount disk groups by name, or specify ALL.

If you try to dismount a disk group that contains open files, the statement fails, unless you also specify the FORCE clause.

In a clustered Oracle ASM environment in RESTRICTED mode, a disk group is mounted in single-instance exclusive mode. No other Oracle ASM instance in that cluster can mount that disk group. In this mode the disk group is not usable by any Oracle ASM client. Use this mode to perform a fast rebalance.

The following SQL statement dismounts all disk groups that are currently mounted to the Oracle ASM instance:

ALTER DISKGROUP ALL DISMOUNT;

The following SQL statement mounts disk group data1:

ALTER DISKGROUP data1 MOUNT;

Mounting Disk Groups Using the FORCE Option

Oracle ASM provides a MOUNT FORCE option with ALTER DISKGROUP to enable Oracle ASM disk groups to be mounted in normal or high redundancy modes even though some Oracle ASM disks may be unavailable to the disk group at mount time. The default behavior without the FORCE option is to fail to mount a disk group that has damaged or missing disks.

The MOUNT FORCE option is useful in situations where a disk is temporarily unavailable and you want to mount the disk group with reduced redundancy while you correct the situation that caused the outage.

To successfully mount with the MOUNT FORCE option, Oracle ASM must be able to find at least one copy of the extents for all of the files in the disk group. In this case, Oracle ASM can successfully mount the disk group, but with potentially reduced redundancy.

The disks that Oracle ASM cannot access are placed in an offline mode. Oracle ASM then begins timing the period that these disks are in an offline mode. If the disk offline time period exceeds the timer threshold set by DISK_REPAIR_TIME disk group attribute, then those disks are permanently dropped from the disk group. You can change the offline timer after a disk is put in an offline state by using the ALTER DISKGROUP OFFLINE statement. For more information about setting the DISK_REPAIR_TIME disk group attribute, see "Oracle ASM Fast Mirror Resync".

An Oracle ASM instance mounts an incomplete disk group differently depending on the specified compatibility. See "Features Enabled By Disk Group Compatibility Attribute Settings".

In clustered Oracle ASM environments, if an Oracle ASM instance is not the first instance to mount the disk group, then using the MOUNT FORCE statement fails. This is because the disks have been accessed by another instance and the disks are not locally accessible.

If all disks are available, then using the FORCE option causes the MOUNT command to fail. This discourages unnecessary and improper use of the feature.

The following example shows how to use the FORCE option to force the mount of the data1 disk group:

ALTER DISKGROUP data1 MOUNT FORCE

QUESTION 124:

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

 

QUESTION 125:

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?

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

B. The tablespace having Flashback Archive is dropped.

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

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

Answer: D

解析：参考Oracle官方文档：

https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS01011

https://docs.oracle.com/database/121/SQLRF/statements_8016.htm#SQLRF20010

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.

 

QUESTION 126:

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

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_networkacl_adm.htm#ARPLS148

 

QUESTION 127:

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

解析：参考Oracle官方文档：

https://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:

$ORACLE_HOME/dbs/oranfstab

/etc/oranfstab

/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:

Change the directory to $ORACLE_HOME/rdbms/lib.

Enter the following command:

make -f ins_rdbms.mk dnfs_on

 

QUESTION 128:

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

解析：参考Oracle官方文档：

https://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 129:

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.

D. Flash recovery area and data file location should be created under Oracle base in a non-Automatic Storage Management (ASM) setup.

Answer: A,B,D

解析：参考Oracle官方文档：

https://docs.oracle.com/cd/E11882_01/server.112/e25494/omf.htm#ADMIN11481

How Oracle Managed Files Are Named

The naming scheme described in this section applies only to files created in operating system file systems. The naming scheme for files created in Oracle Automatic Storage Management (Oracle ASM) disk groups is described in Oracle Automatic Storage Management Administrator's Guide.

The filenames of Oracle Managed Files comply with the Optimal Flexible Architecture (OFA) standard for file naming. The assigned names are intended to meet the following requirements:

• Database files are easily distinguishable from all other files.
• Files of one database type are easily distinguishable from other database types.
• Files are clearly associated with important attributes specific to the file type. For example, a data file name may include the tablespace name to allow for easy association of data file to tablespace, or an archived log name may include the thread, sequence, and creation date.

No two Oracle Managed Files are given the same name. The name that is used for creation of an Oracle managed file is constructed from three sources:

• The default creation location
• A file name template that is chosen based on the type of the file. The template also depends on the operating system platform and whether or not Oracle Automatic Storage Management is used.
• A unique string created by Oracle Database or the operating system. This ensures that file creation does not damage an existing file and that the file cannot be mistaken for some other file.

As a specific example, filenames for Oracle Managed Files have the following format on a Solaris file system:

destination_prefix/o1_mf_%t_%u_.dbf

where:

• destination_prefix is destination_location/db_unique_name/datafile

where:

• • destination_location is the location specified in DB_CREATE_FILE_DEST
  • db_unique_name is the globally unique name (DB_UNIQUE_NAME initialization parameter) of the target database. If there is no DB_UNIQUE_NAME parameter, then the DB_NAME initialization parameter value is used.
• %t is the tablespace name.
• %u is an eight-character string that guarantees uniqueness

For example, assume the following parameter settings:

DB_CREATE_FILE_DEST   = /u01/app/oracle/oradata

DB_UNIQUE_NAME = PAYROLL

Then an example data file name would be:

/u01/app/oracle/oradata/PAYROLL/datafile/o1_mf_tbs1_2ixh90q_.dbf

Names for other file types are similar. Names on other platforms are also similar, subject to the constraints of the naming rules of the platform.

The examples on the following pages use Oracle managed file names as they might appear with a Solaris file system as an OMF destination.

Do not rename an Oracle managed file. The database identifies an Oracle managed file based on its name. If you rename the file, the database is no longer able to recognize it as an Oracle managed file and will not manage the file accordingly.

 

QUESTION 130:

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: A,B,D

解析：参考Oracle官方文档

https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV426

如果使用RMAN连接到源数据库（Target），那么源数据库需要为了实施数据库复制而处于适合的状态。

1、源数据库实例需要处于mount状态或者open状态。

2、如果是处于 active database duplication，则确保必须满足下面的条件：

      （1）如果源数据库处于OPEN状态，则必须处于归档日志模式（archiving must be enabled.）

      （2）如果源数据库没有打开，则数据库不需要进行实例恢复。

3、Follow the instructions in Oracle Database Net Services Administrator's Guide to configure a client for connection to a database and add static service information for the listener.

 

If RMAN is connected to the source database as TARGET, then the source database must be in the proper state for the duplication.

To ensure that the source database is in the proper state:

1. If the source database instance is not mounted or open, then mount or open it.

2. If you are performing active database duplication, then ensure that the following additional requirements are met:

   • If the source database is open, then archiving must be enabled.

   • If the source database is not open, then the database does not require instance recovery.