“Discourage litigation. Persuade your neighbors to compromise whenever you can. As a peacemaker the lawyer has superior opportunity of being a good man. There will still be business enough.”

Abraham Lincoln

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?

“Force is all-conquering, but its victories are short-lived.”

Abraham Lincoln

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?

“He can compress the most words into the smallest ideas of any man I ever met.”

Abraham Lincoln

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?

“I am a firm believer in the people. If given the truth, they can be depended upon to meet any national crises. The great point is to bring them the real facts.”

Abraham Lincoln

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.

" If I were two-faced, would I be wearing this one?”

Abraham Lincoln

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
/

" If this is coffee, please bring me some tea; but if this is tea, please bring me some coffee.”

Abraham Lincoln

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?

“If you would win a man to your cause, first convince him that you are his sincere friend.”

Abraham Lincoln

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?

“It has been my experience that folks who have no vices have very few virtues.”

Abraham Lincoln

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.

“It is difficult to make a man miserable while he feels worthy of himself and claims kindred to the great God who made him.”

Abraham Lincoln

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.

“Let me not be understood as saying that there are no bad laws, nor that grievances may not arise for the redress of which no legal provisions have been made. I mean to say no such thing. But I do mean to say that although bad laws, if they exist, should be repealed as soon as possible, still, while they continue in force, for the sake of example they should be religiously observed.”

Abraham Lincoln

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.

“Most folks are about as happy as they make up their minds to be.”

Abraham Lincoln

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

“Nearly all men can stand adversity, but if you want to test a man's character, give him power.”

Abraham Lincoln

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;

“No man is good enough to govern another man without that other's consent.”

Abraham Lincoln

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

“Tact is the ability to describe others as they see themselves.”

Abraham Lincoln

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;

” When the conduct of men is designed to be influenced, persuasion, kind unassuming persuasion, should ever be adopted. It is an old and true maxim that 'a drop of honey catches more flies than a gallon of gall.' So with men. If you would win a man to your cause, first convince him that you are his sincere friend. Therein is a drop of honey that catches his heart, which, say what he will, is the great highroad to his reason, and which, once gained, you will find but little trouble in convincing him of the justice of your cause, if indeed that cause is really a good one.”

Abraham Lincoln

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

“Whatever you are, be a good one.”

Abraham Lincoln

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 ;

“When you have got an elephant by the hind leg, and he is trying to run away, it's best to let him run.”

Abraham Lincoln

RMAN-Managed the LIST and REPORT commands

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;

“I have always found that mercy bears richer fruits than strict justice.”

Abraham Lincoln, speech in Washington D.C., 1865

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.

“I don't like that man. I must get to know him better.”

Abraham Lincoln

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

" Die when I may, I want it said of me that I plucked a weed and planted a flower where ever I thought a flower would grow."

Abraham Lincoln

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?

"Whenever I hear anyone arguing for slavery, I feel a strong impulse to see it tried on him personally.”

Abraham Lincoln

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.

"The Lord prefers common-looking people.That is why He made so many of them. "

Abraham Lincoln

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?

"Character is like a tree and reputation like a shadow. The shadow is what we think of it; the tree is the real thing. "

Abraham Lincoln

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