|
Oracle DBA #2 Fundamental Exam Questions
Q: What are the Oracle Background Processes?
Q: Describe the V$BGPROCESS view.
Q: Describe the following background processes:
PMON
DBWn
ARC0
CKPT
LGWR
SMON
RECO
Q: Describe an archive log configuration.
Q: What does the ARCHIVE LOG LIST command?
Q: What are disadvantage and advantage of a database in
the NOARCHIVELOG mode?
Q: What are disadvantage and advantage of a database in
the ARCHIVELOG mode?
Q: What does the following SQL statememt?
SQL>
SELECT * FROM v$bgprocess
WHERE
PADDR
<>
'00'
/
Q: How do you test that a database is in archivelog or
not?
Q: What is the controlfile in the Oracle database?
Q: How do you get a list of all your controlfiles’
location?
Q: Describe the following views:
V$CONTROLFILE
view
V$CONTROLFILE_RECORD_SECTION view
Q: What do the following SQL statements?
SQL>
ALTER DATABASE
BACKUP CONTROLFILE
TO 'c:\backupcontrolfile\control_ddmmyyyy.ctl'
/
SQL>
ALTER DATABASE
BACKUP
CONTROLFILE
TO TRACE
/
SQL> SELECT * FROM v$controlfile_record_section
/
Q: You, as a DBA, are responsible to multiplex
controlfiles to protect your organization
from a possible and unexpected loss of
controlfiles, due to media failure. Your
task is to
add one more controlfile to you database. What are the steps?
Q: How do you configure your database to an archivelog
mode?
Q: How do you query your database’s archive log
information?
Q: How do you set an archive log destination?
Q: What is the Server Parameter file (SPFILE)?
Q: What do the following statements do?
ALTER
SYSTEM SET log_archive_start
=true SCOPE=spfile
/
ALTER SYSTEM SET log_archive_dest='c:\archivelogs' SCOPE=spfile
/
ALTER SYSTEM SET log_archive_format='arc%S.%T' SCOPE=spfile
/
Q: You, as a DBA, are responsible to recover any failures
to a point of failure and
also to perform your backup while in online status.
Your shop is 24x7 and you are
not able to shutdown the database. Therefore, the
database has to be in an archive
mode. You should change you database mode from
noarchivelog to archivelog
mode. What are the steps that you should perform to
change your database mode?
Q: Describe an online redo log file in a database.
Q: How do you add a redo log file group 3 to a database
structure?
Q: How do you resize a redo log file?
Q: How do you drop a redo log file 3?
Q: Describe the V$LOG and V$LOGFILE views.
Q: What does the following SQL statement?
SQL>
SELECT * FROM v$archived_log
WHERE recid >
(SELECT MAX(recid) - 10 FROM v$archived_log)
/
Q: You, as a DBA, are responsible to maintain and relocate
the Redo Log files in
order to distribute data among multiple hard disks to
increase I/O performance.
Your task is to relocate only of the redo log file
from it original location
c:\orignial_location to c:\newlocation sub-directory.
What are the steps?
Q: How do you set an Oracle-Managed archive log file
destination?
Q: Describe an Oracle-Managed File (OMF).
Q:
What are the following views?
V$ARCHIVE_DEST
view
V$ARCHIVED_LOG
view
V$LOG_HISTORY view
Q: What is the Sequence Archive log number?
Q: You, as a DBA, are responsible to duplex archived
Online Redo log files in order to
protect the organization from a loss of or a
case of corrupted archived files. Take one of
the redo log file group and add a
member to it in a different disk. What
are the steps?
Q: How many backup do we have?
Q: What is a cold or offline database backup?
Q: Describe a usage of the following views:
V$DATABASE view
V$LOGFILE
view
V$DATAFILE
view
V$CONTROLFILE
view
Q: To perform a COLD backup, does the database need to be
in an archivelog mode?
Q: You, as a DBA, are responsible to backup the database
and restore the data in
case of a loss of data due to media failure. Based on
your organization’s business rules,
the database can be shutdown every day for
5 hours. You also know the backup won’t
take more than an hour. You want to
use the COLD backup process. Write a script to
perform a complete cold backup.
Q: What is a “HOT” or “OFFLINE” backup?
Q: Describe the DBA_DATA_FILES
dictionary view.
Q:
How do you perform a hot backup on a tablespace?
Q:
What are the differences between a hot and cold backup?
Q:
What do the following SQL statements?
SQL>
ALTER TABLESPACE
tools
BEGIN BACKUP
/
SQL> HOST COPY \
C:\ORA9I\ORADATA\SCHOOL\TOOLS01.DBF
\
C:\userhotbkup\*
SQL> ALTER TABLESPACE
tools END BACKUP
/
Q:
Describe the V$DATAFILE
and DBA_DATA_FILES dictionary views?
Q:
Describe the TOTAL PAGES FAILING output from dbv utility.
Q:
How do you OFFLINE a tablespace?
Q:
How many different OFFLINE option do you have for a tablespace?
Q:
How do you perform an integrity check on a datafile?
Q:
What does the dbv utility?
Q
Can you ONLINE a tablespace that was OFFLINE with the TEMPORARY
or
IMMEDIATE options?
Q:
You, as a DBA, are responsible to backup the tablespace or datafile and
restore
the data to the point of failure in case of a loss of data due to a
media hard disk crash.
Your organization is a 24x7 day shop and you are not able
to shutdown the database.
You have to use HOT or ONLINE backup. How do you perform a tablespace backup?
Q: How do you store a destroyed datafile when Oracle is
online and running?
Q: How do you recover a tablespace?
Q: What does the following SQL statement?
SQL>
RECOVER TABLESPACE
users
/
Q: You, as a DBA, are responsible to recover the database to the
point of failure
due to a loss of data and a media failure. Assuming that you
lost your TOOLS’s
datafiles, what are the steps to recover the datafiles to
the point of failure?
Q: What is a physical backup?
Q: What is a logical backup?
Q: How do you perform a logical backup?
Q: How do you perform a logical restore?
Q: You, as a DBA, are responsible to perform a logical
backup using the EXP tool.
Notice that if the loss of data since the last time
of backup is not significant then a
logical backup is a good option to use.
Scott lost its EMP table and you have been
tasked to restore it using the IMP
utility.
Q: What are the steps to perform an automated Oracle COLD backup?
Q:
What does the UTL_FILE_DIR parameter?
Q:
What does the following SQL statements do?
GRANT SELECT ON v_$datafile TO xxx;
GRANT SELECT ON v_$logfile TO xxx;
GRANT SELECT ON v_$controlfile TO xxx;
Q: Write a stored procedure that will create a UNIX script, and then performs
your COLD backup.
Q: Write a UNIX or DOS script to create the "script4coldbackup.sh" script and then
run it in the coldbackup directory. Call it RunColdBackup.sh.
Q:
What are the steps to create an automated Oracle HOT backup?
Q:
Write a procedure
to
create a script to perform the actual HOT or ONLINE backup
Q:
Write a UNIX script to create the "script4hotbackup.sh" script
and then
run it in the hotbackup directory. Call it RunHotBackup.sh.
Q: How do you create the RMAN repository?
Q: How do you create the RMAN user?
Q: How do you create the RMAN objects?
Q: How do you create the RMAN tablespace?
Q: What does the RMAN RCVCAT command?
Q: What does the DBMS_RCVCAT package?
Q: What does the DBMS_RCVMAN package?
Q: What do the following SQL and RMAN commands do?
SQL>
CREATE TABLESPACE
rman_tablespace
DATAFILE 'c:/newfolder/rman_tablespace_01.dbf' SIZE 50M
AUTOEXTEND ON
/
SQL>
CREATE USER
rman
IDENTIFIED BY password
DEFAULT TABLESPACE rman_tablespace
QUOTA UNLIMITED ON rman_tablespace
/
SQL>
GRANT
CONNECT,
RECOVERY_CATALOG_OWNER, SYSDBA
TO rman
/
DOS> rman RCVCAT rman/password@dbs4rman
rman> CREATE CATALOG;
SQL> SELECT object_name FROM dba_objects
WHERE owner = 'RMAN'
and object_type = 'PACKAGE'
/
Q: How do you register a database to a RMAN utility?
Q: How do you synchronize a catalog?
Q: How do you backup a datafile of a database using RMAN?
Q: How do you backup a controlfile using RMAN?
Q: What do the following SQL and RMAN commands do?
SQL>
SELECT file_id, tablespace_name, bytes
FROM dba_data_files
/
DOS>
rman CATALOG
rman/password@dbs4rman
TARGET system/manager@school
rman>
REGISTER DATABASE;
rman>
RESYNC CATALOG;
rman>
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
–
'c:\newfolder\snape01.snp';
rman> BACKUP DATAFILE 7;
rman> BACKUP CURRENT CONTROLFILE;
rman> EXIT;
Q: How do you configure a RMAN retention policy?
Q: How do you exclude a tablespace from a daily backup?
Q: How do you perform an image copy of a datafile?
Q: Describe the NOCHECKSUM parameter.
Q: How do you perform an image copy of your current
controlfile?
Q: What do the following RMAN commands do?
DOS>
RMAN
RMAN>
CONNECT CATALOG
RMAN/password@dbs4RMAN
RMAN> CONNECT TARGET
system/manager@school
RMAN>
CONFIGURE RETENTION POLICY TO
REDUNDANCY 2;
RMAN> CONFIGURE RETENTION POLICY
TO
NONE;
RMAN>
CONFIGURE EXCLUDE FOR TABLESPACE
tools;
RMAN> COPY DATAFILE
7
TO 'c:\RMANBKUP\dbf_yymmdd.dbf' NOCHECKSUM;
RMAN> COPY CURRENT CONTROLFILE
TO 'c:\RMANBKUP\ctlfl_yyyymmdd.ctl';
RMAN> exit
Q: How do you perform a backup in a specific location?
Q: What is the formatting the backup file’s name?
Q: How can you backup database objects using the
incremental option?
Q: What do the following formatting name syntaxes mean?
%T
for the date
%S
for the backup set number
%P
for the piece number
Q: What do the following RMAN commands do?
DOS>RMAN
RMAN>BACKUPDATAFILE 7
FORMAT 'c:\RMANBKUP\file7_%T_%s_%p';
RMAN>BACKUPTABLESPACE
tools
FORMAT 'c:\RMANBKUP\tools_%T';
RMAN>BACKUP
INCREMENTAL LEVEL 0 TABLESPACE tools
FORMAT'c:\RMANBKUP\tools_L0';
RMAN>BACKUP INCREMENTAL LEVEL 1
TABLESPACE tools
FORMAT 'c:\RMANBKUP\tools_L1';
Q: How do you perform a tablespace recovery using RMAN?
Q: How do you set a datafile status using the RMAN tool?
Q: How do you restore a corrupted datafile?
Q: How do you recover a corrupted datafile?
Q: What do the following RMAN commands do?
RMAN>
SQL 'ALTER DATABASE
DATAFILE
7 OFFLINE';
RMAN>
RESTORE DATAFILE
7;
RMAN>
RECOVER DATAFILE
7;
RMAN> SQL 'ALTER DATABASE
DATAFILE
7 ONLINE';
RMAN> SQL 'ALTER TABLESPACE
tools
ONLINE';
Q: How do you perform a complete database backup using the
RMAN tool?
Q: Describe the LIST BACKUP command.
Q: What do the following RMAN commands do?
DOS>
rman CATALOG
rman/password@dbs4rman
& |