September 2008

September 2008

THE LIGHTER SIDE
Top 10 Signs That You Are a DBA (Or Have Been One For Too Long)

The number one sign that you are a DBA (or have been one for too long) is that you debate whether to add an index to improve performance or revoke the access privileges of the complaining user!

The rest of the Top 10 signs

STUMP THE SPECIALISTS
Help, We Updated The Database By Mistake!This month’s question came to us from a customer:

“We inadvertently executed an UPDATE statement which did not have a complete WHERE clause. Thousands of records were updated instead of just one record. What’s the best way to recover the data?”

Iggy Fernandez of the Database Specialists team responds:

Logical recovery of just the selected records is your best bet. Here are some of your options

Exports

If you have logical backups (exports), you can import the backup copy of the affected table into another schema and use it as a basis for recovering the affected records.

Flashback

Alternatively, you can use Oracle’s “flashback” features to magically bring back the old data using the contents of the UNDO tablespace. In the following example, we inadvertently update all records in the Employees table and then magically bring back the old data even though the update has already been committed! Please note that the default value of UNDO_RETENTION is only 15 minutes. I recommend that you change it to a more appropriate value such as 12 hours or 24 hours. You should also increase the size of your UNDO tablespace appropriately.

A good overview of flashback features can be found in Oracle Database 10g Application Developer’s Guide.

SQL> SELECT employee_id,
2         salary
3    FROM employees
4   WHERE ROWNUM < 5;

EMPLOYEE_ID     SALARY
———– ———-
198       2600
199       2600
200       4400
201      13000

SQL> UPDATE employees
2     SET salary = 1.25 * salary;

107 rows updated.

SQL> SELECT employee_id,
2         salary
3    FROM employees
4   WHERE ROWNUM < 5;

EMPLOYEE_ID     SALARY
———– ———-
198       3250
199       3250
200       5500
201      16250

SQL> COMMIT ;

Commit complete.

SQL> MERGE INTO employees
2     USING (SELECT *
3              FROM employees AS OF TIMESTAMP SYSDATE – 1/48) previous_values
4     ON (employees.employee_id = previous_values.employee_id)
5     WHEN MATCHED THEN
6        UPDATE
7           SET salary = previous_values.salary
8        ;

107 rows merged.

SQL> SELECT employee_id,
2         salary
3    FROM employees
4   WHERE ROWNUM < 5;

EMPLOYEE_ID     SALARY
———– ———-
198       2600
199       2600
200       4400
201      13000

SQL> COMMIT ;

Commit complete.

LogMiner

You can also recover the affected data from the archived redo logs using LogMiner as in the following example. LogMiner has the ability to construct “undo SQL” that you can use to selectively reverse changes to your data. However, LogMiner only works as advertised if you have enabled “minimal supplemental logging.” According to the Oracle manuals, this “ensures that LogMiner (and any products building on LogMiner technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables.” The command that enables minimal supplemental logging is ALTER DATABASE ADD SUPPLEMENTAL LOG DATA.

thorough discussion of LogMiner can be found in Chapter 17 of Oracle Database 10g Database 10g Utilities.

SQL> EXECUTE DBMS_LOGMNR.add_logfile (logfilename => ‘C:\APP\IGNATIUS\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_09_20\O1_MF_1_77_4FBYS33F_.ARC’, options => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.add_logfile (logfilename => ‘C:\APP\IGNATIUS\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_09_20\O1_MF_1_78_4FBYS6J3_.ARC’, options => DBMS_LOGMNR.addfile);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.add_logfile (logfilename => ‘C:\APP\IGNATIUS\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_09_20\O1_MF_1_79_4FBYSO0H_.ARC’, options => DBMS_LOGMNR.addfile);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.start_logmnr (options => DBMS_LOGMNR.dict_from_online_catalog + DBMS_LOGMNR.print_pretty_sql + DBMS_LOGMNR.committed_data_only);

PL/SQL procedure successfully completed.

SQL> SELECT sql_undo
2    FROM v$logmnr_contents
3   WHERE seg_name = ‘EMPLOYEES’
4     AND ROWNUM < 5;

SQL_UNDO
————————————————–
update “HR”.”EMPLOYEES”
set
“SALARY” = 2600
where
“SALARY” = 3250 and
ROWID = ‘AAARcwAAFAAAABUAAA’;

update “HR”.”EMPLOYEES”
set
“SALARY” = 2600
where
“SALARY” = 3250 and
ROWID = ‘AAARcwAAFAAAABUAAB’;

update “HR”.”EMPLOYEES”
set
“SALARY” = 4400
where
“SALARY” = 5500 and
ROWID = ‘AAARcwAAFAAAABUAAC’;

update “HR”.”EMPLOYEES”
set
“SALARY” = 13000
where
“SALARY” = 16250 and
ROWID = ‘AAARcwAAFAAAABUAAD’;

The bottom line is that you must be prepared to recover from logical errors such as user errors. Here are some best practices that will ensure that you are well-positioned to do so.

  1. Supplement physical backups (RMAN backups) with logical backups (exports). Use a setting such as FLASHBACK_TIME to ensure the consistency of the exported data.
  2. Increase the value of UNDO_RETENTION from the default value of 15 minutes to a more appropriate value such as 12 hours or 24 hours. Also increase the size of the UNDO tablespace appropriately.
  3. Use the command ALTER DATABASE ADD SUPPLEMENTAL LOG DATA to ensure that LogMiner will have sufficient information to support chained rows and various storage arrangements such as cluster tables. Use the command ALTER DATABASE FORCE LOGGING command to prevent unlogged operations.

I hope this answer helps you. Best of luck to you.

 

 

SQL CHALLENGE
Capitalism In Action  iPod Shuffle

Refer to the explanation below or read Chapter 1 of Beginning Oracle Database 11g Administration by Iggy Fernandez, Senior Staff Consultant at Database Specialists. Construct a relational algebra expression which identifies the suppliers who supply all the parts supplied by TOOL TIME, INC. at cheaper prices. If you prefer to do so, you may submit an SQL query instead of a relational algebra expression but you must restrict yourself to set operators as in the example below and must not use aggregate functions or subqueries. Send your solution to askdbspecialists@dbspecialists.com. The winner will receive their choice of an iPod Shuffle or a $50 Amazon gift certificate, mailed anywhere in the world.

SQL has its roots in “relational algebra.” Relational operators such as Selection, Projection, Union, Difference, and Join produce new tables from old.

Consider the following tables:

CREATE TABLE supplier (
suppliername VARCHAR(32) NOT NULL,
PRIMARY KEY (suppliername)
);

CREATE TABLE part (
partname VARCHAR(32) NOT NULL,
PRIMARY KEY (partname)
);

CREATE TABLE quote (
suppliername VARCHAR(32) NOT NULL,
partname VARCHAR(32) NOT NULL,
quote NUMBER(8,2) NOT NULL,
PRIMARY KEY (suppliername, partname),
FOREIGN KEY (suppliername) REFERENCES supplier,
FOREIGN KEY (partname) REFERENCES part
);

Suppose that we need to identify the suppliers who supply all parts. We can do this in five easy steps. At each step, we use a relational operator and create an intermediate result table.

  1. In the first step, we use the Join operation and form an intermediate result table by concatenating records from the supplier table and the part table. All combinations of suppliername and partname occur in this table.
  2. In the second step, we use the Projection operation and form another intermediate result table by taking only the suppliername and partname columns from the quote table. The result is the list of valid suppliername and partname combinations.
  3. In the third step, we use the Difference operation and form a third intermediate result table by extracting only those rows from the intermediate result table created in the first step that are not to be found in the intermediate result table created in the second step. The result is the list of invalid suppliername and partname combinations. The occurrence of a certain combination of suppliername and partname in this new intermediate table indicates that the supplier in question does not supply the indicated part.
  4. In the fourth step, we use the Projection operation once again and form yet another intermediate result table by taking only the suppliername column from the intermediate result table created in the third step. The result is the list of suppliers who do not supply at least one part.
  5. In the fifth and final step, we use the Difference operation once again and obtain the final result we were seeking by extracting only those rows from the supplier table that do not occur in the intermediate result table created in the fourth step. The result is the list of suppliers who supply all parts.
We can specify the above sequence of steps in a single “relational algebra expression” as shown below.

supplier Minus Projection((part Join supplier) Minus Projection(quote))

The SQL equivalent is as follows. The correspondence with the above relational algebra expression is very obvious.

SELECT suppliername
FROM supplier
MINUS
SELECT suppliername
FROM (SELECT * FROM supplier CROSS JOIN part
MINUS
        SELECT suppliername, partname FROM quote);

 

 

 

 

SCRIPT OF THE MONTH
First Line of Defense 

Oracle supplies the “oerr” utility for Unix platforms only; it displays the text of an error message as well as the cause and action. Yong Huang has now written a Windows version of the oerr utility. To display the text associated with ORA-00001 simply type oerr ora 1.

C:\>oerr ora 1
ORA-00001 unique constraint (string.string) violated
Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
Action: Either remove the unique restriction or do not insert the key.

PET PEEVES
Please Sir, I Want Some Blank Lines 

Don’t you hate it when SQL*Plus cannot handle blank lines in your script? Arrgh!

SQL> SELECT employee_id,
2         salary
3
SQL>   FROM employees;
SP2-0734: unknown command beginning “FROM emplo…” – rest of line ignored.

CONFERENCE ROUNDUP
NoCOUG Autumn Conference Conference

The Northern California Oracle Users Group will hold its autumn conference, a whole day training event, on November at Chevron in San Ramon. Performance guru Jonathan Lewis will deliver the keynote address. NoCOUG makes its archive of conference presentations available on its website. Check it out.

Oracle OpenWorld Is Icumen In

Oracle OpenWorld is here again. People have flocked to San Francisco from all corners of the world to meet old friends once again and enjoy the sights and sounds of San Francisco once again; they might even find the time to attend a few technical sessions. The entire archive of last year’s presentations is available online.

PARTING QUOTE
On This Rock 

It’s my opinion that the relational model is rock solid, and “right,” and will endure. A hundred years from now, I fully expect relational systems still to be based on Codd’s relational model. Why? Because the foundations of that model–namely, set theory and predicate logic–are themselves rock solid in turn. Elements of predicate logic in particular go back well over 2,000 years, at least as far as Aristotle (384 — 322 BCE).

Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how we can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit our website for no-cost resources, white papers, conference presentations and handy scripts.

Sincerely,
David Wolff
CEO, Database Specialists, Inc.

dwolff@dbspecialists.com
(415) 344-0500 x48

Leave a Reply

Your email address will not be published. Required fields are marked *