Université Ibn Tofail Module Administration et optimisation des BD

Ecole Nationale des Sciences Appliquées Kenitra Prof. Habiba. Chaoui

TEST ANSWERS

Oracle Database Administration

===============================================================================================

Partie I
1. _ C and E. The SGA is shared memory, updated by all sessions; PGAs are private to each
session. The SGA is allocated at startup time (but it can be modified later).
_ A, B, and D. A is wrong because it reverses the situation: it is the SGA that exists in shared
memory, not the PGA. B is wrong because sessions write to both their own PGA and to the
SGA. D is wrong because (unlike the SGA) the PGA is only allocated on demand.
2. _ D. This is the client-server split: user processes generate SQL; server processes execute
SQL.
_ A, B, and C. A and B are wrong because they get the use of Oracle Net wrong. Oracle Net
is the protocol between a user process and a server process.
C is wrong because it describes what server processes do, not what user processes do.
3. _ A, D, and F. Every instance must have a database buffer cache, a log buffer, and a shared
pool.
_ B, C, E, and G. B, C, and G are wrong because the Java pool, the large pool, and the
Streams pool are only needed for certain options. E is wrong because the PGA is not part of
the SGA at all.
4. _ D. The log buffer is fixed in size at startup time.
_ A, B, C, E, F, and G. A, B, C, E, and F are wrong because these are the SGA’s resizable
components. G is wrong because the log buffer is static.
5. _ D. The log buffer cannot be resized manually, never mind automatically.
_ A, B, C, E, F, and G. A, B, C, E, and F are wrong because these SGA components can all
be automatically managed. G is wrong because the log buffer is static.
6. _ A. The session updates the copy of the block in memory and writes out the change vector
to the log buffer.
_ B, C, and D. B is wrong, because while this will happen, it does not happen when the
change is made. C is wrong because it confuses the session making changes in memory with
LGWR propagating changes to disk. D is wrong because all changes to data occur in memory
as they are made—the COMMIT is not relevant.
7. _ A. Archiving is not compulsory (though it is usually a good idea).
_ B, C, D, and E. CKPT, DBWn, LGWR, and MMON are all necessary processes.
8. _ C. On COMMIT, the log writer flushes the log buffer to disk. No other background
processes need do anything.
_ A, B, and D. A is wrong because checkpoints only occur on request, or on orderly
shutdown. B is wrong because the algorithm DBWn uses to select buffers to write to the
datafiles is not related to COMMIT processing, but to how busy the buffer is. D is wrong
because ARCn only copies filled online redo logs; it doesn’t copy change vectors in real time.
9. _ A, C, and E. A and C are correct because the DBA can choose to configure multiple
archive and database writer processes. E is correct because one server process will be
launched for every concurrent session.
_ B and D. These are wrong because an instance can have only one log writer process and
only one checkpoint process.
10. _ C. If a tablespace has several datafiles, segments can have extents in all of them.
_ A, B, and D. A is wrong because one extent consists of consecutive block in one datafile. B
is wrong because one segment can only exist in one tablespace (though one tablespace can
contain many segments). D is wrong because while this can certainly be done, one block can
only exist in one datafile.
11. _ C. Two groups of one member is the minimum required for the database to function.
_ A, B, and D. A and B are wrong because at least two groups are always required. D is
wrong because while it is certainly advisable to multiplex the members, it is not a mandatory
requirement.
12. _ A. The checkpoint process writes the RBA to the controlfile.
_ B, C, and D. The online logs, the datafiles, and SGA have no knowledge of where the
current RBA is.
Partie II
1. _ C. SQL Developer is not installed with the OUI; it is delivered as a ZIP file that just
needs to be unzipped.
_ A, B, and D. All other products (even the OUI) are installed with the OUI.
2. _ A, C, and D. DBCA is meant for creating databases, but they can also be created from
SQL*Plus or by instructing the OUI to create a database after installing the Oracle Home.
_ B and E. B is wrong because DBUA can only upgrade an existing database. E is wrong
because Database Control is available only after the database is created.
3. _ B. Oracle Secure Backup is the enterprise backup facility.
_ A and C. These are both wrong because they are limited to backing up database files only.
4. _ B. The Oracle Base directory contains all the Oracle Homes, which can be any versions
of any products.
_ A, C, and D. A is wrong because it inverts the relationship. C is wrong because there is no
requirement for a separate base for each product. D is wrong because it confuses the
[Link] file and the OUI with the OFA.
5. _ A. The rather grandly named Optimal Flexible Architecture is nothing more than a
naming convention for directory structures.
_ B, C, and D. These are wrong because they go way beyond OFA.
6. _ D. Without a DISPLAY set, the OUI will not be able to open any windows.
_ A, B, and C. These are wrong because while they can be set before launching the OUI, the
OUI will prompt for values for them.
7. _ C. Perhaps not advisable, but you can certainly do this.
_ A, B, and D. A is wrong because while it might be a good idea, it is not something you have
to do. B is wrong because the interactive installation will halt. D is wrong because all
prerequisites are checked at the same time.
8. _ A and B. The Oracle Home must exist on a file system, but it can be local or clustered.
_ C and D. Raw devices and ASM devices can be used for databases, but not for an Oracle
Home.
9. _ D. The -ignoresysprereqs switch stops OUI from running the tests.
_ A, B, and C. A is wrong because this will suppress generation of windows, not running
tests. B is wrong because this is the switch to generate a response file. C is wrong because this
is the switch to read a response file.
10. _ D. If the OUI cannot find an inventory, it will create one.
_ A, B, and C. A and B are wrong because one inventory stores details of all Oracle Base and
Oracle Home directories. C is wrong because it is not possible to create an inventory before
running the OUI.
11. _ B. The CREATE DATABASE command can only be issued in NOMOUNT mode.
_ A, C, and D. A is wrong, because if the instance is not started, the only possible command
is STARTUP. C and D are wrong because it is impossible to mount a database if there is no
controlfile, and it cannot be opened if there is no redo log and SYSTEM tablespace.
12. _ C. There are defaults for everything, including the SYSAUX tablespace and datafile
definitions.
_ A, B, and D. A is wrong because the command will succeed. B and D are wrong because
these are not the way the defaults work.
13. _ D. The only time a listener is required is if the DBCA is used, and Database Control is
selected. The DBCA will not continue if it cannot detect a listener.
_ A, B, and C. A is wrong because there is a circumstance where a listener is required; B is
wrong because in all other circumstances a listener is not required. C is wrong because it does
not go far enough: The DBCA will not require a listener, if Database Control is not selected.
14. _ A. This is the correct sequence (though 2 and 3 could be done the other way round).
_ B, C, and D. None of these are possible.
15. _ D. This is the one parameter that can never be changed after creation.
_ A, B, and C. A and B are wrong because DB_BLOCK_SIZE cannot be changed no matter
when you try to do it. C is wrong because the CONTROL_FILES parameter can certainly be
changed, though this will require a shutdown and restart.
16. _ A, C, F, and G. All of these will always be created, by default if they are not specified.
_ B, D, and E. B and D are wrong because these should exist before the instance is started. E
is wrong because the conversion of the static parameter file to a dynamic parameter file only
occurs, optionally, after the database is created.
17. _ D. The database will function, but without the data dictionary views and PL/SQL
packages created by these scripts it will be unusable.
_ A, B, C, and E. A is wrong because the database will open; in fact, it must be open to run
the scripts. B is wrong because tables and other objects can certainly be created. C is wrong
because PL/SQL will be available; it is the supplied packages that will be missing. E is
completely irrelevant to these scripts.
18. _ A. The DBCA is the only tool that can manage templates.
_ B, C, D, and E. These are all wrong because only the DBCA offers template management.
19. _ C and E. C is right because the character set can be set at creation time, no matter how
the creation is done. E is right because it is possible to change character sets after creation
(though you don’t want to do this unless it is really necessary).
_ A, B, and D. A and B are wrong because templates are not relevant. If the template includes
datafiles, the DBCA will change the character set behind the scenes. D is wrong because the
DBCA does not offer an option to do this.
20. _ B. Database Control can be used for each database and will be configured with a
different port for each one.
_ A, C, and D. A is wrong because this is what Grid Control can do. C is wrong because
Database Control must be installed in every database that will use it. D is wrong because
while a Database Control is only for one database, every database can have its own.
Partie III
1. _ B, C, and D. There will always be an error if the database console process has not been
started or it is on a different port, and since the URL used a loopback address, there will be an
error if the browser is not running on the same machine as the console.
_ A, E, and F. A and F are wrong because these are not a problem; the listener and the
database can both be started if the console is accessible. E is wrong because the Grid Control
agent is not necessary for Database Control.
2. _ A. These are the files that make up a database, and must all be synchronized before it can
be opened.
_ B, C, and D. B is wrong because these files are not, strictly speaking, part of the database at
all. C is wrong because an error with the controlfile will mean the database cannot even be
mounted, never mind opened. E is wrong because SMON can only fix problems in datafiles,
not anything else.
3. _ B. Mounting the database entails the opening of all copies of the controlfile.
_ A, C, D, and E. A is wrong because the parameter file is only needed for NOMOUNT. C,
D, and E are wrong because these file types are only needed for open mode.
4. _ D. An immediate shutdown is clean, so no recovery will be required.
_ A, B, and C. These are wrong because no recovery or rollback will be required; all the work
will have been done as part of the shutdown.
5. _ C and D. Database Control will be fine but must be started for each database and
contacted on different ports for each database.
_ A and B. A is wrong because you can use Database Console, but you will need separate
instances for each database. B is wrong because while Grid Control may be a better tool, it is
by no means essential.
6. _ B. The default shutdown mode is SHUTDOWN NORMAL, which will hang until all
sessions have voluntarily disconnected.
_ A, C, and D. A and C are wrong because these would cause an error, not a hang. D is wrong
because it describes SHUTDOWN TRANSACTIONAL, not SHUTDOWN NORMAL.
7. _ D. There is no required action; recovery will be automatic.
_ A, B, and C. A is wrong because this is one thing you should not do after an ABORT. B is
wrong because database recovery is not necessary, only instance recovery. C, instance
recovery, is wrong because it will occur
automatically in mount mode at the next startup.
8. _ B. The default scope of ALTER SYSTEM is both memory and spfile.
_ A and C. A is wrong because this setting will have been replaced by the setting in the
second command. C is wrong because the session-level setting will have been lost during the
restart of the instance.
9. _ D. This is the technique for changing a static parameter.
_ A, B, and C. A is wrong because static parameters can be changed—but only with a
shutdown. B and C are wrong because static parameters cannot be changed for a running
session or instance.
10. _ B and F. Neither of these affects the structure of the database or the instance; they are
not important enough to generate an alert log entry.
_ A, C, D, and E. All of these are changes to physical or memory structures, and all such
changes are recorded in the alert log.
11. _ A. This is the parameter used to determine the location of background trace files.
_ B, C, and D. B is wrong because there is no such parameter. C is wrong because this is the
default location for datafiles, not trace files. D is wrong because while there is a platform-
specific default, it can be overridden with a parameter.
12. _ E and F. These views are populated from the instance and will therefore be available at
all times.
_ A, B, C, and D. A and B are data dictionary views, which can only be seen in open mode. C
and D are dynamic performance views populated from the controlfile, and therefore only
available in mount mode or open mode.
13. _ B. The DBA views list every appropriate object in the database.
_ A, C, and D. A is wrong because this will list only the tables the current user has
permissions on. C is wrong because it will list only the tables owned by SYS. D is wrong
because this is the view that lists all the dynamic performance views, not all tables.
Partie IV
1. _ A is SEGMENT; B is EXTENT; C is ORACLE BLOCK; D is DATAFILE.
_ Neither ROW nor TABLE is included in the model.
2. _ A and E. One extent is several consecutive Oracle blocks, and one segment consists of
one or more extents.
_ B, C, and D. B, C, and D are all wrong because they misinterpret the Oracle storage model.
3. _ C and D. A table can be a type of segment, as is a table partition (in which case the
partitioned table is not a segment).
_ A, B, and E. A, B, and E are wrong because they exist only as objects defined within the
data dictionary. The data dictionary itself is a set of segments.
4. _ C and D. With release 11g, local extent management and automatic segment space
management are enabled by default.
_ A and B. A and B are both wrong because by default autoextension is disabled.
E is wrong because providing a filename will override the OMF mechanism.
5. _ B and D. A small file tablespace can have many files, and all datafiles can be resized
upward.
_ A and C. A is wrong because you cannot convert between a SMALLFILE and a BIGFILE.
C is wrong because a BIGFILE tablespace can have only one file.
6. _ B. Objects can be dropped from read-only tablespaces.
_ A, C, D, and E. All of these commands will fail because they require writing to the table,
unlike a DROP, which only writes to the data dictionary.
7. _ B. It is not possible to change the segment space management method after creation.
_ A C, D, and E. A and C are wrong because a tablespace can be converted to local extent
management or renamed at any time. D is wrong because a datafile can be resized
downward—though only if the space to be freed up has not already been used. E is wrong
because you cannot change the segment space management method without re-creating the
tablespace.
8. _ A. 85 percent and 97 percent are the database-wide defaults applied to all tablespaces.
_ B, C, and D. B is wrong because AUTOEXTEND does not affect the warning mechanism
(though it may make it pointless). C is wrong because the warning mechanism considers only
the tablespace, not the files. D is wrong because by default the space warning is enabled.
9. _ E and F. Joining these views will give the necessary information.
_ A, B, C, and D. A and B are wrong because these views will not be available in mount
mode. C is wrong because it is not relevant to datafiles (and is also not available in mount
mode). D is wrong because there is no datafile or tablespace information in V$DATABASE.
10. _ B, D, F, and H. V$TABLESPACE and DBA_TABLESPACES will list the temporary
tablespaces, and V$TEMPFILE and DBA_TEMP_FILES will list their files.
_ A, C, E, and G. A and E are wrong because V$DATAFILE and DBA_DATA_FILES do
not include tempfiles. C and G are wrong because there are no views with these names.