NIIT sample paper of Oracle with answer

I am sharing the NIIT sample paper of Oracle with answer for your reference. You can go through it for your purpose. I am giving he question paper from the official website for your reference.

You can well prepare for your examinations with the question paper.




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