iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

 

DBA Fundamental II QUESTIONS

We are against multiple choice questions. 

Online Oracle Training

Oracle DBA #2 Fundamental Exam Questions

Oracle Background Processes

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?

 

Multiplexing Control Files

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?

Configuring the Database Archive mode

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?

 

Maintaining Redo Log files

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?

 

Duplexing Archived Online Redo Log files

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?

 

User-Managed COLD or OFFLINE backup

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.

 

User-Managed HOT or ONLINE 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
/

 

User-Managed Tablespaces and Datafiles 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?

 

User-Managed Tablespace Recovery

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?

 

User-Managed Logical Backup and recovery

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.

 

Automated Oracle COLD or OFFLINE backup

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.
 

Automated Oracle HOT or ONLINE backup

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.

 

RMAN-Managed user and repository

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'
/

Use of the RMAN commands

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;

 

Configuration of the RMAN tool

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

 

Using the RMAN backup utility

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

                     TARGET system/manager@school

rman> RUN
    {
       ALLOCATE CHANNEL mybackup TYPE disk;
       BACKUP DATABASE;
    }

rman> BACKUP DATABASE;

RMAN> BACKUP DATABASE FORMAT 'c:\RMANBKUP\full_%T_%s_%p';

rman> LIST BACKUP;   

Q:   Describe the SET TIME command in the SQLPLUS tool.

 

RMAN-Managed incomplete database recovery

Q:   What is an incomplete database recovery?

Q:   What are the differences between an incomplete database recovery and complete database recovery?

Q:   How do you perform an incomplete database recovery?

Q:   How do you perform a restore?

Q:   How do you recover a datafile until a specified time?

Q:   When do you use the RESETLOGS option?

Q:   What do the following RMAN commands do?

RMAN> SHUTDOWN IMMEDIATE;

RMAN> STARTUP MOUNT;
 RMAN> RUN
{
         SQL "ALTER SESSION

         SET NLS_DATE_FORMAT=''DD-MON-YYYY HH24:MI:SS''"; 
         SET UNTIL TIME '04-AUG-2002 01:04:22';
         RESTORE DATABASE;
         RECOVER DATABASE;
}

RMAN> OPEN RESETLOGS DATABASE;

 

RMAN-Managed the LIST and REPORT command

Q:   How do you perform a LIST and REPORT commands in the RMAN tool?

Q:   How do you write a RMAN script?

Q:   How do you replace a RMAN script?

Q:   How do you delete a RMAN script?

Q:   How do you run a RMAN script?

Q:   How do you get a list of the backup of the datafiles for a tablespace?

Q:   How do you get a list of objects that need backup?

Q:   How do you get a list of objects with their confirmed retention policy?

Q:   How do you get a list of a database incarnations?

Q:   How do you get a list of the backup sets that are obsolete?

Q:   How do you get a list of the image copies that are obsolete?

Q:   How do you get a list of the schemas that can be deleted?

Q:   What do the following RMAN commands do?

RMAN> CONNECT CATALOG RMAN/password@dbs4RMAN
RMAN> LIST BACKUP;
RMAN> LIST EXPIRED BACKUPSET;
RMAN> LIST BACKUP OF TABLESPACE tools;
RMAN> LIST INCARNATION OF DATABASE;

RMAN> REPORT NEED BACKUP;
RMAN> REPORT OBSOLETE;
RMAN> REPORT SCHEMA;

RMAN> CREATE SCRIPT backup_tools

      BACKUP TABLESPACE tools;
}

RMAN> RUN {EXECUTE SCRIPT backup_tools;}

RMAN> REPLACE SCRIPT backup_tools
{
     BACKUP TABLESPACE tools PLUS ARCHIVELOG
;
}

RMAN> DELETE SCRIPT backup_tools;

 

Oracle Network

Q:   What is Oracle Networking?

Q:   What tool does Oracle use to establish client/server connectivity?

Q:   What tool does a DBA use to configure an Oracle Network?

Q:   What is a dedicated server?

Q:   What is a shared server?

Q:   What is multithreaded sever (MTS)?

Q:   Describe a dispatcher jobs in the Oracle database.

Q:   Describe the Common Request Queue in the SGA.

Q:   What does the Oracle Connection Manager utility?

Q:   What does the Oracle Internet Directory (OID) provide for Oracle users?

Q:   What is the Oracle Names (ONAME) Utility?

Q:   Describe the Single Sign-On feature in the Oracle environment.

Q:   Describe the Oracle Wallet Manager feature in the Oracle environment.

 

Architecture of Oracle Network

Q:   What is a protocol?

Q:   How many different ways can a user connect to the Oracle database using Oracle NET?

Q:   Describe a connect string.

Q:   What does a connect descriptor contain?

Q:   What is a listener in the Oracle environment?

Q:   What is a System Identifier (SID) in an Oracle database?

Q:   What are the relationships between a listener, user processes, dedicated servers, shared servers, and dispatchers?

Q:  Describe the following command.

SQL> CONNECT iself/schooling@net_service_name

 

Server Side Configuration

Q:   How do you configure a LISTENER in your database server?

Q:   Can you have multiple listeners running on a database server?

Q:   How do you configure a LISTENER with one or more protocol?

Q:   Describe the PAMN background process jobs.

Q:   Describe the following listener configuration file (listener.ora).

LISTENER4MYDBS=

   (DESCRIPTION=

      (ADDRESS_LIST=

         (ADDRESS=(PROTOCOL=tcp) (HOST=mycomupter) (PORT=1521))

         (ADDRESS=(PROTOCOL=ipc) (KEY=extproc))

      )

   )

SID_LIST_LISTENER4MYDBS=

   (SID_LIST=

      (SID_DESC=

         (GLOBAL_DBNAME=mydbs.company)

         (ORACLE_HOME=/u01/app/oracle/product/9.2.0)

         (PROGRAM=extproc)

         (SID_NAME=mydbs)

       )

   )

Q:   How can you assign a password to you listener?

Q:   How do you turn on the trace utility on a listener on the user level?

Q:   How do you start a listener?

Q:   How do you stop a listener?

Q:   What does the reload command do in the listener?

 

Client Side Configuration

Q:   What are the various naming method supported by Oracle Net?

Q:   Describe the Host Naming Method.

Q:   Describe the Local Naming Method.

Q:   Describe the Oracle Names Method.

 

Configuring a Listener using the “Net Manager” tool

Q:   How do you add a listener using the Net Manager tool?

Q:   How do you modify a listener using the Net Manager tool?

Q:   How do you view all listeners in your server using the Net Manager tool?

Q:   How do you enable or disable a listener’s logging or tracing using the Net Manager tool?

 

Configuring a Service Name using the “Net Manager” tool

Q:   How do you add a service name using the Net Manager tool?

Q:   How do you modify a service name the Net Manager tool?

Q:   How do you test a service name using the Net Manager tool?

Answers

 

Google
 
Web web site