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)———–660011Nechtě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;
Commit complete.
Řešení:
Použití technologie FLASHBACK, konkrétně FLASHBACK DATABASE.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – ProductionWith 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 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database (not started)RMAN> startup mountOracle instance starteddatabase mountedTotal System Global Area 767557632 bytesFixed Size 1264136 bytesVariable Size 251659768 bytesDatabase Buffers 511705088 bytesRedo Buffers 2928640 bytesRMAN> FLASHBACK DATABASE TO SCN=614746;Starting flashback at 2011-06-28 12:27:59using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=157 devtype=DISKstarting media recoverymedia recovery complete, elapsed time: 00:00:07Finished flashback at 2011-06-28 12:28:07RMAN> alter database open resetlogs;database openedSQL> SELECT SUM(salary) FROM hr.employees;SUM(SALARY)———–660011