|
Oracle DBA #1 Fundamental Exam Answers
NOTE:
The answers go with their sequences. If a question was not answer, that means
that it a repeating question and the answer was given by the previous questions
or it is not in the scope of this subject.
|
“If you would lift me up you must be on higher ground.”
--Ralph Waldo Emerson
|
Q:
What are the Oracle Architectural components?
A:
The Oracle Architectural components are:
-
Memory (SGA) such as Buffer
Cache, Shared Pool, Redo Log Buffer, Large Pool, Java Pool, etc.
-
Background Processes such as Log
Writer, DB Writer, Checkpoint, Archiver, SMON, etc.
-
Oracle Physical Layout such as
Datafiles, Controlfiles, Online Redo log files, Parameter file, Password
file, etc.
Q:
What are the Oracle Memory Components?
A:
All components such as Shared Pool
(Library
Cache
, Dictionary Cache
), Buffer Cache
, Online Redo Log file
, Large Pool
, Java Pool
as well
as a few other items are referred to as the System Global Area
(SGA
). And the place stores information like bind
variable values
, sort areas, cursor handling, etc for a specific
user is called Program Global Area
(PGA
). The PGA is used to store only real values in place
of bind variables for executing SQL statements. The combination of these two
memories structure while they are running is called Oracle Instance.
Q:
What is the Server Parameter File?
A:
The Server Parameter File is a binary file and
Oracle uses it to change the most of its system parameters dynamically.
Q:
What is the Parameter File?
A: The
Parameter file is a configuration file and it contains all the Oracle instance
and database configuration parameters. When you change any parameter using this
file, you should shutdown and startup the Oracle Database.
Q:
How do you use the init.ora file?
A:
The init.ora file is called initialized or
parameter file. It is a configuration file.
Q:
What is the System Global Area (SGA)?
A: The SGA contains of Shared Pool
(Library
Cache
, Dictionary Cache
), Buffer Cache
, Online Redo Log file
, Large Pool
, Java Pool
as well
as a few other items.
Q:
What is the Shared Pool in SGA?
A:
The Shared Pool
contains
the Library Cache
and the
Dictionary Cache
as well
as a few other items, which are not in the scope of this section. The Library
Cache holds all users’ SQL statements, Functions, Procedures, and Packages. It
stores parsed
SQL
statement with its execution plan for reuse. The Dictionary Cache, sometimes
also referred to as the Row Cache
, holds the Oracle repository
data
information such as tables, indexes, and columns definitions, usernames,
passwords, synonyms, views, procedures, functions, packages, and privileges
information.
Q:
What does the Buffer Cache hold in SGA?
A:
The Buffer Cache
holds
users’ data. Users query their data while they are in the Buffer Cache. If
user’s request is not in the Buffer Cache then server process has to bring it
from disk. The smallest unit in the buffer cache is an Oracle block. The buffer
cache can be increased or decreased by granule unit. The smallest Granule Unit
is 4Meg if the SGA
size is
less than 128Meg and the smallest Granule Unit become 16Meg is the SGA size is
more than 128Meg.
Q:
What are the differences between the Library Cache and Dictionary Cache?
A:
The Library Cache holds user’s SQL
statements, PL/SQL programs, but the Dictionary Cache holds only repository
information such as user’s table name, its access privileges, and etc.
Q:
What is the Redo Log Buffer in SGA?
A:
The Redo Log Buffer
holds
users’ entries such as INSERT, UPDATE, DELETE, etc (DML) and CREATE TABLE,
DROP TABLE (DDL). The Redo Entries are information that will be used to
reconstruct, or redo, changes made to a database. The Log Writer
writes the entries into the
Online Redo Log files
when a
COMMIT occurs, every 3 seconds, or when one third of the Redo Log Buffer is
full. That will guarantee a database recovery to a point of failure if an Oracle
database failure occurred.
Q:
Describe the Large Pool component in SGA.
A:
The Large Pool
holds
information about the Recovery Manager (RMAN) utility when RMAN is running. If
you use the Multi-threaded Server (MTS) process, you may allocate the Oracle
Memory structure such that you can get advantage of using Large Pool instead of
the Shared Pool
. Notice that when you use dedicated servers, user
session information is housed in the PGA
Q:
Describe the Multi-threaded Server process.
A:
The Multi-threaded Server process will be used
when a user send his/her request by using a shared server. A user’s request
will be assigned to a dispatcher based on the availability of dispatchers. Then
the dispatcher will send or receive request from an assigned shared server.
Q:
What are PGA and UGA?
A: When you are running
dedicated servers then the session information can be stored inside the process
global area (PGA). The UGA
is the user
global area, which holds session-based
information. When you are running shared servers then the session
information can be stored inside the user global area (UGA)
Q:
Describe the log writer background process (LGWR).
A:
The LGWR’s job is to write the redo user’s
entries from the Redo Log Buffer
Q:
How often does LGWR write user’s entries to the Online Redo Log Buffer
files?
A:
It writes user’s entries when the
buffer exceeds one third of the Redo Log Buffer, every 3 seconds, or when a user
executes the commit SQL statement.
Q:
Describe the Checkpoint process.
A:
The Checkpoint signals DB writers to write all
dirty blocks into the disk. The Checkpoint will occurred either by a specific
defined time, size of the Online Redo Log file
used by
DBA, or when an Online Redo log file will be switched from on log file to
another.
Q:
How do you automatically force the Oracle to perform a checkpoint?
A:
The following are the parameters that will be
used by a DBA to adjust time or interval of how frequently its checkpoint should
occur on its database.
LOG_CHECKPOINT_TIMEOUT
= 3600
# every one hour
LOG_CHECKPOINT_INTERVAL
=1000 #
number of OS blocks
Q:
What is the Recovery Process?
A:
The RECO
will be
used only if you have a distributed database. You use this process to recover a
database if a failure occurs due to physical server problem or communication
problem.
Q:
What is the Lock Background Process?
A:
The LCKn
background
process will be used if you have multiple instances accessing to only one
database. An example of that is a Parallel Server or a Real Application
Clusters.
Q:
How does the Archive Process work?
A:
This background process archives the Online
Redo Log file
when you
are manually or automatically switching an Online Redo Log file. An example of
manually switching is: ALTER SYSTEM SWITCH LOGFILE or ALTER SYSTEM ARCHIVE LOG
CURRENT.
Q:
How do you configure your database to do an automatic archiving?
A:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Q:
What is the System Monitor Process?
A:
The SMON job is: when you start your database,
it will make sure that all datafiles, controlfiles, and log files are
synchronized before opening a database. If they are no, it will perform an
instance recovery. It will check the last SCN that was performed against the
datafiles. If it finds that there are transactions that were not applied against
the datafile, then it will recover or synchronize the datafile from either the
Online Redo Log files
or the
Archive Log files. The smaller Online Redo log files will bring a faster
database recovery.
Q:
Describe the Program Monitor Process Job.
A:
A user may be disconnected either by canceling
its session or by communication link. In either act, the PMON
will
start and perform an action to clean the reminding memory allocation that was
assigned to the user.
Q:
What are the differences between the SPFILE and PFILE startup?
A:
You can read or change the init.ora file (PFILE)
for startup of the Oracle database. It contains all Oracle parameters file to
configure a database instance. In this file, you can reset and change the Buffer
Cache
size,
Shared Pool
size,
Redo Log Buffer
size,
etc. You also can change the location of your control files, mode of a database
such as archivelog mode or noarchivelog mode, and many other parameter options
that you will learn them in the course of this book.
But
using Server Parameter File-SPFILE, you can not read the file. It is in a binary
format. If you want to change database parameters dynamically, you should create
the Server Parameter file (CREATE SPFILE
FROM
PFILE
) and startup your database using the SPFILE file.
There are some few parameters that you still need to shutdown and startup the
database, if you want to make the parameter in effect.
Q:
What is the controlfile?
A:
You cannot read this file and it is in a
binary format. If you want to see the content of control file or the layout of
your database, you should use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE
statement.
It writes a trace file into the %ORACLE_BASE\admin\<db-name>\UDUMP
directory.
Q:
How do you backup your database controlfiles?
A:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO
c:\ctnlrfl.bk;
Q:
What does a controlfile contain?
A:
It contains information the structure of your
database layout, database name, last System Change Number (SCN) number, your
database mode (archivelog mode or noarchivelog mode), maximum number of log
files, maximum number of log members, maximum number of instances, maximum of
number of datafiles, the location of the database Online Redo Log files
, and backup information.
Q:
Describe the password file.
A:
The password file is an external file that
contains the password of sysdba or sysoper. To use the password file you should
set the REMOTE_LOGIN_PASSWORD
parameter to exclusive or
shared mode in the Parameter File
(Example: REMOTE_LOGIN_PASSWORD=EXCLUSIVE).
Q:
How do you create a password file?
A:
To create the password file, you should run
the ORAPWD utility from operating system.
For
example:
MS-DOS>
ORAPWD FILE=%ORACLE_HOME\dbs\orapw<sid>.pwd
\
PASSWORD=mypass ENTRIES=3
The
ENTRIES parameter specifying the number of user entries allowed for the password
file. Now, the DBA can be connected to the database as a user with sysdba
privilege.
Q:
Describe the Online Redo Log file.
A:
The Online Redo Log files
hold the
Redo Entries. You should have at least two or more Redo Log Groups. Each group
may have more than one member. It is a good practice to multiplex Online Redo
Log members. The Redo Entries are information that will be used to reconstruct,
or redo, changes made to a database. The Log Writer
writes the entries into the
Online Redo Log files when a COMMIT occurs, every 3 seconds, or when one third
of the Redo Log Buffer
is full.
That will guarantee a database recovery to a point of failure if an Oracle
database failure occurred.
Q:
How do you start up an instance with the MOUNT option?
A:
SQL>
CONNECT / AS SYSDBA
SQL> STARTUP MOUNT
--OR--
SQL> STARTUP NOMOUNT
SQL> ALTER DATABASE MOUNT;
Q:
Describe the IMMEDIATE option in the SHUTDOWN command.
A:
The IMMEDIATE
option
means not to wait for a user to log off and roll back uncommitted transactions,
then shut down the instance and close the database.
Q:
Describe the ABORT option in the SHUTDOWN command.
A:
The ABORT option tells Oracle not to wait for
a user and do not roll back for any transaction and shutdown the instance.
If you SHUTDOWN with the ABORT option and then you start your database,
the SMON will perform an instance recovery automatically.
Q:
Describe the PFILE parameter in the STARTUP command.
A:
It tells the Oracle to use the specific
parameter file that is in the PFILE parameter.
Q:
What does the following SQL statement?
SQL> ALTER DATABASE
BACKUP CONTROLFILE
TO 'c:\backupcontrolfile\control_ddmmyyyy.ctl'
/
A: It
will backup a controlfile.
Q:
What is the ALERT file in an Oracle database?
A:
It is a log file that any unknown problems
with the database such as, not enough space in the rollback segment or the
maximum extent reached in a table.
Q:
How many different types of database mode can you change your database
to?
A:
Six! ARCHIVELOG,
NOARCHIVELOG, SUSPEND,
RESUME,
RESTRICTED SESSION, and
QUIESCE RESTRICTED mode.
Q:
What does the following statement do?
SQL> CREATE SPFILE
FROM
PFILE='%ORACLE_HOME%\admin\school\pfile\init.ora'
/
A: It
creates a Server Parameter File using an specific parameter file.
Q:
How do you configure a database to an archive mode?
A: First you should
change the following parameters in the parameter file.
log_archive_dest
= /u01/app/oracle/admin/<database_name>/arch
log_archive_start
= true
log_archive_format
= log%s.arc
Then do the following in the SQLPLUS utility.
SQL> SHUTDOWN
IMMEDIATE
SQL> STARTUP
MOUNT
PFILE=%ORACLE_HOME%\admin\school\pfile\init.ora
SQL> ALTER DATABASE
ARCHIVELOG
;
SQL> ALTER DATABASE
OPEN;
Q: What does the
following SQL statement?
SQL> ALTER DATABASE
RENAME FILE
'C:\ORACLE\ORADATA\SCHOOL\REDO04.LOG'
TO
'C:\ORACLE\ORADATA\redo04a.log'
/
A:
Assuming that Online Redo Log file is offline,
it relocates it to different location.
Q: What are the differences between an Oracle-Managed and
User-Managed files?
A: A User-Managed file
will be defined by an Oracle user. If you drop the tablespace that was using the
file, you should physically delete it from the disk. But an Oracle-Managed file
will be created and defined by Oracle. If you drop the tablespace that was using
the file, oracle will physically deletes the file from the disk. It knows where
the file is located.
Q: How do you maintain a tablespace using the
Oracle-Managed file technique?
A: You should tell
Oracle where it should locate and create datafiles.
SQL> ALTER SYSTEM
SET
db_create_file_dest='c:\newfolder';
Q: What does the
following SQL statement do?
SQL> CREATE TEMPORARY
TABLESPACE mytemp
TEMPFILE 'mytemp_01.tmp' SIZE 20M
EXTENT
MANAGEMENT LOCAL
UNIFORM
SIZE 10M
/
A:
It creates locally managed temporary
tablespace with uniform size option.
Q:
What are the PCTFREE and PCTUSED space allocations in the CREATE TABLE
statement?
A:
The PCTFREE parameter means that an Oracle
user can add records to a block until the unused space block reaches to the
PCTFREE value. When a block uses all space up to the “1-PCTFREE” percentage,
it stops adding records to the block. Oracle takes that block out of the
Freelist. It means that records can not be added to the block any more unless
you delete records from the block till it reaches to the PCTUSED value. Then
Oracle will add the block in the Freelist again and records can be added to the
block. And this process continues to determine when to add records in or stop
adding records from the block.
Q:
How do you create an UNDO tablespace?
A:
SQL> CREATE UNDO TABLESPACE
my_undo_tablespace
DATAFILE SIZE 100K
/
Q:
What are the differences between a DICTIONARY-MANAGED and LOCALLY managed
tablespace?
A:
The difference between the DICTIONALY-MANAGED
and LOCALLY MANAGED tablespace is: In the locally managed tablespace all
information about the datafiles such as the last performed checkpoint, etc are
stored locally in the datafiles of the tablespace but in the DICTIONAY-MANAGED
tablespace all such information would be stored in the Oracle repository in the
SYSTEM tablespace.
Q:
How do you create a TRANSACTION temporary table?
A:
SQL>
CREATE GLOBAL
TEMPORARY
TABLE
test_temp
(col1 NUMBER(5) PRIMARY KEY,
col2 VARCHAR2
(10) check (col2 BETWEEN 'A' AND 'T'))
ON COMMIT
DELETE
ROWS
/
Q:
What are the differences between a row migration and chained record?
A:
A “Chained Record” happens when a user
updates a record and the new value can not fit in the existing allocated
location. So, Oracle stores the value in a space that allocated for them (PCTFREE)
and add a pointer to the row so that it knows where the rest of the record is.
This process calls row Chaining. Now, if there was no space in the PCTFREE area,
then Oracle will take the whole record and migrate it in a different block that
has space. This process calls row Migration.
Q:
How do you monitor a usage of an index table?
A:
SQL>
ALTER INDEX uk_emp
MONITORING USAGE
/
Q:
What does the EXCEPTIONS INTO EXCEPTIONS clause perform in the ALTER
TABLE statement?
A: It will insert all exceptions that were found during
the ALTER TABLE command into the EXCEPTIONS table.
Q: How do you create a user account?
A:
SQL> CREATE USER developer
IDENTIFIED
BY developer
DEFAULT
TABLESPACE iself_data
TEMPORARY
TABLESPACE
temp
QUOTA
10K ON iself_data
QUOTA
0K ON SYSTEM
PROFILE
default
PASSWORD
EXPIRE
ACCOUNT
UNLOCK
/
Q: How do you assign a default tablespace to a user?
A:
SQL> ALTER USER developer
DEFAULT TABLESPACE iself_data
/
Q: How do you lock a user?
A:
SQL> ALTER USER DEVELOPER ACCOUNT
LOCK
/
Q:
What are the Oracle database triggers?
A:
The Oracle database triggers are triggers that
will occur when an Oracle event happens either by the DML statements such as
UPDATE, DELETE , INSERT, etc; the DDL statements such as DROP, CREATE, etc; the
Database events such as SHUTDOWN, STARTUP, etc; or events in a schema such as
dropping a table in an specific schema.
Q: How do you start or stop auditing?
A:
SQL> ALTER SYSTEM
SET
audit_trail=db
SCOPE=spfile
/
and you should shutdown and startup the database.
Then
start auditing by using the AUDIT command.
Ex: SQL> AUDIT DELETE
ON iself.emp
BY
ACCESS
WHENEVER SUCCESSFUL
/
And
stop auditing by using the NOAUDIT command.
Ex: SQL> NOAUDIT ALL
/
Q: What is a cluster table in the Oracle database?
A:
A cluster is a schema object that contains one
or more tables that all have one or more columns in common. Rows
of one or more tables that share the same value in these
common columns are physically stored together within the database. Generally,
you should only cluster tables that are frequently joined on the cluster key
columns in SQL statements. Clustering multiple tables improves the performance
of joins, but it is likely to reduce the performance of full table scans, INSERT
statements, and UPDATE statements that modify cluster key values.
Q:
How do you re-organize all a schema’s tables?
A:
SQL> EXECUTE
dbms_stats.gather_schema_stats ('your_schema_name');
|