Start notes for Flashback database

Start notes for Flashback database

To build an Oracle database for the testing department, you must meet the requirements that can often roll back the entire database to a certain time point. A simple method is to use one of Oracle's flash back features, "flash back database ".

Prerequisites: two types of logs are required to flash back to the database: Flash Back log and redo log. The concept of redo logs does not need to be discussed. The so-called flash log record is the opposite of the record of redo logs. We can simply think that, for example, if the redo log records an insert command, then the flashback log records the delete command related to this record. Of course, this is actually a very complicated process.

I will not introduce the principle of "flashback Database" here, because there is too much information to clarify it, nor will I introduce the operation method of "flashback Database, because it is very simple (execute "flashback database to scn... "Or" flashback database to timestamp... ").

1. Make sure that the archive mode is enabled before you enable DB flashback.

 

SQL> archive log list Database log mode              Archive Mode Automatic archival            Enabled ……

 

2. Check whether the flashback log is enabled

 

SQL> select LOG_MODE,FLASHBACK_ON from v$ database ;  LOG_MODE  FLASHBACK_ON ---------- --------------- ARCHIVELOG NO

 

3. Enable the flashback log function, that is, the flashback database function.

 

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.

 

An error is reported. The flash back function cannot be enabled. The "oerr" command Jun Long is the main character of this article.

4. Use the oerr command to troubleshoot Errors Based on the error code prompted above

 

$ oerr ORA 38709 38709, 00000, "Recovery Area is not enabled." // *Cause:  An ALTER DATABASE FLASHBACK ON command failed because the // Recovery Area was not enabled. // *Action: Set DB_RECOVERY_FILE_DEST to a location and retry.

 

5. Find the cause of the error from the above prompt: DB_RECOVERY_FILE_DEST is not preset

 

SQL> show parameter DB_RECOVERY_FILE_DEST  NAME TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest                string db_recovery_file_dest_size          big integer 0

 

6. Set DB_RECOVERY_FILE_DEST

 

SQL> alter system set db_recovery_file_dest= '/opt/oracle/fast_recovery_area' ; alter system set db_recovery_file_dest= '/opt/oracle/fast_recovery_area' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

 

7. Use the oerr command to troubleshoot

 

$ oerr ORA 19802 19802, 00000, "cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE" // *Cause: There are two possible cause for this error: // 1) The DB_RECOVERY_FILE_DEST parameter was in use when no // DB_RECOVERY_FILE_DEST_SIZE parameter was encountered while // fetching initialization parameter. // 2) An attempt was made to set DB_RECOVERY_FILE_DEST with the // ALTER SYSTEM command when no DB_RECOVERY_FILE_DEST_SIZE // was in use. // *Action: Correct the dependency parameter definitions and retry the command .

 

8. You need to set DB_RECOVERY_FILE_DEST_SIZE in the error cause and solution prompt.

 

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=20G scope=both; System altered. SQL> show parameter db_recovery  NAME TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest                string db_recovery_file_dest_size          big integer 20G

 

9. Set DB_RECOVERY_FILE_DEST

 

SQL> alter system set db_recovery_file_dest= '/opt/oracle/fast_recovery_area' scope=both;  System altered.

 

10. Enable the flashback log function, that is, the flashback database function.

 

SQL> alter database flashback on ; Database altered.

 

At this time, you can see the files with the extension. flb in the directory set by 'db _ recovery_file_dest '. They are the flash back logs.

 

$ ls o1_mf_98mpkdl6_.flb  o1_mf_98okkcs9_.flb

 

11. The storage duration of Flash logs is controlled by the db_flashback _ retention_target parameter (in minutes). Any flash logs that exceed the storage duration will be automatically deleted when the fast recovery zone space is tight.

 

SQL> show parameter db_flashback_retention_target  NAME TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target        integer 10080

 

 

Oracle 11g Flashback Data Archive (flash back Data archiving)

 

Oracle Flashback flash back Mechanism

 

Oracle Flashback database

 

Flashback table quick recovery of accidentally deleted data

 

Oracle backup recovery: Flashback flash back