 |
|
Oracle Tips by Burleson |
Recover an Oracle Database
In this exercise we will use the rollback
command to re-apply undo logs to your Oracle database. As we know,
Oracle keeps copies of all row before-images in the undo logs, and we
can use the rollback command to re-apply the before images when we
make an error.
Let’s begin by issuing a bad update against
our Oracle database:
SQL> update
book set book_retail_price=book_retail_price*4;
20 rows
updated.
Here we have quadrupled the price of every
book in the Oracle database! We can verify that this change has been
made by issuing the following query.
SQL> select
substr(book_title,1,20), book_retail_price from book;
SUBSTR(BOOK_TITLE,1, BOOK_RETAIL_PRICE
-------------------- ------------------------------
windows sucks 139.8
piano greats 131.8
DOS for dummies 79.8
The zen of auto repa 399.8
zero loss finance 87.8
operations research 179.8
non violins in the w 47.8
UNIX for experts 155.8
pay no taxes and go 43.8
the fall of microsof 79.8
writers market 91.8
managing stress 159.8
bears are people too 139.8
reduce spending the 111.8
the willow weeps no 119.8
Oracle 10g sql tuning 199.8
was george washingto 99.8
cooking light
99.8 never eat boogers
43.8
how to housebreak yo 119.8
Let’s assume that quadrupling the price was a
mistake, and we need to put the row data back into its previous state,
before our update statement. We can issue the rollback command to
apply the before-images of the row data from our undo logs:
SQL>
rollback;
Rollback
complete.
Now, we can re-issue our previous query to
ensure that the data is back in its original state:
SQL> select
substr(book_title,1,20), book_retail_price from book;
SUBSTR(BOOK_TITLE,1, BOOK_RETAIL_PRICE
-------------------- ------------------------------
windows sucks 34.95
piano greats 32.95
DOS for dummies 19.95
The zen of auto repa 99.95
zero loss finance 21.95
operations research 44.95
non violins in the w 11.95
UNIX for experts 38.95
pay no taxes and go 10.95
the fall of microsof 19.95
writers market 22.95
managing stress 39.95
bears are people too 34.95
reduce spending the 27.95
the willow weeps no 29.95
Oracle 10g sql tuning 49.95
was george washingto 24.95
cooking light 24.95
never eat boogers
10.95
how to housebreak yo 29.95
As we can see, the rollback command is great
for re-applying changes. If you are using Oracle 10g, you can use the
new flashback query functionality to re-apply changes, even after they
are committed to the database. For more information on Oracle 10g
Flashback, see this link:
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
