2008 中國首屆Oracle資料庫精英工程師評選電子試題 和答案(2008年9月3日)

一、選擇題

1．Which six files are maintained in the Flash Recovery Area? (Choose six.)

A. control file

B. RMAN files

C. password file

D. parameter file

E. flashback logs

F. data file copies

G. core dump files

H. archived log files

I. RMAN recovery scripts

J. control file autobackpus

回答: A，B，E，F，H，J

2．You are unable to move the Unified Job Scheduler occupant from the SYSAUX

tablespace to the USERS tablespace. What could be the reason?

A. None of the SYSAUX occupants can be relocated.

B. The USERS tablespace is a bigfile tablespace (BFT).

C. The united Job Scheduler occupant cannot be relocated.

D. The SYSAUX occupants can be relocated to the SYSTEM tablespace only.

回答: C

3．You want to enforce a company's business policy on several objects by using a single

policy function.

Which two types of policies can be assigned to the policy_type argument in the

dbms_rls.add_policy procedure to achieve the above objective? (Choose two.)

A. DBMS_RLS.STATIC

B. DBMS_RLS.DYNAMIC

C. DBMS_RLS.SHARED_STATIC

D. DBMS_RLS.CONTEXT_SENSITIVE

E. DBMS_RLS.SHARED_CONTEXT_SENSITIVE

回答: C,E

4．You need to ensure that the database users should be able to use the various

flashback query features in order to go back in time by four hours. What should

you do? (Choose two.)

A. set SQL_TRACE=true

B. set UNDO_RETENTION=14400

C. set FAST_ST =240

D. set LOG_CHECKPOINT_INTERVAL=240

E. set DB_FLASHBACK_RETENTION_TARGET=14400

F. Issue the AART_MTTR_TARGETLTER DATABASE FLASHBACK ON; command.

G. set the RETENTION GUARANTEE clause for the undo tablespace

回答: B,G

5．You are using Recovery Manager (RMAN) to perform backups. In which three

situations would you perform a compressed backup? (Choose three.)

A. Your database includes a large number of BFILEs.

B. You are making image copies, and you have not enabled tablespace compression.

C. You are backing up to tape and your tape device performs its won compression.

D. You are using disk-based backups and disk space in your Flash Recovery Area, or

other disk-based backup destinations are limited.

E. You are performing your backups to some device over a network where reduced

network bandwidth is more important than CPU usage.

F. You are using some archival backup media, such as CD or DVD, where reducing

backup sizes saves media costs and archival storage.

回答: D,E,F

6．Identify four uses of the Oracle Scheduler. (Choose four.)

A. Enables you to set idle time limits for a resource plan.

B. Enables you to schedule job execution based on time.

C. Enables you to execute jobs in a clustered environment.

D. Enables you to assign priorities to the consumer groups.

E. Enables you to map a consumer group to an Oracle user.

F. Enables you to create a job that makes use of saved programs and schedules.

G. Enables you to periodically execute operating system script files located on the same

server as the database.

回答: B, C, G, Fwww.gurufl）

7．You flashed back the jobs table at 11:00 a.m. to its state at 9:30 a.m.

At 11:15 a.m., you decided to retrieve the jobs table as it was at 10:12 a.m.

What would you do to retrieve the jobs table fast and with minimum impact to

other objects?

A. drop and re-create the table

B. perform point-in-time recovery

C. use the ROLLBACK command with SCN

D. use the FLASHBACK TABLE command

E. use the FLASHBACK DATABASE command

回答: D

8．You need to transport tablespaces between platforms with different endians. Which

two steps are required in addition to the regular steps needed for transporting

tablespaces? (Choose two.)

A. The DB_FILE_NAME_CONVERT parameter must be set in the init.ora file.

B. The CONVERT command of SQL*Plus must be used to change byte ordering.

C. No other explicit action is required.

D. The CONVERT command of the Recovery Manager utility must be used to change

byte ordering.

E. The COMPATIBLE parameter must be set to 10.0.0 or higher in both the source and

the target databases.

回答: D,E

9．You modified the optimizer statistics of a table by using the

DBMS_STATS.GATHER_TABLE_STATS procedure.

You realized that the newly generated statistics have created suboptimal execution

plans for that particular table.

What would you do to revert to the previous set of statistics as soon as possible?

A. run Automatic Database Diagnostic Monitor (ADDM) Advisor to recommend a

solution

B. wait for the default automatic optimizer statistics generation

C. execute the DBMS_STATS.RESTORE_TABLE_STATS procedure

D. execute the DBMS_STATS.RESTORE_DATABASE_STATS procedure

E. purge the existing table statistics and execute the DBMS_STATS.GATHER_TABLE

procedure with a different set of parameters

回答: C

10．You have 100 segments in the USERS tablespace. You realize that the USERS

tablespace is running low on space. You can use Segment Advisor to _______.

A. add data files to the tablespace

B. identify the segments that you should shrink

C. modify the storage parameters for the tablespace

D. automatically shrink the segments with unused space

E. check the alerts generated for the table space that is running low on space

回答: B

11．Which three statements regarding collection of database usage metrics are correct?

(Choose three.)

A. The metrics survive database reboots and crashes.

B. The MMON process tracks and records the database usage metrics.

C. The MMAN process tracks and records the database usage statistics.

D. The Oracle Enterprise Manager (OEM) repository is used to store metrics.

E. The SMON process tracks and records the database usage metrics once a month.

F. The SYSAUX tablespace contains table, where the database usage metrics are

physically stored.

G. The SYSTEM tablespace by default contains tables where the database usage metrics

and physically stored.

回答: A, B, FGurufl）

12．An online tablespace, TEST_TBS, is full and you realize that no server-managed

tablespace threshold alerts were generated for the TEST_TBS tablespace. What

could be the reason, if the TEST_TBS tablespace does not include autoextensible

data files?

A. TEST_TBS is a small file tablespace.

B. TEST_TBS is a bigfile tablespace (BFT).

C. TEST_TBS is the default temporary tablespace.

D. TEST_TBS is the dictionary-managed tablespace.

E. Threshold values for the TEST_TBS tablespace are not explicitly specified.

回答: D

13．Which three statements regarding compressed backups are correct? (Choose three)

A. The compression applies to only image copies.

B. The compressed backup is applicable to only data files.

C. The COMPATIBLE parameter must be set to 10.0.0 or higher.

D. The compressed backup is applicable to only the entire database backup.

E. Restoration from a compressed backup does not require any special action.

F. The compression provided by media manager and the RMAN backup compression

should not be used together.

回答: C，E，F

14．Which three statements regarding the bigfile tablespace (BFT) are correct? (Choose

three)

A. BFT can be dictionary managed.

B. A BFT always contains a single file.

C. BFT is supported for locally managed tablespaces.

D. In BFT, the maximum file size ranges from 8 TB to 128 TB.

E. BFT, when uses with Oracle Managed Files (OMF), provides complete data file

transparency.

回答: B，C，D

15．Which process performs the rebalance data extent movements across ASM disk

groups in ASM?

A. Checkpoint (CKPT)

B. System Monitor (SMON)

C. ASM Rebalance (ARBn)

D. Process Monitor (PMON)

E. ASM Background (ASMB)

F. ASM Rebalance Master (RBAL)

回答: C

16．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

and execute the following command:

SQL> ALTER DISKGROUP dgroupA ADD DISK '/devices/D4';

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 would result in an error because no wildcard characters can be used in

the disk name.

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

the disk group.

E. The command first detaches all the member disks starting with "D" and then

reattached all of them including the new disk.

回答: A

17．To which three elements is the size of the block change tracking file proportional?

(Choose three)

A. Number of redo log groups.

B. Number of DBWR process.

C. Size of the database in bytes.

D. Number of archiving destinations.

E. Number of old backups maintained by the block change tracking file.

F. Number of enabled threads in Real Application Cluster (RAC) environment.

回答: C, E, F

18．Which three statements regarding resumable statements are correct? (Choose

three)

A. Resumable statements cannot contain a distributed transaction.

B. Resumable statements can only be enabled at the system level.

C. Resumable statements can be enabled at the system level as well as the session level.

D. RESUMABLE_TIMEOUT=0 disabled resumable space allocation

for all sessions.

E. The ALTER SESSION ENABLE RESUMABLE command is obsolete in Oracle

Database 10g.

F. Only the local instance is affected when changing the resumable timeout parameter in

a distribution transaction.

回答: C,D,F

19．Which three files can be managed using an ASM Instance? (Choose three.)

A. Data files

B. Audit files

C. Trace files

D. Export files

E. Alert log files

F. Archived log files

G. Change tracking files

回答: A, F, GGurufl）

20．Which three statements are true about the privileged connection options available

in Oracle 10g? (Choose three.)

A. The CONNECT INTERNAL is disallowed

B. The Server Manager tool is no longer supplied

C. The listener must be running to make a connection

D. The use of a remote password login file (orapwd) is unchanged

回答: A,B,D

二、論述題

1.背景描述：客戶有一個9208的資料庫，沒有使用歸檔方式，資料庫的資料檔案總共800G。

A．客戶執行應用時，出現ORA-01578 ORACLE data block corrupted錯誤，資料不能正常查詢。應如何處理使資料損失最小？

使用DBMS_REPAIR系統包，2.設定10231 events,然後EXPORT備份表，刪除表重建並IMP資料

B．重建表以後，客戶認識到歸檔的重要性，決定使用歸檔方式，用RMAN備份資料庫到DISK，但當執行RMAN備份時仍然報ORA-19566: exceeded limit of 0 corrupt blocks for file file_name錯誤，導致RMAN備份失敗，出錯的資料塊還是原來的塊，檢查發現此資料塊上已無表或索引。問題出在哪？

RMAN備份時將所有修改過的資料塊都要做備份，當壞塊沒有被從新佔用時，還是壞塊

Corrupted blocks will still be reported by RMAN and DBV until they are reused and reformatted.

C．處理完備份問題後，執行RMAN備份成功，備份到硬碟上的備份集大小為20G，費時20分鐘。客戶最近新購買了帶庫裝置，自帶速度50M/S；但向磁帶備份時總共費時3小時，透過備份管理軟體查詢的結果是備份集大小為20G左右。為什麼向磁帶備份耗時較多？如何解決？

回答: 向DISK備份時，只掃描修改過的塊；向TAPE備份時掃描整個資料檔案

2．某客戶使用ORACLE10.2.0.3兩節點RAC，其中每節點有4塊網路卡，兩塊作clust_interconnect, 兩塊作PUBLIC對外服務，兩個OCR裸裝置，三個VOTING DISK；客戶需要做如下測試，請把預期的結果填入下列表中：

Processing continues unaffected. oracle*~;K$R*[;D%p*Eoracle4y7D&d1[9r$E/z$E
No corruption of the database, ASM metadata, voting disks or OCR. oracle2P!B*A1v:Z%t

9]8Q7@)`&`,|"h0_)L5TOCR vote is transferred from failed OCR to surviving OCR mirror, as shown in CW logs
$u$d9R#e6N4Y1L0A5_Individual nodes error, and processing stops.
6{(^"c2Q3A5O"I rProcessing continues unaffected.
:@)k&P%v8R8[/T#h&LGurufl.com.cn-技術論壇
3Q,v7&R,a&p
*u(o)c%I%W3CProcessing continues unaffected. !r'X6V9?1p.s;x/k!v7Loracle-C!d9N7i!q*l7~
Loss of two voting disks causes all cluster nodes to reboot.2Z!w;L:p3~5v2d-q3i"R
CRSD process is restarted. (Check CRS logs)，Processing continues unaffected.
'V1Y+K!R,t%?5[oracleEVMD process is restarted. (Check CRS logs)，Processing continues unaffected.
2T$J,B d.c"z(IGurufl.com.cn-技術論壇Network traffic should fail over to other NIC without any impact on VIP or clients.oracle#h5f,X7]%]:C&x,w"H
CRS and/or RAC will detect split brain situation and evict node and instance from CRS cluster and RAC cluster. In a two node cluster the node with the lowest node number will survive.
"p&}&h:|!j5moracleNetwork traffic should fail over to other NIC without any impact on interconnect traffic or instances.9t-N3}*V/C Z
Network traffic should fail over to other switch without any impact on interconnect traffic or instances.
5^!G2R0s6k(q3PNo impact on connected database sessions.
5g!Y2Z%g2d)w4Y)H3B3nNew connections are redirected to listener on other node (depends on client configuration)"k9b-a5[5F9@*p
Local database instance should still receive new connections via other listeners.oracle4V4_6{0y&z%R
Listener restarted by CRS

3．你做過的最經典的問題排除的例子是什麼？