Problém 53:

Jak mohu vrátit nechtěné změny v databázi, které jsem udělal (za pomocí FLASHBACKU databáze)?
Alternativa vůči obnově ze záloh.

Původní hodnota platů:
SQL> SELECT SUM(salary) FROM hr.employees;

SUM(SALARY)
-----------
660011

Nechtěný update:
SQL> update employees e set salary = least(e.salary,(select (min_salary + max_salary)/2 * 1.10 from jobs j where j.job_id = e.job_id)) where job_id not like 'AD_%';

103 rows updated.

SQL>
SQL> commit;

Řešení:

Použití technologie FLASHBACK, konkrétně FLASHBACK DATABASE.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Jun 28 12:26:27 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     767557632 bytes

Fixed Size                     1264136 bytes
Variable Size                251659768 bytes
Database Buffers             511705088 bytes
Redo Buffers                   2928640 bytes

RMAN> FLASHBACK DATABASE TO SCN=614746;

Starting flashback at 2011-06-28 12:27:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished flashback at 2011-06-28 12:28:07

RMAN> alter database open resetlogs;

database opened

SQL> SELECT SUM(salary) FROM hr.employees;

SUM(SALARY)
-----------
660011