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.

Functions

SQL functions are broken down into character functions, number functions, and date functions. Be sure you know how to use these functions for the Oracle Certified Partner (OCP) exam:

Text Functions

• lpad(x,y[,z]) and rpad(x,y[,z]) Return data in string or column x padded on the left or right side, respectively, to width y. The optional value z indicates the character(s) that lpad() or rpad() use to pad the column data. If no character z is specified, a space is used.
• lower(x), upper(x), and initcap(x) Return data in string or column x in lowercase or uppercase characters, respectively, or change the initial letter in the data from column x to a capital letter.
• length(x) Returns the number of characters in string or column x.
• substr(x,y[,z]) Returns a substring of string or column x, starting at the character in position number y to the end, which is optionally defined by the character appearing in the position z of the string.
• instr(x,y) Determines whether a substring y given can be found in string x.
• trim() A single-row function that behaves like a combination of ltrim() and rtrim(). Trim() accepts a string describing the data you would like to trim from a column value using the following syntax: trim([[keyword] 'x' from] column). Here keyword is replaced by leading, trailing, or both, or it's omitted. Also, x is replaced with the character to be trimmed, or it's omitted. If x is omitted, Oracle assumes it must trim whitespace. Finally, column is the name of the column in the table to be trimmed.

Arithmetic Functions

• abs(x) Obtains the absolute value for a number. For example, the absolute value of –1 is 1, whereas the absolute value of 6 is 6.
• round(x,y) Rounds x to the decimal precision of y. if y is negative, it rounds to the precision of y places to the left of the decimal point. This can also be used on DATE columns.
• ceil(x) Similar to executing round on an integer, except ceil always rounds up.
• floor(x) Similar to ceil, except floor always rounds down.
• mod(x,y) The modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced.
• sign(x) Displays an integer value corresponding to the sign of x: 1 if x is positive, -1 if x is negative.
• sqrt(x) The square root of x.
• trunc(x,y) Truncates x to the decimal precision of y. If y is negative, it truncates to y number of places to the left of the decimal point.
• vsize(x) The storage size in bytes for x.

List Functions

• greatest(x,y,…) Returns the highest value from the list of text, strings, numbers or dates.
• least(x,y,…) Returns the lowest value from the list of text strings, numbers, or dates.
• decode(column name, val1, sub1, val2, sub2,…) Works on the same principle as the if-then-else statement does in many common programming languages.

Date Functions

• add_months(x,y) Returns a date corresponding to date x plus y months.
• last_day(x) Returns the date of the last day of the month that contains date x.
• months_between(x,y) Returns a number of months between dates x and y. If date x is earlier than y, the result is negative; otherwise, the result is positive. If dates x and y contain the same day of different months, the result is an integer; otherwise, the result is a decimal.
• new_time(x,y,z) Returns the current date and time for date x in time zone y as it would be in time zone z.
• next_day(x) Identifies the name of the next day from given date, x.

Several conversion functions are available for transforming data from text to numeric datatypes and back, numbers to dates and back, text to ROWID and back, and so on.

Conversion Functions

• to_char(x) Converts the value x to a character or converts a date to a character string using formatting conventions.
• to_number(x) Converts nonnumeric value x to a number.
• to_date(x[,y]) Converts the nondate value x to a date using the format specified by y.
• to_multi_byte(x) Converts the single-byte character string x to multibyte characters according to national language standards.
• to_single_byte(x) Converts the multibyte character string x to single-byte characters according to national language standards.
• chartorowid(x) Converts the string of characters x into an Oracle ROWID.
• rowidtochar(x) Converts the ROWID value into the string of characters x of VARCHAR2 datatype.

Execute Immediate

This is one of those utilities that you use on a day to day basis without giving a second thought once you learn just enough to be dangerous.

The syntax is fairly straightforward:

EXECUTE IMMEDIATE {sql stmnt}
[INTO var.....]
[USING [IN | OUT | IN OUT] {bind}....]
[{RETURNING | RETURN} INTO {bind} ];

This parses and executes a sql statement in a single step. This is for any SQL statement except multi-row queries - for that you use a cursor.

Please note that the EXECUTE IMMEDIATE statement ends with a semi-colon but the SQL statement being executed does not.

Learning Oracle

I have seen so many questions posted on discussion boards all with the same subject - How do I Learn Oracle?

This is an unfortunate misunderstanding. Oracle is more than one product to learn. Everyone using Oracle, myself included, will know SQL, possibly something about the responsibilities of a DBA and something about operating systems at a minimum. There is no "one" thing to learn, no course where you start at the beginning and come out the other end "knowing" Oracle.

What tends to happen, is that you start out learning some SQL and PL/SQL. This should be the most fundamental part of starting a career in using Oracle. Then you either expand your skill set out into another area, DBA for example, or you concentrate on enhancing your knowledge. Unlike most careers, there is no clear path that once started down means you have burned your bridges and cannot back up and try something else.

After a few years most Oracle professionals have settled into a niche that suits them, some as developers, some as administrators, some as hybrids, some doing Forms/Reports etc.... The field of possible skills is so vast and increasing all the time that it is normal to specialise in one area. All areas of knowledge have one core commonality - SQL.

Learn it well.

Take half an hour a week to look up a function in the documentation and try it out. Look through the provided packages - there may be something there you could use. I guess my message is - There is only one way to learn Oracle start with SQL and keep learning until the day you leave.