Oracle 10g OCP 042 题库 1-30 题 共168题
声明：对于答案的相关的说明，是个人对Oracle的理解。
1. Because of a power outage,instance failure has occurred. From what point in the redo log does recovery begin and where does it end?
A. Current redo log and inactive redo log
B. Checkpoint position to end of redo log
C. Beginning of redo log to end of redo log
D. All redo logs before the point of last commit
E. Beginning of redo log to checkpoint position.
答案B. Checkpoint 之前的数据已经写入到数据文件。 所以用restore 就可以恢复。 而checkpoint之后的数据没有写入到数据文件，所以需要进行recovery。 Recovery时，对于已经commit的数据，前滚写入到数据文件。 没有commit的数据，进行回滚。
2. Which two operations can be flashed back using the flashback technology? (choose two)
A. Drop user smith;
B. Drop table employees;
C. Drop tablespace users;
D. Alter table sales_rep drop partition p1;
E. Alter table employees drop column desig_id;
答案：AB。
Oracle Flashback 技术 总结
http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4677378.aspx
3. You have to shut down the database instance with the abort option because of a hardware failure. Which statement is true about the subsequent opening of the database?
A. The database would open normally.
B. The database would not open,and it would stop at mount stage.
C. The database would open alter automatically performing instance recovery.
D. The database would not open, and have to perform database recovery to open it.
答案： C.
如果实例异常关闭（宕机，shutdown abort），并且数据文件，控制文件，联机日志都没有丢失。在下次启动时，要利用联机日志的内容进行恢复，这种恢复就是实例恢复（Instance Recovery）。
Instance Recovery 主要包括3个阶段：
1） 根据联机日志内容进行Rollover。
2） 打开数据库，提供服务
3） SMON 或者用户进程进行Rollback。
具体参考 Oracle 备份与恢复概述 中的 3.2 节 恢复种类
http://blog.csdn.net/tianlesoftware/archive/2010/04/16/5490733.aspx
4. You backed up the control file to trace. Which statement is true about the trace file generated?
A. The trace file is in binary format.
B. The trace file has a SQL scripts to re-create the control file.
C. The trace file is a backup set created during the backup of the control file.
D. The trace file contains the instructions to manually re-create the control file.
E. The trace file is an image copy of the control file created during the backup of the the control file.
答案： B
联机文档：
An alternative to the CREATE CONTROLFILE statement is ALTER DATABASE BACKUP CONTROLFILE TO TRACE, which generates a SQL script in the trace file to re-create the controlfile
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5003.htm
5. While running the Oracle Universal Installer on a Unix platform to install Oracle Database 10g software,you are prompted to run orainstRoot.sh. What does this scripts accomplish?
A. It creates the pointer file.
B. It creates the base directory.
C. It creates the inventory pointer file.
D. It creates the Oracle user for installation.
E. It modifies the Unix kernel parmeters to match Oracle's requirement.
答案： C
可以查看$ORACLE_BASE/oraInventory/orainstRoot.sh 脚本的内容。该脚本实际上完成了以下工作：
（1）创建software inventory location pointer file: /etc/oraInst.loc，内容为
inventory_loc=$ORACLE_BASE/oraInventory
inst_group=oinstall
修改该文件属性:chmod 644 /etc/oraInst.loc
（2）创建inventory directory: $ORACLE_BASE/oraInventory
修改文件属性： chmod -R 770 $ORACLE_BASE/oraInventory
chgrp oinstall $ORACLE_BASE/oraInventory
oraInventory目录是用来存储oracle安装的所有软件组件的信息的，每个组件可能占用150k的空间.
6. While setting up an Oracle database for one of your critical applications, you want to ensure that the database is backed up at regular intervals without your intervention(介入). What should you do to achieve the objective?
A. Configure the database to run in archivelog mode.
B. Configure the Flash recovery area to enable automatic database backup.
C. Schedule the database backup using DBMS_JOB package after creating the database.
D. Schedule the database backup using recovery manager(RMAN) commands after creating the database.
E. Schedule the database backup using Database Configuration Assistant(DBCA) while creating the database.
答案：E.
刚看到这个答案的时候，以为答案有问题。Google一下，真有。 我们注意看题目，在安装数据库的时候，确保数据库在没有干预的情况下规则的备份。 只有E中的DBCA工具合适了。 而且DBCA 还真有这个功能(玩了几年Oracle，都没有留意到，杯具中...)
7. The application development team has developed PL/SQL procedures and functions for different purposes and calls them as and when required. The loading of individual procedures or functions into memory degrades performance with every call. Also, it causes a security problem for individual subprograms and loss of program units when the whole system is transported into a new location. Which method would you recommend to the application developers to solve this problem?
A. Avoiding the use of cursors in the subprograms.
B. Using anonymous（匿名） PL/SQL blocks instead of subprograms.
C. Referring to views instead of tables inside the subprograms.
D. creating PL/SQL packages to include interrelated(相关关联) subprograms.
答案：D.
8. View the Exhibit.
Your Oracle 10g database has 6 tablespaces in which:
-TEMP is the default temporary tablespace
- UNDOTBS1 is the default undo tablespace
- USERS is the default permanent tablespace
In this database, which three tablespaces can be made offline? (Choose three.)
A) TEMP
B) PROD
C) USERS
D) SYSAUX
E) SYSTEM
F) UNDOTBS1
答案: BCD。
可以参考在线文档中 Overview of Tablespaces 小节的内容。
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/physical.htm#CNCPT1089
9. The junior DBA in your organization has accidentally deleted the alert log file. What will you do to create new alert log file?
A. Create the new text file as alert.log
B. You have to recover the alert log file from the valid backup.
C. Change the value for the BACKGROUND_DUMP_DEST parameter.
D. No action required. The file would be created automatically by the instance.
答案： D.
10. While planning to manage more than one database in your system,you perform the following activities:
（1） organize different categories of files into independent subdirectories.
（2） Use consistent naming convention for database files.
（3） Separate administrative information pertaining to each database.
Which option corresponds(一致) to the type of activities performed by you?
A. Oracle Managed Files.
B. Oracle Grid Architecture.
C. Optimal Flexible architecture.
D. Oracle database architecture.
答案：C
Optimal Flexible Architecture
http://download.oracle.com/docs/cd/B28359_01/install.111/b32002/app_ofa.htm#i633068
11. Data files of which three tablespace can be recovered by performing an open recovery?(Choose three)
A. TEMP
B. UNDO
C. INDEX
D. SYSAUX
E. SYSTEM
答案：ACD
12. Your tnsnames.ora file has the following entry for the service alias ORCL:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.156.24.216) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
The TNSPING command executes successfully when tested with ORCL but you are not able to connect to the
database instance with the following command:
SQL> CONNECT scott/tiger@orcl
What could be the reason for this?
A) The listener is not running at the server.
B) The TNS_ADMIN environmental variable is set to a wrong value.
C) The orcl.oracle.com database service is not registered with the listener.
D) The DEFAULT_DOMAIN parameter is set to a wrong value in the SQLNET.ORA file.
答案：C.
Oracle Listener 动态注册 与 静态注册
http://blog.csdn.net/tianlesoftware/archive/2010/04/30/5543166.aspx
13. View the Exhibit, and examine the alert messages.
You added space to the TEST tablespace to bring the space usage below the threshold value.
Which statement is true about the Tablespace Full alert?
A) The alert is purged because it is a threshold alert.
B) The alert is cleared and transferred to the alert log file.
C) The alert is automatically cleared and sent to the alert history.
D) The alert appears in Oracle Enterprise Manager Database Control until it is manually cleared.
答案：C
14. Which is the memory area that is created when a dedicated server process is started, and contains data and control information for that server process?
A. SGA
B. PGS
C. Shared Pool
D. Streams Pool
答案： B
Oracle 内存 架构 详解
http://blog.csdn.net/tianlesoftware/archive/2010/05/16/5594080.aspx
15. Which two statements about Recovery Manager(RMAN) backups are true?(Choose two).
A. Online redo log files can be backed up.
B. Archived redo log files are backed up.
C. Only used data blocks can be backed up as backup sets.
D. Only consistent database backups can be performed.
E. RMAN backup can be taken only if the database is configured in ARCHIVELOG mode.
答案：BC
如何 搭建 RMAN 备份平台
http://blog.csdn.net/tianlesoftware/archive/2010/07/16/5740896.aspx
16. Which three pieces of information are to be manatorily provided while creating a new listener using Enterprise Manager database Control? (Choose three).
A. The port used by the listener.
B. The protocol used by the listener.
C. The server name where the listener runs.
D. The log file and trace file destination for the listener.
E. The database services to be registered with the listener.
答案：ABC
17. The SCOTT user has an index on the ITEM_DESC column of the ITEM table. As part of the year-ending task, SCOTT updates the ITEM_DESC column for most of the rows in the ITEM table. How does this change to the table affect the index?
A. An update in a leaf row takes place.
B. The index becomes invalid after the update.
C. The leaf block containing the row to be updated is marked as invalid.
D. A row in the leaf block of the index for the key value is deleted and inserted.
答案：D
18. In the middle of a transaction,a user session was abnormally terminated but the instance is still up and the database is open. Which two statements are true in the scenario（方案）? (Choose two).
A. Event viewer gives more details on the failure.
B. The alert log file gives detailed information about the failure.
C. PMON rolls back the transaction and releases the locks.
D. SMON rolls back the transaction and releases the locks.
E. The transaction is rolled backup by the next session that refers to any of the blocks updated by the failed transaction.
F. Data modified by the transaction up to the last commit before the abnormal termination is retained in the database.
答案：C F
19. The application workload on your database is same bwtween 10 a.m. And 11 a.m. On weekdays. Suddenly you observe poor performance between 10 a.m. And 11 a.m. In the middle of the week. How would you identify the changes in configuration settings, workload profile, and statistics to diagnose the possible causes of the performance degradation?
A. By using the SQL access advisor
B. By using the automatic workload repository report.
C. By running the automatic database diagnostic monitor(ADDM)
D. By using the automatic workload repository (AWR) compare period report.
E. By analyzing the output of the v$ACTIVE_SESSION_HISTORY view.
答案：D
Oracle AWR 介绍
http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4682300.aspx
20. Your database is in NOARCHIVELOG mode. After which two operations you should take the backup of the control file?
A. Adding a new user to the database.
B. Dropping a user from the database.
C. Dropping a table from the database.
D. Dropping a data file from a tablespace.
E. Adding a new tablespace to the database.
答案： D E. 控制文件里保存了数据文件的信息，所以添加删除都会修改控制文件里的内容，所以需要修改。
Oracle 控制文件
http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4974440.aspx
21. Which three statements are true regarding the logical structure of the Oracle database? (Choose three).
A. Each segment contains one or more extents.
B. Multiple tablespace can share single data file.
C. A data block is the smallest unit of I/O for data files.
D. It is possible to have tablespace of different block sizes in a database.
E. Each data block in the database always corresponds to one OS block.
答案：ACD
表空间(tableSpace) 段(segment) 盘区(extent) 块(block) 关系
http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4962476.aspx
22. You executed the following command to start the database:
SQL> STARTUP
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 789000 bytes
Variable Size 229635576 bytes
Database Buffers 50331648 bytes
Redo Buffers 262144 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
Which view would you query at this stage to investigate this missing control file?
A) V$INSTANCE
B) V$CONTROLFILE
C) DBA_CONTROL_FILES
D) V$DATABASE_PROPERTIES
E) V$CONTROLFILE_RECORD_SECTION
答案: B
23. Which two are valid locking levels that are used by transactions in an Oracle database? (Choose two)
A. Row level
B. Block level
C. Object level
D. Schema level
E. Database level
答案： AC
在这个地方研究了半天，也不是十分明白。
ORACLE 锁机制
http://blog.csdn.net/tianlesoftware/archive/2009/10/20/4696896.aspx
http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/consist.htm#CNCPT1341
24. Your database is started by using the server parameter file (SPFILE). You issued this command to change
the value of the LOG_BUFFER initialization parameter:
ALTER SYSTEM SET LOG_BUFFER=24M SCOPE=BOTH;
What would be the outcome of this command?
A) The command would return an error because LOG_BUFFER is a static parameter.
B) The parameter value would be changed and it would come into effect immediately.
C) You need to restart the database so that parameter changes can come into effect.
D) The command would succeed only if initialization parameter LOG_ARCHIVE_MAX_PROCESS is set to value 2.
答案： A.
LOG_BUFFER specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.
The log buffer size depends on the number of redo strands in the system. One redo strand is allocated for every 16 CPUs and has a default size of 2 MB. Oracle allocates a minimum of 2 redo strands per instance. When the log buffer size is not specified, any remaining memory in the redo granules is given to the log buffer.
LOG_BUFFER
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams127.htm#REFRN10094
SQL> select name,issys_modifiable from v$parameter where name='log_buffer';
NAME ISSYS_MOD
-------------------- ---------
log_buffer FALSE
通过这个查询，我们知道，修改这个参数必须重启数据库。
25. Which two statements are true about the roles in the Oracle database? (Choose two)
A. A role can be granted to itself.
B. Roles are owned by the sys user.
C. Roles can be granted to other roles.
D. A role can not be assigned external authentication.
E. A role can contain both system and object privileges.
答案： C E
Oracle 用户及角色 介绍
http://blog.csdn.net/tianlesoftware/archive/2009/11/09/4786956.aspx
26. You have been recently hired as a database administrator. Your senior manager asks you to study the
production database server and submit a report on the settings done by the previous DBA. While observing
the server settings, you find that the following parameter has been set in the parameter file of the database:
REMOTE_OS_AUTHENT = TRUE
What could have been the reason to set this parameter as TRUE?
A) to enable operating system authentication for a remote client
B) to restrict the scope of administration to identical operating systems
C) to allow the start up and shut down of the database from a remote client
D) to enable the administration of the operating system from a remote client
E) to disable the administration of the operating system from a remote client
答案： A
参考：http://download.oracle.com/docs/cd/E11882_01/network.112/e10746/asoauth.htm#ASOAG9769
27. View this parameter setting in your database:
DB_CREATE_FILE_DEST='D: /oracle/product/10.2.0/oradata/oracle'
You created a tablespace by using this command:
CREATE TABLESPACE USERS;
Which two statements are true about the USERS tablespace? (Choose two.)
A) The tablespace has two data files.
B) An error is reported and tablespace creation fails.
C) Data files are created with names generated by the instance.
D) The tablespace can be extended without specifying the data file.
E) Data files belonging to the USERS tablespace cannot be renamed.
答案：C D
28. View the Exhibit to see the source and target databases.
You have created a database link, devdb.us.oracle.com, between the databases PRODDB and DEVDB. You
want to import schema objects of the HR user using Oracle Data Pump from the development database,
DEVDB, to the production database, PRODDB. You execute the following command on the target database
server:
$impdp system/manager directory = DB_DATA
dumpfile = schemas.dat
schemas = hr
flashback_time = 2004-02-03 09: 00
The command fails, displaying the following error:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/home/oracle/schema/schemas.dat" for read
ORA-27037: unable to obtain file status
What would you do to overcome the error?
A) remove the dumpfile option in the command
B) remove the flashback_time option in the command
C) add the user, SYSTEM, to the schemas option in the command
D) add network_link = devdb.us.oracle.com option in the command
E) remove the schemas option and add the network_link = devdb.us.oracle.com option in the command
F) remove the dumpfile option and add the network_link = devdb.us.oracle.com option in the command
答案：F
29. What is the implication of setting the initialization parameter FAST_START_MTTR_TARGET to 0 in your
database?
A) MTTR Advisor would be disabled.
B) Redo Log Advisor would be disabled.
C) Automatic tuning of checkpoint would be disabled.
D) Checkpoint information would not be written to the alert log file.
答案： C
FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified, FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams084.htm#REFRN10058
30. Users of HR schema complain about slower-than-normal performance. On investigation the DBA found that maintenance was recently performed on some of the tables. The DBA traced the query that takes longer than normal to execute.
View the Exhibit exhibit_before.
After the DBA resolves the problem, the query performs normally.
View the Exhibit exhibit_after.
What action would the DBA have taken to resolve the performance problem?
A) analyzed the EMPLOYEES table to collect the current statistics
B) moved the EMPLOYEES table in to a locally managed tablespace
C) moved the EMPLOYEES table to another location in the same tablespace
D) reorganized the associated indexes for the EMPLOYEES table that were in an unusable state
E) moved the indexes associated with the EMPLOYEES table to the same tablespace where the EMPLOYEES table exists
答案： D
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977
DBA3 群：63306533; 聊天 群：40132017
