Oracle 11GR2 Database Flashback-Preventing human logic errors

Oracle 11gR2 Database Flashback-- preventing human logic errors

1.Flashback What is the role of the DJI ERP system?

for some human-made errors, such as bulk deletion of data, we can recover through the flashback function. The disadvantage is that the correct operation of other users during this period of time is also lost.

A. set up a flashback window, such as a minute. When a human error occurs, you can revert to any point in time in the last 60 minutes.

B. to set a recovery point at a certain moment, after a problem, that fear after a few months, can return to this moment. Moreover, it can only be restored to this moment, not at any point in the months.

our demand only falls on the flashback Plan B .

we can open flashback in a production library or a disaster recovery repository.

A. after the flashback operation is done using the disaster recovery repository, we can query the table data at the corresponding time, or we can use the static database at this moment to clone a new environment. If cloned into a formal environment, it can play a role in restoring production. But at this time the production downtime may be about half a day.

B. If the production is open flashback, when the need for full-depot flash back, directly in production flash back, the downtime will be within 1 hours.

2. Flash back theory reference

Theoretically, we can all flash back to any point in time in this window. Open the Flashback log, each interval of ten minutes, will change the data block changes before the image is written to flashback log (the unchanged data block will not be remembered). The point at which this can be rolled back depends on the Flashback log window in FRA, which scrolls backwards as the pressure in the space. Since the flashback log is sampled once a time, can we only flash back to a point in time of 10 minutes? What if you want the moment in 10 minutes? Therefore, Flshback database is also required to use redo log to do accurate recovery. The fallback mechanism is this, based on the target time that you want to restore, take the closest flashback log sampled data that was before the target, and then take each chunk of redo log backwards to get to the target SCN exactly. Of course, we have 1440 of this guaranteed time window, and the log and archive logs in the time window are not deleted, even if it complies with the Archive log deletion policy.

3. reference to practice guidance for programme a

Includes the ability to turn on flashback and how to flash back.

It is possible to turn on the flashback in the production library and the disaster recovery storehouse, the method is similar, the following introduces the flashback function on the disaster reserve storehouse .

1 ). Through this query, we can know that our ERP production library, disaster preparedness database flashback function is not open.

Sql> select flashback_on from V$database;

flashback_on

------------------

NO

2 ). Turn on the flashback function. When I want to turn on the flash back function in the DG Library, I am prompted to start the Flashback recovery area and specify where the flashback log should be placed.

Sql> ALTER DATABASE flashback on;

ALTER DATABASE Flashback on

*

ERROR at line 1:

Ora-38706:cannot turn on FLASHBACK DATABASE logging.

Ora-38709:recovery area isn't enabled.

Sql>

3 ). Specifies the Flash recovery area (FRA, flashback Recovery Zone). We set its size to 80G (that is, flashback log, and other files stored in the Fra area, such as archived logs, backups, etc., must not exceed 80G), and the "/u01/erpdg/db/apps_st/fra" two paths as a flashback recovery area.

Sql> alter system set db_recovery_file_dest_size=80g;

System altered.

Sql> alter system set db_recovery_file_dest= '/u01/erpp1/db/apps_st/fra ';

System altered.

Sql>

4 ). Turn on the flashback function again, because our disaster recovery database recovers the log of the production library in real time, so it will error. We have to stop. The process of recovering logs, that is, the MRP process, makes the disaster preparedness Library at a standstill point in time.

5 ). To stop the MRP process:

sql> ALTER database RECOVER MANAGED STANDBY database CANCEL;

Database altered.

6 ). Turn on the flashback feature again and it's successful.

7 ). How big is our flashback window at this time? By looking at the value of the parameter db_flashback_retention_target, it is 1440 minutes. This value can be adjusted. This window is scrolled backwards with the pressure of the space. That is, within 1440 minutes (one day) of the data, the system must be guaranteed to flash back, if the space is not enough to guarantee 1440 minutes of flashback, the system would rather be down to not work, and will not let us not flash back. Is it not 1440 minutes of human error, we must not flash back? Not necessarily, if the space is sufficient, two days, three days before the flashback log, the system will still be retained. Triggering the system to delete the flashback log, usually the system space pressure (for example, set to reach Fra 90% trigger Delete, delete to 60%).

8 ). At this point we will be the DG Library of the MRP process restart, and then real-time application of production library to pass the log.

sql> ALTER DATABASE recover managed standby database using current logfile disconnect from session parallel 8;

Database altered.

Sql>

at this point, the entire DG Library's flashback function is turned on.

9 ). Flash back query. When one day, there are users in the database misoperation, such as artificially deleted data. If it is a table of data, in fact here we can first consider using Undo's flashback query.

SELECT * FROM dept as of Timestamp To_timestamp (' 2012-03-09 21:55:06 ', ' yyyy-mm-dd hh24:mi:ss ');-- flashback query, using rollback segments.

SELECT * FROM dept as of Timestamp To_timestamp (' 2012-03-09 21:55:06 ', ' yyyy-mm-dd hh24:mi:ss ') minus select * from DEP t;-- to get deleted records

Execute SYS.dbms_flashback.enable_at_time (To_timestamp (' 2012-03-09 21:55:06 ', ' yyyy-mm-dd hh24:mi:ss '));

-- To return the current session to a specified point in time, you will see a frozen version of the database, only select, not DML.

Note: The table structure cannot be changed. That is, the flashback cannot span the DDL.

For a flashback query, it is querying the past data of a table. Here, we first discuss the need for the whole library flashback , such as doing some error operations, involving a lot of related tables, we also have difficulty to clarify what to flash back when, we can use a unified whole library flashback operation.

Ten ). It is also necessary to stop the MRP process before the whole library flashback:

sql> ALTER database RECOVER MANAGED STANDBY database CANCEL;

Database altered.

One ). Full Library Flash back operation

Sql> Flashback database to timestamp to_timestamp (' 2014-07-22 14:22:24 ', ' yyyy-mm-dd hh24:mi:ss ');

Flashback complete.

Sql>

A ). Verify. At this point we have flashed back to the time required.

Sql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';

Session altered.

Sql> Col Max (first_time) for A30

Sql> select Controlfile_time from V$database;

Controlfile_time

-------------------

2014-07-22 14:22:25

Sql>

- ). Repeatedly flashback to get the point at which the error occurred just before the time.

ALTER DATABASE open read only;

Open in read-only mode to see if the deleted content was found, constantly starting from shutdown abort , re-flashback, and using the dichotomy to approximate the point before the deletion mode.