ocp 认证分类

来源：互联网发布：qq飞车卡车软件编辑：程序博客网时间：2024/05/20 11:26

厚积薄发、精益求精
1 / 233
目录
第一章 ORACLE 体系结构 ........................................................................................................ 2
第二章 ORACLE 使用 .............................................................................................................. 15
第三章 ORACLE 实例管理 ...................................................................................................... 19
第四章 ORACLE 建立数据库 .................................................................................................. 28
第五章数据字典管理 ............................................................................................................. 43
第六章控制文件管理 ............................................................................................................. 45
第七章管理REDO 日志......................................................................................................... 50
第八章管理表空、存储架构管理 ......................................................................................... 56
第九章管理UNDO 表空间..................................................................................................... 71
第十章管理表、视图 ............................................................................................................. 76
第十一章管理索引 ..................................................................................................................... 87
第十二章管理约束 ..................................................................................................................... 91
第十三章管理安全和资源 ........................................................................................................... 97
第十四章管理用户、权限 ....................................................................................................... 115
第十五章管理角色 ..................................................................................................................... 119
第十六章审计 ............................................................................................................................. 122
第十七章加载数据 ................................................................................................................... 126
第十八章全球支持 ................................................................................................................... 128
第十九章数据库优化 ................................................................................................................. 130
第二十章备份和恢复 ................................................................................................................. 159
第二十一章网络配置 ................................................................................................................. 226
厚积薄发、精益求精
2 / 233
第一章 ORACLE 体系结构
（关键字：Instance 、SGA、share pool、data buffer、logbuffer、DBWR、LGWR、SMON、
PMON、CKPT）
1. 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. PGA
C. Shared Pool
D. Streams Pool
Answer: B
2. You find that the database performance degrades while you backup
the PROD database using Recovery Manager (RMAN). The PROD
database is running in shared server mode. The database instance is
currently using 60% of total operating system memory. You suspect the
shared pool fragmentation to be the reason.
Which action would you consider to overcome the performance
degradation?
A. Configure Java Pool to cache the java objects.
B. Configure Streams Pool to enable parallel processing.
厚积薄发、精益求精
3 / 233
C. Increase Shared Pool size to cache more PL/SQL objects.
D. Increase Database Buffer Cache size to increase cache hits.
E. Configure Large Pool to be used by RMAN and shared server.
F. Increase the total System Global Area (SGA) size to increase memory
hits.
Answer: E
3. Your database is configured with the following parameters related to
SGA:
SGA_TARGET=256MB
SHARED_POOL_SIZE=32MB
DB_CACHE_SIZE=100MB
LARGE_POOL_SIZE=0
JAVA_POOL_SIZE=0
STREAMS_POOL_SIZE=0
Which two statements are true about the configuration? (Choose two.)
A. The SGA_TARGET value cannot be sized smaller than 100 MB.
B. The shared pool and the default buffer pool will not be sized bigger
than 32 MB and 100 MB,respectively.
C. The shared pool and the default buffer pool will not be sized smaller
than 32 MB and 100 MB,respectively.
D. 124 MB (256 minus 132) of memory is available for use by all the
厚积薄发、精益求精
4 / 233
manually sized components.
E. 124 MB (256 minus 132) of memory is available for use by all the
manually and automatically sized components.
Answer: CE
4. Which two statements are true regarding the SGA_TARGET
initialization parameter? (Choose two.)
A. It can be increased up to the value of the SGA_MAX_SIZE parameter.
B. Increasing the value of SGA_TARGET up to the value of
SGA_MAX_SIZE disables the automatic shared memory management
feature.
C. Reducing the value of the SGA_TARGET parameter takes away
memory from both autotuned and manually sized components.
D. Increasing the value of the SGA_TARGET parameter distributes the
increased memory among all the autotuned components.
Answer: AD
5. 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 this 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.
厚积薄发、精益求精
5 / 233
D. SMON rolls back the transaction and releases the locks.
E. The transaction is rolled back 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.
Answer: CF
6. You are working on the 24X7 database with high transaction volume,
to ensure faster instance recovery on your database you set the
FAST_START_MTTR_TARGET initialization parameter to a very low
value. What effect it will have on the database?
A. The database performance would be enhanced.
B. The redo log files would be get filled more frequently.
C. The overall database performance would be degraded.
D. The mean time to recover (MTTR) would be increased.
Answer: C
7. 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
厚积薄发、精益求精
6 / 233
E. beginning of redo log to checkpoint position
Answer: B
8. View the Exhibit.
What would happen if you change the value of Desired Mean Time To
Recover to 30?
A. The MTTR Advisor would be enabled.
B. The Redo Log Advisor would be disabled.
C. Automatic checkpoint tuning would be disabled.
D. The redo log from log buffers would be written to redo log files at a
slower rate.
Answer: A
9. 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.
厚积薄发、精益求精
7 / 233
D. Checkpoint information would not be written to the alert log file.
Answer: C
10. Your production database is running in the ARCHIVELOG mode
and the ARCn process is functional.You have two online redo log groups.
Which three background processes would be involved when a log switch
happens? (Choose three.)
A.archival
B.log writer
C.database writer
D.system monitor
E.process monitor
F.change tracking writer
Answer: ABC
11. 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
厚积薄发、精益求精
8 / 233
F.streams pool
G.keep buffer cache
H.database buffer cache
Answer: BCEH
12. 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.
厚积薄发、精益求精
9 / 233
E.The SHARED_POOL_SIZE initialization parameter is set to a nonzero
value.
Answer: A
13. 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
Answer: C
14. 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
厚积薄发、精益求精
10 / 233
Answer: B
15. 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.
Answer: AB
16. You have set some of the initialization parameters as:
DB_BLOCK_SIZE = 8KB
SGA_MAX_SIZE = 2GB
厚积薄发、精益求精
11 / 233
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.
Answer: AC
17. 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
厚积薄发、精益求精
12 / 233
B.streams pool
C.redo log buffer
D.keep buffer cache
E.recycle buffer cache
Answer: A
18. 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
Answer: A
19. 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.
厚积薄发、精益求精
13 / 233
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.
Answer: BD
20. 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
Answer: B
21. 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
厚积薄发、精益求精
14 / 233
Management mode.
D.The sessions are running in the automatic Program Global Area (PGA)
memory management mode.
Answer: D
厚积薄发、精益求精
15 / 233
第二章 ORACLE 使用
（关键字：OFA、OEM、OUI、DBCA、NETCA）
1. A user wants to connect to the database instance from an application
that is running on a remote machine. Which tools should the DBA use
to establish the required configuration to ensure that the user is able to
connect to the database instance? (Choose two.)
A. Data Pump
B. Oracle Net Manager
C. Oracle Enterprise Manager
D. Oracle Universal Installer (OUI)
E. Database Configuration Assistant (DBCA)
Answer: BC
2. 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
厚积薄发、精益求精
16 / 233
C. Optimal Flexible Architecture
D. Oracle database architecture
Answer: C
3. While running the Oracle Universal Installer on a Unix platform to
install Oracle Database 10g software, you are prompted to run
orainstRoot.sh script. What does this script 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 parameters to match Oracle's
requirement.
Answer: C
4. Which is the correct description of the significance of the
ORACLE_HOME environmental variable?
A. It specifies the directory containing the Oracle software.
B. It specifies the directory containing the OracleManaged Files.
C. It specifies the directory for database files, if not specified explicitly.
D. It specifies the base directory of Optimal Flexible Architecture (OFA).
Answer: A
5. You created a response file and want to check it before starting
厚积薄发、精益求精
17 / 233
installations in silent mode. You started installation of Oracle software
in interactive mode by providing the response file. In the middle of the
installation, you realize that the behavior of the Oracle Universal
Installer (OUI) is not consistent with
the response file.
What action would you take to detect the cause of this behavior?
A. Compare the contents of the install.log file with the response file.
B. Refer to the contents of the oraInst.loc file to verify the steps
performed by OUI.
C. Compare the contents of the installActions.log file with that of the
response file.
D. Refer to the alert log file for information regarding the actions
performed by OUI during installation.
Answer: C
6. When you try to start the Database Control by using the emctl start
dbconsole command the following error is displayed:
TZ set to America/New_york
OC4J Configuration issue.
/u01/app/oracle/product/10.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_
orcl.us.oracle.com not found.
Which two environment variables do you need to set appropriately to
厚积薄发、精益求精
18 / 233
avoid such errors, and start the Database Control successfully?
(Choose two.)
A. NLS_LANG
B. ORACLE_SID
C. ORACLE_HOME
D. ORACLE_BASE
E. LD_LIBRARY_PATH
Answer: BC
厚积薄发、精益求精
19 / 233
第三章 ORACLE 实例管理
（关键字：PFILE 、SPFILE、MOUNT、INSTANCE）
1. You are in the middle of a transaction and very crucial data has been
modified. Because of a hardware failure, the instance has shut down
before synchronizing all the database files.
Which two statements are true? (Choose two.)
A. On startup, SMON coordinates instance recovery.
B. On startup, CKPT coordinates instance recovery.
C. On startup, use RMAN to perform instance recovery.
D. Uncommitted changes will be rolled back after the database is
opened.
E. On startup, perform media recovery and then instance recovery.
F. On startup, all the files will be synchronized and you get both
committed and uncommitted data.
Answer: AD
2. 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:
厚积薄发、精益求精
20 / 233
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
Answer: A
3. 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
厚积薄发、精益求精
21 / 233
come into effect.
D. The command would succeed only if initialization parameter
LOG_ARCHIVE_MAX_PROCESS is set to value 2.
Answer: A
4. Which statement regarding the contents of the V$PARAMETER view
is true?
A. displays only the list of default values
B. displays only the list of all basic parameters
C. displays the currently in effect parameter values
D. displays only the list of all advanced parameters
E. displays the list of all the parameter files of a database
F. displays the current contents of the server parameter file
Answer: C
5. Which three statements are true about the stages of database startup?
(Choose three.)
A. Data files and redo log files can be renamed at the MOUNT stage.
B. Control files are read at the OPEN stage for the location of data files.
C. Control files are required to bring the database to the NOMOUNT
stage.
D. Data files and redo log files are made available to users at the OPEN
stage.
厚积薄发、精益求精
22 / 233
E. Data files and online redo log files are checked for consistency while
opening the database.
Answer: ADE
6. Which two database operations can be performed at the MOUNT
stage of database startup? (Choose two.)
A. renaming of data files
B. dropping the database user
C. renaming of control files
D. multiplexing of control files
E. configuring the database in ARCHIVELOG mode
Answer: AE
7. You executed the STARTUP MOUNT command to start your
database.
For which database operation do you need to start the database in the
MOUNT state?
A. renaming the control files
B. dropping a user in your database
C. enabling or disabling redo log archiving
D. dropping a tablespace in your database
E. recreating
the control files, after you lost all the control files in your database
厚积薄发、精益求精
23 / 233
Answer: C
8. 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 after automatically performing instance
recovery.
D. The database would not open, and you have to perform database
recovery to open it.
Answer: C
9. What are the consequences of executing the SHUTDOWN ABORT
command? (Choose two.)
A. The database files are synchronized.
B. Uncommitted changes are not rolled back.
C. The database is closed, but the instance is still started.
D. Database buffers and redo buffers are not written to the disk.
E. The database undergoes automatic media recovery during the next
startup.
Answer: BD
10. The junior DBA in your organization has accidentally deleted the
厚积薄发、精益求精
24 / 233
alert log file. What will you do to
create new alert log file?
A. Create the new text file 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.
Answer: D
11. Your alert log file has the following information:
Tue May 25 17:43:38 2004
ORA00060:Deadlock detected. More info in file
/u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc.
What would you do to ensure that database is still running correctly?
A. examine the trace file and kill the session that caused the deadlock
B. examine the trace file and kill both the sessions responsible for the
deadlock
C. examine the trace file for details and ask the user who caused the
deadlock to roll back the transaction
D. examine the trace file for details and remember that deadlocks are
resolved automatically by Oracle database
E. examine the trace file for details and use the Undo Advisor to rollback
厚积薄发、精益求精
25 / 233
the transaction that caused the deadlock
Answer: D
12. 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
ORA00205: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
Answer: B
13. You enabled Automatic Shared Memory Management. The
initialization parameters are set as shown below:
厚积薄发、精益求精
26 / 233
SGA_TARGET = 10GB
SGA_MAX_SIZE = 14GB
STREAMS_POOL_SIZE = 1GB
SHARED_POOL_SIZE = 3GB
Which two statements are correct in this scenario? (Choose two.)
A.A maximum of 3 GB can be allocated to shared pool.
B.The value for SGA_TARGET can be increased up to a maximum of 14
GB.
C.A total of 14 GB memory will be allocated to the automatically tuned
memory components.
D.Increasing the value for SGA_TARGET will automatically increase the
memory allocated for STREAMS_POOL_SIZE.
E.Increasing the value for SGA_TARGET to 12 GB will automatically
increase the memory allocated to autotuned parameters.
F.Reducing the value for SGA_TARGET to 9 GB will automatically
decrease the memory allocated to shared pool from 3 GB to 2 GB.
Answer: BE
14. The production database has been functional for the last seven days.
Because of application requirements, some of the initialization
parameters were changed during run time without any comments.You
have been asked to find out the value of the parameters when the
厚积薄发、精益求精
27 / 233
instance was started.
Which source would you use to locate this information?
A.fixed views
B.the alert log file
C.the parameter file
D.the server parameter file
E.dynamic performance views
Answer: B
15. In the parameter file of the production database, the
BACKGROUND_DUMP_DEST parameter is set to
$ORACLE_HOME/users. Which two types of files would you find in this
location? (Choose two.)
A.the alert log file
B.the core dump files
C.the change tracking file
D.the user session trace files
E.the database audit trail files
F.the background process trace files
Answer: AF
厚积薄发、精益求精
28 / 233
第四章 ORACLE 建立数据库
(关键字：CONTROL FILE、DBCA、ASM)
1. You want the user APP_DBA to administer the Oracle database from
a remote machine. APP_DBA is granted the SYSDBA privilege to
perform administrative tasks on the database.
Which file is used by the Oracle database server to authenticate
APP_DBA?
A. control file
B. password file
C. listener controller file
D. control file and password file
Answer: B
2. In which situation would you use the Oracle Shared Server
configuration?
A. when performing export and import using Oracle Data Pump
B. when performing backup and recovery operations using Oracle
Recovery Manager
C. when performing batch processing and bulk loading operation in a
data warehouse environment
D. in an online transaction processing (OLTP) system where large
厚积薄发、精益求精
29 / 233
number of client sessions are idle most of the time
Answer: D
3. You want to create a new optimized database for your transactional
production environment to be used by a financial application. While
creating the database, you want the Oracle software to take care of all
basic settings to optimize the database performance.
Which method would you use to achieve this objective?
A. Use the CREATE DATABASE .. command to create the database with
Oraclemanaged files.
B. Use the Database Configuration Assistant (DBCA) to create the
database with Oraclemanaged files.
C. Use Enterprise Manager to create a new database with the Online
Transaction Processing (OLTP) option.
D. Use Database Configuration Assistant (DBCA) to create the database
with Transaction Processing template.
E. Use the CREATE DATABASE .. command to create the database with
Automatic Storage Management (ASM) file system.
Answer: D
4. 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
厚积薄发、精益求精
30 / 233
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
Answer: E
5. After being hired as a database administrator, you find that there is
only one database that is functional and that is being accessed by the
applications. You want to create a replica of the database, to be used for
testing purposes.
What is the best method to create the replica?
A. create a database by using CREATE DATABASE .. command and
manually copy the data
B. use Database Configuration Assistant (DBCA) to create a template
from the existing database to contain the database structure
C. use DBCA to create a template from the existing database to contain
厚积薄发、精益求精
31 / 233
the database structure and then manually copy the data using Oracle
Data Pump
D. use DBCA to create a template from the existing database to contain
the database structure with data files and then use the same template
to create the database in the new location
Answer: D
6. 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.
Answer: CD
7. Which two statement about Automatic Storage Management (ASM)
are true? (Choose two.)
A. ASM provides mirroring on file by file basis.
厚积薄发、精益求精
32 / 233
B. ASM provides automatic load balancing across all ASM disks.
C. ASM supports the Oracle database and operating system files.
D. ASM can be used to store trace files, alert log files, and the server
parameter file (SPFILE).
Answer: AB
8. Immediately after adding a new disk to or removing an existing disk
from an Automatic Storage Management (ASM) instance, you find that
the performance of the database decreases initially, until the addition
or removal process is completed. Performance then gradually returns to
normal levels.
Which two activities could you perform to maintain a consistent
performance of the database while adding or removing disks? (Choose
two.)
A.increase the number of checkpoint processes
B.define the POWER option while adding or removing the disks
C.increase the number of DBWR processes by setting up a higher value
for DB_WRITER_PROCESSES
D.increase the number of slave database writer processes by setting up
a higher value for DBWR_IO_SLAVES
E.increase the number of ASM Rebalance processes by setting up a
higher value for ASM_POWER_LIMIT during the disk addition or
厚积薄发、精益求精
33 / 233
removal process
Answer: BE
9. 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
厚积薄发、精益求精
34 / 233
F.DB_CREATE_FILE_DEST
Answer: E
10. 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.
Answer: B
11. 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
厚积薄发、精益求精
35 / 233
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
Answer: D
12. 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
厚积薄发、精益求精
36 / 233
dictionary views.
Answer: D
13. 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.
Answer: A
14. Consider the following command to create a tablespace in your
厚积薄发、精益求精
37 / 233
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 userdefined template.
D.It would create a tablespace with a data file that does not have an
alias, and its attributes are set by the userdefined template.
Answer: C
15. 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*';
厚积薄发、精益求精
38 / 233
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.
Answer: A
16. 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',
厚积薄发、精益求精
39 / 233
'/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.
Answer: B
17. 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
厚积薄发、精益求精
40 / 233
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.
Answer: A
18. 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?
厚积薄发、精益求精
41 / 233
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.
Answer: A
19. 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
threeway mirroring.
B.When a file is created in DGROUP1, the MY_TEMP template becomes
the default template.
厚积薄发、精益求精
42 / 233
C.When a file is created in DGROUP1 with the template, it would have
twoway mirroring and file striping.
D.When a file is created in DGROUP1 with the template, it would have
threeway mirroring and file striping.
E.When a file is created in DGROUP1 with the template, it would have
twoway mirroring but no file striping.
Answer: C
20. 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
Answer: A
厚积薄发、精益求精
43 / 233
第五章数据字典管理
(关键字：DBA_、ALL_、USER_、V$ )
1. Which statement regarding the contents of the V$PARAMETER view
is true?
A) displays only the list of default values
B) displays only the list of all basic parameters
C) displays the currently in effect parameter values
D) displays only the list of all advanced parameters
E) displays the list of all the parameter files of a database
F) displays the current contents of the server parameter file
Answer: C
2. 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
厚积薄发、精益求精
44 / 233
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
Answer: B
厚积薄发、精益求精
45 / 233
第六章控制文件管理
(关键字：CONTROLFILE、V$CONTROLFILE)
1. You want to protect your database from single point of failure by
multiplexing the control file. Your database is started using SPFILE.
You have to perform the following tasks:
1. Shut down the database instance.
2. Copy the control file to new location.
3. Configure the CONTROL_FILES parameter.
4. Restart the instance.
Select the correct order in which these tasks need to be performed.
A. 1,2,3,4
B. 3,2,1,4
C. 3,1,2,4
D. 2,3,1,4
Answer: C
2. Your database is having two control files, three redo log file groups
with two members in each group. Failure of which file would cause an
instance to shut down?
A. any control file
B. any archive log file
厚积薄发、精益求精
46 / 233
C. one of the redo log members
D. loss of the initialization parameter file
E. any data file belonging to the default permanent tablespace
Answer: A
3. 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 script to recreate 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 recreate the
control file.
E. The trace file is an image copy of the control file created during the
backup of the control file.
Answer: B
4. Your database is in NOARCHIVELOG mode. After which two
operations you should take the backup
of control file ? (Choose two.)
A. adding a new user to the database
B. dropping a user from the database
C. dropping a table from the database
厚积薄发、精益求精
47 / 233
D. dropping a data file from a tablespace
E. adding a new tablespace to the database
Answer: DE
5. You executed the following command to back up your control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Which initialization parameter is used to specify the location of the
trace file?
A. USER_DUMP_DEST
B. CORE_DUMP_DEST
C. TRACE_DUMP_DEST
D. BACKUP_DUMP_DEST
E. BACKGROUND_DUMP_DEST
Answer: A
6. Your database instance is started using the server parameter file
(SPFILE). Control files are multiplexed and stored on different disks.
Because of a disk failure, you lost one of these control files. You
replaced the damaged disk.
What is the correct sequence of steps that you would follow to recover
the control file?
1. Shut down the instance, if not already done.
2. Copy one of the remaining control files to a new location.
厚积薄发、精益求精
48 / 233
3. Change the value of the CONTROL_FILES initialization parameter to
correspond to the new location of the control files.
4. Start up the database instance to the NOMOUNT stage.
5. Recover the database to the point of failure of the control file.
6. Open the database.
A. 5,2,3,4
B. 1,2,4,3,5
C. 2,4,3,4,5
D. 4,5,6,2,3
Answer: B
7. Your database is configured in NOARCHIVELOG mode. All the
control files have been lost due to a
hard disk failure but the data files are not lost. You have the closed
whole database backup available to
you.
Which two statements are true in this scenario? (Choose two.)
A. The instance aborts.
B. The database cannot be recovered.
C. The database can be recovered by restoring the control files from the
backup.
D. The database remains opened and you have to shut it down with the
厚积薄发、精益求精
49 / 233
ABORT option.
E. The database can be restored till the point of the last closed whole
database backup.
Answer: AE
厚积薄发、精益求精
50 / 233
第七章管理REDO 日志
（关键字：ARCHIVE、NOARCHIVE、REDO LOG、LOG BUFFER）
1. Which two statements are true regarding the database in
ARCHIVELOG mode? (Choose two.)
A. You have to shut down the database to perform the backups.
B. Archiving information is written to the data files and redo log files.
C. You can perform complete database backups without closing the
database.
D. Online redo log files have to be multiplexed before putting the
database in ARCHIVELOG mode.
E. All the previous database backups become invalid after you configure
the database to ARCHIVELOG mode.
Answer: CE
2. Which two statements regarding archive log destinations are true?
(Choose two.)
A. A maximum of 10 destinations can be specified.
B. The archive logs must be written to all the destinations.
C. The archive log files can be written only to local destinations.
D. The archiving information can be traced to the alert log file whenever
the archiving to a destination is successful.
厚积薄发、精益求精
51 / 233
E. The number of archiving destinations must be equal to the number
of archive processes (ARCn).
Answer: AD
3. Your test database is running in NOARCHIVELOG mode. What are
the implications of this?
A. You can perform open database backups.
B. You can perform closed database backups.
C. You cannot perform schemalevel logical backups.
D. You can perform the backup of only the SYSTEM tablespace when
the database is open.
Answer: B
4. You are working on a database, where you have three redo log groups.
When the database is down for a backup, an operating system (OS) user
deletes all the redo log files in a group. What will happen when you open
your database?
A.The instance starts and the database opens because two redo log
groups still exist.
B.Before the instance starts, you receive an error about the missing
redo log files of a group.
C.The instance starts and the database opens with an error message
written to the alert log file.
厚积薄发、精益求精
52 / 233
D.The instance starts and the database opens, but every attempt to
write to redo log files fails with an error.
E.The instance starts, the database is mounted, but efforts to open the
database fail, and an error about the missing redo log group is
displayed.
Answer: E
5. Because of hardware failure, you decided to drop a redo log member
from the database.Which condition should be met to drop a redo log
file?
A.The redo log file should belong to an active group.
B.The redo log file should belong to an inactive group.
C.The redo log file should belong to the current group.
D.The redo log file can be dropped only if all the transactions are
stopped.
E.Before a redo log file is dropped, it should be deleted from the
operating system (OS).
Answer: B
6. A media failure has occurred. This has resulted in all the members of
the next log group being rendered inaccessible to the log writer process
(LGWR) at a log switch. How does this failure affect the operational
database?
厚积薄发、精益求精
53 / 233
A.The database recreates
the missing redo log files automatically.
B.The database continues to function normally with the existing files.
C.The database allows only queries; no other statements are allowed.
D.The database returns an error and the database instance shuts
down.
E.The user sessions that generate redo logs are terminated
automatically.
Answer: D
7. A redo log file is corrupted while the database is open; as a
consequence, database operations are stopped because archiving
cannot continue.What would you do to solve the problem?
A.clear the redo log group
B.perform redo log file import
C.perform an incomplete recovery
D.perform a redo log recovery using Recovery Manager (RMAN)
E.shut down the database and open the database in the
NOARCHIVELOG mode
Answer: A
8. In your database, online redo log files are multiplexed and one of the
members in a group is lost due to media failure.
厚积薄发、精益求精
54 / 233
How would you recover the lost redo log member?
A.import the database from the last export
B.restore all the members in the group from the last backup
C.drop the lost member from the database, and then add a new member
to the group
D.restore all the database files from the backup, and then perform a
complete recovery
E.restore all the database files from the backup, and then perform an
incomplete recovery
Answer: C
9. You are using Oracle Database 10g. The LOG_ARCHIVE_FORMAT
parameter is set to 'LOG%t_%s_%r.dbf'.
Why is %r used in the file name format?
A.to uniquely identify the archived log files with the restore operation
B.to uniquely identify the archived log files with the redo log group
number
C.to uniquely identify the archived log files for each incarnation of the
database
D.to uniquely identify the archived log files with the number of recovery
operations performed
Answer: C
厚积薄发、精益求精
55 / 233
厚积薄发、精益求精
56 / 233
第八章管理表空、存储架构管理
(关键字：TABLESPACE 、DATAFILE、OFFLINE、ONLINE、READ ONLY)
1. Which two statements regarding the LOGGING clause of the CREATE
TABLESPACE. .. statement are correct? (Choose two.)
A. This clause is not valid for a temporary or undo tablespace.
B. If the tablespace is in the NOLOGGING mode, no operation on the
tablespace will generate redo.
C. The tablespace will be in the NOLOGGING mode by default, if not
specified while creating a tablespace.
D. The tablespacelevel logging attribute can be overridden by logging
specifications at the table, index,materialized view, materialized view
log, and partition levels.
Answer: AD
2. 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 tablespaces 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 tablespaces of different block sizes in a
database.
E. Each data block in the database always corresponds to one OS block.
厚积薄发、精益求精
57 / 233
Answer: ACD
3. In your database, the Log Writer (LGWR) process is unable to write to
a member of a current redo log group due to read/write failure. Which
two effects would you see in your database? (Choose two.)
A. The database instance aborts.
B. The database operation temporarily halts till the member becomes
available.
C. Writing proceeds as normal. LGWR writes to the available members
of a group and ignores the unavailable members.
D. The member would be marked as STALE and an error message
would be written to the alert log file and LGWR trace file.
E. The status of the group changes to INACTIVE and an error message
would be written to the alert log file and LGWR trace file.
Answer: CD
4. Redo log files are not multiplexed in your database. Redo log blocks
are corrupted in group 2, and archiving has stopped. All the redo logs
are filled and database activity is halted. Database writer has written
everything to disk. Which command would you execute to proceed
further?
A. RECOVER LOGFILE BLOCK GROUP 2;
B. ALTER DATABASE DROP LOGFILE GROUP 2;
厚积薄发、精益求精
58 / 233
C. ALTER DATABASE CLEAR LOGFILE GROUP 2;
D. ALTER DATABASE RECOVER LOGFILE GROUP 2;
E. ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
Answer: E
5. Which two statements about bigfile tablespaces are true? (Choose
two.)
A. The bigfile tablespace have only one data file.
B. The segment space management is automatic.
C. The extent management is dictionary managed.
D. The database can have only one bigfile tablespace.
E. The bigfile tablespace can be converted to a smallfile tablespace.
Answer: AB
6. You are working on a database created with the Oracle Database 10g
software in which the initialization parameter COMPATIBLE is set to
10.0.0. The hr_tbs tablespace in the database is created as follows:
CREATE TABLESPACE hr_tbs DATAFILE '/oracle/oradata/hr_tbs.dbf'
SIZE 50M;
View the Exhibit to see the properties of the database.
厚积薄发、精益求精
59 / 233
Which statement is correct in this scenario?
A. hr_tbs is a bigfile tablespace.
B. hr_tbs is a dictionarymanaged tablespace.
C. All the tablespaces in the database will be locally managed by
default.
D. All the tablespaces in the database must be of the smallfile
tablespace type.
E. The tablespace type bigfile or smallfile must be mentioned in the
command.
Answer: C
7. You specified extent management as local for a tablespace. How will
it affect space management in the tablespace?
A. All the extents will be of the same size.
B. Bitmap will be used to record free and allocated extents.
C. Free extents will be managed by the data dictionary tables.
厚积薄发、精益求精
60 / 233
D. The tablespace will be system managed and the users cannot specify
the extent size.
Answer: B
8. You specified segment space management as automatic for a
tablespace. What effect would this have on space management?
A. Extents would be managed by the freelists.
B. The segment would be managed by bitmaps.
C. The segment would be managed by the freelists.
D. Free space would be managed by the data dictionary.
Answer: B
9. You want to create a tablespace with the following specifications:
1. The tablespace extends automatically.
2. Used and free extents should be managed by bitmaps.
3. Default PCTUSED attribute is set to 60.
4. All the extents would be of size 1 MB.
Which three options would you choose to create the tablespace?
(Choose three.)
A. tablespace with AUTOEXTEND enabled
B. tablespace with dictionarymanaged extents
C. tablespace with a uniform extent allocation of 1 MB
D. tablespace with segment space management as manual
厚积薄发、精益求精
61 / 233
E. tablespace with segment space management as automatic
Answer: ACD
10. You are creating a locally managed tablespace to meet the following
requirements:All the extents should be of the same size.
The data should be spread across two data files.
A bitmap should be used to record the free space within the allocated
extents.
Which three options would you choose? (Choose three.)
A. set PCTFREE and PCTUSED to 50
B. specify extent allocation as Uniform
C. specify extent allocation as Automatic
D. create the tablespace as bigfile tablespace
E. create the tablespace as smallfile tablespace
F. set segment space management to Automatic
G. use the RESIZE clause while creating the tablespace
Answer: BEF
11. View the Exhibit.
厚积薄发、精益求精
62 / 233
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 two tablespaces can be made read only?
(Choose two.)
A. TEMP
B. PROD
C. USERS
D. SYSAUX
E. SYSTEM
F. UNDOTBS1
Answer: BC
12. Your database is running in the ARCHIVELOG mode. You placed a
tablespace, tbs_1, offline with the immediate option. Which statement
is correct in this scenario?
厚积薄发、精益求精
63 / 233
A. The operation would fail if tbs_1 were a readonly tablespace.
B. The operation would fail if tbs_1 were a read/write tablespace.
C. The operation would fail if tbs_1 were the default tablespace for the
database.
D. If the above operation were successful, media recovery would be
required to bring the tablespace online.
E. If the above operation were successful, instance recovery would be
required to bring the tablespace online.
Answer: D
13. 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.)
厚积薄发、精益求精
64 / 233
A. TEMP
B. PROD
C. USERS
D. SYSAUX
E. SYSTEM
F. UNDOTBS1
Answer: BCD
14. Last night, a media failure caused the loss of a temporary file that
belongs to your database. Which two things may happen this morning
when you try to open your database? (Choose two.)
A.The database opens normally.
B.The database prompts for a database recovery.
C.The database writer writes to a trace file, indicating that the
temporary file is not found.
D.The database gets mounted and throws an error indicating that the
temporary file requires recovery.
Answer: AC
15. A user executes a query on the EMP table, which contains
thousands of rows, to get details about employees in one of the
departments. The user receives the following error:
SQL> SELECT ename FROM emp WHERE deptno=10 ORDER BY sal;
厚积薄发、精益求精
65 / 233
SELECT ename FROM emp WHERE deptno=10 ORDER BY sal
ERROR at line 1:
ORA01157:cannot identify/lock data file 201 see DBWR trace file
ORA01110:data file 201: '/u01/app/oracle/oradata/orcl/temp01.dbf'
What is a possible reason?
A.The tempfile belonging to the default temporary tablespace is
missing.
B.The database is opened in restricted mode, so queries are not
allowed.
C.The default temporary tablespace required to execute the query is
offline.
D.The default temporary tablespace required to execute the query is
dropped.
E.The default temporary tablespace required to execute the query is
readonly.
Answer: A
16. You lost a data file that belongs to an index tablespace in your
database, which operates in ARCHIVELOG mode. Loss of the data file
resulted in increased response time on your queries.
Which two options would you use to solve this problem? (Choose two.)
A.restore the lost data file from the backup, and then flash back the
厚积薄发、精益求精
66 / 233
database
B.restore the data file pertaining to index tablespace, and then recover
the tablespace
C.restore all the data files, and then perform an incomplete recovery to
get the tablespace back
D.restore all the data files, and then perform an incomplete recovery
using the backup control file
E.drop and recreate the index tablespace, and then recreate
all of the indexes in that tablespace
Answer: BE
17. You lost the index tablespace in your database. You are not able to
use tablespace pointintime recovery on the index tablespace. What
could be the reason for this?
A.The index tablespace contains bitmap indexes.
B.The index tablespace contains more than one data file.
C.The index tablespace supports only complete recovery.
D.The index tablespace is not a dictionarymanaged tablespace.
E.There is a dependency relationship between a table and its indexes.
Answer: E
18. You lost the index tablespace in your database. You decided to
recreate the index tablespace and the indexes in the tablespace.
厚积薄发、精益求精
67 / 233
What methods can you use to recreate the indexes? (Choose all that
apply.)
A.Data Pump
B.SQL scripts
C.SQL*Loader
D.Flashback database
E.Recovery Manager (RMAN) script
Answer: AB
19. You lost the PRODSTD tablespace, which was readonly.The
tablespace was readonly when thelast backup was taken. How would
you recover the tablespace?
A.restore the tablespace from the backup
B.restore the tablespace from the backup, and then perform an
incomplete recovery
C.restore the tablespace from the backup, and then apply all the redo
information
D.restore all the data files in the database, and then perform an
incomplete recovery
Answer: A
20. You lost the PRODSTD tablespace, which was read/write. The
tablespace was readonly when thelast backup was performed. How
厚积薄发、精益求精
68 / 233
would you recover the tablespace?
A.restore the tablespace from the backup; there is no need to apply the
redo information
B.restore the full database to recover the data up to the point when you
performed the backup
C.restore the tablespace from the backup, and then perform a recovery
using the backup control file
D.restore the tablespace from the backup, and then recover the
tablespace; all the redo information from the point when the
tablespace was made read/write is applied
Answer: D
21. One of the tablespaces is readonly in your database. The loss of all
control files forced you to recreate the control file.
Which operation do you need to perform after recreating
the control file and opening the database?
A.drop and recreate the readonly tablespaces
B.rename the readonly data files to their correct file names
C.change the tablespace status from read/write to readonly
D.recreate the readonly tablespace because it is automatically removed
Answer: B
22. You have a readonly tablespace on readonly media. You want to
厚积薄发、精益求精
69 / 233
perform a media recovery on the existing data files, but using a backup
control file. The backup control file indicates that the status of the
Readonly tablespace was read/write when the control file was backed
up.What should you consider?
A.drop the readonly tablespace and recreate the tablespace after
recovery
B.take data files from the readonly tablespace offline before performing
a recovery
C.perform a recovery; status of the tablespace in control file will be
changed automatically
D.recovery using backup control file is not possible, so restore all the
files from the last full database backup, and then open the database
Answer: B
23. The loss of the control file forced you to recreate the control file.
After recreating it and opening the database, you find that some of the
data files are named as MISSINGnnnnn, where nnnnn is a fivedigit
number starting with 0.
What could be the possible reason?
A.These are the data files that are corrupted.
B.There is no SYSAUX tablespace in your database
C.There is a readonly tablespace in your database.
厚积薄发、精益求精
70 / 233
D.These are the data files that cannot be recovered.
Answer: C
厚积薄发、精益求精
71 / 233
第九章管理UNDO 表空间
（关键字：UNDO TABLESPACE、ROLL BACK、UNDO SEGMENT）
1. You require the Oracle server to manage the undo segments and
space among various active sessions automatically. You created an
undo tablespace, UNDO_TBS1, in your database.
Which two additional steps would you perform to achieve this? (Choose
two.)
A. Set the UNDO_RETENTION parameter to 900 or more.
B. Create an initial undo segment in the undo tablespace.
C. Enable the retention guarantee for the undo tablespace.
D. Set the UNDO_TABLESPACE parameter to UNDO_TBS1.
E. Set the UNDO_MANAGEMENT initialization parameter to AUTO.
Answer: DE
2. A user receives the following error while executing a query:
ORA01555:snapshot too old
Which two options can be the solutions to avoid such errors in future?
(Choose two.)
A. increase the size of redo log files
B. increase the size of the undo tablespace
C. increase the size of the Database Buffer Cache
厚积薄发、精益求精
72 / 233
D. increase the size of the default temporary tablespace
E. enable the retention guarantee for the undo tablespace
Answer: BE
3. Your database is running under automatic undo management and
the UNDO_RETENTION
parameter is set to 900 sec. You executed the following command to
enable retention guarantee:
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
What effect would this statement have on the database?
A. The extents in undo tablespace retain data until the next full
database backup.
B. The extents containing committed data in the undo tablespace are
never overwritten.
C. The extents which no longer contain uncommitted data in the undo
tablespace are not overwritten for at least15 minutes.
D. The extents containing committed data in the undo tablespace are
not overwritten until the instance is shut down.
Answer: C
4. The UNDO_RETENTION parameter in your database is set to 1000
and undo retention is not
guaranteed. Which statement regarding retention of undo data is
厚积薄发、精益求精
73 / 233
correct?
A. Undo data becomes obsolete after 1,000 seconds.
B. Undo data gets refreshed after every 1,000 seconds.
C. Undo data will be stored permanently after 1,000 seconds.
D. Committed undo data would be retained for 1,000 seconds if free
undo space is available.
E. Undo data will be retained in the UNDO tablespace for 1,000 seconds,
then it gets moved to the TEMPORARY tablespace to provide read
consistency.
Answer: D
5. Which two operations require undo data? (Choose two.)
A. committing a transaction
B. rolling back a transaction
C. recovering from failed transactions
D. recording a transaction to redo log files
E. rolling forward during instance recovery
Answer: BC
6. The user SCOTT executes the following command successfully to
increase the salary values in one of his sessions:
SQL> UPDATE emp SET sal=sal*1.15 WHERE deptno=20;
Before SCOTT ends the transaction, user HR who has the privileges on
厚积薄发、精益求精
74 / 233
EMP table executes a query to fetch the salary details but finds the old
salary values instead of the increased values.
Why does HR still see the old data?
A. because of redo data from redo log file
B. because of data from database buffer cache
C. because of data from a temporary tablespace
D. because of undo data from the undo tablespace
Answer: D
7. View the Exhibit.
Which statement causes more undo generation?
A. SELECT * FROM emp;
厚积薄发、精益求精
75 / 233
B. DELETE FROM emp WHERE empno=7934;
C. UPDATE emp SET comm=400 WHERE empno=7844;
D. INSERT INTO emp
VALUES(7999,'JHON','CLERK',7782,'10MAY83',1500,NULL,10);
Answer: B
8. Which three pieces of information are considered while deciding the
size of the undo tablespace in your database? (Choose three.)
A. the size of an undo block
B. the size of the redo log files
C. undo blocks generated per second
D. the size of the database buffer cache
E. the value of the UNDO_RETENTION parameter
Answer: ACE
厚积薄发、精益求精
76 / 233
第十章管理表、视图
（关键字：TABLE、VIEW、SEGMENT、EXTENT）
1. Examine the following commands executed in your database:
SQL> ALTER SESSION RECYCLEBIN=ON;
Session altered
SQL> CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM
hr.employees;
Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp;
Table dropped.
What happens in this scenario?
A. The table is moved to the SYSAUX tablespace.
B. The table is moved to the SYSTEM tablespace.
C. The table is removed from the database permanently.
D. The table is renamed and remains in the TBSFD tablespace.
Answer: D
2. Which three statements are correct about temporary tables? (Choose
three.)
A. Indexes and views can be created on temporary tables.
厚积薄发、精益求精
77 / 233
B. Both the data and the structure of temporary tables can be exported.
C. Temporary tables are always created in a user's temporary
tablespace.
D. The data inserted into a temporary table in a session is available to
other sessions.
E. Data manipulation language (DML) locks are never acquired on the
data of temporary tables.
Answer: ACE
3. You want to refer the employee personal information stored in
operating system(OS) files in EMPLOYEE table. You plan to add a new
column to EMPLOYEE table to achieve this.
Which data type would you use for the new column?
A. BLOB
B. CLOB
C. BFILE
D. LONG RAW
Answer: C
4. View the Exhibit.
厚积薄发、精益求精
78 / 233
The DBA creates a new user as follows:
SQL> CREATE USER manfus IDENTIFIED BY manfus TEMPORARY
TABLESPACE temp;
User manfus creates a table as follows:
SQL> CREATE TABLE material(id NUMBER(3));
厚积薄发、精益求精
79 / 233
In which tablespace will the material table be created?
A. TEMP
B. PROD
C. USERS
D. SYSAUX
E. SYSTEM
Answer: C
5. Which three descriptions are correct about the effects of the
TRUNCATE command on a table? (Choose three.)
A. The corresponding indexes for the table are also truncated.
B. Delete triggers on the table are fired during the execution of the
TRUNCATE command.
C. Very little or no undo data is generated during the execution of the
TRUNCATE command.
D. The child table is truncated when the TRUNCATE command is
applied on the parent table.
E. The highwater mark (HWM) is set to point to the first useable data
block in the table segment.
Answer: ACE
6. Which two statements are true about simple views? (Choose two.)
A. Views can be created as read only.
厚积薄发、精益求精
80 / 233
B. Views are data segments like tables.
C. Views can be created on the basis of more than one table.
D. Data manipulation language (DML) operations cannot be performed
on views.
Answer: AC
7. The HR user creates a view with this command:
SQL> CREATE VIEW emp_v AS SELECT * FROM scott.emp;
Now HR wants to grant the SELECT privilege on the EMP_V view to the
JIM user. Which statement is true
in this scenario?
A. HR can grant the privilege to JIM but without GRANT OPTION.
B. HR can grant the privilege to JIM because HR is the owner of the
view.
C. SCOTT has to grant the SELECT privilege on the EMP table to JIM
before this operation.
D. HR needs the SELECT privilege on the EMP table with GRANT
OPTION from SCOTT for this operation.
Answer: D
8. The employee IDs of employees who have made sales in the company
are transferred from the
EMPLOYEES table to the BONUS table with a default bonus value.
厚积薄发、精益求精
81 / 233
Later, the human resources
department decides to give bonuses to employees as per the following
conditions:
1. Employees with a salary of $8,000 or less should receive a bonus.
2. Employees who have not made sales get a bonus of 1% of their salary.
3. Employees who already made sales get an increase in their bonus
equal to 1% of their salary.
What would you do to implement these changes in one step?
A. Use SQL*Loader utility.
B. Use the MERGE statement.
C. Use a multitable insert operation.
D. Use a correlated UPDATE statement.
Answer: B
9. 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?
厚积薄发、精益求精
82 / 233
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
Answer: D
10. Which two statements are true regarding a PL/SQL package body?
(Choose two.)
A. It cannot be created without a package specification.
B. It cannot invoke subprograms defined in other packages.
C. It can contain only the subprograms defined in the package
specification.
D. It can be changed and recompiled without making the package
specification invalid.
Answer: AD
11. 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
厚积薄发、精益求精
83 / 233
E. database level
Answer: AC
12. User SCOTT executes the following command on the EMP table but
has not issued COMMIT,ROLLBACK, or any data definition language
(DDL) command:
SQL> SELECT job FROM emp
2 WHERE job='CLERK' FOR UPDATE OF empno;
SCOTT has opened another session to work with the database. Which
three operations would wait when issued in SCOTT's second session?
(Choose three.)
A. LOCK TABLE emp IN SHARE MODE;
B. LOCK TABLE emp IN EXCLUSIVE MODE;
C. DELETE FROM emp WHERE job='MANAGER';
D. INSERT INTO emp(empno,ename) VALUES (1289,'Dick');
E. SELECT job FROM emp WHERE job='CLERK' FOR UPDATE OF
empno;
Answer: ABE
13. User Scott has updated the salary of one of the employees in the
EMPLOYEES table and has not committed the transaction. What are
the two types of locks that this scenario would lead to? (Choose two.)
A. null lock on the row being updated
厚积薄发、精益求精
84 / 233
B. null lock on the table containing the row
C. ROW SHARE lock for the row being updated
D. ROW EXCLUSIVE lock for the row being updated
E. shared rowexclusive
lock for the row being updated
F. a shareable table lock for the table containing the row
G. exclusive tablelevel lock for the table containing the row
Answer: DF
14. The session of user SCOTT receives the following error after
executing an UPDATE command on the EMP table:
ERROR at line 1:ORA00060:deadlock detected while waiting for
resource On investigation, you find that a session opened by user JIM
has a transaction that caused the deadlock.
Which two statements are true regarding the session of SCOTT in this
scenario? (Choose two.)
A. It is terminated after receiving the error and JIM can continue with
his transaction.
B. The transaction in it is terminated after receiving the error and JIM
can continue with his transaction.
C. SCOTT should perform a COMMIT or ROLLBACK to allow JIM to
continue with his transaction.
厚积薄发、精益求精
85 / 233
D. SCOTT has to reexecute the last command in the transaction after he
commits the transaction.
Answer: CD
15. User A executes the following command to update the TRANS table:
SQL> UPDATE B. trans SET tr_amt=tr_amt+500 WHERE c_code='C005'
;
Before user A issues a COMMIT or ROLLBACK command, user B
executes the following command on the TRANS table:
SQl> ALTER TABLE trans MODIFY (tr_type VARCHAR2(3));
What would happen in this scenario?
A. The transaction for user A is rolled back.
B. The ALTER TABLE command modifies the column successfully.
C. The ALTER TABLE command fails due to the resource being busy.
D. The ALTER TABLE command waits until user A ends the
transaction.
Answer: C
16. View the Exhibit.
You have more than one table in the recycle bin having the same
original name, DEPT2. You do not have any table with the name DEPT2
in your schema. You executed the following command:
PURGE TABLE dept2;
Which statement is correct in this scenario?
厚积薄发、精益求精
86 / 233
A.All the tables having the same original name as DEPT2 will be purged
from the recycle bin.
B.The table with dropscn = 1928151 (oldest dropscn) will be purged
from the recycle bin.
C.The table with dropscn = 1937123 (most recent dropscn) will be
purged from the recycle bin.
D.None of the tables will be purged because there are multiple entries
with the same original name in the recycle bin.
Answer: B
厚积薄发、精益求精
87 / 233
第十一章管理索引
(关键字：INDEX、SEGMENT、EXTENT、IOT)
1. The ST_INFO master table has millions of rows that are updated very
rarely. It has a STATE_CODE column that contains the value for 25
states. The table is frequently queried on the STATE_CODE column.
Which type of index would you suggest to improve the query
performance?
A. Btree
B. bitmap
C. reverse key
D. function based
Answer: B
2. Which two statements are true about a bitmap index? (Choose two.)
A. It is recommended for the columns that have unique values.
B. It can be converted to a Btree
index by using the ALTER INDEX command.
C. It has a bitmap segment for each distinct value in the key column,
containing a string of bits in which
each bit represents the presence or absence of a key column value.
D. Updating the key column locks the whole bitmap segment that
厚积薄发、精益求精
88 / 233
contains the bit for the key value to be updated.
Answer: CD
3. The SCOTT user has an index on the ITEM_DESC column of the
ITEM table. As part of the yearending task, SCOTT updates the
ITEM_DESC column for most of the rows in the ITEM table. Howdoes
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.
Answer: D
4. As a result of performance analysis, you created an index on the
prod_name column of the prod_det table, which contains about ten
thousand rows. Later, you updated a product name in the table.
How does this change affect the index?
A. A leaf will be marked as invalid.
B. An update in a leaf row takes place.
C. The index will be updated automatically at commit.
D. A leaf row in the index will be deleted and inserted.
E. The index becomes invalid when you make any updates.
厚积薄发、精益求精
89 / 233
Answer: D
5. 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
Answer: C
6. 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
Answer: BD
7. You noticed that the index tablespace in your database requires a
recovery. However, instead of performing a media recovery, you decided
厚积薄发、精益求精
90 / 233
to recreate the indexes in a new tablespace.
Which two options would you use to reduce the time it takes to recreate
the indexes? (Choose two.)
A.ONLINE
B.REVERSE
C.PARALLEL
D.COMPRESS
E.NOLOGGING
F.COMPUTE STATISTICS
Answer: CE
8. What effect should the loss of an index tablespace have on an Oracle
instance that is running?
A.instance hangs
B.instance gets aborted
C.instance continues running
D.instance gets restarted automatically
Answer: C
厚积薄发、精益求精
91 / 233
第十二章管理约束
(关键字： PRIMARY KEY、FORIGEN KEY、CHECK、UNIQUE、CONSTRAINT)
1. Which two statements are true about the primary key constraint in a
table? (Choose two.)
A. It is not possible to disable the primary key constraint.
B. It is possible to have more than one primary key constraint in a
single table.
C. The primary key constraint can be referred by only one foreign key
constraint.
D. The primary key constraint can be imposed by combining more than
one column.
E. The nondeferrable primary key constraint creates an unique index
on the primary key column if it is not already indexed.
Answer: DE
2. A constraint in a table is defined with the INITIALLY IMMEDIATE
clause. You executed the ALTER
TABLE command with the ENABLE VALIDATE option to enable the
constraint that was disabled.
What are the two effects of this command? (Choose two.)
A. It fails if any existing row violates the constraint.
厚积薄发、精益求精
92 / 233
B. It does not validate the existing data in the table.
C. It enables the constraint to be enforced at the end of each
transaction.
D. It prevents insert, update, and delete operations on the table while
the constraint is in the process of being enabled.
Answer: AD
3. View the Exhibit.
Which statement regarding the dept and emp tables is true?
A. When you delete a row from the emp table, you would receive a
厚积薄发、精益求精
93 / 233
constraint violation error.
B. When you delete a row from the dept table, you would receive a
constraint violation error.
C. When you delete a row from the emp table, automatically the
corresponding rows are deleted from the dept table.
D. When you delete a row from the dept table, automatically the
corresponding rows are deleted from the emp table.
E. When you delete a row from the dept table, automatically the
corresponding rows are updated with null values in the emp table.
F. When you delete a row from the emp table, automatically the
corresponding rows are updated with null values in the dept table.
Answer: D
4. View the Exhibit and analyze the CREATE TABLE statements used to
create the ITEMS and ORDERS tables.
The ITEMS table has 50 rows and ORDERS table has 500 rows.
Because you decide to not deal with the item code 188 in future, you
execute the following command to remove the item:
厚积薄发、精益求精
94 / 233
SQL> DELETE FROM items WHERE item_code=188;
What is the effect of this command?
A. The command fails with integrity violation error.
B. The row containing the value 188 corresponding to the ITEM_CODE
column is deleted from the ITEMS table, and the foreign key constraint
is disabled in the ORDERS table.
C. The row containing the value 188 corresponding to the ITEM_CODE
column is deleted from the ITEMS table, and the dependent rows in the
ORDERS table are also deleted automatically.
D. The row containing the value 188 corresponding to the ITEM_CODE
column is deleted from the ITEMS table, and the dependent rows in the
ORDERS table are populated with NULL values in their ITEM_CODE
columns.
Answer: C
5. View the Exhibit to see the structure of the EMPLOYEES and
DEPARTMENTS tables.
厚积薄发、精益求精
95 / 233
Your organization plans to dissolve the department with department ID
30. You execute the following
command to delete rows from the DEPARTMENTS table:
SQL>delete from DEPARTMENTS
where DEPT_ID = 30;
The command fails and displays the following error:
ERROR at line 1:ORA02292:integrity constraint (HR.SYS_C005374)
violated child record found
Which two actions would you take to overcome this error? (Choose two.)
A. alter the foreign key constraint to include the cascade option
B. alter the foreign key constraint to include the on delete cascade
option
C. first, drop the EMPLOYEES table and then delete the rows from the
DEPARTMENTS table
厚积薄发、精益求精
96 / 233
D. first, drop the DEPARTMENTS table and then delete the rows from
the EMPLOYEES table
E. first, delete all of the rows from EMPLOYEES table and then delete
the rows from the DEPARTMENTS table for department id 30
F. first, delete rows from the EMPLOYEES table for department id 30
and then delete the rows from the DEPARTMENTS table for
department id 30
Answer: BF
厚积薄发、精益求精
97 / 233
第十三章管理安全和资源
(关键字：PASSWORD、RESOURCE)
1. A user complains about getting this error after issuing a certain SQL
statement:
ORA02393:exceeded call limit on CPU usage
Because of the error, the SQL statement gets aborted. What action
would you take to increase the CPU usage limit in the subsequent
sessions of the user?
A. Modify the resource limit in the profile used by the user.
B. Set the RESOURCE_LIMIT initialization parameter to FALSE.
C. Increase the value of the SESSION_CACHED_CURSORS
initialization parameter.
D. Increase the value of the SESSION_MAX_OPEN_FILES initialization
parameter.
Answer: A
2. A user complains that he gets the following error message repeatedly
after executing some SQL statements. The error message forces the
user to log off from and log on to the database to continue his work.
ORA02392:exceeded session limit on CPU usage, you are being logged
off
厚积薄发、精益求精
98 / 233
Which action would you take to increase the session limit on CPU
usage?
A. Modify the profile assigned to the user.
B. Modify the roles assigned to the users.
C. Modify the object privileges assigned to the user.
D. Modify the system privileges assigned to the users.
E. Modify the value for the RESOURCE_LIMIT parameter in the
parameter file.
Answer: A
3 Manually, you set the consumer group of all of the newly created
users to MYDB_GRP. You want the users to be able to change their
consumer groups as per the application requirement.
What was the first step that was needed in the process to achieve this
objective?
A.The user must have been granted the DBA role.
B.The user must have been granted the switch privilege as a part of a
role.
C.The user must have been granted the Resource Manager
administrator privilege.
D.The user must have been granted the switch privilege by using the
DBMS_RESOURCE_MANAGER_PRIVS package.
厚积薄发、精益求精
99 / 233
Answer: D
4. From the V$SESSION_LONGOPS view, you find that some of the
database users have longrunning 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
Answer: C
5. From the V$SESSION_LONGOPS view, you find that some of the
database users have longrunning 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.
厚积薄发、精益求精
100 / 233
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
Answer: C
6. 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
Answer: ABE
7. View the Exhibit and examine the Resource Manager settings for the
groups at different levels.Which two effects would be the result of this
厚积薄发、精益求精
101 / 233
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.
Answer: AB
8. 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
厚积薄发、精益求精
102 / 233
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
Answer: B
9. 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.
Answer: E
10. You are using Resource Manager to manage system resources.
While creating a new database user,you observe that the user is a
member of DEFAULT_CONSUMER_GROUP by default. What would be
the implications of this?
A.The Resource Manager plan does not affect the user.
B.There must be a plan directive in any of the plans of the group to
厚积薄发、精益求精
103 / 233
control the resources.
C.The members of the group DEFAULT_CONSUMER_GROUP always
have resource restrictions.
D.The members of DEFAULT_CONSUMER_GROUP are restrained from
accessing the system tables.
E.The members of this group can change their group without the
database administrator's intervention.
Answer: B
11. You execute the following command to create two consumer groups,
FIN_GROUP1 and
PAY_GROUP1, for a plan, PROD_PLAN:
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN
(SIMPLE_PLAN => 'prod_plan',
CONSUMER_GROUP1 => 'fin_group1',
GROUP1_CPU => 80,
CONSUMER_GROUP2 => 'pay_group1',
GROUP2_CPU => 20);
END;
Which three statements are true? (Choose three.)
A.SYS_GROUP gets 100% CPU resources at level 1.
厚积薄发、精益求精
104 / 233
B.FIN_GROUP1 gets 80% CPU resources at level 1.
C.FIN_GROUP1 gets 80% CPU resources at level 2.
D.PAY_GROUP1 gets 20% CPU resources at level 1.
E.PAY_GROUP1 gets 20% CPU resources at level 2.
F.OTHER_GROUP gets 100% CPU resources at level 1.
G.OTHER_GROUP gets 100% CPU resources at level 2.
Answer: ACE
12. You want to create a consumer group, GROUP1, and you execute
the following command in the commandline interface:
SQL> EXEC
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
(CONSUMER_GROUP =>'group1', COMMENT => 'New Group');
This command errors out displaying the following message:
ERROR at line 1:
ORA29371:pending area is not active
ORA06512:at "SYS.DBMS_RMIN", line 115
ORA06512:at "SYS.DBMS_RESOURCE_MANAGER", line 108
ORA06512:at line 1
What action would you take to overcome this error?
A.create the Pending Area before creating the consumer group
B.activate the Pending Area before creating the consumer group
厚积薄发、精益求精
105 / 233
C.increase the size of the large pool to accommodate the Pending Area
D.increase the size of the shared pool to accommodate the Pending Area
E.increase the size of the database buffer cache to accommodate the
Pending Area
Answer: A
13. You want to create a consumer group, GROUP1, and you execute
the following command in the commandline interface:
SQL> EXEC
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
(CONSUMER_GROUP =>'group1', COMMENT => 'New Group');
This command errors out displaying the following message:
ERROR at line 1:
ORA01031:insufficient privileges
ORA06512:at "SYS.DBMS_RMIN", line 115
ORA06512: at SYS.DBMS_RESOURCE_MANAGER", line 108
ORA06512:at line 1
What action would you take to overcome this error?
A.grant SYSDBA to the user
B.grant SYSOPER to the user
C.grant the RESOURCE role to the user
D.use the GRANT command to grant the
厚积薄发、精益求精
106 / 233
ADMINISTER_RESOURCE_MANAGER privilege to the user
E.grant the ADMINISTER_RESOURCE_MANAGER privilege to the user
by using the
DBMS_RESOURCE_MANAGER_PRIVS package
Answer: E
14. Which statement is true while creating subplans using Resource
Manager?
A.The subplan must belong to the consumer group, SYS_GROUP.
B.The total CPU usage at any given level must be less than 100%.
C.The subplans can be used to restrict the degree of parallelism for a
user.
D.You can use a resource plan instead of a consumer group to
implement a priority ranking within the plan.
Answer: D
15. You have created a resource plan, PROD_DB_PLAN, that uses the
RATIO policy. The following consumer groups and their respective plan
directives have been assigned to this resource plan:
Consumer group HIGH_CG with CPU_P1 as 10.
Consumer group MEDIUM_CG with CPU_P1 as 5.
Consumer group LOW_CG with CPU_P1 as 2.
Consumer group OTHER_GROUPS with CPU_P1 as 1.
厚积薄发、精益求精
107 / 233
Which statement is correct in this scenario?
A.The plan PROD_DB_PLAN can have subplans.
B.The members of the HIGH_CG consumer group would get 10% of the
CPU.
C.The CPU allocation for the consumer groups would be based on the
ratio defined for the CPU_P1.
D.The CPU allocation would be equal between the HIGH_CG and
MEDIUM_CG consumer groups if there are no sessions existing for the
LOW_CG and OTHER_GROUPS consumer groups.
Answer: C
16. You have created a resource plan, PROD_DB_PLAN, with the
following consumer groups with the respective plan directives:
Consumer group SALES with CPU_P1 as 60 and
DEGREE_OF_PARALLELISM as 4.
Consumer group MARKET with CPU_P1 as 20.
Consumer group DEV with CPU_P1 as 20.
Consumer group OTHERS with CPU_P1 as 0 and CPU_P2 as 100.
Which two statements are correct in this scenario? (Choose two.)
A.The maximum degree of parallelism for the members of the consumer
groups is 4.
B.The CPU allocation will always be equal for the consumer groups
厚积薄发、精益求精
108 / 233
MARKET and DEV.
C.The OTHERS consumer group would get 100% of the CPU if there are
unused level 1 CPU resources.
D.The SALES and MARKET consumer groups would get 100% of the
CPU if there are unused level 2 CPU resources.
E.The maximum degree of parallelism for the members of the consumer
group SALES is 4 with no restriction for the remaining groups.
Answer: CE
17. In your production database, you have multiple resource plan
directives that refer to the consumer group DB_GRP. Which two
statements are correct? (Choose two.)
A.The parallel degree limit for DB_GRP will be the minimum of all the
incoming values.
B.The parallel degree limit for DB_GRP will be the maximum of all the
incoming values.
C.The maximum estimated execution time for DB_GRP would be the
most nonrestrictive of all incoming values.
D.If a session switched from the DB_GRP consumer group to another
consumer group because it exceeded the prescribed switch time, then
that session would not execute if the active session pool for the new
consumer group was full.
厚积薄发、精益求精
109 / 233
E.If a session switched from the DB_GRP consumer group to another
consumer group because it exceeded the prescribed switch time, then
that session would execute even if the active session pool for the new
consumer group was full.
Answer: AE
18. Identify four uses of the Oracle Scheduler. (Choose four.)
A.enables you to execute jobs in a clustered environment
B.enables you to schedule the execution of jobs based on time
C.enables you to automate the management of the jobs that are created
using the DBMS_JOB package
D.enables Oracle database to do patches according to a DBAdefined
schedule
E.enables you to create a job that makes use of saved programs and
schedules
F.enables you to periodically execute operating systemscript files
located on the same server as the database
G.enables Oracle database to schedule which processes execute on
which processors in a multiple CPU environment
Answer: ABEF
19. Very soon you will be performing maintenance operations. You
would have several jobs to run as soon as the maintenance period starts.
厚积薄发、精益求精
110 / 233
Which method would you use to create jobs without specifying the time
period when the job should be run?
A.use Oracle Scheduler
B.use DBMS_JOB package
C.use DBMS_IJOB package
D.use Oracle Data Pump Export and Import
Answer: A
20. Your business has departmental reports that are generated every
day. Each department must use the same set of queries, but accesses a
different subset of data in the tables, depending on which department
generates the report. The format of the reports is being developed;
currently, the format changes daily.
How would you configure the database to ensure that each department
generates its report (based on its target data) using the most recent
report format every day?
A.by having each user run the report generation procedure at the
scheduled time, supplying the necessary input variables
B.by creating a program using DBMS_JOB that accepts one or more
variables, and creating a job that calls this program using DBMS_JOB
C.by having each user schedule a job using DBMS_JOB that accepts
one or more input variables and calls a procedure that generates the
厚积薄发、精益求精
111 / 233
report
D.by having each user create a job using DBMS_SCHEDULER that
includes all the information and commands necessary to generate the
report
E.by creating a program using DBMS_SCHEDULER that accepts one or
more variables, and creating a job that calls this program using
DBMS_SCHEDULER
Answer: E
21. In your production database there is a job, CALC_STAT, which has
been scheduled to run every Friday at 5:00 p.m. CALC_STAT updates
the optimizer statistics for the objects owned by the APPS schema. You
want the task to be generic, thereby allowing users to modify the
attributes of the task at run time without affecting the original task.
Which component of Oracle Scheduler must you define to achieve this?
A.Window
B.Program
C.Job class
D.Window group
Answer: B
22. While creating a job class using the DBMS_SCHEDULER package,
you set the logging level to LOGGING_RUNS. What would be the impact
厚积薄发、精益求精
112 / 233
of this setting?
A.Oracle Scheduler would write detailed information to the job log for
each run of each job in the job class.
B.Oracle Scheduler would write detailed information to the job log for
the first run of each job in the job class.
C.Oracle Scheduler would write detailed information to the job log for
each run of only the first job in the job class.
D.Oracle Scheduler would write detailed information for all operations
performed on all jobs in the job class.
Answer: A
23. You need to create a schedule that will run a job on the second
Friday of each month. What should the repeat interval of the schedule
be set to?
A.FREQ = MONTHLY; BYDAY = 2FRI;
B.FREQ = MONTHLY; BYDAY = FRI2;
C.FREQ = MONTHLY; BYDAY = 2FRI;
D.FREQ = MONTHLY; BYDAY = FRI(2);
Answer: A
24. While creating a scheduler window using the DBMS_SCHEDULER
package, a user logged in as DBMGR gets the error message insufficient
privileges.
厚积薄发、精益求精
113 / 233
Which two commands would you use to resolve this error? (Choose
two.)
A.GRANT dba TO dbmgr;
B.GRANT connect TO dbmgr;
C.GRANT resource TO dbmgr;
D.GRANT hs_admin_role TO dbmgr;
E.GRANT manage scheduler TO dbmgr;
Answer: AE
25. You have created a job class, CUST_JOB_1, with two jobs,
CUST_JOB_1_A and CUST_JOB_1_B.
Which two statements are true? (Choose two.)
A.Both the jobs would be executed concurrently.
B.Jobs in the job class must have been assigned the same priority.
C.The member jobs would get dropped when the job class is dropped.
D.The member jobs would get disabled when the job class is dropped.
E.The attributes defined at the class level would be applied to both the
jobs.
Answer: DE
26. Using Oracle Scheduler you have scheduled two jobs, JOB_A and
JOB_B, to run at 9:00 p.m. every Friday. You want both the jobs to use
a single resource plan, WEEKEND_PLAN.
厚积薄发、精益求精
114 / 233
Which task must have already been performed to enable you to achieve
this objective?
A.A window must have been created with the WEEKEND_PLAN
resource plan.
B.A program must have been created with the WEEKEND_PLAN
resource plan.
C.A job class must have been created with the WEEKEND_PLAN
resource plan.
D.A window group must have been created with the WEEKEND_PLAN
resource plan.
Answer: A
厚积薄发、精益求精
115 / 233
第十四章管理用户、权限
（关键字：USER、PRIVILEGE、ROLE、SCHEMA）
1. Which step do you need to perform to enable a user with the SYSDBA
privilege to log in as SYSDBA in iSQL*Plus?
A. The user must be granted the database administrator (DBA)
privilege.
B. The user must be listed in the password file for the authentication.
C. No special setup is needed for the user to connect as SYSDBA in
iSQL*Plus.
D. Set up a user in the Oracle Application Server Containers for J2EE
(OC4J) user manager, and grant the webDba role to the user.
Answer: D
2. View the Exhibit to observe the message received while trying to drop
the SL_REP user in Oracle Enterprise Manager.
The SL_REP user owns objects and is currently connected to the
database instance. What would happen
厚积薄发、精益求精
116 / 233
A. An error is returned, and the user is not dropped.
B. The drop operation waits until the session started by the user ends.
C. The user is dropped successfully, and the session started by the user
is killed.
D. The user is dropped, and the objects owned by the user are
transferred to the recycle bin.
Answer: A
3. You execute the following set of commands to create a database user
and to grant the system privileges in your production environment.
SQL> CREATE USER user01
IDENTIFIED BY oracle
DEFAULT TABLESPACE tbs1
TEMPORARY TABLESPACE temp
PROFILE default
/
SQL> GRANT create session, create table TO user01;
While executing the command to create a table, the user gets the
厚积薄发、精益求精
117 / 233
following error message and the
CREATE TABLE. . command fails.
ERROR at line 1:
ORA01950:no privileges on tablespace
What could be the possible reason for this error message?
A. The tablespace TBS1 is full.
B. The user is not the owner of the SYSTEM tablespace.
C. The user does not have quota on the TBS1 tablespace.
D. The user does not have sufficient system privileges to create table in
the TBS1 tablespace.
E. The user does not have sufficient privileges to create table on the
default permanent tablespace.
Answer: C
4. The HR user owns the EMP table. The HR user grants privileges to
the SCOTT user by using this command:
SQL> GRANT SELECT,INSERT,UPDATE ON emp TO scott WITH GRANT
OPTION;
The SCOTT user executes this command to grant privileges to the JIM
user:
SQL> GRANT SELECT,INSERT,UPDATE ON hr.emp TO jim;
Now the HR user decides to revoke privileges from JIM by using this
厚积薄发、精益求精
118 / 233
command:
SQL> REVOKE SELECT,INSERT,UPDATE ON emp FROM jim;
Which statement is true after HR issues the REVOKE command?
A. The command succeeds and privileges are revoked from JIM.
B. The command fails because SCOTT still has privileges with him.
C. The command fails because HR cannot revoke the privileges from
JIM.
D. Only HR can perform SELECT, INSERT, and UPDATE operations on
the EMP table.
Answer: C
厚积薄发、精益求精
119 / 233
第十五章管理角色
（关键字：USER、PRIVILEGE、ROLE、SCHEMA）
1. 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 cannot be assigned external authentication.
E. A role can contain both system and object privileges.
Answer: CE
2. View the Exhibit to observe the roles assigned to the SCOTT user.
Which statement is true about the assignment of the
厚积薄发、精益求精
120 / 233
SELECT_CATALOG_ROLE role to the SCOTT
user?
A. The user cannot use the role at all.
B. The user can grant the role to other users.
C. The user needs to enable the role explicitly.
D. The user can start using the role immediately.
Answer: C
3. View the Exhibit to observe the privileges granted to the MGR_ROLE
role.
The SCOTT user has been granted the CONNECT and RESOURCE roles
only. The database administrator (DBA) grants MGR_ROLE to the
SCOTT user by using this command:
SQL> GRANT MGR_ROLE TO SCOTT WITH ADMIN OPTION;
Which statement is true about the SCOTT user after he is granted this
厚积薄发、精益求精
121 / 233
role?
A. The SCOTT user can grant the MGR_ROLE role and the privileges in
it to other users.
B. The SCOTT user can grant the privileges in the MGR_ROLE role to
other users but not with ADMIN_OPTION.
C. The SCOTT user can grant only the MGR_ROLE role to other users
but not the privileges in it.
D. The SCOTT user can grant the privileges in the MGR_ROLE role to
other users but cannot revoke privileges from them.
Answer: C
厚积薄发、精益求精
122 / 233
第十六章审计
（关键字：USER、PRIVILEGE、AUDIT）
1. You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER
SUCCESSFUL;
What is the effect of this command?
A. One audit record is created for the whole session if user SCOTT
successfully drops one or more tables in his session.
B. One audit record is created for every session when any user
successfully drops a table owned by SCOTT.
C. One audit record is created for each successful DROP TABLE
command executed by any user to drop tables owned by SCOTT.
D. One audit record is generated for the session when SCOTT grants the
DROP ANY TABLE privilege to other users in his session.
E. One audit record is created for each successful DROP TABLE
command executed in the session of SCOTT.
Answer: A
2. The SAVE_AMT column in the ACCOUNTS table contains the balance
details of customers in a bank.As part of the yearend tax and interest
calculation process, all the rows in the table need to be accessed.The
厚积薄发、精益求精
123 / 233
bank authorities want to track access to the rows containing balance
amounts exceeding $200,000,and then send an alert message to the
administrator.
Which method would you suggest to the bank for achieving this task?
A. implementing valuebased auditing by using triggers
B. implementing finegrained auditing with audit condition and event
handler
C. performing standard database auditing to audit object privileges by
setting the AUDIT_TRAIL parameter to EXTENDED
D. performing standard database auditing to audit SQL statements
with granularity level set to ACCESS
Answer: B
3. Which three statements are true regarding the finegrained
auditing (FGA)? (Choose three.)
A. FGA is possible on SELECT statements only.
B. The audit trail for FGA is stored in the FGA_LOG$ table.
C. The audit trail for FGA is stored in the AUD_LOG$ table.
D. FGA enables a SQL predicate to define when to audit an event.
E. FGA audits DELETE statements only when audit columns are
specified.
F. FGA includes the SQL statement used by the user as part of the audit
厚积薄发、精益求精
124 / 233
event entry.
Answer: BDF
4. You suspect unauthorized data manipulation language (DML)
operations on a particular table. You want to track users who are
performing the transactions and the values used in the transactions.
Also, you plan to transfer these values to another table for analysis.
How would you achieve this?
A. by using triggers
B. by using external tables
C. by using anonymous PL/SQL blocks
D. by auditing all DML operations on the table
Answer: A
5. You suspect unauthorized data manipulation language (DML)
operations on a particular table. You want to track users who are
performing the transactions and the values used in the transactions.
Also, you plan to transfer these values to another table for analysis.
How would you achieve this?
A. by using triggers
B. by using external tables
C. by using anonymous PL/SQL blocks
D. by auditing all DML operations on the table
厚积薄发、精益求精
125 / 233
Answer: A
厚积薄发、精益求精
126 / 233
第十七章加载数据
（关键字：SQL LOADER、EXTERNAL TABLE）
1. You are using flat files as the data source for one of your data
warehousing applications. You plan to move the data from the flat file
structures to an Oracle database to optimize the application
performance.In your database you have clustered tables.
While migrating the data, you want to have minimal impact on the
database performance and optimize the data load operation. Which
method would you use to load data into Oracle database?
A. use the external table population
B. use the Oracle Data Pump export and import utility
C. use the conventional path data load of SQL*Loader utility
D. use the direct path data load of Oracle export and import utility
Answer: C
2. You have a large amount of historical data in an operating system file.
Some analysts in your organization need to query this data. The file is
too large to load into your current database. Which is the most effective
method to accomplish the task?
A. Upgrade the hardware/memory to accommodate the data.
B. Load the data into your database by using the PARALLEL clause.
厚积薄发、精益求精
127 / 233
C. Give analysts DBA privilege, so that they can query
DBA_EXTERNAL_TABLES.
D. Use an external table so you can have the metadata available in your
database, but leave the data in
the operating system files.
Answer: D
3. You have a text file that maintains information on thousands of items.
The enduser
Application requires the transfer of that information into a table in the
database. What would you use to achieve this task? (Choose two.)
A. Oracle Text
B. Data Pump
C. SQL*Loader
D. Oracle Import
E. External table
Answer: CE
厚积薄发、精益求精
128 / 233
第十八章全球支持
（关键字：CHARACTER、NLS_LANGUAGE、NLS_DATA_FORMAT）
1. You want to set up the Oracle Database 10g installation to follow the
French language, with France as the territory, and the date to be
displayed in French for all users’ sessions. Which environmental
variable would you set to achieve this objective?
A. NLS_LANG
B. NLS_LANGUAGE
C. NLS_TERRITORY
D. NLS_CHARACTERSET
E. NLS_DATE_LANGUAGE
F. NLS_LENGTH_SEMANTICS
G. NLS_NCHAR_CHARACTERSET
Answer: A
2. You have set the value of the NLS_TIMESTAMP_TZ_FORMAT
parameter in the parameter file to YYYYMMDD.
The default format of which two data types would be affected by this
setting? (Choose
two.)
A.DATE
厚积薄发、精益求精
129 / 233
B.TIMESTAMP
C.INTERVAL YEAR TO MONTH
D.INTERVAL DAY TO SECOND
E.TIMESTAMP WITH LOCAL TIME ZONE
Answer: BE
3. The current time zone for one of the user sessions is set to the
database local time zone. For one application, the user session requires
the time zone to be set to the local operating system time zone without
affecting other user sessions.
Which two solutions could the user implement to achieve this objective?
(Choose two.)
A.use the ALTER SYSTEM command to change the time zone
B.use the ALTER SESSION command to change the time zone
C.use the ALTER DATABASE command to change the time zone
D.set the value for the operating system variable ORA_SDTZ on the
client machine
E.set the value for the operating system variable ORA_SDTZ on the
database server machine
Answer: BD
厚积薄发、精益求精
130 / 233
第十九章数据库优化
(关键字：AWR、ADDM、ADVICE、PERFORMANCE)
1. In your Oracle 10g database , you have scheduled a job to update the
optimizer statistics at 05:00 pm every Friday. The job has successfully
completed. Which three pieces of information would you check to
confirm that the statistics have been collected? (Choose three.)
A. average row size
B. last analyzed date
C. size of table in bytes
D. size of table in database blocks
E. number of free blocks in the free list
F. number of extents present in the table
Answer: ABD
2. In your database, the STATISTICS_LEVEL initialization parameter is
set to BASIC. What is the impact of this setting?
A. The optimizer statistics are collected automatically.
B. Only the timed operating system (OS) statistics and plan execution
statistics are collected.
C. The Oracle server dynamically generates the necessary statistics on
tables as part of query optimization.
厚积薄发、精益求精
131 / 233
D. The snapshots for the Automatic Workload Repository (AWR) are not
generated automatically.
E. Snapshots cannot be collected manually by using
DBMS_WORKLOAD_REPOSITORY PACKAGE.
Answer: D
3. Your database is functional with peak load for one hour. You want to
preserve the performance statistics collected during this period so that
they can be used for comparison while analyzing the performance of the
database in the future.
What action would you take to achieve this task?
A. Set the STATISTICS_LEVEL initialization parameter to ALL.
B. Create a baseline on a pair of snapshots that have the statistics of
the peakload period in the database.
C. Decrease the snapshot interval in the AWR to collect more number of
snapshots during the peakload period.
D. Set the snapshot retention period in the Automatic Workload
Repository (AWR) to zero to avoid the automatic purging of the
snapshots.
Answer: B
4. The application workload on your database is same between 10 a.m.
and 11 a.m. on weekdays.Suddenly you observe poor performance
厚积薄发、精益求精
132 / 233
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 Compare Period report
E. by analyzing the output of the V$ACTIVE_SESSION_HISTORY view
Answer: D
5. In your database, the snapshot interval is set to 10 minutes for the
Automatic Workload Repository
(AWR). The database instance is running for the past 30 days.
View the Exhibit to examine the Automatic Database Diagnostic
Monitor (ADDM) task.
The ADDM task is performed every 10 minutes, except the last task.
What could be the reason for this?
厚积薄发、精益求精
133 / 233
A. The snapshot retention period for the AWR was increased before the
last task.
B. The optimizer statistics for few objects in the database were locked
before the last task.
C. The STATISTICS_LEVEL initialization parameter was changed to
BASIC and reset to TYPICAL before the last task.
D. The optimizer statistics was collected manually by using the
DBMS_STATS package before the last task.
Answer: C
厚积薄发、精益求精
134 / 233
6. Which two statements are true about the Automatic Database
Diagnostic Monitor (ADDM)? (Choose two.)
A. The ADDM runs after each AWR snapshot is collected.
B. The ADDM requires at least four AWR snapshots for analysis.
C. The ADDM analysis provides only the diagnostic information but
does not provide recommendations.
D. The results of the ADDM analysis are stored in the Automatic
Workload Repository (AWR).
E. The ADDM calls other advisors if required, but does not provide
recommendations about the advisors.
Answer: AD
7. You want to monitor and control the resource usage by sessions. You
want to be warned automatically
when more than 100 sessions are opened with your database.
What action would you take to achieve this?
A. Use the Database Resource Manager.
B. Set the limits in the profiles used by users.
C. Modify the SESSIONS initialization parameter.
D. Set the warning threshold for the Current Logons Count metric.
Answer: D
8. View the Exhibit to examine the output of the
厚积薄发、精益求精
135 / 233
DBA_OUTSTANDING_ALERT view.
After 30 minutes, you executed the following command:
SQL> SELECT reason,metric_value FROM dba_outstanding_alerts;
REASON METRIC_VALUE Tablespace
[TEST] is [28 perce 28.125 nt] full
What could be the two reasons for the elimination of the other rows in
the output? (Choose two.)
A. The threshold alert conditions are cleared.
B. The threshold alerts are transferred to DBA_ALERT_HISTORY.
C. The nonthresholdbased alerts are transferred to
DBA_ALERT_HISTORY.
D. The threshold alerts related to database metrics are permanently
stored in DBA_ALERT_HISTORY but not the threshold alerts related to
instance metrics.
Answer: AB
9. You define an alert to be raised when the USERS tablespace usage
厚积薄发、精益求精
136 / 233
has reached 80% of the total space. Which area would you refer to, in
order to confirm that the alert has been raised due the event?
A. the alert.log file
B. the DBA_ALERTS view
C. the Database Control Home page
D. the Database Control performance page
E. the Database Control Maintenance page
Answer: C
10. 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.
厚积薄发、精益求精
137 / 233
Answer: C
11. View the Exhibit and examine the output.
Which statement describes the conclusion?
A. The users should use bind variables instead of literals in the query.
B. The dictionary cache is consuming more space than the library
cache.
C. The shared pool size should be increased to accommodate the SQL
statements.
D. Preparing indexes on the tables used in the SQL statements would
improve the library cache performance.
Answer: A
12. You received complaints about the degradation of SQL query
performance. You identified top SQL
queries that consume time. What would be your next step to find out
recommendations about statistics
collection and restructuring of the SQL statement to improve query
performance?
A. run Segment Advisor
厚积薄发、精益求精
138 / 233
B. run SQL Tuning Advisor on top SQL statements
C. run the Automatic Workload Repository (AWR) report
D. run the Automatic Database Diagnostic Monitor (ADDM) on top SQL
statements
Answer: B
13. Users of HR schema complain about slowerthannormal
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.
厚积薄发、精益求精
139 / 233
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
Answer: D
14. Which statement is true when you run the SQL Tuning Advisor with
limited scope?
A. Access path analysis is not performed for the SQL statements.
厚积薄发、精益求精
140 / 233
B. SQL structure analysis is not performed for the SQL statements.
C. SQL Profile recommendations are not generated for the SQL
statements.
D. The staleness and absence of the statistics are not checked for the
objects in the query supplied to the SQL Tuning Advisor.
Answer: C
15. You work in a data warehouse environment that involves the
execution of complex queries. The current content of the SQL cache
holds the ideal workload for analysis. You want to analyze only few most
resourceintensive statements.
What would be your suggestion to receive recommendations on the
efficient use of indexes and
materialized views to improve query performance?
A. Run the SQL Access Advisor.
B. Run the SQL Tuning Advisor (STA).
C. Run the Automatic Workload Repository (AWR) report.
D. Run the Automatic Database Diagnostic Monitor (ADDM).
Answer: A
16. You need to check the EMP_EAST partition in the EMPLOYEES
table for physical corruptions. You also need to verify that the rows
belong to the correct partition. Which option could you use?
厚积薄发、精益求精
141 / 233
A.LogMiner
B.the DBNEWID utility
C.the DBVERIFY utility
D.the ANALYZE command
E.the RMAN REPORT command
F.the RMAN CROSSCHECK command
G.the RMAN BLOCKRECOVER command
Answer: D
17. You execute the following command to enable a session in
resumable mode:
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 60;
What is the impact of a timeout on the statements being suspended?
A.The statements remain suspended for at least 60 seconds.
B.The statements are suspended for 60 seconds and then they are
executed.
C.The suspended statements error out if the problem is not rectified
within 60 seconds.
D.The statements are automatically suspended 60 seconds after an
error is received, and then attempt to execute normally again.
Answer: C
18. You observe that database performance has degraded over a period
厚积薄发、精益求精
142 / 233
of time. While investigating the reason, you find that the size of the
database buffer cache is not large enough to cache all the needed data
blocks.
Which advisory component would you refer to, in order to determine the
required size of the database buffer cache?
A.Memory Advisor
B.Segment Advisor
C.SQL Tuning Advisor
D.SQL Access Advisor
E.Automatic Database Diagnostic Monitor (ADDM)
Answer: A
19. View the Exhibit and examine the characteristics of the USERS
tablespace.
You observe that a large volume of inserts and deletes are happening on
the TRANS table in the USERS tablespace and you suspect that the
TRANS table is fragmented.
Which advisory component would you refer to, in order to find
information about table fragmentation?
厚积薄发、精益求精
143 / 233
A.Memory Advisor
B.Segment Advisor
C.SQL Tuning Advisor
D.SQL Access Advisor
E.Automatic Database Diagnostic Monitor (ADDM)
Answer: B
20. You find that the execution time of reports in your datawarehouse
application is significantly high. You suspect the lack of indexes to be
the reason for the degradation in performance.
Which advisory component would you refer to, in order to determine the
appropriate indexes?
A.Memory Advisor
B.Segment Advisor
C.SQL Access Advisor
D.Automatic Workload Repository (AWR)
E.Automatic Database Diagnostic Monitor (ADDM)
Answer: C
厚积薄发、精益求精
144 / 233
21. You want to use the SQL Tuning Advisor to generate
recommendations for badly written SQL statements in your
development environment. Which three sources can you select for the
advisor to analyze? (Choose three.)
A.Top SQL
B.snapshots
C.SQL Tuning sets
D.index access path
E.optimizer statistics
F.materialized view logs
Answer: ABC
21. View the Exhibit.
In your production database, the total waits and the time waited for log
file parallel write are significantly high. While investigating the reason,
you find that there are three redo log groups with two members in each
group, and all redo log members are placed on a single physical disk.
What action would you take to minimize the waits?
厚积薄发、精益求精
145 / 233
A.start the log writer slave processes
B.increase the number of redo log files
C.increase the size of the redo log buffer
D.place the redo log files on different disks
E.increase the number of log writer processes
Answer: D
22. In your production database, you have observed that the database
server performance degrades whenever there is a switch between the log
groups. On further investigation, you find that the database is running
厚积薄发、精益求精
146 / 233
in ARCHIVELOG mode and the archived log files are being written to the
same disk that is used to store the redo log members.
Which two actions would you take to improve the performance of the
database server? (Choose two.)
A.increase the size of the redo log buffer
B.increase the number of redo log groups
C.increase the number of log writer processes
D.increase the number of ARCn processes
E.change the appropriate LOG_ARCHIVE_DEST_n parameter to place
the archived log files in a separate location
Answer: BE
23. In the parameter file of your production database, the
FAST_START_MTTR_TARGET parameter is set to 300 to optimize
instance recovery. While observing the performance of the database
during instance recovery, you find that the redo log files are not sized
properly to support this activity.
Which two sources could you use to determine the optimal size of the
redo log files? (Choose two.)
A.the V$LOG view
B.the V$DBFILE view
C.the V$LOGFILE view
厚积薄发、精益求精
147 / 233
D.the V$INSTANCE_RECOVERY view
E.Oracle Enterprise Manager Database Control
Answer: DE
24. In which two conditions are resumable statements suspended?
(Choose two.)
A.when a user session is terminated
B.when a user exceeds the space quota
C.when a user manually suspends the statement
D.when the maximum extents of a segment is reached
E.when a table that is being accessed by the current transaction is not
found
Answer: BD
25. Which type of PL/SQL construct would you use to automatically
correct the error resulting from a statement that was suspended due to
a spacerelated
problem?
A.function
B.package
C.procedure
D.database trigger
E.anonymous PL/SQL block
厚积薄发、精益求精
148 / 233
Answer: D
26. The warning and critical threshold values have been set to 85% and
97%, respectively, for one of the tablespaces. The current tablespace
space usage is 54%. You modify the warning threshold to be 50% and
critical threshold to be 53% in Database Control.
Which statement is true?
A.The new setting would be applied but no alerts would be raised
immediately.
B.The new setting would be applied and an alert would be raised
immediately.
C.The new setting would be ignored because the tablespace space usage
is more than the specified threshold value.
D.The new setting would cause an error because the tablespace space
usage is more than the specified threshold value.
Answer: A
27. In an Oracle 10g database, the inmemory statistics are gathered at
regular intervals and used to perform growthtrend analysis and
capacity planning of the database. Which component stores these
statistics?
A.recovery catalog
B.Oracle Enterprise Manager Repository
厚积薄发、精益求精
149 / 233
C.Automatic Workload Repository (AWR)
D.Oracle 10g Enterprise Manager Grid Control
E.Automatic Database Diagnostic Monitor (ADDM)
Answer: C
28. You have specified the warning and critical threshold values of an
application tablespace to be 60% and 70%, respectively. From the
tablespace space usage metrics, you find that the actual space usage
has reached the specified warning threshold value, but no alerts have
been generated.What could be the reason for this?
A.The EVENT parameter was not set.
B.The SQL_TRACE parameter is set to FALSE.
C.The Enterprise Manager Grid Control is not used.
D.The STATISTICS_LEVEL parameter is set to BASIC.
E.The TIMED_STATISTICS parameter is set to FALSE.
Answer: D
29. View the Exhibit and examine the properties of the TT tablespace.
Using Database Control, you have scheduled a job to shrink the TRANS
table residing on the TT tablespace. The job would run at 5:00 p.m.
every Friday.
When you examine the space usage of the table after the completion of
the job, you find that the table has not been shrunk.What could have
厚积薄发、精益求精
150 / 233
been the reason for this?
A.The tablespace that contains the TRANS table is online.
B.The tablespace that contains the TRANS table is permanent.
C.The tablespace that contains the TRANS table is locally managed.
D.The segment space management of the tablespace that contains the
TRANS table is manual in nature.
Answer: D
30. View the Exhibit and examine the properties of the USERS
tablespace.You execute the following statement to shrink the TRANS
table existing on the USERS tablespace:
SQL> ALTER TABLE trans SHRINK SPACE CASCADE;
Which objects would be affected by this command? (Choose all that
apply.)
厚积薄发、精益求精
151 / 233
A.the TRANS table
B.the B*Tree indexes on the TRANS table
C.the materialized views log of the TRANS table
D.the materialized views based on the TRANS table
E.the large object (LOB) segments of the TRANS table
Answer: AB
31. In one of your online transaction processing (OLTP) applications,
users are manipulating and querying a database table simultaneously.
From the Segment Advisor, you find that one of the tables is highly
fragmented and you want to shrink the table immediately without
affecting the currently active queries.
Which option would you use with the ALTER TABLE command to
achieve this objective?
A.REBUILD
B.CASCADE
C.TRUNCATE
D.ROW MOVEMENT
厚积薄发、精益求精
152 / 233
E.SHRINK SPACE COMPACT
F.SHRINK SPACE CASCADE
Answer: E
32. While designing the database for one of your online transaction
processing (OLTP) applications, you want to achieve the following:
a) high availability of data
b) faster primary key access to the table data
c) compact storage for the table
Which type of tables would you use to achieve these objectives?
A.heap tables
B.object tables
C.partitioned tables
D.indexorganized tables (IOTs)
Answer: D
33. While designing your database, you have created the EMPLOYEES
table as an indexorganized 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.
厚积薄发、精益求精
153 / 233
D.The PCTTHRESHOLD option must have been specified.
Answer: B
34. 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.indexorganized
table (IOT)
Answer: B
35 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
厚积薄发、精益求精
154 / 233
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
Answer: D
36. In your test database, you have created the ORDERS table as an
indexorganized
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 recreate
the mapping table
C.truncate the mapping table and reinsert the values
D.use the ALTER TABLE .. REBUILD command to defragment the
mapping table
厚积薄发、精益求精
155 / 233
Answer: B
37. 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.indexorganized
table (IOT)
Answer: A
38. 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:ORA01654:unable to extend index
USERS.ORDERS_IND by 8 in tablespace INDEXES
While investigating, you find that the INDEXES tablespace has run out
厚积薄发、精益求精
156 / 233
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 recreate the index
B.coalesce the ORDERS_IND index
C.coalesce the INDEXES tablespace
D.drop and recreate the ORDERS table
E.rebuild the index online and move it to another tablespace
Answer: BE
39. In your test database, you find that a user's session is executing a
lot of SQL statements, resulting in the generation of a large number of
trace files. While investigating the reason, you find that SQL trace has
been enabled at the instance level.You want to disable SQL trace,
remotely, only for that user session to reduce the volume of trace data
being generated.
How do you achieve this objective?
A.by setting the SQL_TRACE parameter to FALSE in the parameter file
B.by using DBMS_MONITOR.SESSION_TRACE_DISABLE to disable the
tracing for the user session
C.by setting the SQL_TRACE parameter to FALSE by using the ALTER
厚积薄发、精益求精
157 / 233
SYSTEM command in the user session
D.by setting the SQL_TRACE parameter to FALSE by using the ALTER
SESSION command in the user session
Answer: B
40. Users are performing a large volume of inserts and deletes on the
application tables in the APPS tablespace. You observe that there are
several warning alerts being generated for the APPS tablespace space
usage metrics. Currently, the warning threshold for the tablespace
space usage metrics is set to 70%.To make the generated alerts more
useful as a problem identification tool, you want to reduce the
frequency of alert generation for the tablespace space usage metrics for
the APPS tablespace. What should you do?
A.disable SQL tracing for the APPS tablespace
B.disable logging attribute for the APPS tablespace
C.modify the tablespace to be a dictionarymanaged tablespace
D.increase the critical threshold value for the tablespace space usage
metrics for the APPS tablespace
E.increase the warning threshold value for the tablespace space usage
metrics for the APPS tablespace
Answer: E
41. While setting up the thresholds for tablespace space usage metrics
厚积薄发、精益求精
158 / 233
for one of your tablespaces, you define the critical threshold as 60% and
warning threshold as 75%. What would be the result of this setting?
A.It would result in an error because the warning threshold cannot be
more than the critical threshold.
B.The warning threshold is ignored and alerts would be generated when
space usage exceeds the critical threshold.
C.The critical threshold is ignored and alerts would be generated when
space usage exceeds the warning threshold.
D.Both threshold values are used and alerts are generated when space
usage exceeds the respective threshold values.
Answer: A
厚积薄发、精益求精
159 / 233
第二十章备份和恢复
(关键字：RESTORE、RECOVER、RMAN)
1. You are using the backup scheduler in Enterprise Manager (EM) to
schedule a backup of your
database.
Which type of script does the backup scheduler generate?
A. SQL script
B. PL/SQL script
C. Operating System (OS) script
D. Enterprise Manager (EM) script
E. Recovery Manager (RMAN) script
Answer: E
2. You executed the following command to export the EMPLOYEES
table from a remote machine:
$ EXPDP hr/hr@data.us.oracle.com
DUMPFILE=my_dir:exp_hr.log
LOGFILE=data_pump_dir:log_hr.log
TABLES=employees
What would be the outcome of this command?
A. The command would execute successfully and the export dump file
厚积薄发、精益求精
160 / 233
would be created in the destination of the directory object MY_DIR.
B. The command would execute successfully. But log file would not be
created as DATA_PUMP_DIR directory is only accessible to user with
SYSDBA privilege.
C. The command fails with an error because DATA_PUMP_DIR directory
have higher precedence overthe perfile directory.
D. The command fails with an error because no absolute path is
specified for log file and dumpfile.
Answer: A
3. 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.
Answer: BC
4. Your database is open, and you plan to perform Recovery Manager
(RMAN) backups. Which three statements are true about these backups?
(Choose three.)
厚积薄发、精益求精
161 / 233
A. The backups would be consistent.
B. The backups would be inconsistent.
C. The backups need to be restored and database has to be recovered in
case of a media failure.
D. The backups need not be restored during recovery in case of a media
failure.
E. The backups would be possible only if the database is running in
ARCHIVELOG mode.
F. The backups would be possible only if the database is running in
NOARCHIVELOG mode.
Answer: BCE
5. You perform differential incremental level 1 backups of your
database on each working day and level 0 backup on Sundays.
Which two statements are true about the differential incremental
backups? (Choose two.)
A. The backup performed on Sundays contains all the blocks that have
ever been use in the database.
B. The backup performed on Sundays contains all the blocks that have
changed since the last level 1 backup.
C. The backup performed on each working day contains all the blocks
that have changed since the last level 0 or level 1 backup.
厚积薄发、精益求精
162 / 233
D. The backup performed on each working day contains all the blocks
that have changed since the last level 0 backup.
Answer: AC
6. According to your backup strategy, you performed an incremental
level 0 backup of your database.
Which statement regarding this backup is true?
A. The backup is similar to image copy.
B. The backup contains all used data blocks.
C. The backup contains only unused data blocks.
D. The backup contains all data blocks changed since the last
incremental level 1 backup.
Answer: B
7. On which three can you use Recovery Manager (RMAN) to perform
incremental backup? (Choose
three.)
A. data files
B. control files
C. tablespaces
D. password file
E. parameter file
F. whole database
厚积薄发、精益求精
163 / 233
G. flashback log file
H. archived log files
I. change tracking file
Answer: ACF
8. You scheduled a backup by using the Schedule Backup option in
Oracle Enterprise Manager.
Which statement about the script created by Oracle Enterprise
Manager is true?
A. It is a SQL script.
B. It is a PL/SQL file.
C. It is an operating system (OS) file.
D. It is a Recovery Manager (RMAN) script.
Answer: D
9. You notice this warning in the alert log file:
ORA19815:WARNING: db_recovery_file_dest_size of 3221225472 bytes
is 100.00% used, and has 0 remaining bytes available.
What would you do to reclaim the used space in the Flash Recovery
Area?
A. Back up the Flash Recovery Area.
B. Increase the retention time for the files.
C. Decrease the retention time for the files.
厚积薄发、精益求精
164 / 233
D. Manually delete all the archived log files from the Flash Recovery
Area by using operating system (OS) commands.
Answer: AC
10. Which statement is true about the loss of systemcritical data files in
ARCHIVELOG mode?
A. The data files can be recovered till the last committed transaction.
B. The data files can be recovered without shutting down the database.
C. The data file is taken offline automatically; the database remains
open.
D. The data files can be recovered to the time of the most recent backup.
Answer: A
11. Data files of which three tablespaces can be recovered by
performing an open recovery? (Choose
three.)
A. TEMP
B. UNDO
C. INDEX
D. SYSAUX
E. SYSTEM
Answer: ACD
12. Due to media failure you lost one of the data files belonging to the
厚积薄发、精益求精
165 / 233
USERS tablespace, and the tablespace is not available to use. Which
statement regarding the status of the database is true?
A. The database remains open.
B. The database gets dismounted.
C. The database becomes read only.
D. The database instance gets aborted.
E. The database gets shut down automatically.
F. The status of the database depends on the status of the USERS
tablespace.
Answer: A
13. Your database is in NOARCHIVELOG mode and a logswitch
happens every 20 minutes. On Thursday
morning, media failure resulted in corruption of a data file belonging to
the TOOLS tablespace. Backups are taken on every Sunday.
What would you do in this situation?
A. recover using Oracle Flashback Database technology
B. restore the data files from backup and perform a complete recovery
C. restore only the corrupted data file and perform tablespace recovery
D. restore the data files from backup and perform cancelbased recovery
E. restore the entire database from the most recent backup and start
the instance and open the database
厚积薄发、精益求精
166 / 233
Answer: E
14. 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;
Answer: AB
15. You are working on an online transaction processing (OLTP) system.
You notice that a PL/SQL procedure got executed twice at 2:00 p.m.
This has incorrectly updated the EMP_SAL table.
How would you revert the table to its state at 2:00 p.m.?
A. Perform pointintime
recovery to 2:00 p.m.
B. Use Flashback Table feature to revert the changes.
C. Restore the entire database from the recent backup and open it.
D. Issue the rollback statement with system change number (SCN).
Answer: B
16. Which two statements about Flashback Query are true? (Choose
two.)
厚积薄发、精益求精
167 / 233
A. It is generated by using the redo log files.
B. It helps in rowlevel
recovery from user errors.
C. It can be performed to recover ALTER TABLE statements
D. It fails when undo data pertaining to the transaction is overwritten.
E. The database has to be opened with the resetlogs option after
performing Flashback Query.
Answer: BD
17. You want to move all the objects of the APPS user in the test
database to the DB_USER schema of the production database. Which
option of IMPDP would you use to accomplish this task?
A. FULL
B. SCHEMAS
C. REMAP_SCHEMA
D. REMAP_DATAFILES
E. REMAP_TABLESPACE
Answer: C
18. User SCOTT wants to export his objects using Oracle Data Pump
and executes the following command:
$ expdp scott/tiger
directory = EXPORT_DIR
厚积薄发、精益求精
168 / 233
dumpfile = scott.dmp
include = table
include = view:”like ‘%DEPARTMENTS%’”
content = DATA_ONLY
Which task would the command accomplish?
A. Oracle Data Pump would export only the data of all of the tables and
views.
B. Oracle Data Pump would export all of the table structures along with
data and all the views.
C. Oracle Data Pump would export the table data and the view
definitions where the view name contains a string named
DEPARTMENTS.
D. Oracle Data Pump would export the table data and the view
definitions with data where view name contains a string named
DEPARTMENTS.
E. Oracle Data Pump would export all of the table structures and the
view definitions with data where view name contains a string named
DEPARTMENTS.
Answer: C
19. View the Exhibit to see the source and target databases.
厚积薄发、精益求精
169 / 233
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 = 20040203
09:00
The command fails, displaying the following error:
ORA39001:invalid argument value
ORA39000:bad dump file specification
厚积薄发、精益求精
170 / 233
ORA31640:unable to open dump file
"/home/oracle/schema/schemas.dat"“for read
ORA27037:unable to obt”in 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
Answer: F
20. You executed the following command in Recovery Manager (RMAN)
to perform a backup of the DETTBS tablespace:
RMAN> BACKUP TABLESPACE DETTBS;
Under which conditions would this command execute successfully?
(Choose all that apply.)
A.The database is in NOMOUNT state.
B.The database is in ARCHIVELOG mode and the tablespace is online.
C.The database is in ARCHIVELOG mode and the tablespace is offline.
厚积薄发、精益求精
171 / 233
D.The database is in NOARCHIVELOG mode and the tablespace is
online.
E.The database is in NOARCHIVELOG mode and the tablespace is
offline.
Answer: BCE
21. You lost a temporary file that belongs to the default temporary
tablespace in your database. From the options provided, which
approach would you take to solve the problem?
A.flash back the database
B.import the temporary tablespace from the last export
C.restore all the data files and temporary files from the last full
database backup and perform a recovery
D.not perform a recovery, but create a new temporary tablespace, make
it the default temporary tablespace and then drop the old tablespace
Answer: D
22. You executed the following command in Recovery Manager (RMAN):
RMAN> RESTORE CONTROLFILE;
Which operation must you perform before this command is executed?
A.back up the control file to trace
B.bring database to the MOUNT state
C.open a connection to the RMAN recovery catalog, which contains the
厚积薄发、精益求精
172 / 233
RMAN metadata for the target database
D.set the database ID (DBID), but only if the DB_NAME parameter
associated with the target database is unique in the recovery catalog
Answer: C
23. You are using Oracle Database 10g. You performed an incomplete
recovery of your database and opened the database with the
RESETLOGS option.
What is the effect of opening the database with the RESETLOGS option?
(Choose two.)
A.This operation resets the SCN for the database.
B.This operation creates a new incarnation of the database.
C.This operation moves all the redo log files to a different location.
D.This operation deletes the old redo log files and creates new redo log
files.
E.This operation updates all current datafiles and online redo logs and
all subsequent archived redo logs with a new RESETLOGS SCN and
time stamp.
Answer: BE
24. On Monday, you dropped the DEPT table from your schema and
then you recreated
the DEPT table in your schema. On Wednesday, you have a
厚积薄发、精益求精
173 / 233
requirement to restore the DEPT table from the recycle bin.
Which statement is correct?
A.You can restore the DEPT table by using the Oracle Flashback Drop
feature, provided you use the RENAME TO clause.
B.You can restore the DEPT table by using the Oracle Flashback Drop
feature and a systemgenerated name will be assigned to the restored
table.
C.You cannot restore the DEPT table by using the Oracle Flashback
Drop feature because a table with
the name DEPT already exists in your schema.
D.You cannot restore the DEPT table by using the Oracle Flashback
Drop feature because the contents of
the recycle bin are purged every 12 hours by default.
Answer: A
25. Which two statements are correct regarding the Oracle Flashback
Drop feature? (Choose two.)
A.Recycle bin exists for the tables only in nonSYSTEM,locally managed
tablespaces.
B.You can flash back a dropped table provided row movement has been
enabled on the table.
C.If you drop an index before dropping its associated table, then the
厚积薄发、精益求精
174 / 233
recovery of the index is not supported when you flash back the dropped
table.
D.When you execute the DROP TABLESPACE INCLUDING CONTENTS
command, the objects in the tablespace are placed in the recycle bin.
E.When a dropped table is moved to the recycle bin, only the table is
renamed to a systemgenerated name; its associated objects and
constraints are not renamed.
F.If you drop a table that is protected by the recycle bin, then associated
bitmapjoined indexes and materialized view logs are also stored in the
recycle bin.
Answer: AC
26. View the Exhibit and examine the Flashback SCNs.
A user has inserted wrong department data in the DEPT3 table in the
USERS tablespace. You use the Flashback Table functionality to rectify
the erroneous inserts. While performing the recovery, you chose
2004343 as the Flashback SCN.
Which two statements are correct in this scenario? (Choose two.)
A.Only the row with DEPARTMENT_ID 290 would be flashed back.
厚积薄发、精益求精
175 / 233
B.The rows with DEPARTMENT_ID 290 and 300 would be flashed back.
C.The rows with DEPARTMENT_ID 290 and 280 would be flashed back.
D.You would have taken the USERS tablespace offline before starting
the Flashback Table operation.
E.You would have enabled row movement for the DEPT3 table before
starting the Flashback Table operation.
Answer: BE
27. You are working in an online transaction processing (OLTP)
environment. You used the FLASHBACK TABLE command to flash back
the CUSTOMERS table. Before executing the FLASHBACK TABLE
command, the System Change Number (SCN) was 663571. After
flashing back the CUSTOMERS table, you realize that the table is not in
the correct state and the resultant changes are not what you had
desired.So, you need to reverse the effects of the FLASHBACK TABLE
command while ensuring that:
a) No other user data in the database is affected.
b) The operation takes the minimum possible time.
Which option would you choose?
A.use the ROLLBACK command with SCN 663571
B.perform Flashback Transaction Query with SCN 663571
C.execute the FLASHBACK DATABASE statement to retrieve the
厚积薄发、精益求精
176 / 233
CUSTOMERS table as it was at SCN 663571
D.execute another FLASHBACK TABLE statement to retrieve the
CUSTOMERS table as it was at SCN 663571
Answer: D
28. You executed the following FLASHBACK TABLE command:
FLASHBACK TABLE emp TO TIMESTAMP ('11:45','hh12:mi');
Which two statements are correct? (Choose two.)
A.The FLASHBACK TABLE statement will not be written to the alert log
file.
B.The changes made to the EMP table since the specified time will be
undone.
C.The EMP table that was dropped by mistake from the database will be
restored.
D.The FLASHBACK TABLE statement will be executed within a single
transaction.
E.The FLASHBACK TABLE statement will not maintain the existing
indexes on the EMP table.
F.The list of transactions that have modified the EMP table since the
specified time will be displayed.
Answer: BD
29. Why would you use the following FLASHBACK TABLE command?
厚积薄发、精益求精
177 / 233
FLASHBACK TABLE emp TO TIMESTAMP ('11:45','hh12:mi');
A.to undo the changes made to the EMP table since the specified time
B.to restore the EMP table that was wrongly dropped from the database
C.to view the transactions that have modified the EMP table since the
specified time
D.to view the changes made to the EMP table for one or more rows since
the specified time
E.to recover the EMP table to a point in time in the past by restoring the
most recent backup
Answer: A
30. For which two SQL statements can you use the Flashback Table
feature to revert a table to its previous state? (Choose two.)
A.UPDATE TABLE
B.CREATE CLUSTER
C.TRUNCATE TABLE
D.ALTER TABLE MOVE
E.INSERT INTO...VALUES
F.ALTER TABLE...DROP COLUMN
G.ALTER TABLE...DROP PARTITION
Answer: AE
31. You are working in an online transaction processing (OLTP)
厚积薄发、精益求精
178 / 233
environment. You realize that the salary for an employee, John, has
been accidentally modified in the EMPLOYEES table. Two days ago, the
data was in the correct state. Flashback logs generated during last two
days are available in the flash recovery area.
Which option would you choose to bring the data to the correct state
while ensuring that no other data in the same table is affected?
A.perform pointintime recovery
B.perform a Flashback Table operation to restore the table to the state it
was in two days ago
C.perform a Flashback Database operation to restore the database to
the state it was in two days ago
D.perform Flashback Versions Query and Flashback Transaction
Query to determine all the necessary undo SQL statements, and then
use them for recovery
Answer: D
32. View the Exhibits.
You performed operations on the DEPT4 table as shown in the Exhibit.
When you perform the Flashback Versions Query, you find that the first
two updates are not listed.
What could be the reason?
A.The row movement is not enabled on the table.
厚积薄发、精益求精
179 / 233
B.The first two updates were not explicitly committed.
C.The Flashback Versions Query lists only the most recent update.
D.The Flashback Versions Query stops producing rows after it
encounters a time in the past when the table structure was changed.
Answer: D
33. Which two statements are correct regarding the Flashback Versions
Query feature? (Choose two.)
A.You can use this feature to identify the versions of V$ views.
B.You can use this feature to identify the versions of external and fixed
tables.
C.You can use this feature for a table only if row movement is enabled
for the table.
D.You can use this feature to identify the committed versions of the
rows, but not the uncommitted versions.
E.You can use this feature to identify the inserts, deletes, and updates
performed on a particular row but not the data definition language
(DDL) operations performed on the table.
Answer: DE
33. By mistake, you ran the batch job (for updating the BILL_DETAILS
table) twice. You are not sure which rows in the BILL_DETAILS table
were affected. You need to identify:
厚积薄发、精益求精
180 / 233
a) a list of changes made along with the transaction identifier of each
change
b) the necessary SQL statements to undo the erroneous changes
Which option would you choose?
A.RMAN only
B.Flashback Table only
C.Flashback Versions Query only
D.Flashback Database and Flashback Transaction Query
E.Flashback Versions Query and Flashback Transaction Query
Answer: E
34. You executed the following query:
SELECT operation, undo_sql, table_name
FROM flashback_transaction_query;
Which statement is correct regarding the query output?
A.It would return information regarding only the last committed
transaction.
B.It would return only the active transactions in all the undo segments
in the database.
C.It would return only the committed transactions in all the undo
segments in the database.
D.It would return both active and committed transactions in all the
厚积薄发、精益求精
181 / 233
undo segments in the database.
E.It would return information regarding the transactions that began
and were committed in the last 30 minutes.
Answer: D
35. By using the transaction identifier provided by _____ for a particular
row change, you can use the Flashback Transaction Query to see the
operation performed by the transaction.
A.Flashback Table
B.Flashback Database
C.Flashback Versions Query
D.the RMAN REPORT command
E.the DBA_PENDING_TRANSACTIONS view
Answer: C
36. There was media failure and you need to check the data files for any
block corruption. Which option would you use to create a report on any
corruptions found within the database?
A.the DBNEWID utility
B.the DBVERIFY utility
C.the ANALYZE command
D.the RMAN REPORT command
E.the RMAN CROSSCHECK command
厚积薄发、精益求精
182 / 233
F.the CHECK_OBJECT procedure of the DBMS_REPAIR package
Answer: B
37. The EMPLOYEES table is stored in the USERS tablespace. You need
to check if the EMPLOYEES table is affected by the block corruption
found in the USERS tablespace. Which option would you use?
A.the DBNEWID utility
B.the ANALYZE command
C.the RMAN LIST command
D.the RMAN REPORT command
E.the RMAN CROSSCHECK command
F.the RMAN BLOCKRECOVER command
Answer: B
38. The DB_BLOCK_CHECKING initialization parameter is set to
FALSE. What level of block checking would be performed?
A.The Oracle database will not perform block checking for any of the
data blocks.
B.The Oracle database will perform block checking for the default
permanent tablespace only.
C.The Oracle database will perform block checking for the data blocks
in all user tablespaces.
D.The Oracle database will perform block checking for the data blocks
厚积薄发、精益求精
183 / 233
in the SYSTEM tablespace only.
E.The Oracle database will perform block checking for the data blocks
in the SYSTEM and SYSAUX tablespaces.
Answer: D
39. The DB_BLOCK_CHECKING initialization parameter is set to TRUE.
What would be the result of this setting on the data blocks being written
to the datafiles, every time the DBWn writes?
A.The Oracle database will check all data blocks by going through the
data on each block, making sure the data is selfconsistent.
B.DBWn and the direct loader will calculate a checksum and store it in
the cache header of every data block when writing it to disk.
C.The Oracle database will check data blocks belonging to the SYSTEM
tablespace only, by going through the data on each block, making sure
the data is selfconsistent.
D.The Oracle database will check data blocks belonging to the SYSAUX
tablespace only, by going through the data on each block, making sure
the data is selfconsistent.
E.The Oracle database will check data blocks in the SYSTEM and
SYSAUX tablespaces only, by going through the data on each block,
making sure the data is selfconsistent.
Answer: A
厚积薄发、精益求精
184 / 233
40. Your database is open and running in ARCHIVELOG mode. You
take RMAN full backups every Sunday night. On Monday morning,
while querying the user1.employees table, you receive the following
error message:
01578: ORACLE data block corrupted (file # 5, block # 51) ORA01110:
data file 5:'/u01/app/oracle/oradata/orcl/example01.dbf'
You need to rectify the corruption while ensuring the following:
The data file should remain online.
The mean time to recover (MTTR) should be minimal.
You are not using a backup control file, and all the archived logs are
accessible. Which option would you choose?
A.flash back the corrupted blocks
B.use the DBMS_REPAIR package
C.use the RMAN TSPITR command
D.use the RMAN BLOCKRECOVER command
E.use the RESTORE DATABASE and RECOVER DATABASE commands
F.investigate the time at which the corruption occurred and perform a
pointintime recovery
Answer: D
41.You are performing a block media recovery on the tools01.dbf
datafile in the SALES database by using RMAN. Which two statements
厚积薄发、精益求精
185 / 233
are correct in this scenario? (Choose two.)
A.You must ensure that the SALES database is mounted or open.
B.You must restore a backup control file to perform a block media
recovery.
C.You must take the tools01.dbf data file offline before you start a block
media recovery.
D.You must put the database in NOARCHIVELOG mode to perform a
block media recovery.
E.You can perform only a complete media recovery of individual blocks
; pointintime recovery of individual data blocks is not supported.
Answer: AE
42. You execute the following RMAN commands in the order shown
below:
BACKUP VALIDATE DATABASE;
BLOCKRECOVER CORRUPTION LIST;
What will these commands do?
A.create a backup of the database and recover all corrupted blocks
found in the backup
B.run a backup validation and list all the logically corrupt blocks as
well as physically corrupt blocks in the database
C.run a backup validation to populate V$COPY_CORRUPTION view,
厚积薄发、精益求精
186 / 233
and then list any corrupt blocks recorded in the view
D.run a backup validation to populate
V$DATABASE_BLOCK_CORRUPTION view, and then repair any
corrupt blocks recorded in the view
E.run a backup validation, repair any corrupt blocks found during the
validation process, and then update
V$DATABASE_BLOCK_CORRUPTION view to indicate which corrupt
blocks have been repaired
Answer: D
43. What are the two advantages of RMAN Block Media Recovery (BMR)
over filelevel recovery? (Choose two.)
A.BMR lowers the mean time to recover (MTTR).
B.BMR supports pointintime recovery of individual data blocks.
C.BMR enables you to use incremental backups for block recovery.
D.BMR enables recovery even when the database is not mounted or
open.
E.BMR enables you to use proxy backups to perform block media
recovery.
F.BMR enables increased availability of data during recovery because
the data file that requires a recovery can remain online.
Answer: AF
厚积薄发、精益求精
187 / 233
44. You want to migrate your existing nonASM 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
Answer: AE
45、You have an Automatic Storage Management (ASM) instance
managing the database files of yourproduction database. The database
contains ASM files and nonASM 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
厚积薄发、精益求精
188 / 233
Managed Files (OMF).
D.RMAN would relocate the ASM files to an ASM disk group and the
nonASM
files to an operating system location, defined using OMF.
Answer: A
46. 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 nonASM 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
Answer: B
47. An RMAN backup job fails, displaying the following error:
ORA19809:limit exceeded for recovery files
Your analysis shows that the Flash Recovery area is full. Which two
actions could you take to resolve the error and avoid this error in future?
(Choose two.)
A.take the backup of the flash recovery area less frequently
厚积薄发、精益求精
189 / 233
B.increase the value of the UNDO_RETENTION initialization parameter
C.change the value of the WORKAREA_SIZE_POLICY initialization
parameter to AUTO
D.change the backup retention policy to retain the backups for a
smaller period of time
E.increase the value of the DB_RECOVERY_FILE_DEST_SIZE
initialization parameter
F.increase the value of the CONTROL_FILE_RECORD_KEEP_TIME
initialization parameter
Answer: DE
48. An RMAN backup job fails, displaying the following error message:
ORA19809:limit exceeded for recovery files
Your analysis shows that the Flash Recovery area is full. What would
you do to resolve the error?
A.increase the value of UNDO_RETENTION
B.change the value of WORKAREA_SIZE_POLICY to AUTO
C.increase the value of DB_RECOVERY_FILE_DEST_SIZE
D.increase the value of CONTROL_FILE_RECORD_KEEP_TIME
Answer: C
49. The database is running in ARCHIVELOG mode and the FORMAT
option is not configured in RMAN for disk backups. You configured the
厚积薄发、精益求精
190 / 233
flash recovery area location and size by using the
DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE
initialization parameters.
Which statement is correct in this scenario?
A.If no other destination is configured for recovery files, then RMAN
places the control file autobackups in the flash recovery area by default.
B.If any local destinations for LOG_ARCHIVE_DEST_n are set, then
archived redo logs are stored in these destinations as well as in the flash
recovery area.
C.If the flash recovery area location is configured using the
DB_RECOVERY_FILE_DEST initialization parameter, then the RMAN
backup of Oracle managed datafiles (OMF) only will go to the flash
recovery area by default.
D.If the FORMAT option to the RMAN BACKUP command is specified,
then RMAN creates backup pieces and image copies in the flash
recovery area with names in the Oracle Managed Files (OMF) format.
Answer: A
50. You defined the Recovery Manager (RMAN) retention policy to
recovery window of 7 days by executing the following command:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW
OF 7 DAYS;
厚积薄发、精益求精
191 / 233
When a backup is performed, what would be the status of this backup
after seven days?
A.The backup would be marked as invalid.
B.The backup would be marked as obsolete.
C.The backup would be removed from the media.
D.The backup would be removed from the RMAN repository.
Answer: B
51. You set the recovery window to seven days and the backup
optimization to ON using the CONFIGURE command of Recovery
Manager (RMAN). The most recent backup of the TOOLS tablespace to
disk was taken on January 3. The TOOLS tablespace is readonly.
On February 21, when you execute a command to back up all the
tablespaces to disk, you find that RMAN backs up the TOOLS
tablespace also, even though the contents of the tablespace have not
changed after the backup on January 3.Because there are no changes
made to the TOOLS tablespace, you decide that the tablespace should
not be backed up by RMAN. What can you to do skip backing up the
TOOLS tablespace without changing the current backup optimization
setting?
A.configure a default device for RMAN backups
B.temporarily disable the retention policy for RMAN backups
厚积薄发、精益求精
192 / 233
C.configure automatic channel allocation for RMAN backups
D.use the CONFIGURE command to reconfigure the recovery window to
60 days
Answer: B
62. In Recovery Manager (RMAN), you have set control file autobackup
to ON by using the following command:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
Which two events would cause the control file to be backed up
automatically? (Choose two.)
A.a tablespace is taken offline
B.the database instance is restarted
C.a new data file is added to an existing tablespace
D.a successful backup is recorded in the RMAN repository
E.the RMAN connection is disconnected from the target database
Answer: CD
63. In Recovery Manager (RMAN), you set the retention policy to NONE
by executing the following command:
CONFIGURE RETENTION POLICY TO NONE;
What would be the impact of this setting?
A.The retention policy is cleared.
B.RMAN does not consider any backup as obsolete.
厚积薄发、精益求精
193 / 233
C.The retention policy is set to its default setting of redundancy 1.
D.The retention policy is set to its default setting of recovery window 7.
Answer: B
64 In the parameter file of your production database, the
CONTROL_FILE_RECORD_KEEP_TIME parameter is set to 31. What
would be the impact of this setting on the backup that is being
performed by using Recovery Manager (RMAN)?
A.The backup would be retained on the media for 31 days.
B.The backup would be maintained in the recovery catalog for 31 days.
C.The backup metadata would be retained in the control file for 31
days.
D.The backup metadata would be maintained in the recovery catalog for
31 days.
E.Automatic backups of the control file will be retained in the flash
recovery area for 31 days.
Answer: C
65. You observe that the senior database administrator always uses the
following command when connecting to Recovery Manager (RMAN), and
then starts executing commands to backup the database files:
$ rman target /
What would be the effect of this command?
厚积薄发、精益求精
194 / 233
A.RMAN would use the current operating system location to place the
backup.
B.RMAN would use the control file of the target database to maintain
the backup.
C.RMAN would use the recovery catalog of the target database to
maintain the backup.
D.RMAN would use the control file of the target database to maintain
the backup metadata.
E.RMAN would use the recovery catalog of the target database to
maintain the backup metadata.
Answer: D
66. You execute the following command to perform a backup by using
Recovery Manager (RMAN):
RMAN> run
{allocate channel c1 type sbt;
backup datafile 1;
}
The command errors out. A part of the error message is as follows:
RMAN03009:failure of allocate command on c1 channel
ORA19554:error allocating device, device type: SBT_TAPE, device
name:
厚积薄发、精益求精
195 / 233
What could explain why the statement failed?
A.sbt is not a valid device type.
B.Media Management Library was not loaded.
C.The sbt device cannot be used for data file backups.
D.No path was defined in the ALLOCATE CHANNEL command.
E.The channel is currently being used by another RMAN
Answer: B
67. View the Exhibit.
You executed the following command to perform a backup
RMAN> BACKUP TABLESPACE USERS;
Which type of backup would this command perform?
A.backup set
B.image copy
C.incremental backup
D.None; the user receives an error indicating that the backup type
must be specified.
Answer: A
厚积薄发、精益求精
196 / 233
68. View the Exhibit.
You executed the following command to perform a backup of the USERS
tablespace:
RMAN> BACKUP TABLESPACE USERS;
Which three types of files are backed up? (Choose three.)
A.password file
B.online redo log files
C.the current control file
D.archived redo log files
E.the current server parameter file
F.all the data files belonging to the USERS tablespace
G.data files belonging to the USERS tablespace and all the default
tablespaces
Answer: CEF
69. Your database is functioning in NOARCHIVELOG mode. Your
database contains 15 tablespaces.You want to use Recovery Manager
(RMAN) to perform backups.
厚积薄发、精益求精
197 / 233
Which two backups would you be able to perform when the database is
being accessed by users? (Choose two.)
A.backup of offline tablespaces
B.backup of readonly
tablespaces
C.backup of systemcritical
tablespaces
D.backup of online, locally managed tablespaces
E.backup of read/write dictionarymanaged
tablespaces
Answer: AB
70. When performing a backup using Recovery Manager (RMAN), which
four types of files can be backed up with the RMAN BACKUP command?
(Choose four.)
A.data file
B.password file
C.archivelog file
D.temporary file
E.online redo log file
F.the current control file
G.the tnsnames.ora file
厚积薄发、精益求精
198 / 233
H.current server parameter file
Answer: ACFH
71. Which two statements regarding compressed backups in Recovery
Manager (RMAN) are true?
(Choose two.)
A.Backup compression is disabled by default.
B.Backup compression applies to only backup sets.
C.Backup compression applies to only image copies.
D.Backup compression cannot be enabled using the CONFIGURE
command.
E.Backup compression can be used with both backup sets and image
copies.
F.Backup compression works only if the COMPATIBILITY parameter is
set to 9.0.0.0 or higher.
Answer: AB
72. Which statement correctly describes the change tracking writer
(CTWR) background process?
A.It keeps track of changes in the background processes.
B.It records the system change number (SCN) in the control file.
C.It writes audit records for user changes to the change tracking file.
D.It records any changes to the listener configuration in the listener.ora
厚积薄发、精益求精
199 / 233
file.
E.It records any changes to the database parameters in the change
tracking file.
F.It records the physical location of all the changes that are made to the
database in the change tracking file.
Answer: F
73. What purpose would you achieve by enabling the block change
tracking feature?
A.eliminate the necessity for backups
B.perform optimized image copy backups
C.perform optimized incremental backups
D.enable checkpoint (CKPT) to perform checkpointing at every block
change
E.enable database writer (DBWn) to write changed blocks to data files
faster
Answer: C
74. You are managing a database for which incremental backups can be
very time consuming. In order to perform optimized incremental
backups you enable the block change tracking feature.
How would you instruct RMAN to use the block change tracking feature
when performing incremental backups?
厚积薄发、精益求精
200 / 233
A.You need to mention it with every BACKUP command.
B.You need to configure it using the CONFIGURE command.
C.The block change tracking feature is used automatically by RMAN.
D.You need to set the necessary initialization parameters to direct
RMAN to use the feature.
Answer: C
75. You are managing an Oracle Database 10g database that uses
Oracle Managed Files (OMF). You enabled the block change tracking
feature for the database.
Which statement regarding the change tracking file is true?
A.One block change tracking file is created for each data file. By default,
the file is created in DB_CREATE_FILE_DEST.
B.One block change tracking file is created for each data file. By default,
the file is created in BACKGROUND_DUMP_DEST.
C.One block change tracking file is created for the database. By default,
the file is created in DB_CREATE_FILE_DEST.
D.One block change tracking file is created for the database. By default,
the file is created in BACKGROUND_DUMP_DEST.
Answer: C
76. You executed the following command in Recovery Manager (RMAN)
against your Oracle 10g database:
厚积薄发、精益求精
201 / 233
RMAN> REPORT NEED BACKUP days 3;
What would be the output of this command?
A.a list of files that require a backup within three days
B.a list of files that have not been backed up for three days
C.a list of files that RMAN recommends be backed up only once in every
three days, based on low volatility
D.a list of files for which a backup has already been performed and may
need a backup after three days
Answer: B
77. Using the LIST command in Recovery Manager (RMAN), which two
pieces of information from the RMAN repository can be listed? (Choose
two.)
A.backups that can be deleted
B.stored scripts in the recovery catalog
C.backup sets and image copies that are obsolete
D.only the most recent backup sets or image copies
E.backups that do not have the AVAILABLE status in RMAN repository
Answer: BE
78. The backup retention policy is configured as RECOVERY WINDOW
2. You executed the following command in Recovery Manager (RMAN)
against your database:
厚积薄发、精益求精
202 / 233
RMAN> REPORT OBSOLETE;
What would you see in the output?
A.a list of all the expired backups and copies
B.a list of all those files that have been deleted in the last two days
C.a list of all those files that have been recovered within the last two
days
D.a list of backups and copies that are outside the range covered by the
current retention policy
Answer: D
79. In your database, all the tablespaces are locally managed. You
started Recovery Manager (RMAN) using recovery catalog and restored
the control file by using the following command:
RMAN> RESTORE CONTROLFILE;
Which two operations do you need to perform after restoring the control
file from backup? (Choose two.)
A.shut down and restart the instance
B.add new tempfiles to the temporary tablespaces after recovery
C.perform a media recovery and open the database with the
RESETLOGS option
D.perform a media recovery and bring the database to NOARCHIVELOG
mode
厚积薄发、精益求精
203 / 233
Answer: BC
80. You used the following command in Recovery Manager (RMAN) as
part of the recovery process:
RESTORE CONTROLFILE FROM AUTOBACKUP;
How does RMAN find the control file autobackup? (Choose all that
apply.)
A.by using the trace file
B.by using the alert log file
C.by using the database ID
D.by using the server parameter file
E.by using the V$CONTROLFILE view
F.by using the autobackup format configuration setting
Answer: CF
81. You are connected to Recovery Manager (RMAN) without a recovery
catalog. There is no copy of the control file available. You want to restore
the control file from an autobackup. To retrieve the autobackup,you
need the database ID (DBID).
In which two sources would you find the DBID? (Choose two.)
A.the trace file
B.the alert log file
C.an RMAN session log file
厚积薄发、精益求精
204 / 233
D.the server parameter file
E.the formatted name of a control file autobackup
Answer: CE
82. Your database operates in ARCHIVELOG mode. Usermanaged
consistent backups are performed every Sunday night.On Tuesday,
client A drops a table at 8:00 a.m. as follows:
SQL> DROP TABLE cust_ord PURGE;
Table dropped.
Client B executes a query at 9:00 a.m. on the same table as follows:
SQL> SELECT * FROM cust_ord;
SELECT * FROM cust_ord
ERROR at line 1:ORA00942:
table or view does not exist
Client B needs the dropped table and reports the problem to you.
With reference to this scenario, which action should you take?
A.retrieve the table by using the flashback feature
B.restore all the data files from last Sunday's backup, and then perform
a timebased recovery
C.restore all the data files from last Sunday’s backup, and then
perform a log sequence recovery
D.restore all the data files from last Sunday’s backup, and then
厚积薄发、精益求精
205 / 233
perform a cancelbased recovery
E.restore from last Sunday’s backup only data files that belong to the
tablespace in which the table was stored, and then perform a complete
recovery
Answer: B
83. Your database operates in ARCHIVELOG mode and usermanaged
consistent backups are performed every Sunday night. On Tuesday, at
9:00 a.m. the current log sequence number was 369. Also,on Tuesday
you lost the data file belonging to the SYSTEM tablespace and an
archivelog file (sequence number 356) that contained redo entries
between 8:00 a.m. and 8:30 a.m.With reference to this scenario, what
would you do to recover the database?
A.restore all the data files from last Sunday's backup, and then perform
a timebased recovery
B.restore al’ the data files from last Sunday's backup, and then
perform a cancelbased’recovery
C.restore all the data files from last Sunday's backup, and then perform
a changebased’recovery
D.restore only data files that belong to the SYSTEM tablespace from last
Sunday's backup, and then perform a complete re’overy
Answer: B
厚积薄发、精益求精
206 / 233
84. You have only two redo log groups and these groups get overwritten
several times in a day. Your database operates in NOARCHIVELOG
mode and usermanaged consistent backups are performed every
Sunday night. On Thursday morning, you find that an important table
has been dropped and purged.
With reference to this scenario, up to what point in time can the data be
recovered?
A.until Thursday morning
B.until last Sunday's backup
C.until Wednesday evening
D.until that point in time when the database is opened
E.until that point in time when the recovery is performed
F.until that point in time when the table is dropped and purged
Answer: B
85. Your database is functioning in ARCHIVELOG mode. In which two
situations would you perform a cancelbased recovery? (Choose two.)
A.You find that one of the redo log members in each redo log group is
lost.
B.You find that a data file that belongs to the USERS tablespace is
damaged.
C.You find that a data file that belongs to the SYSTEM tablespace is
厚积薄发、精益求精
207 / 233
damaged.
D.You find that the current redo log group is damaged and is not
available for recovery.
E.You realized while performing a recovery that an archived redo log file
needed for recovery is lost.
Answer: DE
86. The current time is 12:00 noon. You want to recover the USERS
tablespace from a failure that occurred at 11:50 a.m. You discover that
the only member of an unarchived redo log group containing
information from 11:40 a.m. onwards is corrupt.
With reference to this scenario, if you are not using Recovery Manager
(RMAN), which recovery method would you use?
A.timebased recovery
B.log sequence recovery
C.cancelbased recovery
D.changebased recovery
Answer: C
87. You performed an incomplete recovery on your Oracle Database 10g
database using the following command:
SQL> RECOVER DATABASE
2 UNTIL TIME '20040805:12:10:03'
厚积薄发、精益求精
208 / 233
3 USING BACKUP CONTROLFILE;
In which situation would the above recovery be required?
A.A user table was lost at the specified time.
B.A redo log file was lost at the specified time.
C.The backup control file is on a faster disk than the current one.
D.The current control file has a different path compared with the
intended time of recovery.
E.The current control file does not match the physical structure of the
database at the intended time of recovery.
Answer: E
88. Consider the recovery steps in each of the cases given below:
Case 1:
1 Shut down the database and perform a backup.
2 Restore all the data files.
3 Mount the database.
4 Recover the database.
5 Without applying all the redo log files, open the database using the
RESETLOGS option.
6 Back up the database.
Case 2:
1 Shut down the database and perform a backup.
厚积薄发、精益求精
209 / 233
2 Restore only the data files of the tablespace where user error
damaged the data.
3 Mount the database.
4 Recover the database.
5 Open the database with the RESETLOGS option.
6 Back up the database.
Case 3:
1 Shut down the database
2 Restore the data files
3 Open the database
4 Perform recovery to the current point in time.
Which case has the correct steps for an incomplete recovery?
A.only case 1
B.only case 2
C.only case 3
D.case 1 and 2
E.case 2 and 3
F.case 1 and 3
G.all cases: case 1, case 2 and case 3
Answer: A
89. For an incomplete recovery, which four backup types can be used
厚积薄发、精益求精
210 / 233
by Recovery Manager (RMAN) to restore data files? (Choose four.)
A.RMAN image copies
B.RMAN database backups
C.RMAN tablespace backups
D.user managed backups placed in the flash recovery area
E.user managed backups that have been cataloged with RMAN
F.usermanaged data file backups for which the full path name is
specified
Answer: ABCE
90. Your database operates in ARCHIVELOG mode. The redo log files
are not multiplexed and one of the online redo logs is missing. The
missing redo log sequence, 230, is not archived, and it contained
information from 10:35 a.m. onwards.
The current time is 11:00 a.m. Because of a disk crash, you executed
the following command to perform an incomplete recovery:
RMAN> RUN {
2> SET UNTIL SEQUENCE 230 THREAD 1;
3> ALTER DATABASE MOUNT;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> ALTER DATABASE OPEN RESETLOGS;
厚积薄发、精益求精
211 / 233
7> };
With reference to this scenario, which statement is true?
A.RMAN recovers up to log sequence 230, but not including 230.
B.RMAN returns an error because the log sequence number mentioned
in the command should be 229.
C.RMAN returns an error because the log sequence number mentioned
in the command may never be missing.
D.RMAN recovers up to and including log sequence 229 but then
returns an error because log sequence 230 is missing.
Answer: A
91. Your database operates in ARCHIVELOG mode and all the
tablespaces are online. Due to a usererror, you decided to perform an
incomplete recovery.
Which two tasks would you be required to perform in the recovery
process? (Choose two.)
A.open the database with the RESETLOGS option
B.open the database with the RESTRICTED option
C.recover all the data files to the same system change number (SCN)
D.recover to the required SCN only that data file in which the error
occurred
Answer: AC
厚积薄发、精益求精
212 / 233
92. View the Exhibit.
One of the important tables in the USERS tablespace was dropped and
purged from the recycle bin at 9:00 a.m. You noticed this at 11:00 a.m.
and you want to perform an incomplete recovery to recover the
table.Which statement is true?
A.You must recover all data files to the required system change number
(SCN).
B.You must recover all data files, except those that are offline, to the
required SCN .
C.You must recover all data files belonging to the USERS tablespace to
the required SCN.
D.You cannot recover all data files because segment space management
is performed manually for the undo tablespace.
Answer: B
93. You are using Oracle Database 10g. Which statement regarding an
incomplete recovery is true?
厚积薄发、精益求精
213 / 233
A.You do not need to restore all the data files.
B.You do not need to open the database with the RESETLOGS
operation.
C.You do not need to perform a full backup after the RESETLOGS
operation.
D.You do not need to recover all the data files to the same system
change number (SCN).
Answer: C
94. The loss of which two types of files may require a recovery with the
RESETLOGS option? (Choose two.)
A.control files
B.password file
C.archived log files
D.systemcritical
data files for which all the redo entries are present E.nonsystemcritical
data files for which all the redo entries are present
Answer: AC
95. You are working on Oracle Database 10g, which is in ARCHIVELOG
mode. All the archived log files are intact.In which scenario would
performing a recovery require the opening of the database with the
RESETLOGS option?
厚积薄发、精益求精
214 / 233
A.loss of a tempfile
B.loss of a system data file
C.loss of one of the control files
D.loss of one of the tablespaces
E.loss of a member from each redo log group
F.loss of the only member of an unarchived redo log group
Answer: F
96. Every Sunday, consistent backups are performed on your database.
Because of a user error, you performed an incomplete recovery on
Tuesday and opened the database with the RESETLOGS option.A user
error occurs again on Thursday, which necessitates an incomplete
recovery. Sunday's backup is the most recent backup available.’What
would you do in this scenario?
A.recovery cannot be performed because a backup was not performed
after the last incomplete recovery
B.restore all the files from Sunday's backup, and then perform an
incomplete reco’ery up to the point in time when the user error
occurred on Thursday
C.restore all the files from Sunday's backup, and then recover up to the
point in’time when the RESETLOGS operation was performed on
Tuesday
厚积薄发、精益求精
215 / 233
D.restore all the files from Sunday's backup, and open the database to
reset the ’atabase to the point in time when the backup was performed
on Sunday
Answer: B
97. You are working on an Oracle Database 10g database that operates
in the ARCHIVELOG mode. At
06:00 p.m., you discovered that a table in the SYSTEM tablespace that
does not belong to the data dictionary was dropped at 03:00 p.m. What
would you do to recover the table?
A.perform pointintime
recovery to recover the table
B.recover the table by using the Flashback Table feature
C.restore the entire database from the most recent backup and open it
D.use the Flashback Transaction Query feature to identify the relevant
SQL statement and use it to undrop the table
Answer: A
98. A data file became corrupted in your database due to bad sectors on
the disk. Because of corruption,you lost all the important tables in that
data file.
Which method would you use for recovery?
A.flash back all the tables in the data file, one by one
厚积薄发、精益求精
216 / 233
B.restore the data file to a new location and perform a media recovery
C.flash back the database; there is no need to restore the data file
D.restore the data file from the most recent backup and flash back the
database
Answer: B
99. On Tuesday, a junior DBA dropped an important application user
account, whose schema has important tables. You are asked to recover
all the objects in the schema. On investigation, you find that the user
account was dropped at 11:00 a.m. and Sunday's backup is the most
recent backup.
Which fla’hback feature would you use?
A.Flashback Drop
B.Flashback Table
C.Flashback Database
D.Flashback Version Query
E.Flashback Transaction Query
Answer: C
100. You are working on an Oracle Database 10g database. Because of
data loss, you decided to perform a Flashback Database operation
using the following command:
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE 5/24);
厚积薄发、精益求精
217 / 233
Which two statements are true? (Choose two.)
A.SYSDATE should not be used with TIMESTAMP.
B.The database must have multiplexed redo log files.
C.The database must be in the MOUNT state to execute the command.
D.The database must be opened with the RESETLOGS option after the
flashback operation.
E.The entire database needs to be restored from the most recent backup
before the flashback operation.
Answer: CD
101. You are working on an Oracle Database 10g database. For which
purposes would you use the Flashback Transaction Query feature?
(Choose all that apply.)
A.to recover a dropped table
B.to recover a dropped schema
C.to recover data from a truncated table
D.to view changes made by all the transactions during a given period of
time
E.to view changes made by a single transaction during a given period of
time
Answer: DE
102. View the Exhibit and examine the Flashback Database
厚积薄发、精益求精
218 / 233
architecture.Identify the missing component (shown with a "?") in the
Flashback Database architecture.
A.DBWn
B.RVWR
C.ARCH
D.RECO
E.TRWR
Answer: B
103. What are the prerequisites for enabling Flashback Database?
(Choose all that apply.)
A.The database must be in the NOMOUNT state.
B.The database must have multiplexed control files.
C.The database must be in ARCHIVELOG mode.
厚积薄发、精益求精
219 / 233
D.The database must be opened in RESTRICTED mode.
E.The database must be started in the MOUNT EXCLUSIVE state.
F.The database must be opened by using a textbased parameter file
instead of a server parameter file (SPFILE).
Answer: CE
104. You are working on an Oracle Database 10g database. You
enabled the Flashback Database feature.Which two statements
regarding flashback logs are true? (Choose two.)
A.Flashback logs are not archived.
B.Flashback logs are maintained in redo log files.
C.Flashback logs are maintained in the Flash Recovery Area.
D.Flashback logs are used to maintain Flashback Databaserelated
errors.
E.Flashback logs need to be cleared manually after you disable
Flashback Database.
Answer: AC
105. You want to configure the Flashback Database feature and retain
flashback logs for three days.The steps used in this process are:
A.Set the retention target:
SQL> ALTER SYSTEM SET
DB_FLASHBACK_RETENTION_TARGET=4320;
厚积薄发、精益求精
220 / 233
B.Enable Flashback Database:
SQL> ALTER DATABASE FLASHBACK ON;
C.Mount the database:
SQL> STARTUP MOUNT EXCLUSIVE;
D.Shut down the instance:
SQL> SHUTDOWN IMMEDIATE;
E. Open the database:
SQL> ALTER DATABASE OPEN;
In what sequence would you need to perform these steps to enable the
Flashback Database feature?
A.D, C, A, B, E
B.D, A, C, B, E
C.B, D, C, E, A
D.D, B, E, C, A
Answer: A
106 In which case would you use the Flashback Database feature to
perform a recovery?
A.to recover a dropped tablespace
B.to recover data from a truncated table
C.to recover a data file that has been shrunk
D.to recover a database to the state that existed before the RESETLOGS
厚积薄发、精益求精
221 / 233
operation
Answer: B
107. You disabled the Flashback Database feature by using the
following command:
SQL> ALTER DATABASE FLASHBACK OFF;
What would be the effect of this command on the existing flashback
logs?
A.Flashback logs are not deleted.
B.Flashback logs are deleted automatically.
C.Flashback logs are deleted only if you are using Recovery Manager
(RMAN).
D.Flashback logs are deleted only if you are using Oracle Managed Files
(OMF).
E.Flashback logs are deleted only if you are using Automatic Storage
Management (ASM).
Answer: B
108. You executed the following query in your database:
FROM V$FLASHBACK_DATABASE_LOG;
What would you determine from the output?
A.the time when the last flashback operation in your database was
performed
厚积薄发、精益求精
222 / 233
B.the time when the first flashback operation in your database was
performed
C.a list of flashback operations performed in your database using SCN
and time
D.the approximate time and the lowest system change number (SCN) to
which you can flash back yourdatabase
Answer: D
109. These are the details about V$FLASHBACK_DATABASE_STAT:
SQL> DESC V$FLASHBACK_DATABASE_STAT
Name Null? Type
BEGIN_
TIME DATE
END_TIME DATE
FLASHBACK_DATA NUMBER
DB_DATA NUMBER
REDO_DATA NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
Which two statements regarding the V$FLASHBACK_DATABASE_STAT
view are true? (Choose two.)
A.BEGIN_TIME is the time at which Flashback logging is enabled.
B.END_TIME is the time at which the query is executed on the view.
厚积薄发、精益求精
223 / 233
C.REDO_DATA is the number of bytes of redo data written during the
interval.
D.This view contains information about flashback data pertaining to
the last 24 hours.
E.FLASHBACK_DATA is the amount of flashback data generated since
the database was opened.
Answer: CD
110. You decided to change the location of the flash recovery area. You
executed the following command:
Which statement is true?
A.The location can be changed only if the flash recovery area is empty.
B.After executing the command, all new flash recovery area files will be
created in the new location.
C.The command will generate an error because you cannot change the
flash recovery area while the database is open.
D.All the existing files in the flash recovery area will be moved to the
new location, and all new Flash Recovery Area files will be created in the
new location.
Answer: B
111. You enabled the flashback logging feature, but you have not
specified the location of the flash recovery area.
厚积薄发、精益求精
224 / 233
Which location would be used as the default location for the flash
recovery area?
A.the location used for control files
B.the location used for redo log files
C.the location set by using $ORACLE_BASE
D.the location set by using $ORACLE_HOME
E.the location set by using CORE_DUMP_DEST
F.the location set by using BACKGROUND_DUMP_DEST
Answer: C
112. You configured the flash recovery area in the database. Which two
files would you expect to find in the flash recovery area? (Choose two.)
A.backup pieces
B.control file autobackups
C.copies of all the parameter files
D.trace file generated using BACKUP CONTROLFILE TO TRACE
Answer: AB
113. You performed a backup of the flash recovery area by using the
following command for the first time:
RMAN> BACKUP RECOVERY AREA;
Which files in the flash recovery area are backed up? (Choose all that
apply.)
厚积薄发、精益求精
225 / 233
A.flashback logs
B.current control file
C.control file autobackups
D.incremental backup sets
Answer: CD
厚积薄发、精益求精
226 / 233
第二十一章网络配置
（关键字：NETCA、LISTENER、TNSNAMES）
1. You plan to use static database registration for a new listener when
you create it. What could be the two reasons for this? (Choose two.)
A. More than one database is to be registered with the listener.
B. The users will connect the database by using the host naming
method.
C. The Oracle Enterprise Manager is to be used to monitor an Oracle9i
database.
D. The database that is to be registered with the listener is configured in
shared server mode.
E. The listener is not configured on the default port of 1521 and the
instance is not configured to register with a nondefault port.
Answer: CE
2. Which three pieces of information are to be mandatorily 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
厚积薄发、精益求精
227 / 233
D. the log file and trace file destination for the listener
E. the database services to be registered with the listener
Answer: ABC
3. Which naming method uses the tnsnames.ora file to store the
connect descriptor used by the client
while connecting to the database instance from a remote machine?
A. host naming method
B. local naming method
C. external naming method
D. directory naming method
Answer: B
4. 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)
)
厚积薄发、精益求精
228 / 233
)
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.
Answer: C
5. Your database is open and users are connected using the LISTENER
listener. The new DBA of the system stops the listener by using the
following command:
LSNRCTL> STOP
What would happen to the sessions that are presently connected to the
database instance?
A. The sessions are able to perform only queries.
B. The sessions are not affected and continue to function normally.
厚积薄发、精益求精
229 / 233
C. The active transactions are rolled back and the sessions get
terminated.
D. The sessions are not allowed to perform any operations till the
listener is started.
Answer: B
6. Your database is started with SPFILE. You want the database
instance to be dynamically registered
with a listener L2 with the following details:
Protocol: TCP
Host: indl151e
Port: 1525
Which is the correct order of the steps that you would follow to achieve
this?
1. Set the LOCAL_LISTENER parameter to L2 dynamically.
2. Make an entry for L2 in tnsnames.ora on the database server.
3. Restart L2.
4. Modify the listener.ora file to add the instance name in SID_LIST of
L2.
A. 1, 2, 4, 3
B. 1, 2, 3; 4 is not required.
C. 2, 1; 3 and 4 are not required.
厚积薄发、精益求精
230 / 233
D. 1, 2; 3 and 4 are not required.
Answer: C
7. Your database is not configured for session failover. Your
tnsnames.ora file contains the following
details:
test.us.oracle.com=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=off)
(FAILOVER=ON)
(ADDRESS=(PROTOCOL=tcp)(HOST=test1server)(
PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=test2server)(
PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=test.us.oracle.com)))
Which feature is enabled in this case?
A. Load balancing
B. Instance failover
C. Database failover
D. Connecttime
failover
厚积薄发、精益求精
231 / 233
E. Transparent Application Failover (TAF)
Answer: D
8. You want your listener to be password protected to prevent it from
being shut down. You want to accomplish this task while the listener is
functional. Which method could you use to achieve this objective?
A.use the CHANGE_PASSWORD command of the Listener control
utility
B.use the SET PASSWORD and SAVE_CONFIG commands of the
Listener control utility
C.use the CHANGE_PASSWORD and SAVE_CONFIG commands of the
Listener control utility
D.manually modify the listener.ora file to include the password for the
listener and restart the listener
Answer: C
9. You have two listener processes, named L1 and L2. Both the listener
processes are currently functional. While viewing the listener.ora file on
your database server, you find the following entry:
PASSWORDS_L1 = (1sanfrjp43)
What would be the impact of this setting?
A.The listener process named L1 will disconnect the unauthorized
users.
厚积薄发、精益求精
232 / 233
B.The listener process named L1 will be prevented from being started by
unauthorized users.
C.The listener process named L1 will be prevented from being stopped
by unauthorized users.
D.The listener process named L1 will be prevented from being reloaded
by unauthorized users.
E.The listener process named L1 will be prevented from accepting
connections from unauthorized users.
Answer: C
10. You were recently hired by an organization as a database
administrator. You are asked to find out whether the currently
functional listener process, named L1, has been password protected or
not. You are not familiar with the file system of the new organization.
How would you accomplish the task?
A.by using the STATUS command of the Listener control utility
B.by using the STATUS L1 command of the Listener control utility
C.by using the SHOW RULES command of the Listener control utility
D.by using the SET PASSWORD command of the Listener control utility
E.by searching for the PASSWORD_LISTENER entry in the listener.ora
file
Answer: B
厚积薄发、精益求精
233 / 233

ocp 认证 分类

ocp 认证分类