ocp 043 51-90

51. While designing your database, you have created the EMPLOYEES table as an index-organized table (IOT). You want to create a bitmap index on the JOB_ID column to make queries faster.

Which task must have been completed so that you are able to create the bitmap index?

A.A primary key must have been created.

B.A mapping table must have been created.

C.An overflow tablespace must have been specified.

D.The PCTTHRESHOLD option must have been specified.

答案：B

解析：

A mapping table is required for creating bitmap indexes on an

index-organized table.

你创建了一个IOT 表，想在上面的其中一列建立位图索引，你必须建立一个mapping table

注意到Mapping table 是个heap table, 用来存储IOT table 的logical rowid （primary key)。 Mapping table 的每一行存储了对应的IOT 表中记录的logical rowid.

因此这个mapping table 就维护了IOT 表logical rowid 和 mapping table的每一行的physical rowid 的mapping 的关系。为什么要这样做呢？因为bitmap 索引条目中保存的rowid 要用physical rowid, 而IOT 是无法提供稳定的physical rowid 的，因此就借助于mapping table 的physical rowid。

通过bitmap 索引来访问表中的数据的执行计划大概就是首先根据bitmap index 中的physical rowid 访问mapping table, 然后通过mapping table 中的logical rowid 再来访问IOT 表。

52. In your datawarehousing application, you generate reports frequently. The data is static in the tables being used to generate reports. You are currently using joins, resulting in a large performance overhead.

What kind of database structure would you use to reduce the response time of the query?

A.hash clusters

B.index clusters

C.partitioned tables

D.index-organized table (IOT)

答案：B

解析：

Oracle 中普通的表称为堆表（heap table），堆表中的数据是无序存放的，往往在使用一段时间后，数据就变得非常无序。索引中相同的key 对应的数据存放在不同的block 中，这时，如果要通过索引查询某个key 的数据，就需要访问很多不同的block，代价非常高。

hash cluster 简单的说就是通过预先分配空间的方式，将相同key 的数据存放在一起，以提高查询性能的一种手段，所以准确的设置hashkeys 和size 参数是使用hash cluster 的关键，使用的前提是key 的数量是可以估算的，而且每个key 的数据是基本平均的。但是，在实际使用的环境中，数据量的变化往往是不可预知的，这也造成hash cluster 的应用场景非常有限。

Index cluster 和hash cluster 类似，只不过index cluster 是通过索引实现数据定位，而且index cluster 的空间是动态分配的，但是同样存在正确设置size 参数的问题，设置过大过小都会产生性能问题。

cluster

性能优点

--DISK I/O 减少﹐表连接的提高存储时间。

--所有相同行的每个cluster key 只存储一次。

性能方面的考虑

--全表扫描時﹐cluster table 比无cluster 的表慢。

cluster 类型

--index cluster

--hash cluster

什么时候用何种cluster 比较

53. You are designing an application for a telecom company and you have been asked to design a database table to facilitate monthly bill generation. The bill would include details of customer calls, listed

in chronological order.

Which method would you follow to achieve this objective without increasing the overhead of sorting the rows?

A.create a hash cluster to store the data

B.create an index cluster to store the data

C.create a partitioned table to store the data

D.create a sorted hash cluster to store the data

E.create a heap table with rowid to store the data

答案：D

解析：

There are three cluster types:index cluster/hash cluster/sort hash

cluster.how can these cluster types be uesd?

索引聚簇表是表相关的表共享同一数据块中的相同列，并把相关数据存储中同一个数据块上。创建索引聚簇表中最重要的是对SIZE 参数有很好的估量，否则聚簇将会降低空间利用，降低效率。

使用索引聚簇表的注意点：

1、如果表中数据有大量DML 操作的话，那么聚簇将不适用，因为会消极地影响到DML 性能。

2、聚簇中，全表扫描将受到影响。这是因为将扫描聚簇中不同表的数据，额外增加很多无用的数据。

3、如果经常TRUNCATE 表和装载表的话，聚簇将不适用。聚簇中的表无法被TRUNCATE 的，这是因为每个块中不只是存储一张表的数据。

SQL> truncate table emp;

ORA-03292: Table to be truncated is part of a cluster

4,如果大部分是读取操作，且通过聚簇码索引或聚簇表中其他索引来读取的话，聚簇将会比较比较适用。

索引聚簇加载数据应该是同时装载同一聚簇码的所有数据，而不是一次装载聚簇中不同表的数据。这是因为如果一次装载单张表数据的话，很有可能单个码值的数据大于SIZE 指定的数据，但是由于聚簇码已经分配完成，此时将会聚簇码块有许多链接块，影响性能。通

过一次装载每个码值对应的数据，可以更好地利用聚簇块的空间。

散列（哈希）聚簇表：概念上同索引聚簇表一样，不同的是用哈希函数代替了索引聚簇码来进行数据的分配。事实上在散列聚簇中数据就是索引，因为数据决定行的物理位置。散列聚簇表中ORACLE 根据行的码值，利用内部函数或提供的函数对聚簇码值进行运算，以决

定数据的物理存储位置。散列聚簇通常意味着如果通过聚码访问的话，一个IO 就能够提取到所需的数据。创建散列聚簇时，必须指定散列码值的数目。由于使用哈希函数来确定数据的分布，对散列聚簇表不能使用范围扫描。全扫描散列聚簇表时，不论表是否为空，ORACLE

将全扫描所有块，这是因为散列聚簇中数据块都是预先分配的。散列聚簇的初始数据装载将会比较慢。自定义的散列函数限定只能使用表中可用的列和ORACLE 内置函数。

散列聚簇要点：

1、 散列聚簇通过散列码查询的时候需要的IO 很少。几乎一个IO 就可以提取到所需的数据，除非发生了行溢出。而传统索引至少需要2 个IO 才能得到数据。

2、散列聚簇查询CPU 开销大。散列聚簇是CPU 密集型的，而索引是IO 密集型的。

3、对表中数据量比较有把握，如行数，每行占用空间，有合理的上限，正确设置好HASHKYES和SIZE 参数，那么散列聚簇将比较适用。

4、散列聚簇降低DML 性能。

5、总是经常通过HASHKEY 等值访问数据。

54. In your test database, you have created the ORDERS table as an index-organized table (IOT). To facilitate faster querying, you have created a mapping table and a bitmap index on the ORDER_FILLED

column. You observe that the query performance degrades when users perform. a large volume of transactions.

While investigating the reason, you find that the mapping table segment is fragmented, leading to poor performance.

Which option would you use to defragment the mapping table without affecting the original table data?

A.export and import the mapping table

B.drop and re-create the mapping table

C.truncate the mapping table and reinsert the values

D.use the ALTER TABLE .. REBUILD command to defragment the mapping table

答案：B

解析：SG 13-38 重建mapping table 一定要dropped and re-create

55. In one of your online transaction processing (OLTP) applications, most users frequently modify the values, including the key values, of the application tables. Some users generate application reports by using multiple application tables.

What is the best table structure that you can use to gain optimal performance?

A.heap table

B.object table

C.external table

D.clustered table

E.global temporary table

F.index-organized table (IOT)

答案：A

解析：

heap table 就是普通表

在OLTP 系统中，许多用户频繁修改数据，包括主键值，一些用户还需要关联几个表产生数据,那么首先IOT 无法使用，因为主键值也频繁修改。其次簇表也无法使用，因为频繁修改。

56. Users in your production database complain that they are getting the following error message while trying to insert rows into the ORDERS table:

ERROR at line 1:

ORA-01654: unable to extend index USERS.ORDERS_IND by 8 in tablespace INDEXES

While investigating, you find that the INDEXES tablespace has run out of space and there is no more free space on the disk where the data files are available.

Which two actions could you perform. to overcome this error without affecting the queries that are currently being executed? (Choose two.)

A.drop and re-create the index

B.coalesce the ORDERS_IND index

C.coalesce the INDEXES tablespace

D.drop and re-create the ORDERS table

E.rebuild the index online and move it to another tablespace

答案：BE

解析：

Coalesce the ORDERS_IND index.----------合并索引碎片

是针对具体索引,不是其所在表空间

57. You have many users complaining about slow inserts into a large table. While investigating the reason, you find that the number of indexes on the table is high. You want to find out which indexes are not being used.

Which method would you follow to achieve this?

A.enable index monitoring and query the DBA_OBJECTS view

B.enable index monitoring and query the DBA_INDEXES view

C.enable index monitoring and query the V$OBJECT_USAGE view

D.enable index monitoring and view the DBA_INDEXTYPE_COMMENTS view

答案：C

解析：

1: $object_usage可以用来监控系统中索引的使用情况

2: 监控索引的使用：SQL> alter index index_test_pk monitoring usage;

3: SQL> select * from v$object_usage;

4: $object_usage只包括当前用户的索引使用记录，如果需要查出所有用户的索

引使用记录，使用下面的sql，这个sql来自DBA日记：

SQL> select u.name owner, io.name index_name, t.name table_name,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,

decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,ou.start_monitoring

start_monitoring,ou.end_monitoring end_monitoring

from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage

ou where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#

and u.user# = io.owner#;

5: 取消对索引的监控

SQL> alter index index_test_pk nomonitoring usage;

58. In which scenarios would you rebuild an index? (Choose all that apply.)

A.when you need to disable the index usage

B.when you need to change storage options

C.when you need to enable index monitoring

D.when you need to move the index to another tablespace

答案：BD

解析：

哪种情况下需要你重建索引

a、当你需要改变存储选项

b、当你需要把索引移动到其它的表空间

59. You want an ASM instance to manage the files of your database. To achieve this objective, you specify the following parameters in the parameter file of the database:

INSTANCE_TYPE = RDBMS

DB_NAME = PROD

LARGE_POOL_SIZE = 8MB

DB_BLOCK_SIZE = 4K

LOG_ARCHIVE_DEST = +dgroupA

LOG_ARCHIVE_FORMAT = "$ORACLE_SID_%s_%t.%t"

DB_CREATE_FILE_DEST = +dgroupA

COMPATIBLE = 10.1.0.2.0

CONTROL_FILES = +dgroupA

Which parameter would be ignored while starting up the instance for the database PROD?

A.DB_BLOCK_SIZE

B.CONTROL_FILES

C.LARGE_POOL_SIZE

D.LOG_ARCHIVE_DEST

E.LOG_ARCHIVE_FORMAT

F.DB_CREATE_FILE_DEST

答案：E

解析：

归档位置是：log_archive_dest + log_archive_format。

如果 LOG_ARCHIVE_DEST 设置为不完整的 ASM 文件名（例如+dGroupA），则将忽略LOG_ARCHIVE_FORMAT。

如果 LOG_ARCHIVE_DEST 设置为 ASM 目录（例如+dGroupA/myarchlogdir/），则将使用 LOG_ARCHIVE_FORMAT，并且文件是非OMF 文件。Oracle 数据库会自动为归档日志创建唯一的文件名。

60. You have been assigned to manage a set of databases. The previous DBA did not leave you notes regarding the structure of each of the databases. While analyzing an instance, you notice that the system identifier (SID) for the instance is set to '+ASM'.

What is the purpose of this instance?

A.This instance is being used to manage the operating system files.

B.This instance is being used to manage the files of other databases.

C.This instance is being used to manage the instances of other databases.

D.This instance is being used to manage the background processes of other instances.

答案：B

解析：

要使用 ASM，在启动数据库实例之前，必须启动一个名为 ASM 实例的特殊实例。ASM 实例不会装载数据库，而是管理使 ASM 文件可用于普通数据库实例所必需的那些元数据。ASM实例和数据库实例都能访问一些公共的磁盘集，这些公共磁盘集称为磁盘组。数据库实例直接访问 ASM 文件的内容，它们与ASM 实例通信的目的只是为了获取这些文件的布局信息。ASM 指的是一个在物理卷（或者磁盘分区）上特别为Oracle 数据文件而创建的"专用"文件系统。该文件系统由Oracle 内核保存并维护，Oracle 知道数据所处的位置，并自动管理这些位置，进而获得适用于数据库的最高性能和最大可用性

61. You are using an Automatic Storage Management (ASM) instance to manage the files of your production database. While studying the parameter file of the ASM instance, you find the following entry:

ASM_DISKSTRING = /devices/*

What could be the reason for this setting?

A.to enable the ASM instance to identify the alert log file

B.to enable the ASM instance to identify the server parameter file

C.to enable the ASM instance to define the locations for Oracle Managed Files (OMF)

D.to enable the ASM instance to identify all disks that can be accessed by it in the /devices directory

答案：D

解析：

ASM_DISKSTRING specifies an operating system-dependent value used by Automatic Storage Management to limit the set of disks considered for discovery. When a new disk is added to a disk group, each Automatic Storage Management instance that has the disk group mounted must be able to discover the new disk using the value of ASM_DISKSTRING.

62. Consider the following scenario:

You have a directory, data, under the disk group tdgroupA. You want to create an alias for one of the data files and you execute the following command:

ALTER DISKGROUP tdgroupA

ADD ALIAS '+tdgroupA/data/datafile.dbf'

FOR '+tdgroupA.231.45678';

Which task would be accomplished by the command?

A.The command drops the file +tdgroupA.231.45678.

B.The command physically relocates the file to +tdgroupA/data and renames the file to datafile.dbf.

C.The command creates a copy of the +tdgroupA.231.45678 file and places it in +tdgroupA/data after renaming the file to datafile.dbf.

D.The command creates an alias, datafile.dbf, and places it in +tdgroupA/data and does not remove the +tdgroupA.231.45678 file.

E.The command creates a file, datafile.dbf, in +tdgroupA/data and removes the references for +tdgroupA.231.45678 from the data dictionary views.

答案：D

解析：

ALTER DISKGROUP tdgroupA ADD ALIAS '+tdgroupA/data/datafile.dbf'

FOR '+tdgroupA.231.45678'

该命令创建了一个同义词datafile.dbf 并放在+tdgroupA/data/目录下，但不影响原来的的+tdgroupA.231.45678 文件

63. You are using an Automatic Storage Management (ASM) instance to manage the files of your production database. You have two disk groups, DG1 and DG2, with one device each.

In the parameter file of the production database, the following parameters have been specified:

DB_CREATE_ONLINE_LOG_DEST_1 = '+dg1'

DB_CREATE_ONLINE_LOG_DEST_2 = '+dg2'

What would be the impact of this setting?

A.When a new log group is added, it would have one member in each disk group.

B.When a new log group is added, it would have two members in each disk group.

C.When a new tablespace is added, it would have one data file in each disk group.

D.When a new log file is added, it would have one member spread across the disk groups.

答案：A

解析：

DB_CREATE_ONLINE_LOG_DEST_1 = '+dg1'

DB_CREATE_ONLINE_LOG_DEST_2 = '+dg2'

当一个新的日志组加入了以后，会在每个磁盘组中有个成员。

64. Consider the following command to create a tablespace in your production database (which is using an Automatic Storage Management [ASM] instance to manage the database files):

CREATE TABLESPACE user_tbsp

DATAFILE '+dgroup3(user_temp)/user_files/user_tbsp' SIZE 200M;

What would be the result of this command?

A.It would result in an error because the template cannot be used along with the disk group.

B.It would result in an error because the path cannot be specified while creating a tablespace in a disk group.

C.It would create a tablespace with a data file that has an alias, and its attributes are set by the user-defined template.

D.It would create a tablespace with a data file that does not have an alias, and its attributes are set by the user-defined template.

答案：C

解析：

该命令将创建一个表空间包括一个数据文件有同义词，而且他的特性由用户自己定义。(条带和镜像配置模板)

65. Consider the following configuration:

/devices/D1 is a member of disk group dgroupA.

/devices/D2 is a member of disk group dgroupA.

/devices/D3 is a member of disk group dgroupA.

You plan to add a new disk, /devices/D4, to the disk group dgroupA.

You execute the following command:

SQL> ALTER DISKGROUP dgroupA ADD DISK '/devices/D*';

Which task would be accomplished by the command?

A.The command adds the new disk, D4, to the disk group.

B.The command would result in an error because there is no disk by the name "/devices/D*".

C.The command will be ignored because disks starting with "D" are already members of the disk group.

D.The command would result in an error because no wildcard characters can be used in the disk name.

E.The command first detaches all the member disks starting with "D," and then reattaches them including the new disk.

答案：A

解析：

/devices/D1 is a member of disk group DGROUPA.

/devices/D2 is a member of disk group DGROUPA.

/devices/D3 is a member of disk group DGROUPA.

/devices/D4 is a candidate disk.

So,if you execute the command above,oracle will add a member automaticly.

Reblanceis automatically done as disks are added, dropped, or resized

ALTER DISKGROUP dgroupA ADD DISK '/devices/D*';

将D4 添加到 DGROUPA 磁盘组。由于其它磁盘已经是 DGROUPA 磁盘组的成员，因此即使它们与搜索字符串匹配，第二个语句仍将忽略这些磁盘。向磁盘组添加磁盘时，ASM 实例将确保该磁盘是可寻址的并且可用，然后，才会对该磁盘进行格式化并使其重新平衡。由于需要将每个文件的分配单元移到新磁盘上，因此重新平衡过程非常耗时。

66. Consider the following command to add a new disk group called "tdgroupA" with two failover groups:

CREATE DISKGROUP tdgroupA NORMAL REDUNDANCY

FAILOVERGROUP control01 DISK

'/devices/A1',

'/devices/A2',

'/devices/A3'

FAILOVERGROUP control02 DISK

'/devices/B1',

'/devices/B2',

'/devices/B3';

The disk "/devices/A1" is currently a member disk of a disk group by name "tdgroup1".

Which task would be accomplished by the command?

A.This command would result in an error because a disk group can have only one failover group.

B.This command would result in an error because the /devices/A1 disk is a member of another disk group

tdgroup1.

C.A new disk group called tdgroupA will be added with two failover groups and the /devices/A1 disk will

get reattached to the new disk group without being detached from the existing one.

D.A new disk group called tdgroupA will be added with two failover groups and the /devices/A1 disk will be

ignored for the new disk group because it is a member of an existing disk group tdgroup1.

E.A new disk group called tdgroupA will be added with two failover groups and the /devices/A1 disk gets detached from the existing disk group tdgroup1 and attached to the new disk group tdgroupA.

答案：B

解析：

一个磁盘已经加入到ASM 组里，你又把他加一遍就会报错，这里使用的是明细命令

67. In your Automatic Storage Management (ASM) instance, one of the nonempty disk groups,DGROUP1, is no longer required and you want this disk group to be removed. You execute the following command to achieve this objective:

DROP DISKGROUP dgroup1 EXCLUDING CONTENTS;

What would be the result of this command?

A.This command would result in an error because the disk group is not empty.

B.The command would drop the disk group, ignoring the EXCLUDING CONTENTS option.

C.The command would result in the disk group being marked as INVALID because it cannot be dropped.

D.The command would result in the contents being moved to the parent disk group and dropping of the disk group.

E.The command would distribute the contents of the specified disk group among all other disk groups and then drop the specified disk group.

答案：A

解析：

Dropping Disk Groups

DROP DISKGROUP 语句可以删除一个ASM 磁盘组，可选择的，也包括其中的文件。可以指

定INCLUDING CONTENTS 删除磁盘组中的文件。默认值是EXCLUDING CONTENTS，which provides syntactic consistency 同时阻止删除含有文件的磁盘组。

ASM 实例必须已经启动，磁盘组已经安装，并且没有打开的文件，这样DROP DISKGROUP才能成功。在删除掉磁盘组之前，语句不会返回。

当删除一个磁盘组时，ASM 卸载磁盘组，如果使用SPFILE，则会从ASM_DISKGROUPS 中移走磁盘组的名字。如果使用的是PFILE，并且该磁盘组也列在ASM_DISKGROUPS 参数中，必须记住在下次关闭并重启ASM 实例前，手工从这个参数中移除该磁盘组。

删除磁盘组dgroup1

DROP DISKGROUP dgroup1;

在确认磁盘组dgroup1 中已经没有打开的文件后，ASM 重写磁盘组中的每个磁盘的头部区域，以删除ASM 格式信息。这个语句没有指定INCLUDING CONTENTS，因此如果磁盘组中有文件，那么操作会失败。

68. You are using an Automatic Storage Management (ASM) instance to manage the database files of your production database. After adding a new disk group to the ASM instance, you execute the following

command:

ALTER DISKGROUP dg3 REBALANCE POWER 0;

What would be the result of this command?

A.The rebalancing of the disk group would be stopped.

B.The rebalancing of the disk group would be completed as fast as possible.

C.The command would result in an error because the value of POWER cannot be 0.

D.The rebalancing of the disk group would be completed without affecting the disk group DG3.

E.The command would result in an error because the disk groups cannot be manually rebalanced.

答案：A

解析：

Oracle通过一个新增的参数:asm_power_limit 来控制速度。

该参数取值范围为1~11,参数值越大平衡速度越快。

如果为0会停止

69. You have a disk group, DGROUP1, with three disks and NORMAL redundancy. You execute the following command to create a template for the disk group:

ALTER DISKGROUP dgroup1

ADD TEMPLATE my_temp

ATTRIBUTES (MIRROR FINE);

Which statement is true?

A.When a file is created in DGROUP1 with the template, it would have three-way mirroring.

B.When a file is created in DGROUP1, the MY_TEMP template becomes the default template.

C.When a file is created in DGROUP1 with the template, it would have two-way mirroring and file striping.

D.When a file is created in DGROUP1 with the template, it would have three-way mirroring and file striping.

E.When a file is created in DGROUP1 with the template, it would have two-way mirroring but no file Striping.

答案：C

解析：

ASM File Template 就是用来定义文件的条带和镜像配置的模板,是磁盘组固

有的属性,并且以文件的形式保存在ASM 磁盘组中(Template Directory),只是这

些文件对用户不可见。

ASM 有镜像mirror 和条带stripe 功能(粗粒度条带Coarse Striping 和细粒

度条带Fine Striping)

在创建磁盘组冗余选择(Redundancy)

1. High 高度冗余,可以通过 asm 实现3 路镜像,需要3 个failure groups

2. Normal 正常冗余, 通过asm 实现2 路镜像,需要2 个failure groups

3. External 外部冗余,asm 之外的技术实现冗余(磁盘raid10 等,通常选择)

70. Your production database uses an Automatic Storage Management (ASM) instance to manage its files.

You want to add a new disk group to the ASM instance to manage the increased data load.

What action would you perform. to include the new disk group in the ASM instance without causing any impact on the currently connected users?

A.mount the new disk group in the ASM instance

B.restart the ASM instance and the production database instance

C.register the new disk groups in the production database instance

D.restart the ASM instance without restarting the production database instance

E.include the new disk group in the ASM_DISKSTRING parameter in the parameter file and restart the ASM instance

答案：A

解析：

http://space.itpub.net/8183550/viewspace-678626

71. You want to migrate your existing non-ASM files to ASM files for the database PROD. Which method or command would you use to accomplish this task? (Choose all that apply.)

A.the CONVERT command of RMAN

B.Data Pump Export and Import into the same database

C.conventional export and import into the same database

D.the BACKUP DATABASE and RESTORE DATABASE commands of RMAN

E.the BACKUP AS COPY DATABASE and SWITCH DATABASE TO COPY command of RMAN

答案：AE

解析：

Step 1 .First need to set below parameter for controlfile,datafile or FRA.

NOTE: I have two disk group here I am using "DGRP2" disk group.

SQL> alter system set control_files='+DGRP2' scope=spfile;

SQL> alter system set db_create_file_dest='+DGRP2' scope=spfile;

SQL> alter system set db_recovery_file_dest='+DGRP2' scope=spfile;

Step: 2 Restart DB server to take above parameter value.

SQL> shutdown immediate;

SQL> startup nomount

Step:3 Connect with RMAN session & restore controlfile on ASM system.

C:\>RMAN target=sys

RMAN> restore controlfile from 'C:\app\m.taj\oradata\test\CONTROL01.ctl';

RMAN> alter database mount;

RMAN> backup as copy database format '+DGRP2';

RMAN> switch database to copy;

Step:5 Again connect to sqlplus session and perform. incomplete recovery

C:\>sqlplus sys/oracle as sysdba

SQL> recover database using backup controlfile until cancel;

Step:6 OPEN database with RESETLOGS option.

SQL> alter database open resetlogs;

Step:7 Drop old tempfile and create new tempfile in existing temp tablespace

SQL> alter database tempfile 'c:\app\m.taj\oradata\test\temp01.dbf' 2 drop including datafiles;

SQL> alter tablespace temp add tempfile size 512m 2 autoextend on next 250m maxsize unlimi

SQL> select tablespace_name, file_name, bytes from dba_temp_files;

Step:8 Recreate All redolog group on ASM diskgroup

SQL> select a.group#, a.member, b.bytes

from v$logfile a, v$log b where a.group# = b.group#;

SQL> select group#,status from v$log;

SQL> alter system switch logfile;

SQL> alter system switch logfile;

SQL> select group#,status from v$log;

SQL> alter database drop logfile group 1;

alter database drop logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery

of instance test (thread 1)ORA-00312: online log 1 thread 1:

'C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG'

When you get above error message then set checkpoint with below command.

SQL> alter system checkpoint global;

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1 size 10m;

SQL> alter system checkpoint global;

SQL> alter database drop logfile group 2;

SQL> alter database add logfile group 2 size 10m;

SQL> alter system checkpoint global;

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 3 size 10m;

SQL> column member format a30

SQL> select a.group#, a.member, b.bytes

from v$logfile a, v$log b where a.group# = b.group#;

Step:9 Recreate SPFILE on ASM diskgroup

SQL> create pfile='c:\initTEST.ora' from spfile;

SQL> create spfile='+DGRP2/spfileTEST.ora' from pfile='c:\initTEST.ora

7. Detele all backup copy from RMAN

RMAN> delete noprompt force copy;

72. You have an Automatic Storage Management (ASM) instance managing the database files of your production database. The database contains ASM files and non-ASM files. Using Recovery Manager(RMAN), you migrate the complete production database to use the ASM instance.

Which statement is true?

A.RMAN would relocate all the database files to an ASM disk group.

B.RMAN would change the file definitions in the control file to use the ASM but would not relocate the database files physically.

C.RMAN would relocate all the data files to an ASM disk group and other files to an operating system location, defined using Oracle Managed Files (OMF).

D.RMAN would relocate the ASM files to an ASM disk group and the non-ASM files to an operating system location, defined using OMF.

答案：A

解析：

将数据库移植到 ASM 存储区,由于 ASM 文件无法通过正常的操作系统界面访问，因此 RMAN 是复制 ASM 文件的唯一途径。虽然由于表空间的历史原因，表空间中的文件既可以是ASM 文件，也可以是非 ASM 文件，但是 RMAN 命令会将非 ASM 文件移到 ASM 磁盘组中。通过以下过程， 可以将整个数据库移到 ASM 磁盘组中： （假定您使用的是服务器参数文件。 ）

1 使用V$CONTROLFILE 和V$LOGFILE，获取当前控制文件和联机重做日志的文件名。

2 像平常一样关闭数据库。按如下所述，修改数据库的服务器参数文件：

-将必要的 OMF 目标参数设置为所需的 ASM 磁盘组。

-删除 CONTROL_FILES 参数。

3.编辑和运行 RMAN 命令文件，这将备份数据库、将当前数据文件移到备份中并重命名联机

重做日志。使用 BACKUP AS COPY 命令只能移动表空间或数据文件。

4.删除旧的数据库文件

73. In your production database you want to use an Automatic Storage Management (ASM) instance to manage the database files.

Which option would you use to migrate the database files from a non-ASM instance to an ASM instance?

A.Oracle Migration Assistant

B.Recovery Manager (RMAN)

C.Oracle Data Pump Export and Import

D.conventional Oracle Export and Import

E.operating system utilities to copy the files to the ASM instance

答案：B

解析：

见上题

74. You have configured Automatic Shared Memory Management. Which four memory structures would be automatically tuned? (Choose four.)

A.log buffer

B.Java pool

C.large pool

D.fixed SGA

E.shared pool

F.streams pool

G.keep buffer cache

H.database buffer cache

答案：BCEH

解析：

当管理员设置了 SGA_TARGET 的值后， Oracle 10g 会自动地设定各个 SGA 内存组件， 包括：

1.共享池（shared pool）（供 SQL and PL/SQL 执行使用）

2.Java 池（Java pool）（供 Java 执行使用）

3.大型池（large pool）(供大块的内存分配使用，例如 RMAN 备份时所需 缓冲区）

4.数据缓存区（buffer cache） 即Database buffer cache

5.数据流池（streams pool）

管理员无需为这些内存组件显式地设置容量。默认情况下这些内存组件的参 数值将显示为零。当某个内存组件需要更多的内存时，可以通过 Oracle 内 部的自动调整机制从其他组件获取。这些工作对用户而言是透明的，无需人工干预

75. In your running instance, some of the initialization parameters are set as shown below:

SGA_MAX_SIZE = 14GB

DB_CACHE_SIZE = 1GB

SHARED_POOL_SIZE = 3GB

STATISTICS_LEVEL = BASIC

PGA_AGGREGATE_TARGET = 0

You plan to enable Automatic Shared Memory Management but you are not able to set SGA_TARGET to a nonzero value. What could be the reason?

A.The STATISTICS_LEVEL initialization parameter is set to BASIC.

B.The PGA_AGGREGATE_TARGET initialization parameter is set to zero.

C.The SGA_MAX_SIZE initialization parameter is set to less than 20 GB.

D.The DB_CACHE_SIZE initialization parameter is set to less than 5 GB.

E.The SHARED_POOL_SIZE initialization parameter is set to a nonzero value.

答案：A

解析：

By default, SGA_TARGET is set to 0, which means that Automatic Shared Memory Management is disabled and you must manually configure the database memory.如果不为0,则自动管理内存

条件：Automatic Shared Memory Management requires that STATISTICS_LEVEL be set to TYPICAL or ALL.

76. The database has the data block size set to 8 KB. You need to import a tablespace with a 4 KB block size into the database. Which additional memory parameter(s) should be configured to perform. a transportable tablespace import?

A.DB_CACHE_SIZE only

B.DB_8K_CACHE_SIZE only

C.DB_4K_CACHE_SIZE only

D.SGA_TARGET and DB_4K_CACHE_SIZE

E.DB_BLOCK_SIZE and DB_8K_CACHE_SIZE

F.DB_BLOCK_SIZE and DB_4K_CACHE_SIZE

答案：C

解析：

不同的block size用于在不同block size的数据库之间转移或传输数据；为了提高性能可在不同存储位置用不同的 block size。 如果使用了非标准块，如 4KB，则必须指定相应的 cache 大小，这里即

DB_4K_CACHE_SIZE。DB_nK_CACHE_SIZE 的值默认为 0。但其中的标准块大小对应的这个参数的值不要指定，由 DB_CACHE_SIZE 指定

77. You configured the large pool for Oracle backup and restore operations. As a result, which component would require less memory?

A.log buffer

B.shared pool

C.streams pool

D.keep buffer cache

E.recycle buffer cache

答案：B

解析：

由于从大型池为 Oracle 共享服务器分配会话内存，因此共享池中由于频繁分配和取消分配大对象而产生的碎片也就很少。

将大对象从共享池中分离出来，可增加共享池内存的使用效率，这意味着，它可以将更多内存用于处理新的请求， 以及在需要时用于保留现有数据。

大型池必须显式配置。大型池的内存不是来自共享池，而是直接来自 SGA，这就增大了 Oracle 服务器在实例启动时需要的共享内存量。

78. You have set some of the initialization parameters as:

DB_BLOCK_SIZE = 8KB

SGA_MAX_SIZE = 2GB

SGA_TARGET = 0

SHARED_POOL_SIZE = 120MB

DB_CACHE_SIZE = 896MB

STREAM_POOL_SIZE = 0

LARGE_POOL_SIZE = 110MB

Which two statements are correct? (Choose two.)

A.You cannot set a value for the DB_8K_CACHE_SIZE parameter.

B.The memory used by Streams will be allocated from the shared pool.

C.If you increase the size of large pool to 120 MB, then the memory allocated to shared pool will be reduced to 110 MB.

D.If an application attempts to allocate more than 120 MB from the shared pool, and free space is available in the buffer pool, then the free space from the buffer pool is allocated to the shared pool.

答案：AB

解析：

A 是正确的，没什么好说的，只有看SG。

c： 因为你的SGA_MAX_SIZE是2G
D: buffer pool不会分配内存给shared pool

79. You have set some of the initialization parameters as:

DB_BLOCK_SIZE = 8KB

SGA_MAX_SIZE = 2GB

SGA_TARGET = 0

SHARED_POOL_SIZE = 120MB

DB_CACHE_SIZE = 896MB

STREAM_POOL_SIZE = 0

LARGE_POOL_SIZE = 110MB

Which two statements are correct? (Choose two.)

A.You cannot set a value for the DB_8K_CACHE_SIZE parameter.

B.If you increase the size of the large pool to 120 MB, then the memory allocated to the shared pool will be reduced to 110 MB.

C.If the value for SGA_TARGET is changed to 1 GB and SHARED_POOL_SIZE is set to 120 MB, then memory cannot be taken from the shared pool, even if the shared pool has free space available.

D.If an application attempts to allocate more than 120 MB from the shared pool, and free space is available in the buffer pool, then the free space from the buffer pool is allocated to the shared pool.

答案：AC

解析：

A 是正确的，没什么好说的，只有看SG。
B： 因为你的SGA_MAX_SIZE是2G
D: buffer pool不会分配内存给shared pool

80. You noticed that large memory allocations for the Oracle backup and restore operations are causing a performance overhead on the shared pool. Which memory structure would you configure to avoid this performance overhead?

A.large pool

B.streams pool

C.redo log buffer

D.keep buffer cache

E.recycle buffer cache

答案：A

解析：

large pool

Optional area in the system global area that provides large memory allocations for Oracle backup and restore operations, I/O server processes, and session memory for the shared server and Oracle XA.

81. Your database is using shared server configuration. Which optional memory structure would you configure to store the User Global Area (UGA) information and improve the shared pool performance?

A.large pool

B.streams pool

C.keep buffer cache

D.recycle buffer cache

答案:A

解析：

UGA

User Global Area. A memory region in the large pool used for user sessions.

82. You are working in a dedicated server environment. Your database is running in the automatic Program Global Area (PGA) memory management mode.

Which two statements are correct in this scenario? (Choose two.)

A.The WORK_AREASIZE_POLICY initialization parameter cannot be set to AUTO.

B.The Oracle database automatically controls the amount of PGA memory allotted to SQL work areas.

C.Setting the value of the SGA_TARGET initialization parameter to 0 will disable the automatic PGA memory management.

D.The SORT_AREA_SIZE parameter is ignored by all the sessions running in the automatic PGA memory management mode.

答案：BD

解析：

oracle 已经不建议使用 SORT_AREA_SIZE 参数了，oracle 建议通过设置PGA_AGGREGATE_TARGET 来激活自动工作区管理, 一旦设置了pga_aggregate_target 以后，所有的*_area_size 就将被忽略.

启用 PGA 自动管理是很容易的，只要设置两个初始化参数即可。首先，设置workarea_size_policy 参数。该参数为 auto（也是缺省值）时，表示启用 PGA自动管理；而设置该参数为 manual 时，则表示禁用 PGA 自动管理，仍然沿用 9i之前的方式，即使用*_area_size 对 PGA 进行管理。

1: SORT_AREA_SIZE：在信息换出到磁盘之前，用于对信息排序的RAM 总量。

SORT_AREA_RETAINED_SIZE：排序完成后用于保存已排序数据的内存总量。也就是说，如果SORT_AREA_SIZE是512 KB，SORT_AREA_RETAINED_SIZE是256 KB，那

么服务器进程最初处理查询时会用512 KB的内存对数据排序。等到排序完成时， 排序区会“收缩”为 256 KB，这 256 KB 内存中放不下的已排序数据会写出到临时表空间中

2: 当 work_areasize_policy 为 auto 的时候，系统只会使用

pga_aggregate_target 参数对应的值来分配相应的排序空间,这个参数是可以在 session 中动态修改的。

83. Your database is running in the automatic Program Global Area (PGA) memory management and Shared Memory Management mode. You want to increase the memory available for the SQL work areas.

What would you do?

A.modify the HASH_AREA_SIZE initialization parameter

B.modify the PGA_AGGREGATE_TARGET initialization parameter

C.modify the WORK_AREASIZE_POLICY initialization parameter

D.increase the value of the SGA_TARGET initialization parameter

E.increase the value of the SGA_MAX_SIZE initialization parameter

F.increase the value of the SORT_AREA_SIZE initialization parameter

答案：B

解析：

With PGA_AGGREGATE_TARGET, sizing of work areas for all dedicated

sessions is automatic and all *_AREA_SIZE parameters are ignored for these

sessions.

自动管理共享内存功能要求将 STATISTICS_LEVEL 设置为 TYPICAL 或 ALL。

PGA_AGGREGATE_TARGET

在自动 PGA 内存管理模式下运行时， 所有会话的工作区大小的调整都是自动的，在该模式下运行的所有会话都会忽略*_AREA_SIZE 参数（例如 SORT_AREA_SIZE）。在任何给定时间，可用于实例中各活动工作区的 PGA 内存总量自动从 PGA_AGGREGATE_TARGET 初始化参数派生。此内存量设置为PGA_AGGREGATE_TARGET 值减去系统其它组件分配的 PGA 内存量（例如，会话分配的 PGA 内存）。由此得到的 PGA 内存随后按照各活动工作区的特定内存需求分配给相应的工作区

84. SORT_AREA SIZE and HASH_AREA_SIZE parameters are ignored in some of the user sessions.

What could be the reason?

A.The User Global Area (UGA) is not configured.

B.The sessions were started using the login name SYS.

C.The sessions are running in the Automatic Shared Memory Management mode.

D.The sessions are running in the automatic Program Global Area (PGA) memory management mode.

答案：D

解析：

In earlier releases, the database administrator controlled the maximum size of SQL work areas by setting the following parameters: SORT_AREA_SIZE,HASH_AREA_SIZE,BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE. Setting these parameters is difficult, because the maximum work area size is ideally selected from the data input size and the total number of work areas active in the system. These two factors vary a lot from one work area to another and from one time to another. Thus, the various*_AREA_SIZE parameters are hard to tune under the best of circumstances.

85. From the V$SESSION_LONGOPS view, you find that some of the database users have long-running queries and are consuming a lot of CPU time. This causes problems for you when you try to log on as the system user and perform. small maintenance tasks. You would like to ensure that users SYS and SYSTEM get priority over all other users.

Which method would you use to achieve this objective?

A.create a plan directive in the SYS_GROUP

B.create the pending area for the consumer group

C.activate the provided SYSTEM_PLAN in Resource Manager

D.set the TIMED_STATISTICS parameter to TRUE in the parameter file

E.set the execution time limit for all users, except SYS and SYSTEM, in their profiles

答案：C

解析：

Oracle Database provides one default resource manager plan, SYSTEM_PLAN, which gives priority to system sessions. SYSTEM_PLAN is defined as follows:

86. From the V$SESSION_LONGOPS view, you find that some of the database users have long-running queries that consume a lot of CPU time. This causes performance problems for other users in the database, who have much shorter queries.

You would like to make sure that the users with large queries do not use all the CPU time, but you still do not want to terminate them with an error message.

Which method would you follow to achieve this?

A.set the CPU time per call in the users profile

B.set the CPU time per session in the users profiles

C.set the CPU levels for the users' group using Resource Manager

D.set the TIMED_STATISTICS parameter to TRUE in the parameter file

答案：C

解析：见上题

87. In your production database, you observe that users' transactions are consuming a lot of resources and you plan to impose resource restrictions by using Resource Manager.

For which three resources can you enable usage restrictions by using Resource Manager? (Choose three.)

A.CPU usage

B.degree of parallelism

C.number of open cursors

D.number of sorts performed

E.idle time for blocking sessions

F.number of disk I/O operations performed

G.number of memory I/O operations performed

答案：ABE

解析：

数据库资源管理器提供了多种分配资源的方式：

1.CPU 方法： 可以指定在使用者组和子计划之间如何分配 CPU 资源。

2.并行度限制:可以控制使用者组中任何操作的最大并行度。

3.具有队列的活动会话池：可以限制使用者组或子计划的并发活动会话数。如果某个组的会话数超过了允许的最大值，则新的会话将放在队列中，等待某个活动会话完成。您还可以指定会话在退出并返回错误之前将等待的时间限制。

4.还原池：可以控制使用者组或子计划能够生成的还原操作的总数。当还原空间的总数超过 UNDO_POOL 指定的数量时， 在相同组中其它会话释放还原空间或者增大使用者组的还原池之前，不允许执行任何新的 INSERT、UPDATE 或 DELETE 命令。如果使用者组在执行 DML 语句时超过了限额，则中止操作并返回错误。此时仍可进行查询，即便使用者组已经超出其还原阈值。

5.执行时间限制：可以指定操作所允许的最大执行时间。Oracle 数据库使用基于成本的优化程序统计信息来估计操作所需的时间。如果耗时超过了所允许的最大时间

(MAX_EST_EXEC_TIME)，则操作返回错误并且不会启动。如果资源使用者组有多个指定了 MAX_EST_EXEC_TIME 的计划指令，则资源管理器将选择所有传入值中限制性最强的那个值。

6.空闲时间限制：可以指定会话的空闲时间，超过该时间后将终止会话 (MAX_IDLE_TIME)。

可以进一步限制资源管理器，使其只终止阻塞其它会话的(MAX_IDLE_TIME_BLOCKER)。

另: create user test7 identified by test7 default profile;

口令 10 次锁定 password 验证函数 idle time cpu 空闲

建立新的 profile sys: @..\rdbms\admin\utlpwdmg.sql;可以修改这段脚本

password 验证函数 verify_function

88. View the Exhibit and examine the Resource Manager settings for the groups at different levels.

Which two effects would be the result of this setting? (Choose two.)

A.The members of LOW_GROUP would get more priority than those of OTHER_GROUPS.

B.The members of SYS_GROUP would get most of the CPU allocation at level 1.

C.The members of LOW_GROUP would get most of the CPU allocation because it has CPU allocation at two different levels.

D.The members of LOW_GROUP would get no CPU allocation at level 1 if the members of OTHER_GROUPS are using all the CPU at level 2.

E.The members of SYS_GROUP would get no CPU allocation at level 1 if the members of OTHER_GROUPS are using all the CPU at level 2.

答案：AB

解析：

resource plan 是设定 cpu 使用权的先后顺序

例如 level 1 sys_group 100%

level 2 oltp 75%

level 2 dss 25%

表示sys_group 可用所有的CPU resource , 若其有剩下在按比率分给 oltp 与dss，level 高的先分配

89. You have created a resource plan, DB_PLAN, using

DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN and you want to impose a restriction on utilization of resources by the database users.

Which step must be performed before you can start using the resource plan?

A.assign users to consumer groups

B.set the resource plan for the instance

C.configure a simple resource plan and specify plan directives

D.configure a complex resource plan and specify plan directives

答案：B

解析：

assign user to group:

将用户OE 分配到组oltp 中

dbms_resource_manager_privs.grant_switch_comsumer_group (

grantee_name=>'OE',

consumer_group =>'OLTP',

grant_option=> false

)

set initial group to user:

就是用户登陆后，自动属于那个consumer_group ,如果不指定，则系统自动认为属于default_consumer_group 。

dbms_resource_manager.set_initial_consumer_group (

user =>'OE',

consumer_group=>'OLTP'

)

90. Which statement is true regarding the creation of nested plans using Resource Manager?

A.Only one nested subplan is allowed.

B.The plans can be nested up to four levels.

C.Resource Manager does not support nested plans.

D.Nested plans control only the degree of parallelism but not the CPU.

E.Each nested plan gets a proportion of the CPU resources assigned to its parent group.

答案：E

解析：proportion:比例