Oracle 11gR2 database flashback function-prevents human logic errors and 11gr2flashback

Oracle 11gR2 database flashback function-prevents human logic errors and 11gr2flashback

Oracle 11gR2 database crash-Prevention of human logic errors

 

1. What is the role of Flashback on the dji erp system?

For some human operation errors, such as batch data deletion, we can use the Flashback function to restore them. The disadvantage is that the correct operations of other users during this period will also be lost.

A. Set up a flashback window, for example, 60 minutes. When a human error occurs, it can be restored to any time point in the past 60 minutes.

B. Set a recovery point at a certain time point. If something goes wrong in the future, it may take several months to recover to this point. In addition, it can only be restored to this time point, not any time point in the past few months.

 

Our requirements only fall in the flash backSolution B.

 

We can enable flashback on the production database or disaster recovery database.

A. After the flash back operation is completed using the disaster recovery database, we can query the table data at the corresponding time point, or use the static database at this time point to clone A new environment. Cloning to the official environment can restore production. However, the production downtime may take about half a day.

B. If flashback is enabled for production, you can directly flash back in production when a full database flash is required, and the downtime will be within one hour.

 

 

2. theoretical reference of flashback

Theoretically, we can all return to any time point in this time window. With the flashback log enabled, the image will be written to the flashback log every 10 minutes before the data block changes (the data block will not be recorded ). The point that can be rolled back depends on the flash log window in FRA. The window is rolled back with the pressure of space. Since flash back logs are sampled once in a period of time, can we only flash back to the time point in the unit of 10 minutes? What if I want to spend 10 minutes? Therefore, the redo log must be used for precise recovery during the flshback database operation. The rollback mechanism is as follows. Based on the target time to be restored, the closest data of the flashback log before the target time is taken out, then, each data block uses the redo log to go back to the target scn accurately. Of course, with the 1440 guaranteed time window, logs and archived logs in the time window will not be deleted, even if it meets the archiving log deletion policy.

 

 

 

3. Practice reference of solution

This includes enabling the flash back function and how to flash back.

You can enable the flashback function on the production database and disaster recovery database. The following describes how to enable the flashback function on the disaster recovery database.

 

1) through this query, we can know that the flash back function of our ERP production database and disaster recovery database is not enabled.

SQL> select flashback_on from v $ database;

FLASHBACK_ON

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

NO

 

2). Enable the flashback function. When I want to enable the flash back function in the DG database, I will prompt that the flash back recovery zone must be started first to specify where the flash back 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 is not enabled.

SQL>

 

3). Specify the flash recovery area (fra, flash recovery area ). We set the size to 80 GB (that is, flashback log and other files stored in fra, such as archive logs and backups, which cannot exceed 80 GB ), the two paths "/u01/erpdg/db/apps_st/fra" are used as the flash recovery zone.

SQL> alter system set db_recovery_file_dest_size = 80g;

System altered.

SQL> alter system set db_recovery_file_dest = '/u01/erpp1/db/pai_st/fra ';

System altered.

SQL>

 

4). Enable the flashback function again. Because our disaster recovery database restores logs of the production database in real time, an error is reported. We must first stop the log recovery process, that is, the MRP process, so that the disaster recovery database is at a static time point.

5) Stop the MRP process:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

6). Enable the flash back feature again.

 

7) What is the size of our flashback time window? View the value of db_flashback_retention_target, Which is 1440 minutes. This value can be adjusted. The window is rolled backward with the pressure of space. That is to say, for data within 1440 minutes (one day), the system must ensure that the data can be flashed back. If the space is insufficient to ensure that the data can be flashed back within 1440 minutes, the system would rather go down and not run, it won't make us unable to flash back. Is there a human error 1440 minutes away, so we cannot flash back? Not necessarily. If the space is sufficient, the system will keep the flash logs two or three days ago. Trigger the system to delete the flash back log, which is generally caused by the system space pressure (for example, if it is set to 90% of fra, the deletion will be triggered until 60% is deleted ).

 

 

8) at this time, we will re-start the mrp process of the dg database, and then apply the logs uploaded from the production database in real time.

 

SQL> alter database recover managed standby database using current logfile disconnect from session parallel 8;

 

Database altered.

 

SQL>

 

In this case, the flashback function of the entire DG database is enabled.

 

9). Flash back query. One day, some users mistakenly operate in the database, such as manually deleting data. For the data of a table, we can first consider using the flash-back query of undo.

Select * from dept as of timestamp to_timestamp ('2017-03-09 21:55:06 ', 'yyyy-mm-dd hh24: mi: ss'); -- flashback query, use rollback segments.

Select * from dept as of timestamp to_timestamp ('2017-03-09 21:55:06 ', 'yyyy-mm-dd hh24: mi: ss') minus select * from dept; -- Get the deleted record

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

-- Let the current session return to the specified time point, and a frozen database will be displayed. Only select, not DML.

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

For a flashback query, it is to query the past data of a table. Here, we will first discuss the needsFull-database flash backFor example, when some error operations are performed and many associated tables are involved, it is difficult for us to clarify exactly what to flash back, so we can use the unified full-database flash back operation.

 

 

10). Stop the MRP process before the full-database flashback:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

Database altered.

 

11). Full-database Flash back operation

SQL> flashback database to timestamp to_timestamp ('2017-07-22 14:22:24 ', 'yyyy-mm-dd hh24: mi: ss ');

Flashback complete.

SQL>

 

 

12). Verify. Now we have flashed back to the required time point.

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>

13). Repeatedly flash back to get the time point before the error occurs.

Alter database open read only;

Open in read-only mode, check whether the deleted content is found by mistake, and start from shutdown abort, re-flashback, and continuously adopt the binary method to approach the point before the deleted mode.

 

 

Oracle enabling flashback can fail, error message: ORA-00439: failed to start the work can: Flashback Database;

Flashback database requires that the database be in archive mode, and you must use resetlogs to open the database after the flash.
Check whether the database archive mode and flash back are enabled
SQL> select log_mode, open_mode, flashback_on from v $ database;

LOG_MODE OPEN_MODE FLASHBACK_ON
----------------------------------------
Archivelog read write no -- FLASHBACK_ON is NO, which indicates that the flash back feature is not enabled.

Oracle Advanced database application, lab report: Use flashback to restore tables, modes, and database-level accidental deletion of data flash back

First, you need to know the dependency:
Flashback database depends on: flashback log
Flashback drop depends on: Recycle Bin
Flashback table/query depends on: undo
To enable flashback, you must be in the mount status:
Alter system set db_flashback_retention_target = 2880 scope = both;
Alter database flashback on;
In addition, you need to use the transaction number to obtain the undo statement.
View the transaction number: select versions_xid, empno, ename, sal from tt01
Versions between timestamp minvalue and maxvalue
Order by empno;
View the undo_ SQL statement based on the obtained transaction number:
Select undo_ SQL from flashback_transaction_query
Where xid = 'versions _ xid' // the xid here is the versions_xid found above.
If only the tables accidentally deleted are flashed back:
Flashback table TT01 to before drop;
Flashback refers to the recently deleted table. You can also customize the accidentally deleted table as needed.
Steps:
View the content of the DB recycle bin: show recyclebin;
You can view the deleted table, and view the table structure according to the recyclebin name.
For example, desc "BIN $ 3naDFKEKFIDISB332DI"