Database Recovery

Assumptions

It is assumed that you have a working knowledge of the Oracle data structures and syntax, and also some knowledge of Windows file systems.

For the purposes of this document it is assumed that you are either restoring a database that has ‘crashed’ / needs to be restored in a new location, or you are copying a database for test purposes.

You can login to the database as administrator.

Steps leading to failure:

1. The NT server crashes

2. Database is in archivelog mode

3. Online backup of datafiles and controlfile(s) is available

Option 1A:

Re-create the database in an identical machine, with the database files placed in the same directory. However, the archivelog files needed for rolling forward are located in a shared drive.

1. Create oracle service using oradim utility. Check that the services are created (START\CONTROL PANEL\SERVICES).

oradim -new -sid [INST_NAME] -intpwd [PASSWD] -startmode auto -pfile d:\ora805....

2. Copy the datafiles and controlfile(s) into proper directories. 

3. Once all database files are in place, change the log_archive_dest in the init.ora file to point to the shared drive (e.g. j:\archive).

4. Go to control panel\services and double click on the oracleservice[SID].

   Change info in "Log on As" from "SYSTEM ACCOUNT" to "THIS ACCOUNT" and enter the administrator user (e.g. "administrator"). Also enter and confirm the administrator password. Click on "APPLY" and "CLOSE".

5. Do the same for the oraclestart[SID] .

6. Stop then restart the service oracleservice[SID].

7. Open a DOS window, type "set oracle_sid= [SID]"

   An alternative would be to set this environment variable permanently. Click on Start/Settings/Control Panel/System. Then click on the environment tab and fill in VARIABLE (oracle_sid) and VALUE (SID). Then click on APPLY and OK.

8. Open server manager and connect internal/[passwd]

9. Startup in mount mode.

   (SVRMGR> startup mount pfile=d:\ora805\database...)

10. Do incomplete recovery.

   (SVRMGR>recover database using backup controlfile until cancel;)

11. Apply archive logs when prompted.

12. When oracle asks for archive log that does not exist, type "CANCEL".

13. Open the database.

   (SVRMGR> alter database open resetlogs).

14. Shutdown the database, and exit from Server Manager.

15. Modify the log_archive_dest parameter in the init.ora file back to local drive.

16.  Go back to control panel\services\oracleservice[SID] and modify 

     "Log on As" section back to "SYSTEM" account.

17.  Do the same for oraclestart[SID].

18.  Shutdown both services. Restart just oracleservice[SID].

19.  Go back to server manager and restart the database. 

20.  Shutdown the database and perform a full backup.

Note:

1. If the service "LOG ON AS" section is not changed from "SYSTEM" account, you

   will get the errors below:

ORA-00256 error occurred in translating archive text string 'j:\archive_dest'

ORA-09291 sksachk: invalid device specified for archive destination

OSD-04018 Unable to access the specified directory or device

2. If "UNTIL CANCEL" is not specified during recovery, you will get: 

   ORA-01113 file d:\orant\database\sys.dbf needs media recovery 

Scenario 2:

-----------

1. The NT server crashes

2. DB in archivelog mode

3. Online backup of datafiles is available

4. No backup of controlfile or a controlfile trace is available

1. Create service using oradim utility. (See Step 1, Option 1A for syntax)

2. Copy the datafiles into proper directories. 

3. Open a DOS window, type "set oracle_sid= [SID]".

4. Open Server Manager and connect internal/[password].

5. Startup in nomount mode.

  (SVRMGR> startup mount pfile=d:\ora805\database...)

6. Run the create controlfile script. You need to modify a few lines in this script. For example, be sure to have "RESETLOGS" and "ARCHIVELOG" in the CREATE CONTROLFILE statement. If you do not have RESETLOGS option for example, you will get the following errors since oracle will look for your redo log files. Remember the REUSE and SET keywords in the script. REUSE will REUSE the database and SET allows you to change the database name. You also have the option of ‘pre-positioning your data files etc.. before creating your control file. Just remember to reflect all changes

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

*

ORA-01503: CREATE CONTROLFILE failed

ORA-01565: error in identifying file 'D:\ORA_805\DATABASE\LOG4ORCL.ORA'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET DATABASE "MAPBACK2" RESETLOGS  ARCHIVELOG

--  SET STANDBY TO MAXIMIZE PERFORMANCE

    MAXLOGFILES 50

    MAXLOGMEMBERS 5

    MAXDATAFILES 100

    MAXINSTANCES 1

    MAXLOGHISTORY 113

LOGFILE

  GROUP 1 'E:\ORACLE\TEST\REDO01.LOG'  SIZE 100M,

  GROUP 2 'E:\ORACLE\TEST\REDO02.LOG'  SIZE 100M,

  GROUP 3 'E:\ORACLE\TEST\REDO03.LOG'  SIZE 100M

-- STANDBY LOGFILE

DATAFILE

  'E:\ORACLE\TEST\SYSTEM01.DBF',

  'E:\ORACLE\TEST\UNDOTBS01.DBF',

  'E:\ORACLE\TEST\CWMLITE01.DBF',

  'E:\ORACLE\TEST\DRSYS01.DBF',

  'E:\ORACLE\TEST\EXAMPLE01.DBF',

  'E:\ORACLE\TEST\INDX01.DBF',

  'E:\ORACLE\TEST\TOOLS01.DBF',

  'E:\ORACLE\TEST\USERS01.DBF',

  'E:\ORACLE\TEST\RBS01.DBF',

  'E:\ORACLE\TEST\MAP_TABLES01.DBF',

  'E:\ORACLE\TEST\MAP_LOB01.DBF',

  'E:\ORACLE\TEST\MAP_INDEXES_IM.ORA',

  'E:\ORACLE\TEST\MAP_INDEXES_PK.ORA',

  'E:\ORACLE\TEST\LOC2003091.DBF',

  'E:\ORACLE\TEST\LOC2003081.DBF',

  'E:\ORACLE\TEST\LOC2003071.DBF',

  'E:\ORACLE\TEST\LOC2003061.DBF',

  'E:\ORACLE\TEST\LOC2003101.DBF',

  'E:\ORACLE\TEST\LOC2003111.DBF'

CHARACTER SET WE8ISO8859P1

;

7. Do incomplete recovery.

  (SVRMGR>recover database using backup controlfile until cancel;)

8. Apply archive logs when prompted.

9. When oracle asks for archive log that does not exist, type "CANCEL".

10. Open database 

    (SVRMGR> alter database open resetlogs;) 

11. Shutdown the database and perform a full backup.

Oracle date format

With the functions to_char and to_date, a date format can be used.

Example:
select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;
will return something like: 24/03/2006 14:36:43

Here is a list of all the formats that can be used:
Format mask Description
CC Century
SCC Century BC prefixed with -
YYYY Year with 4 numbers
SYYY Year BC prefixed with -
IYYY ISO Year with 4 numbers
YY Year with 2 numbers
RR Year with 2 numbers with Y2k compatibility
YEAR Year in characters
SYEAR Year in characters, BC prefixed with -
BC BC/AD Indicator *
Q Quarter in numbers (1,2,3,4)
MM Month of year 01, 02...12
MONTH Month in characters (i.e. January)
MON JAN, FEB
WW Weeknumber (i.e. 1)
W Weeknumber of the month (i.e. 5)
IW Weeknumber of the year in ISO standard.
DDD Day of year in numbers (i.e. 365)
DD Day of the month in numbers (i.e. 28)
D Day of week in numbers(i.e. 7)
DAY Day of the week in characters (i.e. Monday)
FMDAY Day of the week in characters (i.e. Monday)
DY Day of the week in short character description (i.e. SUN)
J Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH Hournumber of the day (1-12)
HH12 Hournumber of the day (1-12)
HH24 Hournumber of the day with 24Hours notation (1-24)
AM AM or PM
PM AM or PM
MI Number of minutes (i.e. 59)
SS Number of seconds (i.e. 59)
SSSSS Number of seconds this day.
DS Short date format. Depends on NLS-settings. Use only with timestamp.
DL Long date format. Depends on NLS-settings. Use only with timestamp.
E Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE The full era name
FF The fractional seconds. Use with timestamp.
FF1..FF9 The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM Fill Mode: suppresses blianks in output from conversion
FX Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I the last 3,2,1 digits of the ISO standard year. Output only
RM The Roman numeral representation of the month (I .. XII)
RR The last 2 digits of the year.
RRRR The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC Century. BC dates are prefixed with a minus.
CC Century
SP Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH Spelled and ordinal format; 1 results in first.
TH Converts a number to it's ordinal format. For example 1 becoms 1st.
TS Short time format. Depends on NLS-settings. Use only with timestamp.
TZD Abbreviated time zone name. ie PST.
TZH Time zone hour displacement.
TZM Time zone minute displacement.
TZR Time zone region
X Local radix character. In america this is a period (.)

SUBSTR

SUBSTR Returns a substring. For more information see Oracle substring
SUBSTRB Returns a substring expressed in bytes instead of characters.
SUBSTRC Returns a substring expressed in Unicode code points instead of characters.
SUBSTR2 Returns a substring using USC2 code points.
SUBSTR4 Returns a substring using USC4 code points.

SOUNDEX

SOUNDEX returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English.

Example: select * from emp where lastname SOUNDEX('SMITH');

RTRIM

RTRIM removed characters from the right of a string if they are equal to the specified string.

Example: rtrim('bcaaaaaa','a') = 'bc'

If the last parameter is not specified, spaces are removed from the right side.

RPAD

Add characters to the right of a string until a fixed number is reached.

Example: rpad('abc',8,'x') = 'abcxxxxx'.

If the last parameter is not specified, spaces are added to the right.

REVERSE

Reverses the characters of a String.

Example: REVERSE('1234567890') = '0987654321'

REPLACE

The replace function replaces every occurrence of a search_string with a new string. If no new string is specified, all occurrences of the search_string are removed.

Example: replace('a1a1a1','a','2') = '212121'.

LTRIM

LTRIM removed characters from the left of a string if they are equal to the specified string.

Example: ltrim('aaaaaabc','a') = 'bc'

If the last parameter is not specified, spaces are removed from the left side.

LPAD

Add characters to the left of a string until a fixed number is reached. Example: lpad('abc',8,'x') = 'xxxxxabc'. If the last parameter is not specified, spaces are added to the left.

LENGTH

Oracle string functions

The LENGTH functions returns the length of char. LENGTH calculates length using characters as defined by the input character set.

Example: length('abcdefghij') = 10

LENGTHB Returns the length of a string, expressed in bytes.

INITCAP

Oracle string functions

Transform String to init cap

Example: INITCAP('UPPERCASE') = 'Uppercase'

DUMP

Oracle string functions

The DUMP function returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set.

CONVERT

Oracle string functions

The CONVERT function converts a string from one characterset to another. The datatype of the returned value is VARCHAR2.

Example: CONVERT('This is an example','UTF-8','WE8ISO8859P1' )

SELECT CONVERT('Ä Ê Í Ó Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;
-------------
A E I ? ? A B C D E ?

CONCAT

Oracle string functions

The CONCAT function returns the concatenation of 2 strings. You can also use the || command for this.

Example: CONCAT('abc','def') = 'abcdef'

COALESCE

Oracle string functions

The COALESCE function returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null. Example: select COALESCE(col1, col2, col3) FROM emp;

ASCII

The ASCII function returns the decimal representation in the database character set of the first character of char. Example: ASCII('b') =98

TRIM - function

Oracle pl/sql trim function

The trim function removed characters from beginning and/or end of a string in Oracle. Oracle has 3 functions for this:

TRIM
The TRIM function trims specified characters from the left and/or right.If no characters are specified, the left and right spaces are left out.
Example: trim(' will it trim ') = 'will it trim'.

Another option is:
trim(trailing 'a' from 'aaaabcbaaaa') which results in 'aaaabb' or
trim(leading 'a' from 'aaaabcbaaaa') which results in 'bbaaaa' or
trim(both 'a' from 'aaaabcbaaaa') which results in 'bb'.

LTRIM
LTRIM removes characters from the left of a string if they are equal to the specified string. Example: ltrim('aaaaaabc','a') = 'bc' If the last parameter is not specified, spaces are removed from the left side.

RTRIM
RTRIM removes characters from the right of a string if they are equal to the specified string. Example: rtrim('bcaaaaaa','a') = 'bc' If the last parameter is not specified, spaces are removed from the right side.

CHR - function

CHR: The CHR function returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set.

Example: CHR(10)CHR(13) = carriage return plus line feed.

In Oracle/PLSQL, the CHR function is the opposite of the ascii function. It returns the character based on the NUMBER code.

The syntax for the chr function is: CHR( number_code [, USING NCHAR_CS])
where the number_code is the NUMBER code used to retrieve the character.

Use of the CHR function (either with or without the optional USING NCHAR_CS clause) results in code that is not portable between ASCII- and EBCDIC-based machine architectures.

For single-byte character sets, if n > 256, then Oracle returns the binary equivalent of n mod 256. For multibyte character sets, n must resolve to one entire codepoint. Invalid codepoints are not validated, and the result of specifying invalid codepoints is indeterminate.

DBA Fundamentals II (1Z0-032 ) - What you need to know

What you should know to pass this test:

Understand the architecture of Oracle Net (called Net8 or SQL*Net in prior releases).

Know how to configure Oracle clients, servers, and the shared server option. Especially understand the different configuration options for clients and how to set up each. Know how to debug Oracle Net problems.

Understand the steps Oracle’s Listener takes in responding to user requests. How do these steps differ for bequeathing and the shared server option? What is the role of the dispatchers, and what SGA memory areas do they use?

For backup and recovery, understand Oracle’s architecture and how the database product handles database updates, transactions, redo logs, and archived logs. Which instance background processes have a role in these activities?

What is the difference between redo and undo records?

Know what transactions are, and how they relate to recovery scenarios.

Know what a database checkpoint is, what background processes are involved in it, and how it differs from a COMMIT.

Understand RMAN’s purpose and benefits, and when to use an RMAN catalog. Also be familiar with the RMAN commands, and installation and maintenance procedures.

Know how to backup a database, with and without RMAN.

Know how to perform complete and incomplete database recovery with and without RMAN.

Know the differences between ARCHIVELOG and NOARCHIVELOG mode databases. What are the advantages of each, and how do they differ when it comes to recovery?

Understand when you must shutdown a database to recover it, versus when you can do a “database-up” recovery.

What is the difference between “restore” and “recovery”? What is the role of the redo and archive logs in recovery? When can you recover without logs versus when are they required?

Understand the roles of LogMiner and Oracle Flashback Query in recovering from user error.

Understand the role and uses of the Export and Import utilities. How do they complement physical database backups and recoveries? Know the main Export and Import commands.

What is the DB_VERIFY utility and how is it used?

Be prepared to answer questions about which data dictionary views you would query to locate backup and recovery information.

Hot or Cold Backup?

Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup means taking a backup of the database while it is still up and running where the database is in archive log mode. A cold backup means taking a backup of the database while it is shut down and does not require that it is in archive log mode.

The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time.

The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

To View Sort Area Information

Using the following query we can get some information about the sorting happening on a particular database.

SELECT *
  FROM v$sysstat
 WHERE NAME LIKE '%sorts%'

STATISTIC# NAME         CLASS VALUE         STAT_ID
341         sorts (memory) 64 27568047 2091983730
342         sorts (disk) 64 158         2533123502
343         sorts (rows) 64 9867427817 3757672740

sorts (memory) - If the number of disk writes is non-zero for a given sort operation, then this statistic is incremented. Sorts that require I/O to disk are quite resource intensive. Try increasing the initialization parameter SORT_AREA_SIZE.

sorts (disk) - If the number of disk writes is zero, then the sort was performed completely in memory and this statistic is incremented. This is more an indication of sorting activity in the application workload. You can't do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.

The sorting algorithms and resources used have improved with every release of oracle and I see future releases improving further in this regard.

To View SGA Information

The (SGA) System Global Area is shared memory structures that are created at instance startup. They hold information about

the instance, and control its behavior. The following query gives a window into the various memory pools available in the

SGA.

SELECT NAME, VALUE
  FROM v$parameter
 WHERE NAME IN
          ('shared_pool_size', 'java_pool_size', 'streams_pool_size',
           'log_buffer', 'db_cache_size', 'db_2k_cache_size',
           'db_4k_cache_size', 'db_8k_cache_size', 'db_16k_cache_size',
           'db_32k_cache_size', 'db_keep_cache_size', 'db_recycle_cache_size',
           'large_pool_size');

and the sizes of the various pools in use.

SELECT NAME, pool, ROUND (BYTES / 1024 / 1024, 2) free_mb
  FROM v$sgastat
 WHERE NAME IN
          ('%free memory%', 'parameters', 'memory in use', 'db_block_buffers',
           'log_buffer', 'dictionary_cache,', 'sql area', 'library cache');

Finding the Global Database Name

The full name of the database which uniquely identifies it from any other database. The global database name is of the

form "database_name.database_domain," for example, sales.us.acme.com.

The database name portion, sales, is a simple name you wish to call your database. The database domain portion,

us.acme.com, specifies the database domain in which the database is located, making the global database name unique. When

possible, Oracle Corporation recommends that your database domain mirror the network domain.

The global database name is the default service name of the database, as specified by the SERVICE_NAMES parameter in the

initialization parameter file.

SELECT NAME, value$
  FROM SYS.props$
 WHERE NAME = 'GLOBAL_DB_NAME';

or

SELECT *
  FROM GLOBAL_NAME;

How do I find my SID?

The SID is the Oracle System ID. It is used to uniquely identify a database. In RAC, all instances belonging to the same database must have unique SID's.



In Windows:


set ORACLE_SID=orcl



Unix/ Linux:


export ORACLE_SID=orcl

SELECT enabled, open_time, status, INSTANCE
  FROM v$thread;

Oracle Version Number?

You may need to find what version of database you are running. Not often I admit, but it can happen.

SELECT banner FROM V$VERSION;

Not difficult at all.

Migrating Oracle

I have just been migrating a couple of databases off of VMS running Oracle version 8 to a brand new Solaris 10.2 environment.

Fortunately both of the Oracle databases were small being less than 100G each, and most of that indexes, so the actual move went really smoothly. In fact the business was very impressed at how little interruption there actually was, seeing as we did it all out of office hours.

What they don't know was, I and another Oracle developer, had already migrated these systems close on ten times already as practise runs. We had been tripped up before, stumbled around all the problems and solved what we hoped was all the performance issues. What processes could be scripted, had been, what could be tested beforehand, had been. What we expected, we got.

The actual go-live migration was anti climatic in the extreme. Merely a matter of watching the process scripts run, ticking off checkpoints and checking logfiles. All the hard work and mistakes had been done in the practise runs. We had removed most of the risk to routine and what risk remained we managed.

From the practise runs we were able to gather vital information.
Firstly :- would it all work.
Secondly :- could we do it in a reasonable time.
Thirdly :- was it justified.

We were able to answer those questions and go to the business with our times and schedule for migration and happily it all went to plan and we were up and running an hour before we said we would be.

We had allowed ourselves a two hour window of extra time in case things went terribly wrong and I am happy to say that we spent most of one of our hours of extra time ensuring that datafiles were in the correct place, logfiles were generated in the correct place, we could flashback, and most importantly getting a baseline RMAN backup.

If things had gone wrong we wouldn't have heard the end of it. As it is, our manager thanked us for a job well done and then went to inform the rest of the business who hadn't even noticed.

Ah, the joys of a career in IT. If it goes wrong - everyone knows, if it goes right - no-one cares.

Create a sequence AAAAA - ZZZZZ

Create a sequence AAAAA - ZZZZZ

--sequence AAAAA to ZZZZZ....... wow.

 select   CHR(65+MOD((rownum-1)/(26*26*26*26), 26))
        ||CHR(65+MOD((rownum-1)/(26*26*26), 26))
        ||CHR(65+MOD((rownum-1)/(26*26), 26))
        ||CHR(65+MOD((rownum-1)/26, 26))
        ||CHR(65+MOD((rownum-1), 26)) as AA_ZZ_sequence
   from dual 
connect by level <= 26*26*26*26*26;

This is along the lines of a trick sql statement and demonstrates what can be achieved when you use the connect by terminology.

What is a DBA?

A DBA is seen as the best person to go to when there is a database problem and even sometimes a general problem. Why? Well a DBA is usually someone who has spent most of their working life solving problems and finding ways to do things more simply or clearly. More often than not, a DBA will have the answer to a database problem or can point the way to the solution.

A DBA will have a general background and working knowledge of may different areas of IT:

Data administration – The data administration role, although important, is often undefined in many IT organizations. Those responsibilities, by default, are usually awarded to the shop's database administration unit. Data administrators view data from the business perspective and must have an understanding of the business to be truly effective. DBAs organize, categorize and model data based on the relationships between the data elements themselves and the business rules that govern them. Data administrators provide the framework for defining and interpreting data and its structure enabling the organization to share timely and accurate data across diverse program areas resulting in sound information-based decisions.

Operating system – The only folks that spend more time in the operating system than DBAs are the system administrators themselves. Database administrators must have an intimate knowledge of the operating systems and hardware platforms their databases are running on. DBAs automate many functions and are usually accomplished operating system scriptwriters. They have a strong understanding of operating system kernel parameters, disk and file subsystems, operating system performance monitoring tools and various operating system commands.

Networking – Database administrators are responsible for end-to-end performance management. End users don't care where the bottleneck is, they just want their data returned quickly. DBAs need to have expertise in basic networking concepts, terminologies and technology to converse intelligently with LAN administrators.

Data Security – Much to the consternation of many business data owners, the DBA is usually the shop's data security specialist. They have complete jurisdiction over the data stored in their database environments. The DBA uses the internal security features of the database to ensure that the data is available only to authorized users.

Oracle Database Administration Responsibilities.
------------------------------------------------

There is no one exhaustive list of all the duties that a DBA may be asked to perform. As a general rule though, a DBA will encounter most combinations of possible tasks in the progression of their career.

Those new to Oracle should work through topics at their leisure by picking one area and reading the Oracle documentation, trying out the exercises and attempting various smaller projects to try out your new skills. Then move on to a new area. You will soon find that after completing a few sections there are similarities in ideas and overlaps in knowledge between areas of Oracle.

Taking Oracle Classroom Education.
------------------------------------

When is the best time to take the classes? This may sound trite, but it is best to follow Oracle's recommendations on the sequence of classes. Take the intro classes before taking the more advanced classes. If you have the luxury (meaning you aren't the only DBA in your shop), gain some day-to-day experience before taking the more advanced classes (SQL or database tuning, backup and recovery, etc.). You shouldn't be asking questions like "What is an init.ora parameter file, anyway?" in a tuning or backup and recovery class. Instructors don't have the time and your fellow students won't have the patience to bring you up to speed before continuing on to more advanced topics.

If it is an emergency situation, like your shop's DBA gives two week's notice (Oracle DBAs are now considered to be migratory workers by many companies) bring yourself up to speed by:

Reading as much information as you can on the class you are taking before you take it. Oracle press books, Oracle's Technet web site and non-Oracle consulting company's web sites contain a wealth of information. Read the course descriptions and course content at Oracle Education's website ( http://education.oracle.com). You may not know the mechanics, but you do need to know the lingo and the concepts used.

When you attend the class, inform your instructor that you don't have a lot of day-to-day experience. We want you to get the most out of class, we'll help you by staying later, coming in earlier and giving you reading recommendations.

Familiarize yourself with the next day's material by reading it the night before. If an instructor sees that you are making an extra effort to overcome your lack of day-to-day experience by coming in early, staying late and being prepared, they will be more prone to help you. Instructors like to see people excited about what we are teaching. Seeing someone enthused about learning makes us want to make sure they get the most out of class. Don't let your ego get in the way of you getting the utmost benefit of the class you are taking - ask questions and get involved!

Oracle9i Curriculum Changes.
------------------------------
The Oracle9i Instructor Led Training (ILT) Classes have been dramatically changed for Oracle9i. The Oracle9i curriculum consists of the following ILT classes:

Introduction to Oracle9i: SQL (5 Days) - This class prepares students for Oracle Certification Test #170-007. You'll notice that the title is a little different that the Oracle8i Introductory Class. The Oracle8i class titled "Introduction to Oracle8i SQL and PL/SQL" included several chapters on understanding and writing Oracle's procedural language PL/SQL. PL/SQL is no longer taught in the Oracle9i:SQL introductory class. It has been replaced with information on Oracle's ISQL*Plus product, correlated subqueries, GROUP BY extensions (ROLLUP, CUBE), multitable INSERT statements and how to write SQL statements that generate SQL statements.

Introduction to Oracle8i SQL and PL/SQL (5 days) - Although titled as an Oracle8i class, the class is intended to prepare students for Oracle9i Certification Test #170-001 (Introduction to Oracle SQL and PL/SQL). The class provides in-depth information on SQL including information on SQL basics, joins, aggregations and subqueries. In addition, the class also provides information on basic PL/SQL programming.

Oracle9i Database Administration Fundamentals I (5 Days) - The Oracle9i Database Administration Fundamentals I class prepares students for Certification Test #170-001. The new Oracle9i DBA intro class is much like it's Oracle8i counterpart that was titled "Enterprise DBA Part1A: Architecture and Administration." Although much of the material remains the same, there are a few changes that should be noted: the export and import information has been moved to the Fundamentals II class, Oracle's load utility (SQL*Loader) is no longer covered and more time is spent learning and using Oracle's administrative toolkit Oracle Enterprise Manager.

Oracle9i Database Administration Fundamentals II (5 Days) - This class prepares students for Certification Test #170-032. The class combines a small subset of the material covered in the two-day Oracle8i "Enterprise DBA Part3: Network Administration" class with all of the information covered in the four-day "Enterprise DBA Part1B: Backup and Recovery" class. The class also contains information on Oracle's Export and Import utilities for good measure.

Oracle9i Database Performance Tuning (5 Days) - The Oracle9i database tuning class prepares students for Certification Test #170-033 and follows the same general format of the Oracle8i tuning class with four days of classroom instruction followed by a one-day workshop.


Oracle9i Oracle Certifications.
-------------------------------
Oracle has also changed the certification process for Oracle9i. Database administrators wanting to become Oracle8i Certified Professionals (OCP) were required to pass 5 certification tests, one for each database administration class that Oracle offered: Intro, DBA Part 1A: Architecture and Administration, DBA Part 1B: Backup and Recovery, DBA Part 2: Tuning and Performance and DBA Part 3: Network Administration. Oracle has changed the certification process for Oracle9i by adding two new certifications (Associates and Masters) and requiring additional hands-on classroom training to obtain Oracle Certified Professional certification.

Oracle Certified Database Associate (OCA).
------------------------------------------
Two exams are required to become an Oracle Certified Database Associate. Those wanting to become Oracle Certified Database Associates must pass either the "Intro To Oracle9I: SQL" or the "Intro to Oracle: SQL and PL/SQL" certification tests and pass the "Oracle9i Database Administration Fundamentals I" exam.

The minimum scoring requirements and test durations for the Oracle Certified Database Associate tests are as follows: Introduction to Oracle: SQL and PL/SQL - Oracle Certification Test #170-007 contains 57 questions. The test requires 68% (39 questions) to be correctly answered to pass. The test must be completed within two hours.

Introduction to Oracle9i: SQL – Oracle Certification Test #1Z0-007 contains 57 questions. The test requires 70% (40 questions) to be answered correctly to pass. The test must be completed within two hours.

Note: This is the only certification test that can be taken online at the Oracle Education website (http://education.oracle.com). If you do not have good Internet access, Oracle also allows the test to be taken at an Oracle University Training Center or an Authorized Prometric Testing Center. All other certification tests must be taken at a Prometric (see section on Prometric Testing Centers below).

Oracle Database: Fundamentals I - Oracle Certification Test #170-031 contains 60 questions. The test requires 73% (44 questions) to be correctly answered to pass. Students must complete the test within 1.5 hours.

Oracle Certified Database Professional (OCP).
---------------------------------------------
Administrators wanting to become Oracle Certified Database Professionals must first start their journey by earning their Oracle Certified Database Associate certification. In addition, candidates must also pass two additional tests: "Oracle9i Database Administration Fundamentals II" and "Oracle9i Database Performance Tuning". The final requirement is to attend at least one of the following Oracle University hands-on courses:

Oracle9i Introduction to SQL
Oracle9i Database Fundamentals I
Oracle9i Database Fundamentals II
Oracle9i Database Performance Tuning
Oracle9i Database New Features
Introduction to Oracle: SQL and PL/SQL

The minimum scoring requirements and test durations for the
Oracle Certified Database Professional tests are as follows:

Oracle Database: Fundamentals II - Oracle Certification Test #170-032 contains 63 questions. The test requires a 77% correct answer score (49 questions) to be correctly answered to pass. Students must complete the test within 1.5 hours.

Oracle Database: Performance Tuning - Oracle Certification Test #170-033 contains 59 questions. The Performance Tuning test requires a 64% correct answer score (38 questions) to be correctly answered to pass. Students must complete the test within 1.5 hours.

Oracle Certified Master Database Administrator (OCM).
-----------------------------------------------------

Those wanting to reach "Oracle Nirvana" and become Oracle Certified Masters must first earn their Oracle Certified Professional (OCP) certification. Candidates must also attend two of the eight advanced Oracle University hands-on courses listed below:

Oracle Enterprise Manager 9i
Oracle9i SQL Tuning Workshop
Oracle9i Database: Implement Partitioning
Oracle9i Database: Advanced Replication
Oracle9i Database: Spatial
Oracle9i Database: Warehouse Administration
Oracle9i Database: Security
Oracle9i: Real Application Clusters

The final step is to attend (and pass) a two-day live application event that requires participants to complete a series of scenarios and resolve technical problems in an Oracle9i database environment. Attendees will be scored on their ability to successfully complete the assigned tasks.

Oracle 9i DBA OCP Upgrade Path.
-------------------------------
Database administrators wanting to continue to stay current as an Oracle Certified Professional are able to upgrade their certifications by completing the Oracle migration exams. Oracle Education provides multiple upgrade certification tests to allow administrators to upgrade their current certification to Oracle 9i OCP.

The number of tests the DBA must take depends upon their current certification. OCP DBAs must complete each upgrade test in order to upgrade their OCP credentials. An Oracle7.3 DBA OCPs would be required to pass exam #1Z0-010, 1Z0-020 and 1Z0-030 to upgrade their OCP credential to Oracle9i Database Administrator.

Each upgrade exam closely follows the material provided in the Oracle database new features classes, which provide information on all of the new "bells and whistles" contained in the release. Administrators studying for the upgrade certification should focus on the material provided in the New Features section of the documentation provided with the Oracle software.

Listed below are the migration exams that are currently available to previously certified Oracle DBAs (the descriptions are from our Oracle instructor's website):

Upgrade Exam: Oracle7.3 to Oracle8 OCP DBA (#1Z0-010). - This exam covers information provided in the Oracle8 New Features for Administrators class. The exam focuses on partitioned tables and indexes, parallelizing INSERT, UPDATE and DELETE operations, extended ROWIDS, defining object-relational objects, managing large objects ( i.e. LOBS, CLOBS), advanced queuing, index organized tables and Oracle8 security enhancements.

Upgrade Exam: Oracle8 to Oracle8i OCP DBA (#1Z0-020) - The Oracle8i upgrade exam covers information provided by the Oracle8i New Features for Administrators class. This exam focuses on the Oracle Java implementation, optimizer and query performance improvements, materialized views, bitmap index and index-organized enhancements and range, hash and composite partitioning. The exam also covers Oracle installer enhancements, locally managed tablespaces, transportable tablespaces and the Oracle8i database resource manager.

Upgrade Exam: Oracle8i to Oracle9i OCP DBA (#1Z0-030) - Like its aforementioned counterparts, the Oracle9i upgrade covers information provided by the Oracle9i New Features for Administrators Class. The exam covers information on fine grained auditing, partitioned fine grained access control, secure application roles, global context, flashback query, resumable space allocation, SPFILEs, log miner enhancements and recovery manager new features.

Other Recommend Classes.
------------------------
Oracle Education provides dozens of additional classes on Oracle technologies. These additional classes provide more in-depth information in key areas of database administration. A few recommendations on additional classes follow (the course descriptions are straight from our instructor's website):

Oracle9I Program with PL/SQL. An excellent class for database administrators wanting to learn more about Oracle's procedural language. This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. Students learn to create stored procedures, SQL functions, packages, and database triggers.

Oracle9i Database: SQL Tuning Workshop R2. This course is designed to give the student a firm foundation in the art of SQL tuning. The participant learns the skills and toolsets used to effectively tune SQL statements. The course contains numerous workshops that allow students to practice their SQL tuning skills. The students learn EXPLAIN, SQL Trace and TKPROF, SQL*Plus AUTOTRACE.

Oracle Enterprise Manager 9i. This course is taught on Oracle9i Release 2. Students learn how to use Oracle Enterprise Manager to effectively administer a multiple database environment. This class is a must attend for those that want to make sure that they are utilizing Oracle Enterprise Manager to its fullest potential.

Oracle9i: New Features for Administrators R2. This course introduces students to the new features in contained in Oracle9i. All of the latest features are discussed and tested.

Managing Oracle on Linux. Students learn how to configure and administer the Oracle9i database on Linux. Database creation, configuration, automated startup/shutdown scripts, file system choices are just a few of the topics covered in this class. Hands-on lab exercise help students reinforce the knowledge obtained from lectures.

Preparing for the Oracle Certified Professional Exams.
------------------------------------------------------
The best time to take the exam is a week or two after taking the Oracle class that the exam pertains to. Passing the certification test is much easier when the information is fresh. The class workbook should be used as the primary study guide. I have passed every exam I have taken by studying only the information contained in the class workbooks. The classes are not required to obtain Oracle9i OCP certification, but the requirements have changed for Oracle9i OCP certification (see Oracle9i Oracle Certifications above).

The Oracle Education website (http://education.oracle.com) allows administrators to purchase practice exam tests. Free sample questions are also available. Practice tests provide the administrator with a firm understanding of the areas that they are strong in as well as the areas where they need to shore up their knowledge.

The Oracle provided practice tests provide a thorough coverage of the Oracle certification requirements and use the same test question technology as the real exams including simulations, scenarios, hot spots and case studies. Other practice test features include:

Tutorials and text references that enhance the learning process.

Random generation of test questions provides a dynamic and challenging testing environment.

Testing and grading by objective to allow administrators to focus on specific areas.

Thorough review of all answers (both correct and incorrect).

More questions provided than any other source.

Taking the Certification Exams.
-------------------------------
Oracle partners with Prometric Testing Centers to provide testing centers throughout the world. The Prometric Testing Center website ( http://www.2test.com/) provides a test center locator to help you find testing centers in your area.

The following hints and tips will prepare you for the day you take your certification tests:

You must have two forms of identification, both containing your signature. One must be a government issued photo identification.

Try to show up early (at least 15 minutes) before your scheduled exam. If you show up more than 15 minutes late, the testing center coordinator has the option of cancelling your exam and asking you to reschedule your test.

You cannot bring any notes or scratch paper to the testing center. Paper will be provided by the testing center and will be destroyed when you leave.

Testing center personnel will provide you with a brief overview of the testing process. The computer will have a demo that will show you how to answer and review test questions.

Don't leave any questions unanswered. All test questions left unanswered will be marked as incorrect.

Your exam score is provided to you immediately and the exam results are forwarded to Oracle Certification Program management. Make sure you keep a copy of your test results for your records.

If you fail a test, you must wait at least 30 days before retaking it (except for exam #1Z0-007 Introduction to Oracle9i: SQL).

Finding Information Quickly - The Key to Success.
-------------------------------------------------
If you remember anything from this whitepaper, make it the
following statement:

The hallmark of a being a good DBA is not knowing everything, but knowing where to look when you don't.

But there is so much information available on Oracle that it tends to become overwhelming. How do you find that one facet of information, that one explanation you are looking for when you are confronted with seemingly endless sources of information? Here's a hint, GO TO THE MANUALS FIRST. The Concepts manual is a good start, closely followed by the SQL*PLUS Users Guide, the Administrator's Guide, the Reference manual and the SQL Reference manual. The next two should be Oracle Backup and Recovery Concepts and the Oracle Enterprise Manager User's Reference Manual. If you don't find the information you are looking for in Oracle's Technical Reference Guides, then look elsewhere. Whether you have been burned or not, you must trust the information they provide. This paper will provide you with alternative sources of information but they are NOT intended to be substitutions for the vendor's reference guides.

A very experienced co-worker of mine was at a customer site installing an Oracle9i database on LINUX. He was reading the installation manual when the customer demanded to know why he was reading the manual when he was supposed to be "the high-priced expert." He quickly replied, "I'm reading the manual because I am an expert." As your experience grows, you'll find that you'll become just like my co-worker, an avid user of the reference guides and not afraid to admit it.

Reference Manuals.
------------------
It is good practice to keep a set of reference manuals for each major release of the database you are administering. Oracle does have a tendency to change default values for object specifications. In addition, each new release contains new parameters that affect the database's configuration. When you receive the latest and greatest version of Oracle's database (one of the benefits of purchasing support), turn straight to the "OracleX New Features" section to find out what impact the new release will have on your daily administrative activities. You'll also find many new features that haven't been covered by Oracle's new release whitepapers and marketing propaganda.

Oracle Internal Resources.
--------------------------
The Oracle websites contain a wealth of information on the Oracle product sets. The trick is knowing where to look. Some may think that Oracle webmasters rewrite the websites from time to time just to make it challenging for us to find the information we are looking for.

The following Oracle websites are favorites of mine and are ranked according to my personal preference:

metalink.oracle.com - Oracle's premier web support service is available to all customers who have current support service contracts. Oracle MetaLink allows customers to log and track service requests. Metalink also allows users to search Oracle's support and bug databases. When you experience an Oracle problem, look up the return code (if one is provided) in the Oracle reference manuals. If you are unable to solve the problem, search the Metalink bug database using the return code or error message as the search criteria. The website also contains a patch and patchset download area, product availability and life cycle information and technical libraries containing whitepapers and informational documents.

docs.oracle.com – Oracle's technical reference manual website. This website stores technical reference manuals for Oracle7, Oracle8, Oracle8i, Oracle9i, Oracle RDB, Oracle Gateways and Applications 10.7, 11 and 11i. A quick and easy way to get access to the information you need.

partner.oracle.com – If you are an Oracle partner, (and there are a lot of us), then this is the website for you. Oracle's partner website contains information on partner initiatives and provides customized portlets categorized into partner activity and job role.

technet.oracle.com - Technet's software download area allows visitors to download virtually any product Oracle markets. Visitors are also able to view Oracle documentation, download product whitepapers, search for jobs that use Oracle technologies and obtain information on Oracle education.

education.oracle.com – Oracle University's web site contains information on Oracle education including course descriptions, class schedules, self-study courses and certification requirements.

www.oracle.com - Oracle's home page on the web.

External Resources.
-------------------
Non-Oracle websites are also excellent sources of information. The Internet has an abundance of web sites containing hundreds of scripts, tips, tricks and techniques. Some of my favorites are:

www.dbazine.com - How can you not love this website? The contributing authors list reads like a "who's who" of the database industry. Topics range from entry-level discussions to information that even the most experienced database user would find enlightening. Experts like Mullins, Inmon, Ensor, Celko and Burleson provide readers with articles that are topical and interesting. Great articles and a pleasing, easy-to-navigate website makes DBAZine the place to go for database information.

www.orafaq.com - Orafaq discussion forums are excellent sources of information. Post a question to hundreds of experienced Oracle DBAs and you'll find out just how helpful Orafaq can be. Orafaq provides an intelligent search engine that visitors can use to search the discussion forums for topics of interest. The website also provides hints, tips, scripts, whitepapers and an on-line chatroom.

www.oracle.com/oramag - Oracle Corporation's own technical magazine. Oracle Magazine provides readers with product announcements, customer testimonials, technical information and upcoming events. Oracle magazine is available in hardcopy and on the web.

www.lazydba.com - Why write scripts when you can download them from the web? There are numerous web sites to choose from but this site is one of my favorites. The scripts are written by numerous contributors and, on the whole, well written. Find the script that solves your problem, download it, test and implement!

www.orsweb.com - Another excellent site that contains dozens of useful scripts.