NIIT Oracle Test Sample Papers
Here I am giving you question paper for NIIT oracle test in zip file attached with it so you can get it easily…. Some content is given below :
QUESTION 1:
Which six files are maintained in the Flash Recovery Area? (Select SIX)
A. control file
B. RMAN files
C. password file
D. parameter file
E. flashback logs
F. data file copies
G. core dump files
H. archived log files
I. RMAN recovery scripts
J. control file autobackpus
Answer: A, B, E, F, H, J
Explanation:
The Flash Recovery Area is a unified storage location for all recovery-related files and activities in an Oracle
Database. It includes Control File, Archived Log Files, Flashback Logs, Control File Autobackups, Data
Files, and RMAN files.
QUESTION 2:
The Automatic Database Diagnostic Monitor (ADDM) analysis runs every 60 minutes on your database.
Your database if facing a series of interrelated problems over a period of two hours. You need to ensure that
the ADDM analysis is run over a time span of two hours in future.
What would you do?
A. Create two custom ADDM tasks.
B. Modify the AWR snapshot time interval to two hours.
C. Create a new scheduler window for a time period of two hours.
D. Modify the time interval by using the DBMS_JOB.INTERVAL procedure.
E. Modify the Automatic Workload Repository (AWR) snapshot retention period to two hours.
Answer: B
Explanation:
The Automatic Database Diagnostic Monitor (ADDM) provides a holistic tuning solution. ADDM analysis
can be performed over any time period defined by a pair of AWR snapshots taken on a particular instance.
Analysis is performed top down, first identifying symptoms and then refining them to reach the root causes of
performance problems. The goal of the analysis is to reduce a single throughput metric called DB time. DB
time is the cumulative time spent by the database server in processing user requests. It includes wait time and
CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and
V$SYS_TIME_MODEL views.
QUESTION 3:
Which four statements regarding the Clone Database tool are correct? (Select FOUR)
A. It clones Oracle databases from release 8.1.7 or later.
B. It clones a source database while the database is in NOMOUNT state.
C. It clones a source database while the database is in MOUNT state.
D. It clones an Oracle database by using Recovery Manager (RMAN).
E. It clones a source database at the specified Oracle home and starts the new database instance in the open
mode.
F. It clones a source database and makes the new database instance consistent with the source database up to
the backup time of archived log.
Answer: A, C, D, F
Note: The question is wrong. There isn't a "CLONE DATABASE tool". There are only DBCA and RMAN
tools to clone a database.
Explanation:
Observation: If we want to clone a database using the DBCA, first we create a database template; after that
doesn’t matter if the database source is in NOMOUNT or MOUNT state.
Cloning Databases Using DBCA
The "Template Management" section of the Database Configuration Assistant (DBCA) can be used to clone
databases. The following method creates a clone of and existing database including both the structure and the
data:
1. Start the Database Configuration Assistant (DBCA).
2. On the "Welcome" screen click the "Next" button.
3. On the "Operations" screen select the "Manage Templates" option and click the "Next" button.
4. On the "Template Management" screen select the "Create a database template" option and select the "From
and existing database (structure as well as data)" sub-option then click the "Next" button.
5. On the "Source database" screen select the relevant database instance and click the "Next" button.
6. On the "Template properties" screen enter a suitable name and description for the template, confirm the
location for the template files and click the "Next" button.
7. On the "Location of database related files" screen choose either to maintain the file locations or to convert
to OFA structure (recommended) and click the "Finish" button.
8. On the "Confirmation" screen click the "OK" button.
9. Wait while the Database Configuration Assistant progress screen gathers information about the source
database, backs up the database and creates the template. By default the template files are located in the
"$ORACLE_HOME/assistants/dbca/templates" directory.
QUESTION 4:
The ______ identifies and helps to resolve performance problems relating to the execution of SQL statements
by recommending which indexes, materialized views, or materialized view logs to create, drop, or retain.
A. Undo Advisor
B. MTTR Advisor
C. Memory Advisor
D. Segment Advisor
E. SQL Tuning Advisor
F. SQL Access Advisor
Answer: F
Explanation: The SQL Access Advisor helps you achieve your performance goals by recommending the
proper set of materialized views, naterialized view logs, and indexes for a given workload. The SQL Access
Advisor recommends bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response
time for many types of ad hoc queries and reduced storage requirements compared to other indexing
techniques. B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys.
Another component of the SQL Access Advisor also recommends how to optimize materialized views so that
they can be fast refreshable and take advantage of general query rewrite.
QUESTION 5:
In the server parameter file (SPFILE), the UNDO_TABLESPACE initialization parameter is set to
UNDOTBS. You executed the following SQL statement to rename the UNDOTBS undo tablespace: ALTER
TABLEPACE undobts RENAME TO undobts_old;
Which statement is correct in this scenario?
A. The tablespace will be renamed but the data file headers will not be updated.
B. The above SQL statement will fail because you cannot rename an undo tablespace.
C. The tablespace will be renamed and all the changes will be logged in the alert log file.
D. The tablespace will be renamed but a message will be added to the alert log file indicating that you should
change the corresponding initialization parameter files.
E. To be able to rename the UNDOTBS undo tablespace, you would need to set the UNDO_TABLESPACE
initialization parameter to some other tablespace name and then execute the above SQL command.
Answer: C
Explanation:
Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary
tablespace. The COMPATIBLE parameter must be set to 10.0 or higher. If the tablespace is an undo
tablespace and if the following conditions are met, then the tablespace name is changed to the new tablespace
name in the server parameter file (SPFILE).
- The server parameter file was used to start up the database.
- The tablespace name is specified as the UNDO_TABLESPACE for any instance.
If a traditional initialization parameter file (PFILE) is being used then a message is written to the alert log
stating that the initialization parameter file must be manually changed.
Reference:
Oracle(r) 10g Administrator Guide , 8-19
QUESTION 6:
Identify four uses of the Oracle Scheduler. (Select FOUR)
A. Enables you to set idle time limits for a resource plan.
B. Enables you to schedule job execution based on time.
C. Enables you to execute jobs in a clustered environment.
D. Enables you to assign priorities to the consumer groups.
E. Enables you to map a consumer group to an Oracle user.
F. Enables you to create a job that makes use of saved programs and schedules.
G. Enables you to periodically execute operating system script files located on the same server as the
database.
Answer: B, C, G, F
Explanation:
A - FALSE. Isn't an Oracle scheduler capability.
B - TRUE. Schedule job execution based on time or events. The most basic capability of a job scheduler is the
ability to schedule a job to run at a particular date and time or when a particular event occurs.
C - TRUE. Execute and manage jobs in a clustered environment. A cluster is a set of database instances that
cooperates to perform the same task. Oracle Real Application Clusters (RAC) provides scalability and
reliability without any change to your applications. The Scheduler fully supports execution of jobs in such a
clustered environment. To balance the load on your system and for better performance, you can also specify
the database service where you want a job to run.
D - FALSE. The Scheduler activity is logged and information such as the status of the job and the last run
time of the job can be easily tracked, this way the DBA would track witch particular JOB time and statistics,
not consumer groups.
F - TRUE. For example, consider the situation where a patch needs to be applied to a database that is in
production. To minimize disruptions, this task will need to be performed during non-peak hours. This can be
easily accomplished using the Scheduler. Instead of having IT personnel manually carry out this task during
non-peak hours, you can instead create a job and schedule it to run at a specified time using the Scheduler.
G - TRUE. Application developers can create programs and program libraries that end users can use to create
or monitor their own jobs. In addition to typical database jobs, you can schedule and monitor jobs that run as
part of an application suite.
QUESTION 7:
You are using SQL Tuning Advisor (STA) to tune SQL workload in your database.
Which four types of recommendations do you get from Automatic Tuning Optimizer (ATO) via the STA?
(Select FOUR)
A. recommendation to create indexes
B. recommendation to use stored outlines
C. recommendation to create a SQL profile
D. recommendation to gather relevant statistics
E. recommendation to denormalize the schema
F. recommendation to restructure SQL statements
Answer: A, C, D, F
Explanation:
For the A answer see the point n°3.
For the C answer see the point n°2.
Oracle 10g allows the optimizer to run in tuning mode where it can gather additional information and make
recommendations about how specific statements can be tuned further. This process may take several minutes
for a single statement so it is intended to be used on high-load resource-intensive statements. In tuning mode
the optimizer performs the following analysis:
1. Statistics Analysis - The optimizer recommends the gathering of statistics on objects with missing or stale
statistics. Additional statistics for these objects are stored in an SQL profile.
2. SQL Profiling - The optimizer may be able to improve performance by gathering additional statistics and
altering session specific parameters such as the OPTIMIZER_MODE. If such improvements are possible the
information is stored in an SQL profile. If accepted this information can then used by the optimizer when
running in normal mode. Unlike a stored outline which fixes the execution plan, an SQL profile may still be
of benefit when the contents of the table alter drastically. Even so, it's sensible to update profiles periodically.
The SQL profiling is not performed when the tuning optimizer is run in limited mode. The SQL profile
doesn't recommend to create a SQL profile.
3. Access Path Analysis - The optimizer investigates the effect of new or modified indexes on the access path.
It's index recommendations relate to a specific statement so where necessary it will also suggest the use of the
SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
4. SQL Structure Analysis - The optimizer suggests alternatives for SQL statements that contain structures
that may impact on performance. The implementation of these suggestions requires human intervention to
check their validity. Access Path Analysis - The optimizer investigates the effect of new or modified indexes
on the access path. It's index recommendations relate to a specific statement so where necessary it will also
suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL
workload.
QUESTION 8:
You work as a database administrator for Pass .com. You started the instance using the init.orafile. You have
two control files and three redo log groups in your database. You decided to protect the database against
failures by adding one more control file.
Which file is the appropriate way to perform this task?
A. Shut down the instance, copy the control file to the third location and open the database.
B. Abort the instance, copy the control file to the third location, modify the CONTROL_FILES parameter in
the init.ora file and open the database.
C. Copy the control file to the third location, modify the CONTROL_FILES parameter in the init.ora file,
restart the instance and open the database.
D. Shut down the instance, copy the control file to the third location, modify the CONTROL_FILES
parameter in the init.ora file and open the database.
E. Shut down the instance, startup in the mount state, copy the control file to the third location, modify the
CONTROL_FILES parameter in init.ora and open the database.
Answer: D
Explanation:
You can create an additional control file copy for multiplexing by copying an existing control file to a new
location and adding the file name to the list of control files. Similarly, you rename an existing control file by
copying the file to its new name or location, and changing the file name in the control file list. In both cases,
to guarantee that control files do not change during the procedure, shut down the database before copying the
control file.
To add a multiplexed copy of the current control file or to rename a control file:
1. Shut down the database
2. Copy an existing control file to a new location, using operating system commands.
3. Edit the CONTROL_FILES parameter in the database initialization parameter file to add the new control
file name, or to change the existing control filename.
4. Restart the database.
QUESTION 9:
Which four tablespaces can be renamed? (Select FOUR)
A. an undo tablespace
B. an offline tablespace
C. a read-only tablespace
D. a temporary tablespace
E. the SYSTEM tablespace
F. the default permanent tablespace for the non-SYSTEM users
Answer: A, C, D, F
Explanation: Besides any other, The COMPATIBLE parameter must be set to 10.0 or higher.
A - True. UNDO Tablespace. If the tablespace is an undo tablespace and if the following conditions are met,
then the tablespace name is changed to the new tablespace name in the server parameter file (SPFILE).
- The server parameter file was used to start up the database.
- The tablespace name is specified as the UNDO_TABLESPACE for any instance.
If a traditional initialization parameter file (PFILE) is being used then a message is written to the alert log
stating that the initialization parameter file must be manually changed.
B - False. Offline Tablespaces. If any datafile in the tablespace is offline, or if the tablespace is offline, then
the tablespace is not renamed and an error is raised.
C - True. Read-only Tablespace. If the tablespace is read only, then datafile headers are not updated. This
should not be regarded as corruption; instead it cause a message to be written to the alert log
indicating that datafile headers have not been renamed. The data dictionary and control file are updated.
D - True.Temporary Tablespace.If the tablespace is the default temporary tablespace, then the corresponding
entry in the database properties table is updated and the DATABASE_PROPERTIES view shows the new
name.
E - False. SYSTEM Tablespace. If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX
tablespace, then it will not be renamed and an error is raised.
F - True. When you rename a tablespace the database updates all references to the tablespace name in the data
dictionary, control file, and (online) datafile headers. The database does not change the tablespace ID so if
this tablespace were, for example, the default tablespace for a user, then the renamed tablespace would show
as the default tablespace for the user in the DBA_USERS view.
QUESTION 10:
Which statements are true regarding the new Persistent CONFIGURATION FEATURE IN RMAN? (Select
TWO)
A. It allows you to set the retention period of backups.
B. It remember the last backup command you used so that you can reschedule it easily.
C. It enables you to store your channel attribute settings in the Recovery catalog with each script.
D. It enables you to store the settings for channel attributes so that you do not have to specify them in each
backup o restore command
Answer: A, D
Explanation:
When implementing an RMAN-based backup strategy, you can use RMAN more effectively if you
understand the more common options available to you. Many of these can be set in the RMAN environment
on a persistent basis, so that you do not have to specify the same options every time you issue a command. To
simplify ongoing use of RMAN for backup and recovery, the RMAN lets you set a number of persistent
configuration settings for each target database. These settings control many aspects of RMAN's behavior
when working with that database, such as backup retention policy, default destinations for backups to tape or
disk, default backup device type (tape or disk), and so on.
Reference:
Oracle(r) Backup and Recovery Basics 10g Release 2 (10.2), 3-7 and 3-8
QUESTION 11:
What does the Metadata Application Programming Interface (API) allow you to do?
A. repair damaged data dictionary entries
B. delete data dictionary information about database objects you n o longer need
C. extract data definition commands from the data dictionary in a variety of formats
D. prepare pseudo code modules for conversion to Java or PL/SQL programs with a Metadata code generator
Answer: C
Explanation:
When you fetch metadata for an object, you may want to use it to re-create the object in a different database
or schema. You may not be ready to make remapping decisions when you fetch the metadata. You may want
to defer these decisions until later. To accomplish this, you fetch the metadata as XML and store it in a file or
table. Later you can use the submit interface to re-create the object. The submit interface is similar in form to
the retrieval interface. It has an OPENW procedure in which you specify the object type of the object to be
created. You can specify transforms, transform parameters, and parse items. You can call the CONVERT
function to convert the XML to DDL, or you can call the PUT function to both convert XML to DDL and
submit the DDL to create the object.
Oracle(r) Database Utilities, 18-10
QUESTION 12:
Consider the following configuration: /devices/D1 is a member of disk group dgroupA . /devices/D2 is a
1Z0-045 Demo – 100% Money Back Guaranteed!!!
member of disk group dgroupA . /devices/D3 is a member of disk group dgroupA . You plan to add a new
disk, /devices/D4, to the disk group dgroupA and execute the following command: SQL> ALTER
DISKGROUP dgroupA ADD DISK '/devices/D*';
Which task would be accomplished by the command?
A. The command first detaches all the member disks starting with "D" and then reattaches all of them
including the new disk.
B. The command adds the new disk, D4 , to the disk group.
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 there is no disk by the name " /devices/D* ".
E. The command would result in an error because no wildcard characters can be used in the disk name.
Answer: B
QUESTION 13:
In Oracle 10g, the LogMiner allows storage of a copy of the data dictionary in _____ and_____ . (Select
TWO)
A. a database file
B. the control file
C. the online redo log files
D. the supplemental log file
E. an operating system flat file
Answer: C, E
Explanation:
LogMiner requires a dictionary to translate object IDs into object names when it returns redo data to you.
LogMiner gives you three options for supplying the dictionary:
- Using the Online Catalog Oracle recommends that you use this option when you will have access to the
source database from which the redo log files were created and when no changes to the column definitions in
the tables of interest are anticipated. This is the most efficient and easy-to-use option.
- Extracting a LogMiner Dictionary to the Redo Log Files Oracle recommends that you use this option when
you do not expect to have access to the source database from which the redo log files were created, or if you
anticipate that changes will be made to the column definitions in the tables of interest.
- Extracting the LogMiner Dictionary to a Flat File This option is maintained for backward compatibility with
previous releases. This option does not guarantee transactional consistency. Oracle recommends that you use
either the online catalog or extract the dictionary from redo log files instead.
QUESTION 1:
Which six files are maintained in the Flash Recovery Area? (Select SIX)
A. control file
B. RMAN files
C. password file
D. parameter file
E. flashback logs
F. data file copies
G. core dump files
H. archived log files
I. RMAN recovery scripts
J. control file autobackpus
Answer: A, B, E, F, H, J
Explanation:
The Flash Recovery Area is a unified storage location for all recovery-related files and activities in an Oracle
Database. It includes Control File, Archived Log Files, Flashback Logs, Control File Autobackups, Data
Files, and RMAN files.
QUESTION 2:
The Automatic Database Diagnostic Monitor (ADDM) analysis runs every 60 minutes on your database.
Your database if facing a series of interrelated problems over a period of two hours. You need to ensure that
the ADDM analysis is run over a time span of two hours in future.
What would you do?
A. Create two custom ADDM tasks.
B. Modify the AWR snapshot time interval to two hours.
C. Create a new scheduler window for a time period of two hours.
D. Modify the time interval by using the DBMS_JOB.INTERVAL procedure.
E. Modify the Automatic Workload Repository (AWR) snapshot retention period to two hours.
Answer: B
Explanation:
The Automatic Database Diagnostic Monitor (ADDM) provides a holistic tuning solution. ADDM analysis
can be performed over any time period defined by a pair of AWR snapshots taken on a particular instance.
Analysis is performed top down, first identifying symptoms and then refining them to reach the root causes of
performance problems. The goal of the analysis is to reduce a single throughput metric called DB time. DB
time is the cumulative time spent by the database server in processing user requests. It includes wait time and
CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and
V$SYS_TIME_MODEL views.
QUESTION 3:
Which four statements regarding the Clone Database tool are correct? (Select FOUR)
A. It clones Oracle databases from release 8.1.7 or later.
B. It clones a source database while the database is in NOMOUNT state.
C. It clones a source database while the database is in MOUNT state.
D. It clones an Oracle database by using Recovery Manager (RMAN).
E. It clones a source database at the specified Oracle home and starts the new database instance in the open
mode.
F. It clones a source database and makes the new database instance consistent with the source database up to
the backup time of archived log.
Answer: A, C, D, F
Note: The question is wrong. There isn't a "CLONE DATABASE tool". There are only DBCA and RMAN
tools to clone a database.
Explanation:
Observation: If we want to clone a database using the DBCA, first we create a database template; after that
doesn’t matter if the database source is in NOMOUNT or MOUNT state.
Cloning Databases Using DBCA
The "Template Management" section of the Database Configuration Assistant (DBCA) can be used to clone
databases. The following method creates a clone of and existing database including both the structure and the
data:
1. Start the Database Configuration Assistant (DBCA).
2. On the "Welcome" screen click the "Next" button.
3. On the "Operations" screen select the "Manage Templates" option and click the "Next" button.
4. On the "Template Management" screen select the "Create a database template" option and select the "From
and existing database (structure as well as data)" sub-option then click the "Next" button.
5. On the "Source database" screen select the relevant database instance and click the "Next" button.
6. On the "Template properties" screen enter a suitable name and description for the template, confirm the
location for the template files and click the "Next" button.
7. On the "Location of database related files" screen choose either to maintain the file locations or to convert
to OFA structure (recommended) and click the "Finish" button.
8. On the "Confirmation" screen click the "OK" button.
9. Wait while the Database Configuration Assistant progress screen gathers information about the source
database, backs up the database and creates the template. By default the template files are located in the
"$ORACLE_HOME/assistants/dbca/templates" directory.
QUESTION 4:
The ______ identifies and helps to resolve performance problems relating to the execution of SQL statements
by recommending which indexes, materialized views, or materialized view logs to create, drop, or retain.
A. Undo Advisor
B. MTTR Advisor
C. Memory Advisor
D. Segment Advisor
E. SQL Tuning Advisor
F. SQL Access Advisor
Answer: F
Explanation: The SQL Access Advisor helps you achieve your performance goals by recommending the
proper set of materialized views, naterialized view logs, and indexes for a given workload. The SQL Access
Advisor recommends bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response
time for many types of ad hoc queries and reduced storage requirements compared to other indexing
techniques. B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys.
Another component of the SQL Access Advisor also recommends how to optimize materialized views so that
they can be fast refreshable and take advantage of general query rewrite.
QUESTION 5:
In the server parameter file (SPFILE), the UNDO_TABLESPACE initialization parameter is set to
UNDOTBS. You executed the following SQL statement to rename the UNDOTBS undo tablespace: ALTER
TABLEPACE undobts RENAME TO undobts_old;
Which statement is correct in this scenario?
A. The tablespace will be renamed but the data file headers will not be updated.
B. The above SQL statement will fail because you cannot rename an undo tablespace.
C. The tablespace will be renamed and all the changes will be logged in the alert log file.
D. The tablespace will be renamed but a message will be added to the alert log file indicating that you should
change the corresponding initialization parameter files.
E. To be able to rename the UNDOTBS undo tablespace, you would need to set the UNDO_TABLESPACE
initialization parameter to some other tablespace name and then execute the above SQL command.
Answer: C
Explanation:
Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary
tablespace. The COMPATIBLE parameter must be set to 10.0 or higher. If the tablespace is an undo
tablespace and if the following conditions are met, then the tablespace name is changed to the new tablespace
name in the server parameter file (SPFILE).
- The server parameter file was used to start up the database.
- The tablespace name is specified as the UNDO_TABLESPACE for any instance.
If a traditional initialization parameter file (PFILE) is being used then a message is written to the alert log
stating that the initialization parameter file must be manually changed.
Reference:
Oracle(r) 10g Administrator Guide , 8-19
QUESTION 6:
Identify four uses of the Oracle Scheduler. (Select FOUR)
A. Enables you to set idle time limits for a resource plan.
B. Enables you to schedule job execution based on time.
C. Enables you to execute jobs in a clustered environment.
D. Enables you to assign priorities to the consumer groups.
E. Enables you to map a consumer group to an Oracle user.
F. Enables you to create a job that makes use of saved programs and schedules.
G. Enables you to periodically execute operating system script files located on the same server as the
database.
Answer: B, C, G, F
Explanation:
A - FALSE. Isn't an Oracle scheduler capability.
B - TRUE. Schedule job execution based on time or events. The most basic capability of a job scheduler is the
ability to schedule a job to run at a particular date and time or when a particular event occurs.
C - TRUE. Execute and manage jobs in a clustered environment. A cluster is a set of database instances that
cooperates to perform the same task. Oracle Real Application Clusters (RAC) provides scalability and
reliability without any change to your applications. The Scheduler fully supports execution of jobs in such a
clustered environment. To balance the load on your system and for better performance, you can also specify
the database service where you want a job to run.
D - FALSE. The Scheduler activity is logged and information such as the status of the job and the last run
time of the job can be easily tracked, this way the DBA would track witch particular JOB time and statistics,
not consumer groups.
F - TRUE. For example, consider the situation where a patch needs to be applied to a database that is in
production. To minimize disruptions, this task will need to be performed during non-peak hours. This can be
easily accomplished using the Scheduler. Instead of having IT personnel manually carry out this task during
non-peak hours, you can instead create a job and schedule it to run at a specified time using the Scheduler.
G - TRUE. Application developers can create programs and program libraries that end users can use to create
or monitor their own jobs. In addition to typical database jobs, you can schedule and monitor jobs that run as
part of an application suite.
QUESTION 7:
You are using SQL Tuning Advisor (STA) to tune SQL workload in your database.
Which four types of recommendations do you get from Automatic Tuning Optimizer (ATO) via the STA?
(Select FOUR)
A. recommendation to create indexes
B. recommendation to use stored outlines
C. recommendation to create a SQL profile
D. recommendation to gather relevant statistics
E. recommendation to denormalize the schema
F. recommendation to restructure SQL statements
Answer: A, C, D, F
Explanation:
For the A answer see the point n°3.
For the C answer see the point n°2.
Oracle 10g allows the optimizer to run in tuning mode where it can gather additional information and make
recommendations about how specific statements can be tuned further. This process may take several minutes
for a single statement so it is intended to be used on high-load resource-intensive statements. In tuning mode
the optimizer performs the following analysis:
1. Statistics Analysis - The optimizer recommends the gathering of statistics on objects with missing or stale
statistics. Additional statistics for these objects are stored in an SQL profile.
2. SQL Profiling - The optimizer may be able to improve performance by gathering additional statistics and
altering session specific parameters such as the OPTIMIZER_MODE. If such improvements are possible the
information is stored in an SQL profile. If accepted this information can then used by the optimizer when
running in normal mode. Unlike a stored outline which fixes the execution plan, an SQL profile may still be
of benefit when the contents of the table alter drastically. Even so, it's sensible to update profiles periodically.
The SQL profiling is not performed when the tuning optimizer is run in limited mode. The SQL profile
doesn't recommend to create a SQL profile.
3. Access Path Analysis - The optimizer investigates the effect of new or modified indexes on the access path.
It's index recommendations relate to a specific statement so where necessary it will also suggest the use of the
SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
4. SQL Structure Analysis - The optimizer suggests alternatives for SQL statements that contain structures
that may impact on performance. The implementation of these suggestions requires human intervention to
check their validity. Access Path Analysis - The optimizer investigates the effect of new or modified indexes
on the access path. It's index recommendations relate to a specific statement so where necessary it will also
suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL
workload.
QUESTION 8:
You work as a database administrator for Pass .com. You started the instance using the init.orafile. You have
two control files and three redo log groups in your database. You decided to protect the database against
failures by adding one more control file.
Which file is the appropriate way to perform this task?
A. Shut down the instance, copy the control file to the third location and open the database.
B. Abort the instance, copy the control file to the third location, modify the CONTROL_FILES parameter in
the init.ora file and open the database.
C. Copy the control file to the third location, modify the CONTROL_FILES parameter in the init.ora file,
restart the instance and open the database.
D. Shut down the instance, copy the control file to the third location, modify the CONTROL_FILES
parameter in the init.ora file and open the database.
E. Shut down the instance, startup in the mount state, copy the control file to the third location, modify the
CONTROL_FILES parameter in init.ora and open the database.
Answer: D
Explanation:
You can create an additional control file copy for multiplexing by copying an existing control file to a new
location and adding the file name to the list of control files. Similarly, you rename an existing control file by
copying the file to its new name or location, and changing the file name in the control file list. In both cases,
to guarantee that control files do not change during the procedure, shut down the database before copying the
control file.
To add a multiplexed copy of the current control file or to rename a control file:
1. Shut down the database
2. Copy an existing control file to a new location, using operating system commands.
3. Edit the CONTROL_FILES parameter in the database initialization parameter file to add the new control
file name, or to change the existing control filename.
4. Restart the database.
QUESTION 9:
Which four tablespaces can be renamed? (Select FOUR)
A. an undo tablespace
B. an offline tablespace
C. a read-only tablespace
D. a temporary tablespace
E. the SYSTEM tablespace
F. the default permanent tablespace for the non-SYSTEM users
Answer: A, C, D, F
Explanation: Besides any other, The COMPATIBLE parameter must be set to 10.0 or higher.
A - True. UNDO Tablespace. If the tablespace is an undo tablespace and if the following conditions are met,
then the tablespace name is changed to the new tablespace name in the server parameter file (SPFILE).
- The server parameter file was used to start up the database.
- The tablespace name is specified as the UNDO_TABLESPACE for any instance.
If a traditional initialization parameter file (PFILE) is being used then a message is written to the alert log
stating that the initialization parameter file must be manually changed.
B - False. Offline Tablespaces. If any datafile in the tablespace is offline, or if the tablespace is offline, then
the tablespace is not renamed and an error is raised.
C - True. Read-only Tablespace. If the tablespace is read only, then datafile headers are not updated. This
should not be regarded as corruption; instead it cause a message to be written to the alert log
indicating that datafile headers have not been renamed. The data dictionary and control file are updated.
D - True.Temporary Tablespace.If the tablespace is the default temporary tablespace, then the corresponding
entry in the database properties table is updated and the DATABASE_PROPERTIES view shows the new
name.
E - False. SYSTEM Tablespace. If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX
tablespace, then it will not be renamed and an error is raised.
F - True. When you rename a tablespace the database updates all references to the tablespace name in the data
dictionary, control file, and (online) datafile headers. The database does not change the tablespace ID so if
this tablespace were, for example, the default tablespace for a user, then the renamed tablespace would show
as the default tablespace for the user in the DBA_USERS view.
QUESTION 10:
Which statements are true regarding the new Persistent CONFIGURATION FEATURE IN RMAN? (Select
TWO)
A. It allows you to set the retention period of backups.
B. It remember the last backup command you used so that you can reschedule it easily.
C. It enables you to store your channel attribute settings in the Recovery catalog with each script.
D. It enables you to store the settings for channel attributes so that you do not have to specify them in each
backup o restore command
Answer: A, D
Explanation:
When implementing an RMAN-based backup strategy, you can use RMAN more effectively if you
understand the more common options available to you. Many of these can be set in the RMAN environment
on a persistent basis, so that you do not have to specify the same options every time you issue a command. To
simplify ongoing use of RMAN for backup and recovery, the RMAN lets you set a number of persistent
configuration settings for each target database. These settings control many aspects of RMAN's behavior
when working with that database, such as backup retention policy, default destinations for backups to tape or
disk, default backup device type (tape or disk), and so on.
Reference:
Oracle(r) Backup and Recovery Basics 10g Release 2 (10.2), 3-7 and 3-8
QUESTION 11:
What does the Metadata Application Programming Interface (API) allow you to do?
A. repair damaged data dictionary entries
B. delete data dictionary information about database objects you n o longer need
C. extract data definition commands from the data dictionary in a variety of formats
D. prepare pseudo code modules for conversion to Java or PL/SQL programs with a Metadata code generator
Answer: C
Explanation:
When you fetch metadata for an object, you may want to use it to re-create the object in a different database
or schema. You may not be ready to make remapping decisions when you fetch the metadata. You may want
to defer these decisions until later. To accomplish this, you fetch the metadata as XML and store it in a file or
table. Later you can use the submit interface to re-create the object. The submit interface is similar in form to
the retrieval interface. It has an OPENW procedure in which you specify the object type of the object to be
created. You can specify transforms, transform parameters, and parse items. You can call the CONVERT
function to convert the XML to DDL, or you can call the PUT function to both convert XML to DDL and
submit the DDL to create the object.
Oracle(r) Database Utilities, 18-10
QUESTION 12:
Consider the following configuration: /devices/D1 is a member of disk group dgroupA . /devices/D2 is a
1Z0-045 Demo – 100% Money Back Guaranteed!!!
member of disk group dgroupA . /devices/D3 is a member of disk group dgroupA . You plan to add a new
disk, /devices/D4, to the disk group dgroupA and execute the following command: SQL> ALTER
DISKGROUP dgroupA ADD DISK '/devices/D*';
Which task would be accomplished by the command?
A. The command first detaches all the member disks starting with "D" and then reattaches all of them
including the new disk.
B. The command adds the new disk, D4 , to the disk group.
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 there is no disk by the name " /devices/D* ".
E. The command would result in an error because no wildcard characters can be used in the disk name.
Answer: B
QUESTION 13:
In Oracle 10g, the LogMiner allows storage of a copy of the data dictionary in _____ and_____ . (Select
TWO)
A. a database file
B. the control file
C. the online redo log files
D. the supplemental log file
E. an operating system flat file
Answer: C, E
Explanation:
LogMiner requires a dictionary to translate object IDs into object names when it returns redo data to you.
LogMiner gives you three options for supplying the dictionary:
- Using the Online Catalog Oracle recommends that you use this option when you will have access to the
source database from which the redo log files were created and when no changes to the column definitions in
the tables of interest are anticipated. This is the most efficient and easy-to-use option.
- Extracting a LogMiner Dictionary to the Redo Log Files Oracle recommends that you use this option when
you do not expect to have access to the source database from which the redo log files were created, or if you
anticipate that changes will be made to the column definitions in the tables of interest.
- Extracting the LogMiner Dictionary to a Flat File This option is maintained for backward compatibility with
previous releases. This option does not guarantee transactional consistency. Oracle recommends that you use
either the online catalog or extract the dictionary from redo log files instead.
0 comments:
Post a Comment