|
Oracle DBA #1 Fundamental Exam Questions
|
“As far as the laws of mathematics refer to reality, they
are not certain; and as far as they are certain, they do not refer to
reality.”
Albert Einstein
|
Q:
What are the Oracle Architectural components?
Q:
What are the Oracle Memory Components?
Q:
What is the Server Parameter File?
Q:
What is the Parameter File?
Q:
How do you use the init.ora file?
Q:
What is the System Global Area (SGA)?
Q:
What is the Shared Pool in SGA?
Q:
What is the Buffer Cache in SGA?
Q:
What does the Buffer Cache hold in SGA?
Q:
What are the differences between the Library Cache and Dictionary Cache?
Q:
What is the Redo Log Buffer in SGA?
Q:
Describe the Large Pool component in SGA?
Q:
Describe the Multi-threaded Server process?
Q:
What are PGA and UGA?
Q:
Describe the log writer background process (LGWR)?
Q:
How often LGWR writes user’s entries to the Online Redo Log Buffer
files?
Q:
Describe the Checkpoint process?
Q:
How do you automatically force the Oracle to perform a checkpoint?
Q:
What is the Recovery Process?
Q:
What is the Lock Background Process?
Q:
How does the Archive Process work?
Q:
How do you configure your database to do an automatic archiving?
Q:
What is the System Monitor Process?
Q:
Describe the Program Monitor Process Job?
Q:
What are the differences between the SPFILE and PFILE startup?
Q:
What is the controlfile?
Q:
How do you backup your database controlfiles?
Q:
What does a controlfile contain?
Q:
Describe the password file?
Q:
How do you create a password file?
Q:
Describe the Online Redo Log file.
|
“Before
God we are all equally wise - and equally foolish.”
Albert Einstein
|
Q:
Describe a data dictionary in the Oracle database.
Q:
Describe the CATALOG.SQL script.
Q:
What are the uses of ALL_
, DBA_,
and USER_
dictionary views?
Q:
Describe the DBA_VIEWS
dictionary view.
Q:
Describe the DBA_TABLES
dictionary view.
Q:
Describe the V$PWFILE_USERS view.
Q:
Describe the V$PARAMETER view.
Q:
Describe the V$SYSTEM_PARAMETER view.
Q:
Describe the V$SGA view.
Q:
Describe the V$OPTION view.
Q:
Describe the V$PROCESS view.
Q:
Describe the V$SESSION view.
Q:
Describe the V$VERSION view.
Q:
Describe the V$INSTANCE view.
Q:
Describe the V$THREAD view.
Q:
Describe the V$CONTROLFILE view.
Q:
Describe the DBA_VIEWS
dictionary view.
Q:
Describe the V$DATAFILE view.
Q:
Describe the V$DATABASE view.
Q:
Describe the V$LOGFILE view.
Q:
Describe the V$LOG view.
Q:
What do the following scripts create?
CATAUDIT.SQL
CATLDR.SQL
CATEXP.SQL
CATPART.SQL
CATADT.SQL
STANDARD.SQL
Q:
What does the SHOW PARAMETER command?
|
“Great spirits have always found violent opposition from
mediocrities. The latter cannot understand it when a man does not
thoughtlessly submit to hereditary prejudices but honestly and
courageously uses his intelligence.”
Albert Einstein
|
Q:
Describe the NOMOUNT option in the STATUP command’
Q:
How do you start up an instance with the NOMOUNT option?
Q:
Describe the MOUNT option in the STARTUP command’
Q:
How do you start up an instance with the MOUNT option?
Q:
Describe the FORCE option in the STARTUP command.
Q:
How do you start up an instance using the FORCE option?
Q:
Describe the OPEN option in the STARTUP command.
Q:
How do you start up an instance using the OPEN option?
Q:
Describe the TRANSACTIONAL option in the SHUTDOWN command.
Q:
How do you shutdown an instance using the TRANSACTIONAL option?
Q:
Describe the IMMEDIATE option in the SHUTDOWN command.
Q:
How do you shutdown an instance using the IMMEDIATE option?
Q:
Describe the NORMAL option in the SHUTDOWN command.
Q:
How do you shutdown an instance using the NORMAL option?
Q:
Describe the ABORT option in the SHUTDOWN command.
Q:
How do you shutdown an instance using the ABORT option?
Q:
Describe the READ ONLY mode option.
Q:
How do you start up an instance with the READ ONLY mode option?
Q:
Describe the BACKGROUND_DUMP_DEST parameter.
Q:
Describe the PFILE parameter in the STARTUP command.
|
“I never think of the future - it comes soon enough.”
Albert Einstein
|
Q:
Describe an Oracle database controlfile.
Q:
How do you multiplex a controlfile?
Q:
Describe the V$CONTROLFILE view.
Q:
Describe the V$CONTROLFILE_RECORD_SECTION view.
Q:
What does the ‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE’ command?
Q:
What does the following SQL statement?
SQL>
ALTER DATABASE
BACKUP
CONTROLFILE
TO 'c:\backupcontrolfile\control_ddmmyyyy.ctl'
/
Q:
How do you view the divided sections of a controlfile?
|
“Imagination is more important than knowledge...”
Albert Einstein
|
Q:
What is the ALERT file in an Oracle database?
Q:
What type of information does the ALERT file contain?
Q:
What does the Top Part of ALERT file contain?
Q:
What type of system parameters does the ALTERT file show?
Q:
Describe the background processes in the ALERT file.
Q:
Describe the archived information in the ALERT file.
Q:
Describe the checkpoint process activities messages in the ALERT file.
Q:
Describe the System Error Messages in the ALERT file.
|
“Laws alone can not secure freedom of expression; in order
that every man present his views without penalty there must be spirit of
tolerance in the entire population.”
Albert Einstein
|
Q:
How many different types of database mode can you change your database
to?
Q:
Describe the Oracle database SUSPEND mode.
Q:
Describe the Oracle database RESUME mode.
Q:
Describe the Oracle database RESTRICTED SESSION mode.
Q:
Describe the Oracle database QUIESCE RESTRICTED mode.
Q: Descript the
RESOURCE_MANAGER_PLAN
parameter.
Q:
How do you change a database mode to the SUSPEND mode?
Q:
How do you turn on the Resource Manager Plan?
|
"My
religion consists of a humble admiration of the illimitable superior
spirit who reveals himself in the slight details we are able to perceive
with our frail and feeble mind.”
Albert Einstein
|
Q:
Describe the Server Parameter File.
Q:
Describe the MEMORY option in the ALTER SYSTEM statement.
Q:
Describe the SPFILE option in the ALTER SYSTEM statement.
Q:
Describe the BOTH option in the ALTER SYSTEM statement.
Q:
How can you create the Server Parameter File?
Q:
What are the differences between SPFILE and PFILE?
Q:
How do you change a database dynamically?
Q:
What does the following statement do?
SQL> CREATE
SPFILE
FROM
PFILE='%ORACLE_HOME%\admin\school\pfile\init.ora'
/
|
"Only
two things are infinite, the universe and human stupidity, and I'm not
sure about the former.”
Albert Einstein
|
Q:
How do you configure a database to an archive mode?
Q:
What are the benefits of changing a database mode to the archive mode?
Q:
How do you maintain an Oracle Online redo log file?
Q:
How do you monitor and maintain the checkpoint process?
Q:
Describe the database NOARCHIVELOG mode.
Q:
Describe the DBA_USERS view.
Q:
Describe the V$LOG dictionary view.
Q:
What does the STATUS column indicate in the V$LOG view?
Q:
Describe the following parameters:
LOG_CHECKPOINT_INTERVAL
parameter
LOG_CHECKPOINT_TIMEOUT
parameter
LOG_CHECKPOINTS_TO_ALERT
parameter
Q:
Describe the ARCHIVE LOG LIST command.
Q:
What does the following statement do?
SQL>
STARTUP
MOUNT
PFILE=%ORACLE_HOME%\admin\school\pfile\init.ora
/
Q:
How do you switch an online redo log file?
Q:
How do you perform a checkpoint manually?
Q:
How do you perform a checkpoint automatically?
|
"Reading,
after a certain age, diverts the mind too much from its creative pursuits.
Any man who reads too much and uses his own brain too little falls into
lazy habits of thinking.”
Albert Einstein
|
Q: How do you maintain and relocate a redo log file?
Q: Describe the V$LOG view.
Q: Describe the V$LOGFILE view.
Q: How do you add an online redo log group?
Q: How do you add an online redo log file member?
Q: How do you relocate or rename an online redo log file?
Q: How do you drop an online redo log file?
Q: How do you drop an online redo log group?
Q: How do you delete a physical online redo log file?
Q: What does the following SQL statement?
SQL>
ALTER DATABASE
ADD
LOGFILE GROUP
4
'c:\oracle\oradata\school\redo04.log' size 500k
/
Q:
What does the following SQL statement?
SQL>
ALTER DATABASE
RENAME FILE
'C:\ORACLE\ORADATA\SCHOOL\REDO04.LOG' TO
'C:\ORACLE\ORADATA\redo04a.log'
/
|
"The
important thing is not to stop questioning. Curiosity has its own reason
for existing. One cannot help but be in awe when he contemplates the
mysteries of eternity, of life, of the marvelous structure of reality. It
is enough if one tries merely to comprehend a little of this mystery every
day. Never lose a holy curiosity.”
Albert Einstein
|
Q: Why do you multiplex an online redo log file?
Q: How do you multiplex an online redo log file?
Q: Describe the Oracle-Managed files.
Q: Describe the User-Managed files.
Q: What are the differences between an Oracle-Managed and
User-Managed files?
Q: How do you create an online redo log member?
Q:
What does the following SQL statement?
SQL>
ALTER SYSTEM
SET
db_create_online_log_dest_4='c:\newfolder'
/
|
"Reality
is merely an illusion, albeit a very persistent one.”
Albert Einstein
|
Q: Describe a tablespace.
Q: How do you create a tablespace in a database?
Q: How do you maintain a tablespace using the
Oracle-Managed file technique?
Q: How do you maintain a tablespace using the User-Managed
file technique?
Q: How do you maintain a datafile using the Oracle-Managed
file technique?
Q: How do you maintain a datafile using the User-Managed
file technique?
Q: Describe the AUTOEXTEND ON option in the CREATE
TABLESPACE statement.
Q: Describe the following storage options in the CREATE
TABLESPCES statement.
INITIAL
parameter
NEXT
parameter
MINEXTENTS
parameter
MAXEXTENTS
parameter
Q:
Describe the PERMANENT ONLINE option.
Q:
What does it mean that a tablespace LOCALLY managed?
Q:
Describe the DBA_TABLESPACES
view.
Q:
How do you add a datafile to an existing tablespace?
Q:
When do you use the DB_CREATE_FILE_DEST
parameter?
Q:
What do the following SQL statement do?
What are the differences between the first CREATE statement and the
second CREATE statement?
SQL>
CREATE TABLESPACE
myfirst_tablespace
DATAFILE 'c:\oracle\oradata\school\myfirst_tablespace_01.dbf'
SIZE 10M
AUTOEXTEND ON
DEFAULT STORAGE
(INITIAL
100K NEXT 100K
MINEXTENTS 10 MAXEXTENTS
200)
PERMANENT ONLINE
/
SQL>
CREATE TABLESPACE
my2nd_tablespace
/
Q:
How do you drop a tablespace?
Q:
How do you drop a tablespace if it contains objects?
|
"The
ideals which have lighted my way, and time after time have given me new
courage to face life cheerfully, have been Kindness, Beauty, and Truth.
The trite subjects of human efforts, possessions, outward success, luxury
have always seemed to me contemptible.”
Albert Einstein
|
Q:
How do you create a TEMPORARY tablespace using Oracle-Managed File (OMF)
technique?
Q:
How do you create a TEMPORARY tablespace using User-Managed File (UMF)
technique?
Q:
What is the DB_CREATE_FILE_DEST parameter?
Q:
Describe the DBA_TABLESPACES view.
Q:
Describe the DBA_DATA_FILES view.
Q:
Describe the following views.
DBA_DATA_FILES
directory view
DBA_TEMP_FILES
directory view
V$SESSION
dictionary view
V$SORT_SEGMENT
view
Q:
How do you drop a temporary tablespace?
Q:
Create a temporary tablespace using an Oracle original syntax.
Q:
Create a temporary tablespace using the TEMPFILE clause.
Q:
Describe the UNIFORM option.
Q:
What do the following SQL statements do?
SQL>
SELECT
tablespace_name, initial_extent, max_extents,
contents, logging, status
FROM dba_tablespaces
/
SQL> SELECT
s.username, tablespace, contents, extents, blocks
FROM v$session s, v$sort_usage
WHERE s.saddr = session_addr
/
SQL> SELECT
tablespace_name, extent_size,
total_extents, max_sort_blocks
FROM v$sort_segment
/
SQL> CREATE
TEMPORARY
TABLESPACE mytemp
TEMPFILE 'mytemp_01.tmp' SIZE 20M
EXTENT
MANAGEMENT LOCAL
UNIFORM SIZE 10M
/
|
"The
important thing is not to stop questioning.”
Albert Einstein
|
Q:
How do you change a tablespace mode to the READ ONLY mode?
Q:
How do you relocate a tablespace?
Q:
How do you change a tablelspace mode to the READ WRITE mode?
Q:
How do you OFFLINE a tablespace mode?
Q:
How do you ONLINE a tablespace mode?
Q:
When do you OFFLINE a tablespace mode?
Q:
When can you perform the following SQL statement?
SQL> ALTER
DATABASE
RENAME FILE
'c:\oracle\oradata\school\users01.dbf'
TO
'c:\newfolder\users01.dbf'
/
|
"The
most incomprehensible thing about the world is that it is at all
comprehensible.”
Albert Einstein
| |